Design a forecast store schema on Postgres/TimescaleDB — the tables, keys, defaults, and queries for storing forecasts with leakage-free backtesting, reproducible evaluation, and vintage history. Use when asked to store forecasts/predictions, backtest without leakage, track forecast accuracy, keep forecast versions/vintages, or evaluate models or vendors in SQL. Plain SQL; no library required.
Design a forecast store schema on Postgres/TimescaleDB — the tables, keys, defaults, and queries for storing forecasts with leakage-free backtesting, reproducible evaluation, and vintage history. Use when asked to store forecasts/predictions, backtest without leakage, track forecast accuracy, keep forecast versions/vintages, or evaluate models or vendors in SQL. Plain SQL; no library required.
Designing a forecast store schema
A forecast store holds forecasts, the input data they were made from, and the
observations they are scored against, in plain Postgres/TimescaleDB tables. It makes
three things teams otherwise hand-roll easy and correct:
Vintages. A forecast made Monday for Wednesday and a forecast made Tuesday for the
same Wednesday are different records. Both are kept; either can be retrieved.
Leakage-free backtesting. A backtest must see only data that was knowable at the
simulated forecast time. Leakage — accidentally reading later data — inflates
backtest accuracy and evaporates in production.
Accuracy as data. Scoring forecasts against realized values is a SQL join, so
accuracy is a queryable time series, not a notebook artifact.
The normative contract is docs/forecast-store-convention.md; per-rule reasoning is
docs/forecast-store-rationale.md. A Python generator/SDK exists
(pip install forecast-store) but nothing below requires it.
Two rules generate everything
Rule 1 — every row carries three clocks. Forecast data is tri-temporal:
bi-temporal modeling (valid time + transaction time, per SQL:2011) is not enough,
because when the source knew it is a third, independent time. Three timestamp
columns, on every table, answering three different questions:
Column
Question it answers
Who sets it
target_time
What moment is the value about?
the writer
available_at
When did the value become knowable?
the writer (a domain claim)
recorded_at
When did this database write the row?
DEFAULT now() — never the client
The three genuinely differ. A Monday weather forecast about Wednesday is knowable
Monday (available_at before target_time). A meter reading about Monday arriving
through a 48-hour feed is knowable Wednesday (available_at after target_time). If
that reading is backfilled next month, recorded_at is next month. Filtering on
available_at <= t simulates what was knowable at time t; recorded_at is the
unforgeable witness that keeps the simulation honest, because available_at is writable
by design. Naming rule: a column may sound like system time only if the database
stamps it.
Rule 2 — tables are append-only: a log of beliefs, not a table of values. Every row
is a belief: "value v about moment T, held as of moment A". A correction or a newer
forecast is a new row, never an UPDATE. A vintage is the set of beliefs that were
current at some knowledge time; the as-of query (below) selects one by cutting on
available_at. Append-only is what makes vintages, as-of reads, and reproducible
evaluation fall out of the schema with no versioning machinery.
The tables
Beliefs live in three kinds of points table, split by what a row is, not what it
measures. The names below are just the defaults — what identifies a table is the
role declared in its store_tables row, and a store may hold several instances of
a role under any names (e.g. a second forecast table with a different quantile band):
forecast.actuals (role actuals) — measurements: rows written after the
value became fixed.
forecast.predictors (role predictors) — predictions from an opaque producer
(e.g. a vendor's weather feed): all anyone knows is (target, publication time, value).
forecast.forecasts (role forecasts) — run-bearing predictions, yours or any with provenance, each tied to a
forecast.runs row recording the model, parameters, and input window that produced it.
Two metadata tables make the store self-describing: forecast.series (the registry:
one row per measured or forecasted quantity, carrying its declared resolution) and
forecast.store_tables (one row per points table declaring its role, columns, and
quantile band — any client can reconstruct the store's shape from the store itself).
The design procedure
Steps 1–4 are design decisions — no SQL runs. Step 5 creates every table. Registering
series and writing data (steps 6–7) is only possible after step 5, because the registry
and points tables have to exist first.
1. Inventory the quantities and pick each one's grid
Plan one registry entry per quantity (site 42's load, site 42's irradiance, the FR
day-ahead price). For each, decide:
Name — an immutable machine identifier, conventionally a slash path
(site42/load, site42/wx/wind_speed_80m); human naming goes in description.
Grid — the sample_interval. Every target_time for the series must be a bucket
boundary at this resolution: a timestamp here names an interval, not a point ("the
load at 18:00" means "average power over [18:00, 18:15)"). Exact alignment makes "the
same target" an equality — a correction supersedes its predecessor instead of becoming
a near-duplicate sibling, and evaluation is a plain equi-join. If a source emits
off-grid timestamps, ingest snaps them to the grid.
Know the interval from the source's contract; never infer it from observed data spacing.
One quantity = one series, even when several tables hold beliefs about it — measured
load in actuals, a vendor's load prediction in predictors, your model's in
forecasts, all under one series_id.
2. Classify every stream by the realization test
Every quantity has a realization moment — the instant its true value becomes fixed
(the meter interval ends; the auction clears). Classify each stream by which side of
that moment its rows are written on:
Written after realization → a measurement: it can be mis-measured but never
wrong; a newer row corrects it → actuals.
Written before realization → a prediction: it can be wrong but is never
corrected; a newer row re-predicts from newer information, and every superseded
vintage stays individually scoreable → predictors (producer opaque) or forecasts
(producer known: a run with model, params, context).
Timestamps do not decide this. The day-ahead auction price publishes before delivery yet
realizes when the auction clears — a measurement whose available_at precedes its
target_time. Your desk's forecast of that price is a prediction. One quantity may
hold both kinds of belief, which is why the kind lives in the table address, never on
the series or as a row label.
This classification is load-bearing, not taxonomy — everything downstream derives from
it: what a newer row for the same target means sets the revision rules (step 3), the
measurement/prediction split sets each table's available_at contract (step 7), and
evaluation always scores predictions against measurements (the join below).
3. For actuals: does the source revise?
Some sources restate history (settlement-grade meter data is corrected for weeks); for
others a second value for the same target is a bug. Same columns either way; the
primary key is the switch:
Revisioned — PRIMARY KEY (series_id, target_time, available_at) — for sources
that restate. A correction is a new row under a new available_at. Idempotent ingest
is ON CONFLICT DO NOTHING: the key names a belief's full coordinates, so a colliding
row with a different value would silently rewrite history — it is refused, first
write wins.
Single-belief — PRIMARY KEY (series_id, target_time) — only for pipelines you
own, where a second belief is a defect to surface. Its write is
INSERT .. ON CONFLICT DO UPDATE SET value = EXCLUDED.value under the belief_guard
trigger (ships in the DDL): identical re-delivery is a silent no-op, a conflicting
value raises with both values in the message — never silently swallowed.
Trap: "never revises" is a claim about the world, and the world restates (exchanges
republish "final" prices). A publish-once external feed belongs in the revisioned
shape, where a restatement lands as data and can alarm; reserve single-belief for
pipelines you own.
4. Choose the quantile band per forecast table
A probabilistic forecast is stored as wide typed columns, one per quantile level, named
by a fixed bijection: q05 ↔ 0.05, q50 ↔ 0.5, q02_5 ↔ 0.025. Decide the band (the
set of levels) per forecast table; it is declared in that table's store_tables row and
the columns derive from it. Two rules:
Never store a mean in q50 — a mean is not a median. Your own forecast table gets
an honest mean column; a vendor's point column is value, because their statistic
(mean? median? deterministic run?) may be unknown — if it is known, it's registry
metadata, not a column name.
A model emitting a different band gets its own forecast table whose declared band
is true — never NULL-padded columns in another table's band.
5. Create the tables: catalog once, then one block per stream
Now execute DDL, in this order:
references/catalog-ddl.sql — paste once per store,
first. The decision-invariant layer: the series registry and its resolver
functions, the store_tables catalog, run provenance (runs), evaluation tables,
guard functions, and data_quality_sweep(scan_window) (finds unregistered ids,
off-grid target_time, out-of-bucket observations; schedule it with cron or
TimescaleDB add_job). The sweep is catalog-driven — it discovers points tables from
their store_tables rows at execution time — so nothing you decided in steps 2–4
changes this file.
references/points-tables.sql — one self-contained
block per stream you classified: the table, its index/view/triggers, its own
store_tables declaration row, and its TimescaleDB layer (hypertable + columnstore:
segmentby series_id, orderby the two clocks — the resulting minmax sparse indexes
match exactly the as-of predicates). The default three blocks (forecasts, predictors,
revisioned actuals) are the common case; the variant blocks are the step-3
single-belief actuals and a step-4 second forecast table with its own band — derive
further instances the same way, columns from the band by the bijection.
Because every block carries its own declaration row, adding a table to a live store =
executing its block: the sweep and any catalog-reading client pick it up immediately.
Integrity is monitor-first: no foreign keys on points tables (per-row FK checks tax
bulk ingest); the strict resolver at write time plus the scheduled sweep cover it.
6. Register the series
The registry now exists. Register every series from step 1 — always through the
resolver functions, never a raw INSERT into forecast.series:
Use get_series_id() inside every write (VALUES (forecast.get_series_id('site42/load'), ...))
so a typo'd series name fails loudly at insert time instead of landing as an orphan id.
7. Write data: each table's available_at contract
Never write recorded_at, on any table — it is always the column default. For
available_at the defaults differ per table, and both directions are load-bearing:
actuals.available_at DEFAULT now() — a fact's knowledge time is when you learned
it, and arrival is measurable. The default can only overstate lateness, never
fabricate zero-lag knowledge, so the lazy write is the safe write. Backfills with
genuine historical availability state it per row.
predictors.available_at NOT NULL, no default — a prediction's knowledge time is
its publication time, which only the writer can state. Defaulting it to arrival would
score a vendor's 06:00 forecast delivered at 08:00 as two hours fresher than it was.
recorded_at − available_at then measures vendor delivery lag.
forecasts — write the run row and its points in one transaction. Production
stamps available_at = now(); a backtest stamps the simulated decision moment (that
writability is the point — and recorded_at makes the retro-stamping visible).
Idempotent ingest follows step 3's shape: revisioned → ON CONFLICT DO NOTHING;
single-belief → the belief_guard upsert. Never use ON CONFLICT DO NOTHING on a
single-belief table — it silently swallows conflicting values, unrecoverably.
Canonical queries
As-of (vintage selection) — "what did we believe about these targets, as of moment
X?": per target, the row with the latest available_at at or before the cutoff. This is
the workhorse for serving, covariate assembly, and backtest inputs. Never read a belief
log without a knowledge cutoff — an uncut read means "everything I know now", which
inside a backtest is leakage:
SELECTDISTINCTON (target_time) target_time, available_at, q50
FROM forecast.forecasts
WHERE series_id = forecast.get_series_id('site42/load')
AND target_time BETWEEN :t0 AND :t1
AND available_at <= :asof -- the knowledge cutoffORDERBY target_time, available_at DESC;
The same shape against predictors is leakage-free feature assembly; with
asof = now() it is "current best forecast" (or use the generated
forecast.latest_<table> view for the hot path).
Evaluation join — score the forecast that would have been used. The cutoff is
relative to the operational decision deadline (here "gate closure": day-ahead markets
close ~12h before delivery, so the forecast in force is the last one available by then).
The join's shape is step 2's split: predictions on the left, measurements (actuals)
on the right as truth — swap a predictors table in for forecasts and the same query
scores a vendor. Pick the vintage first, then join to truth:
WITH ef AS (
SELECTDISTINCTON (f.series_id, f.target_time)
f.series_id, f.target_time, f.q50
FROM forecast.forecasts f
WHERE f.available_at <= f.target_time -interval'12 hours'-- gate closureAND f.recorded_at <= :frozen_at -- the pinORDERBY f.series_id, f.target_time, f.available_at DESC
), ea AS (
SELECTDISTINCTON (series_id, target_time) series_id, target_time, valueFROM forecast.actuals
WHERE available_at <= now() AND recorded_at <= :frozen_at
ORDERBY series_id, target_time, available_at DESC
)
SELECT ef.series_id, avg(abs(ef.q50 - ea.value)) AS mae
FROM ef JOIN ea USING (series_id, target_time)
GROUPBY1;
The two cutoffs do different jobs. available_at cutoffs simulate what was knowable
at each decision moment. The pin — recorded_at <= :frozen_at — freezes the dataset
as the store physically held it at one instant, so re-running with the same pin returns
identical rows forever, even after backfills and revisions land. Because available_at
is a writable claim, the pin must guard every belief-log read in the query,
forecasts included. Pin placement is the semantics: pin = now() → live monitoring;
pinned once at experiment start → reproducible forever; recorded_at <= S per simulated
origin S → the model as operated, ingestion delays included; pinned at one
decision's timestamp → forensics ("what did we know at bid time").
Invariants (check every design against these)
recorded_at is never written by a client, on any table.
Every belief-log read states its knowledge cutoff; frozen reads pin recorded_at too.
Append-only. The one sanctioned DELETE: re-running a benchmark may delete-then-insert
rows scoped to its own run label — production history is never deleted or updated.
Never ON CONFLICT DO NOTHING on a single-belief table.
target_time is always on the series' declared grid; the sweep audits.
mean is never spelled q50; a band column exists only if the producer emits that
level.
One quantity = one series, across all tables; wide columns are reserved for the
quantile distribution, never for multiple quantities.
