inkwatch/deploy/init.sql

-- 墨观 元数据 + 时序初始化（PostgreSQL，TimescaleDB 为可选加速）
-- 见方案 V4.0 第 10 章：元数据层 + 时序历史层同库
--
-- ⚠️ 本脚本必须同时能在"有 TimescaleDB"与"普通 PostgreSQL"上跑完
--   - 生产：`timescale/timescaledb` 镜像 → 启用超表分区 + 自动保留 + 连续聚合
--   - 本地/CI：`postgres:15-alpine`（compose 用 POSTGRES_IMAGE 切换）→ 上述三项跳过，
--     表结构与指标列**完全一致**，仅存储优化缺失，功能测试与开发不受影响
--   TimescaleDB 专有能力全部收敛在下方两个 DO 块内，缺扩展时只打印 NOTICE 不报错。
--   （不这么做的话，缺扩展会让 docker-entrypoint 的 ON_ERROR_STOP 直接中断建库。）

-- ==================== 元数据层 ====================

-- 账号（方案 6.4）。口令只存 scrypt 哈希 + 盐，绝不存明文；
-- 口令参数、令牌格式与 TTL 全部在 shared/accounts.js 定义，本文件不重复声明。
CREATE TABLE IF NOT EXISTS users (
    id                BIGSERIAL PRIMARY KEY,
    email             TEXT UNIQUE NOT NULL,
    display_name      TEXT,
    plan              TEXT NOT NULL DEFAULT 'free',   -- free | pro | team
    secret_hash       TEXT,        -- scrypt 哈希（hex）
    secret_salt       TEXT,        -- 每账号独立盐
    secret_updated_at TIMESTAMPTZ,
    disabled_at       TIMESTAMPTZ, -- 封禁账号（保留数据，登录直接拒绝）
    -- 邮箱验证：NULL = 未验证。未验证不是"不能用"，而是**降权**
    -- （限 1 台设备、不能邀请、不能导出），见 shared/permissions.js 的 PLAN_TABLE。
    email_verified_at TIMESTAMPTZ,
    -- 注销：NULL = 正常。非空 = 冷静期内（30 天），期内同邮箱登录可撤销。
    -- 绝不物理删除 users 行：devices.owner_id 是 ON DELETE SET NULL，
    -- 物理删会让设备在无人知情的情况下变成"无主"，而用户本意可能只是"这段不用了"。
    deleted_at        TIMESTAMPTZ,
    last_login_at     TIMESTAMPTZ,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 会话令牌（查看端 / 界面登录态）。**只存 sha256 哈希**：脱库也拿不到可用令牌。
-- 采用 opaque 令牌而非 JWT 的理由见 shared/accounts.js 文件头（可吊销优先）。
CREATE TABLE IF NOT EXISTS sessions (
    token_hash   TEXT PRIMARY KEY,
    user_id      BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_used_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at   TIMESTAMPTZ NOT NULL,        -- 滑动过期：每次使用顺延
    revoked_at   TIMESTAMPTZ,                 -- 登出/踢下线即标记，无需等 TTL
    user_agent   TEXT,
    -- 界面上给用户看的"这是哪台设备上的登录"。**仅显示用，不参与任何判定** ——
    -- 客户端自报，可伪造；拿它当身份依据等于请对方自证。
    device_label TEXT,
    revoked_reason TEXT                       -- logout | evicted | kicked | secret_changed | account_deleted
);
CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_user_active ON sessions(user_id, last_used_at DESC);

-- 一次性凭据：验证邮箱 / 重置口令 / 接受邀请。
-- **一张表统管三种用途**，而不是三张表三套 TTL —— 三套必然漂移（本仓库第六次教训）。
-- 只存 sha256 哈希：脱库拿不到可用凭据。
CREATE TABLE IF NOT EXISTS auth_tokens (
    token_hash TEXT PRIMARY KEY,
    purpose    TEXT NOT NULL,                 -- verify_email | reset_secret | invite
    subject    TEXT NOT NULL,                 -- verify/reset = 用户 id；invite = 目标邮箱
    payload    JSONB NOT NULL DEFAULT '{}'::jsonb,   -- invite: {device_id, role, inviter_id}
    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);

-- ⚠️ 建表顺序即执行顺序：**被引用的表必须先建**。
-- 真实事故（2026-09-15 首次部署到腾讯云时暴露）：device_tokens 原写在 devices 之前，
-- 它带 `REFERENCES devices(device_id)` → 全新库上 psql 直接报
-- `ERROR: relation "devices" does not exist` → entrypoint 因 ON_ERROR_STOP 中止 →
-- 容器重启后看到数据目录非空就**跳过初始化** → 结果库里只建了 users/sessions，
-- 其余表全缺，而容器健康检查是绿的（pg 能连上）——"部署成功但功能全废"。
-- 本地一直没暴露，是因为本地数据卷是很早就建好的（新表靠文件末尾的增量迁移段补），
-- 全新库这条路径从来没跑过。现由 tests/schema-consistency-test.js
-- 「建表顺序」段锁定：任何 CREATE TABLE 的 REFERENCES 都必须指向已定义的表。
CREATE TABLE IF NOT EXISTS devices (
    device_id     TEXT PRIMARY KEY,
    device_name   TEXT,
    platform      TEXT,
    owner_id      BIGINT REFERENCES users(id) ON DELETE SET NULL,
    capabilities  JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_seen_at  TIMESTAMPTZ,
    -- 稳定硬件指纹（2026-09-15 新增，见 shared/device-identity.js 文件头）：
    -- 客户端上报（Android = ANDROID_ID 完整值，桌面端 = 主机名）。
    -- 它**与 device_id 的派生规则无关** —— 正因如此，即使 device_id 变了
    -- （真事故：修机型显示名把 device_id 也改了），服务端仍能认出是同一台机。
    hardware_id   TEXT
);
CREATE INDEX IF NOT EXISTS idx_devices_owner ON devices(owner_id);
-- 归并判据①的查询路径：同 owner 名下按 hardware_id 找兄弟行
CREATE INDEX IF NOT EXISTS idx_devices_hardware ON devices(owner_id, hardware_id);

-- 设备令牌（采集端专用，方案 6.4「与查看会话权限分离，云端可吊销或轮换」）。
-- 采集端长期运行，故不设过期，只靠 revoked_at 吊销；轮换 = 发新的 + 吊销旧的。
-- 注意：必须排在 devices **之后**（见上方顺序说明）。
CREATE TABLE IF NOT EXISTS device_tokens (
    token_hash   TEXT PRIMARY KEY,
    device_id    TEXT NOT NULL REFERENCES devices(device_id) ON DELETE CASCADE,
    issued_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_seen_at TIMESTAMPTZ,
    revoked_at   TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_device_tokens_device ON device_tokens(device_id);

-- 授权关系：被查看设备主动授予查看账号
--
-- account_id 用 TEXT 且**仍是权威**：业务层账号 ID 可能是邮箱/第三方 openid，
-- 不保证数字；LEGACY 自报串也走这一列（兼容旧客户端）。
-- grantee_id 是给"已登录账号"用的强一致影子列 —— 唯一目的是让删号能级联，
-- 而不用在 TEXT 列上做不可靠的 cast。
CREATE TABLE IF NOT EXISTS device_grants (
    device_id   TEXT NOT NULL REFERENCES devices(device_id) ON DELETE CASCADE,
    account_id  TEXT NOT NULL,
    granted_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    -- viewer | manager | owner（取值集合见 shared/permissions.js 的 GRANT_ROLES）
    -- 默认 viewer：**新加的授权一律只读**，要写权限得显式提。
    role        TEXT NOT NULL DEFAULT 'viewer',
    granted_by  BIGINT REFERENCES users(id) ON DELETE SET NULL,
    expires_at  TIMESTAMPTZ,
    grantee_id  BIGINT REFERENCES users(id) ON DELETE CASCADE,
    PRIMARY KEY (device_id, account_id)
);
CREATE INDEX IF NOT EXISTS idx_grants_account ON device_grants(account_id);
CREATE INDEX IF NOT EXISTS idx_grants_grantee ON device_grants(grantee_id);

CREATE TABLE IF NOT EXISTS subscriptions (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    plan        TEXT NOT NULL,
    status      TEXT NOT NULL DEFAULT 'active',
    started_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at  TIMESTAMPTZ
);

-- 审计日志（配对尝试、令牌吊销、授权变更）
CREATE TABLE IF NOT EXISTS audit_log (
    id          BIGSERIAL PRIMARY KEY,
    event       TEXT NOT NULL,
    account_id  TEXT,
    device_id   TEXT,
    detail      JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_log(created_at DESC);
-- 按设备查「权限变更」审计（owner 可见）：谁被授过权限、谁转过归属、谁认领过、
-- 谁签发或吊销过令牌（另有管理端 admin_* 动作）。
-- **不是「谁看过我的设备」**：本表不记录"某账号在某时刻看了某设备"，那需要在每次
-- viewer:subscribe 时落一条访问日志（写放大 + 保留策略），是另一个功能、不在本表。
-- 权威源（本仓可点开）：server/shared/repository.js:1016 的 audit() 及其全部调用点；
--   本索引的查询方是 server/business/index.js:1883（→ repo.getDeviceAudit）。
-- 标准表述与裁定：项目文档 PRD-AS-IS.md 矛盾 18 / §9.7.2（该文档不在本仓）。
CREATE INDEX IF NOT EXISTS idx_audit_device ON audit_log(device_id, created_at DESC);

-- ==================== 时序历史层 ====================

-- 指标列与 shared/metrics-schema.js 的权威清单严格对齐
-- （metrics-schema 的 dbColumn 非空项必须在本表中存在同名列，
--   一致性由 tests/schema-consistency-test.js 强制校验）
-- 另存 payload 全量原文，后续新增指标无需改表（查询可走 JSONB）。
CREATE TABLE IF NOT EXISTS device_metrics (
    ts              TIMESTAMPTZ NOT NULL,
    device_id       TEXT NOT NULL,
    seq             BIGINT,
    -- 采集端进程的会话标识（客户端每次换进程就换一个，见 gateway.js 的 session.sessionId）。
    -- 断线补传的幂等判据是 (device_id, session_id, seq)：**不含 ts**，
    -- 因为 ts 由服务端改写，补传同一帧时 ts 会不同 —— 含 ts 的唯一键根本表达不了幂等。
    session_id      TEXT,
    -- CPU
    cpu_usage       DOUBLE PRECISION,   -- CPU 使用率 %
    cpu_temp        DOUBLE PRECISION,   -- CPU 温度 ℃（平台受限，多数为 NULL）
    -- 内存
    mem_usage       DOUBLE PRECISION,   -- 内存使用率 %
    mem_used_mb     DOUBLE PRECISION,   -- 已用内存 MB
    mem_total_mb    DOUBLE PRECISION,   -- 物理内存总量 MB
    -- GPU
    gpu_usage       DOUBLE PRECISION,   -- GPU 使用率 %（V1.0 未接入，恒 NULL）
    -- 磁盘
    disk_free_gb    DOUBLE PRECISION,   -- 可用空间合计 GB
    disk_total_gb   DOUBLE PRECISION,   -- 总容量合计 GB
    disk_read_mbps  DOUBLE PRECISION,   -- 磁盘读速率 MB/s（V1.0 未接入）
    disk_write_mbps DOUBLE PRECISION,   -- 磁盘写速率 MB/s（V1.0 未接入）
    -- 网络（口径 Mbps，Rust 侧由字节/秒换算后写入）
    net_rx_mbps     DOUBLE PRECISION,   -- 下行速率 Mbps
    net_tx_mbps     DOUBLE PRECISION,   -- 上行速率 Mbps
    -- 温度（增强项）
    battery_temp    DOUBLE PRECISION,   -- 电池温度 ℃（仅移动端可得）
    payload         JSONB               -- 全量原文（含 cpu_cores 数组等无列指标）
);

CREATE INDEX IF NOT EXISTS idx_metrics_device_ts ON device_metrics(device_id, ts DESC);

-- 断线补传幂等：**由网关侧的持久化去重窗口承担**（见 gateway.js 的 `_seqWindow`），
-- 不在 DB 层建唯一索引。
--
-- ⚠️ 为什么不能建唯一索引（2026-10-25 在真实部署形态上实测，勿再尝试）
--   `device_metrics` 在 TimescaleDB 分支上是 **hypertable**，按 `ts` 分区；
--   PostgreSQL 要求**分区表上的唯一索引必须包含分区键**，实测直接报
--     ERROR: cannot create a unique index without the column "ts"
--            (used in partitioning)
--   而把 ts 放进唯一键就丧失幂等语义 —— ts 由服务端改写，补传同一帧时 ts 不同。
--
-- ⚠️ 历史教训：此处曾写「不加唯一约束，因为 ts 会被服务端改写」——
--   推理对，但不能只停在「不可行」：少了去重防线，网关一重启窗口就清空，
--   客户端补传最多 120 帧全被判成新 seq → 重复入库，
--   历史回溯的均值被慢慢拉偏，且**不报任何错**。防线因此挪到应用层。
--
-- 下面的普通索引仍保留：历史查询与去重窗口的 key 都要按 device_id 过滤。
CREATE INDEX IF NOT EXISTS idx_metrics_device_seq ON device_metrics(device_id, seq);

-- ==================== TimescaleDB 可选加速（缺失即优雅降级）====================
-- 超表：按时间自动分区，支撑按保留期 drop chunk
-- 保留策略：free 24h / pro 7d / team 30d（生产按账号档位分别配置，此处取上限 30 天）
DO $inkwatch$
DECLARE
    has_ts boolean;
BEGIN
    SELECT EXISTS (SELECT 1 FROM pg_available_extensions WHERE name = 'timescaledb')
      INTO has_ts;

    IF has_ts THEN
        CREATE EXTENSION IF NOT EXISTS timescaledb;
        PERFORM create_hypertable('device_metrics', 'ts', if_not_exists => TRUE);
        PERFORM add_retention_policy('device_metrics', INTERVAL '30 days', if_not_exists => TRUE);
        RAISE NOTICE '墨观: TimescaleDB 已启用（超表 + 30 天自动保留）';
    ELSE
        RAISE NOTICE '墨观: 未检测到 TimescaleDB，跳过超表与自动保留策略（表结构与指标列已建全，功能不受影响）';
    END IF;
END
$inkwatch$;

-- 降采样：超过 1 天的数据按 10 秒粒度聚合（控制存储成本，见方案 10.3）
--
-- ⚠️ 两个分支的**指标列清单必须完全一致**，只允许分桶表达式不同：
--      TimescaleDB 用 time_bucket()，普通 PG 用 to_timestamp(floor(epoch/10)*10)
--    新增/改名指标时两处都要改，一致性由 tests/schema-consistency-test.js 校验（否则红灯）。
DO $inkwatch$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_available_extensions WHERE name = 'timescaledb') THEN
        EXECUTE $sql$
            CREATE MATERIALIZED VIEW IF NOT EXISTS device_metrics_10s
            WITH (timescaledb.continuous) AS
            SELECT
                time_bucket('10 seconds', ts) AS bucket,
                device_id,
                avg(cpu_usage)     AS cpu_usage,
                avg(cpu_temp)      AS cpu_temp,
                avg(mem_usage)     AS mem_usage,
                avg(mem_used_mb)   AS mem_used_mb,
                avg(mem_total_mb)  AS mem_total_mb,
                avg(gpu_usage)     AS gpu_usage,
                avg(disk_free_gb)  AS disk_free_gb,
                avg(disk_total_gb) AS disk_total_gb,
                avg(net_rx_mbps)   AS net_rx_mbps,
                avg(net_tx_mbps)   AS net_tx_mbps
            FROM device_metrics
            GROUP BY bucket, device_id
            WITH NO DATA
        $sql$;

        PERFORM add_continuous_aggregate_policy('device_metrics_10s',
            start_offset => INTERVAL '2 days',
            end_offset   => INTERVAL '1 hour',
            schedule_interval => INTERVAL '30 minutes',
            if_not_exists => TRUE);
        RAISE NOTICE '墨观: device_metrics_10s 已建为连续聚合（自动刷新）';
    ELSE
        EXECUTE $sql$
            CREATE MATERIALIZED VIEW IF NOT EXISTS device_metrics_10s AS
            SELECT
                to_timestamp(floor(extract(epoch FROM ts) / 10) * 10) AS bucket,
                device_id,
                avg(cpu_usage)     AS cpu_usage,
                avg(cpu_temp)      AS cpu_temp,
                avg(mem_usage)     AS mem_usage,
                avg(mem_used_mb)   AS mem_used_mb,
                avg(mem_total_mb)  AS mem_total_mb,
                avg(gpu_usage)     AS gpu_usage,
                avg(disk_free_gb)  AS disk_free_gb,
                avg(disk_total_gb) AS disk_total_gb,
                avg(net_rx_mbps)   AS net_rx_mbps,
                avg(net_tx_mbps)   AS net_tx_mbps
            FROM device_metrics
            GROUP BY 1, device_id
            WITH NO DATA
        $sql$;
        RAISE NOTICE '墨观: device_metrics_10s 已建为普通物化视图（无自动刷新，需手动 REFRESH）';
    END IF;
END
$inkwatch$;


-- ==================== 增量迁移（已有库升级用）====================
-- ⚠️ 迁移语句**不写在这里**，全部集中在 `migrate.sql`（单一真相源）。
-- 原因：本文件挂在 /docker-entrypoint-initdb.d/，**只在数据目录为空时执行** ——
--       写在这里的迁移在已有库上永远不会跑（2026-09-15 的运维坑，详见 migrate.sql 头注释）。
-- 下面这一行让**全新库**也走同一份迁移定义，使两条路径完全一致。
\i /migrate.sql

-- 连续聚合视图定义变更后需重建才生效（IF NOT EXISTS 不会更新既有定义）
-- 注意：删除连续聚合会一并丢弃其历史聚合数据，重跑后按 policy 重新物化。
-- DROP MATERIALIZED VIEW IF EXISTS device_metrics_10s;


-- ── 日志与可观测性（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();