End-to-End Feature Design and Development Questions

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

• 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. PUT ON twice is still ON. DELETE on a like that does not exist returns 200 with liked: false, not 404, so a retry after a lost response is harmless.
• A POST /toggle endpoint 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), 429 with a Retry-After header (rate limit). The client retries network failures, timeouts and 5xx up 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 other 4xx answers, and honours Retry-After on 429 (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. In RESPONSE_OK, the if handles a user who tapped again while the request was in flight: the answer says liked: true but desired is now false, so the state stays inflight: true and a second request {send: false} goes out.
• confirmed is the last thing the server told us. desired is what the user last asked for. The screen shows desired if 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, if desired still 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 SNAPSHOT is any other read of the post (a refocus refetch, a poll, a list reload). While a request is in flight or desired is 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 to confirmed together, 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_count equal to count(*) 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 compares like_count to count(*) of likes for 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.

Direct answer

An edge row is one row in a follows table saying "user A follows user B" (in a social graph the people are nodes and each follow is an edge). That row is the truth. The count is a derived value: a number computed from the edge rows and then stored or cached for speed, so it can disagree with them for a while. The profile page can read the count from several places that each lag the edge rows by a different amount: an HTTP or CDN cache (content delivery network, a cache near the user that serves saved copies of responses), a database read replica (a copy of the database that trails the main one, called the primary), a counter that a batch job updates later, or the client's own cache overwriting the screen with an older response.

Start with step 1 of the checks below. One look at the follow request and the next profile GET shows whether the right number ever left the server, and that splits "a cache or the client is at fault" from "the database is at fault". Then split what must be exact from what may be late. Accept a late count on someone else's popular profile. Never accept a wrong answer to "do I follow this person?", never let a stale response undo the user's own action (the property usually called read-your-own-write: after you write something, your own next read must show it), and never accept a count that does not converge, meaning it must settle back to the true value without anyone repairing it by hand.

Why it happens, layer by layer

Ordered checks (cheapest and most informative first)

1. Network panel, same tap. In Chrome DevTools, Network tab: find the follow request and read its response body (did it return follower_count?). Then find the profile GET after the refresh: read the body, and in Headers read Cache-Control and Age. Age is the number of seconds the response has sat in a shared cache, for example Age: 37 (illustrative value); a header showing Age above zero means a shared cache served it, and no Age header suggests the response came from the origin. A body with the right count that the screen does not show means the client discarded it (go to step 5).
2. Bypass caches. Tick "Disable cache" for the browser cache, and replay the request with a Cache-Control: no-cache header or a unique query string to skip shared ones. If the count is now right, a cache is the cause; fix Cache-Control for the viewer-specific part of the response.
3. Ask the primary database. Compare the stored counter with the real edge count for that user. If the edge row is missing, the write never happened; if it exists but the counter is off, go to step 4. The queries below do this and show the repair. Here a follow tap becomes an INSERT into follows in the request; the users.follower_count counter is not touched in that request, and a batch job fixes it later, which is the state the first query catches in between.
4. Check replica lag and the job. On the replica run SELECT pg_last_xact_replay_timestamp() (the commit time, as it was on the primary, of the last transaction the replica applied; PostgreSQL documents that it returns NULL when the server was started normally without recovery, which is why it is only meaningful on a replica). The result is a timestamp such as 2026-10-07 12:00:03+00 (illustrative); compare it with now() on the replica, and a gap of several seconds means that replica is that far behind the primary, assuming the primary is taking writes. If the count is right on the primary and late on the replica, the read path is the cause. If both are late, look at when the batch job last ran.
5. Client cache. In the app's query-cache devtools (or logging), check whether a refetch that began before the mutation completed wrote its result after the mutation's result.

The database side, run in a postgres:16 container. Query 1 puts the stored counter next to a live count of edge rows. Query 2 asks one exact question about one pair of users. Query 3 is the drift check: LEFT JOIN keeps users who have no followers at all, coalesce(c.n, 0) turns the missing count into 0, and the WHERE keeps only rows where counter and edge count differ. Query 4 recomputes each user's real count with a correlated subquery (a subquery that is re-run for each user row, using that row's id) and overwrites only the counters that are wrong.

CREATE TABLE users (id bigint PRIMARY KEY, follower_count integer NOT NULL DEFAULT 0);
CREATE TABLE follows (
  follower_id bigint NOT NULL, followee_id bigint NOT NULL,
  PRIMARY KEY (follower_id, followee_id)
);
INSERT INTO users SELECT g FROM generate_series(1, 5) g;

-- three people follow user 1; the counter is maintained later by a batch job, not in the request
INSERT INTO follows VALUES (2, 1), (3, 1), (4, 1);

-- 1. what the profile page's cached counter says, and what the edge table says
SELECT u.follower_count AS counter, (SELECT count(*) FROM follows f WHERE f.followee_id = u.id) AS edges
FROM users u WHERE u.id = 1;

-- 2. "do I follow this account?" is a primary-key lookup on the edge table, so it is exact while the counter lags
SELECT EXISTS (SELECT 1 FROM follows WHERE follower_id = 3 AND followee_id = 1) AS viewer_follows;

-- 3. drift check: rows where the stored counter and the real edge count disagree
SELECT u.id, u.follower_count AS counter, coalesce(c.n, 0) AS edges
FROM users u LEFT JOIN (SELECT followee_id, count(*) AS n FROM follows GROUP BY 1) c ON c.followee_id = u.id
WHERE u.follower_count <> coalesce(c.n, 0);

