A developer has built a FIFA World Cup 2026 companion app that combines on-chain parimutuel betting on Solana with a Snowflake-ready analytics pipeline. The open-source project, submitted to the DEV Weekend Challenge — Passion Edition, lets fans pick a country, view group standings and knockout paths, and place match-winner bets in USDC with no house taking the other side. The betting layer uses a Solana Anchor program, while the analytics layer streams every event into an append-only log designed for Snowflake, validated locally with DuckDB.
Companion app core
The app's lightweight core uses FastAPI with SQLite, ingesting match data from Football-Data.org once daily. The front end is built with React, Vite, TypeScript, TailwindCSS, and React Query. A key design principle is "derive, don't recompute": qualification and knockout progression are read directly from the matches feed rather than reimplementing FIFA's tie-break rules for best third-placed teams.
On-chain parimutuel betting on Solana
The betting system uses a parimutuel model, where all bets on a match go into one pool and winners split it proportionally. Odds emerge from where the money flows, eliminating the need for a house bankroll or odds oracle. The Anchor program (written in Rust) manages four PDAs: Config, Market (with a USDC vault), Bet, and Subscription. Instructions cover the full lifecycle: create_market, place_bet, settle_market, void_market, claim, and subscribe.
Only HOME and AWAY outcomes are offered; a DRAW voids the market and refunds all bets. The house fee is charged only on profit, at a rate snapshotted onto each Bet, allowing subscription tiers to change a bettor's fee without breaking pool math. Subscriptions serve triple duty: a Standard tier gates betting and unlocks premium companion features; Premium reduces the fee. The backend acts as an oracle but never custodies funds — only the program moves money, with a Squads multisig as settlement authority.
Payout math is mirrored exactly in Python for preview consistency: profit = stake * losing_pool // winning_pool; fee = profit * fee_bps // 10,000; payout = stake + profit - fee. A worked example with pools of 800/200 USDC and a 100 USDC bet on HOME at 2% Premium rate yields profit 25, fee 0.5, payout 124.5 USDC. Conservation holds regardless of per-bettor fees.
The Solana program is written, specified, and its off-chain oracle/indexer and UI are built and tested, but not yet deployed to devnet due to missing toolchain. The betting UI carries a disclaimer and disables real bet/claim actions until deployment.
Warehouse-ready analytics pipeline
The analytics pipeline avoids premature warehouse provisioning by emitting the correct data shape from the start. Every betting state change writes one row to an append-only analytics_events table in the same transaction as the mirror update, ensuring history never drifts from current state. The design is Snowflake-native: money as integer USDC base units (casts to NUMBER(38,6)), canonical-JSON payloads map to VARIANT, monotonic event_ids enable incremental extraction, idempotent dedupe_keys allow replay without duplication, and a schema_version on every row supports painless contract evolution.
An admin watermark feed provides a loader-agnostic seam via GET /api/betting/analytics/events?after_id=<n>&limit=<n>. A DuckDB proof-of-concept consumes that feed, lands into raw_betting_events, and builds a star schema with dim_market, dim_wallet, fact_bet, fact_settlement, fact_claim, and fact_subscription. A cross-fact reconciliation query verifies that for every settled market, the pool total equals the sum of stakes. The demo market reconciled at diff 0, and the reconciliation caught a real bug — a test that had been leaking events into the shared dev database.
Porting to Snowflake is mechanical: JSON to VARIANT, json_extract_string to payload:x, INSERT OR IGNORE to MERGE on event_id. The target DDL is documented in analytics-schema.md, including the landing table, staging view, fact tables, and incremental merge logic.
What's real vs. what's next
The companion app runs locally with uvicorn and Vite; the betting page shows live parimutuel odds, pool sizes, and payout previews. The Solana program and Snowflake pipeline are fully designed and locally validated, but not deployed to production. The developer emphasizes honesty over a shiny demo, preferring to show a truthful architecture rather than fake a deployment.