Data Model

PreviousNext

Tables, columns, and per-board stat semantics

Three new tables — board (league) and BoardTeam (a team in that league, which carries the per-board stat and points to a shared store/campaign) — plus a stat-event log, a session field, and a leads table. There is no separate "entry" table: a BoardTeam is the per-board entry.

Schema lives in packages/db/src/schema/merchLeaderboardTables.ts (registered via packages/db/src/schema/index.ts). Access functions live in packages/db/src/access/merch/merch-leaderboard.ts and are exported from packages/db/src/index.ts.

merch_leaderboard (a board = a league)

merch_leaderboard
  id          text pk (nanoid)
  slug        varchar(255) unique not null   (the URL, e.g. 'world-cup', 'nba', 'nba-finals')
  name        varchar(255) not null          (display name, e.g. 'World Cup')
  isDefault   boolean default false          (exactly one true -> root '/' redirects here)
  -- chrome (per board)
  title, subtitle  text
  heroImageUrls    json $type<string[]> default []
  backgroundColor  varchar(7)
  sponsorLogoUrl, sponsorLinkUrl  text (nullable; none yet)
  footerText, footerLink          text
  storeLinkTarget  varchar default 'same'    ('same' | 'new' tab)
  -- ops
  isActive    boolean default true           (kill switch for this board)
  userCap     integer default 30000
  createdAt / updatedAt  timestamptz
indexes: unique(slug), partial unique(isDefault) where isDefault

merch_board_team (a team in a league + per-board stat)

merch_board_team
  id            text pk (nanoid)
  slug          varchar(255) unique not null   (e.g. 'spain-football' = <state>-<league>)
  leaderboardId text -> merch_leaderboard.id  not null   (the board this team belongs to)
  name          varchar(255) not null          (display name, e.g. 'Spain')
  campaignId    text -> merch_campaign.id (nullable)
                  -- the SHARED store/experience; many BoardTeams may reuse one.
                  -- SINGLE SOURCE OF TRUTH for the store target; the handoff URL is
                  -- built at runtime from the campaign's accountSlug + slug.
  flagImgUrl    text        (flag / logo image)
  countryCode   varchar(8)  (nullable; e.g. 'es', 'gb-eng' for flag-CDN fallback)
  mainColor     varchar(7)  (primary color — used for the stat bar)
  colors        json $type<string[]> default []   (full ordered swatch)
  facePaintImageUrls json $type<string[]> default []   (example face-paint photos)
  searchTerms   text        (nullable; synonyms, e.g. "USA, United States")
  seedCount     integer default 0   (per-board popularity baseline so the board isn't all-zero)
  isActive      boolean default true
  createdAt / updatedAt   timestamptz
indexes: unique(slug), (leaderboardId), (campaignId), trigram/tsvector(name, searchTerms)

Key rules:

  • A BoardTeam belongs to one board (leaderboardId). Stats are per BoardTeam, so spain-football and spain-basketball have independent counters.
  • The store is shared, not the BoardTeam: multiple BoardTeams can have the same campaignId. The credit always lands on the BoardTeam, never the store.
  • No maintained counter. Live count = seedCount + COUNT(stat events for the BoardTeam).
  • Displayed percentage is per board: pct = count / SUM(count over the board's active BoardTeams) * 100.
  • Ordering: highest count first within the board (no stored rank).
  • No externalUrl column — it was dropped (migration 0127) so the campaign pointer is the single source of truth.

merch_leaderboard_stat_event (source of truth for live counts)

merch_leaderboard_stat_event
  id, boardTeamId, merchSessionId, userId (nullable),
  eventType varchar default 'face_paint_completed', ipAddress, createdAt
indexes: (boardTeamId)   -- live count = COUNT(*) GROUP BY boardTeamId
unique: (merchSessionId, eventType)   -- idempotency: one completion per session

Counting events is the single source of truth (no counter to keep in sync). At this scale a GROUP BY boardTeamId is trivial; a cached counter can be added later if needed.

merch_session (which BoardTeam a generation credits)

merch_session
  + boardTeamId  text (nullable)   (the BoardTeam credited for this generation/vote)

Set at handoff (the BoardTeam the user picked, which already implies its board). On a successful face-paint generation, the backend writes one stat event for that BoardTeam — a per-board credit.

lead_submission (For Teams / For Brands)

lead_submission
  id, kind ('team'|'brand'), orgName, contactPerson, email, phone, message,
  source ('face.zooly.ai'), createdAt

Access in packages/db/src/access/leadSubmission.ts (listLeadSubmissions powers the admin leads view).

Stat semantics (seeding → real participation)

  • Live count = boardTeam.seedCount + COUNT(stat events for that BoardTeam). No maintained counter to drift.
  • Displayed % = count / SUM(count over the board's active BoardTeams) * 100. Nothing is stored as a "stat value".
  • Seeding sets each BoardTeam's seedCount to a popularity baseline so the board isn't all-zero at launch; real completions add on top.
  • Per board by design: a BoardTeam belongs to one board, so its counter is that board's; a sibling BoardTeam on another board (sharing the same store) is independent.
  • Only board-originated completions count: a session with no boardTeamId (a direct store visit) is not credited.

Migrations

Hand-written and applied locally via db:migrate against .env.local:

  • packages/db/drizzle/0126_merch_leaderboard_tables.sql
  • packages/db/drizzle/0127_drop_board_team_external_url.sql
  • packages/db/drizzle/meta/_journal.json

Follow the _journal.json timestamp rule (max created_at in drizzle.__drizzle_migrations + 1000). Never db push; never run on a remote DB without explicit approval.