DIP1 Schema 数据库详细设计¶

文档编号：DOC-D01-DB / 简称：DIP1-SCHEMA 版本：V1.1 · 对应 Alembic 迁移 revision 0001_initial_schema + 0002_cst_mobile_push 创建日期：2026-08-09 最近更新：2026-08-09 数据库：PostgreSQL 16.3-alpine（生产 RDS PG 16；开发 docker-compose postgres:16-alpine）约定：主键全部 ULID 26 字符（前缀 ORD/QTE/WKR/CUS/CAT/BLD）；ENUM 类型在 DIP1-SPEC §1.4 唯一真值来源；所有表软删除 deleted_at；行级策略（RLS）§5 明确定义；索引前缀规则 idx_<table>_<col> / 唯一 uk_<table>_<cols>

V1.1 变更：① 新增 4 表（cst_sessions 客户端会话 / cst_push_preferences 推送偏好 / mobile_push_tokens Push Token 注册 / push_delivery_logs 推送送达日志）；② 微调 2 表（cpt_orders 新增 lead_source 字段、mds_cpl_customers 新增 referral_code 字段）；③ 新增 5 枚举（cst_lead_source / push_code_8 / mobile_platform_2 / app_type_2 / push_delivery_status_4）；④ 新增 2 条 RLS 策略（cst_sessions 客户端只能看自己 / cst_push_preferences 同上）；⑤ Alembic 新增 0002 迁移脚本；⑥ 表总数 18 → 22。

一、ER 总图（22 表关系总览，V1.0 18 表 + V1.1 新增 4 表）¶

