Ball Knowledge

Version 1 shipped as a static site and so it required no backend at all, however version 2 let users create accounts, so it needed a database. I chose to host Postgres on Supabase because of its generous free tier and ease of use via Agentic AI. However, I made a conscious decision to minimize my dependency on Supabase’s extra features like authentication, email verification, storage, real time for a particular reason: flexibility.

I did not want to be locked into Supabase’ architecture for the future if this application ever scales past the free tier that Supabase provides. Based on online discourse, I have found that Supabase quickly becomes expensive once you have to serve larger traffic and hence I envisioned myself eventually moving this application to a Virtual Private Server (VPS) in the future. In order to make this transition as smooth as possible, I decided to use Better Auth instead of Supabase Auth because Better Auth is open-source and lives within your own Postgres database. I decided to use plain SQL and utilize migration files to drive table changes over letting agents use the Supabase MCP to modify the configuration as I wanted a documented way to recreate the Postgres table in the future.

How the schema works:

• The schema is 12 numbered, forward-only .sql files in db/migrations, from 0001_baseline.sql to 0012_delete_comments.sql, run by pnpm db:migrate. An applied migration is never edited. A fix is always a new file.
• Migrations run with an admin login (MIGRATE_DATABASE_URL) that is never saved to a file. The running app connects as app_user, a role created by db/app-role.sql with nosuperuser nobypassrls, so every security policy applies to it.
• After migrating, kysely-codegen writes the TypeScript types for every table to src/db/types.ts. CI migrates a fresh database, regenerates the types and fails if they differ from what's committed.
• Development and preview deploys share the one Supabase project, because a second project wasn't in the budget. Tests, CI and the end-to-end suite never touch it. Each run spins up its own throwaway Postgres.

db/RUNBOOK.md describes the move: create the app role on the new server, pg_dump, pg_restore, change DATABASE_URL in Vercel (the goal will eventually be to also host hte web server on the VPS as well). To ensure that the runbook never goes stale CI rehearses it on every pull request with pnpm db:check-portability. It migrates a database, dumps it, restores it into a fresh one and compares the two:

// scripts/check-portability.mjs
for (const [label, text] of [
  ["the same tables exist", tables],
  ["the data was restored", "select * from rate_limit order by key"],
  ["the migration history was restored", "select name from kysely_migration order by name"],
  ["row-level security is enabled and forced on every table", security],
  ["the same row-level security policies exist", policies],
]) {
  assertSame(label, await query(urlFor(SOURCE), text), await query(urlFor(TARGET), text));
}

It also fails if any table is missing forced row-level security, checks that app_current_user_id() still works after the move, and checks that running the migrations again changes nothing. If that job is green, the move to a VPS is a dump, a restore and a settings change.

Every application table enables and forces row-level security. Policies never call a Supabase function. They call one function of mine, which reads a setting:

-- db/migrations/0002_current_user_function.sql
create function app_current_user_id() returns text
language sql stable
as $$ select nullif(current_setting('app.user_id', true), '') $$;

Server code runs user-scoped queries only through withUser, which sets that value for one transaction:

// src/db/with-user.ts
export async function withUser<Database, Result>(
  db: Kysely<Database>,
  userId: string | null,
  run: (trx: Transaction<Database>) => Promise<Result>,
): Promise<Result> {
  return db.transaction().execute(async (trx) => {
    await sql`select set_config('app.user_id', ${userId ?? ""}, true)`.execute(trx);
    return run(trx);
  });
}

🔐 The true matters. It makes the setting local to the transaction. The app connects through a transaction pooler, so the next request may reuse the same connection. A session-level setting could leak one person's identity into somebody else's query. A transaction-local one can't. A logged-out visitor passes null, and a policy that compares against null matches nothing.

Visitor (V1, still the default): open the site → both conferences are pre-filled with last season's order → drag or tap the arrows → name the prediction → Export → share sheet on a phone, download on desktop. No account and no network calls.

Visitor who wants to save (V2): the one flow where losing work would hurt.

