Skip to content

DB Schema

  • Status: CANONICAL CURRENT
  • Owns: current database schema and operational relationship reference
  • Does not own: DB mutation approval, query safety policy, or target schema design
  • Update when: verified schema, relationship, or authoritative-data evidence changes
  • Related sources: DB_QUERY_RULES.md, ARCHITECTURE.md, AUDIT_AND_TRANSITION_EVIDENCE.md, docs/current-state/CTC_AS_IS_OPERATING_MODEL.md

Purpose

This file summarizes the current MySQL schema and operational relationships. For an accessible runtime DB, verified information_schema is authoritative. .agents/reference/schema.sql is an incomplete historical snapshot (45 table definitions versus 66 live local base tables on 2026-07-14) and must not be treated as a complete active dump. The legacy MK source dump used by migration scripts is reference/mkmetal/mk.sql.

Engine And Access

  • Engine: MySQL.
  • Access layer: app/utils/KJDatabase.php.
  • Mutation diagnostics: KJDatabase records bounded request/mutation metadata such as statement/table family, insert ID, affected rows, touched tables, transaction outcome, request context, timing, and errors. It is not the general immutable before/after business-row snapshot authority.
  • Tenant scope: CLIENT_ID is mandatory for tenant-owned data in the current runtime schema.

Production Physical Inventory — 2026-09-21

Post-migration information_schema inspection of Production prod (@@server_uuid=bcfe3d12-e0a7-11e9-9bcb-06cc777fe830) found 112 base tables and 2,273 columns after excluding T_CTC_* and views. The complete ordered inventory is in all_tables.csv and all_tables_columns.csv.

The ID/UUID stem inventory contains 414 candidates: 174 dual, 138 ID-only, and 102 UUID-only. The 2026-09-21 migrations added and backfilled 110 fixed-parent plus 10 discriminator-controlled relation UUID columns and normalized five comma-separated relationship lists into T_USER_ROLE, T_MENU_ROLE, T_COMPANY_TYPE, T_BOOKING_SHIPPER, and T_PLANNER_LOADER in Local, Hub, and Production. The post-migration gap report is zero for confirmed missing, polymorphic-design, and meaning-review classes. relation_columns.csv owns the per-column evidence and full-row counts.

This completes additive relation-column coverage and point-in-time backfill only. Writer dual-write, UUID reader/join conversion, constraints, numeric-consumer zero checks, and numeric-column retirement remain separate NS1-009 stages.

