| 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.
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"
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 defaultALTER 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
SELECT query, calls, mean_exec_time, total_exec_time, rows
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;
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;
SELECT sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0)
AS cache_hit_ratio
FROM pg_statio_user_tables;
SELECT schemaname, relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables WHERE n_dead_tup > 10000 ORDER BY n_dead_tup DESC;
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
3.2 MySQL Live Queries
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;
SELECT * FROM sys.schema_unused_indexes;
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)
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: [// | 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: [ | 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: [ | not_required | NOT_CALLED — VIOLATES_TRIGGER]
python_audit: [ | not_required | NOT_CALLED — VIOLATES_TRIGGER]
scan_secrets: [ | NOT_CALLED — VIOLATES_TRIGGER]
pattern_calls:
unbounded-findmany: [ | NOT_CALLED — VIOLATES_TRIGGER]
await-in-loop: [ | NOT_CALLED — VIOLATES_TRIGGER]
toctou: [ | 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 — ]
**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 "^<!-- 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
