Run any Skill in Manus with one click

db-audit

Stars6

Forks0

UpdatedJune 2, 2026 at 15:55

Database performance and safety audit. 70+ checks across 13 dimensions (DB1-DB13): query patterns, indexes, schema design, connections, transactions, migrations, caching, query optimization, ORM anti-patterns, observability, data lifecycle, DB security, and migration deployment safety. Code-level checks for all ORMs. Optional live analysis via PostgreSQL or MySQL connection. Switches: zuvo:db-audit full | [path] | [file] | --schema | --queries | --connections | --live <conn>

Installation

Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.

Run Skill in Manus

Source

greglas75

greglas75/zuvo

View GitHub Repository View Creator Repositories

Download

Run Skill in Manus

Related occupationsSOC

Based on SOC occupation classification

Database ArchitectsComputer and Mathematical Occupations·SOC 15-1243

SKILL.md

readonly

zuvo:db-audit

Audit database interactions from code patterns through schema design to live query plans. Produces a scored report with specific, actionable fixes ranked by impact and effort.

When to use: Before releases, after adding models or queries, when latency increases, after scaling incidents, periodic health check. When NOT to use: Code quality (zuvo:review), full-stack performance (zuvo:performance-audit), security-only (/security-audit).

Mandatory File Loading

Read every file below before starting. Print the checklist.

CORE FILES LOADED:
  1. ../../shared/includes/codesift-setup.md      -- [READ | MISSING -> STOP]
  2. ../../shared/includes/env-compat.md           -- [READ | MISSING -> STOP]
  3. ../../shared/includes/run-logger.md           -- [READ | MISSING -> STOP]

Deferred (lazy load):

DEFERRED FILES (read only when needed):
  - ../../shared/includes/retrospective.md  -- read right before Phase 6 (saves ~3K tokens during audit)

Note: cq-patterns.md is NOT loaded — this is a read-only audit, not a code quality review. Loading it wastes ~7K tokens per turn.

If any CORE file is MISSING, STOP. Do not proceed from memory.

MANDATORY TOOL CALLS — Audit Validity Gate

This audit is INVALID if any of the tools below are skipped when their trigger condition holds. "DEFERRED", "N/A", "no diff vs prior audit" are NOT valid reasons. The presence of trigger artifacts (migrations directory, .sql files, ORM schema, etc.) is what dictates the call — not whether they changed since the last audit.

Required tool list

Tool	Trigger	Reason	Skip allowed?
`sql_audit`	Project has any `.sql` file (migrations, schema, dumps) anywhere under `TARGET_ROOT`	DB6/DB12/DB13 — bundles 5 gates (drift, orphan, lint, dml, complexity) that no manual scan reproduces	NO — audit FAILS if skipped while trigger holds
`analyze_schema`	Same as `sql_audit` (`.sql` files exist)	DB3 schema design — extracts tables/columns/FKs/relationships, generates ERD for executive summary	NO when `.sql` exists
`diff_migrations`	`migrations/` dir exists (any ORM/framework)	DB6/DB13 deployment safety — classifies every op as additive/modifying/destructive with risk ranking; surfaces destructive ops missed by `sql_audit lint` gate	NO when migrations exist
`trace_query`	At least one HIGH/MEDIUM finding mentions a table OR `sql_audit orphan` gate flags any orphan	DB3/DB13 verification — confirms zero references for "orphan" claim and traces every cited table across DDL/DML/FK/ORM (Prisma + Drizzle)	NO when condition holds
`search_columns`	Always	DB12 PII discovery — find every `email`/`password`/`ssn`/`token` column across all tables	NO — always required
`migration_lint`	Postgres detected (any of: `pg`, `psycopg2`, `@prisma/adapter-pg`, `postgres-js` in deps) AND `migrations/` dir exists	DB13 migration deployment safety (squawk: 30+ PG-specific patterns including `NOT NULL` without default, `CREATE INDEX` without `CONCURRENTLY`, etc.)	NO when both conditions hold
`analyze_prisma_schema`	`prisma/schema.prisma` exists	DB2/DB3/DB6 Prisma-specific schema gates (FK index coverage %, unindexed FKs, soft-delete detection, `status: String` smell)	NO when schema exists
`explain_query`	Prisma project AND any HIGH/MEDIUM finding cites a `prisma.<model>.<call>` query	DB1/DB2 Prisma-specific N+1 + missing-index detection via simulated EXPLAIN ANALYZE; finds risks `sql_audit dml` cannot see	NO when condition holds
`python_audit`	Language detected as Python	DB1 N+1 detection (`n-plus-one-django` pattern), DB9 ORM anti-patterns	NO when Python project
`nest_audit`	Framework detected as NestJS (`@nestjs/*` in deps)	DB1/DB4 NestJS DI + repository scoping issues	NO when NestJS project
`analyze_django_settings` + `get_model_graph`	`django` in pyproject/requirements	DB6 Django migration safety, DB3 model graph	NO when Django
`scan_secrets`	Always	DB12 hardcoded credentials in code or `.env`	NO — always required
`search_patterns(pattern="unbounded-findmany")` + `search_patterns(pattern="await-in-loop")` + `search_patterns(pattern="toctou")`	Always	DB1/DB5 — these are the ONLY tool-verified gates for those patterns	NO — always required

Pre-flight check (run BEFORE any phase)

Before Phase 0, verify the required tools are reachable:

# Detect triggers
sql_files=$(find TARGET_ROOT -name "*.sql" -not -path "*/node_modules/*" -not -path "*/.git/*" | head -1)
migrations_dir=$(find TARGET_ROOT -type d -name "migrations" -not -path "*/node_modules/*" | head -1)
prisma_schema=$([ -f TARGET_ROOT/prisma/schema.prisma ] && echo "yes" || echo "no")

For each trigger that holds, the matching tool MUST be reachable before Phase 1.

Do NOT treat absence from the session-start deferred-tools banner as "unreachable." The CodeSift SQL toolchain (sql_audit, analyze_schema, diff_migrations, trace_query, search_columns, analyze_prisma_schema, migration_lint, explain_query) is non-core (is_core: false) — these tools are never in the banner, yet are reachable on demand. The banner lists only deferred-but-known schemas; CodeSift's ~95 hidden tools sit behind a reveal step and will never appear there. Aborting on "not in banner" is a false alarm and is what forced prior runs into unnecessary degraded mode.

For each held trigger, resolve the tool in this order — abort ONLY at the last step:

In preloaded list already? (from codesift-setup.md Step 2.5 ToolSearch — the sql group is auto-included when .sql files exist.) → use it.
Not preloaded? Attempt to reveal it before concluding anything:
- Claude Code: ToolSearch(query="select:mcp__codesift__<tool>") (or re-run Step 2.5 preload with the full union).
- Codex/other CodeSift hosts: describe_tools(names=["<tool>"], reveal=true).
Reveal succeeded? (schema returned / tool now in list) → use it. This is the expected path for a CodeSift-backed session.
Reveal genuinely failed — and only then ABORT. Genuine failure means ONE of:
- the reveal mechanism itself is unavailable (ToolSearch/describe_tools not present — i.e. CodeSift not connected at all), or
- the reveal call confirms the tool does not exist in this CodeSift version (e.g. CodeSift older than v0.4.x for sql_audit).
On genuine failure:
- Print [ABORT] Required tool '<name>' not reachable after reveal attempt. db-audit cannot produce a valid report without it.
- Do NOT proceed with grep fallback. The audit is incomplete by definition.
- Exit with status INCOMPLETE and add a backlog item: [BLOCKER] db-audit needs <tool> on <project>.

Required POSTAMBLE — retrospective append (NOT optional)

After the audit report is written and the Run line is appended, the audit is NOT complete until the retrospective protocol has appended an entry to BOTH ~/.zuvo/retros.log AND ~/.zuvo/retros.md. Reaching the Run line is not the end of the skill — it is the midpoint between findings-output and process-feedback.

This is the failure mode the 2026-04-09 → 2026-05-04 db-audit history shows: 11 db-audit runs, 0 retrospective entries written. Every prior run reached the Run line, declared "task done", and skipped retrospective.md entirely. The skipped retros lost ~11 sessions of skill feedback that would otherwise have caught the very issues this MANDATORY section now enforces.

Hard requirement, in this order, before considering db-audit complete:

Write zuvo/audits/db-audit-<date>.md.
Print Validity Gate block.
Print Run line + append to runs.log.
Load ../../shared/includes/retrospective.md if not already loaded.
Fill all 9 retrospective fields per protocol.
Execute the bash append commands that write RETRO: line to ~/.zuvo/retros.log and the long-form entry to ~/.zuvo/retros.md.
Print confirmation: RETRO_APPENDED: retros.log=YES retros.md=YES (verified).

If you reach step 3 and stop — the audit is INVALID regardless of finding count. The Validity Gate's gate_status flips to FAIL — retrospective not appended and the verdict is overridden to INCOMPLETE.

Forbidden escape hatches

The following telemetry values are forbidden when the trigger condition holds:

Value	Forbidden when	Required value instead
`sql_audit: DEFERRED`	`.sql` files exist	`sql_audit: <gates_passed>/<gates_run>`
`sql_audit: N/A`	`.sql` files exist	(same as above)
`sql_audit: skipped (no diff vs prior)`	EVER	(same as above — trigger is presence, not delta)
`migration_lint: DEFERRED`	Postgres + migrations exist	`migration_lint: <findings>`
`scan_secrets: DEFERRED`	EVER	`scan_secrets: <count>`
`codesift: unavailable`	`mcp__codesift__*` was in deferred-tools session-start banner	`codesift: deferred-not-preloaded (FAILURE: skill required preload)`
`retrospective: skipped`	EVER	`retrospective: appended (retros.log=N entries, retros.md=N bytes added)` — see Required POSTAMBLE above
Stopping after Run line without retrospective	EVER	Not allowed — the Validity Gate catches this and overrides to INCOMPLETE

Audit completion verification (run BEFORE writing PASS/WARN/FAIL status)

At the end of the audit, before emitting the status block:

Re-check each trigger condition.
For each held trigger, verify the corresponding tool was actually called in this session (the LLM must self-report honestly — there is no automated post-execution gate yet, so use the run log as ground truth).
If ANY required tool is missing for a held trigger:
- Override the verdict to INCOMPLETE regardless of finding count.
- Print: [VALIDITY GATE FAIL] <tool> required by <trigger>, not called. Audit cannot be trusted.
- Add the gap to the backlog as B-db-audit-incomplete-<date>.

A db-audit that says "0 critical findings" while skipping sql_audit on a project with 34 migrations is lying, not passing. The completion gate exists to catch that.

Argument Parsing

Token	Behavior
(empty) or `full`	All 13 dimensions across the project (auto-decides mode in Phase 0.4)
`[path]`	Scope to a directory or module
`[file]`	Deep audit of a single file (all applicable dimensions)
`--schema`	Schema analysis only (DB2, DB3, DB6)
`--queries`	Query pattern analysis only (DB1, DB8, DB9)
`--connections`	Connection and pool management only (DB4)
`--live <conn>`	Enable Phase 3: connect to the database for EXPLAIN and statistics
`--force-full`	Skip audit mode decision in Phase 0.4 — always run full audit even if a recent prior audit exists
`--delta`	Force delta mode (only allowed if commits_since < 5 AND hours_since < 4 — see Phase 0.4)

Safety Gates

GATE 1 -- Read-Only

This audit is read-only. The only write target is zuvo/audits/.

FORBIDDEN:

Running any migration
Modifying schema files, model files, or ORM configuration
Executing INSERT/UPDATE/DELETE against any database
Modifying connection strings or pool settings

GATE 2 -- Live Mode Scoping

When --live <conn> is used:

Only SELECT and EXPLAIN queries are permitted
No DDL (CREATE, ALTER, DROP)
No DML (INSERT, UPDATE, DELETE)
Connection must be read-only if the database supports read replicas

Phase 0: Detect and Prepare

0.0 CodeSift Capability Check

If CodeSift MCP is available, run these two calls before anything else:

get_extractor_versions() — check if the project's primary language has a full parser (symbol-level tools) or only text-stub support. If text-stub only: skip all symbol-based CodeSift calls (search_symbols, get_file_outline, trace_call_chain, find_references) and use Grep/Read fallbacks instead. Print the result.
analyze_project() — returns detected stack (framework, language, package manager, monorepo), file classifications, dependency counts, and git health. Use the output to pre-fill ORM, Engine, and Deployment detection below instead of manual file scanning.

If analyze_project returns enough to populate the stack table, skip 0.1/0.2/0.3 manual detection and jump to the output block. If it returns partial data (e.g. framework=null), fill the gaps with the manual tables below.

Worktree de-pollution (do this before ANY file/table/migration count). If the repo has git worktrees checked out under the tree (.worktrees/, .claude/worktrees/, worktrees/), every .sql/migration/model file is duplicated N times and inflates counts (the same class as the [[repo-file-counting]] node_modules/vendor exclusion). Exclude them from every count and CodeSift scope: find ... -not -path '*/.worktrees/*' -not -path '*/.claude/worktrees/*' -not -path '*/worktrees/*' (and the standard node_modules/.git/dist/build/vendor). When a CodeSift SQL tool reports a migration/table count, sanity-check it against git worktree list — if there are W worktrees, a count that is ~W× the migrations/ file count is worktree-inflated; report the de-duplicated figure. (CodeSift SQL tools lack an exclude_pattern/--no-worktrees param — surfaced as a tool gap; until it lands, verify counts against the primary worktree only.)

0.1 ORM Detection

If not resolved by analyze_project:

