ad7fba6021
- core：配置（prod 拒绝默认密钥 / dev 短信 / 假模型）、租户事务 + 行级安全策略、 problem+json 错误码、信封加密与签名下载、对象存储、事务内入队、Valkey stream 事件流、权限点 - identity：手机号 HMAC 存储、注册、登录（挤下线需确认，API3）、服务端会话 - billing：额度流水只追加，预扣 / 结算 / 退回，一笔预扣只有一个结局 - document：分片上传、按文件头识别格式、Gotenberg 转换、文字层与稳定句子编号 - checklist：标注规则识别、模型抽取、三道校验、标注类条款规则兜底、重跑对齐 - ai：提示词注册表（extract.substantive v1 来自 S4 v2）、假模型、OpenAI 兼容供应商、ai_call 流水 - 迁移：data-model 基线、套餐、Procrastinate、会话状态函数 - 测试 30 个：租户隔离、额度、失败退回、对象加密、契约一致性 - Compose（含 migrate）、镜像、CI Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
77 lines
3.8 KiB
SQL
77 lines
3.8 KiB
SQL
-- 1. identity：人、登录方式、企业主体、成员身份、会话（架构 §3.14）
|
||
-- 来源：docs/architecture/schema-draft.sql（2026-11-03 验证过的草案）
|
||
CREATE TABLE tenant (
|
||
id uuid PRIMARY KEY,
|
||
name text NOT NULL,
|
||
uscc text, -- 统一社会信用代码，MVP 可空（G9）
|
||
status text NOT NULL DEFAULT 'active'
|
||
CHECK (status IN ('active', 'suspended', 'closed')),
|
||
closed_at timestamptz, -- 注销：业务数据删除，本行保留给订单和发票
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
updated_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
|
||
-- 全局表：不带 tenant_id，不受 RLS 约束，只有 identity 模块能读写
|
||
CREATE TABLE user_account (
|
||
id uuid PRIMARY KEY,
|
||
display_name text NOT NULL,
|
||
status text NOT NULL DEFAULT 'active'
|
||
CHECK (status IN ('active', 'disabled', 'anonymized')),
|
||
anonymized_at timestamptz, -- 注销账号：抹掉个人信息，行保留（data-model §6.2）
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
updated_at timestamptz NOT NULL DEFAULT now()
|
||
);
|
||
|
||
CREATE TABLE login_identity (
|
||
id uuid PRIMARY KEY,
|
||
user_id uuid NOT NULL REFERENCES user_account (id),
|
||
provider text NOT NULL
|
||
CHECK (provider IN ('phone', 'password', 'feishu', 'wecom', 'oidc', 'ldap')),
|
||
external_org_id text NOT NULL DEFAULT '', -- 手机号、账号密码：空串
|
||
external_user_id text NOT NULL, -- 手机号：HMAC(pepper, E.164 号码)，不存明文（DM5）
|
||
contact_cipher bytea, -- 手机号密文，发短信和运营查询用
|
||
contact_hint text, -- 138****5678
|
||
secret_hash text, -- 只有 password 来源用（argon2id）
|
||
verified_at timestamptz,
|
||
last_login_at timestamptz,
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
UNIQUE (provider, external_org_id, external_user_id)
|
||
);
|
||
CREATE INDEX ON login_identity (user_id);
|
||
|
||
CREATE TABLE membership (
|
||
id uuid PRIMARY KEY,
|
||
tenant_id uuid NOT NULL REFERENCES tenant (id),
|
||
user_id uuid NOT NULL REFERENCES user_account (id),
|
||
role text NOT NULL DEFAULT 'owner'
|
||
CHECK (role IN ('owner', 'admin', 'member')), -- MVP 只有 owner
|
||
status text NOT NULL DEFAULT 'active'
|
||
CHECK (status IN ('invited', 'active', 'disabled', 'left')),
|
||
joined_at timestamptz NOT NULL DEFAULT now(),
|
||
left_at timestamptz,
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
UNIQUE (tenant_id, id),
|
||
UNIQUE (tenant_id, user_id)
|
||
);
|
||
CREATE INDEX ON membership (user_id);
|
||
|
||
CREATE TABLE user_session (
|
||
id uuid PRIMARY KEY,
|
||
tenant_id uuid NOT NULL,
|
||
membership_id uuid NOT NULL,
|
||
user_id uuid NOT NULL REFERENCES user_account (id),
|
||
token_hash bytea NOT NULL UNIQUE, -- SHA-256(令牌)，令牌本身不落库（AR8）
|
||
client_kind text NOT NULL CHECK (client_kind IN ('web', 'desktop')),
|
||
user_agent text,
|
||
ip inet,
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
last_seen_at timestamptz NOT NULL DEFAULT now(), -- 最多每 5 分钟写一次
|
||
expires_at timestamptz NOT NULL,
|
||
revoked_at timestamptz,
|
||
revoke_reason text CHECK (revoke_reason IN ('logout', 'concurrent_login', 'admin', 'tenant_closed', 'expired')),
|
||
FOREIGN KEY (tenant_id, membership_id) REFERENCES membership (tenant_id, id)
|
||
);
|
||
CREATE INDEX ON user_session (user_id) WHERE revoked_at IS NULL;
|