SQL Prompts | prompts.chat

Skill

Reviews PostgreSQL and MySQL schema migrations (raw SQL or ORM-generated) for table locks, rewrites, data loss, and breaking changes, then proposes safe zero-downtime rewrites with a clear verdict.

---
name: migration-safety-review
description: Reviews database schema migrations (raw SQL or ORM-generated from Rails, Django, Alembic, Prisma, Knex, Laravel, Flyway) for production risks before they ship - table-locking DDL, full table rewrites, data loss, breaking changes for running app code, and missing rollback paths - and proposes safe zero-downtime rewrites. Use when a diff or PR adds or changes migration files, when the user asks "is this migration safe?", or before deploying schema changes to a busy PostgreSQL or MySQL database.
---

# Migration Safety Review

You are reviewing schema migrations the way a careful senior DBA would before a
production deploy. The goal is a clear verdict plus concrete, safer SQL - not a
generic lecture about databases.

## Files in this skill

- `scripts/scan_migration.py` - fast heuristic scanner for risky SQL statements
- `references/risk-catalog.md` - operation-by-operation hazards and safe patterns
- `references/expand-contract.md` - keeping old and new app code working during rollout
- `templates/review-report.md` - the report format you must produce
- `examples/example-review.md` - a complete worked review to calibrate tone and depth

## Workflow

### 1. Find the migrations in scope
- If reviewing a branch or PR: `git diff --name-only origin/main...HEAD` and keep
  files under migration folders (`migrations/`, `db/migrate/`, `alembic/versions/`,
  `prisma/migrations/`, `database/migrations/`, `db/migration/`).
- Otherwise use the files or SQL the user pointed to.
- Note which migrations are new versus already applied in any environment.
  Never suggest editing an applied migration; propose a new follow-up migration.

### 2. Establish context
Determine, from config files, docker-compose, or by asking the user:
- Engine and major version (e.g. PostgreSQL 15, MySQL 8.0). Lock behavior depends on it.
- Approximate size and write traffic of each touched table.
- How deploys work: are migrations run before, during, or after new code rolls out?

If size or traffic is unknown, assume the table is large and hot, and say so.

### 3. Get the real SQL
ORM code hides what actually runs. Render the SQL first:

| Framework | Command |
|-----------|---------|
| Django | `python manage.py sqlmigrate <app> <migration>` |
| Rails | `rails db:migrate` on a scratch DB, then inspect `db/structure.sql` diff |
| Alembic | `alembic upgrade <from>:<to> --sql` |
| Prisma | read `prisma/migrations/<name>/migration.sql` |
| Laravel | `php artisan migrate --pretend` |
| Knex | run on a scratch DB with `DEBUG=knex:query` and copy the logged SQL |
| Flyway / Liquibase | the `.sql` file or `liquibase update-sql` |

Save rendered SQL to a temp file if it is not already a `.sql` file.

### 4. Run the scanner
```bash
python3 scripts/scan_migration.py --dialect postgres path/to/migration.sql
python3 scripts/scan_migration.py --dialect mysql db/*.sql
```
It prints `file:line [SEVERITY] RULE message` and exits 1 if any HIGH finding exists.
Treat its output as leads, not as the verdict: it uses regexes, can miss dynamic SQL,
and cannot know table sizes.

### 5. Review every statement manually
For each statement, use `references/risk-catalog.md` to answer:
1. What lock does it take, and for how long (instant, table scan, or full rewrite)?
2. Can it lose or corrupt data? Is that intended and backed up?
3. Will it queue behind long transactions? Is `lock_timeout` (Postgres) or
   `lock_wait_timeout` (MySQL) set so it fails fast instead of blocking all traffic?
4. Does it run in a transaction where it must not (e.g. `CREATE INDEX CONCURRENTLY`)?
5. Are large data backfills batched and separated from DDL?

### 6. Check application compatibility
During a rolling deploy, old and new code run at the same time against the new schema.
Follow `references/expand-contract.md`:
- Search the codebase (`rg -n '<column_or_table_name>'`) for every renamed, dropped,
  or retyped object, including raw SQL, serializers, and analytics queries.
- Flag any change the currently deployed code cannot tolerate.

### 7. Verify the rollback path
- Does a down migration exist, and does it actually restore the previous state?
- Drops and lossy type changes are one-way: require a backup or a staged plan.

### 8. Write the report
Fill in `templates/review-report.md` exactly. Match the depth of
`examples/example-review.md`. For every HIGH or MEDIUM finding, give replacement SQL
or migration code that achieves the same end state safely, split into ordered deploy
steps when needed.

## Verdicts
- **SAFE** - no blocking locks on large tables, no data loss, backward compatible.
- **SAFE WITH CHANGES** - can ship once the listed rewrites are applied.
- **UNSAFE** - would cause downtime, data loss, or errors in running code as written.

## Rules
- Never run migrations against production or shared databases yourself.
- Do not modify migration files unless the user asks; propose changes in the report.
- Be specific: name the table, the lock, and the failure mode. Skip generic advice.
- If you are unsure about a version-specific behavior, say so and suggest testing on
  a production-sized copy with `\timing` / `EXPLAIN` and lock monitoring.
FILE:references/risk-catalog.md
# Risk Catalog: Common Migration Operations

Lock names are PostgreSQL. ACCESS EXCLUSIVE blocks all reads and writes;
SHARE blocks writes; SHARE UPDATE EXCLUSIVE blocks neither.

## The lock queue problem (applies to everything below)
Even an "instant" ALTER TABLE needs ACCESS EXCLUSIVE briefly. If a long query or
idle-in-transaction session holds the table, the ALTER waits - and every new query
queues behind it. A 1 ms change can cause a multi-minute outage.
Always start risky migrations with:
```sql
SET lock_timeout = '5s';        -- fail fast, retry later
SET statement_timeout = '15min'; -- optional upper bound
```
MySQL equivalent: `SET SESSION lock_wait_timeout = 5;` (metadata locks).

## PostgreSQL operations

| Operation | Risk | Safe pattern |
|-----------|------|--------------|
| `CREATE INDEX` | SHARE lock: writes blocked for whole build | `CREATE INDEX CONCURRENTLY`, outside a transaction; on failure drop the INVALID index and retry. Rails: `disable_ddl_transaction!`; Django: `atomic = False` |
| `DROP INDEX` | ACCESS EXCLUSIVE | `DROP INDEX CONCURRENTLY` |
| `ADD COLUMN` nullable, no default | Instant | Safe (still set lock_timeout) |
| `ADD COLUMN ... DEFAULT <constant>` | Instant on PG 11+, rewrite before 11 | Safe on 11+ |
| `ADD COLUMN ... DEFAULT now()/random()/gen_random_uuid()` | Volatile default: full table rewrite | Add nullable column, backfill in batches, then set default |
| `ADD COLUMN ... NOT NULL` without default | Fails on non-empty table | Add nullable, backfill, then enforce NOT NULL (below) |
| `ALTER COLUMN ... SET NOT NULL` | Full scan under ACCESS EXCLUSIVE | `ADD CONSTRAINT c CHECK (col IS NOT NULL) NOT VALID`; `VALIDATE CONSTRAINT c`; then `SET NOT NULL` (PG 12+ skips the scan); drop `c` |
| `ALTER COLUMN ... TYPE` | Usually full rewrite + index rebuild under ACCESS EXCLUSIVE | Safe only if binary-coercible (varchar(n) to larger n or to text). Otherwise new column + dual write + backfill + swap |
| `ADD FOREIGN KEY` | Locks both tables while validating all rows | `ADD CONSTRAINT ... NOT VALID`, then `VALIDATE CONSTRAINT` in a separate step |
| `ADD CHECK` | Scan under ACCESS EXCLUSIVE | Same NOT VALID + VALIDATE pattern |
| `ADD UNIQUE` / `ADD PRIMARY KEY` | Builds index under lock | `CREATE UNIQUE INDEX CONCURRENTLY idx ...`; then `ADD CONSTRAINT ... UNIQUE USING INDEX idx` |
| `RENAME COLUMN` / `RENAME TO` | Instant, but breaks running code | Expand/contract (see expand-contract.md) |
| `DROP COLUMN` | Instant, but irreversible; old code selecting it errors | Remove all code references and deploy first; then drop |
| `DROP TABLE` / `TRUNCATE` | Irreversible data loss | Confirm backup and zero readers; consider renaming to `_deprecated` first |
| `ALTER TYPE ... ADD VALUE` | New value unusable in same transaction; no transaction at all before PG 12 | Put it in its own migration |
| `VACUUM FULL` / `CLUSTER` / `REINDEX` | Full rewrite under ACCESS EXCLUSIVE | `REINDEX CONCURRENTLY` (PG 12+), `pg_repack` for bloat |
| Big `UPDATE` / `DELETE` | Long row locks, WAL spike, replica lag | Batch by primary key (1k-10k rows), commit per batch, run outside the DDL migration |

## MySQL 8.0 (InnoDB) notes
- Always state the algorithm so MySQL errors instead of silently copying the table:
  `ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INSTANT;` or
  `ALTER TABLE t ADD INDEX i (c), ALGORITHM=INPLACE, LOCK=NONE;`
- `ADD COLUMN` is INSTANT on 8.0.12+ (last position) and 8.0.29+ (any position).
- `MODIFY` / `CHANGE COLUMN` type changes use ALGORITHM=COPY: writes blocked.
- For large tables with COPY-only changes use `gh-ost` or `pt-online-schema-change`.
- DDL is not transactional in MySQL: a failed multi-statement migration leaves
  the schema half-applied. Keep one DDL statement per migration.
FILE:references/expand-contract.md
# Expand / Contract: Backward-Compatible Schema Changes

During a rolling deploy, old and new application versions run side by side.
If migrations run before the new code is live, the old code must work with the
new schema. If they run after, the new code must work with the old schema.
Expand/contract makes every step compatible with both.

## The three phases
1. **Expand** - add new structures only (columns, tables, indexes). Nothing is
   removed or renamed. Old code ignores the additions.
2. **Migrate** - deploy code that writes to both old and new structures, backfill
   existing rows in batches, then switch reads to the new structure.
3. **Contract** - once no deployed code touches the old structure, drop it in a
   separate, later migration.

Each phase is its own deploy. Never combine expand and contract in one migration.

## Recipes

### Rename a column (`users.name` to `users.full_name`)
1. Migration: add nullable `full_name`.
2. Code: write both `name` and `full_name`; read `name`.
3. Backfill `full_name = name` in batches where `full_name IS NULL`.
4. Code: read `full_name`; keep writing both.
5. Code: stop writing `name`. (Rails: add `name` to `ignored_columns` here.)
6. Migration: drop `name`.

### Change a column type (`orders.amount` int to numeric)
Same as rename: add `amount_numeric`, dual write, backfill, switch reads, drop old.
A trigger can handle dual writes if application changes are hard.

### Make a column NOT NULL
1. Code: always write a value.
2. Backfill NULL rows in batches.
3. Migration: CHECK ... NOT VALID, VALIDATE, SET NOT NULL (see risk-catalog.md).

### Drop a column or table
1. Code: remove every read and write (search ORM models, raw SQL, views,
   reports, ETL jobs, and other services sharing the database).
2. Deploy and wait at least one full release cycle.
3. Migration: drop. Take a backup or snapshot of the data first if it matters.

### Split or move a table
Create the new table, dual write, backfill, switch reads, stop old writes, drop.

## Compatibility questions to answer for each change
- Does any deployed code `SELECT *` or map all columns (ORMs often cache the
  column list at boot and fail when one disappears)?
- Does an insert from old code fail because a new column is NOT NULL without default?
- Do other services, cron jobs, BI dashboards, or replicas read this table?
- Can the deploy be rolled back to the previous code version without a down migration?

If the answer to the last question is "no", the change is not backward compatible.
FILE:templates/review-report.md
# Migration Safety Review: <migration name or PR title>

**Verdict:** SAFE | SAFE WITH CHANGES | UNSAFE
**Engine:** <e.g. PostgreSQL 15>  |  **Files reviewed:** <count>
**Assumptions:** <table sizes, traffic, deploy order - mark anything guessed>

## Summary
<2-4 sentences: what the migration does, the biggest risk, and what to change.>

## Findings

| # | Severity | File:Line | Statement | Risk |
|---|----------|-----------|-----------|------|
| 1 | HIGH | <path:line> | `<short SQL>` | <lock / data loss / breaks old code> |

### 1. <Short title of finding>
- **What happens:** <lock taken, duration, who is blocked, or what breaks>
- **Why it matters here:** <table size, traffic, code that depends on it>
- **Safe alternative:**
```sql
-- replacement SQL or migration code, in run order
```

<Repeat for each HIGH and MEDIUM finding. Group LOW findings in one list.>

## Application Compatibility
- <Each renamed / dropped / retyped object and where the code still uses it>
- <Or: "No code references affected - checked with rg for X, Y.">

## Rollback Plan
- <Does the down migration restore state? What is irreversible?>
- <Backup or snapshot required before running: yes/no>

## Recommended Deploy Sequence
1. <Migration or code deploy step>
2. <...>

## Scanner Output
```
<paste scripts/scan_migration.py output, or note false positives>
```
FILE:examples/example-review.md
# Example Review

**Input:** PR "Add order status tracking" with one Rails migration for PostgreSQL 15.
`orders` has ~40M rows and receives constant writes. Migrations run before new code.

```sql
-- rendered from db/migrate/20261002_add_status_to_orders.rb
ALTER TABLE orders ADD COLUMN status varchar NOT NULL DEFAULT 'pending';
ALTER TABLE orders RENAME COLUMN shipped_on TO shipped_at;
CREATE INDEX index_orders_on_status ON orders (status);
ALTER TABLE orders ADD CONSTRAINT fk_orders_carrier
  FOREIGN KEY (carrier_id) REFERENCES carriers (id);
```

**Scanner:** 3 HIGH (rename, index-not-concurrent, fk-validated), 1 MEDIUM (no-lock-timeout).

---

# Migration Safety Review: Add order status tracking

**Verdict:** UNSAFE
**Engine:** PostgreSQL 15  |  **Files reviewed:** 1
**Assumptions:** orders ~40M rows, high write traffic (from user); carriers is small.

## Summary
Adds an order status column, renames `shipped_on`, indexes status, and adds a carrier
foreign key. The status column itself is safe on PG 15, but the rename will break the
running app, and the index and FK will block writes on `orders` for minutes.
Split into three migrations and use concurrent / NOT VALID variants.

## Findings

| # | Severity | File:Line | Statement | Risk |
|---|----------|-----------|-----------|------|
| 1 | HIGH | rendered.sql:3 | `RENAME COLUMN shipped_on` | Old code errors on deploy |
| 2 | HIGH | rendered.sql:4 | `CREATE INDEX ... (status)` | Writes blocked during build |
| 3 | HIGH | rendered.sql:5 | `ADD ... FOREIGN KEY` | Full validation scan under lock |
| 4 | MEDIUM | rendered.sql:1 | no `lock_timeout` | ALTERs can queue and stall traffic |

### 1. Column rename breaks running code
- **What happens:** the rename is instant, but app servers still on the old release
  query `shipped_on` and fail with `column does not exist` until the deploy finishes.
- **Why it matters here:** `rg -n shipped_on` finds 7 references, including
  `app/serializers/order_serializer.rb` and the nightly `reports/fulfillment.sql`.
- **Safe alternative:** expand/contract. Add `shipped_at`, dual write, backfill in
  batches, switch reads, then drop `shipped_on` in a later release.

### 2. Index build blocks writes
- **Safe alternative** (separate migration, `disable_ddl_transaction!`):
```sql
CREATE INDEX CONCURRENTLY index_orders_on_status ON orders (status);
```

### 3. Foreign key validates 40M rows under lock
- **Safe alternative:**
```sql
SET lock_timeout = '5s';
ALTER TABLE orders ADD CONSTRAINT fk_orders_carrier
  FOREIGN KEY (carrier_id) REFERENCES carriers (id) NOT VALID;
-- next migration (takes only SHARE UPDATE EXCLUSIVE on orders):
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_carrier;
```

**LOW:** none. Note `ADD COLUMN ... DEFAULT 'pending'` is metadata-only on PG 11+.

## Application Compatibility
- `shipped_on`: 7 code references plus one SQL report; must stay until contract phase.

## Rollback Plan
- Down migration drops `status` (data loss acceptable: new column). Rename is reversible.
- No backup required for this change set once the rename is removed.

## Recommended Deploy Sequence
1. Migration A: `SET lock_timeout`; add `status`; add `shipped_at`; add FK NOT VALID.
2. Migration B (no transaction): create status index concurrently.
3. Migration C: validate FK. Deploy code that dual writes `shipped_on`/`shipped_at`.
4. Backfill `shipped_at`; switch reads; later release drops `shipped_on`.
FILE:scripts/scan_migration.py
#!/usr/bin/env python3
"""Heuristic scanner for risky SQL in migration files (PostgreSQL / MySQL).
Usage: python3 scan_migration.py [--dialect postgres|mysql] FILE [FILE ...]
Exit codes: 0 = no HIGH findings, 1 = HIGH findings, 2 = usage error."""
import re, sys

F = re.I | re.S
COLDEF = r"(?:\([^)]*\)|[^,(])*"  # one column definition, allowing numeric(10,2)
RULES = [  # (severity, rule id, dialect or None for both, regex, message)
    ("HIGH", "drop-table", None, r"^DROP\s+TABLE\b", "Irreversible data loss; confirm backup and no readers"),
    ("HIGH", "truncate", None, r"^TRUNCATE\b", "Irreversible data loss"),
    ("HIGH", "drop-column", None, r"^ALTER\s+TABLE\b.*\bDROP\s+(COLUMN\b|(?!CONSTRAINT|INDEX|KEY|PRIMARY|FOREIGN|CHECK|DEFAULT|NOT|IDENTITY|EXPRESSION)\w)", "Data loss; deployed code reading it will fail - remove code refs first"),
    ("HIGH", "rename", None, r"^ALTER\s+TABLE\b.*\bRENAME\b", "Breaks running code; use expand/contract"),
    ("HIGH", "type-change", "postgres", r"^ALTER\s+TABLE\b.*\bALTER\s+(COLUMN\s+)?\S+\s+(SET\s+DATA\s+)?TYPE\b", "Usually a full table rewrite under ACCESS EXCLUSIVE"),
    ("HIGH", "type-change", "mysql", r"^ALTER\s+TABLE\b.*\b(MODIFY|CHANGE)\s+(COLUMN\s+)?\S+", "Column redefinition usually uses ALGORITHM=COPY (writes blocked)"),
    ("HIGH", "index-not-concurrent", "postgres", r"^CREATE\s+(UNIQUE\s+)?INDEX\s+(?!CONCURRENTLY)", "Blocks writes during build; use CREATE INDEX CONCURRENTLY"),
    ("MEDIUM", "drop-index-not-concurrent", "postgres", r"^DROP\s+INDEX\s+(?!CONCURRENTLY)", "Takes ACCESS EXCLUSIVE; use DROP INDEX CONCURRENTLY"),
    ("HIGH", "fk-validated", "postgres", r"^ALTER\s+TABLE\b(?!.*\bNOT\s+VALID\b).*\b(FOREIGN\s+KEY|REFERENCES)\b", "Validates all rows while locking both tables; add NOT VALID, then VALIDATE"),
    ("MEDIUM", "check-validated", "postgres", r"^ALTER\s+TABLE\b(?!.*\bNOT\s+VALID\b).*\bADD\s+(CONSTRAINT\s+\S+\s+)?CHECK\b", "Full scan under lock; add NOT VALID, then VALIDATE"),
    ("MEDIUM", "set-not-null", "postgres", r"\bSET\s+NOT\s+NULL\b", "Full scan under ACCESS EXCLUSIVE; validate a CHECK (col IS NOT NULL) first"),
    ("HIGH", "add-not-null-no-default", None, r"^ALTER\s+TABLE\b.*\bADD\s+(COLUMN\s+)?(?!" + COLDEF + r"\bDEFAULT\b)" + COLDEF + r"\bNOT\s+NULL\b", "Fails on non-empty tables (or old code inserts fail); add nullable, backfill, then enforce"),
    ("MEDIUM", "volatile-default", "postgres", r"^ALTER\s+TABLE\b.*\bADD\b.*\bDEFAULT\s+(now|random|clock_timestamp|gen_random_uuid|uuid_generate_v\d)\s*\(", "Volatile default rewrites the table; add nullable, backfill, then set default"),
    ("MEDIUM", "unique-without-index", "postgres", r"^ALTER\s+TABLE\b(?!.*\bUSING\s+INDEX\b).*\bADD\s+(CONSTRAINT\s+\S+\s+)?(UNIQUE|PRIMARY\s+KEY)\b", "Builds index under lock; create it CONCURRENTLY, then ADD CONSTRAINT ... USING INDEX"),
    ("MEDIUM", "mysql-no-algorithm", "mysql", r"^(ALTER\s+TABLE|CREATE\s+(UNIQUE\s+)?INDEX)\b(?!.*\bALGORITHM\s*=)", "State ALGORITHM=INSTANT|INPLACE, LOCK=NONE so MySQL refuses a blocking copy"),
    ("HIGH", "dml-no-where", None, r"^(UPDATE|DELETE)\b(?!.*\bWHERE\b)", "Touches every row in one transaction; batch it"),
    ("LOW", "dml-in-migration", None, r"^(UPDATE|DELETE|INSERT)\b.*\bWHERE\b", "Data change in migration; batch it if the table is large"),
    ("MEDIUM", "table-rewrite", "postgres", r"^(VACUUM\s+FULL|CLUSTER|REINDEX\s+(?!.*CONCURRENTLY))", "Rewrites under ACCESS EXCLUSIVE; use REINDEX CONCURRENTLY or pg_repack"),
    ("LOW", "enum-add-value", "postgres", r"^ALTER\s+TYPE\b.*\bADD\s+VALUE\b", "New value unusable in same transaction; keep in its own migration"),
]

def statements(sql):
    """Yield (line_number, statement) after stripping comments. Naive ';' split."""
    sql = re.sub(r"/\*.*?\*/", lambda m: re.sub(r"[^\n]", " ", m.group()), sql, flags=re.S)
    sql = re.sub(r"--[^\n]*", "", sql)
    pos = 0
    for part in sql.split(";"):
        stripped = part.lstrip()
        line = sql.count("\n", 0, pos + len(part) - len(stripped)) + 1
        pos += len(part) + 1
        if stripped.strip():
            yield line, " ".join(stripped.split())

