From e9b472eb0b7e4f832d9df96b674dc8da73b34b94 Mon Sep 17 00:00:00 2001
From: cel 🌸 <cel@bunny.garden>
Date: Sun, 17 Aug 2025 09:15:53 +0100
Subject: feat: new db schema

---
 filamento/migrations/1.sql | 82 ++++++++++++++++++++++++----------------------
 1 file changed, 42 insertions(+), 40 deletions(-)

(limited to 'filamento/migrations/1.sql')

diff --git a/filamento/migrations/1.sql b/filamento/migrations/1.sql
index 502c5a9..ec574fc 100644
--- a/filamento/migrations/1.sql
+++ b/filamento/migrations/1.sql
@@ -1,9 +1,8 @@
 PRAGMA foreign_keys = on;
 
 -- a user jid will never change, only a chat user will change
--- TODO: avatar, nick, etc.
 create table if not exists users(
-    -- TODO: enforce bare jid
+    -- bare jid
     jid text primary key not null,
     nick text,
     avatar text,
@@ -12,28 +11,26 @@ create table if not exists users(
     -- TODO: last_seen
 );
 
--- -- links to messages, jabber users, stores jid history, etc.
--- create table identities(
---     id text primary key not null
--- );
-
--- create table identities_users(
---     id text not null,
---     jid text not null,
---     -- whichever has the newest timestamp is the active one.
---     -- what to do when somebody moves, but then the old jid is used again without having explicitly moved back? create new identity to assign ownership to?
---     -- merging of identities?
---     activated_timestamp not null,
---     foreign key(id) references identities(id),
---     foreign key(jid) references users(jid),
---     primary key(activated timestamp, id, jid)
--- );
-
-create table if not exists resources(
-    bare_jid text not null,
-    resource text not null,
-    foreign key(bare_jid) references users(jid),
-    primary key(bare_jid, resource)
+-- links to messages, jabber users, stores jid history, etc.
+-- every identity requires a user, so imported messages must be assigned to an existing jabber account.
+create table if not exists identities(
+    id text primary key not null,
+    -- each time a user moves, their identity's primary jid is updated, and the new jid is added to the identities log
+    primary_jid text not null unique,
+    foreign key(primary_jid) references users(jid)
+);
+-- this also allows e.g. mucs with imported histories to assign messages temporarily to muc-generated temporary users, then send user move events to assign message ownership once that person joins the new group chat with a jabber account.
+
+-- identities log basically.
+create table if not exists identities_users(
+    id text not null,
+    jid text not null,
+    -- what to do when somebody moves, but then the old jid is used again without having explicitly moved back? create new identity to assign ownership to?
+    -- merging of identities?
+    activated_timestamp not null,
+    foreign key(id) references identities(id),
+    foreign key(jid) references users(jid),
+    primary key(id, jid)
 );
 
 -- enum for subscription state
@@ -71,7 +68,8 @@ create table if not exists chats (
     id text primary key not null,
     have_chatted bool not null,
     correspondent text not null unique,
-    foreign key(correspondent) references users(jid)
+    -- TODO: how many messages to store locally/rely on mam, whether to auto-download/store media too.
+    foreign key(correspondent) references identities(id)
 );
 
 -- enum for subscription state
@@ -92,27 +90,17 @@ create table if not exists messages (
     -- channel_id uuid,
     -- check ((chat_id == null) <> (channel_id == null)),
     -- check ((chat_id == null) or (channel_id == null)),
-    -- user is the current "owner" of the message
     -- TODO: queued messages offline
-    -- TODO: timestamp
     timestamp text not null,
 
-    -- TODO: icky
-    -- the user to show it coming from (not necessarily the original sender)
-    -- from_identity text not null,
-    -- original sender details (only from jabber supported for now)
-    from_jid text not null,
-    -- resource can be null
-    from_resource text,
-    -- check (from_jid != original_sender),
+    -- the identity to show it coming from (not necessarily the original sender)
+    from_identity text not null,
+
+    -- TODO: unread message count, read bool not null,
 
     foreign key(delivery) references delivery(state),
-    -- TODO: from can be either a jid, a moved jid (for when a contact moves, save original sender jid/user but link to new user), or imported (from another service (save details), linked to new user)
-    -- TODO: read bool not null,
     foreign key(chat_id) references chats(id) on delete cascade,
-    -- foreign key(from_identity) references identities(id),
-    foreign key(from_jid) references users(jid),
-    foreign key(from_jid, from_resource) references resources(bare_jid, resource)
+    foreign key(from_identity) references identities(id)
 );
 
 -- enum for subscription state
@@ -139,3 +127,17 @@ create table if not exists capability_hash_nodes (
 );
 
 insert into capability_hash_nodes ( node, capabilities ) values ('https://bunny.garden/filamento#mSavc/SLnHm8zazs5RlcbD/iXoc=', 'aHR0cDovL2phYmJlci5vcmcvcHJvdG9jb2wvY2Fwcx9odHRwOi8vamFiYmVyLm9yZy9wcm90b2NvbC9kaXNjbyNpbmZvH2h0dHA6Ly9qYWJiZXIub3JnL3Byb3RvY29sL2Rpc2NvI2l0ZW1zH2h0dHA6Ly9qYWJiZXIub3JnL3Byb3RvY29sL25pY2sfaHR0cDovL2phYmJlci5vcmcvcHJvdG9jb2wvbmljaytub3RpZnkfHGNsaWVudB9wYx8fZmlsYW1lbnRvIDAuMS4wHx4cHA==') on conflict do nothing;
+
+-- TODO: later maybe just a full stanza log period?
+create table if not exists message_stanzas (
+    id integer primary key not null,
+    raw text not null,
+    timestamp text not null,
+    -- should be 1 to many as the same original stanza could technically have multiple envelopes...(e.g. message sync, mam), also makes making dangling stanzas not possible easier when modeling the relationship this way.
+    envelope_of integer,
+    linked_message text,
+    -- check that stanza is either linked to another stanza, or a message (no dangling stanzas)
+    check ((envelope_of == null) <> (linked_message == null)),
+    foreign key(linked_message) references messages(id),
+    foreign key(envelope_of) references message_stanzas(id)
+)
-- 
cgit 

