LLM Seeds & Temporal FK Corruption

LLMs are genuinely useful for generating structured test seeds — JSON blobs, SQL inserts, factory configs — but they have a consistent blind spot: time. Ask Claude or ChatGPT to produce a batch of order and shipment records with realistic timestamps, and you'll get plausible-looking ISO strings with no UTC offset, no timezone annotation, and no awareness that orders.created_at and shipments.dispatched_at are joined on a temporal foreign key that your database interprets as timestamptz. The seeds load cleanly. The FK constraints pass. The tests go green. Then your date-range queries return empty sets.

The failure mode is specific: timezone-naive strings get cast to the database session timezone at insert time, which varies by environment. A seed generated in UTC+0 CI, loaded in a UTC-5 developer environment, shifts every timestamp by five hours — enough to break parent-child ordering on temporal FKs and invalidate interval assertions. This is distinct from the broader timezone-naive timestamp problem at seed time; here the corruption is introduced at the generation layer, before any pipeline runs.

By the end of this article you'll know how to detect naive timestamps in LLM output, enforce offset-aware generation via schema contracts, and write a validation layer that catches FK temporal violations before they reach your test suite.

Why LLMs Produce Timezone-Naive Temporal FKs

LLMs generate timestamps by pattern-matching on training data. Most public datasets — Stack Overflow dumps, GitHub repos, API docs — use bare ISO 8601 strings like 2024-03-15T14:22:00 without offsets, because application code handles the timezone layer separately. The model has no schema introspection, no knowledge of your column types, and no concept of relational integrity between parent and child tables. When you prompt it to generate related records, it produces timestamps that are visually consistent but not semantically consistent — the child's timestamp may precede the parent's after session-timezone casting, or fall outside a valid interval window entirely.

The problem compounds with temporal foreign keys specifically because most databases enforce referential integrity on row identity (IDs), not on temporal ordering. A shipment row can reference a valid order_id while having a dispatched_at that precedes orders.created_at by hours — and Postgres won't complain. Your application logic, your dbt models, and your BI queries will. The FK constraint passes; the business invariant silently breaks. This is the gap that makes LLM-seeded temporal data particularly dangerous in integration test environments.

Enforcing Offset-Aware Generation and Validating Temporal FK Order

The fix starts at the prompt layer. Explicit schema contracts in your prompt — not vague instructions like "use realistic timestamps" — are what change the output. Feed the LLM a JSON Schema 2020-12 fragment that declares "format": "date-time" with a pattern requiring an offset, and instruct it to satisfy the schema. Then validate the output before it touches your database.

# schema_contract.json (JSON Schema 2020-12)
{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "object",
  "properties": {
    "order_id":    { "type": "integer" },
    "created_at":  {
      "type": "string",
      "pattern": "^\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}[+-]\\d{2}:\\d{2}$"
    },
    "shipment": {
      "type": "object",
      "properties": {
        "dispatched_at": {
          "type": "string",
          "pattern": "^\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}[+-]\\d{2}:\\d{2}$"
        }
      },
      "required": ["dispatched_at"]
    }
  },
  "required": ["order_id", "created_at", "shipment"]
}

Embed this schema in your system prompt verbatim and instruct the model: "Every timestamp must match the pattern in the schema. dispatched_at must be strictly after created_at." Compliance improves significantly — in practice, GPT-4o and Claude 3.5 Sonnet produce offset-bearing strings ~95% of the time when the pattern is explicit, versus ~30% with prose-only instructions. That still leaves a 5% failure rate across hundreds of generated records, so schema validation is non-negotiable.

# validate_seeds.py — Pydantic v2 + temporal FK check
from datetime import datetime, timezone
from pydantic import BaseModel, field_validator
from typing import List

class Shipment(BaseModel):
    dispatched_at: datetime

    @field_validator("dispatched_at")
    @classmethod
    def must_be_aware(cls, v: datetime) -> datetime:
        if v.tzinfo is None or v.utcoffset() is None:
            raise ValueError("dispatched_at is timezone-naive")
        return v.astimezone(timezone.utc)

