schema.sql

-- 账号系统设计 v0.8.1 / 2026-09-13；可在空 SQLite 3.38+ 执行验证。
-- 这是参考 DDL，尚未接入运行中的游戏或服务端，不是已发布 migration。
-- 生产连接另设 journal_mode=WAL / synchronous=FULL / busy_timeout=5000。
PRAGMA foreign_keys = ON;
BEGIN IMMEDIATE;

CREATE TABLE schema_metadata (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL
) STRICT;
INSERT INTO schema_metadata VALUES ('design_version', '0.8.1');

CREATE TABLE users (
  id TEXT PRIMARY KEY,
  kind TEXT NOT NULL CHECK (kind IN ('guest','registered')),
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','disabled','merged','deleting')),
  display_name TEXT NOT NULL CHECK (length(display_name) BETWEEN 1 AND 32),
  auth_version INTEGER NOT NULL DEFAULT 1 CHECK (auth_version > 0),
  profile_version INTEGER NOT NULL DEFAULT 1 CHECK (profile_version > 0),
  data_revision INTEGER NOT NULL DEFAULT 0 CHECK (data_revision >= 0),
  merged_into_user_id TEXT REFERENCES users(id),
  created_at INTEGER NOT NULL CHECK (created_at >= 0),
  updated_at INTEGER NOT NULL CHECK (updated_at >= created_at),
  last_active_at INTEGER NOT NULL CHECK (last_active_at >= created_at),
  CHECK (merged_into_user_id IS NULL OR merged_into_user_id <> id),
  CHECK ((status = 'merged') = (merged_into_user_id IS NOT NULL))
) STRICT;
CREATE INDEX users_guest_cleanup ON users(kind, status, last_active_at);

CREATE TABLE auth_identities (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  provider TEXT NOT NULL,
  issuer TEXT NOT NULL,
  subject TEXT NOT NULL,
  created_at INTEGER NOT NULL CHECK (created_at >= 0),
  UNIQUE (provider, issuer, subject),
  UNIQUE (user_id, id),
  CHECK (provider <> 'password' OR (
    issuer = 'local' AND length(subject) BETWEEN 4 AND 32
    AND substr(subject,1,1) GLOB '[a-z]'
    AND subject NOT GLOB '*[^a-z0-9_]*'
  ))
) STRICT;
CREATE UNIQUE INDEX one_password_identity ON auth_identities(user_id) WHERE provider = 'password';

CREATE TABLE password_credentials (
  identity_id TEXT PRIMARY KEY REFERENCES auth_identities(id) ON DELETE CASCADE,
  password_hash TEXT NOT NULL CHECK (password_hash LIKE '$argon2id$%'),
  changed_at INTEGER NOT NULL CHECK (changed_at >= 0)
) STRICT;
CREATE TRIGGER credential_requires_password_identity BEFORE INSERT ON password_credentials
WHEN NOT EXISTS (SELECT 1 FROM auth_identities WHERE id = NEW.identity_id AND provider = 'password')
BEGIN SELECT RAISE(ABORT, 'password identity required'); END;

CREATE TABLE recovery_codes (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  code_hash TEXT NOT NULL UNIQUE CHECK (length(code_hash) = 64),
  created_at INTEGER NOT NULL,
  consumed_at INTEGER,
  CHECK (consumed_at IS NULL OR consumed_at >= created_at)
) STRICT;

CREATE TABLE devices (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  client_install_id TEXT NOT NULL,
  label TEXT NOT NULL CHECK (length(label) BETWEEN 1 AND 80),
  platform TEXT NOT NULL CHECK (platform IN ('web','mac','ios','android','windows')),
  created_at INTEGER NOT NULL,
  last_seen_at INTEGER NOT NULL,
  UNIQUE (user_id, client_install_id),
  UNIQUE (user_id, id)
) STRICT;

CREATE TABLE sessions (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  device_id TEXT NOT NULL,
  token_hash TEXT NOT NULL UNIQUE CHECK (length(token_hash) = 64),
  csrf_hash TEXT NOT NULL CHECK (length(csrf_hash) = 64),
  auth_version INTEGER NOT NULL CHECK (auth_version > 0),
  created_at INTEGER NOT NULL,
  last_seen_at INTEGER NOT NULL,
  idle_expires_at INTEGER NOT NULL,
  absolute_expires_at INTEGER NOT NULL,
  revoked_at INTEGER,
  UNIQUE (user_id, id),
  FOREIGN KEY (user_id, device_id) REFERENCES devices(user_id, id),
  CHECK (idle_expires_at <= absolute_expires_at),
  CHECK (absolute_expires_at > created_at)
) STRICT;
CREATE INDEX sessions_by_user ON sessions(user_id, revoked_at, absolute_expires_at);
CREATE INDEX sessions_expiry ON sessions(absolute_expires_at);