-- 4. the repair (also the nightly rollup)
UPDATE users u SET follower_count = coalesce(c.n, 0)
FROM (SELECT u2.id, (SELECT count(*) FROM follows f WHERE f.followee_id = u2.id) AS n FROM users u2) c
WHERE c.id = u.id AND u.follower_count <> c.n;

SELECT follower_count AS counter_after_repair FROM users WHERE id = 1;

It prints:

counter | edges            ->  0 | 3
viewer_follows               ->  t
drift check (id, counter, edges) -> 1 | 0 | 3
counter_after_repair         ->  3

Reading the output: the first line shows the counter at 0 while three edge rows exist; the second shows user 3 follows user 1; the drift check lists only user 1 (users 2 to 5 have counter 0 and 0 edges, so they match); after the repair the counter reads 3. The counter says 0, the edge table says 3, and the exact question "does user 3 follow user 1?" is still answered correctly by the primary-key lookup. This is the key design point: the button state comes from the edge row, the number may come from a derived value.

The fix on the client: the pending marker plus a viewer-state check

Names in the code: confirmed is the last state the server told us about for the viewer's own follow action, pending is true while that request is on the wire, a snapshot is any profile response (from a refetch, poll, cache or replica) carrying viewer_follows and follower_count, and onSnapshot decides whether to apply it. The button itself shows the new state as soon as pending is true; the log prints confirmed, so following=false right after the tap is expected and does not mean the button is wrong.

// confirmed = last server answer to MY action; pending = a request of mine is on the wire
const initial = { confirmed: { following: false, count: 41 }, pending: false };

function onTap(s)      { return { ...s, pending: true }; }
function onResponse(s, r) { return { confirmed: { following: r.following, count: r.follower_count }, pending: false }; }
function onSnapshot(s, snap) {
  if (s.pending) return { state: s, action: 'ignored: my request is in flight' };
  if (snap.viewer_follows !== s.confirmed.following)
    return { state: s, action: 'discarded: snapshot disagrees about MY follow state, refetch in 2s' };
  return { state: { ...s, confirmed: { ...s.confirmed, count: snap.follower_count } }, action: 'applied' };
}

let s = initial;
const show = (label, extra = '') => console.log(label.padEnd(44), `following=${s.confirmed.following} count=${s.confirmed.count}`, extra);
show('start');
s = onTap(s);                                            show('tap Follow (request sent)');
let r = onSnapshot(s, { viewer_follows: false, follower_count: 41 }); s = r.state; show('stale snapshot arrives mid-request', r.action);
s = onResponse(s, { following: true, follower_count: 42 }); show('POST answers 200 {following:true, count:42}');
r = onSnapshot(s, { viewer_follows: false, follower_count: 41 }); s = r.state; show('replica or CDN snapshot after refresh', r.action);
r = onSnapshot(s, { viewer_follows: true, follower_count: 40 }); s = r.state; show('snapshot agrees on follow, count is 40', r.action);

Run in a node:22 container, it prints (the state shown is the last server-confirmed one; the button already displays the new state after the tap):

start                                        following=false count=41 
tap Follow (request sent)                    following=false count=41 
stale snapshot arrives mid-request           following=false count=41 ignored: my request is in flight
POST answers 200 {following:true, count:42}  following=true count=42 
replica or CDN snapshot after refresh        following=true count=42 discarded: snapshot disagrees about MY follow state, refetch in 2s
snapshot agrees on follow, count is 40       following=true count=40 applied

A snapshot is ignored while the user's own request is in flight. After the answer arrives, a snapshot that disagrees about the user's own follow state came from before the write (a replica or a cache) and is discarded with a later retry. A snapshot that agrees is newer truth, including a lower count when other people unfollowed.

The fix on the server

• The follow and unfollow responses return {following, follower_count} read in the same transaction as the write, so the client always has a post-write number.
• viewer_follows is computed from the edge table on the primary for the signed-in viewer. Mark that response Cache-Control: private so a shared cache never serves one person's answer to another.
• If profile traffic is large enough that you need a shared cache, split the response: a public, cacheable part (name, avatar, counts) and a small private part (viewer_follows). That costs a second request and is worth it only when the public part is read far more often than it changes. For ordinary traffic one private response is simpler.
• Repair (and detect drift, meaning stored counters that have wandered away from the real edge counts) by running the drift query on a schedule and alerting if the same row mismatches on two consecutive runs, since one mismatch may be a write in flight.

When to accept the lag, and when not

Verify

A Playwright test follows, then routes the next profile GET to a stale body (viewer_follows: false, old count) and asserts the button still reads "Following" and the count shows the new value. A database test inserts an edge without moving the counter and asserts the drift query reports the row and the repair clears it. A latency-injection test delays the replica read and asserts the same screen result. Observe: number of drift rows per run, the share of discarded snapshots (a high share means a systematic cache or replica problem), and replica lag.

Direct answer

Three ideas carry this design, and each can be said in one breath. First, the file is cut into numbered parts that go straight from the browser to object storage, so one dropped connection costs one part and not the whole file. Second, every status change on the upload row is a conditional update (an UPDATE whose WHERE clause names the status it expects, so only one of several racing callers changes the row). Third, cleanup has more than one layer, because no single layer catches every abandoned upload.

