Postgres Schema Design

Schema decisions are the most expensive to reverse. Take the time to model correctly, write reversible migrations, and prove index choices with EXPLAIN.

Step 1 — Establish context

1. Identify the toolchain: prisma/schema.prisma, drizzle/ + drizzle.config.*, supabase/migrations/, or plain migrations/*.sql. Follow its conventions for naming and for how migrations are generated.
2. Dump the current schema so you design against reality: pg_dump --schema-only, prisma db pull, or supabase db dump --schema public.
3. Ask (or infer from the code) the three questions that decide the design: expected row counts per table, the top five read queries, and who writes (single service, many tenants, end users through PostgREST).

Step 2 — Model

• Keys. Primary key id as bigint generated always as identity for internal tables; UUIDv7 (uuid with a v7 generator) when ids are exposed publicly or generated client-side. Avoid random UUIDv4 as clustered keys on hot tables.
• Names. snake_case, singular or plural consistently with the existing schema, foreign keys as <table>_id, timestamps created_at/updated_at as timestamptz not null default now().
• Types. text over varchar(n); numeric for money (never float); timestamptz never timestamp; jsonb only for genuinely schemaless data, and pull hot fields out into columns.
• Constraints are documentation that cannot rot. Add not null by default, check constraints for enums and ranges, unique for natural keys, and references ... on delete with a deliberate choice (cascade for owned children, restrict for shared references).
• Enums. Prefer a check (status in (...)) or a lookup table over a Postgres enum type unless the tool handles enum migrations well; renaming enum values is painful.
• Soft delete. Only if required; add deleted_at timestamptz and a partial index where deleted_at is null.
• Multi-tenancy. Put org_id on every tenant-scoped table, include it in composite indexes first, and enforce it with RLS.

Step 3 — Index deliberately

• Every foreign key column gets an index unless the table is tiny.
• Composite indexes follow the query: equality columns first, then range/sort columns. (org_id, created_at desc) serves "latest items for a tenant".
• Use partial indexes for status filters (where status = 'open'), gin for jsonb containment and array membership, gin with pg_trgm for ILIKE '%term%', and covering indexes (include (...)) for index-only scans.
• Do not add an index you cannot tie to a query. Each one slows writes.
• Verify: explain (analyze, buffers) <query> before and after; look for Seq Scan on large tables and Sort nodes that an index would remove.

Step 4 — Security (Supabase and any user-facing API)