1 files changed, 57 insertions, 0 deletions
diff --git a/migrations/20240928002608_channel_lifecycle.sql b/migrations/20240928002608_channel_lifecycle.sql
new file mode 100644
index 0000000..bc690d7
--- /dev/null
+++ b/migrations/20240928002608_channel_lifecycle.sql
@@ -0,0 +1,57 @@
+alter table channel
+rename to old_channel;
+
+-- Add new columns
+create table channel (
+	id text
+		not null
+		primary key,
+	name text
+		not null
+		unique,
+	created_at text
+		not null
+);
+
+-- Transfer data from original table
+insert into channel
+select
+	channel.id,
+	channel.name,
+	coalesce(
+		min(message.sent_at),
+		strftime('%FT%R:%f+00:00', 'now', 'utc')
+	) as created_at
+from old_channel as channel
+	left join message on channel.id = message.channel
+group by channel.id, channel.name;
+
+-- Fix up `message` foreign keys
+alter table message
+rename to old_message;
+
+create table 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 message
+select * from old_message;
+
+-- Bury the bodies respectfully
+drop table old_message;
+drop table old_channel;