Database Schema (Prisma)

Relevant source files

The following files were used as context for generating this wiki page:

• services/api/prisma/schema.prisma
• services/api/src/lib/prisma.ts
• services/api/src/lib/prismaRead.ts
• services/api/package.json
• blueprint.md
• services/api/test/api.integration.test.ts
• services/api/src/routes/admin.ts
• services/api/scripts/create-admin.ts

The database layer for the email platform is managed by Prisma, an open-source ORM for Node.js and TypeScript. It serves as the single source of truth for the application's data models and provides a type-safe client for database queries. The underlying database is PostgreSQL, as indicated by the provider in the schema file.

This document outlines the database schema, client initialization, data models, and their relationships as defined in the project's source code. The schema is designed to support a multi-tenant email service, including user management, domain handling, inbox creation, message storage, and administrative functions.

Prisma Client Initialization

The project utilizes a read/write splitting pattern by initializing two separate Prisma clients. This architecture is designed to distribute database load, directing write operations to a primary database and read operations to a replica.

Primary Client (Writes)

The primary Prisma client is configured for all database operations, but is intended for write-heavy tasks like create, update, and delete. It connects to the main database specified by the DATABASE_URL environment variable.

Sources: services/api/src/lib/prisma.ts

Read Replica Client

A second client, prismaRead, is configured to connect to a read-replica database specified by DATABASE_READ_URL. This client is intended for all read-only queries (findMany, findUnique, count, etc.) to improve performance and enable horizontal read scaling. If DATABASE_READ_URL is not set, it defaults to the primary database.

// services/api/src/lib/prismaRead.ts:8-16
const prismaRead = new PrismaClient({
    datasources: {
        db: {
            url: appConfig.databaseReadUrl,
        },
    },
    log: process.env.NODE_ENV === 'development' ? ['query', 'warn', 'error'] : ['warn', 'error'],
});

Sources: services/api/src/lib/prismaRead.ts:8-16

Entity Relationship Diagram (ERD)

The following diagram illustrates the primary entities and their relationships within the database schema.

This diagram shows the core relationships: a User owns multiple Domains. Each Domain can have many Inboxes. Each Inbox can contain many Messages, and each Message can have multiple Attachments.

Sources: services/api/prisma/schema.prisma, blueprint.md:213-223

Data Models

The schema is composed of several models that represent the application's core entities.

User Model

The User model represents an account in the system. It handles authentication, authorization, and ownership of resources.

Field	Type	Description
id	String	Unique identifier (UUID).
email	String	User's email address (unique).
passwordHash	String	Hashed password for authentication.
role	UserRole	User's role (USER or ADMIN).
tier	Tier	Subscription tier (FREE, STARTER, PROFESSIONAL, ENTERPRISE).
emailVerified	DateTime?	Timestamp when the user's email was verified.
verificationToken	String?	Token sent for email verification.
twoFactorEnabled	Boolean	Flag indicating if 2FA is enabled.
twoFactorSecret	String?	Encrypted 2FA secret.
isDisabled	Boolean	Flag to disable a user account.
domains	Domain[]	A list of domains owned by the user.
auditLogs	AuditLog[]	A list of audit log entries related to the user.

Sources: services/api/prisma/schema.prisma:11-42, services/api/src/routes/billing.ts:13-18

Domain Model

The Domain model represents a custom domain added by a user to receive emails.

Field	Type	Description
id	String	Unique identifier (UUID).
name	String	The domain name (e.g., example.com). Unique.
ownerId	String	Foreign key linking to the User who owns the domain.
status	DomainStatus	Verification status (PENDING, VERIFIED, FAILED).
verificationToken	String	Token used for DNS-based domain ownership verification.
isPublic	Boolean	If true, other users can create inboxes on this domain.
contributionStatus	ContributionStatus	Status for making a domain public (NONE, PENDING_REVIEW, APPROVED, REJECTED).
inboxes	Inbox[]	A list of inboxes associated with this domain.

