Nakodo 支援對話的訊息時間戳逐毫秒遞增，避免同一交易寫入相同時間

Nakodo's support widget does not open a ticket straight away. It walks a manual of answers, offers a model that may only answer from that manual, and opens a conversation with a person when neither got there. By the time a conversation is opened, the widget has already shown several things, and all of it goes into the thread so nobody is asked to repeat themselves.

That means opening a conversation is not one insert. It is a ticket plus up to 31 messages, in one transaction, and the first problem is the one in the title.

defaultNow() cannot order rows it wrote together

Every message row has createdAt, and the thread is read back with orderBy(supportMessages.createdAt). If those timestamps come from defaultNow(), Postgres gives every row in the transaction the same value, because now() is the transaction's start time, not the statement's and certainly not the row's. The thread then comes back in whatever order the rows happen to land in, which for a multi-row insert is usually the right one and is not guaranteed to be.

So the timestamps are written by the application, a millisecond apart:

const base = Date.now();
const lastMessageAt = new Date(base + lines.length);

await tx.insert(supportMessages).values(
  lines.map((line, i) => ({
    ticketId: ticket.id,
    author: line.author,
    body: line.body,
    meta: line.meta ?? (i === 0 && input.topic ? { topic: input.topic } : null),
    createdAt: new Date(base + i),
  })),
);

The ticket itself gets createdAt: new Date(base) and lastMessageAt one millisecond past the last line, so lastMessageAt is strictly greater than every message in the thread. And then:

// They have just written it, so there is nothing here they haven't read.
readAt: lastMessageAt,

Which is the second half of the design, below.

clock_timestamp() would have worked too, and it would have been truer: it moves inside a transaction. We did not use it because the ordering wanted here is not "when did Postgres write the row", it is "in what order were these things said", and that order is known in the application before the insert starts. A sequence column would also have worked and would have cost a column.

A reference short enough to quote is short enough to collide

People quote a reference back at us, read off a screen or over the phone, so it is six characters from an alphabet with no lookalikes:

export const SUPPORT_REF_ALPHABET = "abcdefghjkmnpqrstuvwxyz23456789"; // 31
export const SUPPORT_REF_LENGTH = 6;

31 characters, 6 long, 887,503,681 references. That sounds like plenty and it is not, because the question is never "will this one collide with a particular other one", it is the birthday question. At 10,000 conversations the chance that some pair shares a reference is about 5%. At 30,000 it is about 40%. (The comment in our code says a clash needs hundreds of thousands of conversations. The comment is wrong and the code is right, which is the better way round.)

The code is right because it does not reason about probability at all. It asks the database:

for (let attempt = 0; attempt < REF_ATTEMPTS; attempt++) {
  const reference = newRef();
  const opened = await db.transaction(async (tx) => {
    const [ticket] = await tx
      .insert(supportTickets)
      .values({ reference, /* ... */ })
      .onConflictDoNothing({ target: supportTickets.reference })
      .returning({ id: supportTickets.id });
    if (!ticket) return null;
    // ... the messages, in the same transaction
    return { id: ticket.id, reference, token };
  });
  if (opened) return opened;
}
throw new Error("Could not find an unused support reference");

onConflictDoNothing plus returning is the whole trick: a conflict gives you no row back, which you can test. Five attempts, and the unique index support_tickets_reference_uq is the only thing that decides. Note that the collision check costs nothing extra: it is the insert.

Unread is one SQL expression, used everywhere

"Unread" here means the thread moved after the person last looked at it:

const unread = sql<boolean>`(${supportTickets.readAt} is null or ${supportTickets.lastMessageAt} > ${supportTickets.readAt})`;

const summaryColumns = {
  reference: supportTickets.reference,
  kind: supportTickets.kind,
  status: supportTickets.status,
  subject: supportTickets.subject,
  topic: supportTickets.topic,
  lastMessageAt: supportTickets.lastMessageAt,
  createdAt: supportTickets.createdAt,
  unread,
};

