Logistics operations platform物流运营平台Routing services, a read-only analytics spine, a five-surface delivery system and a production rebuild plan handed over to an external team.路径服务、只读的分析数据脊柱、五端配送系统，以及移交给外部团队的生产级重构方案。

01Scope范围

Context and my role背景与我的角色

In-house software for a cross-border freight and logistics operator, April 2024 to 2026. I was the sole developer on three workstreams. The production rebuild of the delivery platform went to an external engineering team.2024 年 4 月至 2026 年，为一家跨境货运与物流公司开发内部软件。我是三条工作线上唯一的开发者。派送平台的生产级重构交给了外部工程团队。

The business covers air and sea freight, customs clearance, warehousing and local delivery. Before this work, its operational facts sat in an existing operational system, spreadsheets and chat threads. Five groups touch each package: the public checking a tracking number, shipping customers, warehouse staff, the operations and dispatch team, and drivers.这家企业的业务包括空运与海运、清关、仓储和本地派送。在这些工作之前，运营事实分散在已有的业务系统、电子表格和聊天记录里。每个包裹会经过五类人：查询单号的公众、寄件客户、仓库人员、运营与调度团队，以及司机。

My title was Software Engineer (Operations Technology). I worked with the operations lead on requirements and, from June 2026, with an external engineering team that took over the production rebuild.我的职位是 Software Engineer（运营技术）。需求方面与运营负责人合作；2026 年 6 月起，与接手生产级重构的外部工程团队合作。

Routing and backend services路径与后端服务

Versioned REST services in FastAPI, with SQLAlchemy models and Pydantic request validation. Geospatial pipelines in GeoPandas, Shapely, OSMnx and NetworkX supply time windows, access rules and network friction to routing and allocation.基于 FastAPI 的带版本 REST 服务，使用 SQLAlchemy 模型和 Pydantic 请求校验。用 GeoPandas、Shapely、OSMnx 和 NetworkX 构建地理空间管线，为路径规划和派单提供时间窗、通行规则和路网阻抗。

02Data数据

Data spine before AI先数据脊柱，后 AI

Facts first, reconciled truth second, automation last. Reports read a mart built from a read-only replica; they never query production.先有事实，再有对齐后的真相，最后才是自动化。报表读取由只读副本构建的数据集市，从不直接查询生产库。

The roadmap I prepared for management put the work in four steps: turn operational events into structured facts; line up operational and financial records at one grain, the shipment; build forecasts on that history; then let agents act on it. Agents on top of unreconciled data give confident wrong answers. Most of the effort is master data and grain alignment, not models. The roadmap is a discussion paper, not an approved plan.我为管理层准备的路线图把工作分成四步：先把运营事件变成结构化事实；再把运营记录和财务记录对齐到同一粒度，即每一票货；然后在这些历史上做预测；最后才让 agent 基于这些数据执行动作。建立在未对齐数据上的 agent，只会给出看似可信的错误答案。大部分工作量在主数据和粒度对齐，而不是模型。这份路线图是讨论稿，不是已批准的计划。

1. Production read replica · read-only生产只读副本
2. Bounded aggregate ETL · approved analysis server有界聚合 ETL · 经批准的分析服务器
3. Server-local mart服务器本地数据集市
4. Snapshot / API bridge快照 / API 桥接
5. Vite + React + TypeScript

The replica is AWS RDS with a read-only user, reachable only from one approved analysis server; the mart is MariaDB on that server. No step writes to production.副本是 AWS RDS，使用只读账号，只能从一台经批准的分析服务器访问；数据集市是这台服务器上的 MariaDB。整条路径没有任何一步写入生产库。

What bounded means「有界」的含义

• Query timeouts. Every query is single-threaded and runs under a 10-second statement timeout. Scheduled refreshes keep that limit. A slow source is split into smaller keyset windows, not given more time.查询时限。每条查询单线程执行，语句时限 10 秒。定时刷新始终保持这一时限。慢的数据源拆成更小的 keyset 窗口，而不是放宽时限。
• Index-aware windows. Date windows follow existing indexes, and a query plan is checked with EXPLAIN before a new query runs.按索引取窗口。日期窗口沿已有索引划分；新查询执行前先用 EXPLAIN 检查执行计划。
• Aggregates, not rows. The ETL writes grouped metrics with a row cap into staging tables, then publishes them to the mart in one atomic step.只取聚合，不取明细。ETL 把带行数上限的分组指标写入 staging 表，再一次性原子发布到数据集市。
• Honest gaps. A source that cannot be aggregated inside the limit stays marked pending. Its module shows a pending card, not an estimated chart.如实留空。在时限内无法聚合的数据源保持「待定」，对应模块显示待定卡片，而不是估算出来的图表。

