End-to-End Feature Design and Development Questions
The integrated 'build a complete feature' walkthrough: taking one concrete feature (a follow button, a like button, a comments thread, a create-post form, a profile editor, a large file upload) from the user-facing flow through the API contract, backend logic, data model and storage in a single coherent answer. Includes optimistic UI and the backend idempotency it needs, concurrent and repeated requests, denormalized counts that must stay correct, client and server validation with errors mapped back to the right field, a contract shared by web and mobile clients, and how the feature is tested, rolled out behind a flag and observed end to end. Tests whether the candidate connects the layers and makes consistent trade-offs across them rather than depth in any one. Deep dives in a single layer (UI state, API style, schema, caching, pipelines, observability, security and authentication, rate limiting), payments and notification delivery, feature-flag platforms, migrations and deployment pipelines, and standalone large-scale system design are covered elsewhere.
On a support-ticket page, agents need to see a customer's new replies without refreshing. Walk me through how new replies reach the open page: which delivery option you would ship first (polling, server-sent events or WebSockets) and why, what the API and the client state look like, and how you make sure a reply that arrives while the connection is dropping is neither lost nor shown twice.
Sample Answer
Direct answer
Ship server-sent events (SSE: the browser opens one ordinary HTTP request that the server keeps open and writes small text messages into, one direction only, server to browser) first. Give every reply a per-ticket sequence number seq (1, 2, 3, ...) with no holes. The page remembers the highest seq it has shown; every message carries its seq as the event id; and the browser's built-in reconnect sends that id back, so the server replays everything after it. The page applies a reply only if its seq is the next one, ignores any seq it already has, and fetches the missing range if it sees a hole. That gives "not lost" (the database, not the connection, is the record, and replay starts from the last seq shown) and "not twice" (a seq can only be applied once) from one mechanism.
Why not the other two. Polling (the page asks every few seconds) is the simplest and always works, but it costs requests whether or not anything happened and delay is up to the interval. WebSockets (one long-lived two-way connection) pay for a second protocol, and this page needs nothing from the browser to the server except normal POSTs that already exist. SSE is plain HTTP, reconnects by itself, and carries an event id natively.
| Option | Requests for 2,000 open agent pages | Delay for a new reply | What it needs beyond HTTP |
|---|---|---|---|
| Polling every 5 s | 2,000 / 5 = 400 requests per second, nearly all returning nothing | 0 to 5 s, average 2.5 s | nothing |
| SSE | 2,000 open connections, one message per real reply | the time to write one message | proxies must not buffer the stream |
| WebSocket | 2,000 open connections | same as SSE | upgrade handshake, own message framing, own reconnect and resume logic |
The 2,000 pages is an assumed load for the arithmetic, not a measurement. What would flip the choice: if agents also need typing indicators or live cursors (frequent browser-to-server messages), use WebSockets; if the network between agents and the server strips long-lived responses and you cannot change it, ship polling behind the same API and keep the rest of the design unchanged.
Data model: a gapless sequence per ticket
CREATE TABLE tickets (
id bigint PRIMARY KEY,
last_seq integer NOT NULL DEFAULT 0
);
CREATE TABLE replies (
ticket_id bigint NOT NULL REFERENCES tickets(id),
seq integer NOT NULL,
author text NOT NULL,
body text NOT NULL CHECK (char_length(body) BETWEEN 1 AND 5000),
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (ticket_id, seq)
);
INSERT INTO tickets (id) VALUES (1);
Three database terms first. A session is one open connection to the database. A transaction is a group of statements the database treats as one unit: other sessions see none of its changes until it commits (is finalized), and if it rolls back (is abandoned) none of them ever appear. A row lock is a hold the database puts on a row while a transaction changes it: any other transaction that wants to change the same row waits until the holder commits or rolls back.
Here is the failure with concrete steps, using a plain auto-increment id as the cursor (the cursor is "the highest id the page has seen; ask for everything after it"):
- Writer A starts a transaction and inserts a reply. The database hands it id 1. A has not committed, so nobody else can see reply 1.
- Writer B starts a transaction, inserts a reply, gets id 2, and commits. Reply 2 is visible.
- The page asks for "ids greater than 0" and receives only reply 2. Its cursor becomes 2.
- A commits; reply 1 is now visible. The page asks for "ids greater than 2" and receives nothing. Reply 1 is never delivered.
With a per-ticket seq, step 2 cannot happen: taking a number locks the ticket row, so B's request for a number waits until A commits.
The obvious choice, one global auto-increment id on replies and a cursor of "give me ids greater than the last one I saw", loses messages. The id is handed out when the INSERT runs, but a row becomes visible only when its transaction commits, and two transactions can commit in the opposite order. The seq here is taken by an UPDATE tickets SET last_seq = last_seq + 1, which locks the ticket row until the transaction ends, so two replies on one ticket cannot be in flight together, and they commit in seq order. Run in a postgres:16 container with two sessions and a third reading (gap-demo.mjs below), the two designs behave like this:
A. global id: poll after 0 sees [2] -> cursor = 2
A. then poll after 2 sees [] (reply 1 is never delivered)
B. A holds seq 1 ; B is waiting on a lock, and nothing of B is visible: []
B. after A commits, B gets seq 2 ; reader sees [1,2]
C. A took seq 3 then rolled back; the next writer gets seq 3 (no hole)
Line A prints steps 3 and 4 above (lines B and C are the fix, read next). In line A, a reader that saw only reply 2 moves its cursor to 2 and never sees reply 1, even though reply 1 commits afterwards. Lines B show the fix: session B really waits on a lock (pg_stat_activity, a built-in PostgreSQL view that lists every session and what it is currently waiting for, reports wait_event_type = 'Lock' for it) while A is open, so B cannot commit before A, and the reader never sees 2 without 1. Line C shows that a rolled-back reply gives its number back (the counter update is rolled back with it), so the sequence has no holes, which is what lets the client treat any jump as a missing message.
API
| Call | Purpose | Response |
|---|---|---|
GET /api/tickets/{id}/replies?after=N | catch-up: replies with seq greater than N, oldest first, at most 200 | 200 {"replies": [...]} |
GET /api/tickets/{id}/stream?after=N | the live stream | 200, Content-Type: text/event-stream, stays open |
POST /api/tickets/{id}/replies {author, body} | a customer or agent adds a reply | 201 with the saved reply, 404 unknown ticket, 422 invalid |
Each stream message looks like this (a blank line ends a message; id becomes the browser's last event id):
id: 4
event: reply
data: {"seq":4,"author":"customer","body":"reply 4","created_at":"..."}
The stream's starting point is the larger of the Last-Event-ID request header and the after query. On a reconnect the browser itself sends a request header (a named line sent along with the request) called Last-Event-ID carrying the last event id it received; the HTML specification says it sends the header only when that id is non-empty, so a connection that never received a message falls back to after. The server begins with retry: 500, the SSE field that sets the reconnect delay in milliseconds (the spec leaves the default to the browser, "a few seconds"). Lines starting with a colon are comments the browser ignores, so the server sends : keepalive periodically to stop idle-timeout rules in proxies from closing a quiet connection.
Backend: one code path for replay and live
import http from 'node:http';
import { readFile } from 'node:fs/promises';
import pg from 'pg';
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const HEARTBEAT_MS = Number(process.env.HEARTBEAT_MS ?? 25000);
const subs = new Map(); // ticket id (string) -> Set of open streams
let outage = false; // test hook: refuse new streams
const json = (res, status, data) => {
res.writeHead(status, { 'Content-Type': 'application/json' });
res.end(JSON.stringify(data));
};
const COLS = 'seq, author, body, created_at';
// Write every reply this stream has not yet received. The database is the source of truth,
// so replay after a reconnect and live delivery are the same code path.
async function flush(sub) {
if (sub.busy) { sub.again = true; return; }
sub.busy = true;
try {
do {
sub.again = false;
let rows;
do {
({ rows } = await pool.query(
`SELECT ${COLS} FROM replies WHERE ticket_id = $1 AND seq > $2 ORDER BY seq LIMIT 100`,
[sub.ticket, sub.last]));
for (const r of rows) sub.res.write(`id: ${r.seq}\nevent: reply\ndata: ${JSON.stringify(r)}\n\n`);
if (rows.length) sub.last = rows[rows.length - 1].seq;
} while (rows.length === 100);
} while (sub.again);
} catch (err) { sub.res.destroy(); }
finally { sub.busy = false; }
}
// One LISTEN connection wakes the streams. NOTIFY is only a wake-up, never the data.
const listener = new pg.Client({ connectionString: process.env.DATABASE_URL });
await listener.connect();
await listener.query('LISTEN ticket_replies');
listener.on('notification', (msg) => { for (const sub of subs.get(msg.payload) ?? []) flush(sub); });
function openStream(req, res, ticket, url) {
if (outage) return req.socket.destroy();
const fromHeader = Number(req.headers['last-event-id'] ?? 0) || 0;
const fromQuery = Number(url.searchParams.get('after') ?? 0) || 0;
res.writeHead(200, {
'Content-Type': 'text/event-stream', 'Cache-Control': 'no-cache', 'X-Accel-Buffering': 'no',
});
res.write('retry: 500\n\n'); // reconnect delay in ms, set by the server
const sub = { ticket, res, last: Math.max(fromHeader, fromQuery), busy: false, again: false };
if (!subs.has(ticket)) subs.set(ticket, new Set());
subs.get(ticket).add(sub); // register BEFORE the first read, so nothing slips between
const beat = setInterval(() => res.write(': keepalive\n\n'), HEARTBEAT_MS);
req.on('close', () => { clearInterval(beat); subs.get(ticket).delete(sub); });
flush(sub);
}
async function readJson(req) {
let raw = '';
for await (const c of req) { raw += c; if (raw.length > 65536) throw Object.assign(new Error('too large'), { status: 413 }); }
try { return JSON.parse(raw); } catch { throw Object.assign(new Error('invalid JSON'), { status: 400 }); }
}
async function addReply(req, res, ticket) {
const input = await readJson(req);
const body = typeof input.body === 'string' ? input.body.trim() : '';
const author = typeof input.author === 'string' ? input.author.trim() : '';
if (!body || body.length > 5000 || !author) return json(res, 422, { errors: { body: 'A reply needs an author and 1 to 5000 characters.' } });
const db = await pool.connect();
try {
await db.query('BEGIN');
// Taking the next number locks the ticket row, so replies on one ticket commit in seq order.
const t = await db.query('UPDATE tickets SET last_seq = last_seq + 1 WHERE id = $1 RETURNING last_seq', [ticket]);
if (t.rowCount === 0) { await db.query('ROLLBACK'); return json(res, 404, { error: 'No such ticket' }); }
const { rows } = await db.query(
`INSERT INTO replies (ticket_id, seq, author, body) VALUES ($1, $2, $3, $4) RETURNING ${COLS}`,
[ticket, t.rows[0].last_seq, author, body]);
await db.query('SELECT pg_notify($1, $2)', ['ticket_replies', ticket]); // delivered when the transaction commits
await db.query('COMMIT');
json(res, 201, rows[0]);
} catch (err) { await db.query('ROLLBACK').catch(() => {}); throw err; }
finally { db.release(); }
}
http.createServer(async (req, res) => {
try {
const url = new URL(req.url, 'http://x');
const file = { '/': ['index.html', 'text/html; charset=utf-8'], '/client.js': ['client.js', 'text/javascript'], '/replies.js': ['replies.js', 'text/javascript'] }[url.pathname];
if (req.method === 'GET' && file) {
res.writeHead(200, { 'Content-Type': file[1] });
return res.end(await readFile(new URL(`./public/${file[0]}`, import.meta.url)));
}
if (process.env.TEST_HOOKS === '1' && url.pathname === '/test/outage') {
outage = url.searchParams.get('on') === '1';
if (outage) for (const set of subs.values()) for (const s of set) s.res.socket.destroy();
return json(res, 200, { outage });
}
const m = url.pathname.match(/^\/api\/tickets\/(\d{1,18})\/(replies|stream)$/);
if (!m) return json(res, 404, { error: 'Not found' });
const [, ticket, what] = m;
if (what === 'stream' && req.method === 'GET') return openStream(req, res, ticket, url);
if (what === 'replies' && req.method === 'GET') {
const after = Number(url.searchParams.get('after') ?? 0) || 0;
const { rows } = await pool.query(
`SELECT ${COLS} FROM replies WHERE ticket_id = $1 AND seq > $2 ORDER BY seq LIMIT 200`, [ticket, after]);
return json(res, 200, { replies: rows });
}
if (what === 'replies' && req.method === 'POST') return await addReply(req, res, ticket);
json(res, 405, { error: 'Method not allowed' });
} catch (err) {
if (err.status) return json(res, err.status, { error: err.message });
console.error(err); json(res, 500, { error: 'Server error' });
}
}).listen(3000, () => console.log('listening'));
LISTENandNOTIFYare PostgreSQL's built-in publish and subscribe: a connection that ranLISTEN ticket_repliesreceives a short message whenever any session runsNOTIFY(or the function form,pg_notify) on that channel. The server keeps one such dedicated listener connection open for the whole process.- A write takes the next
seq, inserts the row, and callspg_notifyin the same transaction. PostgreSQL delivers a notification only if the transaction commits, and delivers notifications from different transactions in commit order, so a rolled-back reply wakes nobody. - A notification is only a wake-up. Its payload is the ticket id; each open stream for that ticket then runs
flush, which queries "rows after what this stream last sent" and writes them. A reconnect runs the sameflushwithlasttaken fromLast-Event-ID. There is no separate "replay" and "live" logic to disagree. - No race at the start. The stream registers itself in
subsbefore its firstflush. A reply committed during the first read sets theagainflag and is picked up by the loop. A reply committed before registration is found by the query. There is no instant when a reply falls between the two. - Tracing
flushwith real values.busymeans "a flush for this stream is already running" andagainmeans "a wake-up arrived while it was running". Say a stream haslast = 2, the database holds replies 1 to 4, and reply 5 is committed while the first query is in flight. (1)flushstarts:busy = true,again = false. (2) The inner query asks forseq > 2and returns replies 3 and 4 (2 rows, fewer than the page size of 100, so the inner loop ends); they are written andlast = 4. (3) Meanwhile reply 5 committed and its notification calledflushagain; becausebusywas true that call only setagain = trueand returned. (4) The outerwhile (sub.again)seestrue, so the loop runs again:againis reset tofalse, the query asks forseq > 4, returns reply 5, andlast = 5. (5)againis stillfalse, so the function ends andbusybecomesfalse. Nothing can slip between step 5's check andbusy = false, because that part of the code has noawait, and JavaScript runs one piece of synchronous code to completion before any other callback runs. If a query ever returns exactly 100 rows, the innerwhile (rows.length === 100)repeats the query for the next page. - Because the notification travels through the database, this works with several server instances: each holds its own
LISTENconnection. The shipped listener has no reconnect handler: an unhandled error on that connection would end the process. A production version reconnects, re-runsLISTEN, and then runsflushfor every open stream, which repairs any missed wake-up because the data is never in the notification; the keepalive write notices dead sockets. - The
X-Accel-Buffering: noheader tells nginx (a widely used web server that is often placed in front of an application and forwards requests to it, which is what a reverse proxy does) not to buffer this response; nginx documents that header as turning response buffering off for one response, and buffering is on by default, which would hold messages back. outageand the/test/outageroute exist only so the test can force a dropped connection; they are inert unlessTEST_HOOKS=1.
Client state
The rules for the list live in a pure function with no DOM or network:
// Pure state for the reply list. No DOM, no network: easy to test.
export const initial = () => ({ last: 0, items: [] });
// Apply a batch of replies. Rules, per reply in seq order:
// seq <= last already shown (a replay or a duplicate): ignore
// seq === last + 1 the next one: append it
// seq > last + 1 a hole: stop, and tell the caller to fetch the missing range
export function apply(state, incoming) {
let { last, items } = state;
let gap = false, added = 0;
for (const r of [...incoming].sort((a, b) => a.seq - b.seq)) {
if (r.seq <= last) continue;
if (r.seq !== last + 1) { gap = true; break; }
items = [...items, r]; last = r.seq; added++;
}
return { state: { last, items }, gap, added };
}
The page wires it to the network:
import { initial, apply } from './replies.js';
const TICKET = 1;
let state = initial();
let es = null;
const setConn = (text) => { document.getElementById('conn').textContent = text; };
function render() {
const ol = document.getElementById('replies');
ol.replaceChildren(...state.items.map((r) => {
const li = document.createElement('li');
li.dataset.seq = r.seq;
li.textContent = `${r.author}: ${r.body}`;
return li;
}));
}
function ingest(list) {
const r = apply(state, list);
state = r.state;
if (r.added) render();
if (r.gap || list.length === 200) catchUp(); // a hole, or a full page that may have more behind it
}
async function catchUp() {
const res = await fetch(`/api/tickets/${TICKET}/replies?after=${state.last}`);
if (res.ok) ingest((await res.json()).replies);
}
function connect() {
es = new EventSource(`/api/tickets/${TICKET}/stream?after=${state.last}`);
es.onopen = () => setConn('Live');
es.addEventListener('reply', (e) => ingest([JSON.parse(e.data)]));
es.onerror = () => {
setConn('Reconnecting...');
if (es.readyState === EventSource.CLOSED) { es.close(); setTimeout(connect, 2000); } // the browser gave up (for example on a non-200 reply): retry ourselves
};
}
await catchUp(); // history first, so the stream starts from state.last
connect();
<!doctype html>
<html lang="en">
<head><meta charset="utf-8"><title>Ticket 1</title></head>
<body>
<h1>Ticket 1</h1>
<p id="conn" role="status">Connecting...</p>
<ol id="replies"></ol>
<script type="module" src="/client.js"></script>
</body>
</html>
- The order at load matters: fetch history first, then open the stream with
after=state.last. Anything posted in between arrives on the stream, anything before is already in history. ingestis the only way a reply reaches the screen, whether it came from the stream, from a catch-up fetch, or from both at once. That single door is what prevents a double display.es.onerrorfires for every dropped connection.readyStateis the connection's state:CONNECTINGmeans the browser is trying (again) to connect,OPENmeans it is connected, andCLOSEDmeans it has stopped for good. WhilereadyStateisCONNECTINGthe browser is already retrying, so the code only updates the status text. If it isCLOSED, the browser has given up (the HTML specification fails the connection for any HTTP status other than 200, and a204is the way to tell the client to stop), so the code retries itself.- Rendering uses
textContent, so a reply containing markup shows as text. - Browsers limit simultaneous HTTP/1.1 connections per site; MDN gives 6 per browser for SSE, which agents with many tickets in tabs can hit. With HTTP/2, which carries many independent streams over one connection, the negotiated limit defaults to 100 streams (MDN), so serve the stream over HTTP/2.
The drop, walked through
The reducer on its own, then the server's starting-point rule, then the real page in Chromium with the connection killed while replies keep arriving:
import { chromium } from 'playwright';
import { initial, apply } from './public/replies.js';
const base = 'http://localhost:3000';
const post = (author, body) => fetch(`${base}/api/tickets/1/replies`, {
method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ author, body }) }).then((r) => r.json());
const seqs = (page) => page.$$eval('#replies li', (els) => els.map((e) => Number(e.dataset.seq)));
// 1. The reducer rules on their own
const r = (n) => ({ seq: n });
let s = initial();
const step = (label, batch) => { const o = apply(s, batch); s = o.state; console.log(label.padEnd(34), 'shown=' + JSON.stringify(s.items.map((x) => x.seq)), 'gap=' + o.gap); };
step('1, 2 arrive', [r(1), r(2)]);
step('2 again (duplicate)', [r(2)]);
step('1, 2, 3 replayed (overlap)', [r(1), r(2), r(3)]);
step('5 arrives, 4 missing', [r(5)]);
step('catch-up brings 4, 5', [r(4), r(5)]);
// 2. What the server does with Last-Event-ID and ?after
async function streamSeqs(url, headers, want) {
const ctl = new AbortController();
const res = await fetch(url, { headers, signal: ctl.signal });
const got = []; let buf = '';
for await (const chunk of res.body) {
buf += Buffer.from(chunk).toString();
for (const m of buf.matchAll(/^id: (\d+)$/gm)) if (!got.includes(+m[1])) got.push(+m[1]);
if (got.length >= want) break;
}
ctl.abort(); return got;
}
for (const n of [1, 2, 3]) await post('customer', `reply ${n}`);
console.log('after=0, no header ->', JSON.stringify(await streamSeqs(`${base}/api/tickets/1/stream?after=0`, {}, 3)));
console.log('after=0, Last-Event-ID: 2 ->', JSON.stringify(await streamSeqs(`${base}/api/tickets/1/stream?after=0`, { 'Last-Event-ID': '2' }, 1)));
// 3. The real page, with a dropped connection and replies posted during the outage
const browser = await chromium.launch();
const page = await browser.newPage();
await page.goto(base);
await page.waitForFunction(() => document.getElementById('conn').textContent === 'Live');
console.log('loaded:', JSON.stringify(await seqs(page)));
await post('customer', 'reply 4');
await page.waitForSelector('li[data-seq="4"]');
console.log('live push:', JSON.stringify(await seqs(page)));
await fetch(`${base}/test/outage?on=1`);
await page.waitForFunction(() => document.getElementById('conn').textContent === 'Reconnecting...');
await post('customer', 'reply 5');
await post('customer', 'reply 6');
console.log('during outage the page shows:', JSON.stringify(await seqs(page)));
await fetch(`${base}/test/outage?on=0`);
await page.waitForSelector('li[data-seq="6"]');
await page.waitForFunction(() => document.getElementById('conn').textContent === 'Live');
console.log('after reconnect:', JSON.stringify(await seqs(page)));
await browser.close();
It prints (run in the Playwright image, Node 20.18, Chromium 130):
1, 2 arrive shown=[1,2] gap=false
2 again (duplicate) shown=[1,2] gap=false
1, 2, 3 replayed (overlap) shown=[1,2,3] gap=false
5 arrives, 4 missing shown=[1,2,3] gap=true
catch-up brings 4, 5 shown=[1,2,3,4,5] gap=false
after=0, no header -> [1,2,3]
after=0, Last-Event-ID: 2 -> [3]
loaded: [1,2,3]
live push: [1,2,3,4]
during outage the page shows: [1,2,3,4]
after reconnect: [1,2,3,4,5,6]
Reading the output: replaying 1, 2, 3 over a page that already shows 1, 2 adds only 3 (no double), and a jump to 5 is held back until the catch-up brings 4 (no loss, still in order). The stream started with after=0 and Last-Event-ID: 2 sends only 3, so the header wins when it is larger. In the page, the outage refuses every new stream, replies 5 and 6 are posted meanwhile, the page still shows [1,2,3,4], and a moment after the outage ends it shows [1,2,3,4,5,6], each once, in order. The whole run (server, test and the two-session demo) prints the same output every time, because every browser wait is on a condition, not a delay.
Failure modes and what to watch
Ordered by how many users a failure touches: a restart touches every page, a slow reader touches one stream, and the rest are checks that keep the system honest.
- Server restart or deploy: every stream drops and every page reconnects within the 500 ms hint at once. Add jitter (a random extra delay, so clients do not all act in the same instant) by sending a randomised
retryvalue, so a thousand pages do not arrive together. - Slow or stuck client:
res.writequeues in memory for a client that stopped reading. Handling this is called back-pressure: the sender notices the receiver cannot keep up and stops queueing for it. Production code checks the write result and closes streams that fall behind; the reconnect replays what it missed. - Authorization: the stream must check that this agent may see this ticket before registering; that check belongs in the same place as the one on the catch-up call.
- Observe: the number of open streams, reconnects per minute, the age of the oldest unsent reply per stream, and the count of
gapcatch-ups (a non-zero count in steady state means a bug or a dropped message path). - Tests to keep: the reducer cases above (pure), the two-session ordering demo against a real database, and the outage scenario in a real browser.
Running the code
docker network create tix-net
docker run -d --rm --name tix-pg --network tix-net -e POSTGRES_PASSWORD=pw postgres:16
sleep 5 # wait for Postgres to accept connections
docker exec tix-pg psql -U postgres -c "CREATE DATABASE tix"
docker exec -i tix-pg psql -U postgres -d tix < schema.sql
# in a folder holding schema.sql, server.mjs, test.mjs, gap-demo.mjs, public/ and package.json {"type":"module"}:
docker run --rm --network tix-net -v "$PWD":/w -w /w -e DATABASE_URL=postgres://postgres:pw@tix-pg:5432/tix \
-e TEST_HOOKS=1 -e HEARTBEAT_MS=300 mcr.microsoft.com/playwright:v1.48.2-jammy \
sh -c 'npm i pg@8 playwright@1.48.2; node server.mjs & sleep 3; node test.mjs; node gap-demo.mjs'
gap-demo.mjs, the two-session program that printed the lines A, B and C above:
import pg from 'pg';
const url = process.env.DATABASE_URL;
const client = async () => { const c = new pg.Client({ connectionString: url }); await c.connect(); return c; };
const [a, b, reader] = [await client(), await client(), await client()];
const ids = async (sql, params) => (await reader.query(sql, params)).rows.map((r) => r.n);
await reader.query('CREATE TABLE naive (id bigserial PRIMARY KEY, body text)');
await a.query('BEGIN'); await a.query("INSERT INTO naive (body) VALUES ('from A')"); // A takes id 1
await b.query('BEGIN'); await b.query("INSERT INTO naive (body) VALUES ('from B')"); // B takes id 2
await b.query('COMMIT'); // B commits first
const first = await ids('SELECT id::int AS n FROM naive WHERE id > 0 ORDER BY id');
console.log('A. global id: poll after 0 sees', JSON.stringify(first), '-> cursor =', Math.max(...first));
await a.query('COMMIT'); // A commits late
console.log('A. then poll after', Math.max(...first), 'sees', JSON.stringify(await ids('SELECT id::int AS n FROM naive WHERE id > $1 ORDER BY id', [Math.max(...first)])), '(reply 1 is never delivered)');
await reader.query('CREATE TABLE tk (id int PRIMARY KEY, last_seq int NOT NULL DEFAULT 0)');
await reader.query('CREATE TABLE rp (ticket_id int, seq int, body text, PRIMARY KEY (ticket_id, seq))');
await reader.query('INSERT INTO tk VALUES (1, 0)');
const take = async (c, body) => { const { rows } = await c.query('UPDATE tk SET last_seq = last_seq + 1 WHERE id = 1 RETURNING last_seq'); await c.query('INSERT INTO rp VALUES (1, $1, $2)', [rows[0].last_seq, body]); return rows[0].last_seq; };
await a.query('BEGIN'); const seqA = await take(a, 'from A');
await b.query('BEGIN');
const pidB = (await b.query('SELECT pg_backend_pid() AS p')).rows[0].p;
const pendingB = take(b, 'from B'); // starts, then must wait for A
let waiting = false;
while (!waiting) {
waiting = (await reader.query("SELECT 1 FROM pg_stat_activity WHERE pid = $1 AND wait_event_type = 'Lock'", [pidB])).rowCount === 1;
if (!waiting) await new Promise((r) => setTimeout(r, 20));
}
console.log('B. A holds seq', seqA, '; B is waiting on a lock, and nothing of B is visible:', JSON.stringify(await ids('SELECT seq AS n FROM rp')));
await a.query('COMMIT');
const seqB = await pendingB; await b.query('COMMIT');
console.log('B. after A commits, B gets seq', seqB, '; reader sees', JSON.stringify(await ids('SELECT seq AS n FROM rp ORDER BY seq')));
await a.query('BEGIN'); const rolled = await take(a, 'will roll back'); await a.query('ROLLBACK');
await b.query('BEGIN'); const next = await take(b, 'next'); await b.query('COMMIT');
console.log('C. A took seq', rolled, 'then rolled back; the next writer gets seq', next, '(no hole)');
await reader.query('DROP TABLE naive, rp, tk');
await Promise.all([a.end(), b.end(), reader.end()]);
Field technicians fill in an inspection checklist in a web app on tablets, often in basements with no signal. Walk me through how a submitted checklist reaches the server reliably: what the browser stores and where, how and when it retries, how the server avoids saving the same checklist twice, and what the technician sees at each step.
Sample Answer
Direct answer
Treat the tablet as the first place the checklist is saved and the server as the second. When the technician taps Submit, the app writes the finished checklist to the browser's own database (IndexedDB) together with an id generated on the tablet, tells the technician "Saved on this tablet", and only then tries the network. A small background loop (the outbox) sends queued checklists with PUT /v1/inspections/{id}, retries failures with a growing, randomised pause, and deletes the local copy only after the server has confirmed. The server stores each id at most once, so a retry after a lost reply is harmless. The screen never says "Submitted" until the server has answered with a success.
The id is the key to the whole design. Because the tablet picks it before the first attempt, every retry of the same checklist carries the same id, and the database can recognise a repeat. Without it, a lost response ("did it save or not?") forces a choice between losing data and saving twice.
What the technician sees at each step
| Moment | What happens underneath | What the screen says |
|---|---|---|
| Fills the form in the basement | answers held in the form; optionally draft-saved to IndexedDB every few seconds | normal form, a small "Offline" badge |
| Taps Submit | record written to IndexedDB (state: queued), button disabled so one tap makes one record | "Saved on this tablet. It will be sent when there is a signal." |
Send attempt fails (no signal, timeout, 5xx) | attempt counter goes up, next try scheduled after a random pause | "Saved on this tablet. Will retry automatically." plus a count of waiting checklists |
Signal returns, PUT answered 201 or 200 | local record deleted | "Sent. The office has your checklist." with the time |
Server answers 401 (signed out while offline) | record stays queued, no timer; retried after the technician signs in | "Please sign in again. Your checklist is safe on this tablet." |
Server answers 409 or 422 | record kept, marked needs_attention, never retried automatically | "This checklist was rejected. Tap to review it." |
| App closed and reopened with items waiting | outbox loop starts again on page load | "1 checklist waiting to be sent." |
Two wording rules follow from this. "Saved" and "Sent" are different words for different facts, and a technician who leaves the building after seeing "Saved" must understand the office does not have it yet. And a waiting count must stay visible on the home screen, because the main failure in the field is a technician who believes everything was sent.
What the browser stores, and where
- IndexedDB holds the outbox. (An origin is the site's address: scheme, host and port. Browser storage belongs to one origin, so another site cannot read it.) It is an asynchronous browser database that stores structured data, including files and blobs, so a checklist with photo attachments fits. A Blob is a chunk of binary data such as a photo.
localStorageholds only strings and suits small settings, not a queue of records with photos. - A service worker (a script the browser runs separately from the page, able to answer requests from a cache) keeps the app's own files, so the app opens in the basement on a cold start (opened fresh, with the browser not already holding the page). The demo below blocks only the API calls; the service worker is what makes the page files available offline.
- Eviction means the browser deleting stored data to free space. By default, browser storage is best effort: the browser may delete an origin's data under storage pressure, and MDN notes Safari deletes script-created data for an origin with no user interaction in seven days of browser use when cross-site tracking prevention is on.
navigator.storage.persist()asks the browser to keep the data until the user clears it; the browser may or may not agree (Firefox asks the user; Safari and Chromium decide from the user's history with the site). So request persistence at startup, treat afalseresult as normal, and keep the sync window short: the sooner a checklist reaches the server, the less it depends on the tablet's storage. - Photos are Blobs in IndexedDB uploaded as separate requests with their own client-generated ids, and the checklist references them. The checklist is marked sent only after the server confirms it, and its photos are confirmed separately.
When and how it retries
Triggers: right after Submit, when the page loads, when the browser fires the online event, when the app returns to the foreground (the tab becomes visible again; the browser fires a visibilitychange event), and on a timer after a failure. Do not wait for online alone: navigator.onLine can be true on a network that reaches no internet (MDN: a LAN connection counts as online), and MDN calls the property inherently unreliable, so use it as a hint and let a real request failure be the truth. The Background Synchronization API (a service worker sync event that fires when connectivity returns) fits this problem, but MDN lists it as limited availability and not Baseline, so it is an enhancement on top of the triggers above rather than the mechanism.
What to retry: network errors, timeouts, 408, 429 and 5xx. What not to retry automatically: other 4xx. A 401 is the exception that waits for a person: flush() stops, keeps the record queued and asks for sign-in, then the next trigger sends it. A 422 means the content is invalid and sending it again changes nothing; a 409 means the id exists with different content. Those go to needs_attention for a human.
The pause grows with each failed attempt and is randomised ("exponential backoff with full jitter"): wait a random time between 0 and min(cap, base * 2^attempt). The randomisation matters at scale: if a depot of tablets regains signal together after a power cut, fixed delays make them all retry in the same instant. Worked numbers for the code below (base 2 s, cap 300 s; the wait after failed attempt n is a random value between 0 and min(300 s, 2 s * 2^n)):
| Failed attempt n | Upper limit of the random wait |
|---|---|
| 1 | 4 s |
| 2 | 8 s |
| 3 | 16 s |
| 4 | 32 s |
| 5 | 64 s |
| 6 | 128 s |
| 7 | 256 s |
| 8 and later | 300 s (512 s would exceed the cap) |
Each actual wait is drawn at random inside its range, so two tablets that failed together will retry at different moments. Retry-After is a response header telling the client when to try again, written either as a number of seconds (Retry-After: 120) or as an HTTP date (Retry-After: Wed, 21 Oct 2015 07:28:00 GMT); honour it when the server sends one, and take the later of it and the backoff wait. The code below keeps to the backoff alone for brevity.
How the server avoids saving it twice
PUT /v1/inspections/{id} (an alternative is POST plus an Idempotency-Key header, a client-chosen token the server uses to recognise repeats) with the client-generated UUID (universally unique identifier, a random 128-bit id) in the path. MDN describes PUT as idempotent (calling it once is no different from calling it several times), and says the server answers 201 Created when it creates the resource and 200 or 204 when the resource already existed and was modified. Here the server uses 201 for the first save and 200 for a replay of identical content, and the client treats both as success.
DROP TABLE IF EXISTS inspections;
CREATE TABLE inspections (
id uuid PRIMARY KEY, -- generated on the tablet, the idempotency key
technician_id integer NOT NULL,
site text NOT NULL,
answers jsonb NOT NULL,
payload_hash text NOT NULL, -- fingerprint of the body, to tell a replay from a clash
completed_at timestamptz NOT NULL, -- when the technician finished (tablet clock)
received_at timestamptz NOT NULL DEFAULT now()
);
The primary key on id is what makes a duplicate impossible, even when two requests for the same id arrive at the same moment (a retry while the first attempt is still in flight). The insert uses ON CONFLICT (id) DO NOTHING (if a row with that primary key already exists, skip the insert instead of raising an error). The answers column is jsonb, PostgreSQL's binary JSON type. When the insert stores nothing, the server compares the stored payload_hash (a fingerprint of the body) and the technician id with the new request: same content from the same technician is a replay (200), anything else is a clash (409 Conflict, which MDN defines as a conflict with the current state of the target resource).
The code
How to read the browser code: IndexedDB works through events. tx() in outbox.js opens the database, starts a transaction (a group of reads and writes that complete together) on the outbox object store (IndexedDB's name for a table), runs the function passed in, and wraps the callbacks in a promise: oncomplete means the transaction has committed so the promise resolves, onerror rejects it. By default the browser may report a commit before it has verified the bytes on the storage medium (a power cut can still lose the last write); passing { durability: 'strict' } to db.transaction asks it to wait for persistent storage first, which is worth the small cost for a record the technician believes is saved (the code below leaves the default durability for brevity). Every outbox read and write goes through it, and save() awaits it before any network call, which is why a submitted checklist survives the page or app being closed or crashing. In the server, faults.loseResponses is a fault injector for the demo: when above zero the server saves the checklist and then cuts the connection without replying, which reproduces a lost response on demand.
The outbox (browser side), the page that uses it, the server, and the driver that exercises all of it in a real Chromium:
// outbox.js
const cfg = () => window.OUTBOX_RETRY || { base: 2000, cap: 300000 }; // ms
const open = () => new Promise((ok, no) => {
const r = indexedDB.open('inspections', 1);
r.onupgradeneeded = () => r.result.createObjectStore('outbox', { keyPath: 'id' });
r.onsuccess = () => ok(r.result); r.onerror = () => no(r.error);
});
const tx = async (mode, fn) => {
const db = await open();
return new Promise((ok, no) => {
const t = db.transaction('outbox', mode); const out = fn(t.objectStore('outbox'));
t.oncomplete = () => { db.close(); ok(out.result); }; t.onerror = () => no(t.error);
});
};
export const requestPersistence = async () => (navigator.storage?.persist ? navigator.storage.persist() : false);
export const pending = () => tx('readonly', (s) => s.count());
export async function save({ site, answers }) {
const item = { id: crypto.randomUUID(), site, answers, completed_at: new Date().toISOString(), attempts: 0, state: 'queued' };
await tx('readwrite', (s) => s.put(item)); // durable on the tablet BEFORE any network call
return item.id;
}
let flushing = false, timer = null;
const say = (t) => window.dispatchEvent(new CustomEvent('outbox-state', { detail: t }));
export async function flush() {
if (flushing) return; flushing = true; clearTimeout(timer);
try {
const items = await tx('readonly', (s) => s.getAll());
for (const item of items.filter((i) => i.state === 'queued')) {
say('Sending...');
let res;
try {
res = await fetch(`/v1/inspections/${item.id}`, {
method: 'PUT', headers: { 'content-type': 'application/json', 'x-demo-user': '7' },
body: JSON.stringify({ site: item.site, answers: item.answers, completed_at: item.completed_at }),
signal: AbortSignal.timeout(15000) });
} catch { res = null; } // no signal, timeout, or lost response
if (res && (res.status === 200 || res.status === 201)) {
await tx('readwrite', (s) => s.delete(item.id)); // delete locally only after the server confirmed
say('Sent. The office has your checklist.');
} else if (res && res.status === 401) { // signed out while offline: keep the record, ask for sign-in, no timer
say('Please sign in again. Your checklist is safe on this tablet.');
break;
} else if (res && res.status >= 400 && res.status < 500 && res.status !== 429 && res.status !== 408) {
await tx('readwrite', (s) => s.put({ ...item, state: 'needs_attention', status: res.status }));
say('This checklist was rejected. Tap to review it.');
} else { // retryable: network error, 5xx, 429, 408
const n = item.attempts + 1;
await tx('readwrite', (s) => s.put({ ...item, attempts: n }));
const { base, cap } = cfg();
const wait = Math.random() * Math.min(cap, base * 2 ** n); // exponential backoff with full jitter
say('Saved on this tablet. Will retry automatically.');
timer = setTimeout(flush, wait);
break;
}
}
} finally { flushing = false; }
}
<!-- index.html -->
<!doctype html>
<meta charset="utf-8">
<title>Inspection</title>
<form id="f">
<label>Site <input id="site" required></label>
<label><input type="checkbox" id="q1"> Fire exit clear</label>
<label><input type="checkbox" id="q2"> Extinguisher in date</label>
<button id="submit">Submit checklist</button>
</form>
<p id="status" role="status" aria-live="polite"></p>
<script type="module">
import { save, flush, pending, requestPersistence } from '/outbox.js';
const status = document.getElementById('status');
const show = (t) => { status.textContent = t; };
window.addEventListener('outbox-state', (e) => show(e.detail));
await requestPersistence();
document.getElementById('f').addEventListener('submit', async (ev) => {
ev.preventDefault();
document.getElementById('submit').disabled = true; // one tap makes one record
await save({ site: site.value, answers: { q1: q1.checked, q2: q2.checked } });
show('Saved on this tablet. It will be sent when there is a signal.');
flush();
});
if ((await pending()) > 0) { show('1 checklist waiting to be sent.'); flush(); }
window.addEventListener('online', flush);
document.addEventListener('visibilitychange', () => { if (!document.hidden) flush(); });
</script>
// server.mjs
import http from 'node:http';
import crypto from 'node:crypto';
import fs from 'node:fs';
export function makeServer(pool, faults = { loseResponses: 0 }) {
const seen = new Map(); // id -> number of requests received
const srv = http.createServer(async (req, res) => {
const url = new URL(req.url, 'http://x');
if (req.method === 'GET' && url.pathname === '/') { res.setHeader('content-type', 'text/html'); return res.end(fs.readFileSync('index.html')); }
if (req.method === 'GET' && url.pathname === '/outbox.js') { res.setHeader('content-type', 'text/javascript'); return res.end(fs.readFileSync('outbox.js')); }
const m = url.pathname.match(/^\/v1\/inspections\/([0-9a-f-]{36})$/);
if (!m || req.method !== 'PUT') { res.statusCode = 404; return res.end(); }
const tech = Number(req.headers['x-demo-user']);
if (!tech) { res.statusCode = 401; return res.end(); }
let raw = ''; for await (const c of req) raw += c;
let b; try { b = JSON.parse(raw); } catch { res.statusCode = 400; return res.end(); }
if (typeof b.site !== 'string' || !b.site || typeof b.answers !== 'object' || isNaN(Date.parse(b.completed_at))) {
res.statusCode = 422; return res.end(JSON.stringify({ error: 'invalid_checklist' }));
}
seen.set(m[1], (seen.get(m[1]) || 0) + 1);
const hash = crypto.createHash('sha256').update(JSON.stringify([b.site, b.answers, b.completed_at])).digest('hex');
const ins = await pool.query(
`INSERT INTO inspections (id, technician_id, site, answers, payload_hash, completed_at)
VALUES ($1,$2,$3,$4,$5,$6) ON CONFLICT (id) DO NOTHING RETURNING id`,
[m[1], tech, b.site, b.answers, hash, b.completed_at]);
let status = 201;
if (ins.rowCount === 0) { // this id was saved before: replay or clash?
const { rows: [old] } = await pool.query('SELECT payload_hash, technician_id FROM inspections WHERE id=$1', [m[1]]);
status = old.payload_hash === hash && old.technician_id === tech ? 200 : 409;
}
if (faults.loseResponses > 0) { faults.loseResponses--; return req.socket.destroy(); } // saved, but the answer never arrives
res.statusCode = status; res.setHeader('content-type', 'application/json'); res.end(JSON.stringify({ id: m[1] }));
});
srv.seen = seen; return srv;
}
// run.mjs
import pg from 'pg';
import fs from 'node:fs';
import { chromium } from 'playwright';
import { makeServer } from './server.mjs';
const pool = new pg.Pool({ host: 'demo-pg', user: 'postgres', password: 'pw', database: 'postgres' });
await pool.query(fs.readFileSync('schema.sql', 'utf8'));
const faults = { loseResponses: 1 };
const srv = makeServer(pool, faults).listen(3200);
const count = async () => (await pool.query('SELECT count(*)::int n FROM inspections')).rows[0].n;
const idb = (page) => page.evaluate(() => new Promise((ok) => {
const r = indexedDB.open('inspections', 1);
r.onsuccess = () => { const q = r.result.transaction('outbox').objectStore('outbox').getAll(); q.onsuccess = () => ok(q.result.map((i) => i.state + ':' + i.attempts)); };
}));
const text = (page) => page.locator('#status').innerText();
const browser = await chromium.launch();
const ctx = await browser.newContext();
await ctx.addInitScript(() => { window.OUTBOX_RETRY = { base: 40, cap: 200 }; });
const page = await ctx.newPage();
await page.goto('http://127.0.0.1:3200/');
// 1. Technician is in the basement: no signal
await page.route('**/v1/**', (r) => r.abort('internetdisconnected'));
await page.fill('#site', 'Plant room B2'); await page.check('#q1');
await page.click('#submit');
const shown = await (await page.waitForFunction(() => {
const t = document.getElementById('status').innerText;
return t.includes('retry automatically') ? t : false;
})).jsonValue();
console.log('1 offline submit | screen:', shown, '| server rows:', await count(), '| tablet outbox:', (await idb(page)).length);
// 2. Page is reloaded (or the app killed) while still offline: the checklist survives
await page.reload();
await page.waitForFunction(() => document.getElementById('status').innerText.length > 0);
console.log('2 reload offline | tablet outbox kept:', (await idb(page)).length === 1, '| server rows:', await count());
// 3. Signal returns, but the first success response is lost after the server saved it
await page.unroute('**/v1/**');
await page.waitForFunction(() => document.getElementById('status').innerText.startsWith('Sent'));
const id = [...srv.seen.keys()][0];
console.log('3 signal back | screen:', await text(page), '| server rows:', await count(), '| server received the same id more than once:', srv.seen.get(id) > 1, '| tablet outbox:', (await idb(page)).length);
// 4. Same id, different content is a clash, not a replay
const clash = await fetch(`http://127.0.0.1:3200/v1/inspections/${id}`, { method: 'PUT', headers: { 'content-type': 'application/json', 'x-demo-user': '7' },
body: JSON.stringify({ site: 'Other site', answers: {}, completed_at: new Date().toISOString() }) });
const bad = await fetch(`http://127.0.0.1:3200/v1/inspections/${id}`, { method: 'PUT', headers: { 'content-type': 'application/json', 'x-demo-user': '7' }, body: JSON.stringify({ site: '' }) });
console.log('4 same id, other body ->', clash.status, '| empty site ->', bad.status, '| server rows:', await count());
await browser.close(); srv.close(); await pool.end();
Run in the mcr.microsoft.com/playwright:v1.48.2-jammy container against postgres:16, it prints:
1 offline submit | screen: Saved on this tablet. Will retry automatically. | server rows: 0 | tablet outbox: 1
2 reload offline | tablet outbox kept: true | server rows: 0
3 signal back | screen: Sent. The office has your checklist. | server rows: 1 | server received the same id more than once: true | tablet outbox: 0
4 same id, other body -> 409 | empty site -> 422 | server rows: 1
Reading it: in step 1 the API is blocked, the checklist is on the tablet (outbox 1) and not on the server (0 rows). Step 2 reloads the page while still blocked and the record survives. In step 3 the block is lifted, and the server is told to save the first request and then drop the connection before replying, so the tablet never learns it succeeded. The tablet retries the same id; the server received it more than once yet holds one row, and the screen ends on "Sent". Step 4 shows the two refusals: same id with different content gets 409, an empty site gets 422. The four lines are deterministic: they print the same way on repeated runs, because every step waits for a specific screen text or response rather than for a fixed delay, and step 1 prints the text it matched rather than re-reading the screen afterwards (the retry timer can already have changed the screen to "Sending..." by then). The driver overrides the retry delays to a base of 40 ms and a cap of 200 ms (window.OUTBOX_RETRY) so the demo finishes in seconds; the shipped defaults are the 2 s and 300 s of the table above.
The concurrent case, two requests for one id at the same instant, is shown with two database sessions (race.sh, executed inside the Postgres container as the last command of the Running the code section):
P="psql -U postgres -qAt"
$P -c "TRUNCATE inspections"
INS="INSERT INTO inspections (id, technician_id, site, answers, payload_hash, completed_at) VALUES ('11111111-1111-1111-1111-111111111111', 7, 'Plant room B2', '{}', 'h', now()) ON CONFLICT (id) DO NOTHING RETURNING 'inserted'"
TS="SELECT extract(epoch FROM clock_timestamp())"
printf "BEGIN;\n$INS;\nSELECT pg_sleep(1.5);\n$TS;\nCOMMIT;\n" | $P > a.out &
sleep 0.5
$P -c "$INS; $TS" > b.out
wait
echo "session A rows inserted: $(grep -c inserted a.out)"
echo "session B rows inserted: $(grep -c inserted b.out)"
echo "session B returned after A reached its commit: $(awk 'NR==FNR{ if ($1+0>1e9) a=$1; next } { if ($1+0>1e9) b=$1 } END { print (b>a) ? "true" : "false" }' a.out b.out)"
$P -c "SELECT 'rows in table: ' || count(*) FROM inspections"
session A rows inserted: 1
session B rows inserted: 0
session B returned after A reached its commit: true
rows in table: 1
How race.sh proves it, line by line: it empties the table, then starts session A, which opens a transaction, inserts the row, sleeps 1.5 s with pg_sleep and records a timestamp just before committing. Half a second later session B runs the same insert and records a timestamp when it returns. The shell then prints how many rows each session inserted, whether B's timestamp is later than A's (it can only be later if B waited for A's commit), and the row count. Session B's insert waited for session A to commit, then skipped, so the table holds one row. Without the unique key, nothing would make session B wait or skip, and both sessions could insert the same checklist.
Design choices and what would change them
- Client-generated id plus
PUT, versus anIdempotency-Keyheader onPOST. Either gives retries a stable identity; the path id makes the server record itself addressable and needs no key-expiry table. If the API must stayPOST /inspectionsfor other clients, keep a separate keys table with an expiry and store the first response to replay it. - Tablet clock.
completed_atcomes from the tablet and can be wrong;received_atis the server's. Show the technician the tablet time, sort and audit by the server time, and do not usecompleted_atfor anything where being wrong has consequences. - Large queues. Send oldest first and one at a time, so a slow connection finishes whole checklists rather than starting many.
- Authentication expiring while offline. The
401branch inflush()keeps the recordqueuedand prompts for sign-in; the run above does not trigger it. The demo's header user id stands in for a session.
Running the code
cd work # holds outbox.js index.html server.mjs run.mjs schema.sql race.sh
echo '{"type":"module","dependencies":{"pg":"8.13.1","playwright":"1.48.2"}}' > package.json
docker network create demo-net
docker run -d --rm --name demo-pg --network demo-net -e POSTGRES_PASSWORD=pw postgres:16
until docker exec demo-pg pg_isready -h 127.0.0.1 -q; do sleep 1; done
docker run --rm --ipc=host --network demo-net -v "$PWD":/w -w /w mcr.microsoft.com/playwright:v1.48.2-jammy sh -c 'npm i --silent && node run.mjs'
docker cp race.sh demo-pg:/race.sh && docker exec -w /tmp demo-pg bash /race.sh
Take the follow button on a profile page from tap to database row. Cover what the client shows, the API calls involved, how follows are stored, and what happens when a user double-taps, two people follow the same account at once, or the account has millions of followers.
Sample Answer
Direct answer
A follow is one row in an edge table (a table whose rows are the links between two things, here two users), follows(follower_id, followee_id), with a composite primary key (a key made of two columns together) so the same pair can exist only once. The button flips at once on tap (an optimistic update), sends an idempotent "make this follow exist" request, and settles to the {following, follower_count} the server returns. A double tap and a retry are harmless because the database rejects the duplicate row. Two people following at once are safe because each inserts a different row and the counter increments are serialised (made to run one after another) by the row lock (while one transaction is changing a row, any other transaction that wants to change that same row waits until the first one commits; a transaction is a group of statements that succeed or fail together). A million-follower account needs a different counter (spread across several rows) and cursor-based follower lists (each page asks for "rows after the last one you saw" instead of "skip N rows"), but the same edge table.
Tap to row, layer by layer
| Layer | Decision |
|---|---|
| UI | Two button states, Follow and Following. A tap shows the new state immediately and the button stays tappable, so there is no separate spinner state. The profile payload carries viewer_follows so the first paint is right. Unfollow asks for confirmation only on private accounts or on a long press, never on a plain tap. |
| API | PUT /v1/users/{id}/follow and DELETE /v1/users/{id}/follow, no body, both answer 200 {"following": true, "follower_count": 201}. GET /v1/users/{id}/followers?limit=50&cursor=... lists followers. Following yourself returns 422. |
| Logic | One transaction: insert or delete the edge, then move the two counters by the number of rows that changed. |
| Data | follows edge table plus denormalised counters (a stored copy of a count, kept so profile reads do not run count(*)). |
The client logic is the same single-request-in-flight state machine used for a like button: taps while a request is pending only change what the user wants, one follow-up request goes out if the answer disagrees, and a failure restores the last confirmed state.
Schema and the write path
CREATE TABLE users (
id bigint PRIMARY KEY,
follower_count integer NOT NULL DEFAULT 0 CHECK (follower_count >= 0),
following_count integer NOT NULL DEFAULT 0 CHECK (following_count >= 0)
);
CREATE TABLE follows (
follower_id bigint NOT NULL REFERENCES users(id),
followee_id bigint NOT NULL REFERENCES users(id),
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (follower_id, followee_id),
CHECK (follower_id <> followee_id)
);
CREATE INDEX follows_by_followee ON follows (followee_id, created_at DESC, follower_id);
CREATE FUNCTION set_follow(p_me bigint, p_target bigint, p_follow boolean)
RETURNS TABLE (out_following boolean, out_followers integer)
LANGUAGE plpgsql AS $$
DECLARE changed integer;
BEGIN
PERFORM 1 FROM users WHERE id IN (p_me, p_target) ORDER BY id FOR UPDATE;
IF p_follow THEN
INSERT INTO follows (follower_id, followee_id) VALUES (p_me, p_target) ON CONFLICT DO NOTHING;
ELSE
DELETE FROM follows WHERE follower_id = p_me AND followee_id = p_target;
END IF;
GET DIAGNOSTICS changed = ROW_COUNT;
UPDATE users SET follower_count = follower_count + CASE WHEN p_follow THEN changed ELSE -changed END WHERE id = p_target;
UPDATE users SET following_count = following_count + CASE WHEN p_follow THEN changed ELSE -changed END WHERE id = p_me;
RETURN QUERY SELECT p_follow, u.follower_count FROM users u WHERE u.id = p_target;
END $$;
INSERT INTO users SELECT g FROM generate_series(1, 300) g;
SELECT * FROM set_follow(1, 2, true); -- run twice
SELECT * FROM set_follow(1, 2, false); -- run twice
SELECT * FROM set_follow(5, 5, true);
- The
PERFORM ... ORDER BY id FOR UPDATEline locks both user rows in id order before anything else. Without it, user 1 following user 2 updates row 2 (the target) and then row 1, while user 2 following user 1 updates row 1 and then row 2 (the function updates the target's counter first and the follower's own counter second), and each can wait forever for the other (a deadlock; PostgreSQL aborts one of them). Locking in a fixed order removes that cycle. - Worked timeline of that deadlock with ids, using the unlocked order above: at time 1, A (user 1 follows user 2) locks row 2; at time 1, B (user 2 follows user 1) locks row 1; at time 2, A asks for row 1 and waits for B; at time 2, B asks for row 2 and waits for A. Each holds what the other needs, so neither can move. Sorting by
idmeans both A and B ask for row 1 first, so one simply waits for the other and then continues. ON CONFLICT DO NOTHINGskips the insert if the pair already exists, andGET DIAGNOSTICS changed = ROW_COUNTstores how many rows the insert or delete actually changed (1, or 0 for a repeat). The counters then move bychanged, so a repeat moves no counter.- The index on
(followee_id, created_at DESC, follower_id)serves the follower list, and the primary key serves "does the viewer follow this account?" with a single lookup.
Run in a postgres:16 container, the double tap prints follower counts t | 1, t | 1, f | 0, f | 0, and the self follow fails with violates check constraint "follows_check".
Double tap and simultaneous followers
Two taps from one user: the second INSERT waits for the first transaction, hits the primary key, does nothing, and returns the same answer. Two different users: their rows differ, so both inserts succeed, and the two UPDATE users statements queue on the target's row and each adds one to the latest value.
Checked with pgbench (PostgreSQL's load-testing tool; each client is one database session) in the same container. Three small script files: a.sql is SELECT set_follow(1,2,true);, b.sql is SELECT set_follow(2,1,true);, and f.sql is \set u :base + :client_id then SELECT set_follow(:u, 2, true);. Users 1 and 2 follow each other from 40 sessions at once, 50 transactions per session, each picking one of the two directions at random; then 200 different users follow user 2 in five waves of 40 simultaneous sessions. Deadlocks are read from the server's own counter, SELECT deadlocks FROM pg_stat_database WHERE datname = current_database(), before and after:
pgbench -n -c 40 -t 50 -f a.sql -f b.sql demo
for base in 3 43 83 123 163; do pgbench -n -c 40 -t 1 -D base=$base -f f.sql demo; done
counter_user2 | edges_user2 | counter_user1 | edges_user1
---------------+-------------+---------------+-------------
201 | 201 | 1 | 1
deadlocks during the run: 0
Reading the columns: counter_user2 is the stored follower count of user 2 and edges_user2 is a live count(*) of rows in follows pointing at user 2; they match at 201 (200 followers plus user 1), which shows no increment was lost. counter_user1 and edges_user1 are the same pair for user 1, who is followed by user 2 only, so both are 1. The repeated mutual follows in both directions are the deadlock trap, and the unchanged deadlock counter shows none occurred, because the fixed lock order removes the cycle. The check can fail: with the PERFORM ... FOR UPDATE line deleted from the function, the mutual-follow run made PostgreSQL abort transactions with deadlock errors. In one measured shorter run (-t 3, 3 transactions per session) the deadlock counter rose by about a hundred, and the full -t 50 run is slow because each deadlock waits out PostgreSQL's 1 second deadlock_timeout before one transaction is aborted; the exact count varies from run to run.
An account with millions of followers
Three things break first, in this order:
- The counter row becomes a queue. (Fan-out, used in point 3, means copying one event to many recipients, for example writing a new post into each follower's feed; a shard is one of several rows or partitions that share the work of one thing.) Every follow of that account updates the same
usersrow, and those updates run one after another. Find out whether it matters by watching lock wait time on that row (how long updates sit waiting for the lock); do not shard before measuring, since most accounts never need it. When it does matter, give the account several counter rows and add to a random one:
CREATE TABLE follower_shards (
user_id bigint NOT NULL, shard smallint NOT NULL, n bigint NOT NULL DEFAULT 0,
PRIMARY KEY (user_id, shard)
);
CREATE FUNCTION bump_followers(p_user bigint) RETURNS void LANGUAGE sql AS $$
INSERT INTO follower_shards (user_id, shard, n)
VALUES (p_user, floor(random() * 16)::smallint, 1)
ON CONFLICT (user_id, shard) DO UPDATE SET n = follower_shards.n + 1;
$$;
SELECT bump_followers(99) FROM generate_series(1, 1000);
SELECT sum(n) AS follower_count, count(*) AS shard_rows, max(n) < 1000 AS spread_over_rows
FROM follower_shards WHERE user_id = 99;
Reading the code: floor(random() * 16)::smallint picks a shard number from 0 to 15 for each call; ON CONFLICT (user_id, shard) DO UPDATE SET n = follower_shards.n + 1 creates that shard's row with n = 1 the first time and otherwise adds 1 to it; the generate_series(1, 1000) call performs 1,000 follows. In the final query, sum(n) is the follower count, count(*) is how many shard rows exist, and max(n) < 1000 is true when no single row holds all 1,000, meaning the load was spread. Run in the same container this prints 1000 | 16 | t: the total is exact and the writes landed on 16 rows (each row holds about 1000/16, roughly 62, so the busiest row's queue is roughly a dozen times shorter than a single counter row's; the split among shards varies per run, the total does not). Reads sum 16 small rows, which is cheap, and the profile can cache the sum for a few seconds. The trade-off is that concurrent writers no longer wait for each other, at the price of an extra read step and a rule that only flagged hot accounts use shards.
- The follower list. Use keyset pagination (also called cursor-based), which means the cursor is the
(created_at, follower_id)of the last row shown and the next page asks for rows after it.OFFSET 5000000has to walk past five million rows; the keyset query jumps straight into the index. - Fan-out. It is tempting to push the new follow (or the followee's later posts) into every follower's stored feed at write time; for a million followers that is a million writes inside one tap. Following must not copy anything to the followee's followers. Whatever builds feeds reads the edge table later, outside this request.
Test, roll out, observe
- Tests: repeat and concurrent calls end with
follower_countequal to the number of edge rows (the check above), a mutual-follow stress test must produce zero deadlock errors, and a Playwright test with the route aborted must show the button reverting. - Roll out: ship the edge table and endpoints first, backfill counters (fill them in for existing data) from
count(*)per user, then switch the profile read, behind a flag. - Observe: lock wait on hot user rows, rate of
422and5xxon the follow endpoints, and a scheduled job that compares each sampled user's counter tocount(*)on the edge table.
Pitfalls
Do not make follow a toggle endpoint, do not read the follower count and then write it back from application code (two requests overwrite each other), and do not shard counters for every account because most accounts never need it.
You built a "redeem a gift code" feature: a form, a POST endpoint, and a table of codes that can each be used once. Which tests would you write at each layer, what would each test mock and what would it run for real, and which single end-to-end test is worth its cost?
Sample Answer
Direct answer
Test each layer for the thing only that layer can get wrong, and mock only the boundary the layer sits on. A mock is a stand-in that returns a pre-written answer instead of doing the real work: a mocked database that always replies "row updated" never enforces anything, while a mocked fetch that replies 409 lets a form test run without a server. The single-use rule lives in the database, so it is tested against a real Postgres with 20 callers at once; a mock cannot catch a race condition (a bug that appears only when two requests overlap in time, because the outcome depends on who gets there first). The endpoint is tested with a real HTTP server and that real database, checking status codes and the exact fields of every response. The form is tested in a simulated browser (jsdom) with the network call faked, because the form's job is to turn each server answer into the right screen. One end-to-end test (a real browser, real server, real database) is worth its cost: redeem a code with a messy-looking input, then redeem it again. It catches the wiring that every other test mocks away, and it covers the success path and the "already used" path in one run.
What to write first
If time is short, write in this order: the database test with simultaneous callers (the only place the money rule is proved), then the API test (the contract), then the component test, then the unit test, and the single end-to-end test last. The order follows risk: a double credit costs real money, a wrong error message costs a support ticket.
What each layer tests, mocks, and runs for real
| Layer | What it proves | Runs for real | Mocked |
|---|---|---|---|
Unit: normalizeCode | " k7qm-2xd4 p9ta " becomes K7QM2XD4P9TA; wrong lengths and symbols give null | the function | nothing |
Database: redeem() | success credits and marks the code; second redeem is 409; unknown is 404; a failed credit puts the code back to unused; 20 simultaneous redeemers produce one winner | Postgres 16, real transactions, real constraints | nothing |
API: POST /v1/gift-codes/redeem | each status (200, 401, 404, 409, 422) is returned with exactly the documented fields | HTTP server, handler, Postgres | the session (a cookie stands in for the login system) |
Component: RedeemForm | success text, specific error text per server answer, empty input never calls the server, double click sends one request, network failure re-enables the button | React component in jsdom, user events | fetch (the network boundary), with fake responses built from the shared contract |
| End to end | the bundled form in Chromium reaches the real endpoint, the cookie travels, the database changes, the second try is refused | everything | nothing except seeding the data |
A seam is a point where one part of the system hands off to the next (browser to server, server to database). Bugs hide at seams, because each side's tests assume the other side behaves. A contract file is one shared description of what a response contains, used by both sides' tests.
Two choices carry most of the value. First, the database tests do not mock the database. The rule "a code can be used once" is enforced by one conditional UPDATE ... WHERE redeemed_at IS NULL inside a transaction (a group of statements that commit together or not at all), and a mock would return whatever the test author told it to. Second, the component tests and the API tests share one contract file, so the fake responses the component is tested against have the same fields as the responses the API test proves the server really sends. If the server renames balance_cents, the API test fails first. If the contract file is then updated to the new name, the component test fails until the form reads it, because the success response it is given comes from the same file.
Why exactly one of 20 simultaneous callers wins
The rule is UPDATE gift_codes SET redeemed_at = now(), ... WHERE code_hash = $2 AND redeemed_at IS NULL. Take two callers, A and B, arriving at the same instant:
- A and B each start a transaction and send the
UPDATE. Both find the one matching row, because both seeredeemed_atasNULL. - The database lets one of them lock that row first. Say A does. A writes
redeemed_atand holds the lock until its transaction commits. - B now wants the same row, so PostgreSQL makes B wait. It does not fail B and it does not let B write.
- A commits. The row now has a
redeemed_atvalue. - B wakes up and re-evaluates its
WHEREclause against the committed row. The PostgreSQL manual describes this for the default isolation level (Read Committed): the waiting updater re-checks the search condition on the updated version of the row and skips the row if it no longer matches.redeemed_at IS NULLis now false, so B updates zero rows. - Zero rows means B is not the winner, and
redeem()answers409. With 20 callers the same thing repeats: one holds the lock, the other 19 wait, re-check and match nothing.
That is the whole mechanism: the database serialises writers on the row, and the IS NULL test is repeated after the wait. The check and the write are one statement, so there is no gap between them. A race is exactly such a gap: code that looks first and writes afterwards leaves time in between for another request to change the answer.
The naive version leaves that gap. Two callers, A and B, with the 50 ms pause where other work happens:
| Time | Caller A | Caller B |
|---|---|---|
| t0 | SELECT: code is unused | SELECT: code is unused |
| t1 | (pause) | (pause) |
| t2 | UPDATE marks the code used, credits $25.00 | UPDATE marks the code used again, credits $25.00 |
Both decisions were made at t0 on information that stopped being true at t2. With 20 callers all 20 pass the check, which is the "20 winners" in the output below.
The feature under test
DROP TABLE IF EXISTS gift_codes, users;
CREATE TABLE users (
id integer PRIMARY KEY,
balance_cents integer NOT NULL DEFAULT 0 CHECK (balance_cents BETWEEN 0 AND 100000) -- wallet limit: $1,000.00
);
CREATE TABLE gift_codes (
code_hash text PRIMARY KEY, -- sha256 of the normalised code, never the code itself
value_cents integer NOT NULL CHECK (value_cents > 0),
redeemed_by integer REFERENCES users(id),
redeemed_at timestamptz,
CHECK ((redeemed_by IS NULL) = (redeemed_at IS NULL)) -- a code is either unused or fully recorded as used
);
Only a hash of each code is stored, so a leaked table does not leak usable codes. The wallet limit (BETWEEN 0 AND 100000) is $1,000.00 in cents. The CHECK that ties redeemed_by and redeemed_at together means a code can never be half recorded as used. The wallet limit on balance_cents gives the "crediting fails" test something real to trip.
// redeem.mjs
import crypto from 'node:crypto';
// "k7qm-2xd4 p9ta " -> "K7QM2XD4P9TA"; null when it cannot be a code (12 letters or digits).
export function normalizeCode(raw) {
if (typeof raw !== 'string') return null;
const code = raw.replace(/[\s-]/g, '').toUpperCase();
return /^[A-Z0-9]{12}$/.test(code) ? code : null;
}
const hash = (code) => crypto.createHash('sha256').update(code).digest('hex');
const outcome = (status, body) => ({ status, body });
export async function redeem(pool, userId, raw) {
const code = normalizeCode(raw);
if (!code) return outcome(422, { error: 'invalid_code_format' });
const client = await pool.connect();
try {
await client.query('BEGIN');
// The single-use rule lives here: only one caller can flip redeemed_at from NULL.
const used = await client.query(
`UPDATE gift_codes SET redeemed_by = $1, redeemed_at = now()
WHERE code_hash = $2 AND redeemed_at IS NULL RETURNING value_cents`, [userId, hash(code)]);
if (used.rowCount === 0) {
await client.query('ROLLBACK');
const known = await pool.query('SELECT 1 FROM gift_codes WHERE code_hash = $1', [hash(code)]);
return known.rowCount ? outcome(409, { error: 'code_already_redeemed' }) : outcome(404, { error: 'code_not_found' });
}
const credited = await client.query(
'UPDATE users SET balance_cents = balance_cents + $2 WHERE id = $1 RETURNING balance_cents',
[userId, used.rows[0].value_cents]);
await client.query('COMMIT');
return outcome(200, { added_cents: used.rows[0].value_cents, balance_cents: credited.rows[0].balance_cents });
} catch (err) {
await client.query('ROLLBACK').catch(() => {});
throw err;
} finally {
client.release();
}
}
// The tempting version: look first, then write. A pause between the two stands for
// "call the fraud service" or any other work that sits between the check and the write.
export async function redeemNaive(pool, userId, raw) {
const code = normalizeCode(raw);
if (!code) return outcome(422, { error: 'invalid_code_format' });
const { rows: [c] } = await pool.query('SELECT value_cents, redeemed_at FROM gift_codes WHERE code_hash = $1', [hash(code)]);
if (!c) return outcome(404, { error: 'code_not_found' });
if (c.redeemed_at) return outcome(409, { error: 'code_already_redeemed' });
await pool.query('SELECT pg_sleep(0.05)');
await pool.query('UPDATE gift_codes SET redeemed_by = $1, redeemed_at = now() WHERE code_hash = $2', [userId, hash(code)]);
const r = await pool.query('UPDATE users SET balance_cents = balance_cents + $2 WHERE id = $1 RETURNING balance_cents', [userId, c.value_cents]);
return outcome(200, { added_cents: c.value_cents, balance_cents: r.rows[0].balance_cents });
}
redeem() does one thing in one place: the first statement flips redeemed_at from NULL only for the caller that gets there first; the others match zero rows and are told 409 (code exists) or 404 (it does not). The credit is in the same transaction, so a failure there undoes the "used" mark. redeemNaive() is the tempting alternative (read the row, see it unused, then write); it is included only so the test can be shown failing.
// contract.mjs
// What the endpoint answers. The API test checks real responses against these key lists;
// the component test builds its fake responses from the same table.
export const CONTRACT = {
200: { keys: ['added_cents', 'balance_cents'], example: { added_cents: 2500, balance_cents: 4000 } },
401: { keys: ['error'], error: 'sign_in_required' },
404: { keys: ['error'], error: 'code_not_found' },
409: { keys: ['error'], error: 'code_already_redeemed' },
422: { keys: ['error'], error: 'invalid_code_format' },
};
// server.mjs
import http from 'node:http';
import fs from 'node:fs';
import { redeem } from './redeem.mjs';
export function makeServer(pool, { staticDir } = {}) {
return http.createServer(async (req, res) => {
const send = (status, body, type = 'application/json') => {
res.writeHead(status, { 'content-type': type });
res.end(typeof body === 'string' ? body : JSON.stringify(body));
};
if (staticDir && req.method === 'GET') {
const f = req.url === '/' ? 'index.html' : req.url.slice(1);
if (fs.existsSync(`${staticDir}/${f}`)) return send(200, fs.readFileSync(`${staticDir}/${f}`, 'utf8'), f.endsWith('.js') ? 'text/javascript' : 'text/html');
}
if (req.method !== 'POST' || req.url !== '/v1/gift-codes/redeem') return send(404, { error: 'not_found' });
const userId = Number(/demo_session=(\d+)/.exec(req.headers.cookie || '')?.[1]); // stands in for the real session lookup
if (!userId) return send(401, { error: 'sign_in_required' });
let raw = ''; for await (const c of req) raw += c;
let body; try { body = JSON.parse(raw); } catch { body = {}; }
try {
const r = await redeem(pool, userId, body.code);
send(r.status, r.body);
} catch { send(500, { error: 'server_error' }); }
});
}
// RedeemForm.jsx
import { useState } from 'react';
const MESSAGES = {
invalid_code_format: 'That does not look like a gift code. It has 12 letters and numbers.',
code_not_found: 'We could not find that code. Check it and try again.',
code_already_redeemed: 'This code has already been used.',
sign_in_required: 'Please sign in again to redeem a code.',
};
const dollars = (cents) => `$${(cents / 100).toFixed(2)}`;
export function RedeemForm() {
const [code, setCode] = useState('');
const [view, setView] = useState({ kind: 'idle' }); // idle | submitting | success | error
async function submit(event) {
event.preventDefault();
if (!code.trim()) { setView({ kind: 'error', message: 'Enter the code from your gift card.' }); return; }
setView({ kind: 'submitting' });
try {
const res = await fetch('/v1/gift-codes/redeem', {
method: 'POST', headers: { 'content-type': 'application/json' }, body: JSON.stringify({ code }) });
const body = await res.json();
if (res.ok) { setView({ kind: 'success', added: body.added_cents, balance: body.balance_cents }); setCode(''); }
else setView({ kind: 'error', message: MESSAGES[body.error] ?? 'Something went wrong. Please try again.' });
} catch {
setView({ kind: 'error', message: 'We could not reach the server. Check your connection and try again.' });
}
}
return (
<form onSubmit={submit}>
<label htmlFor="code">Gift code</label>
<input id="code" value={code} onChange={(e) => setCode(e.target.value)} autoComplete="off" />
<button type="submit" disabled={view.kind === 'submitting'}>
{view.kind === 'submitting' ? 'Redeeming...' : 'Redeem'}
</button>
{view.kind === 'error' && <p role="alert">{view.message}</p>}
{view.kind === 'success' && <p role="status">Added {dollars(view.added)}. Your balance is {dollars(view.balance)}.</p>}
</form>
);
}
The tests
What the trickier tests set up, and what a failure would mean:
test.eachtable (API test). One test body runs four times, once per row[expected status, request body, session]. A failure names the row, so you see which status code broke. Each run also compares the response's field names to the contract list, which catches a renamed field.- Double-click test (component).
fetchis replaced byvi.fn(...), a spy function that records its calls (toHaveBeenCalledTimes(1)reads that record). It returns a promise that stays unresolved until the test callsrelease(). Holding the answer back keeps the form in its "submitting" state, so the test can double-click and prove the second click does nothing. If the button were not disabled,fetchwould be called twice and the test fails. Afterrelease()the test waits for the success message so nothing is left running. mockResolvedValueandmockRejectedValue. The first makes the fakefetchreturn a ready answer (a server reply); the second makes it throw (no network).findByRole/getByRole. They look elements up the way a user and a screen reader find them: astatusis a polite announcement (success text), analertis an urgent one (error text), and a button is found by its visible name.findBywaits for the element to appear;getByexpects it to be there already.- Crediting-fails test (database). It sets a balance of $990.00 so the $25.00 credit would pass the $1,000.00 wallet limit. The database rejects the credit, and the test checks the code is back to unused. If the code stayed marked used, a customer would lose a code and get nothing.
redeemNaiveandpg_sleep(0.05).pg_sleeppauses the database session for 50 ms; it stands in for work that sits between looking and writing.
Unit, with no setup:
// unit.test.mjs
import { test, expect } from 'vitest';
import { normalizeCode } from './redeem.mjs';
test('normalises case, spaces and hyphens', () => {
expect(normalizeCode(' k7qm-2xd4 p9ta ')).toBe('K7QM2XD4P9TA');
});
test('rejects wrong length, symbols and non-strings', () => {
for (const bad of ['', 'K7QM2XD4P9T', 'K7QM2XD4P9TAA', 'K7QM2XD4P9T!', null, 42]) expect(normalizeCode(bad)).toBeNull();
});
Database, against real Postgres, including the concurrency test:
// db.test.mjs
import { test, expect, beforeAll, beforeEach, afterAll } from 'vitest';
import pg from 'pg';
import fs from 'node:fs';
import crypto from 'node:crypto';
import * as impl from './redeem.mjs';
const redeem = process.env.IMPL === 'naive' ? impl.redeemNaive : impl.redeem;
const pool = new pg.Pool({ host: 'demo-pg', user: 'postgres', password: 'pw', database: 'postgres', max: 30 });
const CODE = 'K7QM2XD4P9TA';
const h = crypto.createHash('sha256').update(CODE).digest('hex');
const state = async () => ({
code: (await pool.query('SELECT redeemed_by FROM gift_codes WHERE code_hash=$1', [h])).rows[0].redeemed_by,
credited: (await pool.query('SELECT coalesce(sum(balance_cents),0)::int AS s FROM users')).rows[0].s,
});
beforeAll(async () => { await pool.query(fs.readFileSync('schema.sql', 'utf8')); });
beforeEach(async () => {
await pool.query('TRUNCATE gift_codes, users CASCADE');
await pool.query('INSERT INTO users SELECT g, 0 FROM generate_series(1, 20) g');
await pool.query('INSERT INTO gift_codes (code_hash, value_cents) VALUES ($1, 2500)', [h]);
});
afterAll(() => pool.end());
test('redeeming credits the user and marks the code used', async () => {
const r = await redeem(pool, 1, 'k7qm-2xd4-p9ta');
expect(r).toEqual({ status: 200, body: { added_cents: 2500, balance_cents: 2500 } });
expect(await state()).toEqual({ code: 1, credited: 2500 });
});
test('a second redeem is 409 and credits nothing more', async () => {
await redeem(pool, 1, CODE);
expect((await redeem(pool, 2, CODE)).status).toBe(409);
expect(await state()).toEqual({ code: 1, credited: 2500 });
});
test('an unknown code is 404', async () => {
expect((await redeem(pool, 1, 'AAAAAAAAAAAA')).status).toBe(404);
});
test('if crediting fails, the code goes back to unused', async () => {
await pool.query('UPDATE users SET balance_cents = 99000 WHERE id = 20'); // 99000 + 2500 would pass the 100000 wallet limit
await expect(redeem(pool, 20, CODE)).rejects.toThrow('users_balance_cents_check');
expect(await state()).toEqual({ code: null, credited: 99000 });
});
test('20 users redeem the same code at once: exactly one wins and 2500 is credited once', async () => {
const results = await Promise.all(Array.from({ length: 20 }, (_, i) => redeem(pool, i + 1, CODE)));
const winners = results.filter((r) => r.status === 200).length;
expect(winners, `winners (expected 1)`).toBe(1);
expect(results.filter((r) => r.status === 409)).toHaveLength(19);
expect((await state()).credited).toBe(2500);
});
API, real server and database, checking the contract:
// api.test.mjs
import { test, expect, beforeAll, beforeEach, afterAll } from 'vitest';
import pg from 'pg';
import fs from 'node:fs';
import crypto from 'node:crypto';
import { makeServer } from './server.mjs';
import { CONTRACT } from './contract.mjs';
const pool = new pg.Pool({ host: 'demo-pg', user: 'postgres', password: 'pw', database: 'postgres' });
const h = crypto.createHash('sha256').update('K7QM2XD4P9TA').digest('hex');
let srv, base;
const post = async (body, session = 7) => {
const r = await fetch(`${base}/v1/gift-codes/redeem`, { method: 'POST',
headers: { 'content-type': 'application/json', ...(session ? { cookie: `demo_session=${session}` } : {}) }, body: JSON.stringify(body) });
return { status: r.status, body: await r.json() };
};
beforeAll(async () => {
await pool.query(fs.readFileSync('schema.sql', 'utf8'));
srv = makeServer(pool).listen(0); base = `http://127.0.0.1:${srv.address().port}`;
});
beforeEach(async () => {
await pool.query('TRUNCATE gift_codes, users CASCADE');
await pool.query('INSERT INTO users VALUES (7, 0)');
await pool.query('INSERT INTO gift_codes (code_hash, value_cents) VALUES ($1, 2500)', [h]);
});
afterAll(async () => { srv.close(); await pool.end(); });
test.each([
[200, { code: 'k7qm-2xd4-p9ta' }, 7],
[401, { code: 'K7QM2XD4P9TA' }, null],
[404, { code: 'AAAAAAAAAAAA' }, 7],
[422, { code: 'nope' }, 7],
])('%i response has exactly the documented fields', async (status, body, session) => {
const r = await post(body, session);
expect(r.status).toBe(status);
expect(Object.keys(r.body).sort()).toEqual(CONTRACT[status].keys);
if (CONTRACT[status].error) expect(r.body.error).toBe(CONTRACT[status].error);
});
test('409 after the code is used, with the documented fields', async () => {
await post({ code: 'K7QM2XD4P9TA' });
const r = await post({ code: 'K7QM2XD4P9TA' });
expect(r.status).toBe(409);
expect(Object.keys(r.body).sort()).toEqual(CONTRACT[409].keys);
});
Component, with only fetch faked:
// component.test.jsx
// @vitest-environment jsdom
import { test, expect, vi, afterEach } from 'vitest';
import { render, screen, cleanup } from '@testing-library/react';
import userEvent from '@testing-library/user-event';
import { RedeemForm } from './RedeemForm.jsx';
import { CONTRACT } from './contract.mjs';
afterEach(() => { cleanup(); vi.restoreAllMocks(); });
const reply = (status, body) => vi.fn().mockResolvedValue({ ok: status < 300, status, json: async () => body });
const errorBody = (status) => ({ error: CONTRACT[status].error });
test('success shows the amount added and the new balance', async () => {
globalThis.fetch = reply(200, CONTRACT[200].example);
render(<RedeemForm />);
await userEvent.type(screen.getByLabelText('Gift code'), 'K7QM2XD4P9TA');
await userEvent.click(screen.getByRole('button', { name: 'Redeem' }));
expect((await screen.findByRole('status')).textContent).toBe('Added $25.00. Your balance is $40.00.');
});
test('an already used code shows a specific message', async () => {
globalThis.fetch = reply(409, errorBody(409));
render(<RedeemForm />);
await userEvent.type(screen.getByLabelText('Gift code'), 'K7QM2XD4P9TA');
await userEvent.click(screen.getByRole('button', { name: 'Redeem' }));
expect((await screen.findByRole('alert')).textContent).toBe('This code has already been used.');
});
test('an empty box never calls the server', async () => {
globalThis.fetch = vi.fn();
render(<RedeemForm />);
await userEvent.click(screen.getByRole('button', { name: 'Redeem' }));
expect((await screen.findByRole('alert')).textContent).toBe('Enter the code from your gift card.');
expect(globalThis.fetch).not.toHaveBeenCalled();
});
test('a double click sends one request and the button is disabled while it is pending', async () => {
let release;
globalThis.fetch = vi.fn(() => new Promise((ok) => { release = () => ok({ ok: true, status: 200, json: async () => CONTRACT[200].example }); }));
render(<RedeemForm />);
await userEvent.type(screen.getByLabelText('Gift code'), 'K7QM2XD4P9TA');
const button = screen.getByRole('button', { name: 'Redeem' });
await userEvent.dblClick(button);
expect(globalThis.fetch).toHaveBeenCalledTimes(1);
expect(screen.getByRole('button', { name: 'Redeeming...' }).disabled).toBe(true);
release();
await screen.findByRole('status');
});
test('a network failure shows a retry message and re-enables the button', async () => {
globalThis.fetch = vi.fn().mockRejectedValue(new TypeError('Failed to fetch'));
render(<RedeemForm />);
await userEvent.type(screen.getByLabelText('Gift code'), 'K7QM2XD4P9TA');
await userEvent.click(screen.getByRole('button', { name: 'Redeem' }));
expect((await screen.findByRole('alert')).textContent).toContain('could not reach the server');
expect(screen.getByRole('button', { name: 'Redeem' }).disabled).toBe(false);
});
Run with Vitest 2.1.8 in a node:22 container against postgres:16, the suite prints:
✓ component.test.jsx > success shows the amount added and the new balance
✓ component.test.jsx > an already used code shows a specific message
✓ component.test.jsx > an empty box never calls the server
✓ component.test.jsx > a double click sends one request and the button is disabled while it is pending
✓ component.test.jsx > a network failure shows a retry message and re-enables the button
✓ db.test.mjs > redeeming credits the user and marks the code used
✓ db.test.mjs > a second redeem is 409 and credits nothing more
✓ db.test.mjs > an unknown code is 404
✓ db.test.mjs > if crediting fails, the code goes back to unused
✓ db.test.mjs > 20 users redeem the same code at once: exactly one wins and 2500 is credited once
✓ api.test.mjs > 200 response has exactly the documented fields
✓ api.test.mjs > 401 response has exactly the documented fields
✓ api.test.mjs > 404 response has exactly the documented fields
✓ api.test.mjs > 422 response has exactly the documented fields
✓ api.test.mjs > 409 after the code is used, with the documented fields
✓ unit.test.mjs > normalises case, spaces and hyphens
✓ unit.test.mjs > rejects wrong length, symbols and non-strings
Test Files 4 passed (4)
Tests 17 passed (17)
The suite is deterministic: it passes 17 of 17 on repeated runs. A safety net should be seen failing once. Pointing the database tests at redeemNaive (IMPL=naive) gives, in one sample run (the number of winners varies with timing, but the pause in the naive version makes more than one winner the usual outcome):
× db.test.mjs > 20 users redeem the same code at once: exactly one wins and 2500 is credited once
→ winners (expected 1): expected 20 to be 1 // Object.is equality
All 20 callers were told "success", so 20 credits of $25.00 went out for one gift card. A test with a mocked database would have passed this code. (expected 20 to be 1 // Object.is equality is Vitest's way of saying the winners count was 20 and the test required exactly 1.) The same naive run also fails the "crediting fails" test, because the naive version has no transaction to undo.
Setup files the suite needs (save the JSON block below as package.json; JSON allows no comment line, so it has none). jsx: 'automatic' tells the build tool (esbuild) that JSX can be used without importing React in each file; fileParallelism: false makes Vitest run one test file at a time because the database tests share one database; jsdom is a simulated browser (a DOM, the page's element tree, running inside Node) so component tests need no real browser.
// vitest.config.mjs
export default {
esbuild: { jsx: 'automatic' }, // lets .jsx files use JSX without importing React
test: { fileParallelism: false }, // the DB tests share one database
};
{"type": "module", "devDependencies": {"vitest": "2.1.8", "jsdom": "25.0.1", "react": "18.3.1", "react-dom": "18.3.1", "@testing-library/react": "16.0.1", "@testing-library/dom": "10.4.0", "@testing-library/user-event": "14.5.2", "pg": "8.13.1", "playwright": "1.48.2", "esbuild": "0.24.0"}}
The one end-to-end test
// e2e.mjs
import pg from 'pg';
import fs from 'node:fs';
import crypto from 'node:crypto';
import { chromium } from 'playwright';
import { makeServer } from './server.mjs';
const pool = new pg.Pool({ host: 'demo-pg', user: 'postgres', password: 'pw', database: 'postgres' });
await pool.query(fs.readFileSync('schema.sql', 'utf8'));
await pool.query('INSERT INTO users VALUES (7, 1500)');
await pool.query('INSERT INTO gift_codes (code_hash, value_cents) VALUES ($1, 2500)', [crypto.createHash('sha256').update('K7QM2XD4P9TA').digest('hex')]);
const srv = makeServer(pool, { staticDir: 'static' }).listen(3300);
const balance = async () => (await pool.query('SELECT balance_cents FROM users WHERE id = 7')).rows[0].balance_cents;
const browser = await chromium.launch();
const ctx = await browser.newContext();
await ctx.addCookies([{ name: 'demo_session', value: '7', url: 'http://127.0.0.1:3300' }]);
const page = await ctx.newPage();
await page.goto('http://127.0.0.1:3300/');
await page.getByLabel('Gift code').fill('k7qm-2xd4-p9ta'); // typed the way a person copies it
await page.getByRole('button', { name: 'Redeem' }).click();
console.log('first try :', await page.getByRole('status').innerText(), '| database balance:', await balance());
await page.getByLabel('Gift code').fill('K7QM2XD4P9TA');
await page.getByRole('button', { name: 'Redeem' }).click();
console.log('second try:', await page.getByRole('alert').innerText(), '| database balance:', await balance());
await browser.close(); srv.close(); await pool.end();
// main.jsx
import { createRoot } from 'react-dom/client';
import { RedeemForm } from './RedeemForm.jsx';
createRoot(document.getElementById('root')).render(<RedeemForm />);
<!-- static/index.html -->
<!doctype html>
<meta charset="utf-8"><title>Redeem</title>
<div id="root"></div>
<script src="/app.js"></script>
Run in the Playwright 1.48.2 container (Chromium), it prints the same two lines on repeated runs:
first try : Added $25.00. Your balance is $40.00. | database balance: 4000
second try: This code has already been used. | database balance: 4000
The code is typed lowercase with hyphens, the way a person copies it from an email, so the test also confirms normalisation survives the browser and the real endpoint. The balance started at $15.00, so the $40.00 after one redemption and the unchanged database balance after the second try are two independent proofs that the refusal did not credit again.
Why this one and not more: it is the only test that crosses every seam at once (bundle, route, cookie, JSON, database, screen), and these seams are exactly what the unit tests mock out. Every extra browser test (expired code, wrong format) re-proves logic already covered more cheaply and faster lower down, while adding browser start-up, data seeding and flake risk (a flaky test passes on some runs and fails on others with no code change, which erodes trust in the whole suite). What would change the choice: if redemption moves money, add a post-deploy check that redeems a reserved test code in production.
Pitfalls
- Mocking the thing that enforces the rule. A database mock for the single-use rule tests your belief about the database, not the database.
- A concurrency test that cannot fail. One caller after another proves nothing about races; the test must fire simultaneous requests, and must be shown failing against a deliberately broken version.
- Fake responses invented by hand in the component test. They drift from the API. Build them from a shared contract and test the contract against the real server.
- Asserting on implementation. The component tests look for roles and visible text (
status,alert, button names), which is also what a user and a screen reader get. - Brute-force guessing of codes. Rate limiting belongs in front of this endpoint, and the tests above do not exercise it. A separate test would fire many wrong codes from one session and expect a
429once the limit is reached.
Running the code
cd work # every file above; index.html goes in work/static/
docker network create demo-net
docker run -d --rm --name demo-pg --network demo-net -e POSTGRES_PASSWORD=pw postgres:16
until docker exec demo-pg pg_isready -h 127.0.0.1 -q; do sleep 1; done # wait for the image's post-init restart
docker run --rm --network demo-net -v "$PWD":/w -w /w node:22 sh -c 'npm i --silent && npx vitest run --reporter=verbose'
# the failing naive run: add -e IMPL=naive and put db.test.mjs after "vitest run"
docker run --rm --ipc=host --network demo-net -v "$PWD":/w -w /w mcr.microsoft.com/playwright:v1.48.2-jammy \
sh -c 'npm i --silent && npx esbuild main.jsx --bundle --jsx=automatic --outfile=static/app.js && node e2e.mjs'
A product manager asks for "saved searches" on a job-listings site: a signed-in user saves a search and, when they come back, sees how many new jobs match it. Before writing any code, walk me through how you break this into user flows, states and edge cases, and what you would build first in the UI, the API and the database.
Sample Answer
Direct answer
Treat "saved searches" as three small flows on top of the search that already exists: save the current filters under a name, list the saved searches with a "new" count on each, and open one, which runs the search and resets its "new" count to zero. Build one thin vertical slice first, all three layers at once: a saved_searches table that stores the filters plus a "last seen" timestamp, a few endpoints, and a list with badges in the UI. The count is computed on demand as "active jobs that match the filters and were posted after last seen". Do not start with email alerts, background jobs or a counter you store and keep in sync, because none of them is needed to prove the feature.
Step 1: Pin down what "new" means before any code
The word "new" hides the most important decision, so ask the product manager (PM) to settle it first.
- New since when? Recommended answer: new since the user last opened that saved search, with the baseline set to the moment of saving. If the baseline were "since the user last visited the site", every saved search would share one baseline, so visiting the site for any reason would wipe every badge, including for jobs the user never looked at.
- Which jobs count? Only jobs that are still active. An expired posting counted yesterday and gone today makes the badge look broken.
- What is "the search"? A set of structured filters (keywords, location, remote only), not the raw URL. A raw URL breaks the day someone renames a query parameter and cannot be de-duplicated.
Step 2: User flows, states and edge cases
| Flow | Happy path | States the UI needs | Edge cases to decide now |
|---|---|---|---|
| Save | On the results page, a "Save search" button asks for a name (pre-filled from the filters) and stores it | idle, saving, saved, error | Signed-out user clicks it: send them to sign in, then come back with the filters intact and finish the save. The same filters saved twice: do not create a second row. Empty filters: refuse (nothing to watch). Limit per user (say 20): say so plainly |
| List | A "Saved searches" page shows name, filters in words, and a badge such as "3 new" | loading, empty ("You have no saved searches yet" plus how to make one), loaded, error | Count is 0: show no badge. Count is huge: show "99+" rather than an exact number. One count fails to load: show the row without a badge, not an error page |
| Open | Click runs the normal search and sets the baseline to now | the normal results states | Two browser tabs open: the second tab's badge is stale until refreshed, which is acceptable. Deleted search opened from an old link: 404 page with a link back to the list |
| Rename or delete | Inline edit and a delete with undo | saving, error | Deleting while another tab is open on it: the next request returns 404, show "this search no longer exists" |
Every row of that table is a state somebody will hit in the first week, which is why the table is written before any code.
Step 3: What to build first, layer by layer
Database
Two tables: the existing jobs and a new saved_searches. The filters live in a jsonb column (PostgreSQL's binary JSON type) because the set of filters will grow. A criteria_hash column holds a fingerprint of the normalised filters, and a unique constraint on (user_id, criteria_hash) makes a duplicate save impossible even if two requests arrive together. The partial index on jobs lets the count query read only active jobs, newest first.
CREATE TABLE jobs (
id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
title text NOT NULL,
location text NOT NULL,
remote boolean NOT NULL DEFAULT false,
status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','expired')),
created_at timestamptz NOT NULL
);
CREATE INDEX jobs_active_created_idx ON jobs (created_at DESC) WHERE status = 'active';
CREATE TABLE saved_searches (
id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
user_id bigint NOT NULL,
name text NOT NULL CHECK (length(name) BETWEEN 1 AND 80),
criteria jsonb NOT NULL,
criteria_hash text NOT NULL,
last_seen_at timestamptz NOT NULL DEFAULT now(),
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (user_id, criteria_hash)
);
INSERT INTO jobs (title, location, remote, status, created_at) VALUES
('Data Engineer', 'london', false, 'active', '2026-10-01 09:00+00'),
('Senior Data Engineer','london', false, 'active', '2026-10-03 09:00+00'),
('Data Engineer', 'london', true, 'active', '2026-10-04 09:00+00'),
('Data Engineer', 'paris', false, 'active', '2026-10-05 09:00+00'),
('Data Engineer', 'london', false, 'expired', '2026-10-06 09:00+00'),
('Frontend Engineer', 'london', false, 'active', '2026-10-07 09:00+00');
INSERT INTO saved_searches (user_id, name, criteria, criteria_hash, last_seen_at)
VALUES (7, 'DE London', '{"q":"data engineer","location":"london"}', '3cc1be9560295d23', '2026-10-02 12:00+00'),
(7, 'Remote only', '{"remote":true}', 'e77ab421075e7c7e', '2026-10-02 12:00+00');
SELECT s.id, s.name,
(SELECT count(*) FROM (
SELECT 1 FROM jobs j
WHERE j.status = 'active'
AND j.created_at > s.last_seen_at
AND (s.criteria->>'q' IS NULL OR j.title ILIKE '%' || (s.criteria->>'q') || '%')
AND (s.criteria->>'location' IS NULL OR j.location = s.criteria->>'location')
AND (s.criteria->>'remote' IS NULL OR j.remote)
LIMIT 100) c) AS new_matches
FROM saved_searches s WHERE s.user_id = 7 ORDER BY s.id;
INSERT INTO saved_searches (user_id, name, criteria, criteria_hash)
VALUES (7, 'again', '{"q":"data engineer","location":"london"}', '3cc1be9560295d23')
ON CONFLICT (user_id, criteria_hash) DO NOTHING RETURNING id;
UPDATE saved_searches SET last_seen_at = '2026-10-08 08:00+00' WHERE id = 1 AND user_id = 7;
Run in a postgres:16 container, the count query prints two rows: id 1, "DE London", 2 new matches, and id 2, "Remote only", 1 new match. For "DE London" the two matches are the senior role posted on 3 October and the remote role posted on 4 October: the 1 October job is older than the baseline, the Paris job has the wrong location, the 6 October job is expired, and the front-end job has the wrong title. For "Remote only" the single match is the remote role posted on 4 October, because that is the only active remote job posted after the baseline. The second INSERT returns (0 rows) because the same filters were already saved, and the UPDATE (what "open" does) prints UPDATE 1.
Reading the SQL in plain words:
- A partial index is an index that covers only the rows matching a
WHEREcondition.jobs_active_created_idxlists only active jobs, newest first, so the database does not wade through expired ones. - A unique constraint (
UNIQUE (user_id, criteria_hash)) makes the database reject a second row with the same pair of values, whatever the application does. criteria->>'q'reads the field namedqout of thejsonbvalue and returns it as text.ILIKE '%data engineer%'means "contains this text, ignoring upper and lower case" (%stands for any run of characters).ON CONFLICT (user_id, criteria_hash) DO NOTHINGmeans "if this insert would break the unique constraint, skip it quietly instead of raising an error".RETURNING idthen returns nothing, which is how the API knows it was a duplicate.- The count query, read from the inside out: the inner
SELECT 1 ... LIMIT 100finds at most 100 matching new jobs and returns a dummy1per match (only the number of rows matters, not their contents); the outercount(*)counts those rows, so the answer can never exceed 100. The conditions are, in order: the job is active, it was posted after the baselinelast_seen_at, and then one condition per filter that the saved search actually has. Each filter is written asfilter IS NULL OR match, so a filter the user never set (a search saved with only "remote", or with no location) is skipped instead of turning the whole comparison into NULL, which would silently match nothing and show a badge stuck at zero. - The second
INSERTuses the same filters as the first row and the fingerprint the JavaScript below computes for them, which is why the database treats it as a duplicate. The "Remote only" row's fingerprint is the one the JavaScript prints on its last line.
Two details carry the design. LIMIT 100 inside the subquery stops counting at 100, which is what makes a "99+" badge cheap: the database never counts a million rows to display a cap. And WHERE ... AND user_id = 7 on the update means a user can only reset their own baseline.
Making duplicates detectable: normalise before hashing
Two people (or one person twice) type "Data Engineer " and "data engineer". They must produce the same fingerprint, so the server lowercases, trims and collapses spaces, drops empty and false fields, then hashes the sorted result.
import { createHash } from 'node:crypto';
export function normalise(input) {
const q = String(input.q ?? '').trim().toLowerCase().replace(/\s+/g, ' ');
const location = String(input.location ?? '').trim().toLowerCase();
const out = {};
if (q) out.q = q;
if (location) out.location = location;
if (input.remote === true) out.remote = true;
return out;
}
export const hash = (c) => createHash('sha256').update(JSON.stringify(Object.entries(c).sort())).digest('hex').slice(0, 16);
const a = normalise({ q: ' Data Engineer ', location: 'London', remote: false });
const b = normalise({ location: 'london ', q: 'data engineer' });
console.log(JSON.stringify(a), JSON.stringify(b), hash(a) === hash(b));
console.log(Object.keys(normalise({})).length);
console.log(hash(a));
console.log(hash(normalise({ remote: true })));
Run in a node:22 container it prints the same normalised object twice followed by true, then 0, then 3cc1be9560295d23, then the fingerprint for a remote-only search: both spellings collapse to one fingerprint (the same value stored in the SQL above), and a request with no filters normalises to zero fields, which the API rejects.
How the code gets there: normalise trims, lowercases and collapses repeated spaces, and leaves out fields that are empty or false, so equal searches become equal objects. hash turns the object into a list of [key, value] pairs with Object.entries, sorts that list so the order the keys were typed in cannot matter, converts it to text with JSON.stringify, runs it through SHA-256 (a function that turns any text into a fixed-length scrambled string, identical for identical input) and keeps the first 16 characters. A short fingerprint is enough here because it only needs to be unique among one user's searches.
API
| Call | Purpose | Success | Errors |
|---|---|---|---|
POST /v1/saved-searches with {name, criteria} | save | 201 Created for a new one, 200 OK with the existing row if the same filters were already saved | 401 not signed in, 422 empty or invalid filters, 409 the per-user limit is reached |
GET /v1/saved-searches | list with counts | 200 {items:[{id,name,criteria,new_count,capped}]} | 401 |
POST /v1/saved-searches/{id}/open | reset the baseline, return nothing the client does not need | 204 No Content | 401, 404 |
PATCH and DELETE /v1/saved-searches/{id} | rename, delete | 200 / 204 | 401, 404 |
On status codes: MDN defines 201 Created as the request having led to the creation of a resource, 409 Conflict as a conflict with the current state of the target resource (the user is already at their limit), and 422 Unprocessable Content as a request the server understood but could not process (valid JSON, but no usable filters). Every query filters on the signed-in user_id, and another user's id returns 404, not 403, so ids do not reveal what exists.
The reset on "open" is a separate POST, not a side effect of GET. A GET that changes data gets triggered by prefetching (a browser loading a link in advance), link previews (chat apps fetching a link to show a thumbnail) and retries, and would silently clear badges the user never saw.
UI
Build in this order, each step usable on its own:
- The "Save search" button on the results page, with name field and the four states (idle, saving, saved, error).
- The list page with the empty state first (new users see this one), then rows with badges.
- The badge in the main navigation, only after the list works. It is a second place to show the same numbers and can wait.
Show the filters in words ("data engineer in London") beside the name, so a renamed search is still recognisable.
Trade-offs and what is left out on purpose
- Compute the count on read, do not store it. A stored counter must be updated whenever a job is posted, expires, or any search changes, and it drifts. Counting at read time with a cap is always correct. The cost is one capped query per saved search on each list view; with the 20-search limit that is at most 20 cheap, index-assisted queries, which is acceptable at launch. Measure it before changing it. If the list page becomes slow, add a short cache of a few minutes per user, not a stored counter.
- No email or push alerts yet. They need a background job, deduplication of what was already sent, unsubscribe handling and delivery infrastructure, which is a separate project. The data model above (a saved search with a baseline) is exactly what that project would read, so nothing is wasted.
- Structured filters, not free text, for matching. The
ILIKEmatch above is fine for a first version; if the product already has a search engine, the count must call the same code path as the results page, or the badge says 3 while the results page shows 5. - The common wrong turn is starting with the notification system or a clever counter table because they feel like the "real" engineering, then discovering after weeks that nobody agreed what "new" meant.
Running the code
Save the SQL block as saved_searches.sql and the JavaScript block as normalise.mjs (Node 22 reads .mjs files as modules, so no package.json is needed).
docker run -d --rm --name pg-demo -e POSTGRES_PASSWORD=x postgres:16
sleep 8
docker exec -i pg-demo psql -U postgres -v ON_ERROR_STOP=1 < saved_searches.sql
docker stop pg-demo
docker run --rm -v "$PWD":/w -w /w node:22 node normalise.mjs
Unlock Full Question Bank
Get access to all 20 End-to-End Feature Design and Development interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.