Signal	ORM
`prisma/schema.prisma`	Prisma
`ormconfig.*` or `DataSource` import	TypeORM
`drizzle.config.*`	Drizzle
`.sequelizerc` or `sequelize` in deps	Sequelize
`knexfile.*` or `knex` in deps	Knex
`settings.py` with `DATABASES`	Django ORM
`sqlalchemy` in requirements	SQLAlchemy
Raw `pg`/`mysql2` without ORM	Raw SQL client

0.2 Database Engine Detection

If not resolved by analyze_project:

Signal	Engine	Managed provider
`postgresql` in connection string or schema provider	PostgreSQL	—
`mysql` in connection string or provider	MySQL	—
`sqlite` in provider	SQLite	—
`mongodb` in provider or `mongoose`	MongoDB	—
`neon.tech` in DATABASE_URL	PostgreSQL	Neon (built-in pooler)
`supabase.co` in DATABASE_URL	PostgreSQL	Supabase (built-in pooler)
`pscale.sh` or `psdb.cloud`	MySQL	PlanetScale (built-in pooler)
`cockroachlabs.cloud`	CockroachDB	Cockroach Cloud (built-in pooler)
`rds.amazonaws.com`	PostgreSQL/MySQL	AWS RDS
`azure.com` with `database` segment	PostgreSQL/MySQL/SQL Server	Azure Database
`googleapis.com` with `cloudsql`	PostgreSQL/MySQL	Cloud SQL
`mongodb.net`	MongoDB	MongoDB Atlas

Managed provider note: When a managed provider with built-in pooling is detected (Neon, Supabase, PlanetScale, Cockroach Cloud), DB4 should NOT be marked critical-fail for "no PgBouncer config" — the platform handles pooling. Mark these findings as TOOL-VERIFIED with note "managed pooling: " and pass DB4 if no other issues exist.

0.3 Deployment Detection

If not resolved by analyze_project:

Signal	Type
`vercel.json`, `netlify.toml`	Serverless (Vercel/Netlify)
`wrangler.toml`	Serverless (Cloudflare Workers)
`serverless.yml`	Serverless (AWS Lambda)
`Dockerfile`, `docker-compose`	Containerized
None of above	Traditional

Print detection results:

DB AUDIT STACK
------------------------------------
ORM:       [Prisma / TypeORM / Drizzle / Django / SQLAlchemy / Raw SQL]
Engine:    [PostgreSQL / MySQL / SQLite / MongoDB]
Deploy:    [Serverless / Container / Traditional]
Scope:     [full / path / file]
Dims:      [DB1-DB13 / subset]
CodeSift:  [full-parser / text-stub / unavailable]
------------------------------------

0.4 Audit Mode Decision

Default mode is full. Delta mode is a narrow exception, NOT a shortcut.

If --force-full was passed: mode = "full" — skip the rest of this section.

Otherwise, look for the most recent prior audit at zuvo/audits/db-audit-*.md:

PRIOR_AUDIT=$(ls -t zuvo/audits/db-audit-*.md 2>/dev/null | head -1)

if [ -z "$PRIOR_AUDIT" ]; then
  mode="full"  # baseline
else
  PRIOR_SHA=$(grep -E '^\| HEAD_SHA' "$PRIOR_AUDIT" | head -1 | awk '{print $NF}')
  PRIOR_MTIME=$(stat -f %m "$PRIOR_AUDIT" 2>/dev/null || stat -c %Y "$PRIOR_AUDIT")
  NOW=$(date +%s)
  HOURS_SINCE=$(( (NOW - PRIOR_MTIME) / 3600 ))
  COMMITS_SINCE=$(git rev-list --count "${PRIOR_SHA}..HEAD" 2>/dev/null || echo 999)

  if [ "$COMMITS_SINCE" -eq 0 ] && [ "$HOURS_SINCE" -lt 2 ]; then
    mode="sanity-check"
  elif [ "$COMMITS_SINCE" -lt 5 ] && [ "$HOURS_SINCE" -lt 4 ]; then
    mode="delta"
  else
    mode="full"
  fi
fi

Mode	When	What it does
`full`	No prior audit, OR commits_since ≥ 5, OR hours_since ≥ 4, OR `--force-full`	Independent re-evaluation of all dimensions. Default.
`delta`	commits_since < 5 AND hours_since < 4 AND prior audit exists	Verify prior findings + scan changed files only. Requires Phase 0.5 checklist.
`sanity-check`	commits_since == 0 AND hours_since < 2	Spot-verify 1-2 specific fixes. Not a full audit.

If user passed --delta but the conditions for delta are not met: print a warning and override to full. Do NOT silently honor the flag — the user's "I want delta" is overridden by methodology safety.

CRITICAL — Mode does NOT affect MANDATORY TOOL CALLS

Mode (full / delta / sanity-check) controls scope of additional analysis — which dimensions get deep-dived, which agent-dispatched explorations run, how many findings are re-examined. Mode does NOT waive any tool from the MANDATORY TOOL CALLS section above.

Specifically, in EVERY mode (including delta and sanity-check):

sql_audit MUST run if any .sql file exists.
analyze_schema MUST run if any .sql file exists (companion to sql_audit — generates ERD).
diff_migrations MUST run if migrations/ dir exists (classifies destructive ops).
search_columns MUST run (PII discovery — every audit).
scan_secrets MUST run.
search_patterns(unbounded-findmany | await-in-loop | toctou) MUST run.
migration_lint MUST run if Postgres + migrations/ dir exists. If it degrades (squawk CLI not installed — migration_lint returns a squawk_unavailable/empty result), do NOT silently pass DB13: fall back to diff_migrations + a manual DDL scan for the high-severity squawk patterns (NOT NULL without default, CREATE INDEX without CONCURRENTLY, ALTER COLUMN TYPE, DROP COLUMN) and record DB13 as WARN (migration_lint degraded — manual scan, install squawk for full coverage), never PASS.
analyze_prisma_schema MUST run if prisma/schema.prisma exists.
explain_query MUST run on every Prisma query cited in a HIGH/MEDIUM finding.
trace_query MUST run on every table cited in a HIGH/MEDIUM finding (or flagged by sql_audit orphan).
Stack-specific mandatory tools (nest_audit, python_audit, django/celery/etc.) MUST run when their language/framework is detected.

These tools ARE the audit's validity floor — without them the report cannot be trusted regardless of how small the delta is. They are also fast (single composite calls), so "delta is too small to bother" is never a defensible reason.

If you are tempted to mark any mandatory tool as DEFERRED (delta-mode, low risk) or N/A (no DB changes): STOP. That is the exact failure mode this section exists to prevent. The trigger is presence of .sql/migrations//schema.prisma/etc. — never delta or risk.

Print the decision:

AUDIT MODE: [full / delta / sanity-check]
Reason:     prior=[date or "none"] | commits_since=[N] | hours_since=[N.N]
Override:   [user --force-full | user --delta accepted | user --delta REJECTED→full | none]
Mandatory-tools-acknowledgment: I will run sql_audit + analyze_schema + diff_migrations + search_columns + scan_secrets + migration_lint (if PG) + analyze_prisma_schema (if Prisma) + search_patterns(unbounded-findmany, await-in-loop, toctou) + stack-specific mandatory tools (nest_audit/python_audit/etc. when detected) + trace_query + explain_query (on cited tables/queries) in this mode. [REQUIRED — print verbatim]

0.5 Delta Verification Checklist

Skip this section if mode != "delta".

When mode == "delta", you MUST complete every item below before writing the report. Any skipped item forces mode = "full" and restart from Phase 1.

DELTA VERIFICATION CHECKLIST
[ ] git diff --name-only <prior_sha>..HEAD  → list changed files (CHANGED_FILES)
[ ] scan_secrets on CHANGED_FILES (NEVER skip, even if DB12 was 4/4 in prior)
[ ] For every finding from prior audit:
      → 1× codebase_retrieval batch call (do NOT iterate per-finding)
[ ] For every severity downgrade you propose (M→L, H→M):
      → find_references on the symbol → document the count
      → DOWNGRADE BLOCKED without count evidence in the report
[ ] For every endpoint mentioned in any finding:
      → trace_route to confirm hot-path / cold-path status
