retention: dedupeSignalSnapshots keeps the STALE row on Postgres (MAX(rowid)→MAX(ctid)) — confirmed deleting fresh snapshots in production

[JSONbored]

Summary

dedupeSignalSnapshots keeps "the latest row per key" by selecting MAX(rowid). On Postgres — the primary production database — the dialect shim rewrites rowid to ctid, which is a physical heap location, not insertion order. The dedupe therefore keeps a stale snapshot and permanently deletes the fresh one for a large fraction of keys.

This is empirically confirmed on the production database, not inferred. Measured on edge-nl-01 on 2026-07-27, ~36% of multi-row keys for contributor-evidence-graph have ctid ordering that disagrees with actual recency.

Mechanism (verified at HEAD, 776c414)

src/db/retention.ts:256:

const staleCondition = `signal_type = ?1 AND rowid NOT IN (SELECT MAX(rowid) FROM signal_snapshots WHERE signal_type = ?1 GROUP BY target_key)`;

src/selfhost/pg-dialect.ts:192-194:

export function translateRowid(sql: string): string {
  return sql.replace(/\browid\b/gi, "ctid");
}

ctid is (page, tuple). After the age-prune frees pages, a newer row inserted into a reclaimed early page receives a lower ctid than an older row sitting on a later page. MAX(ctid) then selects a stale row, and the NOT IN delete removes the genuinely newest snapshot. The loss is permanent.

The shim's own documentation forbids this usage

src/selfhost/pg-dialect.ts:185-191 states ctid is safe only for

internal bookkeeping resolved within one statement … never for durable application-facing row identity

But MAX(rowid) here decides which row survives a DELETE — durable row identity by definition. Meanwhile retention.ts:236-239 asserts the invariant:

"Latest" is the highest rowid: signal_snapshots is populated by a single sequential batch job, so insertion order and generated_at agree, and rowid (unlike generated_at) can never tie.

That is a SQLite-only truth which became false the moment the pg dialect shim was introduced. Neither comment references the other.

Production evidence (edge-nl-01, loopover-postgres-1, 2026-07-27)

Comparing, per (signal_type, target_key) with more than one row, the row chosen by ctid against the row chosen by recency:

SELECT signal_type, count(*) AS keys_where_ctid_picks_wrong_row FROM (
  SELECT signal_type, target_key,
         (array_agg(id ORDER BY ctid DESC))[1]                        AS by_ctid,
         (array_agg(id ORDER BY generated_at DESC, id DESC))[1]       AS by_time
  FROM signal_snapshots GROUP BY 1,2 HAVING count(*) > 1
) t WHERE by_ctid IS DISTINCT FROM by_time GROUP BY 1 ORDER BY 2 DESC;

signal_type	keys where ctid picks the wrong row	dedupe-eligible?
contributor-evidence-graph	114 of 319	yes
contributor-strategy	112 of 310	yes
contributor-outcome-history	112 of 310	yes
repo-doc-refresh-attempt	3	yes
config-quality	3	yes
repo-public-focus-manifest	3	yes
contributor-decision-pack	157	no — excluded by design
queue-health	16	no — excluded by design

Dedupe eligibility per LATEST_ONLY_SIGNAL_SNAPSHOT_TYPES (src/db/retention.ts:198-218). The two largest divergence counts are excluded from dedupe and are therefore not part of the impact — roughly 344 keys across eligible types are.

The dedupe demonstrably runs, and deletes at scale

audit_events where event_type = 'retention.prune':

2026-07-27T03:03:13.729Z | pruned 0 row(s), deduped  8570 signal_snapshots row(s)
2026-07-26T03:03:13.536Z | pruned 0 row(s), deduped  1977 signal_snapshots row(s)
2026-07-25T03:03:13.651Z | pruned 0 row(s), deduped  2031 signal_snapshots row(s)
2026-07-24T03:03:13.872Z | pruned 0 row(s), deduped 16928 signal_snapshots row(s)

Note pruned 0 row(s) — the age-prune deletes nothing (everything is inside its window), so the dedupe is doing all of the deleting. The measurement above was taken after the 03:03 run, on rows accumulated since; it is the population the next run will process. The mechanism has been active on every prior daily run.

Impact

signal_snapshots is the largest table in the database (818 MB / 13,155 rows). The affected types back contributor-intelligence and repo-policy state. The observable symptom is older intelligence state silently reappearing after a retention run, with no error and no log — readers order by generated_at DESC and faithfully serve whichever row survived.

Additional note: on PostgreSQL < 14 the same SQL hard-errors (function max(tid) does not exist), which would make runRetentionPrune throw after the prune step (src/queue/retention.ts:20). The compose file pins pgvector/pg16, so it silently mis-selects instead of failing loudly.

Requirements

1. "Latest per key" must be decided by a real, monotonic, application-owned column — never by physical row location.
2. The fix must be correct on both backends.
3. The regression must be caught by a test that runs against real Postgres, not SQLite.

Deliverables

• Replace the MAX(rowid) selection with a deterministic application-level key: MAX(generated_at) with id as the tie-break, or MAX(id) if id ordering is guaranteed monotonic. State which and why.
• Audit every other rowid usage that reaches Postgres for the same class of error. translateRowid's doc names retention.ts batched pruning and orb/relay.ts ordering as the sanctioned uses — confirm each is genuinely "resolved within one statement" and not a durable selection. (src/orb/relay.ts:348-351's coalesce rowid < predicate is a known suspect and is tracked separately.)
• Strengthen translateRowid's doc with an explicit "never use for MAX/MIN row selection" clause, since that is the exact misuse that occurred.
• Correct the stale invariant comment at retention.ts:236-239.
• Assess recoverability: determine whether the surviving stale rows can be re-derived (the canonical latest for the contributor types lives in contributor_evidence / contributor_scoring_profiles upsert tables per retention.ts:203-208) and, if so, whether a one-off re-derivation is warranted.

Tests (must fail against current main)

• Against real Postgres: insert rows for a key, delete some to free pages, insert a newer row (so it lands on a reclaimed page with a lower ctid), run the dedupe, assert the surviving row is the one with the greatest generated_at. This is the test whose absence allowed the bug — a SQLite-only test cannot catch it.
• SQLite parity: same assertion, unchanged behaviour.
• Excluded types (contributor-decision-pack, queue-health) are still not deduped.

Expected outcome

The dedupe keeps the newest snapshot per key on every backend, and the class of bug — SQLite-shaped SQL silently changing meaning under the pg shim — is closed by documentation plus a real-Postgres test.

[linear-code]

JSO-6534