-- auth_receipts 不保存可离线猜测密码的裸 SHA 摘要。
-- request_mac = HMAC(独立服务端密钥, 规范化请求)；响应用外部密钥 AEAD 加密。
CREATE TABLE auth_receipts (
  scope TEXT NOT NULL,
  request_id TEXT NOT NULL,
  proof_hash TEXT NOT NULL CHECK (length(proof_hash) = 64),
  request_mac TEXT NOT NULL CHECK (length(request_mac) = 64),
  user_id TEXT REFERENCES users(id) ON DELETE CASCADE,
  session_id TEXT REFERENCES sessions(id) ON DELETE CASCADE,
  response_ciphertext BLOB NOT NULL,
  key_id TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  expires_at INTEGER NOT NULL CHECK (expires_at > created_at),
  PRIMARY KEY (scope, request_id)
) STRICT;
CREATE INDEX auth_receipts_expiry ON auth_receipts(expires_at);

CREATE TABLE rule_revisions (
  revision TEXT PRIMARY KEY,
  manifest_json TEXT NOT NULL CHECK (json_valid(manifest_json)),
  sha256 TEXT NOT NULL CHECK (length(sha256) = 64),
  offline_import_allowed INTEGER NOT NULL CHECK (offline_import_allowed IN (0,1)),
  created_at INTEGER NOT NULL
) STRICT;
CREATE TABLE content_revisions (
  content_id TEXT NOT NULL,
  revision TEXT NOT NULL,
  mode TEXT NOT NULL,
  manifest_json TEXT NOT NULL CHECK (json_valid(manifest_json)),
  sha256 TEXT NOT NULL CHECK (length(sha256) = 64),
  PRIMARY KEY (content_id, revision)
) STRICT;
CREATE TABLE daily_challenges (
  challenge_date TEXT NOT NULL CHECK (length(challenge_date) = 10),
  tier TEXT NOT NULL CHECK (tier IN ('easy','medium','expert')),
  content_id TEXT NOT NULL,
  content_revision TEXT NOT NULL,
  rules_revision TEXT NOT NULL REFERENCES rule_revisions(revision),
  PRIMARY KEY (challenge_date, tier),
  FOREIGN KEY (content_id, content_revision) REFERENCES content_revisions(content_id, revision)
) STRICT;

CREATE TABLE profile_settings (
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  field_key TEXT NOT NULL CHECK (field_key IN ('theme','sound','appearance','combo','racing_mode')),
  value_json TEXT NOT NULL CHECK (json_valid(value_json)),
  version INTEGER NOT NULL CHECK (version > 0),
  updated_at INTEGER NOT NULL,
  PRIMARY KEY (user_id, field_key)
) STRICT;

-- game_runs 为已登记的对局元信息；终局单独不可变，避免 run 状态覆盖重复领奖。
CREATE TABLE game_runs (
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  run_id TEXT NOT NULL,
  origin_id TEXT NOT NULL,
  mode TEXT NOT NULL CHECK (mode IN ('classic','custom','campaign','daily','field_lab','sudoku','single_mine')),
  content_id TEXT NOT NULL,
  content_revision TEXT NOT NULL,
  rules_revision TEXT NOT NULL REFERENCES rule_revisions(revision),
  config_json TEXT NOT NULL CHECK (json_valid(config_json)),
  created_at INTEGER NOT NULL,
  PRIMARY KEY (user_id, run_id),
  UNIQUE (user_id, origin_id),
  FOREIGN KEY (content_id, content_revision) REFERENCES content_revisions(content_id, revision)
) STRICT;

