One Call, Two Ids: Deduplicating Telemetry When the Event Key Changes

My usage collector sees one model call from two places. A local proxy sits on ANTHROPIC_BASE_URL, watches the request go out and the response come back, and keys the event on the message id in the response, msg_…. A transcript harvester reads the session log afterward and, until 2026-09-16, keyed the same call on the request id written in the log, req_…. The event id is the SHA-256 of provider|request_id|attempt, cut to 24 hex characters. Both ids are correct ids for the call. Neither hashes to the other.

Two observers spelled the request id differently, so the same call landed in the store twice. Three repairs followed in one week of September. The first proved 4,327 duplicate pairs by exact id association and marked them superseded instead of deleting them. The second hid every unowned row from the public totals. That fixed a doubled aggregate and zeroed seven public days. The third proved 173,280 one-to-one twins and restored the 28,371 rows that had no owned twin. The same day, a second rule settled 424 Codex duplicate groups from forked and mirrored sessions: the copy ingested first wins.

The scope is one collector, its PostgreSQL store on Supabase, and the public day totals that store publishes. Every count comes from the repair documents and the read-only measurements dated 2026-09-17 to 2026-09-23. The store is live, and the totals have moved since.

This is the id function, from the recorder's event.go, with one long line wrapped:

func EventID(provider string, requestID *string, attempt int) string {	if requestID != nil && *requestID != "" {		h := sha256.Sum256([]byte(			provider + "|" + *requestID + "|" + itoa(attempt)))		return "e_" + hex.EncodeToString(h[:])[:24]	}	var b [12]byte	_, _ = rand.Read(b[:])	return "e_" + hex.EncodeToString(b[:])}

The database has no other notion of "the same call". event_id is the primary key of usage_events, and there is no unique index on install, instant, model and tokens. A second spelling of the request id is a second primary key, and the insert goes through.

Exact Id Associations Proved 4,327 Pairs, and No Row Was Deleted

On 2026-09-17 I read the anonymous install's rows for September 1 to 16, read-only. There were 41,949 observations: 32,259 transcript exports and 9,690 proxy observations. The local transcripts still held both ids for each call, the requestId on the line and the message.id inside the message. The repair scanner takes that association, computes both event ids with the recorder's own hash, and looks for both rows on the server. It proved 4,327 pairs, all between September 9 and 16.

The scanner refuses anything it can't prove. An ambiguous association, a pair across two installs, two providers or two attempts, an aggregate row, a quarantined row and a row already superseded are each rejected. Two rows with the same four token counts a second apart aren't a pair on that evidence. The scanner has no code path that compares sizes, so two distinct calls of the same size stay two rows.

Each old transcript row stays in the table with superseded_by pointing at its canonical row. Usually that's the proxy's row, which carries the latency and time to first token the transcript lacks. The rollup, the per-install flood-cap counter and the public totals skip a superseded row, and a resend of one is ignored. Nothing is deleted and no quarantine flag changes. The apply script and its undo ran first in a disposable PostgreSQL replay of the reviewed snapshots, and the production apply validated every reviewed row's full snapshot before it marked anything. Afterward 205,063 raw rows were retained, 4,327 carried the marker and 200,736 were canonical.

At 22:54 UTC that night the rebuilt totals still weren't verified. The rollup writer had always emitted an aggregate_calls column that the usage_cohorts table never had, and the first approved rollup failed on it. One additive migration later, four rollups cleared the 184 dirty days. The final read-only check, at 23:17 UTC, compared 71 cohorts across the eight repaired days against the canonical events and found zero mismatches.

Hiding Every Unowned Row Fixed the Total and Zeroed Seven Public Days

The second duplication ran on a different axis. Before uploads were authenticated the collector wrote under an anonymous install id. After auth set the same spool was re-uploaded under an owned install, covering the same span from 2026-03-06. An owned event id is a digest of owner, install and wire id, so the two copies never collided, and the public day aggregates counted both. Measured read-only on 2026-09-22 (one line wrapped):

unowned events   205,978   (4,327 already superseded)                           2026-03-06 .. 2026-09-18owned events     188,898public total     394,876  ->  188,898 after the repair

