Ln 57 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: 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, tool failure, a zero exit status, or a self-reported success is not proof; only the observed outcome is. 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. When no one can answer during the run, state the exact question and apply the skill's verdict for the remaining gap instead of waiting or guessing. 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

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 parallelism.
• 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 partial-success states.
• Find long-held transactions, network or file calls inside transactions, user interaction during locks, and transactions spanning unnecessary computation.
• Check isolation assumptions, lost updates, write skew, duplicate processing, optimistic or pessimistic locking, and retry safety.
• Verify idempotency keys, unique constraints, deduplication, outbox or inbox behavior, and at-least-once delivery paths where applicable.
• Check triggers, notifications, events, and subscribers for naming, commit-time visibility, payload compatibility, ordering, duplicate delivery, and orphan consumers; do not add intermediate commits merely to expose progress if that breaks business atomicity.
• Check migrations and backfills for transactional behavior, lock duration, mixed-version compatibility, resumability, and failure recovery.
• Verify recommendations against official semantics for the installed database, driver, and framework when behavior is version-sensitive.