Betting App Skill

You are building a full-stack sports betting platform. This skill encodes hard-won patterns from shipping a real cricket betting app (BCL Bet) that ran a complete tournament with 15+ players, 200+ bets, and zero data integrity incidents.

Reference implementation: https://github.com/sklls/BPL_BET

Core principle: Keep financial logic in Postgres RPC functions, not in application code. The database is the only place that can enforce atomicity.

---

1. Database Schema

CREATE TYPE user_role AS ENUM ('user', 'admin');
CREATE TYPE market_status AS ENUM ('open', 'closed', 'settled');
CREATE TYPE bet_status AS ENUM ('pending', 'won', 'lost', 'void');
CREATE TYPE transaction_type AS ENUM ('bet', 'win', 'topup', 'refund');

-- User wallets and roles
CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id),
  display_name TEXT,
  role user_role DEFAULT 'user',
  wallet_balance DECIMAL(12,2) DEFAULT 0 CHECK (wallet_balance >= 0)
);

-- The event being bet on
CREATE TABLE matches (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  team_a TEXT NOT NULL, team_b TEXT NOT NULL,
  match_date TIMESTAMPTZ, venue TEXT,
  status TEXT DEFAULT 'upcoming',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- A betting question ("Who wins?", "Top scorer?")
CREATE TABLE markets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  match_id UUID REFERENCES matches(id) ON DELETE CASCADE,
  title TEXT, market_type TEXT NOT NULL,
  status market_status DEFAULT 'open',
  result TEXT, total_pool DECIMAL(12,2) DEFAULT 0,
  house_edge_pct DECIMAL(5,2) DEFAULT 5.0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- The choices within a market ("Team A", "Team B")
CREATE TABLE bet_options (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  market_id UUID REFERENCES markets(id) ON DELETE CASCADE,
  label TEXT NOT NULL, total_amount_bet DECIMAL(12,2) DEFAULT 0
);

-- Individual user bets
CREATE TABLE bets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES profiles(id),
  market_id UUID REFERENCES markets(id),
  bet_option_id UUID REFERENCES bet_options(id),
  amount DECIMAL(12,2) NOT NULL CHECK (amount > 0),
  odds_at_placement DECIMAL(10,4),
  status bet_status DEFAULT 'pending',
  payout DECIMAL(12,2),
  placed_at TIMESTAMPTZ DEFAULT NOW(), settled_at TIMESTAMPTZ
);

-- Full audit trail of every wallet movement
CREATE TABLE transactions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES profiles(id),
  type transaction_type NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  description TEXT, reference_id UUID,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Leaderboard View

CREATE VIEW leaderboard AS
SELECT p.id, p.display_name, p.wallet_balance,
  COALESCE(SUM(CASE WHEN t.type = 'win' THEN t.amount ELSE 0 END), 0) AS total_winnings,
  COUNT(CASE WHEN b.status = 'won' THEN 1 END) AS bets_won,
  COUNT(b.id) AS total_bets
FROM profiles p
LEFT JOIN bets b ON b.user_id = p.id
LEFT JOIN transactions t ON t.user_id = p.id
GROUP BY p.id, p.display_name, p.wallet_balance
ORDER BY total_winnings DESC;

Row-Level Security

-- Enable RLS on all tables
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE bets ENABLE ROW LEVEL SECURITY;
ALTER TABLE transactions ENABLE ROW LEVEL SECURITY;
ALTER TABLE markets ENABLE ROW LEVEL SECURITY;
ALTER TABLE bet_options ENABLE ROW LEVEL SECURITY;
ALTER TABLE matches ENABLE ROW LEVEL SECURITY;

-- Users see only their own financial data
CREATE POLICY own_bets ON bets FOR ALL USING (auth.uid() = user_id);
CREATE POLICY own_txns ON transactions FOR ALL USING (auth.uid() = user_id);
-- Everyone can read public data (needed for displaying odds)
CREATE POLICY pub_matches  ON matches     FOR SELECT USING (true);
CREATE POLICY pub_markets  ON markets     FOR SELECT USING (true);
CREATE POLICY pub_options  ON bet_options FOR SELECT USING (true);
CREATE POLICY pub_profiles ON profiles    FOR SELECT USING (true);

---

2. Pari-Mutuel Odds Engine

The pool is split among winners; house takes a cut. Nobody sets prices — the crowd does.

Formula: odds = (total_pool / amount_on_option) x (1 - house_edge%)

