ln-25-persistence-auditor

# Persistence Auditor

**Goal:** Perform a read-only audit of persistence and data-heavy runtime paths. Connect static candidates to real query, transaction, resource, or consistency mechanisms and avoid claiming performance impact without evidence.

**Execution contract:** Treat the ordered checkbox workflow below as this skill's Definition of Done. Track every checkbox as `PENDING`, then resolve it to `PROVEN` with concrete evidence, `CLEARED` with evidence that its conditional trigger is absent, or `UNPROVEN`; reading, mentioning, delegating, skipping, or tool failure is not proof.
Before returning, resolve every `PENDING`, count only `PROVEN` and `CLEARED` items as complete, apply this skill's verdict, decision, and approval rules to every `UNPROVEN`, and prepend **Checklist: X/Y complete**<br>**Incomplete: None | section/item — reason; outcome impact; exact next action**; list every `UNPROVEN` item.

## Tool Routing

| Need | Preferred tool | Use it when | Fallback |
|---|---|---|---|
| Data-layer map | Native file search over manifests, models, mappings, repositories, migrations, queries, cache, queue, and pool configuration | Establishing stores, frameworks, ownership, and scope | Trace from known request, job, or command entrypoints |
| Call paths and resource ownership | Language server or host-native code intelligence | Following service calls, transaction boundaries, async flow, session scopes, and cleanup | Targeted search plus direct inspection of definitions and callers |
| Query behavior | Existing query logs, tracing, ORM diagnostics, and application metrics | Establishing frequency, duplication, timing, rows, and cache behavior | Static query-in-loop and fetch-shape analysis with explicit limits |
| Query plans | Database-native explain tooling on an approved non-production target | A safe read query and representative schema/data are available | Inspect indexes, predicates, joins, statistics assumptions, and generated SQL statically |
| Runtime performance | Existing profiler, benchmark, or repository diagnostic command | Allocation, blocking, loop amplification, or I/O cost needs measurement | Complete static cost path marked as unmeasured |
| Correctness verification | Repository-defined tests, integration environment, and migration checks | Reproducing transaction, retry, consistency, or lifecycle behavior safely | Static failure trace and required verification plan |
| External semantics | Official database, driver, framework, and runtime documentation matching installed versions | Isolation, pooling, cancellation, caching, trigger, or async semantics affect a finding | Primary-source web research; otherwise mark `UNVERIFIED` |

Never connect to production or run mutating diagnostics. `EXPLAIN ANALYZE` executes the statement: use it only for confirmed read-only queries on an approved disposable or non-production target. Do not create indexes, migrate, vacuum, rewrite data, or change pool settings during the audit.

## Evidence Rules

- Query count, duration, rows, plan, lock, or profile evidence is stronger than a static performance suspicion.
- Static evidence can prove correctness and lifecycle defects when the full path is visible, but performance impact must be labeled unmeasured.
- ORM conventions, bounded administrative paths, startup-only work, and intentionally small datasets require context before becoming findings.
- Transaction advice must match the actual database, isolation level, driver, framework, and retry model.
- Recommendations must preserve data integrity and failure semantics, not only reduce latency.

## Checklist

### 1. Establish the Data and Runtime Context

- [ ] Detect databases, ORMs, drivers, caches, queues, schemas, migrations, pools, dependency-injection scopes, and runtime entrypoints in scope.
- [ ] Resolve installed versions, database capabilities, deployment topology, consistency requirements, and expected workload from repository evidence.
- [ ] Identify critical data paths, high-volume paths, streaming paths, scheduled work, and operations that hold transactions or resources across external calls.
- [ ] Read repository instructions and inspect Git state before running diagnostics or interpreting current work.
- [ ] Establish available query logs, metrics, traces, profiles, representative data, test environments, and safe diagnostic permissions.
- [ ] Keep the audit read-only and document every executed query, command, target, and artifact created.

### 2. Audit Query and Cache Efficiency

- [ ] Trace representative paths from entrypoint through business logic to generated query and materialization.
- [ ] Find N+1 behavior, repeated identical fetches, query-in-loop patterns, sequential independent reads, and redundant existence or count queries.
- [ ] Check over-fetching, broad entity loading, unbounded reads, premature materialization, missing pagination, and user-controlled result amplification.
- [ ] Check missing bulk operations, per-row writes, avoidable round trips, fragmented commits, and opportunities for set-based work.
- [ ] Inspect predicates, joins, sort and group operations, index alignment, query-plan assumptions, and statistics only with schema and workload context.
- [ ] Check cache ownership, key design, scope, invalidation, stampede control, negative caching, staleness tolerance, and duplication with database guarantees.
- [ ] Distinguish latency caused by query shape, connection wait, locks, network, serialization, application work, or downstream services before recommending a fix.
- [ ] Measure or clearly label the expected impact; do not present a candidate index or cache as a proven optimization.

### 3. Audit Transactions and Consistency

- [ ] Identify who begins, commits, rolls back, retries, and disposes each transaction and whether ownership matches the business operation.
- [ ] Check atomicity across related writes, early commits, missing rollback, swallowed exceptions, nested transaction behavior, and