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

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:

1. In preloaded list already? (from codesift-setup.md Step 2.5 ToolSearch — the sql group is auto-included when .sql files exist.) → use it.
2. 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).
3. Reveal succeeded? (schema returned / tool now in list) → use it. This is the expected path for a CodeSift-backed session.

4. 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 record the blocker Process item (../../shared/includes/backlog-protocol.md "Filing from a skill"): ~/.zuvo/backlog-add --repo "$R" --class PROCESS --conf 90 --source db-audit --target subject:db-audit-blocker --title "$T", with T the fixed title db-audit INCOMPLETE: a required tool is not reachable, the same words on every run, no date and no tool name; the tool and the project go in the report, never in the title. Assign "$R" and "$T" before the call, as "Free text goes through a variable" in backlog-protocol.md "Filing from a skill" says; an empty value is a bug, and backlog-add refuses a missing required field (REFUSED missing-field:<f>). It is kept in the findings ledger, not ticketed; name its F-id in the report.

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:

1. Write zuvo/audits/db-audit-<date>.md.
2. Print Validity Gate block.
3. Print Run line + append to runs.log.
4. Load ../../shared/includes/retrospective.md if not already loaded.
5. Fill all 9 retrospective fields per protocol.
6. Execute the bash append commands that write RETRO: line to ~/.zuvo/retros.log and the long-form entry to ~/.zuvo/retros.md.
7. 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:

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

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

1. Re-check each trigger condition.
2. 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).
3. 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.
   • Record the incomplete Process item (../../shared/includes/backlog-protocol.md "Filing from a skill"): ~/.zuvo/backlog-add --repo "$R" --class PROCESS --conf 90 --source db-audit --target subject:db-audit-incomplete --title "$T", with T the fixed title db-audit run INCOMPLETE: validity gate failed, the same words on every run and no date; the missing tool goes in the report, never in the title. Assign "$R" and "$T" before the call, as "Free text goes through a variable" in backlog-protocol.md "Filing from a skill" says; an empty value is a bug, and backlog-add refuses a missing required field (REFUSED missing-field:<f>). It is kept in the findings ledger, not ticketed. File it once per run, here, before the report's Backlog writes: line is printed, so its wrote= paths are on that line. The final Validity Gate files it only if this step did not (a postamble field that fails after the report is saved), and otherwise cites this call's F-id; that block carries the F-id on a line incomplete-item: <F-id> and, for a late call, its paths on a line Backlog writes (gate): <path>, … | none.

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

Safety Gates

GATE 1 -- Read-Only

This audit is read-only. The only write target is zuvo/audits/, plus the memory/ paths ~/.zuvo/backlog-add writes (the backlog, its archive and the findings ledger) for the process items at the missing-tool and validity-gate steps and for the Backlog Entries, and the one tick a closed backlog entry takes (Closing, not removing). It writes nothing else in the repository, and never touches the database.

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:

1. 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.
2. 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.) When you hit this, say so in the report and point at zuvo:worktree prune, which migrates nested worktrees to a sibling ../<repo>-worktrees/ and removes the inflation at the source instead of filtering around it every run.

0.1 ORM Detection

If not resolved by analyze_project:

0.2 Database Engine Detection

If not resolved by analyze_project:

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: <provider>" and pass DB4 if no other issues exist.

0.3 Deployment Detection

If not resolved by analyze_project:

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

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:

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:

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:

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

Dispatch follows ../../shared/includes/execution-policy.md through env-compat. Reuse existing authorization within that policy; session restrictions take precedence. Run each required gate and report its actual independence or an unmet requirement.

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

When parallel dispatch is available:

Agent prompt rules:

1. 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).
2. 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.
3. 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).

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

Critical gate: N+1 in hot path → triggers FAIL. The gate fires on that FINDING; a DB1 score of 0 also fires it but is not required.

DB2: Index Strategy -- Weight 15, Max 15

DB3: Schema Design -- Weight 8, Max 8

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

Critical gate: no pooling → triggers FAIL. The gate fires on that FINDING; a DB4 score of 0 also fires it but is not required.

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

Critical gate: multi-table mutations without transaction → triggers FAIL. The gate fires on that FINDING; a DB5 score of 0 also fires it but is not required.

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

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

DB11: Data Lifecycle -- Weight 4, Max 4

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

Critical gate: SQL injection → triggers FAIL. The gate fires on that FINDING; a DB12 score of 0 also fires it but is not required.

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.

How to audit:

1. Read all migration files in the migrations directory (last 20 if >20 exist)
2. 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
3. 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)?
4. 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

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

4.3 Cross-Dimension Correlations

Flag compound patterns:

4.4 Scoring

Score each dimension per the rubric. Calculate weighted total.

Critical gate check — each fires on the FINDING, whatever the dimension's numeric score:

Any one of them overrides the overall grade to FAIL.

Read as "the dimension scored 0" these gates are nearly unreachable, and the most severe one is the worst affected: DB12 carries four checks across four points, so a codebase with an actual SQL injection but least-privilege, TLS and encrypted PII in place scores 3/4 — and the injection never fails the audit. A dimension score of 0 still fires the gate; it is simply not necessary.

Grade calculation (excluding N/A dimensions):

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
[for each HIGH+ finding, the B- or F-id the helper printed, or NOT PERSISTED (<reason>)]