The fix was one migration. The two functions that stamp a day revision and backfill a day gained OR owner_id IS NULL, so an unowned event never enters a public total again. One UPDATE set removed=true on the 201,651 unowned revisions already written. Nothing was deleted. The document that proposed it said what the cut would cost. It keeps the owned copy of each call and drops the anonymous one, which undercounts the legacy rows with no owned twin, estimated at roughly 21,700, against an overcount of roughly 184,000. Matching them pair by pair was named as a different, larger repair, and I approved the cut.

The next day the public totals showed zero calls for 2026-09-10 to 09-15 and for 09-17, 5 calls for 09-16, and dips from 09-04 to 09-09. The 21,700 estimate was low. Measured on 2026-09-23, 28,371 of the 201,651 live unowned rows had no owned transcript twin: 18,184 Codex rows, 10,158 proxied Anthropic rows, 9 transcript rows keyed req_ from two sessions in March to May, 2 transcript rows keyed msg_, and 18 local smoke-test rows. On 09-10 the 970 calls that had vanished were 645 Codex rows and 325 proxied Anthropic rows, each the only record of its call.

A Twin Is One Anonymous Row With Exactly One Owned Row

The third repair replaced the category with a relation. I paged every owned and unowned row out of the store and joined them offline. An anonymous req_ transcript row and an owned transcript row are twins when they agree on occurred_at to the millisecond, provider, model, the Claude Code session id and all four token counts: input, cached, cache write and output. Of the 173,289 live anonymous req_ rows, 173,280 had exactly one such owned row. No key had two owned rows or two anonymous rows, so the relation is one-to-one, and leaving the session id out of the key gave the same 173,280.

The other 9 req_ rows had no owned row at all, and they count again. Every owned twin is a live claude-code-export row keyed msg_, and it agrees with its anonymous row on the cancelled flag, the call count, the status, the attempt, the one-hour cache-write count and the quarantine flag as well. No anonymous row outside the req_ transcript class shares a request id with any owned row. So the 201,651 live unowned rows less 173,280 twins leaves the 28,371 the cut had hidden, and the split by source in the section above is that number.

The rule is a SQL function, one definition of "anonymous copy of an owned call". This is it from the migration, trimmed of its grant and search-path lines and wrapped to fit:

CREATE OR REPLACE FUNCTION public.usage_public_twin_hidden(  e public.usage_events) RETURNS BOOLEAN LANGUAGE sql STABLE AS $$ SELECT e.owner_id IS NULL AND e.source='claude-code-export'   AND e.provider_request_id LIKE 'req\_%' AND EXISTS(  SELECT 1 FROM public.usage_events o  WHERE o.occurred_at=e.occurred_at AND o.owner_id IS NOT NULL   AND o.superseded_by IS NULL   AND o.source='claude-code-export'   AND o.provider_request_id LIKE 'msg\_%'   AND o.provider=e.provider AND o.model=e.model   AND o.logical_call_id IS NOT DISTINCT FROM e.logical_call_id   AND o.input_tokens IS NOT DISTINCT FROM e.input_tokens   AND o.cached_tokens IS NOT DISTINCT FROM e.cached_tokens   AND o.cache_write_tokens IS NOT DISTINCT FROM e.cache_write_tokens   AND o.output_tokens IS NOT DISTINCT FROM e.output_tokens);$$;

The token comparisons use IS NOT DISTINCT FROM, so a count that is null on both rows still matches. The function tests that a matching owned row exists. The one-to-one count came from the offline join before the apply, and the migration's pre-apply gate expected 173,280 twins and 28,371 restored rows from the same predicate. The migration also refuses to run if owned rows on a day with unowned rows have been moved to an archive, since a twin would then look missing. The trigger that stamps a day revision and the backfill walker call the function where OR owner_id IS NULL used to be, and nothing else in either body changed. One reverse update set removed=false for unowned, unsuperseded rows that aren't twins, on their own UTC day: 28,371 rows in production. It marked exactly their days dirty, 78 of them, so the rollup republishes each one. A re-run restores nothing and marks no day. usage_events, usage_event_identity and usage_event_revisions were untouched, and the account dashboard reads only owned rows, so it didn't move.

The figure above runs three rules on eight made-up rows from two observers of one session. Exact id association proves two twins and invents none. Token counts within 2% merge the same two pairs by chance and then merge two more pairs that are distinct calls of about the same size. Dropping the harvester's rows loses two calls the proxy never saw. The note under the rows carries the production counts: 4,327 pairs, 173,280 twins, 28,371 rows that were a call's only copy.

