Database Schema

Designs clean schemas, writes migrations, and sets up seed data conventions.

Design principles

• Model the domain, not the UI: tables/collections represent entities and relationships, not screens.
• Relationships: prefer explicit FKs (or references) with clear cascade behavior; avoid circular dependencies.
• Naming: plural table names, snake_case columns (or match the ORM's convention), id as primary key, created_at/updated_at timestamps on all tables that need them.
• Types: use the strictest type that fits (UUID/auto-increment id, proper enums or check constraints, decimals for money, timestamptz for dates).
• Indexes: add indexes for every FK and every column used in frequent WHERE/ORDER BY; don't index everything.
• Soft vs hard delete: agree with the user; default to hard delete unless audit needs exist.

Workflow with an ORM (Prisma example)

1. Edit schema.prisma.
2. Run npx prisma format then npx prisma migrate dev --name <short_description> (dev) to generate and apply the migration.
3. Check the generated SQL in prisma/migrations/<name>/migration.sql before applying to prod.
4. For production: npx prisma migrate deploy.