sequenceDiagram
  actor V as Visitor
  participant B as Browser
  participant S as Next.js server
  participant DB as Postgres
  V->>B: Build a prediction, tap Save
  B->>B: stashDraft() into storage (expires in 1 hour)
  B->>S: Sign up (email or Google)
  S->>DB: Create account, pick a username
  S-->>B: Back to Predict
  B->>B: takeDraft(): restore once, then delete
  V->>B: Confirm Save
  B->>S: POST /api/predictions
  S->>S: Rate limit, validate picks
  S->>DB: insert inside withUser()
  DB->>DB: Triggers check the cap of 5 and write the Stats ledger
  DB-->>S: id, or error BK001 if the cap is reached
  S-->>B: Link to /predictions/id

Signed-in member (V2): Predict → Save (up to 5 per Season, for good) → share the /predictions/<id> link (it gets its own link preview) → Explore the newest public predictions or search for friends → follow (or request to follow a private account) → comment.

I wanted version 1 to be quick and easy to use as there was barely any time. I wanted my friends to be able to predict and then send. There should be no user friction in the prediction phase and making them create an account would have been unnecessary friction.

This changes in version 2, which is about structured, recorded predictions. Accounts arrive, and each saved prediction is added to the Stats ledger. The friction moves to the moment it's worth it: you can still build and export a prediction without an account, and you only sign up when you want to keep one. Your picks survive the trip through sign-up (see the save flow above).

📒 The Stats ledger (ADR 0003) is an anonymous, append-only record written every time a Prediction is saved. It has no account id, no prediction id and no foreign keys. It can never be updated or deleted, and it outlives the account that wrote it. The trade-off: a deleted person's picks stay in the totals. That's fine, because nothing in the ledger identifies them, and it means season-wide stats never shrink.

The database writes the ledger itself, in the same transaction as the save and straight from the stored picks, so the two can never disagree. with ordinality turns each array index into the predicted Position:

-- db/migrations/0006_saved_predictions.sql
insert into stats_ledger (league, season, table_id, entry_id, position)
select new.league, new.season, per_table.key, pick.entry_id, pick.place
from jsonb_each(new.tables) as per_table (key, value)
cross join lateral jsonb_array_elements_text(per_table.value)
  with ordinality as pick (entry_id, place);

As sports lovers, we love the NBA, but we also love to watch all kinda of sports. Soccer, Football, Baseball, F1, etc. If its a sport, someone from our group probably watches it and so I wanted something that can easily scale to various sports.

Leagues have various structures and various zones, promotion and relegation schemes, and it should be able to handle any case without requiring a total refactor every time I want to support a new league. This brought me to LeagueConfig , a way to represent any league’s structure in a structured, type safe way so a new league or sport is just a new config file, not new components and a whole refactor.

It started with words, not code. Before building any UI I wrote GLOSSARY.md so the code would speak "sport" and not "NBA":

💡 Key finding: early on, "Standings" meant both the real results and a person's guess. Settling that (Standings means real results only) is what makes the planned "score your Prediction against the Standings" feature possible to talk about at all.

The schema uses those words directly, and "Seed" is data, not a hard-coded label:

// src/lib/league-config/schema.ts
export const leagueConfigSchema = z
  .object({
    league: z.string().min(1),
    season: z.string().min(1),
    title: z.string().min(1),
    /** What this sport calls a position in a table, e.g. "Seed". */
    positionLabel: z.string().min(1),
    entries: z.array(entrySchema).min(1),
    tables: z.array(tableSchema).min(1),
  })
  .superRefine(checkLeagueConfig);

Zod checks the shape. checkLeagueConfig checks what a shape can't: every Entry sits in exactly one Table, and each Table's Zones cover every Position once, with no gaps and no overlaps. A broken config fails the tests instead of breaking the page.

Configs change, old predictions don't. A team gets renamed or a Zone moves, but a prediction saved last month must still read the way it did. Every Saved Prediction stores a config_version, a 12-character SHA-256 hash of the config it was made on (leagueConfigVersion in src/lib/league-config/version.ts). Replaced configs are archived as data/<league>/history/<season>/<version>.json, and a test checks that every archived file is named by its own hash.

A Ranking Prediction is a read-only map from Table id to an ordered list of Entry ids, and every change goes through small pure functions:

// src/lib/ranking-prediction.ts
export type RankingPrediction = Readonly<Record<string, readonly string[]>>;

