database-designlisted
Install: claude install-skill mahdy-gribkov/arcana
# Database design
Scope: PostgreSQL (currently supported majors 14 to 18) and SQLite, from a running application's point of view. Read the existing
schema, migrations folder and ORM config before proposing anything; follow the project's migration tool and naming.
## Rules
1. **Constraints live in the database**: primary keys, `NOT NULL`, `UNIQUE`, `CHECK` and foreign keys. Application checks are a second
layer, never the only one.
2. **Right types**: `timestamptz` for instants, `numeric` or integer minor units for money (never floats), `uuid` or
`bigint GENERATED ALWAYS AS IDENTITY` for keys, `jsonb` only for data you do not filter on relationally.
3. **Index every foreign key you join or delete through.** PostgreSQL does not create these automatically.
4. **Every query has a bound**: a `LIMIT`, a keyset predicate, or a key lookup. No unbounded `SELECT *` on a growing table.
5. **Migrations are backward compatible with the code still running**: expand, migrate data, switch reads, then contract in a later
release.
6. **Measure before indexing**: read `EXPLAIN (ANALYZE, BUFFERS)` for the real query with real parameters.
## Foreign key without an index
BAD: the join and every `DELETE FROM users` scan `orders`.
```sql
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id bigint NOT NULL REFERENCES users (id),
total_cents bigint NOT NULL CHECK (total_cents >= 0),
created_at timestamptz NOT NULL DEFAULT now()
);
```
GOOD: an index