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.