Row Versioning and History Tracking Prompt Template

This prompt is designed for data engineers and backend developers who need an AI model to produce versioned INSERT statements for temporal tables or slowly changing dimensions (SCD Type 2). The core job-to-be-done is generating records that carry complete temporal metadata—version numbers, effective date ranges (valid_from, valid_to), and current-flag indicators (is_current)—while enforcing critical business rules against overlapping date intervals and version sequence gaps. Use this when you are building AI-assisted data entry pipelines, migration workflows, or audit trail generation where every row must be traceable through time. The ideal user has a target table schema with temporal columns already defined and can provide the natural-language description of the change event that triggered the new version.

The prompt requires specific context to function reliably. You must supply the target table schema including column names, data types, and which columns serve as the business key, surrogate key, version number, and date range. You also need to provide the current state of the affected row (the existing active record) and a description of the change that is occurring—whether it's an update to a tracked attribute, a new entity insertion, or a logical deletion. The model uses this context to expire the old record by setting its valid_to date and is_current flag, then generates a new INSERT with an incremented version number and a contiguous valid_from timestamp. Without the current row state, the model cannot correctly close the previous version or prevent date range overlaps.

Do not use this prompt for simple append-only tables, log streams without versioning, or workflows where the model must also execute the SQL against a live database. It is not suitable for tables that lack temporal columns, for bulk-upsert patterns that rely on MERGE statements, or for scenarios where versioning logic is handled entirely by database triggers or temporal table features (e.g., SQL:2011 system-versioned tables). If your workflow requires the model to generate the initial table DDL, handle schema migration, or resolve foreign key conflicts across multiple related tables, use a different prompt from the Database Row and Record Mapping group. This prompt assumes the temporal infrastructure already exists and focuses exclusively on correct row versioning.

This prompt template instructs the model to produce a series of SQL INSERT statements that implement a Slowly Changing Dimension (SCD) Type 2 pattern. It is designed to be wired into a data pipeline where the application layer provides the target table schema, a natural key to identify the entity, the new record values, and the existing version history for that entity. The model's job is to calculate the correct version number, set the effective_start_date and effective_end_date for the previous and new records, and toggle the is_current flag, ensuring no gaps or overlaps in the timeline.

textCopy
You are a database engineer specializing in temporal table management. Your task is to generate SQL INSERT statements to implement a Type 2 Slowly Changing Dimension (SCD) for a given entity.

Follow these rules strictly:
1.  **Schema Adherence:** Use the exact table schema provided in [TABLE_SCHEMA]. Do not add or remove columns.
2.  **Natural Key:** The entity is uniquely identified by the column(s) specified in [NATURAL_KEY].
3.  **Versioning Columns:** The table uses the following columns for versioning, which you must manage:
    - [VERSION_COLUMN]: An integer that increments by 1 for each new version.
    - [START_DATE_COLUMN]: The timestamp when this version became effective.
    - [END_DATE_COLUMN]: The timestamp when this version was superseded. Use a far-future sentinel date (e.g., '9999-12-31') for the current version.
    - [CURRENT_FLAG_COLUMN]: A boolean or integer flag (e.g., TRUE/FALSE or 1/0) indicating the current active record.
4.  **Input Data:** You will receive:
    - [NEW_RECORD_VALUES]: A JSON object containing the new column values for the entity.
    - [EXISTING_VERSION_HISTORY]: A JSON array of the current rows for this entity from the database, ordered by [VERSION_COLUMN] ascending.
5.  **Logic:**
    a. Compare [NEW_RECORD_VALUES] with the latest record in [EXISTING_VERSION_HISTORY] (where [CURRENT_FLAG_COLUMN] is true).
    b. If the relevant attribute columns have changed, generate two INSERT statements:
        i.  **Expire the old record:** An INSERT for a new version of the old record with [END_DATE_COLUMN] set to [EFFECTIVE_TIMESTAMP], [CURRENT_FLAG_COLUMN] set to false, and [VERSION_COLUMN] incremented by 1.
        ii. **Insert the new record:** An INSERT for the new values with [START_DATE_COLUMN] set to [EFFECTIVE_TIMESTAMP], [END_DATE_COLUMN] set to '9999-12-31', [CURRENT_FLAG_COLUMN] set to true, and [VERSION_COLUMN] incremented by 2 from the last max version.
    c. If no relevant columns have changed, output a JSON object with a "no_change" status and a message. Do not generate any INSERT statements.
6.  **Output Format:**
    - If changes are detected, output a JSON object containing a single key "sql_statements" with an array of the generated SQL strings.
    - Ensure all SQL values are correctly quoted and escaped for the target database type specified in [DB_TYPE].
    - Use the provided [EFFECTIVE_TIMESTAMP] for all date assignments.

[TABLE_SCHEMA]
[DB_TYPE]
[NATURAL_KEY]
[VERSION_COLUMN]
[START_DATE_COLUMN]
[END_DATE_COLUMN]
[CURRENT_FLAG_COLUMN]
[EFFECTIVE_TIMESTAMP]
[NEW_RECORD_VALUES]
[EXISTING_VERSION_HISTORY]

To adapt this template, replace every square-bracket placeholder with concrete values from your application. The [NEW_RECORD_VALUES] and [EXISTING_VERSION_HISTORY] should be serialized JSON objects injected directly into the prompt. The [TABLE_SCHEMA] can be a CREATE TABLE statement or a detailed column list. For high-stakes data pipelines, always wrap this prompt in an application-level harness that validates the generated SQL against the schema, checks for overlapping date ranges, and requires human approval before executing any INSERT statements against a production database.

The prompt generates INSERT statements for a history or temporal table; it does not execute them. Your application layer must treat the model's output as a proposed transaction. Before execution, parse the generated SQL, validate that the version sequence is contiguous, that effective date ranges do not overlap for the same entity, and that exactly one row per entity carries is_current = true. Any violation should block the insert and trigger a retry or human review, not a silent write.

Wrap the parsed statements in a database transaction. The order of operations matters: insert the new current row first, then update the previous current row's valid_to and is_current flag within the same transaction. If the model produces multiple version inserts for a single entity, apply them in ascending version order. Use a strict SERIALIZABLE or equivalent isolation level to prevent concurrent history writes from corrupting the version chain. On any constraint violation—such as a duplicate version number or a date-range gap—roll back the entire transaction, log the failed payload and the model's raw output, and surface the specific validation error to the retry prompt.

For production pipelines, implement a pre-execution validation layer that checks: (1) valid_from < valid_to for every row, (2) no gaps between consecutive version date ranges for the same entity, (3) version numbers increment by exactly 1, and (4) the is_current flag is true for only the latest version. If the model output fails validation, feed the specific error message and the rejected rows back into a repair prompt rather than attempting heuristic fixes in code. For high-stakes data (compliance, financial, healthcare), always require human approval in the loop before the transaction commits, and retain the full prompt, model output, and validation result as an audit record.