1 files changed, 38 insertions, 0 deletions
diff --git a/migrations/20240924232919_message_serial_numbers.sql b/migrations/20240924232919_message_serial_numbers.sql
new file mode 100644
index 0000000..a53e4a2
--- /dev/null
+++ b/migrations/20240924232919_message_serial_numbers.sql
@@ -0,0 +1,38 @@
+create table sequenced_message (
+	id text
+		not null
+		primary key,
+	sequence bigint
+		not null,
+	channel text
+		not null
+		references channel (id),
+	sender text
+		not null
+		references login (id),
+	body text
+		not null,
+	sent_at text
+		not null,
+	unique (channel, sequence)
+);
+
+insert into sequenced_message
+select
+	id,
+	rank() over (
+		partition by channel
+		order by sent_at
+	) as sequence,
+	channel,
+	sender,
+	body,
+	sent_at
+from message;
+
+drop table message;
+
+alter table sequenced_message
+rename to message;
+
+create index message_sent_at on message (channel, sent_at);