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

Submission for the [DEV Weekend Challenge — Passion Edition].
(https://dev.to/challenges/weekend-2026-07-09)
The prompt asked us to build;
Something inspired by passion, and even name-dropped "World Cup companion apps." I couldn't resist. This one is for everyone who has rearranged their weekend around a group-stage fixture.

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:

1. 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)
2. 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

Piece	Status
Companion app (API + UI)	Built, tested (28 backend tests green, clean frontend build)
Betting math + backend oracle/indexer + betting UI	Built & tested
wc_betting Anchor program	Written & specified; not yet deployed to devnet (needs the Solana toolchain)
Analytics event log + extraction feed	Built & tested
DuckDB ELT proof-of-concept	Built & tested; validates the star schema + reconciliation
Snowflake warehouse	Designed (DDL + dbt/ELT flow); DuckDB stands in for the local proof

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

Comments

[Jasper Aavik]

Great article! I really enjoyed seeing how you combined blockchain, analytics, and sports into a practical project. The architecture and implementation details were interesting, especially the integration with Snowflake. Thanks for sharing your work and the insights behind it!

I'd love to connect and learn more about your projects and experiences. If you'd like to stay in touch, feel free to reach out to me on Telegram: @Benjamindev24.