Case study · 2025 – 2026
Rebuilding an applicant tracking system, data model first
A recruiting platform for trucking companies ran on WordPress. Filtering a list of leads took about fifteen seconds. I rebuilt it as a multi-tenant GraphQL platform, measured both systems side by side in production, and I run the new one, including deploys, backups, alerts and incidents.
- Role
- Sole engineer, covering design, build, deploy and operations
- Stack
- TypeScript, Bun, Next.js, Pothos GraphQL, Prisma, MariaDB, better-auth, SendGrid, Twilio, Docker, Traefik
- Scale of the work
- About 4,000 tests in 384 spec files, 32 decision records, since July 2026
The system I inherited
Recruiters at trucking companies use the platform to work their leads, the drivers who applied through a job board, a Meta lead form or a recruiter’s own entry. One WordPress install served all of it: the recruiter app’s REST endpoints, the public job board, logins and every application.
- Key-value tables, no relations, no indexes. A lead was a bag of meta rows. Filtering by position or status had nothing to stand on, and requests took 3 to 14 seconds.
- The tenant travelled in the URL. Which company’s data you saw depended on a parameter the client sent, which is exactly what a multi-tenant system must never trust.
- The audit log was written by the client. It recorded only what the interface remembered to log.
I didn’t profile WordPress internals, so I won’t claim exactly where its seconds went. What I can show is what the replacement does instead, and how much faster it is.
The options
Rejected
Optimise the WordPress endpoints
The data lived in key-value rows with no relations and no indexes to use, so there was nothing for a faster query to stand on. The same WordPress install also served the job board, logins and every application, so any change put all of them at risk.
Chosen
Rebuild, data model first
Design the relational schema, tenancy and audit rules first, then the API, then the screens, and run both systems side by side until the new one is measurably better. It costs more up front and is the only option that fixes the cause.
You can’t fix a data problem from the frontend. Rebuilding meant running two systems for a while, which is also what made an honest side-by-side measurement possible.
The shape of the rebuild
Boring, typed, one language end to end. Two Next.js apps, the recruiter app and the public job board, talk to one GraphQL API and one database. The browser only ever talks to one origin:
- Browserfirst-party session cookie, no CORS
- Next.js on Vercelrewrites the API call server-side
- TraefikTLS; the API is the only application it exposes
- GraphQL APIsession → company → scoped query
- MariaDBno public port; reachable only from the API
The schema is code-first with Pothos and both apps generate their types from it, so removing a field the frontend still uses fails the build by name, not production. Every rule that matters is enforced by a test or the type checker: one file per resolver, one shared validation object per domain concept, and components whose props simply don’t accept ad-hoc styling.
How decisions got made
Anything with a trade-off gets a decision record: what was chosen, the options rejected and why, and the condition that would reopen it. Some decisions are deferred rather than rejected, with the trigger written down, so the day it fires nobody re-derives the argument. When a decision becomes a rule, a test or the type checker enforces it: tenant scoping, one validation rule per concept, one file per resolver. There are 32 records so far.
The structure was chosen so that growth is a configuration change, not a rewrite. Apps are dependency sinks and a feature folder is the seam for splitting into separate repositories later. Every list that can grow is a cursor-paginated connection from day one. Indexes lead with the tenant. The API is stateless behind one origin, so a second instance is a compose change. The server itself is small on purpose, and the gaps section below says what a larger footprint would need.
Decisions along the way
When does a list become paginated, and who owns the page size?
Options plain lists, paginate later · cursor connections from day one · library defaults for page size
Anything a user can add to is a cursor connection from the first commit, with a page size pinned per field and checked by a test.
Retrofitting pagination later is a schema migration; declaring it early costs nothing. Library defaults move silently on upgrade, so the number lives in our code.
How do you charge a rate limit stored in the database without races?
Options read, compare and write with a retry · SELECT FOR UPDATE · one conditional decrement
A single UPDATE that subtracts only if enough budget remains; the affected-row count is the verdict.
The retry version admitted 4 of 30 concurrent calls while 98 percent of the budget sat unspent. A limiter that says "too many requests" with the budget full is reporting its own write contention.
Concurrent first-of-day reservations deadlocked. Retry, or remove the cause?
Options catch the deadlock and retry · an upsert · SELECT FOR UPDATE · make sure the row exists before anyone reserves
Insert a zero row first and ignore the duplicate-key error, then one conditional update.
That turns a gap lock into a record lock. 253 of 300 test rounds had thrown before; after, a denial can only mean the cap is genuinely reached, never that this call lost a race.
Pagination cursors decoded to ids that embed the creation time. What changes?
Options time-ordered UUIDs · re-key every row now · sign or encrypt the cursor · a new id generator for new rows only
A non-time-ordered id generator, as a code-only change with no migration.
Time-ordered UUIDs would reintroduce the very leak being fixed. Fixing the id at its source removes the leak everywhere the id travels, not just in the cursor.
Tenancy and integrity, by construction
The tenant comes from the session, never the request. Every write carries the company in its where, the schema is closed by default, and an isolation suite runs against a real MariaDB with two companies: every root field tries to touch the other’s rows, or carries an explicit exemption. The audit trail moved from the client into database triggers that record the old value, the new value and who changed it, deletes included.
// DEFAULT-DENY: closes every root field, including future ones, unless it
// opts out; the inverse of Pothos's fail-open default. Opting out needs BOTH
// skipTypeScopes and the field's own authScopes, so `public` alone is denied.
builder.queryType({ authScopes: { active: true } })
builder.mutationType({ authScopes: { active: true } })A new field someone forgets to protect is closed, not open. Nine fields are public, each on an allowlist a test pins.
// What a lead read may SEE. companyId and deletedAt are written AFTER the
// filter spread so a client value can never win.
export function leadScope(
filter: Prisma.LeadWhereInput | null | undefined,
companyId: string,
): Prisma.LeadWhereInput {
return { ...(filter ?? {}), companyId, ...VISIBLE_LEAD }
}companyId comes from the session, never from the request. The legacy system carried it in the URL.
// The tenant predicate ON THE WRITE, never a bare `where: { id }`,
// Lead ids are sequential Ints and therefore guessable.
const { count } = await tx.lead.updateMany({
// UNSOURCED: only leads entered by hand may be deleted.
where: { ...leadById({ id: args.id, companyId }), OR: UNSOURCED },
// updatedById is what the audit trigger reads; without it the action
// most worth attributing is logged against nobody.
data: { deletedAt: new Date(), updatedById: userId },
})
// Same answer for "no such lead", "another tenant's" and "already deleted":
// telling them apart would turn a guessable Int into a cross-tenant probe.
if (count === 0) throw new NotFoundError('Lead')Checking first and writing second leaves a gap. The rule lives in the write, and a test counts every resolver that uses the helper.
Measured, not claimed
The speed comes from less work per query: composite indexes that lead with the tenant, one typed request per view selecting only the fields it shows, and cursor pagination instead of loading everything.
Filter by position24× faster
Filter by status15× faster
Open the leads grid3× faster
Logarithmic scale, 50 ms to 20 s.
Filter by position154× faster
Filter by status39× faster
Open the leads grid28× faster
Logarithmic scale, 50 ms to 20 s.
Method. Playwright, headless Chromium, on both live production systems on 26 September 2026, with the same machine and network, a fresh browser session per run, medians of 5 to 15 runs, tenants of 758 and 680 leads. Client caches were excluded so both systems had to answer from the server.
What didn’t improve: first paint of the public job board is about equal on both systems; the gain there is in full load, roughly 2.6 to 3 times faster.
The same numbers as a table
| Action | Layer | Legacy | Rebuild |
|---|---|---|---|
| Filter by position | On screen | 15.2 s | 630 ms |
| Filter by status | On screen | 3.47 s | 220 ms |
| Open the leads grid | On screen | 7.25 s | 2.36 s |
| Filter by position | On the server | 14.4 s | 93 ms |
| Filter by status | On the server | 3.34 s | 85 ms |
| Open the leads grid | On the server | 3.66 s | 128 ms |
Shipping it and keeping it up
Every push to main ships to production, migrations included, so the deploy has to be safe by itself. One script runs it in phases under a lock, and the same script is the break-glass path over SSH, because a path used only in emergencies gets its first real test on the worst night of the year.
- stage
- build
- pull
- backup
- migrate
- roll
- smoke
- stamp
- prune
A backup is taken before every migration; only a release that passes the smoke test is recorded as deployed; rollback is a tag. Secrets are committed encrypted with SOPS, so a configuration change is a reviewed diff rather than an SSH session nobody recorded.
The incident that changed it. In September the disk filled for most of a day while every health check stayed green. The fix wasn’t only freeing space: disk and memory alerts every five minutes, a weekly clean-up that needs no secrets so it still runs on a full disk, and a rule to capture logs before any recovery, because that recovery had destroyed most of its own evidence.
Building it with AI agents
Most of this was written with coding agents working in parallel. That only works with rules, and every rule came from something breaking: one isolated worktree and one database per agent, file-disjoint briefs, a landing loop that rebases, regenerates and re-runs the full suite before anything reaches main, and never killing a process by name pattern. The speed is the agents; the quality is the tests, types and decision records they have to get past.
What isn’t done yet
- Backups live on the same server, so the worst case is up to 24 hours of data and the server itself.
- One 2 GB server with no failover runs the API and the database.
- Hosted CI tests are off; the gate is a pre-push pipeline on the developer machine.
- No third-party penetration test yet, and no browser end-to-end suite, so a manual pass covers that class.
What I’d do differently
- Measure the legacy system on day one, not at the end, because the baseline is the cheapest number to get early.
- Move backups off the box before the first real customer data, not after.
- Put a staging environment in front of continuous deploys sooner.
See it live
The public job board is the rebuild in production, and its offers are server-rendered from the same API and database the recruiters use: ats-flow-portal.vercel.app
The recruiter app, signed in to a demo company with fictional data. Nothing you do there leaves the demo: no email or SMS goes out, and destructive actions are refused. Open the demo