diff options
Diffstat (limited to 'filamento/migrations/20240113011930_luz.sql')
| -rw-r--r-- | filamento/migrations/20240113011930_luz.sql | 12 |
1 files changed, 12 insertions, 0 deletions
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, |