## Database Schema Rules

### Naming
- Tables: plural snake_case (user_profiles, order_items)
- Columns: singular snake_case (created_at, user_id, email_address)
- Foreign keys: referenced_table_singular_id (user_id, order_id)
- Indexes: idx_table_column (idx_users_email, idx_orders_created_at)
- Unique constraints: uniq_table_column (uniq_users_email)
- Junction tables: alphabetical (category_products, not product_categories)

### Column Standards
- Primary keys: id as UUID v7 (time-sortable), never auto-increment integers
- Timestamps: created_at and updated_at on every table, stored as timestamptz
- Soft deletes: deleted_at column (nullable timestamptz), never hard delete user-facing data
- Money: stored as integer cents, never floating point
- Status fields: use a check constraint with allowed values, not a boolean

## Query Patterns

### ORM Usage (Prisma)
- All database access through src/repositories/ — NEVER import PrismaClient in services or routes
- Use Prisma's select to fetch only needed columns — no select-star equivalents
- Use Prisma transactions for multi-table writes: prisma.$transaction([...])
- Pagination: cursor-based using id, never offset-based for user-facing lists

### Performance
- Every WHERE clause must use an indexed column — check schema before writing queries
- No N+1 queries — use Prisma include or explicit joins
- Queries returning lists MUST have a LIMIT (default 100, max 1000)
- Count queries on large tables: use estimated counts from pg_stat_user_tables

## Migration Rules

### Safety Requirements
- NEVER drop a column in the same deploy that stops using it. Two-phase: stop reading first, drop in next release.
- NEVER rename a column. Add new column, backfill, switch reads, drop old column.
- New NOT NULL columns MUST have a DEFAULT value
- Index creation: use CREATE INDEX CONCURRENTLY (does not lock table)
- Large table migrations: always include estimated row count and expected lock duration in the migration comment

### Migration File Standards
- One logical change per migration file
- File name: YYYYMMDDHHMMSS_descriptive_name.sql
- Include a comment block at the top explaining what and why
- Reversible: every UP migration has a corresponding DOWN

# .claude/rules/database.md
---
paths:
  - "src/repositories/**/*.ts"
  - "prisma/**"
  - "migrations/**"
---

## Repository Implementation Rules
- Every repository method handles its own error mapping
- Prisma errors → AppError subtypes (P2002 → ConflictError, P2025 → NotFoundError)
- Repository methods return domain objects, never raw Prisma types
- Include query timing logs for operations expected to exceed 100ms

## Test Data
- Seed data in prisma/seed.ts — deterministic, idempotent
- Test factories in tests/factories/ using @faker-js/faker
- Factory functions return plain objects matching domain types
- Tests use isolated transactions that roll back after each test
- NEVER use production data patterns in seeds (no real emails, names, or addresses)

## Database
- ORM: Prisma 6 with PostgreSQL 16
- Schema: prisma/schema.prisma is the source of truth
- Access: only through src/repositories/
- @docs/database-naming-conventions.md
- @docs/migration-safety-rules.md

## Database Performance
- Connection pool: 20 connections default, configured in src/config/database.ts
- Query timeout: 30 seconds for read queries, 60 seconds for write queries
- Slow query logging: queries exceeding 500ms logged at WARN level
- Connection retry: exponential backoff with max 3 retries
- Read replicas: use for analytics and reporting queries
- Write operations: always against primary database

## Post-Migration Validation
- Every migration includes a verification step that checks the schema matches Prisma expectations
- Run prisma db pull after migration to verify schema sync
- Migration PR must include the prisma diff output
- Seed data must work against the new schema (run prisma db seed in CI)