Data Model¶

FTL uses two storage layers:

Redis — hot state for sub-millisecond reads and atomic trade execution. Not durable (no AOF/RDB). Populated from Postgres on startup via instrumentSvc.HydrateRedis.
Postgres — durable ledger. Source of truth for all financial records.

Decision: hot state (wallets, live prices, leaderboard) in Redis for sub-ms reads; durable ledger (trades, positions, users) in Postgres for ACID guarantees.

Postgres Tables¶

35 migrations in ftl-backend/migrations/. Key tables:

Core¶

Table	Migration	Purpose
`users`	000001	One row per Google account. `google_id` UNIQUE. `district_id` identifies the Kerala district. `role` is `user` or `admin`. Survives Day 40 teardown — all other tables are dropped.
`wallets`	000002	One row per user. `balance DECIMAL(18,2)`. Default 10,000 (migration 000027).
`instruments`	000003	One row per FIFA player. `sportmonks_id` maps to Sportmonks API. `base_price`, `k` (AMM slope), `net_shares_sold`. `is_active` gates trading. `k_mod` (migration 000033) is the CFD-path slope.
`positions`	000004	Legacy buy-and-hold positions. `PRIMARY KEY (user_id, instrument_id)`. Replaced by `cfd_positions` in ADR-0004 but retained during rollout.

Trade Records¶

Table	Migration	Purpose
`trades`	000005	Range-partitioned by `created_at` (daily partitions, June 1 – July 31 2026, plus a `trades_default` catch-all). `client_request_id` carries the idempotency key.
`cfd_positions`	000031	CFD-style leveraged positions. `direction` is `long` or `short`. `lot_size` maps to 5 shares of exposure at 1:10 leverage. `closed_by` is `user`, `stop_loss`, `take_profit`, `washout`, or `auto_exit_ft`. Partial indexes for open-position queries.
`trade_outbox`	000026	Durable retry queue for `trades` INSERTs that failed the synchronous Postgres write after succeeding in Redis. Drained by `flusher.NewOutboxDrainer` every 5 seconds.
`position_outbox`	000035	Same pattern for `cfd_positions` INSERTs and close-UPDATEs. Drained by `flusher.NewPositionOutboxDrainer`.

Market Data¶

Table	Migration	Purpose
`price_ticks`	000006	Range-partitioned by `tick_time`. Index: `(instrument_id, tick_time DESC)`. Used for chart history queries and offline-window margin replay. The flusher writes `source='synthetic'` rows for noisy-channel ticks every `NoiseSampleEveryN` flush ticks.
`matches`	000008	FIFA fixture metadata.
`match_scores`	000010	Running scores per fixture.
`processed_fixtures`	000025	Idempotency record for post-match processing (FT liquidation).

Leaderboard¶

Table	Migration	Purpose
`leaderboard_snapshots`	000007	JSONB snapshot of top-100 rankings, written every 5 minutes via `pg_cron`. Used for Redis recovery after a crash and historical records.

Users and Engagement¶

Table	Migration	Purpose
`notifications`	000014	In-app notification feed. Written by `notify.Service`.
`achievements`	000017	User badge records.
`referral_invites`	000029	Invite link metadata per user.
`referrals`	000029	Completed referral records.
`margin_events`	000032	Audit log for washout, SL/TP, and FT close events.

Admin¶

Table	Migration	Purpose
`admin_settings`	000021	Runtime tunables (e.g. `score_event_points`, `sell_cooldown_seconds`). Loaded at startup and applied via `settingsSvc.SetOnUpdate` callbacks.
`feature_flags`	000020	Boolean flags checked by `featureflag.Service`.

Redis Keyspace¶

