Bitemporal Data Modelling for Insurance Systems

Here is a question that breaks a surprising number of insurance systems:

A claim occurs on 14 March. On 2 April, an endorsement is processed with an effective date of 1 March, increasing the sum insured. On 20 June, the claim is being adjudicated.

What was the sum insured for this claim?

And the second question, which is the one that breaks things:

What did our system say the sum insured was, on 20 March?

Those are different questions with different answers, and if your data model cannot answer both, you have a problem that will surface during an audit, a reserving exercise, or a dispute — and by then it is not fixable, because the information required was never retained.

Two independent time axes

Insurance data needs two dimensions of time, and they move independently.

Valid time — the period during which a fact was true in the real world. The sum insured was £500,000 from 1 March.

Transaction time — the period during which the system believed that fact. The system knew about the £500,000 from 2 April, when the endorsement was processed.

A backdated endorsement creates a gap between them. Between 1 March and 2 April, the sum insured was £500,000 in valid time, but the system believed it was £300,000. Any decision made in that window — a claim payment, a reserve, a regulatory return — was made on the old figure, and reconstructing why requires knowing what the system believed at the time, not what turned out to be true.

Single-temporal systems that model only valid time overwrite the old value when the endorsement is processed. The £300,000 belief is gone. You can answer the first question and not the second.

Why this is not a hypothetical

Four concrete situations that require the second axis:

Reserving. Your reserve at year-end was calculated on the information available then. When the auditor asks why the reserve was what it was, "here is what we know now" is not an answer. You need what you knew then.

Regulatory reporting. Returns must be reproducible as filed. If a restatement is required, you need both the original figures and the corrected ones, with the reason for divergence.

Claims disputes. A policyholder disputes a decision made in March. Defending it requires showing what the system presented to the adjuster on that date, not what the record says today.

Commission clawbacks. Commission was paid on a premium later reduced by a backdated endorsement. Calculating the clawback requires the premium as believed at payment time and as it stands now.

None of these are edge cases. All of them arrive within the first two years of operation.

Implementation

Two workable approaches.

Bitemporal tables. Every row carries four timestamps and rows are never updated in place:

CREATE TABLE policy_cover (
  policy_id      UUID NOT NULL,
  sum_insured    NUMERIC(15,2) NOT NULL,
  valid_from     DATE NOT NULL,
  valid_to       DATE NOT NULL,        -- real-world effectiveness
  tx_from        TIMESTAMPTZ NOT NULL, -- when we started believing it
  tx_to          TIMESTAMPTZ NOT NULL, -- when we stopped (or 'infinity')
  ...
);

"What did we believe on 20 March about 14 March?" becomes a query rather than an archaeology project:

SELECT sum_insured FROM policy_cover
WHERE policy_id = $1
  AND '2026-03-14' BETWEEN valid_from AND valid_to
  AND '2026-03-20' BETWEEN tx_from   AND tx_to;

Correcting a fact never destroys the prior belief: close the old row's tx_to and insert a new one. The old row remains, permanently, as the answer to what you used to think.

Event sourcing. Store the events — PolicyBound, EndorsementApplied, ClaimNotified — each carrying both an effective date and a recorded timestamp, then project current state. Historical belief is reconstructed by replaying events up to a cutoff transaction time.

More flexible, more machinery. Bitemporal tables are usually the pragmatic choice for policy and claim stores; event sourcing earns its complexity when you need to replay with different projection logic.

The cost, and why it is worth paying

Roughly fifteen per cent more implementation effort up front. Queries are more verbose. Developers need to understand the model, and the first few weeks produce bugs where someone forgets a transaction-time predicate and gets duplicate rows.

Against that: retrofitting is frequently impossible. Not expensive — impossible. If the system overwrote the £300,000 in April, that information does not exist anywhere. You cannot reconstruct a belief you did not record. The audit question has no answer and the honest response is "we cannot tell you."

That asymmetry is the whole argument. Fifteen per cent now against an unanswerable question later.

Practical notes

• Never UPDATE a fact. Close the current row's transaction time and insert. Enforce it at the database level if you can — application discipline erodes.
• Use infinity rather than NULL for open-ended tx_to, so range predicates work without special-casing.
• Index on (entity_id, tx_to) — the overwhelming majority of queries want current belief, and tx_to = 'infinity' should be fast.
• Build a current-state view so ordinary application code does not carry temporal predicates everywhere. Reserve the full model for the queries that need it.
• Test with a backdated endorsement in your fixtures from day one. It is the scenario that exposes every modelling mistake.
• Claims develop over years. A claim record is a time series, not a row, and the same reasoning applies to reserve movements.

Full guide — the six functional areas of insurance software, build versus buy, AI in claims and underwriting, and the integration surface: Insurance Software Solutions. We also review insurance data architecture.

Frequently Asked Questions

What is bitemporal data modelling?

Tracking two independent time dimensions: valid time, when a fact was true in the real world, and transaction time, when the system believed it. Backdated corrections create a gap between them, and answering audit questions requires both.

Why does insurance specifically need it?

Because backdated endorsements are routine and decisions get made on the information available at the time. Reserving, regulatory reporting, claims disputes and commission clawbacks all require knowing what the system believed on a past date, not what turned out to be true.

Can we add it later?

Usually not. Retrofitting requires history you never retained — if the old value was overwritten, that belief does not exist anywhere. This is one of the few architectural decisions that is genuinely irreversible rather than merely expensive.

What does it cost?

Around fifteen per cent additional implementation effort, more verbose queries, and a learning curve where developers forget transaction-time predicates. Against an audit question you would otherwise have no answer to at all.

Tables or event sourcing?

Bitemporal tables are usually the pragmatic choice for policy and claim stores. Event sourcing is more flexible and more machinery, and earns its complexity when you need to replay history through different projection logic.

What should we test from day one?

A backdated endorsement in your fixtures. It is the scenario that exposes every modelling mistake, and teams that add it late discover the gaps after the model is load-bearing.