Temporal Table Design Prompt

This prompt is for backend engineers and data architects who need to design a new temporal table or review an existing one. The job-to-be-done is producing a rigorous, implementation-ready schema that correctly handles system-versioned or application-time period tracking. You should use this when you are modeling entities whose state changes over time and you must answer point-in-time queries ('what did this record look like on March 1st?') or interval queries ('show me all changes between Q1 and Q2') with full auditability. The ideal user has a clear understanding of the business entity, its lifecycle, and the access patterns that will hit the table.

Do not use this prompt for simple audit logging where a separate append-only events table is sufficient, or for high-throughput append-only time-series data that is better served by a specialized time-series database. This prompt is also inappropriate when the temporal requirements are purely about created_at/updated_at timestamps without the need for non-destructive period tracking. If you are designing a slowly changing dimension for a data warehouse, prefer the 'Slowly Changing Dimension Type Selection Prompt' first, then return here for the physical schema design. The prompt assumes a relational database context; if you are using a temporal feature built into a specific DBMS (e.g., SQL:2011 system-versioned tables), you should provide that as a [CONSTRAINTS] input to avoid designing a custom solution that duplicates native functionality.

Before running this prompt, gather the entity's non-temporal attributes, the business keys that define identity across time, the expected mutation frequency, and the types of temporal queries the application will issue. The output is a DDL specification with period columns, a primary key strategy that includes the temporal dimension, and a set of query patterns. The real value of this playbook is the harness: it provides validation checks for temporal consistency rules (no overlapping periods for the same entity, no gaps unless allowed, proper constraint enforcement) that you can wire into your CI pipeline. After using the prompt, you must test the schema with concurrent write scenarios and verify that your chosen primary key strategy does not produce duplicates under race conditions.

The following prompt template is designed to be copied directly into your AI harness or development environment. It accepts a set of inputs describing your entity, its lifecycle, and the required temporal semantics, and it produces a concrete schema design. All placeholders use square brackets and must be replaced with your specific context before execution. The template is self-contained and can be used independently of the surrounding playbook.

textCopy
You are a senior database architect specializing in temporal data modeling. Your task is to design a temporal table schema based on the provided requirements.

## INPUT
- Entity Name: [ENTITY_NAME]
- Entity Description: [ENTITY_DESCRIPTION]
- Temporal Semantics: [TEMPORAL_SEMANTICS] (Choose: SYSTEM_VERSIONED, APPLICATION_TIME, or BITEMPORAL)
- Current Attributes: [ATTRIBUTES] (List each attribute with name, type, and whether it changes over time)
- Known Access Patterns: [ACCESS_PATTERNS] (List queries with their temporal predicates, e.g., "Get all versions of order 123 as of 2024-03-15")
- Retention Requirements: [RETENTION_REQUIREMENTS] (How long must history be kept? Any purging rules?)
- Target Database: [TARGET_DATABASE] (e.g., PostgreSQL, SQL Server, MySQL 8.0+)

## CONSTRAINTS
- The primary key must support both current and historical row identification.
- Period columns must use closed-open intervals `[start, end)` unless the target database enforces a different convention.
- Include CHECK constraints to prevent period overlaps and ensure start < end.
- For system-versioned tables, the system period columns must be generated automatically where the database supports it.
- For application-time tables, the application period columns must be managed by application code.
- Foreign keys referencing temporal tables must specify whether they reference the current row or a specific version.

## OUTPUT_SCHEMA
Return a JSON object with the following structure:
{
  "table_name": "string",
  "temporal_type": "SYSTEM_VERSIONED | APPLICATION_TIME | BITEMPORAL",
  "columns": [
    {
      "name": "string",
      "type": "string",
      "nullable": true,
      "description": "string",
      "temporal_role": "BUSINESS_KEY | SYSTEM_PERIOD_START | SYSTEM_PERIOD_END | APP_PERIOD_START | APP_PERIOD_END | ATTRIBUTE"
    }
  ],
  "primary_key": ["column_names"],
  "unique_constraints": [
    {
      "name": "string",
      "columns": ["column_names"],
      "predicate": "string or null"
    }
  ],
  "check_constraints": [
    {
      "name": "string",
      "expression": "string"
    }
  ],
  "foreign_keys": [
    {
      "name": "string",
      "columns": ["column_names"],
      "references_table": "string",
      "references_columns": ["column_names"],
      "temporal_scope": "CURRENT_ONLY | AS_OF_QUERY | FULL_HISTORY"
    }
  ],
  "indexes": [
    {
      "name": "string",
      "columns": ["column_names"],
      "type": "BTREE | GIST | other",
      "purpose": "string"
    }
  ],
  "query_patterns": [
    {
      "description": "string",
      "sql_template": "string with :placeholders",
      "temporal_predicate": "string"
    }
  ],
  "notes": ["string"]
}

