Backend Architecture

-- Users table: maps X identity to wallet
users (
  id               UUID PRIMARY KEY,
  privy_id         TEXT UNIQUE NOT NULL,
  x_handle         TEXT NOT NULL,
  x_id             TEXT UNIQUE NOT NULL,        -- immutable X numeric ID
  avatar_url       TEXT,                        -- X profile picture, updated on each login
  wallet_address   TEXT NOT NULL,               -- embedded wallet on Base
  privy_wallet_id  TEXT,                        -- Privy embedded wallet ID (used for server-side gasless transactions)
  factory_approved BOOLEAN DEFAULT FALSE,       -- true once user has infinite USDC approval to factory
  pre_registered   BOOLEAN DEFAULT FALSE,       -- true if wallet was pre-generated before user login
  created_at       TIMESTAMP DEFAULT NOW(),
  updated_at       TIMESTAMP DEFAULT NOW()
)

-- Bets table: off-chain metadata + on-chain reference
bets (
  id                UUID PRIMARY KEY,
  contract_address  TEXT UNIQUE,               -- on-chain bet contract address
  challenger_id     UUID REFERENCES users(id),
  opponent_id       UUID REFERENCES users(id), -- NULL if open bet
  title             TEXT NOT NULL,
  description       TEXT,
  category          TEXT DEFAULT 'Other',
  amount            NUMERIC NOT NULL,          -- USDC amount per side
  status            TEXT NOT NULL,             -- pending, active, resolving, resolved, cancelled, expired
  outcome           TEXT,                      -- challenger_wins, opponent_wins, draw, NULL
  expiration_date   TIMESTAMP NOT NULL,
  acceptance_deadline TIMESTAMP,
  draft_challenge_id UUID,                     -- link to draft if created from X bot
  tweet_url         TEXT,                      -- original tweet context
  created_at        TIMESTAMP DEFAULT NOW(),
  resolved_at       TIMESTAMP,
  claimed_at        TIMESTAMP
)

-- Disputes table
disputes (
  id              UUID PRIMARY KEY,
  bet_id          UUID REFERENCES bets(id),
  filed_by        UUID REFERENCES users(id),
  reason          TEXT NOT NULL,
  evidence_url    TEXT,
  status          TEXT NOT NULL,               -- open, reviewing, resolved
  resolution_note TEXT,
  created_at      TIMESTAMP DEFAULT NOW(),
  resolved_at     TIMESTAMP
)

-- Activity log for leaderboard and profiles
activity_log (
  id          UUID PRIMARY KEY,
  user_id     UUID REFERENCES users(id),
  bet_id      UUID REFERENCES bets(id),
  action      TEXT NOT NULL,                   -- created, accepted, won, lost, claimed, cancelled
  amount      NUMERIC,
  created_at  TIMESTAMP DEFAULT NOW()
)

CREATE INDEX idx_bets_status ON bets(status);
CREATE INDEX idx_bets_challenger ON bets(challenger_id);
CREATE INDEX idx_bets_opponent ON bets(opponent_id);
CREATE INDEX idx_bets_expiration ON bets(expiration_date);
CREATE INDEX idx_activity_user ON activity_log(user_id);
CREATE INDEX idx_users_x_handle ON users(x_handle);
CREATE INDEX idx_users_x_id ON users(x_id);