Skip to content

adr: 0023 title: "Durable, lossless football-event capture (SP1 reliability layer)" status: accepted implementation-status: in-progress date: 2026-06-07 implemented-date: null implemented-in: - ftl-backend feat/event-storage - ftl-backend feat/event-fallback-recovery - ftl-backend bug/livescores-inplay-source - ftl-backend bug/sportmonks-ticker-hardening - ftl-backend bug/missing-price-trigger-events - ftl-backend feat/event-reconciliation - ftl-backend feat/event-replay - ftl-backend bug/event-ordering deciders: [@amalkrsihna] affects-specs: [event-ingestion] affects-code: - ftl-backend/migrations/000041_create_match_events.up.sql - ftl-backend/migrations/000042_create_event_bump_log.up.sql - ftl-backend/migrations/000043_create_event_recovery_tasks.up.sql - ftl-backend/internal/sportmonks/events_feed.go - ftl-backend/internal/sportmonks/ticker.go - ftl-backend/internal/sportmonks/scoring.go - ftl-backend/internal/sportmonks/client.go - ftl-backend/internal/sportmonks/postmatch.go - ftl-backend/internal/sportmonks/reconciler.go - ftl-backend/internal/match/events.go - ftl-backend/cmd/api-server/main.go - ftl-backend/scripts/replay-events/main.go supersedes: null superseded-by: null

ADR-0023: Durable, lossless football-event capture (SP1 reliability layer)

Context