## RISK_LEVEL
[TEMPORAL_SEMANTICS] determines risk. BITEMPORAL designs are high-risk and require explicit review of period alignment, constraint interactions, and query correctness across both time dimensions.

## EXAMPLES
[EXAMPLES] (Optional: Provide 1-2 example temporal table designs for the target database to guide style and conventions.)

Generate the schema design now.

To adapt this template, start by replacing [ENTITY_NAME] and [ENTITY_DESCRIPTION] with the specific business entity you are modeling. The [TEMPORAL_SEMANTICS] field is the most critical decision point: choose SYSTEM_VERSIONED if the database should automatically track row history, APPLICATION_TIME if your business logic controls the effective dates, or BITEMPORAL if you need both. For [ATTRIBUTES], be explicit about which columns change over time—this determines whether they belong in the temporal table or a separate current-state table. The [ACCESS_PATTERNS] field drives index design, so include every query you anticipate, especially those with AS OF or BETWEEN predicates on period columns. If you have existing temporal table examples in your codebase, add them to [EXAMPLES] to improve output consistency with your team's conventions.

Before using the output in production, validate that the generated primary key correctly handles multiple versions of the same business entity, that CHECK constraints prevent overlapping periods for the same business key, and that the query templates in query_patterns actually return the expected results for your access patterns. For BITEMPORAL designs, add an additional manual review step to verify that the interaction between system time and application time does not produce silent data corruption under concurrent writes. If your target database supports native temporal features (e.g., SQL Server system-versioned tables, PostgreSQL's temporal_tables extension), prefer those over hand-rolled period columns and note any deviations in the notes field.

The temporal table design prompt is not a one-shot code generator—it is a schema design advisor that must be embedded in a harness that validates temporal consistency rules before any DDL reaches a database. The harness should treat the model output as a design proposal, not executable SQL. After the model returns a schema, the harness must parse the proposed period columns, primary key strategy, and constraints, then run a suite of temporal-specific validation checks: every temporal table must have exactly one pair of period columns (start and end timestamps), the primary key must include the period columns or a surrogate plus a separate temporal unique constraint, and the period columns must be non-nullable with a defined default for the start column. If the model proposes overlapping period logic, the harness must verify that exclusion constraints or application-level checks are specified. These validations are not optional—a temporal table with a missing or malformed period definition silently becomes a regular table, breaking all point-in-time and interval queries that depend on it.

Wire the prompt into a pipeline with three stages: generation, structural validation, and temporal consistency verification. In the generation stage, call the model with the prompt template, providing the base table schema, access patterns, and temporal requirements as [INPUT]. Use a model with strong SQL and schema reasoning—Claude 3.5 Sonnet or GPT-4o are appropriate for this task. Set temperature low (0.0–0.2) because schema design requires deterministic, repeatable output. In the structural validation stage, parse the model output into a structured schema representation and check that all required temporal columns exist, types are correct (typically TIMESTAMPTZ or TIMESTAMP), and primary key definitions are syntactically valid. In the temporal consistency verification stage, apply domain-specific rules: verify that period_start <= period_end is enforced by a CHECK constraint, confirm that the primary key strategy handles overlapping periods correctly (either via exclusion constraints using tstzrange and GiST indexes, or application-level logic with explicit documentation), and validate that the proposed query patterns for AS OF and FOR clauses match the period column names. Log every validation failure with the specific rule violated and the model's raw output for debugging.

For retry logic, implement a two-tier approach. If structural validation fails—missing period columns, unparseable DDL, wrong data types—retry the prompt once with the validation error message appended as a [CONSTRAINTS] amendment. If temporal consistency verification fails, do not automatically retry; instead, surface the failure for human review because temporal logic errors often indicate the model misunderstood the access patterns or the input requirements. For production use, store every generated schema alongside its validation results in a schema registry or design log. This creates an audit trail for compliance and makes it possible to compare temporal table designs across iterations. If the temporal table will hold regulated data (financial transactions, healthcare records, audit logs), require explicit human approval before the schema is applied to any database. The harness should block automated DDL execution and route the design to a review queue with the full validation report attached.

When integrating with existing schema migration tooling, the harness should output the temporal table design in a format compatible with your migration framework—typically a migration file with up and down scripts. The up script contains the temporal table DDL; the down script must handle the reverse migration, including dropping the temporal period and converting the table back to a non-temporal form if needed. The harness should also generate a companion test script that inserts overlapping and non-overlapping periods, runs point-in-time queries at known timestamps, and asserts that the correct row versions are returned. This test script becomes part of your CI pipeline, ensuring that future schema changes do not break temporal query semantics. Finally, instrument the harness with observability: log the model used, prompt version, input schema hash, output schema hash, validation pass/fail status, and human approval timestamp. This data feeds into prompt regression testing and helps detect drift when model behavior changes across API versions.