Core Rules

  • Tenant-scoped tables include CLIENT_ID.
  • Tenant natural keys are unique only together with CLIENT_ID.
  • Canonical tenant identity target: Control Tower GLOBAL_TENANT_UUID = T_CLIENT.UUID = tenant-owned T_*.TENANT_ID. TENANT_ID is UUID-valued; there is no separate target T_CLIENT.CLIENT_UUID.
  • Runtime integer T_CLIENT.ID and row CLIENT_ID remain deployment-local compatibility data until the reactivated NS1-009 mapping, Legacy/New-System query/write/job/import callers, constraints, rollback and caller/data-zero checks are complete (2026-09-14 direct user direction; former P4 delegation is historical). Current CLIENT_ID predicates remain mandatory until each exact caller is migrated.
  • During staged coexistence, existing shared tables retain numeric aliases until their exact NS1-009 wave is verified. All application-owned base tables, including non-T_* and join tables, are included in row-UUID coverage; an existing UUID-native table does not gain a numeric ID. Lifecycle omissions and actual DDL/backfill require their own verified migration scope; the first UUID-column stage does not incidentally change lifecycle or enum columns.
  • Canonical ordinary-entity identity uses UUID BINARY(16) NOT NULL. The final target makes that UUID value the primary logical/physical identity and makes every referencing *_ID UUID-valued; numeric auto-increment ID and numeric foreign keys are compatibility only until NS1-009 completes verified table-family retirement for both Legacy and New-System. Former root P12 delegation is historical, not an active implementation owner.
  • The explicit UUID/*_UUID names distinguish values during coexistence. Any later collapse to UUID-valued ID/*_ID names requires one bounded schema and caller migration with uniqueness, orphan, readback and rollback evidence.
  • Active-row reads must filter DELETED_AT IS NULL.
  • Request-time tenant is getClientId().
  • Cron/script tenant defaults may use Config::CLIENT_ID.
  • Write flows keep explicit transaction boundaries.
  • FOR UPDATE is permanently forbidden in runtime SQL; use tenant-scoped conditional UPDATE/CAS predicates and the caller's transaction or savepoint boundary instead.
  • Do not add subqueries. See DB_QUERY_RULES.md.

Transition Audit Store

Task #1190 added a tenant-scoped, append-only transition-audit family to a verified local MySQL 8.4 schema. Current source also contains a selected Loader Transloading writer. For any named environment, information_schema, writer reachability, row population, and retention evidence remain authoritative. The store is independent from KJDatabase JSONL and T_OPERATION_EVENT; neither existing surface changes ownership or semantics.

  • T_PROCESS_TRANSITION_AUDIT stores one bounded, reference-only transition envelope. It uses (CLIENT_ID, UUID) for idempotency and (CLIENT_ID, ID) for child integrity.
  • T_PROCESS_TRANSITION_ENTITY stores ordered typed entity references and is restricted to its tenant-matched parent audit row.
  • T_PROCESS_TRANSITION_EVIDENCE stores ordered typed evidence references and is restricted to its tenant-matched parent audit row. OPERATION_EVENT evidence remains notification-only; ACK is never approval.
  • The store has no generic JSON/text/blob payload column. Current source contains a selected Loader Transloading writer through CtcLoaderTransitionEvidence, but universal Process coverage is absent and current deployment activation/rows require separate evidence. No enabled cleanup worker is established. Retention/legal-hold fields are immutable at this stage.
  • Timeline, entity/evidence lookup, and cleanup-candidate access patterns are tenant-first and use IDX_TRANSITION_AUDIT_PROCESS, IDX_TRANSITION_ENTITY_LOOKUP, IDX_TRANSITION_EVIDENCE_LOOKUP, and IDX_TRANSITION_AUDIT_PURGE respectively.

Authoritative Data

Authoritative field/table Mirrors or derived fields Rule
T_YARD_LOCATION.OCCUPIED_TYPE/OCCUPIED_ID T_TRANSLOAD.WAREHOUSE_LOCATION, T_EQUIPMENT.YARD_LOCATION_CODE, and display-only location fields Update authoritative row first, then mirror in the same transaction.
T_YARD_DISPATCH_QUEUE T_EQUIPMENT.YARD_QUEUE_* and queue display state Planning and actual destinations are queue-owned; dashboard mirrors refresh in the same transaction as normal app yard writes. Export dashboard reads the equipment mirror only when tenant config EXPORT_YARD_EQUIPMENT_STATE_READY=1.
T_LOAD, T_LOAD_ITEM order UI summaries Order documents are source of truth for OMS.
T_INVENTORY_LOT inventory table summaries Lot is source of truth for WMS inventory.
T_SCALE_TICKET order ticket fields Scale ticket is source of truth for current weight capture.
T_SCALE_TICKET_EVENT no mutable projection Append-only correction/void lineage is the source of truth for Scale weight changes and their reason/actor/request provenance.

Warehouse Map Location State

Task #772 stages T_WAREHOUSE_LOCATION as the Warehouse Map location-state source of truth. Target DBs must apply and verify scripts/migrations/2026-06-11_task-772_warehouse_location_forward.sql and scripts/migrations/2026-06-11_task-772_warehouse_location_seed.php before Warehouse Map placement commands are enabled for that tenant.

Planned behavior:

  • ROW_TYPE='LOCATION' stores selectable warehouse location master rows, coordinates, capacity modes, overflow metadata, and block/firelane state.
  • ROW_TYPE='PLACEMENT' stores active inventory lot or transload placement state. Active placement uniqueness is by CLIENT_ID + ENTITY_TYPE + ENTITY_ID.
  • ROW_TYPE='MARKER' stores manual overlay markers such as blocked areas, damaged bales, temporary storage, or unknown inventory.
  • Manual marker rows use ENTITY_TYPE='MANUAL_MARKER' and do not create fake inventory lots or transload records. Resolving a marker sets STATUS='RESOLVED' and soft-deletes the row so it remains auditable but drops from active map state.
  • BLOCKED_AREA markers are incident overlays in this phase; they do not automatically toggle LOCATION.MANUAL_BLOCK. Location-level blocking remains owned by the LOCATION row fields until a separate block command is approved.
  • Warehouse Map placement rows are active for controlled inventory moves and mapped unload-to-inventory handoff. Existing T_INVENTORY_LOT.WAREHOUSE_LOCATION and T_TRANSLOAD.WAREHOUSE_LOCATION remain mirror/cache columns and must be kept in the same transaction as placement changes.
  • During the Warehouse Map transition, completed-unload handoff may recover a missing active TRANSLOAD placement from T_TRANSLOAD.WAREHOUSE_LOCATION when that mirror points to an active Warehouse Map location. This keeps historical or bypassed T_TRANSLOAD source-of-truth rows from permanently blocking INVENTORY_LOT placement creation. Blank or unmapped T_TRANSLOAD.WAREHOUSE_LOCATION values still skip placement handoff.
  • Inbound purchase lot sync must not leave a completed mapped unload with only an active TRANSLOAD placement. When it can identify one unambiguous UNLOADED source transload, it must fill lot source metadata, copy the mapped warehouse location to the lot mirror when no lot mirror exists yet, create/update the active INVENTORY_LOT placement, and close the source TRANSLOAD placement.
  • Warehouse Map active transload placement display and table comparison include non-terminal T_TRANSLOAD rows with an active warehouse location mirror or placement. Completed-unload handoff still uses unambiguous UNLOADED source transloads to create inventory lots and close the source TRANSLOAD placement. TRANSLOADED, CANCELLED, NOSHOW, and NO SHOW are not warehouse-resident source loads. For CTC, an Office UNLOADED → SPARE transition preserves the already handed-off INVENTORY_LOT placement and changes that lot to LOT_SOURCE_TYPE='SPARE'; the SPARE source transload remains warehouse-resident only through that exact active lot.
  • Active INVENTORY_LOT placement validity is driven by the lot row lifecycle, remaining balance, and exact source transload contract. An unambiguous Purchase lot is resident while its source is UNLOADED; a CTC Spare lot is resident while the same source is SPARE and the lot source type is SPARE. Other terminal source states close or exclude the lot.
  • Warehouse Map inventory reads include exact non-deleted source transloads in UNLOADED or SPARE; a SPARE row is usable only through the preserved active lot and is never auto-recreated from historical transload status.
  • Any workflow that clears T_TRANSLOAD.WAREHOUSE_LOCATION directly must close the active TRANSLOAD placement in the same workflow; direct updates must not leave T_WAREHOUSE_LOCATION active after the mirror is cleared.
  • When terminal transload source handling closes linked WMS inventory lots, any active INVENTORY_LOT placement rows for those closed lots must be closed in the same workflow. Closed or zero-balance lots must not remain active Warehouse Map placements.
  • Phase 1 seed applies LOCATION master rows only and reports legacy placement values for review. It does not write PLACEMENT rows.
  • Future reload source controls must use active inventory placement rows and must not rely on legacy text-only warehouse locations.
  • Composite location strings such as 69+69A are not location master rows. Use atomic LOCATION rows plus placement footprint fields (FOOTPRINT_CODE, OCCUPIED_LOCATION_CODES_JSON).
  • The staged table follows the base-table lifecycle rule: ID, nullable non-unique UUID BINARY(16), CREATED_AT, UPDATED_AT, and DELETED_AT.

Warehouse Map Grid State

T_WAREHOUSE_GRID owns the visible, tenant-scoped Excel-coordinate cells of the canonical Warehouse Map. Every active cell points to a T_WAREHOUSE_LOCATION group through WAREHOUSE_LOCATION_ID, but the cell's spatial meaning is owned by CELL_TYPE:

  • STORAGE, AISLE, FIRELANE, FACILITY, and RESTRICTED are the allowed cell types.
  • Only STORAGE cells may render a T_INVENTORY_LOT.WAREHOUSE_LOCATION occupancy overlay. Non-storage cells must keep IS_STORAGE_ALLOWED=0.
  • Task #1348 bridges the existing T_WAREHOUSE_LOCATION.IS_FIRELANE / LOCATION_KIND='FIRELANE' values into Grid only during the additive migration. V1 compatibility values remain on the location master and are not the V2 spatial read source after migration.

Module Table Ownership

Area Tables
Platform T_USER, T_ROLE, T_MENU, T_USER_MENU_FAVORITE, T_MODULE, T_MODULE_PAGE, T_TENANT_MODULE, T_TENANT_CONFIG, T_OPERATION_EVENT, T_PROCESS_TRANSITION_AUDIT, T_PROCESS_TRANSITION_ENTITY, T_PROCESS_TRANSITION_EVIDENCE, T_CONTROL_TOWER_DEPLOYMENT, T_CONTROL_TOWER_TENANT, T_CONTROL_TOWER_TENANT_DEPLOYMENT
Master data T_COMPANY, T_COMMODITY, T_MASTER
YMS yard T_YARD_LOCATION, T_YARD_LOCATION_HISTORY, T_YARD_DISPATCH_QUEUE
YMS equipment T_EQUIPMENT, T_EQUIPMENT_LOAD, T_EQUIPMENT_COMMODITY, T_EQUIPMENT_INFORMATION, T_EQUIPMENT_REPAIR_LOG, T_EQUIPMENT_ACTIVITY
TMS transload T_TRANSLOAD, T_LOADING_PLANNER, T_TRANSLOAD_APPOINTMENT_BLOCK, T_STAFF_TIME_EVENT
TMS delivery T_DELIVERY, T_BOOKING, T_DRIVER_LOG
OMS order T_COMMERCIAL_ORDER, T_COMMERCIAL_ORDER_ITEM, T_LOAD, T_LOAD_ITEM, T_LOAD_ALLOCATION
WMS inventory T_INVENTORY_LOT, T_INVENTORY_LOT_LEDGER, T_INVENTORY_LOT_SNAPSHOT
BMS T_TRANSACTION, T_TRANSACTION_LIST, T_BILLING_PRESET, T_SCALE_TICKET, T_SCALE_TICKET_EVENT, T_COMMODITY_RATE, T_TRUCKING_RATE
Files T_FILE
Mail/Gmail intake T_MAILBOX_WATCH_STATE, T_MAIL_MESSAGE, T_MAIL_REQUEST_ITEM

Legacy MK Source Dump

reference/mkmetal/mk.sql is a legacy MK source-system dump, not the active CTC runtime schema. Current dump metadata:

  • Generated: Jun 02, 2026 at 11:56 AM.
  • Source DB: prod on db.cbvcxdxkfmax.us-west-2.rds.amazonaws.com:3306.
  • Server version: MySQL 8.0.42.
  • Contents: schema plus INSERT data for migration/reference use.
  • Scope: legacy tables mostly are not tenant-scoped; migration scripts injected the target tenant (TARGET_CLIENT_ID = 2) and used generated mapping files in scripts/mk_mappings/ (both retired to scripts/_archive/ 2026-09-20).
  • Runtime fallback: retired scripts/migrate_mk_*.php (now scripts/_archive/migrate-mk/) read mk_source when present, otherwise parsed reference/mkmetal/mk.sql.
Legacy table Rows in latest dump Migration / reference role
T_BOOKING 3,048 Legacy booking confirmations; migrated to tenant-scoped T_BOOKING by retired scripts/migrate_mk_booking.php (now scripts/_archive/migrate-mk/).
T_CLIENT 2 Legacy account metadata; reference only unless a migration explicitly maps it.
T_COMMODITY 88 Commodity master; migrated to tenant-scoped T_COMMODITY.
T_COMMODITY_RATE 8 Commodity rate source; migrated to tenant-scoped T_COMMODITY_RATE.
T_COMPANY 175 Company master and contact source; migrated to T_COMPANY, with contact sync handled by scripts/sync_mk_company_contacts.php when used.
T_MASTER 45 Legacy lookup/master rows; reference for migration behavior.
T_PUBLIC_MESSAGE 6 Historical public-message rows; reference only.
T_PURCHASE 4,776 Inbound purchase headers; migrated to T_LOAD with ORDER_CLASS='PURCHASE'.
T_PURCHASE_COMMODITY 13,554 Inbound purchase lines; migrated to T_LOAD_ITEM and T_INVENTORY_LOT.
T_SALES 4,906 Outbound sales headers and equipment context; migrated to T_LOAD with ORDER_CLASS='SALES' and used by outbound equipment migration.
T_SALES_COMMODITY 15,604 Outbound sales lines and source allocation pointers; migrated to T_LOAD_ITEM and T_LOAD_ALLOCATION.
T_SETTING 1 Legacy company/weight-unit settings; reference for tenant config decisions.
T_SYNC 0 Legacy sync watermark table; reference only.
T_TRUCKING_RATE 3 Legacy trucking rate source; migrated to tenant-scoped T_TRUCKING_RATE.
T_USER 4 Legacy users; not the active auth source for CTC.

Key Tables

Platform

  • T_USER: login identity and role linkage. QUICKBOOKS_TIME_USER_ID stores the linked QuickBooks Time user id for staff-time sync. TMS driver identity is stored on T_USER; delivery and driver-log runtime code references driver users through USER_ID.
  • T_ROLE: role name and default menu.
  • T_MENU: tenant menu tree and route permission source. IS_PERMISSION_ONLY=1 rows grant route roles without rendering a sidebar/menu item; these rows are excluded from menu tree/favorite reads but remain in route access checks.
  • T_USER_MENU_FAVORITE: per-user sidebar favorite menu mapping. It uses integer ID plus UUID BINARY(16) NOT NULL, lifecycle columns, USER_UUID, MENU_UUID, and SEQUENCE; it intentionally does not carry CLIENT_ID. Tenant scope is enforced by joining USER_UUID to T_USER.UUID and MENU_UUID to T_MENU.UUID with the current request CLIENT_ID. Deletes are soft deletes through DELETED_AT; re-adding the same favorite revives the existing USER_UUID / MENU_UUID row.
  • T_MODULE: module catalog.
  • T_MODULE_PAGE: page registry, page URL, menuability, module mapping.
  • T_TENANT_MODULE: tenant module enablement.
  • T_TENANT_CONFIG: tenant runtime config (TIMEZONE, prefixes, brand fields, integration settings, COMMERCIAL_ORDER_COMPANY_TEMPLATES JSON). Tenant config structure and field inventory live in TENANT_CONFIG.md; Control Tower is the editable source for published tenant config; module and integration enablement belongs to T_TENANT_MODULE.
  • T_OPERATION_EVENT: tenant-scoped persisted operation notification ledger. Required lifecycle columns are ID, UUID BINARY(16), CREATED_AT, UPDATED_AT, and DELETED_AT; event UI details live in PAYLOAD_JSON. ACKED_AT / ACKED_BY mean the message was read, not that the underlying business process was confirmed.
  • T_CONTROL_TOWER_DEPLOYMENT: Control Tower-owned deployment/server registry in the dedicated CONTROL_TOWER_DB_* database. It stores deployment id, environment, hosting mode, state, runtime URLs, management reference, DB reference, secret-scope reference, code reference, apply flag, and notes. References are metadata only; do not store credentials.
  • T_CONTROL_TOWER_TENANT: Control Tower-owned global tenant identity registry in the dedicated CONTROL_TOWER_DB_* database. GLOBAL_TENANT_UUID is published as the same canonical value as runtime T_CLIENT.UUID and tenant-owned TENANT_ID.
  • T_CONTROL_TOWER_TENANT_DEPLOYMENT: Control Tower-owned mapping from the canonical tenant UUID to target deployment. RUNTIME_CLIENT_ID is a deployment-local compatibility projection only; publication/onboarding must reject a runtime T_CLIENT.UUID mismatch and retain release/rollback metadata.

Yard / Equipment

  • T_YARD_LOCATION: yard occupancy source of truth.
  • T_YARD_LOCATION_HISTORY: yard state audit events.
  • T_YARD_DISPATCH_QUEUE: move/reserve/claim/confirm queue. API v2 yard-move write routes keep queue and T_YARD_LOCATION updates in explicit transactions.
  • T_EQUIPMENT: equipment/container master. YARD_LOCATION_CODE, YARD_QUEUE_TO_LOCATION, YARD_QUEUE_STATUS, YARD_QUEUE_ID, and YARD_STATE_SYNCED_AT are export-dashboard read mirrors of T_YARD_LOCATION / T_YARD_DISPATCH_QUEUE, not authoritative state. Tenant config EXPORT_YARD_EQUIPMENT_STATE_READY=1 means the tenant backfill has completed and export dashboard yard sort/filter can use these mirrors.
  • T_EQUIPMENT_LOAD: load binding for equipment.
  • T_EQUIPMENT_COMMODITY: equipment commodity detail; use delete-and-reinsert lifecycle where current code does.
  • T_EQUIPMENT_INFORMATION: common non-container equipment master. Fleet management should reuse this table for chassis, trailer, tractor, forklift, yard truck, and other owned/serviceable equipment instead of adding a fleet-specific master table. The equipment-activity migration extends it with status, yard, size, plate, current odometer, remark, and lifecycle columns where missing. The fleet asset master field extension adds optional owner/ownership, current hours, fuel type, capacity, meter type, and current meter value fields for type-specific fleet profiles.
  • T_EQUIPMENT_REPAIR_LOG: legacy repair/cost log tied to T_EQUIPMENT_INFORMATION. Keep it compatible during rollout; new fleet-style repair, PM, inspection, odometer, and work-order rows should use the planned generic activity ledger instead of adding more specialized repair tables.
  • T_EQUIPMENT_ACTIVITY: common activity ledger for service/repair, PM, inspection, odometer, work-order, and status history rows tied to T_EQUIPMENT_INFORMATION. It intentionally uses ACTIVITY_TYPE plus shared fields rather than separate fleet-specific tables; vendor context uses COMPANY_ID / COMPANY_NAME, and driver/actor context uses ACTOR_USER_ID / ACTOR_NAME. Attach reports, invoices, registrations, insurance, and odometer photos through T_FILE with TABLE_NAME set to T_EQUIPMENT_INFORMATION or T_EQUIPMENT_ACTIVITY.

Transload / Delivery

  • T_TRANSLOAD: appointment and execution unit. Final Bale Review is held here as BALE_REVIEW_STATUS, BALE_REVIEW_CONFIRMED_BY, BALE_REVIEW_CONFIRMED_AT, and BALE_REVIEW_WEIGHT_REASON; the latest accepted BOL send snapshot is BOL_LAST_SENT_AT, BOL_LAST_SENT_BY_USERNAME, and BOL_LAST_SENT_METRICS (WT,QTY). Operational STATUS remains unchanged and inventory source provenance remains in T_INVENTORY_LOT_LEDGER.
  • T_LOADING_PLANNER: dock/time/resource plan, loader assignment queue ordering (LOADER_QUEUE_ORDER), and loader job runtime timing mirror (JOB_START_TIME, JOB_END_TIME, JOB_ACCEPTED_USER). Current source contains two Loader Work Assignment representations: the active Route uses SPECIAL_INSTRUCTION='QUEUE_WORK_ASSIGNMENT', COMMODITY_NAME, and FINAL_DRAFT, while a separate typed helper uses nullable ASSIGNMENT_DATA JSON. Current environment column/index/row distribution requires RBR-AWCO-002 read evidence; do not assume either representation is the sole persisted contract.
  • T_TRANSLOAD_APPOINTMENT_BLOCK: reserved appointment capacity for a billing customer; other customers are blocked from the reserved timeblock.
  • T_STAFF_TIME_EVENT: staff work-time event ledger for clock in/out, break/lunch, and job start/end. The schema is generic for forklift, yard truck, office, general staff, and future work contexts. Loader break/lunch starts are allowed only from office-created break queue rows started on the inbound-loader page or manager correction flows, not as always-available self-service break buttons.
  • T_DELIVERY: driver delivery operation. USER_ID stores the assigned driver T_USER.ID. API v2 driver-operation status, assignment, arrival signal/action, parking workflow, and field updates must include CLIENT_ID in write predicates. Arrival V1 uses ARRIVAL_SIGNAL, ARRIVAL_SIGNAL_AT, and ARRIVAL_ACTION for notification state only. Parking workflow uses PARKING_STATUS, PARKING_REQUESTED_AT, PARKING_ASSIGNED_AT, and PARKING_CONFIRMED_AT; actual yard/dock location remains in T_YARD_DISPATCH_QUEUE / T_YARD_LOCATION.
  • T_DRIVER_LOG: driver inspection/session log. USER_ID stores the driver T_USER.ID; this table has no separate driver-master runtime reference.
  • T_BOOKING: export booking confirmation data including terminals, POL/POD, min/max weight, shipping instruction fields, booking print fields, and optional SALES_ORDER_ID / SALES_ORDER_UUID pointers to the primary outbound T_LOAD sales order.

Orders / Inventory

  • T_COMMERCIAL_ORDER: commercial order header. SHIPPING_PERIOD stores the commercial order Due Date as nullable DATE; legacy free-text shipping period values are no longer preserved. QUANTITY_40F, QUANTITY_40S, QUANTITY_40H, QUANTITY_45H, and QUANTITY_20S mirror the booking-style planned container counts; CONTAINER_TEXT and SIZE remain legacy/print summary fields. SELLER_SIGNATURE and BUYER_SIGNATURE store jSignature-compatible captured signature payloads for the commercial confirmation print form.
  • T_COMMERCIAL_ORDER_ITEM: commercial order commodity/rate detail.
  • T_LOAD: canonical inbound/outbound execution-load header for MK-style operations. Inbound PURCHASE and outbound SALES writes may use COMMERCIAL_ORDER_ID=-1 until a commercial PO/SO is available; positive purchase IDs must validate against ORDER_TYPE='PURCHASE', and positive sales IDs must validate against ORDER_TYPE='SALES'. ORDER_NUMBER remains an internal generated/load number, not the commercial PO/SO source of truth. ORDER_STATUS is a separate manual close label and is not proof of item, transload, lot, allocation, scale, packing or delivery completion.
  • T_LOAD_ITEM: order line item. COMMERCIAL_ORDER_ITEM_ID is the item-level commercial source pointer for multi-item commercial order execution; the existing header-level T_LOAD.COMMERCIAL_ORDER_ITEM_ID remains for backward compatibility.
  • T_LOAD_ALLOCATION: inventory allocation from T_INVENTORY_LOT to outbound T_LOAD_ITEM; OMS allocation API v2 apply writes must update it inside the allocation transaction.
  • Inbound receipt state is not one table state: T_LOAD_ITEM.ITEM_STATUS, T_TRANSLOAD.STATUS, T_SCALE_TICKET evidence and T_INVENTORY_LOT lifecycle must be reread separately. The current direct UNLOADED/first OUT_TIME path can materialize a lot without explicit finalize. Exact source lot sync currently enforces one source transload to one active purchase item and one lot; multi-line receipt finalization rolls back at this boundary.
  • Outbound operational/readiness values are derived from T_LOAD_ITEM, T_LOAD_ALLOCATION, source lots/items, Booking, equipment and Scale evidence; they are not stored completion states. Direct outbound CRUD does not create T_LOAD_ALLOCATION.
  • T_EQUIPMENT_LOAD.OUTBOUND_ORDER_ID plus its equipment/commodity rows form the outbound execution bridge. T_BOOKING.SALES_ORDER_ID / SALES_ORDER_UUID are reverse pointers; current unprogressed outbound delete must be verified for stale reverse-reference evidence because the delete helper does not explicitly clear them.
  • T_INVENTORY_LOT: received inventory lot and remaining balance. WMS inventory API v2 owns lot list/detail/update/adjust routes and must tenant-scope every access by CLIENT_ID. Bale adjustment and completed-unload inventory source tracking use LOT_SOURCE_TYPE, SOURCE_TRANSLOAD_ID, SOURCE_TRANSLOAD_UUID, and SOURCE_CODE. The active Extra workflow has no reservation step: Office applies a selected Spare source through a guarded balance write after physical completion. The deployed RESERVED_QUANTITY column is legacy compatibility state pending a separate verified-empty cleanup migration.
  • T_INVENTORY_LOT_LEDGER: WMS bale adjustment ledger for source lot balance movements. It keeps integer ID primary key plus UUID BINARY(16) NOT NULL UNIQUE, lifecycle columns, CLIENT_ID, lot ID/UUID, related transload ID/UUID, optional allocation ID/UUID, event type, quantity/weight deltas, before/after balances, reference/reason, remark, source warehouse-location snapshot, and created user.
  • T_TRANSLOAD.EXTRA_BALE_SOURCE_LOT_UUID and EXTRA_BALE_SOURCE_QTY are legacy compatibility columns from the retired Loader-selection design. Active code does not read or write them; Yard Closing has no inventory side effect. They may be dropped only by a separate post-deployment migration after a verified-empty preflight. The durable Extra source relation is the active T_INVENTORY_LOT_LEDGER.EVENT_TYPE='EXTRA_APPLIED' row written by Office Inventory Adjustment.
  • T_INVENTORY_LOT_SNAPSHOT: monthly customer-reported inventory lot snapshot rows for the Inventory Report Physical tab. One row stores one commodity summary row for a company/period. Tenant scope keeps runtime integer CLIENT_ID; company scope uses COMPANY_UUID instead of integer COMPANY_ID. The table keeps only snapshot grouping/status fields (SNAPSHOT_UUID, period, DRAFT/FINALIZED, FINALIZED_AT) plus the reported commodity values (COMMODITY, ST, BALE_QTY, AVG_WEIGHT_LB, SORT_ORDER). Current draft generation reads active remaining T_INVENTORY_LOT rows; Previous Physical Inventory reads finalized rows from this table, not live inventory lot recalculation.
  • V_TRADE_SUMMARY: OMS-owned trade summary/deal ledger reporting view exposed through read-only API v2 routes.

Billing / Files / Mail

  • T_TRANSACTION, T_TRANSACTION_LIST: AP/AR transaction data. New AR Billing container invoices keep T_TRANSACTION.TYPE='AR', BILLING_SCOPE='CONTAINER', BOOKING_UUID, and RELATED_TABLE_UUID pointing to the billed container; line rows carry source/rate UUID traceability where the source event differs from the container header. Legacy AP and source-specific AR rows keep BILLING_SCOPE='LEGACY'.
  • T_BILLING_PRESET: customer-scoped AR Billing preview preset. Each active customer can have one preset; presets propose container charge lines only, and confirmed invoice rows are written to T_TRANSACTION / T_TRANSACTION_LIST.
  • T_SCALE_TICKET: scale ticket header and weight/ticket metadata. BMS scale-ticket API v2 update/delete and closing/outbound-weight writes must include CLIENT_ID in write predicates.
  • T_SCALE_TICKET_EVENT: tenant-scoped immutable Scale correction/void/reversal ledger. Each row owns UUIDv7 and common lifecycle columns, the ticket/event/stage identity, before/after axle-weight snapshots, input/canonical/display UOM, mandatory reason, actor and request ID. The current writer creates REWEIGH_CORRECTION before a completed-stage weight changes, TICKET_VOID before an incomplete ticket is soft-deleted, and TICKET_REVERSAL when a completed ticket has no downstream receipt/inventory/ledger/allocation/delivery execution. A successful reversal clears the active order/transload/equipment ticket pointers; progressed tickets remain blocked. It is intentionally separate from the reference-only T_PROCESS_TRANSITION_AUDIT, which does not accept free-form reasons or weight snapshots.
  • T_COMMODITY_RATE, T_TRUCKING_RATE: BMS rate tables exposed through API v2 rate routes. Optional EFFECTIVE_DATE / EXPIRATION_DATE fields track date-specific rate sheets; default lookup behavior remains date-agnostic until date-based matching is explicitly enabled. Trucking-rate terminal matching must avoid SQL subqueries and keep tenant scope on both rate and company lookups.
  • T_FILE: file metadata for uploaded documents. Hybrid storage rows use STORAGE (efs or s3) to interpret PATH. Existing EFS rows keep PATH as the legacy media path; new S3 rows store the S3 object key in PATH using uploads/{CLIENT_ID}/{TABLE_NAME}/{UUID}.{ext} and do not include TABLE_ID in the object key. OBJECT_CLEANUP_AT records successful post-delete object cleanup or intentional retention for another active metadata reference; a soft-deleted row with a null value remains retryable and must not be purged without another cleanup attempt. OBJECT_CLEANUP_ATTEMPT_AT makes the bounded weekly retry oldest-attempt-first so a permanent failure cannot block later rows. Local prod has IDX_T_FILE_OBJECT_CLEANUP (OBJECT_CLEANUP_AT, DELETED_AT, OBJECT_CLEANUP_ATTEMPT_AT, ID) and IDX_T_FILE_ACTIVE_OBJECT (STORAGE, DELETED_AT, PATH(191), ID); archive schema sync adds the latter when missing without dropping archive-only indexes. S3 download URLs are direct public object/CDN URLs by default; signed URLs are opt-in through S3_FILE_DOWNLOAD_MODE=signed. URL is a generated/display URL, not the storage source of truth. AP invoice/payment documents are linked to T_TRANSACTION; a shared payment document is represented by one metadata row per related transaction.
  • T_MAILBOX_WATCH_STATE: Gmail poll state.
  • T_MAIL_MESSAGE: normalized inbound mail record.
  • T_MAIL_REQUEST_ITEM: extracted mail action items.

Status Values

Table Important values
T_TRANSLOAD.STATUS SCHEDULED, PRE_PLANNED, ARRIVED, AUTO_PLANNED, STAGING, DOCKED, UNLOADING, TRANSLOADING, UNLOADED, TRANSLOADED, NO SHOW, CANCELLED
T_LOADING_PLANNER.JOB_STATUS current Route uses SCHEDULED, STARTED, FINISHED, CANCELLED; the separate typed helper writes CANCELED. Current database population by spelling requires RBR-AWCO-002 read evidence before normalization.
T_STAFF_TIME_EVENT.EVENT_TYPE active: CLOCK_IN, CLOCK_OUT, BREAK_IN, BREAK_OUT, JOB_START, JOB_END; loader break starts come from assigned break queue starts on the inbound-loader page or manager correction flows
T_STAFF_TIME_EVENT.WORK_CONTEXT FORKLIFT, YARD_TRUCK, OFFICE, GENERAL, OTHER
T_STAFF_TIME_EVENT.SOURCE TELEGRAM_PRIVATE, TELEGRAM_GROUP, INBOUND_LOADER, SYSTEM, BACKFILL
T_EQUIPMENT_LOAD.STATUS AVAILABLE, VESSEL IN TRANSIT, EMPTY, CLOSED
T_DELIVERY.STATUS SCHEDULED, DISPATCHED, DELIVERED, TROUBLE, DRY-RUN, CANCELLED, COMPLETED; do not overload this for arrival notification state
T_DELIVERY.ARRIVAL_SIGNAL V1 notification values: blank, NEAR_YARD, AT_YARD_ENTRANCE
T_DELIVERY.ARRIVAL_ACTION V1 notification action values: blank, ASSIGNED, HOLD_OUTSIDE, DISMISSED
T_YARD_DISPATCH_QUEUE.STATUS QUEUED, SCHEDULED, CLAIMED, COMPLETED, CANCELLED
T_TRANSACTION.STATUS OPEN, CLOSED, VOID style existing values
T_LOAD.ORDER_STATUS OPEN, SCHEDULED, PARTIAL, COMPLETED, CANCELLED, CLOSED style values
T_COMMERCIAL_ORDER.STATUS DRAFT, OPEN, PARTIAL, FULFILLED, CANCELLED; current PATCH accepts manual selection and does not derive transitions from T_LOAD execution
T_LOAD_ITEM.ITEM_STATUS OPEN, SCHEDULED, UNLOADED, PARTIAL, ALLOCATED, COMPLETED, CANCELLED
T_INVENTORY_LOT.LOT_STATUS live local enum: OPEN, PARTIAL, CLOSED, HOLD; historical docs may mention future ALLOCATED/VOID values that are not currently deployed
T_INVENTORY_LOT.LOT_SOURCE_TYPE PURCHASE, LEFTOVER, SPARE, MANUAL, ADJUSTMENT
T_INVENTORY_LOT_LEDGER.EVENT_TYPE LOT_CREATED, EXTRA_APPLIED, LEFTOVER_CREATED, SPARE_CREATED, LEFTOVER_LOAD_APPLIED, RELOAD_APPLIED, LOCATION_MOVED, COUNT_CHECK, REVERSAL
T_YARD_LOCATION_HISTORY.EVENT_TYPE MOVE, MODE_SET, BLOCK_SET, BLOCK_CLEAR, OCCUPY, VACATE, SYNC

Relationship Rules

  • T_LOAD_ITEM.ORDER_ID belongs to T_LOAD.ID.
  • T_LOAD.COMMERCIAL_ORDER_ID belongs to T_COMMERCIAL_ORDER.ID; positive inbound purchase load values must reference ORDER_TYPE='PURCHASE', positive outbound sales load values must reference ORDER_TYPE='SALES', and PO/SO-less load values may remain unlinked with -1.
  • T_COMMERCIAL_ORDER_ITEM.COMMERCIAL_ORDER_ID belongs to T_COMMERCIAL_ORDER.ID; item rows carry commodity/rate details for PO/SO lines.
  • T_LOAD_ITEM.COMMERCIAL_ORDER_ITEM_ID optionally points to the commercial item that supplied commodity/rate context for that execution line.
  • T_INVENTORY_LOT.SOURCE_ORDER_ID and SOURCE_ORDER_ITEM_ID point to inbound order data.
  • T_INVENTORY_LOT.SOURCE_TRANSLOAD_ID and SOURCE_TRANSLOAD_UUID point to the loader completion context that created leftover/spare/adjustment source lots and to the completed unload that produced an unambiguous purchase inventory lot. SOURCE_CODE should be the user-facing reference/load number, not an internal row id. During the ID-to-UUID transition, WMS bale adjustment APIs validate both values when both are supplied and write both where available.
  • T_INVENTORY_LOT_LEDGER.INVENTORY_LOT_ID / INVENTORY_LOT_UUID point to T_INVENTORY_LOT; RELATED_TRANSLOAD_ID / RELATED_TRANSLOAD_UUID point to the completed T_TRANSLOAD when the ledger event came from loader completion, reload source application, or a created leftover load source apply. Ledger rows are never deleted for void behavior; void inserts a REVERSAL row and attempts the inverse balance delta. A negative resulting quantity is rejected, while a negative resulting weight is clamped to zero even though the full inverse weight delta is recorded.
  • T_LOAD_ALLOCATION.INVENTORY_LOT_ID points to T_INVENTORY_LOT.ID and must be tenant-scoped by CLIENT_ID.
  • T_LOAD_ALLOCATION.OUTBOUND_ORDER_ID and OUTBOUND_ORDER_ITEM_ID point to outbound order demand and must be written with the same CLIENT_ID as the target T_LOAD / T_LOAD_ITEM.
  • T_EQUIPMENT_LOAD connects equipment to operational load/transload context.
  • T_YARD_LOCATION points to occupied entity type/id rather than relying on equipment mirror fields.
  • T_DELIVERY.USER_ID and T_DRIVER_LOG.USER_ID point to driver users in T_USER.ID; no separate driver master table is part of the runtime contract.
  • During the Task #206 staged production rollout, legacy DRIVER_ID columns and the legacy driver master table may remain as compatibility-only schema until cleanup. Runtime code must still read/write USER_ID; the compatibility migration keeps legacy and new columns synchronized for old workers or rollback before final cleanup.
  • T_LOADING_PLANNER.UNLOADING_EQUIPMENT_LOAD_ID points to the related T_TRANSLOAD.ID in active loader planning/runtime flows.
  • Loader runtime Start updates T_LOADING_PLANNER.JOB_START_TIME, JOB_ACCEPTED_USER, and JOB_STATUS='STARTED'; Complete updates JOB_END_TIME and JOB_STATUS='FINISHED'.
  • Loader runtime Start/Complete also writes T_STAFF_TIME_EVENT JOB_START / JOB_END rows linked by JOB_SOURCE_TYPE='LOADING_PLANNER' and JOB_SOURCE_ID=T_LOADING_PLANNER.ID.
  • Staff clock buttons write T_STAFF_TIME_EVENT CLOCK_IN / CLOCK_OUT rows. Telegram time-clock webhook resolves staff from the Telegram chat/group id stored on T_USER.TELEGRAM_USER_ID; Telegram actor user ids are not used as the staff lookup key and are not duplicated into this table.
  • Manager timeline corrections and assigned break queue starts from the inbound-loader page write/update/soft-delete T_STAFF_TIME_EVENT clock and break rows. Correction reason is required for manager edits and stored in NOTE; correction actor is stored in CREATED_BY. 10MIN BREAK and LUNCH BREAK starts create a BREAK_IN row; once the configured duration has elapsed, the system creates the paired BREAK_OUT row automatically. Telegram break callbacks do not create staff-time rows.
  • When QUICKBOOKS_TIME_ACCESS_TOKEN is configured in T_TENANT_CONFIG or env, T_STAFF_TIME_EVENT remains the source of truth for QuickBooks Time staff-time sync. The outbound sync runs only after the CTC write has committed, reads jobcode mapping from env/tenant config, reads the QuickBooks Time user id from T_USER.QUICKBOOKS_TIME_USER_ID, then logs success/failure without adding QuickBooks-specific columns to the staff-time table. QuickBooks OAuth setup stores QUICKBOOKS_TIME_ACCESS_TOKEN, optional refresh token, OAuth user/client id, and expiry metadata in T_TENANT_CONFIG for the configured CTC client.
  • If T_STAFF_TIME_EVENT has not been deployed on a target DB, staff-time reads must fail closed or return an empty timeline without a 500; writes must report that staff-time storage is not configured.
  • Loader Timeline reads T_STAFF_TIME_EVENT as the source of truth for staff activity blocks and idle gaps. T_LOADING_PLANNER remains joined context for job detail, not the timeline event source.
  • Legacy MK source IDs are external identifiers only. Do not assume T_PURCHASE.PURCHASE_ID, T_PURCHASE_COMMODITY.PURCHASE_COMMODITY_ID, T_SALES.SALES_ID, T_SALES_COMMODITY.SALES_COMMODITY_ID, T_BOOKING.BOOKING_ID, or T_COMPANY.ID equal active CTC row IDs; use the migration mapping files (company_map, booking_map, purchase_order_map, purchase_item_lot_map, sales_order_map, sales_item_map) when bridging data.

Delete And Update Patterns

  • Normal application deletes are soft deletes through DELETED_AT.
  • Hard deletes must use an explicit hard-delete escape hatch and must be justified by the calling flow.
  • Use ACTIVE_UNIQUE generated-column indexes for natural keys that must allow re-creation after soft delete. Do not rely on UNIQUE (..., DELETED_AT).
  • Use delete-and-reinsert detail lifecycles only when the unique keys are active-row aware or the flow explicitly restores/updates existing rows.
  • Preserve visible errors when data integrity blocks a write.

Planned / Active Registry Schema

  • T_MODULE: module definition.
  • T_TENANT_MODULE: tenant enablement.
  • T_TENANT_CONFIG: tenant config.
  • T_MODULE_PAGE: page registry and menuable pages.
  • T_CONTROL_TOWER_DEPLOYMENT: deployment/server registry.
  • T_CONTROL_TOWER_TENANT: global tenant UUID registry.
  • T_CONTROL_TOWER_TENANT_DEPLOYMENT: tenant-to-deployment runtime mapping.

Loader Assignment Compatibility

  • Repository source contains a migration for nullable T_LOADING_PLANNER.ASSIGNMENT_DATA JSON. The current active Route contract separately uses SPECIAL_INSTRUCTION='QUEUE_WORK_ASSIGNMENT', COMMODITY_NAME, and FINAL_DRAFT. Current database column/index/row distribution is not inferred from source and is owned by the RBR-AWCO-002 read-only preflight.
  • Both representations reuse Planner lifecycle/time/queue columns and T_STAFF_TIME_EVENT, but they differ in request identity, terminal Clock Out visibility, Final Break identity, notification evidence, and cancellation spelling. Do not describe either representation as the sole current contract without environment evidence.
  • Historical candidate evidence remains valid only for its exact source/environment. Current Production schema and row behavior require explicit readback before any correction or migration decision.

Sources

  • Historical/incomplete CTC schema snapshot: .agents/reference/schema.sql
  • Legacy MK source dump: reference/mkmetal/mk.sql (generated Jun 02, 2026 at 11:56 AM)
  • Architecture: .agents/rules/ARCHITECTURE.md
  • Query rules: .agents/rules/DB_QUERY_RULES.md
  • Audit and Transition evidence: .agents/rules/AUDIT_AND_TRANSITION_EVIDENCE.md
  • Target design: docs/DESIGN.md

---

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