Use a vector of sequence numbers, not timestamps, to restart /api/events streams.

The timestamp-based approach had some formal problems. In particular, it assumed that time always went forwards, which isn't necessarily the case:

* Alice calls `/api/channels/Cfoo` to send a message.
* The server assigns time T to the request.
* The server stalls somewhere in send() for a while, before storing and broadcasting the message. If it helps, imagine blocking on `tx.begin().await?` for a while.
  * In this interval, Bob calls `/api/events?channel=Cfoo`, receives historical messages up to time U (after T), and disconnects.
* The server resumes Alice's request and finishes it.
  * Bob reconnects, setting his Last-Event-Id header to timestamp U.

In this scenario, Bob never sees Alice's message unless he starts over. It wasn't in the original stream, since it wasn't broadcast while Bob was subscribed, and it's not in the new stream, since Bob's resume point is after the timestamp on Alice's message.

The new approach avoids this. Each message is assigned a _sequence number_ when it's stored. Bob can be sure that his stream included every event, since the resume point is identified by sequence number even if the server processes them out of chronological order:

* Alice calls `/api/channels/Cfoo` to send a message.
* The server assigns time T to the request.
* The server stalls somewhere in send() for a while, before storing and broadcasting.
   * In this interval, Bob calls `/api/events?channel=Cfoo`, receives historical messages up to sequence Cfoo=N, and disconnects.
* The server resumes Alice's request, assigns her message sequence M (after N), and finishes it.
  * Bob resumes his subscription at Cfoo=N.
  * Bob receives Alice's message at Cfoo=M.

There's a natural mutual exclusion on sequence numbers, enforced by sqlite, which ensures that no two messages have the same sequence number. Since sqlite promises that transactions are serializable by default (and enforces this with a whole-DB write lock), we can be confident that sequence numbers are monotonic, as well.

This scenario is, to put it mildly, contrived and unlikely - which is what motivated me to fix it. These kinds of bugs are fiendishly hard to identify, let alone reproduce or understand.

I wonder how costly cloning a map is going to turn out to be…

A note on database migrations: sqlite3 really, truly has no `alter table … alter column` statement. The only way to modify an existing column is to add the column to a new table. If `alter column` existed, I would create the new `sequence` column in `message` in a much less roundabout way. Fortunately, these migrations assume that they are being run _offline_, so operations like "replace the whole table" are reasonable.

1 files changed, 5 insertions, 7 deletions

diff --git a/docs/api.md b/docs/api.md
index 969e939..8bb3c0b 100644
--- a/docs/api.md
+++ b/docs/api.md
@@ -156,20 +156,22 @@ The returned stream may terminate, to limit the number of outstanding messages h
 
 This endpoint accepts the following query parameters:
 
-* `channel`: a channel ID. Events for this channel will be included in the response. This parameter may be provided multiple times.
+* `channel`: a channel ID to subscribe to. Events for this channel will be included in the response. This parameter may be provided multiple times. Clients should not subscribe to the same channel more than once in a single request.
 
 Browsers generally limit the number of open connections, often to embarrassingly low limits. Clients should subscribe to multiple streams in a single request, and should not subscribe to each stream individually.
 
-Requests without parameters will be successful, but will return an empty stream.
+Requests without a subscription return an empty stream.
 
 (If you're wondering: it has to be query parameters or something equivalent to it, since `EventSource` can only issue `GET` requests.)
 
 #### Request headers
 
-This endpoint accepts an optional `Last-Event-Id` header for resuming an interrupted stream. If this header is provided, it must be set to the `id` of the last event processed by the client. The new stream will resume immediately after that event. If this header is omitted, then the stream will start from the beginning.
+This endpoint accepts an optional `Last-Event-Id` header for resuming an interrupted stream. If this header is provided, it must be set to the `id` field sent with the last event the client has processed. When `Last-Event-Id` is sent, the response will resume immediately after the corresponding event. If this header is omitted, then the stream will start from the beginning.
 
 If you're using a browser's `EventSource` API, this is handled for you automatically.
 
+The event IDs `hi` sends in `application/event-stream` encoding are ephemeral, and can only be reused within the brief intervals required to reconnect to the event stream. Do not store them, and do not parse them. The message data's `"id"` field is the durable identifier for each message.
+
 #### On success
 
 The returned event stream is a sequence of events:
@@ -187,7 +189,3 @@ data:     "body": "my amazing thoughts, by bob",
 data:     "sent_at": "2024-09-19T02:30:50.915462Z"
 data: }
 ```
-
-The event `id` (`1234`, in the example above) is used to support resuming the stream after an interruption. See the "Request headers" section, above, for details. Event IDs are ephemeral, and can only be reused within the brief intervals required to reconnect to the event stream. Do not store them, and do not parse them.
-
-The `"id"` field uniquely identifies the message in related API requests, but is not used to resume the stream.