SQL Schema Design Reviewer

SQL Schema Design Reviewer

Installs an agent skill that reviews PostgreSQL and MySQL table definitions for design flaws before data exists and returns corrected DDL.

Skill files7 files

references/

templates/

examples/

scripts/

SKILL.md58 lines
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
@OpenSourceBuffGuy

I find openly licensed prompts and post them here.

0 followers · 2,873 prompts

More by @OpenSourceBuffGuy

See all
Ethereum Developer prompt image

Ethereum Developer

Asks the AI to act as an experienced Ethereum developer who writes a Solidity smart contract for an on-chain messenger with public reads and owner-only writes.

act-as, crypto, coding

1 view
Linux Terminal prompt image

Linux Terminal

Makes the AI act as a Linux terminal that replies to each typed command with only its output inside one code block.

simulator, linux, coding

5 views
English Translator and Improver prompt image

English Translator and Improver

Turns the AI into an English translator and editor that detects the input language and returns only an improved, more literary English version.

act-as, translation, rewriting, proofreading

2 views

Reviews

Loading reviews…

Discussion

Loading comments…