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.
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
You are shipping an "order history" page that lists a user's past orders with a date filter. Walk me through every state the page can be in, from the first load to a backend timeout, what the API returns in each case, and which numbers you would watch after launch to know the feature actually works for real users.
Sample Answer
Direct answer
The page is a small state machine (code that is always in exactly one named state and moves to another on a defined event) with eleven views, and each one maps to one API outcome, so the UI never has to guess. The API answers 200 with a list (possibly empty) for everything that worked, 422 for a bad filter, 401 for an expired session, and 503 with a Retry-After header when the backend cannot answer in time. The client adds the cases the server cannot report: it gives up after its own timeout, it can be offline, and it must ignore an old response that arrives after a newer request. After launch, the numbers worth watching are the share of page views that end in a good view (list or a legitimate empty) within a time limit (for example 3 seconds; an illustrative target, to be replaced by one set from the first week of real traffic), the rate of each error view, the latency of the first page of orders, and the empty-result rate with a date filter, because a sudden jump in that last one is how a time zone or filter bug shows up.
The eleven views and what the API returns
The three kinds of view to be able to name first are loaded (views 2 and 3), empty (views 5 and 6) and the retryable error (view 9); the other six are variations on those.
| # | View | When | API response | What the user sees |
|---|---|---|---|---|
| 1 | Loading | first request, nothing to show yet | (pending) | skeleton rows (grey placeholder bars shaped like rows) the height of real rows, so nothing jumps when data arrives |
| 2 | Loaded | orders returned, no further page | 200 {orders:[...], next_cursor:null} | the list, newest first |
| 3 | Loaded, more pages | next_cursor is not null | 200 {orders:[...], next_cursor:"..."} | the list plus a "Load more" button |
| 4 | Refreshing | the filter changed while rows are on screen | (pending) | previous rows dimmed and marked busy, not a blank page |
| 5 | Empty, no orders ever | no filter, zero orders | 200 {orders:[], next_cursor:null} | "You have not placed any orders yet" and a link to browse |
| 6 | Empty, filter matched nothing | filter set, zero orders | 200 {orders:[], ...} | "No orders between these dates" and a Clear filter button |
| 7 | Invalid filter | from date after to date, malformed date | 422 {error:"invalid_filter"} | the field is flagged; the previous list stays |
| 8 | Signed out | session expired | 401 {error:"sign_in_required"} | sign-in prompt that returns to this page |
| 9 | Backend slow or down | database timeout, upstream error, rate limit, or the client gave up | 503 with Retry-After, or 504, or no response before the client timeout (a 429 rate-limit answer would map here too; the demo server never sends one) | "We could not load your orders" with a Try again button |
| 10 | Offline | the request never reached any server | none (fetch fails) | "You appear to be offline" and a retry that works when the connection returns |
| 11 | Load more failed | the next-page request failed | same as 9, for page 2 | the existing list stays; an inline "Could not load more. Try again" under it |
The key design decisions:
- Empty is a success, not an error. An empty list is a normal
200with[]. A404would make every client special-case "no data" as a failure, and monitoring would count normal new customers as errors. Empty with a filter (view 6) and empty with no filter (view 5) need different words and different actions, which is why the client tracks whether a filter is active. - Never show a blank page while refreshing. View 4 keeps the old rows visible, which avoids layout jumping (CLS, cumulative layout shift) and makes the page feel fast.
- An error that happens on page 2 must not wipe page 1. View 11 is separate from view 9 for that reason.
- Do not retry blindly. Views 7 and 8 are not retryable as they stand (same request, same answer), so they have no automatic retry. View 9 is retryable.
API contract
GET /v1/orders?from=2026-06-01&to=2026-06-30&tz=America/New_York&limit=20&cursor=...
fromandtoare calendar dates in the user's time zone, both inclusive. The server turns them into the half-open range "from 00:00 onfrom, up to but not including 00:00 on the day afterto", in the zonetz. Treating the dates as UTC puts late-evening orders on the wrong day. In the run below, an order placed at 23:30 in New York on March 31 (03:30 UTC on April 1) is found by a March 31 filter and not by an April 1 filter in New York, while an April 1 filter read as UTC wrongly finds it.- Paging uses a cursor (an opaque token marking where the last page ended) over
(placed_at, id), notOFFSET. With an offset, a new order arriving while the user reads shifts every row down and page 2 repeats a row from page 1; the cursor says "after this exact row". Theidis the tie-break for two orders in the same instant. 401: MDN describes it as the request lacking valid authentication credentials.422: syntax fine, instructions could not be processed (MDN adds that repeating the same request unchanged fails again).503: the server is not ready to handle the request; MDN says to use it for temporary conditions withRetry-Aftercarrying the estimated recovery time. The header's value is either a non-negative whole number of seconds (used here) or an HTTP date.504: a gateway or proxy did not get a response in time from the server behind it.
Backend: the timeout is a feature
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
id bigserial PRIMARY KEY,
user_id integer NOT NULL,
placed_at timestamptz NOT NULL,
total_cents integer NOT NULL,
status text NOT NULL
);
-- one user's orders, newest first, with a stable tie-break: serves the list and the "next page" cursor
CREATE INDEX orders_user_recent ON orders (user_id, placed_at DESC, id DESC);
-- user 1: an order every 3 days for 45 orders, ending 2026-06-30 12:00 UTC
INSERT INTO orders (user_id, placed_at, total_cents, status)
SELECT 1, timestamptz '2026-06-30 12:00+00' - (n * interval '3 days'), 1000 + n, 'delivered'
FROM generate_series(0, 44) n;
-- user 2: no orders at all. user 3: one order at 23:30 on March 31 in New York (03:30 UTC on April 1)
INSERT INTO orders (user_id, placed_at, total_cents, status) VALUES (3, timestamptz '2026-04-01 03:30+00', 4200, 'delivered');
-- user 4: three orders inside one millisecond (microsecond timestamps, as now() produces)
INSERT INTO orders (user_id, placed_at, total_cents, status) VALUES
(4, timestamptz '2026-07-01 10:00:00.123900+00', 1, 'delivered'),
(4, timestamptz '2026-07-01 10:00:00.123500+00', 2, 'delivered'),
(4, timestamptz '2026-07-01 10:00:00.123100+00', 3, 'delivered');
In plain words, the filter lines do this. Take user 3 in New York with an order placed at 23:30 on March 31, which is 03:30 UTC on April 1. ($2::date)::timestamp AT TIME ZONE $4 turns the text date into a midnight on that calendar day and says "that midnight is in the user's time zone" (::date and ::timestamp are casts, type conversions). For from = 2026-04-01 in New York that midnight is 04:00 UTC, so the order at 03:30 UTC is before it and is excluded. For to = 2026-03-31, the upper bound is ($3::date + 1) = April 1 midnight in New York = 04:00 UTC, and the comparison is < (the range is half-open: includes its start, excludes its end), so the 03:30 UTC order is included. The (placed_at, id) < ($5, $6) line compares two columns as a pair: a row qualifies if its time is earlier, or the times are equal and its id is smaller, which is "after the last row of the previous page" in the list's sort order. The query asks for limit + 1 rows; if the extra row exists there is another page, and the handler returns only limit rows. The cursor is a small JSON array [placed_at, id] of the last row shown, encoded with base64url (a way to write bytes as URL-safe text), so it is opaque to the client but easy to decode on the server. The placed_at inside it is the exact database value written out as text with microseconds (to_char(... US) in the query), not a JavaScript Date: a Date keeps only milliseconds, and a rounded cursor would silently skip orders placed in the same millisecond as the last row shown. pg_sleep(1) pauses the database session for one second and is only the demo's stand-in for a slow query.
The index matches the query's WHERE user_id = ... ORDER BY placed_at DESC, id DESC, so a page is a short walk down one user's rows instead of a sort.
// server.mjs
import http from 'node:http';
const isDate = (s) => typeof s === 'string' && /^\d{4}-\d{2}-\d{2}$/.test(s) && !isNaN(Date.parse(s));
const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
export function makeServer(pool) {
return http.createServer(async (req, res) => {
const send = (status, body, headers = {}) => { res.writeHead(status, { 'content-type': 'application/json', ...headers }); res.end(JSON.stringify(body)); };
const url = new URL(req.url, 'http://x');
if (req.method !== 'GET' || url.pathname !== '/v1/orders') return send(404, { error: 'not_found' });
const userId = Number(/demo_session=(\d+)/.exec(req.headers.cookie || '')?.[1]); // stands in for the session lookup
if (!userId) return send(401, { error: 'sign_in_required' });
const from = url.searchParams.get('from'), to = url.searchParams.get('to');
const tz = url.searchParams.get('tz') || 'UTC';
const limit = Math.min(Number(url.searchParams.get('limit') || 20), 50);
if ((from && !isDate(from)) || (to && !isDate(to)) || (from && to && from > to) || !(limit >= 1))
return send(422, { error: 'invalid_filter' });
let after = [null, null];
if (url.searchParams.get('cursor')) {
try { after = JSON.parse(Buffer.from(url.searchParams.get('cursor'), 'base64url').toString()); }
catch { return send(422, { error: 'invalid_cursor' }); }
}
// demo-only hooks so the page's slow and stuck paths can be reproduced on demand
if (req.headers['x-demo-delay-ms']) await sleep(Number(req.headers['x-demo-delay-ms']));
const client = await pool.connect();
try {
await client.query('BEGIN');
await client.query('SET LOCAL statement_timeout = 300'); // the database gives up before the user does
if (req.headers['x-demo-slow-query']) await client.query('SELECT pg_sleep(1)');
// The user's own local days: [from 00:00, to + 1 day 00:00) in their time zone, half-open.
const { rows } = await client.query(
`SELECT id, placed_at, total_cents, status,
to_char(placed_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"') AS cursor_at FROM orders
WHERE user_id = $1
AND ($2::date IS NULL OR placed_at >= ($2::date)::timestamp AT TIME ZONE $4)
AND ($3::date IS NULL OR placed_at < (($3::date + 1))::timestamp AT TIME ZONE $4)
AND ($5::timestamptz IS NULL OR (placed_at, id) < ($5::timestamptz, $6::bigint))
ORDER BY placed_at DESC, id DESC LIMIT $7`,
[userId, from, to, tz, after[0], after[1], limit + 1]);
await client.query('COMMIT');
const page = rows.slice(0, limit);
const last = page[page.length - 1];
send(200, {
orders: page.map((o) => ({ id: Number(o.id), placed_at: o.placed_at.toISOString(), total_cents: o.total_cents, status: o.status })),
next_cursor: rows.length > limit ? Buffer.from(JSON.stringify([last.cursor_at, Number(last.id)])).toString('base64url') : null,
});
} catch (err) {
await client.query('ROLLBACK').catch(() => {});
if (err.code === '57014') return send(503, { error: 'temporarily_unavailable' }, { 'retry-after': '5' }); // statement_timeout hit
if (err.code === '22023') return send(422, { error: 'invalid_filter' }); // unknown time zone name
send(500, { error: 'server_error' });
} finally { client.release(); }
});
}
Four time limits have to nest, shortest inside. Illustrative values: database statement limit 300 ms (the demo's value), then the handler's own limit about 2 s, then the gateway in front of the service about 5 s (it would return 504), then the client's 8 s. SET LOCAL statement_timeout = 300 tells PostgreSQL to abort any statement in the current transaction that runs longer than 300 ms, and the setting ends with the transaction. In the shipped code only the database limit and the client limit exist; the handler and gateway limits belong to the hosting setup, so the numbers in between are examples of how they should be ordered. The demo uses 300 ms for the statement only so the slow path is quick to reproduce; the real value comes from the measured slow end of that query. If an outer limit is shorter than an inner one, the user gets a client-side "gave up" while the server keeps working on an abandoned request. The statement timeout surfaces as PostgreSQL error 57014 (query_canceled), which the handler turns into 503 plus Retry-After: 5; an unknown time zone name raises 22023 (invalid_parameter_value) and becomes 422.
Client: one function from outcome to view
// client.mjs
// The page's data layer: one function turns every possible outcome into one named view state.
export function viewFromResponse(status, body, retryAfter, hasFilter) {
if (status === 200) {
if (body.orders.length > 0) return { kind: 'loaded', orders: body.orders, next: body.next_cursor };
return { kind: hasFilter ? 'empty_filtered' : 'empty_no_orders', orders: [], next: null };
}
if (status === 401) return { kind: 'error_auth' };
if (status === 422) return { kind: 'error_validation' };
if (status === 429 || status >= 500) return { kind: 'error_retryable', retryAfterSeconds: retryAfter ? Number(retryAfter) : null };
return { kind: 'error_other' };
}
export function createOrdersClient({ base, cookie, timeoutMs = 8000, onEvent = () => {} }) {
let latest = 0;
let state = { kind: 'idle', orders: [], next: null };
async function load(filter = {}, extraHeaders = {}) {
const id = ++latest; // only the newest request may change the screen
const hasFilter = Boolean(filter.from || filter.to);
state = { ...state, kind: state.orders.length ? 'refreshing' : 'loading' };
const qs = new URLSearchParams({ ...(filter.from && { from: filter.from }), ...(filter.to && { to: filter.to }), tz: filter.tz || 'UTC' });
const t0 = performance.now();
let next, status = null;
try {
const res = await fetch(`${base}/v1/orders?${qs}`, { headers: { cookie, ...extraHeaders }, signal: AbortSignal.timeout(timeoutMs) });
status = res.status;
next = viewFromResponse(status, await res.json(), res.headers.get('retry-after'), hasFilter);
} catch (e) {
next = e.name === 'TimeoutError' ? { kind: 'error_retryable', reason: 'client_timeout' } : { kind: 'offline' };
}
if (id !== latest) { onEvent({ event: 'orders_response_ignored_stale' }); return state; }
state = next;
onEvent({ event: 'orders_view', view: next.kind, http_status: status, has_filter: hasFilter, duration_ms: Math.round(performance.now() - t0) });
return state;
}
return { load, get state() { return state; } };
}
viewFromResponse is a pure mapping (same inputs, same output, no side effects), so it is easy to test. The client does three things beyond the mapping. It gives the request a deadline with AbortSignal.timeout, which aborts with a TimeoutError. It numbers each request and lets only the newest one write to the screen (a stale response: an old answer arriving after a newer request, which would otherwise replace the right list with the wrong one). And it emits one event per finished load, which feeds the launch numbers below.
Running every state
// run.mjs
import pg from 'pg';
import fs from 'node:fs';
import { makeServer } from './server.mjs';
import { createOrdersClient } from './client.mjs';
const pool = new pg.Pool({ host: 'demo-pg', user: 'postgres', password: 'pw', database: 'postgres' });
await pool.query(fs.readFileSync('schema.sql', 'utf8'));
const srv = makeServer(pool).listen(3400);
const base = 'http://127.0.0.1:3400';
const events = [];
const as = (uid, opts = {}) => createOrdersClient({ base, cookie: uid ? `demo_session=${uid}` : '', onEvent: (e) => events.push(e), ...opts });
const rows = [];
const scenario = async (name, client, filter, headers) => {
events.length = 0;
const s = await client.load(filter, headers);
const e = events.find((x) => x.event === 'orders_view');
rows.push({ scenario: name, http: e.http_status ?? '(none)', view: s.kind, orders: s.orders?.length ?? 0, next_page: Boolean(s.next), extra: s.retryAfterSeconds ? `retry in ${s.retryAfterSeconds}s` : s.reason ?? '' });
};
await scenario('first visit, 45 orders on record', as(1), {});
await scenario('filter 2026-06-01..2026-06-30', as(1), { from: '2026-06-01', to: '2026-06-30' });
await scenario('filter with no matches', as(1), { from: '2025-01-01', to: '2025-01-31' });
await scenario('new customer, no orders ever', as(2), {});
await scenario('from after to', as(1), { from: '2026-06-30', to: '2026-06-01' });
await scenario('session expired', as(null), {});
await scenario('database too slow', as(1), {}, { 'x-demo-slow-query': '1' });
await scenario('server stuck, client gives up', as(1, { timeoutMs: 200 }), {}, { 'x-demo-delay-ms': '1000' });
await scenario('no network at all', createOrdersClient({ base: 'http://127.0.0.1:1', cookie: '', onEvent: (e) => events.push(e) }), {});
console.table(rows);
// two quick filter changes; the first answer arrives last and must not win
events.length = 0;
const racer = as(1);
const slow = racer.load({ from: '2026-06-01', to: '2026-06-30' }, { 'x-demo-delay-ms': '400' });
const fast = racer.load({});
await Promise.all([slow, fast]);
console.log('race: events =', events.map((e) => e.event === 'orders_view' ? `view:${e.has_filter ? 'filtered' : 'unfiltered'}` : e.event).join(', '), '| screen shows unfiltered list:', racer.state.orders.length === 20);
// paging: follow next_cursor to the end; every order must appear exactly once
const walk = async (uid, limit) => {
let cur = null, n = 0; const seen = new Set();
do {
const r = await (await fetch(`${base}/v1/orders?limit=${limit}${cur ? `&cursor=${cur}` : ''}`, { headers: { cookie: `demo_session=${uid}` } })).json();
r.orders.forEach((o) => seen.add(o.id)); n += r.orders.length; cur = r.next_cursor;
} while (cur);
return `${n} rows, ${seen.size} distinct`;
};
console.log('paging | user 1, 45 orders, 20 per page:', await walk(1, 20), '| user 4, 3 orders in one millisecond, 1 per page:', await walk(4, 1));
// time zones: user 3's only order is 2026-03-31 23:30 in New York (2026-04-01 03:30 UTC)
const tzCount = async (day, tz) => (await as(3).load({ from: day, to: day, tz })).orders.length;
console.log('order at 23:30 New York time, Mar 31 | filter Mar 31 in New York:', await tzCount('2026-03-31', 'America/New_York'),
'| filter Apr 1 in New York:', await tzCount('2026-04-01', 'America/New_York'),
'| filter Apr 1 read as UTC:', await tzCount('2026-04-01', 'UTC'));
await new Promise((r) => setTimeout(r, 1200)); // let the abandoned slow request finish before closing
srv.close(); await pool.end();
Run in a node:22 container against postgres:16 (the run covers views 2, 3, 5, 6, 7, 8, 9 and 10 plus the stale-response guard and cursor paging, and does not exercise views 1, 4 or 11), it prints:
┌─────────┬────────────────────────────────────┬──────────┬────────────────────┬────────┬───────────┬──────────────────┐
│ (index) │ scenario │ http │ view │ orders │ next_page │ extra │
├─────────┼────────────────────────────────────┼──────────┼────────────────────┼────────┼───────────┼──────────────────┤
│ 0 │ 'first visit, 45 orders on record' │ 200 │ 'loaded' │ 20 │ true │ '' │
│ 1 │ 'filter 2026-06-01..2026-06-30' │ 200 │ 'loaded' │ 10 │ false │ '' │
│ 2 │ 'filter with no matches' │ 200 │ 'empty_filtered' │ 0 │ false │ '' │
│ 3 │ 'new customer, no orders ever' │ 200 │ 'empty_no_orders' │ 0 │ false │ '' │
│ 4 │ 'from after to' │ 422 │ 'error_validation' │ 0 │ false │ '' │
│ 5 │ 'session expired' │ 401 │ 'error_auth' │ 0 │ false │ '' │
│ 6 │ 'database too slow' │ 503 │ 'error_retryable' │ 0 │ false │ 'retry in 5s' │
│ 7 │ 'server stuck, client gives up' │ '(none)' │ 'error_retryable' │ 0 │ false │ 'client_timeout' │
│ 8 │ 'no network at all' │ '(none)' │ 'offline' │ 0 │ false │ '' │
└─────────┴────────────────────────────────────┴──────────┴────────────────────┴────────┴───────────┴──────────────────┘
race: events = view:unfiltered, orders_response_ignored_stale | screen shows unfiltered list: true
paging | user 1, 45 orders, 20 per page: 45 rows, 45 distinct | user 4, 3 orders in one millisecond, 1 per page: 3 rows, 3 distinct
order at 23:30 New York time, Mar 31 | filter Mar 31 in New York: 1 | filter Apr 1 in New York: 0 | filter Apr 1 read as UTC: 1
Reading the output: row 0 is view 3 (20 orders and a next page), row 1 is view 2 (10 orders in June, no next page), row 2 is view 6, row 3 is view 5, row 4 is view 7, row 5 is view 8, rows 6 and 7 are view 9 (a 503 with Retry-After: 5 from the database timeout, and a client that gave up with no response at all), and row 8 is view 10. The race line shows the slow filtered request finishing after the fast unfiltered one and being discarded, so the screen keeps the list matching the current filter. The paging line follows next_cursor to the end: 45 orders at 20 per page come back as 45 distinct rows, and the three orders placed within one millisecond come back one per page without losing any, which is what the microsecond cursor and the id tie-break are for. Views 1 and 4 are the loading and refreshing values the client sets while a request is in flight; they are visible only while waiting, so the run does not print them. View 11 is not coded in the run: it would send the same request with the cursor parameter added and feed the answer through the same mapping, with the difference that success appends rows under the list and failure shows an inline message under it.
What to watch after launch
Define "works for real users" as a number before launch, then watch these:
Example targets (illustrative, to be replaced by the first week's real baseline): good-view rate (a good view arriving within 3 seconds) at or above 99 percent of page views over a day, and a page of orders in 2 seconds or less at the 95th percentile.
| Signal | How it is measured | What a bad movement means |
|---|---|---|
| Good-view rate | orders_view events with view loaded or empty_* and duration_ms within the time limit (3 seconds in the example above), divided by all orders_view events | falls when errors or slow loads rise; the headline number |
| Error-view mix | share of error_retryable, offline, error_auth, error_validation | a rise in error_validation points at a front-end date bug; in error_auth, session handling; in error_retryable, the backend |
| First-page latency | duration_ms from the client event (p50 and p95, i.e. the median and the 95th percentile), next to the server's own request duration | the gap between the two is network and client time; a climbing p95 predicts the timeouts |
Backend 5xx and statement-timeout rate | server logs and database metrics for the orders query | a slow query before users complain; compare against an alert threshold set from the first week's baseline |
| Empty rate with a filter | empty_filtered divided by loads with a filter | a sudden jump suggests the time-zone or range logic is wrong |
| Retry and Try again clicks | click events on the error button | users hitting retry repeatedly means the error is persistent, not blips |
| Load-more usage and cursor errors | click rate and invalid_cursor responses | cursors breaking after a deploy |
| Page experience | Core Web Vitals field data (real-user measurements): LCP (largest contentful paint, when the main content appears) 2.5 s or less, INP (interaction to next paint, how quickly the page responds to taps and clicks) 200 ms or less, CLS (cumulative layout shift, how much the layout jumps) 0.1 or less, at the 75th percentile (web.dev) | the page loaded but felt slow or jumpy |
Two cautions. Alert on the good-view rate over a window of real traffic (for example, page someone if it stays below 95 percent for 15 minutes; create a ticket below 99 percent over a day), not on a single failed request. And segment (split the numbers into groups) by time zone, device and session age, because an overall average can hide a bug that only hits users east of UTC.
Pitfalls
- A skeleton or spinner with no timeout: the user waits forever. Every wait needs an end state.
OFFSETpagination on a list that changes.- Showing "You have no orders" on an error because the error handler fell through to the empty branch.
- Logging only server-side: the client's timeout and offline views never reach the server, so only a client event sees them.
Running the code
cd work # schema.sql server.mjs client.mjs run.mjs
echo '{"type":"module","dependencies":{"pg":"8.13.1"}}' > 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 # 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 && node run.mjs'
Users of a social app can write a post with text and an image. Walk me through that feature from the compose screen to the database row: what the client does while they type and on submit, what the request looks like, what the server checks and stores, and what happens when the network drops mid-submit or the server rejects the post.
Sample Answer
Direct answer
The compose screen keeps the draft and uploads the image as soon as it is picked, so submit only has to send the text plus an image_id. Submit is a POST carrying a client-generated client_request_id, which the server stores under a unique constraint (a database rule that refuses a second row with the same value in the named columns): a retry after a dropped connection returns the original post instead of creating a second one. The server validates everything again (the client's checks are only for speed), writes the post and attaches the image in one transaction, and answers 201 with the post, or 422 with a list of field errors. The draft is cleared only after a success response.
The flow, step by step
| Step | Client | Server / storage |
|---|---|---|
| Typing | Draft saved to local storage on a short debounce (a wait that restarts on every keystroke), keyed by user. Counter shows characters left, counted in code points (one per Unicode code point: a simple emoji such as the grinning face counts 1, whereas JavaScript's plain .length counts it as 2; a family emoji or a flag is built from several code points, 5 and 2 respectively, so it counts more than 1, which is accepted because the unit only has to be the same on the client and the server), the same unit the server and PostgreSQL's char_length use. Submit disabled when empty or over 500. | nothing |
| Pick image | Preview appears at once; upload starts in the background with its own progress and its own error. Submit stays disabled until the image is uploaded or removed. | POST /v1/images returns an image_id and a short-lived upload URL; the bytes go straight to object storage (a service that stores files by key, such as Amazon S3), not through the API server |
| Submit | Button shows "Posting...", the text box is read-only, the draft is kept. | POST /v1/posts with {"body", "image_id", "client_request_id"} |
| Success | Clear the draft, close the composer, put the post at the top of the feed using the returned object. | 201 {"id": ...} |
| Rejected | Keep everything, put each message next to its field, focus the first invalid field. | 422 {"errors": [{"field": "body", "code": "too_long"}]} |
| Network drop | Retry automatically up to 3 times with the same client_request_id, then show "Couldn't post. Retry" with the draft intact. | a request that arrived is stored once |
What the server checks
- The user is signed in and under the rate limit (a cap on requests per user per time window; the server answers
429with aRetry-Afterheader saying how long to wait otherwise). body, after trimming, is 1 to 500 characters. The databaseCHECKrepeats the length limit, so no code path can store a body over 500 characters; it measures the stored text as it is, so a body of only spaces passes it, which is why the server trims and rejects an empty result before the insert.- If
image_idis present: it belongs to this user, its upload finished, the stored bytes really are an image of an allowed type (read the file's first bytes, never trust the declaredContent-Type, the label the uploader attached; a file renamed fromvirus.exetophoto.pngstill starts with the bytes of an executable, not of a PNG, and the client's own checks cannot be relied on because anyone can call the API directly without the app), and it is not already attached to another post. The attach is a conditional update inside the same transaction as the insert, so a failure rolls back both. - The
(author_id, client_request_id)pair: new means insert; seen before with the same payload means return the stored post with200; seen before with a different payload means409 idempotency_key_reused(409 Conflict: the request clashes with stored state). Idempotent means doing the request twice has the same effect as doing it once. The server keepspayload_hash, anmd5fingerprint of the body and image, so it can tell a true retry (same fingerprint, return the old post) from a reused id on different content (different fingerprint, refuse), instead of silently returning a post that does not match what was sent.
Storing it and replaying it
CREATE TABLE feed_posts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
author_id bigint NOT NULL,
body text NOT NULL CHECK (char_length(body) BETWEEN 1 AND 500),
image_id uuid,
client_request_id uuid NOT NULL,
payload_hash text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (author_id, client_request_id)
);
CREATE FUNCTION create_post(p_author bigint, p_body text, p_image uuid, p_req uuid)
RETURNS TABLE (out_id bigint, out_replayed boolean)
LANGUAGE plpgsql AS $$
DECLARE h text := md5(coalesce(p_body,'') || '|' || coalesce(p_image::text,'')); new_id bigint; old_hash text; old_id bigint;
BEGIN
INSERT INTO feed_posts (author_id, body, image_id, client_request_id, payload_hash)
VALUES (p_author, p_body, p_image, p_req, h)
ON CONFLICT (author_id, client_request_id) DO NOTHING RETURNING id INTO new_id;
IF new_id IS NOT NULL THEN RETURN QUERY SELECT new_id, false; RETURN; END IF;
SELECT id, payload_hash INTO old_id, old_hash FROM feed_posts WHERE author_id = p_author AND client_request_id = p_req;
IF old_hash <> h THEN RAISE EXCEPTION 'idempotency_key_reused'; END IF;
RETURN QUERY SELECT old_id, true;
END $$;
SELECT * FROM create_post(7, 'hello world', 'aaaaaaaa-0000-0000-0000-000000000001', 'bbbbbbbb-0000-0000-0000-000000000001');
SELECT * FROM create_post(7, 'hello world', 'aaaaaaaa-0000-0000-0000-000000000001', 'bbbbbbbb-0000-0000-0000-000000000001');
SELECT * FROM create_post(7, 'hello EDITED', 'aaaaaaaa-0000-0000-0000-000000000001', 'bbbbbbbb-0000-0000-0000-000000000001');
SELECT count(*) AS rows_stored FROM feed_posts;
SELECT * FROM create_post(7, repeat('x', 501), NULL, 'bbbbbbbb-0000-0000-0000-000000000002');
Run in a postgres:16 container, the three create_post calls print 1 | f, then 1 | t (the replay), then ERROR: idempotency_key_reused. The count query prints rows_stored = 1, and the 501-character body fails with violates check constraint "feed_posts_body_check". If two copies of the same request arrive at the same moment, the second INSERT waits for the first to commit, finds the key taken, and returns the existing row.
The client side of a retry
import { randomUUID } from 'node:crypto';
// ---- client ----
export const newDraft = () => ({ text: '', imageId: null, requestId: randomUUID() });
export const editDraft = (d, text) => ({ ...d, text, requestId: randomUUID() }); // new content = new request id
const NETWORK = Symbol('network');
export async function submit(draft, send, attempts = 3) {
for (let i = 1; i <= attempts; i++) {
try {
const r = await send({ body: draft.text, image_id: draft.imageId, client_request_id: draft.requestId });
if (r.status === 201 || r.status === 200) return { kind: 'posted', id: r.json.id, replayed: r.status === 200, attempts: i };
if (r.status === 422) return { kind: 'field_errors', errors: r.json.errors }; // keep the draft, show errors
if (r.status === 409) return { kind: 'conflict' };
if (r.status >= 500 && i < attempts) continue; // safe to repeat: same request id
return { kind: 'failed', status: r.status };
} catch (e) {
if (e === NETWORK && i < attempts) continue;
return { kind: 'failed_offline', attempts: i }; // draft stays; user taps Retry
}
}
}
// ---- fake server: stores by (author, request id) like the unique index does ----
const rows = new Map(); let nextId = 1;
function server(req, { dropResponseOnce } = {}) {
const trimmed = req.body.trim();
if ([...trimmed].length < 1 || [...trimmed].length > 500) return { status: 422, json: { errors: [{ field: 'body', code: [...trimmed].length ? 'too_long' : 'required' }] } };
const hash = trimmed + '|' + req.image_id;
const old = rows.get(req.client_request_id);
if (old) return old.hash === hash ? { status: 200, json: { id: old.id } } : { status: 409, json: { error: 'idempotency_key_reused' } };
const row = { id: nextId++, hash }; rows.set(req.client_request_id, row);
return { status: 201, json: { id: row.id } };
}
// ---- scenarios ----
let d = { ...newDraft(), text: 'hello world', imageId: 'img-1' };
let dropped = false;
const flaky = async (req) => { const res = server(req); if (!dropped) { dropped = true; throw NETWORK; } return res; };
console.log('1. response lost on the first attempt:', JSON.stringify(await submit(d, flaky)), '| rows stored:', rows.size);
console.log('2. user taps Retry later with same draft:', JSON.stringify(await submit(d, async (r) => server(r))), '| rows stored:', rows.size);
const edited = editDraft(d, 'hello world, edited');
console.log('3. user edits after a failure, new request id:', JSON.stringify(await submit(edited, async (r) => server(r))), '| rows stored:', rows.size);
console.log('4. same id, different text (a bug):', JSON.stringify(await submit({ ...d, text: 'other' }, async (r) => server(r))));
console.log('5. 501 characters:', JSON.stringify(await submit({ ...newDraft(), text: 'x'.repeat(501) }, async (r) => server(r))));
console.log('6. offline for every attempt:', JSON.stringify(await submit({ ...newDraft(), text: 'hi' }, async () => { throw NETWORK; })), '| rows stored:', rows.size);
Run in a node:22 container, it prints:
1. response lost on the first attempt: {"kind":"posted","id":1,"replayed":true,"attempts":2} | rows stored: 1
2. user taps Retry later with same draft: {"kind":"posted","id":1,"replayed":true,"attempts":1} | rows stored: 1
3. user edits after a failure, new request id: {"kind":"posted","id":2,"replayed":false,"attempts":1} | rows stored: 2
4. same id, different text (a bug): {"kind":"conflict"}
5. 501 characters: {"kind":"field_errors","errors":[{"field":"body","code":"too_long"}]}
6. offline for every attempt: {"kind":"failed_offline","attempts":3} | rows stored: 2
Scenario 1 is the case the question asks about. Walked through: attempt 1 reaches the server, which stores post 1 and answers 201, but the fake network throws the answer away, so the client sees a network failure. Attempt 2 sends the same client_request_id; the server finds the stored row and answers 200 with id 1; the client reports replayed: true, attempts: 2. The user experiences one successful post, and exactly one row is stored. Scenario 2 is the user tapping Retry later: same request id, same stored post, no new row. Scenario 4 reuses the request id with different text, which the server refuses with 409 because the fingerprint differs. Scenario 5 sends 501 characters, so the server answers 422 with too_long. Scenario 6 throws on every attempt: after 3 tries the client gives up, shows Retry with the draft intact, and nothing new is stored. Scenario 3 shows the rule that completes the design: when the user edits the text after a failure, the draft gets a new client_request_id, because reusing the old one with new text would be rejected as a conflict.
Failure cases to name
- Server rejects the post: keep the draft, show the message at the field. Never clear first and ask questions later.
- The image fails halfway: only the attachment shows an error, with Retry and Remove; the typed text is untouched.
- The tab closes mid-submit: the draft and its request id are in local storage, so reopening and pressing Post is a safe retry.
- A
5xxresponse: treated like a lost response and retried with the same id, which is why the endpoint must be idempotent.
Test, roll out, observe
Tests: the SQL checks above, a Playwright test (browser automation) that aborts the network route once and asserts exactly one post appears, and a test for each 422 field mapping. Roll out behind a flag with the old composer still deployable. Observe the share of submits that end in failed_offline, the 409 count (it should be near zero; a spike means a client is reusing ids after edits), and the time from tap to 201.
Trade-off
Uploading the image before submit makes the final request tiny and retries cheap, but leaves orphan uploads when users abandon the composer. Add an expiry job that deletes images not attached to a post within 24 hours. Sending the image inside the post request avoids orphans but makes every retry re-send megabytes.
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()]);
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.