Pickled Citizens
Lightweight web app for running casual pickleball leagues: create leagues, add
players, schedule game sessions, auto-generate balanced doubles matchups, and track
match results with lifetime statistics.
Live site: pickledcitizens.com
Last updated: 2026-10-30
What it does
- Leagues — create leagues, add existing players by email, assign per-league admin roles.
Sole-admin protection prevents a league from being orphaned; the owner can only leave by deleting the league.
- Game sessions — schedule sessions for 6, 8, 10, or 12 players. The app sorts
players by DUPR (the official dupr.com rating when synced, otherwise self-reported)
and generates balanced doubles teams with a snaking algorithm. Guest (non-member)
players can be added to a single session.
- League rankings — a per-league board derived from every scored game: win
percentage once a player has 15 games, plus "rivals", the counterpart across the
net in every game of a session, with the head-to-head record.
- Official DUPR — a player links their dupr.com page; a weekly browser sync
reads the official doubles rating into the profile without overwriting the
self-reported one. See
docs/dupr-sync.md.
- Match tracking — record per-match scores, view team and per-player win/loss
records and lifetime statistics. A session's creator or any admin of its league
can mark wins, clear them, or delete the session; a delete is permanent, so the
audit log keeps a snapshot of the players and scores it removed.
- Admin tools — a single super-admin sees
/admin/events (audit log),
/admin/users (create a user with name, DUPR, dupr.com link and league
picks, edit or delete one), and /admin/leagues.
- Social sharing — Open Graph and Twitter card metadata per session, with a static social image.
Tech stack
| Layer | Technology |
|---|
| Framework | Next.js 14 (App Router, React Server Components), React 18, TypeScript 5.6 |
| Styling | Tailwind CSS 3.4 — editorial B&W design tokens, monospace labels |
| Database | Cloudflare D1 (SQLite) accessed via Drizzle ORM |
| Auth | Clerk (password + magic link), Clerk-hosted UI |
| Hosting | Cloudflare (SSR) via OpenNext for Cloudflare (@opennextjs/cloudflare) |
| CLI / migrations | Wrangler 4 |
| Tests | Vitest |
Note: This project was migrated off Supabase (Postgres + Auth + RLS) to
Cloudflare D1 + Clerk. The live schema is the Drizzle definition in
src/lib/db/schema.ts with migrations under drizzle/.
Project structure
app/ Next.js App Router pages + API routes
api/
admin/users/ POST/PATCH/DELETE — admin user mgmt (Clerk + D1)
clerkwebhook/ Clerk user lifecycle webhook (svix-verified)
admin/ Super-admin pages (events, users, leagues)
auth/ Clerk SignIn/SignUp + /auth/complete profile finisher
leagues/, sessions/ League + session pages
sitemap.ts Dynamic sitemap (/sitemap.xml)
src/
components/ui/ Reusable UI library (Button, Input, Modal, SectionLabel)
components/sessions/ CreateSessionForm, SessionsList, GuestModal
lib/
db/schema.ts Drizzle schema — source of truth for D1
db/client.ts getDbAsync() — Drizzle client over the Worker DB binding
db/queries/ Per-domain queries with explicit auth checks
actions/ 'use server' actions called from client components
teamGeneration.ts Snaking team-balance algorithm (unit-tested)
standings.ts League board: win % + rivals, derived from results
dupr.ts DUPR formatting, range and profile-link validation
clerk.ts Clerk Backend API client + name length bound
middleware.ts Clerk middleware — CSP nonce + /admin gating
drizzle/ Drizzle-generated D1 migrations
__tests__/ Vitest tests (team generation, authorization, standings, DUPR, actions, admin users, webhook)
docs/dupr-sync.md How the weekly official-DUPR sync runs
Path alias: @/* maps to ./src/*.
Getting started
Prerequisites
- Node.js 20+
- A Cloudflare account with a D1 database
- A Clerk application
Install & run
npm install
npm run dev # Next.js dev server — NOTE: the D1 binding is NOT available here
npm run preview # OpenNext build + Cloudflare local preview (D1 + Clerk env required)
npm run lint
npm test
For any database work use npm run preview (bindings available) or the Wrangler
local D1 emulator.
Environment variables
Public (committed in wrangler.toml [vars]):
NEXT_PUBLIC_SITE_URL — public site URL (default https://pickledcitizens.com)NEXT_PUBLIC_CLERK_PUBLISHABLE_KEY — Clerk client key (safe to commit)NEXT_PUBLIC_CLERK_SIGN_IN_URL / _SIGN_UP_URL / fallback redirect URLs
Server-only (set as Cloudflare secrets — never commit):
CLERK_SECRET_KEY — Clerk Backend API keyCLERK_WEBHOOK_SIGNING_SECRET — verifies the Clerk user lifecycle webhookDISPLAY_TIMEZONE — IANA timezone for session metadata (default America/Los_Angeles)
Copy .env.example to .env.local and fill in the values above to run locally.
Local .env.local files are git-ignored.
Database
D1 is SQLite. The schema lives in src/lib/db/schema.ts (Drizzle). To change it:
# 1. Edit src/lib/db/schema.ts
npx drizzle-kit generate --name <description>
# 2. Apply
wrangler d1 migrations apply pickled-citizens --local # local
wrangler d1 migrations apply pickled-citizens --remote # production
D1 has no row-level security — all authorization is enforced in TypeScript,
but not uniformly at the query layer:
- Mutations in
src/lib/db/queries/ take the calling user's ID explicitly
and check ownership/membership themselves (canManageSession,
isLeagueOwner).
- Reads —
getSessionById, listLeagues, listMembersOfLeague,
listAllProfiles, listAdminEvents and others — take no caller ID and
perform no check. Their caller is responsible for the gate.
So when adding a read path, the authorization check belongs in the server action
or page, not in the query module. Server actions are POST-reachable RPC
endpoints: "the page only renders for members" is not a defense.
Tables: profiles, leagues, league_members, game_sessions, matches, match_players, match_results, session_guests,
admin_events.
Deployment
The app is built with OpenNext for Cloudflare and deployed via Cloudflare's
Pages GitHub integration on push to main. wrangler.toml declares
pages_build_output_dir = ".open-next/pages" and the D1 binding.
The CI workflow (.github/workflows/cloudflare-deploy.yml) runs typecheck +
lint + tests as a gate; it does not itself deploy.
npm run pages:build # OpenNext build assembled for Cloudflare Pages output
Super-admin
A single super-admin email ([email protected]) is defined in src/lib/constants.ts
as ADMIN_EMAIL and enforced in middleware.ts, src/lib/db/auth-helpers.ts,
and src/components/AdminFooterLinks.tsx. Changing it is a one-line edit — no DB
migration needed.
License
Proprietary — personal/league use. Reach out via GitHub if you would like to use
this codebase for your own league.
