PRAGMA foreign_keys = on;
-- a user jid will never change, only a chat user will change
-- TODO: avatar, nick, etc.
create table users(
-- TODO: enforce bare jid
jid text primary key not null,
-- can receive presence status from non-contacts
cached_status_message text
-- 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 resources(
bare_jid text not null,
resource text not null,
foreign key(bare_jid) references users(jid),
primary key(bare_jid, resource)
);
-- enum for subscription state
create table subscription(
state text primary key not null
);
insert into subscription ( state ) values ('none'), ('pending-out'), ('pending-in'), ('pending-in-pending-out'), ('only-out'), ('only-in'), ('out-pending-in'), ('in-pending-out'), ('buddy');
-- a roster contains users, with client-set nickname
CREATE TABLE roster(
user_jid text primary key not null,
name TEXT,
subscription text not null,
foreign key(subscription) references subscription(state),
foreign key(user_jid) references users(jid)
);
create table groups(
group_name text primary key not null
);
create table groups_roster(
group_name text not null,
contact_jid text not null,
foreign key(group_name) references groups(group_name),
foreign key(contact_jid) references roster(user_jid) on delete cascade,
primary key(group_name, contact_jid)
);
-- chat includes reference to user jid chat is with
-- specifically for dms, groups should be different
-- can send chat message to user (creating a new chat if not already exists)
create table chats (
id text primary key not null,
correspondent text not null unique,
foreign key(correspondent) references users(jid)
);
-- messages include reference to chat they are in, and who sent them.
create table messages (
id text primary key not null,
body text,
chat_id text not null,
-- TODO: channel stuff
-- 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),
-- 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)
);
-- enum for subscription state
create table show (
state text primary key not null
);
insert into show ( state ) values ('away'), ('chat'), ('do-not-disturb'), ('extended-away');
create table cached_status (
id integer primary key not null,
show text,
message text,
foreign key(show) references show(state)
);
insert into cached_status (id) values (0);
