database

name: database description: MUST read before creating or modifying schemas, migrations, seeders, or repositories. Covers 5NF schema design with Drizzle ORM, naming conventions, relations, transactions, and repository patterns on Amazon RDS PostgreSQL.

Database Guidelines

PostgreSQL on Amazon RDS. Drizzle ORM. All schemas in 5NF.

Schema Conventions

• Table names → snake_case, plural (users, user_profiles)
• Column names → snake_case
• Primary key → id (UUID) or external ID (e.g. cognito_sub)
• Foreign key → <singular_table>_id (e.g. role_id)
• Timestamps → created_at, updated_at on every table
• Soft delete → deleted_at (nullable timestamp) where needed
• Boolean → is_ prefix (e.g. is_active)
• Indexes on all foreign keys and frequently queried columns

Fifth Normal Form (5NF)

• Every table represents a single fact
• No multi-valued dependencies
• No join dependencies that can be decomposed
• Separate concerns by update frequency and optionality

Core Schema

user_profiles

Stores business data only. Auth (email, password, OAuth, session) handled by Cognito.

• cognito_sub (string, PK) → User ID from Cognito
• name (string, required)
• birthdate (date, required)
• created_at, updated_at

roles

Defines available roles in the system. Editable from UI.

• id (UUID, PK)
• name (string, unique, required) → e.g. "admin", "user"
• description (text, optional)
• created_at, updated_at

permissions

Defines granular actions. Format: resource:action.

• id (UUID, PK)
• name (string, unique, required) → e.g. "user:read", "user:delete"
• description (text, optional)
• created_at, updated_at

Note: Permission names follow resource:action format for consistency. AI must follow this format when adding new permissions.

role_permissions

Links roles to permissions. Many-to-many junction table.

• role_id (UUID, FK → roles)
• permission_id (UUID, FK → permissions)
• Composite PK: (role_id, permission_id)

Note: Deleting a role cascades here. Always seed default permissions after migration.

user_roles

Links Cognito users to roles. Many-to-many junction table.

• cognito_sub (string, FK → Cognito user)
• role_id (UUID, FK → roles)
• Composite PK: (cognito_sub, role_id)

Note: New users should be assigned "user" role by default on registration.

Migration Rules

• One migration per change — never modify existing migrations
• Migration file → <timestamp>_<description>.ts
• Always include rollback
• Test rollback before merging
• Run via drizzle-kit generate and drizzle-kit migrate

Repository Pattern

• One repository per aggregate root (e.g. user-repository.ts)
• Repositories live in packages/database/repositories/
• Return plain objects, not Drizzle instances
• Multi-step mutations wrapped in transactions
• Parameterized queries only (Drizzle handles this by default)

Runtime Connection Safety

• If DB credentials are loaded from DATABASE_SECRET_ARN, validate required fields before building URL.
• Required keys: username, password, host (or hostname), port, dbname (or database).
• Never allow connection strings with placeholder/undefined values; throw a clear error early.

Seeder Rules

• Seeders live in packages/database/seeders/
• Dev seeders: realistic fake data for development
• Always idempotent — safe to run multiple times
• Default roles seeder: admin, user as baseline