feat(orestar-api): donor read surface, folded by normalized name

Second slice of the client-facing ORESTAR API (Substrate plan 75a0). Adds the donor-scoped reads the first slice deliberately withheld, plus the prospects endpoint Linear THO-37 is blocked on.

Route	What
GET /v1/committees/:id/donors	Who funded this committee, folded by donor
GET /v1/committees/:id/prospects	Donors to comparable committees, absent from yours
GET /v1/donors?q=	Donor search (q required)
GET /v1/donors/:id	Totals, committees funded, identity block
GET /v1/donors/:id/transactions	That donor's filings

:id accepts our canonical donor:orestar-<b64url>, a raw payer_id off a transaction row (person:orestar-name-…), or a committee id. The raw form is the id a caller actually holds after reading /transactions, and it resolves to the same folded donor — so it returns the filings under that donor's other hashes too.

The split diagnosis was wrong, and it changes the fix

Plan 75a0 recorded a measured 19.7% donor-id split and attributed it to case/whitespace, prescribing "normalize case + whitespace before hashing" as a cheap ingest-side fix.

Reading packages/ingest/.../orestar/mapper.py counterparty_ref, that normalization has been in the id derivation since the store's first commit (2738f1e4):

name   = _WS.sub(" ", row.get("contributor_payee", "").strip().lower())
digest = hashlib.sha256(f"{name}|{row.get('zip', '')}".encode()).hexdigest()[:12]

So the prescribed fix is a no-op. The measured magnitude stands — 11,288 normalized-name groups covering 25,780 of 131,056 person entities — but the cause is the ZIP component. The same donor filing under a second, blank, or mistyped ZIP mints a second id. Sonya Fischer / sonya Fischer are two ids because their ZIPs differ; the casing difference is incidental.

Two consequences:

• There is no cheap ingest-side half. The split can't be repaired by re-hashing. Folding has to happen on read, which is what this MR does.
• Dropping ZIP from the digest would be worse. ZIP currently suppresses part of the 1.3% merge problem; removing it would trade a repairable split for an unrepairable merge.

Also found: ZIP is never emitted as a statement — it goes into the digest and is discarded — so the store can't explain its own splits. Emitting it as a Person/Organization prop is a cheap ingest follow-up and the only way a caller could ever confirm two ids are the same human.

Honesty rules this MR holds to

• identity on every donor-shaped row — basis, the folded ORESTAR ids, name variants, employer count, possible_merge, and a plain-language caveat. A total without it is a name match presented as a fact.
• Fold exactly as far as the measurement went — lowercase, trim, collapse whitespace, nothing else. No punctuation stripping, no nickname expansion. On a table that already merges 1.3% of ids, an unmeasured fold is the failure mode that matters. Tests assert the non-folds (J. Smith ≠ J Smith).
• Merge is flagged, never fixed. >3 distinct employers → possible_merge: true and a sharper caveat. Real ER stays Phase 2.
• Committee donors are exempt and say so (basis: orestar_committee_id).
• No blended relevance score. The caller picks a sort axis (amount, recent, transactions, committees). One ranking number computed off a name match would be an opinion read as a fact.
• Prospects return their peer set (peers.committee_ids, peers.basis), overridable with ?peer=. "Comparable" is the whole judgement, so it's visible.
• Amounts per direction, never netted — same rule /totals follows on amendments.
• Search truncation is reported past 1000 names. A silently trimmed result set reads as a complete one.

Bug caught in review

The obvious one-pass search — filter payments by the search string, sum what matched — is wrong. Sonya Fischer (two spaces) and Sonya Fischer fold to one donor, but only one of them ILIKEs sonya fischer, so the donor's total would silently omit their other filings. Search is therefore two phases: find the matching normalized names, then aggregate every payment belonging to those names through the expression index. Scope by the fold key, never by the text the caller typed.

Migration

20260805000003 adds the normalized-payerName expression index the fold rides, CONCURRENTLY + txmode none like 20260805000002 (ftm.statements is the ingest's write target). Its expression must stay character-identical to normalizeNameSql() — a near-miss is a silently ignored index rather than an error, so test/donors.test.ts reads the migration file and asserts the two agree.

Verification status — read before merging

• ✅ tsc --noEmit clean
• ✅ 45 tests pass (19 prior + 26 new)
• ✅ All 11 generated SQL statements + the migration parse clean under libpg_query (real Postgres grammar)
• ❌ Not run against prod data. The local permission classifier blocked task db:tunnel:prod, so no query here has touched real rows.

That last one is the gap that hid both bugs the first slice found (direction filter applied after LIMIT; dates serialized as JS Date instead of ISO). Syntax parsing does not catch column-resolution or aggregate-legality errors. This should not merge until the queries have run against prod.

Also outstanding

Migrations 20260805000002 and 20260805000003 are both unapplied. Until they land, donor reads scan the 1.04M-row payerName partition and /prospects pays the 5.2s committee enumeration for peer discovery. /v1/committees/:id/donors is fast regardless — it rides the pre-existing statements_prop_value_idx.

🤖 Generated with Claude Code