PostgreSQL Schema & Prisma Configuration

Relevant source files

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

• backend/src/infrastructure/database/prisma/schema.prisma
• backend/src/config/database.ts
• backend/src/infrastructure/database/repositories/BookingRepository.ts
• infrastructure/terraform/main.tf
• infrastructure/aws/cdk/lib/atlasclean-stack.ts
• backend/package.json
• docker-compose.yml

The PostgreSQL Schema and Prisma Configuration define the data persistence layer for the AtlasCleanAI Enterprise platform. This system manages transactional data for users, bookings, payments, and cleaner management. Prisma acts as the Object-Relational Mapper (ORM), providing type-safe database access and automated migration workflows.

Sources: backend/src/infrastructure/database/prisma/schema.prisma, README.md:16-24

Data Models and Relationships

The database schema consists of six primary entities that facilitate the cleaning service lifecycle. Relationships ensure data integrity between users who book services, cleaners who perform them, and the resulting payments and reviews.

Entity Relationship Diagram

The following diagram illustrates the relationships between core database entities:

Core Tables Summary

Model	Primary Key	Description
User	id (UUID)	Contains identity and profile data for all platform roles.
Cleaner	id (UUID)	Stores professional details, ratings, and skills for cleaning staff.
Service	id (UUID)	Defines service types, base prices, and expected durations.
Booking	id (UUID)	Central entity linking users, cleaners, and specific service sessions.
Payment	id (UUID)	Tracks transaction status and external provider (Stripe) references.
Review	id (UUID)	Stores user-submitted ratings and feedback for completed bookings.

Sources: backend/src/infrastructure/database/prisma/schema.prisma:41-143

Prisma Configuration

Prisma manages the connection to PostgreSQL and generates a type-safe client for the Node.js backend.

Database Connection and Client Initialization

The prisma client is initialized as a singleton to prevent exhausting database connection pools. In development, the client logs errors and warnings, while production environments restrict logging to errors only.

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    datasources: {
      db: {
        url: config.databaseUrl,
      },
    },
    log: config.env === 'development' ? ['error', 'warn'] : ['error'],
  });

Sources: backend/src/config/database.ts:7-17

Database Management Scripts

The project uses standard Prisma CLI commands for schema synchronization and client generation:

• npm run db:generate: Generates the Prisma Client based on the schema.
• npm run db:migrate: Creates and applies database migrations.
• npm run db:push: Pushes schema changes directly to the database without creating migration files.

Sources: backend/package.json:11-13

Infrastructure and Provisioning

The PostgreSQL database is provisioned across different environments using automated infrastructure-as-code and containerization.

Deployment Tiers

1. Local Development: A postgres:15-alpine container runs via Docker Compose, exposing port 5432.
2. AWS Deployment (CDK/Terraform): The platform uses Amazon RDS for PostgreSQL (version 15.5). In production, it uses db.t4g.medium instances with Multi-AZ deployment and deletion protection enabled.

Sources: docker-compose.yml:7-21, infrastructure/terraform/main.tf:230-252, infrastructure/aws/cdk/lib/atlasclean-stack.ts:114-136

Network Security

The database resides in isolated private subnets. Access is restricted via Security Groups, permitting ingress only from the backend ECS Fargate service on port 5432.

Data Access Layer (Repositories)

The application interacts with the database through the Repository pattern. The BookingRepository uses the Prisma client to perform CRUD operations and handle complex queries, such as identifying scheduling conflicts.

Booking Persistence Flow

When a booking is created, the repository converts the domain entity into a Prisma-compatible data structure:

  async create(booking: Booking): Promise<Booking> {
    const record = await prisma.booking.create({
      data: {
        id: booking.id,
        userId: booking.userId,
        cleanerId: booking.cleanerId,
        // ... mapping fields
        amount: new Prisma.Decimal(booking.amount.amount),
        address: booking.address.toJSON() as unknown as Prisma.InputJsonValue,
      },
    });
    return toDomain(record);
  }

Sources: backend/src/infrastructure/database/repositories/BookingRepository.ts:55-76

Query Optimization

The schema includes specific indexes to optimize frequent queries:

• @@index([userId, startTime]) on Booking: Optimizes retrieval of user history.
• @@index([cleanerId, startTime]) on Booking: Accelerates cleaner schedule lookups.
• @@index([cleanerId, createdAt]) on Review: Improves performance for cleaner rating summaries.

Sources: backend/src/infrastructure/database/prisma/schema.prisma:98-99, 142

PostgreSQL and Prisma provide the foundational persistence layer, ensuring that all platform activities—from AI-driven scheduling to payment processing—remain durable and consistent across local and cloud environments.