// lib/odds.ts
export function calculateOdds(
  options: { id: string; total_amount_bet: number }[],
  selectedId: string,
  extraAmount: number = 0,  // include user's preview stake in pool
  houseEdgePct: number = 5
): number {
  const totalPool = options.reduce((s, o) => s + o.total_amount_bet, 0) + extraAmount
  const selected  = options.find(o => o.id === selectedId)
  if (!selected) return 1
  const amountOnSelected = selected.total_amount_bet + extraAmount
  if (amountOnSelected <= 0) return 1
  const raw = totalPool / amountOnSelected
  return Math.max(1.01, raw * (1 - houseEdgePct / 100))
}

export const calcPayout = (stake: number, odds: number) => Math.round(stake * odds * 100) / 100
export const formatOdds = (odds: number) => `${odds.toFixed(2)}x`

Always pass extraAmount for preview — the player's stake is already in the pool. Clamp minimum to 1.01x so a bet is never worthless.

---

3. Atomic Bet Placement RPC

Never do wallet deduction in application code — race conditions will allow overdrafts.

CREATE OR REPLACE FUNCTION place_bet(
  p_user_id UUID, p_market_id UUID, p_bet_option_id UUID,
  p_amount DECIMAL, p_odds DECIMAL
) RETURNS JSON AS $$
DECLARE v_balance DECIMAL; v_bet_id UUID;
BEGIN
  -- FOR UPDATE locks the row — prevents two concurrent bets from overdrawing
  SELECT wallet_balance INTO v_balance FROM profiles
  WHERE id = p_user_id FOR UPDATE;

  IF v_balance < p_amount THEN RAISE EXCEPTION 'Insufficient balance'; END IF;

  UPDATE profiles SET wallet_balance = wallet_balance - p_amount WHERE id = p_user_id;

  INSERT INTO bets (user_id, market_id, bet_option_id, amount, odds_at_placement)
  VALUES (p_user_id, p_market_id, p_bet_option_id, p_amount, p_odds)
  RETURNING id INTO v_bet_id;

  UPDATE bet_options SET total_amount_bet = total_amount_bet + p_amount WHERE id = p_bet_option_id;
  UPDATE markets SET total_pool = total_pool + p_amount WHERE id = p_market_id;

  INSERT INTO transactions (user_id, type, amount, description, reference_id)
  VALUES (p_user_id, 'bet', p_amount, 'Bet placed', v_bet_id);

  RETURN json_build_object('bet_id', v_bet_id);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

In the API route, always recalculate odds server-side — never trust client-submitted odds.

---

4. Market Settlement (with Early Bird Bonus)

CREATE OR REPLACE FUNCTION settle_market(p_market_id UUID, p_winning_option_id UUID)
RETURNS VOID AS $$
DECLARE
  bet RECORD; v_payout DECIMAL; v_winning_label TEXT;
  v_created_at TIMESTAMPTZ; v_cutoff TIMESTAMPTZ; v_is_early BOOLEAN;
BEGIN
  SELECT label INTO v_winning_label FROM bet_options WHERE id = p_winning_option_id;
  SELECT created_at INTO v_created_at FROM markets WHERE id = p_market_id;
  v_cutoff := v_created_at + INTERVAL '30 minutes';

  FOR bet IN
    SELECT b.id, b.user_id, b.amount, b.odds_at_placement, b.placed_at FROM bets b
    WHERE b.market_id = p_market_id AND b.bet_option_id = p_winning_option_id AND b.status = 'pending'
  LOOP
    v_is_early := bet.placed_at < v_cutoff;
    v_payout   := bet.amount * bet.odds_at_placement;
    IF v_is_early THEN v_payout := v_payout * 1.10; END IF;  -- early bird +10%

    UPDATE profiles SET wallet_balance = wallet_balance + v_payout WHERE id = bet.user_id;
    UPDATE bets SET status = 'won', payout = v_payout, settled_at = NOW() WHERE id = bet.id;
    INSERT INTO transactions (user_id, type, amount, description, reference_id) VALUES (
      bet.user_id, 'win', v_payout,
      CASE WHEN v_is_early THEN 'Early bird +10%: ' || v_winning_label
                           ELSE 'Bet won: ' || v_winning_label END,
      bet.id
    );
  END LOOP;

  UPDATE bets SET status = 'lost', payout = 0, settled_at = NOW()
  WHERE market_id = p_market_id AND bet_option_id != p_winning_option_id AND status = 'pending';

  UPDATE markets SET status = 'settled', result = v_winning_label, updated_at = NOW()
  WHERE id = p_market_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

---

5. Two Supabase Clients — Never Mix Them

// lib/supabase-server.ts

// Uses ANON key — respects RLS. For user-scoped data (own bets, own balance).
export function createServerClient() { /* standard Supabase SSR client */ }

