Skip to content

adr: 0032 title: "Persistent instrument idx + lineup-sourced instrument universe" status: accepted implementation-status: implemented date: 2026-06-09 implemented-date: 2026-06-09 implemented-in: - ftl-backend feat/squad-coverage-lineup-sourced deciders: [@amalkrsihna] affects-specs: [amm-pricing, event-ingestion] affects-code: - ftl-backend/migrations/000041_add_persistent_idx.up.sql - ftl-backend/internal/instrument/service.go - ftl-backend/internal/sportmonks/squadsync.go - ftl-backend/internal/sportmonks/ticker.go - ftl-backend/internal/sportmonks/scheduler.go supersedes: null superseded-by: null

ADR-0032: Persistent instrument idx + lineup-sourced instrument universe

Context

Players appearing in a live match generated chart events/stats but were not tradeable — e.g. Trinidad & Tobago's A. Rampersad and D. Smith. Evidence (live Sportmonks):

  • The instrument universe was built from squads/teams/{teamID} (squadsync.go), which for national teams is incomplete — squads/teams for T&T returned 14 players (pagination: None), while the fixture lineups endpoint (the authoritative matchday roster, and the same data the live ticker prices from) had 21.
  • So 7+ lineup players per national-team fixture had no instruments row → their events/stats fired with a playerName but no tradeable instrument.

The obvious fix (source the universe from the lineup, and/or auto-create an instrument when the ticker meets an unknown lineup player) was blocked by a second problem: idx — the 16-bit index used in the WS binary price frame — was not persisted. It was recomputed positionally (ORDER BY id, n+1) independently in instrument.HydrateRedis() (ws side) and instrument.GetAll() (client side); they only agreed because both sorted identically. Adding ANY instrument shifts every later instrument's idx, and the ws-server's idx cache (hydrated at boot, refreshed only on a miss) would keep the stale value → live prices map to the wrong player after any re-hydrate. So no instrument could be added mid-session safely.

Decision

Two changes, in order:

1. Persistent idx column (the prerequisite)

Migration 000041 adds instruments.idx INTEGER NOT NULL UNIQUE, backfilled with the exact legacy positional order (ROW_NUMBER() OVER (ORDER BY id)) so ws/client idx maps stay valid across the cutover. HydrateRedis and GetAll now read the column instead of recomputing positionally. New rows are assigned MAX(idx)+1 in code, only on genuine insert — NOT via a sequence DEFAULT: a default would evaluate nextval on every ON CONFLICT upsert of an existing player (≈ one whole squad per sync), overflowing the uint16 wire index over a 39-day tournament. Existing instruments' idx never change → the ws-server keeps mapping prices correctly, and its live-miss fallback picks up new idx.

2. Lineup-sourced instrument universe

  • Squad sync (collectSquads) now unions each fixture's lineups.player with squads/teams, so the full matchday squad is tradeable once lineups publish (~1h pre-kickoff). Players already in squads/teams keep their richer metadata. (fetchSquad also gained per_page=50 to stop a 26-man squad clipping at the default page.)
  • Ticker auto-register (ensureLineupInstruments, run each poll) creates an instrument for any lineup player with no sportmonks_player:{id} mapping, then re-hydrates once. The ticker already receives entry.Lineups, so this is the authoritative source and self-heals late call-ups in real time. Now safe because idx is persistent.

Alternatives considered

  • Seed the current match's players. Rejected by the product owner — a one-off patch that doesn't fix the next match/tournament.
  • Keep positional idx + refresh the ws cache via pub/sub. Rejected: idx still reshuffles, so the client's idx map (from its last GetAll) also goes stale; both sides would need coordinated refresh. A persistent idx fixes it at the source.
  • Sequence DEFAULT for new idx. Rejected: burns a value per ON CONFLICT upsert → uint16 overflow risk over the tournament. MAX(idx)+1 on genuine insert only.
  • Lineup-sourced sync OR auto-register alone. Both shipped: sync gives pre-match availability; auto-register is the real-time safety net.

Consequences

  • Positive: every player who can appear in a live match is tradeable — pre-match (sync) and in real time (auto-register). idx is stable, so incremental instrument creation no longer corrupts the price→player mapping.
  • Cost: auto-register inserts + one HydrateRedis when a genuinely-new player is first seen (rare after the first poll of a match). The wider livescores include (lineups.player.position;…nationality) adds a little payload per poll.
  • Limitation: auto-registered players seed base_price from computeSeedFormIndex (no history yet); the next squad sync enriches metadata. idx is MAX+1 under a single-writer assumption (scheduler/squad-sync are Redis-leased) with the UNIQUE index as the backstop.

Migration / rollout

000041 is additive (ADD COLUMN, backfill, NOT NULL, UNIQUE INDEX). The backfill matches the legacy positional idx exactly, so deploying the new code against the migrated schema is seamless — existing ws/client idx maps remain valid. No data loss; no destructive operation. Going-forward only.