Database Insert-Ready Record Prompt

This prompt is for data engineers and pipeline builders who have already assembled a coherent source record from extracted fields and now need to shape it for direct insertion into a target database table. The job-to-be-done is strict database-level shaping: taking a source record, a target DDL, and a column mapping specification, then producing a fully validated, insert-ready payload. The ideal user already has field extraction working upstream and needs a reliable bridge between assembled records and the database ingestion layer.

Use this prompt when you need type casting that matches exact database column types, foreign key resolution against existing reference tables, constraint compliance checks (NOT NULL, UNIQUE, CHECK constraints), and a dry-run validation report before the record hits your database. The prompt expects three concrete inputs: a source record as a JSON object with extracted fields, a target DDL as a CREATE TABLE statement or equivalent schema definition, and a column mapping specification that defines how source fields map to target columns, including any transformation rules. The output is an insert-ready payload with a validation report that flags type mismatches, missing required fields, constraint violations, and unresolved foreign keys before you attempt the actual INSERT.

Do not use this prompt for initial field extraction from raw documents—that belongs upstream in the extraction pipeline. Do not use it for cross-document entity resolution or deduplication—those are separate assembly concerns. This prompt assumes you already have a coherent source record and need database-level shaping. If your source record has conflicting field values, resolve those conflicts before passing the record here. If your target schema has complex triggers, cascading rules, or application-layer validation that cannot be expressed in DDL, you will need additional product code beyond what this prompt validates. For high-stakes production databases, always run the dry-run validation report through a human review step before executing the actual insert, especially when foreign key resolution depends on fuzzy matching or when constraint violations could cascade into downstream systems.

This template takes assembled extraction fields and shapes them into a payload that matches your target database schema exactly. Replace each square-bracket placeholder with your actual values before sending the prompt to the model. The prompt enforces type casting, required-field validation, foreign key resolution, and constraint compliance so the output can flow directly into an INSERT statement or ingestion pipeline with minimal post-processing.

textCopy
You are a database record formatter. Your job is to convert extracted fields into a single JSON record that matches the target database table schema exactly.

## INPUT
Extracted fields with their values and confidence scores:
[EXTRACTED_FIELDS]

## TARGET SCHEMA
Table: [TABLE_NAME]
Columns and constraints:
[TABLE_DDL]

## OUTPUT REQUIREMENTS
1. Produce a single JSON object where every key matches a column name in the target schema.
2. Cast every value to the column's declared type. If casting fails, set the value to null and add an entry to the `_cast_errors` array.
3. For required columns (NOT NULL), if the extracted value is missing or null, set the value to null and add an entry to the `_missing_required` array.
4. For foreign key columns, resolve the extracted value to the canonical identifier using the provided lookup map. If resolution fails, set the value to null and add an entry to the `_fk_resolution_failures` array.
5. For enum columns, map the extracted value to one of the allowed enum values. If no match, set to null and add an entry to the `_enum_mismatches` array.
6. For columns with CHECK constraints, validate the value against the constraint expression. If validation fails, set to null and add an entry to the `_constraint_violations` array.
7. Include a `_record_metadata` object with:
   - `assembly_timestamp`: current UTC ISO 8601 timestamp
   - `overall_confidence`: the minimum confidence score across all non-null fields
   - `null_field_count`: number of fields set to null
   - `warning_count`: total entries across all error arrays
8. Include a `_dry_run_result` object with:
   - `insertable`: boolean, true only if `_missing_required` is empty and all type casts succeeded
   - `blocking_issues`: array of strings summarizing what prevents insertion
   - `warnings`: array of strings summarizing non-blocking issues

## FOREIGN KEY LOOKUP MAP
[FK_LOOKUP_MAP]

## ENUM VALUE MAPS
[ENUM_MAPS]

## OUTPUT SCHEMA
Return ONLY a valid JSON object. No markdown fences, no commentary. The object must have this structure:
{
  "[column_name]": value,
  ...
  "_cast_errors": [{"column": "string", "extracted_value": "string", "target_type": "string", "error": "string"}],
  "_missing_required": ["column_name"],
  "_fk_resolution_failures": [{"column": "string", "extracted_value": "string", "lookup_key": "string"}],
  "_enum_mismatches": [{"column": "string", "extracted_value": "string", "allowed_values": ["string"]}],
  "_constraint_violations": [{"column": "string", "value": "string", "constraint": "string"}],
  "_record_metadata": {
    "assembly_timestamp": "string",
    "overall_confidence": number,
    "null_field_count": number,
    "warning_count": number
  },
  "_dry_run_result": {
    "insertable": boolean,
    "blocking_issues": ["string"],
    "warnings": ["string"]
  }
}

## CONSTRAINTS
- Never invent values not present in the extracted fields.
- If a column has no corresponding extracted field, treat it as missing.
- Preserve the original extracted value in error objects for auditability.
- If overall_confidence is below [CONFIDENCE_THRESHOLD], set `insertable` to false and add a blocking issue.

Before wiring this into production, test the template against a golden set of extracted fields where you know the correct database record. Compare the model's output to your expected record, paying special attention to type casting edge cases (empty strings to integers, date format variations, truncated decimals) and foreign key resolution failures. If your table has more than 30 columns, consider splitting the DDL into a separate system prompt to avoid token pressure on the extraction fields. For high-throughput pipelines, add a post-generation validation step that runs the output through your actual INSERT statement in a transaction that rolls back, catching schema mismatches before they reach production.

This prompt is designed to sit at the final stage of an extraction-to-ingestion pipeline, immediately before data lands in a target table. The harness should treat the prompt as a schema compiler: it receives an assembled record and a DDL specification, and it must produce a payload that passes a dry-run INSERT. The application layer is responsible for providing the exact target DDL, the assembled record, and any foreign key resolution context. The model should never be trusted to guess column types, nullable constraints, or foreign key values from memory.

The implementation loop follows a clear sequence: (1) assemble the prompt with the target DDL, the assembled record, and any lookup tables for foreign key resolution; (2) call the model with response_format set to a strict JSON schema matching the expected INSERT payload shape; (3) validate the output against the DDL using a schema validator that checks column names, types, nullability, and foreign key existence; (4) if validation fails, feed the error messages back into a retry prompt with the original context and the specific violation details; (5) if validation passes, execute a dry-run INSERT in a transaction and roll it back to confirm the database accepts the payload; (6) log the final payload, the model version, the prompt hash, and any confidence flags before committing. For high-risk tables, insert a human approval step between validation and commit. Use a model with strong JSON mode and low creative temperature (0.0–0.1). Avoid models that hallucinate column names or invent enum values when the DDL is explicit.

Common failure modes to instrument for: the model omits a NOT NULL column, casts a string to the wrong type, invents a foreign key value not present in the provided lookup table, or silently drops a column that exists in the DDL. Your harness should catch all of these before the dry-run. Log every validation failure with the violating field, the expected constraint, and the model's output. If retries exceed a configurable limit (start with 2), escalate the record to a human review queue with the full context. Never allow a record that failed validation to reach a production INSERT. For teams using change data capture or event sourcing, attach the prompt version and model identifier to every record as metadata columns so downstream consumers can trace provenance.