inkwatch/deploy/migrate.sql

-- ==================== 幂等增量迁移（**每次启动都跑**）====================
--
-- ⚠️ 为什么要单独一个文件（2026-09-15 修的运维坑）────────────────────────
-- 迁移段原先只写在 `init.sql` 末尾，而 `init.sql` 挂在
-- `/docker-entrypoint-initdb.d/` —— **那个目录只在数据目录为空时才执行**。
-- 也就是说：**已有库上，这段代码从来没跑过**，全靠人手敲 SQL。
--
-- 后果不是"少个字段"，而是**静默的链路中断**：新增 `devices.hardware_id` 后，
-- `registerDevice` 会写一个不存在的列 → SQL 报错 → 网关侧 catch 掉只记日志
-- （`[gateway] 设备落库失败`）→ **设备不再落库，而服务健康检查全绿**。
-- 与 `init.sql` 里那段"首次部署才走、结果云上全废"是同一个病：
-- 凡是"只在某种时序下执行"的代码，都必须有一个不受时序影响的执行点。
--
-- 故：本文件是**所有 schema 变更的唯一落点**，
--   · 全新库：`init.sql` 末尾 `\i /migrate.sql` 走一遍；
--   · 已有库：compose 里的 `migrate` 一次性服务在 postgres healthy 后跑一遍。
-- 两条路径共用同一份定义（单一真相源），且全部语句幂等（可反复执行）。
--
-- ⚠️ 纪律：**新增任何列/索引/表，都加到这里**（而不是只改上面的 CREATE TABLE，
--    那只对全新库有效）。改完跑 `bash tests/run-all.sh` —— 
--    `schema-consistency-test.js` 会校验建表顺序与清单一致性。

-- ---- 指标列（骨架期只有 7 列，权威清单有 17 个字段，缺列的指标只能落在 payload）----
ALTER TABLE device_metrics ADD COLUMN IF NOT EXISTS mem_used_mb     DOUBLE PRECISION;
ALTER TABLE device_metrics ADD COLUMN IF NOT EXISTS mem_total_mb    DOUBLE PRECISION;
ALTER TABLE device_metrics ADD COLUMN IF NOT EXISTS disk_free_gb    DOUBLE PRECISION;
ALTER TABLE device_metrics ADD COLUMN IF NOT EXISTS disk_total_gb   DOUBLE PRECISION;
ALTER TABLE device_metrics ADD COLUMN IF NOT EXISTS disk_write_mbps DOUBLE PRECISION;
ALTER TABLE device_metrics ADD COLUMN IF NOT EXISTS battery_temp    DOUBLE PRECISION;

-- ---- 账号体系的凭据列（新表走 CREATE TABLE IF NOT EXISTS，已存在的库只需补列）----
ALTER TABLE users ADD COLUMN IF NOT EXISTS secret_hash       TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS secret_salt       TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS secret_updated_at TIMESTAMPTZ;
ALTER TABLE users ADD COLUMN IF NOT EXISTS disabled_at       TIMESTAMPTZ;

-- ---- 设备硬件指纹（2026-09-15）----
-- 用途见 `server/shared/device-identity.js`：让服务端能认出"这还是同一台机"，
-- 从而把同一台设备因 device_id 漂移产生的多条记录归并掉。
--
-- ⚠️ 存量行留 NULL，**不要**拿 device_id 回填：device_id 是**派生的、会变的值**，
--    拿它当"稳定指纹"等于把 bug 固化进数据。NULL 只是让它走"后缀兜底"判据。
ALTER TABLE devices ADD COLUMN IF NOT EXISTS hardware_id TEXT;
CREATE INDEX IF NOT EXISTS idx_devices_hardware ON devices(owner_id, hardware_id);


-- ── 日志与可观测性（2026-10-17）────────────────────────────────────
-- log_line_prefix：单容器里 5 个进程日志共用一个流，PG 默认格式没有标识，
--   排查时要靠猜。加 [postgres] 前缀后与 [gateway]/[business]/[supervisor] 对齐。
-- log_checkpoints=off：每 5 分钟两条 checkpoint 日志是纯噪音（默认 off，
--   某些镜像会开），关掉后 docker logs 里业务日志一眼可见。
-- log_min_duration_statement：**慢查询落日志**。不依赖 pg_stat_statements
--   （那要装扩展+重启），超过 200ms 的语句直接出现在日志里，是排查
--   "为什么这次请求慢"最省事的抓手。
ALTER SYSTEM SET log_line_prefix = '[postgres] %m [%p] ';
ALTER SYSTEM SET log_checkpoints = off;
ALTER SYSTEM SET log_min_duration_statement = 200;
SELECT pg_reload_conf();


