Data Authority And State Reconstruction Map¶

Status: CURRENT RECONSTRUCTION MAP
Current database: MySQL
Current schema authority: .agents/rules/DB_SCHEMA.md plus verified target schema and current source
Target database sequence: MySQL during React + Next.js migration; PostgreSQL only after accepted stabilization under P11
Purpose: identify which records own business truth, which fields are mirrors/derived projections, which identities cross processes and which state/audit semantics must survive a rebuild

1. Reconstruction Principles¶

A table does not own business meaning merely because a page writes it.
The owning process and application command define why a record changes.
Tenant-scoped records use explicit tenant context.
Numeric ID is a local relational key; UUID or another approved reference is required for portable identity.
Soft deletion, correction and reversal are distinct lifecycle operations.
A compatibility mirror is updated with its authority but never becomes an independent source of truth.
Audit and notification records do not automatically mean approval.
Provider IDs, file IDs and physical evidence require separate reconciliation.
Derived reports and statuses must name their source data and freshness.
PostgreSQL migration must preserve semantics, not copy MySQL structure mechanically.

2. Platform Identity And Access¶

erDiagram
    T_CLIENT ||--o{ T_USER : contains
    T_CLIENT ||--o{ T_ROLE : defines
    T_USER }o--o{ T_ROLE : assigned
    T_CLIENT ||--o{ T_MENU : configures
    T_ROLE }o--o{ T_MENU : permits
    T_CLIENT ||--o{ T_MODULE : catalogs
    T_CLIENT ||--o{ T_TENANT_MODULE : enables
    T_MODULE ||--o{ T_TENANT_MODULE : selected
    T_CLIENT ||--o{ T_TENANT_CONFIG : configures
    T_USER ||--o{ T_USER_MENU_FAVORITE : owns
    T_MENU ||--o{ T_USER_MENU_FAVORITE : targets

Authority table¶

Record	Owns	Key reconstruction requirements
`T_CLIENT`	current deployment-local tenant row	local integer identity versus future durable UUID mapping
`T_USER`	login/user identity and role linkage	personal actor identity, impersonation subject, linked provider staff IDs
`T_ROLE`	role definition	role name is not enough; capability/menu/record policy remains separate
`T_MENU`	tenant route/menu permission and navigation	permission-only rows versus rendered menu rows; stable route linkage
`T_MODULE`, `T_TENANT_MODULE`	module catalog and tenant enablement	module enablement is not record/action authorization
`T_TENANT_CONFIG`	tenant/profile/provider/UI/runtime configuration	typed configuration, provenance, version, secret references outside Git
`T_USER_MENU_FAVORITE`	user-specific navigation preference	tenant scope through user/menu identity joins

Target reconstruction¶

TenantExecutionContext
+ Actor / Subject
+ Operating Profile
+ Capability Resolution
+ Menu / Page Authorization
+ Command Record Authorization

These are separate decisions. The target system must not infer command authority from page visibility alone.

3. Master Data¶

erDiagram
    T_CLIENT ||--o{ T_COMPANY : owns
    T_CLIENT ||--o{ T_COMMODITY : owns
    T_CLIENT ||--o{ T_MASTER : owns
    T_COMPANY ||--o{ T_COMPANY_EMAIL : has

Record	Owns	Reconstruction notes
`T_COMPANY`	customer/supplier/carrier/partner identity and display/legal data	stable company identity, type/capability, aliases and provider references must be explicit
`T_COMPANY_EMAIL`	typed contact destinations	email purpose and active/retired lifecycle; do not infer recipients from display name
`T_COMMODITY`	commodity identity and units/defaults	canonical units, allowed conversions and profile terminology
`T_MASTER`	legacy lookup values	target system should replace hidden string categories with typed contracts progressively, without breaking active values

Customer-specific business behavior must not be selected by company display name. Stable policy/configuration references are required.

4. Appointment, Planning, Assignment And Loader Execution¶

erDiagram
    T_TRANSLOAD ||--o{ T_LOADING_PLANNER : planned_by
    T_LOADING_PLANNER ||--o{ T_STAFF_TIME_EVENT : job_evidence
    T_TRANSLOAD ||--o{ T_FILE : evidence
    T_TRANSLOAD }o--|| T_COMPANY : customer
    T_LOADING_PLANNER }o--o{ T_USER : assigned_loader
    T_TRANSLOAD }o--o| T_EQUIPMENT_LOAD : loading_equipment

Primary records¶

Record	Owns	Important fields/semantics
`T_TRANSLOAD`	appointment/execution source record and broad operational state	customer/shipper/reference, appointment, load type, commodity, execution status, final quantities/weights, review fields, related equipment and warehouse mirrors
`T_LOADING_PLANNER`	current planning/assignment/job compatibility state	assignment, queue order, work type, area/dock, job status/start/end, Telegram time; Break currently reuses this table through sentinel values
`T_STAFF_TIME_EVENT`	append-style Clock/Break/Job evidence	event type/time, staff, work context, related job/entity, source and note

Authority split¶

Appointment/execution lifecycle
= T_TRANSLOAD + owning TMS commands

Plan/assignment/job attempt
= T_LOADING_PLANNER + planning/Loader commands

Workforce Clock/Break/Job evidence
= T_STAFF_TIME_EVENT

Provider synchronization
= separate Telegram / QuickBooks Time effect result

Compatibility fields that require target redesign¶

comma-separated Loader IDs in LOADER_NAME;
Break queue rows represented as T_LOADING_PLANNER with SPECIAL_INSTRUCTION='QUEUE_BREAK' and sentinel relations;
LAST_SENT_TIME serving both notification evidence and readiness behavior;
transload/warehouse text fields that mirror WMS/YMS state;
page-derived statuses such as Current/Next/Ready that combine planner and transload data.

Target entities¶

InboundWork
Plan
Assignment
JobAttempt
StaffShift
BreakRequest / BreakSession
NotificationIntent / NotificationAttempt

These may map to existing tables during migration but must be distinct in application/domain contracts.

5. Final Loading Review And Inventory Adjustment¶

erDiagram
    T_TRANSLOAD ||--o{ T_INVENTORY_LOT : source_of
    T_INVENTORY_LOT ||--o{ T_INVENTORY_LOT_LEDGER : movements
    T_INVENTORY_LOT ||--o| T_WAREHOUSE_LOCATION : placed_at
    T_TRANSLOAD ||--o{ T_PROCESS_TRANSITION_AUDIT : reviewed_by

Review authority¶

Current persisted review fields on T_TRANSLOAD:

review status;
confirmed actor;
confirmed time;
weight/review reason;
final quantity/weight inputs used by readiness.

The rebuild must separate:

FinalLoadingReview
- eligibility
- readiness derivation
- confirmation/reopen state
- reviewer and reason
- immutable transition history

InventoryAdjustment
- Source lot consumption
- Leftover lot creation
- count correction
- lot/ledger/placement mutation
- Void/Reversal lineage

Non-equivalence¶

Reopen Review
!= Void Inventory Adjustment
!= Reverse Ledger Movement
!= Undo Loader Completion

Inventory records¶

Record	Owns	Reconstruction requirements
`T_INVENTORY_LOT`	lot identity, source, quantity/weight balance and lifecycle	source process/record, original and current balance, lot type, UOM, status, placement linkage
`T_INVENTORY_LOT_LEDGER`	movement/adjustment/reversal history	signed amount, movement type, related process/record, actor/reason, reversal reference and idempotency
`T_WAREHOUSE_LOCATION`	warehouse location master, placement and marker state	distinguish location, placement and manual marker rows; atomic location identities
`T_WAREHOUSE_GRID`	visible spatial cell model	cell type, storage eligibility and location-group relationship

A rebuild must never derive current balance only from a mutable lot total without preserving ledger/reversal provenance.

6. Yard, Equipment And Delivery¶

erDiagram
    T_EQUIPMENT ||--o{ T_EQUIPMENT_LOAD : loaded_as
    T_YARD_LOCATION }o--o| T_EQUIPMENT : contains
    T_YARD_DISPATCH_QUEUE }o--|| T_EQUIPMENT : moves
    T_DELIVERY }o--|| T_EQUIPMENT_LOAD : transports
    T_DELIVERY ||--o{ T_FILE : evidence
    T_DELIVERY ||--o{ T_OPERATION_EVENT : signals
    T_DRIVER_LOG }o--|| T_USER : workday_of

Yard authority¶

Record	Owns	Mirrors/derived
`T_YARD_LOCATION`	current physical occupancy and location state	equipment/transload Yard display fields
`T_YARD_DISPATCH_QUEUE`	planned/claimed/completed Yard instruction	equipment Yard queue summary fields
`T_EQUIPMENT`	container/chassis master and current compatibility state	not an independent Yard movement ledger
`T_EQUIPMENT_LOAD`	load lifecycle and linkage to delivery/booking/order	current equipment/load status projection

Yard queue identities¶

A Yard instruction must preserve:

queue ID/UUID;
entity type and entity ID;
source/planned/actual destination;
dispatch type;
state;
claim actor/time;
complete actor/time;
originating process or delivery reference where known;
dependency/queue-after-clear reference;
idempotency/correlation.

Delivery authority¶

Record	Owns	Reconstruction requirements
`T_DELIVERY`	assignment, delivery status, pickup/delivery times, parking and arrival fields	assigned actor enforcement, explicit transition policy, partial Yard/provider checkpoints
`T_DRIVER_LOG`	Truck Operator workday Check In/Out and inspection/mileage context	personal driver identity and one active log rules
`T_FILE`	uploaded evidence object/link	distinguish upload success from delivery completion
`T_OPERATION_EVENT`	arrival/location/attention event	ACK is notification acknowledgement, not delivery completion

Partial-result data states to preserve¶

Closing scale saved + Yard queue still claimed
Delivery delivered + queue-after-clear still active
Parking assigned + physical placement not confirmed
Photo uploaded + Finish failed
Yard vacated + delivery Start failed
Provider notification sent/failed independently

The target system should model these checkpoints explicitly rather than forcing them into one delivery or queue status.

7. Commercial Orders, Booking And Allocation¶

erDiagram
    T_COMMERCIAL_ORDER ||--o{ T_COMMERCIAL_ORDER_ITEM : contains
    T_COMMERCIAL_ORDER ||--o{ T_LOAD : materializes
    T_LOAD ||--o{ T_LOAD_ITEM : contains
    T_LOAD_ITEM ||--o{ T_LOAD_ALLOCATION : demand_or_source
    T_INVENTORY_LOT ||--o{ T_LOAD_ALLOCATION : allocated_from
    T_BOOKING }o--|| T_LOAD : supports
    T_EQUIPMENT_LOAD }o--o| T_BOOKING : booked_for

Order identities¶

Record	Owns
`T_COMMERCIAL_ORDER` / item	commercial Purchase/Sales agreement and lines
`T_LOAD` / item	operational Purchase/Sales order representation and executable line state
`T_BOOKING`	carrier/shipping booking and related transport context
`T_LOAD_ALLOCATION`	inventory source to outbound demand allocation

Target separation¶

Commercial agreement
-> operational inbound/outbound order
-> booking/equipment context
-> inventory availability/allocation
-> conditional Scale
-> cost/rate/document/report projections

Allocation requirements¶

A rebuild must document:

source lot eligibility;
demand line identity;
quantity and weight UOM;
conditional balance predicate;
concurrent overdraw behavior;
basket atomicity;
correction/unallocate policy;
ledger and audit relationship;
ambiguous timeout readback.

Do not preserve customer-specific Booking contracts selected by numeric CLIENT_ID.

8. Scale And Weight¶

erDiagram
    T_SCALE_TICKET ||--o{ T_SCALE_TICKET_EVENT : corrected_by
    T_SCALE_TICKET }o--o| T_LOAD : finalizes
    T_SCALE_TICKET }o--o| T_DELIVERY : closes
    T_SCALE_TICKET }o--o| T_EQUIPMENT_LOAD : weighs

Record	Owns	Reconstruction requirements
`T_SCALE_TICKET`	accepted scale session/ticket values and linkage	stage, device/operator, gross/tare/net, canonical/original unit, owner-process linkage
`T_SCALE_TICKET_EVENT`	append-only correction/void lineage	original/new value, reason, actor, timestamp and request identity

Target weight contract must preserve:

original reading and input unit;
canonical storage value/unit;
display/pricing units;
conversion version;
precision and rounding;
operator/device/source/time;
correction/reweigh/void lineage;
owner-process finalization as a separate command.

Scale-ticket completion does not automatically complete Purchase, Sales, Yard or Delivery.

9. Billing, Rates, Files And Providers¶

erDiagram
    T_TRANSACTION ||--o{ T_TRANSACTION_LIST : lines
    T_TRANSACTION ||--o{ T_FILE : documents
    T_BILLING_PRESET ||--o{ T_TRANSACTION : proposes
    T_COMMODITY_RATE }o--o{ T_TRANSACTION_LIST : prices
    T_TRUCKING_RATE }o--o{ T_TRANSACTION_LIST : prices

Local financial authority¶

Record	Owns
`T_TRANSACTION`	AP/AR transaction header, source and local lifecycle
`T_TRANSACTION_LIST`	transaction lines and amounts
`T_BILLING_PRESET`	source-backed candidate/default rules; not final invoice authority
rate tables	current commodity/trucking/freight pricing inputs
`T_FILE`	linked source/output document metadata and storage reference

Provider/effect records required in target design¶

Even if current persistence does not have dedicated tables for every provider effect, the target contract must represent:

EffectIntent
- local business/source ID
- provider and operation
- payload hash/reference
- idempotency key
- requested by/at

EffectAttempt
- attempt number
- started/completed time
- request/reference
- response classification
- provider ID
- retryability

EffectReconciliation
- local state
- provider state
- matched/unmatched/unknown
- manual decision and actor

QBO invoice, Gmail email, Telegram message, QuickBooks Time and AI extraction must not share one generic success flag.

10. Files And Evidence¶

T_FILE must be reconstructed as a shared storage/link boundary, not as an undifferentiated attachment list.

Required concepts:

storage object identity;
tenant;
file type/category;
source process/entity;
relation/link identity;
content type/size/hash where available;
sequence and display name;
creator/time;
retention/legal hold;
deleted/unlinked/object-missing distinctions;
provider/source provenance;
human review/approval where applicable.

File uploaded
!= file linked to intended business record
!= file readable
!= business command complete
!= provider delivery complete

11. Mail And AI Intake¶

erDiagram
    T_MAIL_MESSAGE ||--o{ T_MAIL_REQUEST_ITEM : contains
    T_MAIL_REQUEST_ITEM }o--o| T_TRANSLOAD : creates_or_updates
    T_FILE }o--o| T_MAIL_MESSAGE : attachment

Required reconstruction:

mailbox/watch identity;
Gmail message/thread identity;
normalized request-item identity;
attachment/file relationship;
deterministic extraction versus AI extraction;
model/prompt/version/result provenance;
operator edits and review outcome;
accepted/rejected/rescheduled/cancelled/manual-contact state;
idempotent creation/update of scheduled work;
provider reply/draft identity and no-blind-retry recovery.

AI output is proposal/evidence until a human-approved application command commits business state.

12. Audit, Operation Events And Transition Evidence¶

Record	Meaning	Not equivalent to
KJDatabase mutation log	technical DML before/after snapshot	process approval or user-facing history
`T_OPERATION_EVENT`	persisted operational notification/attention item	business completion; ACK means read
`T_PROCESS_TRANSITION_AUDIT`	bounded transition envelope	arbitrary payload store or automatic process owner
transition entity/evidence children	referenced entities/evidence	duplicated source records
process-specific ledger/event	business correction/reversal lineage	generic technical log

Target audit must allow a future operator/support engineer to answer:

who initiated the action;
acting tenant/profile;
subject/record;
previous and resulting state;
command/request/correlation identity;
evidence and reason;
related effect intents/results;
correction/reversal relationship;
which user-visible result was expected.

13. Cross-Process Stable Identity Map¶

From	Stable identity carried	To	Required reread
mail request item	mail message/item + transload ID	Planning	scheduled transload and original request outcome
Planning	transload ID + planner ID + assigned user IDs	Loader	planner/transload current state
Loader completion	transload ID + final equipment/load + evidence IDs	Final Review / Inventory / Yard	completion state plus downstream queue/readiness
Final Review	transload ID + review transition ID	Inventory Adjustment or close	review state and active ledger evidence
Inventory Adjustment	adjustment/ledger/lot IDs + source transload	Final Review / Warehouse	lot balance, ledger and recalculated readiness
Yard	queue ID + entity/equipment + actual location	Driver or close	queue and Yard location
Driver	delivery ID + equipment/load + file IDs	Billing/Yard	delivery, photo, parking and physical queue/location
Local billing	transaction ID/UUID + source IDs	QBO/file/email	local transaction and each independent effect
Commercial order	commercial/order IDs	Inbound/Outbound	accepted operational child records
Inbound	load/item + receipt/transload/lot IDs	Inventory/Scale	lot and scale/finalization states
Outbound	load/item + booking/equipment IDs	Allocation/Scale/Documents	demand, booking and allocated balance
Scale	ticket ID/UUID + owner process/record	Purchase/Sales/Yard/Delivery	accepted weight and owner finalization

Do not rely only on display reference, timestamp or actor name for cross-process correlation.

14. Mirror And Derived Field Register¶

The exact list must be completed per capability. Known important categories include:

Authority	Mirror/derived example	Rule
`T_YARD_LOCATION`	equipment/transload Yard location fields	update/read as compatibility projection; location remains authority
`T_YARD_DISPATCH_QUEUE`	equipment queue summary	queue owns plan/claim/completion
`T_WAREHOUSE_LOCATION` placement	lot/transload warehouse text fields	placement and mirror stay synchronized in owning transaction
`T_INVENTORY_LOT_LEDGER`	lot current balance	ledger/reversal provenance must remain reconstructable
`T_STAFF_TIME_EVENT`	Timeline/current totals	totals are derived; events are evidence authority
process records	dashboard/card status	UI status must document derivation and freshness
source orders/transactions	Trade Summary	Trade is derived/read-only
source records	printed/browser document	output does not become source completion

Every migration packet must classify each field as:

AUTHORITATIVE
DERIVED
CACHE / MIRROR
COMPATIBILITY ONLY
EXTERNAL REFERENCE
DISPLAY ONLY

15. MySQL To PostgreSQL Preservation Map¶

PostgreSQL work begins after React + Next.js + MySQL stabilization. Required semantic review includes:

integer auto-increment versus identity/sequence;
BINARY(16) UUID conversion and byte ordering;
collations and case-insensitive comparisons;
empty string versus NULL behavior;
MySQL boolean/int conventions;
comma-separated legacy fields requiring normalization;
FIND_IN_SET, GROUP_CONCAT, date/time functions and MySQL-specific SQL;
JSON fields and indexes;
soft-delete uniqueness;
tenant-scoped composite keys;
conditional/CAS updates and affected-row behavior;
decimal precision, weight and money rounding;
timezone storage and presentation;
foreign keys currently enforced only by application logic;
provider/file IDs and large text/blob storage;
audit/ledger immutability;
migration batch idempotency and reconciliation totals;
failback and dual-read limitations.

The target schema may normalize compatibility fields, but only after the application contracts and migration mappings are explicit.

16. Data Reconstruction Completion Checklist¶

For every business record:

[ ] owner process/module;
[ ] tenant scope;
[ ] local and durable identity;
[ ] natural key and uniqueness;
[ ] lifecycle/soft delete;
[ ] state and transitions;
[ ] authoritative versus derived fields;
[ ] relationships and handoff IDs;
[ ] units/precision/rounding;
[ ] validation and conditional update;
[ ] transaction boundary;
[ ] audit/evidence;
[ ] correction/reversal;
[ ] retention/export/purge;
[ ] current adapters/callers;
[ ] target repository mapping;
[ ] migration transform and reconciliation;
[ ] rollback/failback.

17. Current Data Documentation Gaps¶

Priority gaps:

full live schema inventory reconciled with current migrations;
exact FK/application-integrity matrix;
all compatibility mirrors and their update owners;
normalized target model for Loader assignment, Break and job attempts;
durable provider-effect/outbox data model;
role/capability configuration model replacing numeric/customer branches;
Final Review immutable history and Reopen metadata policy;
Yard queue dependency/queue-after-clear identity;
Driver parking and partial-result checkpoint model;
allocation correction/unallocate model;
scale UOM/conversion/correction model;
file retention/link/object deletion model;
MySQL-to-PostgreSQL mapping and failback contract.

These gaps must be resolved through the owning process/module tasks rather than guessed during implementation.

---¶

Source: docs/reconstruction/DATA_AUTHORITY_AND_STATE_RECONSTRUCTION_MAP.md — ctc repository · This page is an English translation of the source document. Identifiers, file paths and code symbols are unchanged.