Temporal Versioning and Effective Dating Model

1. Core Idea

Enterprise data tidak hanya menjawab:

Apa nilai data sekarang?

Tetapi juga:

Apa nilai data pada waktu tertentu, versi tertentu, periode tertentu, atau saat keputusan bisnis dibuat?

Dalam CPQ / Quote / Order / Billing / Catalog / Telco BSS/OSS, waktu dan versi adalah bagian dari correctness.

Contoh:

• harga yang berlaku saat quote dibuat,
• catalog version yang berlaku saat quote accepted,
• billing account yang berlaku saat invoice dibuat,
• product characteristic yang berlaku pada service period tertentu,
• agreement term yang berlaku saat order submitted,
• approval evidence yang berlaku saat decision dibuat,
• subscription charge yang berlaku pada billing cycle tertentu.

Mental model:

Temporal modelling protects historical truth. Current state is not enough for enterprise systems.

2. Why Temporal Modelling Matters

Tanpa temporal model yang benar:

• old invoice berubah ketika billing address current berubah,
• order menggunakan catalog rule baru, bukan rule yang accepted,
• quote approval tidak bisa dibuktikan karena price sudah berubah,
• recurring charge lama overwrite oleh charge baru,
• product characteristic history hilang setelah modify,
• customer hierarchy reporting berubah tanpa historical explanation,
• audit hanya menunjukkan updated_at terakhir,
• billing period salah karena effective date tidak jelas,
• support tidak bisa menjawab "pada tanggal itu layanan saya seperti apa?"

Temporal correctness sangat penting untuk dispute, audit, billing, revenue reporting, dan incident review.

3. Current State vs Historical State

Ada dua kebutuhan berbeda.

Contoh:

Current product bandwidth = 500 Mbps
Historical product bandwidth on 2026-01-01 = 100 Mbps

Jika hanya menyimpan current state, historical billing dispute tidak bisa dijawab.

4. Valid Time vs Transaction Time

Dua dimensi waktu penting:

Example:

Product was effective from Jan 1.
System recorded correction on Jan 10.

Valid time:

effective_from = Jan 1

Transaction time:

created_at = Jan 10

Keduanya penting.

Use cases:

• backdated activation,
• late usage event,
• corrected billing address,
• retroactive price adjustment,
• product termination effective last month,
• delayed OSS update.

5. Effective Dating

Effective dating uses:

effective_from
effective_to

Common convention:

effective_from inclusive
effective_to exclusive

So a row is active at time t if:

effective_from <= t
and (effective_to is null or t < effective_to)

This avoids overlap ambiguity.

But always verify internal convention.

6. Effective-Dated Row Pattern

Example for product characteristic:

product_instance_characteristic
- product_instance_id
- characteristic_name
- characteristic_value
- effective_from
- effective_to

History:

bandwidth = 100 Mbps, effective 2026-01-01 to 2026-04-01
bandwidth = 500 Mbps, effective 2026-04-01 to null

Query current:

select *
from product_instance_characteristic
where product_instance_id = :id
  and characteristic_name = 'bandwidth'
  and effective_to is null;

Query as-of:

select *
from product_instance_characteristic
where product_instance_id = :id
  and characteristic_name = 'bandwidth'
  and effective_from <= :as_of
  and (effective_to is null or :as_of < effective_to);

7. Non-Overlapping Period Invariant

Effective-dated records for the same key should not overlap.

Example invariant:

For same product_instance_id + characteristic_name,
effective periods must not overlap.

PostgreSQL can enforce with exclusion constraints if range types are used, but many systems enforce in application service.

Data quality check:

select c1.product_instance_id, c1.characteristic_name, c1.id, c2.id
from product_instance_characteristic c1
join product_instance_characteristic c2
  on c1.product_instance_id = c2.product_instance_id
 and c1.characteristic_name = c2.characteristic_name
 and c1.id <> c2.id
where c1.effective_from < coalesce(c2.effective_to, 'infinity')
  and c2.effective_from < coalesce(c1.effective_to, 'infinity');

8. Gap Policy

Some effective-dated data must be continuous. Some may allow gaps.

