Reference data published after the period: provisional values, then frozen observations

·6 mins

A tax engine converts foreign-currency trades into the reporting currency. The law allows two methods per tax year: the central bank’s daily rate for each transaction date, or a single annual rate per currency that the tax authority publishes. Conservative mode always uses the annual rate.

The annual rate for a year is published in the first months of the next year. Users also want in-year numbers, so for most of a calendar year, conservative mode needs a value that does not exist yet.

The same thing happens with any reference value fixed after the period it covers: annual average rates, inflation indices used for indexation, final settlement prices, revised statistics. This post covers the design that handled it, and the bug in the first version of its “immutable cache”.

Make the status part of the value

While the annual rate is unpublished, conservative mode computes on the daily rate as a proxy. Without a label, a reviewer who sees conservative mode using daily rates in September reports it as a bug. So every conversion carries its status:

type Conversion =
  | { ok: true; amount: Decimal; status: "provisional"; rateRefs: RateKey[] }  // daily proxy
  | { ok: true; amount: Decimal; status: "final"; rateRefs: [RateKey] }        // annual rate published
  | { ok: true; amount: Decimal; status: "frozen"; frozenFrom: "provisional" | "final";
      rateObservations: RateObservation[] }                                    // pinned at handoff
  | { ok: false; reason: "missing-rate"; needed: RateKey[] };

The dashboard shows a provisional badge next to each figure computed that way.

Publication is not a stored flag. The annual rate counts as published when its rate record exists:

const annual = await readRate(tx, annualRateKey(currency, taxYear));
if (annual) return { ok: true, amount: multiply(amount, annual.rate), status: "final", rateRefs: [annualKey] };
await requestAnnualRate(tx, annualKey);   // enqueue a fetch job, deduplicated per key
return daily;                             // the provisional proxy

An open assessment always reads through this function. Once the fetch job stores the annual rate, the next read returns final. No “rate published” boolean can disagree with the data.

While the case is open, everything recomputes live. When it is handed to an advisor, the conversions freeze. That gives two rules: open assessments track the newest data, and handed-off assessments stay reproducible forever.

“Immutable, keyed by the rate’s identity” cannot store a correction

The first schema followed the design doc literally: a rate is a historical fact, so store it once and never refetch.

create table rate (
  source text, base text, quote text, rate_date date,
  rate numeric not null,
  fetched_at timestamptz not null default now(),
  primary key (source, base, quote, rate_date)
);
grant select, insert on rate to app;   -- no UPDATE, no DELETE
-- writes: insert ... on conflict do nothing

The same design doc also said: “a correction is a new record, not an overwrite.” The primary key makes that impossible. A provider that corrects a published value sends a second row with the same key, and on conflict do nothing drops it without an error. The cache stays immutable by losing the corrected value.

The second migration separated the two requirements. Observations are append-only, and a small pointer table says which observation is current:

create table rate_observation (
  id uuid primary key default gen_random_uuid(),
  source text, base text, quote text, rate_date date,
  rate numeric not null, fetched_at timestamptz not null
);

create table rate_canonical (
  source text, base text, quote text, rate_date date,
  observation_id uuid not null references rate_observation(id) on delete restrict,
  primary key (source, base, quote, rate_date)
);

create view rate as
  select o.* from rate_canonical c join rate_observation o on o.id = c.observation_id;

A security definer function performs every write. It takes a per-key advisory lock. A retry with an equal value returns the existing observation. A different value inserts a new observation and moves the pointer. Open assessments read the rate view and pick up the correction. A frozen assessment stores the observation IDs it used, one per conversion leg, and never reads the pointer again. A currency that the central bank doesn’t list converts through a pivot currency, so its conversion pins two observations.

Three Postgres gotchas from making it immutable

The first implementation of the freeze broke 10 tests. One fix commit covered three defects:

No NUL in text. The advisory lock key concatenated the key parts with chr(0) as a separator. Postgres text cannot contain a null byte, so every write raised an error. Use a separator that cannot appear in the parts and is legal in text, such as chr(31):

perform pg_advisory_xact_lock(hashtextextended(
  concat_ws(chr(31), p_source, p_base, p_quote, p_rate_date::text), 0));

FOR SHARE needs UPDATE privilege. At handoff, the freeze must not race a correction that moves the pointer. The first version locked the pointer row with select ... for share. In Postgres, every row-locking clause requires UPDATE privilege on the table, and the app role has none, on purpose. The fix takes the same advisory lock the writer takes, so the advisory lock becomes the single point of serialization. When one handoff freezes many conversions, it acquires the locks in a fixed order (sorted by conversion id), so two concurrent handoffs cannot deadlock.

node-postgres turns date into a shifted Date. By default, pg parses a date column (OID 1082) into a JavaScript Date at local midnight. Serialized back as UTC in a timezone east of Greenwich, 2026-03-02 becomes 2026-03-01T23:00:00.000Z, the previous day. The schema types already said string. Keep the literal:

import { types } from "pg";
types.setTypeParser(1082, (value) => value);   // 'YYYY-MM-DD' stays a string; timestamptz unaffected

Two smaller rules

  • Exact decimals end to end. The central bank quotes some currencies per 100 units. The parser divides the listed rate by the quoted quantity in exact decimal arithmetic, and rejects a result that would not terminate rather than rounding it. No rate passes through an IEEE float.
  • Crypto is an asset, not a currency. The coding agent’s first plan gave crypto its own rate subsystem. I corrected that during the design session: crypto is priced through its trading pair (BTC/EUR, ETH/USD), like a stock, and the pair’s quote currency converts through an ordinary rate. Only a crypto-to-crypto swap needs a separate market valuation, and that is asset valuation, not currency conversion.

Takeaway

When a reference value is published after the period it describes, put the status in the value (provisional, final, frozen) so that a proxy cannot pass for the real number. Let the existence of the record mean “published”. Treat “immutable” and “correctable” as two tables, not one primary key: append observations, point to the current one, and let frozen results pin observation IDs.

If your cache is immutable through ON CONFLICT DO NOTHING, send it a corrected value for an existing key and check whether anything records that the value changed.