def scan(path, dialect):
    text = open(path, encoding="utf-8", errors="replace").read()
    stmts, out = list(statements(text)), []
    for line, st in stmts:
        for sev, rid, dia, rx, msg in RULES:
            if (dia is None or dia == dialect) and re.search(rx, st, F):
                out.append((sev, f"{path}:{line} [{sev}] {rid}: {msg}\n    > {st[:110]}"))
    has_ddl = any(re.match(r"(ALTER|CREATE\s+(UNIQUE\s+)?INDEX|DROP)\b", s, re.I) for _, s in stmts)
    timeout = "lock_timeout" if dialect == "postgres" else "lock_wait_timeout"
    if has_ddl and timeout not in text.lower():
        out.append(("MEDIUM", f"{path}:1 [MEDIUM] no-lock-timeout: DDL without {timeout}; it may queue and block all traffic"))
    if re.search(r"\bCONCURRENTLY\b", text, re.I) and re.search(r"^\s*(BEGIN|START\s+TRANSACTION)\b", text, re.I | re.M):
        out.append(("HIGH", f"{path}:1 [HIGH] concurrently-in-transaction: CONCURRENTLY cannot run inside a transaction block"))
    return out

def main(argv):
    dialect = "postgres"
    if len(argv) >= 2 and argv[0] == "--dialect":
        dialect, argv = argv[1].lower(), argv[2:]
    if dialect not in ("postgres", "mysql") or not argv:
        print(__doc__, file=sys.stderr)
        return 2
    try:
        findings = [f for p in argv for f in scan(p, dialect)]
    except OSError as e:
        print(f"error: {e}", file=sys.stderr)
        return 2
    for _, text in findings:
        print(text)
    counts = {s: sum(1 for f in findings if f[0] == s) for s in ("HIGH", "MEDIUM", "LOW")}
    print(f"\n{len(argv)} file(s) scanned: {counts['HIGH']} HIGH, {counts['MEDIUM']} MEDIUM, {counts['LOW']} LOW")
    print("Heuristic only: confirm each finding against references/risk-catalog.md.")
    return 1 if counts["HIGH"] else 0

if __name__ == "__main__":
    sys.exit(main(sys.argv[1:]))

Backend Best Practices DevOps+2

F@f

SQL Index Recommendation Reviewer

Skill

Reviews a schema and slow SQL queries, proposes composite indexes in the right column order (equality, sort, range) with covering columns and tradeoffs, flags non-sargable filters like functions on columns and leading-wildcard LIKE, and warns about duplicate, redundant, and low-selectivity indexes. Use when a user shares a slow query, EXPLAIN output, or schema and asks "what index do I need?" or "too many indexes?". Includes a tested stdlib Python helper.

---
name: sql-index-recommendation-reviewer
description: Reviews a schema and slow SQL queries, proposes composite indexes in the right column order (equality, sort, range) with covering columns and tradeoffs, flags non-sargable filters like functions on columns and leading-wildcard LIKE, and warns about duplicate, redundant, and low-selectivity indexes. Use when a user shares a slow query, EXPLAIN output, or schema and asks "what index do I need?" or "too many indexes?". Includes a tested stdlib Python helper.
---

# SQL Index Recommendation Reviewer

You help developers make slow queries fast without burying their tables in indexes. You read the schema and the query the way a database engineer would: which columns are compared with equality, which with ranges, what the query sorts by, what it joins on, and which indexes already exist. You propose the smallest set of indexes that serves the workload, in the right column order, and you are honest that only EXPLAIN on real data can confirm it.

## Files in this skill

- `scripts/suggest_indexes.py` - parses CREATE TABLE / CREATE INDEX statements and SELECT, UPDATE, and DELETE queries (subqueries and CTE bodies separately), then prints candidate indexes with DDL, the queries each one serves, covering columns, overlaps, and schema findings (Python 3 standard library only; no database connection)
- `references/indexing-rules.md` - column order, covering indexes, partial and expression indexes, sargability, and over-indexing rules
- `references/explain-reading-guide.md` - how to read EXPLAIN in PostgreSQL, MySQL, and SQLite to confirm or reject a suggestion
- `templates/index-review.md` - the review report to return
- `examples/example-shop-orders-review.md` - a worked review of an online shop's slow queries

## Workflow

### 1. Collect the facts
Ask, or assume and say so:
- The engine and version (PostgreSQL, MySQL/MariaDB, SQLite, SQL Server), and the schema with existing indexes (`\d table`, `SHOW CREATE TABLE`, or the migration files).
- The slow queries exactly as the app sends them, with typical parameter values, how often each runs, and how slow it is.
- Table sizes (rows), how write-heavy each table is, and any EXPLAIN or EXPLAIN ANALYZE output.
- Constraints: can the team build indexes online, disk space, and replication lag concerns.

### 2. Run the helper
```bash
python3 scripts/suggest_indexes.py --schema schema.sql slow-queries.sql
python3 scripts/suggest_indexes.py all-in-one.sql --dialect mysql
python3 scripts/suggest_indexes.py --schema schema.sql - --json < query.sql
python3 scripts/suggest_indexes.py --schema schema.sql queries.sql --strict   # exit 1 on non-sargable filters or duplicate indexes
```
The helper is a heuristic parser, not a planner: it does not know row counts, data distribution, or which plan the database will choose. It can miss predicates in complex SQL (it lists them as "not understood"). Read every query yourself and treat each suggestion as a candidate to test. If you cannot run the script, apply `references/indexing-rules.md` by hand in the same order.

### 3. Decide
1. Fix the query before adding an index: rewrite non-sargable filters (functions on columns, arithmetic, leading-wildcard LIKE, OR across columns) so an index can be used at all.
2. For each query, choose column order: equality columns (most selective first when you know it), then ORDER BY or GROUP BY columns if the query sorts with LIMIT, then one range column.
3. Merge: one composite index can serve several queries when they share its leftmost columns. Prefer extending an existing index over adding a new one.
4. Add covering (INCLUDE) columns only for hot, read-heavy queries, and keep them few.
5. Remove before adding: drop duplicate and redundant-prefix indexes after checking usage statistics (`pg_stat_user_indexes`, `sys.schema_unused_indexes`).
6. Count the cost: every index slows inserts and updates of its columns and uses disk and memory. Write-heavy tables deserve fewer, broader indexes.

### 4. Deliver
Fill in `templates/index-review.md`: per-query diagnosis, the recommended DDL in build order with the queries each serves, indexes to drop, query rewrites, tradeoffs, and how to verify with EXPLAIN before and after (see `references/explain-reading-guide.md`). Follow the style of `examples/example-shop-orders-review.md`.

## Rules
- Never claim a speed-up without saying how to measure it; give expected plan changes ("index scan instead of sequential scan and sort"), not invented timings.
- Use safe build commands for production: `CREATE INDEX CONCURRENTLY` in PostgreSQL, online DDL (`ALGORITHM=INPLACE, LOCK=NONE`) in MySQL, and a maintenance window for SQLite.
- Do not recommend an index on a low-selectivity column alone (status, booleans); use it inside a composite or as a partial index condition.
- Keep primary keys and unique constraints; never suggest dropping them to remove "redundancy".
- Tell the user to build one index at a time and compare plans, and to remove indexes that EXPLAIN never uses.
FILE:references/indexing-rules.md
# Indexing rules

The rules the helper applies, plus the judgment it cannot make. Examples use PostgreSQL syntax unless noted.

## 1. Column order in a composite index

A B-tree index is sorted by its first column, then the second, and so on. A query can seek on a leftmost run of columns compared with equality, then use one more column for a range or for sorting.

Order: **equality columns, then sort columns, then one range column**.
```sql
-- WHERE customer_id = ? AND status = 'paid' ORDER BY created_at DESC LIMIT 20
CREATE INDEX ON orders (customer_id, status, created_at);
```
- Among equality columns, put the one used by the most queries first so the index serves them all; selectivity matters less than reuse once all of them are equalities.
- After a range column (`>`, `<`, `BETWEEN`, `LIKE 'abc%'`), later columns cannot be used for seeking, only for filtering inside the index.
- A sort can use the index only if every ORDER BY column comes from the index in the same order and directions are all the same (or all reversed). Mixed directions need a matching index definition (`created_at DESC, id ASC`).
- With a range and an ORDER BY on a different column, the database must choose: seek with the range and sort, or walk the sort order and filter. With LIMIT, walking the sort order often wins.

## 2. Leftmost prefix and merging

An index on `(a, b, c)` serves queries on `a`, on `a, b`, and on `a, b, c`, but not on `b` or `c` alone. Two candidates where one is a leftmost prefix of the other become one index. An existing index `(a)` is redundant once `(a, b)` exists, unless it is unique, partial, or much smaller and very hot.

## 3. Joins and foreign keys

- Index the column on the side that is looked up: usually the foreign key (`orders.customer_id`). The primary key side is already indexed.
- PostgreSQL does not index foreign keys automatically (MySQL InnoDB does). Missing FK indexes also make deletes on the parent table slow.
- A join column plus that table's own filters can form one composite: `(customer_id, created_at)`.

## 4. Covering indexes

If the index holds every column the query reads, the database can skip the table (index-only scan).
- PostgreSQL 11+ and SQL Server: `INCLUDE (col, ...)` stores extra columns without sorting by them.
- MySQL and SQLite: append the columns to the key; InnoDB secondary indexes already carry the primary key.
- Use it for hot, read-mostly queries with few selected columns. Wide covering indexes are big and slow to write.
- PostgreSQL index-only scans also need a well-vacuumed table (visibility map).

## 5. Partial and expression indexes

- Partial (PostgreSQL, SQLite; filtered index in SQL Server): `CREATE INDEX ON orders (created_at) WHERE status = 'paid' AND shipped_at IS NULL;` Tiny and fast when the condition matches few rows. The query must repeat the same condition.
- Expression: `CREATE INDEX ON users (lower(email));` serves `WHERE lower(email) = ?`. MySQL 8 supports functional indexes `((lower(email)))` or a generated column with an index.

## 6. Sargability: filters an index cannot use

| Pattern | Problem | Fix |
|---|---|---|
| `WHERE date(created_at) = '2026-10-12'` | function on the column | `created_at >= '2026-10-12' AND created_at < '2026-10-13'` |
| `WHERE YEAR(updated_at) = 2026` | function on the column | range on the column |
| `WHERE price * 1.2 > 100` | arithmetic on the column | `price > 100 / 1.2` |
| `WHERE name LIKE '%lamp%'` | leading wildcard | full-text search, or `pg_trgm` GIN index |
| `WHERE lower(name) = ?` | function | expression index on `lower(name)` or a case-insensitive collation |
| `WHERE a = 1 OR b = 2` | OR across columns | `UNION` of two indexed queries, or one index per column (bitmap OR in PostgreSQL) |
| `WHERE status <> 'done'`, `NOT IN` | negative filter | positive list `IN (...)` or a partial index |
| `WHERE id = '42'` on an integer column | implicit cast (MySQL may skip the index on string columns compared to numbers) | compare with the right type |

## 7. Over-indexing

- Every index is updated on INSERT, on DELETE, and on UPDATE of its columns; it also uses disk, memory (buffer cache), and backup and replication bandwidth.
- Duplicates (same columns, same condition) are pure cost: drop one.
- Single-column indexes on low-selectivity columns (booleans, status, type) are rarely used; the planner prefers a scan when a value matches more than about 5 to 20 percent of rows. Use them inside composites or as partial-index conditions.
- As a rule of thumb, more than 5 or 6 indexes on a write-heavy table deserves a review; read-mostly tables can carry more.
- Find unused indexes before dropping: PostgreSQL `pg_stat_user_indexes.idx_scan = 0` (since the last stats reset, on every replica), MySQL `sys.schema_unused_indexes`. Drop with `DROP INDEX CONCURRENTLY` in PostgreSQL.

## 8. Building safely

- PostgreSQL: `CREATE INDEX CONCURRENTLY` avoids blocking writes; it cannot run inside a transaction and leaves an INVALID index on failure (drop and retry).
- MySQL 8: `ALTER TABLE ... ADD INDEX ..., ALGORITHM=INPLACE, LOCK=NONE`; watch replication lag on large tables.
- SQLite: index builds lock the database; run during low traffic.
- Build one index at a time, check the plan, then the next.
FILE:references/explain-reading-guide.md
# Reading EXPLAIN to confirm an index

Always compare the plan before and after, on production-like data and with realistic parameter values. Small test tables make the planner choose scans that are right for small tables.

## PostgreSQL

```sql
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;   -- runs the query; wrap writes in BEGIN ... ROLLBACK
```
| Plan node | Meaning |
|---|---|
| Seq Scan | reads the whole table; fine for small tables or when most rows match |
| Index Scan | seeks the index, then reads matching table rows |
| Index Only Scan | answered from the index; check "Heap Fetches" is low (vacuum if not) |
| Bitmap Index Scan + Bitmap Heap Scan | collects many matches, then reads pages in order; common for medium selectivity and OR |
| Sort (with "external merge") | sorting spilled to disk; an index in ORDER BY order can remove it |
| Rows Removed by Filter | rows read and thrown away; high numbers mean the index does not cover the filter |

Look at: actual time of the top node, rows estimated versus actual (a 10x gap means stale statistics: run `ANALYZE table`), and shared hit/read buffers. Confirm a new index is used with `pg_stat_user_indexes.idx_scan` after a day of traffic.

## MySQL 8

```sql
EXPLAIN FORMAT=TREE SELECT ...;
EXPLAIN ANALYZE SELECT ...;               -- 8.0.18+, runs the query
```
In classic `EXPLAIN` output check: `type` (ALL = full scan, ref/eq_ref/range = index use, index = full index scan), `key` (the index chosen), `rows` (estimate), and `Extra` ("Using index" = covering, "Using filesort" = extra sort, "Using temporary" = temporary table for GROUP BY or DISTINCT). Refresh statistics with `ANALYZE TABLE t`.

## SQLite

```sql
EXPLAIN QUERY PLAN SELECT ...;
```
"SCAN t" is a full scan; "SEARCH t USING INDEX idx (a=? AND b>?)" shows which columns seek; "USING COVERING INDEX" means no table access; "USE TEMP B-TREE FOR ORDER BY" means an extra sort. Run `ANALYZE;` so the planner has statistics.

## What success looks like

- The slow query's plan switches from a full scan plus sort to an index (only) scan with no sort, and the rows read drop close to the rows returned.
- Writes on the table do not get noticeably slower (check p95 insert and update latency after deploying).
- No other important query changed plan for the worse; run the top queries by total time (`pg_stat_statements`, MySQL performance schema digest tables) before and after.

## When the index is not used

1. Statistics are stale: run ANALYZE.
2. The filter is not sargable (see indexing-rules.md section 6).
3. The value matches too many rows; the planner is right to scan.
4. Column types or collations differ between the query and the column (implicit cast).
5. A parameterized query uses a generic plan; test with the real prepared statement.
6. The index columns are in the wrong order for the query.
FILE:templates/index-review.md
# Index review: app_or_service

**Engine:** engine_and_version | **Tables:** tables_with_row_counts | **Write load:** write_profile
**Helper:** n_queries queries analyzed, n_suggested candidates, n_findings schema findings

## Summary

Two or three sentences: the main cause of slowness, the indexes to add and drop, and the expected plan change.

## Per-query diagnosis

| Query | Runs | Now (plan / time) | Problem | Fix |
|---|---|---|---|---|
| Q1 | per_minute | Seq Scan + Sort | | |

## Query rewrites (do these first)

```sql
-- before
-- after
```

## Indexes to add (build in this order)

| # | DDL | Serves | Why this column order | Cost |
|---|---|---|---|---|
| 1 | | | | |

## Indexes to drop (after checking usage)

| Index | Reason | Check first |
|---|---|---|
| | duplicate / redundant prefix / unused / low selectivity | `idx_scan` on primary and replicas |

## Net effect per table

| Table | Indexes before | After | Note |
|---|---|---|---|

## How to verify

1. Capture `EXPLAIN (ANALYZE, BUFFERS)` for each query before.
2. Build one index (`CONCURRENTLY` / online DDL), run `ANALYZE`, capture the plan again.
3. Expect: (plan change per query).
4. Watch write latency and replication lag for a day; then drop the indexes listed above.

## Caveats

- Suggestions come from query text and schema only; row counts and data skew can change the best choice.
FILE:examples/example-shop-orders-review.md
# Example: index review for an online shop

## User request

"Our PostgreSQL 16 shop database is getting slow. orders has 12 million rows and gets about 40 inserts a second, customers has 900k rows, products 20k. Here are the schema and the six queries that show up in pg_stat_statements. What indexes should we add, and do we have too many already?"

## Helper run

```
$ python3 scripts/suggest_indexes.py --schema shop-schema.sql slow-queries.sql
Schema: 4 table(s), 12 existing index(es) incl. keys | dialect: postgres
Queries analyzed: 7

Q1 SELECT: SELECT o.id, o.total, o.created_at FROM orders o WHERE o.customer_id = $1 AND o.status = 'paid' ORDER BY o.cre...
  equality: orders.customer_id (=), orders.status (=)
  order by: orders.created_at DESC  (LIMIT)
Q2 SELECT: SELECT c.email, count(*) AS orders FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.country = 'D...
  equality: customers.country (=)
  range: orders.created_at (range)
  joins: orders.customer_id = customers.id
  group by: customers.email
Q3 SELECT: SELECT * FROM products WHERE lower(name) LIKE '%lamp%'
  WARN non-sargable: lower(name) in the WHERE clause hides the column from a normal index; ... the LIKE pattern '%lamp%' also starts with a wildcard, so only full-text search or a trigram index can help
Q4 SELECT: ... WHERE oi.order_id IN (SELECT id FROM orders WHERE customer_id = 42 AND created_at > now() - interval '30 days')
  note: subquery analyzed as Q5
Q6 UPDATE: UPDATE orders SET status = 'shipped', shipped_at = now() WHERE status = 'paid' AND shipped_at IS NULL AND crea...
Q7 SELECT: SELECT id FROM customers WHERE email = 'ann@example.com' OR phone = '+491701234567'
  WARN OR across different columns (...): one composite index cannot serve both sides; consider UNION ALL of two indexed queries or one index per side

Suggested indexes (4):
  1. CREATE INDEX CONCURRENTLY idx_customers_country_email ON customers (country, email);
  2. CREATE INDEX CONCURRENTLY idx_orders_customer_id_created_at ON orders (customer_id, created_at);
     serves: Q2, Q5 | overlap: same leading column as #3
  3. CREATE INDEX CONCURRENTLY idx_orders_customer_id_status_created_at ON orders (customer_id, status, created_at) INCLUDE (id, total);
     serves: Q1 | overlap: same leading column as #2
  4. CREATE INDEX CONCURRENTLY idx_orders_status_shipped_at_created_at ON orders (status, shipped_at, created_at);
     serves: Q6 | alternative: ... ON orders (status, created_at) WHERE shipped_at IS NULL;

Schema findings (5):
  WARN redundant-prefix  customers: idx_customers_country (country) is a leftmost prefix of idx_customers_country_created ...
  WARN low-selectivity   customers: idx_customers_active indexes only 'is_active' ...
  HIGH duplicate-index   orders: idx_orders_created and orders_created_at_idx have the same columns (created_at); drop one
  WARN low-selectivity   orders: idx_orders_status indexes only 'status' ...
  WARN many-indexes      orders: 4 existing + 3 suggested = 7 indexes (limit 6) ...
```

## Review (filled template, shortened)

**Summary:** orders has no index that starts with customer_id, so the order history page (Q1) and the recent-orders lookup (Q5) scan millions of rows, and two of its four indexes are a duplicate and a low-selectivity status index. Add one composite and one partial index on orders, a phone index and a trigram index elsewhere, and drop three indexes: orders ends with the same count of 4 but every one is useful.

