# DB Audit

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

- Skill: `greglas75/db-audit` (Agent Skill)
- Install (CLI): `npx skillmds@latest add greglas75/db-audit`
- Raw SKILL.md: https://api.skillmd.com/api/skills/greglas75/db-audit/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Security
- Author: greglas75 (https://skillmd.com/u/greglas75)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/greglas75/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

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

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

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

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

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

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

| 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: <provider>" 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`:

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

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

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

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

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

### CRITICAL — Mode does NOT affect MANDATORY TOOL CALLS

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

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

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

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

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

Print the decision:

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

### 0.5 Delta Verification Checklist

**Skip this section if `mode != "delta"`.**

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

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

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

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

---

## Phase 1: Schema Analysis

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

### 1.1 Schema Inventory

Read the schema source for the detected ORM and extract:

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

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

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

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

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

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

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

### 1.2 External Index Detection

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

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

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

### 1.3 Migration Analysis (DB6)

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

### 1.4 Model Inventory Output

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

---

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

### 2.0 CodeSift Pre-Scan

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

#### 2.0a — Generic anti-pattern scans

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

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

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

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

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

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

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

Map each gate to the corresponding DB dimension:

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

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

The `sql_audit` result has shape:
```json
{
  "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

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

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

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

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

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

| 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 al

…(truncated)