A broker asks you to backdate a cover change to three months ago. How do you model the policy so that works?

Two clocks, not one

A policy has two independent timelines and the whole design turns on keeping them apart. The first is business time: the span over which a fact is true of the risk. The vehicle was garaged in Leeds from 1 April, the sum insured was £250,000 from 12 June, the additional driver was on cover from 3 March. The second is system time: the instant at which your organisation came to know that fact and wrote it down. Backdating means precisely that the two disagree, by three months in the question as asked.

The vocabulary varies by market. UK personal lines call a change a mid-term adjustment; US commercial lines call the same thing an endorsement, and the document evidencing it an endorsement or a revised declarations page. The underlying model is the same. What you are storing is not a policy, it is an ordered sequence of transactions against a policy term, each of which carries an effective date, a recorded timestamp, and the delta or the full snapshot it produces.

What the two clocks let you answer

Only a bitemporal model can answer both of the questions the business will ask, and it will ask both.

The business-time question is "what cover was in force on 14 March?" You answer it by taking every transaction whose effective interval contains 14 March and folding them in order. This is the question that claims handling asks, because coverage is determined by the terms in force at the date of loss and by nothing else.

The system-time question is "what did we believe the cover was on 14 March?" You answer it by discarding every transaction recorded after that instant and then asking the business-time question against what remains. This is the question that audit, complaints handling, disputed claims and regulators ask, and it is the one a single-timeline design cannot answer at all. If a customer complains that the certificate they were sent in March was wrong, you have to be able to regenerate that certificate exactly, including its error, and then show the transaction that corrected it and when it arrived.

Storage shape

The pragmatic shape is a header row per policy term plus an append-only policy_version table, one row per transaction, keyed by policy term and version number:

-- effective_from/to is business time: when the cover applies.
-- recorded_at/superseded_at is system time: when we knew it.
-- No row is ever UPDATEd except to stamp superseded_at.
CREATE TABLE policy_version (
  policy_term_id   bigint      NOT NULL,
  version_no       int         NOT NULL,
  txn_type         text        NOT NULL,  -- new-business | endorsement | cancellation | reinstatement
  effective_from   date        NOT NULL,
  effective_to     date,                  -- null = to end of term
  recorded_at      timestamptz NOT NULL,
  superseded_at    timestamptz,           -- null = current knowledge
  snapshot         jsonb       NOT NULL,  -- fully resolved risk, cover, limits, excesses
  PRIMARY KEY (policy_term_id, version_no)
);

Two decisions in that sketch are worth defending in an interview. Storing a resolved snapshot rather than only a delta costs space but makes as-at reads a single indexed lookup instead of a fold over an unbounded chain, and it means a schema change to the risk model does not silently alter how a five-year-old policy replays. And stamping superseded_at rather than deleting is what keeps system time queryable; the row that was wrong has to survive.

Several databases give you part of this natively. SQL:2011 defined both application-time period tables, which are business time, and system-versioned tables, which are system time; SQL Server, MariaDB and Db2 implement system versioning with automatic history tables. That helps with the second clock. It does not model the first for you, because effective dating in insurance interacts with premium, documents and term boundaries in ways no generic mechanism knows about.

Why the recalculation is the hard part

Suppose the term runs from 1 January. In April you recorded an endorsement adding a driver effective 1 April, and charged a pro-rata additional premium for the remaining nine months. Now, in July, a backdated change arrives effective 1 February: the declared annual mileage was understated. That change alters the rated premium for the whole term, which means the April endorsement's additional premium was computed against a base that no longer exists.

You cannot leave it. Either you replay the transaction chain from the earliest affected effective date and restate the premium for every later transaction, issuing one net adjustment to the customer, or you take the position that earlier transactions are financially closed and price only the incremental difference forward. Both are defensible and different insurers do both; what is not defensible is having no explicit rule, because then the premium the system holds depends on the order in which changes happened to be keyed. The reason this bites is that the financial transactions have already left the building: premium has been written, commission has accrued to the broker, tax has been recognised, and instalments have been collected. Restating the ledger requires reversal and re-issue entries, not an in-place correction, and the reversals have to be dated in system time even though they concern business time in the past.

The related failure is the out-of-sequence read. If your current-state view is "the most recently recorded version wins", the July backdated change silently overwrites the April driver addition, because it was recorded later but is effective earlier. Ordering must be by effective date within the fold and by recorded time only for tie-breaks and for the system-time filter.

Boundaries that are not negotiable

An endorsement lives inside one policy term and cannot cross a renewal, because a renewal is a new contract with its own rates, its own terms and possibly a different insurer on the risk. A change genuinely spanning the boundary is two transactions. Similarly, cancellation is a transaction with an effective date like any other, so retroactive cancellation is expressible, but whether it is permitted is a legal and regulatory question that varies by market and product, not a modelling one. And a claim already settled against the term freezes nothing in your data model, but it does force a human decision, because the cover that was applied to that claim may no longer be the cover the policy records.

The single question that separates a real design from a plausible one is whether it can reproduce, byte for byte, a document you issued before the backdated change arrived. If it cannot, you have one timeline dressed up as two.