# 数据库侧需求单（SaaS → 采集/数据库）— 2026-09-24

> **✅ 执行状态（2026-09-24 当日，用户放行权限后由 SaaS 会话直接执行完毕）**
> R1 已修并验收；R2 代码已落地（每小时自动跑）+ 历史 backfill 完成；执行中追查出 **3 个更深的 zf 采集器根因**（凭证丢失/缓存串号/漏配 6 市场）并一并修复，详见文末「执行记录」。

**移交对象**：mercury（104.194.81.222）侧维护人
**提出方**：SaaS（mars）——本文档只定义需求与验收，不含修复动作；SaaS 侧已做的呈现兜底见文末。
**背景**：用户就工作台「24小时订单分布」「每日订单数」图表准确度连续 3 天提问 10 次（09-17 / 09-23 / 09-24）。SaaS 侧已三轮修复（标题语义 / 同步中断 / 基准日口径），本轮四层对账（rawdata→cleandata→api→web）+ mercury 只读通道实证，锁定**两个源头根因**。相关前单：`db-requirements-2026-09-09.md` R1（未执行）、`mercury-requirements-2026-09-23.md`。

---

## R1（P0 主修）：mercury fg 订单视图时区——UTC 日切 vs 北京日切，同一采集 SQL 里两种日历

**现象（全部 mercury 只读通道实证，2026-09-24 09:30 北京时间）**：

- `v_fittingirls_order_finance_detail` 视图定义中：`o.purchase_date::date AS order_date`。`purchase_date` 为 **timestamptz**，裸 `::date` 按会话时区（UTC）切日
- SaaS 同步 SQL `_ORDERS_UNION` 的 zipforce/onno 分支显式写 `(purchase_date AT TIME ZONE 'Asia/Shanghai')::date`，**fittingirls 分支直接吃视图的 UTC 日**——同一条 SQL、两种日历
- 当日实证：`fittingirls_amazon_spapi_orders` 活表按**北京日** 09-24 已有 **18 单**；按视图（UTC 日）09-24 只有 **1 单**（北京时间 0-8 点的单全被切到 09-23）
- 连锁后果（SaaS 侧实测）：
  - fg 订单表（UTC 日）与 `sales_hourly`（北京日）逐日对不上，近一周 diff = **-14 ~ +15 相邻互补**（09-20 订单表+15、09-21 订单表-14，即同批单日界挪移）
  - 工作台同屏矛盾：24小时图（小时表）显示 fg 今日 17 单，KPI/每日订单图（订单表）fg 今日 0 单

**需求**：
1. `v_fittingirls_order_finance_detail` 的三处日期表达式统一为北京日：
   - `o.purchase_date::date AS order_date` → `(o.purchase_date AT TIME ZONE 'Asia/Shanghai')::date`
   - `o.last_update_date::date AS shipped_date` → 同款北京日
   - `to_char(o.purchase_date::date::timestamp, 'YYYY-MM') AS order_month` → 北京日再取月（**当前 UTC 语义会把每月末北京时间 0-8 点的单错切到上月**，直接影响 fg 月度费用摊销分母 `monthly_order_count`，金额虽小但口径错）
2. 排查 fg 视图族其余同款问题：`v_fittingirls_monthly_overhead`、`v_fittingirls_finances_flat` 及任何对 timestamptz 裸 `::date` 的 fg 派生视图
3. 修后无需回补历史（SaaS 周日 02:00 全量任务自动带回正确归属日）

**验收（mercury 侧执行）**：
```sql
-- ① 视图日语义与活表北京日一致(差≤1个采集周期):
SELECT (SELECT count(*) FROM v_fittingirls_order_finance_detail
         WHERE order_date = (now() AT TIME ZONE 'Asia/Shanghai')::date),
       (SELECT count(*) FROM fittingirls_amazon.fittingirls_amazon_spapi_orders
         WHERE (purchase_date AT TIME ZONE 'Asia/Shanghai')::date = (now() AT TIME ZONE 'Asia/Shanghai')::date);
-- ② 视图定义不再含裸 ::date(pg_get_viewdef 检查 purchase_date/last_update_date 两处)
```
SaaS 侧复验：`amazon_orders` 与 `sales_hourly` 按 fg 近 7 天逐日 diff 全部归零（±1 增量周期内）。

## R2（P0，合并并扩容 09-09 R1）：Pending/Unshipped 状态回刷——扩展到全部品牌

**现象（09-24 实证）**：
- `db-requirements-2026-09-09.md` R1 要求 fg 补 Pending 回刷，**至今未执行**
- 新实证扩展到 zipforce：**08-17 周 7 单**（5 Pending + 2 Unshipped，见下表）在 mercury 侧状态卡住、`spapi_finances` 0 财务事件、`revenue_usd` 空——SaaS 侧费用覆盖率断言 83%（<90% 阈值）门禁 FAIL
  ```
  204-0574361-7205904  Pending    08-17
  204-5275497-2003527  Pending    08-17
  204-6011141-3902716  Pending    08-18
  204-7444142-7173139  Pending    08-18
  S02-9650201-3148231  Unshipped  08-19
  S02-0575354-2740943  Unshipped  08-22
  S02-9191541-9650947  Unshipped  08-23
  ```
- Pending/Unshipped 单 Amazon 不下发 OrderTotal、不产生结算事件——**状态不回刷 = 金额永远为空 + 费用事件永远缺失**，这是 fg 8-9 月金额断流与本次 zf 费用缺口的同一根因