Examples requiring continuity:

• active recurring charge period,
• contract term coverage,
• billing account assignment for active product,
• subscription item quantity over active term.

Examples that may allow gaps:

• temporary promotion,
• optional discount,
• resource reservation,
• site access instruction.

Define per entity:

overlap_allowed = false
gap_allowed = true/false

A gap can cause:

• missing billing,
• missing entitlement,
• invalid reporting,
• failed eligibility check.

9. Version Number vs Effective Date

Version number and effective date solve different problems.

Example:

Quote version 4 accepted on July 1
Price effective from July 1 to August 1
Record updated at July 2 due to metadata

Do not use version as substitute for effective period.

10. Revision Chain

Revision chain is common for quote/order/catalog.

Fields:

revision_number
previous_revision_id
superseded_by_id
is_current
revision_reason
created_from_id

Example:

Quote Q-100 v1 -> v2 -> v3 -> v4 accepted

Important:

• old revision must remain accessible,
• accepted revision must be immutable,
• approval belongs to revision,
• order conversion uses accepted revision,
• reporting must know latest vs all revisions.

11. Supersession Model

Supersession means one record replaces another.

Examples:

• quote revision supersedes previous quote revision,
• product offering version supersedes old offering,
• billing account merged into another,
• product instance upgraded/superseded,
• price rule replaced,
• agreement version amended.

Model:

entity_supersession
- old_entity_type
- old_entity_id
- new_entity_type
- new_entity_id
- supersession_type
- reason_code
- effective_at

Supersession is not deletion. It preserves historical trace.

12. Snapshot Model

Snapshot captures full or partial data at a point in time.

Use cases:

• quote accepted snapshot,
• price snapshot,
• serviceability snapshot,
• billing address snapshot on invoice,
• approval evidence snapshot,
• order conversion snapshot,
• catalog publication snapshot.

Snapshot fields:

snapshot
- id
- snapshot_type
- source_entity_type
- source_entity_id
- source_entity_version
- snapshot_payload
- snapshot_hash
- captured_at
- captured_by

Snapshots are useful when source data may change but historical evidence must remain stable.

13. Snapshot vs Reference

For accepted quote, invoice, approval, and billing evidence, reference + snapshot is often safest.

Example:

invoice.billing_address_id = current address reference
invoice.billing_address_snapshot = address used at issue time

14. Immutable Boundary

Certain states create immutable boundaries.

Examples:

• quote accepted,
• invoice issued,
• approval decision recorded,
• order completed,
• billing adjustment approved,
• catalog version published.

After immutable boundary:

• data should not be edited directly,
• correction should create new record/adjustment,
• history should be preserved,
• audit reason required.

Rule:

Do not update historical truth. Add new truth with effective date, revision, correction, or adjustment.

15. Temporal Catalog

Catalog needs versioning:

• product offering version,
• product specification version,
• characteristic version,
• price version,
• rule version,
• bundle relationship version,
• eligibility rule version.

Quote/order must reference the version used.

Failure mode:

Order decomposition uses current product spec instead of accepted quote spec version.

Prevention:

quote_item.product_offering_version
order_item.product_offering_version
decomposition.rule_version

16. Temporal Pricing

Pricing is highly temporal.

Price can vary by:

• effective date,
• currency,
• price list,
• customer segment,
• agreement,
• promotion,
• site,
• channel,
• product version.

Price snapshot should preserve:

• base price,
• discount,
• override,
• tax assumption if relevant,
• rule version,
• price list version,
• validity,
• calculated amount.

Do not recompute old quote from current price rules and expect same answer.

17. Temporal Billing

Billing needs effective periods:

• charge effective period,
• billing period,
• invoice period,
• subscription term,
• proration period,
• tax period,
• payment due date,
• credit adjustment period.

Examples:

recurring_charge effective_from = 2026-07-10
billing_period_start = 2026-08-01
invoice_date = 2026-09-01
payment_due = 2026-09-30

Each date has different meaning.

18. Temporal Product Inventory

Product inventory changes over time:

• product status,
• characteristic values,
• site/location,
• billing account,
• parent-child relationship,
• service realization,
• subscription link,
• product plan.

