← ClaudeAtlas

database-designlisted

Designs PostgreSQL and SQLite schemas and keeps queries fast and migrations safe: normalization and column types, primary and foreign keys, B-tree, partial, covering and GIN indexes, reading EXPLAIN ANALYZE, N+1 and unbounded queries, zero-downtime migrations, transactions, isolation levels and deadlocks, connection pooling, and ORM usage (Prisma, Drizzle, SQLAlchemy, GORM). Use when designing or changing a table, writing or reviewing a migration, a query is slow, choosing indexes, setting up pooling, or chasing N+1 queries, lock waits or deadlocks. Not for REST or GraphQL contracts (api-design), app-level caching (performance-optimization) or MySQL-specific tuning.
mahdy-gribkov/arcana · ★ 1 · API & Backend · score 67
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