mirror of
https://github.com/619dev/PaperPhone.git
synced 2026-05-06 22:12:41 +08:00
388 lines
20 KiB
SQL
388 lines
20 KiB
SQL
-- PaperPhone IM — Database Schema
|
|
-- MySQL 8.0+ | utf8mb4 | InnoDB
|
|
--
|
|
-- Usage:
|
|
-- Manual : mysql -u root -p paperphone < schema.sql
|
|
-- Docker : mounted into /docker-entrypoint-initdb.d/
|
|
-- Server : auto-executed on startup via index.js / initMomentsTables()
|
|
--
|
|
-- Note: CREATE DATABASE / USE are intentionally omitted.
|
|
-- The target database must already be selected before importing.
|
|
|
|
-- ── Users ─────────────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
username VARCHAR(64) NOT NULL UNIQUE,
|
|
nickname VARCHAR(128) NOT NULL,
|
|
avatar VARCHAR(512) DEFAULT NULL,
|
|
password VARCHAR(255) NOT NULL,
|
|
-- ECDH Identity Key (Curve25519 public key, base64)
|
|
ik_pub TEXT NOT NULL,
|
|
-- Signed PreKey (Curve25519 public, base64)
|
|
spk_pub TEXT NOT NULL,
|
|
spk_sig TEXT NOT NULL,
|
|
-- ML-KEM-768 long-term public key (base64)
|
|
kem_pub TEXT NOT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
last_seen DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
is_online TINYINT(1) NOT NULL DEFAULT 0,
|
|
INDEX idx_username (username)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ── One-Time PreKeys (X3DH OPKs) ─────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS prekeys (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id VARCHAR(36) NOT NULL,
|
|
key_id INT NOT NULL,
|
|
opk_pub TEXT NOT NULL,
|
|
used TINYINT(1) NOT NULL DEFAULT 0,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
INDEX idx_user_unused (user_id, used)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ── Friendships ───────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS friends (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id VARCHAR(36) NOT NULL,
|
|
friend_id VARCHAR(36) NOT NULL,
|
|
status ENUM('pending','accepted','blocked') NOT NULL DEFAULT 'pending',
|
|
auto_delete INT NOT NULL DEFAULT 604800,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uk_pair (user_id, friend_id),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (friend_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Migration: add auto_delete to friends (idempotent)
|
|
SET @f_ad = (SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'friends' AND COLUMN_NAME = 'auto_delete');
|
|
SET @f_sql = IF(@f_ad = 0,
|
|
'ALTER TABLE friends ADD COLUMN auto_delete INT NOT NULL DEFAULT 604800 AFTER status',
|
|
'SELECT 1');
|
|
PREPARE f_stmt FROM @f_sql;
|
|
EXECUTE f_stmt;
|
|
DEALLOCATE PREPARE f_stmt;
|
|
|
|
-- Migration: add message to friends (idempotent) — friend request message (≤512 chars)
|
|
SET @f_msg = (SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'friends' AND COLUMN_NAME = 'message');
|
|
SET @f_msg_sql = IF(@f_msg = 0,
|
|
'ALTER TABLE friends ADD COLUMN message VARCHAR(512) DEFAULT NULL AFTER auto_delete',
|
|
'SELECT 1');
|
|
PREPARE f_msg_stmt FROM @f_msg_sql;
|
|
EXECUTE f_msg_stmt;
|
|
DEALLOCATE PREPARE f_msg_stmt;
|
|
|
|
-- ── Groups ────────────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS `groups` (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
name VARCHAR(128) NOT NULL,
|
|
avatar VARCHAR(512) DEFAULT NULL,
|
|
owner_id VARCHAR(36) NOT NULL,
|
|
notice TEXT DEFAULT NULL,
|
|
auto_delete INT NOT NULL DEFAULT 604800,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (owner_id) REFERENCES users(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Migration: add auto_delete to groups (idempotent)
|
|
SET @g_ad = (SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'groups' AND COLUMN_NAME = 'auto_delete');
|
|
SET @g_sql = IF(@g_ad = 0,
|
|
'ALTER TABLE `groups` ADD COLUMN auto_delete INT NOT NULL DEFAULT 604800 AFTER notice',
|
|
'SELECT 1');
|
|
PREPARE g_stmt FROM @g_sql;
|
|
EXECUTE g_stmt;
|
|
DEALLOCATE PREPARE g_stmt;
|
|
|
|
-- ── Group Members ─────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS group_members (
|
|
group_id VARCHAR(36) NOT NULL,
|
|
user_id VARCHAR(36) NOT NULL,
|
|
role ENUM('owner','admin','member') NOT NULL DEFAULT 'member',
|
|
muted TINYINT(1) NOT NULL DEFAULT 0,
|
|
joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (group_id, user_id),
|
|
FOREIGN KEY (group_id) REFERENCES `groups`(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Migration: add muted column to group_members (idempotent)
|
|
SET @gm_muted = (SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'group_members' AND COLUMN_NAME = 'muted');
|
|
SET @gm_sql = IF(@gm_muted = 0,
|
|
'ALTER TABLE group_members ADD COLUMN muted TINYINT(1) NOT NULL DEFAULT 0 AFTER role',
|
|
'SELECT 1');
|
|
PREPARE gm_stmt FROM @gm_sql;
|
|
EXECUTE gm_stmt;
|
|
DEALLOCATE PREPARE gm_stmt;
|
|
|
|
-- ── Messages ──────────────────────────────────────────────────────────────
|
|
-- Server stores encrypted payloads for offline delivery only.
|
|
-- Once delivered via WebSocket, messages may be pruned.
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
type ENUM('private','group') NOT NULL,
|
|
from_id VARCHAR(36) NOT NULL,
|
|
to_id VARCHAR(36) NOT NULL, -- user_id or group_id
|
|
ciphertext LONGTEXT NOT NULL, -- base64 encrypted payload (for recipient)
|
|
header TEXT DEFAULT NULL, -- ephemeral public key (for E2EE, recipient)
|
|
self_ciphertext LONGTEXT DEFAULT NULL, -- base64 encrypted payload (for sender)
|
|
self_header TEXT DEFAULT NULL, -- ephemeral public key (for E2EE, sender)
|
|
msg_type ENUM('text','image','file','voice','video_call','system','sticker') NOT NULL DEFAULT 'text',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
delivered TINYINT(1) NOT NULL DEFAULT 0,
|
|
read_at DATETIME DEFAULT NULL,
|
|
INDEX idx_to_undelivered (to_id, delivered, created_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Migration: add self_ciphertext / self_header for dual encryption (idempotent)
|
|
SET @db_name = DATABASE();
|
|
SET @tbl = 'messages';
|
|
|
|
SET @col_check = (SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @tbl AND COLUMN_NAME = 'self_ciphertext');
|
|
SET @sql = IF(@col_check = 0,
|
|
'ALTER TABLE messages ADD COLUMN self_ciphertext LONGTEXT DEFAULT NULL AFTER header, ADD COLUMN self_header TEXT DEFAULT NULL AFTER self_ciphertext',
|
|
'SELECT 1');
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Migration: add 'sticker' to msg_type ENUM (idempotent)
|
|
SET @mt_check = (SELECT COLUMN_TYPE FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'messages' AND COLUMN_NAME = 'msg_type');
|
|
SET @mt_sql = IF(@mt_check NOT LIKE '%sticker%',
|
|
"ALTER TABLE messages MODIFY COLUMN msg_type ENUM('text','image','file','voice','video_call','system','sticker') NOT NULL DEFAULT 'text'",
|
|
'SELECT 1');
|
|
PREPARE mt_stmt FROM @mt_sql;
|
|
EXECUTE mt_stmt;
|
|
DEALLOCATE PREPARE mt_stmt;
|
|
|
|
-- Migration: add 'video' to msg_type ENUM (idempotent)
|
|
SET @mt_vid = (SELECT COLUMN_TYPE FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'messages' AND COLUMN_NAME = 'msg_type');
|
|
SET @mt_vid_sql = IF(@mt_vid NOT LIKE '%video\'%',
|
|
"ALTER TABLE messages MODIFY COLUMN msg_type ENUM('text','image','file','voice','video_call','system','sticker','video') NOT NULL DEFAULT 'text'",
|
|
'SELECT 1');
|
|
PREPARE mt_vid_stmt FROM @mt_vid_sql;
|
|
EXECUTE mt_vid_stmt;
|
|
DEALLOCATE PREPARE mt_vid_stmt;
|
|
|
|
-- ── Moments (朋友圈) ──────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS moments (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id VARCHAR(36) NOT NULL,
|
|
text_content VARCHAR(1024) NOT NULL DEFAULT '',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_user_id (user_id),
|
|
INDEX idx_created_at (created_at),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ── Moment Images ─────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS moment_images (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
moment_id BIGINT UNSIGNED NOT NULL,
|
|
url TEXT NOT NULL,
|
|
sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0,
|
|
INDEX idx_moment_id (moment_id),
|
|
FOREIGN KEY (moment_id) REFERENCES moments(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ── Moment Videos ─────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS moment_videos (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
moment_id BIGINT UNSIGNED NOT NULL,
|
|
url TEXT NOT NULL,
|
|
thumbnail TEXT DEFAULT NULL,
|
|
duration SMALLINT UNSIGNED NOT NULL DEFAULT 0,
|
|
INDEX idx_moment_id (moment_id),
|
|
FOREIGN KEY (moment_id) REFERENCES moments(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ── Moment Likes ──────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS moment_likes (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
moment_id BIGINT UNSIGNED NOT NULL,
|
|
user_id VARCHAR(36) NOT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uniq_like (moment_id, user_id),
|
|
FOREIGN KEY (moment_id) REFERENCES moments(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ── Moment Comments ───────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS moment_comments (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
moment_id BIGINT UNSIGNED NOT NULL,
|
|
user_id VARCHAR(36) NOT NULL,
|
|
text_content VARCHAR(512) NOT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_moment_id (moment_id),
|
|
FOREIGN KEY (moment_id) REFERENCES moments(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ── Push Subscriptions (Web Push / VAPID) ─────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS push_subscriptions (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id VARCHAR(36) NOT NULL,
|
|
endpoint TEXT NOT NULL,
|
|
p256dh TEXT NOT NULL,
|
|
auth TEXT NOT NULL,
|
|
user_agent VARCHAR(255) DEFAULT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uk_user_endpoint (user_id, endpoint(512)),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
INDEX idx_push_user (user_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ── OneSignal Players (Median.co native push) ────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS onesignal_players (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id VARCHAR(36) NOT NULL,
|
|
player_id VARCHAR(64) NOT NULL,
|
|
platform VARCHAR(16) DEFAULT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uk_player (player_id),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
INDEX idx_os_user (user_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ── Sessions (Login Device Tracking) ─────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
user_id VARCHAR(36) NOT NULL,
|
|
device_name VARCHAR(128) DEFAULT NULL,
|
|
device_type VARCHAR(16) DEFAULT NULL,
|
|
os VARCHAR(64) DEFAULT NULL,
|
|
browser VARCHAR(64) DEFAULT NULL,
|
|
ip_address VARCHAR(45) DEFAULT NULL,
|
|
last_active DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
revoked TINYINT(1) NOT NULL DEFAULT 0,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
INDEX idx_sess_user (user_id, revoked)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ── Friend Tags ──────────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS friend_tags (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id VARCHAR(36) NOT NULL,
|
|
name VARCHAR(32) NOT NULL,
|
|
color VARCHAR(7) DEFAULT '#2196F3',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uk_user_tag (user_id, name),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
INDEX idx_ft_user (user_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ── Friend Tag Assignments (many-to-many) ────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS friend_tag_assignments (
|
|
tag_id BIGINT UNSIGNED NOT NULL,
|
|
friend_id VARCHAR(36) NOT NULL,
|
|
PRIMARY KEY (tag_id, friend_id),
|
|
FOREIGN KEY (tag_id) REFERENCES friend_tags(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (friend_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ── Moment Visibility Rules ──────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS moment_visibility (
|
|
moment_id BIGINT UNSIGNED NOT NULL,
|
|
type ENUM('whitelist','blacklist') NOT NULL,
|
|
target_type ENUM('tag','user') NOT NULL,
|
|
target_id VARCHAR(36) NOT NULL,
|
|
PRIMARY KEY (moment_id, target_type, target_id),
|
|
FOREIGN KEY (moment_id) REFERENCES moments(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Migration: add visibility column to moments (idempotent)
|
|
SET @m_vis = (SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'moments' AND COLUMN_NAME = 'visibility');
|
|
SET @m_sql = IF(@m_vis = 0,
|
|
"ALTER TABLE moments ADD COLUMN visibility ENUM('public','whitelist','blacklist') NOT NULL DEFAULT 'public' AFTER text_content",
|
|
'SELECT 1');
|
|
PREPARE m_stmt FROM @m_sql;
|
|
EXECUTE m_stmt;
|
|
DEALLOCATE PREPARE m_stmt;
|
|
|
|
-- ── TOTP Two-Factor Authentication ───────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS user_totp (
|
|
user_id VARCHAR(36) PRIMARY KEY,
|
|
totp_secret VARCHAR(64) NOT NULL,
|
|
recovery_codes TEXT DEFAULT NULL,
|
|
enabled TINYINT(1) NOT NULL DEFAULT 0,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ── Moment Privacy (user-level moments visibility) ──────────────────────
|
|
CREATE TABLE IF NOT EXISTS moment_privacy (
|
|
user_id VARCHAR(36) NOT NULL,
|
|
target_id VARCHAR(36) NOT NULL,
|
|
hide_their TINYINT(1) NOT NULL DEFAULT 0,
|
|
hide_mine TINYINT(1) NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (user_id, target_id),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (target_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ── Timeline (public posts, Xiaohongshu-style) ──────────────────────────
|
|
CREATE TABLE IF NOT EXISTS timeline_posts (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id VARCHAR(36) NOT NULL,
|
|
text_content VARCHAR(2000) NOT NULL DEFAULT '',
|
|
is_anonymous TINYINT(1) NOT NULL DEFAULT 0,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_tl_created (created_at),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS timeline_media (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
post_id BIGINT UNSIGNED NOT NULL,
|
|
url TEXT NOT NULL,
|
|
media_type ENUM('image','video') NOT NULL DEFAULT 'image',
|
|
thumbnail TEXT DEFAULT NULL,
|
|
duration SMALLINT UNSIGNED NOT NULL DEFAULT 0,
|
|
sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0,
|
|
INDEX idx_tl_media_post (post_id),
|
|
FOREIGN KEY (post_id) REFERENCES timeline_posts(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS timeline_likes (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
post_id BIGINT UNSIGNED NOT NULL,
|
|
user_id VARCHAR(36) NOT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uniq_tl_like (post_id, user_id),
|
|
FOREIGN KEY (post_id) REFERENCES timeline_posts(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS timeline_comments (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
post_id BIGINT UNSIGNED NOT NULL,
|
|
user_id VARCHAR(36) NOT NULL,
|
|
is_anonymous TINYINT(1) NOT NULL DEFAULT 0,
|
|
text_content VARCHAR(512) NOT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_tl_comment_post (post_id),
|
|
FOREIGN KEY (post_id) REFERENCES timeline_posts(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ── Group Invites (QR code invite links with expiration) ─────────────────
|
|
CREATE TABLE IF NOT EXISTS group_invites (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
group_id VARCHAR(36) NOT NULL,
|
|
created_by VARCHAR(36) NOT NULL,
|
|
expires_at DATETIME NOT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (group_id) REFERENCES `groups`(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
|
|
INDEX idx_gi_group (group_id),
|
|
INDEX idx_gi_expires (expires_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|