Skip to content

Durable snapshot_stamp table so primitive vintage is a recorded fact, not an inference #247

Description

@NewGraphEnvironment

Problem

The primitive-vintage gate added in #246 / v0.47.0 infers freshness from
GREATEST(last_analyze, last_autoanalyze) in pg_stat_user_tables. That works
— it reproduces the known 2026-05-23/26 vintage on m1 exactly — but it is an
inference about statistics collection, not a record of when the data was
loaded
, and it has two known weaknesses stated rather than hidden in
R/lnk_preflight_vintage.R:

  1. It is an upper bound. Autoanalyze fires on ordinary write activity, so
    the timestamp can move forward without any reload. It can read too-new,
    never too-old — the unsafe direction for a staleness gate. It cannot
    plausibly turn three months into one week, which is why it is adequate
    today, but the failure direction is wrong.
  2. It cannot distinguish "loaded and quiet" from "never loaded". Both
    currently surface as a failure, which is the safe behaviour, but the
    operator gets no help telling them apart.

There is also a related gap the gate cannot see: data-raw/logs/bcfp_baselines.csv
is written by the loader rather than derived from the database, is scoped by
Sys.info()[["nodename"]], and its last non-CI row is 2026-05-14. It is what
lnk_baseline_current() uses to decide whether snapshot_bcfp.sh may skip — so
a host can sit on May primitives while believing itself current. That is
plausibly how m1 got three months behind.

Proposal

Have snapshot_bcfp.sh record what it actually did, in the database it did it
to:

CREATE TABLE IF NOT EXISTS fresh.snapshot_stamp (
  host          text        NOT NULL,
  table_name    text        NOT NULL,
  loaded_at     timestamptz NOT NULL,
  n_rows        bigint,
  bcfp_head_sha text,
  PRIMARY KEY (host, table_name)
);

One row per primitive, written at the end of each load.
lnk_preflight_vintage() then prefers the stamp and falls back to
pg_stat_file / pg_stat_user_tables with a printed note saying which source
it used.

The valuable property is that absence becomes FATAL. A host with no stamp
has demonstrably never recorded a snapshot, so the gate's first run says so and
names the remediation — which fixes staleness by construction rather than by
threshold tuning. Deferred from #246 only to keep new DDL out of a PR that was
already large.

Acceptance

  • fresh.snapshot_stamp created and populated by snapshot_bcfp.sh
  • lnk_preflight_vintage() prefers the stamp, falls back with a note
  • Absent stamp is FATAL, with the remediation command in the message
  • Both known answers exercised: a host with a stamp, and one without
  • RUNBOOK.md §8d updated to describe the stamp as the primary source

Relates to #246. Version pin: [email protected].

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions