Database Schema
Relevant source files
The following files were used as context for generating this wiki page:The MessageJS database schema is the persistence layer for the core backend service. It is designed to store all data related to users, projects, service configurations, message templates, and message logs. The schema is defined using Prisma, which generates a TypeScript client for database interactions and manages migrations.
The database technology used is PostgreSQL, as specified in the development environment configuration. The schema is structured to support multi-tenancy, where each User can own multiple Projects, and all subsequent resources like services and messages are scoped to a specific project. This ensures data isolation between different users and applications.
Sources: packages/core/prisma/schema.prisma:9-12, docker-compose.yml:16-18, packages/core/package.json:20
Entity Relationship Diagram
The following diagram illustrates the relationships between the main data models in the MessageJS database.
This ERD shows the central role of the Project model, which links a User to their Service configurations, Templates, and MessageLogs. All relationships from Project to its child tables are configured with onDelete: Cascade, meaning that deleting a project will also delete all associated resources.
Sources: packages/core/prisma/schema.prisma:41-42, 53-54, 65-66, 78-79, 90-91, 96-97
Enums
The schema defines two enums to enforce data integrity for specific fields at the database level.
Sources: packages/core/prisma/schema.prisma:21-36, packages/core/prisma/migrations/20251029214807_init/migration.sql:2-8
ServiceType
This enum defines the set of allowed third-party messaging services.
| Value | Description |
|---|---|
WHATSAPP | Represents the WhatsApp messaging service. |
TELEGRAM | Represents the Telegram messaging service. |
TWILIO_SMS | Represents the Twilio SMS service. |
Sources: packages/core/prisma/schema.prisma:22-26
MessageStatus
This enum tracks the lifecycle of a message as it is processed by the system.
| Value | Description |
|---|---|
QUEUED | The message has been accepted by the API but not yet sent. (Default) |
SENT | The message has been successfully sent to the third-party provider. |
DELIVERED | Confirmation of delivery has been received from the provider. |
FAILED | The message failed to send from the system or was rejected by the provider. |
UNDELIVERED | The provider confirmed the message could not be delivered to the recipient. |
Sources: packages/core/prisma/schema.prisma:28-35, packages/core/src/types/dataModels.ts:80-85
Data Models
The schema consists of five core models, which map to tables in the PostgreSQL database.
User
The User model represents a developer account in the MessageJS system.
| Field | Type | Constraints & Defaults | Description |
|---|---|---|---|
id | String | @id @default(cuid()) | Unique identifier for the user (CUID). |
email | String | @unique | The user's unique email address. |
passwordHash | String | Hashed password for authentication. | |
createdAt | DateTime | @default(now()) | Timestamp of when the user was created. |
projects | Project[] | Relation to the projects owned by the user. |
Sources: packages/core/prisma/schema.prisma:38-45, packages/core/prisma/migrations/20251029214807_init/migration.sql:11-17
Project
The Project model is a container for services, templates, API keys, and message logs. It acts as the primary organizational unit for a user.
| Field | Type | Constraints & Defaults | Description |
|---|---|---|---|
id | String | @id @default(cuid()) | Unique identifier for the project (CUID). |
name | String | The user-defined name of the project. | |
apiKey | String | @unique | The unique API key for authenticating requests. |
createdAt | DateTime | @default(now()) | Timestamp of when the project was created. |
userId | String | Foreign Key to User.id | The ID of the user who owns the project. |
user | User | @relation | Relation to the parent User model. |
services | Service[] | Relation to the services within the project. | |
templates | Template[] | Relation to the templates within the project. | |
messageLogs | MessageLog[] | Relation to the message logs for the project. |
Sources: packages/core/prisma/schema.prisma:47-60, packages/core/prisma/migrations/20251029214807_init/migration.sql:20-28
Service
The Service model stores the configuration for a single third-party messaging service (e.g., a specific WhatsApp account).
| Field | Type | Constraints & Defaults | Description |
|---|---|---|---|
id | String | @id @default(cuid()) | Unique identifier for the service (CUID). |
type | ServiceType | The type of messaging service (e.g., WHATSAPP). | |
credentials | String | AES-256 encrypted credentials blob for the third-party service. | |
createdAt | DateTime | @default(now()) | Timestamp of when the service was created. |
projectId | String | Foreign Key to Project.id | The ID of the project this service belongs to. |
project | Project | @relation | Relation to the parent Project model. |
messageLogs | MessageLog[] | Relation to message logs sent via this service. |
Sources: packages/core/prisma/schema.prisma:62-73, packages/core/prisma/migrations/20251029214807_init/migration.sql:31-38
Template
The Template model stores reusable message templates.
| Field | Type | Constraints & Defaults | Description |
|---|---|---|---|
id | String | @id @default(cuid()) | Unique identifier for the template (CUID). |
name | String | The user-defined name of the template. | |
body | String | The template content, with placeholders (e.g., {{code}}). | |
createdAt | DateTime | @default(now()) | Timestamp of when the template was created. |
projectId | String | Foreign Key to Project.id | The ID of the project this template belongs to. |
project | Project | @relation | Relation to the parent Project model. |
Sources: packages/core/prisma/schema.prisma:75-84, packages/core/prisma/migrations/20251029214807_init/migration.sql:41-48
MessageLog
The MessageLog model tracks every message sent through the system, serving as an audit log.
| Field | Type | Constraints & Defaults | Description |
|---|---|---|---|
id | String | @id @default(cuid()) | Unique identifier for the message log (CUID). |
status | MessageStatus | @default(QUEUED) | The current delivery status of the message. |
recipient | String | The recipient's identifier (e.g., phone number). | |
externalMessageId | String? | The unique ID from the third-party provider (e.g., Twilio SID). | |
error | String? | Stores the error message if the status is FAILED. | |
timestamp | DateTime | @default(now()) | Timestamp of when the message was logged. |
projectId | String | Foreign Key to Project.id | The ID of the project this message belongs to. |
project | Project | @relation | Relation to the parent Project model. |
serviceId | String | Foreign Key to Service.id | The ID of the service used to send the message. |
service | Service | @relation | Relation to the Service model used for sending. |
An index is defined on projectId and serviceId to optimize queries that filter messages by project and service.
Sources: packages/core/prisma/schema.prisma:86-104, packages/core/prisma/migrations/20251029214807_init/migration.sql:51-62