Change Data Capture Payload Prompt for Outbox Patterns

This prompt is designed for event-driven architects and platform engineers who need to turn extraction outputs into change data capture (CDC) events compatible with the outbox pattern. The core job is to take a set of extracted records—often from a document or unstructured source—and produce a strictly typed payload that includes the operation type (INSERT, UPDATE, DELETE), a before state, an after state, and source position metadata. The output must be ready for insertion into an outbox table, where it will be polled by a message relay (such as Debezium or a custom publisher) and emitted to a log like Kafka. The reader is expected to have an existing outbox table schema and a downstream consumer that expects exactly-once semantics.

Use this prompt when your extraction pipeline is the source of truth for a business event and you need to guarantee that every state change produces a single, ordered, replayable event. The prompt is most effective when you can supply the current extracted state alongside the previously known state, allowing the model to compute the delta and assign the correct operation type. It is not a replacement for a database transaction log; it is a bridge between unstructured data extraction and a structured event stream. Do not use this prompt for high-frequency, low-latency CDC directly from a database—native log-based CDC tools are the right choice there. This prompt is for cases where the source of change is an AI extraction result, not a database row mutation.

The prompt requires several inputs to function correctly: the current extracted payload, the previous known state (if any), the target outbox table schema, and a source position identifier (such as a document version, extraction timestamp, or log sequence number). Without the previous state, the model cannot reliably determine whether the operation is an INSERT or an UPDATE. The output must include an aggregate_id for partitioning, an aggregate_type for routing, and a monotonically increasing source_position for ordering guarantees. The model is instructed to detect no-op changes—where the before and after states are semantically identical—and suppress those events to avoid polluting the outbox with empty deltas.

Before deploying this prompt into a production pipeline, you must implement a validation harness that checks the output against the outbox table schema, verifies that the source_position is strictly greater than the last published position, and confirms that the operation field is one of the allowed enum values. For high-risk domains such as financial or compliance systems, add a human review step for DELETE operations and for any event where the model's confidence in the delta is flagged as low. The prompt template includes a [RISK_LEVEL] parameter that adjusts the verbosity of the model's reasoning and the strictness of its no-op detection. Start with standard risk and escalate to high when the outbox feeds into a system of record.

This template is designed to be dropped into an extraction pipeline where the model has already produced a structured record from source text. Its job is to compare that extracted record against a previous state and emit a change data capture event suitable for an outbox table. The prompt expects you to supply the current extraction, the previous state (or null for inserts), a target schema, and ordering metadata. Every placeholder is enclosed in square brackets so you can substitute values programmatically before sending the prompt to the model.

textCopy
You are a CDC event builder for an outbox pattern. Your output must be a single JSON object conforming to [OUTPUT_SCHEMA].

## Inputs
- Current extracted record: [CURRENT_RECORD]
- Previous state (null if this is an insert): [PREVIOUS_STATE]
- Source document identifier: [SOURCE_ID]
- Extraction timestamp (ISO 8601): [EXTRACTION_TS]
- Outbox sequence hint (monotonically increasing integer): [SEQUENCE_HINT]

## Operation Rules
1. Compare [CURRENT_RECORD] with [PREVIOUS_STATE] field by field.
2. If [PREVIOUS_STATE] is null, set `operation` to `"insert"` and `before` to null.
3. If any field value differs, set `operation` to `"update"`. Include the full `before` and `after` payloads.
4. If no fields differ, set `operation` to `"noop"`. Include `before` and `after` as identical payloads.
5. If [CURRENT_RECORD] is null or empty and [PREVIOUS_STATE] is not null, set `operation` to `"delete"` and `after` to null.
6. Never invent fields. Use only the fields present in [OUTPUT_SCHEMA].
7. Set `source_position` to a string combining [SOURCE_ID] and [EXTRACTION_TS] in the format `"[SOURCE_ID]@[EXTRACTION_TS]"`.
8. Set `sequence` to [SEQUENCE_HINT]. The consumer will use this for ordering, not you.

## Constraints
- Do not include commentary, explanations, or markdown fences.
- Return only the JSON object.
- If you cannot determine the operation with certainty, set `operation` to `"update"` and set `confidence` to `"low"`.
- For regulated or high-risk domains, flag the event with `"requires_review": true` when confidence is low.

Adaptation notes: Replace [OUTPUT_SCHEMA] with your actual JSON Schema definition for the outbox event envelope—this should include fields like event_id, aggregate_id, operation, before, after, source_position, sequence, timestamp, and confidence. If your outbox table uses a different ordering mechanism (e.g., a database-generated sequence), remove [SEQUENCE_HINT] and adjust rule 8. For teams using Avro or Protobuf, add a constraint that field ordering must match the schema exactly. Always validate the model's output against [OUTPUT_SCHEMA] in your application layer before writing to the outbox table—never trust the model to get the schema right on every call.

What to do next: Wire this prompt into a pipeline step that runs after extraction and before outbox insertion. Add a JSON Schema validator in your harness that rejects malformed events and triggers a retry or dead-letter queue. For high-throughput systems, consider batching comparisons and using a cheaper model for the diff step, reserving a more capable model only for ambiguous cases flagged with confidence: "low". Avoid using this prompt for streaming CDC from databases—it is designed for extraction-derived events, not log-based capture.

This prompt is designed to sit between your extraction layer and your outbox table writer. The model receives a source document and a previous state snapshot, then produces a structured CDC payload containing operation, before, after, changed_fields, and source_position. The application layer is responsible for validating this payload, assigning the outbox sequence, and writing it transactionally alongside any business entity changes. Do not let the model generate the outbox ID, transaction ID, or publication timestamp—those must be assigned by the application to preserve ordering and idempotency guarantees.

Validation and retry flow. Before writing to the outbox, validate the model output against a strict schema: operation must be one of insert, update, or delete; before and after must be valid JSON objects or explicit null; changed_fields must be a string array listing only fields present in the after object; source_position must include a non-empty document_id and a span_start integer. If validation fails, retry once with the validation error message appended to the prompt as a correction hint. If the retry also fails, route the record to a dead-letter queue with the original input, both failed outputs, and the validator error trace. For delete operations, require that after is null and changed_fields is empty—this is a common failure mode where models hallucinate residual fields.

Exactly-once delivery and outbox ordering. Use a database transaction to insert the validated CDC payload into your outbox table with an auto-incrementing sequence_id, a deterministic idempotency_key (hash of document_id + operation + source_position.span_start), and a published_at timestamp set to NULL. Your outbox poller or Debezium connector reads unpublished rows in sequence_id order and marks published_at after successful publication. If the same idempotency key appears again, skip insertion. This pattern keeps the model focused on payload structure while the application owns delivery semantics. Log every outbox write with the model version, prompt version, and validation result for auditability. When upgrading the prompt, run a regression suite of known document-and-prior-state pairs to confirm that operation classification and changed_fields detection remain stable across versions.