Key pattern	Type	Purpose
`wallet:{userId}`	Hash	`balance` field. Written by Lua; read by Lua; flushed to `wallets` table.
`instrument:{id}`	Hash	`base_price`, `k`, `net_shares_sold` (legacy path), `live_base_price` (Sportmonks live price), `k_mod`, `net_position_imbalance` (CFD path), `is_active`, `frozen`, `last_noisy_price`, `last_published_price` (the most-recently WS-broadcast price; primary slippage reference per ADR-0020), `live_match_until` (epoch seconds; set by ticker to gate live-match noise publishing per ADR-0021).
`position:{userId}:{instrumentId}`	Hash	Legacy buy/hold position: `shares`, `avg_price`.
`position:{positionId}`	Hash	CFD position: `direction`, `lot_size`, `open_price`, `opened_at_ms`, `stop_loss`, `take_profit`, `closed_at_ms`, `close_price`, `realized_pnl`, `closed_by`.
`positions:{userId}`	Sorted Set	Set of open CFD positionIds (score = opened_at ms). Iterated by `position_open.lua` and `position_close.lua` to compute equity.
`leaderboard:overall`	Sorted Set	Score = total unrealized PnL. Updated by `trade_execute.lua` on every trade.
`leaderboard:roi:overall`	Sorted Set	Score = accumulated ROI points (per-exit). Updated by `trade_execute.lua` on every SELL. Primary ranking for the tournament leaderboard.
`leaderboard:district:{id}`	Sorted Set	District-scoped unrealized PnL rankings.
`leaderboard:roi:district:{id}`	Sorted Set	District-scoped ROI rankings.
`user_pnl_by_inst:{userId}`	Hash	Per-instrument unrealized PnL contribution. Used by `trade_execute.lua` to compute leaderboard delta without full position scan.
`user:{userId}:trade_stats`	Hash	`invested`, `realized_pnl`, `wins`, `sells` — tiebreaker data.
`dirty:instruments`	Set	Instrument IDs waiting to be flushed to Postgres.
`dirty:wallets`	Set	User IDs waiting to be flushed.
`dirty:positions`	Set	Position keys (`userId:instrumentId` for legacy, `positionId` for CFD) waiting to be flushed.
`idem:{userId}:{clientRequestId}`	String	`SET NX EX 86400`. Idempotency lock; claimed by first executor.
`idem_result:{userId}:{clientRequestId}`	String	Cached JSON response for idempotency replay. 24h TTL.
`last_seen:{userId}`	String	Epoch ms of last disconnect. Written by ws-server disconnect hook. 24h TTL.
`last_open:{userId}:{instrumentId}`	String	PositionId of most-recently opened position on this instrument. TTL = 180s (minimum hold).
`instrument_idx`	Hash	Maps instrument UUID → uint16 numeric index (and reverse). Used by ws-server to resolve subscription commands.
`lease:margin-backstop`	String	Redis lease for the 5-minute backstop sweeper. `SetNX 30s`. Only one api-server replica runs the sweep.

The Four Lua Scripts¶

trade_execute.lua¶

File: ftl-backend/internal/redis/lua/trade_execute.lua

Legacy BUY/SELL path. 13 KEYS, 7 ARGV. Handles: - AMM price calculation using arithmetic series: each share i costs base_price + k × (net_shares + i) - Slippage check against limit_price - Balance check on BUY, share check on SELL - VWAP average price tracking in position:{userId}:{instrumentId} - Leaderboard update via user_pnl_by_inst:{userId} delta - ROI accumulation on SELL into leaderboard:roi:overall - Dirty set marking, price publish, portfolio publish - Idempotency guard (SET NX + result cache)

position_open.lua¶

File: ftl-backend/internal/redis/lua/position_open.lua

CFD open path (ADR-0004). 8 KEYS, 11 ARGV. Handles: - Slippage check against last_published_price (the price actually broadcast over the WebSocket), falling back to last_noisy_price, then server_price, when those fields are unset (ADR-0020). Both fields are pre-fetched in the same HMGET call as the rest of the instrument state — no extra round-trip. - Idempotency claim: SET idem:{userId}:{clientRequestId} NX EX 86400. Rejections that occur after the claim but before the first durable mutation (slippage / PRICE_MOVED, invalid SL/TP, insufficient margin, missing wallet) call a reject() helper that DELs the freshly-claimed idem key before returning, so a retry with the same clientRequestId re-evaluates cleanly rather than receiving a stale {status:"duplicate"} marker (ADR-0020). Instrument-state checks (inactive, frozen, non-positive base) run before the claim, so they never consume the key. The idem key persists ONLY for a successful open. - Equity computation by iterating positions:{userId} sorted set - Free-margin check: equity - used_margin >= margin_required - Writing position:{positionId} hash with optional stop_loss / take_profit - Minimum-hold lock: SET last_open:{userId}:{instrumentId} <positionId> EX 180 - Price and portfolio publish

