`gate_results` is never written — `br gate report` only populates `gate_result_history`

[Gerry9000]

gate_results is never written — br gate report only populates gate_result_history

Version: br 0.5.7

Summary

br gate report writes to gate_result_history and never writes to
gate_results. The gate_results table has a full current-state schema — a
composite primary key on (issue_id, gate, provider), a supporting index, and a
foreign key with ON DELETE CASCADE — so it is clearly intended to hold the
current gate state per issue. In practice it stays empty forever.

br gate list is unaffected because it reads from history, so the operator-facing
view is correct. The problem is that anything querying gate_results directly
gets a definitive-looking zero.

Reproduction

mkdir -p /tmp/gate-repro && cd /tmp/gate-repro
br init

# a policy that actually requires gates is needed, or `br gate report` correctly
# refuses with "no configured transition ... requires gate". Copy any policy.yaml
# whose workflow.gates block requires independent_validation on in_review -> closed.
cp /path/to/a/policy-with-gates.yaml .beads/policy.yaml

ID=$(br create "gate report repro" -t task -p 3 | grep -oE '[a-z0-9]+-[a-z0-9]+' | head -1)
br update $ID --status in_progress
br update $ID --status in_review

sqlite3 .beads/*.db "select count(*) from gate_results"         # 0
sqlite3 .beads/*.db "select count(*) from gate_result_history"  # 0

br gate report $ID --gate independent_validation --provider some-reviewer --status pass
# -> ✓ Recorded gate 'independent_validation' = pass (provider some-reviewer) ...

sqlite3 .beads/*.db "select count(*) from gate_results"         # 0   <-- still empty
sqlite3 .beads/*.db "select count(*) from gate_result_history"  # 1

Control — this is not an artefact of a fresh workspace

Six unrelated repositories with real, long-running gate activity:

repo A   gate_results = 0    gate_result_history =  52
repo B   gate_results = 0    gate_result_history =   8
repo C   gate_results = 0    gate_result_history =  95
repo D   gate_results = 0    gate_result_history =  78
repo E   gate_results = 0    gate_result_history = 214
repo F   gate_results = 0    gate_result_history = 137

584 gate results recorded across six databases. gate_results holds zero rows
in all of them. The table appears never to have been written by any code path.

The schema says it was meant to be current-state

CREATE TABLE IF NOT EXISTS gate_results (
        issue_id TEXT NOT NULL,
        gate TEXT NOT NULL,
        provider TEXT NOT NULL,
        passed INTEGER NOT NULL DEFAULT 0,
        note TEXT,
        recorded_by TEXT,
        recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (issue_id, gate, provider),
        FOREIGN KEY (issue_id) REFERENCES issues(id) ON DELETE CASCADE
    );
CREATE INDEX IF NOT EXISTS idx_gate_results_issue ON gate_results(issue_id);

The composite primary key is exactly the shape you would choose for "latest
result per (issue, gate, provider)", which is the natural complement to an
append-only history table.

Why this matters

An always-empty table adjacent to a populated one is a trap for anyone querying
the database directly — dashboards, external CI reporting, audit scripts, or an
operator checking whether a gate was satisfied. gate_results returns zero rows
with no error, which reads as "no gate has ever been recorded" rather than "this
table is not used".

It is the same failure shape as a query that cannot see a positive case: the
result is plausible, definitive-looking, and wrong.

Suggested fix — either direction closes it

Populate it. On br gate report, upsert into gate_results on the existing
primary key alongside the history insert. This is what the schema implies and it
gives direct-query consumers a correct current-state view.

Or remove it. If history is intended to be the only source of truth, drop the
table and its index in a migration. A table that is never written should not
exist in the schema.

Either is fine. What should not persist is a fully-specified, indexed,
foreign-keyed table that no code path writes to.

Note on discovery

This surfaced while diagnosing a review backlog. Beads that had genuinely passed
review were sitting blocked, and part of the confusion was that the current-state
gate table was empty. The actual cause in that case was reviewers recording
verdicts in comments without running br gate report at all — but an empty
gate_results made the diagnosis harder, because querying it produced a
confident zero that agreed with the wrong hypothesis.