-- ── 断线补传幂等：session_id 列（2026-10-25）────────────────────────
--
-- 背景见 init.sql 里 idx_metrics_device_seq 的注释：网关进程内的 seq 去重窗口
-- **重启即清空**，而客户端 session_id 不变、会重发最多 120 帧补传
-- → 全部被判成新 seq → 重复入库 → 历史回溯的均值被慢慢拉偏，且不报任何错。
--
-- ⚠️ 为什么**不在 DB 层建唯一索引**（2026-10-25 在生产形态上实测，勿再尝试）
--
--   `device_metrics` 在 TimescaleDB 分支上是 **hypertable**（实测 pg_inherits 里挂着
--   多个 `_timescaledb_internal._hyper_1_*_chunk`）。hypertable 按 `ts` 分区，
--   而 PostgreSQL 硬性要求：**分区表上的唯一索引必须包含分区键**。实测报错——
--     ERROR: cannot create a unique index without the column "ts"
--            (used in partitioning)
--
--   把 ts 加进唯一键同样不行：ts 由服务端改写，补传同一帧时 ts 不同，
--   唯一键就失去幂等语义 —— 骨架期那条注释「不加唯一约束因为 ts 会被改写」
--   想说的正是这一点，只是它把结论下成了「所以不能建」，
--   而正确结论是「所以唯一键不该含 ts，改由应用层承担」。
--
--   另注意：唯一索引这条路在**普通 postgres 镜像**上本来可行，
--   但两种镜像必须共用同一份 schema 定义（单一真相源），
--   不能一边建索引一边不建 —— 否则两套部署的幂等语义就分叉了。
--
--   → 幂等改由**网关侧持久化去重窗口**承担（见 gateway.js 的 `_seqWindow`），
--     `session_id` 是那个窗口 key 的组成部分。
--
-- 本列仍然有价值，不只是给去重当 key：
--   ① 区分「同一会话内重连补传」与「换了进程后的新流」—— 两者去重语义不同
--      （前者要判重，后者要重置窗口，否则新进程的 seq 从 0 开始会被全判成重复）；
--   ② 诊断时能看出一次 agent 进程生命周期的边界。
--
-- 两条执行路径（init.sql / 本文件）共用同一份列定义，语句幂等。

ALTER TABLE device_metrics ADD COLUMN IF NOT EXISTS session_id TEXT;


-- ══════════════════════════════════════════════════════════════════
-- 用户机制（2026-10-30）：生命周期 / 角色 / 一次性凭据 / 审计可见性
-- ══════════════════════════════════════════════════════════════════
--
-- 设计稿见本地 `inkwatch-docs-local/user-model-design-2026-10-30.md`（文档不入仓库）。
-- 本段与 init.sql 的对应定义**必须逐列相等**，由 schema-consistency-test 第 14 段锁定 ——
-- 否则又会重演"全新库与已有库两套 schema"的首次部署事故。

-- ---- 用户生命周期列 ----
ALTER TABLE users ADD COLUMN IF NOT EXISTS email_verified_at TIMESTAMPTZ;
ALTER TABLE users ADD COLUMN IF NOT EXISTS deleted_at        TIMESTAMPTZ;
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login_at     TIMESTAMPTZ;

-- ---- 会话：界面展示用的设备标签 + 吊销原因 ----
-- 两者都**不参与任何判定**：device_label 由客户端自报可伪造，revoked_reason 只日志用。
ALTER TABLE sessions ADD COLUMN IF NOT EXISTS device_label   TEXT;
ALTER TABLE sessions ADD COLUMN IF NOT EXISTS revoked_reason TEXT;
CREATE INDEX IF NOT EXISTS idx_sessions_user_active ON sessions(user_id, last_used_at DESC);

