Database

Rule

Rules for database design and operations. The data-engineer agent reviews for migration safety and data integrity; these rules codify the baseline. See stack.md for approved tools (Neon + Drizzle).

Schema Design

  • MUSTPrefer cuid2 or uuid over auto-increment for distributed/serverless.
  • SHOULDAdd created_at and updated_at timestamps to all tables.
  • SHOULDUse text over varchar in PostgreSQL — there is no performance difference.
  • NEVERStore derived data that can be computed from existing columns.

Migrations

  • MUSTMigrations are additive in production — add columns, don't drop them.
  • MUSTNew columns on existing tables MUST be nullable or have a default value.
  • MUSTTest migrations against a copy of production data before deploying.
  • NEVERDrop columns, tables, or indexes in the same deployment as the code change. Remove the code reference first, deploy, then drop.
  • SHOULDUse db:push for development, migrations for production.
  • SHOULDKeep migrations small and focused — one concern per migration.

Queries

  • SHOULDUse select() to fetch only needed columns — avoid SELECT *.

Connection Management

  • SHOULDSet reasonable connection limits per environment (dev: 5, production: 25+).

Soft Deletes

  • SHOULDUse soft deletes (deleted_at timestamp) for user-facing data that may need recovery.
  • MUSTIf using soft deletes, filter deleted records in all default queries.
  • MAYUse hard deletes for ephemeral data (logs, sessions, temporary tokens).