erDiagram
    %% 1. 基础：系统用户 + 角色权限（RBAC 6 角色 + 21 权限）
    sys_users ||--o{ sys_user_roles : "user_id"
    sys_roles ||--o{ sys_user_roles : "role_id"
    sys_roles ||--o{ sys_role_permissions : "role_id"

    %% 2. MDS 四库（12 主表中 4 张）
    mds_scl_categories ||--o{ mds_scl_sub_categories : "category_id"
    mds_bpl_buildings }o--|| mds_scl_categories : "n/a"
    mds_wpl_workers {
        ulid worker_id PK
        varchar level "L1/L2/L3"
        jsonb skill_matrix "8 cat × 5 proficiency"
    }
    mds_cpl_customers {
        ulid customer_id PK
        varchar name
        tsvector search_vector "姓名+联系方式 GIN 索引"
    }

    %% 3. TSMM 标签体系
    mds_tsmm_tags ||--o{ mds_entity_tags : "tag_id"
    mds_cpl_customers ||--o{ mds_entity_tags : "customer_id"
    mds_wpl_workers ||--o{ mds_entity_tags : "worker_id"
    mds_bpl_buildings ||--o{ mds_entity_tags : "building_id"
    mds_scl_categories ||--o{ mds_entity_tags : "category_id"

    %% 4. QSV 报价服务
    qsv_param_versions ||--o{ qsv_quotes : "ver_id"
    mds_scl_categories ||--o{ qsv_quotes : "category_id"
    mds_wpl_workers ||--o{ qsv_quotes : "worker_level_ref (tag)"
    mds_cpl_customers ||--o{ qsv_quotes : "customer_id"
    cpt_orders ||--o{ qsv_quotes : "order_id"

    %% 5. CPT 订单跟踪（核心）
    mds_cpl_customers ||--o{ cpt_orders : "customer_id"
    mds_scl_categories ||--o{ cpt_orders : "category_id"
    mds_bpl_buildings ||--o{ cpt_orders : "building_id"
    mds_wpl_workers ||--o{ cpt_orders : "assigned_worker_id"
    sys_users ||--o{ cpt_orders : "follow_up_id (OL)"
    cpt_orders ||--|{ cpt_order_stages : "order_id"
    cpt_orders ||--o{ cpt_order_stage_data_l6 : "order_id (1:1 有 L6)"
    cpt_orders ||--o{ cpt_order_stage_data_l7 : "order_id (1:1 有 L7)"
    cpt_orders ||--o{ cpt_order_stage_data_l8 : "order_id (1:1 有 L8)"
    cpt_orders ||--o{ cpt_order_attachments : "order_id"
    cpt_orders ||--o{ cpt_order_exceptions : "order_id"

    %% 6. OPS 线索池 + 推荐 + 奖励 + WOM 口碑
    mds_cpl_customers ||--o{ ops_leads : "customer_id (如果转化后填入)"
    mds_cpl_customers ||--o{ ops_referral_edges : "from_customer_id"
    mds_cpl_customers ||--o{ ops_referral_edges : "to_customer_id"
    cpt_orders ||--o{ ops_rewards : "new_order_id"
    mds_cpl_customers ||--o{ ops_rewards : "referrer_customer_id"
    cpt_order_stage_data_l8 ||--o{ ops_wom_candidates : "l8_id"

    %% 7. DSP 负载/请假
    mds_wpl_workers ||--o{ dsp_worker_leaves : "worker_id"

    %% 8. 审计 + 死信 + 飞书同步
    sys_users ||--o{ audit_logs : "user_id"

二、12 主表完整字段与约束说明（按业务域分组）¶

2.1 系统域：sys_users / sys_roles / sys_user_roles / sys_role_permissions（4 表 RBAC 基础）¶

2.1.1 `sys_users`（系统用户，运营后台 5 角色 + FL 师傅登录）¶

列名	类型	非空	约束	默认值	说明
user_id	CHAR(26)	✅	PK	gen_ulid('USR')	ULID，前缀 USR-
username	VARCHAR(32)	✅	UK 唯一		登录账号（ol_chengwen / fl_huang）
password_hash	VARCHAR(255)	✅			Argon2id（memory=64MB t=3 p=4）永不存明文
display_name	VARCHAR(64)	✅			真实姓名（曾总/郭总/司徒总/成文/黄总/洪哥等）
email	VARCHAR(128)		UK 唯一 NULLS NOT DISTINCT
worker_id	CHAR(26)		FK → mds_wpl_workers	NULL	仅 FL 角色必填，关联 WPL 档案
status	VARCHAR(16)	✅	ENUM	'ACTIVE'	ACTIVE / DISABLED / LOCKED_5FA / PWD_RESET
last_login_at	TIMESTAMPTZ			NULL
last_login_ip	INET			NULL
created_at	TIMESTAMPTZ	✅		now()
updated_at	TIMESTAMPTZ	✅		now()（TRIGGER 自动维护）
deleted_at	TIMESTAMPTZ		IDX 偏部分 WHERE deleted_at IS NULL	NULL	软删除，所有业务默认过滤

索引：

idx_sys_users_status (status) WHERE deleted_at IS NULL;
idx_sys_users_worker_id (worker_id) WHERE worker_id IS NOT NULL;
uk_sys_users_username UNIQUE (username) WHERE deleted_at IS NULL;   -- 软删除后可重建同名
uk_sys_users_email    UNIQUE (email)    WHERE deleted_at IS NULL AND email IS NOT NULL;

2.1.2 `sys_roles`（固定 6 角色不可删除，可自定义描述与权限开关）¶

列名	类型	非空	约束	默认
role_id	VARCHAR(16)	✅	PK	— 枚举：SPO/BO/TL/OL/ML/FL
name_zh	VARCHAR(32)	✅
description	VARCHAR(255)
is_builtin	BOOLEAN	✅		TRUE（6 角色 TRUE；自定义角色 FALSE MVP 暂不支持）
created_at	TIMESTAMPTZ	✅	now()

Seed 数据（V1.0 初始迁移必插，不可改 role_id）：

('SPO','项目负责人（曾总）','全局监控与审批', TRUE)
('BO', '业务负责人（郭总）','报价审批/业务仲裁', TRUE)
('TL', '技术负责人（司徒总）','全量权限/RBAC/Rls 配置', TRUE)
('OL', '运营负责人（成文+黄双人）','订单/客户/师傅/看板', TRUE)
('ML', '营销负责人（洪哥 BO 兼任）','线索/口碑/奖励', TRUE)
('FL', '一线师傅','师傅端 H5 个人单操作', TRUE)

2.1.3 `sys_user_roles`（用户 × 角色 N:M）¶

user_id (FK)	role_id (FK)	assigned_at	assigned_by
PK1	PK2	now()	user_id

约束：同一 user 同一 role 不能重复。

2.1.4 `sys_role_permissions`（role × permission_key BOOLEAN 开关）¶

列名	类型	说明
role_id	VARCHAR(16) FK	SPO/BO/TL/OL/ML/FL
permission_key	VARCHAR(64)	21 条 Key 固定，见 §3 矩阵
allowed	BOOLEAN 非空	TRUE/FALSE
updated_by	CHAR(26) FK → sys_users	最后修改人（TL）
updated_at	TIMESTAMPTZ	自动维护

PK = (role_id, permission_key)

2.2 MDS 主数据域（4 库 + TSMM = 7 表：mds_scl_categories / sub / mds_bpl_buildings / mds_wpl_workers / mds_cpl_customers / mds_tsmm_tags / mds_entity_tags）¶

2.2.1 `mds_scl_categories`（SCL 品类 8 大类）¶

列名	类型	约束	默认	说明
category_id	CHAR(26)	PK	gen_ulid('CAT')	CAT-
code	VARCHAR(32)	UK 枚举唯一		对齐 DIP1-SPEC §1.4.2：WARDROBE/KITCHEN_CABINET/BED/DINING_TABLE/BOOKSHELF/TV_CABINET/OFFICE_DESK/COMBINATION_FURNITURE
name_zh	VARCHAR(32)	✅		衣柜/橱柜...
base_fee_hkd	NUMERIC(10,2)	✅ ≥ 0	0
base_hours	NUMERIC(4,2)	✅ > 0		单位：小时
cap_price_hkd	NUMERIC(10,2)	✅ ≥ base_fee		封顶价保护
required_worker_level	VARCHAR(8)	✅ ENUM	L1	L1/L2/L3
display_order	SMALLINT			运营后台排序用
is_active	BOOLEAN		TRUE	OFFICE_DESK & COMBINATION MVP 先 FALSE
created_at / updated_at / deleted_at

2.2.2 `mds_scl_sub_categories`（子类目，MVP 仅 WARDROBE 插入 5 子类目）¶

sub_id	category_id FK	name	base_fee_delta_pct
ULID	PK2	2 门滑门衣柜	0.0（默认 0）

2.2.3 `mds_bpl_buildings`（楼宇档案 7 类）¶

列名	类型	约束	默认	说明
building_id	CHAR(26)	PK	gen_ulid('BLD')	BLD-
address	VARCHAR(512)	✅		全地址 + 门牌号（MVP 不含经纬度）
address_normalized_hash	CHAR(64)	UK		SHA256(address 繁体→简体去空格) 查重去重
district_hk_18	VARCHAR(16)			香港 18 区枚举（ISLANDS/KWAI_TSING/...）
building_type	VARCHAR(24)	✅ ENUM		PUBLIC_HOUSING/HOS/PRIVATE/VILLAGE_HOUSE/COMMERCIAL/INDUSTRIAL/OTHER
total_floors	SMALLINT	≥ 0	NULL
has_elevator	BOOLEAN		TRUE
floor_extra_rule	JSONB		{}	MVP 中 {"noElevatorFloorSurchargePct": {"default": 0.05, "cap_floor": 5}}，不同类型不同
parking_fee_hkd	NUMERIC(6,2)		NULL
cargo_elevator_tons_limit	NUMERIC(3,1)		NULL	INDUSTRIAL/COMMERCIAL 用
property_access_note	VARCHAR(512)		NULL
verified_by_worker_at	TIMESTAMPTZ		NULL	FL 现场核实时间
verified_by_worker_id	CHAR(26) FK → mds_wpl_workers		NULL
orders_count_lifetime	INTEGER	≥ 0	0
created_at / updated_at / deleted_at

索引：

GIN idx_bpl_address_trgm (address gin_trgm_ops);  -- 2 字符 autocomplete（需 pg_trgm 扩展）
idx_bpl_type (building_type);
idx_bpl_orders_count (orders_count_lifetime DESC) WHERE deleted_at IS NULL LIMIT 20;

2.2.4 `mds_wpl_workers`（师傅档案）¶

列名	类型	约束	默认	说明
worker_id	CHAR(26)	PK	gen_ulid('WKR')	WKR-
display_name	VARCHAR(32)	✅		FL 显示在卡片的姓名
phone	VARCHAR(32)	✅ UK (部分唯一见下)
whatsapp	VARCHAR(32)	UK	NULL
emergency_contact_name	VARCHAR(32)		NULL
emergency_contact_phone	VARCHAR(32)		NULL
level	VARCHAR(4)	✅ ENUM	L1	L1/L2/L3
status	VARCHAR(16)	✅ ENUM	'AVAILABLE'	AVAILABLE/BUSY/ON_LEAVE/SUSPENDED
joined_at	DATE	✅
skill_matrix	JSONB	✅	'{}'::jsonb	Key=category_code（8）/ Value=1-5 熟练度；如
can_do_sub_ids	VARCHAR(26)[]		'{}'	可处理的子类目 ID 数组
total_orders	INTEGER	≥ 0	0
avg_nps	NUMERIC(3,1)	CHECK 0-10	NULL	累计 ≥ 5 单才更新
monthly_target_hours	NUMERIC(5,1)		160.0	默认 160 小时/月
rating_maturity_flag	BOOLEAN		FALSE	累计 ≥ 5 单 TRUE 才对外展示平均
pay_settlement_bank_code	VARCHAR(16)		NULL	MVP 手填；不存卡号
pay_settlement_reference	VARCHAR(64)		NULL	FPS 手机号/识别码
feishu_union_id	VARCHAR(64) UK		NULL	未来飞书免登
created_at / updated_at / deleted_at

唯一/索引：

uk_wpl_phone UNIQUE (phone) WHERE deleted_at IS NULL;
idx_wpl_level_status (level, status);
idx_wpl_avg_nps (avg_nps DESC NULLS LAST);

2.2.5 `mds_cpl_customers`（客户档案）¶

列名	类型	约束	默认	说明
customer_id	CHAR(26)	PK	gen_ulid('CUS')	CUS-
name	VARCHAR(64)	✅
phone	VARCHAR(32)		UK (soft del)	NULL
whatsapp	VARCHAR(32)		UK	NULL
email	VARCHAR(128)		UK	NULL
district_hk_18	VARCHAR(16)		NULL
first_order_scene	VARCHAR(32)	ENUM 6 场景	NULL
referral_from_customer_id	CHAR(26) FK → mds_cpl_customers		NULL	自反：推荐链
total_orders	INTEGER	≥ 0	0
lifetime_value_hkd	NUMERIC(12,2)	≥ 0	0	累计成交金额
last_order_at	DATE		NULL
avg_nps	NUMERIC(3,1)		NULL
trust_badges	VARCHAR(32)[]		'{}'	LOYAL_3 / HIGH_RECOMMEND / VIP_BRAND_PARTNER 枚举
pdpo_consent_at	TIMESTAMPTZ		NULL	客户同意保留联系方式时间
pdpo_right_to_be_forgotten_requested_at	TIMESTAMPTZ		NULL	申请后：联系方式置 NULL + audit 留痕
search_vector	TSVECTOR			GIN 索引 name+phone+whatsapp
created_at / updated_at / deleted_at

索引：

GIN idx_cpl_search (search_vector);
uk_cpl_phone UNIQUE (phone) WHERE deleted_at IS NULL AND phone IS NOT NULL;
idx_cpl_ltv (lifetime_value_hkd DESC) WHERE deleted_at IS NULL;
idx_cpl_referral_from (referral_from_customer_id);  -- 推荐关系 2 层查询

2.2.6 `mds_tsmm_tags`（标签定义 3 层分类 × PDPO 隐私级别）¶

tag_id	name (zh)	entity (4 枚举)	tier (BASIC/ABILITY/CONTEXT)	pdpo_level (PUBLIC/INTERNAL/SENSITIVE)	is_active
ULID PK	UK 部分	UK 部分			BOOL

唯一约束：uk_tag_name_entity UNIQUE (name, entity) WHERE deleted_at IS NULL

2.2.7 `mds_entity_tags`（实体 × 标签，单表通用 4 实体，其中必有一个 FK 非空）¶

列名	类型	说明
entity_tag_id	BIGSERIAL PK	普通自增
tag_id	CHAR(26) FK → mds_tsmm_tags	必有
worker_id	CHAR(26) FK → mds_wpl_workers
customer_id	CHAR(26) FK → mds_cpl_customers
building_id	CHAR(26) FK → mds_bpl_buildings
category_id	CHAR(26) FK → mds_scl_categories
attached_by	CHAR(26) FK → sys_users	谁打标
attached_at	TIMESTAMPTZ	now()
expires_at	TIMESTAMPTZ	NULL = 永久

约束：CHECK ((worker_id IS NOT NULL)::int + (customer_id IS NOT NULL)::int + (building_id IS NOT NULL)::int + (category_id IS NOT NULL)::int = 1) —— 必须且仅命中 1 个实体。

2.3 QSV 报价域（2 表）¶

2.3.1 `qsv_param_versions`（参数版本，每 TL 生效一次 INSERT 1 条）¶

version_id (ULID PK V-)	version_code (V7/V8)	effective_from DATE	factor_payload JSONB 32 因子完整快照	created_by TL	created_at	note

2.3.2 `qsv_quotes`（报价单主表，状态机 DRAFT/CONFIRMED/REJECTED/EXPIRED）¶

列名	类型	约束	默认	说明
quote_id	CHAR(26)	PK	gen_ulid('QTE')	QTE-
order_id	CHAR(26) FK → cpt_orders	IDX	NULL	若为订单内报价则填入（可空：独立计算器场景）
customer_id	CHAR(26) FK → mds_cpl_customers	IDX ✅
category_id	CHAR(26) FK → mds_scl_categories	✅
building_id	CHAR(26) FK → mds_bpl_buildings		NULL
param_version_id	CHAR(26) FK → qsv_param_versions	✅		创建时刻快照，保证历史不改
channel_template_id	CHAR(26)		NULL	MVP 简化 VARCHAR；未来版本字典表
base_fee_hkd	NUMERIC(10,2)	✅ ≥ 0
factor_total	NUMERIC(5,4)	✅ 0.5-2.0		8 因子累乘值
addons_total_hkd	NUMERIC(10,2)	≥ 0	0
discount_total_hkd	NUMERIC(10,2)	≥ 0	0
subtotal_hkd	NUMERIC(10,2)	✅ ≥ 0
cap_price_hkd	NUMERIC(10,2)	✅		创建时的封顶价快照
final_hkd	NUMERIC(10,2)	✅ ≥ 0		MIN(subtotal, cap_price)
capped_flag	BOOLEAN	✅		TRUE 则 subtotal > cap
status	VARCHAR(16)	✅ ENUM IDX	'DRAFT'	DRAFT/CONFIRMED/REJECTED/EXPIRED
factor_breakdown	JSONB	✅	'{}'	8 因子明细（factor_key + value + impact）
calc_snapshot	JSONB	✅	'{}'	所有入参快照（客户改了楼宇也能回溯原值）
bo_approval_required	BOOLEAN	✅	FALSE	final≥2000 或 discount>15%
bo_approval_status	VARCHAR(16)	ENUM	NULL	PENDING/APPROVED/REJECTED
bo_approved_by	CHAR(26) FK → sys_users		NULL
bo_approved_at	TIMESTAMPTZ		NULL
confirmed_by	CHAR(26) FK → sys_users		NULL
confirmed_at	TIMESTAMPTZ		NULL
rejected_reason	VARCHAR(512)		NULL
valid_until	DATE	✅	created + 30 天	30 天有效期
export_pdf_object_key	VARCHAR(512)		NULL	R2 存储 Key
created_by	CHAR(26) FK	✅		OL
created_at / updated_at / deleted_at

2.4 CPT 订单跟踪域（1 主 + 1 阶段时间线 + 3 阶段数据表 + 附件 + 异常 = 7 表，合计 12 主表）¶

2.4.1 `cpt_orders`（订单主表 8 阶段）¶

列名	类型	约束	默认	说明
order_id	CHAR(26)	PK	gen_ulid('ORD')	ORD-
stage	VARCHAR(4)	✅ ENUM IDX	'L1'	L1..L8 / DONE
status	VARCHAR(20)	✅ ENUM IDX	'IN_PROGRESS'	IN_PROGRESS/COMPLETED/CANCELLED/EXCEPTION_PAUSED
lead_source_scene	VARCHAR(32)	✅ ENUM 8 场景 IDX		DIP1-SPEC §1.4.6
referral_from_customer_id	CHAR(26) FK → mds_cpl_customers	IDX	NULL	订单级推荐来源
lead_id	CHAR(26) FK → ops_leads		NULL	由 OPS 线索转化则有
customer_id	CHAR(26) FK → mds_cpl_customers	IDX	NULL	L2 时首次建立 CUS-
customer_name	VARCHAR(64)	✅		冗余字段（方便看板不 JOIN）
customer_phone	VARCHAR(32)	✅		原始值，展示层用 view cpt_orders_view 自动脱敏
customer_whatsapp	VARCHAR(32)			同上
category_id	CHAR(26) FK → mds_scl_categories	IDX	NULL	L2 录入
sub_category_id	CHAR(26) FK → mds_scl_sub_categories		NULL
product_model	VARCHAR(128)			宜家 PAX 3 门 250cm
product_params	JSONB		'{}'	尺寸/重量/材质字典（未来 AI 自动提取）
building_id	CHAR(26) FK → mds_bpl_buildings	IDX	NULL
address_detail	VARCHAR(256)			门牌补充；BPL + 这里才是完整地址
site_conditions	JSONB		'{}'	5 复选（电梯/货车位/停车/物业/楼道限制）+ 备注
install_entry_photo_attach_ids	CHAR(26)[]		'{}'	L2 门框/楼道照（附件 ID 数组）
customer_confirm_whatsapp_screenshot_ids	CHAR(26)[]		'{}'	L2 客户确认 WhatsApp 截图
expected_install_date	DATE	IDX	NULL	OL 期望安装日期（L2 填）
expected_install_slot	VARCHAR(8)	ENUM AM/PM/WEEKEND_PRIOR	NULL
latest_quote_id	CHAR(26) FK → qsv_quotes	UNIQUE NULLS NOT DISTINCT PARTIAL WHERE status=CONFIRMED	NULL	当前确认报价
signed_at	TIMESTAMPTZ	IDX	NULL	L4 签约时间
payment_method	VARCHAR(16)	ENUM 4 选 1	NULL	FPS/CREDIT_CARD/CASH/MONTHLY
payment_status	VARCHAR(16)	ENUM 4	NULL	UNPAID/PAID/PARTIAL/REFUNDED
down_payment_pct	NUMERIC(5,2)	0-100	NULL	MVP 0/50/100 手填
assigned_worker_id	CHAR(26) FK → mds_wpl_workers	IDX	NULL	DSP-001~6 派单
expected_start_at	TIMESTAMPTZ	IDX	NULL	OL+师傅协定的时间
delivery_method	VARCHAR(20)	ENUM 4	NULL	SELF_DELIVER/LOGISTICS/CUSTOMER_PICKUP/BRAND_DIRECT
logistics_tracking_no	VARCHAR(64)		NULL
delivery_status	VARCHAR(16)	ENUM 4	NULL	PENDING/IN_TRANSIT/RECEIVED/EXCEPTION
delivery_received_at	TIMESTAMPTZ		NULL
follow_up_id	CHAR(26) FK → sys_users OL	IDX	NULL	跟进人（RACI OL）
force_transition_flag	BOOLEAN	✅	FALSE	若 TRUE → 有 TL 强制跳阶段记录
last_exception_id	BIGINT FK → cpt_order_exceptions		NULL
created_by	CHAR(26) FK	✅		L1 创建人
created_at	TIMESTAMPTZ	✅ IDX DESC	now()
last_updated_at	TIMESTAMPTZ	✅ IDX	now()	TRIGGER 更新
completed_at	TIMESTAMPTZ		NULL	L8 保存后
deleted_at	TIMESTAMPTZ	偏索引	NULL

索引（MVP 重点性能）：

idx_cpt_pool_queries (stage, status, expected_install_date, follow_up_id, assigned_worker_id)
  WHERE deleted_at IS NULL;   -- 6 跟踪池看板主查询
idx_cpt_customer (customer_id, created_at DESC);
idx_cpt_worker (assigned_worker_id, stage);  -- FL RLS 仅看自己订单
idx_cpt_created_month (date_trunc('month', created_at));  -- 月度对账

2.4.2 `cpt_order_stages`（阶段时间线：每条订单每个阶段 1 行；L1/DONE/EXCEPTION 也算）¶

stage_id (BIGSERIAL PK)	order_id FK	stage	status (PENDING/IN_PROGRESS/DONE/SKIPPED)	entered_at	completed_at	operator_id FK sys_users	transition_reason	stage_meta JSONB (L1:联系次数/L3:报价数)

UK 部分：uk_order_stage UNIQUE (order_id, stage) WHERE NOT deleted

2.4.3 `cpt_order_stage_data_l6`（L6 安装记录 1:1）¶

order_id (PK FK)	departed_at	arrived_at	install_finished_at	rest_minutes	actual_hours_auto	actual_hours_override (NULL 用自动)	six_s_6_items JSONB(6 key → bool)	photo_entry_ids	photo_process_ids	photo_after_ids	install_video_ids

addons JSONB 数组 / exceptions JSONB 数组 / worker_signoff_note / created_at / updated_at

约束：CHECK (six_s_6_items ?& array['shoe_covers','floor_protection','workbench_tidy','trash_taken','product_clean','before_after_photos']) 6 Key 必须存在。

2.4.4 `cpt_order_stage_data_l7`（L7 验收 1:1）¶

order_id (PK FK)	result ENUM	rectify_items JSONB	signature_attachment_id FK → cpt_order_attachments (UK)	accepted_at	complaint_description

2.4.5 `cpt_order_stage_data_l8`（L8 回访 1:1，数据闭环起点）¶

l8_id BIGSERIAL PK	order_id FK UK	nps_score INT 0-10	dimensions JSONB 5 Key → bool	notes VARCHAR 2000	wom_consent_given BOOL	recorded_by	recorded_at

索引：idx_l8_nps_high (nps_score >= 9) 用于 WOM 候选自动入池。

2.4.6 `cpt_order_attachments`¶

attachment_id (CHAR(26) PK ATT-)	order_id FK	stage	category 8 枚举（ENTRY/INSTALL_PROCESS/AFTER/SIGNATURE/CONTRACT/DELIVERY_DAMAGE/EXCEPTION/OTHER）	r2_object_key VARCHAR(512) ✅ UK	original_filename	content_type	size_bytes	width_px / height_px (图片)	duration_sec (视频≤60s)	sha256 CHAR(64) UK	uploaded_by FK	uploaded_at

2.4.7 `cpt_order_exceptions`（异常池记录，每次异常 1 条）¶

| exception_id BIGSERIAL PK | order_id FK IDX | trigger_stage | level L1/L2/L3 | kind 10 枚举（PRODUCT_BROKEN/PART_MISSING/INSTALL_CONDITION/PROPERTY_BLOCK/RESCHEDULE/L6_INSTALL_DAMAGE/DELIVERY_DAMAGE/CUSTOMER_COMPLAINT/CANCELLED/OTHER） | description | reported_by | reported_at | resolved_at | resolution | returned_to_stage | bo_escalation_flag | cost_impact_hkd | audit_trail JSONB | |---|---|---|---|---|---|---|---|---|---|---|---|---|

三、辅助表（OPS / DSP / 审计 / DLQ / 飞书同步 10 表，含索引）¶

本节为 DIP1-SPEC 功能点对应落地： - OPS 线索 ops_leads；推荐边 ops_referral_edges；奖励 ops_rewards；口碑 ops_wom_candidates - DSP 请假 dsp_worker_leaves - 审计 audit_logs；DLQ sys_dlq_messages；飞书同步 2 表 feishu_sync_state / feishu_sync_idempotency - TSMM 已在 §2.2 列出。

三补、V1.1 新增表（CST 客户端 + 移动推送 4 表）¶

V1.1 新增：对应 DIP1-SPEC V2.0 §3.8 CST 客户端服务 + §3.9 移动认证 & 推送。Alembic 0002 迁移脚本实现本节 4 表。

3补.1 `cst_sessions`（客户端会话表，对应 F-CST-001 / F-MOB-001）¶

列名	类型	非空	约束	默认值	说明
session_id	CHAR(26)	✅	PK	gen_ulid('CSS')	会话 ULID
customer_id	CHAR(26)	✅	FK → mds_cpl_customers.customer_id		客户 ID
whatsapp	VARCHAR(32)	✅			登录用的 WhatsApp 号码
access_token_jti	VARCHAR(64)	✅	UK		access token JWT ID（用于黑名单）
refresh_token_hash	VARCHAR(255)	✅			refresh token 哈希（Argon2id，不存明文）
refresh_expires_at	TIMESTAMPTZ	✅			refresh token 过期时间（30 天）
biometric_binding	JSONB			NULL	生物识别绑定信息（device_id / public_key / bound_at）
otp_session_id	VARCHAR(64)			NULL	最近一次 OTP 会话 ID（用于审计）
last_login_at	TIMESTAMPTZ	✅		now()	最近登录时间
last_login_ip	INET			NULL	最近登录 IP
user_agent	TEXT			NULL	客户端 User-Agent（App 版本/设备型号）
revoked_at	TIMESTAMPTZ			NULL	会话撤销时间（登出/被踢）
created_at	TIMESTAMPTZ	✅		now()
updated_at	TIMESTAMPTZ	✅		now()	触发器自动维护

索引： - uk_cst_sessions_access_jti UNIQUE (access_token_jti) - idx_cst_sessions_customer (customer_id, revoked_at) - idx_cst_sessions_refresh_expiry (refresh_expires_at) WHERE revoked_at IS NULL

3补.2 `cst_push_preferences`（客户端推送偏好表，对应 F-CST-017）¶

列名	类型	非空	约束	默认值	说明
preference_id	CHAR(26)	✅	PK	gen_ulid('CPP')
customer_id	CHAR(26)	✅	FK → mds_cpl_customers.customer_id, UK		一客户一行
push_new_order	BOOLEAN	✅		TRUE	仅 WKR 端使用，CST 默认 TRUE 但不触发
push_order_reminder_1h	BOOLEAN	✅		TRUE	安装前 1 小时提醒
push_order_status	BOOLEAN	✅		TRUE	订单阶段流转通知
push_worker_departed	BOOLEAN	✅		TRUE	师傅出发通知
push_review_invite	BOOLEAN	✅		TRUE	L8 评价邀请
push_referral_reward	BOOLEAN	✅		TRUE	推荐奖励到账
push_quote_ready	BOOLEAN	✅		TRUE	报价已生成
push_system	BOOLEAN	✅		TRUE	系统通知
created_at	TIMESTAMPTZ	✅		now()
updated_at	TIMESTAMPTZ	✅		now()

索引： - uk_cst_push_preferences_customer UNIQUE (customer_id)

3补.3 `mobile_push_tokens`（移动端 Push Token 注册表，对应 F-MOB-002，WKR + CST 双端共用）¶

列名	类型	非空	约束	默认值	说明
token_id	CHAR(26)	✅	PK	gen_ulid('MPT')
user_id	CHAR(26)	✅			关联 sys_users.user_id 或 mds_cpl_customers.customer_id
user_type	app_enum__app_type_2	✅			WKR=师傅 / CST=客户
push_token	VARCHAR(255)	✅			ExpoPushToken（ExponentPushToken[xxxxxx]）
platform	app_enum__mobile_platform_2	✅			IOS / ANDROID
app_version	VARCHAR(32)			NULL	App 版本号（如 1.0.0）
device_id	VARCHAR(128)			NULL	设备唯一标识
is_active	BOOLEAN	✅		TRUE	是否活跃（登出/卸载后置 FALSE）
last_seen_at	TIMESTAMPTZ	✅		now()	最近活跃时间
created_at	TIMESTAMPTZ	✅		now()
updated_at	TIMESTAMPTZ	✅		now()

索引： - uk_mobile_push_tokens_token UNIQUE (push_token) - idx_mobile_push_tokens_user (user_id, user_type, is_active)

3补.4 `push_delivery_logs`（推送送达日志表，对应 NFR-18 送达率统计）¶

列名	类型	非空	约束	默认值	说明
log_id	CHAR(26)	✅	PK	gen_ulid('PDL')
token_id	CHAR(26)	✅	FK → mobile_push_tokens.token_id		关联 Push Token
push_code	app_enum__push_code_8	✅			8 类推送模板之一
payload	JSONB	✅			推送内容参数（模板变量）
expo_ticket_id	VARCHAR(128)			NULL	Expo 推送返回的 ticket ID
delivery_status	app_enum__push_delivery_status_4	✅		'PENDING'	PENDING / SENT / FAILED / DELIVERED
error_message	TEXT			NULL	失败原因（如 DeviceNotRegistered / InvalidCredentials）
sent_at	TIMESTAMPTZ			NULL	Expo API 调用时间
delivered_at	TIMESTAMPTZ			NULL	APNs/FCM 回执送达时间（若开启回执）
created_at	TIMESTAMPTZ	✅		now()

索引： - idx_push_delivery_logs_token (token_id, created_at DESC) - idx_push_delivery_logs_status (delivery_status, sent_at) WHERE delivery_status IN ('PENDING', 'FAILED') - idx_push_delivery_logs_code_date (push_code, sent_at) — 用于按推送类型统计送达率

3补.5 V1.1 微调 2 表字段¶

`cpt_orders` 新增 `lead_source` 字段（对应 F-CST-003 客户端询价来源追踪）¶

ALTER TABLE cpt_orders
  ADD COLUMN lead_source app_enum__cst_lead_source_3
    NOT NULL DEFAULT 'OL_MANUAL';

COMMENT ON COLUMN cpt_orders.lead_source IS
  '订单来源：CST_APP_SELF=客户端 App 自助 / OPS_REFERRAL=OPS 推荐链路 / OL_MANUAL=OL 代客下单（默认）';

`mds_cpl_customers` 新增 `referrer_code` 字段（对应 F-CST-013 推荐码生成）¶

ALTER TABLE mds_cpl_customers
  ADD COLUMN referrer_code VARCHAR(8);

CREATE UNIQUE INDEX uk_cpl_customers_referrer_code
  ON mds_cpl_customers (referrer_code) WHERE referrer_code IS NOT NULL;

COMMENT ON COLUMN mds_cpl_customers.referrer_code IS
  '客户推荐码（CUS-ULID 后 6 位，首次生成推荐码时写入；NULL=未生成过）';

四、枚举类型完整定义（迁移脚本 CREATE TYPE ... AS ENUM）¶

迁移脚本 V1.0 开头按以下顺序创建类型，所有表的列引用类型（不使用 CHECK 字符串模拟，便于未来 ALTER TYPE ADD VALUE）：

CREATE TYPE app_enum__cpt_stage           AS ENUM ('L1','L2','L3','L4','L5','L6','L7','L8','DONE','EXCEPTION');
CREATE TYPE app_enum__cpt_status          AS ENUM ('IN_PROGRESS','COMPLETED','CANCELLED','EXCEPTION_PAUSED');
CREATE TYPE app_enum__scl_code_8          AS ENUM ('WARDROBE','KITCHEN_CABINET','BED','DINING_TABLE','BOOKSHELF','TV_CABINET','OFFICE_DESK','COMBINATION_FURNITURE');
CREATE TYPE app_enum__bld_type_7          AS ENUM ('PUBLIC_HOUSING','HOS','PRIVATE','VILLAGE_HOUSE','COMMERCIAL','INDUSTRIAL','OTHER');
CREATE TYPE app_enum__worker_level        AS ENUM ('L1','L2','L3');
CREATE TYPE app_enum__worker_status       AS ENUM ('AVAILABLE','BUSY','ON_LEAVE','SUSPENDED');
CREATE TYPE app_enum__ops_scene_8         AS ENUM ('SCENE01_ONLINE_AD','SCENE02_PACKAGE_INSERT','SCENE03_ECOMMERCE','SCENE04_LOGISTICS_REF','SCENE05_BRAND_PARTNER','SCENE06_CAINIAO','REFERRAL','OTHER');
CREATE TYPE app_enum__pay_method_4        AS ENUM ('FPS','CREDIT_CARD','CASH','MONTHLY');
CREATE TYPE app_enum__pay_status_4        AS ENUM ('UNPAID','PAID','PARTIAL','REFUNDED');
CREATE TYPE app_enum__delivery_method_4   AS ENUM ('SELF_DELIVER','LOGISTICS','CUSTOMER_PICKUP','BRAND_DIRECT');
CREATE TYPE app_enum__delivery_status_4   AS ENUM ('PENDING','IN_TRANSIT','RECEIVED','EXCEPTION');
CREATE TYPE app_enum__time_slot_3         AS ENUM ('AM','PM','WEEKEND_PRIOR');
CREATE TYPE app_enum__attach_category_8   AS ENUM ('ENTRY','INSTALL_PROCESS','AFTER','SIGNATURE','CONTRACT','DELIVERY_DAMAGE','EXCEPTION','OTHER');
CREATE TYPE app_enum__exception_kind_10   AS ENUM ('PRODUCT_BROKEN','PART_MISSING','INSTALL_CONDITION','PROPERTY_BLOCK','RESCHEDULE','L6_INSTALL_DAMAGE','DELIVERY_DAMAGE','CUSTOMER_COMPLAINT','CANCELLED','OTHER');
CREATE TYPE app_enum__l7_result_3         AS ENUM ('PASS','RECTIFY','COMPLAINT');
CREATE TYPE app_enum__quote_status_4      AS ENUM ('DRAFT','CONFIRMED','REJECTED','EXPIRED');
CREATE TYPE app_enum__bo_approval_3       AS ENUM ('PENDING','APPROVED','REJECTED');
CREATE TYPE app_enum__reward_status_4     AS ENUM ('PENDING_APPROVAL','APPROVED','PAID','EXPIRED');
CREATE TYPE app_enum__wom_candidate_st_3  AS ENUM ('CUSTOMER_PENDING','BO_PENDING','PUBLISHED','REJECTED');
CREATE TYPE app_enum__user_status_4       AS ENUM ('ACTIVE','DISABLED','LOCKED_5FA','PWD_RESET');
CREATE TYPE app_enum__audit_action_9      AS ENUM ('LOGIN','LOGOUT','CREATE','UPDATE','DELETE','RBAC_CHANGE','EXPORT','FORCE_TRANSITION','RLS_CHANGE');
CREATE TYPE app_enum__tsmm_entity_4       AS ENUM ('WORKER','CUSTOMER','BUILDING','CATEGORY');
CREATE TYPE app_enum__tsmm_tier_3         AS ENUM ('BASIC','ABILITY','CONTEXT');
CREATE TYPE app_enum__pdpo_level_3        AS ENUM ('PUBLIC','INTERNAL','SENSITIVE');
CREATE TYPE app_enum__feishu_table_6      AS ENUM ('SCL','BPL','CPL','WPL','QSV','CPT');
CREATE TYPE app_enum__dlq_dead_type_5     AS ENUM ('FEISHU_SYNC','WEBHOOK_EVENT','CRON_JOB','REPORT_GENERATION','REWARD_CALC');
CREATE TYPE app_enum__sys_role_6          AS ENUM ('SPO','BO','TL','OL','ML','FL');
-- ========== V1.1 新增（0002_cst_mobile_push）==========
CREATE TYPE app_enum__cst_lead_source_3   AS ENUM ('CST_APP_SELF','OPS_REFERRAL','OL_MANUAL');
CREATE TYPE app_enum__push_code_8         AS ENUM ('PUSH_NEW_ORDER','PUSH_ORDER_REMINDER_1H','PUSH_ORDER_STATUS','PUSH_WORKER_DEPARTED','PUSH_REVIEW_INVITE','PUSH_REFERRAL_REWARD','PUSH_QUOTE_READY','PUSH_SYSTEM');
CREATE TYPE app_enum__mobile_platform_2   AS ENUM ('IOS','ANDROID');
CREATE TYPE app_enum__app_type_2          AS ENUM ('WKR','CST');
CREATE TYPE app_enum__push_delivery_st_4  AS ENUM ('PENDING','SENT','FAILED','DELIVERED');

五、RLS（Row Level Security）7 条物理策略（V1.0 5 条 + V1.1 新增 2 条，对齐 DIP1-P2 §5.2）¶

-- 5.1 开关（所有业务表开启 RLS；辅助/字典表按需关）
ALTER TABLE cpt_orders              ENABLE ROW LEVEL SECURITY;
ALTER TABLE cpt_order_attachments   ENABLE ROW LEVEL SECURITY;
ALTER TABLE mds_wpl_workers         ENABLE ROW LEVEL SECURITY;  -- 师傅档案：手机号完整需权限
ALTER TABLE mds_cpl_customers       ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_logs              ENABLE ROW LEVEL SECURITY;
-- V1.1 新增
ALTER TABLE cst_sessions            ENABLE ROW LEVEL SECURITY;
ALTER TABLE cst_push_preferences    ENABLE ROW LEVEL SECURITY;

-- 5.2 师傅（role = FL）只能操作 assigned_worker_id = 本账号的订单 + 附件
CREATE POLICY rls_fl_see_own_orders ON cpt_orders
  FOR SELECT USING (
    current_setting('app.current_role', true) = 'FL'
    AND assigned_worker_id = current_setting('app.worker_id', true)::char(26)
  );

-- 5.3 OL 可看全部（默认 follow_up_id 过滤留给前端条件，RLS 只限制越权 FL）
CREATE POLICY rls_bo_tl_ol_full_access ON cpt_orders FOR ALL USING (
  current_setting('app.current_role', true) IN ('BO','TL','OL','SPO')
);

-- 5.4 ML 角色：仅允许操作 ops_* 表 + mds_cpl_customers（无 QSV 金额明细 RLS MVP 通过 RBAC 权限列限制）
CREATE POLICY rls_ml_ops_only_cpl ON mds_cpl_customers FOR SELECT USING (
  current_setting('app.current_role', true) IN ('BO','TL','ML','SPO')
);

-- 5.5 审计日志仅 TL/SPO 可读
CREATE POLICY rls_audit_read_tl_spo ON audit_logs FOR SELECT USING (
  current_setting('app.current_role', true) IN ('TL','SPO')
);

-- 5.6 V1.1 新增：客户端 CUS 只能看自己的会话（app.current_customer_id 由后端登录中间件设置）
CREATE POLICY rls_cst_see_own_sessions ON cst_sessions
  FOR ALL USING (
    current_setting('app.current_role', true) = 'CUS'
    AND customer_id = current_setting('app.current_customer_id', true)::char(26)
  );

-- 5.7 V1.1 新增：客户端 CUS 只能看自己的推送偏好
CREATE POLICY rls_cst_see_own_push_prefs ON cst_push_preferences
  FOR ALL USING (
    current_setting('app.current_role', true) = 'CUS'
    AND customer_id = current_setting('app.current_customer_id', true)::char(26)
  );

⚠ 实现约束：所有数据库连接必须先调用 SET app.current_role = $1; SET app.worker_id = $2;（SQLAlchemy 的 before_cursor_execute 事件；未设置则 RLS 走空 → 返回 0 行，等于强制鉴权）。

⚠ V1.1 新增：客户端 App 登录后需额外调用 SET app.current_customer_id = $3;（CUS 角色专用，配合 5.6/5.7 策略）。mobile_push_tokens / push_delivery_logs 不开启 RLS（由后端应用层鉴权，因 CUS/WKR 双端共用且涉及运维查询）。

六、Alembic 迁移配套规则¶

迁移脚本存放目录：dip1/backend/alembic/versions/*.py
命名规范：NNNN_26chars_title.py，revision 用 Alembic 标准 12 字符哈希，down_revision 链接
V1.0 初始脚本：0001_initial_schema.py，down_revision = None（18 表 + 30 枚举 + 5 RLS）
V1.1 增量脚本：0002_cst_mobile_push.py，down_revision = 0001（4 表 + 5 枚举 + 2 RLS + 2 ALTER）
新增表：cst_sessions / cst_push_preferences / mobile_push_tokens / push_delivery_logs
新增枚举：app_enum__cst_lead_source_3 / app_enum__push_code_8 / app_enum__mobile_platform_2 / app_enum__app_type_2 / app_enum__push_delivery_st_4
ALTER 表：cpt_orders ADD COLUMN lead_source；mds_cpl_customers ADD COLUMN referrer_code + UNIQUE INDEX
RLS：cst_sessions / cst_push_preferences ENABLE RLS + 2 条 POLICY
downgrade 必须：DROP POLICY + DISABLE RLS + DROP TABLE + DROP COLUMN + DROP TYPE，全部可逆
CI Job migrate-check：alembic upgrade head → alembic downgrade base → alembic upgrade head 三循环零错（含 0001 + 0002）
数据迁移类（如 MVP-FS → PG）不写入 Alembic，放在 scripts/migration/；DML 脚本用 alembic bulk_insert + 版本号绑定

修订记录¶

版本	日期	修订人	修订内容
V1.0	2026-08-09	DT	初始化：18 表 + 30 枚举 + 5 RLS 策略；Alembic 0001 初始迁移脚本；ER Mermaid 图；按业务域分组（系统/MDS/QSV/CPT/辅助）
V1.1	2026-08-09	DT	CST 客户端 + 移动推送：① 新增 4 表（cst_sessions / cst_push_preferences / mobile_push_tokens / push_delivery_logs）；② cpt_orders 新增 lead_source 字段；③ mds_cpl_customers 新增 referrer_code 字段 + 唯一索引；④ 新增 5 枚举（cst_lead_source_3 / push_code_8 / mobile_platform_2 / app_type_2 / push_delivery_st_4）；⑤ 新增 2 条 RLS 策略（cst_sessions / cst_push_preferences 客户端只能看自己）；⑥ Alembic 新增 0002_cst_mobile_push 迁移脚本；⑦ 表总数 18→22，枚举 30→35，RLS 5→7

本文件为 DIP1 数据库详细设计（DOC-D01-DB V1.1），Alembic 脚本 0001_initial_schema.py + 0002_cst_mobile_push.py 实现本章 §1~§5；Code Agent 需保证 alembic upgrade head 在空 PG16 执行无错后再开始业务代码开发。

DIP1 Schema 数据库详细设计¶

一、ER 总图（22 表关系总览，V1.0 18 表 + V1.1 新增 4 表）¶

二、12 主表完整字段与约束说明（按业务域分组）¶

2.1 系统域：sys_users / sys_roles / sys_user_roles / sys_role_permissions（4 表 RBAC 基础）¶

2.1.1 sys_users（系统用户，运营后台 5 角色 + FL 师傅登录）¶

2.1.2 sys_roles（固定 6 角色不可删除，可自定义描述与权限开关）¶

2.1.3 sys_user_roles（用户 × 角色 N:M）¶

2.1.4 sys_role_permissions（role × permission_key BOOLEAN 开关）¶

2.2 MDS 主数据域（4 库 + TSMM = 7 表：mds_scl_categories / sub / mds_bpl_buildings / mds_wpl_workers / mds_cpl_customers / mds_tsmm_tags / mds_entity_tags）¶

2.2.1 mds_scl_categories（SCL 品类 8 大类）¶

2.2.2 mds_scl_sub_categories（子类目，MVP 仅 WARDROBE 插入 5 子类目）¶

2.2.3 mds_bpl_buildings（楼宇档案 7 类）¶

2.2.4 mds_wpl_workers（师傅档案）¶

2.2.5 mds_cpl_customers（客户档案）¶

2.2.6 mds_tsmm_tags（标签定义 3 层分类 × PDPO 隐私级别）¶

2.2.7 mds_entity_tags（实体 × 标签，单表通用 4 实体，其中必有一个 FK 非空）¶