Effective-dated history allows:

What did customer have on date X?
What bandwidth was billed in period Y?
Which billing account paid for product during July?

This is essential for billing dispute and support.

19. Backdating

Backdating is dangerous but sometimes required.

Examples:

• activation happened yesterday but event arrived today,
• billing correction effective from last month,
• contract term starts earlier,
• product termination effective last billing cycle.

Backdating must require:

• reason,
• authority,
• audit,
• validation against closed periods,
• billing/revenue impact check,
• downstream adjustment.

Do not allow arbitrary backdated updates without guard.

20. Future-Dated Changes

Future-dated changes are common.

Examples:

• subscription cancellation effective end of month,
• price change effective next quarter,
• billing account change next invoice period,
• product upgrade scheduled for future date,
• catalog version future publication.

Future-dated records need:

• scheduled state,
• effective date,
• cancellation of scheduled change,
• conflict detection,
• job/event to activate,
• visibility in UI/reporting.

21. Timezone

Timezones affect:

• quote validity,
• billing period,
• subscription renewal,
• usage period,
• order appointment,
• service activation,
• SLA.

Storage recommendation:

• store instants as timestamptz in UTC,
• store business date separately where semantics are date-only,
• store timezone used for calculation,
• define period boundary convention.

Example:

billing_cycle_timezone = Asia/Jakarta
invoice_period_start = local date
event timestamps = UTC

22. PostgreSQL Physical Design

Effective-dated example:

create table product_instance_characteristic (
  id uuid primary key,
  product_instance_id uuid not null,
  characteristic_name text not null,
  characteristic_value text,
  value_type text,
  effective_from timestamptz not null,
  effective_to timestamptz,
  source_order_item_id uuid,
  created_at timestamptz not null,
  created_by text
);

Current row query index:

create index idx_pic_current
on product_instance_characteristic (product_instance_id, characteristic_name)
where effective_to is null;

As-of query index:

create index idx_pic_asof
on product_instance_characteristic (product_instance_id, characteristic_name, effective_from, effective_to);

Snapshot table:

create table business_snapshot (
  id uuid primary key,
  snapshot_type text not null,
  source_entity_type text not null,
  source_entity_id uuid not null,
  source_entity_version integer,
  snapshot_payload jsonb,
  snapshot_hash text,
  captured_by text,
  captured_at timestamptz not null
);

Supersession table:

create table entity_supersession (
  id uuid primary key,
  old_entity_type text not null,
  old_entity_id uuid not null,
  new_entity_type text not null,
  new_entity_id uuid not null,
  supersession_type text not null,
  reason_code text,
  effective_at timestamptz not null,
  created_at timestamptz not null
);

23. Java/JAX-RS Backend Implications

Temporal operations should be explicit commands:

POST /subscriptions/{id}/schedule-cancellation
POST /product-instances/{id}/schedule-change
POST /billing-accounts/{id}/effective-profile-changes
GET /product-instances/{id}?asOf=2026-07-01T00:00:00Z
GET /quotes/{id}/revisions/{revision}

Service responsibilities:

• validate effective period,
• prevent overlap,
• handle backdating authority,
• write audit,
• create supersession/revision where needed,
• protect immutable snapshots,
• publish effective-date events,
• reconcile downstream systems.

Avoid generic update that overwrites history.

24. MyBatis/JPA/JDBC Implications

MyBatis

Useful for explicit as-of queries and effective-dated joins.

JPA

Be careful with:

• current-state entity overwriting history,
• effective-dated collections,
• lazy loading old versions,
• accidental update of immutable snapshots.

JDBC

Useful for batch temporal correction and reconciliation.

General rule:

Temporal history is a domain model, not merely an audit table.

25. Query Patterns

Common queries:

-- Current active record
select *
from recurring_charge
where subscription_id = :subscription_id
  and status = 'ACTIVE'
  and effective_to is null;

-- As-of billing account assignment
select *
from product_billing_account_history
where product_instance_id = :product_instance_id
  and effective_from <= :as_of
  and (effective_to is null or :as_of < effective_to);

