1 files changed, 217 insertions, 0 deletions
diff --git a/migrations/20241009031441_login_created_at.sql b/migrations/20241009031441_login_created_at.sql
new file mode 100644
index 0000000..001c48e
--- /dev/null
+++ b/migrations/20241009031441_login_created_at.sql
@@ -0,0 +1,217 @@
+-- figure out new event seqeuence.
+--
+-- The main problem here is ensuring that the resulting stream is _consistent_.
+-- For any message, the login it refers to should be created in the event stream
+-- before the message does, and the channel it refers to should likewise be
+-- created first.
+--
+-- Messages come after channels by time (clock slew allowing), so we can reuse
+-- those timestamps to recover a global ordering between channels and messages.
+--
+-- We synthesize a login's created_at from the earliest of:
+--
+-- * the earliest message they sent that we know of, or
+-- * right now (no messages in recorded history).
+--
+-- This produces a consistent sequence, up to clock slew, at the expense of
+-- renumbering every event.
+
+create table unsequenced (
+	at text
+		not null,
+	login text
+		references login (id),
+	channel text
+		references channel (id),
+	message text
+		references message (id),
+	check (
+		(login is not null and channel is null and message is null)
+		or (login is null and channel is not null and message is null)
+		or (login is null and channel is null and message is not null)
+	)
+);
+
+insert into unsequenced (at, login)
+select
+	coalesce (
+		min(message.sent_at),
+		strftime('%FT%R:%f+00:00', 'now', 'utc')
+	),
+	login.id
+from login
+left join message
+	on login.id = message.sender;
+
+insert into unsequenced (at, channel)
+select created_at, id
+from channel;
+
+insert into unsequenced (at, message)
+select sent_at, id
+from message;
+
+create table event (
+	at text
+		not null,
+	sequence
+		primary key
+		not null,
+	login text
+		references login (id),
+	channel text
+		references channel (id),
+	message text
+		references message (id),
+	check (
+		(login is not null and channel is null and message is null)
+		or (login is null and channel is not null and message is null)
+		or (login is null and channel is null and message is not null)
+	)
+);
+
+insert into event (at, sequence, login, channel, message)
+select
+	at, row_number() over (order by at, login is null, message is null), login, channel, message
+from unsequenced;
+
+-- Get this out of memory.
+drop table unsequenced;
+
+-- Because of how foundational `login` is, we pretty much have to recreate the
+-- whole schema.
+alter table message
+rename to old_message;
+
+alter table channel
+rename to old_channel;
+
+alter table token
+rename to old_token;
+
+alter table login
+rename to old_login;
+
+create table login (
+	id text
+		not null
+		primary key,
+	name text
+		not null
+		unique,
+	password_hash text
+		not null,
+	created_sequence bigint
+		unique
+		not null,
+	created_at text
+		not null
+);
+
+create table token (
+	id text
+		not null
+		primary key,
+	secret text
+		not null
+		unique,
+	login text
+		not null
+		references login (id),
+	issued_at text
+		not null,
+	last_used_at text
+		not null
+);
+
+create table channel (
+	id text
+		not null
+		primary key,
+	name text
+		unique
+		not null,
+	created_sequence bigint
+		unique
+		not null,
+	created_at text
+		not null
+);
+
+create table message (
+	id text
+		not null
+		primary key,
+	channel text
+		not null
+		references channel (id),
+	sender text
+		not null
+		references login (id),
+	sent_sequence bigint
+		unique
+		not null,
+	sent_at text
+		not null,
+	body text
+		not null
+);
+
+-- Copy data from the original tables, assigning sequence numbers as we go.
+insert into login (id, name, password_hash, created_sequence, created_at)
+select
+	old.id,
+	old.name,
+	old.password_hash,
+	event.sequence,
+	event.at
+from old_login as old
+join event on old.id = event.login
+order by event.sequence;
+
+insert into token (id, secret, login, issued_at, last_used_at)
+select id, secret, login, issued_at, last_used_at from old_token;
+
+insert into channel (id, name, created_sequence, created_at)
+select
+	old.id,
+	old.name,
+	event.sequence,
+	old.created_at
+from old_channel as old
+join event on old.id = event.channel
+order by event.sequence;
+
+insert into message (id, channel, sender, sent_sequence, sent_at, body)
+select
+	old.id,
+	old.channel,
+	old.sender,
+	event.sequence,
+	old.sent_at,
+	old.body
+from old_message as old
+join event on old.id = event.message
+order by event.sequence;
+
+-- Restart the event sequence, using the highest sequence number in the new
+-- event series.
+update event_sequence
+set last_value = (select coalesce(max(sequence), 0) from event);
+
+-- Clean up the now-unused original tables, plus the resequencing temp table
+drop table event;
+drop table old_message;
+drop table old_channel;
+drop table old_token;
+drop table old_login;
+
+-- Reindex, now that the original indices are no longer in the way
+create index token_issued_at
+on token (issued_at);
+
+create index message_sent_at
+on message (sent_at);
+
+create index channel_created_at
+on channel (created_at);