Incident: duplicate records from retries

A householder rings the sweeps' association to book a sweep. The clerk puts her on hold while radioing the sweeps, who are all up ladders this morning.

On the booking line	In the incident
ten minutes of hold music, then she rings off and calls again	the app's timeout and resend (steps 6–7)
a second clerk books a second sweep	the resend runs the whole operation again
two sweeps arrive, each charging a call-out fee, though she was told nothing was booked	two rows, two charges, "Registration failed" (step 1)
the two bookings on the day sheet, ten minutes apart	the pair's gap equals the timeout (step 4)
a job number she is given when she first asks, and reads out on every call	one key per intent (step 9)
the clerk reads the number to the sweeps' rota, so a sweep hearing a taken number stands down	the key passed on to the provider (step 10)
a "one line per job number" rule, written only after the doubled lines are struck out and refunded	clean, then constrain (steps 11–12)

Breaks down when: a clerk recognises a caller's voice. An API cannot: unless the request carries the number, two calls with the same words are two bookings.

Contrasting case: a candidate who taps Register twice. The rows pair up too, but they sit as far apart as her taps and appear on every release. Each tap makes its own key, so a key per tap keeps them apart: to the API they are two intents.

In real terms: after a page about doubled records, measure the gap and group by release before reading code, and fix every hop that resends, not only the app.

4.1 ExamHall EXH-2980: registration during the 11:00 slowdown

Candidate app 3.8 registers 80 candidates for the ECON-201 session, one tap every 15 s from 10:50. From 11:00:00 to 11:09:15 the provider takes 12 s per charge, otherwise 2 s:

4.1 PostgreSQL 17.11 · app 3.8's Register code · 80 ECON-201 taps, one every 15 s from 10:50:00 · provider 12 s per charge from 11:00:00 to 11:09:15, 2 s otherwise
4.1 taps 80 · requests sent 117 · registrations 117 · provider charges 117
4.1 candidates with two ECON-201 registrations: 37 · charged twice: 37 · their app showed "Registration failed": 37 of 37
4.1 each pair's two rows created 10 s apart, the app's timeout: 37 of 37
4.1 the other 43 taps: one registration, one charge, "Registered": 43 of 43

Every candidate whose tap met the slowdown has two rows and two charges, and was told the registration failed (steps 1 and 6). Each pair's rows sit exactly the app's 10 s timeout apart, so the second row is the resend, not a second tap (step 4).

4.2 Chat MR !3280: clean the pairs, then constrain

A chat app's release 7.1 already gave every send one id, made at the tap and reused by its resend, but the send endpoint stored it without checking it:

async function send(roomId, text) {
  const clientMsgId = crypto.randomUUID();   // once per tap; the resend reuses it
  for (let attempt = 1; attempt <= 2; attempt++) {
    try {
      const res = await fetch(`/api/rooms/${roomId}/messages`, {
        method: 'POST',
        headers: { 'content-type': 'application/json' },
        body: JSON.stringify({ text, clientMsgId }),
        signal: AbortSignal.timeout(5_000),
      });
      if (res.status === 201) return 'sent';
    } catch {}
  }
  return 'not sent';
}

MR !3280 first removes the later copy of each pair, then builds the unique index without blocking writes and attaches it, each statement on its own:

DELETE FROM messages a USING messages b
  WHERE a.client_msg_id = b.client_msg_id AND a.id > b.id;
CREATE UNIQUE INDEX CONCURRENTLY messages_client_msg_id_key ON messages (client_msg_id);
ALTER TABLE messages ADD CONSTRAINT messages_client_msg_id_key UNIQUE USING INDEX messages_client_msg_id_key;

Then the endpoint inserts first, and asks the push service only for a new row, passing it the id as Idempotency-Key:

INSERT INTO messages (room_id, author_id, body, client_msg_id, created_at)
  VALUES ($1, $2, $3, $4, now())
  ON CONFLICT (client_msg_id) DO NOTHING;

4.2 PostgreSQL 17.11 · release 7.1 (one client_msg_id per tap, reused by the resend) · 9 sends 20:02-20:10 · push service 8 s per call
4.2 before MR !3280: messages 18 · client_msg_ids written twice 9 · push notifications 18
4.2 MR !3280 repair, each statement on its own: DELETE FROM messages · CREATE UNIQUE INDEX · ALTER TABLE messages · all ok
4.2 after the repair: messages 9 · client_msg_ids written twice 0 · unique constraints 1 · every index valid: true
4.2 the same slowdown after MR !3280, 9 new sends: messages 9 · push notifications 9 · the app showed "sent": 9 of 9

With the pairs gone, the index builds and the constraint attaches (steps 11–12). In the next slowdown each send writes one row and one notification, and the resend is answered at once, so the app shows "sent" (steps 9–10).