-- Future scheduled changes
select *
from product_instance_change_schedule
where effective_at > now()
  and status = 'SCHEDULED';

Design indexes around actual query patterns.

26. Event Model

Temporal events:

• EntityRevisionCreated
• EntitySuperseded
• EffectiveDatedChangeScheduled
• EffectiveDatedChangeActivated
• EffectiveDatedChangeCancelled
• SnapshotCaptured
• BackdatedCorrectionApplied
• FutureDatedChangeApplied
• TemporalOverlapDetected

Payload example:

{
  "eventId": "uuid",
  "eventType": "EffectiveDatedChangeScheduled",
  "eventVersion": 1,
  "occurredAt": "2026-07-12T10:00:00Z",
  "entityType": "PRODUCT_INSTANCE",
  "entityId": "product-instance-id",
  "changeType": "BILLING_ACCOUNT_CHANGE",
  "effectiveFrom": "2026-08-01T00:00:00Z",
  "correlationId": "corr-123"
}

Events must clarify whether event occurrence time equals business effective time.

27. Reporting Impact

Temporal model affects:

• historical revenue,
• point-in-time installed base,
• customer hierarchy at time of invoice,
• active subscription count at month-end,
• churn date,
• product migration timeline,
• price changes over time,
• approval validity,
• discount trend by effective date,
• billing account changes.

Reporting must define:

• as-of date,
• historical vs current hierarchy,
• event date vs effective date,
• transaction date vs business date,
• revision inclusion/exclusion.

28. Data Quality Checks

-- Current row duplicate
select product_instance_id, characteristic_name, count(*)
from product_instance_characteristic
where effective_to is null
group by product_instance_id, characteristic_name
having count(*) > 1;

-- Invalid effective interval
select id, effective_from, effective_to
from product_instance_characteristic
where effective_to is not null
  and effective_to <= effective_from;

-- Snapshot missing hash for immutable evidence
select id, snapshot_type, source_entity_type, source_entity_id
from business_snapshot
where snapshot_type in ('QUOTE_ACCEPTED', 'APPROVAL_EVIDENCE', 'INVOICE_ADDRESS')
  and snapshot_hash is null;

29. Failure Modes

30. PR Review Checklist

When reviewing temporal changes, ask:

• Is this current-state or historical-state data?
• Does this data need effective dates?
• Is valid time different from transaction time?
• Are periods inclusive/exclusive defined?
• Can periods overlap?
• Are gaps allowed?
• Is a snapshot needed?
• Is version/revision needed?
• Is supersession needed?
• Does this change affect quote/order/billing/product history?
• Is backdating allowed?
• Is future dating allowed?
• Is timezone defined?
• Are as-of queries supported?
• Are immutable states protected?
• Are reporting definitions impacted?
• Are data quality checks available?

31. Internal Verification Checklist

Verify these in the internal CSG/team context:

• Which entities are versioned vs overwritten.
• Whether quote revision/version is immutable after acceptance.
• Whether product offering/price/catalog versions are preserved in quote/order.
• Whether billing profile/address/invoice snapshots exist.
• Whether product characteristics are effective-dated.
• Whether recurring charges are effective-dated.
• Whether customer/account hierarchy is effective-dated.
• Whether backdated changes are allowed and controlled.
• Whether future-dated changes are supported.
• Whether timezone and period convention are documented.
• Whether temporal overlaps are prevented.
• Whether as-of queries exist for support/reporting.
• Whether immutable snapshots are hashed or protected.
• Whether incidents mention stale price, wrong historical invoice, duplicate active period, or lost product history.

32. Summary

Temporal versioning and effective dating protect historical truth.

A strong model must define:

• current vs historical state,
• valid time,
• transaction time,
• effective_from/effective_to,
• non-overlap and gap rules,
• revision chain,
• supersession,
• immutable snapshots,
• backdated changes,
• future-dated changes,
• timezone semantics,
• as-of queries,
• audit and reporting impact.

The key principle:

In enterprise systems, correctness is not only what is true now. It is what was true at the time a quote was accepted, an order was fulfilled, an invoice was issued, or a decision was made.