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


From d3fbecc57b5d6fa3223b945a45fe21eb78ffd49b Mon Sep 17 00:00:00 2001
From: Owen Jacobson <owen@grimoire.ca>
Date: Fri, 18 Oct 2024 23:25:21 -0400
Subject: Switch to blanking tombstoned data with null, not empty string.

This accomplishes two things:

* It removes the need for an additional `channel_name_reservation` table, since `channel.name` now only contains non-null values for active channels, and
* It nicely dovetails with the idea that `null` means an unknown value in SQL-land.
---
 ...f6b07e69ab792b7365f2eb2831f7a2ac13e2ecf323.json | 38 -------------
 ...568c112d0f6fe2aa136f0758a68f1eb66787db52f0.json | 50 +++++++++++++++++
 ...4a4d68d68c8d9f8b9483af48de5d4e09857181d632.json | 20 -------
 ...1ef99603f820d3b391f212c2c0883506a53ee757ca.json | 62 ++++++++++++++++++++++
 ...0c5b16e9ae908c4ea30e2d682d59d4720699b8de91.json |  2 +-
 ...8da75962b278d8191f77ffdea8dad8441f2bf72786.json | 12 -----
 ...46d2ea19a3007558abe81a1635c64ef7960acd30b2.json | 50 +++++++++++++++++
 ...767190eeaaf479b458c18f79ff62c46b7aac158a77.json | 62 ----------------------
 ...1fc629c8edba240926d21fbe03d884e2a48b034336.json | 50 -----------------
 ...9e603b3087a17906db95d14a35034889a253f23418.json |  2 +-
 ...e65e101d0941f2a266679209f793fd27466b9e3719.json |  2 +-
 ...31ca611395405093dbbed738579d715b2d623921be.json | 62 ++++++++++++++++++++++
 ...283b4094fa9dfa65dc95991f689355889606ab1c46.json | 38 +++++++++++++
 ...b3abb8945a49ce18fb715d249bc9d93fc7d10e817d.json |  2 +-
 ...bb69067dd15b2e7602aafcfaf04aa1f41e118a1f87.json | 20 +++++++
 ...6a6a72a129f9484b4d9b9e62e7c546d965cdde721a.json | 62 ----------------------
 ...55c7cf413825151e1cda939378984611dc2d9a789a.json | 20 -------
 ...b2060b9a6d6e0bbcb4cf8d01c48b297e6f0a950ebc.json |  2 +-
 ...e32c2cc4f93832948768dcbd1d12abcdbc5089ca8e.json | 50 -----------------
 ...5294ae908114b03719dc0cb237cec11f807bf757b1.json |  2 +-
 migrations/20241017005219_retain_deleted.sql       | 23 ++------
 src/channel/app.rs                                 |  6 +--
 src/channel/repo.rs                                | 49 +++++------------
 src/message/repo.rs                                | 20 +++----
 24 files changed, 317 insertions(+), 389 deletions(-)
 delete mode 100644 .sqlx/query-023b1e263b68a483704ae5f6b07e69ab792b7365f2eb2831f7a2ac13e2ecf323.json
 create mode 100644 .sqlx/query-152cbd56cdce2ee7e94ae2568c112d0f6fe2aa136f0758a68f1eb66787db52f0.json
 delete mode 100644 .sqlx/query-2252b49c77ff76a88ee7d24a4d68d68c8d9f8b9483af48de5d4e09857181d632.json
 create mode 100644 .sqlx/query-34d3bc2b18bc0813cbaafc1ef99603f820d3b391f212c2c0883506a53ee757ca.json
 delete mode 100644 .sqlx/query-58c17422db7edd8f0462378da75962b278d8191f77ffdea8dad8441f2bf72786.json
 create mode 100644 .sqlx/query-647c3387fd01747b6318b946d2ea19a3007558abe81a1635c64ef7960acd30b2.json
 delete mode 100644 .sqlx/query-6c82af3b136a6719d52f49767190eeaaf479b458c18f79ff62c46b7aac158a77.json
 delete mode 100644 .sqlx/query-771d1afa5961bae86112a51fc629c8edba240926d21fbe03d884e2a48b034336.json
 create mode 100644 .sqlx/query-8e816ccef8ecee937d88ff31ca611395405093dbbed738579d715b2d623921be.json
 create mode 100644 .sqlx/query-9da5b746de5d481eeb0755283b4094fa9dfa65dc95991f689355889606ab1c46.json
 create mode 100644 .sqlx/query-c83af5b462071927d05664bb69067dd15b2e7602aafcfaf04aa1f41e118a1f87.json
 delete mode 100644 .sqlx/query-c90cbe4c836adf7f104b7b6a6a72a129f9484b4d9b9e62e7c546d965cdde721a.json
 delete mode 100644 .sqlx/query-d6feb736b61c645649082e55c7cf413825151e1cda939378984611dc2d9a789a.json
 delete mode 100644 .sqlx/query-e621d064ba52cd13b196bce32c2cc4f93832948768dcbd1d12abcdbc5089ca8e.json

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

