ln-652-transaction-correctness-auditor

Paths: File paths ( shared/ , references/ , ../ln- ) are relative to skills repo root. If not found at CWD, locate this SKILL.md directory and go up one level for repo root. Transaction Correctness Auditor (L3 Worker) Specialized worker auditing database transaction patterns for correctness, scope, and trigger interaction. Purpose & Scope Worker in ln-650 coordinator pipeline - invoked by ln-650-persistence-performance-auditor Audit transaction correctness (Priority: HIGH) Check commit patterns, transaction boundaries, rollback handling, trigger/notify semantics Write structured findings to file with severity, location, effort, recommendations Calculate compliance score (X/10) for Transaction Correctness category Inputs (from Coordinator) MANDATORY READ: Load shared/references/audit_worker_core_contract.md . Receives contextStore with: tech_stack , best_practices , db_config (database type, ORM settings, trigger/notify patterns), codebase_root , output_dir . Domain-aware: Supports domain_mode + current_domain . Workflow MANDATORY READ: Load shared/references/two_layer_detection.md for detection methodology. Parse context from contextStore Extract tech_stack, best_practices, db_config, output_dir Determine scan_path Discover transaction infrastructure Find migration files with triggers ( pg_notify , CREATE TRIGGER , NOTIFY ) Find session/transaction configuration ( expire_on_commit , autocommit , isolation level) Map trigger-affected tables Scan codebase for violations Trace UPDATE paths for trigger-affected tables Analyze transaction boundaries (begin/commit scope) Check error handling around commits Collect findings with severity, location, effort, recommendation Calculate score using penalty algorithm Write Report: Build full markdown report in memory per shared/templates/audit_worker_report_template.md , write to {output_dir}/652-transaction-correctness.md in single Write call Return Summary: Return minimal summary to coordinator (see Output Format) Audit Rules (Priority: HIGH) 1. Missing Intermediate Commits What: UPDATE without commit when DB trigger/NOTIFY depends on transaction commit Detection: Step 1: Find triggers in migrations: Grep for pg_notify|NOTIFY|CREATE TRIGGER|CREATE OR REPLACE FUNCTION.trigger in alembic/versions/ , migrations/ Extract: trigger function name, table name, trigger event (INSERT/UPDATE) Step 2: Find code that UPDATEs trigger-affected tables: Grep for repo.update|session.execute.update|.progress|.status related to trigger tables Step 3: Check for commit() between sequential updates: If multiple UPDATEs to trigger table occur in a loop/sequence without intermediate commit() , NOTIFY events are deferred until final commit Real-time progress tracking breaks without intermediate commits Severity: CRITICAL: Missing commit for NOTIFY/LISTEN-based real-time features (SSE, WebSocket) HIGH: Missing commit for triggers that update materialized data Exception: Single atomic operation with no intermediate observable state → downgrade CRITICAL to MEDIUM. Transaction scope documented as intentional (ADR, architecture comment) → downgrade one level Recommendation: Add session.commit() at progress milestones (throttled: every N%, every T seconds) Or move real-time notifications out of DB triggers (Redis pub/sub, in-process events) Effort: S-M (add strategic commits or redesign notification path) 2. Transaction Scope Too Wide What: Single transaction wraps unrelated operations, including slow external calls Detection: Find async with session.begin() or explicit transaction blocks Check if block contains external calls: await httpx. , await aiohttp. , await requests. , await grpc. Check if block contains file I/O: open( , .read( , .write( Pattern: DB write + external API call + another DB write in same transaction Severity: HIGH: External HTTP/gRPC call inside transaction (holds DB connection during network latency) MEDIUM: File I/O inside transaction Recommendation: Split into separate transactions; use Saga/Outbox pattern for cross-service consistency Effort: M-L (restructure transaction boundaries) 3. Transaction Scope Too Narrow What: Logically atomic operations split across multiple commits Detection: Multiple session.commit() calls for operations that should be atomic Pattern: create parent entity, commit, create child entities, commit (should be single transaction) Pattern: update status + create audit log in separate commits Severity: HIGH: Parent-child creation in separate commits (orphan risk on failure) MEDIUM: Related updates in separate commits (inconsistent state on failure) Recommendation: Wrap related operations in single transaction using async with session.begin() or unit-of-work pattern Effort: M (restructure commit boundaries) 4. Missing Rollback Handling What: session.commit() without proper error handling and rollback Detection: Find session.commit() not inside try/except block or context manager Find session.commit() in try without session.rollback() in except Pattern: bare await session.commit() in service methods Exception: async with session.begin() auto-rollbacks (safe) Severity: MEDIUM: Missing rollback (session left in broken state on failure) LOW: Missing explicit rollback when using context manager (auto-handled) Recommendation: Use async with session.begin() (auto-rollback), or add explicit try/except/rollback pattern Effort: S (wrap in context manager or add error handling) 5. Long-Held Transaction What: Transaction open during slow/blocking operations Detection: Measure scope: count lines between transaction start and commit Flag if >50 lines of code between begin() and commit() Flag if transaction contains await calls to external services (network latency) Flag if transaction contains time.sleep() or asyncio.sleep() Severity: HIGH: Transaction held during external API call (connection pool exhaustion risk) MEDIUM: Transaction spans >50 lines (complex logic, high chance of lock contention) Recommendation: Minimize transaction scope; prepare data before opening transaction, commit immediately after DB operations Effort: M (restructure code to minimize transaction window) Scoring Algorithm MANDATORY READ: Load shared/references/audit_worker_core_contract.md and shared/references/audit_scoring.md . Output Format MANDATORY READ: Load shared/references/audit_worker_core_contract.md and shared/templates/audit_worker_report_template.md . Write report to {output_dir}/652-transaction-correctness.md with category: "Transaction Correctness" and checks: missing_intermediate_commits, scope_too_wide, scope_too_narrow, missing_rollback, long_held_transaction. Return summary to coordinator: Report written: docs/project/.audit/ln-650/{YYYY-MM-DD}/652-transaction-correctness.md Score: X.X/10 | Issues: N (C:N H:N M:N L:N) Critical Rules MANDATORY READ: Load shared/references/audit_worker_core_contract.md . Do not auto-fix: Report only Trigger discovery first: Always scan migrations for triggers/NOTIFY before analyzing transaction patterns ORM-aware: Check if ORM context manager auto-rollbacks ( async with session.begin() is safe) Exclude test transactions: Do not flag test fixtures with manual commit/rollback Database-specific: PostgreSQL NOTIFY semantics differ from MySQL event scheduler Definition of Done MANDATORY READ: Load shared/references/audit_worker_core_contract.md . contextStore parsed successfully (including output_dir) scan_path determined Trigger/NOTIFY infrastructure discovered from migrations All 5 checks completed: missing intermediate commits, scope too wide, scope too narrow, missing rollback, long-held Findings collected with severity, location, effort, recommendation Score calculated using penalty algorithm Report written to {output_dir}/652-transaction-correctness.md (atomic single Write call) Summary returned to coordinator Reference Files Audit output schema: shared/references/audit_output_schema.md Version: 1.0.0 Last Updated: 2026-02-04