给出差周报看板加上「详情弹窗高德地图标注 + 位置真实性审计」能力:坐标不进主表、全量进 append-only 流水表,记录表只留一个审计结论位;历经四个小版本(流水表落地 → UNION 500 修复 → 软删同事务联动 → 独立补录接口)最终全链路打通。本文沉淀数据模型设计、事务一致性方案、接口契约的取舍逻辑,以及 7 个真实踩坑,读者可以直接复用这套「流水表 + 结论位」的审计型数据模型。
1. 背景与目标
业务背景:销售/售前团队每周填报出差周报,管理侧需要一个看板查看每日出差明细。新需求有两层:
- 详情地图标注:点开某天的出差记录,能在高德地图上看到出差位置(多天多次提交的每个点都要标出来,并按提交时间连成轨迹);
- 位置真实性审计:填报的位置是否可信需要有一个可核验的结论(已验证/存疑/未验证),用于反作弊——「人当时是否真的在那个城市」。
约束条件:
- 服务器出网被认证网关拦截,任何依赖外网 API 的服务端逻辑都不可行,审计必须纯离线(点在多边形判定);
- 地图渲染只能发生在用户浏览器(高德 JS API),服务端只存坐标;
- 坐标系为 GCJ-02(火星坐标),全链路不转换;
- 历史数据有 112 条存量记录需要兼容。
成功标准:保存记录时坐标自动入流水、详情弹窗地图正常渲染、软删/恢复时流水与记录状态始终一致、审计结论位三态可展示。
2. 技术选型
2.1 坐标存哪:主表加列 vs append-only 流水表
| 维度 | 方案 A:travel_records 加经纬度列 | 方案 B:独立流水表 tb_travel_coord_log(选定) |
|---|---|---|
| 审计语义 | 只存最终态,丢失「每次提交」历史 | 每次提交一行,完整保留提交历史 |
| 多点轨迹 | 无法表达多次提交的多个位置 | 天然支持,按 create_time 连轨迹 |
| 图片/原文快照 | 需要再加列,且被后续提交覆盖 | 每行自带本次 raw_input + image_urls 快照 |
| 点级审计 | 做不到(一列一个结论) | 每行独立 audit_status/audit_time |
| 查询复杂度 | 简单 | 详情需额外查流水表 + 图片并集聚合 |
选 B 的核心理由:审计需要的是「每次提交」这个事件的事实,而不是最终态。后续补图、改坐标都不应改写历史行,只追加新行。代价是查询侧多一次聚合(图片取全流水并集),可接受。
2.2 坐标入口:MCP(智能体对话)vs REST(前端/直连)
对话链路里 LLM 拿到的通常只有城市名,LLM 没有「城市名→精确坐标」的可靠知识,让它填坐标等于让它编造一个格式合法的数字——这比没有坐标更糟:一个幻觉坐标会把记录标成「未验证」进入审计队列,而那个坐标根本不是事实。所以坐标入口只留给可信数据源(前端地图选点、浏览器定位),通过 REST 直连提交;智能体对话提交默认不带坐标(流水行照记,坐标为空)。
3. 架构设计
3.1 总体架构
┌─────────────────────────── 用户浏览器 ───────────────────────────┐
│ 出差看板 Next.js 前端 │
│ ├─ DailyTable(日历明细,「位」三色徽章) │
│ └─ DetailModal ── LocationMap 组件 │
│ ├─ AMap JS API 2.0(动态 import,key 构建时注入) │
│ └─ 多 Marker(点级三色)+ Polyline 提交轨迹 │
└──────────────┬───────────────────────────────────────────────────┘
│ ① 详情查询(含 coord_logs 流水)
┌──────────────▼─────────────── 服务器 ─────────────────────────────┐
│ Flask travel-api(gunicorn 多 worker) │
│ ├─ save-record-batch:保存记录 + 同事务追加流水 + 置结论位 │
│ ├─ POST /api/v1/coord-log:独立补录接口(v0.19.3) │
│ ├─ delete / re-save:同事务软删/恢复流水(_sync_coord_log_deleted)│
│ └─ get-by-id:流水查询 + 图片并集 │
└──────────────┬───────────────────────────────────────────────────┘
│ ② 同事务读写
┌──────────────▼────────────────────────────────────────────────────┐
│ PostgreSQL │
│ ├─ travel_records:业务主表 + coord_status 结论位(无坐标列) │
│ └─ tb_travel_coord_log:append-only 坐标流水(部分索引) │
└───────────────────────────────────────────────────────────────────┘
数据流走读(一次带坐标的保存):前端/智能体提交记录 → save-record-batch 解析出经纬度 → 同一事务内 INSERT travel_records + INSERT tb_travel_coord_log(坐标+原文+图片快照+来源)+ 坐标成对时 UPDATE coord_status='unverified' → commit。详情查询时一次带回全部未删流水行,前端按提交时间画 Marker 和虚线轨迹。
3.2 数据模型:流水表逐列设计
tb_travel_coord_log 结构与设计理由:
| 列 | 类型 | 设计理由 |
|---|---|---|
| id | varchar(64) PK | coord_ 前缀 + 时间戳 + 6 位随机,与业务表 ID 风格统一 |
| record_id | varchar(64) NOT NULL | 关联 travel_records.id;只允许 rec_ 前缀(出差计划不采集坐标) |
| longitude / latitude | numeric(10,6) 可空 | GCJ-02 原始值,服务端不做任何转换——高德直接吃 GCJ-02,而 GCJ-02→WGS84 转换有损,存储层转换会永久损失精度;空=本次提交无坐标 |
| raw_input | text | 本次提交的原始说明文本快照 |
| image_urls | text | 本次提交的图片 URL JSON 数组快照 |
| source | varchar(16) | agent=智能体提交 / manual=人工补录 / history=存量迁移。审计字段,见 3.4 |
| user_id | varchar(64) | 提交人 |
| audit_status / audit_time | varchar(16) / timestamp | 点级审计结论(夜间离线审计任务填写,只审 NULL 行实现幂等) |
| is_deleted | boolean | 软删标记,与所属记录可见性同事务联动(见 3.5) |
| create_time / update_time | timestamp | 提交时间 / 状态变更时间 |
索引只有两个:主键 + 部分索引 idx_coord_log_record ON (record_id) WHERE is_deleted = false——查询永远只看未删流水,软删行自动退出索引,不占索引体积。
主表 travel_records 只加一列 coord_status varchar(16)(verified/suspect/unverified,空=无坐标流水),不加经纬度、不加图片列——明细全在流水,主表只留「结论位」供列表页徽章快速渲染,避免列表查询 join 流水表。
3.3 写入路径设计:三个入口,一套事务语义
入口 1:save-record-batch(主路径)。保存记录时若带经纬度,同事务追加流水行并置结论位。多天行程按日期拆分成多条记录,每条各得一行流水。
入口 2:attach-images(补图)。v0.19.0 起补图不再 UPDATE 旧列,而是 INSERT 一条 source=manual 流水行(本次图片为快照),历史行不动。
入口 3:POST /api/v1/coord-log(v0.19.3 独立补录接口)。给前端地图选点、定位等可信数据源直连补录用。接口契约:
| 字段 | 必填 | 校验规则 |
|---|---|---|
| record_id | 是 | 须 rec_ 前缀、存在、归属认证头本人、未软删;404 文案区分「不存在/非本人/已软删」三种原因 |
| longitude / latitude | 否 | 严格校验:未传=合法(本次无坐标);传了但非数值或超范围(经 [73,136]、纬 [3,54])→ 400。与批量保存的静默容错刻意区分:专用接口必须让调用方感知坏数据 |
| raw_input | 否 | 本次说明文本快照 |
| image_urls | 否 | 数组 / JSON 数组字符串 / 逗号分隔字符串三形态兼容 |
| source | 否 | 白名单 agent/manual,缺省 manual;history 拒收(400) |
单事务完成:INSERT 流水 + 成对坐标置 coord_status='unverified' + 刷新记录 update_time + 事务内回读结论位与图片并集。记录删除/恢复时的流水联动复用既有逻辑(3.5),接口本身零额外处理。
source 为什么不让调用方随便传:source 是审计字段,记录「这行流水是谁提交的」。走 MCP 进来的调用,身份在传输层已知(=智能体),服务端直接写 agent,让 LLM 申报只会引入幻觉污染;history 只该由存量迁移脚本产生,任何客户端接口都不开放。REST 直连无法可靠推断调用方身份(前端补录是 manual、外部系统转发是 agent,两者用同一套认证头),所以只能白名单 + 缺省 manual 兜底,但迁移值永远拒收。
3.4 读取路径:图片并集与轨迹
get-by-id 详情返回 coord_logs(未删流水按提交时间升序)+ image_urls(全部流水行图片快照的并集,去重保序)。并集口径的原因:图片分散在多次提交的快照里,详情页要展示的是「这条记录累计关联了哪些图」,而流水行本身保留每次提交的原始快照供审计。
前端 LocationMap 组件要点:
- AMap loader 必须 useEffect 内动态 import——loader 模块顶层引用 window,SSR 下静态 import 直接崩;
- securityJsCode 必须在 loader 加载前挂到
window._AMapSecurityConfig(2021 年后申请的 key 不配会报 INVALID_USER_SCCODE); - 每个带坐标流水点一个 Marker,点级 audit_status 三色着色,label 为提交时间;按提交时间升序连虚线轨迹(带方向箭头,表达提交顺序而非真实移动路径);
- 无 key 或无坐标点时显示占位,不初始化地图。
3.5 事务一致性:流水软删与记录增删同事务联动
流水表的 is_deleted 必须始终镜像所属记录的可见性,否则会出现两类脏数据:记录删了流水还在(孤儿流水,详情查询不到但数据在);记录恢复后流水丢了(历史轨迹凭空消失)。实现是一个幂等辅助函数,在记录增删的同一事务内调用:
def _sync_coord_log_deleted(cur, record_id, deleted):
"""流水软删状态与所属记录同事务联动。幂等:仅按 record_id 整体翻转。"""
cur.execute(
"UPDATE tb_travel_coord_log SET is_deleted = %s, update_time = %s "
"WHERE record_id = %s", (deleted, now_str(), record_id)
)
三个挂载点:delete_record 删 records 时置 True(plan 无流水不动);save 的 UPDATE 分支恢复 is_delete=0 时置 False(被连带软删的流水一并恢复);首次 INSERT 无需处理。事务原子性是结构性的——所有写路径共用单个 cursor + 单次 commit,任何一步异常整体 rollback。
3.6 为什么不给 MCP 工具加坐标字段
MCP 工具的输入 schema 没有声明经纬度,这是刻意为之(见 2.2):LLM 填坐标是审计毒数据。但 MCP 层对 records 数组是原样透传的,如果调用方硬塞坐标字段,数据会流到批量端点并入库——功能上「暗中能通」,契约上「不支持」。这个状态是可接受的:正常 agent 不会传,真有坐标的场景走 REST 补录接口。
4. 踩坑记录(共 7 个)
坑 1:双表 UNION 列数不一致,所有列表查询 500
现象:v0.19.0 部署后,出差记录列表、统计等所有走双表 UNION 的查询全部 500,报错原文:
psycopg2.errors.SyntaxError: each UNION query must have the same number of columns
原因:v0.19.0 把 travel_records 的 raw_input/image_urls 旧列从查询里移除、换成 coord_status,但 travel_plan 分支仍保留 raw_input 列。records 分支去掉两列、只加一列,UNION 两侧列数对不上了。
解决:records 分支补对齐列,恒空串,明细以详情接口的流水查询为准:
SELECT ..., '' as raw_input, coord_status FROM travel_records ...
UNION ALL
SELECT ..., raw_input, '' as coord_status FROM travel_plan ...
预防:双表 UNION 的 SELECT 列表改动必须两侧同步数列;把「列数对齐」写进这类查询的修改检查项,部署后第一时间打一次列表接口冒烟。
坑 2:init_db 残留已 DROP 列的引用,gunicorn worker 崩溃循环
现象:执行迁移脚本 DROP 掉 travel_records.raw_input/image_urls 后重启服务,gunicorn worker 反复退出(exit 3)起不来,应用日志只有启动中断,traceback 要去 journalctl / error.log 里翻:
psycopg2.errors.UndefinedColumn: column travel_records.raw_input does not exist
原因:init_db 里残留三类旧列引用——幂等 ADD COLUMN image_urls(在 DROP 后执行会把旧列复活)、两条 COMMENT ON travel_records.raw_input/image_urls(DROP 后执行直接 UndefinedColumn 崩溃)、CREATE TABLE 模板仍含两列。本地开发与线上共用一个库,代码已停读写旧列但 DDL 还在引用。
解决:三处残留全部移除,保证新装库直接就是 v0.19 结构;已崩的服务重启恢复。
预防:DROP 列的迁移要全库 grep 该列名的所有 DDL 引用(ADD/COMMENT/CREATE TABLE/索引),一个不留;启动崩溃先看 journalctl 的 worker traceback,应用日志往往看不到 Python 堆栈。
坑 3:查询参数被静默忽略,误判为「残留测试数据」
现象:验证数据时用 GET /api/records?user_id=xxx 查指定人记录,返回的却是当前认证用户自己的记录,一度误判系统里有别人的残留测试数据。
原因:早期版本支持 user_id 查询参数,鉴权改造后该参数被 traveller 参数替代,但旧参数名不报错、直接被忽略,查询缺省回落到认证头用户自己。
解决:改用 traveller 参数;同时用 SQL 直查确认目标 user_id 确实无数据,排除残留疑虑。
预防:接口参数改名/废弃时,对旧参数名要么显式 400 要么打 deprecation 日志,静默忽略最坑;验证数据一律以 SQL 直查为准,API 返回只是参考。
坑 4:直接改库补坐标,绕过置位逻辑导致结论位缺失
现象:某条记录的流水行已有成对经纬度,但主表 coord_status 仍是 NULL,列表页徽章不显示。
原因:坐标是直接 UPDATE 流水行写进数据库的(调试行为),绕过了 API——置位逻辑(坐标成对时 UPDATE coord_status='unverified')只在 API 写入路径里执行,直改库不会触发。
解决:按设计语义做存量补齐:
UPDATE travel_records tr SET coord_status = 'unverified'
WHERE (tr.coord_status IS NULL OR tr.coord_status = '')
AND EXISTS (SELECT 1 FROM tb_travel_coord_log l
WHERE l.record_id = tr.id AND l.is_deleted = false
AND l.longitude IS NOT NULL AND l.latitude IS NOT NULL);
预防:调试期改数据也走接口(哪怕是 curl 直调补录接口),不要直改业务表;结论位这类派生字段要定期用类似上面的 EXISTS 语句做一致性巡检。
坑 5:NEXT_PUBLIC_ 环境变量是构建时注入,重启不生效
现象:把高德 key 写进 .env.production 后重启前端服务,页面地图仍是「未配置地图 Key」占位。
原因:Next.js 的 NEXT_PUBLIC_ 前缀变量在 next build 时被静态替换进 bundle,运行时读取环境变量毫无作用;只重启等于用旧 bundle 跑。
解决:改 env 后必须重新 npm run build 再部署;部署脚本自带构建步骤,已有新鲜产物时用 --no-build 跳过重复构建省时间。验证方法:在构建产物 chunk 里 grep key 值,命中才说明注入成功。
预防:把「NEXT_PUBLIC_ 变量变更 → 必须重建」写进部署清单;部署前用产物 grep 做注入确认,别等浏览器里发现。
坑 6:高德 Key 服务类型选错直接不可用
现象:创建 Key 时若服务平台选了「Web服务」,前端 JS API 加载会报 key 类型不匹配/鉴权失败。
原因:高德把 Key 按服务平台绑定:浏览器端 JS API 渲染必须选「Web端(JS API)」类型;「Web服务」是给后端调 REST 接口(地理编码、路径规划)用的,两者不通用。
解决:选「Web端(JS API)」类型创建,并同时拿到该 Key 的 securityJsCode(安全密钥),两个值都配进前端环境变量。
预防:申请 Key 前先明确使用场景——浏览器渲染选 Web端(JS API),服务端 REST 选 Web服务,混用必炸;2021 年后申请的 Key 一律要配 securityJsCode。
坑 7:自动化验证登录态的三连坑
现象:用 CDP 连 headless Chrome 验证线上地图渲染,第一步就卡住:访问内网地址报 SSO「未认证授权的服务」,换入口后又跳登录页,进了看板还找不到目标用户的行。
原因:三个独立问题——① 该服务的 SSO 注册只认外网域名,内网 IP 直连会被 CAS 拒绝;② headless Chrome 的 profile 没有有效 SSO 会话;③ 看板日历表的人名显示的是人员配置表的 display_name,不是账号 ID,按账号 ID 找行当然找不到。
解决:
# ① 统一走外网域名入口
# ② 服务端 Redis 直写一个冒烟会话(会话结构 {"user_id","display_name","department"},TTL 24h)
redis-cli -p 6379 SET session:<uuid> '{"user_id":"<用户名>","display_name":"<显示名>","department":""}' EX 86400
# 浏览器侧注入同名 Cookie(HTTPS 站点须 secure:true),测完 DEL 会话键
# ③ 按 display_name 定位表格行,从「执行明细」视图进详情弹窗
验证通过后立即删除冒烟会话,不留登录态后门。
预防:自动化验证内网系统前,先确认该服务的 SSO 注册域名;冒烟会话直写法要形成固定脚本(写会话→注 Cookie→验证→清理),避免每次现场推导。
5. 总结
最终形态:坐标全量进 append-only 流水表(每次提交一行,含坐标/原文/图片快照/来源),主表只留一个审计结论位;三个写入入口共享同一套事务语义,软删/恢复时流水与记录同事务联动,杜绝孤儿数据;详情侧流水升序返回、图片并集聚合,前端多 Marker + 提交轨迹渲染,key 缺失优雅降级占位。四个小版本迭代(流水表落地 → UNION 修复 → 事务联动 → 独立补录接口)全部线上冒烟验证通过。
经验教训:审计型需求优先考虑「事件流水 + 结论位」而不是「主表加列」——前者保留完整历史、天然支持点级审计,代价只是查询侧一次聚合;而审计字段(谁提交的)绝不能让被审计方自行申报,身份在传输层可判定的就服务端注入,判定不了的也要白名单收口。另一个反复被验证的教训是:任何「构建时注入」的配置,改完必须重建,任何绕过 API 的直改库操作都会漏掉派生逻辑。
后续计划:夜间离线审计任务(纯离线点在多边形判定,自动把结论位从 unverified 推进到 verified/suspect)尚未实施,是下一步的收尾工作。
转载自 CSDN-专业IT技术社区