// Bypasses RLS entirely. SERVER-SIDE ONLY. Never expose to client.
export function createAdminClient() {
  return createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!,
    { auth: { persistSession: false } }
  )
}

Use admin client for: leaderboard, bettors list, admin dashboard, settlement, wallet top-up. Use server client for: user's own bets/balance, placing bets.

---

6. TypeScript Supabase Join Type Cast

Supabase types FK joins as arrays, but FK joins to unique fields return single objects at runtime. This causes Vercel build failures if not handled.

// Wrong: bet.profiles?.display_name  <- TS error (thinks it's an array)
// Correct:
const name = (bet.profiles as unknown as { display_name: string } | null)?.display_name ?? 'Unknown'

---

7. Prevent Vercel Caching Stale Financial Data

// Add to every server component/page showing live financial data
export const dynamic = 'force-dynamic'

For admin financial stats, use direct table queries — RPC results can be cached by Vercel:

const [topups, settledBets, winTxns] = await Promise.all([
  admin.from('transactions').select('amount').eq('type', 'topup'),
  admin.from('bets').select('amount').in('status', ['won', 'lost']),
  admin.from('transactions').select('amount').eq('type', 'win'),
])
const totalCashIn  = (topups.data  ?? []).reduce((s, r) => s + Number(r.amount), 0)
const totalStaked  = (settledBets.data ?? []).reduce((s, r) => s + Number(r.amount), 0)
const totalPaidOut = (winTxns.data ?? []).reduce((s, r) => s + Number(r.amount), 0)

---

8. Per-Match Leaderboard + Fresh Dropdown

Don't rely on server-rendered props for dynamic lists. Fetch client-side on interaction:

useEffect(() => {
  if (tab !== 'match') return
  fetch('/api/matches').then(r => r.json()).then(setLiveMatches)
}, [tab])

Per-match leaderboard — use admin client, join through markets filtered by match_id:

const { data } = await admin.from('bets')
  .select('user_id, amount, status, payout, profiles(display_name), markets!inner(match_id)')
  .eq('markets.match_id', matchId)
// aggregate per user_id -> staked, winnings, net P&L, sort by net desc

---

9. BetSlip Live Preview + Early Bird Banner

// Live odds update as user types stake
useEffect(() => {
  const num = parseFloat(amount)
  if (!isNaN(num) && num > 0)
    setPreviewOdds(calculateOdds(market.bet_options, selectedOption.id, num, market.house_edge_pct))
  else setPreviewOdds(null)
}, [amount])

// Early bird window check (30 min from market creation)
const isEarlyBird = marketCreatedAt
  ? new Date() < new Date(new Date(marketCreatedAt).getTime() + 30 * 60 * 1000)
  : false

{isEarlyBird && (
  <div className="bg-yellow-500/10 border border-yellow-500/30 rounded-lg px-3 py-2 text-xs text-yellow-400">
    Early bird! Bet now for a +10% bonus on your payout.
  </div>
)}

---

10. Lazy Bettor Loading

Only fetch bettors when a market is expanded — don't load all on page mount:

function toggleExpand(marketId: string) {
  setExpandedMarkets(prev => {
    const next = new Set(prev)
    if (next.has(marketId)) { next.delete(marketId) }
    else {
      next.add(marketId)
      if (!bettors[marketId]) fetchBettors(marketId) // lazy
    }
    return next
  })
}

---

11. Admin Role Check in API Routes

Always read role from the database — JWT claims are cached and won't reflect immediate changes:

const { data: profile } = await supabase.from('profiles').select('role').eq('id', user.id).single()
if (profile?.role !== 'admin') return NextResponse.json({ error: 'Forbidden' }, { status: 403 })

---

12. Common Pitfalls

Pitfall	Fix
No force-dynamic	Vercel serves stale balances/odds — add to every financial page
Role from JWT	JWT is cached — always read role from profiles table per request
Client-side odds only	Players can manipulate — recalculate server-side in bet API
No FOR UPDATE in RPC	Race conditions overdraw wallets
Anon client for leaderboard	RLS blocks it — use admin client server-side
Supabase join types	Vercel build fails — cast with as unknown as ExpectedType
Load all bettors on mount	Slow with many markets — lazy-fetch only when expanded

---

13. Environment Variables

NEXT_PUBLIC_SUPABASE_URL=        # Safe for client
NEXT_PUBLIC_SUPABASE_ANON_KEY=   # Safe for client
SUPABASE_SERVICE_ROLE_KEY=       # SERVER ONLY - never expose
CRON_SECRET=                     # Bearer token for cron job auth
NEXT_PUBLIC_SITE_URL=            # App URL

First admin: UPDATE profiles SET role = 'admin' WHERE id = 'your-uuid';