1
---
2
name: sql-schema-design-reviewer
3
description: Reviews PostgreSQL and MySQL tables (schema.sql, a dump or a pull request's CREATE TABLE) for design flaws that are cheap to fix before data exists - missing primary keys, money in float columns, foreign keys with mismatched types or no index, 32-bit ids, timestamps without time zone, nullable flags, free-text statuses, reserved names and case-sensitive unique emails. Returns corrected DDL. Includes a stdlib Python linter with --self-test.
4
---
5
6
# SQL Schema Design Reviewer
7
8
You review database schemas the way an experienced data modeler would at design time: before the first row is written, when changing a type or a key is a one-line edit instead of a weekend migration. You look at the shape of the data (keys, types, constraints, names, indexes), explain the concrete failure each problem leads to, and hand back corrected DDL.
9
10
This skill reviews the design of tables. It is not about deploying changes to a live database (locks, backfills, expand/contract rollouts); for that, use a migration safety review.
11
12
## Files in this skill
13
14
- `scripts/schema_lint.py` - parses CREATE TABLE, CREATE INDEX and ALTER TABLE ... ADD statements and reports keys, types, constraints, naming and index problems with severities and fixes; auto-detects PostgreSQL or MySQL (Python 3 standard library only, no database connection)
15
- `references/design-rules.md` - every finding code with the failure it causes, when it is acceptable to keep, and the fix
16
- `references/type-choices.md` - which type to use for ids, money, time, text, flags, statuses and JSON in PostgreSQL and MySQL
17
- `templates/schema-review.md` - the review to hand back
18
- `examples/shop-schema.sql` - a small online shop schema with deliberate design flaws
19
- `examples/shop-schema-fixed.sql` - the same schema after the review; it lints clean
20
21
## Workflow
22
23
### 1. Get the schema and the context
24
Ask for the DDL (schema.sql, `pg_dump --schema-only`, `mysqldump --no-data`, or the CREATE TABLE from the pull request), the engine and version, expected row counts per table in a year, and how the data is used (OLTP app, reporting, multi-tenant, replicated, exposed through an ORM). Ask which rules the team already has (UUID vs bigint ids, soft deletes, naming).
25
26
### 2. Run the linter
27
```
28
python3 scripts/schema_lint.py examples/shop-schema.sql
29
python3 scripts/schema_lint.py schema.sql --dialect mysql --min-severity MEDIUM
30
python3 scripts/schema_lint.py db/*.sql --ignore NO_CREATED_AT --json > schema-review.json
31
python3 scripts/schema_lint.py --self-test
32
```
33
Exit 0 means no HIGH findings, 1 means at least one HIGH (use as a CI gate on schema files), 2 means bad input. The linter is heuristic: it reads names (a `price` column in float, an `order_id` without a foreign key), so confirm each finding against the user's intent.
34
35
### 3. Review what a script cannot see
36
Then read the schema yourself (see `references/design-rules.md`):
37
- Does every table have one clear meaning and a natural unique key besides the surrogate id (sku, email, provider + provider_id)?
38
- Are relationships right: one-to-many vs many-to-many, ON DELETE behaviour (RESTRICT by default; CASCADE only for true children), nullable foreign keys only where the relation is optional?
39
- Are invariants in CHECK constraints (quantity > 0, ends_at > starts_at) instead of only in app code?
40
- Multi-tenant: does every tenant-owned table carry tenant_id, is it in the unique keys and the leading index column?
41
- Soft deletes: are unique constraints partial (`WHERE deleted_at IS NULL`)?
42
- Personal data: which columns hold PII, and do they need separate access or retention rules?
43
44
### 4. Deliver
45
Fill `templates/schema-review.md`: verdict, findings ranked by impact with the failure each one causes, then the corrected DDL (see `examples/shop-schema-fixed.sql`) and a list of decisions left to the team. If the tables already hold data, say which fixes now need a migration and flag them for a migration safety review instead of rewriting live tables in place.
46
47
## Example result
48
`examples/shop-schema.sql` (PostgreSQL, 5 tables): 20 findings, 4 HIGH. order_items has no primary key (duplicate lines possible); products.price is double precision and payments.amount is real (cents drift); payments.order_id is integer but references "order".id (bigserial). MEDIUM: serial ids on customers and products, timestamp without time zone on customers.created_at and placed_at, a table named "order" (reserved), foreign keys on "order".customer_id and order_items.order_id with no index. LOW: case-sensitive unique email, varchar(255) habit, nullable is_verified, free-text status, product_id without a foreign key, a redundant payments index, JSON kept in text. `examples/shop-schema-fixed.sql` fixes all of them (orders, bigint identity, numeric(12,2), timestamptz, citext, CHECK on status, composite key on order_items) and lints clean.
49
50
## Rules
51
52
- Explain each finding as the failure it causes (wrong totals, orphans, duplicate signups, an outage at 2.1 billion ids), not as a style rule.
53
- Keep the user's conventions (UUID ids, naming) unless they cause a real problem; say when a finding is a judgement call.
54
- Prefer constraints in the database over checks in the application; the database is the last line of defence.
55
- Money is exact: numeric/DECIMAL or integer minor units, never float, and store the currency.
56
- Time is an instant: timestamptz in PostgreSQL, UTC everywhere in MySQL.
57
- Do not propose changes to tables that already hold production data without pointing out that each one is a migration with its own risks.
58