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.

RouteShape
POST /admin/accounts/:slug/links~3–4 statements per creator over the whole roster
POST /v1/creators/bulkThe same, capped at 200 creators
PATCH /admin/links and PATCH /v1/linksOne UPDATE per entry
POST /v1/creators4 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_id is indexed. Every account-scoped read filters on it.

  • clicks (campaign_id, created_at) is indexed, for the windowed campaign leaderboard.

  • apps/track has 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.