export function moveEntry(prediction, tableId, entryId, toIndex): RankingPrediction {
  const order = prediction[tableId];
  const fromIndex = order.indexOf(entryId);
  const isNoOp =
    fromIndex === -1 || toIndex < 0 || toIndex >= order.length || toIndex === fromIndex;
  if (isNoOp) return prediction; // same object, so React skips the re-render

  const reordered = [...order];
  reordered.splice(fromIndex, 1);
  reordered.splice(toIndex, 0, entryId);
  return { ...prediction, [tableId]: reordered };
}

In the same file, rankingPredictionProblem(config, tables) says why a set of tables isn't a complete prediction. It ended up with three callers: the server's check before saving, the check on a draft restored after sign-up, and the tests.

A Saved Prediction is permanent. It can't be edited or deleted on its own, and each account gets 5 per Season, for life. If those rules lived in an API handler, any future endpoint could forget them. So Postgres enforces them with triggers:

-- the cap, safe when two saves arrive at once
perform pg_advisory_xact_lock(
  hashtextextended(new.owner_id || '/' || new.league || '/' || new.season, 0)
);
if (select count(*) from saved_prediction
    where owner_id = new.owner_id and league = new.league and season = new.season) >= 5 then
  raise exception 'saved prediction limit reached' using errcode = 'BK001';
end if;

-- permanent, unless the whole account is being deleted
if pg_trigger_depth() < 2 then
  raise exception 'a saved prediction can only be removed by deleting its account'
    using errcode = 'BK002';
end if;

• The lock: without it, two saves at the same moment could both count 4 and both insert. It's scoped to one account and Season, so nobody else waits.
• The depth check: a direct DELETE runs the trigger at depth 1 and is refused. Deleting an account cascades from auth_user, which runs it one level deeper, and is let through.
• Custom error codes: BK001 to BK003 (the cap, a refused change, the 7-day username cooldown) are listed in one place, DATABASE_ERRORS in src/lib/errors.ts, each next to the migration that raises it. That file also gives every refusal its HTTP status, so a refusal keeps one shape from the database all the way to the browser.

Accounts are public or private, and their predictions follow them. That rule lives in the read policy on saved_prediction: you can read a prediction if it's yours, if its owner is public, or if you're an accepted Follower. Comments then get privacy for free, because their policy just asks whether you can see the prediction, through that table's own policy:

-- db/migrations/0011_comments.sql
create policy comment_read on comment for select using (
  exists (select 1 from saved_prediction where saved_prediction.id = comment.prediction_id)
);

When an account goes private, its comments disappear for everyone who lost access, immediately, with no extra code. One more detail: a link to a private prediction should say "this prediction is private" rather than 404, but the page isn't allowed to read the prediction. A tiny side table, saved_prediction_owner, stores only which account a prediction id belongs to (no title, picks or date), so the page can tell the two cases apart.

Export never screenshots the live editor. It mounts a fixed 1080 × 1350 card off-screen (4:5, which fits social feeds and phone screens), captures it and unmounts it. On a phone it opens the share sheet so the image goes straight to the group chat. Desktop falls back to a download.

// src/lib/export-image.ts
const { toBlob } = await import("html-to-image"); // loaded only when Export is tapped
await document.fonts?.ready; // otherwise the text can render blank
// let React flush any pending state (for example a just-typed name) into the card
await new Promise((resolve) => requestAnimationFrame(() => resolve(null)));

Following someone and posting or deleting a comment update the screen immediately and roll back, with the reason shown, if the server refuses. Settings is the deliberate exception: one Save button sends every field in one request, because a half-saved settings page is worse than a slow one. Every TanStack Query key starts with the signed-in account (queryKey() in src/lib/query/query-keys.ts), so one person's cached data can never show up for the next person on the same device.

The motive behind this project was exploring the practice of Agentic Engineering. I used this project to trial Matt Pocock’s agent skills framework to take a project from an idea to a full-fledged production deployment.

🤖 Agentic Engineering is building software by directing AI coding agents instead of typing every line yourself. The engineer owns the spec, the architecture, the domain language and the review. The agent writes most of the code, inside guardrails the engineer sets: written rules, tests, lint and CI. The skill shifts from writing code to deciding what should exist and proving that what got built is right.

