DB Benchmark Cases

The DB benchmark (pnpm bench:db) times repository READ functions against db-prod.sqlite3 to find the slowest queries. Every exported READ function in a *Repository.server.ts file should have a case registered — when adding a new read function to a repository, add a matching benchmark case.

Files

File	Purpose
scripts/benchmark-db.ts	Harness: CLI, timing loop, stats, output. Rarely needs changes.
scripts/benchmark-db/cases.ts	Case registry, grouped by repository file. New cases go here.
scripts/benchmark-db/fixtures.ts	Resolves worst-case arguments (heavy rows) from the DB. New argument kinds go here.
scripts/seed-chat.ts	Fills the production copy with chat data, which it ships without (pnpm run bench:db:seed-chat). Run once before the chat cases mean anything.

Adding a case

Cases live in buildCases() in scripts/benchmark-db/cases.ts, grouped under a // <RepositoryName> comment in file order. Three registration helpers:

// Function needs a fixture (skipped automatically if the fixture is null)
add("UserRepository.findLeanById", fx.heavyUser, (user) =>
	UserRepository.findLeanById(user.id),
);

// Function takes no arguments
addStatic("BadgeRepository.all", () => BadgeRepository.all());

// Function needs two fixtures
add(
	"SavedCalendarEventRepository.isSaved",
	both(fx.heavyUser, fx.heavyTournamentId),
	([user, tournamentId]) =>
		SavedCalendarEventRepository.isSaved({ userId: user.id, tournamentId }),
);

Rules:

• READ functions only. Never register inserts, updates, deletes, upserts or transactional helpers — the benchmark runs against real data in db-prod.sqlite3.
• Case name is FileBasename.functionName. For multiple variants of one function, suffix with a label: BuildRepository.abilityPointAverages.all / .byWeapon. The two SkillRepository files are disambiguated as MmrSkillRepository (mmr) — sendouq-match's has no reads.
• Arguments should be worst-case, not minimal: heaviest user, largest tournament, showPrivate/withMembers/include* options enabled, short search query (SEARCH_QUERY). The point is surfacing slow queries.
• Functions calling actorId()/actorIdOrNull() work: the harness runs all cases inside userAsyncLocalStorage with the heavy user as actor.

Adding a fixture

If no existing fixture fits the new function's arguments, add one to scripts/benchmark-db/fixtures.ts:

1. Add a field to the Fixtures interface. Every field is T | null — null means "table empty, skip dependent cases". Bundle related values into one object (e.g. heavyOrg) so a case depends on a single field.
2. Add a resolve<Name>() helper at the bottom of the file and call it from resolveFixtures().
3. Pick the heaviest row, not any row — the standard pattern is a groupBy + count + orderBy count desc + limit 1 query:

async function resolveHeavyBuildUserId() {
	const row = await db
		.selectFrom("Build")
		.select(({ fn }) => ["ownerId", fn.countAll<number>().as("count")])
		.groupBy("ownerId")
		.orderBy("count", "desc")
		.limit(1)
		.executeTakeFirst();

	return row?.ownerId ?? null;
}

4. Never throw on empty tables — always resolve to null (executeTakeFirst() + ?? null).
5. Verify table/column names against app/db/tables.ts.

Verifying

pnpm run typecheck:scripts
pnpm exec biome check --error-on-warnings --write scripts/benchmark-db.ts scripts/benchmark-db
DB_PATH=db-prod.sqlite3 pnpm exec vite-node scripts/benchmark-db.ts -- --filter <caseName> --iterations 3

The filtered run must show the new case with plausible timings — not in the "Skipped" list (fixture resolved null: check the fixture query against real data) and not in the "errored" list (the case threw: check argument shapes). Note pnpm bench:db --filter x also works but runs the full fixture resolution either way.