Domain · Product
How product versioning works
A worked example with real, normalized data (normalized — each fact stored once, in separate linked tables). The design workbench loads a product as a nested array
(line → form → provision → options). That array is a design-time view; underneath,
production stores it as three tables — provisions, shareable options, and a junction
that links them with effective/expiry dates + version. This page shows those tables, then changes the
product and shows exactly what is written.
The model in one line: a provision (a choice, e.g. "Water Backup sub-limit") offers several options
($5,000 / $10,000 / …). You almost never edit an option's value — to change the product you add a new
option link or expire an existing one. The dates & version live on the link (junction), not on the
option — because an option is shareable across many provisions.
0 · A product has two versioned parts
PRODUCT = FORM (what is covered) + RATE (how much it costs)
─ provisions + their options ─ a rate per provision-option + a rating method
─ versioned by effective dates ─ versioned as rate versions 2026.1, 2026.2
on the junction (+ a label) ─ re-filed often, WITHOUT touching the form
Independent clocks: re-filing rates does not change the form; adding a coverage
option does not force a rate re-file. A bound policy pins both the form version and the rate version.
1 · The real shape — three tables (not one)
A provision has many options; an option is shareable — the same option record can hang
off more than one provision (and more than one product). So provisions and options are separate tables, joined by
a third.
Table 1 · provision — the choices/slots
| provision_id | name |
|---|
| PRV-COVA-LS | Coverage A — loss settlement |
| PRV-DED | All-perils deductible |
| PRV-WB | Water Backup sub-limit |
| PRV-JWL | Jewelry theft sub-limit |
Table 2 · option — reusable values (shareable)
| option_id | label |
|---|
| OPT-RC | Replacement Cost |
| OPT-ACV | Actual Cash Value |
| OPT-1000 | $1,000 |
| OPT-2500 | $2,500 |
| OPT-5K | $5,000 shared |
| OPT-10K | $10,000 |
| OPT-25K | $25,000 |
OPT-5K ("$5,000") is just a value — it isn't
"owned" by Water Backup. It can be linked under Water Backup and Jewelry (below). That's why dates can't
live on the option.
Table 3 · product_provision_option — the junction (this IS the "load array"), with effective / expiry / version
| product | provision_id | option_id | default? | effective | expiry | rate_id |
|---|
| HO3 | PRV-COVA-LS | OPT-RC | default | 2026-01-01 | — | R-COVA-RC |
| HO3 | PRV-COVA-LS | OPT-ACV | | 2026-01-01 | — | R-COVA-ACV |
| HO3 | PRV-DED | OPT-1000 | default | 2026-01-01 | — | R-DED-1000 |
| HO3 | PRV-DED | OPT-2500 | | 2026-01-01 | — | R-DED-2500 |
| HO3 | PRV-WB | OPT-5K | default | 2026-01-01 | — | R-WB-5K |
| HO3 | PRV-WB | OPT-10K | | 2026-01-01 | — | R-WB-10K |
| HO3 | PRV-JWL | OPT-5K | default | 2026-01-01 | — | R-JWL-5K |
Read the two highlighted rows: OPT-5K appears twice — once
under Water Backup, once under Jewelry — the same option record, two junction rows. Each junction row carries
its own effective / expiry / rate. That's where versioning lives — on the link, not on the option or
the provision.
2 · Changing the product — add & expire links, never edit a value
On 2026-04-01 we want a $25,000 Water Backup choice. We do not change the $5,000
option to $25,000 (that would rewrite history for everyone who chose $5,000). Two valid ways:
Case A · additive — keep $5k & $10k, ADD $25k
| provision | option | effective | expiry |
|---|
| PRV-WB | OPT-5K | 2026-01-01 | — |
| PRV-WB | OPT-10K | 2026-01-01 | — |
| PRV-WB | OPT-25K insert | 2026-04-01 | — |
New business (newly sold policies) after Apr 1 sees three choices. One row inserted; nothing else touched. The $25k option may already exist (shared) — you just link it.
Case B · replace — retire $5k, ADD $25k
| provision | option | effective | expiry |
|---|
| PRV-WB | OPT-5K | 2026-01-01 | 2026-03-31 set expiry |
| PRV-WB | OPT-10K | 2026-01-01 | — |
| PRV-WB | OPT-25K insert | 2026-04-01 | — |
New business after Apr 1 sees $10k & $25k. We set an expiry on the junction row — we do not delete it or edit OPT-5K.
Why the expiry goes on the junction, not the option: remember OPT-5K
is also linked under Jewelry. Expiring Water Backup's $5,000 must not disturb Jewelry's $5,000. Because
the expiry lives on the (product, provision, option) row, the Jewelry link is untouched —
and policies that already chose Water-Backup $5,000 still resolve their exact junction row forever.
So do we "clone the product to V2"? No. You insert one junction row (and maybe
set an expiry on another). The ~114 unchanged provisions/options aren't touched or copied at all — you write
only the delta.
3 · So what is "V1 / V2"? — a date window in its own table
Effective dates are the truth. A version label (V1, V2) is a row in a separate
product_version table that names an effective window — used for filing/communication. You
don't duplicate anything to make V2; you point a label at a date.
product_version (version lives in its OWN table)
┌──────────────┬──────────┬──────────────┬──────────────────────┐
│ product_ver │ label │ effective │ retired (new biz) │
├──────────────┼──────────┼──────────────┼──────────────────────┤
│ HO3-V1 │ ZA-HO3 V1│ 2026-01-01 │ 2026-03-31 │
│ HO3-V2 │ ZA-HO3 V2│ 2026-04-01 │ — │
└──────────────┴──────────┴──────────────┴──────────────────────┘
"V2" is NOT a copy of the product. It is the set of junction rows whose
[effective , expiry) window overlaps V2's window — resolved by a query.
Three things in three tables, on purpose: the option is shareable (no dates),
the junction carries effective/expiry, and the version label/window is its own table. Change the
product by writing junction rows; the version label just re-points at a date.
4 · How a product loads — the join that builds the nested array
The workbench's nested line → provision → options array is the result of
one join, filtered by the as-of date (or the version's window):
-- load HO-3's provisions & available options, as offered on :asof
SELECT pr.name AS provision, op.label AS option, ppo.is_default, ppo.rate_id
FROM product_provision_option ppo
JOIN provision pr ON pr.provision_id = ppo.provision_id
JOIN option op ON op.option_id = ppo.option_id
WHERE ppo.product = 'HO3'
AND ppo.effective <= :asof
AND (ppo.expiry IS NULL OR ppo.expiry >= :asof)
ORDER BY pr.sort, ppo.sort;
:asof = 2026-02-15 (V1) → Water Backup options = { $5,000*, $10,000 }
:asof = 2026-04-15 (V2) → Water Backup options = { $5,000, $10,000, $25,000 } (Case A)
or { $10,000*, $25,000 } (Case B)
(* = default)
A bound policy doesn't store "V2". It stores the option the customer actually picked
(the ppo_id / option_id) plus the rate version.
So a renewal, endorsement, or claim years later resolves the exact option + price that was sold — even after
V3, V4 add or expire other options. You query by what was pinned, never "whatever is current."
Endorsement vs. version. Adding/expiring options on the junction = the product
evolves (new business sees it). One in-force policy changing (this homeowner raises their Water Backup) =
an endorsement, which re-rates on the option set of that policy's
bound version — never silently on a newer one.
5 · Bitemporal — "two clocks" (for backdated changes)
Bitemporal (by-temporal, "two times") = every junction row records two dates:
when it's true in the real world (valid-time = effective/expiry) and when the system recorded it
(transaction-time). You need both the moment you do anything backdated.
On 2026-06-10 Compliance says the $25,000 option should have been available from
2026-04-01, but it was actually keyed on 2026-04-15 (two weeks late). We correct it without erasing
what we believed — so an audit can still see "what could a customer pick on Apr 20, as we knew it then?" vs "as we know it now?"
| junction row | valid from
(real world) | valid to | txn from
(we recorded) | txn to |
|---|
| PRV-WB · OPT-25K | 2026-04-15 | ∞ | 2026-04-15 | 2026-06-10 |
| PRV-WB · OPT-25K | 2026-04-01 | ∞ | 2026-06-10 | ∞ |
Row 1 = what we believed (available from Apr 15) until the Jun 10 correction.
Row 2 = the corrected truth (available from Apr 1). Nothing is overwritten — both are kept, so you can answer
"as-of any date, on either clock." A single "last_updated" timestamp can't do this.
6 · Rate — priced per option, on its own version
Each junction row points to a rate_id; the rate for an option lives in a
separate, separately-versioned rate table. A rating method
(R1 — the versioned pricing algorithm; it becomes R2 when the
formula itself changes, not just the numbers) turns the customer's selected options into a premium — each
option contributes its factor.
rate table (rate version 2026.1) — a row per provision-option
| rate_id | for option | rate effect |
|---|
| R-DED-1000 | All-perils ded $1,000 | base |
| R-WB-5K | Water Backup $5,000 | +$60 |
| R-WB-10K | Water Backup $10,000 | +$80 |
| R-WB-25K | Water Backup $25,000 | +$95 new w/ the option |
Adding the $25k option adds one rate row (R-WB-25K) —
it doesn't change the price of $5k or $10k. That's the pattern in one line: the rate may vary, but we're
providing a new option — pricing and product structure move independently.
Rating worksheet — the customer picked $25k Water Backup (Form V2 + Rate 2026.1)
| Step | Selected option | Factor | Running premium |
|---|
| Base rate | HO-3 · territory 12 (a geographic rating zone) · TIV $1.1M | — | $1,200 |
| Coverage A | Replacement Cost (default) | ×1.00 | $1,200 |
| Deductible | $2,500 (chosen) | ×0.91 | $1,092 |
| Water Backup | $25,000 (chosen) | +$95 | $1,187 |
| Catastrophe load | cat-model wildfire 78 | ×1.45 | $1,721 |
| Annual premium | frozen at bind w/ the picked option_ids + rate 2026.1 | $1,721 |
Rate version is independent. Re-file hurricane-season rates → Rate 2026.2
with the same Form V2 (same junction rows). The method (R1→R2) or just the tables change.
A bound policy locks both. the picked option_ids (its slice of the
junction) + rate_version 2026.1 → reproducible to the cent forever
(premium freeze).
7 · The authoring lifecycle — draft → effective → superseded
"Make the provision/option but don't put it into effect yet — experiment first" is a classic carrier
authoring step: it's the draft state — junction rows not yet given an effective date.
DRAFT ──set effective date──► EFFECTIVE (offered to new business) ──set expiry──► RETIRED
junction row exists but appears in the load query for its expiry date set;
has no effective date; [effective, expiry) window; bindable no new business, but
experiment freely existing policies keep it
The whole model is add-only in spirit: author by inserting junction rows; retire by
setting an expiry; never edit an option's value or delete a link. That's what keeps every past policy
reproducible.
⚖️ One-paragraph recapexpand ▼
Provisions and options are separate, normalized tables; options are shareable, so one option can
hang off many provisions. The link between them — the product_provision_option junction —
is where effective/expiry dates and the version live. To change a product you add a junction row (offer
a new option) and/or set an expiry on one (retire an option); you never edit an option's value or delete
a link, so history stays intact. A "version" (V1/V2) is just a labeled effective-date window in its own table — the
offered option set is resolved by a dated join, not by cloning. Each junction row carries its own
rate (separately versioned), and a bound policy pins the picked option_ids + rate version so it is
reproducible forever.