Irreversible Database Write Confirmation Prompt

This prompt is for database administrators, SREs, and agent developers who need a reliable, auditable confirmation gate before an AI agent or automated system executes a destructive database operation. The job-to-be-done is preventing catastrophic data loss from DROP, TRUNCATE, or unqualified UPDATE/DELETE statements by forcing a structured review of the affected rows, schema impact, and available recovery options. The ideal user is an engineer integrating an AI agent into a production data pipeline where the agent has been granted tool access to a live database, and where a single malformed query can corrupt business-critical records.

Use this prompt when the agent's tool set includes direct database execution capabilities and the cost of a mistake is high—measured in data loss, revenue impact, or recovery time. It is specifically designed for environments where a human operator must explicitly approve the action after reviewing a machine-generated impact summary. The prompt template includes placeholders for the proposed SQL statement, the affected table schemas, row-count estimates, and a mandatory recovery plan. It also includes a validation instruction that prevents the agent from bypassing the gate through prompt injection or by omitting the confirmation step.

Do not use this prompt for read-only queries, idempotent inserts, or low-risk operations where the blast radius is a single row in a non-critical table. It is also inappropriate for agents that operate in a fully sandboxed environment with no production data access, or for workflows where a separate change management system (like a ticketing tool or deployment pipeline) already provides the approval gate. If your database platform supports transactional DDL with automatic rollback, you may still want this prompt for the audit trail it generates, but the recovery-plan section should be adapted to leverage those platform features. The next step after understanding this use case is to copy the prompt template, wire it into your agent's tool execution harness, and add eval checks that verify the gate cannot be skipped.

This prompt template creates a mandatory confirmation gate for any agent or automated system attempting to execute irreversible database operations. It forces the model to produce a structured impact assessment that a human operator must review before the write proceeds. The template is designed to be inserted as a tool-call interceptor or pre-execution hook in agent workflows that interact with production databases. It does not execute the write itself—it produces the approval request that a separate execution harness must validate and gate.

textCopy
You are a database safety gate. You will receive a proposed destructive database operation. Your job is to produce a structured confirmation request that a human operator must approve before execution. You must never approve, execute, or simulate the operation yourself.

## PROPOSED OPERATION
[OPERATION_STATEMENT]

## DATABASE CONTEXT
- Target database: [DATABASE_NAME]
- Target schema: [SCHEMA_NAME]
- Environment: [ENVIRONMENT]
- Requesting agent: [AGENT_ID]
- Request timestamp: [TIMESTAMP]

## CONSTRAINTS
- Do not execute, simulate, or approve the operation.
- Do not minimize or downplay the impact.
- If the operation statement is ambiguous, request clarification instead of assuming scope.
- If the operation lacks a WHERE clause on UPDATE or DELETE, flag it as a full-table mutation.
- If the operation is DROP or TRUNCATE, flag it as schema-destructive.
- Surface recovery options even if they are partial or time-consuming.

## OUTPUT SCHEMA
Return a JSON object with exactly these fields:
{
  "operation_type": "DROP" | "TRUNCATE" | "DELETE" | "UPDATE" | "OTHER",
  "is_destructive": true | false,
  "affected_table": "string",
  "estimated_affected_rows": "string (range or count, or 'unknown')",
  "schema_impact": "string (columns dropped, constraints removed, cascades triggered)",
  "where_clause_present": true | false,
  "where_clause_condition": "string (the filter condition, or 'NONE - full table')",
  "recovery_options": ["string (backup-based, point-in-time, transaction log, manual re-insert, etc.)"],
  "recovery_time_estimate": "string (best-case recovery duration)",
  "data_loss_permanence": "permanent" | "recoverable_with_effort" | "recoverable_easily",
  "risk_level": "critical" | "high" | "medium" | "low",
  "requires_peer_review": true | false,
  "clarification_needed": true | false,
  "clarification_question": "string (if clarification_needed is true, what must be clarified)",
  "approval_request_summary": "string (one-paragraph summary for a human reviewer)"
}

## EXAMPLES

Example 1: Unqualified DELETE
Input: "DELETE FROM users;"
Output includes: operation_type: "DELETE", is_destructive: true, where_clause_present: false, risk_level: "critical", requires_peer_review: true

Example 2: Targeted UPDATE with WHERE
Input: "UPDATE orders SET status = 'cancelled' WHERE order_date < '2023-01-01';"
Output includes: operation_type: "UPDATE", is_destructive: true, where_clause_present: true, estimated_affected_rows: "depends on table size", risk_level: "high"

Example 3: DROP TABLE
Input: "DROP TABLE audit_log_2022;"
Output includes: operation_type: "DROP", is_destructive: true, schema_impact: "Table and all data permanently removed. Dependent views and foreign keys may break.", data_loss_permanence: "permanent"

