Skip to content

System Data Specification — Data Dictionary and ERD

0. Document Control And Model Level

Item Baseline
Document ID / type SPEC-DATA / Data Dictionary and ERD
Model level the conceptual, logical and physical models are distinguished
Original the work meaning owner, the real schema, C1 Legacy, and C2 reference and New-System
Identification the public UUID and the numeric compatibility inside an adapter are separated
Applicability a logical relationship is not evidence of a real FK, index or cardinality
Change control a schema, migration, transfer, preservation and deletion need a separate approval and evidence

Entities, fields and relationships are not mixed in the same table as the work meaning and the real physical implementation. An example SQL or an ERD is not an execution instruction, and the real DB structure is confirmed only when there is a source or schema basis.

1. Data Scope And Interpretation

It defines the entities, field meanings, relationships, writers and source correspondence of the whole system. It is linked to the work result of SRS and the processing responsibility of SDD. The relationships below are the logical relationships that organise the module contract and the system overview. The existence of a physical FK, a cardinality, a real column or index is not confirmed by this diagram.

2. System Logical Relationships And The ERD Scope

flowchart LR
    Master[Company, item, unit and place] --> Order[Trade order]
    Order --> Fulfillment[Inbound and outbound execution order]
    Fulfillment --> Evidence[Work and weighing evidence]
    Evidence --> Inventory[Lot, ledger and location]
    Inventory --> Allocation[Allocation and loading]
    Booking[Booking and transport plan] --> Allocation
    Equipment[Equipment and yard record] --> Allocation
    Allocation --> Projection[Document and billing source query]
    Order --> Projection
    Projection --> Settlement[Billing and settlement material]

An arrow means a work reference and evidence consumption, not a DB foreign key. The whole physical ERD is expanded after the key, NULL, relationship and delete contract of each area is compared. The current Booking data and relationship diagram is a partial detail, not the whole ERD.

2.1 Operating Physical Schema Baseline — 2026-09-21

The Production @@server_uuid=bcfe3d12-e0a7-11e9-9bcb-06cc777fe830 and the DB prod were read again with only SELECT and information_schema. The baseline excluding T_CTC_* and the views is as follows.

Item The operating baseline The basis
base table 112 all_tables.csv
column 2,273 all_tables_columns.csv
the ID and UUID stem candidates 414 relation_columns.csv
ID+UUID dual 174 two columns exist in the same stem
ID-only 138 the investigation denominator including a numeric and tenant compatibility that is not a relationship
UUID-only 102 a UUID-native relationship and an external or config UUID included together

This figure is not immediately the number of FKs or migrations. After classifying the parent and the meaning, 120 relation UUID columns and 5 list relationship tables were applied. A UUID column is not added and a parent is not chosen by name similarity alone.

2.2 Operating Physical Relationship ERD — 2026-09-21

What follows are the core work relationships whose parent was confirmed by source and data in this full audit. Each relationship label shows the applied dual column, and a discriminator relationship is not enforced as a single parent FK.