[ ] For every finding with a matching docs/specs/*-plan.md reference:
      → tag as PLANNED (not HIGH/MEDIUM)

Why this checklist exists: previous delta audits inherited prior assumptions and silently propagated errors. Severity downgrades without evidence, skipped scans on changed files, and untraced endpoint claims are the four most common delta-mode failures. This checklist eliminates them.

If at any point during the audit you find yourself thinking "the prior audit already covered this," STOP — that's the anchoring bias the checklist is designed to break. Run the verification.

Phase 1: Schema Analysis

Skip if: no schema file and no migration directory found. Mark DB2, DB3, DB6 as INSUFFICIENT DATA.

1.1 Schema Inventory

Read the schema source for the detected ORM and extract:

Item	What to Count
Models/tables	Total count
Fields per model	Average and maximum
Relations	1:1, 1:N, N:M counts
Indexes	Count per model, which fields
Unique constraints	Count per model
Defaults	Fields with/without default values
Nullable fields	Count and distribution
Enums vs string	Enum definitions vs raw string status/type fields

CodeSift accelerated (Prisma): When CodeSift has a Prisma parser (check Phase 0.0), use symbol-level tools instead of reading the entire schema file:

# Get all models, enums, and types at a glance
get_file_outline(file_path="prisma/schema.prisma")

# Search for specific model patterns
search_symbols(query="@@index", file_pattern="*.prisma", include_source=true)
search_symbols(query="@@unique", file_pattern="*.prisma", include_source=true)

# For large schemas (>500 lines), use assemble_context instead of Read:
assemble_context(query="prisma models with relations", level="L1", token_budget=8000)

This replaces reading a 500-1500 line schema file in full, saving ~5-10K tokens.

ORM-specific sources (manual fallback):

Prisma: prisma/schema.prisma -- @@index, @@unique, @default, ? nullable
TypeORM: Entity files -- @Column, @Index, @JoinColumn, @ManyToOne
Django: models.py -- Field types, class Meta indexes, ForeignKey
Drizzle: Schema files -- index(), unique(), references()
SQLAlchemy: Model files -- Column, Index, ForeignKey, UniqueConstraint

1.2 External Index Detection

Before scoring DB2 as "zero indexes", scan for indexes defined outside the ORM:

SQL scripts with CREATE INDEX outside migration directories
MongoDB shell scripts with createIndex or ensureIndex
Standalone index management files

If found, inventory those indexes and add a DB2.10 finding: indexes managed outside ORM/migrations are not reproducible on fresh environments.

1.3 Migration Analysis (DB6)

Read the last 10 migration files and flag:

CREATE INDEX without CONCURRENTLY (PostgreSQL)
ALTER COLUMN SET NOT NULL without prior default
ALTER COLUMN TYPE (type changes on populated tables)
DROP COLUMN or DROP TABLE without soft-delete strategy
Missing down/reverse migration (except Prisma, which is forward-only by design)

1.4 Model Inventory Output

MODEL INVENTORY
| Model | Fields | Relations | Indexes | Uniques | Issues |
|-------|--------|-----------|---------|---------|--------|
| User  | 12     | 3         | 2       | 1       | Missing FK index on orgId |
| Order | 18     | 5         | 1       | 0       | No unique for idempotency |

Phase 2: Code-Level Analysis (DB1-DB13)

2.0 CodeSift Pre-Scan

Before dispatching agents or running manual analysis, run these automated checks when CodeSift is available. They replace ~20 manual Grep calls and provide TOOL-VERIFIED findings.

2.0a — Generic anti-pattern scans

# DB1: N+1 and unbounded query detection (automated)
search_patterns(pattern="unbounded-findmany")     # findMany without take/limit
search_patterns(pattern="await-in-loop")           # sequential await in loop = N+1

# DB5: Race condition pre-scan
search_patterns(pattern="toctou")                  # read-then-write without atomic op

# DB12: Secret exposure (hidden tool — reveal first)
# Claude Code: ToolSearch("select:mcp__codesift__scan_secrets")
# Codex/other: describe_tools(names=["scan_secrets"], reveal=true)
scan_secrets(min_confidence="medium")              # hardcoded DB passwords, connection strings

If scan_secrets is unavailable, fall back to: Grep for password=, DATABASE_URL=, connection_string, API keys in .env committed to git.

2.0b — SQL composite audit (`sql_audit`) — MANDATORY when `.sql` files exist

REQUIRED CALL. If find TARGET_ROOT -name "*.sql" -not -path "*/node_modules/*" returns ≥1 file, you MUST call sql_audit in this phase. There is no condition under which "skipped" is acceptable on a .sql-bearing repo: not "no diff vs prior", not "DEFERRED", not "low risk this run". The 5 internal gates are independent of delta — they re-run every time and re-validate the schema↔ORM mapping from scratch. Skipping = audit invalid (see MANDATORY TOOL CALLS section above).

# Claude Code: ToolSearch("select:mcp__codesift__sql_audit")
# Codex/other: describe_tools(names=["sql_audit"], reveal=true)
sql_audit()                                        # runs all 5 gates: drift, orphan, lint, dml, complexity

Map each gate to the corresponding DB dimension:

sql_audit gate	Maps to	What it catches
`drift`	DB13 (migration deploy safety)	Prisma↔SQL field/type mismatches — "forgot to run migration" bugs
`orphan`	DB3 (schema design)	Tables defined in SQL with zero references in code or ORM
`lint`	DB2 + DB3	Missing PK, wide tables (>20 cols), duplicate index names
`dml`	DB12 (DB security)	DELETE/UPDATE without WHERE (data loss), SELECT * (unbounded)
`complexity`	DB3 (schema design)	God tables: column count + FK count + index count score ≥25

For finer control, run a subset of gates: sql_audit({ checks: ["drift", "dml"] }).

The sql_audit result has shape:

{
  "gates": [
    { "check": "drift", "pass": false, "critical": true, "finding_count": 3, "summary": "3 drifts: 2 extra in ORM, 0 extra in SQL, 1 type mismatches", "data": {...} },
    { "check": "orphan", "pass": true, ... },
    ...
  ],
  "summary": { "total_findings": 12, "critical_findings": 1, "gates_run": 5, "gates_passed": 2, "gates_failed": 3 }
}

Pass each gate's findings to the corresponding DB dimension scoring as TOOL-VERIFIED evidence. Critical gates (drift with type_mismatches > 0, dml with high-severity findings) propagate to the matching DB critical gate (DB13, DB12).

MANDATORY false-positive verification for dml DELETE/UPDATE-without-WHERE (before it counts toward DB12). A hallucinated CRITICAL in a read-only safety audit destroys trust in the whole report — in a 2026-05 run all 4 "CRITICAL dml" hits were false positives that would have flipped a passing DB to FAIL. For EVERY dml DELETE/UPDATE-without-WHERE hit, Read the cited file:line through line+5 and confirm no WHERE clause follows before counting it. The two dominant false-positive shapes:

ORM builder chains where .where() is a separate call: db.delete(t).where(eq(...)), db.update(t).set({...}).where(...) (Drizzle), repo.delete({...}) / qb.delete().where(...) (TypeORM) — the WHERE is real, just not adjacent to the verb token.
Multi-line raw SQL: DELETE FROM x on one line, WHERE ... on the next — a single-line regex misses it. Only a hit with NO WHERE within the read window (and not an ORM builder chain that applies one downstream) is a true CRITICAL. Record the verification in the finding evidence (verified file:line, no WHERE in [line..line+5]). Add this shape to the False Positive Filters used in scoring.

If sql_audit is unavailable (CodeSift older than v0.4.x or no .sql files), skip 2.0b and rely on the manual schema/migration analysis in Phase 1 + agent dispatch.

2.0c — Additional SQL query tools (optional)

When deeper investigation is needed for specific findings:

Tool	When to use
`analyze_schema`	Generate ERD (Mermaid) for the executive summary section
`trace_query(table)`	MANDATORY for every "orphan" finding from `sql_audit` and every table cited in HIGH/MEDIUM findings — verify zero references across `.ts`/`.py`/`.go`/`.kt`/Prisma/Drizzle
`search_columns(query)`	MANDATORY for DB12 PII discovery — find all `email`/`password`/`ssn`/`token`/`secret`/`hash` columns. Run with empty query first to inventory PII surface, then targeted queries for specific concerns.
`diff_migrations`	MANDATORY when `migrations/` exists — DB6/DB13 destructive op classification. Reports `additive`/`modifying`/`destructive` counts. Now in MANDATORY TOOL CALLS section above.
`analyze_schema(output_format='mermaid')`	MANDATORY when `.sql` exists — DB3 schema inventory + ERD for executive summary.
`explain_query(code='prisma.<model>.<call>(...)')`	MANDATORY for every Prisma query cited in HIGH/MEDIUM finding — DB1/DB2 simulated EXPLAIN ANALYZE catches N+1 from `include`, unbounded `findMany`, missing indexes that `sql_audit` cannot see (Prisma-only).

Previously these were "drill down only" — promoted to mandatory after the 2026-05-04 audit on tgm-survey-platform showed sql_audit alone misses ~30% of issues that surface when paired with diff_migrations + analyze_schema + trace_query.

If CodeSift is entirely unavailable, skip Phase 2.0 and proceed directly to Agent Dispatch — agents will use Grep/Read.

Collect results from 2.0a + 2.0b. Pass them into agent prompts as "pre-verified findings" (HIGH confidence, tool-verified). Agents should NOT re-scan for these patterns — they should verify context and discover patterns the automated scan missed.

Agent Dispatch

Refer to env-compat.md for the dispatch pattern.

When parallel dispatch is available:

Agent	Dimensions	Focus
Schema Analyst	DB2, DB3, DB6, DB13	Schema design + migration safety + deploy safety
Query Scanner	DB1, DB5, DB8, DB9	Code-level query patterns
Infrastructure Auditor	DB4, DB7, DB10, DB11, DB12	Connections, cache, observability, security

Agent prompt rules:

CodeSift tool loading — include this block at the very top of every agent prompt so tools are callable:

FIRST: Load CodeSift tools before doing anything else.
- Claude Code: Run ToolSearch("select:mcp__codesift__search_text,mcp__codesift__search_symbols,mcp__codesift__codebase_retrieval,mcp__codesift__trace_route,mcp__codesift__find_references,mcp__codesift__get_file_outline,mcp__codesift__trace_call_chain,mcp__codesift__search_patterns,mcp__codesift__assemble_context")
- Codex: Call mcp__codesift__search_text directly — MCP tools are pre-registered.
- Cursor/Antigravity: CodeSift unavailable — use Grep/Read.
If any tool call fails, fall back to Grep/Read.

Adjust the tool list per agent role — Schema Analyst needs get_file_outline + search_symbols + sql_audit + analyze_schema + search_columns; Query Scanner needs trace_route + codebase_retrieval + search_patterns + trace_query; Infrastructure Auditor needs search_text + find_references + diff_migrations (for DB13 destructive op review).

Token budget: Each agent must keep its report under 800 words. Structured as: findings list (ID, severity, file:line, 1-sentence description) + 1-paragraph summary. No prose explanations per finding.

CodeSift cheat sheet — include right after the tool loading block:

CodeSift: batch 3+ searches → codebase_retrieval(queries=[...]).
Endpoints → trace_route first. Skip list_repos (auto-resolve).
If empty results → fallback to Grep (parser may be unavailable).

Pre-verified findings: Pass Phase 2.0 results to agents with instruction: "These findings are TOOL-VERIFIED. Do not re-scan for them. Focus on patterns the pre-scan cannot catch."

Without parallel dispatch: Execute all dimensions sequentially.

DB1: Query Patterns -- Weight 15, Max 15, Critical Gate

Check	Good	Bad	Severity
N+1 queries	Eager loading (`include`, `joinedload`), batched IDs	`findMany` / `find` inside a loop	CRITICAL
Select efficiency	`select` only needed fields	`SELECT *` / no select clause	HIGH
Bulk operations	`createMany`, `updateMany`, bulk insert	Individual create/update in loop	HIGH
Raw query safety	Parameterized queries (`$queryRaw` with template, `%s` params)	String concatenation in SQL	CRITICAL

Critical gate: DB1=0 (N+1 in hot path) triggers audit FAIL.

DB2: Index Strategy -- Weight 15, Max 15

Check	Good	Bad	Severity
FK indexes	Every foreign key has an index	FK columns without index	HIGH
Composite indexes	Multi-column indexes for common query patterns	Single-column indexes on individually queried fields	MEDIUM
Covering indexes	Index includes all fields for frequent queries	Extra lookups required	LOW
Unused indexes	All indexes serve active queries	Indexes that are never hit	MEDIUM

DB3: Schema Design -- Weight 8, Max 8

Check	Good	Bad	Severity
Normalization	Appropriate normal form, no data duplication	Same data stored in multiple tables	HIGH
Enum usage	Database enums or constrained strings for status/type	Arbitrary strings without validation	MEDIUM
Timestamps	`createdAt`/`updatedAt` on mutable models, `deletedAt` for soft delete	No audit trail	MEDIUM
Naming conventions	Consistent naming (snake_case or camelCase), clear foreign key names	Mixed conventions, ambiguous names	LOW

DB4: Connection Management -- Weight 10, Max 10, Critical Gate

Check	Good	Bad	Severity
Connection pooling	Pool configured with min/max, singleton client	New client per request	CRITICAL
Serverless awareness	External pooler (PgBouncer, Supabase pooler) for serverless	Direct connection from Lambda/Worker	CRITICAL
Connection limits	Pool size matches deployment (serverless: small, container: tuned)	Default unlimited	HIGH
Client instantiation	Single PrismaClient/DataSource instance	Multiple `new PrismaClient()` calls	HIGH

Critical gate: DB4=0 (no pooling) triggers audit FAIL.

DB5: Transaction Safety -- Weight 12, Max 12, Critical Gate

Check	Good	Bad	Severity
Multi-table mutations	Wrapped in transaction	Separate writes without transaction	CRITICAL
Transaction scope	Minimal scope, no external API calls inside	HTTP request or email send inside transaction	HIGH
Rollback handling	Explicit error handling, compensation logic	Silent swallow on transaction failure	HIGH
Deadlock prevention	Consistent lock ordering, timeout on transactions	Arbitrary ordering, no timeout	MEDIUM

Critical gate: DB5=0 (multi-table mutations without transaction) triggers FAIL.

DB6: Migration Safety -- Weight 8, Max 8

Check	Good	Bad	Severity
Non-blocking DDL	`CREATE INDEX CONCURRENTLY`, `ADD COLUMN` with default	Locking index creation on large table	HIGH
Data migration	Separate data migration from schema migration	Mixed DDL and DML in one migration	MEDIUM
Reversibility	Down migrations exist and tested (non-Prisma ORMs)	No rollback path	MEDIUM
Type changes	Multi-step migration for type changes (add new, migrate, drop old)	Direct `ALTER TYPE` on populated column	HIGH

DB7: Caching Layer -- Weight 8, Max 8

Check	Good	Bad	Severity
Query result cache	Redis/Memcached for expensive/repeated queries, TTL configured	Every request hits database	HIGH
Cache invalidation	Event-driven or TTL with jitter	Manual invalidation, no TTL	MEDIUM
Cache-aside pattern	Read-through with fallback to DB on miss	All-or-nothing cache (miss = error)	MEDIUM
Cache key design	Includes tenant/org scope, versioned	Global keys, no scoping	MEDIUM

DB8: Query Optimization -- Weight 10, Max 10

Check	Good	Bad	Severity
Pagination	Cursor-based for large datasets, keyset pagination	OFFSET pagination on growing table	HIGH
LIKE queries	Prefix match only, full-text search for complex needs	`%term%` LIKE on unindexed column	MEDIUM
Function on column	Avoid function calls on indexed columns in WHERE	`WHERE LOWER(email) = ...` (defeats index)	MEDIUM
Sorting	Sort on indexed column	Sort on computed/unindexed column for large result	MEDIUM

DB9: ORM-Specific Anti-Patterns -- Weight 6, Max 6

Patterns vary by detected ORM:

Prisma: $queryRawUnsafe, missing select on deep includes, findMany without take, $transaction with long-running operations.

TypeORM: Lazy relations without awareness, find() without select, QueryBuilder without parameter binding.

Django: N+1 via object.related_set.all() without select_related/ prefetch_related, .count() on unevaluated queryset.

SQLAlchemy: Lazy loading N+1, session.query() without limit, missing yield_per for large result sets.

DB10: Observability -- Weight 4, Max 4

Check	Good	Bad	Severity
Query logging	Structured logging with query duration, parameterized	No query logging in production	MEDIUM
Slow query alerting	Threshold-based alerts (> 1s), dashboard	No monitoring for slow queries	MEDIUM
Connection metrics	Pool utilization tracked, alerts on exhaustion	No visibility into connection state	LOW

DB11: Data Lifecycle -- Weight 4, Max 4

Check	Good	Bad	Severity
Archival strategy	Old data archived or partitioned	Unbounded table growth	MEDIUM
Batch processing	Batch deletes/updates with limits, off-peak scheduling	Full-table operations during peak hours	MEDIUM
Soft delete	`deletedAt` pattern with default scope excluding deleted	Hard delete without audit trail	LOW

DB12: Database Security -- Weight 4, Max 4, Critical Gate

Check	Good	Bad	Severity
SQL injection	All queries parameterized, no string concatenation	`$queryRawUnsafe` or string-built SQL	CRITICAL
Least privilege	Application user has minimal permissions	App connects as superuser	HIGH
Connection encryption	SSL/TLS required in connection string	Plaintext database connection	HIGH
Sensitive data	PII encrypted at rest, column-level encryption for secrets	Plaintext passwords or tokens in DB	CRITICAL

Critical gate: DB12=0 (SQL injection) triggers audit FAIL.

DB13: Migration Deployment Safety -- Weight 8, Max 8

Goes beyond DB6 (migration code quality) to assess whether migrations can be deployed safely to a running production system.

Check	Good	Bad	Severity
Destructive operations	`DROP COLUMN`/`DROP TABLE` preceded by deprecation migration, data backed up	Direct `DROP` on populated columns without prior migration to remove usage	CRITICAL
Lock duration estimation	Short-lived locks: `ADD COLUMN` with default (PG 11+), `CREATE INDEX CONCURRENTLY`	`ALTER TABLE` operations that acquire `ACCESS EXCLUSIVE` lock on large tables (>100K rows)	CRITICAL
Rollback plan	Down migration exists and tested, or forward-fix strategy documented	No rollback path — failed migration leaves DB in inconsistent state	HIGH
Data loss risk	`NOT NULL` additions have `DEFAULT` value, type changes preserve data	`ALTER COLUMN SET NOT NULL` without default on populated table, truncating type changes	CRITICAL
Backward compatibility	New columns nullable or with defaults (old app version still works), rename = add+copy+drop	Column renames or type changes that break currently-deployed app code	HIGH
Idempotency	Migrations use `IF NOT EXISTS`, `IF EXISTS` guards	Migrations fail on re-run (no idempotency — partial failure leaves broken state)	HIGH
Migration ordering	Migrations numbered/timestamped, no conflicts in team branches	Multiple migrations with same timestamp, or migrations that depend on unapplied predecessors	MEDIUM
Long-running DML	Data backfills use batched updates with `LIMIT` and sleep between batches	Single `UPDATE` on millions of rows (locks table, blocks queries, risks timeout)	HIGH
Connection impact	Migration runs outside connection pool, or uses dedicated migration connection	Migration runs through application pool, potentially exhausting connections during deploy	MEDIUM
Zero-downtime readiness	Migration + app deploy order documented, blue-green or rolling deploy compatible	Migration requires app downtime — schema and app must change simultaneously	HIGH

How to audit:

Read all migration files in the migrations directory (last 20 if >20 exist)
For each migration, classify operations:
- SAFE: ADD COLUMN (nullable or with default), CREATE TABLE, CREATE INDEX CONCURRENTLY
- CAUTION: ADD COLUMN NOT NULL with default (PG 11+ safe, older = table rewrite), ALTER COLUMN SET DEFAULT
- DANGEROUS: DROP COLUMN, DROP TABLE, ALTER COLUMN TYPE, ALTER COLUMN SET NOT NULL without default
- BLOCKING: CREATE INDEX without CONCURRENTLY, ALTER TABLE on large table without estimated lock time
For DANGEROUS/BLOCKING operations, check:
- Is there a prior migration removing code references to dropped columns?
- Is there a rollback migration?
- Is the table large enough to cause lock contention (estimate from schema relations)?
Check deployment documentation for migration strategy

Scoring:

0 DANGEROUS ops without safeguards = 8/8
Each unguarded DANGEROUS op: -2
Each BLOCKING op without CONCURRENTLY: -1
No rollback path for any destructive migration: -2
No idempotency guards: -1

N/A: If no migration files found, DB13=N/A.

Phase 3: Live Analysis (optional --live)

Skip unless --live <conn> was provided. Requires database client access.

Supported engines: PostgreSQL (full), MySQL (partial). SQLite/MongoDB: skip Phase 3, rely on code-level analysis.

3.1 PostgreSQL Live Queries

-- Top 20 slow queries (requires pg_stat_statements)
SELECT query, calls, mean_exec_time, total_exec_time, rows
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

-- Unused indexes
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Cache hit ratio
SELECT sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0)
  AS cache_hit_ratio
FROM pg_statio_user_tables;

-- Table bloat
SELECT schemaname, relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables WHERE n_dead_tup > 10000 ORDER BY n_dead_tup DESC;

-- Active connections by state
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

3.2 MySQL Live Queries

-- Slow queries via performance_schema
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;

-- Unused indexes
SELECT * FROM sys.schema_unused_indexes;

-- Active connections
SHOW PROCESSLIST;

3.3 Unsupported Engines

If the engine is not PostgreSQL or MySQL, print: "Live analysis requires PostgreSQL or MySQL. Skipping Phase 3." Mark Phase 3 as SKIPPED.

Phase 4: Verification and Scoring

4.1 False Positive Filters

Pattern	Skip When
`findMany` without `take`	Inside admin tool, migration script, or seed file
N+1 loop	Loop is bounded (< 10 items) AND commented as intentional
`await-in-loop` (search_patterns hit)	Function contains `*_CONCURRENCY_LIMIT`, `BATCH_SIZE`, `CHUNK_SIZE`, or similar bounded-concurrency constant
`await-in-loop` (search_patterns hit)	Loop body is inside a seed/migration/admin script (path contains `seed/`, `scripts/`, `migrations/`, `tools/`)
`await-in-loop` (search_patterns hit)	Loop is wrapped by `pLimit`, `Promise.all` with chunks, `pAll`, `bottleneck`, or similar concurrency limiter
`await-in-loop` (search_patterns hit)	Loop is bounded by literal `< 10` and the bound is visible in source
Missing index	Table known to be small (< 1000 rows, documented)
No indexes in ORM	External index scripts found (use external inventory for scoring)
No connection pool	Using managed service that pools for you (Neon, Supabase, PlanetScale, Cockroach Cloud — see Phase 0.2)
Raw SQL flagged	Uses tagged template `$queryRaw` (safe, parameterized)

Anti-noise rule for await-in-loop: If search_patterns(pattern="await-in-loop") returns more than 10 hits, do NOT report each one as a finding. Instead, group them by file, identify which match the false-positive filters above, and report only the residual count with the worst 3 examples. Reporting 30+ "potential N+1" findings without filtering = noise that drowns signal.

4.2 Severity Classification

Level	Criteria
CRITICAL	Data loss, connection exhaustion, SQL injection, OOM in production
HIGH	User-visible latency, missing safety guard, scalability wall
MEDIUM	Performance debt, suboptimal pattern, maintenance risk
LOW	Recommendation, best practice gap, future-proofing

4.3 Cross-Dimension Correlations

Flag compound patterns:

Pattern	Dimensions	Impact
N+1 on unindexed column	DB1+DB2	Exponential degradation
Unbounded query without cache on large table	DB1+DB7+DB11	OOM risk
External API call inside transaction without timeout	DB5+DB4	Connection pool exhaustion
Serverless + no external pooler + multiple client instances	DB4+deployment	Connection storm
OFFSET pagination on growing table	DB8+DB11	Degrading page load times

4.4 Scoring

Score each dimension per the rubric. Calculate weighted total.

Critical gate check:

DB1=0 (N+1 in hot path) -> FAIL
DB4=0 (no connection pooling) -> FAIL
DB5=0 (multi-table mutations without transaction) -> FAIL
DB12=0 (SQL injection) -> FAIL

Any critical gate = 0 overrides the overall grade to FAIL.

Grade calculation (excluding N/A dimensions):

Grade	Percentage
A	>= 85%
B	70-84%
C	50-69%
D	< 50%

Phase 5: Report

Save to: zuvo/audits/db-audit-[YYYY-MM-DD].md — at the project root (zuvo/ resolves via git rev-parse --show-toplevel; override $ZUVO_OUTPUT_DIR. See ../../shared/includes/report-output-location.md).

REQUIRED: emit the Tool Availability Block (template in ../../shared/includes/codesift-setup.md) at the top of the report, after the title and before findings. Auditing degraded runs depends on this — do NOT skip it.

Report Structure

# Database Audit Report

## Metadata
| Field | Value |
|-------|-------|
| Project | [name] |
| Date | [YYYY-MM-DD] |
| ORM | [detected ORM] |
| Engine | [detected engine] |
| Deployment | [detected deployment type] |
| Scope | [full / path / file] |
| Live analysis | [enabled / skipped] |
| CodeSift | [full-parser / text-stub / unavailable] |
| Prior audit | [date or "none — baseline"] |

## Executive Summary

**Score: [N] / [MAX]** -- [A/B/C/D or FAIL]

| Metric | Count |
|--------|-------|
| CRITICAL findings | N |
| HIGH findings | N |
| MEDIUM findings | N |
| LOW findings | N |

[2-3 sentence summary]

## Dimension Scores

| # | Dimension | Score | Max | Notes |
|---|-----------|-------|-----|-------|
| DB1 | Query Patterns | [N] | 15 | |
| DB2 | Index Strategy | [N] | 15 | |
| DB3 | Schema Design | [N] | 8 | |
| DB4 | Connection Mgmt | [N] | 10 | |
| DB5 | Transaction Safety | [N] | 12 | |
| DB6 | Migration Safety | [N] | 8 | |
| DB7 | Caching Layer | [N] | 8 | |
| DB8 | Query Optimization | [N] | 10 | |
| DB9 | ORM Anti-Patterns | [N] | 6 | |
| DB10 | Observability | [N] | 4 | |
| DB11 | Data Lifecycle | [N] | 4 | |
| DB12 | DB Security | [N] | 4 | |
| DB13 | Migration Deploy Safety | [N] | 8 | |
| **Total** | | **[N]** | **[M]** | |

## Critical Gate Status
[DB1, DB4, DB5, DB12 -- PASS/FAIL per gate]

## Model Inventory
[From Phase 1]

## Findings (sorted by severity)

Per finding:
- **ID:** DB{dimension}-{NNN} (e.g. DB1-001)
- **Severity:** CRITICAL / HIGH / MEDIUM / LOW
- **Status:** NEW / RESOLVED / PARTIAL / PLANNED / REGRESSION (see status rules below)
- **Confidence:** TOOL-VERIFIED (from Phase 2.0 pre-scan) / HIGH / MEDIUM
- **File:line:** exact location
- **Description:** 1 sentence
- **Fix:** concrete code change or command
- **Effort:** S (<1h) / M (1-4h) / L (4h+)
- **Evidence:** required for any severity downgrade — `find_references` count, `trace_route` hot/cold path, etc.

### Status rules

| Status | When to use | Counted in severity totals? |
|--------|-------------|----------------------------|
| `NEW` | First time this finding appears | Yes |
| `RESOLVED` | Prior finding no longer present (verified, not assumed) | No (moved to "Resolved" section) |
| `PARTIAL` | Part of prior issue is fixed, part remains. Severity stays at original level with `(partial)` suffix — DO NOT downgrade. | Yes, at original severity |
| `PLANNED` | Finding has a linked task in `docs/specs/*-plan.md` or `memory/backlog.md`. Tracked separately. | No (moved to "Planned" section) |
| `REGRESSION` | Finding was RESOLVED in prior audit but reappeared | Yes, severity = max(prior, current) |

**Severity downgrade rule:** A finding's severity may only be lowered (e.g. M→L) when ALL of these are true:
1. The Evidence field includes a concrete count from `find_references` or `trace_route`
2. The downgrade reason is documented in 1 sentence in the Description
3. If `mode == "delta"`, the Phase 0.5 checklist was completed for this specific finding

If any of the three conditions fail, keep the prior severity. Anchoring to a number you didn't independently verify is the #1 cause of audit drift.

## Delta from Prior Audit

If a prior `zuvo/audits/db-audit-*.md` exists, include:

| Finding | Prior status | Current status | Change |
|---------|-------------|----------------|--------|
| DB1-001 | CRITICAL | RESOLVED | Fixed in [commit] |
| DB2-003 | HIGH | HIGH | Still open |
| DB9-001 | — | NEW | First detected |

Score delta: [prior score] → [current score] ([+/-N])

If no prior audit exists, print: "No prior audit found — baseline established."

## Cross-Cutting Patterns
[Compound patterns found]

## Top 5 Action Items

Per item: priority (P0/P1/P2), effort (S/M/L), blast radius (N files), concrete action.

## Delete These Tomorrow

Actionable commands for findings that require zero design decisions — just execute:

```bash
# Example format:
# DB1-003: Add take: 100 to unbounded findMany
# File: src/services/user.service.ts:45

# DB2-001: Add missing FK index
# npx prisma migrate dev --name add_org_id_index

# DB12-002: Remove hardcoded connection string
# Move to .env: DATABASE_URL=...

List only findings with effort=S. If none qualify, omit this section.

Backlog Entries

[/backlog add commands for HIGH+ findings]


### Report Validation

After writing, verify:
- Dimension scores sum to total in Executive Summary
- Finding counts match Executive Summary (counted: NEW + PARTIAL + REGRESSION; not counted: RESOLVED + PLANNED)
- All models from inventory are addressed
- Critical gate status is accurate
- Every finding has a DB{N}-{NNN} ID, severity, status, confidence, file:line, and effort
- Every severity downgrade has Evidence field populated (find_references count or trace_route output)
- PLANNED findings have a verified link to `docs/specs/*-plan.md` or `memory/backlog.md`
- PARTIAL findings keep prior severity with `(partial)` suffix — no downgrade
- Delta section references correct prior audit (or states "baseline")
- TOOL-VERIFIED findings match Phase 2.0 pre-scan output
- "Delete These Tomorrow" only contains effort=S items
- If `mode == "delta"`: Phase 0.5 checklist is fully completed (all 6 items checked)

---

## Phase 6: Next-Step Routing

RECOMMENDED NEXT ACTION

DB1 CRITICAL (N+1) -> zuvo:refactor [service file] DB4 no connection pool -> direct fix (add pool config) DB2 missing indexes -> direct migration (add indexes) DB12 SQL injection -> /security-audit [path] DB13 unsafe migrations -> rewrite migrations with CONCURRENTLY, rollbacks, batch DML DB9 ORM anti-patterns -> zuvo:refactor [service file] Multiple dimensions fail -> zuvo:review [path]


---

## Completion Gate Check

Before printing the final output block, verify every item. Unfinished items = pipeline incomplete.

COMPLETION GATE CHECK [ ] ORM + engine + deployment type detected and printed [ ] N+1 query detection ran (DB1 critical gate) [ ] Unbounded query detection ran (DB1/DB7) [ ] Migration safety audit ran (DB6) [ ] Critical gates printed: DB1, DB2, DB4, DB6, DB7, DB10, DB11 [ ] Report saved to zuvo/audits/ [ ] Backlog updated for HIGH+ findings [ ] Run: line printed and appended to log


## DB-AUDIT COMPLETE

Score: [N] / [MAX] -- [grade]
ORM: [detected] | Engine: [detected]
Dimensions: [N scored] | Critical gates: [PASS/FAIL]
Findings: [N critical] / [N total]

### Validity Gate (REQUIRED — print BEFORE Run line)


**The Validity Gate must be printed AFTER step 6 of the Required POSTAMBLE (after retros append), not before.** Printing it before retro append guarantees `retros_*_appended: NOT_APPENDED`.

If `gate_status = FAIL`, override the VERDICT below to `INCOMPLETE` regardless of finding count, append `[VALIDITY GATE FAIL]` to the Run line NOTES column, and add a backlog item.

Run: <ISO-8601-Z>	db-audit	<project>	<N-critical>	<N-total>	<VERDICT>	-	<N>-dimensions	<NOTES>	<BRANCH>	<SHA7>	<INCLUDES>	<TIER>


### Retrospective (REQUIRED)

**Load now (deferred):** Read `../../shared/includes/retrospective.md` if not already loaded.

Follow the retrospective protocol from `retrospective.md`.
Gate check → structured questions → TSV emit → markdown append.
If gate check skips: print "RETRO: skipped (trivial session)" and proceed.

**Append via wrapper (REQUIRED).** Never `>>` directly to `~/.zuvo/runs.log` — the wrapper is the gate that verifies a retro entry exists for this run. Order: retro bash executed → wrapper invoked → completion claimed.

```bash
printf '%b\n' "$RUN_LINE" | ~/.zuvo/append-runlog

Expected stdout: OK: appended to runs.log (retro verified for <skill> on <project>). If exit 2 with RETRO_REQUIRED — go execute the retro bash from retrospective.md first; never bypass with ZUVO_SKIP_RETRO_GATE=1. After the wrapper succeeds, print a Logs: evidence line (tail -1 ~/.zuvo/retros.log, grep -c "^" ~/.zuvo/retros.md, tail -1 ~/.zuvo/runs.log) before claiming completion. Printing the markdown retro section without executing the bash leaves all three log files empty.

VERDICT: PASS (0 critical findings), WARN (1-3 critical), FAIL (4+ critical).

Execution Notes

All commands use the resolved TARGET_ROOT from argument parsing
CodeSift integration follows codesift-setup.md -- use indexed search when available, fall back to Grep/Read/Glob when not
Agent dispatch follows env-compat.md -- parallel when supported, sequential otherwise
ORM detection determines which anti-pattern checks to run (DB9 is ORM-specific)
Live analysis (Phase 3) is strictly opt-in and read-only
Prisma does not use down migrations -- do not flag this as an issue

name	db-audit
description	Database performance and safety audit. 70+ checks across 13 dimensions (DB1-DB13): query patterns, indexes, schema design, connections, transactions, migrations, caching, query optimization, ORM anti-patterns, observability, data lifecycle, DB security, and migration deployment safety. Code-level checks for all ORMs. Optional live analysis via PostgreSQL or MySQL connection. Switches: zuvo:db-audit full \| [path] \| [file] \| --schema \| --queries \| --connections \| --live <conn>
codesift_tools	{"always":["analyze_project","index_status","index_folder","index_file","plan_turn","search_patterns","audit_scan","get_file_tree","get_file_outline","search_text","search_symbols","get_symbol","get_symbols","find_references","trace_call_chain"],"by_stack":{"typescript":["get_type_info","resolve_constant_value"],"javascript":[],"python":["python_audit","analyze_async_correctness","resolve_constant_value"],"php":["php_project_audit","php_security_scan","resolve_php_namespace"],"kotlin":["analyze_sealed_hierarchy","find_extension_functions","trace_flow_chain","trace_suspend_chain","trace_compose_tree","analyze_compose_recomposition","trace_hilt_graph","trace_room_schema","analyze_kmp_declarations","extract_kotlin_serialization_contract"],"nestjs":["nest_audit"],"nextjs":["framework_audit","nextjs_route_map"],"astro":["astro_audit","astro_actions_audit","astro_hydration_audit"],"hono":["analyze_hono_app","audit_hono_security"],"express":[],"fastify":[],"react":["react_quickstart","analyze_hooks","analyze_renders"],"django":["analyze_django_settings","effective_django_view_security","taint_trace","get_model_graph"],"fastapi":["trace_fastapi_depends","get_pydantic_models"],"flask":["find_framework_wiring"],"jest":[],"yii":["resolve_php_service"],"prisma":["analyze_prisma_schema","explain_query"],"drizzle":[],"sql":["sql_audit","analyze_schema","diff_migrations","trace_query","search_columns"],"postgres":["migration_lint"]}}

zuvo:db-audit

Audit database interactions from code patterns through schema design to live query plans. Produces a scored report with specific, actionable fixes ranked by impact and effort.

Mandatory File Loading

Read every file below before starting. Print the checklist.

CORE FILES LOADED:
  1. ../../shared/includes/codesift-setup.md      -- [READ | MISSING -> STOP]
  2. ../../shared/includes/env-compat.md           -- [READ | MISSING -> STOP]
  3. ../../shared/includes/run-logger.md           -- [READ | MISSING -> STOP]

Deferred (lazy load):

DEFERRED FILES (read only when needed):
  - ../../shared/includes/retrospective.md  -- read right before Phase 6 (saves ~3K tokens during audit)

Note: cq-patterns.md is NOT loaded — this is a read-only audit, not a code quality review. Loading it wastes ~7K tokens per turn.

If any CORE file is MISSING, STOP. Do not proceed from memory.

MANDATORY TOOL CALLS — Audit Validity Gate

Required tool list

Tool	Trigger	Reason	Skip allowed?
`sql_audit`	Project has any `.sql` file (migrations, schema, dumps) anywhere under `TARGET_ROOT`	DB6/DB12/DB13 — bundles 5 gates (drift, orphan, lint, dml, complexity) that no manual scan reproduces	NO — audit FAILS if skipped while trigger holds
`analyze_schema`	Same as `sql_audit` (`.sql` files exist)	DB3 schema design — extracts tables/columns/FKs/relationships, generates ERD for executive summary	NO when `.sql` exists
`diff_migrations`	`migrations/` dir exists (any ORM/framework)	DB6/DB13 deployment safety — classifies every op as additive/modifying/destructive with risk ranking; surfaces destructive ops missed by `sql_audit lint` gate	NO when migrations exist
`trace_query`	At least one HIGH/MEDIUM finding mentions a table OR `sql_audit orphan` gate flags any orphan	DB3/DB13 verification — confirms zero references for "orphan" claim and traces every cited table across DDL/DML/FK/ORM (Prisma + Drizzle)	NO when condition holds
`search_columns`	Always	DB12 PII discovery — find every `email`/`password`/`ssn`/`token` column across all tables	NO — always required
`migration_lint`	Postgres detected (any of: `pg`, `psycopg2`, `@prisma/adapter-pg`, `postgres-js` in deps) AND `migrations/` dir exists	DB13 migration deployment safety (squawk: 30+ PG-specific patterns including `NOT NULL` without default, `CREATE INDEX` without `CONCURRENTLY`, etc.)	NO when both conditions hold
`analyze_prisma_schema`	`prisma/schema.prisma` exists	DB2/DB3/DB6 Prisma-specific schema gates (FK index coverage %, unindexed FKs, soft-delete detection, `status: String` smell)	NO when schema exists
`explain_query`	Prisma project AND any HIGH/MEDIUM finding cites a `prisma.<model>.<call>` query	DB1/DB2 Prisma-specific N+1 + missing-index detection via simulated EXPLAIN ANALYZE; finds risks `sql_audit dml` cannot see	NO when condition holds
`python_audit`	Language detected as Python	DB1 N+1 detection (`n-plus-one-django` pattern), DB9 ORM anti-patterns	NO when Python project
`nest_audit`	Framework detected as NestJS (`@nestjs/*` in deps)	DB1/DB4 NestJS DI + repository scoping issues	NO when NestJS project
`analyze_django_settings` + `get_model_graph`	`django` in pyproject/requirements	DB6 Django migration safety, DB3 model graph	NO when Django
`scan_secrets`	Always	DB12 hardcoded credentials in code or `.env`	NO — always required
`search_patterns(pattern="unbounded-findmany")` + `search_patterns(pattern="await-in-loop")` + `search_patterns(pattern="toctou")`	Always	DB1/DB5 — these are the ONLY tool-verified gates for those patterns	NO — always required

Pre-flight check (run BEFORE any phase)

Before Phase 0, verify the required tools are reachable:

# Detect triggers
sql_files=$(find TARGET_ROOT -name "*.sql" -not -path "*/node_modules/*" -not -path "*/.git/*" | head -1)
migrations_dir=$(find TARGET_ROOT -type d -name "migrations" -not -path "*/node_modules/*" | head -1)
prisma_schema=$([ -f TARGET_ROOT/prisma/schema.prisma ] && echo "yes" || echo "no")

For each trigger that holds, the matching tool MUST be reachable before Phase 1.

For each held trigger, resolve the tool in this order — abort ONLY at the last step:

In preloaded list already? (from codesift-setup.md Step 2.5 ToolSearch — the sql group is auto-included when .sql files exist.) → use it.
Not preloaded? Attempt to reveal it before concluding anything:
- Claude Code: ToolSearch(query="select:mcp__codesift__<tool>") (or re-run Step 2.5 preload with the full union).
- Codex/other CodeSift hosts: describe_tools(names=["<tool>"], reveal=true).
Reveal succeeded? (schema returned / tool now in list) → use it. This is the expected path for a CodeSift-backed session.
Reveal genuinely failed — and only then ABORT. Genuine failure means ONE of:
- the reveal mechanism itself is unavailable (ToolSearch/describe_tools not present — i.e. CodeSift not connected at all), or
- the reveal call confirms the tool does not exist in this CodeSift version (e.g. CodeSift older than v0.4.x for sql_audit).
On genuine failure:
- Print [ABORT] Required tool '<name>' not reachable after reveal attempt. db-audit cannot produce a valid report without it.
- Do NOT proceed with grep fallback. The audit is incomplete by definition.
- Exit with status INCOMPLETE and add a backlog item: [BLOCKER] db-audit needs <tool> on <project>.

Required POSTAMBLE — retrospective append (NOT optional)

Hard requirement, in this order, before considering db-audit complete:

Write zuvo/audits/db-audit-<date>.md.
Print Validity Gate block.
Print Run line + append to runs.log.
Load ../../shared/includes/retrospective.md if not already loaded.
Fill all 9 retrospective fields per protocol.
Execute the bash append commands that write RETRO: line to ~/.zuvo/retros.log and the long-form entry to ~/.zuvo/retros.md.
Print confirmation: RETRO_APPENDED: retros.log=YES retros.md=YES (verified).

Forbidden escape hatches

The following telemetry values are forbidden when the trigger condition holds:

Value	Forbidden when	Required value instead
`sql_audit: DEFERRED`	`.sql` files exist	`sql_audit: <gates_passed>/<gates_run>`
`sql_audit: N/A`	`.sql` files exist	(same as above)
`sql_audit: skipped (no diff vs prior)`	EVER	(same as above — trigger is presence, not delta)
`migration_lint: DEFERRED`	Postgres + migrations exist	`migration_lint: <findings>`
`scan_secrets: DEFERRED`	EVER	`scan_secrets: <count>`
`codesift: unavailable`	`mcp__codesift__*` was in deferred-tools session-start banner	`codesift: deferred-not-preloaded (FAILURE: skill required preload)`
`retrospective: skipped`	EVER	`retrospective: appended (retros.log=N entries, retros.md=N bytes added)` — see Required POSTAMBLE above
Stopping after Run line without retrospective	EVER	Not allowed — the Validity Gate catches this and overrides to INCOMPLETE

Audit completion verification (run BEFORE writing PASS/WARN/FAIL status)

At the end of the audit, before emitting the status block:

Re-check each trigger condition.
For each held trigger, verify the corresponding tool was actually called in this session (the LLM must self-report honestly — there is no automated post-execution gate yet, so use the run log as ground truth).
If ANY required tool is missing for a held trigger:
- Override the verdict to INCOMPLETE regardless of finding count.
- Print: [VALIDITY GATE FAIL] <tool> required by <trigger>, not called. Audit cannot be trusted.
- Add the gap to the backlog as B-db-audit-incomplete-<date>.

A db-audit that says "0 critical findings" while skipping sql_audit on a project with 34 migrations is lying, not passing. The completion gate exists to catch that.

Argument Parsing

Token	Behavior
(empty) or `full`	All 13 dimensions across the project (auto-decides mode in Phase 0.4)
`[path]`	Scope to a directory or module
`[file]`	Deep audit of a single file (all applicable dimensions)
`--schema`	Schema analysis only (DB2, DB3, DB6)
`--queries`	Query pattern analysis only (DB1, DB8, DB9)
`--connections`	Connection and pool management only (DB4)
`--live <conn>`	Enable Phase 3: connect to the database for EXPLAIN and statistics
`--force-full`	Skip audit mode decision in Phase 0.4 — always run full audit even if a recent prior audit exists
`--delta`	Force delta mode (only allowed if commits_since < 5 AND hours_since < 4 — see Phase 0.4)

Safety Gates

GATE 1 -- Read-Only

This audit is read-only. The only write target is zuvo/audits/.

FORBIDDEN:

Running any migration
Modifying schema files, model files, or ORM configuration
Executing INSERT/UPDATE/DELETE against any database
Modifying connection strings or pool settings

GATE 2 -- Live Mode Scoping

When --live <conn> is used:

Only SELECT and EXPLAIN queries are permitted
No DDL (CREATE, ALTER, DROP)
No DML (INSERT, UPDATE, DELETE)
Connection must be read-only if the database supports read replicas

Phase 0: Detect and Prepare

0.0 CodeSift Capability Check

If CodeSift MCP is available, run these two calls before anything else:

get_extractor_versions() — check if the project's primary language has a full parser (symbol-level tools) or only text-stub support. If text-stub only: skip all symbol-based CodeSift calls (search_symbols, get_file_outline, trace_call_chain, find_references) and use Grep/Read fallbacks instead. Print the result.
analyze_project() — returns detected stack (framework, language, package manager, monorepo), file classifications, dependency counts, and git health. Use the output to pre-fill ORM, Engine, and Deployment detection below instead of manual file scanning.

0.1 ORM Detection

If not resolved by analyze_project:

Signal	ORM
`prisma/schema.prisma`	Prisma
`ormconfig.*` or `DataSource` import	TypeORM
`drizzle.config.*`	Drizzle
`.sequelizerc` or `sequelize` in deps	Sequelize
`knexfile.*` or `knex` in deps	Knex
`settings.py` with `DATABASES`	Django ORM
`sqlalchemy` in requirements	SQLAlchemy
Raw `pg`/`mysql2` without ORM	Raw SQL client

0.2 Database Engine Detection

If not resolved by analyze_project:

Signal	Engine	Managed provider
`postgresql` in connection string or schema provider	PostgreSQL	—
`mysql` in connection string or provider	MySQL	—
`sqlite` in provider	SQLite	—
`mongodb` in provider or `mongoose`	MongoDB	—
`neon.tech` in DATABASE_URL	PostgreSQL	Neon (built-in pooler)
`supabase.co` in DATABASE_URL	PostgreSQL	Supabase (built-in pooler)
`pscale.sh` or `psdb.cloud`	MySQL	PlanetScale (built-in pooler)
`cockroachlabs.cloud`	CockroachDB	Cockroach Cloud (built-in pooler)
`rds.amazonaws.com`	PostgreSQL/MySQL	AWS RDS
`azure.com` with `database` segment	PostgreSQL/MySQL/SQL Server	Azure Database
`googleapis.com` with `cloudsql`	PostgreSQL/MySQL	Cloud SQL
`mongodb.net`	MongoDB	MongoDB Atlas

0.3 Deployment Detection

If not resolved by analyze_project:

Signal	Type
`vercel.json`, `netlify.toml`	Serverless (Vercel/Netlify)
`wrangler.toml`	Serverless (Cloudflare Workers)
`serverless.yml`	Serverless (AWS Lambda)
`Dockerfile`, `docker-compose`	Containerized
None of above	Traditional

Print detection results:

DB AUDIT STACK
------------------------------------
ORM:       [Prisma / TypeORM / Drizzle / Django / SQLAlchemy / Raw SQL]
Engine:    [PostgreSQL / MySQL / SQLite / MongoDB]
Deploy:    [Serverless / Container / Traditional]
Scope:     [full / path / file]
Dims:      [DB1-DB13 / subset]
CodeSift:  [full-parser / text-stub / unavailable]
------------------------------------

0.4 Audit Mode Decision

Default mode is full. Delta mode is a narrow exception, NOT a shortcut.

If --force-full was passed: mode = "full" — skip the rest of this section.

Otherwise, look for the most recent prior audit at zuvo/audits/db-audit-*.md:

PRIOR_AUDIT=$(ls -t zuvo/audits/db-audit-*.md 2>/dev/null | head -1)

if [ -z "$PRIOR_AUDIT" ]; then
  mode="full"  # baseline
else
  PRIOR_SHA=$(grep -E '^\| HEAD_SHA' "$PRIOR_AUDIT" | head -1 | awk '{print $NF}')
  PRIOR_MTIME=$(stat -f %m "$PRIOR_AUDIT" 2>/dev/null || stat -c %Y "$PRIOR_AUDIT")
  NOW=$(date +%s)
  HOURS_SINCE=$(( (NOW - PRIOR_MTIME) / 3600 ))
  COMMITS_SINCE=$(git rev-list --count "${PRIOR_SHA}..HEAD" 2>/dev/null || echo 999)

  if [ "$COMMITS_SINCE" -eq 0 ] && [ "$HOURS_SINCE" -lt 2 ]; then
    mode="sanity-check"
  elif [ "$COMMITS_SINCE" -lt 5 ] && [ "$HOURS_SINCE" -lt 4 ]; then
    mode="delta"
  else
    mode="full"
  fi
fi

Mode	When	What it does
`full`	No prior audit, OR commits_since ≥ 5, OR hours_since ≥ 4, OR `--force-full`	Independent re-evaluation of all dimensions. Default.
`delta`	commits_since < 5 AND hours_since < 4 AND prior audit exists	Verify prior findings + scan changed files only. Requires Phase 0.5 checklist.
`sanity-check`	commits_since == 0 AND hours_since < 2	Spot-verify 1-2 specific fixes. Not a full audit.

CRITICAL — Mode does NOT affect MANDATORY TOOL CALLS

Specifically, in EVERY mode (including delta and sanity-check):

sql_audit MUST run if any .sql file exists.
analyze_schema MUST run if any .sql file exists (companion to sql_audit — generates ERD).
diff_migrations MUST run if migrations/ dir exists (classifies destructive ops).
search_columns MUST run (PII discovery — every audit).
scan_secrets MUST run.
search_patterns(unbounded-findmany | await-in-loop | toctou) MUST run.
migration_lint MUST run if Postgres + migrations/ dir exists. If it degrades (squawk CLI not installed — migration_lint returns a squawk_unavailable/empty result), do NOT silently pass DB13: fall back to diff_migrations + a manual DDL scan for the high-severity squawk patterns (NOT NULL without default, CREATE INDEX without CONCURRENTLY, ALTER COLUMN TYPE, DROP COLUMN) and record DB13 as WARN (migration_lint degraded — manual scan, install squawk for full coverage), never PASS.
analyze_prisma_schema MUST run if prisma/schema.prisma exists.
explain_query MUST run on every Prisma query cited in a HIGH/MEDIUM finding.
trace_query MUST run on every table cited in a HIGH/MEDIUM finding (or flagged by sql_audit orphan).
Stack-specific mandatory tools (nest_audit, python_audit, django/celery/etc.) MUST run when their language/framework is detected.

Print the decision:

AUDIT MODE: [full / delta / sanity-check]
Reason:     prior=[date or "none"] | commits_since=[N] | hours_since=[N.N]
Override:   [user --force-full | user --delta accepted | user --delta REJECTED→full | none]
Mandatory-tools-acknowledgment: I will run sql_audit + analyze_schema + diff_migrations + search_columns + scan_secrets + migration_lint (if PG) + analyze_prisma_schema (if Prisma) + search_patterns(unbounded-findmany, await-in-loop, toctou) + stack-specific mandatory tools (nest_audit/python_audit/etc. when detected) + trace_query + explain_query (on cited tables/queries) in this mode. [REQUIRED — print verbatim]

0.5 Delta Verification Checklist

Skip this section if mode != "delta".

When mode == "delta", you MUST complete every item below before writing the report. Any skipped item forces mode = "full" and restart from Phase 1.

DELTA VERIFICATION CHECKLIST
[ ] git diff --name-only <prior_sha>..HEAD  → list changed files (CHANGED_FILES)
[ ] scan_secrets on CHANGED_FILES (NEVER skip, even if DB12 was 4/4 in prior)
[ ] For every finding from prior audit:
      → 1× codebase_retrieval batch call (do NOT iterate per-finding)
[ ] For every severity downgrade you propose (M→L, H→M):
      → find_references on the symbol → document the count
      → DOWNGRADE BLOCKED without count evidence in the report
[ ] For every endpoint mentioned in any finding:
      → trace_route to confirm hot-path / cold-path status
[ ] For every finding with a matching docs/specs/*-plan.md reference:
      → tag as PLANNED (not HIGH/MEDIUM)

If at any point during the audit you find yourself thinking "the prior audit already covered this," STOP — that's the anchoring bias the checklist is designed to break. Run the verification.

Phase 1: Schema Analysis

Skip if: no schema file and no migration directory found. Mark DB2, DB3, DB6 as INSUFFICIENT DATA.

1.1 Schema Inventory

Read the schema source for the detected ORM and extract:

Item	What to Count
Models/tables	Total count
Fields per model	Average and maximum
Relations	1:1, 1:N, N:M counts
Indexes	Count per model, which fields
Unique constraints	Count per model
Defaults	Fields with/without default values
Nullable fields	Count and distribution
Enums vs string	Enum definitions vs raw string status/type fields

CodeSift accelerated (Prisma): When CodeSift has a Prisma parser (check Phase 0.0), use symbol-level tools instead of reading the entire schema file:

# Get all models, enums, and types at a glance
get_file_outline(file_path="prisma/schema.prisma")

# Search for specific model patterns
search_symbols(query="@@index", file_pattern="*.prisma", include_source=true)
search_symbols(query="@@unique", file_pattern="*.prisma", include_source=true)

# For large schemas (>500 lines), use assemble_context instead of Read:
assemble_context(query="prisma models with relations", level="L1", token_budget=8000)

This replaces reading a 500-1500 line schema file in full, saving ~5-10K tokens.

ORM-specific sources (manual fallback):

Prisma: prisma/schema.prisma -- @@index, @@unique, @default, ? nullable
TypeORM: Entity files -- @Column, @Index, @JoinColumn, @ManyToOne
Django: models.py -- Field types, class Meta indexes, ForeignKey
Drizzle: Schema files -- index(), unique(), references()
SQLAlchemy: Model files -- Column, Index, ForeignKey, UniqueConstraint

1.2 External Index Detection

Before scoring DB2 as "zero indexes", scan for indexes defined outside the ORM:

SQL scripts with CREATE INDEX outside migration directories
MongoDB shell scripts with createIndex or ensureIndex
Standalone index management files

If found, inventory those indexes and add a DB2.10 finding: indexes managed outside ORM/migrations are not reproducible on fresh environments.

1.3 Migration Analysis (DB6)

Read the last 10 migration files and flag:

CREATE INDEX without CONCURRENTLY (PostgreSQL)
ALTER COLUMN SET NOT NULL without prior default
ALTER COLUMN TYPE (type changes on populated tables)
DROP COLUMN or DROP TABLE without soft-delete strategy
Missing down/reverse migration (except Prisma, which is forward-only by design)

1.4 Model Inventory Output

MODEL INVENTORY
| Model | Fields | Relations | Indexes | Uniques | Issues |
|-------|--------|-----------|---------|---------|--------|
| User  | 12     | 3         | 2       | 1       | Missing FK index on orgId |
| Order | 18     | 5         | 1       | 0       | No unique for idempotency |

Phase 2: Code-Level Analysis (DB1-DB13)

2.0 CodeSift Pre-Scan

Before dispatching agents or running manual analysis, run these automated checks when CodeSift is available. They replace ~20 manual Grep calls and provide TOOL-VERIFIED findings.

2.0a — Generic anti-pattern scans

# DB1: N+1 and unbounded query detection (automated)
search_patterns(pattern="unbounded-findmany")     # findMany without take/limit
search_patterns(pattern="await-in-loop")           # sequential await in loop = N+1

# DB5: Race condition pre-scan
search_patterns(pattern="toctou")                  # read-then-write without atomic op

# DB12: Secret exposure (hidden tool — reveal first)
# Claude Code: ToolSearch("select:mcp__codesift__scan_secrets")
# Codex/other: describe_tools(names=["scan_secrets"], reveal=true)
scan_secrets(min_confidence="medium")              # hardcoded DB passwords, connection strings

If scan_secrets is unavailable, fall back to: Grep for password=, DATABASE_URL=, connection_string, API keys in .env committed to git.

2.0b — SQL composite audit (`sql_audit`) — MANDATORY when `.sql` files exist

# Claude Code: ToolSearch("select:mcp__codesift__sql_audit")
# Codex/other: describe_tools(names=["sql_audit"], reveal=true)
sql_audit()                                        # runs all 5 gates: drift, orphan, lint, dml, complexity

Map each gate to the corresponding DB dimension:

sql_audit gate	Maps to	What it catches
`drift`	DB13 (migration deploy safety)	Prisma↔SQL field/type mismatches — "forgot to run migration" bugs
`orphan`	DB3 (schema design)	Tables defined in SQL with zero references in code or ORM
`lint`	DB2 + DB3	Missing PK, wide tables (>20 cols), duplicate index names
`dml`	DB12 (DB security)	DELETE/UPDATE without WHERE (data loss), SELECT * (unbounded)
`complexity`	DB3 (schema design)	God tables: column count + FK count + index count score ≥25

For finer control, run a subset of gates: sql_audit({ checks: ["drift", "dml"] }).

The sql_audit result has shape:

{
  "gates": [
    { "check": "drift", "pass": false, "critical": true, "finding_count": 3, "summary": "3 drifts: 2 extra in ORM, 0 extra in SQL, 1 type mismatches", "data": {...} },
    { "check": "orphan", "pass": true, ... },
    ...
  ],
  "summary": { "total_findings": 12, "critical_findings": 1, "gates_run": 5, "gates_passed": 2, "gates_failed": 3 }
}

ORM builder chains where .where() is a separate call: db.delete(t).where(eq(...)), db.update(t).set({...}).where(...) (Drizzle), repo.delete({...}) / qb.delete().where(...) (TypeORM) — the WHERE is real, just not adjacent to the verb token.
Multi-line raw SQL: DELETE FROM x on one line, WHERE ... on the next — a single-line regex misses it. Only a hit with NO WHERE within the read window (and not an ORM builder chain that applies one downstream) is a true CRITICAL. Record the verification in the finding evidence (verified file:line, no WHERE in [line..line+5]). Add this shape to the False Positive Filters used in scoring.

If sql_audit is unavailable (CodeSift older than v0.4.x or no .sql files), skip 2.0b and rely on the manual schema/migration analysis in Phase 1 + agent dispatch.

2.0c — Additional SQL query tools (optional)

When deeper investigation is needed for specific findings:

Tool	When to use
`analyze_schema`	Generate ERD (Mermaid) for the executive summary section
`trace_query(table)`	MANDATORY for every "orphan" finding from `sql_audit` and every table cited in HIGH/MEDIUM findings — verify zero references across `.ts`/`.py`/`.go`/`.kt`/Prisma/Drizzle
`search_columns(query)`	MANDATORY for DB12 PII discovery — find all `email`/`password`/`ssn`/`token`/`secret`/`hash` columns. Run with empty query first to inventory PII surface, then targeted queries for specific concerns.
`diff_migrations`	MANDATORY when `migrations/` exists — DB6/DB13 destructive op classification. Reports `additive`/`modifying`/`destructive` counts. Now in MANDATORY TOOL CALLS section above.
`analyze_schema(output_format='mermaid')`	MANDATORY when `.sql` exists — DB3 schema inventory + ERD for executive summary.
`explain_query(code='prisma.<model>.<call>(...)')`	MANDATORY for every Prisma query cited in HIGH/MEDIUM finding — DB1/DB2 simulated EXPLAIN ANALYZE catches N+1 from `include`, unbounded `findMany`, missing indexes that `sql_audit` cannot see (Prisma-only).

If CodeSift is entirely unavailable, skip Phase 2.0 and proceed directly to Agent Dispatch — agents will use Grep/Read.

Agent Dispatch

Refer to env-compat.md for the dispatch pattern.

When parallel dispatch is available:

Agent	Dimensions	Focus
Schema Analyst	DB2, DB3, DB6, DB13	Schema design + migration safety + deploy safety
Query Scanner	DB1, DB5, DB8, DB9	Code-level query patterns
Infrastructure Auditor	DB4, DB7, DB10, DB11, DB12	Connections, cache, observability, security

Agent prompt rules:

CodeSift tool loading — include this block at the very top of every agent prompt so tools are callable:

FIRST: Load CodeSift tools before doing anything else.
- Claude Code: Run ToolSearch("select:mcp__codesift__search_text,mcp__codesift__search_symbols,mcp__codesift__codebase_retrieval,mcp__codesift__trace_route,mcp__codesift__find_references,mcp__codesift__get_file_outline,mcp__codesift__trace_call_chain,mcp__codesift__search_patterns,mcp__codesift__assemble_context")
- Codex: Call mcp__codesift__search_text directly — MCP tools are pre-registered.
- Cursor/Antigravity: CodeSift unavailable — use Grep/Read.
If any tool call fails, fall back to Grep/Read.

Token budget: Each agent must keep its report under 800 words. Structured as: findings list (ID, severity, file:line, 1-sentence description) + 1-paragraph summary. No prose explanations per finding.

CodeSift cheat sheet — include right after the tool loading block:

CodeSift: batch 3+ searches → codebase_retrieval(queries=[...]).
Endpoints → trace_route first. Skip list_repos (auto-resolve).
If empty results → fallback to Grep (parser may be unavailable).

Pre-verified findings: Pass Phase 2.0 results to agents with instruction: "These findings are TOOL-VERIFIED. Do not re-scan for them. Focus on patterns the pre-scan cannot catch."

Without parallel dispatch: Execute all dimensions sequentially.

DB1: Query Patterns -- Weight 15, Max 15, Critical Gate

Check	Good	Bad	Severity
N+1 queries	Eager loading (`include`, `joinedload`), batched IDs	`findMany` / `find` inside a loop	CRITICAL
Select efficiency	`select` only needed fields	`SELECT *` / no select clause	HIGH
Bulk operations	`createMany`, `updateMany`, bulk insert	Individual create/update in loop	HIGH
Raw query safety	Parameterized queries (`$queryRaw` with template, `%s` params)	String concatenation in SQL	CRITICAL

Critical gate: DB1=0 (N+1 in hot path) triggers audit FAIL.

DB2: Index Strategy -- Weight 15, Max 15

Check	Good	Bad	Severity
FK indexes	Every foreign key has an index	FK columns without index	HIGH
Composite indexes	Multi-column indexes for common query patterns	Single-column indexes on individually queried fields	MEDIUM
Covering indexes	Index includes all fields for frequent queries	Extra lookups required	LOW
Unused indexes	All indexes serve active queries	Indexes that are never hit	MEDIUM

DB3: Schema Design -- Weight 8, Max 8

Check	Good	Bad	Severity
Normalization	Appropriate normal form, no data duplication	Same data stored in multiple tables	HIGH
Enum usage	Database enums or constrained strings for status/type	Arbitrary strings without validation	MEDIUM
Timestamps	`createdAt`/`updatedAt` on mutable models, `deletedAt` for soft delete	No audit trail	MEDIUM
Naming conventions	Consistent naming (snake_case or camelCase), clear foreign key names	Mixed conventions, ambiguous names	LOW

DB4: Connection Management -- Weight 10, Max 10, Critical Gate

Check	Good	Bad	Severity
Connection pooling	Pool configured with min/max, singleton client	New client per request	CRITICAL
Serverless awareness	External pooler (PgBouncer, Supabase pooler) for serverless	Direct connection from Lambda/Worker	CRITICAL
Connection limits	Pool size matches deployment (serverless: small, container: tuned)	Default unlimited	HIGH
Client instantiation	Single PrismaClient/DataSource instance	Multiple `new PrismaClient()` calls	HIGH

Critical gate: DB4=0 (no pooling) triggers audit FAIL.

DB5: Transaction Safety -- Weight 12, Max 12, Critical Gate

Check	Good	Bad	Severity
Multi-table mutations	Wrapped in transaction	Separate writes without transaction	CRITICAL
Transaction scope	Minimal scope, no external API calls inside	HTTP request or email send inside transaction	HIGH
Rollback handling	Explicit error handling, compensation logic	Silent swallow on transaction failure	HIGH
Deadlock prevention	Consistent lock ordering, timeout on transactions	Arbitrary ordering, no timeout	MEDIUM

Critical gate: DB5=0 (multi-table mutations without transaction) triggers FAIL.

DB6: Migration Safety -- Weight 8, Max 8

Check	Good	Bad	Severity
Non-blocking DDL	`CREATE INDEX CONCURRENTLY`, `ADD COLUMN` with default	Locking index creation on large table	HIGH
Data migration	Separate data migration from schema migration	Mixed DDL and DML in one migration	MEDIUM
Reversibility	Down migrations exist and tested (non-Prisma ORMs)	No rollback path	MEDIUM
Type changes	Multi-step migration for type changes (add new, migrate, drop old)	Direct `ALTER TYPE` on populated column	HIGH

DB7: Caching Layer -- Weight 8, Max 8

Check	Good	Bad	Severity
Query result cache	Redis/Memcached for expensive/repeated queries, TTL configured	Every request hits database	HIGH
Cache invalidation	Event-driven or TTL with jitter	Manual invalidation, no TTL	MEDIUM
Cache-aside pattern	Read-through with fallback to DB on miss	All-or-nothing cache (miss = error)	MEDIUM
Cache key design	Includes tenant/org scope, versioned	Global keys, no scoping	MEDIUM

DB8: Query Optimization -- Weight 10, Max 10

Check	Good	Bad	Severity
Pagination	Cursor-based for large datasets, keyset pagination	OFFSET pagination on growing table	HIGH
LIKE queries	Prefix match only, full-text search for complex needs	`%term%` LIKE on unindexed column	MEDIUM
Function on column	Avoid function calls on indexed columns in WHERE	`WHERE LOWER(email) = ...` (defeats index)	MEDIUM
Sorting	Sort on indexed column	Sort on computed/unindexed column for large result	MEDIUM

DB9: ORM-Specific Anti-Patterns -- Weight 6, Max 6

Patterns vary by detected ORM:

Prisma: $queryRawUnsafe, missing select on deep includes, findMany without take, $transaction with long-running operations.

TypeORM: Lazy relations without awareness, find() without select, QueryBuilder without parameter binding.

Django: N+1 via object.related_set.all() without select_related/ prefetch_related, .count() on unevaluated queryset.

SQLAlchemy: Lazy loading N+1, session.query() without limit, missing yield_per for large result sets.

DB10: Observability -- Weight 4, Max 4

Check	Good	Bad	Severity
Query logging	Structured logging with query duration, parameterized	No query logging in production	MEDIUM
Slow query alerting	Threshold-based alerts (> 1s), dashboard	No monitoring for slow queries	MEDIUM
Connection metrics	Pool utilization tracked, alerts on exhaustion	No visibility into connection state	LOW

DB11: Data Lifecycle -- Weight 4, Max 4

Check	Good	Bad	Severity
Archival strategy	Old data archived or partitioned	Unbounded table growth	MEDIUM
Batch processing	Batch deletes/updates with limits, off-peak scheduling	Full-table operations during peak hours	MEDIUM
Soft delete	`deletedAt` pattern with default scope excluding deleted	Hard delete without audit trail	LOW

DB12: Database Security -- Weight 4, Max 4, Critical Gate

Check	Good	Bad	Severity
SQL injection	All queries parameterized, no string concatenation	`$queryRawUnsafe` or string-built SQL	CRITICAL
Least privilege	Application user has minimal permissions	App connects as superuser	HIGH
Connection encryption	SSL/TLS required in connection string	Plaintext database connection	HIGH
Sensitive data	PII encrypted at rest, column-level encryption for secrets	Plaintext passwords or tokens in DB	CRITICAL

Critical gate: DB12=0 (SQL injection) triggers audit FAIL.

DB13: Migration Deployment Safety -- Weight 8, Max 8

Goes beyond DB6 (migration code quality) to assess whether migrations can be deployed safely to a running production system.

Check	Good	Bad	Severity
Destructive operations	`DROP COLUMN`/`DROP TABLE` preceded by deprecation migration, data backed up	Direct `DROP` on populated columns without prior migration to remove usage	CRITICAL
Lock duration estimation	Short-lived locks: `ADD COLUMN` with default (PG 11+), `CREATE INDEX CONCURRENTLY`	`ALTER TABLE` operations that acquire `ACCESS EXCLUSIVE` lock on large tables (>100K rows)	CRITICAL
Rollback plan	Down migration exists and tested, or forward-fix strategy documented	No rollback path — failed migration leaves DB in inconsistent state	HIGH
Data loss risk	`NOT NULL` additions have `DEFAULT` value, type changes preserve data	`ALTER COLUMN SET NOT NULL` without default on populated table, truncating type changes	CRITICAL
Backward compatibility	New columns nullable or with defaults (old app version still works), rename = add+copy+drop	Column renames or type changes that break currently-deployed app code	HIGH
Idempotency	Migrations use `IF NOT EXISTS`, `IF EXISTS` guards	Migrations fail on re-run (no idempotency — partial failure leaves broken state)	HIGH
Migration ordering	Migrations numbered/timestamped, no conflicts in team branches	Multiple migrations with same timestamp, or migrations that depend on unapplied predecessors	MEDIUM
Long-running DML	Data backfills use batched updates with `LIMIT` and sleep between batches	Single `UPDATE` on millions of rows (locks table, blocks queries, risks timeout)	HIGH
Connection impact	Migration runs outside connection pool, or uses dedicated migration connection	Migration runs through application pool, potentially exhausting connections during deploy	MEDIUM
Zero-downtime readiness	Migration + app deploy order documented, blue-green or rolling deploy compatible	Migration requires app downtime — schema and app must change simultaneously	HIGH

How to audit:

Read all migration files in the migrations directory (last 20 if >20 exist)
For each migration, classify operations:
- SAFE: ADD COLUMN (nullable or with default), CREATE TABLE, CREATE INDEX CONCURRENTLY
- CAUTION: ADD COLUMN NOT NULL with default (PG 11+ safe, older = table rewrite), ALTER COLUMN SET DEFAULT
- DANGEROUS: DROP COLUMN, DROP TABLE, ALTER COLUMN TYPE, ALTER COLUMN SET NOT NULL without default
- BLOCKING: CREATE INDEX without CONCURRENTLY, ALTER TABLE on large table without estimated lock time
For DANGEROUS/BLOCKING operations, check:
- Is there a prior migration removing code references to dropped columns?
- Is there a rollback migration?
- Is the table large enough to cause lock contention (estimate from schema relations)?
Check deployment documentation for migration strategy

Scoring:

0 DANGEROUS ops without safeguards = 8/8
Each unguarded DANGEROUS op: -2
Each BLOCKING op without CONCURRENTLY: -1
No rollback path for any destructive migration: -2
No idempotency guards: -1

N/A: If no migration files found, DB13=N/A.

Phase 3: Live Analysis (optional --live)

Skip unless --live <conn> was provided. Requires database client access.

Supported engines: PostgreSQL (full), MySQL (partial). SQLite/MongoDB: skip Phase 3, rely on code-level analysis.

3.1 PostgreSQL Live Queries

-- Top 20 slow queries (requires pg_stat_statements)
SELECT query, calls, mean_exec_time, total_exec_time, rows
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

-- Unused indexes
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Cache hit ratio
SELECT sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0)
  AS cache_hit_ratio
FROM pg_statio_user_tables;

-- Table bloat
SELECT schemaname, relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables WHERE n_dead_tup > 10000 ORDER BY n_dead_tup DESC;

-- Active connections by state
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

3.2 MySQL Live Queries

-- Slow queries via performance_schema
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;

-- Unused indexes
SELECT * FROM sys.schema_unused_indexes;

-- Active connections
SHOW PROCESSLIST;

3.3 Unsupported Engines

If the engine is not PostgreSQL or MySQL, print: "Live analysis requires PostgreSQL or MySQL. Skipping Phase 3." Mark Phase 3 as SKIPPED.

Phase 4: Verification and Scoring

4.1 False Positive Filters

Pattern	Skip When
`findMany` without `take`	Inside admin tool, migration script, or seed file
N+1 loop	Loop is bounded (< 10 items) AND commented as intentional
`await-in-loop` (search_patterns hit)	Function contains `*_CONCURRENCY_LIMIT`, `BATCH_SIZE`, `CHUNK_SIZE`, or similar bounded-concurrency constant
`await-in-loop` (search_patterns hit)	Loop body is inside a seed/migration/admin script (path contains `seed/`, `scripts/`, `migrations/`, `tools/`)
`await-in-loop` (search_patterns hit)	Loop is wrapped by `pLimit`, `Promise.all` with chunks, `pAll`, `bottleneck`, or similar concurrency limiter
`await-in-loop` (search_patterns hit)	Loop is bounded by literal `< 10` and the bound is visible in source
Missing index	Table known to be small (< 1000 rows, documented)
No indexes in ORM	External index scripts found (use external inventory for scoring)
No connection pool	Using managed service that pools for you (Neon, Supabase, PlanetScale, Cockroach Cloud — see Phase 0.2)
Raw SQL flagged	Uses tagged template `$queryRaw` (safe, parameterized)

4.2 Severity Classification

Level	Criteria
CRITICAL	Data loss, connection exhaustion, SQL injection, OOM in production
HIGH	User-visible latency, missing safety guard, scalability wall
MEDIUM	Performance debt, suboptimal pattern, maintenance risk
LOW	Recommendation, best practice gap, future-proofing

4.3 Cross-Dimension Correlations

Flag compound patterns:

Pattern	Dimensions	Impact
N+1 on unindexed column	DB1+DB2	Exponential degradation
Unbounded query without cache on large table	DB1+DB7+DB11	OOM risk
External API call inside transaction without timeout	DB5+DB4	Connection pool exhaustion
Serverless + no external pooler + multiple client instances	DB4+deployment	Connection storm
OFFSET pagination on growing table	DB8+DB11	Degrading page load times

4.4 Scoring

Score each dimension per the rubric. Calculate weighted total.

Critical gate check:

DB1=0 (N+1 in hot path) -> FAIL
DB4=0 (no connection pooling) -> FAIL
DB5=0 (multi-table mutations without transaction) -> FAIL
DB12=0 (SQL injection) -> FAIL

Any critical gate = 0 overrides the overall grade to FAIL.

Grade calculation (excluding N/A dimensions):

Grade	Percentage
A	>= 85%
B	70-84%
C	50-69%
D	< 50%

Phase 5: Report

Report Structure

# Database Audit Report

## Metadata
| Field | Value |
|-------|-------|
| Project | [name] |
| Date | [YYYY-MM-DD] |
| ORM | [detected ORM] |
| Engine | [detected engine] |
| Deployment | [detected deployment type] |
| Scope | [full / path / file] |
| Live analysis | [enabled / skipped] |
| CodeSift | [full-parser / text-stub / unavailable] |
| Prior audit | [date or "none — baseline"] |

## Executive Summary

**Score: [N] / [MAX]** -- [A/B/C/D or FAIL]

| Metric | Count |
|--------|-------|
| CRITICAL findings | N |
| HIGH findings | N |
| MEDIUM findings | N |
| LOW findings | N |

[2-3 sentence summary]

## Dimension Scores

| # | Dimension | Score | Max | Notes |
|---|-----------|-------|-----|-------|
| DB1 | Query Patterns | [N] | 15 | |
| DB2 | Index Strategy | [N] | 15 | |
| DB3 | Schema Design | [N] | 8 | |
| DB4 | Connection Mgmt | [N] | 10 | |
| DB5 | Transaction Safety | [N] | 12 | |
| DB6 | Migration Safety | [N] | 8 | |
| DB7 | Caching Layer | [N] | 8 | |
| DB8 | Query Optimization | [N] | 10 | |
| DB9 | ORM Anti-Patterns | [N] | 6 | |
| DB10 | Observability | [N] | 4 | |
| DB11 | Data Lifecycle | [N] | 4 | |
| DB12 | DB Security | [N] | 4 | |
| DB13 | Migration Deploy Safety | [N] | 8 | |
| **Total** | | **[N]** | **[M]** | |

## Critical Gate Status
[DB1, DB4, DB5, DB12 -- PASS/FAIL per gate]

## Model Inventory
[From Phase 1]

## Findings (sorted by severity)

Per finding:
- **ID:** DB{dimension}-{NNN} (e.g. DB1-001)
- **Severity:** CRITICAL / HIGH / MEDIUM / LOW
- **Status:** NEW / RESOLVED / PARTIAL / PLANNED / REGRESSION (see status rules below)
- **Confidence:** TOOL-VERIFIED (from Phase 2.0 pre-scan) / HIGH / MEDIUM
- **File:line:** exact location
- **Description:** 1 sentence
- **Fix:** concrete code change or command
- **Effort:** S (<1h) / M (1-4h) / L (4h+)
- **Evidence:** required for any severity downgrade — `find_references` count, `trace_route` hot/cold path, etc.

### Status rules

| Status | When to use | Counted in severity totals? |
|--------|-------------|----------------------------|
| `NEW` | First time this finding appears | Yes |
| `RESOLVED` | Prior finding no longer present (verified, not assumed) | No (moved to "Resolved" section) |
| `PARTIAL` | Part of prior issue is fixed, part remains. Severity stays at original level with `(partial)` suffix — DO NOT downgrade. | Yes, at original severity |
| `PLANNED` | Finding has a linked task in `docs/specs/*-plan.md` or `memory/backlog.md`. Tracked separately. | No (moved to "Planned" section) |
| `REGRESSION` | Finding was RESOLVED in prior audit but reappeared | Yes, severity = max(prior, current) |

**Severity downgrade rule:** A finding's severity may only be lowered (e.g. M→L) when ALL of these are true:
1. The Evidence field includes a concrete count from `find_references` or `trace_route`
2. The downgrade reason is documented in 1 sentence in the Description
3. If `mode == "delta"`, the Phase 0.5 checklist was completed for this specific finding

If any of the three conditions fail, keep the prior severity. Anchoring to a number you didn't independently verify is the #1 cause of audit drift.

## Delta from Prior Audit

If a prior `zuvo/audits/db-audit-*.md` exists, include:

| Finding | Prior status | Current status | Change |
|---------|-------------|----------------|--------|
| DB1-001 | CRITICAL | RESOLVED | Fixed in [commit] |
| DB2-003 | HIGH | HIGH | Still open |
| DB9-001 | — | NEW | First detected |

Score delta: [prior score] → [current score] ([+/-N])

If no prior audit exists, print: "No prior audit found — baseline established."

## Cross-Cutting Patterns
[Compound patterns found]

## Top 5 Action Items

Per item: priority (P0/P1/P2), effort (S/M/L), blast radius (N files), concrete action.

## Delete These Tomorrow

Actionable commands for findings that require zero design decisions — just execute:

```bash
# Example format:
# DB1-003: Add take: 100 to unbounded findMany
# File: src/services/user.service.ts:45

# DB2-001: Add missing FK index
# npx prisma migrate dev --name add_org_id_index

# DB12-002: Remove hardcoded connection string
# Move to .env: DATABASE_URL=...

List only findings with effort=S. If none qualify, omit this section.

Backlog Entries

[/backlog add commands for HIGH+ findings]


### Report Validation

After writing, verify:
- Dimension scores sum to total in Executive Summary
- Finding counts match Executive Summary (counted: NEW + PARTIAL + REGRESSION; not counted: RESOLVED + PLANNED)
- All models from inventory are addressed
- Critical gate status is accurate
- Every finding has a DB{N}-{NNN} ID, severity, status, confidence, file:line, and effort
- Every severity downgrade has Evidence field populated (find_references count or trace_route output)
- PLANNED findings have a verified link to `docs/specs/*-plan.md` or `memory/backlog.md`
- PARTIAL findings keep prior severity with `(partial)` suffix — no downgrade
- Delta section references correct prior audit (or states "baseline")
- TOOL-VERIFIED findings match Phase 2.0 pre-scan output
- "Delete These Tomorrow" only contains effort=S items
- If `mode == "delta"`: Phase 0.5 checklist is fully completed (all 6 items checked)

---

## Phase 6: Next-Step Routing

RECOMMENDED NEXT ACTION

DB1 CRITICAL (N+1) -> zuvo:refactor [service file] DB4 no connection pool -> direct fix (add pool config) DB2 missing indexes -> direct migration (add indexes) DB12 SQL injection -> /security-audit [path] DB13 unsafe migrations -> rewrite migrations with CONCURRENTLY, rollbacks, batch DML DB9 ORM anti-patterns -> zuvo:refactor [service file] Multiple dimensions fail -> zuvo:review [path]


---

## Completion Gate Check

Before printing the final output block, verify every item. Unfinished items = pipeline incomplete.


## DB-AUDIT COMPLETE

Score: [N] / [MAX] -- [grade]
ORM: [detected] | Engine: [detected]
Dimensions: [N scored] | Critical gates: [PASS/FAIL]
Findings: [N critical] / [N total]

### Validity Gate (REQUIRED — print BEFORE Run line)


**The Validity Gate must be printed AFTER step 6 of the Required POSTAMBLE (after retros append), not before.** Printing it before retro append guarantees `retros_*_appended: NOT_APPENDED`.

If `gate_status = FAIL`, override the VERDICT below to `INCOMPLETE` regardless of finding count, append `[VALIDITY GATE FAIL]` to the Run line NOTES column, and add a backlog item.

Run: <ISO-8601-Z>	db-audit	<project>	<N-critical>	<N-total>	<VERDICT>	-	<N>-dimensions	<NOTES>	<BRANCH>	<SHA7>	<INCLUDES>	<TIER>


### Retrospective (REQUIRED)

**Load now (deferred):** Read `../../shared/includes/retrospective.md` if not already loaded.

Follow the retrospective protocol from `retrospective.md`.
Gate check → structured questions → TSV emit → markdown append.
If gate check skips: print "RETRO: skipped (trivial session)" and proceed.

**Append via wrapper (REQUIRED).** Never `>>` directly to `~/.zuvo/runs.log` — the wrapper is the gate that verifies a retro entry exists for this run. Order: retro bash executed → wrapper invoked → completion claimed.

```bash
printf '%b\n' "$RUN_LINE" | ~/.zuvo/append-runlog

VERDICT: PASS (0 critical findings), WARN (1-3 critical), FAIL (4+ critical).

Execution Notes

All commands use the resolved TARGET_ROOT from argument parsing
CodeSift integration follows codesift-setup.md -- use indexed search when available, fall back to Grep/Read/Glob when not
Agent dispatch follows env-compat.md -- parallel when supported, sequential otherwise
ORM detection determines which anti-pattern checks to run (DB9 is ORM-specific)
Live analysis (Phase 3) is strictly opt-in and read-only
Prisma does not use down migrations -- do not flag this as an issue

db-audit

More from this repository

More from this repository

zuvo:db-audit

Mandatory File Loading

MANDATORY TOOL CALLS — Audit Validity Gate

Required tool list

Pre-flight check (run BEFORE any phase)

Required POSTAMBLE — retrospective append (NOT optional)

Forbidden escape hatches

Audit completion verification (run BEFORE writing PASS/WARN/FAIL status)

Argument Parsing

Safety Gates

GATE 1 -- Read-Only

GATE 2 -- Live Mode Scoping

Phase 0: Detect and Prepare

0.0 CodeSift Capability Check

0.1 ORM Detection

0.2 Database Engine Detection

0.3 Deployment Detection

0.4 Audit Mode Decision

CRITICAL — Mode does NOT affect MANDATORY TOOL CALLS

0.5 Delta Verification Checklist

Phase 1: Schema Analysis

1.1 Schema Inventory

1.2 External Index Detection

1.3 Migration Analysis (DB6)

1.4 Model Inventory Output

Phase 2: Code-Level Analysis (DB1-DB13)

2.0 CodeSift Pre-Scan

2.0a — Generic anti-pattern scans

2.0b — SQL composite audit (sql_audit) — MANDATORY when .sql files exist

2.0c — Additional SQL query tools (optional)

Agent Dispatch

DB1: Query Patterns -- Weight 15, Max 15, Critical Gate

DB2: Index Strategy -- Weight 15, Max 15

DB3: Schema Design -- Weight 8, Max 8

DB4: Connection Management -- Weight 10, Max 10, Critical Gate

DB5: Transaction Safety -- Weight 12, Max 12, Critical Gate

DB6: Migration Safety -- Weight 8, Max 8

DB7: Caching Layer -- Weight 8, Max 8

DB8: Query Optimization -- Weight 10, Max 10

DB9: ORM-Specific Anti-Patterns -- Weight 6, Max 6

DB10: Observability -- Weight 4, Max 4

DB11: Data Lifecycle -- Weight 4, Max 4

DB12: Database Security -- Weight 4, Max 4, Critical Gate

DB13: Migration Deployment Safety -- Weight 8, Max 8

Phase 3: Live Analysis (optional --live)

3.1 PostgreSQL Live Queries

3.2 MySQL Live Queries

3.3 Unsupported Engines

Phase 4: Verification and Scoring

4.1 False Positive Filters

4.2 Severity Classification

4.3 Cross-Dimension Correlations

4.4 Scoring

Phase 5: Report

Report Structure

Backlog Entries

RECOMMENDED NEXT ACTION

Execution Notes

zuvo:db-audit

Mandatory File Loading

MANDATORY TOOL CALLS — Audit Validity Gate

Required tool list

Pre-flight check (run BEFORE any phase)

Required POSTAMBLE — retrospective append (NOT optional)

Forbidden escape hatches

Audit completion verification (run BEFORE writing PASS/WARN/FAIL status)

Argument Parsing

Safety Gates

GATE 1 -- Read-Only

GATE 2 -- Live Mode Scoping

Phase 0: Detect and Prepare

0.0 CodeSift Capability Check

0.1 ORM Detection

0.2 Database Engine Detection

0.3 Deployment Detection

0.4 Audit Mode Decision

CRITICAL — Mode does NOT affect MANDATORY TOOL CALLS

2.0b — SQL composite audit (`sql_audit`) — MANDATORY when `.sql` files exist

2.0b — SQL composite audit (`sql_audit`) — MANDATORY when `.sql` files exist