CREATE TABLE blobs (
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  id TEXT NOT NULL,
  sha256 TEXT NOT NULL CHECK (length(sha256) = 64),
  codec TEXT NOT NULL CHECK (codec IN ('gzip','identity')),
  purpose TEXT NOT NULL CHECK (purpose='legacy_import'),
  size_bytes INTEGER NOT NULL CHECK (size_bytes BETWEEN 1 AND 8388608),
  unpacked_bytes INTEGER NOT NULL CHECK (unpacked_bytes BETWEEN 1 AND 33554432),
  content BLOB,
  created_at INTEGER NOT NULL,
  purged_at INTEGER,
  PRIMARY KEY (user_id, id),
  UNIQUE (user_id, purpose, sha256),
  CHECK ((purged_at IS NULL AND content IS NOT NULL AND length(content) = size_bytes)
    OR (purged_at IS NOT NULL AND content IS NULL AND purged_at >= created_at))
) STRICT;

CREATE TABLE run_results (
  user_id TEXT NOT NULL,
  run_id TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('won','lost','abandoned')),
  result_json TEXT NOT NULL CHECK (json_valid(result_json)),
  payload_hash TEXT NOT NULL CHECK (length(payload_hash) = 64),
  provenance TEXT NOT NULL CHECK (provenance IN ('client_reported','server_validated')),
  received_at INTEGER NOT NULL,
  PRIMARY KEY (user_id, run_id),
  FOREIGN KEY (user_id, run_id) REFERENCES game_runs(user_id, run_id) ON DELETE CASCADE
) STRICT;
CREATE TRIGGER results_immutable BEFORE UPDATE ON run_results
BEGIN SELECT RAISE(ABORT, 'terminal result immutable'); END;

CREATE TABLE save_slots (
  user_id TEXT NOT NULL,
  slot_id TEXT NOT NULL,
  run_id TEXT NOT NULL,
  branch_id TEXT NOT NULL,
  device_id TEXT NOT NULL,
  format_version INTEGER NOT NULL CHECK (format_version > 0),
  snapshot_json TEXT CHECK (snapshot_json IS NULL OR json_valid(snapshot_json)),
  version INTEGER NOT NULL CHECK (version > 0),
  updated_at INTEGER NOT NULL,
  deleted_at INTEGER,
  PRIMARY KEY (user_id, slot_id),
  UNIQUE (user_id, run_id, branch_id),
  FOREIGN KEY (user_id, run_id) REFERENCES game_runs(user_id, run_id) ON DELETE CASCADE,
  FOREIGN KEY (user_id, device_id) REFERENCES devices(user_id, id),
  CHECK ((deleted_at IS NULL AND snapshot_json IS NOT NULL) OR (deleted_at IS NOT NULL AND snapshot_json IS NULL))
) STRICT;

CREATE TABLE level_progress (
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  content_id TEXT NOT NULL,
  content_revision TEXT NOT NULL,
  rules_revision TEXT NOT NULL REFERENCES rule_revisions(revision),
  assist_class TEXT NOT NULL,
  completed INTEGER NOT NULL CHECK (completed IN (0,1)),
  best_ms INTEGER CHECK (best_ms IS NULL OR best_ms >= 0),
  version INTEGER NOT NULL CHECK (version > 0),
  updated_at INTEGER NOT NULL,
  PRIMARY KEY (user_id, content_id, content_revision, rules_revision, assist_class),
  FOREIGN KEY (content_id, content_revision) REFERENCES content_revisions(content_id, revision)
) STRICT;

CREATE TABLE daily_rewards (
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  challenge_date TEXT NOT NULL,
  tier TEXT NOT NULL,
  won INTEGER NOT NULL CHECK (won IN (0,1)),
  paid_units INTEGER NOT NULL CHECK (paid_units BETWEEN 0 AND 100000000000000),
  rules_revision TEXT NOT NULL REFERENCES rule_revisions(revision),
  version INTEGER NOT NULL CHECK (version > 0),
  PRIMARY KEY (user_id, challenge_date, tier),
  FOREIGN KEY (challenge_date, tier) REFERENCES daily_challenges(challenge_date, tier)
) STRICT;