position_close.lua¶

File: ftl-backend/internal/redis/lua/position_close.lua

CFD close path. Called by user-initiated closes, stop-loss/take-profit worker, margin washout replayer, and FT auto-exit. 11 KEYS (KEYS[10]=leaderboard:roi:overall, KEYS[11]=leaderboard:roi:district:<code> for the ROI feed), 8 ARGV. Handles: - 180s minimum-hold check for close_reason == 'user' only (system closes bypass) - Override close price for washout-at-breach and FT snapshot scenarios - Slippage check for user closes with clientPrice supplied - Freeze check for user closes (system closes bypass the freeze gate) - PnL realization: (closePrice - openPrice) × lotSize × 5 × sign - Balance credit, position stamp (closed_at_ms, close_price, realized_pnl, closed_by) - ZREM positions:{userId} <positionId> - Imbalance update on instrument - Dirty set marking, price publish, portfolio publish

The post-close equity-snapshot loop (recomputing unrealized PnL across remaining positions for the portfolio publish) is best-effort: a missing instrument price for a sibling position is skipped rather than aborting an already-committed close (ADR-0020). The wallet credit, position stamp, and imbalance update are all durable before the loop runs — a sibling eviction can only affect the portfolio snapshot payload, not the close itself. The next portfolio fetch corrects the numbers.

ping.lua¶

Health check script. No-op read used to verify Redis connectivity and script pre-loading.

Error codes (position service)¶

Error	HTTP status	Meaning
`PRICE_MOVED`	409	`clientPrice` deviates more than 2.5% from `last_published_price`. Retry with a fresh `clientRequestId` after re-quoting.
`ErrDuplicateInFlight`	409	Replayed or in-flight request with no usable cached result (Lua replayed `{status:"duplicate", openPrice:0}`). Retry with a fresh `clientRequestId`.
`ErrCorruptPositionState`	409	Fresh zero-price open or corrupt close state. Previously surfaced as 500.
`ErrInvalidSLTP`	422	Stop-loss or take-profit level invalid for the current price and direction.

Both ErrDuplicateInFlight and ErrCorruptPositionState were previously unmapped and fell through to HTTP 500 (ADR-0020).

Flusher Pipeline Detail¶

File: ftl-backend/internal/flusher/flusher.go

The flusher runs at FlushInterval (100ms). Each tick:

snapshotDirtySet("dirty:instruments") — RENAME dirty:instruments dirty:instruments:flushing:<ts>. Returns the snapshot key.
SMEMBERS dirty:instruments:flushing:<ts> — get all instrument IDs.
Begin Postgres transaction. For each instrument ID: HGETALL instrument:{id} → UPDATE instruments SET net_shares_sold = ... WHERE id = ...
Commit. On failure, SADD dirty:instruments <all members> to retry the full batch next tick.
On per-row failure, SADD dirty:instruments <failed-id> to retry only failures.
DEL dirty:instruments:flushing:<ts>.

Wallets use individual UPDATE wallets SET balance = ... WHERE user_id = ... calls (no transaction — each is a single row).

Positions use INSERT ... ON CONFLICT DO UPDATE for upsert semantics.

On startup, RecoverOrphanSnapshots finds any dirty:*:flushing:* keys left by a crash and SUNIONSTOREs them back to the live dirty set before the first flush tick. This prevents silent data loss if the flusher crashed between the RENAME and the Postgres writes.

CFD position writes are handled by the position_outbox drainer, not the main flush loop. The main flush loop's dirty:positions members follow the userId:instrumentId composite format for the legacy positions table.

Known issues (Sportmonks upstream)¶

These are upstream data gaps in the Sportmonks feed, not bugs in the FTL backend or data model:

Goal and card event feed empty for some fixtures. Sportmonks does not supply goals and cards for every fixture on the current plan. The events:{sportmonksId} Redis list may be populated but the events array contains no goals or cards. No code fix applies.
Per-player long-ball, aerial, and tackle stats absent for U18 competitions. The Sportmonks plan does not include these stat types for under-18 fixtures. aggregateTeamStats will omit these keys for U18 matches. Expected behaviour.
Kazakhstan U18 squad returns 0 players. The squad is absent from the Sportmonks plan. HydrateRedis will find 0 instruments for this team. Not a deployment bug.