# 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_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.