The dashboard has one data entry point: an adapter over a typed snapshot contract. A seeded, deterministic mock with the same shape stands in until the API is wired, and the presentation view warns when it shows mock data. Days a source has not loaded are null and appear as gaps, never as zeros. Every chart has a table view.看板只有一个数据入口：基于类型化快照契约的 adapter。API 接通之前，由结构相同、带固定种子的确定性 mock 代替；演示视图在显示 mock 数据时会给出警告。数据源未加载的日期记为 null，在图上显示为空缺，绝不显示为 0。每张图都附有表格视图。

The target design puts analytics in a cloud account separate from production. Production writes only approved exports; analytics owns the ETL, the reporting database, the dashboard API and the front end.目标设计把分析环境放在与生产分离的云账号里。生产端只写出经批准的导出；分析端负责 ETL、报表数据库、看板 API 和前端。

Status: exploration and mart validation; the separate analytics account is the target design.状态：探索与数据集市验证阶段；独立分析账号为目标设计。

03System系统

Five surfaces, one system五个端，一套系统

One system covers the five surfaces a package passes through, from the customer's manifest to the driver's proof of delivery.一套系统覆盖包裹经过的五个端，从客户提交 manifest 到司机的签收证明。

Stack: Next.js (React), Postgres and a React Native driver app. The five surfaces are five views of one set of package facts, not five systems.技术栈：Next.js（React）、Postgres，以及 React Native 司机 app。五个端是同一套包裹事实的五种视图，而不是五个系统。

What it covers覆盖的流程

It runs the full workflow: manifests, warehouse scans, discrepancies, staging, dispatch, driver jobs, POD, billing evidence and offline replay on mobile. In testing, camera QR scanning was quick and reliable, while camera scanning of 1D barcodes was not trusted, so the rebuild plan puts a QR code on every label and keeps laser scanners for barcodes.它覆盖完整流程：manifest、仓库扫描、差异、staging、调度、司机任务、POD、计费证据，以及移动端的离线重放。测试中，摄像头扫 QR 码又快又稳，摄像头扫一维条码则不可靠，所以方案要求每张面单都印 QR 码，条码仍用激光扫描枪。

Towards production走向生产

Production means many customers writing at once, so each business fact needs exactly one place that decides it. The rebuild plan moves the rules from database policies and triggers into a single backend command layer (section 04), keeps seed, reset and role-switching controls out of production, and, from a schema review of about 51 tables and 595 columns, moves 61 state, code and type fields to enums or bounded strings.生产环境意味着大量客户同时写入，每个业务事实都需要唯一的决定位置。重构方案把规则从数据库策略和触发器收拢到单一的后端命令层（见第 04 节），让造数据、重置和切换角色的控件留在生产之外，并根据对约 51 张表、595 个字段的结构审查，把 61 个状态、代码和类型字段改为 enum 或有长度约束的字符串。

Status: built by me; its screens and mobile workflow carry into the rebuild.状态：由我开发；界面和移动端流程沿用到重构中。

04Plan方案

Production rebuild plan: single writer生产级重构方案：单一写入方

Only the backend command layer may change a business fact. The plan was reviewed by the external team and handed over to them.只有后端命令层可以改变业务事实。这份方案经外部团队审阅后移交给他们。

The architecture review named the risks under production load: many customers submitting shipments at the same time, label uniqueness, database locking, bulk PDF generation, routing jobs, and front ends writing to the database directly. None of these is a traffic problem. Each is a correctness problem: two actions touching the same package at once, or one action applied twice.架构评审列出了生产负载下的风险：多个客户同时提交货件、面单唯一性、数据库锁、批量 PDF 生成、路线任务，以及前端直接写数据库。这些都不是流量问题，而是正确性问题：两个动作同时改同一个包裹，或者同一个动作被执行两次。

Facts, status, projections事实、状态、投影

• Facts record what happened: an order submitted, a scan accepted, a POD submitted, a delivery attempt failed. Append-only where possible.事实记录已经发生的事：提交了订单、扫描被接受、提交了 POD、一次派送失败。尽量只追加、不改写。
• Status is the stage derived from facts: package received, shipment partly delivered. Stored for speed, always explainable from facts.状态是由事实推出的当前阶段：包裹已入库、货件部分签收。为查询效率可以存下来，但必须能由事实解释。
• Projections are views for one audience: public tracking, the dispatch board, a driver's route for the day. They can be rebuilt, cached or delayed, and are never the source of truth.投影是给某类使用者看的视图：公开查询、调度看板、司机当日路线。可以重算、缓存或延迟更新，但永远不是事实来源。

One command, in order一条命令的执行顺序

1. Validate + authorise校验 + 鉴权
2. Idempotency check幂等检查
3. Lock + check transition加锁 + 校验状态转换
4. Write facts, status, audit, job写事实、状态、审计、任务
5. Commit提交
6. Worker: SMS, PDF, routingWorker：短信、PDF、路线