-- ---- 一次性凭据（验证邮箱 / 重置口令 / 邀请）----
CREATE TABLE IF NOT EXISTS auth_tokens (
    token_hash TEXT PRIMARY KEY,
    purpose    TEXT NOT NULL,
    subject    TEXT NOT NULL,
    payload    JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at TIMESTAMPTZ NOT NULL,
    used_at    TIMESTAMPTZ,
    attempts   SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_auth_tokens_subject ON auth_tokens(subject, purpose);

-- ---- 授权行升级为带角色 ----
-- 存量行一律回落 viewer：**默认最小权限**，不猜测历史意图。
ALTER TABLE device_grants ADD COLUMN IF NOT EXISTS role       TEXT NOT NULL DEFAULT 'viewer';
ALTER TABLE device_grants ADD COLUMN IF NOT EXISTS granted_by BIGINT;
ALTER TABLE device_grants ADD COLUMN IF NOT EXISTS expires_at TIMESTAMPTZ;
ALTER TABLE device_grants ADD COLUMN IF NOT EXISTS grantee_id BIGINT;

-- 数值型 account_id 回填（LEGACY 自报串如 `acct_xxx` 不是数字，留 NULL 走兼容通道）
-- ⚠️ 必须带 EXISTS：历史上可能残留指向已删账号的孤儿授权行，
--    直接 cast 回填会让下面的外键**在已有库上**失败（全新库反而看不出问题）。
UPDATE device_grants g
   SET grantee_id = g.account_id::bigint
 WHERE g.grantee_id IS NULL
   AND g.account_id ~ '^[0-9]+$'
   AND EXISTS (SELECT 1 FROM users u WHERE u.id = g.account_id::bigint);

CREATE INDEX IF NOT EXISTS idx_grants_grantee ON device_grants(grantee_id);

-- 外键用 DO 块做幂等（PostgreSQL 没有 ADD CONSTRAINT IF NOT EXISTS）
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'device_grants_grantee_fk'
  ) THEN
    ALTER TABLE device_grants
      ADD CONSTRAINT device_grants_grantee_fk
      FOREIGN KEY (grantee_id) REFERENCES users(id) ON DELETE CASCADE;
  END IF;
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'device_grants_granted_by_fk'
  ) THEN
    ALTER TABLE device_grants
      ADD CONSTRAINT device_grants_granted_by_fk
      FOREIGN KEY (granted_by) REFERENCES users(id) ON DELETE SET NULL;
  END IF;
END $$;

-- ---- 审计：按设备查（"谁看过我的设备"）----
CREATE INDEX IF NOT EXISTS idx_audit_device ON audit_log(device_id, created_at DESC);

-- ---- 审计：按时间查 / 按保留期清理 ----
-- init.sql 里一直有这一条，**migrate.sql 里没有** —— 也就是说：
-- 建库时跑过 init.sql 的实例有它，而从旧版本 migrate 上来的实例没有。
-- 保留期清理（audit-prune）是按 created_at 范围删的，缺这条索引就是全表扫；
-- 管理端的审计列表也是按 created_at 倒序分页。补上，两条路径对齐。
CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_log(created_at DESC);