**需求**：
1. 09-09 R1 原文照做（fg 近 35 天 Pending 回刷 + 8 月起一次性回补）
2. **扩展到全部品牌**（zipforce/onno 同机制）：每轮对近 35 天 `order_status IN ('Pending','Unshipped')` 的订单按 `amazon_order_id` 重查 SP-API `getOrders` UPSERT 回写状态与金额；已确认取消/超龄（>60 天仍 Pending）的标记归档而非无限回刷
3. 上表 7 单作为首批验收样本

**验收（mercury 侧执行）**：
```sql
-- 近35天 Pending/Unshipped 中 last_update_date 落后 >3 天的订单数 → 0(每品牌):
SELECT brand, count(*) FROM (
  SELECT 'zipforce' brand, amazon_order_id FROM zipforce_amazon.zipforce_amazon_spapi_orders
   WHERE order_status IN ('Pending','Unshipped') AND purchase_date > now()-interval '35 day'
     AND last_update_date < now()-interval '3 day'
  UNION ALL
  SELECT 'fittingirls', amazon_order_id FROM fittingirls_amazon.fittingirls_amazon_spapi_orders
   WHERE order_status IN ('Pending','Unshipped') AND purchase_date > now()-interval '35 day'
     AND last_update_date < now()-interval '3 day') t GROUP BY 1;
```
SaaS 侧复验：`dashboard_audit` 的费用覆盖门全周 ≥90%（当前 zipforce 08-17 周 83% FAIL）。

## R3（数据契约重申）：订单业务日唯一基准 = 北京时间，禁止裸 `::date`

- SaaS 侧 `sales_hourly` 与订单表同源（均从各品牌 `spapi_orders` 派生，`_HOURLY_BRANCH` 显式北京日）——**两表本无独立管道**，此前观测的偏差全部来自 R1 的日历分裂 + 增量时点差
- 契约：mercury 侧任何订单/财务派生视图与聚合，凡遇 timestamptz 切日，必须显式 `AT TIME ZONE 'Asia/Shanghai'`，禁止裸 `::date`（本次 fg 事故即由此而来）
- 观察项（不阻塞）：onno 订单表 vs 小时表存在 +2~+4 的增量时点差（两表聚合时刻不同步所致，量级小；R1/R2 修复后 SaaS 侧持续观测，若仍显著另立需求）

## SaaS 侧已做的兜底（无需 mercury 配合）
- 工作台 24小时图基准日统一取订单表最大日（09-09 铁律重申，本次修复连带消除跨品牌基准日漂移）
- 日粒度图当日进行中柱加 `*` 星标 + 小时图副题标注实际数据日（09-24 上线）
- fg 金额断流期间的呈现兜底见 09-09 需求单文末（Pending 计数 + 「金额待结算」标注，不显示误导性 0）

## 修复后联动（无需 SaaS 发版）
mercury 侧修复生效后：SaaS 周日 02:00 订单全量任务自动带回正确数据；`ingest_today_incremental` 每 2 小时增量同步恢复 fg 当日订单；清洗层（audit.cleaned_*）自动跟进，API/Web 无需改动。

---

## 执行记录（2026-09-24，SaaS 会话直执，快照 `backups-code-20260924/` + `*.bak-*-20260924`）

### R1 ✅ 已修验收
- 视图三处日期表达式改北京日（`r1-fix-view.sql`，diff 核对除三处外逐字一致；表名手滑 `spapi` 曾漏，diff 抓回）
- 验收：视图当日 1 单 → **18 单**（=活表北京日）；裸 `::date` 残留 0；`v_fittingirls_monthly_overhead` 等其余视图无切日操作未动（overhead 换底表属 09-09 R2 老单）
- 附带：mercury 死脚本 `fg_fetch_orders.py` 同步部署了 09-10 备好的 R1 补丁（归档性质，cron 已停）

### R2 ✅ 代码落地 + backfill
- `zf_amazon.py` 新增 `status_refresh`（fg 同款，每小时 full 后自动跑）+ `--status-only`（run.py 透传经 main() 解析）+ `STATUS_REFRESH_DAYS/PER_MARKET` 环境变量
- `fg_amazon.py` 窗口/批量参数化
- backfill（429 恢复型限速，分轮跑）：fg 存量 1,168 → 剩 ~23（连续 10 轮，金额覆盖 0%→96%）；zf 35 天内 110 → 2

### 执行中追查出的 zf 采集器三根因（用户实锤"SA/AE 在正常销售、全球站点订单都不对"后深挖）

**R2-a（P0）SA/AE 凭证丢失 → 断流 14 天**：
`sellers/v1/marketplaceParticipations` 实测——de.json 的 EU token 授权 16 市场但**不含 SA/AE**；旧 JS `fetch-today-orders.js`（09-03 死）本用独立 `sa.json`/`ae.json`（`/root/.openclaw/.../amazon-creds/`），zf_amazon.py 09-10 上线只搬了 de token → SA/AE 从上线日起拉不到单（表内两站订单停在 09-10，"138 单死单"实为断流期单据）。已找回两凭证接入 `secrets/zf_lwa/{sa,ae}.json`（同 LWA app、独立 rt）。

**R2-b（P0）token() 缓存串号**：缓存键只有 region——同 EU region 的 de/sa/ae 三套 rt 会互相复用第一个换到的 token（即使 a 修好凭证也全串）。已改键 `(region, rt)`。

**R2-c（P1）MARKETS 漏配 6 个已授权市场**：IE/TR/BE/CA/MX/BR 有授权从未采集（上线时清单照抄旧 JS 且从未对照授权范围）。已补齐 18 市场；首跑 BE 即进 4 单。

