From 777e4281431a036eb663b5eec70f347b7425737d Mon Sep 17 00:00:00 2001
From: Owen Jacobson <owen@grimoire.ca>
Date: Thu, 17 Oct 2024 01:45:58 -0400
Subject: Retain deleted messages and channels temporarily, to preserve events
 for replay.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Previously, when a channel (message) was deleted, `hi` would send events to all _connected_ clients to inform them of the deletion, then delete all memory of the channel (message). Any disconnected client, on reconnecting, would not receive the deletion event, and would de-synch with the service. The creation events were also immediately retconned out of the event stream, as well.

With this change, `hi` keeps a record of deleted channels (messages). When replaying events, these records are used to replay the deletion event. After 7 days, the retained data is deleted, both to keep storage under control and to conform to users' expectations that deleted means gone.

To match users' likely intuitions about what deletion does, deleting a channel (message) _does_ immediately delete some of its associated data. Channels' names are blanked, and messages' bodies are also blanked. When the event stream is replayed, the original channel.created (message.sent) event is "tombstoned", with an additional `deleted_at` field to inform clients. The included client does not use this field, at least yet.

The migration is, once again, screamingingly complicated due to sqlite's limited ALTER TABLE … ALTER COLUMN support.

This change also contains capabilities that would allow the API to return 410 Gone for deleted channels or messages, instead of 404. I did experiment with this, but it's tricky to do pervasively, especially since most app-level interfaces return an `Option<Channel>` or `Option<Message>`. Redesigning these to return either `Ok(Channel)` (`Ok(Message)`) or `Err(Error::NotFound)` or `Err(Error::Deleted)` is more work than I wanted to take on for this change, and the utility of 410 Gone responses is not obvious to me. We have other, more pressing API design warts to address.
---
 migrations/20241017005219_retain_deleted.sql | 92 ++++++++++++++++++++++++++++
 1 file changed, 92 insertions(+)
 create mode 100644 migrations/20241017005219_retain_deleted.sql

(limited to 'migrations/20241017005219_retain_deleted.sql')

diff --git a/migrations/20241017005219_retain_deleted.sql b/migrations/20241017005219_retain_deleted.sql
new file mode 100644
index 0000000..6205482
--- /dev/null
+++ b/migrations/20241017005219_retain_deleted.sql
@@ -0,0 +1,92 @@
+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
+		not null,
+	created_sequence bigint
+		unique
+		not null,
+	created_at text
+		not null,
+	unique (id, name)
+);
+
+insert into channel (id, name, created_sequence, created_at)
+select id, name, created_sequence, created_at from old_channel;
+
+create table channel_name_reservation (
+	id text
+		not null
+		unique,
+	name text
+		not null
+		unique,
+	primary key (id, name),
+	foreign key (id, name)
+		references channel (id, name)
+);
+
+insert into channel_name_reservation (id, name)
+select id, name from old_channel;
+
+create table channel_deleted (
+	id text
+		not null
+		primary key
+		references channel (id),
+	deleted_sequence bigint
+		unique
+		not null,
+	deleted_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
+);
+
+insert into message (id, channel, sender, sent_sequence, sent_at, body)
+select id, channel, sender, sent_sequence, sent_at, body from old_message;
+
+create table message_deleted (
+	id text
+		not null
+		primary key
+		references message (id),
+	deleted_sequence bigint
+		unique
+		not null,
+	deleted_at text
+		not null
+);
+
+drop table old_message;
+drop table old_channel;
+
+create index message_sent_at
+on message (sent_at);
+
+create index channel_created_at
+on channel (created_at);
-- 
cgit v1.2.3

