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.channel_name == subscriber.channel_name` -> OK
  - Near-miss: Levenshtein distance <= 2 OR one is substring of the other -> flag as MISMATCH
  - Orphaned publisher: channel exists in publishers but not in subscribers -> flag as ORPHAN
  - Orphaned subscriber: channel exists in subscribers but not in publishers -> flag as ORPHAN

**Layer 2 Context Analysis (MANDATORY):**
- If channel name comes from shared config constant or env var (e.g., `CHANNEL = os.environ["EVENT_CHANNEL"]`) and both publisher and subscriber use same source -> NOT a mismatch
- If channel uses dynamic suffix pattern (e.g., `job_events:{job_id}`) and both sides use same template -> NOT orphaned
- Exclude test files (`**/test*/**`, `**/*.test.*`) from both publisher and subscriber discovery

**Severity:**
- **CRITICAL:** Channel name mismatch (near-miss: publisher sends to `job_events`, subscriber listens on `job_event`)
- **HIGH:** Orphaned publisher -- events sent but never consumed (data loss risk if events carry state changes)
- **MEDIUM:** Orphaned subscriber -- listener registered but no publisher found (dead code or future feature)

**Recommendation:**
- For mismatches: unify channel name to a single constant shared between publisher and subscriber
- For orphaned publishers: add subscriber or remove unused NOTIFY/publish
- For orphaned subscribers: add publisher or remove dead listener

**Effort:** S (fix typo/add constant) to M (design missing subscriber/publisher)

## Scoring Algorithm

**MANDATORY READ:** Load `references/audit_scoring.md`.

## Output Format

**MANDATORY READ:** Load `references/templates/audit_worker_report_template.md`.

Write JSON summary per `references/audit_summary_contract.md`. In managed mode the caller passes both `runId` and `summaryArtifactPath`; in standalone mode the worker generates its own run-scoped artifact path per shared contract.

Write report to `{output_dir}/ln-652--global.md` with `category: "Transaction Correctness"` and checks: missing_intermediate_commits, scope_too_wide, scope_too_narrow, missing_rollback, long_held_transaction, event_channel_consistency.

Return summary per `references/audit_summary_contract.md`.

When `summaryArtifactPath` is absent, write the standalone runtime summary under `.hex-skills/runtime-artifacts/runs/{run_id}/evaluation-worker/{worker}--{identifier}.json` and optionally echo the same summary in structured output.
```
Report written: .hex-skills/runtime-artifacts/runs/{run_id}/audit-report/ln-652--global.md
Score: X.X/10 | Issues: N (C:N H:N M:N L:N)
```

## Critical Rules

Apply the already-loaded `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

Apply the already-loaded `references/audit_worker_core_contract.md`.

- [ ] contextStore parsed successfully (including output_dir)
- [ ] scan_path determined
- [ ] Trigger/NOTIFY infrastructure discovered from migrations
- [ ] All 6 checks completed:
  - missing intermediate commits, scope too wide, scope too narrow, missing rollback, long-held, event channel consistency
- [ ] Findings collected with severity, location, effort, recommendation
- [ ] Score calculated using penalty algorithm
- [ ] Report written to `{output_dir}/ln-652--global.md` (atomic single Write call)
- [ ] Summary written per contract

## Reference Files

- **Audit output schema:** `references/audit_output_schema.md`

---
**Version:** 1.1.0
**Last Updated:** 2026-03-15