External calls never run inside a transaction. A command writes the fact and a job row in the same transaction; after commit, a worker sends the SMS or builds the PDF, with its own attempt count, retry schedule and dead-letter state.外部调用绝不放在事务里。命令在同一事务中写入事实和一条任务记录；提交之后，由 worker 发短信或生成 PDF，worker 自带尝试次数、重试计划和死信状态。

Customer order APIs require an Idempotency-Key: the same key returns the same result, and the same key with another payload is a conflict. The driver app's offline queue carries a replay key per action, so a POD replayed twice is accepted once. Worker jobs are idempotent as well.客户下单 API 必须带 Idempotency-Key：同一个 key 返回同一个结果，同一个 key 配上不同内容则返回冲突。司机 app 的离线队列为每个动作带一个 replay key，同一份 POD 重放两次也只接受一次。Worker 任务同样是幂等的。

Package-level state machine按件的状态机

The package is the unit of execution; shipment status is derived from package facts. Delivery, failed delivery, re-delivery, return to sender (RTS), temporary warehouse return and final closure are separate states with allowed transitions, so one shipment can be partly delivered and partly returned. One POD can cover many packages on a business drop: a POD record with one linked record per package, instead of a photo field on a shipment.包裹是执行的最小单位，货件状态由包裹事实推出。签收、派送失败、再派送、退回寄件人（RTS）、临时退回仓库和最终关闭是各自独立的状态，并规定允许的转换，因此一票货可以部分签收、部分退回。企业客户一次签收可以覆盖多个包裹：用一条 POD 记录加上每个包裹各一条关联记录来表示，而不是在货件上挂一个照片字段。

Labels and schema面单与数据结构

Bulk labels are a worker job: generated asynchronously, with progress shown and the file downloaded later, in chunks or a zip instead of one huge PDF. A label id is the shipment number plus a package suffix (A123-001, A123-002), so customer systems can match labels to shipments. Every label carries a QR code.批量面单是 worker 任务：异步生成、显示进度、稍后下载，分块或打包成 zip，而不是生成一个巨大的 PDF。面单编号是货件号加按件后缀（A123-001、A123-002），方便客户系统把面单对回货件。每张面单都印 QR 码。

Fields are typed: enums for stable workflow states, lookup tables for configurable ones, and bounded strings with format checks for tracking numbers, barcodes, postcodes and idempotency keys. Indexes on lookup-heavy paths go in from the start. Unique constraints are the last guard: two requests can pass an application check at the same moment, and the database rejects the second.字段要有类型：稳定的流程状态用 enum，可配置项用 lookup 表，单号、条码、邮编和幂等 key 用带长度和格式约束的字符串。高频查询路径一开始就建索引。唯一约束是最后一道防线：两个请求可能同时通过应用层检查，由数据库拒绝第二个。

The plan's first vertical slice: an order API with idempotency → package facts → first warehouse scan → derived status → tracking projection → one post-commit worker job → tests for simultaneous writes. Its test matrix lists duplicate orders, duplicate scans, dispatch races, double POD and offline replay. Redis, Kafka and a search cluster were deferred until a measurement calls for them.方案的第一条纵向切片：带幂等的下单 API → 包裹事实 → 仓库首次扫描 → 推出的状态 → 查询投影 → 一个提交后的 worker 任务 → 同时写入测试。测试矩阵包括重复下单、重复扫描、调度竞争、重复 POD 和离线重放。Redis、Kafka 和搜索集群推迟到有测量数据证明需要时再引入。

Status: designed, reviewed and handed over to the external team in June–July 2026.状态：2026 年 6–7 月完成设计与评审，移交外部团队。

05Handoff移交

Handoff and what stayed移交与保留

The handoff was a manual, reusable front-end modules, documentation and screenshots. The project paused in July 2026, waiting on the counterparties.移交内容包括操作手册、可复用的前端模块、文档和截图。项目于 2026 年 7 月暂停，等待合作方推进。

1. 2024-04Start as the in-house software engineer.以内部软件工程师身份入职。
2. 2026-06Architecture review; rebuild plan written.架构评审；编写重构方案。
3. 2026-06External team takes over the rebuild; weekly iteration cadence agreed.外部团队接手重构；约定每周迭代节奏。
4. 2026-07Handoff package delivered.移交材料交付。
5. 2026-07Read-only access granted; analytics exploration starts.获得只读访问权限；分析探索开始。

After the takeover my role was product validation: test each iteration against the business flow, collect driver and operator feedback, and turn it into clear issues. Issues were collected across several days and reviewed once a week.接手之后，我的角色是产品验证：按业务流程测试每次迭代，收集司机和运营的反馈，整理成清晰的问题。问题在几天内汇总，每周评审一次。

