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.
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'
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
A profile editor lets users submit a bio and a photo in one form. Where does validation live on the client and on the server, how does a server-side rejection reach the right field, and what does the user see when the photo is too large, the wrong type, or the connection drops halfway?
Sample Answer
Direct answer
Validation lives in three places that each do a different job. The browser checks for speed and tells the user before anything is sent. The server checks again because the browser can be bypassed, and it alone can check things the browser cannot see (what the bytes of the photo really are, who owns the uploaded file). The database holds a CHECK constraint as the last line of defence. The rules for bio and photo are written once in a module that both the browser bundle (the JavaScript files the build tool packs together and ships to the browser) and the server import, so they cannot disagree. Of the three places, only the server check cannot be bypassed. A server rejection comes back as 422 (Unprocessable Content: the request was well formed but its values failed validation; 400 is for a request that is malformed, for example broken JSON) with a list of {field, code} entries, and the form maps each one to the input it belongs to. The photo and the text are saved in two steps, so a failed photo never loses the bio.
Why two steps, not one multipart form
A multipart form is a single HTTP request that carries several parts, here the text fields and the photo's bytes, in one body.
| One multipart request | Upload photo first, then save the profile | |
|---|---|---|
| Dropped connection | the whole request, bio included, fails; a slow photo blocks the save | only the photo upload fails; the bio is untouched |
| Error routing | one response must describe two kinds of failure | each failure belongs to one step and one field |
| Retry | resend megabytes | retry the upload alone, or retry the tiny PATCH alone |
| Cost | simplest server | an extra round trip and a possible orphan upload (a stored photo that no profile ever references, to be deleted by a cleanup job) to clean up |
Recommended: two steps, because the connection-drop case is explicitly part of the requirement. A single multipart request is reasonable only when the photo is tiny and the form is rarely used on mobile networks.
The flow
- User picks a photo. The browser checks
file.sizeandfile.typeand shows a message at the photo field immediately if either fails.file.typecomes from the file extension and can be wrong, so it is only a quick filter. - The photo uploads to storage (
POST /v1/imagesreturns animage_idand an upload URL, then the bytes go straight to storage). Progress and errors show at the photo field. - On Save, the browser runs
validateProfile, and if clean sendsPATCH /v1/me/profilewith{"bio": "...", "photo_id": "..."}.PATCHis the HTTP method for changing part of a resource (PUTreplaces the whole thing). Here it sets values, so sending it twice leaves the same result and a retry after a dropped connection is safe. - The server runs the same
validateProfile, then the checks only it can do: the photo belongs to this user, its upload finished, its stored size is within the limit, and its first bytes match an allowed image format (the declared type is user-controlled). It also runs anything the client could not know, such as moderation (automated or human review of content, for example to block abusive text). It writes the row in one transaction and returns200with the saved profile, or422. - The database repeats the limit with
bio text CHECK (char_length(bio) <= 160)(aCHECKconstraint is a rule the database enforces on every write), so no future code path can store an oversized bio. Postgreschar_lengthcounts characters, so 160 emoji give160while their UTF-8 size is 640 bytes; the shared rule therefore counts code points too ([...bio.trim()].length), notbio.length, which counts UTF-16 units and would call those 160 emoji 320.
The three units, using one emoji such as the grinning face: a code point is one entry of the Unicode table (usually one letter or symbol), so it counts 1. UTF-16 units are the 16-bit chunks JavaScript strings are made of, and this emoji needs two of them, so .length says 2. UTF-8 bytes are the bytes it takes in a file or on the wire, 4 for this emoji. Multiply by 160: 160 code points, 320 UTF-16 units, 640 bytes. For letters and simple emoji, counting code points matches what the user sees and what the database counts. It is not the same as counting what the eye sees: a family emoji is 5 code points (and 8 UTF-16 units) and a flag is 2 code points, so 32 family emoji already reach the 160 limit. That is accepted, because the limit only has to use the same unit in the browser, the server and the database.
The shared rules and the error mapping
// profile-rules.mjs: ONE module imported by the browser bundle and by the API server
export const LIMITS = { bioMax: 160, photoMaxBytes: 5_000_000, photoTypes: ['image/jpeg', 'image/png', 'image/webp'] };
export function validateProfile({ bio, photo }) {
const errors = [];
const chars = [...(bio ?? '').trim()].length; // code points, same unit as Postgres char_length
if (chars > LIMITS.bioMax) errors.push({ field: 'bio', code: 'too_long', max: LIMITS.bioMax, actual: chars });
if (photo) {
if (photo.size > LIMITS.photoMaxBytes) errors.push({ field: 'photo', code: 'too_large', max: LIMITS.photoMaxBytes, actual: photo.size });
if (!LIMITS.photoTypes.includes(photo.type)) errors.push({ field: 'photo', code: 'wrong_type' });
}
return errors;
}
// Server only: the declared type is user-controlled, so read the first bytes of the stored file.
// Many file formats begin with a fixed "magic number" (file signature) that identifies the format by content, not by name.
// A PNG always starts with the 8 bytes 89 50 4E 47 0D 0A 1A 0A (50 4E 47 spells "PNG"), a JPEG with FF D8 FF,
// and a WebP with the text RIFF, four size bytes, then WEBP.
export function sniffImageType(buf) {
if (buf.length >= 8 && buf.subarray(0, 8).equals(Buffer.from([0x89, 0x50, 0x4e, 0x47, 0x0d, 0x0a, 0x1a, 0x0a]))) return 'image/png';
if (buf.length >= 3 && buf[0] === 0xff && buf[1] === 0xd8 && buf[2] === 0xff) return 'image/jpeg';
if (buf.length >= 12 && buf.toString('latin1', 0, 4) === 'RIFF' && buf.toString('latin1', 8, 12) === 'WEBP') return 'image/webp';
return null;
}
// Client: route the server's 422 body to the right inputs; anything unmatched goes to a form-level banner.
const MESSAGES = {
too_long: (e) => `Keep it to ${e.max} characters (now ${e.actual}).`,
too_large: (e) => `Photo must be ${e.max / 1e6} MB or smaller.`,
wrong_type: () => 'Use a JPEG, PNG or WebP image.',
};
export function applyServerErrors(errors, knownFields) {
const byField = {}, banner = [];
for (const e of errors) {
const msg = (MESSAGES[e.code] ?? (() => 'Please check this field.'))(e);
if (knownFields.includes(e.field)) (byField[e.field] ??= []).push(msg); else banner.push(msg);
}
return { byField, banner, focus: knownFields.find((f) => byField[f]) ?? null };
}
// ---- driver ----
const show = (label, v) => console.log(label.padEnd(34), JSON.stringify(v));
show('bio of 161 ASCII chars', validateProfile({ bio: 'a'.repeat(161) }));
show('bio of 160 + spaces around', validateProfile({ bio: ' ' + 'a'.repeat(160) + ' ' }));
show('bio of 160 emoji (UTF-16 length 320)', validateProfile({ bio: '😀'.repeat(160) }));
show('photo 6 MB gif', validateProfile({ bio: 'hi', photo: { size: 6_000_000, type: 'image/gif' } }));
const png = Buffer.concat([Buffer.from([0x89, 0x50, 0x4e, 0x47, 0x0d, 0x0a, 0x1a, 0x0a]), Buffer.alloc(32)]);
const exe = Buffer.concat([Buffer.from('MZ'), Buffer.alloc(32)]);
show('sniff real PNG', sniffImageType(png));
show('sniff renamed .exe (declared png)', sniffImageType(exe));
const r = applyServerErrors([{ field: 'photo', code: 'too_large', max: 5000000, actual: 6000000 }, { field: 'bio', code: 'too_long', max: 160, actual: 171 }, { field: 'display_name', code: 'taken' }], ['bio', 'photo']);
show('server 422 mapped to the form', r);
Run in a node:22 container, it prints:
bio of 161 ASCII chars [{"field":"bio","code":"too_long","max":160,"actual":161}]
bio of 160 + spaces around []
bio of 160 emoji (UTF-16 length 320) []
photo 6 MB gif [{"field":"photo","code":"too_large","max":5000000,"actual":6000000},{"field":"photo","code":"wrong_type"}]
sniff real PNG "image/png"
sniff renamed .exe (declared png) null
server 422 mapped to the form {"byField":{"photo":["Photo must be 5 MB or smaller."],"bio":["Keep it to 160 characters (now 171)."]},"banner":["Please check this field."],"focus":"bio"}
- Reading
applyServerErrorsline by line:byFieldcollects messages per input andbannercollects the rest. For each error,MESSAGES[e.code] ?? (() => 'Please check this field.')looks up the message builder for the code, and??falls back to a generic builder when the code is unknown.(byField[e.field] ??= []).push(msg)means "ifbyField[e.field]isnullorundefined, set it to an empty list, then add the message", a short way to create the list on first use.knownFields.find((f) => byField[f]) ?? nullreturns the first field name that has messages, ornullif none. In the run above,display_nameis not a form field, so its generic message goes to the banner, andknownFieldsis['bio', 'photo'], sobiois first even though the server listedphotofirst. - The row
bio of 160 + spaces aroundprints[](no errors) because.trim()removes the spaces before counting, leaving exactly 160, which is within the limit. The 160-emoji row also prints[]because its length counted in code points is 160. - The server sends codes and numbers, not sentences. The client builds the text, so copy can change, be translated, and stay identical on web and mobile.
- An error whose
fieldis not on the form (thedisplay_namecase) goes to a banner at the top of the form rather than being dropped, so no rejection is ever invisible. focusis the first field, in the order of theknownFieldslist (bio, thenphoto), that has an error. Here that isbio, and the form moves keyboard focus to it.
What the user sees in each case
| Situation | Where it shows | What the user can do |
|---|---|---|
| Photo too large or wrong type (caught in the browser) | under the photo field, "Photo must be 5 MB or smaller." or "Use a JPEG, PNG or WebP image." | pick another file; Save stays available for the bio |
| Same problems caught only by the server (for example a renamed file) | the same photo field, from the 422 | same |
| Server rejects the bio | under the bio field, focus moves to it | edit and save |
| Connection drops during the photo upload | the photo field: "Upload interrupted" with Retry; bio text intact | Retry resends the upload |
| Connection drops during Save | banner "Couldn't save. Retry"; all inputs keep their values | Retry sends the same PATCH |
Accessibility wiring: each error text gets an id and the input points to it with aria-describedby (an attribute that links an input to the element describing it, which screen readers announce with the field) and aria-invalid="true" (marks the input as failing validation), so the error is part of the field's description and not only a red outline. The Save button shows Saving... and stays in the DOM; leaving the page with unsaved changes asks for confirmation.
Test, roll out, observe
Test the shared module with the table of cases above, once per boundary (160, 161, no photo, and a photo of exactly 5,000,000 bytes). Add a Playwright test (browser automation) that routes the PATCH to a 422 and asserts the message appears under the right input with focus on it, another that aborts the upload route and asserts the bio survives, and a server test that uploads a renamed executable declared as image/png and expects wrong_type. Roll out behind a flag. Observe the 422 rate by code: a rising too_long rate means the client counter and server disagree on how to count.
Pitfalls
Trusting file.type, counting length differently in the browser and the server, returning 400 with a prose message the client has to parse, clearing the form after a failed save, and putting the photo bytes in the same request as the text.
A like button on a post has to feel instant even on a slow connection. Walk me through how the UI, the API and the backend cooperate so that a failed or repeated request never leaves the heart or the count wrong.
Sample Answer
Direct answer
Flip the heart on the screen the instant the user taps (an optimistic update: showing the result you expect before the server confirms it). Send the server a request that says "this user's like state is ON", not "toggle". Store one row per (user, post) so a repeat cannot create a second like, and have the server answer with the canonical state {liked, like_count}. The client keeps at most one request in flight (sent and not yet answered) per post, replaces its guess with the server's answer when it arrives, and rolls back to the last confirmed state if the request fails. The canonical state is the server's own record of the truth, here the pair of the user's like and the post's total.
The contract
| Call | Meaning | Success response |
|---|---|---|
PUT /v1/posts/{id}/like | make my like state ON | 200 {"liked": true, "like_count": 42} |
DELETE /v1/posts/{id}/like | make my like state OFF | 200 {"liked": false, "like_count": 41} |
- Neither call has a request body: the method and path carry the whole intent.
- Idempotent means doing it twice leaves the same result as doing it once.
PUTON twice is still ON.DELETEon a like that does not exist returns200withliked: false, not404, so a retry after a lost response is harmless. - A
POST /toggleendpoint is the wrong shape: if the response is lost and the client retries, the retry flips the state back and the heart is wrong. - Errors:
401(not signed in),404(post deleted),429with aRetry-Afterheader (rate limit). The client retries network failures, timeouts and5xxup to two times with a short backoff (a growing pause between attempts, for example 200 ms then 400 ms) because the calls are safe to repeat. It does not retry other4xxanswers, and honoursRetry-Afteron429(a response header giving the number of seconds to wait before trying again).
Backend: the edge row decides, the count follows
An edge row is the row in likes that links one user to one post (the user and the post are the two ends of the edge). If that row exists, the user likes the post; the like_count on posts is only a stored total kept for fast reads. The function below runs in PL/pgSQL, PostgreSQL's built-in procedural language, and a transaction is a group of statements that commit together or not at all.
CREATE TABLE posts (
id bigint PRIMARY KEY,
like_count integer NOT NULL DEFAULT 0 CHECK (like_count >= 0)
);
CREATE TABLE likes (
user_id bigint NOT NULL,
post_id bigint NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (user_id, post_id)
);
CREATE FUNCTION set_like(p_user bigint, p_post bigint, p_liked boolean)
RETURNS TABLE (out_liked boolean, out_count integer)
LANGUAGE plpgsql AS $$
DECLARE changed integer;
BEGIN
IF p_liked THEN
INSERT INTO likes (user_id, post_id) VALUES (p_user, p_post) ON CONFLICT DO NOTHING;
ELSE
DELETE FROM likes WHERE user_id = p_user AND post_id = p_post;
END IF;
GET DIAGNOSTICS changed = ROW_COUNT; -- 1 if the edge changed, 0 if it was a repeat
UPDATE posts p
SET like_count = p.like_count + CASE WHEN p_liked THEN changed ELSE -changed END
WHERE p.id = p_post;
RETURN QUERY SELECT p_liked, p.like_count FROM posts p WHERE p.id = p_post;
END $$;
INSERT INTO posts VALUES (1, 0);
SELECT * FROM set_like(7, 1, true); -- run three times
SELECT * FROM set_like(7, 1, false); -- run twice
Line by line: ON CONFLICT DO NOTHING skips the insert quietly if the row already exists. GET DIAGNOSTICS changed = ROW_COUNT copies the number of rows the previous statement actually inserted or deleted into changed: 1 for a real change, 0 for a repeat. The UPDATE then adds +changed for a like or -changed for an unlike, and the last line returns the new state. Walking the calls: the first true inserts the row (changed = 1, count 0 to 1); the second and third find the row, change nothing (changed = 0), count stays 1; the first false deletes the row (count 1 to 0); the second false deletes nothing (count stays 0).
The primary key (user_id, post_id) is what makes a duplicate impossible: the database refuses a second row no matter how many requests race. The counter moves only by the number of rows that actually changed (ROW_COUNT), so a repeat adds 0. The edge change and the counter update sit in one transaction, so they commit or fail together. Run in a postgres:16 container, the three true calls each print t | 1, and the two false calls each print f | 0.
Two different users liking at the same moment is safe too: both update the same posts row, the database makes the second wait for the first, and each increment is applied to the latest value. Run in the same container with pgbench (PostgreSQL's load-testing tool, which opens many database sessions at once), 20 users each sending the like twice at the same instant (40 sessions), then 10 of them each sending the unlike twice (20 sessions). The two script files and commands are:
-- like.sql -- unlike.sql
\set u 1 + (:client_id % 20) \set u 1 + (:client_id % 10)
SELECT set_like(:u, 1, true); SELECT set_like(:u, 1, false);
pgbench -n -c 40 -t 1 -f like.sql demo -- 40 clients, two per user id
pgbench -n -c 20 -t 1 -f unlike.sql demo -- 20 clients, two per user id 1..10
SELECT p.like_count AS counter, (SELECT count(*) FROM likes) AS edge_rows FROM posts p WHERE id = 1;
After each run the final query prints:
counter | edge_rows counter | edge_rows
---------+----------- ---------+-----------
20 | 20 10 | 10
Client: the state machine
A state machine is code whose behaviour depends on a current state plus an incoming event. reduce(state, event) is a reducer: a pure function (same inputs always give the same output, and it changes nothing outside itself) that takes the old state and an event and returns the new state plus a list of effects, things the caller must then do, such as sending a request.
export const initial = (liked, count) => ({ confirmed: { liked, count }, desired: null, inflight: false, error: null });
export const view = (s) => {
const liked = s.desired ?? s.confirmed.liked;
return { liked, count: s.confirmed.count + (Number(liked) - Number(s.confirmed.liked)), error: s.error };
};
export function reduce(s, ev) {
switch (ev.type) {
case 'TAP': {
const desired = !view(s).liked;
const next = { ...s, desired, error: null };
return s.inflight ? { state: next, effects: [] }
: { state: { ...next, inflight: true }, effects: [{ send: desired }] };
}
case 'RESPONSE_OK': {
const confirmed = { liked: ev.liked, count: ev.count };
if (s.desired !== null && s.desired !== ev.liked)
return { state: { ...s, confirmed, inflight: true }, effects: [{ send: s.desired }] };
return { state: { ...s, confirmed, desired: null, inflight: false }, effects: [] };
}
case 'RESPONSE_FAIL':
return { state: { ...s, desired: null, inflight: false, error: 'Could not update. Try again.' }, effects: [] };
case 'SNAPSHOT': {
if (s.inflight || s.desired !== null) return { state: s, effects: [] };
if (ev.liked !== s.confirmed.liked) return { state: s, effects: [{ refetchLater: true }] };
return { state: { ...s, confirmed: { liked: ev.liked, count: ev.count } }, effects: [] };
}
}
}
- Read the non-obvious lines this way. In
view,count: s.confirmed.count + (Number(liked) - Number(s.confirmed.liked))converts the booleans to 1 or 0: if the screen shows liked but the server last said not liked, the difference is 1 - 0 = +1; if it shows not liked while the server said liked, it is 0 - 1 = -1; if they agree, it is 0. InRESPONSE_OK, theifhandles a user who tapped again while the request was in flight: the answer saysliked: truebutdesiredis nowfalse, so the state staysinflight: trueand a second request{send: false}goes out. confirmedis the last thing the server told us.desiredis what the user last asked for. The screen showsdesiredif there is one, and the displayed count is the confirmed count plus the local change (+1 or -1).- One request in flight per post. Taps during a request only change
desired. When the response lands, ifdesiredstill differs from what the server now says, one more request goes out. Two requests for the same post can therefore never overtake each other, so there is no "older answer arrives last" problem between taps. - The pending marker. A
SNAPSHOTis any other read of the post (a refocus refetch, a poll, a list reload). While a request is in flight ordesiredis set, snapshots are ignored, because they were produced before the server saw the tap and would flash the old count over the new one. After the action settles, a snapshot that disagrees about the user's own like state is treated as stale and re-requested later instead of applied. The reducer shows only that first step: a production version also counts refetches, because a disagreement that survives a refetch is real (for example the user unliked on another device) and must then be applied, or the heart would never converge. - A failure discards
desired, so the heart and count fall back toconfirmedtogether, and the error text appears.
The reducer has no network in it, so a driver feeds it the events a response would produce. Saved as like-machine.mjs (the reducer above) next to this driver:
import { initial, view, reduce } from './like-machine.mjs';
function run(title, s, events) {
console.log(title);
for (const ev of events) {
const r = reduce(s, ev); s = r.state; const v = view(s);
console.log(ev.type.padEnd(13), '-> heart=' + (v.liked ? 'on ' : 'off'), 'count=' + v.count,
'inflight=' + s.inflight, 'sends=' + JSON.stringify(r.effects) + (v.error ? ` error="${v.error}"` : ''));
}
console.log();
}
const T = { type: 'TAP' };
run('A. three quick taps (on, off, on) while the first request is in flight', initial(false, 41),
[T, T, T, { type: 'RESPONSE_OK', liked: true, count: 42 }]);
run('B. two taps (on, off): the second state differs from the request in flight', initial(false, 41),
[T, T, { type: 'RESPONSE_OK', liked: true, count: 42 }, { type: 'RESPONSE_OK', liked: false, count: 41 }]);
run('C. server error after one tap', initial(false, 41), [T, { type: 'RESPONSE_FAIL' }]);
run('D. a snapshot mid-request, a stale one after settling, a fresh one', initial(false, 41),
[T, { type: 'SNAPSHOT', liked: false, count: 41 }, { type: 'RESPONSE_OK', liked: true, count: 42 },
{ type: 'SNAPSHOT', liked: false, count: 41 }, { type: 'SNAPSHOT', liked: true, count: 44 }]);
Run in a node:22 container (the post starts unliked with a count of 41), it prints the trace for four situations:
A. three quick taps (on, off, on) while the first request is in flight
TAP -> heart=on count=42 inflight=true sends=[{"send":true}]
TAP -> heart=off count=41 inflight=true sends=[]
TAP -> heart=on count=42 inflight=true sends=[]
RESPONSE_OK -> heart=on count=42 inflight=false sends=[]
B. two taps (on, off): the second state differs from the request in flight
TAP -> heart=on count=42 inflight=true sends=[{"send":true}]
TAP -> heart=off count=41 inflight=true sends=[]
RESPONSE_OK -> heart=off count=41 inflight=true sends=[{"send":false}]
RESPONSE_OK -> heart=off count=41 inflight=false sends=[]
C. server error after one tap
TAP -> heart=on count=42 inflight=true sends=[{"send":true}]
RESPONSE_FAIL -> heart=off count=41 inflight=false sends=[] error="Could not update. Try again."
D. a snapshot mid-request, a stale one after settling, a fresh one
TAP -> heart=on count=42 inflight=true sends=[{"send":true}]
SNAPSHOT -> heart=on count=42 inflight=true sends=[]
RESPONSE_OK -> heart=on count=42 inflight=false sends=[]
SNAPSHOT -> heart=on count=42 inflight=false sends=[{"refetchLater":true}]
SNAPSHOT -> heart=on count=44 inflight=false sends=[]
Case A sends one request for three taps. Case B sends two, in order. In case D the post starts unliked with a count of 41, and the three snapshots carry (liked false, count 41) during the request, (liked false, count 41) after the response, and (liked true, count 44). The first is ignored because inflight is true. The second arrives after the like was confirmed (confirmed.liked is true) but says false, so it was produced before the server saw the tap: it is not applied and a later refetch is requested instead. The third agrees with the confirmed like state, so it is trusted, and its count of 44 replaces 42 because other people liked the post in the meantime.
Test, roll out, observe
- Unit: the reducer above is a pure function, so the four traces are the tests. Database: repeat and concurrent calls must leave
like_countequal tocount(*)of the edge rows. End to end: a Playwright test (a browser-automation test tool) aborts the network route and asserts the heart rolls back with the error text, and a throttled-connection test asserts the heart changes before the response is fulfilled. - Roll out behind a feature flag by percentage of users (the new code runs for, say, 5% of users first, then more, and a switch turns it off instantly), with the old endpoint still served until the flag reaches 100%.
- Observe: the share of like calls that end in
RESPONSE_FAIL, p95 latency of the endpoint (the time that 95% of calls beat, so it exposes slow outliers that an average hides), and a scheduled drift check that compareslike_counttocount(*)oflikesfor a sample of posts. Drift above zero means some code path writes the counter or the edge without the other.
Trade-offs and pitfalls
- The stored counter makes reads cheap but concentrates writes on one row per post. For an ordinary post that is fine; for a viral post, spread the counter over several rows (a random one of, say, 16 per like, summed on read) or accept a slightly delayed count. For example, 1,000 likes might land as roughly 62 on each of 16 rows, and the page shows their sum, 1,000. Most posts never need this, so add it only when measurements show waiting on that one row.
- Never compute "count + 1" on the client and keep it forever: it drifts as soon as another user likes the post. Always replace it with the server's number.
- Rolling back silently confuses users, so show the failure.
Build the home feed for a small social app: it shows posts from the people the user follows, newest first, with infinite scroll. Walk me through the API contract, the query and indexes behind it, the client-side state, and what the user sees when new posts are published while they are scrolling.
Sample Answer
Direct answer
Page the feed with a keyset cursor (also called seek pagination: "give me the 20 posts older than this exact post", where the post is identified by its timestamp plus its id), never with OFFSET. Order by (created_at, id) newest first, back it with an index on the author, and return an opaque cursor with every page. On the client, keep the list in a small reducer (a pure function that takes the old state and an event and returns the new state) that appends pages, removes duplicates by post id, and holds posts published while the user scrolls in a separate "pending" list. A "3 new posts" pill moves them to the top only when the user clicks it, so the list never jumps under their thumb. This is fan-out-on-read (the feed is assembled when it is requested by joining follows to posts), which is the right choice at small scale; the section on trade-offs says what flips it.
API contract
| Call | Meaning | Success | Errors |
|---|---|---|---|
GET /v1/feed?limit=20 | first page | 200 {"items":[...],"next_cursor":"...","head":"..."} | 401 not signed in |
GET /v1/feed?limit=20&cursor=<next_cursor> | the next older page | 200, next_cursor is null on the last page | 400 for a cursor that does not decode |
GET /v1/feed?after=<head> | posts newer than the newest post the client has | 200 {"items":[...]} (up to 50, newest first) | 400 for a bad head |
limithas a server-side maximum (say 50); the client cannot ask for 10,000 rows.- The cursor is opaque (a value the client treats as a sealed token, never looks inside, and sends back unchanged); here it is a small JSON object (timestamp plus id) encoded as base64url (a way of writing arbitrary bytes using only letters, digits,
-and_, safe to put in a URL). That lets the server change its contents later without breaking clients. The server still validates it on every request, because a client can send anything: MDN describes400 Bad Requestas the server refusing a request it considers a client error, and notes that repeating it without modification will fail with the same error, so the client should not resend a400unchanged. headis a cursor pointing at the newest post in the first page. It is the anchor for "anything newer?".- A page of
Nitems is requested when the user is within a screen or two of the bottom. The usual trigger is a sentinel element (an empty marker placed under the last post) watched by anIntersectionObserver(a browser API that reports when a watched element enters the viewport). A visible "Load more" button stays available as the fallback for keyboard and screen-reader users, who cannot scroll a sentinel into view in the same way.
Data model and the query
Three facts are needed: who follows whom, the posts, and a stable sort order. The stable order matters most. If two posts carry the same timestamp, a sort on created_at alone has no defined order between them, and a page boundary can fall between them and lose one. Adding id as a tie-breaker makes the order total (every pair of posts has a defined before and after, with no ties left).
CREATE TABLE follows (
follower_id bigint NOT NULL,
followee_id bigint NOT NULL,
PRIMARY KEY (follower_id, followee_id)
);
CREATE TABLE posts (
id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
author_id bigint NOT NULL,
body text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX posts_author_time_idx ON posts (author_id, created_at DESC, id DESC);
-- user 1 follows authors 2 and 3; posts are one minute apart, two share a timestamp
INSERT INTO follows VALUES (1,2),(1,3);
INSERT INTO posts (author_id, body, created_at) VALUES
(2,'p1','2026-10-08 10:01+00'),(3,'p2','2026-10-08 10:02+00'),
(2,'p3','2026-10-08 10:03+00'),(3,'p4','2026-10-08 10:04+00'),
(2,'p5','2026-10-08 10:05+00'),(3,'p6','2026-10-08 10:05+00'),
(2,'p7','2026-10-08 10:06+00'),(3,'p8','2026-10-08 10:07+00'),
(4,'not-followed','2026-10-08 10:08+00');
The page query uses a row comparison, (created_at, id) < (cursor_time, cursor_id), which means "strictly before that post in the (created_at, id) order". Reading it with the two earlier posts that share 10:05: p6 (id 6) sorts before p5 (id 5) in descending order, so a cursor at p6 returns p5 next, and neither is lost or repeated.
-- page 1 (3 per page)
SELECT p.id, p.body FROM posts p JOIN follows f ON f.followee_id = p.author_id
WHERE f.follower_id = 1
ORDER BY p.created_at DESC, p.id DESC LIMIT 3;
-- page 2, cursor = last row of page 1 = (10:05, id 6)
SELECT p.id, p.body FROM posts p JOIN follows f ON f.followee_id = p.author_id
WHERE f.follower_id = 1 AND (p.created_at, p.id) < ('2026-10-08 10:05+00', 6)
ORDER BY p.created_at DESC, p.id DESC LIMIT 3;
What OFFSET does when a post is published in between
A reader fetches page 1, another user then publishes a post by author 2 at 10:09, and the reader fetches page 2 both ways. Each request is its own query and each statement commits on its own, so one script reproduces it exactly:
-- reader, page 1 by OFFSET
SELECT string_agg(body, ', ' ORDER BY created_at DESC, id DESC) AS offset_page1 FROM (
SELECT p.id, p.body, p.created_at FROM posts p JOIN follows f ON f.followee_id = p.author_id
WHERE f.follower_id = 1 ORDER BY p.created_at DESC, p.id DESC LIMIT 3 OFFSET 0) page;
-- another user publishes
INSERT INTO posts (author_id, body, created_at) VALUES (2, 'NEW', '2026-10-08 10:09+00');
-- reader, page 2 by OFFSET
SELECT string_agg(body, ', ' ORDER BY created_at DESC, id DESC) AS offset_page2 FROM (
SELECT p.id, p.body, p.created_at FROM posts p JOIN follows f ON f.followee_id = p.author_id
WHERE f.follower_id = 1 ORDER BY p.created_at DESC, p.id DESC LIMIT 3 OFFSET 3) page;
-- reader, page 2 by keyset (cursor = last row of page 1 = 10:05, id 6)
SELECT string_agg(body, ', ' ORDER BY created_at DESC, id DESC) AS keyset_page2 FROM (
SELECT p.id, p.body, p.created_at FROM posts p JOIN follows f ON f.followee_id = p.author_id
WHERE f.follower_id = 1 AND (p.created_at, p.id) < ('2026-10-08 10:05+00', 6)
ORDER BY p.created_at DESC, p.id DESC LIMIT 3) page;
Run against the seed above, the three queries print:
| page 1 | page 2 after the new post | |
|---|---|---|
OFFSET | p8, p7, p6 | p6, p5, p4 (p6 appears twice) |
| keyset | p8, p7, p6 | p5, p4, p3 |
The new post pushes every older post one position down, so OFFSET 3 lands on a row the reader already saw. In the other direction (a post deleted while scrolling) OFFSET would silently skip one. Keyset paging anchors on a post, not a position, so insertions above the cursor change nothing below it.
Keep the full timestamp in the cursor
The cursor must carry the timestamp exactly as the database stores it. PostgreSQL keeps microseconds, but a JavaScript Date keeps milliseconds. Take three posts:
| post | id | created_at |
|---|---|---|
| a | 1 | 10:00:00.123456 |
| b | 2 | 10:00:00.123789 |
| c | 3 | 10:00:00.123100 |
Newest first the order is b, a, c (b has the latest microseconds). Page 1 has size 1 and returns b, so the cursor is (10:00:00.123789, 2). Page 2 should return a and c.
- Cursor built through a
Date(milliseconds only):(10:00:00.123, 2). The comparison(created_at, id) < (10:00:00.123, 2)asks for rows before.123000, but a (.123456) and c (.123100) are both after.123000, so it returns zero rows and both vanish. - Cursor with the full text:
(10:00:00.123789, 2). Now a and c are both before it, and the query returnsaandc.
TRUNCATE posts RESTART IDENTITY;
INSERT INTO posts (author_id, body, created_at) VALUES
(2, 'a', '2026-10-08 10:00:00.123456+00'),
(2, 'b', '2026-10-08 10:00:00.123789+00'),
(2, 'c', '2026-10-08 10:00:00.123100+00');
-- page 2 with a cursor rebuilt through a JavaScript Date (milliseconds only)
SELECT body FROM posts WHERE (created_at, id) < ('2026-10-08 10:00:00.123+00', 2) ORDER BY created_at DESC, id DESC;
-- page 2 with the full timestamp text
SELECT body FROM posts WHERE (created_at, id) < ('2026-10-08 10:00:00.123789+00', 2) ORDER BY created_at DESC, id DESC;
Run in postgres:16, the first query prints no rows and the second prints a then c. So the server prints the timestamp as text, base64url-encodes it with the id, and never routes it through a Date. The cursor helpers at the top of the next code block do exactly that (they run on the server).
What the index buys, and where it stops
With this data generator (400,000 posts spread over 5,000 authors, user 1 following 200 of them) and the query below, whose cursor is newer than every row so it behaves like a first page:
TRUNCATE posts, follows RESTART IDENTITY;
INSERT INTO follows SELECT 1, g FROM generate_series(2, 201) g;
INSERT INTO posts (author_id, body, created_at)
SELECT 2 + (g % 5000), 'x', timestamptz '2026-01-01 00:00+00' + (g || ' seconds')::interval
FROM generate_series(1, 400000) g;
ANALYZE;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT p.id, p.created_at FROM posts p JOIN follows f ON f.followee_id = p.author_id
WHERE f.follower_id = 1 AND (p.created_at, p.id) < ('2026-02-01 00:00+00', 300000)
ORDER BY p.created_at DESC, p.id DESC LIMIT 20;
It prints this plan (read from the bottom of the tree upwards; actual rows is what each step really produced):
Limit (actual rows=20 loops=1)
-> Sort (actual rows=20 loops=1)
Sort Key: p.created_at DESC, p.id DESC
Sort Method: top-N heapsort Memory: 25kB
-> Merge Join (actual rows=16000 loops=1)
Merge Cond: (p.author_id = f.followee_id)
-> Index Only Scan using posts_author_time_idx on posts p (actual rows=16001 loops=1)
Index Cond: (ROW(created_at, id) < ROW('2026-02-01 00:00:00+00'::timestamp with time zone, 300000))
Heap Fetches: 16001
-> Index Only Scan using follows_pkey on follows f (actual rows=200 loops=1)
Index Cond: (follower_id = 1)
Heap Fetches: 200
What the nodes mean: an index-only scan answers from an index (here posts_author_time_idx, then the primary key of follows) without opening the table, but only for rows the table's visibility map (a per-page note that every row on the page is visible to all transactions) marks as safe; Heap Fetches counts the rows for which it still had to open the table to check. The data in this demo was bulk-loaded and not yet vacuumed, so every row needed that check (Heap Fetches: 16001); after a VACUUM the same query reports Heap Fetches: 0 with the same row counts. The merge join walks the two sorted inputs (posts by author, followees by id) side by side and pairs up equal authors. The top-N heapsort keeps only the 20 best rows seen so far while the 16,000 flow past it. Limit then returns those 20. The arithmetic: 400,000 posts over 5,000 authors is 80 posts per author; user 1 follows 200 authors, so 200 x 80 = 16,000 posts, and because the cursor is newer than every row, all 16,000 match (the plan shows actual rows=16000; the one extra row in the index scan, 16001, is most likely the join reading one row past the last matching author to know it is finished). The signal is the gap: 16,000 rows produced to return 20, and that gap grows with posts per followee, not with the page size. In plain words: this query's cost grows with how much the followed authors have posted, not with the page size. At a few hundred followees and modest posting rates that is fine; for a user following thousands of prolific accounts it is not. Two mitigations come before changing the architecture: add a recency window to the query (for example only posts from the last 30 days), and cache the first page for a few seconds.
Client state and what the user sees
The code block below opens with the server-side cursor helpers; the reducer that follows is the client's whole behaviour. Its tests also check that a cursor with a valid timestamp but a non-numeric id is rejected, and that a numeric id with an invalid timestamp is rejected, so each validation line is exercised on its own. Reading the reducer's events: PAGE_OK appends a page (first page or "load more"); POLL_OK stores newly published posts in pending and does not touch items; SHOW_PENDING runs when the user clicks the pill; PAGE_FAIL keeps everything already on screen and records an error so a "Try again" button can appear.
import test from 'node:test';
import assert from 'node:assert/strict';
export const encodeCursor = (createdAt, id) =>
Buffer.from(JSON.stringify({ t: createdAt, id: String(id) })).toString('base64url');
export function decodeCursor(raw) {
try {
const { t, id } = JSON.parse(Buffer.from(raw, 'base64url').toString());
if (typeof t !== 'string' || !/^\d+$/.test(id) || Number.isNaN(Date.parse(t))) return null;
return { t, id };
} catch { return null; }
}
export const initial = { items: [], seen: new Set(), next: null, head: null, loading: false, error: null, pending: [] };
export function reduce(s, ev) {
switch (ev.type) {
case 'PAGE_START': return { ...s, loading: true, error: null };
case 'PAGE_OK': {
const fresh = ev.items.filter((p) => !s.seen.has(p.id));
return { ...s, loading: false, items: [...s.items, ...fresh],
seen: new Set([...s.seen, ...fresh.map((p) => p.id)]),
next: ev.next, head: s.head ?? ev.head };
}
case 'PAGE_FAIL': return { ...s, loading: false, error: 'Could not load more posts.' };
case 'POLL_OK': {
const known = new Set([...s.seen, ...s.pending.map((p) => p.id)]);
const fresh = ev.items.filter((p) => !known.has(p.id));
return { ...s, pending: [...fresh, ...s.pending] };
}
case 'SHOW_PENDING': {
const ids = s.pending.map((p) => p.id);
return { ...s, items: [...s.pending, ...s.items], seen: new Set([...s.seen, ...ids]),
pending: [], head: ev.head ?? s.head };
}
}
return s;
}
const post = (id) => ({ id: String(id), body: `p${id}` });
test('cursor round trip and tamper rejection', () => {
const c = encodeCursor('2026-10-08 10:05:00.123456+00', 6);
assert.deepEqual(decodeCursor(c), { t: '2026-10-08 10:05:00.123456+00', id: '6' });
assert.equal(decodeCursor('not-a-cursor'), null);
const enc = (o) => Buffer.from(JSON.stringify(o)).toString('base64url');
assert.equal(decodeCursor(enc({ t: '2026-10-08 10:05:00+00', id: '1; DROP' })), null);
assert.equal(decodeCursor(enc({ t: 'x', id: '1' })), null);
});
test('new posts wait in pending and do not move the list', () => {
let s = reduce(initial, { type: 'PAGE_OK', items: [post(8), post(7), post(6)], next: 'c1', head: 'h8' });
s = reduce(s, { type: 'POLL_OK', items: [post(9)] });
assert.deepEqual(s.items.map((p) => p.id), ['8', '7', '6']);
assert.equal(s.pending.length, 1);
s = reduce(s, { type: 'SHOW_PENDING', head: 'h9' });
assert.deepEqual(s.items.map((p) => p.id), ['9', '8', '7', '6']);
});
test('overlap between polls and pages never duplicates a post', () => {
let s = reduce(initial, { type: 'PAGE_OK', items: [post(8), post(7)], next: 'c1', head: 'h8' });
s = reduce(s, { type: 'POLL_OK', items: [post(9), post(8)] });
s = reduce(s, { type: 'POLL_OK', items: [post(9)] });
assert.deepEqual(s.pending.map((p) => p.id), ['9']);
s = reduce(s, { type: 'PAGE_OK', items: [post(7), post(6)], next: null });
assert.deepEqual(s.items.map((p) => p.id), ['8', '7', '6']);
});
test('a failed page keeps what is on screen and offers retry', () => {
let s = reduce(initial, { type: 'PAGE_OK', items: [post(3)], next: 'c1', head: 'h3' });
s = reduce(reduce(s, { type: 'PAGE_START' }), { type: 'PAGE_FAIL' });
assert.equal(s.items.length, 1);
assert.equal(s.loading, false);
assert.ok(s.error);
});
Run with node --test, the four tests pass (# pass 4, # fail 0).
A traced example with concrete ids (steps 0, 1, 2 and 4 are the second test; step 3 adds the overlap case from the third test):
| step | event | items | pending |
|---|---|---|---|
| 0 | start | empty | empty |
| 1 | PAGE_OK with posts 8, 7, 6 | 8, 7, 6 | empty |
| 2 | POLL_OK with post 9 (published meanwhile) | 8, 7, 6 (unchanged) | 9 |
| 3 | POLL_OK again with posts 9, 8 | 8, 7, 6 | 9 (8 is already shown, 9 already waiting, so neither is added) |
| 4 | user clicks the pill: SHOW_PENDING | 9, 8, 7, 6 | empty |
What the user sees, state by state:
| Situation | UI |
|---|---|
| First load | A few skeleton cards (grey placeholders the shape of a post), not a spinner on a blank page |
| Follows nobody, or followed accounts have posted nothing | An empty state that says so and links to finding people |
| Scrolling near the bottom | The next page is requested early; a small "Loading" row sits under the last post |
| Page request fails | Posts already shown stay; the bottom row says "Could not load more posts" with a "Try again" button |
| Last page reached | "You're all caught up", and no more requests |
| New posts published meanwhile | The list does not move. A pill at the top reads "3 new posts"; it is announced politely to screen readers (aria-live="polite"); clicking it prepends the posts and scrolls to the top |
Polling for the pill: every 30 to 60 seconds while the tab is visible, call GET /v1/feed?after=<head>. Stop polling when the tab is hidden (document.visibilityState). If a poll returns the full 50 items there may be more than fit, so the pill reads "50+ new posts" and clicking it reloads the first page instead of prepending; prepending only the newest 50 and then moving head forward would silently skip the older posts in between.
The commit-order gap, and the overlap that closes it
A post's created_at is set by now() when its transaction starts, not when it commits. This script, run with the schema above loaded, starts two psql sessions and shows the consequence:
C=pg-demo
psql_() { docker exec -i "$C" psql -U postgres -q -A -t "$@"; }
psql_ -c "TRUNCATE posts, follows RESTART IDENTITY; INSERT INTO follows VALUES (1,2),(1,3);"
# session A: the transaction starts (and now() is fixed), inserts, then commits 4 seconds later
psql_ -c "BEGIN; INSERT INTO posts (author_id, body) VALUES (2, 'from-A'); SELECT pg_sleep(4); COMMIT;" > /dev/null &
sleep 1
# session B: inserts and commits at once
psql_ -c "INSERT INTO posts (author_id, body) VALUES (3, 'from-B')" > /dev/null
echo "poll while A is open:"; psql_ -c "SELECT body FROM posts ORDER BY created_at DESC, id DESC"
wait
echo "newer than head (B) after A commits:"
psql_ -c "SELECT count(*) FROM posts WHERE (created_at, id) > (SELECT created_at, id FROM posts WHERE body = 'from-B')"
echo "newer than head by id instead of by time:"
psql_ -c "SELECT count(*) FROM posts WHERE id > (SELECT id FROM posts WHERE body = 'from-B')"
echo "with a 60 second overlap before head:"
psql_ -c "SELECT body FROM posts WHERE created_at > (SELECT created_at FROM posts WHERE body = 'from-B') - interval '60 seconds' ORDER BY created_at DESC, id DESC"
Session A begins a transaction, inserts from-A, and sleeps 4 seconds before committing. Meanwhile session B inserts from-B and commits at once, and the reader's poll sees only from-B and takes it as the newest post. The script prints from-B for the poll, 0 for "newer than from-B" after A commits, 0 again when "newer" is judged by id instead of time, and with the 60 second overlap from-B then from-A. As a timeline:
| time | session A | session B | what the reader sees |
|---|---|---|---|
| 0 s | begins, inserts from-A (gets created_at t0 and id 1) | nothing yet (A has not committed) | |
| 1 s | still open | inserts from-B (t0 + 1 s, id 2) and commits | from-B, which becomes the head |
| 4 s | commits | from-A is now visible but older than the head |
Because session A commits last, from-A has the older timestamp, so the check "anything newer than from-B?" returns 0 even though a fresh first page lists both. Left alone, the pill would never mention from-A.
The fix is an overlap: the server answers after=<head> with every post newer than a point 60 seconds before the head's timestamp, and the client discards ids it has already seen (the reducer's seen set and pending list do exactly that, and its third test sends overlapping ids). A transaction that stays open longer than the overlap can still slip through, so a full refresh on pull-to-refresh or tab return is the backstop. Ordering by id instead does not escape this: the database hands out an identity value when the row is inserted, not when it commits, so a lower id can commit later than a higher one. Here from-A has id 1 and commits after from-B (id 2), and the script's id > check also returns 0.
Trade-offs and what would change the design
- Fan-out-on-read (this design) versus fan-out-on-write. "Fan-out" means spreading one post to many readers. On read (this design) the feed is assembled when requested. On write, publishing a post copies a row into each follower's own feed table (a materialised feed: a stored, precomputed result instead of one computed on request); reading is then a single indexed range scan on one user. It pays off when feed reads vastly outnumber writes and the query above gets slow, but a celebrity with a million followers turns one publish into a million inserts. Stay on read until the
EXPLAINrow counts or the page latency say otherwise, and when that happens the usual compromise is to write feeds for ordinary accounts and merge in the posts of very popular accounts at read time. - Unfollow and delete take effect immediately here, because the query joins the current
followstable. A materialised feed table (the precomputed one described above) would need cleanup jobs. - Long sessions: a list that only grows holds thousands of DOM nodes. Virtualisation (rendering only the rows near the viewport) is the answer when scrolling gets heavy; it changes the rendering layer, not this contract.
- Common wrong turns:
OFFSET, a cursor that is just a timestamp (ties lose posts), a cursor that is milliseconds only, and auto-inserting new posts at the top while the user is reading.
Running the code
Save each SQL block, in the order shown, as feed.sql (schema and seed), pages.sql (the two page queries), offset.sql, micro.sql and plan.sql (the data generator and EXPLAIN); save the shell block as gap.sh with C=pg-demo matching the container name below, and the JavaScript block as feed.mjs.
docker run -d --rm --name pg-demo -e POSTGRES_PASSWORD=x postgres:16
sleep 8
for f in feed.sql pages.sql offset.sql micro.sql plan.sql; do docker exec -i pg-demo psql -U postgres -v ON_ERROR_STOP=1 < $f; done
bash gap.sh
docker stop pg-demo
docker run --rm -v "$PWD":/w -w /w node:22 node --test feed.mjs
Unlock Full Question Bank
Get access to all 11 End-to-End Feature Design and Development interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.