Backlog Entries. Every HIGH+ finding (critical or high) is recorded through backlog-add before this section is written, one call per finding (../../shared/includes/backlog-protocol.md "Filing from a skill"; the bars and the dedupe are the protocol's and are not restated here). A Generic write: ~/.zuvo/backlog-add --repo "$R" --class <C> --conf <N> --source db-audit --file "$F" --line <n> [--anchor "$A"] --severity <level> --title "$T". <level> is critical or high. <C> is PERF for DB1, DB2, DB4 and DB7-DB9 (query, index, pool, cache and ORM performance), DATA for DB5, DB6, DB11 and DB13 (a mutation or migration that can leave or lose wrong data), SEC for DB12, CQ for DB3 (schema design: normalization, enums, timestamps, naming; a maintainability gap, not a wrong result) and DB10 (observability: no query logging or slow-query alerting; the CLI has no monitoring class, ADVISORY is never filed, and a missing log changes no result, so the ledger-only CQ fits), and BUG only when the finding names the input and the wrong output. BUG, SEC and DATA take --anchor "$A", copied from the line that shows the defect or, for something absent (no index, no constraint, no transaction), from the line of the query, model or schema definition that lacks it; a schema-wide DATA finding cites the schema or model definition line ("The location rule"). A schema-wide CQ or PERF finding (missing indexes on foreign keys across the schema, mixed naming) cites the schema or model definition line of the first affected table or model in path order, the same way, and lists the others in --evidence "$E"; it takes no --anchor. Assign "$R", "$F" and "$T" before each call, "$A" only when you pass --anchor, "$E" only when you pass --evidence, as "Free text goes through a variable" in that section says; an empty value you pass is a bug, and backlog-add refuses a missing required field (REFUSED missing-field:<f>). Report Recorded: N (backlog: a, ledger: b) ("The report line" in backlog-protocol.md), plus each NOT PERSISTED (<reason>) by name; on a non-zero exit act on the protocol's exit table, and never append by hand.

Resolved entries (Closing, not removing): a finding gets no call only when this run verified that its cited defect is fixed now (RESOLVED against the prior audit means the code is fixed, not that an older audit said so); a finding still present now is recorded even if an earlier audit marked it resolved. An open entry from an earlier audit that this run re-read and found fixed is closed, never removed: in one edit tick its box (- [x]) and append its resolution marker ([FIXED <sha7>]), as backlog-protocol.md "How to Persist Findings" step 3 says. That tick is the one hand edit the protocol sanctions (no helper ticks), and it is not a wrote= path: this audit does not stage it and names each ticked entry in its report. Moving a closed entry to the archive is the helper's job (~/.zuvo/backlog-archive.py archive, which append-runlog runs at the end of the run), never done by hand.

This audit does not commit: it stages nothing. It lists the wrote= paths backlog-add printed as Backlog writes: <path>, ... (Backlog writes: none when there are none), in its report for the user when it runs standalone, and in its report for the calling skill's closing commit to carry when another skill runs it (backlog-protocol.md "Filing from a skill", Committing what backlog-add wrote).

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/
[ ] HIGH+ findings recorded through backlog-add (Backlog Entries), each B- or F-id reported or named under NOT PERSISTED (<reason>); `Backlog writes:` line printed
[ ] 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)

VALIDITY GATE
  triggers_held:
    sql_files: [yes(N) | no]
    migrations_dir: [yes | no]
    prisma_schema: [yes | no]
    postgres_in_deps: [yes | no]
    framework: [nestjs | django | none]
  required_tool_calls (held triggers only):
    sql_audit: [<gates_passed>/<gates_run> | NOT_CALLED — VIOLATES_TRIGGER]
    analyze_schema: [<table_count> tables | not_required | NOT_CALLED — VIOLATES_TRIGGER]
    diff_migrations: [<additive>/<modifying>/<destructive> | not_required | NOT_CALLED — VIOLATES_TRIGGER]
    trace_query: [<table_count> traced | not_required | NOT_CALLED — VIOLATES_TRIGGER]
    search_columns: [<pii_columns_found> | NOT_CALLED — VIOLATES_TRIGGER]
    migration_lint: [<findings> | not_required | NOT_CALLED — VIOLATES_TRIGGER]
    analyze_prisma_schema: [<fk_index_coverage> | not_required | NOT_CALLED — VIOLATES_TRIGGER]
    explain_query: [<queries_explained> | not_required | NOT_CALLED — VIOLATES_TRIGGER]
    nest_audit: [<score> | not_required | NOT_CALLED — VIOLATES_TRIGGER]
    python_audit: [<findings> | not_required | NOT_CALLED — VIOLATES_TRIGGER]
    scan_secrets: [<findings> | NOT_CALLED — VIOLATES_TRIGGER]
  pattern_calls:
    unbounded-findmany: [<count> | NOT_CALLED — VIOLATES_TRIGGER]
    await-in-loop: [<count> | NOT_CALLED — VIOLATES_TRIGGER]
    toctou: [<count> | NOT_CALLED — VIOLATES_TRIGGER]
  postamble:
    retros_log_appended: [yes(bytes_added=N) | NOT_APPENDED — VIOLATES_REQUIRED_POSTAMBLE]
    retros_md_appended:  [yes(entry_count=N) | NOT_APPENDED — VIOLATES_REQUIRED_POSTAMBLE]
  gate_status: [PASS | FAIL — <which tools or postamble missing>]

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 record the incomplete Process item unless step 3 of "Audit completion verification" above already did (it gives the command and the fixed title; one item per run), and print its F-id in this block on a line incomplete-item: <F-id> (with Backlog writes (gate): for a late call, as step 3 says).

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.

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 "^<!-- RETRO -->" ~/.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