-- ═══════════════════════════════════════════════════════════════════════
-- 实例初始化配置（2026-10-30，第 27 轮）
--
-- 为什么要有这张表：在此之前"这套部署是谁的、允许谁来注册、免费几台设备"
-- 全靠部署时手敲环境变量，敲漏一个就静默降级（默认值往往是最宽松那档）。
-- 且它们**没有任何落库痕迹** —— 半年后没人能回答"这台机器当初是怎么配的"。
--
-- 三条设计约束：
--   ① **单行表**（CHECK id = 1）。允许两行就等于允许两份真相，
--      而这正是本仓库复发过 6 次的病（见 README「单一真相源」）。
--   ② **密钥一律不入库**。DB 密码、SMTP 口令、ADMIN_TOKEN 都留在 env ——
--      库会被备份、会被导出、会被我自己的管理端读出来，塞进去等于扩大暴露面。
--   ③ **bootstrap_state 三态**，不是布尔。因为"全新库要初始化"与
--      "存量库升级"必须走不同路径：前者该挡在门外，后者断服就是事故。
--        pending —— 全新库，尚未初始化：业务端点一律 503，只放 /setup
--        adopted —— 存量库升级上来：按默认值继续跑，管理端提示"可补建管理员"
--        ready   —— 已走完向导
-- ═══════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS instance_config (
    id                   SMALLINT PRIMARY KEY DEFAULT 1,
    instance_name        TEXT        NOT NULL DEFAULT '墨观',
    public_url           TEXT,                       -- 邮件/邀请链接里的对外地址，可空
    allow_open_register  BOOLEAN     NOT NULL DEFAULT FALSE,
    require_auth         BOOLEAN     NOT NULL DEFAULT FALSE,
    require_device_token BOOLEAN     NOT NULL DEFAULT FALSE,
    enforce_device_quota BOOLEAN     NOT NULL DEFAULT TRUE,
    free_device_limit    INTEGER     NOT NULL DEFAULT 3,
    relay_max_bytes      BIGINT      NOT NULL DEFAULT 1048576,
    mail_mode            TEXT        NOT NULL DEFAULT 'console',
    mail_from            TEXT,
    audit_retention_days INTEGER,                    -- NULL = 永久保留
    bootstrap_state      TEXT        NOT NULL DEFAULT 'pending',
    initialized_at       TIMESTAMPTZ,
    initialized_by       TEXT,                       -- 初始化时的来源 IP（审计用，非身份）
    revision             INTEGER     NOT NULL DEFAULT 1,
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT instance_config_single_row CHECK (id = 1),
    CONSTRAINT instance_config_state_chk
        CHECK (bootstrap_state IN ('pending', 'adopted', 'ready')),
    -- 名称进的是页面标题与邮件抬头，限长防"塞一段 HTML 进去"
    CONSTRAINT instance_config_name_chk
        CHECK (length(btrim(instance_name)) BETWEEN 1 AND 64),
    CONSTRAINT instance_config_free_chk
        CHECK (free_device_limit BETWEEN 0 AND 10000),
    -- 上限 2 GiB：与中继的硬上限一致，防把库里的值改成 1TB 再被 OOM 打穿
    CONSTRAINT instance_config_relay_chk
        CHECK (relay_max_bytes BETWEEN 1024 AND 2147483648)
);

-- 管理员标识：与终端用户同表，只多一个布尔位。
-- 不做独立 admin_users 表 —— 那会带来两套口令策略、两套会话、两套审计主体，
-- 而自托管场景的运营者本来就是"一个持有设备的人"。
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_admin BOOLEAN NOT NULL DEFAULT FALSE;

-- 部分索引：管理员是极少数，全表扫描也该很快，但管理端每次请求都要查它
CREATE INDEX IF NOT EXISTS idx_users_admin ON users (is_admin) WHERE is_admin;

-- 落首行。**判据是"库里有没有用户"，不是"迁移跑了第几次"** ——
-- 存量库升级上来时 users 非空 → adopted，直接按默认值继续服务，不断服。
-- 全新库 users 为空 → pending，业务端点在初始化前一律 503。
-- 已存在则不动（幂等）：初始化完成后再跑迁移不会把 ready 打回 pending。
INSERT INTO instance_config (id, bootstrap_state)
SELECT 1, CASE WHEN EXISTS (SELECT 1 FROM users) THEN 'adopted' ELSE 'pending' END
ON CONFLICT (id) DO NOTHING;


-- ═══════════════════════════════════════════════════════════════════════
-- v2 / M2：升档申请（`plan_requests`）
--
-- 正文唯一来源是 `docs/migrations/v2.sql`（评审用），此处为**合入副本**，
-- 两份必须逐字一致。**严禁**再往 `init.sql` 里写一遍这两张表：`init.sql`
-- 末尾已有 `\i /migrate.sql`，全新库与已有库自动共用这一份定义，
-- 而双份真相源是本仓库最贵的教训（README 记 6 次）。
--
-- 与 SPEC §6.1 的一处**偏离**（`account_id TEXT` → `BIGINT`）：`users.id` 是
-- BIGSERIAL，text 与 bigint 之间没有相等算子，建外键会直接报
--   ERROR: foreign key constraint cannot be implemented
--          Key columns are of incompatible types: text and bigint
-- （本机 PG 16 实测）。保留 ON DELETE CASCADE：删号必须级联清掉挂在它上面的
-- 申请，否则留下指向不存在账号的孤儿申请。
--
-- 与 `subscriptions` 表无关：后者是支付订单的空壳（有表结构、有读取、全仓零写入），
-- v2 不写它，也不删它（管理台把它显示成"本期未启用"，消除界面说谎）。
-- ═══════════════════════════════════════════════════════════════════════

