From 91f1994af940085d5d475a97820900ebbf0eb553 Mon Sep 17 00:00:00 2001
From: cel 🌸 <cel@bunny.garden>
Date: Thu, 3 Apr 2025 03:41:38 +0100
Subject: feat: better message handling, pep publish, xep_0172: nick

---
 filamento/migrations/20240113011930_luz.sql | 12 ++++++++++++
 1 file changed, 12 insertions(+)

(limited to 'filamento/migrations/20240113011930_luz.sql')

diff --git a/filamento/migrations/20240113011930_luz.sql b/filamento/migrations/20240113011930_luz.sql
index 148598b..3b56664 100644
--- a/filamento/migrations/20240113011930_luz.sql
+++ b/filamento/migrations/20240113011930_luz.sql
@@ -5,6 +5,7 @@ PRAGMA foreign_keys = on;
 create table users(
     -- TODO: enforce bare jid
     jid text primary key not null,
+    nick text,
     -- can receive presence status from non-contacts
     cached_status_message text
     -- TODO: last_seen
@@ -67,14 +68,24 @@ create table groups_roster(
 -- can send chat message to user (creating a new chat if not already exists)
 create table chats (
     id text primary key not null,
+    have_chatted bool not null,
     correspondent text not null unique,
     foreign key(correspondent) references users(jid)
 );
 
+-- enum for subscription state
+create table delivery(
+    state text primary key not null
+);
+
+insert into delivery ( state ) values ('sending'), ('written'), ('sent'), ('delivered'), ('read'), ('failed'), ('queued');
+
 -- messages include reference to chat they are in, and who sent them.
 create table messages (
     id text primary key not null,
     body text,
+    -- delivery is nullable as only messages sent by the user are markable
+    delivery text,
     chat_id text not null,
     -- TODO: channel stuff 
     -- channel_id uuid,
@@ -94,6 +105,7 @@ create table messages (
     from_resource text,
     -- check (from_jid != original_sender),
 
+    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,
-- 
cgit 

