f42ecf5c5b
- db: cleanup_old_messages(days) purges messages older than N days in batches; recipients/reads/deliveries/reactions follow via ON DELETE CASCADE. Returns attachment file_ids no longer referenced by any surviving message (forwarded copies keep their files) and removes their image_uploads rows - server: MESSAGE_RETENTION_DAYS env var (default 0 = keep forever); hourly cleanup deletes expired messages and securely removes orphaned attachment blobs from the upload dir - schema: email_visible now defaults to 0 — previously any logged-in user who knew a UUID could read another user's email via get_profile - migrations: SQL script to apply the new default and reset the flag on existing databases (run manually, see file header) - docker-compose: document MESSAGE_RETENTION_DAYS Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
190 lines
7.6 KiB
SQL
190 lines
7.6 KiB
SQL
CREATE DATABASE IF NOT EXISTS encrypted_chat
|
|
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
USE encrypted_chat;
|
|
|
|
-- Users: identity_key is Ed25519 (32B), rsa_public_key for login challenge only
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id CHAR(36) NOT NULL PRIMARY KEY,
|
|
username VARCHAR(255) NOT NULL,
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
rsa_public_key TEXT NOT NULL,
|
|
identity_key BLOB NOT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Devices: each user can have multiple devices
|
|
CREATE TABLE IF NOT EXISTS devices (
|
|
id CHAR(36) NOT NULL PRIMARY KEY,
|
|
user_id CHAR(36) NOT NULL,
|
|
device_name VARCHAR(255) DEFAULT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
last_seen_at DATETIME DEFAULT NULL,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
INDEX idx_devices_user (user_id)
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Signed Pre-Keys (X25519, signed by Ed25519 identity key) — per device
|
|
CREATE TABLE IF NOT EXISTS signed_prekeys (
|
|
id CHAR(36) NOT NULL PRIMARY KEY,
|
|
user_id CHAR(36) NOT NULL,
|
|
device_id CHAR(36) DEFAULT NULL,
|
|
public_key BLOB NOT NULL,
|
|
signature BLOB NOT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
INDEX idx_spk_user_device (user_id, device_id)
|
|
) ENGINE=InnoDB;
|
|
|
|
-- One-Time Pre-Keys (consumed on use) — per device
|
|
CREATE TABLE IF NOT EXISTS one_time_prekeys (
|
|
id CHAR(36) NOT NULL PRIMARY KEY,
|
|
user_id CHAR(36) NOT NULL,
|
|
device_id CHAR(36) DEFAULT NULL,
|
|
public_key BLOB NOT NULL,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
INDEX idx_opk_user_device (user_id, device_id)
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Conversations
|
|
CREATE TABLE IF NOT EXISTS conversations (
|
|
id CHAR(36) NOT NULL PRIMARY KEY,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
name VARCHAR(255) DEFAULT NULL,
|
|
created_by CHAR(36) DEFAULT NULL,
|
|
avatar_file VARCHAR(255) DEFAULT NULL
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS conversation_members (
|
|
conversation_id CHAR(36) NOT NULL,
|
|
user_id CHAR(36) NOT NULL,
|
|
joined_at DATETIME NULL,
|
|
PRIMARY KEY (conversation_id, user_id),
|
|
FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
INDEX idx_cm_user (user_id)
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Group invitations (pending invitations to join a group)
|
|
CREATE TABLE IF NOT EXISTS group_invitations (
|
|
id CHAR(36) NOT NULL PRIMARY KEY,
|
|
conversation_id CHAR(36) NOT NULL,
|
|
user_id CHAR(36) NOT NULL,
|
|
invited_by CHAR(36) NOT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uq_conv_user (conversation_id, user_id),
|
|
FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (invited_by) REFERENCES users(id) ON DELETE CASCADE,
|
|
INDEX idx_inv_user (user_id)
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Messages: per-recipient ciphertext (Double Ratchet = each recipient has different ciphertext)
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id CHAR(36) NOT NULL PRIMARY KEY,
|
|
conversation_id CHAR(36) NOT NULL,
|
|
sender_id CHAR(36) NOT NULL,
|
|
sender_device_id CHAR(36) DEFAULT NULL,
|
|
ratchet_header BLOB NOT NULL,
|
|
x3dh_header BLOB DEFAULT NULL,
|
|
sender_chain_id BLOB DEFAULT NULL,
|
|
sender_chain_n INT DEFAULT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at DATETIME DEFAULT NULL,
|
|
image_file_id CHAR(36) DEFAULT NULL,
|
|
pinned_at DATETIME DEFAULT NULL,
|
|
pinned_by CHAR(36) DEFAULT NULL,
|
|
FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
INDEX idx_messages_conv_created (conversation_id, created_at),
|
|
INDEX idx_messages_deleted (conversation_id, deleted_at),
|
|
INDEX idx_messages_pinned (conversation_id, pinned_at)
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Per-recipient encrypted content — per device
|
|
-- device_id '00000000-0000-0000-0000-000000000000' = self-encrypted / legacy
|
|
CREATE TABLE IF NOT EXISTS message_recipients (
|
|
message_id CHAR(36) NOT NULL,
|
|
user_id CHAR(36) NOT NULL,
|
|
device_id CHAR(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
|
|
encrypted_content BLOB NOT NULL,
|
|
nonce BLOB NOT NULL,
|
|
ratchet_header BLOB DEFAULT NULL,
|
|
x3dh_header BLOB DEFAULT NULL,
|
|
PRIMARY KEY (message_id, user_id, device_id),
|
|
FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Sender Keys for groups (distributed via pairwise ratchet) — per device
|
|
CREATE TABLE IF NOT EXISTS group_sender_keys (
|
|
conversation_id CHAR(36) NOT NULL,
|
|
sender_id CHAR(36) NOT NULL,
|
|
device_id CHAR(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
|
|
chain_id BLOB NOT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (conversation_id, sender_id, device_id),
|
|
FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Read receipts
|
|
CREATE TABLE IF NOT EXISTS message_reads (
|
|
message_id CHAR(36) NOT NULL,
|
|
user_id CHAR(36) NOT NULL,
|
|
read_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (message_id, user_id),
|
|
FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
INDEX idx_reads_user (user_id),
|
|
INDEX idx_reads_read_at (read_at)
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Delivery receipts
|
|
CREATE TABLE IF NOT EXISTS message_deliveries (
|
|
message_id CHAR(36) NOT NULL,
|
|
user_id CHAR(36) NOT NULL,
|
|
delivered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (message_id, user_id),
|
|
FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
-- User profiles
|
|
CREATE TABLE IF NOT EXISTS user_profiles (
|
|
user_id CHAR(36) NOT NULL PRIMARY KEY,
|
|
phone VARCHAR(50) DEFAULT NULL,
|
|
phone_visible TINYINT(1) NOT NULL DEFAULT 0,
|
|
email_visible TINYINT(1) NOT NULL DEFAULT 0,
|
|
location VARCHAR(255) DEFAULT NULL,
|
|
location_visible TINYINT(1) NOT NULL DEFAULT 0,
|
|
avatar_file VARCHAR(255) DEFAULT NULL,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Message reactions (emoji reactions on messages)
|
|
CREATE TABLE IF NOT EXISTS message_reactions (
|
|
id CHAR(36) NOT NULL PRIMARY KEY,
|
|
message_id CHAR(36) NOT NULL,
|
|
user_id CHAR(36) NOT NULL,
|
|
reaction VARCHAR(32) NOT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uq_reaction (message_id, user_id),
|
|
FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
INDEX idx_reactions_created_at (created_at)
|
|
) ENGINE=InnoDB;
|
|
|
|
-- Image uploads
|
|
CREATE TABLE IF NOT EXISTS image_uploads (
|
|
file_id CHAR(36) NOT NULL PRIMARY KEY,
|
|
conversation_id CHAR(36) NOT NULL,
|
|
uploader_id CHAR(36) NOT NULL,
|
|
file_size BIGINT NOT NULL DEFAULT 0,
|
|
completed BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (uploader_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|