The video never passes through your API servers. The backend creates an Amazon S3 multipart upload (the object store splits one big file into numbered parts that can be sent in parallel, retried individually, and stitched together at the end), signs a short-lived URL for each part, and the browser sends the parts straight to object storage. A signed (presigned) URL is an ordinary storage URL with a time-limited signature in its query string, produced by the backend with its own credentials. Whoever holds it can perform exactly the one operation it was signed for (for example, PUT part 3 of this upload) until it expires, which is why a browser with no storage credentials is allowed to write to a private bucket. The client then calls complete; the backend asks storage to assemble the object, marks the upload processing, and a worker scans it and transcodes it (re-encodes the video into formats and sizes that browsers and phones play). Cleanup is layered: an explicit cancel, a database reaper (a scheduled job that finds expired rows and cleans them up) for expired uploads, and a bucket lifecycle rule (a storage-side setting that deletes or aborts things after a set age) as a backstop, meaning a last line of defence for what the others missed. Every state change is a conditional update, so the repeated complete calls and the repeated storage events that will happen cannot do the work twice.

What the user sees

1. Pick the file. The page checks size (our product cap, 10 GiB) and type, shows the name and a progress bar. A post can be written while the upload runs.
2. Uploading. A bar driven by bytes that storage has confirmed, a speed and time-left estimate, and Pause and Cancel. Closing the tab triggers a "leave page?" prompt only while parts are in flight.
3. After a failure or reload. The upload id and the finished parts are kept in the browser's IndexedDB (browser-side database). The user reselects the same file; the page accepts it only if name, size and last-modified time match, asks the server which parts exist, and sends only the missing ones.
4. Processing. "Processing your video" until the status is ready. The author can submit the post once the bytes are safely stored, but other people see it only when the video is ready.

One upload, traced

Take a 6 GiB file (GiB = 1,073,741,824 bytes; MiB = 1,048,576 bytes; the sizes below use these binary units). The server picks a 16 MiB part size, so the file becomes 384 parts. The browser slices the file: part 1 is bytes 0 to 16 MiB minus 1, part 2 is the next 16 MiB, and so on. For part 1 it asks the API for a signed URL, sends the bytes with PUT, and storage answers with an ETag header, a short fingerprint of that part. The browser keeps (1, etag1). It repeats this for parts 2 to 384, four at a time. After the last part it calls complete with the list of 384 (part number, ETag) pairs; storage checks each pair, joins the parts into one object, and the row moves on to processing. If the connection dies at part 200, parts 1 to 199 are already stored, so only part 200 onward is sent again.

API contract

How bytes reach storage: the browser sends each part with PUT to its signed URL. The signature is limited by the permissions of whoever signed it, and the URL expires, so a leaked URL is useless soon. The browser must record each part's ETag (a version tag the store returns per part) because the complete request needs the part number and ETag of every part. JavaScript can read that response header only if the bucket's CORS configuration lists it under ExposeHeaders. (CORS, cross-origin resource sharing, is the browser rule that stops a page on one site from reading responses from another origin unless that origin opts in; here the bucket is the other origin, and ExposeHeaders is its opt-in for specific response headers such as ETag.)

Storage limits that shape the plan, as documented for Amazon S3: at most 10,000 parts per upload, each part 5 MiB to 5 GiB (the last may be smaller), and a 48.8 TiB maximum object. ListParts (the storage call that lists which parts of an upload have already arrived) returns at most 1,000 parts per call, so resuming a large upload pages through it. Choosing the part size: the 10,000-part limit means a part must be at least size / 10,000, so the function rounds that up to a whole MiB, and never goes below 16 MiB so that a normal file is not split into thousands of tiny requests. Math.max picks the larger of the two, Math.ceil rounds up. With a 10 GiB cap the 16 MiB floor always wins (10 GiB / 10,000 is about 1 MiB); the size-based term only matters for files above 156.25 GiB.

const MiB = 1024 ** 2, GiB = 1024 ** 3, MAX_PARTS = 10000, MIN_PART = 5 * MiB;
// choose a part size: at least 16 MiB, and large enough that the file fits in 10,000 parts
export function partPlan(sizeBytes) {
  const part = Math.max(16 * MiB, Math.ceil(sizeBytes / MAX_PARTS / MiB) * MiB);
  return { partMiB: part / MiB, parts: Math.ceil(sizeBytes / part), lastPartMiB: (sizeBytes - (Math.ceil(sizeBytes / part) - 1) * part) / MiB };
}
for (const g of [0.5, 6, 10]) console.log(`${g} GiB ->`, JSON.stringify(partPlan(g * GiB)));
console.log('largest file 16 MiB parts allow:', (16 * MiB * MAX_PARTS) / GiB, 'GiB');
// time to re-send if the connection dies: whole file vs one part, at 10 Mbit/s upstream
const bytesPerSec = 10e6 / 8;
console.log('re-send whole 6 GiB at 10 Mbit/s (minutes):', (6 * GiB / bytesPerSec / 60).toFixed(1));
console.log('re-send one 16 MiB part (seconds):', (16 * MiB / bytesPerSec).toFixed(1));

Run in a node:22 container, it prints:

0.5 GiB -> {"partMiB":16,"parts":32,"lastPartMiB":16}
6 GiB -> {"partMiB":16,"parts":384,"lastPartMiB":16}
10 GiB -> {"partMiB":16,"parts":640,"lastPartMiB":16}
largest file 16 MiB parts allow: 156.25 GiB
re-send whole 6 GiB at 10 Mbit/s (minutes): 85.9
re-send one 16 MiB part (seconds): 13.4

That last pair is the reason for parts: a dropped connection costs one 16 MiB part (about 13 seconds at 10 Mbit/s) instead of the whole 6 GiB file (about 86 minutes). The client sends 4 parts at once and retries each part up to 3 times with exponential backoff and jitter (random extra delay so many clients do not retry in lockstep). It pauses on the browser's offline event.

Finish: how the backend learns the upload ended

Two paths lead to the same transition, and both are safe to repeat:

1. The client calls complete. The server moves the row pending to completing with a conditional update, calls storage's complete operation with the part list, then moves it to processing and queues the job. Before assembling it can compare the part list with ListParts, which is meant for verification. After assembly it checks the object size equals the declared size, and fails the upload if not.
2. A storage event. The bucket can notify a queue when an object is created. Amazon S3 documents event notifications as delivered at least once (a message may arrive twice, but is not silently dropped by design), so duplicates are normal. The handler uses the same conditional update, so a second delivery changes zero rows and does nothing. This path rescues the case where the server crashed after storage finished assembling but before the row was updated.

The state table and its guards

CREATE TABLE uploads (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id      bigint      NOT NULL,
  s3_upload_id text        NOT NULL,
  object_key   text        NOT NULL,
  size_bytes   bigint      NOT NULL CHECK (size_bytes BETWEEN 1 AND 10 * 1024^3),
  status       text        NOT NULL DEFAULT 'pending'
               CHECK (status IN ('pending','completing','processing','ready','failed','abandoned')),
  attempts     integer     NOT NULL DEFAULT 0,
  expires_at   timestamptz NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX uploads_open ON uploads (expires_at) WHERE status = 'pending';

INSERT INTO uploads (id, user_id, s3_upload_id, object_key, size_bytes, expires_at) VALUES
 ('00000000-0000-0000-0000-00000000000a', 7, 'mpu-a', 'raw/a', 6442450944, now() + interval '24 hours'),
 ('00000000-0000-0000-0000-00000000000b', 7, 'mpu-b', 'raw/b', 1000000,    now() - interval '1 hour'),
 ('00000000-0000-0000-0000-00000000000c', 8, 'mpu-c', 'raw/c', 1000000,    now() - interval '2 hours');

UPDATE uploads SET status = 'completing' WHERE id = '00000000-0000-0000-0000-00000000000a' AND status = 'pending' RETURNING id, status;
UPDATE uploads SET status = 'completing' WHERE id = '00000000-0000-0000-0000-00000000000a' AND status = 'pending' RETURNING id, status;
UPDATE uploads SET status = 'processing' WHERE id = '00000000-0000-0000-0000-00000000000a';
UPDATE uploads SET attempts = attempts + 1, status = CASE WHEN attempts + 1 >= 3 THEN 'failed' ELSE 'processing' END
 WHERE id = '00000000-0000-0000-0000-00000000000a' RETURNING attempts, status;
UPDATE uploads SET attempts = attempts + 1, status = CASE WHEN attempts + 1 >= 3 THEN 'failed' ELSE 'processing' END
 WHERE id = '00000000-0000-0000-0000-00000000000a' RETURNING attempts, status;
UPDATE uploads SET attempts = attempts + 1, status = CASE WHEN attempts + 1 >= 3 THEN 'failed' ELSE 'processing' END
 WHERE id = '00000000-0000-0000-0000-00000000000a' RETURNING attempts, status;
WITH due AS (
  SELECT id FROM uploads WHERE status = 'pending' AND expires_at < now()
  ORDER BY expires_at LIMIT 100 FOR UPDATE SKIP LOCKED)
UPDATE uploads u SET status = 'abandoned' FROM due WHERE u.id = due.id
RETURNING u.id, u.s3_upload_id, u.status;

Reading the statements in order, with the table as set up by the INSERT: upload a is a live 6 GiB upload, b and c are pending uploads whose expires_at is already in the past.

1. First guarded UPDATE ... WHERE status = 'pending' on a: the condition holds, so one row changes to completing and RETURNING prints it.
2. The identical statement again: a is now completing, the WHERE matches nothing, and zero rows come back. This is a second complete call, or a second copy of the storage event.
3. The unguarded UPDATE to processing prints nothing because it has no RETURNING; it stands for the assemble step finishing.
4. The three retry statements are the same text. attempts = attempts + 1 bumps the counter, and CASE WHEN attempts + 1 >= 3 THEN 'failed' ELSE 'processing' END says: if this is the third failure, give up; otherwise stay in processing for another try. In an UPDATE, attempts on the right side is the value before the update, hence attempts + 1.
5. The WITH due AS (...) query is the reaper. It selects up to 100 pending rows past their expiry, oldest first, and UPDATE ... FROM due marks them abandoned.

Run in a postgres:16 container, the guarded update returns one row the first time (completing) and (0 rows) the second, so only one caller proceeds. The three failure updates print 1 | processing, 2 | processing, 3 | failed: a processing job is retried until three attempts, then marked failed and the user is told. The reaper returns the two expired rows (mpu-c and mpu-b) as abandoned and leaves the live upload alone. FOR UPDATE SKIP LOCKED locks the rows the query selects and skips any row another transaction has already locked, so two reaper instances can run at once without claiming the same row.

Server-side processing at feature altitude

A worker takes the job from a queue (a list of pending jobs that workers pull from one at a time) and (1) scans the object for malware, (2) inspects the container to confirm it really is a video and within limits, (3) writes a thumbnail and web-friendly renditions (copies of the video at several resolutions and bitrates) into a separate processed/ prefix, and (4) sets ready. Retries use the attempt counter above with growing delays; a job that fails three times goes to failed. The raw bucket stays private. Web clients get the processed files through a CDN (content delivery network, a cache close to the viewer) with long cache lifetimes, since each rendition has a new key.

Cleaning up abandoned and failed uploads

Storage keeps uploaded parts, and bills for them, until the upload is completed or stopped. There are three layers:

Part uploads that were in flight when an abort arrived can still land afterwards, which is why the lifecycle rule exists as a sweep for anything the reaper missed. Failed processing keeps the raw object for a few days for investigation, then a retention rule deletes it.

Test, roll out, observe

Tests: the conditional-update SQL above, a Playwright test that kills the connection mid-part and asserts only that part is resent, a test that calls complete twice, and one that delivers the storage event twice. Roll out to staff first with a low size cap. Observe the share of uploads ending abandoned or failed, the age of the oldest pending row, queue depth (how many jobs are waiting for a worker; a rising number means workers cannot keep up), and bytes in incomplete multipart uploads (the leak the lifecycle rule guards).

Trade-offs

Direct-to-storage upload removes video bytes from your servers and gives resumability, and costs a more complex client and a second path to learn completion. For files under about 100 MB a single signed PUT is simpler; multipart pays off as sizes grow and networks worsen.

Direct answer

A follow is one row in an edge table (a table whose rows are the links between two things, here two users), follows(follower_id, followee_id), with a composite primary key (a key made of two columns together) so the same pair can exist only once. The button flips at once on tap (an optimistic update), sends an idempotent "make this follow exist" request, and settles to the {following, follower_count} the server returns. A double tap and a retry are harmless because the database rejects the duplicate row. Two people following at once are safe because each inserts a different row and the counter increments are serialised (made to run one after another) by the row lock (while one transaction is changing a row, any other transaction that wants to change that same row waits until the first one commits; a transaction is a group of statements that succeed or fail together). A million-follower account needs a different counter (spread across several rows) and cursor-based follower lists (each page asks for "rows after the last one you saw" instead of "skip N rows"), but the same edge table.

Tap to row, layer by layer

The client logic is the same single-request-in-flight state machine used for a like button: taps while a request is pending only change what the user wants, one follow-up request goes out if the answer disagrees, and a failure restores the last confirmed state.

Schema and the write path

CREATE TABLE users (
  id              bigint  PRIMARY KEY,
  follower_count  integer NOT NULL DEFAULT 0 CHECK (follower_count  >= 0),
  following_count integer NOT NULL DEFAULT 0 CHECK (following_count >= 0)
);
CREATE TABLE follows (
  follower_id bigint      NOT NULL REFERENCES users(id),
  followee_id bigint      NOT NULL REFERENCES users(id),
  created_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (follower_id, followee_id),
  CHECK (follower_id <> followee_id)
);
CREATE INDEX follows_by_followee ON follows (followee_id, created_at DESC, follower_id);

CREATE FUNCTION set_follow(p_me bigint, p_target bigint, p_follow boolean)
RETURNS TABLE (out_following boolean, out_followers integer)
LANGUAGE plpgsql AS $$
DECLARE changed integer;
BEGIN
  PERFORM 1 FROM users WHERE id IN (p_me, p_target) ORDER BY id FOR UPDATE;
  IF p_follow THEN
    INSERT INTO follows (follower_id, followee_id) VALUES (p_me, p_target) ON CONFLICT DO NOTHING;
  ELSE
    DELETE FROM follows WHERE follower_id = p_me AND followee_id = p_target;
  END IF;
  GET DIAGNOSTICS changed = ROW_COUNT;
  UPDATE users SET follower_count  = follower_count  + CASE WHEN p_follow THEN changed ELSE -changed END WHERE id = p_target;
  UPDATE users SET following_count = following_count + CASE WHEN p_follow THEN changed ELSE -changed END WHERE id = p_me;
  RETURN QUERY SELECT p_follow, u.follower_count FROM users u WHERE u.id = p_target;
END $$;

INSERT INTO users SELECT g FROM generate_series(1, 300) g;
SELECT * FROM set_follow(1, 2, true);   -- run twice
SELECT * FROM set_follow(1, 2, false);  -- run twice
SELECT * FROM set_follow(5, 5, true);

• The PERFORM ... ORDER BY id FOR UPDATE line locks both user rows in id order before anything else. Without it, user 1 following user 2 updates row 2 (the target) and then row 1, while user 2 following user 1 updates row 1 and then row 2 (the function updates the target's counter first and the follower's own counter second), and each can wait forever for the other (a deadlock; PostgreSQL aborts one of them). Locking in a fixed order removes that cycle.
• Worked timeline of that deadlock with ids, using the unlocked order above: at time 1, A (user 1 follows user 2) locks row 2; at time 1, B (user 2 follows user 1) locks row 1; at time 2, A asks for row 1 and waits for B; at time 2, B asks for row 2 and waits for A. Each holds what the other needs, so neither can move. Sorting by id means both A and B ask for row 1 first, so one simply waits for the other and then continues.
• ON CONFLICT DO NOTHING skips the insert if the pair already exists, and GET DIAGNOSTICS changed = ROW_COUNT stores how many rows the insert or delete actually changed (1, or 0 for a repeat). The counters then move by changed, so a repeat moves no counter.
• The index on (followee_id, created_at DESC, follower_id) serves the follower list, and the primary key serves "does the viewer follow this account?" with a single lookup.

Run in a postgres:16 container, the double tap prints follower counts t | 1, t | 1, f | 0, f | 0, and the self follow fails with violates check constraint "follows_check".

Double tap and simultaneous followers

Two taps from one user: the second INSERT waits for the first transaction, hits the primary key, does nothing, and returns the same answer. Two different users: their rows differ, so both inserts succeed, and the two UPDATE users statements queue on the target's row and each adds one to the latest value.

Checked with pgbench (PostgreSQL's load-testing tool; each client is one database session) in the same container. Three small script files: a.sql is SELECT set_follow(1,2,true);, b.sql is SELECT set_follow(2,1,true);, and f.sql is \set u :base + :client_id then SELECT set_follow(:u, 2, true);. Users 1 and 2 follow each other from 40 sessions at once, 50 transactions per session, each picking one of the two directions at random; then 200 different users follow user 2 in five waves of 40 simultaneous sessions. Deadlocks are read from the server's own counter, SELECT deadlocks FROM pg_stat_database WHERE datname = current_database(), before and after:

pgbench -n -c 40 -t 50 -f a.sql -f b.sql demo
for base in 3 43 83 123 163; do pgbench -n -c 40 -t 1 -D base=$base -f f.sql demo; done

 counter_user2 | edges_user2 | counter_user1 | edges_user1
---------------+-------------+---------------+-------------
           201 |         201 |             1 |           1
deadlocks during the run: 0

Reading the columns: counter_user2 is the stored follower count of user 2 and edges_user2 is a live count(*) of rows in follows pointing at user 2; they match at 201 (200 followers plus user 1), which shows no increment was lost. counter_user1 and edges_user1 are the same pair for user 1, who is followed by user 2 only, so both are 1. The repeated mutual follows in both directions are the deadlock trap, and the unchanged deadlock counter shows none occurred, because the fixed lock order removes the cycle. The check can fail: with the PERFORM ... FOR UPDATE line deleted from the function, the mutual-follow run made PostgreSQL abort transactions with deadlock errors. In one measured shorter run (-t 3, 3 transactions per session) the deadlock counter rose by about a hundred, and the full -t 50 run is slow because each deadlock waits out PostgreSQL's 1 second deadlock_timeout before one transaction is aborted; the exact count varies from run to run.

An account with millions of followers

Three things break first, in this order:

1. The counter row becomes a queue. (Fan-out, used in point 3, means copying one event to many recipients, for example writing a new post into each follower's feed; a shard is one of several rows or partitions that share the work of one thing.) Every follow of that account updates the same users row, and those updates run one after another. Find out whether it matters by watching lock wait time on that row (how long updates sit waiting for the lock); do not shard before measuring, since most accounts never need it. When it does matter, give the account several counter rows and add to a random one:

CREATE TABLE follower_shards (
  user_id bigint NOT NULL, shard smallint NOT NULL, n bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (user_id, shard)
);
CREATE FUNCTION bump_followers(p_user bigint) RETURNS void LANGUAGE sql AS $$
  INSERT INTO follower_shards (user_id, shard, n)
  VALUES (p_user, floor(random() * 16)::smallint, 1)
  ON CONFLICT (user_id, shard) DO UPDATE SET n = follower_shards.n + 1;
$$;
SELECT bump_followers(99) FROM generate_series(1, 1000);
SELECT sum(n) AS follower_count, count(*) AS shard_rows, max(n) < 1000 AS spread_over_rows
FROM follower_shards WHERE user_id = 99;

Reading the code: floor(random() * 16)::smallint picks a shard number from 0 to 15 for each call; ON CONFLICT (user_id, shard) DO UPDATE SET n = follower_shards.n + 1 creates that shard's row with n = 1 the first time and otherwise adds 1 to it; the generate_series(1, 1000) call performs 1,000 follows. In the final query, sum(n) is the follower count, count(*) is how many shard rows exist, and max(n) < 1000 is true when no single row holds all 1,000, meaning the load was spread. Run in the same container this prints 1000 | 16 | t: the total is exact and the writes landed on 16 rows (each row holds about 1000/16, roughly 62, so the busiest row's queue is roughly a dozen times shorter than a single counter row's; the split among shards varies per run, the total does not). Reads sum 16 small rows, which is cheap, and the profile can cache the sum for a few seconds. The trade-off is that concurrent writers no longer wait for each other, at the price of an extra read step and a rule that only flagged hot accounts use shards.

2. The follower list. Use keyset pagination (also called cursor-based), which means the cursor is the (created_at, follower_id) of the last row shown and the next page asks for rows after it. OFFSET 5000000 has to walk past five million rows; the keyset query jumps straight into the index.
3. Fan-out. It is tempting to push the new follow (or the followee's later posts) into every follower's stored feed at write time; for a million followers that is a million writes inside one tap. Following must not copy anything to the followee's followers. Whatever builds feeds reads the edge table later, outside this request.

Test, roll out, observe

• Tests: repeat and concurrent calls end with follower_count equal to the number of edge rows (the check above), a mutual-follow stress test must produce zero deadlock errors, and a Playwright test with the route aborted must show the button reverting.
• Roll out: ship the edge table and endpoints first, backfill counters (fill them in for existing data) from count(*) per user, then switch the profile read, behind a flag.
• Observe: lock wait on hot user rows, rate of 422 and 5xx on the follow endpoints, and a scheduled job that compares each sampled user's counter to count(*) on the edge table.

Pitfalls

Do not make follow a toggle endpoint, do not read the follower count and then write it back from application code (two requests overwrite each other), and do not shard counters for every account because most accounts never need it.

Direct answer

Both apps call the same versioned JSON API with three operations: list a post's top-level comments, list one comment's replies, and create or delete a comment. Threads are exactly two levels deep (a top-level comment and its replies). Lists use cursor pagination (the client sends back an opaque bookmark for the last item it saw, not a page number). The post's comment_count is a stored number changed in the same transaction as the insert or delete, and a client-generated client_request_id (a random id the client makes once per submit and resends on every retry, so the server can recognise a repeat) makes a retried submit create nothing new. Frontend and backend build in parallel against an agreed API description and shared example payloads.

Two ideas carry most of the design. Cursor pagination keeps a long thread stable while new comments arrive, and the client_request_id plus a database uniqueness rule makes every write safe to repeat. The rest of the answer shows how each one works.

The contract both clients share

One top-level item looks like this:

{
  "id": "6", "author": {"id": "42", "name": "Dana", "avatar_url": "https://cdn.example.com/a/42.jpg"},
  "body": "text", "created_at": "2026-01-01T10:02:00Z", "deleted": false,
  "reply_count": 1,
  "replies": {"items": [ /* the first 2 replies */ ], "next_cursor": null}
}

Rules that keep a web app and a mobile app on one contract:

• Ids are strings and cursors are opaque, so the server can change their internals. Timestamps are UTC ISO-8601.
• Only additive changes inside v1 (new optional fields; nothing is removed, renamed or made mandatory, so an old client keeps working). Clients ignore fields they do not know. Mobile apps stay installed for months, so the server must keep answering old versions.
• A deleted comment that still has replies is returned with "deleted": true and no body (a tombstone, a placeholder that keeps the thread readable). Rendering it as "Comment removed" is the client's job.
• Validation errors use one shape, 422 {"errors": [{"field": "body", "code": "too_long"}]}, so both clients map them identically.

Replies are embedded as a preview (first 2) so one request draws the whole first screen, which matters on a mobile network. "View 12 more replies" calls the replies endpoint with replies.next_cursor.

Why two levels

A reply to a reply attaches to the same top-level comment and carries an @name mention in its text. Deeper nesting costs a recursive query (a query that repeatedly follows parent links to walk a tree), a layout that runs out of horizontal space on a phone, and moderation complexity. If product later needs deep trees, the change is an added path column on comments and a new response field, and nothing in the contract above breaks. The database enforces the rule rather than trusting the clients.

Tables, count and queries

CREATE TABLE post_stats (id bigint PRIMARY KEY, comment_count integer NOT NULL DEFAULT 0 CHECK (comment_count >= 0));
CREATE TABLE comments (
  id                bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  post_id           bigint      NOT NULL REFERENCES post_stats(id),
  parent_id         bigint      REFERENCES comments(id),
  author_id         bigint      NOT NULL,
  body              text        NOT NULL CHECK (char_length(body) BETWEEN 1 AND 2000),
  client_request_id uuid        NOT NULL,
  created_at        timestamptz NOT NULL DEFAULT now(),
  deleted_at        timestamptz,
  UNIQUE (author_id, client_request_id)
);
CREATE INDEX comments_top   ON comments (post_id, created_at DESC, id DESC) WHERE parent_id IS NULL;
CREATE INDEX comments_reply ON comments (parent_id, created_at, id)         WHERE parent_id IS NOT NULL;

-- A trigger is a function the database runs automatically on every INSERT. This one runs BEFORE the row is stored.
-- p comments%ROWTYPE is a variable shaped like one row of comments (the parent row).
-- replies may only target a top-level comment of the SAME post (threads are two levels deep)
CREATE FUNCTION check_parent() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE p comments%ROWTYPE;
BEGIN
  IF NEW.parent_id IS NOT NULL THEN
    SELECT * INTO p FROM comments WHERE id = NEW.parent_id;
    IF p.post_id <> NEW.post_id THEN RAISE EXCEPTION 'parent_in_other_post'; END IF;
    IF p.parent_id IS NOT NULL   THEN RAISE EXCEPTION 'reply_to_reply_not_allowed'; END IF;
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER comments_check_parent BEFORE INSERT ON comments FOR EACH ROW EXECUTE FUNCTION check_parent();

CREATE FUNCTION add_comment(p_post bigint, p_parent bigint, p_author bigint, p_body text, p_req uuid)
RETURNS TABLE (out_id bigint, out_replayed boolean, out_post_count integer)
LANGUAGE plpgsql AS $$
DECLARE new_id bigint;
BEGIN
  INSERT INTO comments (post_id, parent_id, author_id, body, client_request_id)
  VALUES (p_post, p_parent, p_author, p_body, p_req)
  ON CONFLICT (author_id, client_request_id) DO NOTHING
  RETURNING id INTO new_id;
  IF new_id IS NOT NULL THEN
    UPDATE post_stats SET comment_count = comment_count + 1 WHERE id = p_post;
    RETURN QUERY SELECT new_id, false, c.comment_count FROM post_stats c WHERE c.id = p_post;
  ELSE
    RETURN QUERY SELECT x.id, true, c.comment_count FROM comments x, post_stats c
                  WHERE x.author_id = p_author AND x.client_request_id = p_req AND c.id = x.post_id;
  END IF;
END $$;

CREATE FUNCTION delete_comment(p_id bigint, p_author bigint) RETURNS integer LANGUAGE plpgsql AS $$
DECLARE n integer; pid bigint;
BEGIN
  UPDATE comments SET deleted_at = now()
   WHERE id = p_id AND author_id = p_author AND deleted_at IS NULL RETURNING post_id INTO pid;
  GET DIAGNOSTICS n = ROW_COUNT;
  IF n = 1 THEN UPDATE post_stats SET comment_count = comment_count - 1 WHERE id = pid; END IF;
  RETURN (SELECT comment_count FROM post_stats WHERE id = coalesce(pid, (SELECT post_id FROM comments WHERE id = p_id)));
END $$;

INSERT INTO post_stats VALUES (1, 0);
-- 5 top-level comments, one minute apart
INSERT INTO comments (post_id, author_id, body, client_request_id, created_at)
SELECT 1, g, 'top ' || g, gen_random_uuid(), timestamptz '2026-01-01 10:00:00+00' + g * interval '1 minute'
FROM generate_series(1, 5) g;
UPDATE post_stats SET comment_count = 5 WHERE id = 1;

• UNIQUE (author_id, client_request_id) is the idempotency guard (idempotent: doing it twice has the same effect as once): a retry after a lost response inserts nothing and gets the original row back.
• The trigger rejects a reply whose parent is itself a reply or belongs to a different post. It looks up the parent row into p, raises an error if p.post_id differs, and raises another if p.parent_id is set (the parent is itself a reply). Raising an error aborts the whole insert.
• add_comment tries the insert with ON CONFLICT ... DO NOTHING RETURNING id INTO new_id: new_id is filled only if a row was really inserted. If it is set, the counter goes up by 1; if it is null, the same author and request id already exist, so the function returns that existing comment and flags replayed.
• delete_comment stamps deleted_at only on a row that is not yet deleted, remembering its post in pid. The count drops only when exactly one row changed (n = 1). The final RETURN reads the post's count; the coalesce(pid, (SELECT post_id ...)) part finds the post from the comment itself when nothing changed (a repeat), so a repeated delete still returns the count.
• The two partial indexes (indexes that only contain rows matching a WHERE) match the two list queries exactly: top-level comments for a post newest first, and replies for a parent oldest first.
• comment_count counts visible comments and replies together. Create adds 1 only when a row was actually inserted; delete subtracts 1 only when deleted_at was null before, so repeats cannot drift it.

Driving it:

SELECT * FROM add_comment(1, 2, 42, 'reply to top 2', '11111111-1111-1111-1111-111111111111');
SELECT * FROM add_comment(1, 2, 42, 'reply to top 2', '11111111-1111-1111-1111-111111111111');
SELECT * FROM add_comment(1, 6, 42, 'too deep', '22222222-2222-2222-2222-222222222222');
SELECT delete_comment(6, 42);
SELECT delete_comment(6, 42);
SELECT id, body, created_at FROM comments
 WHERE post_id = 1 AND parent_id IS NULL
 ORDER BY created_at DESC, id DESC LIMIT 3;
SELECT id, body, created_at FROM comments
 WHERE post_id = 1 AND parent_id IS NULL
   AND (created_at, id) < ('2026-01-01 10:04:00+00', 4)
 ORDER BY created_at DESC, id DESC LIMIT 3;

Run in a postgres:16 container, the output is:

reply, first call:        out_id 6 | out_replayed f | out_post_count 6
reply, same request id:   out_id 6 | out_replayed t | out_post_count 6
reply to reply:           ERROR:  reply_to_reply_not_allowed
delete twice:             5, then 5
page 1 (ids):             5, 4, 3
page 2 (ids):             3, 2, 1

The condition (created_at, id) < ('2026-01-01 10:04:00+00', 4) is a row comparison: it compares created_at first, and only when the two times are equal does it compare id. It means "strictly before the bookmarked comment in newest-first order", and comment 4 itself, which equals the bookmark, is excluded. Tracing the data: the five top-level comments were made one minute apart, so comment 5 is at 10:05, comment 4 at 10:04, down to comment 1 at 10:01. After the bookmark (10:04, 4) the qualifying rows are 3, 2 and 1. The reply with id 6 is not in these lists because it has a parent_id. The count of 6 after the first add_comment is the 5 seeded comments plus the new reply (the UPDATE after the seed set the stored count to 5); the repeat inserts nothing, so it stays 6, and the reply to a reply is rejected by the trigger before any count changes.

Each page query asks for limit + 1 rows (here limit 2, so 3 rows; the printed queries use LIMIT 3 for that reason, so each prints one row more than the client receives). The server returns the first 2 and sets next_cursor only if the extra row exists, which tells the client whether more comments exist without a count(*). On page 1 it returns 5 and 4 with a cursor encoding (2026-01-01T10:04:00Z, 4); page 2 starts after that point: the query returns 3, 2 and 1, the server keeps 3 and 2, and it sets a cursor because the extra row, comment 1, exists. A comment posted between the two requests does not shift page 2, which is the failure an OFFSET page number has.

Client behaviour

• The text box keeps its draft until a 201 or 200 arrives. On submit the client adds the comment to the list immediately with a local client_request_id and a "sending" style; the server's item replaces it. On failure the item shows "Not posted. Retry", and Retry resends the same client_request_id.
• The comment count in the header is replaced by the number in the create or delete response, never incremented locally forever.
• New comments from other people are fetched on demand ("3 new comments") rather than inserted under the reader's cursor.

Working in parallel

1. Write the API description first (an OpenAPI file: a machine-readable document listing every endpoint, its parameters and the shape of each response, for example a GET /v1/posts/{id}/comments entry whose 200 response lists items and next_cursor) and review it with both clients and the backend owner.
2. Stand up a mock server (a fake backend that answers from the description with canned data) generated from it, so web and mobile run against fixtures on day one.
3. Keep a shared folder of example responses, including the awkward ones: empty thread, tombstone with replies, last page with next_cursor: null, 422 errors.
4. In CI (the automated checks that run on every change), run a contract test (a test that checks the real server's responses match the agreed description) that calls the real backend and validates every response against the description, so the backend cannot drift from what the clients were built on.

Test, roll out, observe

Database tests: duplicate submit, reply to reply, double delete, and counter equal to the number of non-deleted rows. Client tests: optimistic insert, failed send and retry, tombstone rendering. Roll out behind a flag (a switch that turns the feature on for some users and can turn it off at once), web first. Watch the 422 rate, p95 of the list call (the time that 95% of calls beat), and a scheduled check that comment_count equals the number of visible rows.

Trade-offs

Embedding reply previews makes the first response bigger but saves a round trip per thread. A stored count is fast to read and needs the discipline above; computing count(*) per request is always correct and gets slow on busy posts. Recommended: stored count, with the drift check.