ADR-0032: Retrieval-event log — Postgres now, BigQuery cutover trigger (v1b)

Status: Accepted for staging/alpha (2026-07-07). ai-engineer APPROVE-WITH-CHANGES

• ceo-blair APPROVE-WITH-CONDITIONS; all three conditions closed at the ADR level in this
  ADR (below) — condition (1)‘s metric/alert is committed + specified, but its GCP provisioning
  is a tracked ops/IaC follow-up, not yet live (see Observability).
  Prod go-live not yet authorized — pending human counsel (Allen) ADR-0014 sign-off and
  the Phase-2 access-control gate being built (see Out-of-scope / prod conditions).
  Date: 2026-07-07
  Deciders: Omar (design), Seth / ai-engineer (technical sign-off), Blair / ceo-blair
  (material-choice approval). Sink revision origin: Seth, #201, 2026-07-06.

Review log

• 2026-07-07 — ai-engineer (official): APPROVE-WITH-CHANGES. Reviewed the ADR, spec,
  ADR-0030, and the actual implementation. Architecture / cost posture / best-effort SAVEPOINT
  isolation sound; no unit-economics impact (infra-only). Three conditions to close before
  Accepted — all now closed:
  (1) wire an observable signal (log-based metric + named weekly owner + ~80% alert) for the
  50k-rows/day cutover leg, not just per-request log lines → closed (Observability para);
  (2) note the “nightly rollup p95 > 5s” leg is dormant until the Phase-2 rollup job exists →
  closed (cutover section); (3) move the admin-gating requirement for any future
  retrieval_events read surface into “Out of scope” explicitly → closed (Out-of-scope).
  Full write-up: docs/superpowers/reviews/2026-07-07-adr-0032-ai-engineer.md.
• 2026-07-07 — ceo-blair (official): APPROVE-WITH-CONDITIONS. Postgres-now sink + the
  pre-committed cutover trigger approved; accepts Seth’s cost math (no impact on Free ≤ $1.10 /
  Pro $6–12); marketing-honesty line holds (global Tier-1 only). Cleared for staging/alpha
  now. Requires Seth’s 3 conditions closed before Accepted (done). Before prod: Allen’s
  actual sign-off (not the stand-in) and the F-3 admin-gating built on any read surface — to
  be brought with the Phase-2 report spec, not sequentially.
  Full write-up: docs/superpowers/reviews/2026-07-07-adr-0032-ceo-blair.md.
