132 lines
3.6 KiB
SQL
132 lines
3.6 KiB
SQL
-- 完整业务表（T0.1 的 0001 仅为 SELECT 1 占位且可能已记入 schema_migrations，故放在 0002）。
|
|
CREATE TABLE endpoints (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL DEFAULT '',
|
|
remark TEXT NOT NULL DEFAULT '',
|
|
source TEXT NOT NULL DEFAULT 'admin',
|
|
login_hash TEXT NOT NULL,
|
|
talk_hash TEXT,
|
|
talk_version INTEGER NOT NULL DEFAULT 0,
|
|
default_delay_ms INTEGER NOT NULL DEFAULT 0,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
created_at INTEGER NOT NULL,
|
|
online_since INTEGER,
|
|
offline_since INTEGER,
|
|
session_hash TEXT,
|
|
session_issued_at INTEGER,
|
|
session_used_at INTEGER
|
|
);
|
|
|
|
CREATE TABLE settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE talk_grants (
|
|
sender_id TEXT NOT NULL,
|
|
target_id TEXT NOT NULL,
|
|
target_talk_version INTEGER NOT NULL,
|
|
kind TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
PRIMARY KEY (sender_id, target_id)
|
|
);
|
|
|
|
CREATE TABLE groups (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
owner_id TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE group_members (
|
|
group_id TEXT NOT NULL,
|
|
endpoint_id TEXT NOT NULL,
|
|
joined_at INTEGER NOT NULL,
|
|
PRIMARY KEY (group_id, endpoint_id)
|
|
);
|
|
|
|
CREATE TABLE messages (
|
|
seq INTEGER PRIMARY KEY,
|
|
id TEXT NOT NULL,
|
|
sender_id TEXT NOT NULL,
|
|
dest_kind TEXT NOT NULL,
|
|
dest_id TEXT NOT NULL,
|
|
meta TEXT NOT NULL DEFAULT '{}',
|
|
content_type TEXT NOT NULL,
|
|
body_enc TEXT NOT NULL,
|
|
send_at INTEGER NOT NULL,
|
|
keep INTEGER NOT NULL,
|
|
ttl_seconds INTEGER NOT NULL DEFAULT 0,
|
|
receipt INTEGER NOT NULL,
|
|
state TEXT NOT NULL,
|
|
reason TEXT NOT NULL DEFAULT '',
|
|
created_at INTEGER NOT NULL,
|
|
UNIQUE (sender_id, id)
|
|
);
|
|
|
|
CREATE TABLE message_bodies (
|
|
seq INTEGER PRIMARY KEY REFERENCES messages(seq) ON DELETE CASCADE,
|
|
body BLOB NOT NULL
|
|
);
|
|
|
|
CREATE TABLE deliveries (
|
|
seq INTEGER NOT NULL REFERENCES messages(seq) ON DELETE CASCADE,
|
|
endpoint_id TEXT NOT NULL,
|
|
send_at INTEGER NOT NULL,
|
|
keep INTEGER NOT NULL,
|
|
state TEXT NOT NULL,
|
|
reason TEXT NOT NULL DEFAULT '',
|
|
expire_at INTEGER,
|
|
pushed_conn TEXT,
|
|
pushed_at INTEGER,
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
|
updated_at INTEGER NOT NULL,
|
|
PRIMARY KEY (seq, endpoint_id)
|
|
);
|
|
|
|
CREATE TABLE receipts (
|
|
receipt_id INTEGER PRIMARY KEY,
|
|
sender_id TEXT NOT NULL,
|
|
msg_id TEXT NOT NULL,
|
|
endpoint_id TEXT NOT NULL DEFAULT '',
|
|
state TEXT NOT NULL,
|
|
reason TEXT NOT NULL DEFAULT '',
|
|
created_at INTEGER NOT NULL,
|
|
acked INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE send_keys (
|
|
sender_id TEXT NOT NULL,
|
|
msg_id TEXT NOT NULL,
|
|
request_sha256 BLOB NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
PRIMARY KEY (sender_id, msg_id)
|
|
);
|
|
|
|
CREATE TABLE admin_sessions (
|
|
token_hash TEXT PRIMARY KEY,
|
|
created_at INTEGER NOT NULL,
|
|
expires_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE api_tokens (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
created_at INTEGER NOT NULL,
|
|
last_used_at INTEGER
|
|
);
|
|
|
|
CREATE INDEX idx_messages_due ON messages(state, send_at);
|
|
CREATE INDEX idx_messages_dest ON messages(dest_kind, dest_id, state);
|
|
CREATE INDEX idx_messages_created ON messages(created_at);
|
|
CREATE INDEX idx_messages_sender ON messages(sender_id, created_at);
|
|
CREATE INDEX idx_messages_sender_state ON messages(sender_id, state);
|
|
CREATE INDEX idx_deliveries_outbox ON deliveries(endpoint_id, state, send_at, seq);
|
|
CREATE INDEX idx_deliveries_expire ON deliveries(state, expire_at);
|
|
CREATE INDEX idx_receipts_outbox ON receipts(sender_id, acked, receipt_id);
|
|
CREATE INDEX idx_talk_grants_target ON talk_grants(target_id);
|
|
CREATE INDEX idx_group_members_endpoint ON group_members(endpoint_id);
|