cbbkrd-tech/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
• Return structured findings with severity, location, effort, recommendations
• Calculate compliance score (X/10) for Transaction Correctness category

Inputs (from Coordinator)

MANDATORY READ: Load shared/references/task_delegation_pattern.md#audit-coordinator--worker-contract for contextStore structure.

Receives contextStore with: tech_stack, best_practices, db_config (database type, ORM settings, trigger/notify patterns), codebase_root.

Domain-aware: Supports domain_mode + current_domain.

Workflow

1. Parse context from contextStore

   • Extract tech_stack, best_practices, db_config
   • Determine scan_path

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

3. Scan codebase for violations

   • Trace UPDATE paths for trigger-affected tables
   • Analyze transaction boundaries (begin/commit scope)
   • Check error handling around commits

4. Collect findings with severity, location, effort, recommendation

5. Calculate score using penalty algorithm

6. Return JSON result to coordinator

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

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_scoring.md for unified scoring formula.

Output Format

Return JSON to coordinator:

{
  "category": "Transaction Correctness",
  "score": 5,
  "total_issues": 6,
  "critical": 1,
  "high": 2,
  "medium": 2,
  "low": 1,
  "findings": [
    {
      "severity": "CRITICAL",
      "location": "app/infrastructure/messaging/job_processor.py:412",
      "issue": "Missing intermediate commits: progress UPDATEs trigger pg_notify but no commit() between updates; real-time SSE events deferred",
      "principle": "Transaction Correctness / Trigger Semantics",
      "recommendation": "Add session.commit() at progress milestones (throttled every 5%)",
      "effort": "S"
    }
  ]
}

Critical Rules

• 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

• contextStore parsed (tech_stack, db_config, trigger patterns)
• 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
• JSON returned to coordinator

Reference Files

• Audit scoring formula: shared/references/audit_scoring.md
• Audit output schema: shared/references/audit_output_schema.md

---

Version: 1.0.0 Last Updated: 2026-02-04