03 - Data model (schema reference)

Source: wt-v2-prototype-core at commit 3d70475a (branch main-v2-prototype), schemas under src/*/. Counts come from the local database tms_local_v2 (read-only look, taken 11 Nov 2026).

How to read this document

Topic	Rule in TMS v2
Stack	Plain NestJS 10 + Fastify + mongoose 9. No cc-core-lib.
Relations	Plain string ids or business codes. No mongoose populate. A few fields carry ref: 'x' only as a hint (the field catalog uses it to walk source_store_id.name).
Snapshots	Some documents copy part of another document when they are created (for example plan.store). The copy is not refreshed when the source changes.
Base fields	Every schema that extends BaseSchema gets _id, created_at, updated_at, __v and an unused _translates (Mixed). They are left out of the tables below. JSON output drops _id and __v and exposes id.
Enums	Almost no enum is enforced by mongoose. Statuses and types are plain strings, checked only by DTO validators or service code. Real schema-level enums: export_config.columns[].type (field, text, script), template.channel / page.paper / page.orientation, kafka_logs.direction / status.
Money	Decimal128 fields are turned back into numbers (2 decimals) when a document is serialised.
Required	"Required" in the tables means required: true in the mongoose schema. DTO-level rules are mentioned in the note.
Collection name	Model name is snake_case(ClassName), mongoose pluralises it. Exceptions are stated.

Collection count: 28 mongoose collections in code. Two more names in the spec list have no collection of their own: storage_setting (a settings record, see section 16) and field_catalog (computed at boot from the other schemas, see section 18).

Entity relationship diagram

Solid lines with a snapshot label mean the child copies fields of the parent. Plain labels mean only an id or code is stored.

Not drawn: kafka_logs, google_logs (stand-alone log tables joined by trace_id only) and counters (the running-number table).

Shared building blocks

These small shapes are reused inside several schemas. They are plain nested objects, not collections.

Name	Fields	Used in
GEO_POINT	latitude Number, longitude Number	delivery_order.source_geo / destination_geo, device.last_location, plan stop location
TIME_WINDOW	from String "HH:mm", to String "HH:mm" (wall clock, resolved against the plan date, never a timestamp)	plan shifts, rounds, stops, stop orders
CAPACITY	weight Number (kg), volume Number (m3)	plan shifts, tasks, stop load_after
USER_REF	user_id String (ref user), name String	plan driver / moved_by, plan_history by
VEHICLE_REF	code String (ref vehicle), plate_number String	plan shift and task vehicle

---

1. delivery_order

Purpose	One delivery job (DO) received from OMS over Kafka or created by API; the main transaction record.
Collection	delivery_orders
Points to	store (by source_store_id, plus snapshot), task (by current_task_id, never written)
Written by	Kafka consumer do_consume (create or update by delivery_no), admin API. Items service rewrites the totals.
Embedded snapshot	source_store = { id, code, name } copied from store when the message carries source_store.code; source_geo is also taken from the store location.
Local data	7 documents

Field	Type	Required	Default	Enum / values	Note
delivery_no	String	yes	-	-	Business key, unique. Generated as DO + 8 digits when missing.
type	String	no	-	DELIVERY, PICKUP, RETURN (DTO only)
delivery_type	String	no	-	STANDARD, ON_DEMAND (DTO only)
status	String	no	set to PENDING on create	PENDING, PLANNED, IN_TRANSIT, DELIVERED, FAILED, CANCELLED	Not enforced by schema. Only PENDING is ever set by code today (and every local row is PENDING). A status change enqueues the Kafka updates.
source_store_id	String (ref store)	no	-	-	Store id. Not indexed.
chanel	Mixed	no	-	-	Spelling is from the source system. Object or array; chanel_code / chanel_name. Code decides routing: DONJAI, CHOKCHAI, HLH to OMS; SPO, OD, HLH to staff app.
source_from_store	Boolean	no	true	-
source_store	Mixed	no	-	-	Snapshot { id, code, name }.
source_address	Mixed	no	-	-	Free-form address object.
source_geo	GEO_POINT	no	-	-	Resolved from several candidate paths in the payload.
destination_to_store	Boolean	no	false	-
destination_address	Mixed	no	-	-	Free-form; planner reads first_name, last_name, street1.
destination_geo	GEO_POINT	no	-	-	Planner skips orders without a latitude.
expect_delivery_date	Date	no	-	-
delivery_time_window	Mixed	no	-	-	Shape { from, to } as "HH:mm". Not typed as TIME_WINDOW here.
current_task_id	String (ref task)	no	-	-	Never written by any service. Indexed.
total_weight	Number	no	-	-	kg. Recomputed from items.
total_volume	Number	no	-	-	m3. Recomputed from items.
total_amount	Decimal128	no	-	-	Recomputed from items.
item_count	Number	no	-	-	Recomputed from items.
is_cod	Boolean	no	false	-
cod_amount	Decimal128	no	-	-
note	String	no	-	-
source	Mixed	no	-	-	Raw Kafka payload block (source[0].source_address[0].latlong is read from it). Not in the DTO.
destination	Mixed	no	-	-	Raw Kafka payload block. Not in the DTO.
items	Mixed	no	-	-	Raw Kafka item list. Not the same thing as collection delivery_order_items.

Indexes

Index	Kind
delivery_no	unique
current_task_id	normal

2. job_counter

Purpose	Per-DO event sequence number, used as the version of outgoing do_update_* messages. Its own collection so deleting finished jobs never resets a version.
Collection	job_counters (class name JobCounter, file delivery_order_version.schema.ts)
Points to	delivery_order (_id is the delivery_no)
Local data	4 documents

Field	Type	Required	Default	Enum	Note
_id	String	-	-	-	The delivery_no.
seq	Number	no	0	-	Incremented in the same transaction as the DO change.

Indexes: only _id. No created_at / updated_at (versionKey: false, but it still extends the base factory).

3. delivery_order_item

Purpose	One product line of a delivery order.
Collection	delivery_order_items
Points to	delivery_order (delivery_order_id and delivery_no)
Written by	Item API only (create, update, delete); each write recomputes the totals on the parent DO. The Kafka intake does not create rows here (it keeps the raw list in delivery_order.items).
Local data	0 documents

Field	Type	Required	Default	Enum	Note
item_id	String	yes	-	-	Unique; uuid when not supplied.
delivery_order_id	String (ref delivery_order)	no	-	-	Set from the DO found by delivery_no.
delivery_no	String	no	-	-	Duplicate of the parent key; DTO makes it required.
product_sku	String	no	-	-
product_name	String	no	-	-
quantity	Number	no	-	-
uom	String	no	-	-
amount	Decimal128	no	-	-
weight_kg	Number	no	-	-	Summed into delivery_order.total_weight.
volume_cbm	Number	no	-	-	Summed into delivery_order.total_volume.

Indexes: item_id unique; delivery_order_id; delivery_no; product_sku.

4. delivery_order_revision

Purpose	Intended trail of every placement of a DO onto a task (revision goes up on each reschedule).
Collection	delivery_order_revisions
Points to	delivery_order, task, plan (all by id string)
Written by	Nobody. createRevision exists in the service but no code calls it and the module has no controller.
Embedded snapshots	delivery_order (Mixed) and task (Mixed) were meant to hold full copies at that moment.
Local data	0 documents

Field	Type	Required	Default	Enum	Note
code	String	no	uuid set by service	-	Unique.
delivery_order_id	String	no	-	-
delivery_no	String	no	-	-
revision	Number	no	latest + 1	-	nth placement of this DO.
task_id	String	no	-	-
task_no	String	no	-	-
plan_id	String	no	-	-
reason	String	no	-	-
delivery_order	Mixed	no	-	-	Snapshot of the DO.
task	Mixed	no	-	-	Snapshot of the task.

Indexes: code unique; delivery_order_id + revision(-1); task_id + created_at(-1).

5. task

Purpose	Intended stand-alone delivery task (one round for one driver) with a running number TK########.
Collection	tasks
Points to	Nothing in the schema.
Written by	Nobody. createTask is not called anywhere; there is no controller. Real tasks live inside plan.tasks (section 6), keyed by task_key.
Local data	0 documents

Field	Type	Required	Default	Enum	Note
task_no	String	no	generated TK + 8 digits	-	Unique. The only field.

Indexes: task_no unique.

6. plan

Purpose	One day of route planning for one store: declared shifts, the optimiser output (tasks, stops) and the unallocated pool, all in a single document.
Collection	plans
Points to	store (snapshot), plan_template (snapshot), vehicle_type (values copied into shifts), vehicle and user (refs inside shifts and tasks), delivery_order (ids inside stop orders)
Written by	Plan API (create from template), optimiser (replaces tasks, unallocated, optimize, sets status to planned), stop operations (move / assign / unassign edit tasks in place).
Embedded snapshots	store = { id, code, name, location }; plan_template = { id, code, name }.
Local data	0 documents

Top-level fields

Field	Type	Required	Default	Enum / values	Note
code	String	no	uuid set by service	-	Unique business key.
name	String	no	-	-
target_date	Date	no	-	-	The delivery day.
store	Mixed	no	-	-	Snapshot, see above. Indexed through store.code.
status	String	no	pending on create	pending, planned, generated	generated is only checked, never set. See oddities.
start_date	Date	no	-	-	target_date + store open_time.
end_date	Date	no	-	-	target_date + store close_time.
plan_template	Mixed	no	-	-	Snapshot, see above.
shifts	array of Shift	no	[]	-	Declared resource, one row per vehicle per day (one Gantt row). Built from the template and vehicle types at create time.
tasks	array of Task	no	[]	-	Optimiser output; each one is a round (depot, deliveries, depot).
unallocated	array of Unallocated	no	[]	-	Orders the optimiser could not place.
optimize	Mixed	no	-	-	{ revision, ran_at, ran_by, engine: 'google_ro', summary }. revision goes +1 per successful run and is the optimistic-lock value.
generated	Mixed	no	-	-	Meant to be { task_ids, generated_at, generated_by }. Never written.

Shift (shifts[], no _id)

Field	Type	Default	Note
shift_index	Number	-	Position in the array; tasks point back to it.
shift_key	String	-	Stable uuid for the life of the plan.
shift_label	String	-	Example 4_wheels-1.
vehicle_type	String	-	Vehicle type code.
capacity	CAPACITY	-	Copied from the vehicle type.
loading_time	Number	0	Minutes at depot before each round.
unloading_time	Number	0	Minutes per order at a stop.
window	TIME_WINDOW	-	Vehicle availability.
rounds[]	{ round Number, window TIME_WINDOW }	-	Currently always one round.
vehicle	VEHICLE_REF	-	Empty until real vehicles are bound; no code binds it yet.
driver	USER_REF	-	Empty; no code sets it yet.

Task (tasks[], no _id)

Field	Type	Default	Note
task_key	String	-	Stable uuid; drag-and-drop target.
shift_key, shift_index, round, seq	String, Number, Number, Number	-	Where the task sits in the plan.
shift_label, vehicle_type, capacity, round_window, vehicle, driver	mixed	-	Copied from the shift so the task renders alone.
planned_start_at, planned_end_at	Date	-	Optimiser vehicle start / end time.
stops[]	array of Stop	-	See next table.
total_weight, total_volume	Number	0	Derived.
utilization	{ weight_percent, volume_percent }	0 each	Against this round only.
total_distance_m, total_duration_s, order_count, stop_count	Number	0	Derived.
route_polyline	String	-	Encoded polyline of the whole round.
color	String	-	One of 8 fixed trip colours.
violations[]	{ code, message, stop_seq }	-	Codes: EXCEED_SHIFT_WINDOW, EXCEED_WEIGHT, EXCEED_VOLUME, LATE_AT_STOP, STALE_ETA (the type file also lists ROUND_OVERLAP, INSUFFICIENT_TURNAROUND).
is_dirty	Boolean	false	Edited by hand since the last optimise.

Stop (tasks[].stops[], no _id)

Field	Type	Default	Note
stop_key	String	-	Stable across moves.
seq	Number	-	Order inside the task.
type	String	-	DEPARTURE, DELIVERY, RETURN.
location	GEO_POINT + name, address	-
eta, etd	Date	-
service_duration_s, distance_from_prev_m, duration_from_prev_s	Number	0
is_eta_stale	Boolean	false	True after a manual move until the next optimise.
orders[]	{ delivery_order_id, delivery_no, customer_name, weight, volume, cod_amount, time_window }	weight / volume / cod 0	Snapshot of each DO at this stop.
total_weight, total_volume	Number	0
load_after	CAPACITY	-	Running load after this stop.
time_window	TIME_WINDOW	-	Narrowest window among the orders.
is_late	Boolean	false	eta after time_window.to.
is_pinned	Boolean	false	Moved by hand; kept on re-optimise.
moved_from_task_key, moved_at, moved_by	String, Date, USER_REF	-	Manual move trail.

Unallocated (unallocated[], no _id)

Field	Type	Note
delivery_no	String
reason_codes[]	String	Google Route Optimization reason codes, or MANUALLY_UNASSIGNED.
reason_label	String	Thai wording.

Indexes

Index	Kind
code	unique
store.code + target_date	normal

7. plan_history

Purpose	The plan screen's own audit trail: one row per optimise run or manual stop operation.
Collection	plan_histories
Points to	plan (by plan_code), user (by by.user_id)
Written by	Plan optimise and stop-operation services, inside the same transaction as the plan save (each write also leaves an activity_logs row). A failed optimise writes a stand-alone row with error.
Local data	0 documents

Field	Type	Required	Default	Enum / values	Note
event_key	String	yes	-	-	Unique: plan code + revision + action.
plan_code	String	yes	-	-
at	Date	yes	now	-
by	USER_REF	no	-	-	Absent when the system acted.
action	String	yes	-	CREATE, RE_PLAN, RECALCULATE, MOVE_STOP, ASSIGN_STOP, UNASSIGN_STOP, RESET	Only RE_PLAN, RECALCULATE, MOVE_STOP, ASSIGN_STOP, UNASSIGN_STOP are written today.
revision	Number	no	-	-	Plan optimise revision at that moment.
delivery_nos	[String]	no	-	-
from, to	{ task_key, shift_label, round }	no	-	-	Position before / after.
mode	String	no	-	-	Never written. scope is used instead.
scope	String	no	-	all, dirty
affected_task_keys	[String]	no	-	-
diff	Mixed	no	-	-	{ assigned_in, assigned_out, moved, resequenced, unchanged_count }.
duration_ms	Number	no	-	-
log_id	String	no	-	-	Meant to point at the heavy Google payload; never written.
error	Mixed	no	-	-	{ code, message } on a failed run.

Indexes: event_key unique; plan_code + at(-1); by.user_id + at(-1).

8. plan_template

Purpose	Reusable recipe of shifts (vehicle type, how many, start and end time) a plan is created from.
Collection	plan_templates
Points to	vehicle_type (by code inside shifts[].vehicle_type)
Local data	0 documents

Field	Type	Required	Default	Enum	Note
code	String	no	uuid set by service	-	Unique.
name	String	no	-	-
shifts	Mixed	no	-	-	Array of { vehicle_type code, amount (min 1), start_time "HH:mm", end_time "HH:mm" }, shape enforced by the DTO only.

Indexes: code unique.

9. vehicle_type

Purpose	Class of truck with its capacity and handling times.
Collection	vehicle_types
Points to	Nothing.
Local data	0 documents

Field	Type	Required	Default	Enum	Note
code	String	no	-	-	Unique (declared twice, see oddities).
name	String	no	-	-
capacity	Mixed	no	-	-	{ weight kg, volume m3 }; DTO requires both numbers.
loading_time	Number	no	0	-	Minutes at depot per round.
unloading_time	Number	no	0	-	Minutes per order; planner falls back to 20 when 0.

Indexes: code unique (twice).

10. vehicle

Purpose	A physical truck identified by plate.
Collection	vehicles
Points to	vehicle_type (snapshot)
Embedded snapshot	vehicle_type = { id, code, name } copied on create and update.
Local data	0 documents

Field	Type	Required	Default	Enum	Note
code	String	yes	-	-	Unique. DTO makes it optional but the code that should default it contains a comparison (==) instead of an assignment, so a missing code fails on save.
status	String	no	-	-	Never written.
plate_number	String	no	-	-
type	String	no	-	-	Never written; overlaps vehicle_type.
delivery_support_type	String	no	-	-	Never written.
vehicle_type	Mixed	no	-	-	Snapshot.
loading_time	Number	no	-	-	Never written; the vehicle type holds this value.
unloading_time	Number	no	-	-	Never written.

Indexes: code unique. No index on plate_number.

11. store

Purpose	A store or depot: where deliveries start and the opening hours that bound a plan day.
Collection	stores
Points to	Nothing.
Local data	2 documents (001, 002)

Field	Type	Required	Default	Enum	Note
code	String	yes	-	-	Unique. Same == problem as vehicle when no code is sent.
name	String	no	-	-
open_time	String	no	-	-	"HH:mm". Plan start_date uses it.
close_time	String	no	-	-	"HH:mm".
location	Mixed	no	-	-	{ latitude, longitude }; DTO types both as strings, then parsed with pickGeo.
loading_bay	Number	no	-	-	Stored; not read by any service.

Indexes: code unique.

12. user

Purpose	Login account for admin, store operator, driver and the system user.
Collection	users
Points to	Nothing (role is a fixed enum, there is no role collection in code).
Local data	12 documents

Field	Type	Required	Default	Enum	Note
user_id	String	yes	-	-	Unique uuid.
username	String	yes	-	-	Unique.
first_name, last_name	String	no	-	-
role	String	no	-	admin, store_operator, driver, system_admin	system_admin only for the boot-seeded system user.
state	String	no	-	staged, active, inactive, locked_out, suspended
password	String	no	-	-	Salted hash. Hidden from API output.
last_logged_in_at	Date	no	-	-
system	Boolean	no	-	-	True for the internal system user.

Indexes: user_id unique; username unique (declared twice).

13. application

Purpose	A client app (admin web, driver app, integration) allowed to ask for access tokens, with its key pair.
Collection	applications
Points to	Nothing (roles holds role names).
Local data	3 documents

Field	Type	Required	Default	Enum	Note
name	String	yes	-	-	Unique.
code	String	yes	-	-	Unique. default is the boot-seeded one.
app_key	String	no	generated 32 chars	-	Unique.
secret_key	String	no	generated 44 chars	-	Masked in API output.
enabled	Boolean	no	-	-
roles	[String]	no	-	user role names	Which roles may sign in through this app.
enable_2fa	Boolean	no	-	-
require_device	Boolean	no	-	-	Driver apps must register a device.

Indexes: name unique; app_key unique; code unique.

14. device

Purpose	A driver's phone registered on login; keeps last position and activity.
Collection	devices
Points to	user (by user_id)
Local data	0 documents

Field	Type	Required	Default	Enum	Note
device_id	String	no	-	-	Unique.
user_id	String	no	-	-	Not indexed.
status	String	no	-	-	No service sets it.
name, brand, platform, platform_version, app_version	String	no	-	platform: android, ios (DTO)
last_logged_in_at, last_actived_at	Date	no	-	-	Updated on register and heartbeat.
last_location	GEO_POINT	no	-	-	Updated by heartbeat.

Indexes: device_id unique.

15. attachment

Purpose	Metadata of a file uploaded to S3 through presigned URLs, with virus-scan state.
Collection	attachments
Points to	user (by uploader_id)
Written by	Attachment API and the sweep job (every minute, fails stale pending / scanning rows).
Local data	36 documents (statuses seen: ready, failed; purpose seen: admin_file)

Field	Type	Required	Default	Enum / values	Note
purpose	String	no	-	admin_file	Defined in code (attachment.purposes.ts): jpg, jpeg, png, webp, pdf, max 20 MB.
visibility	String	no	-	private
source	String	no	-	v2
incoming_key	String	no	-	-	S3 key under v2/incoming. Unique.
key	String	no	-	-	S3 key under v2/files once confirmed.
name, size, mime, detected_mime	String / Number	no	-	-
scan_status	String	no	-	pending, clean, infected
uploader_id	String	no	-	-
status	String	no	-	pending, scanning, ready, failed
failed_reason	String	no	-	-
scanning_started_at, confirmed_at	Date	no	-	-

Indexes: status + created_at; uploader_id + status; incoming_key unique.

16. settings (and the storage setting)

Purpose	One document per setting record (log, authen, storage, kafka, google, script); keys inside values. Code declares each key with a default; the document only stores overrides.
Collection	_settings (explicit name, because the old v1 settings collection already exists in the same db)
Points to	Nothing. Each change also writes an activity_logs row (entity setting).
Local data	6 documents (one per record)
Storage setting	There is no separate storage schema or collection. "storage_setting" is the setting record with code storage, owner storage_setting; see 06-master-data.

Field	Type	Required	Default	Enum	Note
code	String	yes	-	-	Record code, unique. Pattern ^[a-z][a-z0-9_]*$.
owner	String	no	-	-	Module that declared the record.
name	String	no	-	-	Display name.
rev	Number	no	0 (boot inserts 1)	-	Optimistic lock: a save must send the current rev.
values	Mixed	no	{}	-	Key to value. Encrypted keys hold an enc string; the API only returns a mask.
updated_by	Mixed	no	-	-	{ id, name }.
updated_at	Date	no	-	-	Declared by hand because mongoose updatedAt is switched off for this schema.

Indexes: code unique.

17. export_config

Purpose	Admin-defined spreadsheet export for one entity: columns, optional script, footer sums.
Collection	export_configs
Points to	A script entity by name (entity), checked against the field catalog.
Local data	0 documents

Field	Type	Required	Default	Enum	Note
name	String	yes	-	-
entity	String	yes	-	script entity name
script	String	no	-	-	Sandboxed JS.
script_reads	[String]	no	-	-	Entities the script may read.
columns[]	{ type, path, text, header, format, align, width, footer_sum }	no	[]	type: field, text, script
revision	Number	no	0	-	Optimistic lock on update.
created_by, updated_by	String	no	-	-	User id.

Indexes: only _id. No index on entity or name.

18. template (and the field catalog)

Purpose	Print, email or SMS layout rendered from an entity with a small script and a body.
Collection	templates
Points to	A script entity by name (entity).
Local data	2 documents (both QA fixtures on entity plan)
Field catalog	field_catalog has no collection. A service builds, at application boot, a list of fields per script entity (delivery_order, delivery_order_item, vehicle, plan, user, store) by reading the mongoose schemas, minus fields the read API hides. Exports and templates validate paths against it.

Field	Type	Required	Default	Enum	Note
code	String	yes	-	-	Unique.
name	String	yes	-	-
channel	String	yes	-	print, email, sms
entity	String	yes	-	script entity name
page	sub-document	no	-	paper: A4, A3, A5, Letter; orientation: portrait, landscape	Also margin_mm, font_url, font_family.
subject	String	no	-	-	Email only.
script	String	no	-	-
body	String	no	-	-
revision	Number	no	0	-	Optimistic lock.
created_by, updated_by	String	no	-	-

Indexes: code unique.

19. jobs

Purpose	Durable outbox and work queue: Kafka sends, schedule runs and other side effects, split into 64 partitions with a fast and a long lane.
Collection	jobs
Points to	schedule (target schedule:<code>), job_partition (by partition)
Local data	0 documents (successful jobs are deleted by the workers; failed ones stay as DEAD until retried through the jobs API)

Field	Type	Required	Default	Enum	Note
code	String	yes	-	-	Event name, for example do_update_oms.
target	String	yes	-	-	Handler name, for example a Kafka topic target or schedule:<code>.
lane	String	yes	-	fast, long
key	String	no	-	-	Ordering key, for example delivery_no.
partition	Number	yes	-	0 to 63
version	Number	no	0	-	Event sequence for the key.
payload	Mixed	no	-	-
dedupe_key	String	yes	-	-	Unique, for example do_update_oms:<delivery_no>:<version>.
status	String	no	PENDING	PENDING, RUNNING, DEAD
attempts	Number	no	0	-	Max 6 (retry after 1, 2, 4, 8, 16 minutes).
next_run_at	Date	no	-	-
last_error	String	no	-	-
claimed_by, lease_until, claim_token, outcome_token, running_code	String / Date	no	-	-	Lease bookkeeping. running_code is unique among RUNNING rows (one run of an exclusive code at a time).
closed_at	Date	no	-	-
trace_id	String	no	-	-
actor	Object	no	-	-	Who enqueued it.
headers	Object	no	-	-	Kafka headers such as event_id.

Indexes: dedupe_key unique; lane + partition + next_run_at (only PENDING); lease_until (only RUNNING); running_code unique (only RUNNING with the field set).

20. job_partition and job_worker

Purpose	Lease table for the 64 job partitions, and one heartbeat row per live pod, so pods share the partitions.
Collections	job_partitions, job_workers
Points to	job_partition.owner = job_worker.pod_id
Local data	64 partitions, 3 workers

job_partitions

Field	Type	Required	Default	Note
partition	Number	yes	-	0 to 63, unique.
owner	String	no	null	Pod id holding the lease.
lease_until	Date	no	-	Lease 30 s, heartbeat 10 s.
inflight	[String]	no	[]	Job ids running now; re-run by a pod that takes over.
inflight_token	String	no	-

Indexes: partition unique; owner.

job_workers

Field	Type	Required	Default	Note
pod_id	String	yes	-	Unique.
heartbeat_at	Date	yes	-

Indexes: pod_id unique; heartbeat_at. No TTL: rows of dead pods stay until cleaned by code.

21. schedule

Purpose	Cron table. Code declares the cron, missed-slot policy and retry count; admin owns enabled and cron_override.
Collection	schedules
Points to	jobs through target schedule:<code>
Written by	Boot sync (upsert of declared schedules), schedules API.
Local data	5 documents, all qa_* test rows. No feature module declares a real schedule yet.

Field	Type	Required	Default	Enum	Note
code	String	yes	-	-	Unique.
cron	String	yes	-	-	Fires in a fixed UTC+7 offset.
enabled	Boolean	no	true	-
cron_override	String	no	-	-	Admin replacement of cron.
missed_slot_policy	String	no	run_once	run_once, skip	Crons running every 5 minutes or faster default to skip.
retry_count	Number	no	3	-
last_run_at, last_skipped_at, next_run_at	Date	no	-	-

Indexes: code unique; next_run_at (only enabled: true).

22. Log collections (activity_logs, kafka_logs, google_logs)

All three share the base log fields and are built by the log registry: collection name = log key, indexes are created at boot (not by mongoose autoIndex), every row has an expire_at and a TTL index (expireAfterSeconds: 0). Retention days are a setting (log.<key>), read when the row is written, so a change applies to new rows only. activity_logs is written inside the business transaction; the other two are buffered (flushed every 1 s or 100 rows, capped at 1000 rows per pod).

Base fields (all three)

Field	Type	Required	Note
at	Date	yes	Event time.
expire_at	Date	no	at + retention days; TTL.
trace_id	String or null	no	Request or job trace. Indexed on kafka_logs and google_logs, not on activity_logs.

22.1 activity_logs

Purpose	Business audit trail: who did what to which record, with a before / after diff.
Collection	activity_logs
Points to	Any entity by entity + entity_id; user by actor.id
Local data	142 documents; entities seen: delivery_order, setting, user, role (the role rows have no writer in the current code; no role schema exists)
Default retention	60 days

Field	Type	Required	Note
entity	String	yes	Examples delivery_order, plan, setting.
entity_id	String	yes	Record id or code.
action	String	yes	In code today: do-create, do-update, setting-change, plus plan actions from plan history.
action_id	String	yes
actor	{ type, id, role, name, via, meta }	no	type is user or system.
channel, reason	String	no
changes	Mixed	no	Field-level diff; secrets are recorded as { key, changed: true } only.
changes_truncated, changes_bytes	Boolean, Number	no	Large diffs are cut.
refs	Mixed	no	Extra context, for example the plan history event.
event_key	String	yes	Unique; makes a replayed event a no-op.

Indexes: expire_at TTL; entity + entity_id + at(-1) + _id(-1); actor.id + at(-1) + _id(-1); event_key unique.

22.2 kafka_logs

Purpose	One row per Kafka message sent or handled.
Collection	kafka_logs
Local data	4 documents (topics seen include bigc.lastmiles.do-post, lastmiles.oms.do-update, lastmiles.staffapp.do-update)
Default retention	60 days

Field	Type	Required	Enum	Note
direction	String	yes	in, out
topic, key, message_id	String	no	-
partition	Number	no	-
offset	String	no	-
req_body, res_body	Mixed	no	-	Bodies, truncated by size.
req_body_truncated, res_body_truncated	Boolean	no	-
req_body_bytes, res_body_bytes	Number	no	-
status	String	yes	sent, consumed, failed
error	{ message }	no	-

Indexes: expire_at TTL; trace_id; at(-1).

22.3 google_logs

Purpose	One row per outbound Google call (token request, route optimisation).
Collection	google_logs
Local data	0 documents
Default retention	30 days

Field	Type	Required	Enum	Note
system	String	yes	-	google.
operation	String	yes	-	token, optimize.
method, url	String	no	-
status	Number	no	-	HTTP status.
outcome	String	yes	OK, ERROR, TIMEOUT
duration_ms, attempt	Number	no	-
req_headers, res_headers, req_body, res_body	Mixed	no	-
req_body_truncated, res_body_truncated, req_body_bytes, res_body_bytes	Boolean / Number	no	-
error	{ code, message }	no	-

Indexes: expire_at TTL; trace_id; system + operation + at(-1).

23. Kafka intake bookkeeping (kafka_consumed, kafka_consume_dlq)

Purpose	kafka_consumeds de-duplicates inbound messages by consume_id (claim before handling). kafka_consume_dlqs keeps messages that failed permanently so ops can inspect and replay.
Collections	kafka_consumeds, kafka_consume_dlqs
Points to	Each other through consume_id; no business entity.
Local data	5 consumed, 0 dead letters

kafka_consumeds

Field	Type	Required	Default	Note
consume_id	String	yes	-	Unique.
event	String	yes	-	Declared event name, for example do_consume.
topic	String	no	-
status	String	no	processing	Set to done after success.
consumed_at	Date	no	now	TTL anchor.

Indexes: consume_id unique; consumed_at TTL 7 days.

kafka_consume_dlqs

Field	Type	Required	Default	Note
consume_id	String	yes	-	Unique.
event	String	yes	-
topic, key, offset	String	no	-
partition	Number	no	-
raw	Mixed	no	-	Original payload.
error	String	no	-
attempts	Number	no	1
status	String	no	pending	resolved once a retry succeeds.
failed_at	Date	no	now

Indexes: consume_id unique. No TTL, so rows stay until removed by hand.

24. counters (running numbers)

Purpose	Atomic counters for human-readable numbers: DO + 8 digits for delivery orders, TK + 8 digits for tasks.
Collection	counters (model counter)
Defined in	src/common/utils/running_number.ts (an inline schema, not a *.schema.ts file). The file also calls mongoose.connect at import time, which is why it is easy to miss.
Local data	0 documents (every local DO has an explicit delivery_no)

Field	Type	Required	Note
code	String	yes	Counter name (delivery_order, task). Unique.
prefix	String	no	Upper-cased. A different prefix restarts the counter at 1.
counter	Number	no

---

Schema oddities worth raising

#	Finding	Where	Why it matters
1	Collections that nothing writes: tasks, delivery_order_revisions (services exist, no caller, no controller). Field delivery_order.current_task_id is also never written.	task, delivery_order_revision, delivery_order	Tasks live inside plan.tasks; decide whether the standalone collections stay in the model.
2	plan.generated and status generated are never set (only a guard reads it). plan_history.mode and log_id are never written.	plan, plan_history	The "generate" step after planning is not built yet.
3	Plan status spelling differs: enum says pending, the type file says draft.	plan.enum.ts vs plan.types.ts	Front end must follow the enum (pending).
4	Vehicle fields never written: status, type, delivery_support_type, loading_time, unloading_time. type overlaps vehicle_type. plan.shifts[].vehicle and driver are also never bound.	vehicle, plan	Dead columns; trucks are not used by the planner yet.
5	Code default for store and vehicle is data.code == uuid (comparison, not assignment). A request with no code fails on the required unique field.	store.service, vehicle.service	Bug; DTO says code is optional for vehicle.
6	Same unique index declared twice (field unique: true plus schema.index): vehicle_type.code, user.username. Mongoose warns about duplicate index.	vehicle_type, user	Harmless, remove one.
7	Duplicated data inside a DO: source / destination raw blocks beside source_address / destination_address / *_geo; source_store_id beside source_store.id; delivery_no copied into each item.	delivery_order, delivery_order_item	Two sources of truth for address and store.
8	Missing indexes: delivery_orders has none on status, expect_delivery_date, source_store_id; devices.user_id; vehicles.plate_number; export_configs and templates on entity; plans.status.	various	Fine at 7 rows, matters at production volume.
9	Enums are not enforced by mongoose for any status or type; only the DTO guards input. Kafka intake bypasses the DTO, so a bad status or type from a message is stored as is.	all
10	kafka_consume_dlqs and job_workers have no TTL; dead-pod rows and old dead letters accumulate.	kafka, jobs
11	settings.updated_at is declared by hand and timestamps.updatedAt is off, so it is only set by save / reset, not by the boot upsert.	settings	Small, explained by the schema comment.
12	user.status is assigned when the initial admin is seeded but the schema has no such field, so mongoose drops it.	user.service	Dead assignment.
13	The local db carries tables from the v1 clone that no v2 schema owns: roles (9), settings, app_settings, script_settings, storage_settings, google_api_logs, kafka_api_logs, job_stats, qa_*, probes. Some stale indexes remain (plans.store_id, delivery_order_items.store_id). The log setting record also still holds keys kafka_api_logs and google_api_logs; the storage record holds allowed_image_extensions, endpoint, force_path_style that code does not declare.	tms_local_v2	Do not read these as part of the model.