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. |
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_noisy_price (fills at clientPrice if within tolerance)
- 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. 9 KEYS, 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
ping.lua¶
Health check script. No-op read used to verify Redis connectivity and script pre-loading.
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.