diff --git a/.sqlx/query-023b1e263b68a483704ae5f6b07e69ab792b7365f2eb2831f7a2ac13e2ecf323.json b/.sqlx/query-023b1e263b68a483704ae5f6b07e69ab792b7365f2eb2831f7a2ac13e2ecf323.json
deleted file mode 100644
index cc23359..0000000
--- a/.sqlx/query-023b1e263b68a483704ae5f6b07e69ab792b7365f2eb2831f7a2ac13e2ecf323.json
+++ /dev/null
@@ -1,38 +0,0 @@
-{
-  "db_name": "SQLite",
-  "query": "\n                insert\n                into channel (id, name, created_at, created_sequence)\n                values ($1, $2, $3, $4)\n                returning\n                    id as \"id: Id\",\n                    name,\n                    created_at as \"created_at: DateTime\",\n                    created_sequence as \"created_sequence: Sequence\"\n            ",
-  "describe": {
-    "columns": [
-      {
-        "name": "id: Id",
-        "ordinal": 0,
-        "type_info": "Text"
-      },
-      {
-        "name": "name",
-        "ordinal": 1,
-        "type_info": "Text"
-      },
-      {
-        "name": "created_at: DateTime",
-        "ordinal": 2,
-        "type_info": "Text"
-      },
-      {
-        "name": "created_sequence: Sequence",
-        "ordinal": 3,
-        "type_info": "Integer"
-      }
-    ],
-    "parameters": {
-      "Right": 4
-    },
-    "nullable": [
-      false,
-      false,
-      false,
-      false
-    ]
-  },
-  "hash": "023b1e263b68a483704ae5f6b07e69ab792b7365f2eb2831f7a2ac13e2ecf323"
-}
diff --git a/.sqlx/query-152cbd56cdce2ee7e94ae2568c112d0f6fe2aa136f0758a68f1eb66787db52f0.json b/.sqlx/query-152cbd56cdce2ee7e94ae2568c112d0f6fe2aa136f0758a68f1eb66787db52f0.json
new file mode 100644
index 0000000..2c7666f
--- /dev/null
+++ b/.sqlx/query-152cbd56cdce2ee7e94ae2568c112d0f6fe2aa136f0758a68f1eb66787db52f0.json
@@ -0,0 +1,50 @@
+{
+  "db_name": "SQLite",
+  "query": "\n                select\n                    id as \"id: Id\",\n                    channel.name,\n                    channel.created_at as \"created_at: DateTime\",\n                    channel.created_sequence as \"created_sequence: Sequence\",\n                    deleted.deleted_at as \"deleted_at?: DateTime\",\n                    deleted.deleted_sequence as \"deleted_sequence?: Sequence\"\n                from channel\n                left join channel_deleted as deleted\n                    using (id)\n                where id = $1\n            ",
+  "describe": {
+    "columns": [
+      {
+        "name": "id: Id",
+        "ordinal": 0,
+        "type_info": "Text"
+      },
+      {
+        "name": "name",
+        "ordinal": 1,
+        "type_info": "Text"
+      },
+      {
+        "name": "created_at: DateTime",
+        "ordinal": 2,
+        "type_info": "Text"
+      },
+      {
+        "name": "created_sequence: Sequence",
+        "ordinal": 3,
+        "type_info": "Integer"
+      },
+      {
+        "name": "deleted_at?: DateTime",
+        "ordinal": 4,
+        "type_info": "Text"
+      },
+      {
+        "name": "deleted_sequence?: Sequence",
+        "ordinal": 5,
+        "type_info": "Integer"
+      }
+    ],
+    "parameters": {
+      "Right": 1
+    },
+    "nullable": [
+      false,
+      true,
+      false,
+      false,
+      false,
+      false
+    ]
+  },
+  "hash": "152cbd56cdce2ee7e94ae2568c112d0f6fe2aa136f0758a68f1eb66787db52f0"
+}
diff --git a/.sqlx/query-2252b49c77ff76a88ee7d24a4d68d68c8d9f8b9483af48de5d4e09857181d632.json b/.sqlx/query-2252b49c77ff76a88ee7d24a4d68d68c8d9f8b9483af48de5d4e09857181d632.json
deleted file mode 100644
index 9975fd1..0000000
--- a/.sqlx/query-2252b49c77ff76a88ee7d24a4d68d68c8d9f8b9483af48de5d4e09857181d632.json
+++ /dev/null
@@ -1,20 +0,0 @@
-{
-  "db_name": "SQLite",
-  "query": "\n                update channel\n                set name = \"\"\n                where id = $1\n                returning 1 as \"updated: bool\"\n            ",
-  "describe": {
-    "columns": [
-      {
-        "name": "updated: bool",
-        "ordinal": 0,
-        "type_info": "Null"
-      }
-    ],
-    "parameters": {
-      "Right": 1
-    },
-    "nullable": [
-      null
-    ]
-  },
-  "hash": "2252b49c77ff76a88ee7d24a4d68d68c8d9f8b9483af48de5d4e09857181d632"
-}
diff --git a/.sqlx/query-34d3bc2b18bc0813cbaafc1ef99603f820d3b391f212c2c0883506a53ee757ca.json b/.sqlx/query-34d3bc2b18bc0813cbaafc1ef99603f820d3b391f212c2c0883506a53ee757ca.json
new file mode 100644
index 0000000..63ff012
--- /dev/null
+++ b/.sqlx/query-34d3bc2b18bc0813cbaafc1ef99603f820d3b391f212c2c0883506a53ee757ca.json
@@ -0,0 +1,62 @@
+{
+  "db_name": "SQLite",
+  "query": "\n                select\n                    id as \"id: Id\",\n                    message.channel as \"channel: channel::Id\",\n                    message.sender as \"sender: login::Id\",\n                    message.sent_at as \"sent_at: DateTime\",\n                    message.sent_sequence as \"sent_sequence: Sequence\",\n                    message.body,\n                    deleted.deleted_at as \"deleted_at?: DateTime\",\n                    deleted.deleted_sequence as \"deleted_sequence?: Sequence\"\n                from message\n                left join message_deleted as deleted\n                    using (id)\n                where message.sent_at < $1\n                    and deleted.id is null\n            ",
+  "describe": {
+    "columns": [
+      {
+        "name": "id: Id",
+        "ordinal": 0,
+        "type_info": "Text"
+      },
+      {
+        "name": "channel: channel::Id",
+        "ordinal": 1,
+        "type_info": "Text"
+      },
+      {
+        "name": "sender: login::Id",
+        "ordinal": 2,
+        "type_info": "Text"
+      },
+      {
+        "name": "sent_at: DateTime",
+        "ordinal": 3,
+        "type_info": "Text"
+      },
+      {
+        "name": "sent_sequence: Sequence",
+        "ordinal": 4,
+        "type_info": "Integer"
+      },
+      {
+        "name": "body",
+        "ordinal": 5,
+        "type_info": "Text"
+      },
+      {
+        "name": "deleted_at?: DateTime",
+        "ordinal": 6,
+        "type_info": "Text"
+      },
+      {
+        "name": "deleted_sequence?: Sequence",
+        "ordinal": 7,
+        "type_info": "Integer"
+      }
+    ],
+    "parameters": {
+      "Right": 1
+    },
+    "nullable": [
+      false,
+      false,
+      false,
+      false,
+      false,
+      true,
+      false,
+      false
+    ]
+  },
+  "hash": "34d3bc2b18bc0813cbaafc1ef99603f820d3b391f212c2c0883506a53ee757ca"
+}
diff --git a/.sqlx/query-400de8e1dca27c48362b060c5b16e9ae908c4ea30e2d682d59d4720699b8de91.json b/.sqlx/query-400de8e1dca27c48362b060c5b16e9ae908c4ea30e2d682d59d4720699b8de91.json
index 3d77307..0caa950 100644
--- a/.sqlx/query-400de8e1dca27c48362b060c5b16e9ae908c4ea30e2d682d59d4720699b8de91.json
+++ b/.sqlx/query-400de8e1dca27c48362b060c5b16e9ae908c4ea30e2d682d59d4720699b8de91.json
@@ -53,7 +53,7 @@
       false,
       false,
       false,
