For the love of the game: a World Cup companion with on-chain betting and Snowflake-ready analytics

What I Built

A FIFA World Cup 2026 companion app - pick a country, see its group standings, qualification status, results, and knockout path - and then two passion-projects layered on top:

• On-chain match-winner betting - a Solana program for parimutuel betting in USDC, with tiered subscriptions. Fans back their team; the pool decides the odds. (→ Best use of Solana)
• A warehouse-ready analytics pipeline - every betting event streams into an append-only log designed to land in Snowflake, and I proved the whole star schema locally with a DuckDB ELT before spending a cent on a warehouse. (→ Best use of Snowflake)

The through-line is fandom: the app is about following your team, and the betting layer turns that loyalty into skin in the game - transparently, on-chain, with no house taking the other side.

Demo

• Repo: https://github.com/Piwe/wc-companion-app
• The companion app runs locally (uvicorn + vite); the Betting page shows live parimutuel odds, pool sizes, and payout previews.

Honesty first (it matters more than a shiny GIF): The Solana program is written, specified, and its off-chain oracle/indexer + UI are built and tested - but it is not yet deployed to devnet (that needs the Solana/Anchor toolchain, which my build box didn't have). The betting UI carries a visible disclaimer and disables real bet/claim actions until deployment. Likewise, Snowflake is a designed target validated by a local DuckDB proof-of-concept. I'd rather show you a truthful architecture than fake a deployment.

Code

Everything is open at https://github.com/Piwe/wc-companion-app:

• backend/ - FastAPI + SQLite (ingestion, progression, betting layer, analytics)
• app/betting.py - parimutuel odds & payout math
• app/analytics.py - append-only event log (the warehouse landing zone)
• elt/ - DuckDB ELT proof-of-concept
• frontend/ - React + Vite SPA (incl. the /betting page)
• anchor/ - wc_betting Anchor program (Rust) - the Solana smart contract
• betting-program-spec.md - full on-chain spec
• analytics-schema.md - star schema + Snowflake DDL

The companion app

A deliberately lightweight core: FastAPI + SQLite (SQLAlchemy 2.0) ingesting from Football-Data.org once a day, and a React + Vite + TypeScript front end with TailwindCSS and React Query.

A nice design principle fell out early - derive, don't recompute: qualification and knockout progression are read straight from the matches feed rather than reimplementing FIFA's "best third-placed teams" tie breaks.

Best use of Solana - parimutuel betting that needs no house

Fixed-odds betting needs a bankroll and an odds oracle. Parimutuel needs neither: everyone who backs a match pays into one pool, and winners split it proportionally. Odds emerge from where the money goes. That's a perfect fit for on-chain - no house, no counterparty, fully transparent.

The wc_betting Anchor program (anchor/programs/wc_betting/) models it with four PDAs - Config, Market (with a USDC vault), Bet, Subscription - and instructions for the full lifecycle: create_market → place_bet → settle_market / void_market → claim, plus subscribe.

Design decisions I'm happy with:

• Only HOME/AWAY are offered; a DRAW voids the market and refunds everyone. You're betting on a team to win, cleanly.
• Fee on profit, per bettor. The house fee is charged only on winnings, at a rate snapshotted onto each Bet. That's what lets subscription tiers change a bettor's fee without breaking the pool math.
• Subscriptions do triple duty: a Standard tier gates betting and unlocks premium companion features; Premium adds a reduced fee.
• The backend is the oracle, never the custodian. It maps Match.winner / FINISHED to a settle/void call; funds only ever move inside the program (a Squads multisig is the settlement authority).

The payout math (spec section 6), which I mirrored exactly in Python so previews match on-chain settlement to the base unit:

# winning_pool includes this bet; fee is charged on profit only
profit = stake * losing_pool // winning_pool
fee = profit * fee_bps // 10_000
payout = stake + profit - fee

Worked example that ships as a test: pools of 800 / 200 USDC, a 100-USDC bet on HOME at the 2% Premium rate → profit 25, fee 0.5, payout 124.5 USDC. Conservation holds regardless of per-bettor fees: the winners' profits sum to exactly the losing pool.

Off-chain, the backend betting.py + routers/betting.py provide live odds, payout previews, and the oracle/indexer endpoints; it's covered by tests including that exact worked example.

Best use of Snowflake - an event log that's born warehouse-ready

Here's the opinion I'll defend: most projects reach for a warehouse far too early. A World Cup side-bet app has, realistically, zero rows on day one. So instead of provisioning Snowflake, I made the app emit the right shape from the start and proved the model locally.

Every betting state change writes one row to an append-only analytics_events table - in the same transaction as the mirror update, so history can never drift from current state. The design is deliberately Snowflake-native:

• Money as integer USDC base units (exact - casts to NUMBER(38,6), never a float)
• A canonical-JSON payload → Snowflake VARIANT
• A monotonic event_id so ELT extracts incrementally with event_id > watermark
• An idempotent dedupe_key so an indexer replay is a no-op
• A schema_version on every row for painless contract evolution

An admin watermark feed is the loader-agnostic seam: GET /api/betting/analytics/events?after_id=<n>&limit=<n>

Then I stood up a DuckDB proof-of-concept (backend/elt/) - DuckDB being the cheap local analogue of Snowflake (columnar, a JSON type standing in for VARIANT, near-identical SQL). It consumes that feed, lands into raw_betting_events, and builds the star schema: dim_market, dim_wallet, fact_bet, fact_settlement, fact_claim, fact_subscription.

The proof is a cross-fact reconciliation: for every settled market, the pool total must equal the sum of stakes.

SELECT
  s.match_id,
  s.total_pool_base,
  SUM(b.stake_base) AS staked_base,
  s.total_pool_base - SUM(b.stake_base) AS diff -- must be 0
FROM fact_settlement s
JOIN fact_bet b ON b.match_id = s.match_id
GROUP BY 1, 2;

I ran it live over HTTP against the running API: a demo market reconciled at diff = 0, with fee revenue computed straight from the claim facts. (It even caught a real bug - a test that had been leaking events into the shared dev DB. Reconciliation earning its keep on day one.)

Porting to Snowflake from here is mechanical, and I wrote the target DDL out in analytics-schema.md: JSON → VARIANT, json_extract_string(payload,'$.x') → payload:x, INSERT OR IGNORE → MERGE on event_id. Landing table, staging view, fact table, and the incremental merge are all there.

-- Snowflake landing table (from analytics-schema.md)
CREATE TABLE RAW.BETTING_EVENTS (
  event_id NUMBER NOT NULL PRIMARY KEY,
  event_type STRING NOT NULL,
  occurred_at TIMESTAMP_NTZ NOT NULL,
  schema_version NUMBER NOT NULL,
  match_id NUMBER,
  wallet STRING,
  payload VARIANT NOT NULL
);

Best use of Solana - the wc_betting Anchor program: parimutuel match-winner betting in USDC with tiered subscriptions, a no-house pool model, per-bettor fee-on-profit, and a never-custodial backend oracle. Full spec in betting-program-spec.md.

Best use of Snowflake - an append-only, VARIANT-shaped analytics event log with a watermark extraction feed, a documented Snowflake star schema and DDL, and a DuckDB ELT that proves the model end-to-end (including reconciliation) before any warehouse spend.

What's real vs. what's next

Next steps are honest and small: anchor build && anchor test && deploy to devnet, swap the Phantom-only wallet hook for the full wallet-adapter, replace the indexer stand-in endpoints with a real on-chain event listener, and (only when volume justifies it) point the ELT at Snowflake.

Why this one is a passion project

I grew up with tournament brackets drawn on the back of school notebooks. This app is that notebook, grown up: the standings and the knockout path are the fandom; the parimutuel pool is the argument you have with your friends about who's actually going to win, made transparent and settled by the match itself.

Building it, I got to indulge two other passions - clean money-math that provably conserves value, and data pipelines that are honest about scale. That's the kind of weekend I'd rearrange around.

Built by Piwe, co-developed with Claude Code. Repo: https://github.com/Piwe/wc-companion-app