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

Parse context from contextStore
- Extract tech_stack, best_practices, db_config
- 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
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()

try

without

session.rollback()

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

See

shared/references/audit_scoring.md

for unified formula and score interpretation.

Output Format

Return JSON to coordinator:

json

{
  "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

Version: 1.0.0 Last Updated: 2026-02-04

ln-652-transaction-correctness-auditor

NPX Install

Tags

SKILL.md Content

Transaction Correctness Auditor (L3 Worker)

Purpose & Scope

Inputs (from Coordinator)

Workflow

Audit Rules (Priority: HIGH)

1. Missing Intermediate Commits

2. Transaction Scope Too Wide

3. Transaction Scope Too Narrow

4. Missing Rollback Handling

5. Long-Held Transaction

Scoring Algorithm

Output Format

Critical Rules

Definition of Done