CREATE TABLE reward_ledger (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  award_key TEXT NOT NULL,
  run_id TEXT,
  kind TEXT NOT NULL CHECK (kind IN ('run','daily_delta','legacy_baseline','merge_adjustment','correction')),
  delta_units INTEGER NOT NULL CHECK (delta_units BETWEEN -100000000000000 AND 100000000000000),
  rules_revision TEXT NOT NULL REFERENCES rule_revisions(revision),
  provenance TEXT NOT NULL CHECK (provenance IN ('legacy_import','client_reported','server_validated','server_adjustment')),
  detail_json TEXT NOT NULL CHECK (json_valid(detail_json)),
  created_at INTEGER NOT NULL,
  UNIQUE (user_id, award_key),
  FOREIGN KEY (user_id, run_id) REFERENCES game_runs(user_id, run_id),
  CHECK (delta_units >= 0 OR kind IN ('merge_adjustment','correction'))
) STRICT;
CREATE INDEX ledger_history ON reward_ledger(user_id, created_at, id);
CREATE TRIGGER ledger_immutable BEFORE UPDATE ON reward_ledger
BEGIN SELECT RAISE(ABORT, 'ledger is append only'); END;

CREATE TABLE player_summary (
  user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  xp_units INTEGER NOT NULL CHECK (xp_units BETWEEN 0 AND 100000000000000),
  played INTEGER NOT NULL CHECK (played >= 0),
  wins INTEGER NOT NULL CHECK (wins BETWEEN 0 AND played),
  best_json TEXT NOT NULL CHECK (json_valid(best_json)),
  version INTEGER NOT NULL CHECK (version > 0),
  updated_at INTEGER NOT NULL
) STRICT;

CREATE TABLE medal_facts (
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  fact_key TEXT NOT NULL,
  run_id TEXT,
  fact_type TEXT NOT NULL,
  value_json TEXT NOT NULL CHECK (json_valid(value_json)),
  provenance TEXT NOT NULL CHECK (provenance IN ('legacy_import','client_reported','server_validated')),
  created_at INTEGER NOT NULL,
  PRIMARY KEY (user_id, fact_key),
  FOREIGN KEY (user_id, run_id) REFERENCES game_runs(user_id, run_id)
) STRICT;
CREATE TABLE player_medals (
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  medal_id TEXT NOT NULL,
  tier_id TEXT NOT NULL,
  unlocked_at INTEGER NOT NULL,
  upgraded_at INTEGER NOT NULL CHECK (upgraded_at >= unlocked_at),
  version INTEGER NOT NULL CHECK (version > 0),
  PRIMARY KEY (user_id, medal_id)
) STRICT;

-- 大墓碑与变更日志可清理；小型退休 ID 在账号生命周期内保留，防止陈旧设备复活实体。
CREATE TABLE retired_entities (
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  entity_type TEXT NOT NULL CHECK (entity_type='save'),
  entity_id TEXT NOT NULL,
  retired_at INTEGER NOT NULL,
  PRIMARY KEY (user_id, entity_type, entity_id)
) STRICT;

CREATE TABLE sync_operations (
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  op_id TEXT NOT NULL,
  device_id TEXT NOT NULL,
  payload_hash TEXT NOT NULL CHECK (length(payload_hash) = 64),
  response_json TEXT NOT NULL CHECK (json_valid(response_json)),
  created_at INTEGER NOT NULL,
  PRIMARY KEY (user_id, op_id),
  FOREIGN KEY (user_id, device_id) REFERENCES devices(user_id, id)
) STRICT;
CREATE INDEX sync_ops_cleanup ON sync_operations(created_at);
CREATE TABLE sync_changes (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  entity_type TEXT NOT NULL,
  entity_id TEXT NOT NULL,
  entity_version INTEGER NOT NULL CHECK (entity_version > 0),
  change_json TEXT NOT NULL CHECK (json_valid(change_json)),
  deleted_at INTEGER,
  created_at INTEGER NOT NULL
) STRICT;
CREATE INDEX sync_pull ON sync_changes(user_id, seq);
CREATE INDEX sync_changes_cleanup ON sync_changes(created_at);
CREATE TABLE sync_state (
  user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  epoch TEXT NOT NULL,
  min_retained_seq INTEGER NOT NULL DEFAULT 0 CHECK (min_retained_seq >= 0)
) STRICT;
CREATE TABLE sync_clients (
  user_id TEXT NOT NULL,
  device_id TEXT NOT NULL,
  ack_seq INTEGER NOT NULL CHECK (ack_seq >= 0),
  last_seen_at INTEGER NOT NULL,
  PRIMARY KEY (user_id, device_id),
  FOREIGN KEY (user_id, device_id) REFERENCES devices(user_id, id) ON DELETE CASCADE
) STRICT;