-      false,
+      true,
       true,
       true
     ]
diff --git a/.sqlx/query-58c17422db7edd8f0462378da75962b278d8191f77ffdea8dad8441f2bf72786.json b/.sqlx/query-58c17422db7edd8f0462378da75962b278d8191f77ffdea8dad8441f2bf72786.json
deleted file mode 100644
index ebfb579..0000000
--- a/.sqlx/query-58c17422db7edd8f0462378da75962b278d8191f77ffdea8dad8441f2bf72786.json
+++ /dev/null
@@ -1,12 +0,0 @@
-{
-  "db_name": "SQLite",
-  "query": "\n                insert into channel_name_reservation (id, name)\n                values ($1, $2)\n            ",
-  "describe": {
-    "columns": [],
-    "parameters": {
-      "Right": 2
-    },
-    "nullable": []
-  },
-  "hash": "58c17422db7edd8f0462378da75962b278d8191f77ffdea8dad8441f2bf72786"
-}
diff --git a/.sqlx/query-647c3387fd01747b6318b946d2ea19a3007558abe81a1635c64ef7960acd30b2.json b/.sqlx/query-647c3387fd01747b6318b946d2ea19a3007558abe81a1635c64ef7960acd30b2.json
new file mode 100644
index 0000000..f706f81
--- /dev/null
+++ b/.sqlx/query-647c3387fd01747b6318b946d2ea19a3007558abe81a1635c64ef7960acd30b2.json
@@ -0,0 +1,50 @@
+{
+  "db_name": "SQLite",
+  "query": "\n                select\n                    channel.id as \"id: Id\",\n                    channel.name,\n                    channel.created_at as \"created_at: DateTime\",\n                    channel.created_sequence as \"created_sequence: Sequence\",\n                    deleted.deleted_at as \"deleted_at?: DateTime\",\n                    deleted.deleted_sequence as \"deleted_sequence?: Sequence\"\n                from channel\n                left join channel_deleted as deleted\n                    using (id)\n                left join message\n                where channel.created_at < $1\n                    and message.id is null\n                    and deleted.id is null\n            ",
+  "describe": {
+    "columns": [
+      {
+        "name": "id: Id",
+        "ordinal": 0,
+        "type_info": "Text"
+      },
+      {
+        "name": "name",
+        "ordinal": 1,
+        "type_info": "Text"
+      },
+      {
+        "name": "created_at: DateTime",
+        "ordinal": 2,
+        "type_info": "Text"
+      },
+      {
+        "name": "created_sequence: Sequence",
+        "ordinal": 3,
+        "type_info": "Integer"
+      },
+      {
+        "name": "deleted_at?: DateTime",
+        "ordinal": 4,
+        "type_info": "Text"
+      },
+      {
+        "name": "deleted_sequence?: Sequence",
+        "ordinal": 5,
+        "type_info": "Integer"
+      }
+    ],
+    "parameters": {
+      "Right": 1
+    },
+    "nullable": [
+      false,
+      true,
+      false,
+      false,
+      false,
+      false
+    ]
+  },
+  "hash": "647c3387fd01747b6318b946d2ea19a3007558abe81a1635c64ef7960acd30b2"
+}
diff --git a/.sqlx/query-6c82af3b136a6719d52f49767190eeaaf479b458c18f79ff62c46b7aac158a77.json b/.sqlx/query-6c82af3b136a6719d52f49767190eeaaf479b458c18f79ff62c46b7aac158a77.json
deleted file mode 100644
index 4251f92..0000000
--- a/.sqlx/query-6c82af3b136a6719d52f49767190eeaaf479b458c18f79ff62c46b7aac158a77.json
+++ /dev/null
@@ -1,62 +0,0 @@
-{
-  "db_name": "SQLite",
-  "query": "\n                select\n                    message.channel as \"channel: channel::Id\",\n                    message.sender as \"sender: login::Id\",\n                    id as \"id: Id\",\n                    message.body,\n                    message.sent_at as \"sent_at: DateTime\",\n                    message.sent_sequence as \"sent_sequence: Sequence\",\n                    deleted.deleted_at as \"deleted_at: DateTime\",\n                    deleted.deleted_sequence as \"deleted_sequence: Sequence\"\n                from message\n                left join message_deleted as deleted\n                    using (id)\n                where id = $1\n            ",
-  "describe": {
-    "columns": [
-      {
-        "name": "channel: channel::Id",
-        "ordinal": 0,
-        "type_info": "Text"
-      },
-      {
-        "name": "sender: login::Id",
-        "ordinal": 1,
-        "type_info": "Text"
-      },
-      {
-        "name": "id: Id",
-        "ordinal": 2,
-        "type_info": "Text"
-      },
-      {
-        "name": "body",
-        "ordinal": 3,
-        "type_info": "Text"
-      },
-      {
-        "name": "sent_at: DateTime",
-        "ordinal": 4,
-        "type_info": "Text"
-      },
-      {
-        "name": "sent_sequence: Sequence",
-        "ordinal": 5,
-        "type_info": "Integer"
-      },
-      {
-        "name": "deleted_at: DateTime",
-        "ordinal": 6,
-        "type_info": "Text"
-      },
-      {
-        "name": "deleted_sequence: Sequence",
-        "ordinal": 7,
-        "type_info": "Integer"
-      }
-    ],
-    "parameters": {
-      "Right": 1
-    },
-    "nullable": [
-      false,
-      false,
-      false,
-      false,
-      false,
-      false,
-      true,
-      true
-    ]
-  },
-  "hash": "6c82af3b136a6719d52f49767190eeaaf479b458c18f79ff62c46b7aac158a77"
-}
diff --git a/.sqlx/query-771d1afa5961bae86112a51fc629c8edba240926d21fbe03d884e2a48b034336.json b/.sqlx/query-771d1afa5961bae86112a51fc629c8edba240926d21fbe03d884e2a48b034336.json
deleted file mode 100644
index 8a92f44..0000000
--- a/.sqlx/query-771d1afa5961bae86112a51fc629c8edba240926d21fbe03d884e2a48b034336.json
+++ /dev/null
@@ -1,50 +0,0 @@
-{
-  "db_name": "SQLite",
-  "query": "\n                select\n                    channel.id as \"id: Id\",\n                    channel.name,\n                    channel.created_at as \"created_at: DateTime\",\n                    channel.created_sequence as \"created_sequence: Sequence\",\n                    deleted.deleted_at as \"deleted_at: DateTime\",\n                    deleted.deleted_sequence as \"deleted_sequence: Sequence\"\n                from channel\n                left join channel_deleted as deleted\n                    using (id)\n                left join message\n                where channel.created_at < $1\n                    and message.id is null\n                    and deleted.id is null\n            ",
-  "describe": {
-    "columns": [
-      {
-        "name": "id: Id",
-        "ordinal": 0,
-        "type_info": "Text"
-      },
-      {
-        "name": "name",
-        "ordinal": 1,
-        "type_info": "Text"
-      },
-      {
-        "name": "created_at: DateTime",
-        "ordinal": 2,
-        "type_info": "Text"
-      },
-      {
-        "name": "created_sequence: Sequence",
-        "ordinal": 3,
-        "type_info": "Integer"
-      },
-      {
-        "name": "deleted_at: DateTime",
-        "ordinal": 4,
-        "type_info": "Text"
-      },
-      {
-        "name": "deleted_sequence: Sequence",
-        "ordinal": 5,
-        "type_info": "Integer"
-      }
-    ],
-    "parameters": {
-      "Right": 1
-    },
-    "nullable": [
-      false,
-      false,
-      false,
-      false,
-      true,
-      true
-    ]
-  },
-  "hash": "771d1afa5961bae86112a51fc629c8edba240926d21fbe03d884e2a48b034336"
-}
diff --git a/.sqlx/query-836a37fa3fbcacbc880c7c9e603b3087a17906db95d14a35034889a253f23418.json b/.sqlx/query-836a37fa3fbcacbc880c7c9e603b3087a17906db95d14a35034889a253f23418.json
index 2ac20b6..4770b7e 100644
--- a/.sqlx/query-836a37fa3fbcacbc880c7c9e603b3087a17906db95d14a35034889a253f23418.json
+++ b/.sqlx/query-836a37fa3fbcacbc880c7c9e603b3087a17906db95d14a35034889a253f23418.json
@@ -43,7 +43,7 @@
       false,
       false,
       false,
