Engineering
Scalability
A code-grounded audit of the two request-path backends — apps/track and
apps/api, both Hono on Neon serverless Postgres, deployed as Vercel
functions.
Verdict
The read path scales to roughly the low thousands of clicks per second, provided the Vercel functions and the Neon endpoint are co-located and Neon compute is on an autoscaling pooled endpoint.
The connection model is correct: the Neon HTTP driver is stateless, so there is no pool to exhaust across cold starts — which is the failure mode that usually takes serverless Postgres down.
It does not scale gracefully at three points, and none of them is the click path everyone worries about.
The cost model that explains all of it
On the Neon HTTP driver, each SQL statement is a separate HTTPS request. Latency is per-statement, not per-transaction. A loop of four statements over two hundred rows is eight hundred serial round-trips against a 30-second function cap, and it fails as a timeout rather than as an error anyone can read.
Anywhere this document says "N+1", that is what it means.
Fix first
1. Serial round-trips in the bulk write paths.
| Route | Shape |
|---|---|
POST /admin/accounts/:slug/links | ~3–4 statements per creator over the whole roster |
POST /v1/creators/bulk | The same, capped at 200 creators |
PATCH /admin/links and PATCH /v1/links | One UPDATE per entry |
POST /v1/creators | 4 statements per call, on every creator-dashboard load |
The partner bulk routes are capped at 200 entries, which bounds the damage but does not remove it: a full batch is several hundred serial round-trips and is the most likely thing in the system to time out. The admin roster path has no cap and is the one that already has.
The fix is set-based SQL: multi-row insert … on conflict … returning, one
insert … select to mint links, update … from (values …) for bulk attach,
and collapsing enrolment's read-after-write into a single
insert … on conflict do update … returning.
2. No idempotency on click ingest. GET /r/:linkId inserts a fresh row
unconditionally, so a retry, a prefetch or a link scanner double-counts the core
product metric. Either add a dedup key — link, ip hash, coarse time window —
with a unique index and on conflict do nothing, or accept at-least-once
explicitly and dedup on read. What is not tenable is leaving it ambiguous while
reporting the number as a ranking.
3. The rate limiter adds a round-trip to every /v1 call. One upsert per
request, on a surface that already makes at least one query. Acceptable now;
the first thing to move to a cheaper store if /v1 volume grows.
Already addressed
links.account_idis indexed. Every account-scoped read filters on it.clicks (campaign_id, created_at)is indexed, for the windowed campaign leaderboard.apps/trackhas a test suite that runs the real handlers and real SQL against an in-process Postgres. It had none when this audit was first written, which was the reason the set-based rewrite above could not be attempted safely.
Do not build the worker layer yet
apps/engine exists and is a skeleton. Filling it in now would be empty
infrastructure: the batch jobs it would host — hold settlement, the fraud scan,
attribution recompute — are all unimplemented, and apps/track has no cron at
all.
Build it when the first unbounded batch job ships. Do not run one as a single 30-second Vercel cron over every row.
The pattern to copy is already in this repo: the email queue in
apps/api/src/database/emails/emails.repository.ts claims a fixed batch with
FOR UPDATE SKIP LOCKED, applies a per-run budget, and re-invokes until
drained.
Keep Neon as the database. The database is not the reason to move anything.