erDiagram
    T_COMPANY ||--o{ T_BOOKING : "BILLING/SHIPPER/INSPECTOR UUID"
    T_BOOKING ||--o{ T_LOAD : "BOOKING_ID + BOOKING_UUID"
    T_COMMERCIAL_ORDER ||--|{ T_COMMERCIAL_ORDER_ITEM : "COMMERCIAL_ORDER_ID + UUID"
    T_COMMERCIAL_ORDER ||--o{ T_LOAD : "COMMERCIAL_ORDER_ID + UUID"
    T_LOAD ||--|{ T_LOAD_ITEM : "header-line"
    T_TRANSLOAD ||--o{ T_LOADING_PLANNER : "UNLOADING_EQUIPMENT_LOAD_ID + UUID"
    T_BOOKING ||--o{ T_LOADING_PLANNER : "LOADING_BOOKING_ID + UUID"
    T_EQUIPMENT ||--o{ T_EQUIPMENT_LOAD : "EQUIPMENT_ID + UUID"
    T_EQUIPMENT_LOAD ||--o{ T_LOADING_PLANNER : "LOADING_EQUIPMENT_LOAD_ID + UUID"
    T_EQUIPMENT_LOAD ||--o{ T_DELIVERY : "EQUIPMENT_LOAD_ID + UUID"
    T_SCALE_TICKET ||--o{ T_TRANSLOAD : "SCALE_TICKET_ID + UUID"
    T_SCALE_TICKET ||--o{ T_EQUIPMENT_LOAD : "SCALE_TICKET_ID + UUID"
    T_LOAD ||--o{ T_INVENTORY_LOT : "SOURCE_ORDER_ID + UUID"
    T_INVENTORY_LOT ||--o{ T_INVENTORY_LOT_LEDGER : "INVENTORY_LOT_ID + UUID"
    T_INVENTORY_LOT ||--o{ T_LOAD_ALLOCATION : "INVENTORY_LOT_ID + UUID"
    T_LOAD ||--o{ T_LOAD_ALLOCATION : "OUTBOUND_ORDER_ID + UUID"
    T_LOAD_ITEM ||--o{ T_LOAD_ALLOCATION : "OUTBOUND_ORDER_ITEM_ID + UUID"
    T_COMPANY ||--o{ T_EQUIPMENT : "LAST_LOCATION_ID + UUID (terminal/company)"
    T_WAREHOUSE_LOCATION ||--o{ T_WAREHOUSE_GRID : "WAREHOUSE_LOCATION_ID + UUID"

The Mermaid ERD is a view for reading the core relationships and is not a replacement for a full column dictionary of 112 tables. The CSV above owns the whole physical list and the row verification result, and this document owns the meaning and the design verdict of that result.

On 2026-09-21 the multiple relationships that stored comma-separated numeric IDs were normalised into the following UUID-native link tables. The existing list columns are kept for compatibility until the reader and writer switch is finished.

The existing list column The UUID relationship table The operating backfill
T_USER.ROLE_ID T_USER_ROLE(USER_UUID, ROLE_UUID, IS_PRIMARY) 83
T_MENU.MENU_ROLE_LIST T_MENU_ROLE(MENU_UUID, ROLE_UUID) 354
T_COMPANY.M_COMPANY_TYPE_IDS T_COMPANY_TYPE(COMPANY_UUID, TYPE_UUID) 1,139
T_BOOKING.SHIPPER_COMPANY_ID_LIST T_BOOKING_SHIPPER(BOOKING_UUID, COMPANY_UUID) 1,272
T_LOADING_PLANNER.LOADER_NAME T_PLANNER_LOADER(PLANNER_UUID, USER_UUID) 35,329

2.3 Verdict Of A Missing UUID Relationship

uuid_transition_gaps.csv separates the ID-only candidates into the following three classes.

Verdict Count Meaning
CONFIRMED_MISSING_UUID_COLUMN 0 110 single parent relation columns added and the backfill is complete
POLYMORPHIC_UUID_DESIGN_REQUIRED 0 10 discriminator relation columns added and the backfill per current type is complete
RELATION_MEANING_REVIEW_REQUIRED 0 the full classification of an external or provider identifier and a real relation under a _ID name is complete

The 2026-09-21 migration added the same 120 relation UUID columns to Local, Hub and Production, and backfilled the parent UUID only for the existing rows whose real parent is confirmed. The real parent of T_LOADING_PLANNER.UNLOADING_EQUIPMENT_LOAD_ID was confirmed as T_TRANSLOAD from the source and the operating discriminator value. T_USER.ROLE_ID is not a single FK but a comma-separated role list, so an incorrect single ROLE_UUID column is not created.

CLIENT_ID and TENANT_ID are excluded from the general relationship UUID missing count. These two are managed by the canonical tenant transition in the DB Schema. This step completed only the nullable column addition and the valid parent backfill. The next steps are the writer dual-write, the switch to a UUID reader and join, the confirmation of 0 ID consumers, and the removal of the numeric column, and the existing dangling values are not backfilled by guessing a UUID.

3. Per-Area Data Dictionary Contents

Area The entity and data group The core items to extend in the dictionary
Administration, access and settings the user, role, tenant and settings the identification, the valid authority, the settings revision and the audit
Master data the company, counterparty, item, unit and place the code and UUID, the name, the classification and the usage relationship
Trade the order, item and price snapshot the quantity, unit, unit price, currency and source reference
Inbound and outbound the execution order, the item and the SourceReference the distinction between the commercial source and the execution source
Field work and Transload the plan, assignment and execution evidence the plan and actual values, the work link and the correction
Weighing the session, reading, ticket and correction the weight, the unit, the measured time and the original evidence
Inventory and warehouse the lot, ledger, location, cost layer and allocation the quantity change, the remaining quantity, the reversal and the source link
Transport and Booking the reservation, the Shipment and the Transport Leg the schedule, the company role, the planned equipment and the state
Equipment and yard the equipment lifecycle, location and photo relationship the equipment identity, assignment, movement and return
Documents and output the template, request, projection and output the source revision, the overlay and the file relationship
Billing and settlement the service rate, AP and AR, the cost and the margin the calculation source, currency, precision and the finalisation snapshot

Apart from Booking, Transload, Scale and Inventory, this step set the dictionary authoring scope of the data groups above, and the physical field detail is unwritten. The existing catalog/data/ and each DB adapter are the starting point of the investigation, and it is not treated as a dictionary with every field written.

3.1 Source-Confirmed Current Data Groups

Data ID and physical record The current semantic authority The relationship, constraint and open item
platform-tenant-access / T_CLIENT, T_USER, T_ROLE, T_MENU, module and config tables the tenant, user, role, menu and current settings compatibility a numeric Client and role are for compatibility; the profile and action model is incomplete
t-company / T_COMPANY, T_COMPANY_EMAIL a tenant-scoped Party and a typed email the party, address and terminal, warehouse and carrier roles are mixed, with comma-separated type IDs
t-commodity / T_COMMODITY a tenant-scoped Material reference the name snapshot of a trade row is distinguished from the current reference
t-master / T_MASTER a generic coded value the target owner needs to be separated per category; it is not one domain master
t-commercial-order / T_COMMERCIAL_ORDER, ITEM a Purchase and Sales Agreement and its lines DRAFT, OPEN, PARTIAL, FULFILLED and CANCELLED are confirmed; the transition policy is not approved
t-load-execution-order / T_LOAD, T_LOAD_ITEM a Purchase and Sales execution order and a process bridge the meaning of the Agreement, Fulfillment and Booking link is distinguished per field
t-transload / T_TRANSLOAD an appointment, work, runtime and result compatibility the plan, assignment and execution facts are mixed
t-loading-planner / T_LOADING_PLANNER a plan, assignment, queue and runtime projection the two assignment representations coexist, and the canonical is unresolved
t-staff-time-event / T_STAFF_TIME_EVENT a Clock, Break and job time ledger it is independent of a work completion
t-scale-ticket / T_SCALE_TICKET accepted gross and tare ticket evidence the real schema of the event table and the whole lifecycle need a per-environment comparison
t-inventory-lot / T_INVENTORY_LOT a lot identity and the persisted remaining values REMAIN_QUANTITY and REMAIN_NET_WEIGHT are different dimensions
t-warehouse-location / T_WAREHOUSE_LOCATION a location and placement compatibility family the real placement cannot be confirmed from the location string of the source or lot alone
t-load-allocation / T_LOAD_ALLOCATION an accepted relation of a source Lot and a target line the current apply decreases the remaining net weight, and there is no quantity conversion
t-booking / T_BOOKING an Ocean Booking record UUID and TENANT native with a CLIENT alias; an update uses ROW_VERSION; the detail dictionary is linked
t-delivery / T_DELIVERY an internal delivery assignment and result SCHEDULED, DISPATCHED, DELIVERED and TROUBLE; it is independent of the equipment and Yard
t-driver-log / T_DRIVER_LOG a Truck Operator workday and check-in it is separated from the Delivery state
t-equipment and t-equipment-load and the relevant tables an equipment identity, a load and terminal bridge and a mirror the whole canonical state of the equipment lifecycle is undetermined
t-yard-dispatch-queue / T_YARD_DISPATCH_QUEUE planned/assigned/claimed Yard work QUEUED/SCHEDULED/CLAIMED/COMPLETED/CANCELLED
t-yard-location / T_YARD_LOCATION a physical-system Yard occupancy the planned spot is distinguished from the real spot and read-back

The IDs in this table come from the existing catalog/data/*.yaml and the source-reconciled documents. A state value is a confirmed current value and is not automatically approved as a target state. The target InventoryCostLayer, Shipment, TransportLeg and ContainerLifecycle persistence is currently NOT_IMPLEMENTED afterwards.

3.2 Shared Unit, Precision And Time Rules

  • The UOM candidates MT, LT, LB, ST and EACH repeat in the current enum and validation, but a single conversion authority is not confirmed.
  • gross, tare and net, quantity, allocation weight and count are not treated as mutually convertible identical values.
  • An amount records the currency, the unit price basis, the scale and the rounding together with the source snapshot. The HALF_AWAY_FROM_ZERO_2DP of the current Settlement source projection is not expanded into a whole-system rule.
  • The ERD, SI_CUTOFF and CUTOFF of Booking preserve the SQL wall-clock datetime, and the ETD and ETA preserve the DATE precision. It is not applied automatically to the time of another area.
  • A time field distinguishes the work time, the device and provider time, the DB creation and modification time, and the timezone provenance. A field that is not compared is unconfirmed.

4. Common Dictionary Format

For each field it records the logical name, the work meaning, the physical table and column, the data type, length and precision, the unit and time zone, the NULL and default, the PK, UK and FK and the validation condition, the writer, the source and mirror, the change, correction and deletion, and the C1 Legacy and C2 reference correspondence. A value that is not compared is left unconfirmed. A date, a weight and an amount are not merged into the same meaning because the names are similar.

The public UUID and the DB numeric compatibility are distinguished. The tenant membership and the relationship validation are included in the data contract. The semantic owner of an entity and the DB adapter that is the real SQL executor are also distinguished.

5. Transportation Area — Booking Detail And Open Items

The existing fields, relationships and source correspondence are kept in the Booking dictionary. An existing open item such as a company role for the document, a Seal or a weight is stated as a difference in the document, and a DB column is not added and existing data is not changed to write the frame.

6. Field Work Area — Transload Data Dictionary

DATA ID Record and relationship The current meaning and authority The separation and open item
TRN-DATA-01 T_TRANSLOAD a compatible record that holds the appointment and the work runtime and result together; the Transload reader and writer do the authoritative re-read the plan, assignment, Office and inventory result are not judged by a single status
TRN-DATA-02 T_LOADING_PLANNER a work plan, the work type, the operator assignment and the queue and runtime projection the update rule of an active SCHEDULED or STARTED is preserved; the target canonical of the two assignment representations is unresolved
TRN-DATA-03 T_STAFF_TIME_EVENT a Clock, Break and job start and end time ledger it is separate from a work completion timestamp, and a missing or duplicate event is not inferred as a completion
TRN-DATA-04 T_FILE → Transload the photo and document metadata and the work relationship; when Transloading completes, the existence of 5 TRANSLOAD photos is verified the metadata, the object storage and the provider delivery are judged separately
TRN-DATA-05 the Equipment and Load, the Warehouse Placement and the Yard Queue and Location the physical and plan records of the neighbouring owner that a start, completion and Undo reference the queue plan and the real occupancy, and the equipment identity and the load state, are not merged
TRN-DATA-06 the purchase Lot and Inventory Placement the follow-up record where the Unloading result is materialised and placed in the Inventory owner the Transload completion commit and the Lot and Placement success and recovery are read back separately
TRN-DATA-07 T_OPERATION_EVENT and the provider attempt and result a notification and event projection and the external effect result it is not the canonical work completion, and a provider failure does not reverse a physical completion

The final equipment, the bale result, the seal and the quantity of the Transloading result, and the active warehouse location of the Unloading result, are not treated as the same field group. Even on an Undo, only the relationship of the equipment load, the placement, the Yard occupancy and the planner and time that was actually compensated for the current step is judged as a change history. The type, NULL and FK of a physical column are promoted to a detail card only when confirmed in the DB adapter or schema, and are not estimated in this document.

7. Weighing Area — Scale Data Dictionary

DATA ID Record and field group The current meaning and constraint Relationship and open item
SCL-DATA-01 T_SCALE_TICKET.UUID, TICKET_NUMBER, CLIENT_ID, SOURCE_ORDER_UUID a tenant-scoped Ticket identity and a Fulfillment source; a UUID uniqueness and schema guard a numeric ID is inside the adapter, and a public relation is a UUID
SCL-DATA-02 GROSS/TARE STEERING, DRIVE, REAR, FOURTH_AXLE, FIFTH_AXLE, *_TIME the axle readings and the measured time per stage; the current input is a non-negative integer and a positive total the device original unit, precision and timezone provenance are not compared
SCL-DATA-03 the derived gross, tare and net, and readyForReceipt an evidence projection calculated from the axle totals and the existence of the two stages it is not converted automatically into the persisted quantity and allocation weight
SCL-DATA-04 reference/equipment/tractor/company/commodity/quantity/driver/weigher/remark the snapshot needed for the order and field display, and the operator input the current reference and the trade snapshot are distinguished; the role authority is unresolved
SCL-DATA-05 bad bale 1~3, driver/weigher signature a compatible quality and signature field of at most 3 that the editor preserves the signature size, preservation and sensitive data policy is a separate review
SCL-DATA-06 T_SCALE_TICKET_EVENT the event UUID, the type, the stage and UOM, the before and after time and 5 axes, the reason, the actor and the request ID when the schema exists the correction lineage is AVAILABLE, otherwise it is SCHEMA_NOT_APPLIED
SCL-DATA-07 T_LOAD.TICKET_NUMBER, T_TRANSLOAD.SCALE_TICKET_ID, T_EQUIPMENT_LOAD.SCALE_TICKET_ID, Inventory source UUID a compatibility relationship that references and consumes the Ticket a link is not a transfer of the Scale authority and not a downstream completion

The INPUT_UOM, CANONICAL_UOM and DISPLAY_UOM of the current event are recorded as LB by the writer, but they are not approved as a whole-system UOM contract. A Void and Reversal preserve the Ticket soft-delete and the event, and release only the allowed compatibility link. The provenance of a downstream ledger is still preserved in the relevant owner.

8. Inventory And Warehouse Area — Inventory Data Dictionary

DATA ID Record and relationship The current meaning The separation and open item
INV-DATA-01 the T_INVENTORY_LOT identity and source fields the lineage of the Lot UUID, the source order and item, transload and scale, the commodity and owner, and the received date the identity is not regenerated from the source string alone
INV-DATA-02 the received and remaining quantity, the net weight and the status a persisted balance projection and a compatible OPEN, PARTIAL, HOLD and CLOSED state the quantity and weight conversion and the whole transition policy are not approved
INV-DATA-03 the Lot ledger and correction evidence the provenance of a Receipt, adjustment and allocation and reversal, and the related source a mutable balance and an immutable evidence are distinguished
INV-DATA-04 the T_WAREHOUSE_LOCATION and Placement relation a location definition and a current placement compatibility an active placement is not confirmed from the location string of the source or lot alone
INV-DATA-05 T_LOAD_ALLOCATION an accepted relation of a source Lot and a target execution item a browser basket is a projection; a soft delete is not an approved business reversal
INV-DATA-06 T_LOAD_ITEM and T_EQUIPMENT_COMMODITY and Load the target item that the Allocation creates and the direct Container loading projection the Outbound, Container and Delivery lifecycle is a separate owner
INV-DATA-07 the unit-rate snapshot and the target Cost Layer the source and lot rate is a trade snapshot; a Cost Layer with freight, handling and landed cost is a separate candidate an owned-cost layer may be NOT_APPLICABLE for a custody inventory

C1 gives weight to the customer and custody party and the bale count and weight, and C2 gives weight to the tenant-owned stock and the purchase source and cost applicability. materialOwnership, the owner and custody party, the source purchase line, the quantity, the weight, the UOM, the location and the costing method are preserved as a fact of that record and are not recalculated from a Client number.

9. Extending The Relationship, Transfer And Preservation Specification

For each relationship it records the parent and child, the direction, the cardinality, the optionality, whether it is a physical FK, the tenant match, the reference after a deletion or reversal, and the time of the source material. The physical structure of C1 Legacy and C2 reference is mapped separately from the product model. The time of the structure, original and real DB comparison is separated, and whether it is applied to the operating DB is recorded in Tests and acceptance.

9.1 Currently Confirmed Core Relationships

The relationship The currently confirmed meaning What is not confirmed
Commercial Order → Items a transaction-owned relationship of an Agreement header and a line whether the state transitions automatically from the executed quantity
Commercial Order → T_LOAD an explicit bridge from an Agreement to an execution order the identity of the Agreement and the execution
Booking → outbound Sales T_LOAD a Booking-side pointer and an execution-side BOOKING_ID coexist a full DB uniqueness, the final cardinality and the link ledger
Scale Ticket → Receipt/Transload/Equipment a compatibility link that consumes the accepted weight evidence the meaning that storing a Ticket is a consuming work completion
Receipt source → Inventory Lot the order and item and transload UUID and the commodity and owner and date lineage the meaning that the source terminal state is a whole Lot close
Inventory Lot → Placement a separate relation and mirror of the Lot and the location confirming the real occupancy from a warehouse location string alone
Inventory Lot → Allocation → target line an accepted source consumption relation an automatic conversion of the quantity and the weight
Delivery → Equipment Load/Yard a cooperating relationship of the internal transport, the equipment and the physical location one transaction and one state authority
Document → source records a revision-bearing read projection the meaning that a document preview is a source mutation, an issuance or a transmission

10. Entity, Field And Relationship Detail Card

Card The required items
Entity the DATA ID, the work definition, the owner, the key, the lifecycle, the source and derivation, and the related requirement
Table the schema and table, the responsible writer and readers, the PK, UK and FK, the history and audit, and the expected size
Column the logical and physical name, the type, length, precision and unit, the NULL, default and domain, and the sensitivity and preservation
Relationship both entities, the cardinality and optionality, the reference rule, and the meaning of the creation, deletion and correction
Integrity the invariant, the validation point, the result of a violation, and the transaction, concurrency and reconciliation method
Performance the query pattern, the index basis, the cardinality and the measurement environment, and the change impact
Transfer the source→target mapping, the conversion and rejection, the ID and history preservation, and the reconciliation

11. Data Review And Change Impact

Source: new-system/docs/specifications/DATA.md — ctc repository · This page is an English translation of the source document. Identifiers, file paths and code symbols are unchanged. A user instruction quoted inside the text is kept in its original language.