Observations "Pending" counts facts the consolidator has permanently given up on

[alvorithm]

Bug Description

pending_consolidation in GET /v1/default/banks/{bank_id}/stats counts every fact with consolidated_at IS NULL. That set includes facts stamped consolidation_failed_at, which the consolidator's own candidate query excludes on purpose. The gauge therefore has a floor that no amount of work can clear, and an operator watching it cannot tell a real backlog from an abandoned residue.

engine/memories/pg/counts.py::consolidation_freshness computes the two figures side by side, and the pending filter carries no exclusion:

COUNT(*) FILTER (WHERE consolidated_at IS NULL AND fact_type IN ('experience', 'world')) AS pending,
COUNT(*) FILTER (WHERE consolidation_failed_at IS NOT NULL AND fact_type IN ('experience', 'world')) AS failed

while the consolidator selects on consolidated_at IS NULL AND consolidation_failed_at IS NULL (engine/memories/pg/reads.py::find_unconsolidated, whose docstring states the intent: a memory the LLM could not handle must not be retried for ever).

Two consequences in the control plane:

• components/data-view.tsx shows the observations badge as "In sync" only when pending_consolidation === 0, and otherwise as an amber clock with "Pending N". A bank holding one permanently failed fact can never show "In sync".
• components/bank-stats-view.tsx passes pending={stats.pending_consolidation} and failed={stats.failed_consolidation} into the same ConsolidationCard, so the failed rows are counted twice, once inside pending and once as failed.

Steps to Reproduce

1. Run a bank with observations enabled and retain enough facts to give the consolidator work.
2. Make consolidation fail permanently for some of them. A rotated or invalid LLM key does it, and so does an exhausted subscription quota (see Transient quota exhaustion is dead-lettered as a permanent consolidation failure; no per-memory transaction boundary means the safe alternative duplicates observations #2973). Those facts end up with consolidation_failed_at set.
3. Restore the key and let the consolidator drain the genuine backlog.
4. Compare the two numbers:

   GET /v1/default/banks/{bank}/stats                          -> pending_consolidation, failed_consolidation
   GET /v1/default/banks/{bank}/memories/list?consolidation_state=pending&limit=1  -> total
   

   pending_consolidation stays above the list total by exactly failed_consolidation, indefinitely.
5. POST /v1/default/banks/{bank}/consolidation/recover clears the residue, after which the two figures agree.

Expected Behavior

pending reads as "work the consolidator will still do", so it should either exclude consolidation_failed_at IS NOT NULL, or be labelled as a superset and always be shown next to the failed count with the recover action. A bank with no live backlog should be able to reach "In sync" without an operator having to know that recover exists.

Actual Behavior

Bank general, measured 2026-08-10 before recovery:

GET /banks/general/stats                        -> pending_consolidation 6975, failed_consolidation 119
GET /memories/list?consolidation_state=pending  -> total 6856
6975 - 6856 = 119 = failed_consolidation

The figure had sat around 6,800 for two days. The failed residue dated from 2026-07-31 (87 facts, a rotated API key), 2026-08-07 (14 facts), and 2026-08-10 (18 facts). POST /banks/general/consolidation/recover cleared all 119, after which the two figures agreed.

Same bank later the same day, after recovery, as a control:

stats: pending_consolidation 17777, failed_consolidation 0
list ?consolidation_state=pending: total 17777

With no failed rows the gap disappears, which is consistent with the gap being exactly the failed set.

Related and distinct: #2973 explains why a permanent residue accumulates in the first place. This issue is only about counting that residue as pending.

Suggested change

Either exclude consolidation_failed_at IS NOT NULL from pending in consolidation_freshness, or keep the superset and render both numbers wherever the UI renders one, with the recover action beside them. The API already exposes failed_consolidation, so this is mostly a labelling and rendering decision, plus one line of SQL if you prefer the strict count.

Version

ghcr.io/vectorize-io/hindsight:latest, digest sha256:6364c3c5f1e551447976d6c3ab369040d0237c0980f10f911d76d981290913b6, built 2026-08-07, GET /version reports api_version 0.9.0. Source read at main 00b520e. Single container with API plus worker, embedded Postgres, Python 3.11.15, rootless Docker on Debian 13, kernel 7.1.3, glibc 2.41. Bank general: 55,716 memory units, 12,200 observations, 1,187 documents, 9 knowledge pages.

LLM Provider

Other: DeepSeek deepseek-v4-flash for every scope. Embeddings local, BAAI/bge-m3, 1024 dimensions.