Football events (goals, cards, subs, VAR, …) drive everything monetizable in FTL: price bumps, the live event feed, market movement, and post-match scoring. A code audit of the as-shipped pipeline (release/staging) found it could permanently lose events, and that the event timeline had no durable store at all:

  1. Redis-only timeline. Events lived solely in the per-fixture Redis LIST match_events:{fixtureID} (24h TTL, 100-entry cap, oldest LTRIM'd). A Redis flush/eviction/restart between kickoff and full-time destroyed the entire timeline with no recovery path. No Postgres table held events (confirmed across all migrations).
  2. Delta-feed ingestion. The ticker polled livescores/latest — a ~10s delta feed that returns a fixture only in the brief window just after it changed. Any missed poll (deploy/rolling restart, GC pause, slow request, open circuit breaker, lost lease) dropped that window's events forever; they were never re-presented.
  3. Flush-vulnerable bump idempotency. The only guard against one event bumping a price twice was the Redis SET seen_events:{fixtureID} (4h TTL). After a flush it was gone, so the next poll re-applied every bump (double price movement). The micro_bump accumulator was Redis-only and not replayable.
  4. No full-time backstop. ProcessCompletedFixture fetched lineups.details.type only — it never reconciled the event timeline at FT.
  5. No recovery / replay primitive. Nothing re-fetched a fixture's events from the provider after a gap. reconcileStaleLiveMatches recovered FT settlement only, not events.
  6. Operational fragility. The 10s ticker was not lease-gated (every replica polled the provider → N× quota burn) and had no recover() (one panic stopped live pricing for all fixtures ~30s); the client had no retry/backoff and a dead halfOpenTimeout; the read path ordered events by Redis insertion, so an out-of-order backfill surfaced at the wrong timeline position.

The platform targets World-Cup traffic (10k–50k users, ~5k concurrent, multiple simultaneous matches). Losing an event is unacceptable.

Decision

Adopt Postgres as the durable source of truth for events, with Redis as a hot cache, and build a layered fallback ladder so no event is permanently lost.

Durable storage (new schema, additive)

  • match_events (mig 000041) — every discrete event is written here in addition to the Redis LIST. UNIQUE (fixture_id, provider_event_id); all writers INSERT … ON CONFLICT DO NOTHING. Stores assist attribution (assist_player_id/name from the provider's related_player), minute, extra_minute, result, team_side, source (live|backfill|reconcile|postmatch|replay), and the full raw_payload.
  • event_bump_log (mig 000042) — exactly-once price-bump ledger, UNIQUE (fixture_id, provider_event_id, instrument_id). The ticker INSERT … ON CONFLICT DO NOTHING RETURNING id before applying the Redis micro_bump; the bump is applied only when a row is inserted. This moves the idempotency boundary into Postgres, so a bump fires exactly once across restart, Redis flush, and replay.
  • event_recovery_tasks (mig 000043) — durable recovery queue (mirrors trade_outbox/position_outbox) with bounded retry, backing outage and cold-restart recovery.

Lossless ingestion + fallback ladder

  • Ingest from livescores/inplay (a full snapshot of every in-play fixture every poll), not livescores/latest. The complete event list is returned each cycle; seen_events + event_bump_log + match_events ON CONFLICT make re-presentation a no-op. This closes the delta-feed loss window. The events.relatedPlayer include is added so assist identity is captured.
  • L1 — client resilience: retry (3×) with exponential backoff + jitter on network/5xx; 429 captured (not retried inline) so the poll level backs off via an accurate RateLimitExhausted; GetAllPages short-circuits while exhausted; dead halfOpenTimeout removed.
  • L2 — reconciliation poller: a 60s lease-gated EventReconciler re-fetches each live fixture's authoritative timeline and replays it through the idempotent ingest path, recovering anything the live ticker missed while the bump still matters.
  • L4 — post-match validation: ProcessCompletedFixture now fetches the event timeline and persists it (source=postmatch) as an authoritative FT backstop. (It does NOT re-apply bumps — at FT the price has moved to the EWMA base price.)
  • L5 — outage recovery: event_recovery_tasks queue, drained with retry.
  • L6 — cold-restart recovery: on boot, enqueue a backfill task for every live fixture.
  • L7 — on-demand full replay: EventReconciler.ReplayFixture + scripts/replay-events <fixtureID> rebuild any fixture's events idempotently.

Operational hardening

  • Lease-gate the live ticker (lease:sportmonks-ticker, TTL 8s < 10s tick) so one replica polls per tick; wrap per-fixture processing in recover() so one bad fixture can't sink the loop.
  • The event read path (match.GetEvents) reads durable Postgres ordered by (minute, extra_minute, id) DESC, with the Redis LIST as a fallback.

The reconciler is gated behind ENABLE_EVENT_RECONCILER=1 until tournament provider-quota headroom is confirmed (at WC peak ~8 live fixtures it adds ~8 provider calls/min).

Alternatives considered

  • Redis Streams as the ingestion log (consumer groups + replay), archived to PG. Rejected: more moving parts than WC throughput (~8 concurrent fixtures) justifies; PG-table-as-truth reuses existing outbox/dedup patterns.
  • Full event-sourcing (append-only ledger + rebuildable projections for price/feed). Rejected for now: largest build and biggest departure from the current architecture; the PG-table + idempotent bump-log delivers the durability/replay guarantees without re-platforming pricing.
  • Keep latest, add a periodic full fixtures/{id} sweep only. Rejected: inplay closes the live loss window directly; the reconciler still adds the sweep for defense in depth.

Consequences

  • Positive: no event lost in the audited failure modes (missed poll, deploy/ restart, GC pause, breaker-open, Redis flush, provider outage, FT gap); every recovery path is idempotent (persist-once, bump-once); events are queryable forever for audit/analytics; assist identity is now captured durably; provider quota is protected (lease + retry + exhaustion gate).
  • Cost: one extra single-row PG INSERT per event per fixture on the hot path (sub-ms, alongside the existing price_ticks INSERT); inplay processes every live player every poll (desired continuous pricing; ~1k price_ticks/min at WC peak).
  • Known follow-up: team-wide propagation bumps (applyTeamEventBump, see ADR-0019) are not yet routed through event_bump_log, so on a Redis-flush replay they could re-apply. Tracked for a follow-up that extends the gate to the team path.

Migration / rollout

All three migrations are additive (CREATE TABLE/INDEX IF NOT EXISTS); down files DROP IF EXISTS (manual only, never CI). New tables start empty → zero deploy risk; no historical backfill (none was stored). Forward-only per the migration-safety rules. The reconciler ships disabled (ENABLE_EVENT_RECONCILER unset) so it can be turned on once on staging and observed before the tournament.