ln-652-transaction-correctness-auditor

Paths: File paths (references/, ../ln-*) are relative to this skill directory.

Transaction Correctness Auditor (L3 Worker)

Type: L3 Worker

Specialized worker auditing database transaction patterns for correctness, scope, and trigger interaction.

Purpose & Scope

• 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

MANDATORY READ: Load references/audit_worker_core_contract.md. Tool policy: follow host AGENTS.md MCP preferences; load references/mcp_tool_preferences.md and references/mcp_integration_patterns.md only when host policy is absent or MCP behavior is unclear.

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.

Use hex-graph first when reference chains or call paths materially improve transaction analysis. Use hex-line first for local code/config reads when available. If MCP is unavailable, unsupported, or not indexed, continue with built-in Read/Grep/Glob/Bash and state the fallback in the report.

Workflow

Detection policy: use two-layer detection (candidate scan, then context verification); load references/two_layer_detection.md only when the verification method is ambiguous.

1) Parse context from contextStore

• Extract tech_stack, best_practices, db_config, output_dir
• 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) Write Report: Build full markdown report in memory per references/templates/audit_worker_report_template.md, write to {output_dir}/ln-652--global.md in single Write call

7) 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)

6. Event Channel Name Consistency

What: Publisher channel/topic name does not match subscriber channel/topic name

Detection:

• Step 1: Collect publisher channel names (extend Phase 2 trigger discovery):
• Migration triggers: extract string argument from pg_notify('channel_name', ...), NOTIFY channel_name
• Application code: Grep for \.publish\(["']|\.emit\(["']|redis.*publish\(["']|\.send_to\(["'] in src/, app/
• Extract: {channel_name, source_file, source_line, technology}
• Step 2: Collect subscriber channel names:
• PostgreSQL: Grep for LISTEN\s+(\w+) in application code (not just migrations)
• Redis: Grep for \.subscribe\(["']([^"']+) in src/, app/
• EventEmitter/WebSocket: Grep for \.on\(["']([^"']+) in handler/listener directories
• Extract: {channel_name, source_file, source_line, technology}
• Step 3: Cross-reference publishers vs subscribers:
• Exact match: `publisher.chann