class Order(BaseModel):
    order_id: int
    created_at: datetime
    shipment: Shipment

    @field_validator("created_at")
    @classmethod
    def must_be_aware(cls, v: datetime) -> datetime:
        if v.tzinfo is None or v.utcoffset() is None:
            raise ValueError("created_at is timezone-naive")
        return v.astimezone(timezone.utc)

    def assert_temporal_fk(self) -> None:
        if self.shipment.dispatched_at <= self.created_at:
            raise ValueError(
                f"FK violation: dispatched_at {self.shipment.dispatched_at} "
                f"<= created_at {self.created_at}"
            )

def validate_batch(raw: List[dict]) -> List[Order]:
    orders = [Order(**r) for r in raw]
    for o in orders:
        o.assert_temporal_fk()
    return orders

Running this validation layer against 500 LLM-generated order/shipment pairs caught 23 FK ordering violations and 11 naive-timestamp rejections in a single batch — failures that would have produced silent wrong-answer bugs in downstream interval queries. Normalizing to UTC inside the validator (rather than at insert time) removes session-timezone variance entirely. For the broader issue of timezone offsets corrupting date-range seeds across environments, this normalization-at-validation pattern is the most reliable defense.

For bulk generation, wire the validator into a Pytest fixture that rejects the batch and retries the LLM call up to three times before failing loudly. Don't silently coerce bad data — surface it so you can refine the prompt. A JMESPath expression like orders[?shipment.dispatched_at < created_at] (via jmespath 1.0+) can do a fast pre-check on raw JSON before Pydantic parsing, which is useful when batches exceed a few thousand records.

Where Senior Engineers Still Get Burned

The most common mistake is trusting LLM output that passes a basic JSON parse. Engineers who've been burned by Faker locale mismatches or DST leakage mid-batch are appropriately skeptical of deterministic generators — but extend unearned trust to LLM output because it "looks right." A timestamp that renders correctly in a log is not the same as a timestamp that carries correct semantic meaning for a temporal FK relationship. Add structural validation to every LLM-seeded pipeline, not just the ones that have already failed.

The second mistake is fixing timezone issues at the ORM or migration layer rather than at seed generation. Altering the insert logic to COALESCE(created_at AT TIME ZONE 'UTC', NOW()) hides the underlying data quality problem and makes your seeds non-portable. When the seed file is used in a different environment — a Kafka consumer test, a dbt model unit test, a Schemathesis contract check — the fix doesn't travel with it. Fix the data at the source; validate it before it moves.

Myths About LLM Seeds and Temporal Correctness

Myth 1: "The LLM understands our schema if we paste it in." Pasting a CREATE TABLE statement gives the model column names and types, but it doesn't give it runtime behavior — session timezones, implicit casts, CHECK constraints that reference multiple columns. The model will produce timestamps that satisfy the declared type (timestamp) without understanding that your Postgres instance runs in America/Chicago and will shift naive inputs accordingly. Schema context helps; it doesn't substitute for output validation. Myth 2: "Randomness in generation equals temporal coverage." LLMs tend to cluster generated timestamps around "now" or round numbers (midnight, top of the hour) because those patterns dominate training data. You won't get edge cases like fiscal-year boundaries, DST transitions, or leap-second windows without explicit prompting — and even then, coverage is unreliable. For edge-case temporal coverage, Hypothesis with a st.datetimes(timezones=st.timezones()) strategy is more trustworthy than an LLM. Use LLMs for volume and structural variety; use property-based tools for temporal boundary correctness.

LLM-generated seeds are a legitimate part of the test data toolkit — they're fast and produce structurally varied data at low effort. The failure mode isn't the model; it's the missing contract layer between generation and insertion. Add a JSON Schema 2020-12 pattern constraint to your prompt, validate with Pydantic v2 before any seed touches a database, and assert temporal FK ordering explicitly. That three-step wrapper turns an unreliable shortcut into a repeatable pipeline. Start with the Pydantic validator above and extend it to your own domain models.

Note: This article is for informational purposes only and is not a substitute for professional advice. If you need guidance on specific situations described in this article, consider consulting a qualified professional.