Start fresh with database migrations.

The migration path from the original project inception to now was complicated and buggy, and stranded _both_ Kit and I with broken databases due to oversights and incomplete migrations. We've agreed to start fresh, once.

If this is mistakenly started with an original-schema-flavour DB, startup will be aborted.

1 files changed, 0 insertions, 126 deletions

diff --git a/migrations/20241002003606_global_sequence.sql b/migrations/20241002003606_global_sequence.sql
deleted file mode 100644
index 198b585..0000000
--- a/migrations/20241002003606_global_sequence.sql
+++ /dev/null
@@ -1,126 +0,0 @@
-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;