What stayed保留下来的部分

• The mobile workflow and its offline queue and replay contract, marked for reuse with a new API underneath.移动端流程及其离线队列和重放契约，标记为复用，底层换成新的 API。
• The existing screens as the front-end model, delivered as reusable modules.现有界面作为前端参考，以可复用模块的形式交付。
• Allocation as "suggest + human review": the system proposes jobs by suburb, postcode, route density and driver capacity; operations approves, splits or merges them. Not autonomous dispatch.派单保持「建议 + 人工审核」：系统按区域、邮编、路线密度和司机运力给出任务建议，由运营批准、拆分或合并。不做全自动调度。

One lesson一条经验

Settle decision rights with the receiving team before writing the plan, so that what the plan treats as decided stays decided through a handover.在写方案之前先和接手团队定好决策权，这样方案里已定的事项在交接之后依然有效。

06Rules规则

Data-access rules数据访问规则

Written before access was granted and followed during the exploration. These are rules, not a shipped AI assistant.在获得访问权限之前写好，并在探索期间遵守。这些是规则，不是已上线的 AI 助手。

• Read-only. A read-only database user, reachable from one approved server. No production writes, no schema changes.只读。使用只读数据库账号，只能从一台经批准的服务器访问。不写生产库，不改表结构。
• No raw export. No full-table exports and no unbounded queries. Select only the needed columns, window by date, aggregate in the database, cap the row count.不导出原始数据。不做整表导出，不跑无边界查询。只取需要的列，按日期限定窗口，在数据库端聚合，限制行数。
• Classify first. Each table is allowed, masked, aggregate-only or off-limits. Addresses, phone numbers, recipient and contact details and free-text notes are off-limits by default.先分级。每张表标为可用、脱敏、仅聚合或禁用。地址、电话、收件人和联系人信息以及自由文本备注默认禁用。
• Aggregate before any model call. Only aggregated, masked or synthetic data may reach an LLM: read-only SQL → aggregate → remove identifiers → check for personal data → small export.调用模型前先聚合。只有聚合、脱敏或合成的数据才能交给 LLM：只读 SQL → 聚合 → 去除标识 → 检查个人信息 → 小规模导出。
• Separate account. The target analytics layer lives in its own cloud account. Reports never query production.独立账号。目标分析层放在独立的云账号里。报表从不直接查询生产库。
• No secrets in the open. No credentials in code, notes, chat or screenshots. Snapshots with real data stay in ignored local folders, out of version control.不暴露凭据。代码、笔记、聊天和截图里都不出现凭据。含真实数据的快照只放在被忽略的本地目录，不进版本控制。

Before a dataset leaves the controlled server, a pre-export check asks: does it hold contact fields or free text; can any id identify a person; is the grain finer than the question needs; are the window and row count limited. If any answer is unclear, the dataset stays on the server. If personal data is exported by mistake, the extract is dropped and rebuilt from approved fields; nobody keeps building on it.数据集离开受控服务器之前，要过一遍导出前检查：是否含联系人字段或自由文本；是否有能识别到个人的 id；粒度是否比问题所需更细；窗口和行数是否受限。任何一项不确定，数据集就留在服务器上。如果误导出了个人信息，就弃用这份数据，用经批准的字段重新生成，不在它上面继续做。

Address analysis for routing needs its own approval path and uses postcode, suburb or zone aggregates, not street addresses. Findings carry a confidence label: verified, candidate or inferred.为路线做地址分析需要单独审批，并使用邮编、区域或服务区的聚合数据，而不是街道地址。分析结论都带可信度标签：已验证、候选或推断。

Status: ruled. Written as project documents and applied in the exploration phase; no LLM assistant ran on this data in production.状态：已定规则。以项目文档形式写成，并在探索阶段执行；没有任何 LLM 助手在生产环境中使用这些数据。

07Scope范围

Built, designed and ruled建成、设计与规则

The delivery system and services were built, the rebuild was designed and handed over, and the data-access rules were set. A detailed version and referees are available on request.配送系统与服务已建成，重构完成设计并移交，数据访问规则已制定。可应要求提供详细版本和推荐人。

• Built: the five-surface delivery system, the routing and backend services, the mart ETL and the dashboard front-end foundation (exploration).已建成：五端配送系统、路径与后端服务、数据集市 ETL 和看板前端基础（探索阶段）。
• Designed: the single-writer backend, the state machines, the label workers, the typed schema and the separate analytics account.已设计：单一写入方后端、状态机、面单 worker、类型化数据结构和独立分析账号。
• Ruled: the data-access rules in section 06.已定规则：第 06 节的数据访问规则。

Detailed version and referees on request可应要求提供详细版本和推荐人 →