-      false
+      true
     ]
   },
   "hash": "836a37fa3fbcacbc880c7c9e603b3087a17906db95d14a35034889a253f23418"
diff --git a/.sqlx/query-8d9b83fb53e4191ee58b68e65e101d0941f2a266679209f793fd27466b9e3719.json b/.sqlx/query-8d9b83fb53e4191ee58b68e65e101d0941f2a266679209f793fd27466b9e3719.json
index c09bdc6..4d28006 100644
--- a/.sqlx/query-8d9b83fb53e4191ee58b68e65e101d0941f2a266679209f793fd27466b9e3719.json
+++ b/.sqlx/query-8d9b83fb53e4191ee58b68e65e101d0941f2a266679209f793fd27466b9e3719.json
@@ -39,7 +39,7 @@
     },
     "nullable": [
       false,
-      false,
+      true,
       false,
       false,
       true,
diff --git a/.sqlx/query-8e816ccef8ecee937d88ff31ca611395405093dbbed738579d715b2d623921be.json b/.sqlx/query-8e816ccef8ecee937d88ff31ca611395405093dbbed738579d715b2d623921be.json
new file mode 100644
index 0000000..5a038ac
--- /dev/null
+++ b/.sqlx/query-8e816ccef8ecee937d88ff31ca611395405093dbbed738579d715b2d623921be.json
@@ -0,0 +1,62 @@
+{
+  "db_name": "SQLite",
+  "query": "\n                select\n                    message.channel as \"channel: channel::Id\",\n                    message.sender as \"sender: login::Id\",\n                    id as \"id: Id\",\n                    message.body,\n                    message.sent_at as \"sent_at: DateTime\",\n                    message.sent_sequence as \"sent_sequence: Sequence\",\n                    deleted.deleted_at as \"deleted_at?: DateTime\",\n                    deleted.deleted_sequence as \"deleted_sequence?: Sequence\"\n                from message\n                left join message_deleted as deleted\n                    using (id)\n                where id = $1\n            ",
+  "describe": {
+    "columns": [
+      {
+        "name": "channel: channel::Id",
+        "ordinal": 0,
+        "type_info": "Text"
+      },
+      {
+        "name": "sender: login::Id",
+        "ordinal": 1,
+        "type_info": "Text"
+      },
+      {
+        "name": "id: Id",
+        "ordinal": 2,
+        "type_info": "Text"
+      },
+      {
+        "name": "body",
+        "ordinal": 3,
+        "type_info": "Text"
+      },
+      {
+        "name": "sent_at: DateTime",
+        "ordinal": 4,
+        "type_info": "Text"
+      },
+      {
+        "name": "sent_sequence: Sequence",
+        "ordinal": 5,
+        "type_info": "Integer"
+      },
+      {
+        "name": "deleted_at?: DateTime",
+        "ordinal": 6,
+        "type_info": "Text"
+      },
+      {
+        "name": "deleted_sequence?: Sequence",
+        "ordinal": 7,
+        "type_info": "Integer"
+      }
+    ],
+    "parameters": {
+      "Right": 1
+    },
+    "nullable": [
+      false,
+      false,
+      false,
+      true,
+      false,
+      false,
+      false,
+      false
+    ]
+  },
+  "hash": "8e816ccef8ecee937d88ff31ca611395405093dbbed738579d715b2d623921be"
+}
diff --git a/.sqlx/query-9da5b746de5d481eeb0755283b4094fa9dfa65dc95991f689355889606ab1c46.json b/.sqlx/query-9da5b746de5d481eeb0755283b4094fa9dfa65dc95991f689355889606ab1c46.json
new file mode 100644
index 0000000..56a8498
--- /dev/null
+++ b/.sqlx/query-9da5b746de5d481eeb0755283b4094fa9dfa65dc95991f689355889606ab1c46.json
@@ -0,0 +1,38 @@
+{
+  "db_name": "SQLite",
+  "query": "\n                insert\n                into channel (id, name, created_at, created_sequence)\n                values ($1, $2, $3, $4)\n                returning\n                    id as \"id: Id\",\n                    name as \"name!\", -- known non-null as we just set it\n                    created_at as \"created_at: DateTime\",\n                    created_sequence as \"created_sequence: Sequence\"\n            ",
+  "describe": {
+    "columns": [
+      {
+        "name": "id: Id",
+        "ordinal": 0,
+        "type_info": "Text"
+      },
+      {
+        "name": "name!",
+        "ordinal": 1,
+        "type_info": "Text"
+      },
+      {
+        "name": "created_at: DateTime",
+        "ordinal": 2,
+        "type_info": "Text"
+      },
+      {
+        "name": "created_sequence: Sequence",
+        "ordinal": 3,
+        "type_info": "Integer"
+      }
+    ],
+    "parameters": {
+      "Right": 4
+    },
+    "nullable": [
+      false,
+      true,
+      false,
+      false
+    ]
+  },
+  "hash": "9da5b746de5d481eeb0755283b4094fa9dfa65dc95991f689355889606ab1c46"
+}
diff --git a/.sqlx/query-bddc3b0d75f6048c36630db3abb8945a49ce18fb715d249bc9d93fc7d10e817d.json b/.sqlx/query-bddc3b0d75f6048c36630db3abb8945a49ce18fb715d249bc9d93fc7d10e817d.json
index f454fbb..abc1851 100644
--- a/.sqlx/query-bddc3b0d75f6048c36630db3abb8945a49ce18fb715d249bc9d93fc7d10e817d.json
+++ b/.sqlx/query-bddc3b0d75f6048c36630db3abb8945a49ce18fb715d249bc9d93fc7d10e817d.json
@@ -51,7 +51,7 @@
       false,
       false,
       false,
-      false,
+      true,
       false,
       false,
       true,
diff --git a/.sqlx/query-c83af5b462071927d05664bb69067dd15b2e7602aafcfaf04aa1f41e118a1f87.json b/.sqlx/query-c83af5b462071927d05664bb69067dd15b2e7602aafcfaf04aa1f41e118a1f87.json
new file mode 100644
index 0000000..01bb071
--- /dev/null
+++ b/.sqlx/query-c83af5b462071927d05664bb69067dd15b2e7602aafcfaf04aa1f41e118a1f87.json
@@ -0,0 +1,20 @@
+{
+  "db_name": "SQLite",
+  "query": "\n                update channel\n                set name = null\n                where id = $1\n                returning 1 as \"updated: bool\"\n            ",
+  "describe": {
+    "columns": [
+      {
+        "name": "updated: bool",
+        "ordinal": 0,
+        "type_info": "Null"
+      }
+    ],
+    "parameters": {
+      "Right": 1
+    },
+    "nullable": [
+      null
+    ]
+  },
+  "hash": "c83af5b462071927d05664bb69067dd15b2e7602aafcfaf04aa1f41e118a1f87"
+}
diff --git a/.sqlx/query-c90cbe4c836adf7f104b7b6a6a72a129f9484b4d9b9e62e7c546d965cdde721a.json b/.sqlx/query-c90cbe4c836adf7f104b7b6a6a72a129f9484b4d9b9e62e7c546d965cdde721a.json
deleted file mode 100644
index eb41cba..0000000
--- a/.sqlx/query-c90cbe4c836adf7f104b7b6a6a72a129f9484b4d9b9e62e7c546d965cdde721a.json
+++ /dev/null
@@ -1,62 +0,0 @@
-{
-  "db_name": "SQLite",
-  "query": "\n                select\n                    id as \"id: Id\",\n                    message.channel as \"channel: channel::Id\",\n                    message.sender as \"sender: login::Id\",\n                    message.sent_at as \"sent_at: DateTime\",\n                    message.sent_sequence as \"sent_sequence: Sequence\",\n                    message.body,\n                    deleted.deleted_at as \"deleted_at: DateTime\",\n                    deleted.deleted_sequence as \"deleted_sequence: Sequence\"\n                from message\n                left join message_deleted as deleted\n                    using (id)\n                where message.sent_at < $1\n                    and deleted.id is null\n            ",
-  "describe": {
-    "columns": [
-      {
-        "name": "id: Id",
-        "ordinal": 0,
-        "type_info": "Text"
-      },
-      {
-        "name": "channel: channel::Id",
-        "ordinal": 1,
-        "type_info": "Text"
-      },
-      {
-        "name": "sender: login::Id",
-        "ordinal": 2,
-        "type_info": "Text"
-      },
-      {
-        "name": "sent_at: DateTime",
-        "ordinal": 3,
-        "type_info": "Text"
-      },
-      {
-        "name": "sent_sequence: Sequence",
-        "ordinal": 4,
-        "type_info": "Integer"
-      },
-      {
-        "name": "body",
-        "ordinal": 5,
-        "type_info": "Text"
-      },
-      {
-        "name": "deleted_at: DateTime",
-        "ordinal": 6,
-        "type_info": "Text"
-      },
-      {
-        "name": "deleted_sequence: Sequence",
-        "ordinal": 7,
-        "type_info": "Integer"
-      }
-    ],
-    "parameters": {
-      "Right": 1
-    },
-    "nullable": [
-      false,
-      false,
-      false,
-      false,
-      false,
-      false,
-      true,
-      true
-    ]
-  },
-  "hash": "c90cbe4c836adf7f104b7b6a6a72a129f9484b4d9b9e62e7c546d965cdde721a"
-}
diff --git a/.sqlx/query-d6feb736b61c645649082e55c7cf413825151e1cda939378984611dc2d9a789a.json b/.sqlx/query-d6feb736b61c645649082e55c7cf413825151e1cda939378984611dc2d9a789a.json
deleted file mode 100644
index f96ceca..0000000
--- a/.sqlx/query-d6feb736b61c645649082e55c7cf413825151e1cda939378984611dc2d9a789a.json
+++ /dev/null
@@ -1,20 +0,0 @@
-{
-  "db_name": "SQLite",
-  "query": "\n                delete from channel_name_reservation\n                where id = $1\n                returning 1 as \"deleted: bool\"\n            ",
-  "describe": {
-    "columns": [
-      {
-        "name": "deleted: bool",
-        "ordinal": 0,
-        "type_info": "Null"
-      }
-    ],
-    "parameters": {
-      "Right": 1
-    },
-    "nullable": [
-      null
-    ]
-  },
-  "hash": "d6feb736b61c645649082e55c7cf413825151e1cda939378984611dc2d9a789a"
-}
diff --git a/.sqlx/query-e2686f26f8646b4cd31beeb2060b9a6d6e0bbcb4cf8d01c48b297e6f0a950ebc.json b/.sqlx/query-e2686f26f8646b4cd31beeb2060b9a6d6e0bbcb4cf8d01c48b297e6f0a950ebc.json
index b8ab769..1883035 100644
--- a/.sqlx/query-e2686f26f8646b4cd31beeb2060b9a6d6e0bbcb4cf8d01c48b297e6f0a950ebc.json
+++ b/.sqlx/query-e2686f26f8646b4cd31beeb2060b9a6d6e0bbcb4cf8d01c48b297e6f0a950ebc.json
@@ -39,7 +39,7 @@
     },
     "nullable": [
       false,
-      false,
+      true,
       false,
       false,
       true,
diff --git a/.sqlx/query-e621d064ba52cd13b196bce32c2cc4f93832948768dcbd1d12abcdbc5089ca8e.json b/.sqlx/query-e621d064ba52cd13b196bce32c2cc4f93832948768dcbd1d12abcdbc5089ca8e.json
deleted file mode 100644
index d56cec0..0000000
--- a/.sqlx/query-e621d064ba52cd13b196bce32c2cc4f93832948768dcbd1d12abcdbc5089ca8e.json
+++ /dev/null
@@ -1,50 +0,0 @@
-{
-  "db_name": "SQLite",
-  "query": "\n                select\n                    id as \"id: Id\",\n                    channel.name,\n                    channel.created_at as \"created_at: DateTime\",\n                    channel.created_sequence as \"created_sequence: Sequence\",\n                    deleted.deleted_at as \"deleted_at: DateTime\",\n                    deleted.deleted_sequence as \"deleted_sequence: Sequence\"\n                from channel\n                left join channel_deleted as deleted\n                    using (id)\n                where id = $1\n            ",
-  "describe": {
-    "columns": [
-      {
-        "name": "id: Id",
-        "ordinal": 0,
-        "type_info": "Text"
-      },
-      {
-        "name": "name",
-        "ordinal": 1,
-        "type_info": "Text"
-      },
-      {
-        "name": "created_at: DateTime",
-        "ordinal": 2,
-        "type_info": "Text"
-      },
-      {
-        "name": "created_sequence: Sequence",
-        "ordinal": 3,
-        "type_info": "Integer"
-      },
-      {
-        "name": "deleted_at: DateTime",
-        "ordinal": 4,
-        "type_info": "Text"
-      },
-      {
-        "name": "deleted_sequence: Sequence",
-        "ordinal": 5,
-        "type_info": "Integer"
-      }
-    ],
-    "parameters": {
-      "Right": 1
-    },
-    "nullable": [
-      false,
-      false,
-      false,
-      false,
-      true,
-      true
-    ]
-  },
-  "hash": "e621d064ba52cd13b196bce32c2cc4f93832948768dcbd1d12abcdbc5089ca8e"
-}
diff --git a/.sqlx/query-e702a0e7ff9a0a9808f8d45294ae908114b03719dc0cb237cec11f807bf757b1.json b/.sqlx/query-e702a0e7ff9a0a9808f8d45294ae908114b03719dc0cb237cec11f807bf757b1.json
index 0b48645..ba35bb9 100644
--- a/.sqlx/query-e702a0e7ff9a0a9808f8d45294ae908114b03719dc0cb237cec11f807bf757b1.json
+++ b/.sqlx/query-e702a0e7ff9a0a9808f8d45294ae908114b03719dc0cb237cec11f807bf757b1.json
@@ -51,7 +51,7 @@
       false,
       false,
       false,
-      false,
+      true,
       false,
       false,
       true,
diff --git a/migrations/20241017005219_retain_deleted.sql b/migrations/20241017005219_retain_deleted.sql
index 6205482..aed87f4 100644
--- a/migrations/20241017005219_retain_deleted.sql
+++ b/migrations/20241017005219_retain_deleted.sql
@@ -9,33 +9,18 @@ create table channel (
 		not null
 		primary key,
 	name text
-		not null,
+		null
+		unique,
 	created_sequence bigint
 		unique
 		not null,
 	created_at text
-		not null,
-	unique (id, name)
+		not null
 );
 
 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
@@ -64,7 +49,7 @@ create table message (
 	sent_at text
 		not null,
 	body text
-		not null
+		null
 );
 
 insert into message (id, channel, sender, sent_sequence, sent_at, body)
diff --git a/src/channel/app.rs b/src/channel/app.rs
index 0409076..75c662d 100644
--- a/src/channel/app.rs
+++ b/src/channel/app.rs
@@ -23,9 +23,9 @@ impl<'a> Channels<'a> {
     pub async fn create(&self, name: &str, created_at: &DateTime) -> Result<Channel, CreateError> {
         let mut tx = self.db.begin().await?;
         let created = tx.sequence().next(created_at).await?;
-        let channel = tx.channels().create(name, &created).await?;
-        tx.channels()
-            .reserve_name(&channel, name)
+        let channel = tx
+            .channels()
+            .create(name, &created)
             .await
             .duplicate(|| CreateError::DuplicateName(name.into()))?;
         tx.commit().await?;
diff --git a/src/channel/repo.rs b/src/channel/repo.rs
index 4b10c54..1cd1c80 100644
--- a/src/channel/repo.rs
+++ b/src/channel/repo.rs
@@ -28,7 +28,7 @@ impl<'c> Channels<'c> {
                 values ($1, $2, $3, $4)
                 returning
                     id as "id: Id",
-                    name,
+                    name as "name!", -- known non-null as we just set it
                     created_at as "created_at: DateTime",
                     created_sequence as "created_sequence: Sequence"
             "#,
@@ -52,22 +52,6 @@ impl<'c> Channels<'c> {
         Ok(channel)
     }
 
-    pub async fn reserve_name(&mut self, channel: &History, name: &str) -> Result<(), sqlx::Error> {
-        let channel = channel.id();
-        sqlx::query!(
-            r#"
-                insert into channel_name_reservation (id, name)
-                values ($1, $2)
-            "#,
-            channel,
-            name,
-        )
-        .execute(&mut *self.0)
-        .await?;
-
-        Ok(())
-    }
-
     pub async fn by_id(&mut self, channel: &Id) -> Result<History, sqlx::Error> {
         let channel = sqlx::query!(
             r#"
@@ -76,8 +60,8 @@ impl<'c> Channels<'c> {
                     channel.name,
                     channel.created_at as "created_at: DateTime",
                     channel.created_sequence as "created_sequence: Sequence",
-                    deleted.deleted_at as "deleted_at: DateTime",
-                    deleted.deleted_sequence as "deleted_sequence: Sequence"
+                    deleted.deleted_at as "deleted_at?: DateTime",
+                    deleted.deleted_sequence as "deleted_sequence?: Sequence"
                 from channel
                 left join channel_deleted as deleted
                     using (id)
@@ -88,7 +72,7 @@ impl<'c> Channels<'c> {
         .map(|row| History {
             channel: Channel {
                 id: row.id,
-                name: row.name,
+                name: row.name.unwrap_or_default(),
                 deleted_at: row.deleted_at,
             },
             created: Instant::new(row.created_at, row.created_sequence),
@@ -121,7 +105,7 @@ impl<'c> Channels<'c> {
         .map(|row| History {
             channel: Channel {
                 id: row.id,
-                name: row.name,
+                name: row.name.unwrap_or_default(),
                 deleted_at: row.deleted_at,
             },
             created: Instant::new(row.created_at, row.created_sequence),
@@ -156,7 +140,7 @@ impl<'c> Channels<'c> {
         .map(|row| History {
             channel: Channel {
                 id: row.id,
-                name: row.name,
+                name: row.name.unwrap_or_default(),
                 deleted_at: row.deleted_at,
             },
             created: Instant::new(row.created_at, row.created_sequence),
@@ -174,17 +158,6 @@ impl<'c> Channels<'c> {
         deleted: &Instant,
     ) -> Result<History, sqlx::Error> {
         let id = channel.id();
-        sqlx::query_scalar!(
-            r#"
-                delete from channel_name_reservation
-                where id = $1
-                returning 1 as "deleted: bool"
-            "#,
-            id,
-        )
-        .fetch_one(&mut *self.0)
-        .await?;
-
         sqlx::query_scalar!(
             r#"
                 insert into channel_deleted (id, deleted_at, deleted_sequence)
@@ -202,10 +175,12 @@ impl<'c> Channels<'c> {
         // retconned to have been the empty string. Someone reading the event stream
         // afterwards, or looking at channels via the API, cannot retrieve the
         // "deleted" channel's information by ignoring the deletion event.
+        //
+        // This also avoids the need for a separate name reservation table to ensure that live channels have unique names, since the `channel` table's name field is unique over non-null values.
         sqlx::query_scalar!(
             r#"
                 update channel
-                set name = ""
+                set name = null
                 where id = $1
                 returning 1 as "updated: bool"
             "#,
@@ -261,8 +236,8 @@ impl<'c> Channels<'c> {
                     channel.name,
                     channel.created_at as "created_at: DateTime",
                     channel.created_sequence as "created_sequence: Sequence",
-                    deleted.deleted_at as "deleted_at: DateTime",
-                    deleted.deleted_sequence as "deleted_sequence: Sequence"
+                    deleted.deleted_at as "deleted_at?: DateTime",
+                    deleted.deleted_sequence as "deleted_sequence?: Sequence"
                 from channel
                 left join channel_deleted as deleted
                     using (id)
@@ -276,7 +251,7 @@ impl<'c> Channels<'c> {
         .map(|row| History {
             channel: Channel {
                 id: row.id,
-                name: row.name,
+                name: row.name.unwrap_or_default(),
                 deleted_at: row.deleted_at,
             },
             created: Instant::new(row.created_at, row.created_sequence),
diff --git a/src/message/repo.rs b/src/message/repo.rs
index 14ff7bf..85a69fc 100644
--- a/src/message/repo.rs
+++ b/src/message/repo.rs
@@ -57,7 +57,7 @@ impl<'c> Messages<'c> {
                 channel: row.channel,
                 sender: row.sender,
                 id: row.id,
-                body: row.body,
+                body: row.body.unwrap_or_default(),
                 deleted_at: None,
             },
             deleted: None,
@@ -95,7 +95,7 @@ impl<'c> Messages<'c> {
                 channel: row.channel,
                 sender: row.sender,
                 id: row.id,
-                body: row.body,
+                body: row.body.unwrap_or_default(),
                 deleted_at: row.deleted_at,
             },
             deleted: Instant::optional(row.deleted_at, row.deleted_sequence),
@@ -132,7 +132,7 @@ impl<'c> Messages<'c> {
                 channel: row.channel,
                 sender: row.sender,
                 id: row.id,
-                body: row.body,
+                body: row.body.unwrap_or_default(),
                 deleted_at: row.deleted_at,
             },
             deleted: Instant::optional(row.deleted_at, row.deleted_sequence),
@@ -153,8 +153,8 @@ impl<'c> Messages<'c> {
                     message.body,
                     message.sent_at as "sent_at: DateTime",
                     message.sent_sequence as "sent_sequence: Sequence",
-                    deleted.deleted_at as "deleted_at: DateTime",
-                    deleted.deleted_sequence as "deleted_sequence: Sequence"
+                    deleted.deleted_at as "deleted_at?: DateTime",
+                    deleted.deleted_sequence as "deleted_sequence?: Sequence"
                 from message
                 left join message_deleted as deleted
                     using (id)
@@ -168,7 +168,7 @@ impl<'c> Messages<'c> {
                 channel: row.channel,
                 sender: row.sender,
                 id: row.id,
-                body: row.body,
+                body: row.body.unwrap_or_default(),
                 deleted_at: row.deleted_at,
             },
             deleted: Instant::optional(row.deleted_at, row.deleted_sequence),
@@ -257,8 +257,8 @@ impl<'c> Messages<'c> {
                     message.sent_at as "sent_at: DateTime",
                     message.sent_sequence as "sent_sequence: Sequence",
                     message.body,
-                    deleted.deleted_at as "deleted_at: DateTime",
-                    deleted.deleted_sequence as "deleted_sequence: Sequence"
+                    deleted.deleted_at as "deleted_at?: DateTime",
+                    deleted.deleted_sequence as "deleted_sequence?: Sequence"
                 from message
                 left join message_deleted as deleted
                     using (id)
@@ -273,7 +273,7 @@ impl<'c> Messages<'c> {
                 id: row.id,
                 channel: row.channel,
                 sender: row.sender,
-                body: row.body,
+                body: row.body.unwrap_or_default(),
                 deleted_at: row.deleted_at,
             },
             deleted: Instant::optional(row.deleted_at, row.deleted_sequence),
@@ -309,7 +309,7 @@ impl<'c> Messages<'c> {
                 channel: row.channel,
                 sender: row.sender,
                 id: row.id,
-                body: row.body,
+                body: row.body.unwrap_or_default(),
                 deleted_at: row.deleted_at,
             },
             deleted: Instant::optional(row.deleted_at, row.deleted_sequence),
-- 
cgit v1.2.3