The Twin Rule Put 28,300 Calls Back Across 78 Days

The rehearsal runs as npm run test:public-totals-repair. It starts a disposable postgres:16-alpine container with no network and no host port. It applies every migration before this one, so the 2026-09-22 cut is live in the container. It seeds one anonymous row of each kind the production store holds: four req_ twins, a req_ row with no owned row, one with a single differing token count, one from a different session, Codex rows, proxied rows, a cancelled row, a superseded row, an unknown listing, and a second owner's rows. Then it publishes public totals through the real rollup slice and accumulator. Public calls on the three fixture days were 3, 2 and 1 under the cut. After the repair they were 6, 4 and 4, which is every non-twin, non-cancelled, known-listing row and nothing more. The undo brought them back to 6 exactly, and the digests of the events, identities and revisions were byte-identical before and after.

The expected production result came from fetching the 28,371 re-included rows in full and running them through the production accumulator with the catalog of the published 2026-09-22 job, per UTC day:

re-included rows                                   28,371  - cancelled (all proxy-anthropic)                    56  - unknown listing (smoke-test models)                14  - one row with calls=1,000,000 (> MAX_EVENT_CALLS)    1= calls added to public totals                     28,300

The accumulator applies those three exclusions to every row it counts, so the repair adds 28,300 calls from the 28,371 rows. Public calls stood at 195,342 before the apply and were expected at 223,642 after it. The migration went in on 2026-09-23 between 05:40 and 05:43 UTC, after a pause that returned 503 to uploads and a drain that waited for 0 rollup jobs in flight. The collector's next sync uploaded 1 event with 0 rejected at 05:44. The 5-minute cron claims one day per run, oldest first, so the 78 days took about 6.5 hours to republish, and until a day was republished it kept its old cohorts.

The per-day numbers are the accumulator's result for each day, computed before the apply. The fifteen days in the figure, 2026-09-04 to 09-18, get 20,351 calls back. 09-09 goes from 3,771 to 8,475. 09-16 goes from 5 to 3,965, and 3,643 of those are proxied Anthropic calls. The seven days that had been published at zero come back between 518 and 1,993 calls each. The other 63 changed days run from 2026-03-29 to 09-03, most of them adding Codex rows only, between 1 and 1,144 calls each. The readiness check two days later re-evaluated every source row against each day's published catalog and reproduced the published total.

The same 173,280 twins were in the recorder's local SQLite spool, where the key change had written every call again under msg_ after a reader-version bump. Grouping the spool's live export rows on the same fields gave 173,280 groups of exactly one req_ and one msg_, 3,377 req_-only rows and 35 msg_-only rows. Reading the 4,037 transcript files mapped every req_ id to exactly its paired msg_ id, 173,280 of 173,280, none missing and none contradicted.

Of the 3,377 req_-only rows, 3,367 had a proxy msg_ twin that the 09-16 local dedupe had already superseded, so they're counted once today and the repair leaves them alone. The other 10 are calls where one requestId carried two message ids, which the old reader merged into one row and the new reader wrote as two. They aren't exact twins, so they're left alone and reported.

The rehearsal ran on a 768 MB copy of the spool. Apply marked the 173,280 req_ rows superseded in about 8 seconds and wrote a receipt with the SHA-256 of every column of every affected row before and after. A second apply was refused with "Reviewed row changed; refusing entire transaction". The undo's receipt reports matches_pre_apply_digest: true, and the digest of the whole events table after undo, 3caae541…, is the digest from before the apply.

On the real spool, with the collector stopped and a backup taken through SQLite's backup API, a fresh plan listed the same 173,280 rows and the apply changed 173,280 rows. A count afterward found 177,607 superseded rows and 3,377 live req_ export rows, and a dry run planned 0. The harvester now records the old requestId as a prior id on each event, and the spool supersedes a live row stored under a prior id in the same transaction as the new row. The reader version was left alone on purpose, so installing the fix doesn't re-read history or touch an existing row.

Repeated Codex Session Files Needed a Second Rule: The Copy Ingested First Wins

