/ln-57-persistence-auditor

name: ln-57-persistence-auditor
description: "Audits queries, transactions, consistency and persistence resource lifetimes; read-only."

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:** The checklist defines completion. Track each item internally as `PENDING`, `PROVEN` with evidence, `CLEARED` with evidence its condition is absent, or `UNPROVEN` with a gap; reading, delegation, or tool failure is not proof. Reconcile after each section. Before returning, resolve all `PENDING`, count only `PROVEN` and `CLEARED`, and apply verdict and approval rules to every gap. Preserve intent, scope, and existing authorization. Continue authorized work; ask only for consequential unresolved choices or required external approval. Scale depth to material risk without skipping checks. Preserve dependency and safety order; otherwise choose an appropriate verification method. Accept equivalent user or repository evidence; no other skill, named artifact, or complete lifecycle is required. Preserve source requirement and decision IDs. Bind reused evidence to relevant source versions, dirty changes, configuration, and environment; invalidate only affected claims. On continuation, reconcile task, authorization, current state, and unresolved evidence. For long work, return a compact continuation record or update an already authorized artifact; read-only skills do not persist it. Distinguish artifact readiness, verified behavior, and external-action authority. Prepare authorized work before required approval. If blocked by an instruction, cite its exact source and unresolved boundary; do not invent approval gates from caution.

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; record executed query shapes, commands, targets, and created artifacts with sensitive parameters, credentials, and row data redacted.

2. Audit Query and Cache Efficiency

• [ ] Trace representative paths from entrypoint to generated query and materialization, including tenant/owner predicates and row-level controls where they determine which data may be read or changed.
• [ ] Find N+1 behavior, repeated fetches, query-in-loop amplification, unnecessary sequential independent reads, and redundant existence/count queries; preserve snapshot, ordering, connection-budget, and transaction constraints when proposing batching or paral