• 2026-07-07 — Prior stand-in first-passes (run before the real persona agents were
  available) are retained at docs/superpowers/reviews/2026-07-07-adr-0032-*-standin.md for
  provenance; superseded by the official reviews above. The counsel leg has no agent
  (CLAUDE.md #199) — the counsel stand-in (CLEAR-FOR-STAGING-WITH-CONDITIONS) is informative
  only; binding prod privacy sign-off remains human counsel (Allen) / ceo-blair as exec.

Context

ADR-0030 (entry-level performance signal, v1a) shipped a nightly read-side
rollup of citation and feedback counts, scoped to tenant_id='global' AND collection<>'memory'. That ADR named a follow-up, “v1b: retrieval-event
log,” and predicted it would sink to BigQuery per ADR-0010/ADR-0021,
reserving the number ADR-0031 for it. ADR-0031 was instead used for the
question-type content facet — an unrelated decision that landed first. This
ADR is that deferred v1b, under its own number, and it revises the sink
decision from BigQuery to Postgres, per Seth’s comment on issue #201
(2026-07-06).

Tracing search_brain() while designing v1b surfaced a reframing that
changes what “citation” has meant in this codebase so far:

• search_brain() over-fetches (limit × 3, or × 6 with profile-boosting),
  dedups by parent doc, re-ranks, then trims to top-k (default 5). The
  losing candidates are discarded before the function returns
  (search_brain() in src/memberintel/api/retrieval/search.py).
• The returned top-k are stored verbatim on Message.sources
  (in the persist block of chat() in src/memberintel/api/chat/service.py), and entry_performance’s
  citation_count is a count of appearances in that stored set.
• No code parses the answer text for [n] markers. So “cited,” across
  the product prior to this work, has silently meant “was in the top-k
  handed to the model,” never “the model actually referenced it.”

Naming this precisely gives a four-level funnel, of which only two levels
were captured before this change:

Level	Meaning	Captured before v1b?
1. Retrieved	matched vector search (the ~15 candidates)	❌ discarded before return
2. Surfaced	survived re-rank into top-k, went into the prompt	✅ Message.sources → entry_performance.citation_count
3. Cited	model wrote [n] in its answer	❌ no answer-text parsing existed
4. Helpful	that cited answer got 👍	✅ via message_feedback

This ADR’s log fills in levels 1 and 3, closing the “retrieved but not
cited” and “never retrieved” gaps that v1a’s rollup could not see. Full
design and rationale: docs/superpowers/specs/2026-07-07-retrieval-event-log-design.md.

Related: issue #201 (brain usage analytics), epic #8, ADR-0028
(profile-aware retrieval), ADR-0014 (cross-pollination boundary), ADR-0010 /
ADR-0021 (observability, BigQuery posture), ADR-0030 (entry_performance v1a).

Decision

A new retrieval_events Postgres table, one row per (answer message,
candidate global-entry): rank (1-based position in the final re-ranked
ordering), score (cosine similarity), surfaced (made the top-k),
cited (model referenced it via [n]), and created_at (copied from the
message’s created_at, naive UTC — matches entry_performance’s cutoff
convention). Scope is identical to entry_performance: log a candidate
only when tenant_id='global' AND collection<>'memory'. entry_id
carries no FK — re-ingest can replace an entry’s UUID, and a hard FK would
either cascade-delete or block that; analytics use LEFT JOIN brain_entries instead.

Write path. search_brain() gains an opt-in trace of the full deduped,
re-ranked candidate set (global/non-memory only) instead of discarding it.
At message-persist time on both chat entrypoints — the non-streaming
path in chat/service.py and the streaming path in chat/router.py —
log_retrieval_events(db, message_id, trace, response_text) derives
cited by parsing [n] markers out of the answer text
(format_citations numbers sources positionally, so this parse is
deterministic) and bulk-inserts the rows inside a SAVEPOINT
(db.begin_nested()), swallowing and logging any failure. This mirrors the
existing persist_tool_result best-effort pattern: a logging failure must
never roll back a paid answer. The memory search (per-user, not global) and
the /retrieval debug endpoint (no answer, no feedback loop) do not
request a trace and are not logged.

Retention. A 90-day DELETE FROM retrieval_events WHERE created_at < :cutoff is folded into the existing nightly entry_performance refresh
(POST /internal/entry-performance/refresh) rather than standing up a new
Cloud Scheduler job or cloud_run_v2_job. Same rationale ADR-0030 used to
avoid introducing that IaC pattern for one task: the purge is a single
idempotent statement, and the refresh endpoint already runs nightly at
03

UTC.

BigQuery cutover trigger (pre-committed). Postgres is the sink now, not
provisionally — but the migration path is decided in advance rather than
being a future surprise. Cut over retrieval_events to a BigQuery sink
(ADR-0010/ADR-0021) when either:

• sustained write volume exceeds ~50k rows/day (the actionable leg today), or
• once the Phase-2 rollup job exists, the nightly rollup query over
  retrieval_events exceeds a p95 of 5s.

The p95 leg is dormant until Phase 2. The only thing that runs nightly
against retrieval_events today is the 90-day purge DELETE — there is no
rollup query yet (that arrives with the Phase-2 usage report). Until then,
only the write-volume leg is measurable; a future reader should not go
looking for a rollup job that does not exist.

This is the same “start simple, migrate at scale” posture this codebase
already used for pgvector→Pinecone: don’t pay for the more complex system
until the simple one demonstrably strains.

Rows-per-answer is not the full over-fetch: search_brain over-fetches
limit × 3/× 6 and dedups, but only global, non-memory candidates are
logged — a subset that excludes the user-tenant and memory rows in the union.
So a “50k rows/day” ceiling corresponds to more daily answers than a naive
50k ÷ overfetch would suggest; treat 50k rows/day as the measured signal to
watch, not a derived answer-count target.

Observability (the signal behind the write-volume leg). Each write emits
a structured retrieval_events_persisted_total log line (event name +
row_count per message) from log_retrieval_events. The committed plan: a
Cloud Logging log-based counter summing row_count on that event,
reviewed weekly by the brain-content review owner (currently Omar, per
#201, folded into the same weekly ritual ADR-0030 established), with an
alert at ~80% of threshold (≈40k rows/day). What closes review-condition
(1) is that committed signal — the emitted retrieval_events_persisted_total
log line plus the named weekly owner and threshold above. Provisioning the
Cloud Logging log-based metric + alert policy from that log line is a small,
tracked ops/IaC follow-up; it does not yet exist in GCP. A pre-committed
trigger nobody watches is not a trigger.

Privacy (ADR-0014). Because logging is scoped to tenant_id='global'
(Tier-1 system content) exactly as entry_performance is, a row carries
only a global entry id, rank, score, two booleans, and a message id —
no per-user or per-customer content, no cross-tenant flow. This clears the
ADR-0014 boundary on inspection. Erasure is structural: retrieval_events.message_id
is a messages.id FK with ON DELETE CASCADE, so a row is deleted together with the
assistant Message it belongs to — a conversation/message erasure removes its
retrieval-event rows in the same operation, with no separate cleanup step. The full
ADR-0014 sign-off packet is
deferred to the Phase-2 usage-report spec, which is where this data is
actually surfaced to a human; this ADR’s scope rule keeps Phase-1 rows
clean regardless of when that packet lands.

Out of scope (Phase 2, separate spec). The usage-report admin surface;
rolling these events up into entry_performance columns (retrieval-count,
retrieved-but-not-cited-rate); the full ADR-0014 sign-off packet; the
ADR-0031 “skip re-embed on metadata-only updates” optimization (tracked as
its own ticket, unrelated to this log).

Access-control constraint on any future read surface (Phase 2, load-bearing).
Any surface that reads retrieval_events must be admin-gated with the
same require_cf_access_admin pattern entry_performance’s panel uses.
Rationale: although a row carries no customer content, its message_id FK
links a global-entry retrieval back to one specific customer message — so an
ungated read surface would be a re-identification path. This is a prod
gate that must be built, not merely documented — a constraint in this list
is necessary but not sufficient (ceo-blair, 2026-07-07).

Prod go-live conditions (beyond staging/alpha). This ADR is cleared for
staging/alpha only. Before any prod go-live: (1) human counsel (Allen)
sign-off on the ADR-0014 packet — not the stand-in first-pass; (2) the
admin-gating above actually implemented on any human-facing read surface.
ceo-blair wants both brought together with the Phase-2 report spec, not
sequentially.

Consequences

Positive:

• Closes the funnel gap v1a left open — levels 1 (retrieved) and 3 (cited)
  are now captured, enabling true “never retrieved” and “retrieved but
  outranked” detection in the Phase-2 report.
• No new infrastructure: reuses the existing Postgres database, the
  existing best-effort savepoint pattern, and the existing nightly refresh
  job for retention. Zero new IaC.
• The cutover trigger means a future migration to BigQuery is a planned
  response to a named threshold, not a reactive scramble.
• Corrects a latent product-honesty gap: “cited” now has a precise,
  parseable definition instead of silently meaning “surfaced.”

Negative / costs:

• A hot-path write on every answer (both chat entrypoints), where none
  existed before. Mitigated by best-effort SAVEPOINT semantics — a failure
  here never blocks or rolls back the answer.
• entry_id has no FK, so an entry deleted between retrieval and any
  future rollup leaves an orphan event that LEFT JOINs to null. Accepted
  as the trade-off for surviving re-ingest UUID churn.
• Until Phase 2 ships, this table has no consumer besides ad hoc queries —
  it is intentionally “plumbing now, report later” because retrieval
  candidates cannot be reconstructed retroactively once embeddings and the
  corpus drift.
• One more table to include in the retention/ops mental model, though it
  rides the same nightly job as entry_performance rather than adding a
  new one.

Alternatives considered

• Snapshot-then-rollup (store the raw candidate set as JSONB on the
  Message, normalize into rows nightly). More faithful to ADR-0030’s
  “no hot-path event stream” precedent, but two-step and 24h-delayed, and
  #201 explicitly calls for a retrieval-event log with per-row retention
  — the event table is the deliverable itself, so a direct hot-path write
  is the right shape here.

• Log inside search_brain(). Keeps chat/service.py and
  chat/router.py untouched, but at retrieval time there is no persisted
  message_id and no answer text yet, so the cited flag (level 3) can’t
  be computed at that point. Rejected.

• CASCADE FK on entry_id, matching entry_performance’s pattern.
  Rejected: re-ingest can replace an entry’s UUID, and a CASCADE FK would
  either orphan-delete history mid-trend or block re-ingest outright. A
  plain indexed UUID with an analytic LEFT JOIN is more robust for a
  time-series log that must survive corpus churn.

• Log all tenants, not just global. Rejected: triples-plus row volume,
  pulls customer-adjacent content into the log, and serves no Phase-2
  analytic — the usage report only ever reviews the global KB.