Split the `user` table into an authentication portion and a chat portion.

We'll be building separate entities around this in future commits, to better separate the authentication data (non-synchronized and indeed "not public") from the chat data (synchronized and public).

1 files changed, 195 insertions, 0 deletions

diff --git a/migrations/20250822213537_split_user.sql b/migrations/20250822213537_split_user.sql
new file mode 100644
index 0000000..1dbbad6
--- /dev/null
+++ b/migrations/20250822213537_split_user.sql
@@ -0,0 +1,195 @@
+-- First, migrate the table we're actually interested in - `user`. This splits it into two parts:
+--
+-- * login, which is concerned with authentication data (and is non-synchronized,
+--   though at the DB that doesn't matter much), and
+-- * user, which is concerned with chat data (and is synchronized, ditto).
+
+alter table user
+    rename to old_user;
+
+create table login (
+    id text
+        not null
+        primary key,
+    display_name text
+        not null,
+    canonical_name text
+        not null
+        unique,
+    password text
+        not null
+);
+
+insert into
+    login (id, display_name, canonical_name, password)
+select
+    id,
+    display_name,
+    canonical_name,
+    password_hash as password
+from
+    old_user;
+
+create table user (
+    id text
+        not null
+        primary key
+        references login (id),
+    created_at text
+        not null,
+    created_sequence bigint
+        not null
+        unique
+);
+
+insert into
+    user (id, created_at, created_sequence)
+select
+    id,
+    created_at,
+    created_sequence
+from
+    old_user;
+
+-- Now, recreate the entire rest of the owl. Everything that referenced the original `user` table _except tokens_
+-- references the user table. Tokens, which are authentication data, reference the login (authn) table.
+
+alter table token
+    rename to old_token;
+
+create table token (
+    id text
+        not null
+        primary key,
+    secret text
+        not null
+        unique,
+    login text
+        not null
+        references login (id),
+    issued_at text
+        not null,
+    last_used_at text
+        not null
+);
+
+insert into
+    token (id, secret, login, issued_at, last_used_at)
+select
+    id,
+    secret,
+    user as login,
+    issued_at,
+    last_used_at
+from
+    old_token;
+
+alter table invite
+    rename to old_invite;
+
+create table invite (
+    id text
+        primary key
+        not null,
+    issuer text
+        not null
+        references user (id),
+    issued_at text
+        not null
+);
+
+insert into
+    invite (id, issuer, issued_at)
+select
+    id,
+    issuer,
+    issued_at
+from
+    old_invite;
+
+alter table message
+    rename to old_message;
+
+create table message (
+    id text
+        not null
+        primary key,
+    conversation text
+        not null
+        references conversation (id),
+    sender text
+        not null
+        references user (id),
+    body text
+        null,
+    sent_at text
+        not null,
+    sent_sequence bigint
+        unique
+        not null,
+    last_sequence bigint
+        not null
+);
+
+insert into
+    message (id, conversation, sender, body, sent_at, sent_sequence, last_sequence)
+select
+    id,
+    conversation,
+    sender,
+    body,
+    sent_at,
+    sent_sequence,
+    last_sequence
+from
+    old_message;
+
+alter table message_deleted
+    rename to old_message_deleted;
+
+create table message_deleted (
+    id text
+        not null
+        primary key
+        references message (id),
+    deleted_at text
+        not null,
+    deleted_sequence bigint
+        unique
+        not null
+);
+
+insert into
+    message_deleted (id, deleted_at, deleted_sequence)
+select
+    id,
+    deleted_at,
+    deleted_sequence
+from
+    message_deleted;
+
+drop table old_message_deleted;
+drop table old_message;
+drop table old_invite;
+drop table old_token;
+drop table old_user;
+
+create index token_issued_at
+    on token (issued_at);
+create index token_last_used_at
+    on token (last_used_at);
+create index token_login
+    on token (login);
+
+create index invite_issued_at
+    on invite (issued_at);
+
+create index message_sent_at
+    on message (sent_at);
+create index message_conversation
+    on message (conversation);
+create index message_last_sequence
+    on message (last_sequence);
+
+create index message_deleted_deleted_at
+    on message_deleted (deleted_at);