CREATE TABLE merge_tickets (
  id TEXT PRIMARY KEY,
  source_user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  source_session_id TEXT NOT NULL,
  ticket_hash TEXT NOT NULL UNIQUE CHECK (length(ticket_hash) = 64),
  expires_at INTEGER NOT NULL,
  consumed_at INTEGER,
  target_user_id TEXT REFERENCES users(id),
  FOREIGN KEY (source_user_id, source_session_id) REFERENCES sessions(user_id, id),
  CHECK (target_user_id IS NULL OR target_user_id <> source_user_id)
) STRICT;
CREATE TABLE merge_jobs (
  id TEXT PRIMARY KEY,
  ticket_id TEXT NOT NULL UNIQUE REFERENCES merge_tickets(id),
  source_user_id TEXT NOT NULL REFERENCES users(id),
  target_user_id TEXT NOT NULL REFERENCES users(id),
  source_revision INTEGER NOT NULL,
  target_revision INTEGER NOT NULL,
  preview_json TEXT NOT NULL CHECK (json_valid(preview_json)),
  result_json TEXT CHECK (result_json IS NULL OR json_valid(result_json)),
  status TEXT NOT NULL CHECK (status IN ('preview','completed','expired')),
  created_at INTEGER NOT NULL,
  expires_at INTEGER NOT NULL,
  CHECK (source_user_id <> target_user_id)
) STRICT;
CREATE TABLE legacy_imports (
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  origin_id TEXT NOT NULL,
  fingerprint TEXT NOT NULL CHECK (length(fingerprint) = 64),
  baseline_json TEXT NOT NULL CHECK (json_valid(baseline_json)),
  applied_at INTEGER NOT NULL,
  PRIMARY KEY (user_id, origin_id),
  UNIQUE (user_id, fingerprint)
) STRICT;

CREATE TABLE background_jobs (
  id TEXT PRIMARY KEY,
  user_id TEXT REFERENCES users(id) ON DELETE CASCADE,
  kind TEXT NOT NULL,
  dedupe_key TEXT NOT NULL UNIQUE,
  payload_json TEXT NOT NULL CHECK (json_valid(payload_json)),
  status TEXT NOT NULL CHECK (status IN ('pending','running','done','failed')),
  attempts INTEGER NOT NULL DEFAULT 0 CHECK (attempts >= 0),
  available_at INTEGER NOT NULL,
  lease_until INTEGER,
  last_error_code TEXT,
  created_at INTEGER NOT NULL
) STRICT;
CREATE INDEX jobs_poll ON background_jobs(status, available_at);
-- bootstrap 与导出分页使用不可变 artifact，避免长时间占用读事务。
CREATE TABLE sync_artifacts (
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  id TEXT NOT NULL,
  kind TEXT NOT NULL CHECK (kind IN ('bootstrap','export')),
  epoch TEXT NOT NULL,
  high_water_seq INTEGER NOT NULL CHECK (high_water_seq >= 0),
  manifest_json TEXT NOT NULL CHECK (json_valid(manifest_json)),
  created_at INTEGER NOT NULL,
  expires_at INTEGER NOT NULL CHECK (expires_at > created_at),
  PRIMARY KEY (user_id, id)
) STRICT;
CREATE TABLE artifact_pages (
  user_id TEXT NOT NULL,
  artifact_id TEXT NOT NULL,
  page_index INTEGER NOT NULL CHECK (page_index >= 0),
  content BLOB NOT NULL,
  sha256 TEXT NOT NULL CHECK (length(sha256) = 64),
  PRIMARY KEY (user_id, artifact_id, page_index),
  FOREIGN KEY (user_id, artifact_id) REFERENCES sync_artifacts(user_id, id) ON DELETE CASCADE
) STRICT;
CREATE TABLE audit_events (
  id TEXT PRIMARY KEY,
  user_id TEXT REFERENCES users(id) ON DELETE SET NULL,
  action TEXT NOT NULL,
  request_id TEXT NOT NULL,
  detail_json TEXT NOT NULL CHECK (json_valid(detail_json)),
  created_at INTEGER NOT NULL
) STRICT;
-- 不 FK 到 users：删除及备份恢复后仍须抑制恢复；到全部旧备份过期才清理。
CREATE TABLE deletion_records (
  user_id TEXT PRIMARY KEY,
  requested_at INTEGER NOT NULL,
  purge_after INTEGER NOT NULL,
  backups_expire_at INTEGER NOT NULL,
  completed_at INTEGER
) STRICT;

COMMIT;