multiplayer-schema.sql
参考源码 · docs/account-system/multiplayer-schema.sql
sql
-- 多人领域参考 DDL v0.8.1 / 2026-09-13。
-- 先执行 schema.sql，再执行本文件；仍是一个 SQLite 数据库，不是第二个库。
PRAGMA foreign_keys = ON;
BEGIN IMMEDIATE;
CREATE TABLE online_games (
id TEXT PRIMARY KEY,
kind TEXT NOT NULL CHECK (kind IN ('daily_normal','daily_ranked')),
status TEXT NOT NULL CHECK (status IN ('waiting','countdown','running','resolving','ended','cancelled','invalid')),
difficulty TEXT NOT NULL CHECK (difficulty IN ('easy','medium','expert')),
rules_revision TEXT NOT NULL REFERENCES rule_revisions(revision),
rules_snapshot_json TEXT NOT NULL CHECK (json_valid(rules_snapshot_json)),
private_map_json TEXT CHECK (private_map_json IS NULL OR json_valid(private_map_json)),
map_hash TEXT CHECK (map_hash IS NULL OR length(map_hash)=64),
version INTEGER NOT NULL DEFAULT 1 CHECK (version>0),
current_result_revision INTEGER NOT NULL DEFAULT 0 CHECK (current_result_revision>=0),
created_at INTEGER NOT NULL,
started_at INTEGER,
total_deadline INTEGER,
ended_at INTEGER,
UNIQUE (id,kind),
CHECK (total_deadline IS NULL OR (started_at IS NOT NULL AND total_deadline>started_at)),
CHECK (ended_at IS NULL OR ended_at>=created_at)
) STRICT;
CREATE INDEX online_game_deadlines ON online_games(status,total_deadline);
CREATE TABLE online_seats (
game_id TEXT NOT NULL REFERENCES online_games(id),
user_id TEXT NOT NULL REFERENCES users(id),
seat_no INTEGER NOT NULL CHECK (seat_no = 1),
control_device_id TEXT, -- NULL仅用于终局账号删除后的脱敏引用壳；活跃局由事务服务强制非空
control_epoch INTEGER NOT NULL DEFAULT 1 CHECK (control_epoch>0),
input_epoch INTEGER NOT NULL DEFAULT 1 CHECK (input_epoch>0),
connection_state TEXT NOT NULL CHECK (connection_state IN ('connected','disconnected','restoring')),
eligibility TEXT NOT NULL CHECK (eligibility IN ('active','finished','forfeited','disqualified')),
initial_health INTEGER CHECK (initial_health BETWEEN 1 AND 3),
remaining_health INTEGER CHECK (remaining_health>=0 AND remaining_health<=initial_health),
damage_count INTEGER NOT NULL DEFAULT 0 CHECK (damage_count>=0),
injuries INTEGER NOT NULL DEFAULT 0 CHECK (injuries>=0),
opened_safe INTEGER NOT NULL DEFAULT 0 CHECK (opened_safe>=0),
remaining_safe_at_start INTEGER NOT NULL CHECK (remaining_safe_at_start>0),
disconnected_at INTEGER,
disconnected_total_ms INTEGER NOT NULL DEFAULT 0 CHECK (disconnected_total_ms>=0),
disconnect_deadline INTEGER,
idle_elapsed_ms INTEGER NOT NULL DEFAULT 0 CHECK (idle_elapsed_ms>=0),
idle_clock_at INTEGER,
finished_at INTEGER,
forfeited_at INTEGER,
forfeit_reason TEXT,
private_state_json TEXT NOT NULL CHECK (json_valid(private_state_json)),
version INTEGER NOT NULL DEFAULT 1 CHECK (version>0),
PRIMARY KEY (game_id,user_id),
UNIQUE (game_id,seat_no),
FOREIGN KEY (user_id,control_device_id) REFERENCES devices(user_id,id),
CHECK (opened_safe<=remaining_safe_at_start),
CHECK ((initial_health IS NULL)=(remaining_health IS NULL))
) STRICT;
-- 一个账号最多一个正式每日尝试。
CREATE TABLE active_competitions (
user_id TEXT PRIMARY KEY,
game_id TEXT NOT NULL,
kind TEXT NOT NULL CHECK (kind IN ('daily_ranked')),
acquired_at INTEGER NOT NULL,
FOREIGN KEY (game_id,kind) REFERENCES online_games(id,kind),
FOREIGN KEY (game_id,user_id) REFERENCES online_seats(game_id,user_id)
) STRICT;
CREATE TRIGGER competition_requires_account BEFORE INSERT ON active_competitions
WHEN NOT EXISTS (SELECT 1 FROM users WHERE id=NEW.user_id AND kind='registered' AND status='active')
BEGIN SELECT RAISE(ABORT,'registered active account required'); END;
CREATE TABLE online_commands (
game_id TEXT NOT NULL,
user_id TEXT NOT NULL,
command_id TEXT NOT NULL,
control_epoch INTEGER NOT NULL CHECK (control_epoch>0),
client_seq INTEGER NOT NULL CHECK (client_seq>0),
request_hash TEXT NOT NULL CHECK (length(request_hash)=64),
received_at INTEGER NOT NULL,
receipt_json TEXT NOT NULL CHECK (json_valid(receipt_json)),
PRIMARY KEY (game_id,user_id,command_id),
UNIQUE (game_id,user_id,control_epoch,client_seq),
FOREIGN KEY (game_id,user_id) REFERENCES online_seats(game_id,user_id)
) STRICT;
CREATE TABLE online_events (
global_seq INTEGER PRIMARY KEY AUTOINCREMENT,
game_id TEXT NOT NULL REFERENCES online_games(id),
game_seq INTEGER NOT NULL CHECK (game_seq>0),
actor_user_id TEXT,
effective_at INTEGER NOT NULL,
kind TEXT NOT NULL,
event_hash TEXT NOT NULL CHECK (length(event_hash)=64),
chain_hash TEXT NOT NULL CHECK (length(chain_hash)=64),
state_hash TEXT NOT NULL CHECK (length(state_hash)=64),
UNIQUE (game_id,game_seq),
FOREIGN KEY (game_id,actor_user_id) REFERENCES online_seats(game_id,user_id)
) STRICT;
CREATE INDEX online_events_time ON online_events(game_id,effective_at,game_seq);
CREATE TABLE online_results (
game_id TEXT NOT NULL REFERENCES online_games(id),
revision INTEGER NOT NULL CHECK (revision>0),
kind TEXT NOT NULL CHECK (kind IN ('completion','time_limit','abandoned','invalid','daily_result')),
winner_user_id TEXT,
reason TEXT NOT NULL,
facts_json TEXT NOT NULL CHECK (json_valid(facts_json)),
effective_at INTEGER NOT NULL,
recorded_at INTEGER NOT NULL,
PRIMARY KEY (game_id,revision),
FOREIGN KEY (game_id,winner_user_id) REFERENCES online_seats(game_id,user_id),
CHECK (kind <> 'invalid' OR winner_user_id IS NULL)
) STRICT;
CREATE TRIGGER online_results_append_only BEFORE UPDATE ON online_results
BEGIN SELECT RAISE(ABORT,'append a result revision'); END;
CREATE TABLE account_restrictions (
user_id TEXT NOT NULL REFERENCES users(id),
scope TEXT NOT NULL CHECK (scope IN ('ranked')),
until_at INTEGER,
reason TEXT NOT NULL,
source_json TEXT NOT NULL CHECK (json_valid(source_json)),
version INTEGER NOT NULL CHECK (version>0),
PRIMARY KEY (user_id,scope)
) STRICT;
CREATE TABLE daily_boards (
challenge_date TEXT PRIMARY KEY,
starts_at INTEGER NOT NULL,
ends_at INTEGER NOT NULL CHECK (ends_at>starts_at),
public_until INTEGER NOT NULL CHECK (public_until>ends_at),
status TEXT NOT NULL CHECK (status IN ('open','frozen','invalid')),
generation INTEGER NOT NULL DEFAULT 0 CHECK (generation>=0),
frozen_at INTEGER,
policy_revision TEXT NOT NULL REFERENCES rule_revisions(revision),
CHECK ((status='frozen')=(frozen_at IS NOT NULL))
) STRICT;
CREATE TABLE daily_account_state (
entry_no INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT NOT NULL REFERENCES users(id),
challenge_date TEXT NOT NULL REFERENCES daily_boards(challenge_date),
view_locked_at INTEGER,
view_lock_order INTEGER,
withdrawn_at INTEGER,
version INTEGER NOT NULL CHECK (version>0),
UNIQUE (user_id,challenge_date),
CHECK ((view_locked_at IS NULL)=(view_lock_order IS NULL))
) STRICT;
CREATE TABLE daily_attempts (
game_id TEXT PRIMARY KEY REFERENCES online_games(id),
user_id TEXT NOT NULL,
challenge_date TEXT NOT NULL,
tier TEXT NOT NULL CHECK (tier IN ('easy','medium','expert')),
entry_kind TEXT NOT NULL CHECK (entry_kind IN ('normal','ranked')),
started_at INTEGER NOT NULL,
finished_at INTEGER,
finished_order INTEGER,
elapsed_ms INTEGER CHECK (elapsed_ms>=0),
initial_health INTEGER NOT NULL CHECK (initial_health BETWEEN 1 AND 3),
damage_count INTEGER NOT NULL DEFAULT 0 CHECK (damage_count>=0),
health_lost INTEGER NOT NULL DEFAULT 0 CHECK (health_lost>=0),
status TEXT NOT NULL CHECK (status IN ('active','won','lost','abandoned','expired')),
verification TEXT NOT NULL CHECK (verification IN ('pending','valid','revoked','invalid')),
ranking_eligible INTEGER NOT NULL CHECK (ranking_eligible IN (0,1)),
record_complete INTEGER NOT NULL CHECK (record_complete IN (0,1)),
confirmed_at INTEGER,
reward_bridge_run_id TEXT,
record_device_id TEXT,
record_manifest_json TEXT CHECK (record_manifest_json IS NULL OR json_valid(record_manifest_json)),
UNIQUE (user_id,game_id),
FOREIGN KEY (user_id,record_device_id) REFERENCES devices(user_id,id),
FOREIGN KEY (game_id,user_id) REFERENCES online_seats(game_id,user_id),
FOREIGN KEY (user_id,challenge_date) REFERENCES daily_account_state(user_id,challenge_date),
FOREIGN KEY (challenge_date,tier) REFERENCES daily_challenges(challenge_date,tier),
FOREIGN KEY (user_id,reward_bridge_run_id) REFERENCES game_runs(user_id,run_id),
CHECK (ranking_eligible=0 OR (entry_kind='ranked' AND damage_count=0 AND health_lost=0)),
CHECK ((finished_at IS NULL)=(finished_order IS NULL))
) STRICT;
CREATE INDEX daily_attempts_confirmation ON daily_attempts(challenge_date,verification,finished_at);
CREATE TABLE daily_view_entitlements (
user_id TEXT NOT NULL,
challenge_date TEXT NOT NULL,
easy_game_id TEXT NOT NULL,
medium_game_id TEXT NOT NULL,
expert_game_id TEXT NOT NULL,
granted_at INTEGER NOT NULL,
revoked_at INTEGER,
version INTEGER NOT NULL CHECK (version>0),
PRIMARY KEY (user_id,challenge_date),
FOREIGN KEY (user_id,challenge_date) REFERENCES daily_account_state(user_id,challenge_date),
FOREIGN KEY (user_id,easy_game_id) REFERENCES daily_attempts(user_id,game_id),
FOREIGN KEY (user_id,medium_game_id) REFERENCES daily_attempts(user_id,game_id),
FOREIGN KEY (user_id,expert_game_id) REFERENCES daily_attempts(user_id,game_id),
CHECK (easy_game_id<>medium_game_id AND easy_game_id<>expert_game_id AND medium_game_id<>expert_game_id)
) STRICT;
CREATE TABLE daily_bests (
user_id TEXT NOT NULL,
challenge_date TEXT NOT NULL,
tier TEXT NOT NULL,
game_id TEXT NOT NULL,
PRIMARY KEY (user_id,challenge_date,tier),
FOREIGN KEY (user_id,challenge_date) REFERENCES daily_account_state(user_id,challenge_date),
FOREIGN KEY (user_id,game_id) REFERENCES daily_attempts(user_id,game_id),
CHECK (tier IN ('easy','medium','expert'))
) STRICT;
CREATE TRIGGER best_requires_valid_attempt BEFORE INSERT ON daily_bests
WHEN NOT EXISTS (
SELECT 1 FROM daily_attempts a JOIN daily_boards d ON d.challenge_date=a.challenge_date
WHERE a.game_id=NEW.game_id AND a.user_id=NEW.user_id AND a.challenge_date=NEW.challenge_date AND a.tier=NEW.tier
AND a.status='won' AND a.verification='valid' AND a.ranking_eligible=1 AND a.record_complete=1
AND a.finished_at>=d.starts_at AND a.finished_at<d.ends_at
AND a.confirmed_at<d.ends_at AND d.status='open'
AND CAST(strftime('%s','now') AS INTEGER)*1000<d.ends_at
)
BEGIN SELECT RAISE(ABORT,'valid zero damage ranked attempt required'); END;
CREATE TRIGGER best_update_requires_valid_attempt BEFORE UPDATE ON daily_bests
WHEN NOT EXISTS (
SELECT 1 FROM daily_attempts a JOIN daily_boards d ON d.challenge_date=a.challenge_date
WHERE a.game_id=NEW.game_id AND a.user_id=NEW.user_id AND a.challenge_date=NEW.challenge_date AND a.tier=NEW.tier
AND a.status='won' AND a.verification='valid' AND a.ranking_eligible=1 AND a.record_complete=1
AND a.finished_at>=d.starts_at AND a.finished_at<d.ends_at
AND a.confirmed_at<d.ends_at AND d.status='open'
AND CAST(strftime('%s','now') AS INTEGER)*1000<d.ends_at
)
BEGIN SELECT RAISE(ABORT,'valid zero damage ranked attempt required'); END;
CREATE TABLE daily_rank_revisions (
challenge_date TEXT NOT NULL REFERENCES daily_boards(challenge_date),
generation INTEGER NOT NULL CHECK (generation>0),
reason TEXT NOT NULL,
created_at INTEGER NOT NULL,
PRIMARY KEY (challenge_date,generation)
) STRICT;
CREATE TABLE daily_rankings (
challenge_date TEXT NOT NULL,
board_kind TEXT NOT NULL CHECK (board_kind IN ('easy','medium','expert','overall')),
generation INTEGER NOT NULL,
user_id TEXT NOT NULL,
rank INTEGER NOT NULL CHECK (rank BETWEEN 1 AND 10),
total_ms INTEGER NOT NULL CHECK (total_ms>=0),
formed_at INTEGER NOT NULL,
entry_no INTEGER NOT NULL REFERENCES daily_account_state(entry_no),
easy_game_id TEXT,
medium_game_id TEXT,
expert_game_id TEXT,
PRIMARY KEY (challenge_date,generation,board_kind,user_id),
UNIQUE (challenge_date,generation,board_kind,rank),
FOREIGN KEY (challenge_date,generation) REFERENCES daily_rank_revisions(challenge_date,generation),
FOREIGN KEY (user_id,easy_game_id) REFERENCES daily_attempts(user_id,game_id),
FOREIGN KEY (user_id,medium_game_id) REFERENCES daily_attempts(user_id,game_id),
FOREIGN KEY (user_id,expert_game_id) REFERENCES daily_attempts(user_id,game_id),
CHECK (
(board_kind='easy' AND easy_game_id IS NOT NULL AND medium_game_id IS NULL AND expert_game_id IS NULL)
OR (board_kind='medium' AND easy_game_id IS NULL AND medium_game_id IS NOT NULL AND expert_game_id IS NULL)
OR (board_kind='expert' AND easy_game_id IS NULL AND medium_game_id IS NULL AND expert_game_id IS NOT NULL)
OR (board_kind='overall' AND easy_game_id IS NOT NULL AND medium_game_id IS NOT NULL AND expert_game_id IS NOT NULL
AND easy_game_id<>medium_game_id AND easy_game_id<>expert_game_id AND medium_game_id<>expert_game_id)
)
) STRICT;
-- 录像正文/播放块只存Redis并带TTL；本表及上传表只存元数据。
-- 同一个game_id的录像仅存一份；单项榜与综合榜引用共享，不接受个人收藏上传。
CREATE TABLE restricted_segments (
id TEXT PRIMARY KEY,
game_id TEXT NOT NULL UNIQUE REFERENCES daily_attempts(game_id),
version INTEGER NOT NULL CHECK (version>0),
expires_at INTEGER NOT NULL,
codec TEXT NOT NULL CHECK (codec='gzip'),
redis_key TEXT NOT NULL UNIQUE CHECK (length(redis_key)>0),
compressed_bytes INTEGER NOT NULL CHECK (compressed_bytes BETWEEN 1 AND 1048576),
storage_expires_at INTEGER NOT NULL,
uncompressed_bytes INTEGER NOT NULL CHECK (uncompressed_bytes BETWEEN 1 AND 8388608),
sha256 TEXT NOT NULL CHECK (length(sha256)=64),
validation_state TEXT NOT NULL CHECK (validation_state IN ('pending','valid','invalid')),
playback_state TEXT NOT NULL CHECK (playback_state IN ('available','temporary_error','expired','removed')),
created_at INTEGER NOT NULL,
CHECK (storage_expires_at>created_at AND storage_expires_at<=expires_at)
) STRICT;
CREATE TABLE restricted_chunks (
segment_id TEXT NOT NULL REFERENCES restricted_segments(id),
chunk_index INTEGER NOT NULL CHECK (chunk_index>=0),
from_ms INTEGER NOT NULL CHECK (from_ms>=0),
to_ms INTEGER NOT NULL CHECK (to_ms>=from_ms),
redis_key TEXT NOT NULL UNIQUE CHECK (length(redis_key)>0),
byte_length INTEGER NOT NULL CHECK (byte_length BETWEEN 1 AND 8388608),
expires_at INTEGER NOT NULL CHECK (expires_at>0),
sha256 TEXT NOT NULL CHECK (length(sha256)=64),
PRIMARY KEY (segment_id,chunk_index)
) STRICT;
CREATE TABLE watch_sessions (
id TEXT PRIMARY KEY,
user_id TEXT REFERENCES users(id),
auth_session_id TEXT REFERENCES sessions(id),
anonymous_id_hash TEXT,
replay_segment_id TEXT NOT NULL REFERENCES restricted_segments(id),
permission_epoch INTEGER NOT NULL CHECK (permission_epoch>0),
state TEXT NOT NULL CHECK (state IN ('waiting','consent_required','prepared','playing','uncertain','ended')),
created_at INTEGER NOT NULL,
expires_at INTEGER NOT NULL,
ended_at INTEGER,
UNIQUE (id,user_id),
FOREIGN KEY (user_id,auth_session_id) REFERENCES sessions(user_id,id),
CHECK ((user_id IS NOT NULL AND auth_session_id IS NOT NULL AND anonymous_id_hash IS NULL)
OR (user_id IS NULL AND auth_session_id IS NULL AND anonymous_id_hash IS NOT NULL))
) STRICT;
CREATE TABLE watch_exposures (
id TEXT PRIMARY KEY,
watch_session_id TEXT NOT NULL REFERENCES watch_sessions(id),
user_id TEXT NOT NULL,
challenge_date TEXT NOT NULL,
permission_epoch INTEGER NOT NULL CHECK (permission_epoch>0),
state TEXT NOT NULL CHECK (state IN ('prepared','released','rendered','cancelled','uncertain')),
prepared_at INTEGER NOT NULL,
released_at INTEGER,
rendered_at INTEGER,
release_order INTEGER,
render_receipt_hash TEXT,
FOREIGN KEY (watch_session_id,user_id) REFERENCES watch_sessions(id,user_id),
FOREIGN KEY (user_id,challenge_date) REFERENCES daily_account_state(user_id,challenge_date),
CHECK (state NOT IN ('released','rendered','uncertain') OR released_at IS NOT NULL),
CHECK (state<>'rendered' OR rendered_at IS NOT NULL)
) STRICT;
CREATE INDEX unresolved_exposure ON watch_exposures(user_id,challenge_date,state);
CREATE TABLE moderation_cases (
id TEXT PRIMARY KEY,
reporter_user_id TEXT REFERENCES users(id),
resource_type TEXT NOT NULL CHECK (resource_type IN ('game','ranking','account')),
resource_id TEXT NOT NULL,
kind TEXT NOT NULL CHECK (kind IN ('report','appeal','platform_incident','deletion')),
status TEXT NOT NULL CHECK (status IN ('open','reviewing','resolved','dismissed')),
reason TEXT NOT NULL,
resolution_json TEXT CHECK (resolution_json IS NULL OR json_valid(resolution_json)),
created_at INTEGER NOT NULL,
resolved_at INTEGER
) STRICT;
-- 在线HTTP命令也需要幂等，不能借私有sync_operations发比赛结果。
CREATE TABLE online_http_receipts (
user_id TEXT NOT NULL REFERENCES users(id),
route_scope TEXT NOT NULL,
idempotency_key TEXT NOT NULL,
request_hash TEXT NOT NULL CHECK (length(request_hash)=64),
response_json TEXT NOT NULL CHECK (json_valid(response_json)),
created_at INTEGER NOT NULL,
PRIMARY KEY (user_id,route_scope,idempotency_key)
) STRICT;
-- 每日地图只由服务器生成；公开目录不得包含此表的私有正文或种子。
CREATE TABLE daily_puzzles (
challenge_date TEXT NOT NULL,
tier TEXT NOT NULL,
generator_revision TEXT NOT NULL,
validator_revision TEXT NOT NULL,
private_map_json TEXT NOT NULL CHECK (json_valid(private_map_json)),
map_hash TEXT NOT NULL CHECK (length(map_hash)=64),
no_guess_required INTEGER NOT NULL CHECK (no_guess_required IN (0,1)),
validation_report_json TEXT NOT NULL CHECK (json_valid(validation_report_json)),
generated_at INTEGER NOT NULL,
published_at INTEGER NOT NULL,
PRIMARY KEY (challenge_date,tier),
FOREIGN KEY (challenge_date,tier) REFERENCES daily_challenges(challenge_date,tier)
) STRICT;
CREATE TRIGGER published_puzzle_immutable BEFORE UPDATE ON daily_puzzles
BEGIN SELECT RAISE(ABORT,'published puzzle immutable'); END;
CREATE TRIGGER published_daily_mapping_immutable BEFORE UPDATE ON daily_challenges
BEGIN SELECT RAISE(ABORT,'daily mapping immutable'); END;
-- candidate只表示允许上传；未核验者不得占据排名。
CREATE TABLE daily_score_submissions (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL,
challenge_date TEXT NOT NULL,
board_kind TEXT NOT NULL CHECK (board_kind IN ('easy','medium','expert','overall')),
easy_game_id TEXT,
medium_game_id TEXT,
expert_game_id TEXT,
total_ms INTEGER NOT NULL CHECK (total_ms>=0),
formed_at INTEGER NOT NULL,
entry_no INTEGER NOT NULL REFERENCES daily_account_state(entry_no),
compared_generation INTEGER NOT NULL CHECK (compared_generation>=0),
status TEXT NOT NULL CHECK (status IN ('not_candidate','awaiting_upload','verifying','published','outpaced','rejected','expired')),
manifest_hash TEXT NOT NULL CHECK (length(manifest_hash)=64),
created_at INTEGER NOT NULL,
expires_at INTEGER NOT NULL CHECK (expires_at>created_at),
published_generation INTEGER,
FOREIGN KEY (user_id,challenge_date) REFERENCES daily_account_state(user_id,challenge_date),
FOREIGN KEY (user_id,easy_game_id) REFERENCES daily_attempts(user_id,game_id),
FOREIGN KEY (user_id,medium_game_id) REFERENCES daily_attempts(user_id,game_id),
FOREIGN KEY (user_id,expert_game_id) REFERENCES daily_attempts(user_id,game_id),
FOREIGN KEY (challenge_date,published_generation) REFERENCES daily_rank_revisions(challenge_date,generation),
UNIQUE (id,user_id),
CHECK (easy_game_id<>medium_game_id AND easy_game_id<>expert_game_id AND medium_game_id<>expert_game_id),
CHECK ((status='published')=(published_generation IS NOT NULL)),
CHECK (
(board_kind='easy' AND easy_game_id IS NOT NULL AND medium_game_id IS NULL AND expert_game_id IS NULL)
OR (board_kind='medium' AND easy_game_id IS NULL AND medium_game_id IS NOT NULL AND expert_game_id IS NULL)
OR (board_kind='expert' AND easy_game_id IS NULL AND medium_game_id IS NULL AND expert_game_id IS NOT NULL)
OR (board_kind='overall' AND easy_game_id IS NOT NULL AND medium_game_id IS NOT NULL AND expert_game_id IS NOT NULL
AND easy_game_id<>medium_game_id AND easy_game_id<>expert_game_id AND medium_game_id<>expert_game_id)
)
) STRICT;
CREATE UNIQUE INDEX one_pending_daily_submission ON daily_score_submissions(user_id,challenge_date,board_kind)
WHERE status IN ('awaiting_upload','verifying');
CREATE TABLE daily_replay_uploads (
id TEXT PRIMARY KEY,
submission_id TEXT NOT NULL,
user_id TEXT NOT NULL,
game_id TEXT NOT NULL,
codec TEXT NOT NULL CHECK (codec='gzip'),
compressed_sha256 TEXT NOT NULL CHECK (length(compressed_sha256)=64),
content_sha256 TEXT NOT NULL CHECK (length(content_sha256)=64),
compressed_bytes INTEGER NOT NULL CHECK (compressed_bytes BETWEEN 1 AND 1048576),
uncompressed_bytes INTEGER NOT NULL CHECK (uncompressed_bytes BETWEEN 1 AND 8388608),
redis_key TEXT NOT NULL UNIQUE CHECK (length(redis_key)>0),
state TEXT NOT NULL CHECK (state IN ('granted','received','valid','invalid','expired')),
expires_at INTEGER NOT NULL,
received_at INTEGER,
verified_at INTEGER,
UNIQUE (submission_id,game_id),
FOREIGN KEY (submission_id,user_id) REFERENCES daily_score_submissions(id,user_id),
FOREIGN KEY (user_id,game_id) REFERENCES daily_attempts(user_id,game_id),
CHECK (state NOT IN ('received','valid') OR received_at IS NOT NULL),
CHECK (state<>'valid' OR verified_at IS NOT NULL)
) STRICT;
CREATE TRIGGER upload_requires_candidate BEFORE INSERT ON daily_replay_uploads
WHEN NOT EXISTS (
SELECT 1 FROM daily_score_submissions s JOIN daily_boards d USING(challenge_date)
WHERE s.id=NEW.submission_id AND s.user_id=NEW.user_id AND s.status='awaiting_upload'
AND NEW.game_id IN (s.easy_game_id,s.medium_game_id,s.expert_game_id)
AND NEW.expires_at<=s.expires_at AND s.expires_at<=d.ends_at
AND d.status='open' AND CAST(strftime('%s','now') AS INTEGER)*1000<NEW.expires_at
)
BEGIN SELECT RAISE(ABORT,'active candidate upload grant required'); END;
-- 历史撤回/违规只能追加注记或撤播放，不覆盖原rank或递补。
CREATE TABLE daily_rank_annotations (
id TEXT PRIMARY KEY,
challenge_date TEXT NOT NULL,
generation INTEGER NOT NULL,
board_kind TEXT NOT NULL CHECK (board_kind IN ('easy','medium','expert','overall')),
rank INTEGER NOT NULL,
kind TEXT NOT NULL CHECK (kind IN ('disqualified','withdrawn','identity_removed','replay_removed','platform_incident')),
reason TEXT NOT NULL,
created_at INTEGER NOT NULL,
FOREIGN KEY (challenge_date,generation,board_kind,rank) REFERENCES daily_rankings(challenge_date,generation,board_kind,rank)
) STRICT;
-- 防止冻结worker延迟时继续发布；数据库时钟是额外护栏，调度器也必须在写事务提交前检查日期。
CREATE TRIGGER rank_revision_before_cutoff BEFORE INSERT ON daily_rank_revisions
WHEN NOT EXISTS (
SELECT 1 FROM daily_boards d WHERE d.challenge_date=NEW.challenge_date AND d.status='open'
AND NEW.generation=d.generation+1
AND NEW.created_at>=d.starts_at AND NEW.created_at<d.ends_at
AND CAST(strftime('%s','now') AS INTEGER)*1000<d.ends_at
)
BEGIN SELECT RAISE(ABORT,'daily board closed'); END;
CREATE TRIGGER ranking_before_cutoff BEFORE INSERT ON daily_rankings
WHEN NOT EXISTS (
SELECT 1 FROM daily_boards d WHERE d.challenge_date=NEW.challenge_date AND d.status='open'
AND NEW.generation=d.generation+1
AND CAST(strftime('%s','now') AS INTEGER)*1000<d.ends_at
)
BEGIN SELECT RAISE(ABORT,'daily board closed'); END;
CREATE TRIGGER rank_revision_immutable BEFORE UPDATE ON daily_rank_revisions
BEGIN SELECT RAISE(ABORT,'ranking revision immutable'); END;
CREATE TRIGGER rank_revision_no_delete BEFORE DELETE ON daily_rank_revisions
BEGIN SELECT RAISE(ABORT,'ranking revision retained'); END;
CREATE TRIGGER ranking_immutable BEFORE UPDATE ON daily_rankings
BEGIN SELECT RAISE(ABORT,'ranking snapshot immutable'); END;
CREATE TRIGGER ranking_no_delete BEFORE DELETE ON daily_rankings
BEGIN SELECT RAISE(ABORT,'ranking snapshot retained'); END;
CREATE TRIGGER frozen_board_immutable BEFORE UPDATE ON daily_boards
WHEN OLD.status='frozen'
BEGIN SELECT RAISE(ABORT,'frozen board immutable'); END;
CREATE TRIGGER board_deadline_immutable BEFORE UPDATE ON daily_boards
WHEN NEW.starts_at<>OLD.starts_at OR NEW.ends_at<>OLD.ends_at OR NEW.challenge_date<>OLD.challenge_date
BEGIN SELECT RAISE(ABORT,'daily deadline immutable'); END;
CREATE TRIGGER board_generation_before_cutoff BEFORE UPDATE OF generation ON daily_boards
WHEN NEW.generation<>OLD.generation AND (
NEW.generation<>OLD.generation+1 OR OLD.status<>'open' OR CAST(strftime('%s','now') AS INTEGER)*1000>=OLD.ends_at
OR NOT EXISTS (SELECT 1 FROM daily_rank_revisions r WHERE r.challenge_date=NEW.challenge_date AND r.generation=NEW.generation)
)
BEGIN SELECT RAISE(ABORT,'cannot publish ranking generation'); END;
CREATE TRIGGER board_freeze_after_cutoff BEFORE UPDATE OF status ON daily_boards
WHEN NEW.status='frozen' AND (NEW.frozen_at<OLD.ends_at OR CAST(strftime('%s','now') AS INTEGER)*1000<OLD.ends_at)
BEGIN SELECT RAISE(ABORT,'cannot freeze before cutoff'); END;
CREATE TRIGGER terminal_attempt_facts_immutable BEFORE UPDATE ON daily_attempts
WHEN OLD.status<>'active' AND (
NEW.game_id<>OLD.game_id OR NEW.user_id<>OLD.user_id OR NEW.challenge_date<>OLD.challenge_date
OR NEW.tier<>OLD.tier OR NEW.entry_kind<>OLD.entry_kind OR NEW.started_at<>OLD.started_at
OR NEW.finished_at IS NOT OLD.finished_at OR NEW.finished_order IS NOT OLD.finished_order
OR NEW.elapsed_ms IS NOT OLD.elapsed_ms OR NEW.initial_health<>OLD.initial_health
OR NEW.damage_count<>OLD.damage_count OR NEW.health_lost<>OLD.health_lost OR NEW.status<>OLD.status
)
BEGIN SELECT RAISE(ABORT,'terminal attempt facts immutable'); END;
COMMIT;