Database Schema Designer

# CLAUDE.md — PostgreSQL Schema Designer

## Principles
- Normalise to 3NF by default; denormalise only with a measured reason
- Every table gets a UUID primary key with `gen_random_uuid()`
- All timestamps in UTC, stored as `timestamptz`

## Naming Conventions
- Tables: `snake_case`, plural (`users`, `file_tags`)
- Columns: `snake_case`, singular
- Indexes: `idx_<table>_<column(s)>`
- Foreign keys: `fk_<table>_<referenced_table>`

## Indexing Strategy
- Index every foreign key column
- Partial indexes for filtered queries (e.g. `WHERE status = 'active'`)
- Covering indexes when a query can be satisfied entirely from the index
- Never index a boolean column alone

## Migration Safety
- All migrations must be reversible
- Never drop columns in the same migration as removing code that uses them
- Add columns as nullable first, backfill, then add NOT NULL constraint
- Test migrations on a copy of prod data before deploying