1 files changed, 126 insertions, 0 deletions
diff --git a/migrations/20241002003606_global_sequence.sql b/migrations/20241002003606_global_sequence.sql
new file mode 100644
index 0000000..198b585
--- /dev/null
+++ b/migrations/20241002003606_global_sequence.sql
@@ -0,0 +1,126 @@
+create table event_sequence (
+	last_value bigint
+		not null
+);
+
+create unique index event_sequence_singleton
+on event_sequence (0);
+
+-- Attempt to assign events sent so far a globally-unique sequence number,
+-- maintaining an approximation of the order they were sent in. This can
+-- introduce small ordering anomalies (where the resulting sequence differs
+-- from the order they were sent in) for events that were sent close in time;
+-- I've gone with chronological order here as it's the closest thing we have to
+-- a global ordering, and because the results will be intuitive to most users.
+create temporary table raw_event (
+	type text
+		not null,
+	at text
+		not null,
+	channel text
+		unique,
+	message text
+		unique,
+	check ((channel is not null and message is null) or (message is not null and channel is null))
+);
+
+insert into raw_event (type, at, channel)
+select
+	'channel' as type,
+	created_at as at,
+	id as channel
+from channel;
+
+insert into raw_event (type, at, message)
+select
+	'message' as type,
+	sent_at as at,
+	id as message
+from message;
+
+create temporary table event (
+	type text
+		not null,
+	sequence
+		unique
+		not null,
+	at text
+		not null,
+	channel text
+		unique,
+	message text
+		unique,
+	check ((channel is not null and message is null) or (message is not null and channel is null))
+);
+
+insert into event
+select
+	type,
+	rank() over (order by at) - 1 as sequence,
+	at,
+	channel,
+	message
+from raw_event;
+
+drop table raw_event;
+
+alter table channel rename to old_channel;
+alter table message rename to old_message;
+
+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
+);
+
+insert into channel
+select
+	c.id,
+	c.name,
+	e.sequence,
+	c.created_at
+from old_channel as c join event as e
+	on e.channel = c.id;
+
+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
+);
+
+insert into message
+select
+	m.id,
+	m.channel,
+	m.sender,
+	e.sequence,
+	m.sent_at,
+	m.body
+from old_message as m join event as e
+	on e.message = m.id;
+
+insert into event_sequence
+select coalesce(max(sequence), 0) from event;
+
+drop table event;