Transaction Boundary Analysis Prompt

This prompt is built for backend engineers and platform teams who need to systematically review database transaction boundaries in code changes before they reach production. The primary job-to-be-done is preventing data integrity bugs—missing rollbacks, partial commits, dirty reads, and silent data loss—that slip past standard code review because transaction logic is notoriously difficult to reason about by eye. Use it when you have a concrete code diff, the relevant schema definitions, and knowledge of the target database system (PostgreSQL, MySQL, SQL Server, etc.). The prompt is designed to slot into CI/CD pipelines as a pre-merge gate, into structured code review checklists for high-risk changes, or into incident postmortem workflows where a transaction boundary defect is the suspected root cause.

The prompt assumes you can provide [DIFF], [SCHEMA_CONTEXT] (table definitions, constraint details, index information), and [DATABASE_SYSTEM] (e.g., PostgreSQL 16, MySQL 8.0) as inputs. It does not require runtime traces or production telemetry, but it also cannot guarantee detection of every transaction anomaly without them. Specifically, it will not catch race conditions that only manifest under specific concurrent load patterns, isolation anomalies that depend on production data distribution, or performance degradation from long-held locks under real traffic. For those, you need runtime transaction tracing, load testing, or database audit logs. The prompt is a static analysis partner, not a replacement for observability. Always pair its findings with your team's existing transaction review practices and escalate any finding marked as high-severity to a human reviewer with database expertise.

Before using this prompt, ensure your diff is scoped to a single logical change—feeding it an entire sprint's worth of commits will degrade analysis quality. If your codebase uses an ORM (Hibernate, SQLAlchemy, ActiveRecord, etc.), include the relevant entity or model definitions in [SCHEMA_CONTEXT] so the prompt can reason about implicit transaction boundaries the ORM may introduce. The prompt outputs a structured transaction flow analysis, a rollback safety assessment, and a ranked list of findings with severity levels. Use the severity rankings to decide what blocks a merge versus what gets logged as a warning. For regulated environments or systems handling financial data, require human sign-off on all findings before closing the review.

This prompt template is designed to be copied directly into your AI tool, configured with your specific code change and constraints, and executed to produce a structured transaction boundary analysis. It uses square-bracket placeholders for all dynamic inputs, ensuring you can adapt it to any codebase, language, or risk profile without modifying the core instruction logic. The template assumes you have a diff or code snippet ready and understand the target database system's transaction capabilities.

textCopy
You are a senior backend engineer specializing in database transaction integrity and concurrency control. Your task is to analyze the provided code change for transaction boundary problems.

## INPUT
Code Change (diff or full file):

[CODE_CHANGE]

codeCopy

Target Database System: [DATABASE_SYSTEM]
Language/Framework: [LANGUAGE_AND_FRAMEWORK]

## ANALYSIS REQUIREMENTS
1. **Transaction Scope Identification**: Map every database operation (read/write) to its enclosing transaction scope. Flag operations that execute outside any explicit transaction.
2. **Isolation Level Assessment**: Identify the isolation level used (or defaulted). Flag any operation that requires a stricter isolation level than what is configured to prevent phenomena like dirty reads, non-repeatable reads, or phantom reads.
3. **Nested Transaction Risk**: Detect nested transaction blocks, savepoints, or framework-managed transaction nesting. Flag risks where inner transaction rollback may not propagate correctly or where commit semantics are ambiguous.
4. **Partial Commit Vulnerability**: Identify any logical unit of work that spans multiple separate transactions without a compensating action or saga pattern, risking partial completion on failure.
5. **Rollback Safety**: For each transaction scope, assess whether a failure at any point leaves data in a consistent state. Flag missing rollback handlers or catch blocks that could silently commit partial state.

## OUTPUT SCHEMA
Return a JSON object with the following structure:
{
  "summary": "A one-paragraph executive summary of the most critical findings.",
  "findings": [
    {
      "id": "string",
      "severity": "CRITICAL | HIGH | MEDIUM | LOW",
      "category": "MISSING_TRANSACTION | ISOLATION_RISK | NESTED_TRANSACTION | PARTIAL_COMMIT | ROLLBACK_GAP",
      "location": "file:line_range or description of the code block",
      "description": "Clear explanation of the problem.",
      "current_behavior": "What the code currently does.",
      "risk": "What can go wrong in production under concurrent or failure conditions.",
      "remediation": "Specific, actionable fix guidance."
    }
  ],
  "transaction_flow_diagram": "A Mermaid.js sequence diagram or flowchart showing transaction boundaries, operations, and failure paths.",
  "rollback_safety_assessment": {
    "overall_score": "SAFE | RISKY | UNSAFE",
    "justification": "Explanation of the score.",
    "recommended_tests": ["List of specific test scenarios to validate the fix."]
  }
}

## CONSTRAINTS
- Only report findings that are present in the code change. Do not invent hypothetical problems.
- If no transaction boundaries are present, state that explicitly in the summary and return an empty findings array.
- Base isolation level analysis on the stated [DATABASE_SYSTEM]'s default and documented behaviors.
- The transaction flow diagram must use valid Mermaid.js syntax.
- If the code change is too large to analyze fully, note the scope limitation in the summary and focus on the most critical paths.

To adapt this template, replace [CODE_CHANGE] with the actual diff or file content, [DATABASE_SYSTEM] with the target database (e.g., PostgreSQL, MySQL, MongoDB), and [LANGUAGE_AND_FRAMEWORK] with the relevant stack (e.g., Python/SQLAlchemy, Java/Spring, Go/database/sql). For high-risk financial or healthcare systems, add a [RISK_LEVEL] placeholder and instruct the model to require human review for all CRITICAL findings. After running the prompt, validate the JSON output against the schema before integrating it into your CI pipeline or review dashboard.

The Transaction Boundary Analysis Prompt is designed to operate as a pre-merge check in a CI/CD pipeline or as a structured review tool within a code review platform. The prompt expects a code diff, optional database schema context, and a set of constraints such as the target isolation level and transaction manager. The output is a structured JSON report containing transaction flow diagrams, rollback safety assessments, and a list of findings ranked by severity. To integrate this into an application, you must wrap the prompt in a harness that validates the output schema, retries on malformed responses, logs every analysis for auditability, and enforces a human approval gate for high-severity findings before the code can be merged.

The implementation harness should first construct the prompt payload by injecting the code diff, schema context, and constraints into the template placeholders. After sending the request to the model, the harness must validate the response against a strict JSON schema that includes required fields such as findings, transaction_flow, rollback_safety, and severity_scores. If validation fails, the harness should retry up to two times with a repair prompt that includes the validation error message and the original malformed output. For high-risk repositories, configure the harness to automatically request human review when any finding has a severity of critical or when the rollback_safety score is below a configurable threshold. Log the full prompt, response, validation result, and reviewer decision to an observability platform for post-merge audits and prompt drift detection.

When choosing a model for this prompt, prefer one with strong code reasoning capabilities and a large context window to accommodate substantial diffs and schema definitions. Avoid using this prompt on diffs that exceed the model's context limit without first chunking the changes by file or module. The harness should also enforce a timeout and a maximum token limit to prevent runaway analyses on extremely large changesets. Finally, do not treat the model's output as a substitute for runtime transaction testing or database-level constraint validation; the prompt identifies risks and patterns, but only actual execution against a test database can confirm rollback safety and isolation behavior under concurrent load.