What that looked like in practice:

1. Grill the idea before building it. The V2 plan came out of a long Q&A session that pressure-tested every decision (private accounts, the 5-per-Season cap, what a logged-out visitor can see) and was written up as a spec in GitHub issue #114.
2. Write the domain down. GLOSSARY.md and three ADRs in docs/adr/ gave the agent and me the same vocabulary, and a record of decisions neither of us should reopen.
3. Break it into tickets. The spec became an epic, 10 stories and about 38 tickets in GitHub Issues, linked by blocked-by edges. Every commit is exactly one ticket (ACC 4.1: Store a Saved Prediction with immutability, cap and ledger).
4. Encode the rules where the agent reads them. AGENTS.md holds the non-negotiables: Postgres only, forced row-level security on every table, user-scoped queries only through withUser, and "never test row-level security as a superuser".
5. Let the machines check. Husky hooks, commitlint, Oxlint, branch and PR-title checks, and the full CI suite reject anything that breaks the rules, whoever wrote it.
6. Review everything. Each story got a code review against both the repo's standards and the ticket's spec before I merged it. I merge every PR myself.

💡 What I learned: an agent is only as good as the rules it can read. Rules I only said out loud drifted. Rules written into AGENTS.md, a test or a CI check held. Reviews also caught patterns I then turned into standing rules: copy-pasted hooks, a dozen copies of the same error paragraph, wrappers that only delegated. Being explicit about "always DRY, delete dead code" changed the output more than any single prompt did.

Feature work rebase-merges into development, so its history reads one commit per ticket. Releases go from development into main as a merge commit. Early on I branched ticket 2 off ticket 1's unmerged branch. A rebase merge rewrites commit hashes, so every stacked branch conflicted the moment the one below it merged. The fix was a process change: one branch and one PR per story, cut from development, with one commit per ticket inside it, and the next story starts only after the last one merges. The same reasoning is why the release PR into main must be a merge commit: rebasing it would give main copies of every commit, and the two branches would never line up again.

Every pull request into development or main runs three GitHub Actions jobs:

⚠️ Learning: CI only proves what it actually runs. Locally, a stale .next folder hid missing generated route types, so type checks passed on my machine and failed on a clean checkout. Now pnpm typecheck runs next typegen first, and I verify in a clean clone before trusting a green run.

Testing row-level security for real. Row-level security does nothing for superusers. A suite that connects as postgres passes every policy test and proves nothing. Database tests (*.db.test.ts, 23 files) connect as app_user, created by the same db/app-role.sql that production uses.

Keeping it fast. A global setup migrates one bk_template database once. Each test file clones it (create database … template bk_template), and each test empties it with TRUNCATE. Bonus: TRUNCATE skips row triggers, so the triggers that make Saved Predictions permanent in production don't get in the way of test cleanup. A later pass (ADM 3.1) ran the Playwright suite on 2 workers, ran screen-independent journeys on desktop only, merged duplicate sign-ups and cached the Playwright browser.

25 Playwright specs cover every journey on both a phone and a desktop viewport where layout matters: sign-up, Google login (stubbed), saving, keeping a draft through sign-up, private accounts, follow requests, comments, export and deleting an account.

• Test the real output. The first export test checked the PNG's file size, which proved nothing. Now it emulates a touch device, catches the share sheet and checks real pixels in the image. A second spec covers the desktop download fallback.
• Stress the layout. A long-names fixture league with absurd team names checks that the editor and the export image both still fit.
• One layout spec for every page. layout.spec.ts checks that every page, on both screens, fits the width, keeps its controls inside it and keeps its bottom clear of the nav bar.
• Nothing private in link previews. A public prediction gets its own Open Graph title and description. A private, deleted or missing one gets only the generic site text, and a test against a real Postgres checks that no private data shows up in the tags.

The wins came from counting, not guessing: one cached viewer lookup shared by the layout and the page, reading public profiles with a plain query instead of a withUser transaction, telling the header who's signed in from the server instead of loading the auth client in the browser, and loading the export card, the image library and dialogs only when they're needed. Splitting the original "god" Predictor into small providers also means typing your name no longer re-renders 30 rows of teams, and there's a test that checks exactly that.