-- 状态机只有三态（pending / approved / rejected），由 CHECK 兜住 ——
-- 不用枚举类型：加一个状态要 ALTER TYPE，而 CHECK 改起来是一次纯文本迁移。
-- ⚠️ 加第四态时必须同步改下面 uq_plan_requests_pending 的谓词。
CREATE TABLE IF NOT EXISTS plan_requests (
    id            BIGSERIAL PRIMARY KEY,
    account_id    BIGINT      NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    from_plan     TEXT        NOT NULL,   -- 提交时刻的档位快照，之后 users.plan 变化不回溯
    to_plan       TEXT        NOT NULL,
    reason        TEXT,                   -- 申请人自述（可空，≤500 字符由应用层截断）
    status        TEXT        NOT NULL DEFAULT 'pending'
                  CHECK (status IN ('pending', 'approved', 'rejected')),
    note          TEXT,                   -- 审批备注
    decided_by    TEXT,                   -- 审批人（ADMIN_NAME / 管理员邮箱 / 应急令牌标识）
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    decided_at    TIMESTAMPTZ
);

-- 审批队列的查询路径：`WHERE status = $1 ORDER BY created_at DESC, id DESC`
CREATE INDEX IF NOT EXISTS idx_plan_requests_status
    ON plan_requests(status, created_at DESC);

-- 用户侧"我的申请"（倒序取前 20）与管理端按账号追溯
CREATE INDEX IF NOT EXISTS idx_plan_requests_account
    ON plan_requests(account_id, created_at DESC);

-- **同一账号同时只允许一条 pending**（AC-06，防连点刷单）。
-- 这是并发下唯一可靠的防线：应用层"先 SELECT 再 INSERT"在两次点击之间会漏
-- （本机已验证：正确谓词下第二条 pending 被数据库拒绝，SQLSTATE 23505）。
-- 实现侧必须捕获 23505 并返回 409 E_REQUEST_PENDING，不要把 409 交给前端猜。
--
-- 注意：部分唯一索引的谓词必须与业务语义逐字一致（`status = 'pending'`）。
--    若日后新增一种"未终态"状态，这条索引**不会**自动覆盖它 ——
--    必须同步改这里，否则防连点失效且没有报错。
CREATE UNIQUE INDEX IF NOT EXISTS uq_plan_requests_pending
    ON plan_requests(account_id) WHERE status = 'pending';


-- ══════════════════════════════════════════════════════════════════════
-- M5 埋点（v2）。5 个事件白名单在**服务端**（`shared/events.js`，权威是
-- SPEC §6.1.1），表上**刻意不设** `name` 的 CHECK：设了就是白名单两处真相，
-- 而且写入失败会被 AC-22 吞成 202 —— 没人会知道。
--
-- `account_id` 用 BIGINT 而不是 SPEC §6.1 原写的 TEXT：
--   · `users.id` 是 BIGSERIAL，TEXT 会让"按账号算率"这一步每次都 ::bigint 强转，
--     转不动时（LEGACY 自报串 `acct_xxx`）只能静默留 NULL —— 而 NULL 与"匿名上报"
--     在表里长得一模一样，事后分不开。同一处错误上一版在 `plan_requests` 上已经犯过。
--   · 可空是必须的：匿名设备也允许上报（端点认证可选）。
--   · `ON DELETE SET NULL`：账号注销后**保留计数、解除关联**。选 CASCADE 会让
--     历史漏斗在注销那一刻缩水（分母变了，率就不可比）；选 SET NULL 则行还在、
--     但已经反查不到人 —— 这正是隐私边界（SPEC §10）想要的形状。
-- `device_id` 不做外键：设备被清掉不该导致埋点写不进（AC-22 的方向）。
CREATE TABLE IF NOT EXISTS events (
    id          BIGSERIAL PRIMARY KEY,
    account_id  BIGINT REFERENCES users(id) ON DELETE SET NULL,
    device_id   TEXT,
    name        TEXT NOT NULL,
    props       JSONB,
    ts          TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 漏斗查询只有两种形状：按事件名倒序取最近 N、按时间窗聚合计数
CREATE INDEX IF NOT EXISTS idx_events_name_ts ON events(name, ts DESC);
CREATE INDEX IF NOT EXISTS idx_events_ts ON events(ts DESC);