DB Audit

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, confirm the matching tool is in the preloaded tool list (from codesift-setup.md Step 2.5 ToolSearch). If a required tool is NOT in the list and is also NOT in the deferred-tools banner:

• Print [ABORT] Required tool '<name>' not reachable. 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:

1. Write 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.
   • 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

Safety Gates

GATE 1 -- Read-Only

This audit is read-only. The only write target is 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:

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.

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: " 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 audits/db-audit-*.md:

PRIOR_AUDIT=$(ls -t 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.
• 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).

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

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: DB1=0 (N+1 in hot path) triggers audit FAIL.

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: DB4=0 (no pooling) triggers audit FAIL.

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

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

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

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 (p

…(truncated)