this repo has no description
rockorager.dev
1CREATE TABLE IF NOT EXISTS users (
2 id INTEGER PRIMARY KEY,
3 did TEXT NOT NULL UNIQUE,
4 nick TEXT NOT NULL,
5 modes INTEGER NOT NULL DEFAULT 0
6);
7
8-- nick index
9CREATE INDEX IF NOT EXISTS idx_users_nick ON users(nick);
10
11CREATE TABLE IF NOT EXISTS channels (
12 id INTEGER PRIMARY KEY,
13 name TEXT NOT NULL UNIQUE,
14 topic TEXT
15);
16
17-- channel name index
18CREATE INDEX IF NOT EXISTS idx_channels_name ON channels(name);
19
20CREATE TABLE IF NOT EXISTS messages (
21 id INTEGER PRIMARY KEY,
22 uuid TEXT NOT NULL,
23 timestamp_ms INTEGER NOT NULL,
24 sender_id INTEGER NOT NULL, -- ID of the sender of the message
25 sender_nick TEXT NOT NULL, -- Who sent the message. This is the nickname of the sender at the time of sending the message
26 recipient_id INTEGER NOT NULL, -- Channel the message was sent to
27 recipient_type INTEGER NOT NULL, -- 0 = channel, 1 = user. This is required to resolve the target foreign key
28 message TEXT, -- Full text of the message, as received by the server
29 FOREIGN KEY (sender_id) REFERENCES users(id)
30);
31
32-- Create an index for the target id and kind
33CREATE INDEX IF NOT EXISTS idx_messages_recipient ON messages(recipient_id, recipient_type);
34
35CREATE TABLE IF NOT EXISTS read_marker (
36 id INTEGER PRIMARY KEY,
37 user_id INTEGER NOT NULL,
38 target_id INTEGER NOT NULL,
39 target_kind INTEGER NOT NULL, -- 0 = channel, 1 = user
40 timestamp_ms INTEGER NOT NULL,
41 FOREIGN KEY (user_id) REFERENCES users(id),
42 UNIQUE (user_id, target_id, target_kind)
43);
44
45-- Create an index for the foreign keys
46CREATE INDEX IF NOT EXISTS idx_read_marker_user_id ON read_marker(user_id);
47CREATE INDEX IF NOT EXISTS idx_read_marker_target_id ON read_marker(target_id, target_kind);
48
49-- Table to track channel membership
50CREATE TABLE IF NOT EXISTS channel_membership (
51 id INTEGER PRIMARY KEY,
52 user_id INTEGER NOT NULL,
53 channel_id INTEGER NOT NULL,
54 privileges INTEGER NOT NULL DEFAULT 0,
55 FOREIGN KEY (user_id) REFERENCES users(id),
56 FOREIGN KEY (channel_id) REFERENCES channels(id),
57 UNIQUE (user_id, channel_id)
58);
59
60CREATE TABLE IF NOT EXISTS user_tokens (
61 id INTEGER PRIMARY KEY,
62 user_id INTEGER NOT NULL, -- FK to users table
63 password_hash TEXT, -- Hashed app password
64 refresh_token TEXT, -- Refresh token
65 refresh_expiry INTEGER, -- Expiration timestamp
66 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
67);