313 lines
12 KiB
SQL
313 lines
12 KiB
SQL
-- 财务上线验收（只读）
|
||
--
|
||
-- 用途：部署 + 数据初始化完成后，一次跑完所有闸门，输出 PASS / FAIL 清单。
|
||
-- 不写任何业务表；结果只落在会话级临时表 _launch_checks。
|
||
--
|
||
-- 用法：
|
||
-- # 1) 通过编排脚本（推荐，自动带上 launch_at）
|
||
-- bash deploy/post-deploy.sh --only=verify --launch-at='2026-10-27 18:00:00+08'
|
||
--
|
||
-- # 2) 手工执行（变量必须传，否则用脚本内的兜底时间）
|
||
-- docker compose exec -T postgres sh -lc \
|
||
-- 'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" \
|
||
-- -v ON_ERROR_STOP=1 -v launch_at="2026-10-27 18:00:00+08" -f -' \
|
||
-- < scripts/verify-finance-launch.sql
|
||
--
|
||
-- 退出码：0 = 无 FAIL（WARN 不阻断）；3 = 存在 FAIL（末尾 RAISE EXCEPTION）。
|
||
-- 前置条件：迁移 103–106 已应用；否则先跑 deploy/ubuntu-deploy.sh。
|
||
|
||
\set ON_ERROR_STOP on
|
||
|
||
\if :{?launch_at}
|
||
\else
|
||
-- 与 post-deploy.sh 一致：默认取本次执行时间（容器 TZ=Asia/Shanghai）。
|
||
\set launch_at `date '+%Y-%m-%d %H:%M:%S%z'`
|
||
\endif
|
||
|
||
\echo ''
|
||
\echo '==================== 财务上线验收 ===================='
|
||
\echo '上线时间（launch_at）: ' :launch_at
|
||
\echo ''
|
||
|
||
-- 建表与 15 条检查的 INSERT 不输出到终端（错误仍会进 stderr）。
|
||
\o /dev/null
|
||
SET client_min_messages = warning;
|
||
DROP TABLE IF EXISTS _launch_checks;
|
||
|
||
CREATE TEMP TABLE _launch_checks (
|
||
seq int,
|
||
id text,
|
||
title text,
|
||
actual text,
|
||
expected text,
|
||
status text,
|
||
hint text
|
||
);
|
||
|
||
-- 01 迁移基线 --------------------------------------------------------------
|
||
INSERT INTO _launch_checks
|
||
SELECT 1, '01', '迁移基线 103–106 已应用',
|
||
v::text, '4',
|
||
CASE WHEN v = 4 THEN 'PASS' ELSE 'FAIL' END,
|
||
'后端未完成迁移：docker compose logs backend | grep -i migration'
|
||
FROM (
|
||
-- 按版本前缀逐个判断，避免后续 106_xxx_hotfix 之类增量迁移把计数顶成 5。
|
||
SELECT (EXISTS (SELECT 1 FROM schema_migrations WHERE COALESCE(filename, name) ~ '^103_'))::int
|
||
+ (EXISTS (SELECT 1 FROM schema_migrations WHERE COALESCE(filename, name) ~ '^104_'))::int
|
||
+ (EXISTS (SELECT 1 FROM schema_migrations WHERE COALESCE(filename, name) ~ '^105_'))::int
|
||
+ (EXISTS (SELECT 1 FROM schema_migrations WHERE COALESCE(filename, name) ~ '^106_'))::int AS v
|
||
) s;
|
||
|
||
-- 02 存量订单项收入回填 ----------------------------------------------------
|
||
INSERT INTO _launch_checks
|
||
SELECT 2, '02', '存量订单项收入已回填（revenue_basis）',
|
||
v::text, '0',
|
||
CASE WHEN v = 0 THEN 'PASS' ELSE 'FAIL' END,
|
||
'跑 npm run revenue:backfill（deploy/post-deploy.sh 会自动执行）；未回填时退款按钮置灰'
|
||
FROM (
|
||
SELECT count(*) AS v FROM order_items WHERE revenue_basis = 'unknown'
|
||
) s;
|
||
|
||
-- 03 履约方式回填 ----------------------------------------------------------
|
||
INSERT INTO _launch_checks
|
||
SELECT 3, '03', '履约方式已回填（fulfillment_mode）',
|
||
v::text, '0',
|
||
CASE WHEN v = 0 THEN 'PASS' ELSE 'FAIL' END,
|
||
'跑 npm run fulfillment:backfill；NULL 会让订单/成本页口径缺失'
|
||
FROM (
|
||
SELECT count(*) AS v FROM order_items WHERE fulfillment_mode IS NULL
|
||
) s;
|
||
|
||
-- 04 履约方式取值合法 ------------------------------------------------------
|
||
INSERT INTO _launch_checks
|
||
SELECT 4, '04', '履约方式取值合法',
|
||
v::text, '0',
|
||
CASE WHEN v = 0 THEN 'PASS' ELSE 'FAIL' END,
|
||
'存在未知 fulfillment_mode，检查回填脚本与历史脏数据'
|
||
FROM (
|
||
SELECT count(*) AS v
|
||
FROM order_items
|
||
WHERE fulfillment_mode IS NOT NULL
|
||
AND fulfillment_mode NOT IN ('work_order', 'manual', 'legacy_platform', 'affiliate_dash')
|
||
) s;
|
||
|
||
-- 05 商品目录 --------------------------------------------------------------
|
||
INSERT INTO _launch_checks
|
||
SELECT 5, '05', '商品目录已初始化（product_catalog）',
|
||
v::text, '> 0',
|
||
CASE WHEN v > 0 THEN 'PASS' ELSE 'FAIL' END,
|
||
'商品与成本 → 标准商品 →「从接单模板初始化」/「按工单回填商品」/「归一人工商品」'
|
||
FROM (SELECT count(*) AS v FROM product_catalog) s;
|
||
|
||
-- 06 有效成本 --------------------------------------------------------------
|
||
INSERT INTO _launch_checks
|
||
SELECT 6, '06', '有效成本已生成（版本定价或正金额快照）',
|
||
'版本 ' || priced_versions::text || ' 个 / 正金额快照 ' || positive_snapshots::text || ' 条',
|
||
'至少一项 > 0',
|
||
CASE WHEN priced_versions > 0 OR positive_snapshots > 0 THEN 'PASS' ELSE 'FAIL' END,
|
||
'迁移只建 0 元占位版本；工单奖励/实际成本会写入快照。两项都为 0 时：先配置成本版本，或确认工单奖励已回写'
|
||
FROM (
|
||
SELECT
|
||
(SELECT count(*) FROM cost_versions
|
||
WHERE status = 'active' AND (amount_fen > 0 OR rate_bps > 0)) AS priced_versions,
|
||
(SELECT count(*) FROM cost_snapshots
|
||
WHERE superseded_at IS NULL AND total_amount_fen > 0) AS positive_snapshots
|
||
) s;
|
||
|
||
-- 07 客服备用金账户 --------------------------------------------------------
|
||
INSERT INTO _launch_checks
|
||
SELECT 7, '07', '客服备用金账户已补建',
|
||
v::text, '0',
|
||
CASE WHEN v = 0 THEN 'PASS' ELSE 'FAIL' END,
|
||
'钱包管理 → 客服账户 →「补建客服账户」（POST /admin/fund/accounts/ensure-agents）'
|
||
FROM (
|
||
SELECT count(*) AS v
|
||
FROM admin_users au
|
||
LEFT JOIN fund_accounts fa
|
||
ON fa.owner_admin_user_id = au.id
|
||
AND fa.account_type = 'agent'
|
||
WHERE au.role = 'support'
|
||
AND fa.id IS NULL
|
||
) s;
|
||
|
||
-- 08 上线后人工项必须有履约配置 -------------------------------------------
|
||
INSERT INTO _launch_checks
|
||
SELECT 8, '08', '上线后人工项已匹配履约配置',
|
||
v::text, '0',
|
||
CASE WHEN v = 0 THEN 'PASS' ELSE 'FAIL' END,
|
||
'商品与成本 → 人工 SKU 配置 →「扫描」并逐项配置；保存后会自动回填近 30 天'
|
||
FROM (
|
||
SELECT count(*) AS v
|
||
FROM order_items oi
|
||
WHERE oi.fulfillment_mode = 'manual'
|
||
AND oi.created_at >= :'launch_at'::timestamptz
|
||
AND oi.fulfillment_config_id IS NULL
|
||
) s;
|
||
|
||
-- 09 上线后人工项必须有成本快照（关账门槛） -------------------------------
|
||
INSERT INTO _launch_checks
|
||
SELECT 9, '09', '上线后人工项均已确认履约（关账门槛）',
|
||
v::text, '0',
|
||
CASE WHEN v = 0 THEN 'PASS' ELSE 'FAIL' END,
|
||
'待办量：有未确认人工单就会 FAIL，不宜当日常健康检查；订单列表/详情 →「确认履约」后写 manual_case 快照并扣备用金/积分'
|
||
FROM (
|
||
SELECT count(*) AS v
|
||
FROM order_items oi
|
||
WHERE oi.fulfillment_mode = 'manual'
|
||
AND oi.created_at >= :'launch_at'::timestamptz
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM cost_snapshots cs
|
||
WHERE cs.subject_type = 'manual_case'
|
||
AND cs.order_item_id = oi.id
|
||
AND cs.superseded_at IS NULL
|
||
)
|
||
) s;
|
||
|
||
-- 10 人工快照唯一性 --------------------------------------------------------
|
||
INSERT INTO _launch_checks
|
||
SELECT 10, '10', '一个订单项最多一条有效人工快照',
|
||
v::text, '0',
|
||
CASE WHEN v = 0 THEN 'PASS' ELSE 'FAIL' END,
|
||
'出现重复快照说明并发确认未被幂等拦截，需排查 manual_case 写入路径'
|
||
FROM (
|
||
SELECT count(*) AS v
|
||
FROM (
|
||
SELECT order_item_id
|
||
FROM cost_snapshots
|
||
WHERE subject_type = 'manual_case' AND superseded_at IS NULL
|
||
GROUP BY 1
|
||
HAVING count(*) > 1
|
||
) dup
|
||
) s;
|
||
|
||
-- 11 快照金额 = 备用金支出（现金路径） ------------------------------------
|
||
INSERT INTO _launch_checks
|
||
SELECT 11, '11', '人工快照金额与备用金支出一致',
|
||
v::text, '0',
|
||
CASE WHEN v = 0 THEN 'PASS' ELSE 'FAIL' END,
|
||
'金额不一致通常是确认事务部分失败；查 cost_snapshots / manual_expenses / fund_entries 三处'
|
||
FROM (
|
||
SELECT count(*) AS v
|
||
FROM cost_snapshots cs
|
||
-- 只核对现金路径：metadata.fundExpenseId 精确定位备用金支出，
|
||
-- 避免同一订单项有多条 manual_expenses（如另外登记的人工支出）时行放大误报。
|
||
JOIN manual_expenses me
|
||
ON me.id = NULLIF(cs.metadata_json->>'fundExpenseId', '')::bigint
|
||
WHERE cs.subject_type = 'manual_case'
|
||
AND cs.superseded_at IS NULL
|
||
AND cs.created_at >= :'launch_at'::timestamptz
|
||
AND cs.total_amount_fen <> me.amount_fen
|
||
) s;
|
||
|
||
-- 12 总账借贷平衡 ----------------------------------------------------------
|
||
INSERT INTO _launch_checks
|
||
SELECT 12, '12', '总账借贷平衡（全部已过账凭证）',
|
||
v::text || '（已过账凭证 ' || posted::text || ' 张）', '借贷为 0 且至少有 1 张凭证',
|
||
CASE WHEN v = 0 AND (posted > 0 OR facts = 0) THEN 'PASS' ELSE 'FAIL' END,
|
||
'财务 → 总账与对账 →「同步销售事实到总账」；有销售事实但 0 张凭证同样视为未完成（空表求和恒为 0，不能直接判 PASS）'
|
||
FROM (
|
||
SELECT
|
||
COALESCE(abs(sum(l.debit_fen) - sum(l.credit_fen)), 0)::bigint AS v,
|
||
count(DISTINCT e.id) AS posted,
|
||
(SELECT count(*) FROM sales_daily_facts) AS facts
|
||
FROM finance_journal_lines l
|
||
JOIN finance_journal_entries e ON e.id = l.entry_id AND e.status = 'posted'
|
||
) s;
|
||
|
||
-- 13 已关账期间不应再产生新凭证 -------------------------------------------
|
||
INSERT INTO _launch_checks
|
||
SELECT 13, '13', '已关账期间无新增凭证',
|
||
v::text, '0',
|
||
CASE WHEN v = 0 THEN 'PASS' ELSE 'FAIL' END,
|
||
'检查关账后是否有人重跑同步；以 closed_at 为基准，正常关账前生成的凭证不算违规'
|
||
FROM (
|
||
SELECT count(*) AS v
|
||
FROM finance_journal_entries e
|
||
JOIN finance_periods p
|
||
ON p.period_key = e.period_key
|
||
AND p.status = 'closed'
|
||
WHERE p.closed_at IS NOT NULL
|
||
AND e.created_at > p.closed_at
|
||
) s;
|
||
|
||
-- 14 销售事实已生成 --------------------------------------------------------
|
||
INSERT INTO _launch_checks
|
||
SELECT 14, '14', '销售事实已生成（sales_daily_facts）',
|
||
v::text, '> 0',
|
||
CASE WHEN v > 0 THEN 'PASS' ELSE 'FAIL' END,
|
||
'跑 finance:sync --mode=facts（post-deploy.sh 会按窗口执行）；定时任务只覆盖最近 7 天'
|
||
FROM (SELECT count(*) AS v FROM sales_daily_facts) s;
|
||
|
||
-- 15 近 7 天成本覆盖（仅提示，不阻断上线） ---------------------------------
|
||
-- 说明：历史人工成本按既定口径不补录，这里只看非人工；自动发货/接单平台
|
||
-- 的成本缺口会直接影响毛利，必须在上线前处理，但不用它单独卡住验收。
|
||
INSERT INTO _launch_checks
|
||
SELECT 15, '15', '近 7 天非人工销售事实成本覆盖（仅提示）',
|
||
v::text, '0',
|
||
CASE WHEN v = 0 THEN 'PASS' ELSE 'WARN' END,
|
||
'存在成本缺失的非人工销售事实：先归一商品并配置成本版本；历史人工成本按既定口径不补录，故不计入'
|
||
FROM (
|
||
SELECT count(*) AS v
|
||
FROM sales_daily_facts
|
||
WHERE stat_date >= current_date - 6
|
||
AND fulfillment_mode <> 'manual'
|
||
AND cost_missing_count > 0
|
||
) s;
|
||
|
||
\o
|
||
\echo '【闸门清单】'
|
||
SELECT seq AS "#",
|
||
status,
|
||
id,
|
||
title AS "检查项",
|
||
actual AS "实际",
|
||
expected AS "期望"
|
||
FROM _launch_checks
|
||
ORDER BY seq;
|
||
|
||
\echo ''
|
||
\echo '【未通过 / 提示项的处理建议】'
|
||
SELECT id,
|
||
status AS "状态",
|
||
title AS "检查项",
|
||
hint AS "处理建议"
|
||
FROM _launch_checks
|
||
WHERE status <> 'PASS'
|
||
ORDER BY seq;
|
||
|
||
\echo ''
|
||
\echo '【汇总】'
|
||
SELECT count(*) FILTER (WHERE status = 'PASS') AS "通过",
|
||
count(*) FILTER (WHERE status = 'FAIL') AS "未通过",
|
||
count(*) FILTER (WHERE status = 'WARN') AS "提示",
|
||
count(*) AS "合计"
|
||
FROM _launch_checks;
|
||
|
||
\echo ''
|
||
\echo '【数据规模参考】'
|
||
SELECT (SELECT count(*) FROM orders) AS "订单",
|
||
(SELECT count(*) FROM order_items) AS "订单项",
|
||
(SELECT count(*) FROM product_catalog) AS "商品",
|
||
(SELECT count(*) FROM cost_snapshots WHERE superseded_at IS NULL) AS "有效成本快照",
|
||
(SELECT count(*) FROM sales_daily_facts) AS "销售事实行",
|
||
(SELECT count(*) FROM finance_journal_entries) AS "总账凭证",
|
||
(SELECT count(*) FROM cost_exceptions WHERE status = 'open') AS "未处理成本异常",
|
||
(SELECT count(*) FROM order_items WHERE revenue_basis = 'unknown') AS "待回填收入项";
|
||
|
||
DO $$
|
||
DECLARE
|
||
failed_count int;
|
||
failed_list text;
|
||
BEGIN
|
||
SELECT count(*), string_agg(id || ' ' || title, '；' ORDER BY seq)
|
||
INTO failed_count, failed_list
|
||
FROM _launch_checks
|
||
WHERE status = 'FAIL';
|
||
|
||
IF failed_count > 0 THEN
|
||
RAISE EXCEPTION '财务上线验收未通过（% 项）：%', failed_count, failed_list;
|
||
END IF;
|
||
|
||
RAISE NOTICE '财务上线验收全部通过';
|
||
END $$;
|