-- Baseline schema(2026-07):對齊 Express 退役時的正式 DB 最終形狀。
-- 來源:git 歷史的 server/database.js(初始 DDL + 全部後續 ALTER 折疊進欄位清單)
-- + Rust main.rs 曾冪等建立的 web_vitals / link_previews。
-- 全部 IF NOT EXISTS:在既有正式 DB 上執行是 no-op(sqlx 只記錄版本),
-- 在全新 DB / 測試 in-memory DB 上則建出完整 schema。
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
role TEXT DEFAULT 'admin',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- posts:初始欄位 + layout_type/i18n(en/zh_cn/ja/ko × title/content/excerpt)
-- + series + allow_comments 等歷次 ALTER 的最終形狀
CREATE TABLE IF NOT EXISTS posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
content TEXT NOT NULL,
excerpt TEXT,
category TEXT,
status TEXT DEFAULT 'published',
author TEXT DEFAULT 'Koimsurai',
view_count INTEGER DEFAULT 0,
likes INTEGER DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
layout_type TEXT DEFAULT 'record',
source_language TEXT DEFAULT 'zh-TW',
title_en TEXT,
content_en TEXT,
excerpt_en TEXT,
title_zh_cn TEXT,
content_zh_cn TEXT,
excerpt_zh_cn TEXT,
title_ja TEXT,
content_ja TEXT,
excerpt_ja TEXT,
title_ko TEXT,
content_ko TEXT,
excerpt_ko TEXT,
series_name TEXT DEFAULT NULL,
series_order INTEGER DEFAULT NULL,
allow_comments INTEGER DEFAULT 1
);
CREATE TABLE IF NOT EXISTS categories (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE NOT NULL,
slug TEXT UNIQUE NOT NULL,
description TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
short_description TEXT DEFAULT ''
);
CREATE TABLE IF NOT EXISTS tags (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS post_tags (
post_id INTEGER,
tag_id INTEGER,
PRIMARY KEY (post_id, tag_id),
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS post_reactions (
post_id INTEGER NOT NULL,
emoji TEXT NOT NULL,
count INTEGER DEFAULT 0,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (post_id, emoji),
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
);
-- comments:post_id 可為 NULL(碎念留言只填 thought_id——見 9bf416b 的 nullable 遷移)
CREATE TABLE IF NOT EXISTS comments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
post_id INTEGER,
author TEXT NOT NULL,
content TEXT NOT NULL,
likes INTEGER DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
status TEXT DEFAULT 'approved',
ip TEXT DEFAULT '',
parent_id INTEGER DEFAULT NULL,
is_admin INTEGER DEFAULT 0,
email TEXT DEFAULT '',
website TEXT DEFAULT '',
avatar_url TEXT DEFAULT '',
thought_id INTEGER DEFAULT NULL,
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS anime_history (
anime_sn INTEGER NOT NULL,
video_sn INTEGER NOT NULL,
title TEXT,
cover_url TEXT,
episode TEXT,
last_watched_at DATETIME DEFAULT CURRENT_TIMESTAMP,
synced_at DATETIME DEFAULT CURRENT_TIMESTAMP,
tmdb_id INTEGER,
PRIMARY KEY (anime_sn, video_sn)
);
CREATE TABLE IF NOT EXISTS film_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
watched_date DATE,
rating INTEGER,
source TEXT,
tmdb_id INTEGER,
poster_url TEXT,
release_year INTEGER,
genres TEXT,
notes TEXT,
synced_at DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE(title, watched_date)
);
CREATE TABLE IF NOT EXISTS tv_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
series_name TEXT NOT NULL,
episode_label TEXT,
watched_date DATE,
source TEXT,
tmdb_id INTEGER,
poster_url TEXT,
genres TEXT,
synced_at DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE(series_name, episode_label, watched_date)
);
CREATE TABLE IF NOT EXISTS thoughts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
content TEXT NOT NULL,
ref_type TEXT,
ref_url TEXT,
ref_json TEXT,
likes INTEGER DEFAULT 0,
dislikes INTEGER DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME,
edited INTEGER DEFAULT 0
);
CREATE TABLE IF NOT EXISTS watch_favorites (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tmdb_id INTEGER NOT NULL,
kind TEXT NOT NULL DEFAULT 'film',
rating INTEGER DEFAULT 5,
quote TEXT DEFAULT '',
poster_url TEXT,
year INTEGER,
sort_order INTEGER DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- collection_items:Rust 端已無端點使用,但正式 DB 存在此表——baseline 保持對齊
CREATE TABLE IF NOT EXISTS collection_items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
original_title TEXT,
year INTEGER,
poster_url TEXT,
overview TEXT,
external_id TEXT,
collection_type TEXT NOT NULL,
media_format TEXT NOT NULL,
source TEXT DEFAULT 'manual',
status TEXT DEFAULT 'completed',
rating INTEGER,
review TEXT,
is_favorite BOOLEAN DEFAULT 0,
watch_date DATE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS newsletter_subscribers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT UNIQUE NOT NULL,
name TEXT,
status TEXT DEFAULT 'active',
unsubscribe_token TEXT UNIQUE,
subscribed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
unsubscribed_at DATETIME
);
CREATE TABLE IF NOT EXISTS ip_blacklist (
id INTEGER PRIMARY KEY AUTOINCREMENT,
ip TEXT UNIQUE NOT NULL,
reason TEXT DEFAULT '',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS keyword_filters (
id INTEGER PRIMARY KEY AUTOINCREMENT,
keyword TEXT UNIQUE NOT NULL,
action TEXT DEFAULT 'spam',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS oauth_users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
provider TEXT NOT NULL,
provider_id TEXT NOT NULL,
display_name TEXT NOT NULL,
email TEXT DEFAULT '',
avatar_url TEXT DEFAULT '',
role TEXT NOT NULL DEFAULT 'USER',
linked_to INTEGER DEFAULT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE(provider, provider_id)
);
CREATE TABLE IF NOT EXISTS books (
id INTEGER PRIMARY KEY AUTOINCREMENT,
isbn TEXT,
title TEXT NOT NULL,
authors TEXT,
publisher TEXT,
published_date TEXT,
description TEXT,
cover_url TEXT,
page_count INTEGER,
language TEXT,
categories TEXT,
reading_status TEXT DEFAULT 'to-read',
rating REAL,
personal_notes TEXT,
date_added DATETIME DEFAULT CURRENT_TIMESTAMP,
date_updated DATETIME DEFAULT CURRENT_TIMESTAMP,
date_started DATETIME,
date_finished DATETIME
);
-- Rust 期新增(原本由 main.rs 冪等建立,收編進 migration)
CREATE TABLE IF NOT EXISTS web_vitals (
id INTEGER PRIMARY KEY AUTOINCREMENT,
metric TEXT NOT NULL,
value REAL NOT NULL,
rating TEXT NOT NULL,
path TEXT NOT NULL,
is_mobile INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS link_previews (
url TEXT PRIMARY KEY,
title TEXT,
description TEXT,
image TEXT,
site_name TEXT,
fetched_at TEXT NOT NULL DEFAULT (datetime('now'))
);
-- ── 索引(Express performance indexes + Rust 期新增,全數折疊)──
CREATE INDEX IF NOT EXISTS idx_posts_status_created ON posts(status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_posts_category ON posts(category);
CREATE INDEX IF NOT EXISTS idx_comments_post_status ON comments(post_id, status);
CREATE INDEX IF NOT EXISTS idx_comments_thought ON comments(thought_id, status);
CREATE INDEX IF NOT EXISTS idx_post_tags_post ON post_tags(post_id);
CREATE INDEX IF NOT EXISTS idx_post_tags_tag ON post_tags(tag_id);
CREATE INDEX IF NOT EXISTS idx_newsletter_status ON newsletter_subscribers(status);
CREATE UNIQUE INDEX IF NOT EXISTS idx_newsletter_token ON newsletter_subscribers(unsubscribe_token);
CREATE INDEX IF NOT EXISTS idx_oauth_provider ON oauth_users(provider, provider_id);
CREATE INDEX IF NOT EXISTS idx_books_reading_status ON books(reading_status);
CREATE INDEX IF NOT EXISTS idx_collection_type ON collection_items(collection_type);
CREATE INDEX IF NOT EXISTS idx_anime_history_watched ON anime_history(last_watched_at DESC);
CREATE INDEX IF NOT EXISTS idx_anime_history_sn ON anime_history(anime_sn);
CREATE INDEX IF NOT EXISTS idx_anime_history_video ON anime_history(video_sn);
CREATE INDEX IF NOT EXISTS idx_film_history_tmdb ON film_history(tmdb_id);
CREATE INDEX IF NOT EXISTS idx_tv_history_series ON tv_history(series_name);
CREATE INDEX IF NOT EXISTS idx_thoughts_created ON thoughts(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_web_vitals_metric_created ON web_vitals(metric, created_at);