## RISK_LEVEL
[RISK_LEVEL] is one of: "critical" | "high" | "medium" | "low". Use the organization's risk matrix. Default to "high" for any unqualified mutation on a production table.

To adapt this template, replace the square-bracket placeholders with values from your agent's tool-call context. The [OPERATION_STATEMENT] should be the exact SQL or ORM command the agent proposes to execute. The [DATABASE_NAME], [SCHEMA_NAME], and [ENVIRONMENT] fields provide context that helps the model assess blast radius—production environments should trigger stricter risk levels. The [AGENT_ID] and [TIMESTAMP] fields support audit trail requirements. If your organization uses a formal risk matrix, replace the [RISK_LEVEL] guidance with your specific tier definitions and approval thresholds. The output schema is intentionally flat and deterministic to simplify downstream validation: every field must be present, and enum values must match exactly. Before deploying, test this prompt against edge cases including empty tables, views, materialized views, and operations wrapped in transactions that could be rolled back—the model should still flag them as requiring confirmation if they are destructive.

This prompt is designed to sit immediately before any database tool call classified as destructive: DROP, TRUNCATE, unqualified UPDATE, or unqualified DELETE. It should not be used for read-only queries or for writes that are inherently reversible (e.g., INSERT with a known rollback window). The harness must intercept the agent's proposed tool call, extract the SQL statement and target connection, populate the prompt's [PROPOSED_STATEMENT], [TARGET_CONNECTION], [ESTIMATED_AFFECTED_ROWS], and [SCHEMA_CONTEXT] placeholders, and then route the model's structured output to a decision engine. The model itself never executes the SQL; it only produces a confirmation assessment that the harness acts upon.

Integration flow: The agent framework (e.g., LangChain, a custom MCP server, or a tool-execution middleware) must implement a pre-execution hook on all database-write tools. When a tool call is detected, the hook pauses execution and calls this confirmation prompt. The harness should provide the model with: (1) the exact SQL the agent intends to run, (2) the database connection identifier and target table(s), (3) an EXPLAIN or row-count estimate if available, and (4) relevant schema snippets showing column types, indexes, and foreign-key relationships. The model returns a JSON object with fields confirmation_required (boolean), risk_level (string enum: low, medium, high, critical), affected_resources (array of table names), recovery_options (array of strings), and recommendation (string). The harness then evaluates confirmation_required and risk_level against a configurable policy. For critical or high risk, the harness must route to a human approval queue and block execution until a signed-off reviewer responds. For medium risk, the harness may allow execution but must log the full assessment to an audit trail. low risk writes can proceed with automated logging only.

Validation and anti-bypass checks: The harness must validate that the model's output is parseable JSON matching the expected schema. If parsing fails, retry once with a stricter output-format reminder appended to the prompt. The harness must also compare the affected_resources array against the tables actually referenced in [PROPOSED_STATEMENT]; a mismatch indicates either model hallucination or a prompt-injection attempt where the agent's SQL was altered after the confirmation was generated. In that case, block execution and escalate. Additionally, the harness must enforce that the confirmation prompt itself cannot be bypassed: the pre-execution hook must be non-removable at the tool-definition level, and any agent attempt to call the database tool directly without going through the hook must be rejected by the tool-execution runtime. Log every confirmation attempt—including the proposed SQL, model assessment, policy decision, and reviewer identity if escalated—to an append-only audit store. This log is your primary evidence for compliance reviews and incident postmortems.

Model choice and latency budget: Use a fast, instruction-following model for this gate (e.g., Claude 3.5 Haiku, GPT-4o-mini, or a fine-tuned smaller model) because this prompt sits on the critical path of database writes. The confirmation step should complete in under 2 seconds for low and medium risk assessments. If latency exceeds 3 seconds, the harness should time out and default to requiring human approval rather than auto-proceeding. For critical risk statements, latency is less important than thoroughness; you may route to a more capable model. Never cache confirmation responses across different SQL statements—each proposed write must receive a fresh assessment. If your database supports dry-run or EXPLAIN modes, run those before calling the prompt and include the output in [SCHEMA_CONTEXT] to ground the model's row-count estimates in real data rather than speculation.

What to avoid: Do not give the model access to execute the SQL itself. Do not skip the confirmation gate for statements that appear safe—attackers and buggy agents can craft UPDATE statements with hidden WHERE clauses or chained subqueries that expand blast radius. Do not treat the model's risk_level as the sole decision signal; always combine it with a human-defined policy that encodes your organization's risk tolerance. Finally, do not log the full SQL statement to observability systems that lack PII or credential redaction; the audit trail must be stored in a secured, access-controlled log store separate from general application logs.