| Query | Problem | Fix |
|---|---|---|
| Q1 order history | no index on customer_id; sort on created_at | (customer_id, created_at) with status and total included: newest rows for the customer, status filtered inside the index, LIMIT stops after 20 |
| Q2 country report | join needs orders.customer_id; customers.country is already served by idx_customers_country_created | same orders index; no new customers index (the helper's #1 only helps the GROUP BY, and the report runs once a night) |
| Q3 product search | `lower(name) LIKE '%lamp%'` cannot use a B-tree | pg_trgm GIN index on lower(name) |
| Q4/Q5 recent items | subquery needs (customer_id, created_at); order_items PK (order_id, product_id) serves the IN | same orders index |
| Q6 shipping job | scans by status | partial index on created_at for paid, unshipped orders only |
| Q7 support lookup | OR across email and phone | UNION of two lookups; email is unique already, add a phone index |

Why not the helper's #3 as well: #2 and #3 share customer_id, and on a table with 40 inserts a second one index is better than two. The user confirmed that about 95 percent of orders are paid, so Q1 throws away few rows, and (customer_id, created_at) with status in INCLUDE keeps Q1 fast. If the share of non-paid orders grows, switch to #3's column order.

**Indexes to add (in this order):**

```sql
CREATE INDEX CONCURRENTLY idx_orders_customer_created
    ON orders (customer_id, created_at) INCLUDE (id, status, total);        -- Q1, Q2, Q5
CREATE INDEX CONCURRENTLY idx_orders_paid_unshipped
    ON orders (created_at) WHERE status = 'paid' AND shipped_at IS NULL;    -- Q6, holds only paid, unshipped orders
CREATE INDEX CONCURRENTLY idx_customers_phone ON customers (phone);       -- Q7 after the rewrite
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_products_name_trgm
    ON products USING gin (lower(name) gin_trgm_ops);                      -- Q3, query unchanged
```

**Query rewrite (Q7):**

```sql
SELECT id FROM customers WHERE email = 'ann@example.com'
UNION
SELECT id FROM customers WHERE phone = '+491701234567';
```

**Indexes to drop (after checking `pg_stat_user_indexes.idx_scan` on the primary and both replicas):**

| Index | Reason |
|---|---|
| orders_created_at_idx | duplicate of idx_orders_created |
| idx_orders_status | low selectivity; Q6 now uses the partial index |
| idx_customers_country | leftmost prefix of idx_customers_country_created |
| idx_customers_active (check) | boolean; keep only if idx_scan shows real use |

**Net effect:** orders 4 -> 4 indexes (two replaced by better ones), customers 5 -> 5 (one dropped, phone added; 4 if is_active goes too), products 2 -> 3.

**Recheck with the helper** (schema after the change, queries after the rewrite; query numbers follow the new file, so Q3 is the old Q5 and Q4/Q5 are the two UNION branches):

```
$ python3 scripts/suggest_indexes.py --schema shop-schema-after.sql shop-queries-after.sql --no-covering
Already served by existing indexes (5):
  customers (email) -> customers_email_key (Q4)
  customers (id, country) -> customers_pkey (Q2)
  customers (phone) -> idx_customers_phone (Q5)
  orders (created_at) -> idx_orders_created (Q2)
  orders (customer_id, created_at) -> idx_orders_customer_created (Q2, Q3)
```
The helper still lists (customer_id, status, created_at) for Q1 and (country, email) for Q2; both were rejected on purpose for the reasons above. There are no duplicate or redundant-prefix findings left.

**How to verify:** before and after each build, run `EXPLAIN (ANALYZE, BUFFERS)` for Q1 with a real customer id. Expect "Index Scan using idx_orders_customer_created" (or Index Only Scan) with no Sort node and about 20 rows read instead of a Bitmap Heap Scan over thousands. Watch insert latency on orders for a day before dropping the old indexes.
FILE:scripts/suggest_indexes.py
#!/usr/bin/env python3
"""Suggest candidate indexes for slow SQL queries from a schema and the query text (no database needed).

Usage:
  python3 suggest_indexes.py --schema schema.sql queries.sql [options]
  python3 suggest_indexes.py all-in-one.sql                 (CREATE statements and queries in one file)
  python3 suggest_indexes.py --schema schema.sql - < slow.sql

Options:
  --schema FILE       CREATE TABLE / CREATE INDEX statements (may also be inside the query file)
  --dialect NAME      postgres (default), mysql, or sqlite; changes INCLUDE and online-build hints
  --max-indexes N     warn when a table would have more than N indexes (default 6)
  --no-covering       do not suggest covering (INCLUDE) columns
  --json              print the result as JSON
  --strict            exit 1 if any query has a non-sargable predicate or the schema has duplicate indexes

What it does: lightweight parsing of SELECT, UPDATE, and DELETE statements (subqueries are analyzed
separately) to find equality filters, range filters, join columns, ORDER BY and GROUP BY columns,
and selected columns per table. It builds composite candidates (equality columns, then sort, then
range), merges candidates that are prefixes of each other, skips those already served by an existing
index, and reports duplicate and redundant indexes, low-selectivity single-column indexes, and tables
with many indexes. It does not know row counts or data distribution: confirm every suggestion with
EXPLAIN on real data.
Exit codes: 0 ok, 1 --strict findings, 2 usage or read error.
Python 3 standard library only.
"""
import json
import re
import signal
import sys

if hasattr(signal, "SIGPIPE"):
    signal.signal(signal.SIGPIPE, signal.SIG_DFL)  # quiet exit when piped into head

KEYWORDS = set("""select from where and or not in is null like ilike between join inner left right full outer cross on
using group by order having limit offset as asc desc distinct union all exists case when then else end update set
delete insert into values returning with true false interval current_date current_timestamp now lateral natural
fetch first rows only for share nowait skip locked""".split())
LOW_CARD = re.compile(r"^(is_|has_|can_|should_)|^(status|state|type|kind|flag|active|enabled|deleted|archived|gender|"
                      r"visible|published|verified|priority|level|tier|role)$", re.I)
CLAUSE_END = r"(?=\b(?:where|group\s+by|order\s+by|having|limit|offset|union|returning|for\s+update|window)\b|$)"


class UsageError(Exception):
    pass


# ---------------------------------------------------------------- text helpers

def strip_comments(sql):
    sql = re.sub(r"/\*.*?\*/", " ", sql, flags=re.S)
    return re.sub(r"--[^\n]*", " ", sql)


def split_statements(sql):
    out, buf, quote = [], [], None
    for ch in sql:
        if quote:
            buf.append(ch)
            if ch == quote:
                quote = None
            continue
        if ch in "'\"`":
            quote = ch
        if ch == ";":
            out.append("".join(buf))
            buf = []
        else:
            buf.append(ch)
    out.append("".join(buf))
    return [s.strip() for s in out if s.strip()]


def unquote_idents(sql):
    sql = re.sub(r'"([A-Za-z_][\w$]*)"', r"\1", sql)
    sql = re.sub(r"`([A-Za-z_][\w$]*)`", r"\1", sql)
    return re.sub(r"\[([A-Za-z_][\w$]*)\]", r"\1", sql)


def mask_strings(sql):
    """Replace string literals with placeholders, remembering LIKE patterns."""
    lits = []
    def rep(m):
        lits.append(m.group(0)[1:-1])
        return f" __lit{len(lits) - 1}__ "
    return re.sub(r"'(?:[^']|'')*'", rep, sql), lits


def split_top(text, sep_regex):
    """Split on a keyword at parenthesis depth 0."""
    parts, depth, last = [], 0, 0
    for m in re.finditer(r"\(|\)|" + sep_regex, text, flags=re.I):
        tok = m.group(0)
        if tok == "(":
            depth += 1
        elif tok == ")":
            depth -= 1
        elif depth == 0:
            parts.append(text[last:m.start()])
            last = m.end()
    parts.append(text[last:])
    return [p.strip() for p in parts if p.strip()]


def split_and(text):
    """Split on top-level AND, keeping 'x BETWEEN a AND b' together."""
    parts = split_top(text, r"\band\b")
    out = []
    for part in parts:
        if out and re.search(r"\bbetween\s+[^\s]+$", out[-1], re.I):
            out[-1] = f"{out[-1]} AND {part}"
        else:
            out.append(part)
    return out


def extract_subqueries(sql):
    """Pull out innermost (SELECT ...) blocks; return (outer_sql, [subqueries])."""
    subs = []
    pattern = re.compile(r"\(\s*(select\b[^()]*(?:\([^()]*\)[^()]*)*)\)", re.I)
    while True:
        m = pattern.search(sql)
        if not m:
            break
        subs.append(m.group(1))
        sql = sql[:m.start()] + " __subq__ " + sql[m.end():]
    return sql, subs


# ---------------------------------------------------------------- schema

def parse_columns_list(text):
    return [re.sub(r"\s+(asc|desc)\b.*$", "", c.strip(), flags=re.I).strip().lower()
            for c in split_top(text, r",")]


def parse_schema(statements):
    tables, indexes = {}, []
    for st in statements:
        s = unquote_idents(strip_comments(st)).strip()
        m = re.match(r"create\s+table\s+(?:if\s+not\s+exists\s+)?([\w.]+)\s*\((.*)\)\s*[^)]*$", s, re.I | re.S)
        if m:
            name = m.group(1).split(".")[-1].lower()
            cols, pk = [], None
            for part in split_top(m.group(2), r","):
                p = part.strip()
                low = p.lower()
                c = re.match(r"(?:constraint\s+\w+\s+)?primary\s+key\s*\(([^)]*)\)", low)
                if c:
                    pk = parse_columns_list(c.group(1))
                    continue
                c = re.match(r"(?:constraint\s+(\w+)\s+)?unique\s*(?:key\s+\w+\s*)?\(([^)]*)\)", low)
                if c:
                    indexes.append({"name": c.group(1) or f"{name}_unique", "table": name,
                                    "columns": parse_columns_list(c.group(2)), "unique": True, "kind": "unique"})
                    continue
                c = re.match(r"(?:key|index)\s+(\w+)\s*\(([^)]*)\)", low)
                if c:
                    indexes.append({"name": c.group(1), "table": name, "columns": parse_columns_list(c.group(2)),
                                    "unique": False, "kind": "index"})
                    continue
                if re.match(r"(constraint|foreign\s+key|check|exclude)\b", low):
                    continue
                col = re.match(r"([a-z_][\w$]*)\s+", low + " ")
                if col:
                    cols.append(col.group(1))
                    if re.search(r"\bprimary\s+key\b", low):
                        pk = [col.group(1)]
                    elif re.search(r"\bunique\b", low):
                        indexes.append({"name": f"{name}_{col.group(1)}_key", "table": name,
                                        "columns": [col.group(1)], "unique": True, "kind": "unique"})
            tables[name] = {"columns": cols}
            if pk:
                indexes.append({"name": f"{name}_pkey", "table": name, "columns": pk, "unique": True,
                                "kind": "primary"})
            continue
        m = re.match(r"create\s+(unique\s+)?index\s+(?:concurrently\s+)?(?:if\s+not\s+exists\s+)?(\w+)\s+on\s+"
                     r"([\w.]+)\s*(?:using\s+\w+\s*)?\(([^;]*?)\)\s*(include\s*\(([^)]*)\))?\s*(where\s+.*)?$",
                     s, re.I | re.S)
        if m:
            cols = parse_columns_list(m.group(4))
            indexes.append({"name": m.group(2), "table": m.group(3).split(".")[-1].lower(), "columns": cols,
                            "unique": bool(m.group(1)), "kind": "index",
                            "include": parse_columns_list(m.group(6)) if m.group(6) else [],
                            "partial": (m.group(7) or "").strip() or None})
    return tables, indexes


# ---------------------------------------------------------------- queries

def parse_from(from_text):
    """Return (tables {alias: table}, order list, join predicates text list)."""
    aliases, order, ons = {}, [], []
    pieces = re.split(r"\b((?:natural\s+)?(?:inner\s+|left\s+(?:outer\s+)?|right\s+(?:outer\s+)?|full\s+(?:outer\s+)?|"
                      r"cross\s+)?join)\b", from_text, flags=re.I)
    items = [pieces[0]] + [pieces[i + 1] for i in range(1, len(pieces) - 1, 2)]
    for item in items:
        on_split = re.split(r"\bon\b", item, maxsplit=1, flags=re.I)
        using = re.search(r"\busing\s*\(([^)]*)\)", on_split[0], re.I)
        head = re.sub(r"\busing\s*\([^)]*\)", "", on_split[0], flags=re.I)
        for ref in head.split(","):
            toks = [t for t in re.split(r"\s+", ref.strip()) if t and t.lower() not in ("as", "lateral", "only")]
            if not toks or toks[0] == "__subq__":
                continue
            table = toks[0].split(".")[-1].lower()
            alias = toks[1].lower() if len(toks) > 1 else table
            aliases[alias] = table
            order.append(alias)
        if len(on_split) > 1:
            ons.append(on_split[1])
        if using and len(order) >= 2:
            for col in parse_columns_list(using.group(1)):
                ons.append(f"{order[-2]}.{col} = {order[-1]}.{col}")
    return aliases, order, ons


COL = r"((?:[a-z_][\w$]*\.)?[a-z_][\w$]*)"
VALUE = r"(?:__lit\d+__|\?|:\w+|\$\d+|@\w+|%s|-?\d+(?:\.\d+)?|true|false|null|__subq__|current_date|current_timestamp|now\(\))"


def resolve(ref, aliases, tables):
    ref = ref.lower()
    if "." in ref:
        a, c = ref.split(".", 1)
        return (aliases.get(a, a), c)
    if len(set(aliases.values())) == 1:
        return (next(iter(aliases.values())), ref)
    owners = [t for t in set(aliases.values()) if ref in tables.get(t, {}).get("columns", [])]
    return (owners[0], ref) if len(owners) == 1 else (None, ref)


def analyze_predicate(pred, lits, aliases, tables, q):
    p = pred.strip()
    while p.startswith("(") and p.endswith(")") and len(split_top(p[1:-1], r"\band\b")) >= 1 and \
            p.count("(") == p.count(")") and _balanced_wrap(p):
        p = p[1:-1].strip()
    if len(split_and(p)) > 1:
        for sub in split_and(p):
            analyze_predicate(sub, lits, aliases, tables, q)
        return
    ors = split_top(p, r"\bor\b")
    if len(ors) > 1:
        cols = set()
        for o in ors:
            m = re.match(rf"^\(?\s*{COL}\s*(=|in\b)", o.strip(), re.I)
            cols.add(m.group(1).lower() if m else None)
        if len(cols) == 1 and None not in cols:
            t, c = resolve(cols.pop(), aliases, tables)
            q["filters"].append({"table": t, "column": c, "kind": "eq", "op": "OR of equalities (same as IN)"})
        else:
            shown = re.sub(r"__lit(\d+)__", lambda m: "'" + lits[int(m.group(1))] + "'", p)
            shown = shown if len(shown) <= 70 else shown[:67] + "..."
            q["warnings"].append(f"OR across different columns ({shown}): one composite index cannot serve "
                                 "both sides; consider UNION ALL of two indexed queries or one index per side")
        return
    if re.match(r"^not\b", p, re.I) or re.search(r"(<>|!=|\bnot\s+in\b|\bnot\s+like\b)", p, re.I):
        m = re.match(rf"^(?:not\s+)?{COL}", p, re.I)
        shown = re.sub(r"__lit(\d+)__", lambda m: "'" + lits[int(m.group(1))] + "'", p)[:50]
        q["notes"].append(f"negative predicate '{shown}' rarely uses an index; it is applied as a filter")
        return
    m = re.match(rf"^{COL}\s*(=|<=>)\s*{COL}$", p, re.I)
    if m and not re.fullmatch(VALUE, m.group(3), re.I) and m.group(3).lower() not in KEYWORDS:
        left, right = resolve(m.group(1), aliases, tables), resolve(m.group(3), aliases, tables)
        if left[0] and right[0] and left[0] != right[0]:
            q["joins"].append((left, right))
            return
    m = re.match(rf"^{VALUE}\s*(=|<|>|<=|>=)\s*{COL}$", p, re.I)
    if m:
        flip = {"<": ">", ">": "<", "<=": ">=", ">=": "<=", "=": "="}
        p = f"{m.group(2)} {flip[m.group(1)]} __v__"
    m = re.match(rf"^{COL}\s*(=|<=>)\s*(.+)$", p, re.I)
    if m and m.group(1).lower() not in KEYWORDS:
        t, c = resolve(m.group(1), aliases, tables)
        q["filters"].append({"table": t, "column": c, "kind": "eq", "op": "="})
        return
    m = re.match(rf"^{COL}\s+in\s+__subq__$", p, re.I)
    if m:
        t, c = resolve(m.group(1), aliases, tables)
        q["filters"].append({"table": t, "column": c, "kind": "eq", "op": "IN (subquery)"})
        return
    m = re.match(rf"^{COL}\s+in\s*\((.*)\)$", p, re.I | re.S)
    if m:
        t, c = resolve(m.group(1), aliases, tables)
        q["filters"].append({"table": t, "column": c, "kind": "eq", "op": "IN"})
        return
    m = re.match(rf"^{COL}\s+is\s+(not\s+)?null$", p, re.I)
    if m:
        t, c = resolve(m.group(1), aliases, tables)
        if m.group(2):
            q["notes"].append(f"'{c} IS NOT NULL' is usually not selective; a partial index can help if most rows are NULL")
        else:
            q["filters"].append({"table": t, "column": c, "kind": "eq", "op": "IS NULL"})
        return
    m = re.match(rf"^{COL}\s*(<|>|<=|>=)\s*(.+)$", p, re.I) or re.match(rf"^{COL}\s+between\s+(.+)\s+and\s+(.+)$", p, re.I)
    if m and m.group(1).lower() not in KEYWORDS:
        t, c = resolve(m.group(1), aliases, tables)
        q["filters"].append({"table": t, "column": c, "kind": "range", "op": "range"})
        return
    m = re.match(rf"^{COL}\s+(i?like)\s+__lit(\d+)__", p, re.I)
    if m:
        t, c = resolve(m.group(1), aliases, tables)
        pattern = lits[int(m.group(3))]
        if pattern.startswith(("%", "_")):
            q["warnings"].append(f"non-sargable: {c} {m.group(2).upper()} '{pattern}' starts with a wildcard, so a "
                                 "B-tree index cannot seek; use full-text search or a trigram index (PostgreSQL pg_trgm)")
            q["non_sargable"] = True
        else:
            q["filters"].append({"table": t, "column": c, "kind": "range", "op": f"LIKE '{pattern}'"})
            if m.group(2).lower() == "ilike":
                q["notes"].append(f"ILIKE on {c}: a plain B-tree index is case-sensitive; index lower({c}) instead")
        return
    m = re.match(rf"^([a-z_]+)\s*\(\s*{COL}[^)]*\)\s*(=|<|>|<=|>=|like|between)", p, re.I)
    if m and m.group(1).lower() not in KEYWORDS:
        func, col = m.group(1).lower(), m.group(2)
        t, c = resolve(col, aliases, tables)
        wild = re.search(r"like\s+__lit(\d+)__", p, re.I)
        extra = ""
        if wild and lits[int(wild.group(1))].startswith(("%", "_")):
            extra = (f"; the LIKE pattern '{lits[int(wild.group(1))]}' also starts with a wildcard, so only full-text "
                     "search or a trigram index can help")
        q["warnings"].append(f"non-sargable: {func}({c}) in the WHERE clause hides the column from a normal index; "
                             f"rewrite as a range on {c} or add an expression index on {func}({c}){extra}")
        q["non_sargable"] = True
        return
    m = re.match(rf"^{COL}\s*[-+*/]\s*\S+\s*(=|<|>|<=|>=)", p, re.I)
    if m:
        t, c = resolve(m.group(1), aliases, tables)
        q["warnings"].append(f"non-sargable: arithmetic on {c} ('{p[:40]}'); move the arithmetic to the other side")
        q["non_sargable"] = True
        return
    if "__subq__" in p and re.search(r"\bexists\b", p, re.I):
        return
    q["notes"].append(f"predicate not understood, check by hand: {p[:60]}")


def _balanced_wrap(p):
    depth = 0
    for i, ch in enumerate(p):
        depth += ch == "("
        depth -= ch == ")"
        if depth == 0 and i < len(p) - 1:
            return False
    return True


def parse_query(raw, tables, number, lits=None, outer_aliases=None):
    if lits is None:
        sql, lits = mask_strings(unquote_idents(strip_comments(raw)))
    else:
        sql = raw
    sql = re.sub(r"\s+", " ", sql).strip()
    outer, subs = extract_subqueries(sql)
    shown = re.sub(r"__lit(\d+)__", lambda m: "'" + lits[int(m.group(1))] + "'", re.sub(r"\s+", " ", raw.strip()))
    shown = re.sub(r"\s+", " ", shown)
    q = {"n": number, "text": shown[:110] + ("..." if len(shown) > 110 else ""), "kind": None, "aliases": {},
         "filters": [], "joins": [], "order_by": [], "group_by": [], "selected": {}, "select_star": False,
         "limit": False, "warnings": [], "notes": [], "non_sargable": False, "subqueries": subs, "lits": lits}
    low = outer.lower()
    cte_names = set()
    if low.startswith("with "):
        cte_names = {n.lower() for n in re.findall(r"(?:with|,)\s*(?:recursive\s+)?(\w+)\s+as\b", outer, re.I)}
        q["notes"].append(f"CTE {', '.join(sorted(cte_names))} analyzed as separate queries; the final SELECT reads from it")
        m = re.search(r"(?:\)|__subq__)\s*((?:select|update|delete)\b.*)$", outer, re.I)
        outer = m.group(1) if m else outer
        low = outer.lower()
    if low.startswith("select"):
        q["kind"] = "SELECT"
        m = re.match(r"select\s+(?:distinct\s+)?(.*?)\s+from\s+(.*?)" + CLAUSE_END, outer, re.I)
        if not m:
            q["notes"].append("no FROM clause found")
            return q
        select_list, from_text = m.group(1), m.group(2)
    elif low.startswith("update"):
        q["kind"] = "UPDATE"
        m = re.match(r"update\s+(.*?)\s+set\s+", outer, re.I)
        select_list, from_text = "", m.group(1) if m else ""
    elif low.startswith("delete"):
        q["kind"] = "DELETE"
        m = re.match(r"delete\s+from\s+(.*?)" + CLAUSE_END, outer, re.I)
        select_list, from_text = "", m.group(1) if m else ""
    else:
        return None
    own, _order, ons = parse_from(from_text)
    own = {a: t for a, t in own.items() if t not in cte_names}
    q["aliases"] = own
    aliases = {**(outer_aliases or {}), **own}  # outer aliases resolve correlated references
    unknown = [t for t in set(own.values()) if tables and t not in tables]
    if unknown:
        q["notes"].append(f"table(s) not in the schema: {', '.join(sorted(unknown))}; existing indexes unknown")
    for on in ons:
        for pred in split_and(on):
            analyze_predicate(pred, lits, aliases, tables, q)
    w = re.search(r"\bwhere\b(.*?)(?=\b(?:group\s+by|order\s+by|having|limit|offset|returning|for\s+update)\b|$)", outer, re.I)
    if w:
        for pred in split_and(w.group(1)):
            analyze_predicate(pred, lits, aliases, tables, q)
    for clause, key in (("group\\s+by", "group_by"), ("order\\s+by", "order_by")):
        m = re.search(rf"\b{clause}\b(.*?)(?=\b(?:having|order\s+by|limit|offset|for\s+update)\b|$)", outer, re.I)
        if m:
            for item in split_top(m.group(1), r","):
                mm = re.match(rf"^{COL}\s*(asc|desc)?(?:\s+nulls\s+(?:first|last))?$", item.strip(), re.I)
                if mm:
                    t, c = resolve(mm.group(1), aliases, tables)
                    q[key].append({"table": t, "column": c, "dir": (mm.group(2) or "asc").lower()})
                else:
                    q["notes"].append(f"{key.replace('_', ' ')} expression '{item.strip()[:40]}' cannot use a plain column index")
    q["limit"] = bool(re.search(r"\b(limit|fetch\s+first)\b", outer, re.I))
    if select_list:
        if re.search(r"(^|,)\s*(\w+\.)?\*\s*(,|$)", select_list):
            q["select_star"] = True
        for ref in re.findall(rf"\b{COL}\b(?!\s*\()", select_list, re.I):
            if ref.lower() in KEYWORDS or ref.startswith("__") or re.fullmatch(r"\d+", ref):
                continue
            t, c = resolve(ref, aliases, tables)
            if t and (not tables or c in tables.get(t, {}).get("columns", [c])):
                q["selected"].setdefault(t, [])
                if c not in q["selected"][t]:
                    q["selected"][t].append(c)
    for f in q["filters"]:
        if f["table"] is None:
            q["notes"].append(f"column '{f['column']}' could belong to several tables; qualify it with an alias")
    return q


# ---------------------------------------------------------------- candidates

def candidates_for(q, dialect, covering, pk_cols=None):
    out = []
    by_table = {}
    for f in q["filters"]:
        if f["table"]:
            by_table.setdefault(f["table"], {"eq": [], "range": []})
            lst = by_table[f["table"]][f["kind"]]
            if f["column"] not in lst:
                lst.append(f["column"])
    tables_in_q = set(q["aliases"].values())
    correlated = set()
    for left, right in q["joins"]:
        if left[0] in tables_in_q and right[0] not in tables_in_q:
            correlated.add(left[0])
        if right[0] in tables_in_q and left[0] not in tables_in_q:
            correlated.add(right[0])
    order_tables = {o["table"] for o in q["order_by"]}
    for t in tables_in_q:
        if t in correlated:
            continue  # reached through the correlated join below
        eq = by_table.get(t, {}).get("eq", [])
        rng = [c for c in by_table.get(t, {}).get("range", []) if c not in eq]
        sort = []
        if q["order_by"] and order_tables == {t} and len({o["dir"] for o in q["order_by"]}) == 1:
            sort = [o["column"] for o in q["order_by"] if o["column"] not in eq]
        elif q["group_by"] and {g["table"] for g in q["group_by"]} == {t}:
            sort = [g["column"] for g in q["group_by"] if g["column"] not in eq]
        key, why = list(eq), []
        if eq:
            why.append(f"equality on {', '.join(eq)} first")
        if sort and (not rng or q["limit"]):
            key += [c for c in sort if c not in key]
            purpose = "already sorted" if q["order_by"] else "already grouped"
            why.append(f"then {', '.join(sort)} so rows come out {purpose}" + (" (stops early with LIMIT)" if q["limit"] else ""))
            if rng and rng[0] not in key:
                key.append(rng[0])
                why.append(f"{rng[0]} last as an in-index filter")
        elif rng:
            key.append(rng[0])
            why.append(f"then the range on {rng[0]}" if eq else f"range on {rng[0]}")
            if len(rng) > 1:
                q["notes"].append(f"several range filters on {t} ({', '.join(rng)}): only the first can seek; the rest filter")
            if sort:
                q["notes"].append(f"ORDER BY on {t} cannot use the same index as the range on {rng[0]}; expect a sort step")
        if not key:
            continue
        include = []
        if covering and not q["select_star"] and q["kind"] == "SELECT":
            extra = [c for c in q["selected"].get(t, []) if c not in key
                     and not (dialect == "mysql" and c in (pk_cols or {}).get(t, []))]
            if 0 < len(extra) <= 3:
                include = extra
        out.append({"table": t, "columns": key, "include": include, "why": "; ".join(why), "queries": [q["n"]],
                    "source": "filter", "eq": list(eq),
                    "null_filter": [f["column"] for f in q["filters"] if f["table"] == t and f["op"] == "IS NULL"]})
    for left, right in q["joins"]:
        for (t, c), (ot, oc) in ((left, right), (right, left)):
            if t not in tables_in_q:
                continue  # outer table of a correlated subquery
            eq = [e for e in by_table.get(t, {}).get("eq", []) if e != c]
            rng = [r for r in by_table.get(t, {}).get("range", []) if r != c and r not in eq][:1]
            why = f"join column {t}.{c} = {ot}.{oc} first (lets the planner look up matching rows)"
            if eq or rng:
                why += f", then {', '.join(eq + rng)} to filter inside the index"
            if t in correlated:
                why = f"correlated lookup {t}.{c} = outer {ot}.{oc} first" + (f", then {', '.join(eq + rng)}" if eq or rng else "")
            out.append({"table": t, "columns": [c] + eq + rng, "include": [], "queries": [q["n"]], "source": "join",
                        "eq": [c] + eq, "why": why})
    return out


def served_by(cand, indexes):
    for ix in indexes:
        if ix["table"] != cand["table"]:
            continue
        cols = ix["columns"]
        if cand["source"] == "join" and ix["unique"] and cols == cand["columns"][:1] and not ix.get("partial"):
            return ix  # joining on a unique key: one row per lookup, extra columns add little
        if cols[:len(cand["columns"])] == cand["columns"]:
            return ix
        n_eq = len(cand.get("eq", []))
        if n_eq > 1 and len(cols) >= len(cand["columns"]) and set(cols[:n_eq]) == set(cand["columns"][:n_eq]) \
                and cols[n_eq:len(cand["columns"])] == cand["columns"][n_eq:]:
            return ix  # same equality columns in another order serve the query equally well
    return None


def served_by_named(c, indexes):
    ix = served_by(c, indexes)
    return {"existing": ix["name"] if ix else "?"}


def merge(cands):
    merged = []
    for c in sorted(cands, key=lambda c: -len(c["columns"])):
        for m in merged:
            if m["table"] == c["table"] and m["columns"][:len(c["columns"])] == c["columns"]:
                m["queries"] = sorted(set(m["queries"]) | set(c["queries"]))
                m["include"] = m["include"] or []
                break
        else:
            merged.append(dict(c))
    return sorted(merged, key=lambda c: (c["table"], c["columns"]))


def schema_findings(indexes, max_indexes, new_by_table):
    out = []
    by_table = {}
    for ix in indexes:
        by_table.setdefault(ix["table"], []).append(ix)
    for t, ixs in by_table.items():
        for i, a in enumerate(ixs):
            for b in ixs[i + 1:]:
                if a["columns"] == b["columns"] and (a.get("partial") == b.get("partial")):
                    out.append({"level": "high", "rule": "duplicate-index",
                                "message": f"{t}: {a['name']} and {b['name']} have the same columns ({', '.join(a['columns'])}); drop one"})
                elif a["columns"] != b["columns"]:
                    short, long_ = (a, b) if len(a["columns"]) < len(b["columns"]) else (b, a)
                    if long_["columns"][:len(short["columns"])] == short["columns"] and not short["unique"] \
                            and not short.get("partial"):
                        out.append({"level": "warn", "rule": "redundant-prefix",
                                    "message": f"{t}: {short['name']} ({', '.join(short['columns'])}) is a leftmost prefix of "
                                               f"{long_['name']} ({', '.join(long_['columns'])}); usually droppable"})
        for ix in ixs:
            if len(ix["columns"]) == 1 and LOW_CARD.search(ix["columns"][0]) and ix["kind"] == "index" and not ix.get("partial"):
                out.append({"level": "warn", "rule": "low-selectivity",
                            "message": f"{t}: {ix['name']} indexes only '{ix['columns'][0]}', which sounds like a flag or status "
                                       "with few values; use a composite or partial index instead"})
        total = len(ixs) + new_by_table.get(t, 0)
        if total > max_indexes:
            out.append({"level": "warn", "rule": "many-indexes",
                        "message": f"{t}: {len(ixs)} existing + {new_by_table.get(t, 0)} suggested = {total} indexes "
                                   f"(limit {max_indexes}); every insert and update maintains all of them"})
    return out


def ddl(c, dialect):
    name = f"idx_{c['table']}_{'_'.join(c['columns'])}"[:63]
    cols = ", ".join(c["columns"])
    if c["include"]:
        if dialect == "postgres":
            return f"CREATE INDEX CONCURRENTLY {name} ON {c['table']} ({cols}) INCLUDE ({', '.join(c['include'])});"
        cols += ", " + ", ".join(c["include"])
    if dialect == "postgres":
        return f"CREATE INDEX CONCURRENTLY {name} ON {c['table']} ({cols});"
    if dialect == "mysql":
        return f"ALTER TABLE {c['table']} ADD INDEX {name} ({cols}), ALGORITHM=INPLACE, LOCK=NONE;"
    return f"CREATE INDEX {name} ON {c['table']} ({cols});"


# ---------------------------------------------------------------- main

def parse_args(argv):
    opts = {"schema": None, "files": [], "dialect": "postgres", "max_indexes": 6, "covering": True,
            "json": False, "strict": False}
    i = 0
    while i < len(argv):
        a = argv[i]
        if a in ("-h", "--help"):
            print(__doc__)
            sys.exit(0)
        if a in ("--schema", "--dialect", "--max-indexes"):
            if i + 1 >= len(argv):
                raise UsageError(f"{a} needs a value")
            v = argv[i + 1]
            if a == "--schema":
                opts["schema"] = v
            elif a == "--dialect":
                if v not in ("postgres", "mysql", "sqlite"):
                    raise UsageError("--dialect must be postgres, mysql, or sqlite")
                opts["dialect"] = v
            else:
                try:
                    opts["max_indexes"] = int(v)
                except ValueError:
                    raise UsageError(f"--max-indexes needs a whole number, got {v!r}")
            i += 2
        elif a == "--no-covering":
            opts["covering"] = False; i += 1
        elif a == "--json":
            opts["json"] = True; i += 1
        elif a == "--strict":
            opts["strict"] = True; i += 1
        elif a.startswith("--"):
            raise UsageError(f"unknown option {a}")
        else:
            opts["files"].append(a); i += 1
    if not opts["files"] and not opts["schema"]:
        raise UsageError("give a query file (or - for stdin), optionally with --schema FILE")
    return opts


def read(path):
    if path == "-":
        return sys.stdin.read()
    with open(path, encoding="utf-8") as f:
        return f.read()


def main(argv):
    try:
        opts = parse_args(argv)
        schema_text = read(opts["schema"]) if opts["schema"] else ""
        query_texts = [read(p) for p in opts["files"]]
    except UsageError as e:
        print(f"error: {e}", file=sys.stderr)
        return 2
    except OSError as e:
        print(f"error: cannot read {e.filename}: {e.strerror}", file=sys.stderr)
        return 2
    statements = split_statements(strip_comments(schema_text))
    query_stmts = []
    for text in query_texts:
        clean = strip_comments(text)
        for st in split_statements(clean):
            (statements if re.match(r"\s*create\s", st, re.I) else query_stmts).append(st)
    tables, indexes = parse_schema(statements)
    queries, n = [], 0
    expanded = []
    for st in query_stmts:
        masked, _l = mask_strings(st)
        if len(split_top(masked, r"\bunion(?:\s+all)?\b")) > 1:  # a real top-level UNION, not one inside a string
            expanded.extend(split_top(st, r"\bunion(?:\s+all)?\b"))  # each branch is analyzed as its own query
        else:
            expanded.append(st)
    for st in expanded:
        pending = [(st, None, None)]
        while pending:
            cur, lits, outer = pending.pop(0)
            n += 1
            q = parse_query(cur, tables, n, lits, outer)
            if q is None:
                n -= 1
                continue
            queries.append(q)
            for sub in q["subqueries"]:
                pending.append((sub, q["lits"], {**(outer or {}), **q["aliases"]}))
                q["notes"].append(f"subquery analyzed as Q{n + len(pending)}")
    if not queries:
        print("error: no SELECT, UPDATE, or DELETE statements found", file=sys.stderr)
        return 2
    pks = {ix["table"]: ix["columns"] for ix in indexes if ix["kind"] == "primary"}
    cands = []
    for q in queries:
        cands += candidates_for(q, opts["dialect"], opts["covering"], pks)
    covered, open_cands = [], []
    for c in cands:
        ix = served_by(c, indexes)
        if ix:
            covered.append({**c, "existing": ix["name"]})
        else:
            open_cands.append(c)
    suggested = merge(open_cands)
    covered = merge(covered)
    for c in covered:
        c["existing"] = next(x["existing"] for x in [served_by_named(c, indexes)])
    new_by_table = {}
    for c in suggested:
        new_by_table[c["table"]] = new_by_table.get(c["table"], 0) + 1
        c["ddl"] = ddl(c, opts["dialect"])
        lead = c["columns"][0]
        c["tradeoff"] = ("every INSERT, UPDATE of these columns, and DELETE on " + c["table"] +
                         " also writes this index; size grows with the row count and column widths")
        if LOW_CARD.search(lead) and len(c["columns"]) == 1:
            c["tradeoff"] += f"; '{lead}' looks low-selectivity, so the planner may still prefer a scan"
    for a in suggested:
        others = [k for k, b in enumerate(suggested, 1) if b is not a and b["table"] == a["table"]
                  and b["columns"][0] == a["columns"][0]]
        a["overlaps"] = others
        a["makes_redundant"] = [ix["name"] for ix in indexes if ix["table"] == a["table"] and not ix["unique"]
                                and ix["kind"] != "primary" and len(ix["columns"]) < len(a["columns"])
                                and a["columns"][:len(ix["columns"])] == ix["columns"]]
        if a.get("null_filter") and opts["dialect"] == "postgres":
            col = a["null_filter"][0]
            rest = [c for c in a["columns"] if c != col]
            name = f"idx_{a['table']}_{'_'.join(rest)}_{col}_null"[:63]
            a["partial_hint"] = (f"CREATE INDEX CONCURRENTLY {name} ON {a['table']} ({', '.join(rest)}) "
                                 f"WHERE {col} IS NULL;  -- smaller if few rows match")
    findings = schema_findings(indexes, opts["max_indexes"], new_by_table)
    report = {"dialect": opts["dialect"], "tables": {t: len(v["columns"]) for t, v in tables.items()},
              "existing_indexes": indexes, "queries": [{k: v for k, v in q.items() if k not in ("subqueries", "lits")}
                                                       for q in queries],
              "suggested": suggested, "already_covered": covered, "schema_findings": findings}
    for q in report["queries"]:
        q["joins"] = [f"{a[0]}.{a[1]} = {b[0]}.{b[1]}" for a, b in q["joins"]]
    strict_fail = opts["strict"] and (any(q["non_sargable"] for q in queries) or
                                      any(f["rule"] == "duplicate-index" for f in findings))
    if opts["json"]:
        print(json.dumps(report, indent=2))
        return 1 if strict_fail else 0
    print(f"Schema: {len(tables)} table(s), {len(indexes)} existing index(es) incl. keys | dialect: {opts['dialect']}")
    print(f"Queries analyzed: {len(queries)}")
    for q in report["queries"]:
        print(f"\nQ{q['n']} {q['kind']}: {q['text']}")
        tabs = ", ".join(f"{t} {a}" if a != t else t for a, t in q["aliases"].items())
        print(f"  tables: {tabs or '-'}")
        eqs = [f"{f['table'] or '?'}.{f['column']} ({f['op']})" for f in q["filters"] if f["kind"] == "eq"]
        rngs = [f"{f['table'] or '?'}.{f['column']} ({f['op']})" for f in q["filters"] if f["kind"] == "range"]
        if eqs:
            print(f"  equality: {', '.join(eqs)}")
        if rngs:
            print(f"  range: {', '.join(rngs)}")
        if q["joins"]:
            print(f"  joins: {', '.join(q['joins'])}")
        if q["order_by"]:
            print("  order by: " + ", ".join(f"{o['table']}.{o['column']} {o['dir'].upper()}" for o in q["order_by"])
                  + ("  (LIMIT)" if q["limit"] else ""))
        if q["group_by"]:
            print("  group by: " + ", ".join(f"{g['table']}.{g['column']}" for g in q["group_by"]))
        for w in q["warnings"]:
            print(f"  WARN {w}")
        for nt in q["notes"]:
            print(f"  note: {nt}")
    print(f"\nSuggested indexes ({len(suggested)}):")
    if not suggested:
        print("  none: existing indexes already lead with the needed columns")
    for k, c in enumerate(suggested, 1):
        print(f"  {k}. {c['ddl']}")
        print(f"     serves: {', '.join('Q' + str(x) for x in c['queries'])} | why: {c['why']}")
        if c["include"]:
            print(f"     covering: {', '.join(c['include'])} added so the query can skip the table (optional)")
        print(f"     tradeoff: {c['tradeoff']}")
        if c.get("overlaps"):
            print(f"     overlap: same leading column as #{', #'.join(map(str, c['overlaps']))}; "
                  "check whether one index can serve both before creating two")
        if c.get("makes_redundant"):
            print(f"     makes redundant: {', '.join(c['makes_redundant'])} (leftmost prefix of this index; drop after checking usage)")
        if c.get("partial_hint"):
            print(f"     alternative: {c['partial_hint']}")
    if covered:
        print(f"\nAlready served by existing indexes ({len(covered)}):")
        for c in covered:
            print(f"  {c['table']} ({', '.join(c['columns'])}) -> {c['existing']} (Q{', Q'.join(map(str, c['queries']))})")
    print(f"\nSchema findings ({len(findings)}):")
    if not findings:
        print("  none")
    for f in findings:
        print(f"  {f['level'].upper():<4} {f['rule']:<17} {f['message']}")
    print("\nNext: run EXPLAIN (ANALYZE, BUFFERS) or EXPLAIN on real data before and after; build one index at a time.")
    return 1 if strict_fail else 0


if __name__ == "__main__":
    sys.exit(main(sys.argv[1:]))

Python SQL Database+2

F@f

SQL Query Performance Reviewer

Skill

Reviews SQL queries and optional EXPLAIN output for common performance problems such as SELECT *, functions or casts on indexed columns, leading-wildcard LIKE, N+1 query loops, OR across columns vs UNION ALL, AND/OR precedence slips, NOT IN subqueries, deep OFFSET paging, seq scans and disk sorts. Explains each problem and gives a rewritten query. Includes a stdlib Python checker with --self-test.

---
name: sql-query-performance-reviewer
description: Reviews SQL queries and optional EXPLAIN output for common performance problems such as SELECT *, functions or casts on indexed columns, leading-wildcard LIKE, N+1 query loops, OR across columns vs UNION ALL, AND/OR precedence slips, NOT IN subqueries, deep OFFSET paging, seq scans and disk sorts. Explains each problem and gives a rewritten query. Includes a stdlib Python checker with --self-test.
---

# SQL Query Performance Reviewer

You review SQL the way an experienced database engineer does in a code review: you read the query and its plan, find the patterns that make it slow (or quietly wrong), and hand back a rewritten query with a short reason for every change. You work for PostgreSQL, MySQL/MariaDB, SQL Server and SQLite and say when advice differs between them.

## Files in this skill

- `scripts/sql_review.py` - scans a .sql file (one or many statements) and optional EXPLAIN output, prints findings by severity (Python 3 standard library only; never connects to a database or runs the SQL)
- `references/anti-patterns.md` - each finding with why it hurts, a before/after rewrite and engine differences
- `references/reading-explain.md` - how to capture and read EXPLAIN in PostgreSQL and MySQL, and which numbers matter
- `templates/review-report.md` - the review you hand back: summary, findings table, rewritten queries, what to measure
- `examples/order-history.sql`, `examples/order-history-explain.txt`, `examples/indexes.txt` - an online shop's order history page with 7 statements, a PostgreSQL plan and the known indexes

## Workflow

### 1. Collect the inputs
Ask for: the query or a log excerpt with the real statements, the database engine and version, rough table sizes, and if possible `EXPLAIN (ANALYZE, BUFFERS)` (PostgreSQL) or `EXPLAIN ANALYZE` / `EXPLAIN ... \G` (MySQL 8) output. Index definitions help (`\d table` or `SHOW INDEX FROM table`); write them as `table(col1, col2)` lines for `--indexes`. Ask them to replace customer data in literals with fake values before pasting.

### 2. Run the checker
```
python3 scripts/sql_review.py examples/order-history.sql --explain examples/order-history-explain.txt --indexes examples/indexes.txt
python3 scripts/sql_review.py slow.sql --json
python3 scripts/sql_review.py --self-test
```
Exit code 1 means HIGH findings, 2 means bad input. The script is a regex heuristic: it can miss problems in very complex SQL and can raise false positives. Read every statement yourself as well.

### 3. Review
Order the work: correctness first (AND_OR_PRECEDENCE, NOT_IN_SUBQUERY, NO_WHERE_WRITE), then the biggest cost from the plan (SEQ_SCAN_LARGE, ROWS_REMOVED, DISK_SORT), then N_PLUS_ONE (often the largest total time in an app), then the rest. For each finding give the rewrite from `references/anti-patterns.md` adapted to the user's columns. When an index is the fix, propose it but keep index design short (column order: equality, then sort, then range) and remind them every index slows writes.

### 4. Deliver
Fill `templates/review-report.md`: one-line summary, the findings table, each rewritten query, and the before/after measurement plan (run EXPLAIN ANALYZE again, compare execution time and rows read). Never claim a speed-up you did not measure; say "expected".

## Example result
For the example files: 7 statements, 6 HIGH, 7 MEDIUM, 2 LOW. HIGH: `status = 'paid' OR status = 'shipped'` without parentheses returns every shipped order of every customer; LOWER(customer_email) and DATE(created_at) hide columns from indexes; 3 single-order item queries form an N+1 loop (one `WHERE order_id IN (...)` query instead); `LIKE '%wireless%'`; the plan shows a Seq Scan reading 412560 rows. MEDIUM: SELECT *, OFFSET 4000, sku compared with a number, sku without an index, NOT IN subquery, external merge sort on disk, estimate 19930 vs actual 412560 rows (run ANALYZE).

## Rules

- Never run SQL against the user's database yourself and never ask for production credentials.
- Ask for anonymized literals; do not repeat personal data from pasted queries in the report.
- Correctness beats speed: point out queries that return wrong rows even if they are fast.
- Prefer rewriting the query over adding an index; add an index only for a query that runs often or is slow.
- Name the engine when advice is engine-specific (e.g. expression indexes, ILIKE, LIMIT a,b).
- Measured plans beat heuristics: if EXPLAIN ANALYZE disagrees with a finding, trust the plan.
FILE:references/anti-patterns.md
# SQL Performance Anti-Patterns

Each section: what the checker flags, why it hurts, and a rewrite. "Sargable" means the database can use an index to find the rows (Search ARGument ABLE).

## AND_OR_PRECEDENCE (HIGH, correctness)
AND binds tighter than OR, so `customer_id = 7 AND status = 'paid' OR status = 'shipped'` means `(customer_id = 7 AND status = 'paid') OR status = 'shipped'`: every shipped order of every customer, and a full scan.
```
-- after
WHERE customer_id = 7 AND status IN ('paid', 'shipped')
```

## FUNC_ON_COLUMN (HIGH)
`WHERE LOWER(email) = ?`, `YEAR(created_at) = 2026`, `DATE(created_at) = ?`, `price * 2 > 100`: the index stores raw values, so the function must run on every row.
```
-- dates: use a half-open range on the raw column
WHERE created_at >= '2026-10-01' AND created_at < '2026-10-02'
-- arithmetic: move it to the constant side
WHERE price > 50
-- case-insensitive search: store normalized data or index the expression
CREATE INDEX ON customers (LOWER(email));          -- PostgreSQL, SQLite 3.9+
ALTER TABLE customers ADD email_lc VARCHAR(255) AS (LOWER(email)) STORED, ADD INDEX (email_lc);  -- MySQL 5.7+
-- MySQL 8.0.13+ also supports functional indexes: CREATE INDEX ix ON customers ((LOWER(email)));
-- PostgreSQL alternative: the citext type
```

## LEADING_WILDCARD (HIGH)
`LIKE '%wireless%'` cannot use a B-tree index (only a prefix like `'wireless%'` can). Options: full-text search (PostgreSQL `tsvector` + GIN, MySQL `FULLTEXT` + `MATCH ... AGAINST`), a trigram index (`CREATE EXTENSION pg_trgm; CREATE INDEX ... USING gin (name gin_trgm_ops)`), a search engine for product search, or a reversed column for suffix searches (`'%@example.com'`).

## N_PLUS_ONE (HIGH)
One query for a list, then one query per row (an ORM lazy-loading a relation). 50 orders = 51 round trips; each is fast, the page is slow.
```
-- before (x N)
SELECT product_id, qty FROM order_items WHERE order_id = 98211;
-- after (1 query)
SELECT order_id, product_id, qty FROM order_items WHERE order_id IN (98211, 98340, 98502);
-- or join it into the list query
```
ORM fixes: Django `prefetch_related`/`select_related`, Rails `includes`, SQLAlchemy `selectinload`, Hibernate `JOIN FETCH` / `@BatchSize`, Prisma `include`. The checker needs the repeated statements in the file (paste a log excerpt).

## ORDER_BY_RANDOM (HIGH)
`ORDER BY RAND() LIMIT 1` reads and sorts the whole table. Pick a random id in range and take the next row (`WHERE id >= ? ORDER BY id LIMIT 1`), keep a random-number column with an index, or use `TABLESAMPLE SYSTEM (1)` in PostgreSQL.

## NO_WHERE_WRITE (HIGH)
`UPDATE`/`DELETE` without WHERE locks and rewrites every row. If that is intended, do it in batches (`DELETE ... WHERE id IN (SELECT id ... LIMIT 5000)` in a loop) or use `TRUNCATE`.

## SELECT_STAR (MEDIUM)
Reads every column (including large text/JSON), sends more bytes, prevents index-only scans and breaks callers when columns change. List the columns. `SELECT *` inside `EXISTS (...)` is fine.

## OR_ACROSS_COLUMNS (MEDIUM) and OR_SAME_COLUMN (LOW)
`WHERE email = ? OR phone = ?`: one index cannot serve both. PostgreSQL may combine two indexes with a BitmapOr and MySQL with index_merge; if the plan shows a scan instead, split:
```
SELECT id FROM users WHERE email = ?
UNION ALL
SELECT id FROM users WHERE phone = ? AND email IS DISTINCT FROM ?;  -- avoid duplicates (MySQL: NOT (email <=> ?))
```
Same column repeated: `status IN ('paid', 'shipped')` is shorter and plans the same or better.

## IMPLICIT_CAST (MEDIUM) and QUOTED_NUMBER (LOW)
A text column compared with a number (`sku = 40017` on a VARCHAR): MySQL converts every row's value to a number, so the index is skipped and '040017' also matches; PostgreSQL raises an operator error. Quote the value. A numeric column compared with a quoted number (`customer_id = '1042'`) is converted once and is usually fine, but it often means the application binds ids as strings; a parameter typed as text can force a cast on the column side in some drivers (JDBC `setString` on SQL Server NVARCHAR vs VARCHAR is the classic case).

## NOT_IN_SUBQUERY (MEDIUM, correctness)
If the subquery returns a single NULL, `NOT IN` returns no rows at all. Use:
```
SELECT c.id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
```
NOT EXISTS plans as an anti-join in every major engine.

## DEEP_OFFSET (MEDIUM)
`LIMIT 20 OFFSET 4000` reads 4020 rows and throws away 4000; page 500 is 25x slower than page 1. Keyset (seek) pagination:
```
SELECT id, created_at, total FROM orders
WHERE customer_id = ? AND (created_at, id) < (?, ?)      -- last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;
```
Index `(customer_id, created_at, id)`. MySQL supports row comparisons too; SQL Server: `created_at < ? OR (created_at = ? AND id < ?)`.

## COUNT_FOR_EXISTS (LOW)
`SELECT COUNT(*) ... > 0` counts all matches. `SELECT EXISTS (SELECT 1 FROM ... WHERE ...)` stops at the first.

## DISTINCT_JOIN (LOW)
`SELECT DISTINCT u.* FROM users u JOIN orders o ...` builds all user x order rows then removes duplicates. Use `WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)`.

## NO_INDEX_FOR_FILTER (MEDIUM, needs --indexes)
A column used in WHERE/ON is not the first column of any listed index. Not every filter needs an index (small tables, low-selectivity flags, rare queries). Composite index order: equality columns first, then the ORDER BY column, then range columns; include selected columns for an index-only scan only when the query is hot.
FILE:references/reading-explain.md
# Reading EXPLAIN

## Capture
| Engine | Command | Notes |
|---|---|---|
| PostgreSQL | `EXPLAIN (ANALYZE, BUFFERS) <query>;` | ANALYZE runs the query; wrap writes in `BEGIN; ... ROLLBACK;` |
| MySQL 8 | `EXPLAIN ANALYZE <query>;` or `EXPLAIN <query>\G` | The checker parses the `\G` vertical format |
| MariaDB | `ANALYZE <query>;` / `EXPLAIN <query>\G` | |
| SQL Server | `SET STATISTICS IO, TIME ON;` + actual execution plan | Read manually |
| SQLite | `EXPLAIN QUERY PLAN <query>;` | Look for `SCAN table` vs `SEARCH table USING INDEX` |

Run it on production-like data. A plan on a 1000-row dev table tells you nothing about a 50-million-row table.

## What the checker looks for
| Finding | PostgreSQL sign | MySQL sign |
|---|---|---|
| SEQ_SCAN_LARGE | `Seq Scan on t` with actual rows >= 10000 | `type: ALL`, `rows` >= 10000, `key: NULL` |
| ROWS_REMOVED | `Rows Removed by Filter` >= 9x the rows kept | `filtered` <= 10% on >= 1000 rows |
| EST_MISMATCH | `rows=` estimate vs `actual ... rows=` off by 10x | (EXPLAIN ANALYZE only, read manually) |
| DISK_SORT | `Sort Method: external merge  Disk: ...` | `Using temporary; Using filesort` |
| NESTED_LOOP_MANY | inner node with `loops=` >= 1000 | many rows in the driving table + `ref` lookups |

## Reading a PostgreSQL plan
- Read from the most indented node up; each `->` feeds its parent.
- `cost=a..b` is the planner's guess (arbitrary units), `actual time=a..b` is milliseconds per loop; multiply by `loops`.
- `rows=` in the cost part is the estimate, in the actual part the real count. A 10x miss means stale or missing statistics: `ANALYZE table;`, or extended statistics for correlated columns (`CREATE STATISTICS`).
- `Index Scan` / `Index Only Scan` / `Bitmap Index Scan` = index used. `Heap Fetches` high on an index-only scan = run VACUUM.
- `Buffers: shared read=` counts 8 kB pages from disk; `hit=` from cache.
- `Sort Method: external merge` = the sort did not fit in `work_mem`. Prefer an index that returns rows in order; raise work_mem per session only for known reporting queries.

## Reading MySQL EXPLAIN
- `type` from best to worst: `const`, `eq_ref`, `ref`, `range`, `index` (full index scan), `ALL` (full table scan).
- `key` is the index chosen (`NULL` = none); `possible_keys` lists candidates.
- `rows` x `filtered`% = rows passed on to the next step.
- `Extra`: `Using index` (covering, good), `Using where` (filter after reading), `Using temporary` and `Using filesort` (extra work for GROUP BY / ORDER BY).

## Before/after measurement
1. Run the original 3 times, record median execution time and rows/buffers read.
2. Apply one change at a time (rewrite, then index).
3. Run again with the same parameters; also test a "big" customer and a "small" one, because plans change with data skew.
4. Check write cost for new indexes on hot tables (insert latency, index size with `pg_relation_size` or `information_schema.STATISTICS`).
FILE:templates/review-report.md
# SQL performance review: <query or endpoint name>

**Engine:** <PostgreSQL 16 / MySQL 8.0 / ...>   **Tables:** <orders ~1.2M rows, order_items ~4M>
**Summary:** <one sentence: the main problem and the expected effect of the fix>

## Findings
| # | Severity | Finding | Statement | Why it matters | Fix |
|---|---|---|---|---|---|
| 1 | HIGH | <AND_OR_PRECEDENCE> | <stmt 1> | <returns all shipped orders, full scan> | <parenthesize / IN (...)> |
| 2 | <MEDIUM> | <...> | <...> | <...> | <...> |

## Rewritten queries
### Statement <n>: <name>
Before:
```sql
<original>
```
After:
```sql
<rewrite>
```
Why: <one or two sentences>

## Index changes (only if needed)
```sql
<CREATE INDEX CONCURRENTLY ix_orders_customer_created ON orders (customer_id, created_at DESC, id);>
```
Write cost: <table write rate, expected index size>

## Measure
| Query | Before (ms, rows read) | After (ms, rows read) |
|---|---|---|
| <stmt 1> | <912 ms, 1.2M rows> | <expected: < 5 ms, ~20 rows> |

Run `EXPLAIN (ANALYZE, BUFFERS)` again after each change, with a large and a small customer.

## Not changed
<findings deliberately left, e.g. SELECT * in an admin export, with the reason>
FILE:examples/order-history.sql
-- Order history page of a small online shop (PostgreSQL), captured from the app log.
-- 1. Order list for the account page
SELECT * FROM orders o
WHERE LOWER(o.customer_email) = 'mina.sato@example.com'
  AND o.status = 'paid' OR o.status = 'shipped'
ORDER BY o.created_at DESC
LIMIT 20 OFFSET 4000;

-- 2-4. Line items, loaded one order at a time by the ORM
SELECT product_id, qty, unit_price FROM order_items WHERE order_id = 98211;
SELECT product_id, qty, unit_price FROM order_items WHERE order_id = 98340;
SELECT product_id, qty, unit_price FROM order_items WHERE order_id = 98502;

-- 5. Search box
SELECT p.id, p.name FROM products p
WHERE p.name LIKE '%wireless%' AND p.sku = 40017;

-- 6. Customers who never ordered
SELECT c.id, c.email FROM customers c
WHERE c.id NOT IN (SELECT customer_id FROM orders)
  AND DATE(c.created_at) = '2026-10-01';

-- 7. Order lookup by id from a URL parameter
SELECT o.id, o.total FROM orders o WHERE o.customer_id = '1042';
FILE:examples/order-history-explain.txt
Limit  (cost=48211.30..48211.35 rows=20 width=212) (actual time=912.4..912.5 rows=20 loops=1)
  ->  Sort  (cost=48201.30..48251.12 rows=19930 width=212) (actual time=905.1..911.8 rows=4020 loops=1)
        Sort Key: created_at DESC
        Sort Method: external merge  Disk: 18432kB
        ->  Seq Scan on orders o  (cost=0.00..41870.00 rows=19930 width=212) (actual time=0.03..801.7 rows=412560 loops=1)
              Filter: (((lower(customer_email) = 'mina.sato@example.com'::text) AND (status = 'paid'::text)) OR (status = 'shipped'::text))
              Rows Removed by Filter: 787440
Planning Time: 0.4 ms
Execution Time: 914.9 ms
FILE:examples/indexes.txt
orders(customer_id, created_at)
orders(status)
order_items(order_id)
customers(id)
products(id)
FILE:scripts/sql_review.py
#!/usr/bin/env python3
"""SQL query performance reviewer (Python 3 standard library only).

Reads a .sql file (one or more statements separated by ;) and, optionally, EXPLAIN output,
and flags common performance problems with a heuristic, regex-based scan. It does not connect
to a database and never runs the SQL. Treat findings as review prompts, not proof.

Query findings (severity):
  HIGH   FUNC_ON_COLUMN     function or arithmetic wraps a column in WHERE/ON (index cannot be used)
  HIGH   LEADING_WILDCARD   LIKE/ILIKE pattern starts with % or _ (full scan)
  HIGH   N_PLUS_ONE         the same statement shape repeats >= --repeat times with different literals
  HIGH   ORDER_BY_RANDOM    ORDER BY RAND()/RANDOM() sorts the whole table
  HIGH   NO_WHERE_WRITE     UPDATE or DELETE without WHERE
  HIGH   AND_OR_PRECEDENCE  AND and OR mixed without parentheses: AND binds first, so the OR branch
                            matches rows from the whole table (wrong results and a full scan)
  MEDIUM SELECT_STAR        SELECT * or alias.* (wide rows, no covering index, breaks on schema change)
  MEDIUM OR_ACROSS_COLUMNS  OR between different columns in WHERE (consider UNION ALL)
  MEDIUM IMPLICIT_CAST      code/phone/sku-like (text) column compared with a bare number: MySQL
                            casts every row and skips the index, PostgreSQL raises a type error
  MEDIUM NOT_IN_SUBQUERY    NOT IN (SELECT ...): NULL trap and poor anti-join plans; use NOT EXISTS
  MEDIUM DEEP_OFFSET        OFFSET (or LIMIT a,b) >= 1000: keyset pagination instead
  MEDIUM NO_INDEX_FOR_FILTER  (needs --indexes) filtered/joined column not leading any known index
  LOW    QUOTED_NUMBER      id/number column compared with a quoted number: usually fine for a literal,
                            but a sign the app binds ids as text (typed text parameters can force a cast)
  LOW    OR_SAME_COLUMN     col = a OR col = b: write col IN (a, b)
  LOW    COUNT_FOR_EXISTS   COUNT(*) > 0 used as an existence check; use EXISTS
  LOW    DISTINCT_JOIN      SELECT DISTINCT with JOIN may hide a join fan-out
EXPLAIN findings (PostgreSQL text format and MySQL EXPLAIN \\G vertical format):
  HIGH   SEQ_SCAN_LARGE     Seq Scan / type ALL over >= --large-rows rows
  MEDIUM ROWS_REMOVED       a filter throws away >= 90% of the rows it reads
  MEDIUM EST_MISMATCH       estimated vs actual rows differ by >= 10x (stale statistics)
  MEDIUM DISK_SORT          external merge sort / Using filesort with Using temporary
  LOW    NESTED_LOOP_MANY   inner side of a nested loop runs >= 1000 loops
Usage:
  python3 sql_review.py query.sql
  python3 sql_review.py query.sql --explain plan.txt --indexes indexes.txt --json
  python3 sql_review.py --self-test
--indexes file: one index per line, e.g. "orders(customer_id, created_at)".
Exit code: 0 no HIGH findings, 1 HIGH findings, 2 bad input.
"""
import argparse
import json
import re
import sys
from collections import defaultdict

SEV_ORDER = {"HIGH": 0, "MEDIUM": 1, "LOW": 2}
FUNCS = ("lower", "upper", "trim", "ltrim", "rtrim", "date", "year", "month", "day", "cast",
         "coalesce", "ifnull", "nvl", "substring", "substr", "left", "right", "to_char",
         "date_trunc", "extract", "date_format", "convert", "abs", "round", "concat", "length")
ID_COL = re.compile(r"(^|_)(id|number|count|qty|quantity|amount)$")
TEXT_COL = re.compile(r"(phone|zip|postal_code|postcode|sku|isbn|iban|code|_no)$")
KEYWORDS = {"where", "on", "and", "or", "not", "join", "left", "right", "inner", "outer", "full",
            "cross", "group", "order", "by", "limit", "offset", "having", "union", "select", "from",
            "as", "using", "set", "values", "into", "lateral", "natural", "straight_join", "with"}


class DataError(ValueError):
    pass


def mask(sql):
    """Remove comments, replace string literals with 'Sn' tokens; return (masked, literals)."""
    sql = re.sub(r"--[^\n]*", " ", sql)
    sql = re.sub(r"/\*.*?\*/", " ", sql, flags=re.S)
    lits = []

    def rep(m):
        lits.append(m.group(0)[1:-1].replace("''", "'"))
        return f"'S{len(lits) - 1}'"
    return re.sub(r"'(?:[^']|'')*'", rep, sql), lits


def split_statements(text):
    masked, lits = mask(text)
    out = []
    for part in masked.split(";"):
        s = " ".join(part.split())
        if s:
            out.append(s)
    return out, lits


def clause(stmt, start, enders):
    m = re.search(r"\b" + start + r"\b", stmt, re.I)
    if not m:
        return ""
    rest = stmt[m.end():]
    e = re.search(r"\b(" + "|".join(enders) + r")\b", rest, re.I)
    return rest[:e.start()] if e else rest


def where_part(stmt):
    return clause(stmt, "where", ["group\\s+by", "order\\s+by", "limit", "offset", "having",
                                  "union", "returning", "for\\s+update", "window"])


def join_on_parts(stmt):
    return re.findall(r"\bon\b(.*?)(?=\b(?:join|left|right|inner|full|cross|where|group|order|limit|union)\b|$)",
                      stmt, re.I)


def aliases(stmt):
    amap = {}
    for m in re.finditer(r"\b(?:from|join)\s+([a-z_][\w.]*)(?:\s+(?:as\s+)?([a-z_]\w*))?", stmt, re.I):
        table, alias = m.group(1).lower(), (m.group(2) or "").lower()
        if table == "select" or table.startswith("("):
            continue
        amap[table.split(".")[-1]] = table.split(".")[-1]
        if alias and alias not in KEYWORDS:
            amap[alias] = table.split(".")[-1]
    return amap


def shape(stmt):
    s = re.sub(r"'S\d+'", "?", stmt)
    s = re.sub(r"\b\d+(\.\d+)?\b", "?", s)
    s = re.sub(r"\(\s*\?(\s*,\s*\?)*\s*\)", "(?)", s)
    return s.lower()


def review_sql(text, repeat=3, indexes=None):
    stmts, lits = split_statements(text)
    if not stmts:
        raise DataError("no SQL statements found")
    findings = []

    def add(sev, code, n, msg):
        findings.append({"severity": sev, "code": code, "statement": n, "message": msg})

    shapes = defaultdict(list)
    for n, st in enumerate(stmts, 1):
        low = st.lower()
        kind = low.split()[0]
        shapes[shape(st)].append(n)
        where = where_part(st)
        ons = join_on_parts(st)
        filt = " AND ".join([where] + ons)
        if re.search(r"\bselect\s+(distinct\s+)?(\w+\.)?\*", low) or re.search(r",\s*\w+\.\*", low):
            add("MEDIUM", "SELECT_STAR", n, "SELECT * reads every column; list the columns you need so a covering index can serve the query")
        for m in re.finditer(r"\b(" + "|".join(FUNCS) + r")\s*\(\s*([a-z_][\w]*(?:\.[a-z_]\w*)?)\b[^()]*\)\s*(=|<>|!=|<=|>=|<|>|\bin\b|\blike\b|\bbetween\b)",
                             filt, re.I):
            col = m.group(2)
            if col.lower() in KEYWORDS or re.fullmatch(r"S\d+", col):
                continue
            add("HIGH", "FUNC_ON_COLUMN", n, f"{m.group(1)}({col}) in a filter hides {col} from its index; compare the bare column (e.g. a range on the raw value) or add an expression index")
        for m in re.finditer(r"([a-z_][\w.]*)\s*[+\-*/]\s*\d+\s*(=|<|>|<=|>=)", filt, re.I):
            add("HIGH", "FUNC_ON_COLUMN", n, f"arithmetic on {m.group(1)} in a filter; move it to the other side of the comparison")
        for m in re.finditer(r"([a-z_][\w.]*)\s+(not\s+)?(i?like)\s+'S(\d+)'", st, re.I):
            pat = lits[int(m.group(4))]
            if pat[:1] in ("%", "_"):
                add("HIGH", "LEADING_WILDCARD", n, f"{m.group(1)} {m.group(3).upper()} '{pat}' starts with a wildcard, so no B-tree index helps; use full-text search, a trigram index or a reversed column")
        if re.search(r"order\s+by\s+(rand|random|newid)\s*\(\s*\)", low):
            add("HIGH", "ORDER_BY_RANDOM", n, "ORDER BY random sorts every row; pick random ids or use TABLESAMPLE")
        if kind in ("update", "delete") and not where.strip():
            add("HIGH", "NO_WHERE_WRITE", n, f"{kind.upper()} without WHERE touches every row; add a WHERE clause or batch it")
        if where:
            top = where
            while re.search(r"\([^()]*\)", top):
                top = re.sub(r"\([^()]*\)", " _ ", top)
            top = re.sub(r"\bbetween\s+\S+\s+and\b", "between x", top, flags=re.I)
            if re.search(r"\band\b", top, re.I) and re.search(r"\bor\b", top, re.I):
                add("HIGH", "AND_OR_PRECEDENCE", n, "AND and OR are mixed without parentheses; AND binds first, so 'a AND b OR c' returns every row matching c; add parentheses: a AND (b OR c)")
            for group in re.split(r"\band\b", where, flags=re.I):
                if not re.search(r"\bor\b", group, re.I):
                    continue
                cols = re.findall(r"([a-z_][\w.]*)\s*(?:=|<>|!=|<|>|\bin\b|\blike\b)", group, re.I)
                cols = [c.lower() for c in cols if c.lower() not in KEYWORDS and not re.fullmatch(r"s\d+", c.lower())]
                if len(set(cols)) >= 2:
                    add("MEDIUM", "OR_ACROSS_COLUMNS", n, f"OR across {', '.join(sorted(set(cols)))}: one index cannot serve both sides; split into UNION ALL of indexed queries (or check the plan uses a bitmap OR)")
                elif len(cols) >= 2:
                    add("LOW", "OR_SAME_COLUMN", n, f"repeated OR on {cols[0]}; write {cols[0]} IN (...)")
        for m in re.finditer(r"([a-z_][\w.]*)\s*(=|<>|!=|<|>)\s*'S(\d+)'", filt, re.I):
            col = m.group(1).split(".")[-1].lower()
            if ID_COL.search(col) and re.fullmatch(r"-?\d+(\.\d+)?", lits[int(m.group(3))]):
                add("LOW", "QUOTED_NUMBER", n, f"{m.group(1)} compared with the string '{lits[int(m.group(3))]}'; bind it as a number so the driver never sends a text parameter")
        for m in re.finditer(r"([a-z_][\w.]*)\s*(=|<>|!=)\s*(\d{3,})\b", filt, re.I):
            col = m.group(1).split(".")[-1].lower()
            if TEXT_COL.search(col):
                add("MEDIUM", "IMPLICIT_CAST", n, f"{m.group(1)} looks like a text column but is compared with the number {m.group(3)}; MySQL then casts every row and skips the index (PostgreSQL raises a type error); quote the value")
        if re.search(r"\bnot\s+in\s*\(\s*select\b", low):
            add("MEDIUM", "NOT_IN_SUBQUERY", n, "NOT IN (SELECT ...) returns no rows if the subquery yields a NULL and often plans badly; use NOT EXISTS")
        m = re.search(r"\boffset\s+(\d+)", low) or re.search(r"\blimit\s+(\d+)\s*,\s*\d+", low)
        if m and int(m.group(1)) >= 1000:
            add("MEDIUM", "DEEP_OFFSET", n, f"OFFSET {m.group(1)} reads and throws away {m.group(1)} rows; use keyset pagination (WHERE (created_at, id) < (?, ?) ORDER BY ... LIMIT n)")
        if re.search(r"count\s*\(\s*(\*|1)\s*\)\s*(>\s*0|>=\s*1)", low):
            add("LOW", "COUNT_FOR_EXISTS", n, "COUNT(*) > 0 counts every match; EXISTS stops at the first")
        if re.search(r"\bselect\s+distinct\b", low) and re.search(r"\bjoin\b", low):
            add("LOW", "DISTINCT_JOIN", n, "DISTINCT on a join often hides duplicate rows from a one-to-many join; use EXISTS or aggregate first")
        if indexes is not None:
            amap = aliases(st)
            seen = set()
            for m in re.finditer(r"\b([a-z_]\w*)\.([a-z_]\w*)\s*(?:=|<|>|<=|>=|\bin\b|\bbetween\b)", filt, re.I):
                table = amap.get(m.group(1).lower())
                col = m.group(2).lower()
                if not table or (table, col) in seen:
                    continue
                seen.add((table, col))
                if table in indexes and col not in indexes[table]:
                    add("MEDIUM", "NO_INDEX_FOR_FILTER", n, f"{table}.{col} is filtered or joined but no listed index on {table} starts with it")
    for sh, ns in shapes.items():
        if len(ns) >= repeat and ("where" in sh):
            add("HIGH", "N_PLUS_ONE", ns[0], f"statements {', '.join(map(str, ns))} have the same shape with different values ({len(ns)}x); fetch them in one query with IN (...) or a JOIN")
    findings.sort(key=lambda f: (SEV_ORDER[f["severity"]], f["statement"], f["code"]))
    shown = [re.sub(r"'S(\d+)'", lambda m: "'" + lits[int(m.group(1))].replace("'", "''") + "'", st) for st in stmts]
    return shown, findings


def parse_indexes(text):
    idx = defaultdict(set)
    for line in text.splitlines():
        m = re.match(r"\s*([\w.]+)\s*\(\s*([\w\s,]+)\)", line)
        if m:
            idx[m.group(1).split(".")[-1].lower()].add(m.group(2).split(",")[0].strip().lower())
    return idx


def review_explain(text, large=10000):
    findings = []

    def add(sev, code, msg):
        findings.append({"severity": sev, "code": code, "statement": 0, "message": msg})
    lines = text.splitlines()
    if re.search(r"^\s*(\*+ \d+\. row|select_type:)", text, re.M):
        for block in re.split(r"\*+ \d+\. row \*+", text):
            f = dict(re.findall(r"^\s*(\w+):\s*(.*?)\s*$", block, re.M))
            if not f:
                continue
            rows = int(f.get("rows", "0") or 0)
            if f.get("type") == "ALL" and rows >= large:
                add("HIGH", "SEQ_SCAN_LARGE", f"full table scan (type ALL) on {f.get('table')} ~{rows} rows; key={f.get('key', 'NULL')}")
            extra = f.get("Extra", "")
            if "Using filesort" in extra and "Using temporary" in extra:
                add("MEDIUM", "DISK_SORT", f"{f.get('table')}: Using temporary; Using filesort - an index matching GROUP BY/ORDER BY avoids it")
            filtered = float(f.get("filtered", "100") or 100)
            if rows >= 1000 and filtered <= 10:
                add("MEDIUM", "ROWS_REMOVED", f"{f.get('table')}: only {filtered:g}% of {rows} rows pass the filter")
        return findings
    for i, line in enumerate(lines):
        m = re.search(r"(Parallel )?Seq Scan on (\S+).*?rows=(\d+)", line)
        if m:
            act = re.search(r"actual[^)]*rows=(\d+)", line)
            rows = int(act.group(1) if act else m.group(3))
            if rows >= large:
                add("HIGH", "SEQ_SCAN_LARGE", f"Seq Scan on {m.group(2)} returns ~{rows} rows; an index on the filter columns may help")
        m = re.search(r"Rows Removed by Filter:\s*(\d+)", line)
        if m:
            removed = int(m.group(1))
            prev = next((l for l in reversed(lines[:i]) if "actual" in l), "")
            k = re.search(r"actual[^)]*rows=(\d+)", prev)
            kept = int(k.group(1)) if k else 0
            if removed >= 1000 and removed >= 9 * max(kept, 1):
                add("MEDIUM", "ROWS_REMOVED", f"filter removed {removed} rows to keep {kept} ({prev.strip()[:60]}...)")
        m = re.search(r"cost=[\d.]+\.\.[\d.]+ rows=(\d+).*actual time=[\d.]+\.\.[\d.]+ rows=(\d+) loops=(\d+)", line)
        if m:
            est, act, loops = int(m.group(1)), int(m.group(2)), int(m.group(3))
            if max(est, act) >= 100 and max(est, act) >= 10 * max(min(est, act), 1):
                node = line.strip().lstrip("->").strip().split("  ")[0]
                add("MEDIUM", "EST_MISMATCH", f"{node[:50]}: estimated {est} rows, actual {act}; run ANALYZE")
            if loops >= 1000:
                node = line.strip().lstrip("->").strip().split("  ")[0]
                add("LOW", "NESTED_LOOP_MANY", f"{node[:50]} runs {loops} loops; check the join index or consider a hash join")
        if re.search(r"Sort Method: external merge", line):
            add("MEDIUM", "DISK_SORT", "sort spilled to disk (external merge); add an index for ORDER BY or raise work_mem for this query")
    findings.sort(key=lambda f: SEV_ORDER[f["severity"]])
    return findings


def render(path, stmts, findings):
    out = [f"# SQL performance review: {path}", "",
           f"{len(stmts)} statement(s); {sum(f['severity'] == 'HIGH' for f in findings)} HIGH, "
           f"{sum(f['severity'] == 'MEDIUM' for f in findings)} MEDIUM, {sum(f['severity'] == 'LOW' for f in findings)} LOW", ""]
    for n, st in enumerate(stmts, 1):
        out.append(f"{n}. {st[:110]}{'...' if len(st) > 110 else ''}")
    out += ["", "## Findings"]
    if not findings:
        out.append("- none (heuristics only: still check the real plan with EXPLAIN ANALYZE)")
    for f in findings:
        where = f"stmt {f['statement']}" if f["statement"] else "EXPLAIN"
        out.append(f"- {f['severity']} {f['code']} ({where}): {f['message']}")
    return "\n".join(out)


def self_test():
    results = []

    def check(name, cond):
        results.append(cond)
        print(("PASS " if cond else "FAIL ") + name)

    def codes(sql, **kw):
        return [f["code"] for f in review_sql(sql, **kw)[1]]
    check("select star", "SELECT_STAR" in codes("SELECT * FROM users WHERE id = 1"))
    check("alias star", "SELECT_STAR" in codes("SELECT o.* FROM orders o WHERE o.id = 1"))
    check("explicit columns ok", "SELECT_STAR" not in codes("SELECT id, name FROM users WHERE id = 1"))
    check("lower(col) HIGH", "FUNC_ON_COLUMN" in codes("SELECT id FROM users WHERE LOWER(email) = 'a@b.c'"))
    check("year(col) HIGH", "FUNC_ON_COLUMN" in codes("SELECT id FROM orders WHERE YEAR(created_at) = 2026"))
    check("arithmetic HIGH", "FUNC_ON_COLUMN" in codes("SELECT id FROM t WHERE price * 2 > 100"))
    check("function on value side ok", "FUNC_ON_COLUMN" not in codes("SELECT id FROM users WHERE email = LOWER('A@B.C')"))
    check("leading wildcard", "LEADING_WILDCARD" in codes("SELECT id FROM p WHERE name LIKE '%phone%'"))
    check("trailing wildcard ok", "LEADING_WILDCARD" not in codes("SELECT id FROM p WHERE name LIKE 'phone%'"))
    check("wildcard inside string literal ignored", "LEADING_WILDCARD" not in codes("SELECT 'x LIKE %y' AS note FROM p WHERE id = 1"))
    check("N+1 detected", "N_PLUS_ONE" in codes("SELECT name FROM users WHERE id = 1; SELECT name FROM users WHERE id = 2; SELECT name FROM users WHERE id = 3;"))
    check("2 repeats not N+1", "N_PLUS_ONE" not in codes("SELECT name FROM users WHERE id = 1; SELECT name FROM users WHERE id = 2;"))
    check("OR across columns", "OR_ACROSS_COLUMNS" in codes("SELECT id FROM u WHERE email = 'a' OR phone = 'b'"))
    check("OR same column LOW", "OR_SAME_COLUMN" in codes("SELECT id FROM u WHERE status = 'a' OR status = 'b'"))
    check("quoted number LOW", "QUOTED_NUMBER" in codes("SELECT id FROM orders WHERE customer_id = '42'"))
    check("implicit cast phone number", "IMPLICIT_CAST" in codes("SELECT id FROM users WHERE phone = 5551234"))
    check("number id ok", "QUOTED_NUMBER" not in codes("SELECT id FROM orders WHERE customer_id = 42"))
    check("NOT IN subquery", "NOT_IN_SUBQUERY" in codes("SELECT id FROM a WHERE id NOT IN (SELECT a_id FROM b)"))
    check("deep offset", "DEEP_OFFSET" in codes("SELECT id FROM a ORDER BY id LIMIT 20 OFFSET 50000"))
    check("mysql limit a,b offset", "DEEP_OFFSET" in codes("SELECT id FROM a ORDER BY id LIMIT 20000, 20"))
    check("order by rand", "ORDER_BY_RANDOM" in codes("SELECT id FROM a ORDER BY RAND() LIMIT 1"))
    check("delete without where", "NO_WHERE_WRITE" in codes("DELETE FROM sessions"))
    check("AND/OR precedence HIGH", "AND_OR_PRECEDENCE" in codes("SELECT id FROM o WHERE a = 1 AND b = 2 OR b = 3"))
    check("parenthesized OR ok", "AND_OR_PRECEDENCE" not in codes("SELECT id FROM o WHERE a = 1 AND (b = 2 OR b = 3)"))
    check("BETWEEN ... AND not an AND", "AND_OR_PRECEDENCE" not in codes("SELECT id FROM o WHERE d BETWEEN 1 AND 5 OR e = 2"))
    check("literals restored in output", "'mina'" in review_sql("SELECT id FROM u WHERE name = 'mina'")[0][0])
    check("count for exists", "COUNT_FOR_EXISTS" in codes("SELECT CASE WHEN COUNT(*) > 0 THEN 1 END FROM a WHERE b = 1"))
    check("distinct join", "DISTINCT_JOIN" in codes("SELECT DISTINCT u.id FROM users u JOIN orders o ON o.user_id = u.id"))
    idx = parse_indexes("orders(customer_id, created_at)\nusers(id)")
    c = codes("SELECT o.id FROM orders o JOIN users u ON u.id = o.customer_id WHERE o.status = 'paid'", indexes=idx)
    check("no index for filter", c.count("NO_INDEX_FOR_FILTER") == 1)
    check("comments stripped", codes("-- SELECT * FROM x\nSELECT id FROM users WHERE id = 1") == [])
    pg = """Seq Scan on orders  (cost=0.00..25000.00 rows=50 width=8) (actual time=0.1..310.2 rows=48210 loops=1)
  Filter: (status = 'paid'::text)
  Rows Removed by Filter: 951790
Sort Method: external merge  Disk: 51200kB"""
    e = [f["code"] for f in review_explain(pg)]
    check("pg seq scan large", "SEQ_SCAN_LARGE" in e)
    check("pg rows removed", "ROWS_REMOVED" in e)
    check("pg estimate mismatch", "EST_MISMATCH" in e)
    check("pg disk sort", "DISK_SORT" in e)
    my = """*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
         type: ALL
          key: NULL
         rows: 980000
     filtered: 5.00
        Extra: Using where; Using temporary; Using filesort"""
    e = [f["code"] for f in review_explain(my)]
    check("mysql type ALL", "SEQ_SCAN_LARGE" in e and "DISK_SORT" in e and "ROWS_REMOVED" in e)
    try:
        review_sql("  -- only a comment\n")
        check("empty input rejected", False)
    except DataError:
        check("empty input rejected", True)
    print(f"{sum(results)}/{len(results)} passed")
    return 0 if all(results) else 1


def main():
    ap = argparse.ArgumentParser(description="SQL query performance reviewer (heuristic)")
    ap.add_argument("sql", nargs="?", help=".sql file with one or more statements")
    ap.add_argument("--explain", help="EXPLAIN output (PostgreSQL text or MySQL \\G)")
    ap.add_argument("--indexes", help="known indexes, one per line: table(col1, col2)")
    ap.add_argument("--repeat", type=int, default=3, help="N+1 threshold (default 3)")
    ap.add_argument("--large-rows", type=int, default=10000, help="rows for SEQ_SCAN_LARGE (default 10000)")
    ap.add_argument("--json", action="store_true")
    ap.add_argument("--self-test", action="store_true")
    a = ap.parse_args()
    if a.self_test:
        return self_test()
    if not a.sql:
        ap.error("give a .sql file or --self-test")
    try:
        with open(a.sql, encoding="utf-8") as fh:
            text = fh.read()
        idx = None
        if a.indexes:
            with open(a.indexes, encoding="utf-8") as fh:
                idx = parse_indexes(fh.read())
        stmts, findings = review_sql(text, a.repeat, idx)
        if a.explain:
            with open(a.explain, encoding="utf-8") as fh:
                findings += review_explain(fh.read(), a.large_rows)
    except (OSError, DataError) as e:
        print(f"error: {e}", file=sys.stderr)
        return 2
    if a.json:
        print(json.dumps({"statements": stmts, "findings": findings}, indent=2))
    else:
        print(render(a.sql, stmts, findings))
    return 1 if any(f["severity"] == "HIGH" for f in findings) else 0


if __name__ == "__main__":
    sys.exit(main())

Python SQL Database+1

F@f

SQL Schema Design Reviewer

Skill

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.

---
name: sql-schema-design-reviewer
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.
---

# SQL Schema Design Reviewer

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.

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.

## Files in this skill

- `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)
- `references/design-rules.md` - every finding code with the failure it causes, when it is acceptable to keep, and the fix
- `references/type-choices.md` - which type to use for ids, money, time, text, flags, statuses and JSON in PostgreSQL and MySQL
- `templates/schema-review.md` - the review to hand back
- `examples/shop-schema.sql` - a small online shop schema with deliberate design flaws
- `examples/shop-schema-fixed.sql` - the same schema after the review; it lints clean

## Workflow

### 1. Get the schema and the context
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).

### 2. Run the linter
```
python3 scripts/schema_lint.py examples/shop-schema.sql
python3 scripts/schema_lint.py schema.sql --dialect mysql --min-severity MEDIUM
python3 scripts/schema_lint.py db/*.sql --ignore NO_CREATED_AT --json > schema-review.json
python3 scripts/schema_lint.py --self-test
```
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.

### 3. Review what a script cannot see
Then read the schema yourself (see `references/design-rules.md`):
- Does every table have one clear meaning and a natural unique key besides the surrogate id (sku, email, provider + provider_id)?
- 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?
- Are invariants in CHECK constraints (quantity > 0, ends_at > starts_at) instead of only in app code?
- Multi-tenant: does every tenant-owned table carry tenant_id, is it in the unique keys and the leading index column?
- Soft deletes: are unique constraints partial (`WHERE deleted_at IS NULL`)?
- Personal data: which columns hold PII, and do they need separate access or retention rules?

### 4. Deliver
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.

## Example result
`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.

## Rules

- 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.
- Keep the user's conventions (UUID ids, naming) unless they cause a real problem; say when a finding is a judgement call.
- Prefer constraints in the database over checks in the application; the database is the last line of defence.
- Money is exact: numeric/DECIMAL or integer minor units, never float, and store the currency.
- Time is an instant: timestamptz in PostgreSQL, UTC everywhere in MySQL.
- Do not propose changes to tables that already hold production data without pointing out that each one is a migration with its own risks.
FILE:references/design-rules.md
# Design rules and finding codes

| Code | Sev | What goes wrong | Acceptable when | Fix |
|---|---|---|---|---|
| NO_PRIMARY_KEY | HIGH | duplicate rows cannot be told apart or deleted one at a time; logical replication and CDC need a key; ORMs misbehave | never for OLTP tables (staging tables loaded and dropped in one job, maybe) | surrogate `id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY` or a composite natural key (order_id, product_id) |
| FLOAT_MONEY | HIGH | 0.1 + 0.2 = 0.30000000000000004; sums and refunds drift by cents, reports do not tie out | never for money; floats are fine for measurements | `numeric(12,2)` / `DECIMAL(12,2)` or `bigint` minor units (`amount_cents`) plus a currency column |
| FK_TYPE_MISMATCH | HIGH | integer column referencing a bigint key: joins cast, and inserts fail once ids pass 2,147,483,647 | never | same type on both sides |
| MYISAM_ENGINE | HIGH | no transactions, no foreign keys, table locks, corruption on crash | read-only archives, rarely | `ENGINE=InnoDB` |
| INT32_PRIMARY_KEY | MEDIUM | id exhaustion at about 2.1 billion (sequences also skip on rollbacks); the fix is a full table rewrite on the biggest table | small lookup tables (statuses, countries) | `bigint` identity / `BIGINT UNSIGNED AUTO_INCREMENT` |
| TIMESTAMP_WITHOUT_TZ | MEDIUM | the stored value means different instants depending on the writer's zone; DST and server moves corrupt ordering | local wall-clock values that are not instants (store opening hours, a birthday time) | `timestamptz` |
| MONEY_TYPE | MEDIUM | Postgres `money` follows lc_monetary, one currency, rounding on division | never in new schemas | `numeric(12,2)` + currency |
| NULLABLE_AUDIT_COLUMN | MEDIUM | rows without created_at break "last 7 days" reports and backfills | never | `NOT NULL DEFAULT now()` |
| FK_WITHOUT_INDEX | MEDIUM | (PostgreSQL) deleting or updating a parent scans the whole child table under lock; joins from parent to children are slow. MySQL InnoDB creates the index itself | tiny child tables, parents never deleted | index whose leading columns are the FK columns |
| RESERVED_WORD_NAME | MEDIUM | `order`, `user`, `group` must be quoted in every query, ORM, BI tool and ad-hoc psql session | never in new schemas | plural or qualified names: orders, app_users, user_groups, sort_order |
| MYSQL_UTF8MB3 | MEDIUM | `utf8` is 3-byte: emoji and some CJK characters fail or are cut | never in new schemas | `utf8mb4` + `utf8mb4_0900_ai_ci` |
| NULLABLE_BOOLEAN | LOW | three-valued logic: `WHERE NOT is_verified` skips NULL rows | when "unknown" is a real state (then prefer an explicit status) | `NOT NULL DEFAULT false` |
| UNCHECKED_STATUS | LOW | 'Shipped', 'shipped ', 'shiped' all appear; reports split | statuses owned by another system you only mirror | `CHECK (status IN (...))`, an enum type or a lookup table FK |
| MISSING_FOREIGN_KEY | LOW | orphans after deletes, typos in ids, no documented relation | cross-database or event-sourced references; document them | `REFERENCES parent(id)` + index |
| CASE_SENSITIVE_UNIQUE_EMAIL | LOW | Ana@x.com and ana@x.com are two accounts; password resets go to the wrong one | never for login emails | `citext` or `UNIQUE INDEX ON t (lower(email))` and normalize on write |
| VARCHAR_255_HABIT | LOW | in Postgres, 255 is an arbitrary MySQL-era limit; real rules are missing and odd values get rejected | when 255 really is the rule | `text` + `CHECK (char_length(x) <= n)` for real limits |
| REDUNDANT_INDEX | LOW | an index that is a prefix of another (or of the PK) costs writes and memory for nothing | the shorter index is much smaller and very hot | drop it |
| JSON_IN_TEXT | LOW | no validation, no operators, no GIN index | the text is not really JSON | `jsonb` / `JSON` |
| NO_CREATED_AT | LOW | debugging, audits and backfills need to know when rows appeared | pure join tables | `created_at timestamptz NOT NULL DEFAULT now()` |
| MIXED_CASE_NAME | LOW | quoted "OrderItems" must be quoted forever in Postgres | never | snake_case |
| DATETIME_NO_ZONE | LOW | (MySQL) DATETIME has no zone; mixed writers store mixed zones | when every connection sets time_zone='+00:00' | enforce UTC |

## Things the linter cannot judge (review by hand)
- **Keys**: is there a natural unique key besides the surrogate id? Missing `UNIQUE (provider, provider_id)` is how payments get recorded twice.
- **ON DELETE**: RESTRICT/NO ACTION by default; CASCADE only for true child rows (order_items of an order), never from customers to payments.
- **CHECK constraints for invariants**: `quantity > 0`, `ends_at > starts_at`, `discount <= total`.
- **Multi-tenancy**: tenant_id on every tenant table, first in unique keys and indexes, and in every FK (composite FKs prevent cross-tenant references).
- **Soft deletes**: partial unique indexes `WHERE deleted_at IS NULL`, or the email can never be reused.
- **Denormalized totals**: if orders.total is stored, say how it stays equal to the sum of order_items.
- **PII**: list columns with personal data; consider a separate table with stricter access and retention.
FILE:references/type-choices.md
# Type choices (PostgreSQL / MySQL 8)

| Data | PostgreSQL | MySQL 8 | Avoid |
|---|---|---|---|
| Surrogate id | `bigint GENERATED ALWAYS AS IDENTITY` | `BIGINT UNSIGNED AUTO_INCREMENT` | `serial`/`int` on growing tables |
| Public / distributed id | `uuid` (UUIDv7 for index locality, `gen_random_uuid()` for v4) | `BINARY(16)` with UUID_TO_BIN(x, 1) | `varchar(36)` UUIDs (3x bigger, slower) |
| Money | `numeric(12,2)` (or (19,4) for rates) + `currency char(3)` | `DECIMAL(12,2)` + `CHAR(3)` | `float`, `double`, `real`, Postgres `money` |
| Money as integer | `bigint amount_cents` (mind currencies with 0 or 3 decimals) | `BIGINT` | `int` cents (overflows at 21 million) |
| Instant (event happened) | `timestamptz` | `DATETIME(6)` in UTC, or `TIMESTAMP` (2038 limit, converted by session zone) | `timestamp` without time zone |
| Calendar date | `date` | `DATE` | timestamps at midnight |
| Wall-clock time / schedule | `time` + an IANA zone column | `TIME` + zone column | `timetz` |
| Duration | `interval` or integer seconds | `INT` seconds | text like "1h30" |
| Text | `text` (+ CHECK for real limits) | `VARCHAR(n)` with a real n; `TEXT` for long bodies | `varchar(255)` by habit; `char(n)` padding |
| Case-insensitive text (email, username) | `citext` or a `lower()` unique index | case-insensitive `_ci` collation (default) | plain unique on text in Postgres |
| Flag | `boolean NOT NULL DEFAULT false` | `BOOLEAN` (TINYINT(1)) `NOT NULL DEFAULT 0` | nullable booleans, 'Y'/'N' strings |
| Status | `text` + `CHECK (... IN (...))`, enum type, or lookup table | `ENUM(...)` or lookup table | unconstrained text |
| JSON | `jsonb` (GIN index for containment) | `JSON` (generated columns to index) | JSON in text |
| IP address | `inet` / `cidr` | `VARBINARY(16)` with INET6_ATON | text |
| Large binary | object storage + key in a `text` column | same | big BLOBs in hot tables |

## Notes
- Changing a column type later rewrites the table in both engines (with a few exceptions such as widening varchar in Postgres). Get ids and money right first.
- Enum types in Postgres: adding a value is cheap, removing or renaming one is not. Lookup tables are easier to evolve and can carry labels and sort order.
- `NOT NULL` with a sensible default is the norm; nullable should mean "genuinely unknown or not applicable".
- Store the currency next to every amount, even if today there is only one.
- Name things the same everywhere: `customer_id` in every table that references customers, `_at` for instants, `_on` or `_date` for dates, `is_`/`has_` for booleans.
FILE:templates/schema-review.md
# Schema review: <project / schema file>, <date>

**Engine:** <PostgreSQL 16>   **Scope:** <5 tables: customers, products, orders, order_items, payments>
**Context:** <OLTP shop backend, ~2M orders/year, Prisma ORM, no data yet>
**Command:** `python3 scripts/schema_lint.py <shop-schema.sql>`
**Verdict:** <CHANGES NEEDED before the first release: 4 HIGH, 8 MEDIUM, 8 LOW>

## Must fix (HIGH)
| # | Table.column | Finding | What goes wrong | Fix |
|---|---|---|---|---|
| 1 | <products.price> | <FLOAT_MONEY> | <0.1 + 0.2 != 0.3; order totals drift by cents> | <numeric(12,2) NOT NULL CHECK (price >= 0)> |

## Should fix (MEDIUM)
| # | Table.column | Finding | What goes wrong | Fix |
|---|---|---|---|---|
| 1 | <"order"> | <RESERVED_WORD_NAME> | <must be quoted in every query and BI tool> | <rename to orders> |

## Consider (LOW / judgement calls)
- <customers.email: citext so Ana@x.com and ana@x.com are one account>

## Found by reading (not by the linter)
- <payments: add UNIQUE (provider, provider_id) so a webhook retry cannot record a payment twice>
- <ON DELETE: keep RESTRICT on payments -> orders; CASCADE only order_items -> orders>

## Corrected DDL
```sql
<full corrected schema, lints clean>
```

## Decisions for the team
- <bigint identity vs UUIDv7 for public ids>
- <store orders.total or compute it from order_items?>

## If tables already hold data
- <each change above is a migration; list them and run a migration safety review before deploying>
FILE:examples/shop-schema.sql
-- Small online shop schema (PostgreSQL), first draft for review.
-- The problems are deliberate: this is what the skill is meant to catch.

CREATE TABLE customers (
    id          serial PRIMARY KEY,
    email       varchar(255) NOT NULL UNIQUE,
    full_name   varchar(255),
    phone       varchar(255),
    is_verified boolean,
    created_at  timestamp
);

CREATE TABLE products (
    id         serial PRIMARY KEY,
    sku        varchar(64) NOT NULL UNIQUE,
    title      text NOT NULL,
    price      double precision NOT NULL,
    attributes text,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE "order" (
    id          bigserial PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers (id),
    status      varchar(20) NOT NULL DEFAULT 'pending',
    total       numeric(12,2) NOT NULL,
    placed_at   timestamp NOT NULL DEFAULT now(),
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    order_id   bigint NOT NULL REFERENCES "order" (id),
    product_id bigint NOT NULL,
    quantity   integer NOT NULL,
    unit_price numeric(12,2) NOT NULL
);

CREATE TABLE payments (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id    integer NOT NULL REFERENCES "order" (id),
    amount      real NOT NULL,
    provider    text NOT NULL,
    provider_id text,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX payments_order_idx ON payments (order_id);
CREATE INDEX payments_order_created_idx ON payments (order_id, created_at);
FILE:examples/shop-schema-fixed.sql
-- Small online shop schema (PostgreSQL), after review.
-- Every change is explained in templates/schema-review.md style in the review.

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE customers (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       citext NOT NULL UNIQUE,
    full_name   text,
    phone       text CHECK (char_length(phone) <= 32),
    is_verified boolean NOT NULL DEFAULT false,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE products (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku        text NOT NULL UNIQUE,
    title      text NOT NULL,
    price      numeric(12,2) NOT NULL CHECK (price >= 0),
    attributes jsonb NOT NULL DEFAULT '{}',
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers (id),
    status      text NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled', 'refunded')),
    total       numeric(12,2) NOT NULL CHECK (total >= 0),
    placed_at   timestamptz NOT NULL DEFAULT now(),
    created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX orders_customer_idx ON orders (customer_id);

CREATE TABLE order_items (
    order_id   bigint NOT NULL REFERENCES orders (id),
    product_id bigint NOT NULL REFERENCES products (id),
    quantity   integer NOT NULL CHECK (quantity > 0),
    unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0),
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (order_id, product_id)
);
CREATE INDEX order_items_product_idx ON order_items (product_id);

CREATE TABLE payments (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id    bigint NOT NULL REFERENCES orders (id),
    amount      numeric(12,2) NOT NULL,
    provider    text NOT NULL,
    provider_id text,
    created_at  timestamptz NOT NULL DEFAULT now(),
    UNIQUE (provider, provider_id)
);
CREATE INDEX payments_order_created_idx ON payments (order_id, created_at);
FILE:scripts/schema_lint.py
#!/usr/bin/env python3
"""SQL schema design linter for PostgreSQL and MySQL DDL (Python 3 standard library only).

Reads CREATE TABLE / CREATE INDEX / ALTER TABLE ... ADD statements (a schema dump,
schema.sql, or the DDL of new tables in a pull request) and reports design problems
that are cheap to fix before data exists and expensive after: tables without a
primary key, money stored in floating point, foreign keys whose type differs from the
referenced key or that have no supporting index (PostgreSQL), *_id columns without a
foreign key, 32-bit primary keys, timestamps without time zone, nullable audit
columns and booleans, unchecked status columns, reserved-word and mixed-case names,
case-sensitive unique emails, redundant indexes, JSON kept in text columns, and MySQL
tables using MyISAM or 3-byte utf8.

It does not connect to a database and does not judge migration safety (locks,
backfills); it reviews the shape of the schema itself.

Usage:
  python3 schema_lint.py FILE.sql [FILE.sql ...] [--dialect auto|postgres|mysql]
        [--min-severity LOW|MEDIUM|HIGH] [--ignore CODE,CODE] [--json]
  python3 schema_lint.py --self-test
Exit codes: 0 = no HIGH findings, 1 = HIGH findings, 2 = bad input (unreadable file,
no CREATE TABLE found).
"""
import argparse, json, re, sys

SEV_ORDER = {"HIGH": 0, "MEDIUM": 1, "LOW": 2}
MONEY_RE = re.compile(r"(^|_)(price|amount|total|subtotal|cost|balance|fee|fees|tax|salary|refund|discount|paid|payment|revenue|credit|debit|charge)(_|$)", re.I)
RESERVED = {"user", "order", "group", "select", "table", "column", "desc", "asc", "check", "default", "from",
            "where", "limit", "offset", "references", "primary", "key", "range", "rank", "window", "all", "both",
            "end", "grant", "to", "type", "values", "constraint", "index", "match", "condition", "interval", "read", "row"}
RESERVED_PG_ONLY_SAFE = {"key", "range", "rank", "type", "values", "match", "condition", "interval", "read", "row", "index"}  # fine unquoted in Postgres
RESERVED_MY_ONLY_SAFE = {"offset", "user", "end", "type", "values", "row"}  # fine unquoted in MySQL 8
STATUS_RE = re.compile(r"^(status|state|kind|role|type|.*_status|.*_state|.*_type)$", re.I)
JSONISH_RE = re.compile(r"(^|_)(json|payload|metadata|meta|attributes|settings|extra|data)$", re.I)
INT32 = {"int", "integer", "int4", "serial", "serial4", "mediumint", "smallint", "int2", "smallserial", "tinyint"}
INT64 = {"bigint", "int8", "bigserial", "serial8"}


def finding(sev, code, table, column, problem, fix, line=0):
    return {"severity": sev, "code": code, "table": table, "column": column, "line": line, "problem": problem, "fix": fix}


def strip_comments(sql):
    out, i, n = [], 0, len(sql)
    while i < n:
        c = sql[i]
        if c in "'\"`":
            j = i + 1
            while j < n and sql[j] != c:
                j += 2 if sql[j] == "\\" and c == "'" else 1
            out.append(sql[i:j + 1]); i = j + 1
        elif sql.startswith("--", i) or c == "#" and (i == 0 or sql[i - 1] in "\n \t"):
            j = sql.find("\n", i); j = n if j < 0 else j
            out.append(" " * (j - i)); i = j
        elif sql.startswith("/*", i):
            j = sql.find("*/", i + 2); j = n if j < 0 else j + 2
            out.append(re.sub(r"[^\n]", " ", sql[i:j])); i = j
        else:
            out.append(c); i += 1
    return "".join(out)


def split_top(s, sep=","):
    parts, depth, cur, q = [], 0, [], None
    for ch in s:
        if q:
            cur.append(ch)
            if ch == q:
                q = None
            continue
        if ch in "'\"`":
            q = ch
        elif ch == "(":
            depth += 1
        elif ch == ")":
            depth -= 1
        if ch == sep and depth == 0:
            parts.append("".join(cur)); cur = []
        else:
            cur.append(ch)
    if "".join(cur).strip():
        parts.append("".join(cur))
    return parts


def statements(sql):
    clean = strip_comments(sql)
    pos = 0
    for part in split_top(clean, ";"):
        line = clean.count("\n", 0, pos + len(part) - len(part.lstrip())) + 1
        pos += len(part) + 1
        if part.strip():
            yield line, part.strip()


def ident(s):
    s = s.strip()
    quoted = s[:1] in "\"`"
    name = s.split(".")[-1].strip().strip("\"`")
    return name, quoted


def cols_list(s):
    return [ident(re.sub(r"\s+(ASC|DESC|NULLS\s+(FIRST|LAST)).*$", "", c.strip(), flags=re.I))[0].lower() if not c.strip().startswith("(") and "(" not in c else c.strip().lower()
            for c in split_top(s)]


class Schema:
    def __init__(self):
        self.tables, self.indexes, self.fks, self.enums = {}, [], [], set()
        self.dialect_hints = {"mysql": 0, "postgres": 0}


def parse(sql):
    sc = Schema()
    if re.search(r"AUTO_INCREMENT|ENGINE\s*=|`|UNSIGNED|DATETIME|TINYINT\(", sql, re.I):
        sc.dialect_hints["mysql"] += 1
    if re.search(r"\bSERIAL\b|BIGSERIAL|TIMESTAMPTZ|JSONB|GENERATED\s+(ALWAYS|BY DEFAULT)\s+AS\s+IDENTITY|::|CREATE\s+TYPE|CITEXT|CONCURRENTLY|\bTEXT\[\]", sql, re.I):
        sc.dialect_hints["postgres"] += 1
    for line, st in statements(sql):
        m = re.match(r"CREATE\s+(?:TEMP(?:ORARY)?\s+|UNLOGGED\s+)?TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?([\w\".`]+)\s*\((.*)\)\s*(.*)$", st, re.I | re.S)
        if m:
            name, quoted = ident(m.group(1))
            t = {"name": name, "quoted": quoted, "line": line, "cols": {}, "order": [], "pk": [], "uniques": [],
                 "checks": " ".join([]), "options": m.group(3), "raw": st}
            checks = []
            for item in split_top(m.group(2)):
                parse_item(item.strip(), t, sc, checks)
            t["checks"] = " ".join(checks).lower()
            sc.tables[name.lower()] = t
            continue
        m = re.match(r"CREATE\s+(UNIQUE\s+)?INDEX\s+(?:CONCURRENTLY\s+)?(?:IF\s+NOT\s+EXISTS\s+)?([\w\".`]*)\s*ON\s+(?:ONLY\s+)?([\w\".`]+)\s*(?:USING\s+\w+\s*)?\((.*)\)\s*(.*)$", st, re.I | re.S)
        if m:
            sc.indexes.append({"table": ident(m.group(3))[0].lower(), "cols": cols_list(m.group(4)), "unique": bool(m.group(1)),
                               "name": ident(m.group(2))[0] if m.group(2) else "", "partial": "where" in m.group(5).lower(), "line": line})
            continue
        m = re.match(r"ALTER\s+TABLE\s+(?:ONLY\s+)?(?:IF\s+EXISTS\s+)?([\w\".`]+)\s+(.*)$", st, re.I | re.S)
        if m:
            tname = ident(m.group(1))[0].lower()
            for action in split_top(m.group(2)):
                a = action.strip()
                am = re.match(r"ADD\s+(?:COLUMN\s+)(?:IF\s+NOT\s+EXISTS\s+)?(.*)$", a, re.I | re.S)
                if am and tname in sc.tables:
                    parse_item(am.group(1), sc.tables[tname], sc, [])
                    continue
                am = re.match(r"ADD\s+(.*)$", a, re.I | re.S)
                if am and tname in sc.tables:
                    chk = []
                    parse_item(am.group(1), sc.tables[tname], sc, chk)
                    sc.tables[tname]["checks"] += " " + " ".join(chk).lower()
            continue
        m = re.match(r"CREATE\s+TYPE\s+([\w\".]+)\s+AS\s+ENUM", st, re.I)
        if m:
            sc.enums.add(ident(m.group(1))[0].lower())
        m = re.match(r"CREATE\s+(?:DOMAIN|EXTENSION)\s+(?:IF\s+NOT\s+EXISTS\s+)?([\w\".]+)", st, re.I)
        if m:
            sc.enums.add(ident(m.group(1))[0].lower())
    return sc


def parse_item(item, t, sc, checks):
    u = item.upper()
    m = re.match(r"CONSTRAINT\s+[\w\"`]+\s+(.*)$", item, re.I | re.S)
    if m:
        item, u = m.group(1).strip(), m.group(1).strip().upper()
    if u.startswith("PRIMARY KEY"):
        t["pk"] = cols_list(re.search(r"\((.*)\)", item, re.S).group(1)); return
    if re.match(r"UNIQUE\b", u):
        cm = re.search(r"\((.*?)\)", item, re.S)
        if cm:
            cols = cols_list(cm.group(1))
            t["uniques"].append(cols)
            sc.indexes.append({"table": t["name"].lower(), "cols": cols, "unique": True, "name": "", "partial": False, "line": t["line"]})
        return
    if u.startswith("FOREIGN KEY"):
        fm = re.match(r"FOREIGN\s+KEY\s*\((.*?)\)\s*REFERENCES\s+([\w\".`]+)\s*(?:\((.*?)\))?", item, re.I | re.S)
        if fm:
            sc.fks.append({"table": t["name"].lower(), "cols": cols_list(fm.group(1)), "ref": ident(fm.group(2))[0].lower(),
                           "ref_cols": cols_list(fm.group(3)) if fm.group(3) else None})
        return
    if u.startswith("CHECK"):
        checks.append(item); return
    if re.match(r"(UNIQUE\s+)?(KEY|INDEX)\b", u) or re.match(r"(FULLTEXT|SPATIAL)\b", u):
        cm = re.search(r"\((.*)\)", item, re.S)
        if cm:
            sc.indexes.append({"table": t["name"].lower(), "cols": cols_list(cm.group(1)), "unique": u.startswith("UNIQUE"),
                               "name": "", "partial": False, "line": t["line"]})
        return
    if u.startswith(("EXCLUDE", "LIKE", "PERIOD")):
        return
    cm = re.match(r"([\"`][^\"`]+[\"`]|[\w$]+)\s+(.*)$", item, re.S)
    if not cm:
        return
    name, quoted = ident(cm.group(1))
    rest = cm.group(2)
    tm = re.match(r"((?:double\s+precision|character\s+varying|bit\s+varying|timestamp(?:\s*\(\d+\))?(?:\s+with(?:out)?\s+time\s+zone)?(?!\w)|time(?:\s+with(?:out)?\s+time\s+zone)?(?!\w)|[\w.]+)(?:\s*\([^)]*\))?(?:\s+unsigned)?(?:\s*\[\])?)", rest, re.I)
    ctype = re.sub(r"\s+", " ", tm.group(1).lower()) if tm else ""
    ru = rest.upper()
    col = {"name": name, "quoted": quoted, "type": ctype, "not_null": "NOT NULL" in ru or "PRIMARY KEY" in ru,
           "default": "DEFAULT" in ru or "IDENTITY" in ru or "AUTO_INCREMENT" in ru or ctype.endswith("serial"),
           "check": "CHECK" in ru}
    t["cols"][name.lower()] = col
    t["order"].append(name.lower())
    if "PRIMARY KEY" in ru:
        t["pk"] = [name.lower()]
    if re.search(r"\bUNIQUE\b", ru):
        t["uniques"].append([name.lower()])
        sc.indexes.append({"table": t["name"].lower(), "cols": [name.lower()], "unique": True, "name": "", "partial": False, "line": t["line"]})
    rm = re.search(r"REFERENCES\s+([\w\".`]+)\s*(?:\((.*?)\))?", rest, re.I)
    if rm:
        sc.fks.append({"table": t["name"].lower(), "cols": [name.lower()], "ref": ident(rm.group(1))[0].lower(),
                       "ref_cols": cols_list(rm.group(2)) if rm.group(2) else None})
    if col["check"]:
        checks.append(f"check ({name})")


def qi(name):
    return f'"{name}"' if name.lower() in RESERVED or name != name.lower() else name


def base_type(ctype):
    t = re.sub(r"\(.*?\)|\s+unsigned|\[\]", "", ctype).strip()
    return {"int4": "integer", "int": "integer", "serial": "integer", "serial4": "integer", "int8": "bigint",
            "bigserial": "bigint", "serial8": "bigint", "int2": "smallint", "smallserial": "smallint"}.get(t, t)


def lint(sql, dialect="auto", ignore=()):
    sc = parse(sql)
    if dialect == "auto":
        dialect = "mysql" if sc.dialect_hints["mysql"] > sc.dialect_hints["postgres"] else "postgres"
    out = []

    def add(*a, **k):
        f = finding(*a, **k)
        if f["code"] not in ignore:
            out.append(f)

    table_names = set(sc.tables)
    for key, t in sc.tables.items():
        tn = t["name"]
        ln = t["line"]
        if not t["pk"]:
            add("HIGH", "NO_PRIMARY_KEY", tn, "", "table has no primary key: duplicate rows cannot be told apart, updates and replication (logical replication, MySQL row-based, CDC) suffer",
                "add a bigint identity (or UUID) primary key, or a composite key on the natural unique columns", line=ln)
        elif len(t["pk"]) == 1 and t["pk"][0] in t["cols"]:
            c = t["cols"][t["pk"][0]]
            bt = base_type(c["type"])
            if bt in {"integer", "smallint", "mediumint", "tinyint"} and not re.search(r"(^|_)(type|status|kind|country|currency|lookup)s?$", tn, re.I):
                add("MEDIUM", "INT32_PRIMARY_KEY", tn, c["name"], f"primary key {c['name']} is {c['type']} (max about 2.1 billion, less for smaller types); running out means an emergency type change on the biggest table",
                    "use bigint GENERATED ALWAYS AS IDENTITY (Postgres) or BIGINT UNSIGNED AUTO_INCREMENT (MySQL)", line=ln)
        if dialect == "postgres" and t["quoted"] and t["name"] != t["name"].lower():
            add("LOW", "MIXED_CASE_NAME", tn, "", f'table "{tn}" is quoted mixed case: every query must quote it forever', "use snake_case lower-case names", line=ln)
        if tn.lower() in RESERVED and not (dialect == "postgres" and tn.lower() in RESERVED_PG_ONLY_SAFE) and not (dialect == "mysql" and tn.lower() in RESERVED_MY_ONLY_SAFE):
            add("MEDIUM", "RESERVED_WORD_NAME", tn, "", f"table name '{tn}' is a reserved word and must be quoted in every query and ORM", "rename (e.g. users, orders, user_groups)", line=ln)
        if dialect == "mysql":
            opts = t["options"].lower()
            if re.search(r"engine\s*=\s*myisam", opts):
                add("HIGH", "MYISAM_ENGINE", tn, "", "ENGINE=MyISAM has no transactions, no foreign keys and table-level locks; crashes can corrupt it", "use ENGINE=InnoDB", line=ln)
            if re.search(r"charset\s*=\s*utf8(mb3)?\b(?!mb4)", opts):
                add("MEDIUM", "MYSQL_UTF8MB3", tn, "", "utf8 in MySQL is 3-byte utf8mb3: emoji and some CJK characters fail to insert or get mangled", "use DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci", line=ln)
        if not any(re.match(r"^(created_at|created|created_on|inserted_at|date_created|creation_date)$", c) for c in t["cols"]):
            add("LOW", "NO_CREATED_AT", tn, "", "no created_at column: hard to debug, audit or backfill later", "add created_at timestamptz NOT NULL DEFAULT now()", line=ln)
        v255 = [c["name"] for c in t["cols"].values() if re.match(r"(varchar|character varying)\s*\(255\)", c["type"])]
        if dialect == "postgres" and len(v255) >= 3:
            add("LOW", "VARCHAR_255_HABIT", tn, ", ".join(v255), f"{len(v255)} columns are varchar(255): in Postgres the limit costs nothing to drop but 255 is rarely the real rule",
                "use text, with a CHECK (char_length(x) <= n) where a real limit exists", line=ln)
        for cname in t["order"]:
            c = t["cols"][cname]
            ct, bt = c["type"], base_type(c["type"])
            cn = c["name"]
            if MONEY_RE.search(cn) and bt in {"float", "real", "double", "double precision", "float4", "float8"}:
                add("HIGH", "FLOAT_MONEY", tn, cn, f"{cn} is {ct}: binary floating point cannot store 0.10 exactly, sums drift by cents",
                    "use numeric(12,2) / DECIMAL(12,2), or bigint minor units (amount_cents)", line=ln)
            if bt == "money" and dialect == "postgres":
                add("MEDIUM", "MONEY_TYPE", tn, cn, "Postgres money type depends on lc_monetary, has one fixed currency and rounds", "use numeric(12,2) plus a currency column", line=ln)
            if dialect == "postgres" and (re.match(r"^timestamp(\s*\(\d+\))?$", ct) or "without time zone" in ct):
                add("MEDIUM", "TIMESTAMP_WITHOUT_TZ", tn, cn, f"{cn} is timestamp without time zone: the instant depends on whatever zone the writer assumed", "use timestamptz (stores an absolute instant, displays in the session zone)", line=ln)
            if dialect == "mysql" and bt == "datetime" and re.search(r"_at$", cn):
                add("LOW", "DATETIME_NO_ZONE", tn, cn, f"{cn} is DATETIME (no zone): make sure every writer stores UTC", "document and enforce UTC (time_zone='+00:00' on connections) or use TIMESTAMP within its 2038 range", line=ln)
            if re.match(r"^(created_at|updated_at|inserted_at)$", cn, re.I) and not c["not_null"]:
                add("MEDIUM", "NULLABLE_AUDIT_COLUMN", tn, cn, f"{cn} is nullable{'' if c['default'] else ' and has no default'}: rows without it will appear", f"{cn} timestamptz NOT NULL DEFAULT now()", line=ln)
            if bt in {"boolean", "bool"} and not c["not_null"]:
                add("LOW", "NULLABLE_BOOLEAN", tn, cn, f"{cn} is a nullable boolean: three states (true, false, unknown) and NULL-unsafe WHERE clauses", f"{cn} boolean NOT NULL DEFAULT false", line=ln)
            if STATUS_RE.match(cn) and re.match(r"^(text|varchar|character varying|char|character)", bt) and not c["check"] \
                    and cn.lower() not in t["checks"] and not any(f["table"] == key and cname in f["cols"] for f in sc.fks):
                add("LOW", "UNCHECKED_STATUS", tn, cn, f"{cn} is free text: typos like 'Shipped ' and 'shiped' will end up in the data", "add CHECK (status IN (...)), an enum type, or a FK to a lookup table", line=ln)
            if bt in {"text", "longtext", "mediumtext"} and JSONISH_RE.search(cn):
                add("LOW", "JSON_IN_TEXT", tn, cn, f"{cn} looks like JSON stored as {ct}: no validation, no indexing", "use jsonb (Postgres) or JSON (MySQL) if it really is JSON", line=ln)
            if cn.lower() in RESERVED and not (dialect == "postgres" and cn.lower() in RESERVED_PG_ONLY_SAFE) and not (dialect == "mysql" and cn.lower() in RESERVED_MY_ONLY_SAFE) and cn.lower() not in {"type"}:
                add("MEDIUM", "RESERVED_WORD_NAME", tn, cn, f"column name '{cn}' is a reserved word and needs quoting everywhere", "rename it (e.g. sort_order, user_id, group_name, ends_at)", line=ln)
            if dialect == "postgres" and c["quoted"] and cn != cn.lower():
                add("LOW", "MIXED_CASE_NAME", tn, cn, f'column "{cn}" is quoted mixed case: every query must quote it', "use snake_case", line=ln)
            if cn.lower().endswith("_id") and cname not in t["pk"] and not any(f["table"] == key and cname in f["cols"] for f in sc.fks):
                stem = cn.lower()[:-3]
                cands = [x for x in (stem, stem + "s", stem + "es", stem[:-1] + "ies" if stem.endswith("y") else None) if x and x in table_names]
                if cands:
                    add("LOW", "MISSING_FOREIGN_KEY", tn, cn, f"{cn} looks like a reference to {cands[0]} but has no foreign key: orphans and typos are possible", f"add REFERENCES {cands[0]}(id) (with an index)", line=ln)
            if re.search(r"(^|_)email$", cn, re.I) and [cname] in t["uniques"] and dialect == "postgres" and bt != "citext":
                if not any(i["table"] == key and any("lower(" in c2 for c2 in i["cols"]) for i in sc.indexes):
                    add("LOW", "CASE_SENSITIVE_UNIQUE_EMAIL", tn, cn, "UNIQUE on email is case-sensitive in Postgres: Ana@x.com and ana@x.com can both sign up", "CREATE UNIQUE INDEX ... ON t (lower(email)) or use citext", line=ln)
    # foreign keys: types and indexes
    for f in sc.fks:
        t = sc.tables.get(f["table"])
        r = sc.tables.get(f["ref"])
        if not t:
            continue
        ref_cols = f["ref_cols"] or (r["pk"] if r else None)
        if r and ref_cols and len(ref_cols) == len(f["cols"]):
            for c, rc in zip(f["cols"], ref_cols):
                a, b = t["cols"].get(c), r["cols"].get(rc)
                if a and b and base_type(a["type"]) != base_type(b["type"]) and not ({base_type(a["type"]), base_type(b["type"])} <= {"uuid"}):
                    add("HIGH", "FK_TYPE_MISMATCH", t["name"], a["name"], f"{a['name']} is {a['type']} but references {r['name']}.{b['name']} ({b['type']}): joins need casts and the FK fails once ids pass the smaller range",
                        f"make {a['name']} the same type as {r['name']}.{b['name']}", line=t["line"])
        if dialect == "postgres":
            covered = any(idx["table"] == f["table"] and not idx["partial"] and idx["cols"][:len(f["cols"])] == f["cols"] for idx in sc.indexes) \
                or t["pk"][:len(f["cols"])] == f["cols"] or any(u[:len(f["cols"])] == f["cols"] for u in t["uniques"])
            if not covered:
                add("MEDIUM", "FK_WITHOUT_INDEX", t["name"], ", ".join(f["cols"]), f"foreign key ({', '.join(f['cols'])}) -> {f['ref']} has no index starting with it: deletes/updates on {f['ref']} scan {t['name']}, and joins are slow",
                    f"CREATE INDEX CONCURRENTLY ON {qi(t['name'])} ({', '.join(f['cols'])})", line=t["line"])
    # redundant indexes
    for i, a in enumerate(sc.indexes):
        t = sc.tables.get(a["table"])
        if not t or a["partial"] or any("(" in c for c in a["cols"]):
            continue
        others = [b for j, b in enumerate(sc.indexes) if j != i and b["table"] == a["table"] and not b["partial"]]
        if t["pk"] and a["cols"] == t["pk"][:len(a["cols"])] and not a["unique"]:
            add("LOW", "REDUNDANT_INDEX", t["name"], ", ".join(a["cols"]), f"index on ({', '.join(a['cols'])}) duplicates the primary key's leading columns", "drop it", line=a["line"])
            continue
        for b in others:
            if len(b["cols"]) > len(a["cols"]) and b["cols"][:len(a["cols"])] == a["cols"] and not a["unique"]:
                add("LOW", "REDUNDANT_INDEX", t["name"], ", ".join(a["cols"]), f"index on ({', '.join(a['cols'])}) is a prefix of the index on ({', '.join(b['cols'])})", "drop the shorter one unless it is much smaller and hot", line=a["line"])
                break
            if b["cols"] == a["cols"] and j_before(sc.indexes, b, a) and a["unique"] == b["unique"]:
                add("LOW", "REDUNDANT_INDEX", t["name"], ", ".join(a["cols"]), f"two indexes on ({', '.join(a['cols'])})", "drop one", line=a["line"])
                break
    out.sort(key=lambda f: (SEV_ORDER[f["severity"]], f["table"], f["column"], f["code"]))
    return dialect, sc, out


def j_before(lst, b, a):
    return lst.index(b) < lst.index(a)


def report(path, dialect, sc, found):
    counts = {s: sum(1 for f in found if f["severity"] == s) for s in SEV_ORDER}
    out = [f"# Schema review: {path} ({dialect})", "",
           f"Tables: {len(sc.tables)}   Indexes: {len(sc.indexes)}   Foreign keys: {len(sc.fks)}   Findings: {len(found)} (HIGH {counts['HIGH']}, MEDIUM {counts['MEDIUM']}, LOW {counts['LOW']})", ""]
    if not found:
        out.append("No findings.")
        return "\n".join(out)
    out += ["| Sev | Code | Table.Column | Problem | Fix |", "|---|---|---|---|---|"]
    for f in found:
        loc = f["table"] + ("." + f["column"] if f["column"] else "")
        out.append(f"| {f['severity']} | {f['code']} | {loc} | {f['problem'].replace('|', '/')} | {f['fix'].replace('|', '/')} |")
    return "\n".join(out)


def self_test():
    ok_pk = "CREATE TABLE t (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, created_at timestamptz NOT NULL DEFAULT now()"
    cases = [
        ("clean postgres table", ok_pk + ", name text NOT NULL);", "postgres", [], ["NO_PRIMARY_KEY", "INT32_PRIMARY_KEY", "NO_CREATED_AT"]),
        ("no primary key", "CREATE TABLE logs (msg text, created_at timestamptz NOT NULL);", "auto", ["NO_PRIMARY_KEY"], []),
        ("table-level composite pk", "CREATE TABLE m (a bigint, b bigint, created_at timestamptz NOT NULL, PRIMARY KEY (a, b));", "auto", [], ["NO_PRIMARY_KEY"]),
        ("named pk constraint", "CREATE TABLE m (id bigint, created_at timestamptz NOT NULL, CONSTRAINT m_pk PRIMARY KEY (id));", "auto", [], ["NO_PRIMARY_KEY"]),
        ("pk added by alter", "CREATE TABLE m (id bigint NOT NULL, created_at timestamptz NOT NULL); ALTER TABLE m ADD CONSTRAINT m_pkey PRIMARY KEY (id);", "auto", [], ["NO_PRIMARY_KEY"]),
        ("serial pk is int32", "CREATE TABLE orders (id serial PRIMARY KEY, created_at timestamptz NOT NULL);", "auto", ["INT32_PRIMARY_KEY"], []),
        ("small lookup table int pk ok", "CREATE TABLE order_status (id smallint PRIMARY KEY, created_at timestamptz NOT NULL);", "auto", [], ["INT32_PRIMARY_KEY"]),
        ("float money", ok_pk + ", total_price double precision);", "postgres", ["FLOAT_MONEY"], []),
        ("numeric money ok", ok_pk + ", amount numeric(12,2) NOT NULL);", "postgres", [], ["FLOAT_MONEY"]),
        ("float non-money ok", ok_pk + ", latitude double precision);", "postgres", [], ["FLOAT_MONEY"]),
        ("money type", ok_pk + ", balance money);", "postgres", ["MONEY_TYPE"], []),
        ("timestamp without tz", ok_pk + ", shipped_at timestamp);", "postgres", ["TIMESTAMP_WITHOUT_TZ"], []),
        ("timestamp(3) without time zone", ok_pk + ", shipped_at timestamp(3) without time zone);", "postgres", ["TIMESTAMP_WITHOUT_TZ"], []),
        ("timestamptz ok", ok_pk + ", shipped_at timestamp with time zone);", "postgres", [], ["TIMESTAMP_WITHOUT_TZ"]),
        ("nullable created_at", "CREATE TABLE t (id bigint PRIMARY KEY, created_at timestamptz);", "postgres", ["NULLABLE_AUDIT_COLUMN"], []),
        ("no created_at", "CREATE TABLE t (id bigint PRIMARY KEY, name text);", "postgres", ["NO_CREATED_AT"], []),
        ("nullable boolean", ok_pk + ", is_active boolean);", "postgres", ["NULLABLE_BOOLEAN"], []),
        ("boolean not null ok", ok_pk + ", is_active boolean NOT NULL DEFAULT true);", "postgres", [], ["NULLABLE_BOOLEAN"]),
        ("unchecked status", ok_pk + ", status varchar(20) NOT NULL);", "postgres", ["UNCHECKED_STATUS"], []),
        ("status with check ok", ok_pk + ", status text NOT NULL CHECK (status IN ('a','b')));", "postgres", [], ["UNCHECKED_STATUS"]),
        ("status table check ok", ok_pk + ", status text NOT NULL, CONSTRAINT st CHECK (status IN ('a')));", "postgres", [], ["UNCHECKED_STATUS"]),
        ("reserved table name", "CREATE TABLE \"user\" (id bigint PRIMARY KEY, created_at timestamptz NOT NULL);", "postgres", ["RESERVED_WORD_NAME"], []),
        ("reserved column order", ok_pk + ", \"order\" int);", "postgres", ["RESERVED_WORD_NAME"], []),
        ("mixed case quoted", "CREATE TABLE \"OrderItems\" (id bigint PRIMARY KEY, created_at timestamptz NOT NULL);", "postgres", ["MIXED_CASE_NAME"], []),
        ("fk without index", "CREATE TABLE a (id bigint PRIMARY KEY, created_at timestamptz NOT NULL); CREATE TABLE b (id bigint PRIMARY KEY, created_at timestamptz NOT NULL, a_id bigint NOT NULL REFERENCES a(id));", "postgres", ["FK_WITHOUT_INDEX"], ["FK_TYPE_MISMATCH"]),
        ("fk with index ok", "CREATE TABLE a (id bigint PRIMARY KEY, created_at timestamptz NOT NULL); CREATE TABLE b (id bigint PRIMARY KEY, created_at timestamptz NOT NULL, a_id bigint REFERENCES a); CREATE INDEX ON b (a_id, id);", "postgres", [], ["FK_WITHOUT_INDEX"]),
        ("fk covered by composite pk", "CREATE TABLE a (id bigint PRIMARY KEY, created_at timestamptz NOT NULL); CREATE TABLE ab (a_id bigint REFERENCES a(id), x bigint, created_at timestamptz NOT NULL, PRIMARY KEY (a_id, x));", "postgres", [], ["FK_WITHOUT_INDEX"]),
        ("fk type mismatch", "CREATE TABLE a (id bigint PRIMARY KEY, created_at timestamptz NOT NULL); CREATE TABLE b (id bigint PRIMARY KEY, created_at timestamptz NOT NULL, a_id integer, FOREIGN KEY (a_id) REFERENCES a (id));", "postgres", ["FK_TYPE_MISMATCH"], []),
        ("missing fk on _id", "CREATE TABLE customers (id bigint PRIMARY KEY, created_at timestamptz NOT NULL); CREATE TABLE orders (id bigint PRIMARY KEY, created_at timestamptz NOT NULL, customer_id bigint NOT NULL);", "postgres", ["MISSING_FOREIGN_KEY"], []),
        ("external _id without table ok", ok_pk + ", stripe_id text);", "postgres", [], ["MISSING_FOREIGN_KEY"]),
        ("case-sensitive unique email", ok_pk + ", email text NOT NULL UNIQUE);", "postgres", ["CASE_SENSITIVE_UNIQUE_EMAIL"], []),
        ("lower(email) index ok", ok_pk + ", email text NOT NULL UNIQUE); CREATE UNIQUE INDEX t_email ON t (lower(email));", "postgres", [], ["CASE_SENSITIVE_UNIQUE_EMAIL"]),
        ("redundant prefix index", ok_pk + ", a bigint, b bigint); CREATE INDEX i1 ON t (a); CREATE INDEX i2 ON t (a, b);", "postgres", ["REDUNDANT_INDEX"], []),
        ("json in text", ok_pk + ", payload text);", "postgres", ["JSON_IN_TEXT"], []),
        ("varchar 255 habit", ok_pk + ", a varchar(255), b varchar(255), c varchar(255));", "postgres", ["VARCHAR_255_HABIT"], []),
        ("mysql myisam and utf8", "CREATE TABLE `t` (`id` int unsigned NOT NULL AUTO_INCREMENT, `created_at` datetime NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;", "auto", ["MYISAM_ENGINE", "MYSQL_UTF8MB3", "INT32_PRIMARY_KEY", "DATETIME_NO_ZONE"], ["TIMESTAMP_WITHOUT_TZ"]),
        ("mysql utf8mb4 innodb ok", "CREATE TABLE `t` (`id` bigint unsigned NOT NULL AUTO_INCREMENT, `created_at` datetime NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;", "auto", [], ["MYISAM_ENGINE", "MYSQL_UTF8MB3", "INT32_PRIMARY_KEY"]),
        ("mysql fk needs no extra index", "CREATE TABLE a (id bigint PRIMARY KEY, created_at datetime NOT NULL) ENGINE=InnoDB; CREATE TABLE b (id bigint PRIMARY KEY, created_at datetime NOT NULL, a_id bigint, FOREIGN KEY (a_id) REFERENCES a(id)) ENGINE=InnoDB;", "mysql", [], ["FK_WITHOUT_INDEX"]),
        ("comments and strings ignored", "-- CREATE TABLE x (a int);\n/* CREATE TABLE y (b int); */\n" + ok_pk + ", note text DEFAULT 'a;b -- c');", "postgres", [], ["NO_PRIMARY_KEY"]),
        ("alter add column parsed", ok_pk + "); ALTER TABLE t ADD COLUMN total_amount float;", "postgres", ["FLOAT_MONEY"], []),
    ]
    passed = 0
    for name, sql, dialect, expect, absent in cases:
        d, sc, found = lint(sql, dialect)
        codes = {f["code"] for f in found}
        ok = all(c in codes for c in expect) and not any(c in codes for c in absent)
        if name == "clean postgres table":
            ok = ok and not found
        if name == "comments and strings ignored":
            ok = ok and len(sc.tables) == 1
        if name.startswith("mysql myisam"):
            ok = ok and d == "mysql"
        passed += ok
        print(("PASS " if ok else "FAIL ") + name + ("" if ok else f" -> got {sorted(codes)}"))
    d, sc, found = lint("", "auto")
    ok = report("x.sql", d, sc, found).endswith("No findings.")
    passed += ok
    print(("PASS " if ok else "FAIL ") + "empty report")
    total = len(cases) + 1
    print(f"{passed}/{total} passed")
    return 0 if passed == total else 1


def main(argv=None):
    ap = argparse.ArgumentParser(description="SQL schema design linter (Postgres/MySQL DDL)")
    ap.add_argument("files", nargs="*")
    ap.add_argument("--dialect", default="auto", choices=["auto", "postgres", "mysql"])
    ap.add_argument("--min-severity", default="LOW", choices=list(SEV_ORDER))
    ap.add_argument("--ignore", default="")
    ap.add_argument("--json", action="store_true")
    ap.add_argument("--self-test", action="store_true")
    a = ap.parse_args(argv)
    if a.self_test:
        return self_test()
    if not a.files:
        ap.print_usage(sys.stderr)
        return 2
    ignore = {c.strip() for c in a.ignore.split(",") if c.strip()}
    sql_all = []
    for p in a.files:
        try:
            with open(p, encoding="utf-8") as fh:
                sql_all.append(fh.read())
        except OSError as e:
            print(f"error: cannot read {p}: {e}", file=sys.stderr)
            return 2
    dialect, sc, found = lint("\n;\n".join(sql_all), a.dialect, ignore)
    if not sc.tables:
        print("error: no CREATE TABLE statements found", file=sys.stderr)
        return 2
    found = [f for f in found if SEV_ORDER[f["severity"]] <= SEV_ORDER[a.min_severity]]
    if a.json:
        print(json.dumps({"dialect": dialect, "tables": len(sc.tables), "findings": found}, indent=2))
    else:
        print(report(", ".join(a.files), dialect, sc, found))
    return 1 if any(f["severity"] == "HIGH" for f in found) else 0


if __name__ == "__main__":
    sys.exit(main())

Backend Python SQL+2

F@f