Database Overview

The database acts as the central foundation for data storage, query processing, and ensuring rapid user access to information. While Dopamine defaults to PostgreSQL for its robustness and feature set, Prisma's flexibility allows you to use many other database engines including MySQL or SQLite. Prisma serves as the ORM layer, providing type-safe database operations regardless of your chosen database engine.

Technology Stack

PostgreSQL

PostgreSQL is the primary database engine for Dopamine. It's a powerful, open-source relational database that provides:

• ACID compliance for reliable transactions
• Advanced SQL features for complex queries
• JSON support for flexible data structures
• Excellent performance with proper indexing
• Strong consistency for critical business data

Prisma ORM

Prisma is used as the database toolkit and ORM, providing:

• Type-safe database access with auto-generated TypeScript types
• Intuitive data modeling with the Prisma schema language
• Database migrations with version control
• Query optimization and connection pooling
• Prisma Studio for visual database management

Schema Design Patterns

Naming Conventions

The schema follows consistent naming patterns:

• Model names use PascalCase
• Field names use camelCase
• Database columns use snake_case
• Table names use snake_case with core_ prefix. Avoid conflicts with postgres reserved words.

// Model names use PascalCase
model WorkspaceMember {
  // Field names use camelCase
  workspaceId String @map("workspace_id") @db.Uuid
 
  // Database columns use snake_case
  createdAt DateTime @default(now()) @map("created_at")
 
  // Table names use snake_case with core_ prefix
  @@map("core_workspace_member")
}

UUID Primary Keys

All entities use UUID primary keys for:

• Security: Prevents enumeration attacks
• Distribution: Works well in distributed systems
• Uniqueness: Globally unique identifiers

Timestamps

Every model includes standard timestamps:

• createdAt: Record creation time
• updatedAt: Last modification time

Cascade Deletions

Proper cascade rules ensure data integrity:

model WorkspaceMember {
  workspace Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade)
  user      User      @relation(fields: [userId], references: [id], onDelete: Cascade)
}

Development Setup

Docker Configuration

PostgreSQL runs in Docker containers for consistent development:

# Main database (port 5432)
postgres:
  image: postgres:17-alpine
  environment:
    - POSTGRES_DB=***
    - POSTGRES_USER=***
    - POSTGRES_PASSWORD=***
 
# Test database (port 5433)
postgres-test:
  image: postgres:17-alpine
  environment:
    - POSTGRES_DB=api-test

Database Operations

Connection Management

The database package exports a singleton Prisma client:

// packages/database/src/index.ts
export const prisma = global.prisma || new PrismaClient();
 
if (process.env.NODE_ENV !== 'production') {
  global.prisma = prisma;
}

Type Safety

All database operations are fully type-safe with generated TypeScript types:

import { prisma, type User, type Workspace } from '@repo/database';
 
// Type-safe queries
const user: User = await prisma.user.findUnique({
  where: { id: userId },
  include: { workspaces: true },
});

Performance Considerations

Indexing Strategy

Key indexes are automatically created for:

• Primary keys (UUID)
• Unique constraints (email, session tokens)
• Foreign key relationships
• Frequently queried fields

Query Optimization

• Use select to limit returned fields
• Implement include for related data
• Leverage Prisma's connection pooling

Connection Pooling

Prisma automatically handles connection pooling with sensible defaults for PostgreSQL connections.