Restore global request_id uniqueness and the missing columns on the partitioned usage_events table #1486

Summary

The hash-partition migration changed the shape of usage_events in ways the application never caught up with. Uniqueness is now (request_id, developer_id) instead of request_id. BillingService never sets developer_id, so every billed row lands in one partition. The columns status_code and api_key, which PostgresUsageStore writes and reads, no longer exist. revenue_ledger.usage_event_id still references the renamed usage_events_old. This issue restores a global idempotency guarantee on request_id, makes every writer populate developer_id, and repairs the columns and foreign key so the partitioned table is the single source of truth.

Background and evidence

• Uniqueness was weakened.
  • migrations/0011_partition_usage_events.sql creates the parent with PRIMARY KEY (id, developer_id) and UNIQUE (request_id, developer_id). A partitioned table cannot have a unique key without the partition key.
  • src/services/billing.ts relies on "the UNIQUE constraint on request_id is a second line of defence" (header comment, and the 23505 handling in deductInternal and deductBulkInternal). That guarantee no longer exists once two writers use different developer_id values for the same request_id.
• Billing writes leave developer_id empty.
  • runPhase1 and runPhase1Bulk insert (user_id, api_id, endpoint_id, api_key_id, amount_usdc, request_id, created_at) with no developer_id, so it defaults to ''.
  • All billing rows hash to the same partition, which defeats the partitioning, and the idx_usage_events_developer_created index is useless for them.
• Columns the code uses are gone.
  • migrations/005_add_persistent_store_columns.sql added api_key and status_code to the flat table. The new parent created in 0011 has neither.
  • PostgresUsageStore.record (src/services/usageStore.ts, about line 237) inserts status_code, and usageEventSelect (about line 202) reads ue.api_key and ue.status_code.
  • Both fail with column ... does not exist on a migrated database.
• A foreign key points at the old table. migrations/003_create_revenue_ledger.sql declares usage_event_id BIGINT REFERENCES usage_events(id). After the 0011 rename, Postgres keeps that constraint attached to usage_events_old, so new ledger rows are validated against the wrong table, or fail.
• Mixed conflict targets. PostgresUsageStore.record uses ON CONFLICT (request_id, developer_id), while billing does not, so the two writers disagree on what a duplicate is.

Scope

In scope

1. Global request-id registry.
   • Add usage_event_request_ids (request_id VARCHAR(255) PRIMARY KEY, usage_event_id BIGINT NOT NULL, developer_id VARCHAR(255) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT NOW()).
   • Every insert into usage_events (billing single and bulk, PostgresUsageStore.record, UsageEventsPgRepository) first inserts here in the same transaction.
   • The billing SELECT ... FOR UPDATE idempotency check moves to this table.
2. Populate developer_id.
   • Resolve the API owner (apis.developer_id) inside the Phase 1 transaction, the same way PostgresUsageStore.record does with a subselect.
   • Reject the deduction with a typed error if the API is unknown, instead of writing ''.
3. Restore the columns. Add status_code INTEGER NOT NULL DEFAULT 200 and api_key VARCHAR(255) to the partitioned parent, or remove them from usageStore.ts and its types. State the choice in the PR. Keep docs/schema.md in sync.
4. Repair the foreign key. Drop the revenue_ledger constraint that references usage_events_old. Because usage_events.id alone is not unique on the partitioned table, either reference usage_event_request_ids(usage_event_id) (made unique) or enforce the link in application code with a documented invariant check.
5. Backfill. Extend scripts/backfill-usage-partitions.ts to populate usage_event_request_ids and to fail loudly on duplicate request_id values across developers. Write those duplicates to a report rather than silently keeping one.

Out of scope

• The amount unit change, which is a separate issue.
• Changing the number of partitions or the hash key.
• Read-replica routing.

Design constraints

• One transaction per write. The registry insert and the usage_events insert share a transaction; a failure of either rolls back both.
• Concurrency. Two concurrent deductions with the same request_id and different developer_id must result in exactly one row. The loser gets the existing row, never a second charge.
• Migration safety. Idempotent, with a .down.sql, and it passes npm run db:check-migrations.
• Bounded hot-path cost. At most one extra indexed insert per deduction. Include an EXPLAIN of the new idempotency lookup in the PR.

Test plan

Use testcontainers Postgres for the partition and foreign-key rows (pg-mem does not support declarative partitioning). Mark those tests with the existing integration pattern under tests/integration/.

Regression proof. Row 5 fails with column "status_code" does not exist and row 3 shows '' on current main.

Acceptance criteria

• No code path inserts into usage_events without also writing usage_event_request_ids in the same transaction.
• grep -n "developer_id" src/services/billing.ts shows developer_id in both Phase 1 inserts.
• npm run db:check-migrations passes.
• npm run lint, npm run typecheck, npm run build, npm test and npm run test:integration pass, and CI is green.
• Line coverage of at least 90% on the touched functions (npm run test:coverage).

Getting started

git checkout -b fix/usage-events-partition-keys
npm ci && npx prisma generate
npm run test:unit -- src/services/billing src/services/usageStore

Read first: migrations/0011_partition_usage_events.sql, migrations/005_add_persistent_store_columns.sql, migrations/003_create_revenue_ledger.sql, runPhase1 / runPhase1Bulk in src/services/billing.ts, and PostgresUsageStore in src/services/usageStore.ts.

PR requirements

• Link this issue (Closes #<n>).
• Explain how global uniqueness is guaranteed despite the partition-key rule, with the EXPLAIN output.
• Paste test output, including the concurrency test, and the coverage summary.
• No unrelated schema changes.
• Example commit: fix(db): add global request_id registry for partitioned usage_events.

Priority rationale

High. Idempotency of money-moving deductions currently rests on a constraint that no longer exists, and two production writers fail outright on the migrated schema. The fix needs knowledge of Postgres partitioning limits, transactional design and migration safety.