Skip to main content

Database Schemas - EML Backend

1. Introduction​

This document details the database schemas used in EML Backend, including models for MongoDB and relational databases such as PostgreSQL or MySQL.

2. MongoDB Schemas​

The following models are defined using Mongoose.

🔹 Users Schema (users.model.ts)​

import { Schema, model } from 'mongoose';

const UserSchema = new Schema({
  name: { type: String, required: true },
  email: { type: String, unique: true, required: true },
  password: { type: String, required: true },
  role: { type: String, enum: ['admin', 'agent', 'user'], required: true },
  createdAt: { type: Date, default: Date.now }
});

export const UserModel = model('User', UserSchema);

🔹 Messages Schema (messages.model.ts)​

import { Schema, model } from 'mongoose';

const MessageSchema = new Schema({
  senderId: { type: Schema.Types.ObjectId, ref: 'User', required: true },
  recipientId: { type: Schema.Types.ObjectId, ref: 'User', required: true },
  message: { type: String, required: true },
  status: { type: String, enum: ['sent', 'delivered', 'read'], default: 'sent' },
  createdAt: { type: Date, default: Date.now }
});

export const MessageModel = model('Message', MessageSchema);

🔹 Calls Schema (calls.model.ts)​

import { Schema, model } from 'mongoose';

const CallSchema = new Schema({
  callerId: { type: Schema.Types.ObjectId, ref: 'User', required: true },
  receiverId: { type: Schema.Types.ObjectId, ref: 'User', required: true },
  duration: { type: Number, required: true },
  status: { type: String, enum: ['ongoing', 'completed', 'failed'], default: 'ongoing' },
  createdAt: { type: Date, default: Date.now }
});

export const CallModel = model('Call', CallSchema);

🔹 Notifications Schema (notifications.model.ts)​

import { Schema, model } from 'mongoose';

const NotificationSchema = new Schema({
  userId: { type: Schema.Types.ObjectId, ref: 'User', required: true },
  message: { type: String, required: true },
  type: { type: String, enum: ['email', 'sms', 'push'], required: true },
  status: { type: String, enum: ['pending', 'sent', 'failed'], default: 'pending' },
  createdAt: { type: Date, default: Date.now }
});

export const NotificationModel = model('Notification', NotificationSchema);

3. Relational Database Schemas (PostgreSQL/MySQL)​

The backend also utilizes TypeORM, indicating the use of a relational database such as PostgreSQL or MySQL.

🔹 Reports Model (report.entity.ts)​

import { Entity, PrimaryGeneratedColumn, Column, CreateDateColumn } from 'typeorm';

@Entity('reports')
export class Report {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  type: string;

  @Column()
  generatedBy: string;

  @Column({ type: 'jsonb' })
  data: object;

  @CreateDateColumn()
  createdAt: Date;
}

🔹 Note: The use of jsonb suggests that PostgreSQL is the preferred database, as it supports this data type.

🔹 User Sessions Model (session.entity.ts)​

import { Entity, PrimaryGeneratedColumn, Column, ManyToOne, CreateDateColumn } from 'typeorm';
import { User } from './user.entity';

@Entity('sessions')
export class Session {
  @PrimaryGeneratedColumn()
  id: number;

  @ManyToOne(() => User, user => user.sessions)
  user: User;

  @Column()
  token: string;

  @CreateDateColumn()
  createdAt: Date;
}

4. Conclusions​

  • MongoDB is used to store dynamic information such as users, messages, calls, and notifications.
  • PostgreSQL or MySQL (TypeORM) is used in modules such as Reports and User Sessions.
  • Different databases optimize system performance based on the type of data being stored.

🚀 This document will be updated as the backend architecture evolves.