# A data architecture that remembers what it knew

> One synthetic corrected value traced through the real observation writer and schema: revisions as new rows, an as-known-at query, frozen run inputs, and freshness judged against schedule and consumer.

Published: 2026-09-22  
Updated: 2026-09-23  
Work period: July 2026  
Product: [Helmsman](https://adityadalmia.com/projects/helmsman/)  
Canonical HTML: https://adityadalmia.com/blog/helmsman-data-architecture/

The data layer in Helmsman has two jobs: make current evidence usable, and keep enough history to explain an earlier run.

Those jobs look alike until a source corrects a value. If we overwrite yesterday's input, we can show the latest number, but we lose the evidence behind yesterday's result.

## Two clocks, two questions

Take an illustrative source that reports a $100.00 reference price for January 2. Helmsman stores it at 10:05. At 10:20 the source issues a **revision**: it republishes the figure for the same period with a corrected value, $101.00. Revisions are routine. Some public statistics are first published from partial submissions and corrected days later, which is why one of our fetchers re-reads a trailing window on every pull.

A run at 10:10 could only have used $100.00. That matters when we **backtest**, which means replaying a decision rule over stored history to see how it would have behaved. If a replay of the 10:10 decision reads the corrected $101.00, it has **look-ahead bias**: it uses information that arrived after the decision. Valued at the corrected price, a 1,000-unit holding is worth $101,000. The real decision saw $100,000.

So every fact needs two timestamps. One records the period the fact describes. The other records when Helmsman received it. The first answers "what is the latest value for January 2?" The second answers "what did we know at 10:10?"

## The schema

Every numeric fact a fetcher produces lands in one table. The columns and key below are the real shape. I rewrote the comments.

```sql
CREATE TABLE observations (
  asset       TEXT NOT NULL,              -- instrument the fact belongs to
  source      TEXT NOT NULL,              -- feed that produced it
  metric      TEXT NOT NULL,
  ts          TEXT NOT NULL,              -- period described, on the source's clock
  rev         INTEGER NOT NULL DEFAULT 0, -- 0 = first value, 1 = first correction, ...
  value       REAL NOT NULL,
  meta        TEXT,                       -- provenance JSON, never read back as a value
  ingested_at TEXT NOT NULL,              -- when Helmsman stored this revision
  PRIMARY KEY (asset, source, metric, ts, rev)
);

-- What most readers query: the newest revision of each fact.
CREATE VIEW obs_current AS
  SELECT * FROM observations o
  WHERE o.rev = (SELECT MAX(rev) FROM observations x
                 WHERE x.asset = o.asset AND x.source = o.source
                   AND x.metric = o.metric AND x.ts = o.ts);
```

The revision number is part of the primary key, so a correction is a different row, not a changed one. The writer follows three rules for each incoming fact:

- A new period is inserted at revision 0.
- A changed value for a known period is inserted at the next revision.
- An identical value writes nothing.

The table sits in the same database as the run ledger, the record of every run, score and decision. A decision can therefore be joined to the stored values behind it. In the cluster, a Postgres trigger rejects every `UPDATE` and `DELETE` on the run, decision and observation tables.

## One value, corrected

To check the contract, I sent the example through Helmsman's real observation writer and schema, on a disposable local Postgres 16 database. The instrument, metric, prices and dates are **synthetic**. I pinned the writer's clock to synthetic ingestion times.

| Ingest clock | Pull returned (synthetic) | Writer result | Rows stored |
| --- | --- | --- | --- |
| 10:05 | Jan 2 = $100.00 | 1 inserted | Jan 2, rev 0, $100.00 |
| 10:12 | Jan 2 = $100.00 | Nothing written | Unchanged |
| 10:20 | Jan 2 = $101.00, Jan 5 = $102.00 | 1 inserted, 1 revised | Adds Jan 2, rev 1, $101.00 and Jan 5, rev 0, $102.00 |
| 10:40 | Same as 10:20 | Nothing written | Unchanged: three rows |

The current view returns $101.00 for January 2. To reconstruct an earlier moment, add one time bound to that view's logic:

```sql
SELECT o.ts, o.rev, o.value
FROM observations o
WHERE o.asset = :asset AND o.source = :source AND o.metric = :metric
  AND o.ingested_at <= :t
  AND o.rev = (SELECT MAX(x.rev) FROM observations x
               WHERE x.asset = o.asset AND x.source = o.source
                 AND x.metric = o.metric AND x.ts = o.ts
                 AND x.ingested_at <= :t);
```

| As known at | Answer |
| --- | --- |
| 10:00 | Nothing: no value yet |
| 10:10 | Jan 2 = $100.00 (rev 0) |
| 10:30 | Jan 2 = $101.00 (rev 1); Jan 5 = $102.00 (rev 0) |

I wrote this query for the article. It is not a shipped code path: the engine's readers of observation values use the current view. The schema supports the question, but nothing in production asks it yet.

The replay also checked three other guarantees:

- A direct `UPDATE` and a direct `DELETE` both failed with the database's append-only error, which says corrections must be new rows. All three rows were unchanged afterwards.
- The schema was applied twice before the first pull. The second application raised no error, and the trigger still fired afterwards.
- The backtest tooling fingerprints its dataset over every revision, including `rev` and `ingested_at`. The hash was unchanged after the 10:12 no-op and changed after the 10:20 correction. A backtest result stamped with that hash therefore records its data vintage: the exact set of revisions it read.

## What a run keeps for itself

The observation history is one record. Each run also keeps its own.

At decision time, a run copies each feed's latest snapshot into its own run directory. It writes a manifest and stores a status row for each feed in the ledger. The status is one of fresh, aging, stale or unavailable. It is recalculated at decision time from the source's own reported date, never from when we fetched it. Suppose a report is pulled at 05:00 and marked fresh. A decision at 20:00 can mark the same snapshot aging, with no new network call.

Our golden-day replays, saved real runs used as regression fixtures, feed a run's frozen inputs back through the decision code with the run's own clock. The result must match the stored decision exactly, apart from fields that record which code and configuration ran.

There are tradeoffs. The run's snapshot files are deliberately overwritten if the same day and run type is re-run. The per-attempt status rows are append-only and remain. In local development, the database is SQLite and has no trigger. Append-only is enforced only by a write interface with no update or delete method.

## Two incidents in the write path

Both happened on July 7, 2026.

**Round trips.** The first observation writer looked up existing state one fact at a time. Over the network between machines, that turned a backfill into a 40-minute job. The fix was to read current state in one query and send the inserts as one batch.

**Lock convoy.** The trigger installer originally dropped and recreated every trigger when a process started. That took an exclusive lock on each protected table. One long-running backfill transaction then queued every process starting behind it. The same day, the installer changed to create a trigger only if it is missing. A day later, startup DDL also began taking a Postgres advisory lock, so processes that start together apply it one at a time.

## Freshness depends on the consumer

On July 19 we changed how stale data is diagnosed. An urgent escalation had called a weekly feed stale because of its raw age. Triage showed that the feed was on schedule: its release day had been misread. A second flagged metric was a byproduct of a backfill, and no code read it.

The resulting decision separates three questions:

- How old is the data? This affects the calculations.
- Did we capture it on the source's declared cadence?
- Did the scheduled job run at all?

The writer contract explains why raw age misleads. A weekly source may be pulled daily. Its unchanged value writes nothing, so its ingestion time moves only when a new report lands. A one-day rule would raise a false alarm every weekday.

Capture checks now classify a late metric before anyone alarms. Only one class is a real alarm: the metric is declared, a live decision reads it, and the whole feed has gone silent. Every other late class is harmless by design:

- **Superseded:** a sibling in the same feed is fresh. One case is an expired **futures contract**. A futures contract is an agreement to trade a fixed quantity at a set price on a later date, and each delivery month trades separately. A January contract last priced at $100.00 stops updating at expiry, while February keeps moving.
- **Orphan:** captured, but declared by no feed and read by no code.
- **No consumer:** declared, but no active module reads it.
- **Parked:** the instrument runs no decisions.
- **Static:** a derived seasonal baseline, correctly old by construction.

The list of consumed feeds deliberately errs toward inclusion. A feed wrongly marked as consumed only makes a warning louder. A feed wrongly marked as unconsumed could hide a real break.

## Evidence and limits

- **Implementation:** the schema, observation writer, trigger and run snapshots date from July 7, 2026. The freshness classifier and the backtest fingerprint date from July 19. I reviewed the August 2026 revision.
- **Replay:** one synthetic metric, four pulls and three answers to the as-known-at query, run locally on September 22, 2026 (UTC). It demonstrates the storage contract, not volume or production behaviour.
- **Tests:** the existing storage and freshness tests, run locally the same day: 23 passed, 1 skipped because it needs a separate Postgres database. The freshness tests reproduce the metrics from the July triage.
- **Limits:** `ingested_at` records when Helmsman stored a revision, not when the source published it. A correction published at 10:15 and pulled at 10:20 is known from 10:20. The as-known-at query is a demonstration, not a production reader.
- **Not covered:** data sources, fetch schedules, and any logic that consumes the values.

Corrections kept as new rows, each run's own inputs, and staleness judged against schedule and reader: together they turn an earlier result into something we can inspect, not a guess about what the system might have seen.