Upsert Payload Extraction Prompt for CRUD APIs

This prompt is designed for the specific job of converting unstructured or semi-structured text—such as emails, logs, chat transcripts, or document extracts—into payloads that can be safely sent to a CRUD API using an upsert (insert or update) operation. The ideal user is an integration engineer or data pipeline builder who must guarantee that running the same extraction twice on the same source material does not create duplicate records in the target system. The prompt enforces a strict output contract that includes natural or composite key generation, explicit merge directives, and change detection fields, making it suitable for high-volume ingestion pipelines where data consistency and idempotency are non-negotiable requirements.

You should use this prompt when your pipeline must handle partial updates, conflicting information, or missing fields from the source text. For example, if a customer record arrives via email with only a new phone number, the prompt must produce a payload that updates only the phone field without nullifying other existing fields. The prompt requires you to define a conflict resolution strategy—such as 'last-write-wins' or 'source-priority'—and it will annotate the output with a merge_directive field (e.g., upsert, partial_update, skip) and a change_detection_hash to enable downstream deduplication. This is not a general extraction prompt; it is a contract-first prompt that assumes you already have a target API schema and need the model to map unstructured text to that schema reliably.

Do not use this prompt when you need raw, unshaped extraction without a target schema, or when the source text is already structured and can be directly mapped via deterministic code. It is also inappropriate for workflows where idempotency is not a concern, such as append-only logging or one-time data migration scripts. Before implementing, ensure you have defined the target API's upsert endpoint, the unique key fields, and the acceptable merge behaviors. The next step is to wire this prompt into an application harness that validates the output against your API contract before sending, logs every payload for auditability, and implements retry logic with exponential backoff for transient API failures.

This template is the core instruction set for converting unstructured text into a payload designed for an upsert (update or insert) operation. It forces the model to identify a natural or composite key, detect changes, and output a record that an integration engineer can send directly to a database or API endpoint. The placeholders allow you to inject your specific target schema, conflict resolution rules, and source document without rewriting the core logic. Use this when your downstream system requires idempotent writes and you need the AI to handle deduplication logic at extraction time.

textCopy
You are an integration engineer extracting data for an idempotent upsert operation.

Your task is to read the provided [INPUT_TEXT] and produce a single JSON object that conforms to the [TARGET_SCHEMA] and is ready for a CRUD upsert endpoint.

## Input Data
[INPUT_TEXT]

## Target Schema
```json
[TARGET_SCHEMA]

Upsert Key Strategy

Identify or construct the unique key for this record using the following rule: [KEY_STRATEGY]. If a reliable key cannot be constructed from the text, set the "_upsert_key" field to null and set "_confidence" to "low".

Change Detection

Compare extracted values against any provided [EXISTING_RECORD].

• If [EXISTING_RECORD] is empty, treat this as a new record insertion.
• If values match, set "_change_detected" to false.
• If values differ, set "_change_detected" to true and populate "_changed_fields" with an array of field names that changed.

Conflict Resolution

Apply the following merge strategy for conflicting fields: [CONFLICT_STRATEGY].

Output Constraints

1. Your entire response must be a single valid JSON object. No markdown fences, no surrounding text.
2. Include the following metadata fields in the root object:
   • "_upsert_key": string or null
   • "_confidence": "high" | "medium" | "low"
   • "_change_detected": boolean
   • "_changed_fields": [string, ...]
   • "_extraction_timestamp": string (ISO 8601)
3. For any field in [TARGET_SCHEMA] that cannot be extracted, use null. Do not invent data.
4. Normalize all dates to ISO 8601 UTC. Normalize all currency strings to decimal numbers.

Risk Level

[RISK_LEVEL]

To adapt this template, replace the square-bracket placeholders with your concrete values. [TARGET_SCHEMA] should be a JSON Schema object defining your target record shape, including types and required fields. [KEY_STRATEGY] must be an explicit rule, such as 'Concatenate email and company_name, lowercased' or 'Use the order_id field directly.' [CONFLICT_STRATEGY] should specify behavior like 'source_wins' or 'destination_wins' for each field group. If you are operating in a high-risk domain where an incorrect upsert could corrupt a customer record or financial ledger, set [RISK_LEVEL] to 'high' and ensure a human reviews the _changed_fields array before the payload is sent to the API. Always validate the output JSON against your [TARGET_SCHEMA] in your application code before executing the database operation.

This prompt is designed to sit inside an integration service that reads unstructured or semi-structured documents and writes to a target CRUD API. The typical call pattern is: receive a document, assemble the prompt with the target schema and conflict-resolution rules, call the LLM, validate the output payload, and then issue an upsert request to the downstream API. The prompt itself is stateless, so the application layer must supply the schema, the natural or composite key definition, and the merge strategy (e.g., 'update changed fields only', 'overwrite all', or 'insert if absent, else skip'). Because the output is a machine-readable payload destined for an API, strict validation is mandatory before any write occurs.

Wire the prompt into your application with a validation guard that checks: (1) the output is valid JSON matching the expected schema, (2) all required key fields are present and non-null, (3) field types match the target API contract (string, number, boolean, array, object), and (4) the merge directive field contains only allowed values. If validation fails, log the raw output and the validation errors, then retry with a repair prompt that includes the original output and the specific schema violations. Set a maximum of two repair retries before routing to a human review queue. For idempotency, generate a deterministic idempotency_key from the document source identifier and the extraction timestamp before calling the downstream API, and store it alongside the request for replay safety.

Choose a model that reliably produces structured JSON with low hallucination on extraction tasks—GPT-4o, Claude 3.5 Sonnet, or Gemini 1.5 Pro are good starting points. For high-throughput pipelines, consider a smaller fine-tuned model if you have a labeled dataset of document-to-payload pairs. Always log the full prompt, the raw model response, the validated payload, and the API response status code. In production, monitor for schema drift (new fields appearing, types changing) and extraction confidence drops. If the downstream API returns a 409 Conflict on an upsert, do not blindly retry; inspect whether the conflict is due to a stale version, a key collision, or a data inconsistency, and escalate accordingly. The most common failure mode is the model inventing values for required fields when the source document is silent—your validation layer must catch this and either force null or route to review.