Ticket Writer

✎ Your Input

The reconcile endpoints for profile stats are timing out in production. All five of them hit the same pattern — they do a full table scan then write back aggregate stats. Need to batch the writes and add an index on profile_id + period.

✨ Enriched Ticket

PLAT-412 Fix reconcile endpoint timeouts via batched writes & composite index

Description

Five profile-stats reconcile endpoints (stage, event, series, award, platform) exceed the 10s gateway timeout under normal load. Root cause: each endpoint performs an unbounded SELECT then individual UPSERTs in a loop. Add a composite index on profile_id, period and batch writes into chunks of 100.

Related Context

StatsReconcileService profile_stats table P95: 12–16s Endpoint Agent flagged

Acceptance Criteria

All five reconcile endpoints respond under 3s at P95
Composite index migration added and tested
Writes batched in chunks of 100 with transaction rollback
Existing unit tests pass; add load test for 500+ profiles