summaryColumns is spread into four queries: the person's own list, one thread, the operator's queue and the operator's single thread. Nothing computes unreadness in TypeScript, so the widget's attention mark and the operator's queue cannot disagree about it.

The rule that makes it cheap is on the write side. Every time the person who opened the conversation adds a reply, from the app or from their mail client, the read mark moves with it:

const [updated] = await tx
  .update(supportTickets)
  .set({ status: "open", lastMessageAt: now, readAt: now })

So their own words are never unread, and an unread thread always means something from our side. That is one set clause instead of a rule about authorship in the query.

One place does repeat the predicate: the count on every signed-in page.

export async function unreadCount(userId: string): Promise<number> {
  const [row] = await db
    .select({ n: count() })
    .from(supportTickets)
    .where(and(eq(supportTickets.userId, userId), or(isNull(supportTickets.readAt), sql`${supportTickets.lastMessageAt} > ${supportTickets.readAt}`)));
  return row?.n ?? 0;
}

A projection cannot be a where clause without wrapping the whole thing in a subquery, so the condition is written twice. Two copies of four tokens, with the comment above them saying why.

Reading a thread clears the mark, in that order

const lines = await linesOf(id);
// Reading it is what clears the mark. Done after the lines are in hand, so a
// failure here leaves the mark rather than hiding an unread reply.
if (options.markRead && summary.unread) {
  await db.update(supportTickets).set({ readAt: new Date() }).where(eq(supportTickets.id, id));
}

If the update came first and the fetch failed, the attention mark would be gone and the reply would still be unread. The worst case in this order is that the mark survives a read, which costs one extra visit.

And getThread takes the user id into the where:

.where(and(eq(supportTickets.reference, reference), eq(supportTickets.userId, userId)))

so somebody else's reference and a reference that does not exist are the same answer, null, by construction rather than by a check afterwards.

The queue pages on two columns

The operator's queue is ordered by last_message_at desc. Offsets are wrong for that list: a conversation that gets a reply jumps to the top, and page two then repeats or skips. So the cursor is the two columns together:

const encodeCursor = (at: Date, id: string): string => `${at.toISOString()}|${id}`;

where.push(
  or(
    lt(supportTickets.lastMessageAt, after.at),
    and(eq(supportTickets.lastMessageAt, after.at), lt(supportTickets.id, after.id)),
  ),
);

.orderBy(desc(supportTickets.lastMessageAt), desc(supportTickets.id))
.limit(limit + 1);

limit + 1 answers "is there another page" without a count. The extra row is sliced off and the last kept row becomes the next cursor.

The tie-break on id is not theoretical here, and that is the part worth taking away: this table writes its own timestamps, a millisecond apart, from one process. Two conversations sharing a millisecond is a thing we actively cause. Order on the timestamp alone and one of them sits on a page boundary and is never shown at all.

The row count per thread comes along as a correlated subquery:

lines: sql<number>`(select count(*)::int from ${supportMessages} where ${supportMessages.ticketId} = ${supportTickets.id})`,

::int because count(*) is bigint and arrives as a string otherwise.

A status that changes needs a line saying so

export async function setStatus(reference: string, status: SupportStatus, note: string) {

Closing or resolving a conversation by hand inserts a system message with the note, in the same transaction as the status change. A status that moves under somebody with no explanation in the thread reads as a bug, and a note costs one row.

Go and look

The widget and the list are behind the login, which you can check from the outside:

$ curl -sI https://nakodo.app/settings/support | grep -iE "^HTTP|^location"
HTTP/2 307
location: /login?next=%2Fsettings%2Fsupport

What is public is who the "operator" in all this code actually is. The about page says we answer the email at hello@nakodo.app ourselves, which is the honest reason the queue is a keyset cursor over one table and not a helpdesk product: it is one queue, read by the people who wrote the thing being asked about. The context fields on a ticket (the page they were on, the campaign, the plan) are there so that queue can be answered without asking them to explain their setup, and how it works is the page those questions are usually about.