Codex session files from CLI versions before 0.153 carry no response id, so the recorder keys each call tc:<session id>:<line time>. When a second session file repeats the same token-count line at its original time, the same call gets a second key, a second event id and a second row. The twin repair had just put 18,184 anonymous Codex rows back, and 345 of them were one copy of a call whose other copy is an owned row. I paged every codex-export row, 31,411 of them, and grouped them offline on the instant, provider, model, the Codex turn and the input, cached, cache-write and output token counts. No group differed in reasoning tokens either. 424 groups had more than one row: 423 pairs and one triple, 849 rows, 425 extra copies. None of the copies was a resp_ row, and in every group each copy named a different session file.

Two patterns made the copies. A session created on 2026-09-23 at 02:36 UTC held 79 rows that were all copies of April and May calls from a session ingested sixteen minutes earlier, which is a fork or a resume. 15 anonymous parent sessions held their own calls plus every call of 34 owned child sessions, each child created 26 to 186 seconds after its parent, which is a parent mirroring its subagents. The fork pattern is 78 pairs and the triple. The parent pattern is the 345 pairs.

The SQL function for this rule matches a live tc: row against another tc: row from a different session file on the instant, provider, model, turn and all five token counts, reasoning included. Within a matching group the copy ingested first counts, and a tie goes to the smaller event id. In all 424 groups that's the original or parent session's copy. Choosing the winner reads one column. A re-upload keeps its original ingested_at through a COALESCE in the ingest path, so a copy that arrives later than the row it repeats loses every time. The recorder does the same thing locally when it stores a legacy Codex row: if a live row stored earlier records the same call, the new one is marked superseded in the same transaction. All 425 rows that stopped counting were owned: 345 owned copies of anonymous calls and 80 owned copies of owned calls. The account dashboard reads every live owned row, so it still counts those 80 twice, and it will until a separate change marks them. The applied update touched 425 rows on 9 days. The cron republishes one day per 5-minute run, so republishing the 9 days takes about 45 minutes.

Isn't the Fix Just One Id?

Going forward, yes. Since the 2026-09-16 commit the harvester keys a call on message.id, the id the proxy already used, and requestId is the fallback for a line with no message id. The reader also names the old requestId as a prior id, so when a re-read stores a call under the new key the spool supersedes the row it stored under the old one. A test replays that end to end: the old reader stores under req_, the offsets are forgotten, the new reader re-reads, and the test checks one live row per call and that a second re-read changes nothing. Without the fix the test sees five live rows for three calls.

The store already held both spellings when that commit landed, and by 2026-09-22 the public total published from it was 394,876. The key change itself shipped without naming the prior key, which is how 173,280 calls came to be stored twice in one spool. The first repair had the transcripts' own requestId to message.id association as proof for every pair. The second had a category, and the category held 28,371 rows with no twin along with the 173,280 copies. The third was the pair-by-pair repair the second document had named and I had declined the day before. The join showed the twin relation was one-to-one, and the repair that followed was one function and one update.

Limits

The 4,327 pairs, the 173,280 twins and the 424 Codex groups are three populations. The pairs are proxy rows against transcript rows inside the anonymous install over September 1 to 16. The twins are anonymous req_ transcript rows against owned msg_ transcript rows over March to September, and the 4,327 superseded rows sit inside the 205,978 unowned rows the twin measurement starts from. The Codex groups are legacy tc: rows with no response id, anonymous and owned alike.

The Codex rule groups rows on content and then picks the winner by ingestion order. A later copy with different token counts isn't in the group, and a later copy that happened to be fuller in some other column would lose. Legacy tc: rows are single-line calls and don't grow, so no current row is in that case.

The 394,876 on 2026-09-22 is 188,898 plus 205,978, raw row counts, so it includes the 4,327 rows the first repair had already superseded. Subtracting them gives 390,549, a figure no document states, and neither sum is the published cohort total at that instant, which skips cancelled calls and unknown listings.

The dashboard still counts the 80 owned Codex copies twice. The 345 owned copies whose winner is an anonymous row are that owner's only copy on the dashboard, so they aren't double counted there.

The question to settle before the first upload is which field each observer hashes. In this recorder the answer now lives in the harvester, whose comment says a future key change must name every earlier key, and the spool supersedes the old-key row in the same transaction as the new one.