Sources: services/api/prisma/schema.prisma:44-61, services/api/src/test/shared_domains.test.ts:69-75

Inbox Model

An Inbox represents a specific email address within a domain.

Field	Type	Description
id	String	Unique identifier (UUID).
domainId	String	Foreign key linking to the Domain.
localPart	String	The part of the email address before the @ (e.g., hello).
deletedAt	DateTime?	Timestamp for soft-deletion.
messages	Message[]	A list of messages received by this inbox.
filters	EmailFilter[]	A list of email filters for this inbox.
labels	Label[]	A list of labels for this inbox.

Sources: services/api/prisma/schema.prisma:63-78

Message Model

The Message model stores the content and metadata of a received email.

Field	Type	Description
id	String	Unique identifier (UUID).
inboxId	String	Foreign key linking to the Inbox.
subject	String?	The email subject.
fromAddress	String?	The sender's email address.
toAddress	String?	The recipient's email address.
textBody	String?	The plain text content of the email.
htmlBody	String?	The HTML content of the email.
isRead	Boolean	Flag indicating if the message has been read.
isPinned	Boolean	Flag indicating if the message is pinned.
snoozedUntil	DateTime?	Timestamp until which the message is snoozed.
deletedAt	DateTime?	Timestamp for soft-deletion.
attachments	Attachment[]	A list of attachments for this message.
labels	MessageLabel[]	The labels applied to this message.

Sources: services/api/prisma/schema.prisma:80-106, services/api/src/routes/messages.ts:258-261

Other Key Models

• Attachment: Stores metadata about email attachments, including filename, MIME type, and a storageKey pointing to the file's location (e.g., on disk or in S3).
• AuditLog: Records significant actions performed by users, such as login, password changes, and resource deletion. This is crucial for security and administration.
• EmailFilter & Label: These models allow users to organize their emails. EmailFilter defines rules to automatically perform actions on incoming messages, while Label allows for manual categorization.
• AbuseReport: Allows users or systems to report abusive behavior.
• Rule: A system-level model for defining global block/allow rules.

Sources: services/api/prisma/schema.prisma:108-189, services/api/src/routes/admin.ts:28-35, services/api/src/routes/filters.ts

Migrations and Database Management

Database schema changes are managed through Prisma Migrate. The package.json file defines scripts for development and deployment workflows.

• prisma:migrate: (prisma migrate dev) Used during development to create and apply new migrations.
• prisma:deploy: (prisma migrate deploy) Used in production environments to apply pending migrations.
• prisma:generate: (prisma generate) Generates the type-safe Prisma Client based on the schema.prisma file.

Sources: services/api/package.json:10-12

Database Seeding and Testing

The database can be seeded with initial data, such as a default admin user. The create-admin.ts script demonstrates how to create or update an admin user programmatically.

Sources: services/api/scripts/create-admin.ts

In the testing environment, the database is reset before each test to ensure a clean state. This is achieved by deleting all records from the tables in a specific order to respect foreign key constraints.

// services/api/test/api.integration.test.ts:11-19
const resetDb = async () => {
  await prisma.attachment.deleteMany();
  await prisma.message.deleteMany();
  await prisma.inbox.deleteMany();
  await prisma.abuseReport.deleteMany();
  await prisma.rule.deleteMany();
  await prisma.domain.deleteMany();
  await prisma.auditLog.deleteMany();
  await prisma.user.deleteMany();
};

Sources: services/api/test/api.integration.test.ts:11-19

Conclusion

The database schema, managed via Prisma, is the backbone of the email platform. It is well-structured to support the core features of a multi-tenant email service, including user and domain management, message handling, and administrative oversight. The use of a read-replica client, detailed auditing, and soft-deletion mechanisms demonstrates a design that considers performance, security, and data integrity. The schema is flexible enough to accommodate future features like advanced filtering, billing tiers, and abuse control.