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.
Your profile page shows a follower count. A user taps follow, refreshes, and the count is unchanged or briefly wrong. Walk me through why that can happen across the UI, API and database, when you would accept it, and when you would not.
Sample Answer
Direct answer
An edge row is one row in a follows table saying "user A follows user B" (in a social graph the people are nodes and each follow is an edge). That row is the truth. The count is a derived value: a number computed from the edge rows and then stored or cached for speed, so it can disagree with them for a while. The profile page can read the count from several places that each lag the edge rows by a different amount: an HTTP or CDN cache (content delivery network, a cache near the user that serves saved copies of responses), a database read replica (a copy of the database that trails the main one, called the primary), a counter that a batch job updates later, or the client's own cache overwriting the screen with an older response.
Start with step 1 of the checks below. One look at the follow request and the next profile GET shows whether the right number ever left the server, and that splits "a cache or the client is at fault" from "the database is at fault". Then split what must be exact from what may be late. Accept a late count on someone else's popular profile. Never accept a wrong answer to "do I follow this person?", never let a stale response undo the user's own action (the property usually called read-your-own-write: after you write something, your own next read must show it), and never accept a count that does not converge, meaning it must settle back to the true value without anyone repairing it by hand.
Why it happens, layer by layer
| Layer | Mechanism | What the user sees |
|---|---|---|
| UI | A refetch or poll that started before the tap returns afterwards and overwrites the optimistic number | count flickers back to the old value, correct on the next load |
| HTTP / CDN cache | The profile response carries a Cache-Control header (it tells caches whether and how long they may keep the response; a shared cache is one that serves many users, such as a CDN) that lets a shared cache keep it; the refresh is served from that copy | count unchanged after refresh, response has an Age header above zero |
| API | The follow response omits the new count, so the client has nothing newer to show | count briefly wrong until the next fetch |
| Database replica | The read goes to a replica that has not yet applied the write | count (and sometimes the button) wrong for a short time, then right |
| Counter | The stored counter is updated by a later job, or by a code path that is not idempotent | wrong until the job runs, or wrong permanently |
Ordered checks (cheapest and most informative first)
- Network panel, same tap. In Chrome DevTools, Network tab: find the follow request and read its response body (did it return
follower_count?). Then find the profileGETafter the refresh: read the body, and in Headers readCache-ControlandAge.Ageis the number of seconds the response has sat in a shared cache, for exampleAge: 37(illustrative value); a header showingAgeabove zero means a shared cache served it, and noAgeheader suggests the response came from the origin. A body with the right count that the screen does not show means the client discarded it (go to step 5). - Bypass caches. Tick "Disable cache" for the browser cache, and replay the request with a
Cache-Control: no-cacheheader or a unique query string to skip shared ones. If the count is now right, a cache is the cause; fixCache-Controlfor the viewer-specific part of the response. - Ask the primary database. Compare the stored counter with the real edge count for that user. If the edge row is missing, the write never happened; if it exists but the counter is off, go to step 4. The queries below do this and show the repair. Here a follow tap becomes an
INSERTintofollowsin the request; theusers.follower_countcounter is not touched in that request, and a batch job fixes it later, which is the state the first query catches in between. - Check replica lag and the job. On the replica run
SELECT pg_last_xact_replay_timestamp()(the commit time, as it was on the primary, of the last transaction the replica applied; PostgreSQL documents that it returns NULL when the server was started normally without recovery, which is why it is only meaningful on a replica). The result is a timestamp such as2026-10-07 12:00:03+00(illustrative); compare it withnow()on the replica, and a gap of several seconds means that replica is that far behind the primary, assuming the primary is taking writes. If the count is right on the primary and late on the replica, the read path is the cause. If both are late, look at when the batch job last ran. - Client cache. In the app's query-cache devtools (or logging), check whether a refetch that began before the mutation completed wrote its result after the mutation's result.
The database side, run in a postgres:16 container. Query 1 puts the stored counter next to a live count of edge rows. Query 2 asks one exact question about one pair of users. Query 3 is the drift check: LEFT JOIN keeps users who have no followers at all, coalesce(c.n, 0) turns the missing count into 0, and the WHERE keeps only rows where counter and edge count differ. Query 4 recomputes each user's real count with a correlated subquery (a subquery that is re-run for each user row, using that row's id) and overwrites only the counters that are wrong.
CREATE TABLE users (id bigint PRIMARY KEY, follower_count integer NOT NULL DEFAULT 0);
CREATE TABLE follows (
follower_id bigint NOT NULL, followee_id bigint NOT NULL,
PRIMARY KEY (follower_id, followee_id)
);
INSERT INTO users SELECT g FROM generate_series(1, 5) g;
-- three people follow user 1; the counter is maintained later by a batch job, not in the request
INSERT INTO follows VALUES (2, 1), (3, 1), (4, 1);
-- 1. what the profile page's cached counter says, and what the edge table says
SELECT u.follower_count AS counter, (SELECT count(*) FROM follows f WHERE f.followee_id = u.id) AS edges
FROM users u WHERE u.id = 1;
-- 2. "do I follow this account?" is a primary-key lookup on the edge table, so it is exact while the counter lags
SELECT EXISTS (SELECT 1 FROM follows WHERE follower_id = 3 AND followee_id = 1) AS viewer_follows;
-- 3. drift check: rows where the stored counter and the real edge count disagree
SELECT u.id, u.follower_count AS counter, coalesce(c.n, 0) AS edges
FROM users u LEFT JOIN (SELECT followee_id, count(*) AS n FROM follows GROUP BY 1) c ON c.followee_id = u.id
WHERE u.follower_count <> coalesce(c.n, 0);
-- 4. the repair (also the nightly rollup)
UPDATE users u SET follower_count = coalesce(c.n, 0)
FROM (SELECT u2.id, (SELECT count(*) FROM follows f WHERE f.followee_id = u2.id) AS n FROM users u2) c
WHERE c.id = u.id AND u.follower_count <> c.n;
SELECT follower_count AS counter_after_repair FROM users WHERE id = 1;
It prints:
counter | edges -> 0 | 3
viewer_follows -> t
drift check (id, counter, edges) -> 1 | 0 | 3
counter_after_repair -> 3
Reading the output: the first line shows the counter at 0 while three edge rows exist; the second shows user 3 follows user 1; the drift check lists only user 1 (users 2 to 5 have counter 0 and 0 edges, so they match); after the repair the counter reads 3. The counter says 0, the edge table says 3, and the exact question "does user 3 follow user 1?" is still answered correctly by the primary-key lookup. This is the key design point: the button state comes from the edge row, the number may come from a derived value.
The fix on the client: the pending marker plus a viewer-state check
Names in the code: confirmed is the last state the server told us about for the viewer's own follow action, pending is true while that request is on the wire, a snapshot is any profile response (from a refetch, poll, cache or replica) carrying viewer_follows and follower_count, and onSnapshot decides whether to apply it. The button itself shows the new state as soon as pending is true; the log prints confirmed, so following=false right after the tap is expected and does not mean the button is wrong.
// confirmed = last server answer to MY action; pending = a request of mine is on the wire
const initial = { confirmed: { following: false, count: 41 }, pending: false };
function onTap(s) { return { ...s, pending: true }; }
function onResponse(s, r) { return { confirmed: { following: r.following, count: r.follower_count }, pending: false }; }
function onSnapshot(s, snap) {
if (s.pending) return { state: s, action: 'ignored: my request is in flight' };
if (snap.viewer_follows !== s.confirmed.following)
return { state: s, action: 'discarded: snapshot disagrees about MY follow state, refetch in 2s' };
return { state: { ...s, confirmed: { ...s.confirmed, count: snap.follower_count } }, action: 'applied' };
}
let s = initial;
const show = (label, extra = '') => console.log(label.padEnd(44), `following=${s.confirmed.following} count=${s.confirmed.count}`, extra);
show('start');
s = onTap(s); show('tap Follow (request sent)');
let r = onSnapshot(s, { viewer_follows: false, follower_count: 41 }); s = r.state; show('stale snapshot arrives mid-request', r.action);
s = onResponse(s, { following: true, follower_count: 42 }); show('POST answers 200 {following:true, count:42}');
r = onSnapshot(s, { viewer_follows: false, follower_count: 41 }); s = r.state; show('replica or CDN snapshot after refresh', r.action);
r = onSnapshot(s, { viewer_follows: true, follower_count: 40 }); s = r.state; show('snapshot agrees on follow, count is 40', r.action);
Run in a node:22 container, it prints (the state shown is the last server-confirmed one; the button already displays the new state after the tap):
start following=false count=41
tap Follow (request sent) following=false count=41
stale snapshot arrives mid-request following=false count=41 ignored: my request is in flight
POST answers 200 {following:true, count:42} following=true count=42
replica or CDN snapshot after refresh following=true count=42 discarded: snapshot disagrees about MY follow state, refetch in 2s
snapshot agrees on follow, count is 40 following=true count=40 applied
A snapshot is ignored while the user's own request is in flight. After the answer arrives, a snapshot that disagrees about the user's own follow state came from before the write (a replica or a cache) and is discarded with a later retry. A snapshot that agrees is newer truth, including a lower count when other people unfollowed.
The fix on the server
- The follow and unfollow responses return
{following, follower_count}read in the same transaction as the write, so the client always has a post-write number. viewer_followsis computed from the edge table on the primary for the signed-in viewer. Mark that responseCache-Control: privateso a shared cache never serves one person's answer to another.- If profile traffic is large enough that you need a shared cache, split the response: a public, cacheable part (name, avatar, counts) and a small private part (
viewer_follows). That costs a second request and is worth it only when the public part is read far more often than it changes. For ordinary traffic one private response is simpler. - Repair (and detect drift, meaning stored counters that have wandered away from the real edge counts) by running the drift query on a schedule and alerting if the same row mismatches on two consecutive runs, since one mismatch may be a write in flight.
When to accept the lag, and when not
| Case | Verdict | Why |
|---|---|---|
| Another user's follower count on a public profile, seconds to minutes late | Accept | nobody can verify it, and it allows caching and cheap batch updates; show abbreviations like 1.2M for large values |
| The viewer's own button state after tapping | Do not accept | the user just acted and can see the contradiction |
| The count immediately after the viewer's own follow | Do not accept | show their own +1 at once and hold it against stale snapshots |
| A counter that stays wrong after the job has run | Do not accept | lag means the value catches up on its own (converges); a value that never catches up is a bug (a non-idempotent write, one that changes the result when repeated, or a code path that never updates the counter), not lag |
| A number used for a decision, such as a follow limit or eligibility | Do not accept | a stale counter could let someone past a limit they have reached or lock out someone who has not; read the edge table, not the derived counter |
Verify
A Playwright test follows, then routes the next profile GET to a stale body (viewer_follows: false, old count) and asserts the button still reads "Following" and the count shows the new value. A database test inserts an edge without moving the counter and asserts the drift query reports the row and the repair clears it. A latency-injection test delays the replica read and asserts the same screen result. Observe: number of drift rows per run, the share of discarded snapshots (a high share means a systematic cache or replica problem), and replica lag.
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.
Field technicians fill in an inspection checklist in a web app on tablets, often in basements with no signal. Walk me through how a submitted checklist reaches the server reliably: what the browser stores and where, how and when it retries, how the server avoids saving the same checklist twice, and what the technician sees at each step.
Sample Answer
Direct answer
Treat the tablet as the first place the checklist is saved and the server as the second. When the technician taps Submit, the app writes the finished checklist to the browser's own database (IndexedDB) together with an id generated on the tablet, tells the technician "Saved on this tablet", and only then tries the network. A small background loop (the outbox) sends queued checklists with PUT /v1/inspections/{id}, retries failures with a growing, randomised pause, and deletes the local copy only after the server has confirmed. The server stores each id at most once, so a retry after a lost reply is harmless. The screen never says "Submitted" until the server has answered with a success.
The id is the key to the whole design. Because the tablet picks it before the first attempt, every retry of the same checklist carries the same id, and the database can recognise a repeat. Without it, a lost response ("did it save or not?") forces a choice between losing data and saving twice.
What the technician sees at each step
| Moment | What happens underneath | What the screen says |
|---|---|---|
| Fills the form in the basement | answers held in the form; optionally draft-saved to IndexedDB every few seconds | normal form, a small "Offline" badge |
| Taps Submit | record written to IndexedDB (state: queued), button disabled so one tap makes one record | "Saved on this tablet. It will be sent when there is a signal." |
Send attempt fails (no signal, timeout, 5xx) | attempt counter goes up, next try scheduled after a random pause | "Saved on this tablet. Will retry automatically." plus a count of waiting checklists |
Signal returns, PUT answered 201 or 200 | local record deleted | "Sent. The office has your checklist." with the time |
Server answers 401 (signed out while offline) | record stays queued, no timer; retried after the technician signs in | "Please sign in again. Your checklist is safe on this tablet." |
Server answers 409 or 422 | record kept, marked needs_attention, never retried automatically | "This checklist was rejected. Tap to review it." |
| App closed and reopened with items waiting | outbox loop starts again on page load | "1 checklist waiting to be sent." |
Two wording rules follow from this. "Saved" and "Sent" are different words for different facts, and a technician who leaves the building after seeing "Saved" must understand the office does not have it yet. And a waiting count must stay visible on the home screen, because the main failure in the field is a technician who believes everything was sent.
What the browser stores, and where
- IndexedDB holds the outbox. (An origin is the site's address: scheme, host and port. Browser storage belongs to one origin, so another site cannot read it.) It is an asynchronous browser database that stores structured data, including files and blobs, so a checklist with photo attachments fits. A Blob is a chunk of binary data such as a photo.
localStorageholds only strings and suits small settings, not a queue of records with photos. - A service worker (a script the browser runs separately from the page, able to answer requests from a cache) keeps the app's own files, so the app opens in the basement on a cold start (opened fresh, with the browser not already holding the page). The demo below blocks only the API calls; the service worker is what makes the page files available offline.
- Eviction means the browser deleting stored data to free space. By default, browser storage is best effort: the browser may delete an origin's data under storage pressure, and MDN notes Safari deletes script-created data for an origin with no user interaction in seven days of browser use when cross-site tracking prevention is on.
navigator.storage.persist()asks the browser to keep the data until the user clears it; the browser may or may not agree (Firefox asks the user; Safari and Chromium decide from the user's history with the site). So request persistence at startup, treat afalseresult as normal, and keep the sync window short: the sooner a checklist reaches the server, the less it depends on the tablet's storage. - Photos are Blobs in IndexedDB uploaded as separate requests with their own client-generated ids, and the checklist references them. The checklist is marked sent only after the server confirms it, and its photos are confirmed separately.
When and how it retries
Triggers: right after Submit, when the page loads, when the browser fires the online event, when the app returns to the foreground (the tab becomes visible again; the browser fires a visibilitychange event), and on a timer after a failure. Do not wait for online alone: navigator.onLine can be true on a network that reaches no internet (MDN: a LAN connection counts as online), and MDN calls the property inherently unreliable, so use it as a hint and let a real request failure be the truth. The Background Synchronization API (a service worker sync event that fires when connectivity returns) fits this problem, but MDN lists it as limited availability and not Baseline, so it is an enhancement on top of the triggers above rather than the mechanism.
What to retry: network errors, timeouts, 408, 429 and 5xx. What not to retry automatically: other 4xx. A 401 is the exception that waits for a person: flush() stops, keeps the record queued and asks for sign-in, then the next trigger sends it. A 422 means the content is invalid and sending it again changes nothing; a 409 means the id exists with different content. Those go to needs_attention for a human.
The pause grows with each failed attempt and is randomised ("exponential backoff with full jitter"): wait a random time between 0 and min(cap, base * 2^attempt). The randomisation matters at scale: if a depot of tablets regains signal together after a power cut, fixed delays make them all retry in the same instant. Worked numbers for the code below (base 2 s, cap 300 s; the wait after failed attempt n is a random value between 0 and min(300 s, 2 s * 2^n)):
| Failed attempt n | Upper limit of the random wait |
|---|---|
| 1 | 4 s |
| 2 | 8 s |
| 3 | 16 s |
| 4 | 32 s |
| 5 | 64 s |
| 6 | 128 s |
| 7 | 256 s |
| 8 and later | 300 s (512 s would exceed the cap) |
Each actual wait is drawn at random inside its range, so two tablets that failed together will retry at different moments. Retry-After is a response header telling the client when to try again, written either as a number of seconds (Retry-After: 120) or as an HTTP date (Retry-After: Wed, 21 Oct 2015 07:28:00 GMT); honour it when the server sends one, and take the later of it and the backoff wait. The code below keeps to the backoff alone for brevity.
How the server avoids saving it twice
PUT /v1/inspections/{id} (an alternative is POST plus an Idempotency-Key header, a client-chosen token the server uses to recognise repeats) with the client-generated UUID (universally unique identifier, a random 128-bit id) in the path. MDN describes PUT as idempotent (calling it once is no different from calling it several times), and says the server answers 201 Created when it creates the resource and 200 or 204 when the resource already existed and was modified. Here the server uses 201 for the first save and 200 for a replay of identical content, and the client treats both as success.
DROP TABLE IF EXISTS inspections;
CREATE TABLE inspections (
id uuid PRIMARY KEY, -- generated on the tablet, the idempotency key
technician_id integer NOT NULL,
site text NOT NULL,
answers jsonb NOT NULL,
payload_hash text NOT NULL, -- fingerprint of the body, to tell a replay from a clash
completed_at timestamptz NOT NULL, -- when the technician finished (tablet clock)
received_at timestamptz NOT NULL DEFAULT now()
);
The primary key on id is what makes a duplicate impossible, even when two requests for the same id arrive at the same moment (a retry while the first attempt is still in flight). The insert uses ON CONFLICT (id) DO NOTHING (if a row with that primary key already exists, skip the insert instead of raising an error). The answers column is jsonb, PostgreSQL's binary JSON type. When the insert stores nothing, the server compares the stored payload_hash (a fingerprint of the body) and the technician id with the new request: same content from the same technician is a replay (200), anything else is a clash (409 Conflict, which MDN defines as a conflict with the current state of the target resource).
The code
How to read the browser code: IndexedDB works through events. tx() in outbox.js opens the database, starts a transaction (a group of reads and writes that complete together) on the outbox object store (IndexedDB's name for a table), runs the function passed in, and wraps the callbacks in a promise: oncomplete means the transaction has committed so the promise resolves, onerror rejects it. By default the browser may report a commit before it has verified the bytes on the storage medium (a power cut can still lose the last write); passing { durability: 'strict' } to db.transaction asks it to wait for persistent storage first, which is worth the small cost for a record the technician believes is saved (the code below leaves the default durability for brevity). Every outbox read and write goes through it, and save() awaits it before any network call, which is why a submitted checklist survives the page or app being closed or crashing. In the server, faults.loseResponses is a fault injector for the demo: when above zero the server saves the checklist and then cuts the connection without replying, which reproduces a lost response on demand.
The outbox (browser side), the page that uses it, the server, and the driver that exercises all of it in a real Chromium:
// outbox.js
const cfg = () => window.OUTBOX_RETRY || { base: 2000, cap: 300000 }; // ms
const open = () => new Promise((ok, no) => {
const r = indexedDB.open('inspections', 1);
r.onupgradeneeded = () => r.result.createObjectStore('outbox', { keyPath: 'id' });
r.onsuccess = () => ok(r.result); r.onerror = () => no(r.error);
});
const tx = async (mode, fn) => {
const db = await open();
return new Promise((ok, no) => {
const t = db.transaction('outbox', mode); const out = fn(t.objectStore('outbox'));
t.oncomplete = () => { db.close(); ok(out.result); }; t.onerror = () => no(t.error);
});
};
export const requestPersistence = async () => (navigator.storage?.persist ? navigator.storage.persist() : false);
export const pending = () => tx('readonly', (s) => s.count());
export async function save({ site, answers }) {
const item = { id: crypto.randomUUID(), site, answers, completed_at: new Date().toISOString(), attempts: 0, state: 'queued' };
await tx('readwrite', (s) => s.put(item)); // durable on the tablet BEFORE any network call
return item.id;
}
let flushing = false, timer = null;
const say = (t) => window.dispatchEvent(new CustomEvent('outbox-state', { detail: t }));
export async function flush() {
if (flushing) return; flushing = true; clearTimeout(timer);
try {
const items = await tx('readonly', (s) => s.getAll());
for (const item of items.filter((i) => i.state === 'queued')) {
say('Sending...');
let res;
try {
res = await fetch(`/v1/inspections/${item.id}`, {
method: 'PUT', headers: { 'content-type': 'application/json', 'x-demo-user': '7' },
body: JSON.stringify({ site: item.site, answers: item.answers, completed_at: item.completed_at }),
signal: AbortSignal.timeout(15000) });
} catch { res = null; } // no signal, timeout, or lost response
if (res && (res.status === 200 || res.status === 201)) {
await tx('readwrite', (s) => s.delete(item.id)); // delete locally only after the server confirmed
say('Sent. The office has your checklist.');
} else if (res && res.status === 401) { // signed out while offline: keep the record, ask for sign-in, no timer
say('Please sign in again. Your checklist is safe on this tablet.');
break;
} else if (res && res.status >= 400 && res.status < 500 && res.status !== 429 && res.status !== 408) {
await tx('readwrite', (s) => s.put({ ...item, state: 'needs_attention', status: res.status }));
say('This checklist was rejected. Tap to review it.');
} else { // retryable: network error, 5xx, 429, 408
const n = item.attempts + 1;
await tx('readwrite', (s) => s.put({ ...item, attempts: n }));
const { base, cap } = cfg();
const wait = Math.random() * Math.min(cap, base * 2 ** n); // exponential backoff with full jitter
say('Saved on this tablet. Will retry automatically.');
timer = setTimeout(flush, wait);
break;
}
}
} finally { flushing = false; }
}
<!-- index.html -->
<!doctype html>
<meta charset="utf-8">
<title>Inspection</title>
<form id="f">
<label>Site <input id="site" required></label>
<label><input type="checkbox" id="q1"> Fire exit clear</label>
<label><input type="checkbox" id="q2"> Extinguisher in date</label>
<button id="submit">Submit checklist</button>
</form>
<p id="status" role="status" aria-live="polite"></p>
<script type="module">
import { save, flush, pending, requestPersistence } from '/outbox.js';
const status = document.getElementById('status');
const show = (t) => { status.textContent = t; };
window.addEventListener('outbox-state', (e) => show(e.detail));
await requestPersistence();
document.getElementById('f').addEventListener('submit', async (ev) => {
ev.preventDefault();
document.getElementById('submit').disabled = true; // one tap makes one record
await save({ site: site.value, answers: { q1: q1.checked, q2: q2.checked } });
show('Saved on this tablet. It will be sent when there is a signal.');
flush();
});
if ((await pending()) > 0) { show('1 checklist waiting to be sent.'); flush(); }
window.addEventListener('online', flush);
document.addEventListener('visibilitychange', () => { if (!document.hidden) flush(); });
</script>
// server.mjs
import http from 'node:http';
import crypto from 'node:crypto';
import fs from 'node:fs';
export function makeServer(pool, faults = { loseResponses: 0 }) {
const seen = new Map(); // id -> number of requests received
const srv = http.createServer(async (req, res) => {
const url = new URL(req.url, 'http://x');
if (req.method === 'GET' && url.pathname === '/') { res.setHeader('content-type', 'text/html'); return res.end(fs.readFileSync('index.html')); }
if (req.method === 'GET' && url.pathname === '/outbox.js') { res.setHeader('content-type', 'text/javascript'); return res.end(fs.readFileSync('outbox.js')); }
const m = url.pathname.match(/^\/v1\/inspections\/([0-9a-f-]{36})$/);
if (!m || req.method !== 'PUT') { res.statusCode = 404; return res.end(); }
const tech = Number(req.headers['x-demo-user']);
if (!tech) { res.statusCode = 401; return res.end(); }
let raw = ''; for await (const c of req) raw += c;
let b; try { b = JSON.parse(raw); } catch { res.statusCode = 400; return res.end(); }
if (typeof b.site !== 'string' || !b.site || typeof b.answers !== 'object' || isNaN(Date.parse(b.completed_at))) {
res.statusCode = 422; return res.end(JSON.stringify({ error: 'invalid_checklist' }));
}
seen.set(m[1], (seen.get(m[1]) || 0) + 1);
const hash = crypto.createHash('sha256').update(JSON.stringify([b.site, b.answers, b.completed_at])).digest('hex');
const ins = await pool.query(
`INSERT INTO inspections (id, technician_id, site, answers, payload_hash, completed_at)
VALUES ($1,$2,$3,$4,$5,$6) ON CONFLICT (id) DO NOTHING RETURNING id`,
[m[1], tech, b.site, b.answers, hash, b.completed_at]);
let status = 201;
if (ins.rowCount === 0) { // this id was saved before: replay or clash?
const { rows: [old] } = await pool.query('SELECT payload_hash, technician_id FROM inspections WHERE id=$1', [m[1]]);
status = old.payload_hash === hash && old.technician_id === tech ? 200 : 409;
}
if (faults.loseResponses > 0) { faults.loseResponses--; return req.socket.destroy(); } // saved, but the answer never arrives
res.statusCode = status; res.setHeader('content-type', 'application/json'); res.end(JSON.stringify({ id: m[1] }));
});
srv.seen = seen; return srv;
}
// run.mjs
import pg from 'pg';
import fs from 'node:fs';
import { chromium } from 'playwright';
import { makeServer } from './server.mjs';
const pool = new pg.Pool({ host: 'demo-pg', user: 'postgres', password: 'pw', database: 'postgres' });
await pool.query(fs.readFileSync('schema.sql', 'utf8'));
const faults = { loseResponses: 1 };
const srv = makeServer(pool, faults).listen(3200);
const count = async () => (await pool.query('SELECT count(*)::int n FROM inspections')).rows[0].n;
const idb = (page) => page.evaluate(() => new Promise((ok) => {
const r = indexedDB.open('inspections', 1);
r.onsuccess = () => { const q = r.result.transaction('outbox').objectStore('outbox').getAll(); q.onsuccess = () => ok(q.result.map((i) => i.state + ':' + i.attempts)); };
}));
const text = (page) => page.locator('#status').innerText();
const browser = await chromium.launch();
const ctx = await browser.newContext();
await ctx.addInitScript(() => { window.OUTBOX_RETRY = { base: 40, cap: 200 }; });
const page = await ctx.newPage();
await page.goto('http://127.0.0.1:3200/');
// 1. Technician is in the basement: no signal
await page.route('**/v1/**', (r) => r.abort('internetdisconnected'));
await page.fill('#site', 'Plant room B2'); await page.check('#q1');
await page.click('#submit');
const shown = await (await page.waitForFunction(() => {
const t = document.getElementById('status').innerText;
return t.includes('retry automatically') ? t : false;
})).jsonValue();
console.log('1 offline submit | screen:', shown, '| server rows:', await count(), '| tablet outbox:', (await idb(page)).length);
// 2. Page is reloaded (or the app killed) while still offline: the checklist survives
await page.reload();
await page.waitForFunction(() => document.getElementById('status').innerText.length > 0);
console.log('2 reload offline | tablet outbox kept:', (await idb(page)).length === 1, '| server rows:', await count());
// 3. Signal returns, but the first success response is lost after the server saved it
await page.unroute('**/v1/**');
await page.waitForFunction(() => document.getElementById('status').innerText.startsWith('Sent'));
const id = [...srv.seen.keys()][0];
console.log('3 signal back | screen:', await text(page), '| server rows:', await count(), '| server received the same id more than once:', srv.seen.get(id) > 1, '| tablet outbox:', (await idb(page)).length);
// 4. Same id, different content is a clash, not a replay
const clash = await fetch(`http://127.0.0.1:3200/v1/inspections/${id}`, { method: 'PUT', headers: { 'content-type': 'application/json', 'x-demo-user': '7' },
body: JSON.stringify({ site: 'Other site', answers: {}, completed_at: new Date().toISOString() }) });
const bad = await fetch(`http://127.0.0.1:3200/v1/inspections/${id}`, { method: 'PUT', headers: { 'content-type': 'application/json', 'x-demo-user': '7' }, body: JSON.stringify({ site: '' }) });
console.log('4 same id, other body ->', clash.status, '| empty site ->', bad.status, '| server rows:', await count());
await browser.close(); srv.close(); await pool.end();
Run in the mcr.microsoft.com/playwright:v1.48.2-jammy container against postgres:16, it prints:
1 offline submit | screen: Saved on this tablet. Will retry automatically. | server rows: 0 | tablet outbox: 1
2 reload offline | tablet outbox kept: true | server rows: 0
3 signal back | screen: Sent. The office has your checklist. | server rows: 1 | server received the same id more than once: true | tablet outbox: 0
4 same id, other body -> 409 | empty site -> 422 | server rows: 1
Reading it: in step 1 the API is blocked, the checklist is on the tablet (outbox 1) and not on the server (0 rows). Step 2 reloads the page while still blocked and the record survives. In step 3 the block is lifted, and the server is told to save the first request and then drop the connection before replying, so the tablet never learns it succeeded. The tablet retries the same id; the server received it more than once yet holds one row, and the screen ends on "Sent". Step 4 shows the two refusals: same id with different content gets 409, an empty site gets 422. The four lines are deterministic: they print the same way on repeated runs, because every step waits for a specific screen text or response rather than for a fixed delay, and step 1 prints the text it matched rather than re-reading the screen afterwards (the retry timer can already have changed the screen to "Sending..." by then). The driver overrides the retry delays to a base of 40 ms and a cap of 200 ms (window.OUTBOX_RETRY) so the demo finishes in seconds; the shipped defaults are the 2 s and 300 s of the table above.
The concurrent case, two requests for one id at the same instant, is shown with two database sessions (race.sh, executed inside the Postgres container as the last command of the Running the code section):
P="psql -U postgres -qAt"
$P -c "TRUNCATE inspections"
INS="INSERT INTO inspections (id, technician_id, site, answers, payload_hash, completed_at) VALUES ('11111111-1111-1111-1111-111111111111', 7, 'Plant room B2', '{}', 'h', now()) ON CONFLICT (id) DO NOTHING RETURNING 'inserted'"
TS="SELECT extract(epoch FROM clock_timestamp())"
printf "BEGIN;\n$INS;\nSELECT pg_sleep(1.5);\n$TS;\nCOMMIT;\n" | $P > a.out &
sleep 0.5
$P -c "$INS; $TS" > b.out
wait
echo "session A rows inserted: $(grep -c inserted a.out)"
echo "session B rows inserted: $(grep -c inserted b.out)"
echo "session B returned after A reached its commit: $(awk 'NR==FNR{ if ($1+0>1e9) a=$1; next } { if ($1+0>1e9) b=$1 } END { print (b>a) ? "true" : "false" }' a.out b.out)"
$P -c "SELECT 'rows in table: ' || count(*) FROM inspections"
session A rows inserted: 1
session B rows inserted: 0
session B returned after A reached its commit: true
rows in table: 1
How race.sh proves it, line by line: it empties the table, then starts session A, which opens a transaction, inserts the row, sleeps 1.5 s with pg_sleep and records a timestamp just before committing. Half a second later session B runs the same insert and records a timestamp when it returns. The shell then prints how many rows each session inserted, whether B's timestamp is later than A's (it can only be later if B waited for A's commit), and the row count. Session B's insert waited for session A to commit, then skipped, so the table holds one row. Without the unique key, nothing would make session B wait or skip, and both sessions could insert the same checklist.
Design choices and what would change them
- Client-generated id plus
PUT, versus anIdempotency-Keyheader onPOST. Either gives retries a stable identity; the path id makes the server record itself addressable and needs no key-expiry table. If the API must stayPOST /inspectionsfor other clients, keep a separate keys table with an expiry and store the first response to replay it. - Tablet clock.
completed_atcomes from the tablet and can be wrong;received_atis the server's. Show the technician the tablet time, sort and audit by the server time, and do not usecompleted_atfor anything where being wrong has consequences. - Large queues. Send oldest first and one at a time, so a slow connection finishes whole checklists rather than starting many.
- Authentication expiring while offline. The
401branch inflush()keeps the recordqueuedand prompts for sign-in; the run above does not trigger it. The demo's header user id stands in for a session.
Running the code
cd work # holds outbox.js index.html server.mjs run.mjs schema.sql race.sh
echo '{"type":"module","dependencies":{"pg":"8.13.1","playwright":"1.48.2"}}' > package.json
docker network create demo-net
docker run -d --rm --name demo-pg --network demo-net -e POSTGRES_PASSWORD=pw postgres:16
until docker exec demo-pg pg_isready -h 127.0.0.1 -q; do sleep 1; done
docker run --rm --ipc=host --network demo-net -v "$PWD":/w -w /w mcr.microsoft.com/playwright:v1.48.2-jammy sh -c 'npm i --silent && node run.mjs'
docker cp race.sh demo-pg:/race.sh && docker exec -w /tmp demo-pg bash /race.sh
Take the follow button on a profile page from tap to database row. Cover what the client shows, the API calls involved, how follows are stored, and what happens when a user double-taps, two people follow the same account at once, or the account has millions of followers.
Sample Answer
Direct answer
A follow is one row in an edge table (a table whose rows are the links between two things, here two users), follows(follower_id, followee_id), with a composite primary key (a key made of two columns together) so the same pair can exist only once. The button flips at once on tap (an optimistic update), sends an idempotent "make this follow exist" request, and settles to the {following, follower_count} the server returns. A double tap and a retry are harmless because the database rejects the duplicate row. Two people following at once are safe because each inserts a different row and the counter increments are serialised (made to run one after another) by the row lock (while one transaction is changing a row, any other transaction that wants to change that same row waits until the first one commits; a transaction is a group of statements that succeed or fail together). A million-follower account needs a different counter (spread across several rows) and cursor-based follower lists (each page asks for "rows after the last one you saw" instead of "skip N rows"), but the same edge table.
Tap to row, layer by layer
| Layer | Decision |
|---|---|
| UI | Two button states, Follow and Following. A tap shows the new state immediately and the button stays tappable, so there is no separate spinner state. The profile payload carries viewer_follows so the first paint is right. Unfollow asks for confirmation only on private accounts or on a long press, never on a plain tap. |
| API | PUT /v1/users/{id}/follow and DELETE /v1/users/{id}/follow, no body, both answer 200 {"following": true, "follower_count": 201}. GET /v1/users/{id}/followers?limit=50&cursor=... lists followers. Following yourself returns 422. |
| Logic | One transaction: insert or delete the edge, then move the two counters by the number of rows that changed. |
| Data | follows edge table plus denormalised counters (a stored copy of a count, kept so profile reads do not run count(*)). |
The client logic is the same single-request-in-flight state machine used for a like button: taps while a request is pending only change what the user wants, one follow-up request goes out if the answer disagrees, and a failure restores the last confirmed state.
Schema and the write path
CREATE TABLE users (
id bigint PRIMARY KEY,
follower_count integer NOT NULL DEFAULT 0 CHECK (follower_count >= 0),
following_count integer NOT NULL DEFAULT 0 CHECK (following_count >= 0)
);
CREATE TABLE follows (
follower_id bigint NOT NULL REFERENCES users(id),
followee_id bigint NOT NULL REFERENCES users(id),
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (follower_id, followee_id),
CHECK (follower_id <> followee_id)
);
CREATE INDEX follows_by_followee ON follows (followee_id, created_at DESC, follower_id);
CREATE FUNCTION set_follow(p_me bigint, p_target bigint, p_follow boolean)
RETURNS TABLE (out_following boolean, out_followers integer)
LANGUAGE plpgsql AS $$
DECLARE changed integer;
BEGIN
PERFORM 1 FROM users WHERE id IN (p_me, p_target) ORDER BY id FOR UPDATE;
IF p_follow THEN
INSERT INTO follows (follower_id, followee_id) VALUES (p_me, p_target) ON CONFLICT DO NOTHING;
ELSE
DELETE FROM follows WHERE follower_id = p_me AND followee_id = p_target;
END IF;
GET DIAGNOSTICS changed = ROW_COUNT;
UPDATE users SET follower_count = follower_count + CASE WHEN p_follow THEN changed ELSE -changed END WHERE id = p_target;
UPDATE users SET following_count = following_count + CASE WHEN p_follow THEN changed ELSE -changed END WHERE id = p_me;
RETURN QUERY SELECT p_follow, u.follower_count FROM users u WHERE u.id = p_target;
END $$;
INSERT INTO users SELECT g FROM generate_series(1, 300) g;
SELECT * FROM set_follow(1, 2, true); -- run twice
SELECT * FROM set_follow(1, 2, false); -- run twice
SELECT * FROM set_follow(5, 5, true);
- The
PERFORM ... ORDER BY id FOR UPDATEline locks both user rows in id order before anything else. Without it, user 1 following user 2 updates row 2 (the target) and then row 1, while user 2 following user 1 updates row 1 and then row 2 (the function updates the target's counter first and the follower's own counter second), and each can wait forever for the other (a deadlock; PostgreSQL aborts one of them). Locking in a fixed order removes that cycle. - Worked timeline of that deadlock with ids, using the unlocked order above: at time 1, A (user 1 follows user 2) locks row 2; at time 1, B (user 2 follows user 1) locks row 1; at time 2, A asks for row 1 and waits for B; at time 2, B asks for row 2 and waits for A. Each holds what the other needs, so neither can move. Sorting by
idmeans both A and B ask for row 1 first, so one simply waits for the other and then continues. ON CONFLICT DO NOTHINGskips the insert if the pair already exists, andGET DIAGNOSTICS changed = ROW_COUNTstores how many rows the insert or delete actually changed (1, or 0 for a repeat). The counters then move bychanged, so a repeat moves no counter.- The index on
(followee_id, created_at DESC, follower_id)serves the follower list, and the primary key serves "does the viewer follow this account?" with a single lookup.
Run in a postgres:16 container, the double tap prints follower counts t | 1, t | 1, f | 0, f | 0, and the self follow fails with violates check constraint "follows_check".
Double tap and simultaneous followers
Two taps from one user: the second INSERT waits for the first transaction, hits the primary key, does nothing, and returns the same answer. Two different users: their rows differ, so both inserts succeed, and the two UPDATE users statements queue on the target's row and each adds one to the latest value.
Checked with pgbench (PostgreSQL's load-testing tool; each client is one database session) in the same container. Three small script files: a.sql is SELECT set_follow(1,2,true);, b.sql is SELECT set_follow(2,1,true);, and f.sql is \set u :base + :client_id then SELECT set_follow(:u, 2, true);. Users 1 and 2 follow each other from 40 sessions at once, 50 transactions per session, each picking one of the two directions at random; then 200 different users follow user 2 in five waves of 40 simultaneous sessions. Deadlocks are read from the server's own counter, SELECT deadlocks FROM pg_stat_database WHERE datname = current_database(), before and after:
pgbench -n -c 40 -t 50 -f a.sql -f b.sql demo
for base in 3 43 83 123 163; do pgbench -n -c 40 -t 1 -D base=$base -f f.sql demo; done
counter_user2 | edges_user2 | counter_user1 | edges_user1
---------------+-------------+---------------+-------------
201 | 201 | 1 | 1
deadlocks during the run: 0
Reading the columns: counter_user2 is the stored follower count of user 2 and edges_user2 is a live count(*) of rows in follows pointing at user 2; they match at 201 (200 followers plus user 1), which shows no increment was lost. counter_user1 and edges_user1 are the same pair for user 1, who is followed by user 2 only, so both are 1. The repeated mutual follows in both directions are the deadlock trap, and the unchanged deadlock counter shows none occurred, because the fixed lock order removes the cycle. The check can fail: with the PERFORM ... FOR UPDATE line deleted from the function, the mutual-follow run made PostgreSQL abort transactions with deadlock errors. In one measured shorter run (-t 3, 3 transactions per session) the deadlock counter rose by about a hundred, and the full -t 50 run is slow because each deadlock waits out PostgreSQL's 1 second deadlock_timeout before one transaction is aborted; the exact count varies from run to run.
An account with millions of followers
Three things break first, in this order:
- The counter row becomes a queue. (Fan-out, used in point 3, means copying one event to many recipients, for example writing a new post into each follower's feed; a shard is one of several rows or partitions that share the work of one thing.) Every follow of that account updates the same
usersrow, and those updates run one after another. Find out whether it matters by watching lock wait time on that row (how long updates sit waiting for the lock); do not shard before measuring, since most accounts never need it. When it does matter, give the account several counter rows and add to a random one:
CREATE TABLE follower_shards (
user_id bigint NOT NULL, shard smallint NOT NULL, n bigint NOT NULL DEFAULT 0,
PRIMARY KEY (user_id, shard)
);
CREATE FUNCTION bump_followers(p_user bigint) RETURNS void LANGUAGE sql AS $$
INSERT INTO follower_shards (user_id, shard, n)
VALUES (p_user, floor(random() * 16)::smallint, 1)
ON CONFLICT (user_id, shard) DO UPDATE SET n = follower_shards.n + 1;
$$;
SELECT bump_followers(99) FROM generate_series(1, 1000);
SELECT sum(n) AS follower_count, count(*) AS shard_rows, max(n) < 1000 AS spread_over_rows
FROM follower_shards WHERE user_id = 99;
Reading the code: floor(random() * 16)::smallint picks a shard number from 0 to 15 for each call; ON CONFLICT (user_id, shard) DO UPDATE SET n = follower_shards.n + 1 creates that shard's row with n = 1 the first time and otherwise adds 1 to it; the generate_series(1, 1000) call performs 1,000 follows. In the final query, sum(n) is the follower count, count(*) is how many shard rows exist, and max(n) < 1000 is true when no single row holds all 1,000, meaning the load was spread. Run in the same container this prints 1000 | 16 | t: the total is exact and the writes landed on 16 rows (each row holds about 1000/16, roughly 62, so the busiest row's queue is roughly a dozen times shorter than a single counter row's; the split among shards varies per run, the total does not). Reads sum 16 small rows, which is cheap, and the profile can cache the sum for a few seconds. The trade-off is that concurrent writers no longer wait for each other, at the price of an extra read step and a rule that only flagged hot accounts use shards.
- The follower list. Use keyset pagination (also called cursor-based), which means the cursor is the
(created_at, follower_id)of the last row shown and the next page asks for rows after it.OFFSET 5000000has to walk past five million rows; the keyset query jumps straight into the index. - Fan-out. It is tempting to push the new follow (or the followee's later posts) into every follower's stored feed at write time; for a million followers that is a million writes inside one tap. Following must not copy anything to the followee's followers. Whatever builds feeds reads the edge table later, outside this request.
Test, roll out, observe
- Tests: repeat and concurrent calls end with
follower_countequal to the number of edge rows (the check above), a mutual-follow stress test must produce zero deadlock errors, and a Playwright test with the route aborted must show the button reverting. - Roll out: ship the edge table and endpoints first, backfill counters (fill them in for existing data) from
count(*)per user, then switch the profile read, behind a flag. - Observe: lock wait on hot user rows, rate of
422and5xxon the follow endpoints, and a scheduled job that compares each sampled user's counter tocount(*)on the edge table.
Pitfalls
Do not make follow a toggle endpoint, do not read the follower count and then write it back from application code (two requests overwrite each other), and do not shard counters for every account because most accounts never need it.
You are adding threaded comments to a post page that a web app and a mobile app both use. Walk me through the feature from the comment box down to the tables: the API contract the two clients share, how replies and pagination work, how the comment count stays right, and how frontend and backend can build in parallel.
Sample Answer
Direct answer
Both apps call the same versioned JSON API with three operations: list a post's top-level comments, list one comment's replies, and create or delete a comment. Threads are exactly two levels deep (a top-level comment and its replies). Lists use cursor pagination (the client sends back an opaque bookmark for the last item it saw, not a page number). The post's comment_count is a stored number changed in the same transaction as the insert or delete, and a client-generated client_request_id (a random id the client makes once per submit and resends on every retry, so the server can recognise a repeat) makes a retried submit create nothing new. Frontend and backend build in parallel against an agreed API description and shared example payloads.
Two ideas carry most of the design. Cursor pagination keeps a long thread stable while new comments arrive, and the client_request_id plus a database uniqueness rule makes every write safe to repeat. The rest of the answer shows how each one works.
The contract both clients share
| Call | Body or query | Response |
|---|---|---|
GET /v1/posts/{id}/comments | limit (default 20, max 50), cursor | 200 {"items": [...], "next_cursor": "..." or null} |
GET /v1/comments/{id}/replies | limit, cursor | same shape, oldest reply first |
POST /v1/posts/{id}/comments | {"body": "...", "parent_id": "..." or null, "client_request_id": "uuid"} | 201 {"comment": {...}, "comment_count": 6}; a repeat of the same client_request_id returns 200 with the same comment |
DELETE /v1/comments/{id} | none | 200 {"comment_count": 5}; repeating it returns the same answer |
One top-level item looks like this:
{
"id": "6", "author": {"id": "42", "name": "Dana", "avatar_url": "https://cdn.example.com/a/42.jpg"},
"body": "text", "created_at": "2026-01-01T10:02:00Z", "deleted": false,
"reply_count": 1,
"replies": {"items": [ /* the first 2 replies */ ], "next_cursor": null}
}
Rules that keep a web app and a mobile app on one contract:
- Ids are strings and cursors are opaque, so the server can change their internals. Timestamps are UTC ISO-8601.
- Only additive changes inside
v1(new optional fields; nothing is removed, renamed or made mandatory, so an old client keeps working). Clients ignore fields they do not know. Mobile apps stay installed for months, so the server must keep answering old versions. - A deleted comment that still has replies is returned with
"deleted": trueand nobody(a tombstone, a placeholder that keeps the thread readable). Rendering it as "Comment removed" is the client's job. - Validation errors use one shape,
422 {"errors": [{"field": "body", "code": "too_long"}]}, so both clients map them identically.
Replies are embedded as a preview (first 2) so one request draws the whole first screen, which matters on a mobile network. "View 12 more replies" calls the replies endpoint with replies.next_cursor.
Why two levels
A reply to a reply attaches to the same top-level comment and carries an @name mention in its text. Deeper nesting costs a recursive query (a query that repeatedly follows parent links to walk a tree), a layout that runs out of horizontal space on a phone, and moderation complexity. If product later needs deep trees, the change is an added path column on comments and a new response field, and nothing in the contract above breaks. The database enforces the rule rather than trusting the clients.
Tables, count and queries
CREATE TABLE post_stats (id bigint PRIMARY KEY, comment_count integer NOT NULL DEFAULT 0 CHECK (comment_count >= 0));
CREATE TABLE comments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
post_id bigint NOT NULL REFERENCES post_stats(id),
parent_id bigint REFERENCES comments(id),
author_id bigint NOT NULL,
body text NOT NULL CHECK (char_length(body) BETWEEN 1 AND 2000),
client_request_id uuid NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
deleted_at timestamptz,
UNIQUE (author_id, client_request_id)
);
CREATE INDEX comments_top ON comments (post_id, created_at DESC, id DESC) WHERE parent_id IS NULL;
CREATE INDEX comments_reply ON comments (parent_id, created_at, id) WHERE parent_id IS NOT NULL;
-- A trigger is a function the database runs automatically on every INSERT. This one runs BEFORE the row is stored.
-- p comments%ROWTYPE is a variable shaped like one row of comments (the parent row).
-- replies may only target a top-level comment of the SAME post (threads are two levels deep)
CREATE FUNCTION check_parent() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE p comments%ROWTYPE;
BEGIN
IF NEW.parent_id IS NOT NULL THEN
SELECT * INTO p FROM comments WHERE id = NEW.parent_id;
IF p.post_id <> NEW.post_id THEN RAISE EXCEPTION 'parent_in_other_post'; END IF;
IF p.parent_id IS NOT NULL THEN RAISE EXCEPTION 'reply_to_reply_not_allowed'; END IF;
END IF;
RETURN NEW;
END $$;
CREATE TRIGGER comments_check_parent BEFORE INSERT ON comments FOR EACH ROW EXECUTE FUNCTION check_parent();
CREATE FUNCTION add_comment(p_post bigint, p_parent bigint, p_author bigint, p_body text, p_req uuid)
RETURNS TABLE (out_id bigint, out_replayed boolean, out_post_count integer)
LANGUAGE plpgsql AS $$
DECLARE new_id bigint;
BEGIN
INSERT INTO comments (post_id, parent_id, author_id, body, client_request_id)
VALUES (p_post, p_parent, p_author, p_body, p_req)
ON CONFLICT (author_id, client_request_id) DO NOTHING
RETURNING id INTO new_id;
IF new_id IS NOT NULL THEN
UPDATE post_stats SET comment_count = comment_count + 1 WHERE id = p_post;
RETURN QUERY SELECT new_id, false, c.comment_count FROM post_stats c WHERE c.id = p_post;
ELSE
RETURN QUERY SELECT x.id, true, c.comment_count FROM comments x, post_stats c
WHERE x.author_id = p_author AND x.client_request_id = p_req AND c.id = x.post_id;
END IF;
END $$;
CREATE FUNCTION delete_comment(p_id bigint, p_author bigint) RETURNS integer LANGUAGE plpgsql AS $$
DECLARE n integer; pid bigint;
BEGIN
UPDATE comments SET deleted_at = now()
WHERE id = p_id AND author_id = p_author AND deleted_at IS NULL RETURNING post_id INTO pid;
GET DIAGNOSTICS n = ROW_COUNT;
IF n = 1 THEN UPDATE post_stats SET comment_count = comment_count - 1 WHERE id = pid; END IF;
RETURN (SELECT comment_count FROM post_stats WHERE id = coalesce(pid, (SELECT post_id FROM comments WHERE id = p_id)));
END $$;
INSERT INTO post_stats VALUES (1, 0);
-- 5 top-level comments, one minute apart
INSERT INTO comments (post_id, author_id, body, client_request_id, created_at)
SELECT 1, g, 'top ' || g, gen_random_uuid(), timestamptz '2026-01-01 10:00:00+00' + g * interval '1 minute'
FROM generate_series(1, 5) g;
UPDATE post_stats SET comment_count = 5 WHERE id = 1;
UNIQUE (author_id, client_request_id)is the idempotency guard (idempotent: doing it twice has the same effect as once): a retry after a lost response inserts nothing and gets the original row back.- The trigger rejects a reply whose parent is itself a reply or belongs to a different post. It looks up the parent row into
p, raises an error ifp.post_iddiffers, and raises another ifp.parent_idis set (the parent is itself a reply). Raising an error aborts the whole insert. add_commenttries the insert withON CONFLICT ... DO NOTHING RETURNING id INTO new_id:new_idis filled only if a row was really inserted. If it is set, the counter goes up by 1; if it is null, the same author and request id already exist, so the function returns that existing comment and flagsreplayed.delete_commentstampsdeleted_atonly on a row that is not yet deleted, remembering its post inpid. The count drops only when exactly one row changed (n = 1). The finalRETURNreads the post's count; thecoalesce(pid, (SELECT post_id ...))part finds the post from the comment itself when nothing changed (a repeat), so a repeated delete still returns the count.- The two partial indexes (indexes that only contain rows matching a
WHERE) match the two list queries exactly: top-level comments for a post newest first, and replies for a parent oldest first. comment_countcounts visible comments and replies together. Create adds 1 only when a row was actually inserted; delete subtracts 1 only whendeleted_atwas null before, so repeats cannot drift it.
Driving it:
SELECT * FROM add_comment(1, 2, 42, 'reply to top 2', '11111111-1111-1111-1111-111111111111');
SELECT * FROM add_comment(1, 2, 42, 'reply to top 2', '11111111-1111-1111-1111-111111111111');
SELECT * FROM add_comment(1, 6, 42, 'too deep', '22222222-2222-2222-2222-222222222222');
SELECT delete_comment(6, 42);
SELECT delete_comment(6, 42);
SELECT id, body, created_at FROM comments
WHERE post_id = 1 AND parent_id IS NULL
ORDER BY created_at DESC, id DESC LIMIT 3;
SELECT id, body, created_at FROM comments
WHERE post_id = 1 AND parent_id IS NULL
AND (created_at, id) < ('2026-01-01 10:04:00+00', 4)
ORDER BY created_at DESC, id DESC LIMIT 3;
Run in a postgres:16 container, the output is:
reply, first call: out_id 6 | out_replayed f | out_post_count 6
reply, same request id: out_id 6 | out_replayed t | out_post_count 6
reply to reply: ERROR: reply_to_reply_not_allowed
delete twice: 5, then 5
page 1 (ids): 5, 4, 3
page 2 (ids): 3, 2, 1
The condition (created_at, id) < ('2026-01-01 10:04:00+00', 4) is a row comparison: it compares created_at first, and only when the two times are equal does it compare id. It means "strictly before the bookmarked comment in newest-first order", and comment 4 itself, which equals the bookmark, is excluded. Tracing the data: the five top-level comments were made one minute apart, so comment 5 is at 10:05, comment 4 at 10:04, down to comment 1 at 10:01. After the bookmark (10:04, 4) the qualifying rows are 3, 2 and 1. The reply with id 6 is not in these lists because it has a parent_id. The count of 6 after the first add_comment is the 5 seeded comments plus the new reply (the UPDATE after the seed set the stored count to 5); the repeat inserts nothing, so it stays 6, and the reply to a reply is rejected by the trigger before any count changes.
Each page query asks for limit + 1 rows (here limit 2, so 3 rows; the printed queries use LIMIT 3 for that reason, so each prints one row more than the client receives). The server returns the first 2 and sets next_cursor only if the extra row exists, which tells the client whether more comments exist without a count(*). On page 1 it returns 5 and 4 with a cursor encoding (2026-01-01T10:04:00Z, 4); page 2 starts after that point: the query returns 3, 2 and 1, the server keeps 3 and 2, and it sets a cursor because the extra row, comment 1, exists. A comment posted between the two requests does not shift page 2, which is the failure an OFFSET page number has.
Client behaviour
- The text box keeps its draft until a
201or200arrives. On submit the client adds the comment to the list immediately with a localclient_request_idand a "sending" style; the server's item replaces it. On failure the item shows "Not posted. Retry", and Retry resends the sameclient_request_id. - The comment count in the header is replaced by the number in the create or delete response, never incremented locally forever.
- New comments from other people are fetched on demand ("3 new comments") rather than inserted under the reader's cursor.
Working in parallel
- Write the API description first (an OpenAPI file: a machine-readable document listing every endpoint, its parameters and the shape of each response, for example a
GET /v1/posts/{id}/commentsentry whose 200 response listsitemsandnext_cursor) and review it with both clients and the backend owner. - Stand up a mock server (a fake backend that answers from the description with canned data) generated from it, so web and mobile run against fixtures on day one.
- Keep a shared folder of example responses, including the awkward ones: empty thread, tombstone with replies, last page with
next_cursor: null,422errors. - In CI (the automated checks that run on every change), run a contract test (a test that checks the real server's responses match the agreed description) that calls the real backend and validates every response against the description, so the backend cannot drift from what the clients were built on.
Test, roll out, observe
Database tests: duplicate submit, reply to reply, double delete, and counter equal to the number of non-deleted rows. Client tests: optimistic insert, failed send and retry, tombstone rendering. Roll out behind a flag (a switch that turns the feature on for some users and can turn it off at once), web first. Watch the 422 rate, p95 of the list call (the time that 95% of calls beat), and a scheduled check that comment_count equals the number of visible rows.
Trade-offs
Embedding reply previews makes the first response bigger but saves a round trip per thread. A stored count is fast to read and needs the discipline above; computing count(*) per request is always correct and gets slow on busy posts. Recommended: stored count, with the drift check.
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.