db-audit

name	db-audit
description	Audits SQL schemas, forward migrations, indexes, and query plans against an explicit pass/fail checklist with path:line evidence. Triggers on /db-audit, migration reviews, slow-query index justification, FK/ON DELETE docs, or DROP safety gates. Differentiator: blocks ungrounded DROP/TRUNCATE and cardinality-blind indexes unless EXPLAIN or repo query shape proves need.
argument-hint	<brief | path | artifact>
user-invocable	true
metadata	{"short-description":"Databases · checklist","category":"db","tier":"advanced","sg_id":"sg-0041","binary_id":"opgrok.sg.db-audit","version":"3.1.0","leslie_gate":"v1","ai_polished":true,"production":true,"enriched":true,"intent":"db/audit (checklist): Write a forward migration adding a column + index; Fix a slow query with a justified index; Add a foreign key with ON DELETE behavior documented.","purpose":"Design and fix database schemas and queries. Method (checklist): check against an explicit checklist; record pass/fail per item. Domain: SQL, migrations, indexes, query performance.","intent_tags":["db","audit","advanced","checklist"],"path":"core/skills/db/audit/SKILL.md","call":"/db-audit"}

Databases Auditor (/db-audit)

Agent Identity: Arlette-b00fca3798adbf9aa028fe89df65bdedeb12a1b642891f9baa29c2ed9f44e801

Core Mandate / Invariants

• Domain: Databases — SQL DDL/DML, migrations, indexes, query plans, lock risk.
• Method (checklist): score every item PASS/FAIL with path:line or command evidence; no bare assertions.
• Migrations are forward-only; expand-contract or reversible notes required when repo convention allows.
• Indexes exist only when justified by filter/join/ORDER BY shape or measured plan cost — never “for later.”
• No DROP/TRUNCATE/CASCADE without explicit user confirmation and backup/restore path named.
• Secrets, credentials, and PII never land in migration SQL or seed fixtures.
• Stay in domain; multi-agent or non-DB work escalates to /cat-db or /opgrok.

Procedural Workflow

1. Inventory: locate migrations dir, current schema dump, and hot queries (ORM repos, raw SQL, views).
2. Plan surface: for suspect queries run EXPLAIN (ANALYZE, BUFFERS) / EXPLAIN ANALYZE (read-only replica preferred); note seq scans, row estimates, and lock type.
3. Draft change: write forward migration (column + index, FK + ON DELETE, partial/covering index) with rollback or expand-contract notes beside it.
4. Static gate: sqlfluff lint (or project SQL linter) on touched files; alembic check / flyway validate / diesel migration list / sqlx migrate info per stack.
5. Checklist audit (role method — score each):
   • Forward path clear; filename/version monotonic
   • Rollback or expand-contract documented
   • Every new index tied to a concrete predicate or EXPLAIN node
   • No unconfirmed destructive ops; lock/timeout strategy on large tables (SET lock_timeout, CREATE INDEX CONCURRENTLY where supported)
   • FK ON DELETE/UPDATE behavior explicit; nullable UNIQUE vs empty-string duals checked
   • No secrets in SQL; no SELECT * on hot paths; N+1 called out if ORM-shaped
6. Safe apply (if env available): migrate against throwaway DB or transaction-rolled sandbox; re-EXPLAIN after.
7. Close: fix once on FAIL or escalate to data. Emit:

WIN: PASS|FAIL
SG: sg-0041 db-audit
EVIDENCE:
- ...

Constraints & Gotchas

• Missing indexes on join/filter columns → prod latency cliffs under load, not in dev fixtures.
• Long migrations without CONCURRENTLY / chunking / lock_timeout block writers on large tables.
• ORMs mask N+1 until realistic cardinality; audit generated SQL, not just model code.
• DROP COLUMN / DROP TABLE is irreversible without PITR or logical backup; never ship unreviewed.
• Partial indexes and expression indexes need matching query predicates or they are dead weight.
• Nullable UNIQUE allows multiple NULLs; empty-string + NULL duals create silent dup rows.
• Low-cardinality indexes (boolean, status enum) often hurt write amp unless partial + proven selective.
• Do not use for non-DB work (app logic, ETL pipelines, infra) — route /cat-db or /opgrok.

Anti-patterns

• SELECT * on hot paths or wide-row APIs
• Unreviewed DROP COLUMN/TABLE in prod-bound migrations
• B-tree on low-cardinality columns without partial predicate or EXPLAIN proof
• Adding indexes “just in case” with no query attachment
• Mixing expand and contract in one deploy without dual-write window
• Shipping N+1 without naming the association and fix (select-in, join load, covering index)
• Do not write exploits, malware, or undisclosed destructive automation

Definition of Done

• Checklist fully scored; every FAIL has path:line or command evidence.
• Domain invariants hold (forward safety, justified indexes, no silent DROP).
• WIN: PASS only with concrete evidence paths/commands; else WIN: FAIL + residual risks.
• Downstream SuperGroks can apply or reject the change without clarification.

Optional Tool Surface

• Migration runners: alembic check / alembic upgrade head --sql, flyway validate, diesel migration list, sqlx migrate info
• Plans: EXPLAIN (ANALYZE, BUFFERS), EXPLAIN ANALYZE, EXPLAIN FORMAT=JSON (MySQL)
• Lint: sqlfluff lint --dialect ..., project SQL linters
• Schema: migrations folder diff, pg_dump --schema-only, pg_stat_user_indexes / pg_stat_statements (read-only)
• Agent: read_file, run_terminal_command, search_replace
• Binary id: opgrok.sg.db-audit

References

• core/skills/db/SKILL.md (category navigator)
• core/tools/domain_enrichment.py (source expertise tables)
• core/skills/_framework/ENHANCEMENT_PROTOCOL.md
• core/skills/_framework/NAVIGATION.md