// ============================================================================= // ACFS VBS Portal - Production Schema v1.6 (Intent Model v0.18.0) // ============================================================================= // Generated: 2026-03-26 | Last Updated: 2026-08-20 // Source: v1.4 + the AS-BUILT OutSystems VBS_ShipmentERD and VBS_BookingERD // (both supplied by Jewel TM, 2026-08-20) // Status: SHIPMENT AND BOOKING SIDES BOTH RECONCILED WITH PRODUCTION // Documents/admin side (delivery_orders, delegations, users) still has no ERD // ============================================================================= // // v1.6 (2026-08-20) - Booking ERD received and reconciled. GOOD NEWS FIRST: // the booking side matches this file almost field for field, including the GST // split, is_late_change/late_change_fee, the cutoff_override trio, notes, // booked_by_user_id/email, truck_count and the whole cancelled/processed audit set. // Payments matches exactly. pricing_zones matches the rate entity (effective_from/ // effective_to/is_active). The build was clearly implemented from this schema, so // the drift found on 2026-08-20 is concentrated on the SHIPMENT/ingestion side, // not here. // // Only 3 booking-side divergences, all annotated in place: // - slot_id/slot_date live on BookingHBLS in production, not on Bookings (OQ-083). // Mirrored onto booking_hbls; the pair on bookings is marked as not-in-production. // - Truck has no booking_id in production (OQ-084). // - Production has Bookings.site_induction_completed AS WELL AS Truck.SiteInductionFlag. // Consistent with BR-017 if the booking-level field is a rollup, but unconfirmed. // // STILL NO ERD FOR: delivery_orders, delegations, delegation_hbls, users, // user_invitations, email_notifications, entity_audit_log, PartyAccountMapping, and // slot blackout dates. Delivery orders are the notable absence given DO upload and // validation are core R2 flows and generated most of August's bug traffic. Those // tables below remain pre-build spec. // // v1.5 (2026-08-20) — Reconciled against the AS-BUILT production ERD. // READ THIS FIRST: this file is no longer the spec. The portal is built and in // UAT on OutSystems, and where this file and the production ERD disagree, the // ERD wins. Only the SHIPMENT side has been reconciled - the supplied ERD covers // 8 entities (Containers, HBLS, Companies, Sites, Slots, DriverRecords, // HBLCustodyChain, ReleaseAuthority). The booking side (bookings, trucks, // payments, delivery_orders, delegations, users) is in a SEPARATE ERD that has // not been supplied, so those tables below remain pre-build spec - treat with // suspicion until the second ERD arrives. // // ADDED to match production: // - hbls: parent_hbl_id, parent_company_id, relationship_type, match_level, // needs_manual_review, is_manually_updated, exception_id, cargo_hold_status, // original_msg_ref — the hierarchy-matching + ingestion-exception block (BR-034). // - containers.container_hold_status; companies.customer_code. // - customs_status enum notes rewritten with the confirmed derivation rules // (LCBP 2760474628). The four values were already correct here; the INTENT // MODEL was the stale copy and has been corrected to match. // // KNOWN DIVERGENCES, deliberately NOT changed here (naming only, no behaviour): // - Production carries UUID + created_by/updated_by on every entity; this file // uses int ids + the audit columns it already had. // - hbls.release_status is a varchar here; production uses ReleaseStatusId, an FK // to an HBLReleaseStatus lookup. // - containers.is_active vs production IsActive_DEPRECATED (production is // phasing the flag out; do not write to it). // - slots.booking_cutoff_relative_day vs production BookingCutoffOffsetDay. // - hbls.free_release_verified has NO counterpart in the production ERD. Either // it was dropped in the build or it lives elsewhere — unresolved, worth asking. // // UNMODELLED, needs an answer: slot BLACKOUT DATES. VBPP-212 added a blackout // field to the slots API and VBPP-256 requires blackout dates to be skipped when // resolving "previous working day" cutoffs, but the production shipment ERD shows // no blackout structure on Slots. Either it is in the unsupplied ERD or nowhere. // // v1.4 (2026-06-25) — Reconciled with intent model v0.14.0 + resolved Maximus understanding: // - payment_gateway: Compay is the confirmed Phase-1 gateway (2026-05-21); stripe // demoted to the earlier, superseded assumption (OQ-035). // - under_bond: reframed as a portal-side manual flag set by LSP/ACFS, NOT synced // from Maximus (C5); kept `under_bond_verified` (matches the model — ACFS records // an external verification result). // - free_release_verified: ADDED to match the model; do_waived now gated on // verification — (free_release AND free_release_verified) OR (under_bond AND // under_bond_verified) — the raw flag alone no longer waives the DO (BR-002). // - alt_hbl_reference / consignee_account_code: notes rewritten — hierarchy is // portal-built by Wt/Vol matching; consignee code is backend-resolved from a // free-text name via ACFS alias mappings (24 Jun call, OQ-034/B1/C7). // - Removed leftover `bookings.site_induction_skipped` (induction is per-truck). // // v1.3 (2026-06-24) — Data Mapping Clarifications call (ingestion + hierarchy): // - Order-pull filter (IntOrderHd): ModuleId=0, JobType=102 (import), // ShipmentType=2 (LCL), OrderCategory contains "CF" but not "CFCU", exclude NZ. // - EDI→order join: EdiRequestedCargo has no OrderNo/ContainerKey — join to the // order via ContainerNo + VesselID + VoyageNo (validated on TRHU2650107 → // order IBR00481829). // - HBL hierarchy/visibility (hbls.party_references): the backend TRANSPOSES // EdiRequestedCargo into {primary ref, alternative ref, logistic provider}, // built by weight+volume matching of FFO vs blank rows (probabilistic + manual // exceptions). The end consignee does not log in; portal users are the LSPs. // - Pre-unpack milestones come from ACFS "Connect" business rules (not Maximus); // Confluence documents the status rules (all but one). NOT discussed on this // call: holds (B2) semantics, data retention, sync cadence. // v1.2 (2026-06-16) — Intent Model v0.14.0 / BRD V2.0: // - Multi-truck booking (BR-039, FR-LSP-30): new `trucks` table; driver/vehicle // fields moved off `bookings` onto `trucks`; `bookings.truck_count` added; // `booking_hbls.truck_id` records which truck collects each HBL. // - Release authority (BR-041): new `release_authorities` table for digital-release // acts against intermediate HBLs; new `release_mechanism` and // `release_authority_status` enums. // - HBL level/release fields (BR-040, BR-042): `hbls.ffo_flag`, // `hbls.hbl_level_indicator`, `hbls.release_status`; new `hbl_level` and // `hbl_release_status` enums. // - Booking status (BRD V2.0 §6.1): `booking_status` enum re-valued to // draft | open | processed | complete | cancelled. // - Site mapping (BR-043): `sites.branch_code` retained as the mapping key. // v1.3 (2026-07-01) — Maximus 1 Jul data-validation call + 30 Jun William/Jason: // - Milestones (B3) RESOLVED (design): four container-level milestones derived // from EDICommon event/movement codes keyed by the CONTAINER KEY // (ACFS_Container_Location_CMT): On Water = AdmVesselSchedule.ActArrivalDate > // today (the DEFAULT); On Wharf = EventCode HL001; In Yard = HL003. Unpacked = // IntOrderItem.TallyInNo -> CfsTallyInHed ("query 2", skips CfsTallyInDet); // Collected = tally-out not null. Supersedes v1.1's "Connect rules / StateCFS". // Implementation not fully closed (EDICommon query perf with William). // - Holds (B2): confirmed at container (IntContainerHold) + cargo (IntCargoHold) // level via AdmHold.Description; cargo join = CargoKey (CargoHoldKey null in // data; spec says CargoHoldKey — Jason to reconcile); container "active" = ANY // hold row lacking a release date; cargo-hold polling is manual. // - Hierarchy (C1): match on 4 fields (Gross+Net+Vol+Qty), certainty 100/95/90/50%, // dedup by highest OriginalMsgRef, container-scoped; ~7k ICS non-match, ~290/day // manual matches. Consignee free-text -> account is a required step. // - Additional data: dangerousGoods = IntContainer.CargoType + IntOrderItem. // IMOCode/UNCode; chepPallets = IntOrderItem.UOM2 + Quantity2; isUrgentUnpack = // IntContainer.Remark (URGENT/IMMEDIATE) — out of initial release. // // v1.5 (2026-07-09) — Maximus tracking-field sweep (future-proofing + deferred columns): // - sites: + warehouse_code (Maximus WHCode; branch_code stays the live mapping key, B5). // - containers: + vessel_id, voyage_no (disambiguate a container_number reused across // voyages), + container_key (the join key for EDICommon milestones + IntContainerHold), // + customer_company_id (L1 wholesale-FF customer at container level → companies), // + maximus_created_date (source-side create timestamp for sync/CDC — OQ-063/064/065). // - hbls: + net_weight_kg (the 4th hierarchy-match field, Gross+Net+Vol+Qty), // + cargo_key (per-HBL spine — tally-out + cargo-hold joins), + collected_by_code // (cfsTallyOutHed.ContractorCode), + maximus_updated_at (source-side update ts), // + un_code / dangerous_goods / pallet_quantity / pallet_type (DG + pallets, OUT OF // INITIAL RELEASE — columns provisioned for the fast follow). // - Existing columns KEPT their portal names (company_id, maximus_reference, // last_free_storage_date) — the Maximus source names (CustomerCode, OrderNo, // StorageStartDate) live in maximus-mapping.ts, deliberately not renamed here. // ============================================================================= // ============================================================================= // ENUMS // ============================================================================= Enum company_type { wholesale_freight_forwarder freight_forwarder transport_carrier customer clearing_agent } Enum user_role { acfs_admin [note: 'Full privileges - user management, slot config, overrides'] acfs_user [note: 'Restricted privileges - DO validation, booking verification'] lsp [note: 'Logistics Service Provider - external company users'] } Enum au_state { NSW VIC QLD SA WA TAS NT ACT } Enum slot_day_of_week { monday tuesday wednesday thursday friday saturday sunday } Enum container_status { received [note: 'Container received at ACFS warehouse'] unpacking [note: 'Container being unpacked'] unpacked [note: 'All HBLs extracted and available for pickup'] } Enum milestone_status { on_vessel [note: 'Container on ship, not yet at port'] at_wharf [note: 'Container at port wharf'] in_yard [note: 'Container moved to yard storage'] unpacked [note: 'Goods unpacked and ready - required for booking per BR-001'] collected [note: 'Physically picked up by booking party'] } Enum hbl_status { unassigned [note: 'Not yet allocated to any LSP'] assigned [note: 'Allocated to primary LSP by ACFS'] delegated [note: 'LSP passed to another party - cannot book per BR-004'] booked [note: 'Pickup scheduled - cannot delegate per BR-004'] } // CONFIRMED 2026-08-20 against Confluence LCBP 2760474628 "HBL Staging to Master Business Rules". // All four values are DERIVED from cargo-hold rows and RECALCULATED on every sync from the // latest Maximus release-date data - a status is never carried forward. Enum customs_status { pending [note: 'No cargo-hold records exist AT ALL, so no final clearance is available. NOT "cleared but unconfirmed" - an HBL with no holds is not bookable'] partial_clearance [note: 'Some holds released, others still active'] fully_cleared [note: 'All applicable holds released - required for booking per BR-001'] held [note: 'One or more ACTIVE cargo holds (BOND, AQIS, Customs or ACFS hold)'] } Enum release_type { do_required [note: 'Delivery Order must be uploaded and validated'] free_release [note: 'No DO required - auto-approved for pickup'] } // v1.2 (BR-040): HBL hierarchy level, derived from ffo_flag. Enum hbl_level { lowest_level [note: 'FFO flag ABSENT - requires a DO for release (DO path)'] intermediate [note: 'FFO flag PRESENT - requires only a digital release (digital-release path)'] } // v1.2 (BR-040/BR-041): derived per-HBL release-authority rollup shown in the HBL list. Enum hbl_release_status { digital_release_provided [note: 'Intermediate HBL - a provided digital_release authority exists'] do_validated [note: 'Lowest-level HBL - DOs validated OR verified waiver (do_waived)'] pending [note: 'Release authority not yet granted'] } // v1.2 (BR-041): the FORM of a release-authority act (distinct from hbls.release_type). Enum release_mechanism { digital_release [note: 'Intermediate HBLs - electronic approval flag, no document'] do_based [note: 'Lowest-level HBLs - backed by a validated DO or verified waiver'] } // v1.2 (BR-041): lifecycle of a release-authority act. Enum release_authority_status { provided [note: 'Release recorded and in effect'] superseded [note: 'Replaced by a later release, or its delegation/assignment was revoked/reassigned'] } Enum do_validation_status { not_provided [note: 'DO not yet uploaded'] uploaded [note: 'DO uploaded, awaiting validation'] pending_validation [note: 'ACFS staff reviewing DO'] validated [note: 'DO confirmed valid - booking can proceed'] flagged [note: 'DO incorrect - requires correction and re-upload'] not_required [note: 'Free release or under-bond - no DO needed'] } Enum booking_status { // v1.2 (BRD V2.0 §6.1): re-valued to draft | open | processed | complete | cancelled. // Maps from prior values: open = former booked/pending_processing; processed unchanged; // complete = former collected. HBL/milestone 'collected' value is UNCHANGED (see milestone_status). draft [note: 'Transient pre-confirmation - being assembled / payment pending'] open [note: 'Confirmed and paid - awaiting ACFS processing (BRD V2.0 §6.1)'] processed [note: 'ACFS pickup-verified via manual CTA - ready for physical pickup'] complete [note: 'DERIVED when all HBLs reach the collected milestone (Tally Out) per BR-028 - no manual change'] cancelled [note: 'Booking cancelled by LSP or ACFS - allowed only before processed'] } Enum payment_gateway { compay [note: 'Compay - confirmed Phase-1 payment gateway (2026-05-21)'] stripe [note: 'Earlier assumption, superseded - the payment integration is abstracted so a gateway could be swapped, but Phase 1 uses Compay (OQ-035)'] } Enum payment_status { pending [note: 'Payment initiated but not confirmed'] completed [note: 'Payment successful - booking confirmed'] failed [note: 'Payment gateway rejected transaction'] refunded [note: 'Payment refunded - processed outside VBS but status tracked'] } Enum delegation_method { existing_lsp [note: 'Delegate to registered LSP account'] one_off_p4tc [note: 'Delegate to one-off party via email (deferred Phase 1)'] } Enum delegation_status { active [note: 'Delegation in effect'] revoked [note: 'Delegation cancelled by ACFS - HBLs revert to delegator'] } Enum notification_type { do_invalid [note: 'DO marked invalid - requires correction'] booking_confirmation [note: 'Booking confirmed with reference number'] booking_cancellation [note: 'Booking cancelled by LSP or ACFS'] booking_modified [note: 'Booking details changed'] user_invitation [note: 'New user invited with sign-in link'] hbl_assignment [note: 'HBLs assigned to LSP'] user_removal [note: 'User account deactivated'] delegation_notification [note: 'HBLs delegated to party'] } Enum notification_status { pending [note: 'Queued for sending'] sent [note: 'Successfully delivered'] failed [note: 'Delivery failed - see error_message'] } Enum audit_action { created [note: 'Entity created'] updated [note: 'Entity modified'] deleted [note: 'Entity soft/hard deleted'] } Enum entity_type { booking hbl delegation slot user delivery_order } // ============================================================================= // TABLES // ============================================================================= // ───────────────────────────────────────────────────────────────────────────── // TABLE GROUP: Companies & Users // ───────────────────────────────────────────────────────────────────────────── Table companies { Note: ''' All external logistics organisations that interact with the VBS portal. ABN captured for Australian tax compliance (GST invoice generation). FIXED v1.1: Added archived_at for soft-delete consistency ''' id int [pk, increment] company_name varchar(255) [not null] company_type company_type [not null] company_id varchar(100) [unique, note: 'External identifier from AGS or onboarding'] customer_code varchar(100) [null, note: 'ADDED 2026-08-20 (prod ERD Companies.CustomerCode). Maximus CustomerCode from IntOrderHd - the join used to resolve the L1 service provider during ingestion. An unresolvable code raises AccountMappingFailed and sets hbls.needs_manual_review (BR-034)'] branch_code varchar(50) [note: 'Multiple branches may exist per company'] abn char(11) [note: 'Australian Business Number - 11 digits no spaces - GST invoices'] email varchar(255) [not null, unique, note: 'Primary contact email for notifications'] state au_state [note: 'Company primary state for GST treatment'] is_active boolean [not null, default: true] archived_at timestamp [null, note: 'FIXED v1.1: Soft-delete timestamp for consistency'] created_at timestamp [not null, default: `now()`] updated_at timestamp [not null, default: `now()`] indexes { (company_type, is_active) [name: 'idx_companies_type_active'] abn [name: 'idx_companies_abn'] email [name: 'idx_companies_email'] } } Table users { Note: ''' Platform users covering three roles (ACFS Admin, ACFS User, LSP). ACFS users authenticate via Okta SSO. LSP users authenticate via email/password with 72hr invitation link per BR-014. ''' id int [pk, increment] company_id int [null, note: 'NULL for ACFS Admin/User'] first_name varchar(100) [not null] last_name varchar(100) [not null] email varchar(255) [not null, unique] role user_role [not null] okta_id varchar(255) [null, unique, note: 'For ACFS users via Okta SSO'] password_hash varchar(255) [null, note: 'For external LSP users only'] is_active boolean [not null, default: true] archived_at timestamp [null, note: 'Soft delete - access disabled but audit retained'] created_at timestamp [not null, default: `now()`] updated_at timestamp [not null, default: `now()`] indexes { email [name: 'idx_users_email'] company_id [name: 'idx_users_company_id'] okta_id [name: 'idx_users_okta_id'] (role, is_active) [name: 'idx_users_role_active'] } } Table user_invitations { Note: ''' One-time password-set invitation links (72hr expiry per BR-014). Once used, token is voided. ''' id int [pk, increment] user_id int [not null] token varchar(512) [not null, unique, note: 'Secure random token'] sent_at timestamp [not null, default: `now()`] expires_at timestamp [not null, note: '72 hours from sent_at'] used_at timestamp [null, note: 'Set when user completes password setup'] indexes { token [name: 'idx_user_invitations_token'] user_id [name: 'idx_user_invitations_user_id'] (expires_at, used_at) [name: 'idx_user_invitations_expiry'] } } // ───────────────────────────────────────────────────────────────────────────── // TABLE GROUP: Sites & Slots // ───────────────────────────────────────────────────────────────────────────── Table sites { Note: ''' Physical ACFS warehouse locations. State field required for Australian address compliance and GST. ''' id int [pk, increment] site_name varchar(255) [not null, unique] branch_code varchar(50) [note: 'Site branch identifier'] warehouse_code varchar(50) [null, note: 'Maximus WHCode (IntOrderHd.WarehouseCode). Future-proofing (B5) - branch_code is the live mapping key while there is one warehouse per state; WHCode stored for when a branch spans multiple warehouses'] address_line1 varchar(255) [not null] address_line2 varchar(255) suburb varchar(100) [not null] state au_state [not null] postcode char(4) [not null, note: 'Australian 4-digit postcode'] is_active boolean [not null, default: true] created_at timestamp [not null, default: `now()`] updated_at timestamp [not null, default: `now()`] indexes { (state, is_active) [name: 'idx_sites_state_active'] branch_code [name: 'idx_sites_branch_code'] } } Table slots { Note: ''' Configurable pickup time windows (BR-005). Relative cutoffs: "previous_working_day" + "16:00" style rules. No hard capacity limits - heat_map_threshold is visual guidance only. FIXED v1.1: Added version column for optimistic locking ''' id int [pk, increment] site_id int [not null] slot_name varchar(255) [not null, note: 'Human-readable name'] slot_area varchar(100) [note: 'Physical area (e.g., Gate 1, Zone A)'] day_of_week slot_day_of_week [not null] start_time time [not null] end_time time [not null] // Structured cutoffs per BR-005 booking_cutoff_relative_day varchar(50) [not null, note: 'previous_working_day | same_day | two_days_prior'] booking_cutoff_time time [not null, note: 'e.g., 16:00'] change_cutoff_relative_day varchar(50) [not null] change_cutoff_time time [not null] // Optional density indicator heat_map_threshold int [note: 'Visual density indicator - does NOT block bookings'] // Administrative is_blocked boolean [not null, default: false, note: 'Holiday or blackout date'] is_active boolean [not null, default: true] // FIXED v1.1: Optimistic locking version int [not null, default: 1, note: 'Optimistic locking - increment on update'] created_at timestamp [not null, default: `now()`] updated_at timestamp [not null, default: `now()`] indexes { site_id [name: 'idx_slots_site_id'] (day_of_week, start_time) [name: 'idx_slots_schedule'] is_active [name: 'idx_slots_is_active'] } } // ───────────────────────────────────────────────────────────────────────────── // TABLE GROUP: Shipments & Delivery Orders // ───────────────────────────────────────────────────────────────────────────── Table containers { Note: ''' Physical shipping containers received at ACFS. Maximus ERP is source of truth. ''' id int [pk, increment] container_number varchar(50) [not null, unique, note: 'Standard container ID (e.g., MSCU4729183)'] vessel_id varchar(50) [null, note: 'Maximus VesselID. With voyage_no, disambiguates a container_number reused across voyages (the EDI→order join is ContainerNo + VesselID + VoyageNo)'] voyage_no varchar(50) [null, note: 'Maximus VoyageNo. Pairs with vessel_id to disambiguate a reused container_number'] ocean_bl varchar(100) [note: 'Ocean bill of lading - multiple containers may share'] site_id int [not null, note: 'Which ACFS site received this'] customer_company_id int [null, note: 'L1 wholesale-FF customer for this container (IntOrderHd.CustomerCode → AdmCustomer). Known at order-header time, before the HBL poll; the same L1 party is resolved onto hbls.assigned_company_id'] status container_status [not null, default: 'received'] received_at timestamp [not null] unpacked_at timestamp [null, note: 'Set when status → unpacked'] maximus_reference varchar(255) [null, note: 'Wholesale FF order ID in Maximus (IntOrderHd.OrderNo)'] container_key varchar(255) [null, note: 'Maximus container key (OrderNo-NNN, ACFS_Container_Location_CMT). The join key for milestones (EDICommon events) and container holds (IntContainerHold). Distinct from maximus_reference/OrderNo'] maximus_created_date timestamp [null, note: 'Source-side create timestamp from Maximus (audit column TBD - OQ-063/064/065). Distinct from created_at (portal ingest time); supports sync/CDC freshness checks'] container_hold_status varchar(50) [null, note: 'ADDED 2026-08-20 (prod ERD Containers.ContainerHoldStatus -> AQISHoldStatus). Container-level hold summary, sourced from IntContainerHold. Distinct from hbls.cargo_hold_status, which is cargo/HBL-level - a container hold blocks everything inside it, a cargo hold blocks one HBL'] created_at timestamp [not null, default: `now()`] updated_at timestamp [not null, default: `now()`] indexes { container_number [name: 'idx_containers_number'] (vessel_id, voyage_no) [name: 'idx_containers_vessel_voyage'] ocean_bl [name: 'idx_containers_ocean_bl'] container_key [name: 'idx_containers_container_key'] customer_company_id [name: 'idx_containers_customer_company'] (site_id, status) [name: 'idx_containers_site_status'] } } Table hbls { Note: ''' House Bill of Lading - core shipment entity. **FIXED v1.1 Changes:** - REMOVED: chargeable_weight (compute on demand, snapshot in booking_hbls) - REMOVED: storage_fee_applicable (always compute from last_free_storage_date) - CHANGED: do_waived is now GENERATED COLUMN ((free_release AND free_release_verified) OR (under_bond AND under_bond_verified)) - ADDED: version for optimistic locking - ADDED: last_edited_by_user_id for audit per acfs:r12 **Two Orthogonal Dimensions (BR-027):** 1. milestone: Physical progress (on_vessel → collected) 2. hbl_status: Business state (unassigned → assigned → delegated → booked) **Booking Readiness (BR-001):** - milestone IN ('unpacked', 'collected') - customs_status = 'fully_cleared' - do_waived = true OR all DOs validated ''' id int [pk, increment] container_id int [not null] // Primary identifiers hbl_number varchar(100) [not null, unique, note: 'Lowest-level house bill from Maximus'] alt_hbl_reference varchar(100) [null, note: 'Upper/parent HBL (EdiRequestedCargo FFO row) - hierarchy is portal-built by Qty/Wt/Vol matching of FFO vs blank rows (probabilistic + manual exceptions), not delivered clean by a feed (OQ-034)'] import_ref varchar(100) [null, note: 'Import order reference'] // Physical attributes description text [note: 'Goods description'] weight_kg decimal(10,3) [note: 'Actual (gross) weight in kilograms - EdiRequestedCargo.GrossWeight'] net_weight_kg decimal(10,3) [note: 'Net weight - EdiRequestedCargo.NetWeight. Gross is weight_kg; net is one of the four fields the 1 Jul hierarchy-matching algorithm matches on (Gross+Net+Vol+Qty)'] volume_m3 decimal(10,3) [note: 'Volume in cubic meters'] quantity int [note: 'Number of packages'] pack_type varchar(100) [note: 'Package type'] // Dangerous goods + pallets (OUT OF INITIAL RELEASE - 1 Jul; columns provisioned for the fast follow) un_code varchar(20) [null, note: 'UN dangerous-goods code (IntOrderItem.UNCode). Out of initial release'] dangerous_goods boolean [not null, default: false, note: 'Derived DG flag (IntContainer.CargoType + IntOrderItem.IMOCode/UNCode). Out of initial release'] pallet_quantity int [null, note: 'CHEP/pallet count (IntOrderItem.Quantity2). Out of initial release'] pallet_type varchar(50) [null, note: 'Pallet UOM/type (IntOrderItem.UOM2). Out of initial release'] // REMOVED: chargeable_weight - compute as max(weight_kg, volume_m3) on demand // REMOVED: storage_fee_applicable - compute as (current_date > last_free_storage_date) // Two orthogonal dimensions (BR-027) milestone milestone_status [not null, default: 'on_vessel', note: 'Physical progress - DERIVED (not stored): EDICommon event/movement codes keyed by container key (On Wharf=HL001, In Yard=HL003) + ActArrivalDate (On Water default) + tally records (Unpacked/Collected). See v1.3 changelog'] hbl_status hbl_status [not null, default: 'unassigned', note: 'Business state - managed within VBS'] // Customs and clearance customs_status customs_status [not null, default: 'pending', note: 'Must be fully_cleared before booking (BR-001)'] customs_clearance_date date [null] // Under-bond (BR-002) - portal-side flag, NOT synced from Maximus (C5) under_bond boolean [not null, default: false, note: 'Manual flag set by LSP/ACFS (Maximus carries no bond signal, C5) - goods moving between bonded facilities before customs clearance; movement permission replaces the DO requirement'] under_bond_verified boolean [not null, default: false, note: 'ACFS verification of the under-bond marking. Verification happens outside the portal (an ACFS checklist); the portal only records the result. ACFS staff only (OQ-025)'] // v1.2 (BR-040): HBL hierarchy level - the single switch selecting the release path ffo_flag boolean [not null, default: false, note: 'FFO flag from EDI Requested Cargo - true => intermediate HBL, false => lowest-level (BR-040)'] hbl_level_indicator hbl_level [not null, default: 'lowest_level', note: 'Derived from ffo_flag - lowest_level (DO path) vs intermediate (digital-release path) (BR-040)'] // v1.2 (BR-040/BR-041): derived per-HBL release-authority rollup for the HBL list release_status hbl_release_status [not null, default: 'pending', note: 'Derived rollup: digital_release_provided | do_validated | pending (FR-LSP-02)'] // Release and storage // NOTE (BR-040): release_type / do_waived apply ONLY to lowest-level HBLs; intermediate HBLs use the digital-release path release_type release_type [not null, default: 'do_required', note: 'DO path (lowest-level HBLs only): do_required or free_release. free_release set by ACFS only (BR-002) - LSPs request offline'] free_release_verified boolean [not null, default: false, note: 'ACFS verification of the free_release marking. Mirrors under_bond_verified - ACFS sets it after offline verification; ACFS staff only'] // FIXED v1.1: do_waived is now GENERATED COLUMN - always consistent // v1.4: gated on verification to match the intent model - the raw flag alone does NOT waive the DO (BR-002) // PostgreSQL: GENERATED ALWAYS AS ((release_type = 'free_release' AND free_release_verified) OR (under_bond AND under_bond_verified)) STORED // MySQL: GENERATED ALWAYS AS ((release_type = 'free_release' AND free_release_verified = 1) OR (under_bond = 1 AND under_bond_verified = 1)) STORED do_waived boolean [note: 'GENERATED: true when (release_type=free_release AND free_release_verified) OR (under_bond AND under_bond_verified)'] last_free_storage_date date [null, note: 'After this date, storage fees apply'] // storage_fee_applicable computed as: (CURRENT_DATE > last_free_storage_date) // Critical for dispatch planning (BR-031) pickup_site_id int [not null, note: 'Which warehouse - MUST be visible and filterable'] // Consignee (OQ-034 / B1 / C7) consignee_name varchar(255) [note: 'Free-text from customs (EdiRequestedCargo.ConsigneeName) - high cardinality, never used raw'] consignee_account_code varchar(100) [note: 'Resolved by the Mule/OutSystems backend: maps the free-text consignee name to an account code via ACFS-provided alias mappings; the matching rule itself is still TBD (24 Jun)'] // Assignment assigned_company_id int [null, note: 'LSP allocated by ACFS or auto-assignment'] // Integration references maximus_hbl_id varchar(255) [null, note: 'Source record ID in Maximus for bidirectional sync'] cargo_key varchar(255) [null, note: 'Maximus CargoKey - the per-HBL spine (unique per HBL; IntOrderItem/CfsCargo). THE key for tally-out (collection) and cargo-hold joins - never identify an HBL by container_key'] collected_by_code varchar(100) [null, note: 'Collecting carrier code (cfsTallyOutHed.ContractorCode), populated at Tally Out / the collected milestone'] maximus_updated_at timestamp [null, note: 'Source-side last-updated timestamp from Maximus (audit column TBD - OQ-063/064/065). Distinct from updated_at (portal); supports sync/CDC change detection'] original_msg_ref varchar(255) [null, note: 'ADDED 2026-08-20 (prod ERD HBLS.OriginalMsgRef). EdiRequestedCargo message ref. De-dup key: where a container has several cargo records the HIGHEST OriginalMsgRef wins, selected in MuleSoft before the portal sees it'] // ADDED 2026-08-20: hierarchy matching + ingestion exceptions (prod ERD VBS_ShipmentERD) // Written by the MuleSoft->OutSystems staging pass, not user-editable. See BR-034. parent_hbl_id int [null, note: 'Resolved parent (upper-level) HBL - prod ERD HBLS.ParentHBLId. NB ONE parent per child: a competing candidate match cannot be persisted. The architect guide (LCBP 2685468677) recommended a separate hbl_relationships table for exactly this reason and the build did not adopt it - OQ-081'] parent_company_id int [null, note: 'prod ERD HBLS.ParentCompanyId. SEMANTICS UNCONFIRMED - this field appears nowhere in the architect guide (LCBP 2685468677 v32); the build added it undocumented. "Issuer of the parent HBL" is the obvious reading but is an inference; "resolved consignee of the parent row" is an equally possible reading that means something different for visibility. Confirm with Jewel before building a per-party reference view on it - OQ-073'] relationship_type varchar(50) [null, note: 'Hierarchy-matching outcome (prod HBLS.RelationshipTypeId -> HBLRelationshipType). Includes NO_PARENT: a lowest-level HBL with no valid parent is VALID, not an error'] match_level varchar(50) [null, note: 'Confidence band from hierarchy matching (prod HBLS.MatchLevelId -> HBLMatchLevel). 100% = all 4 of qty/gross/net/volume match; 95% = 3 match + 1 missing; 90% = 3 match + 1 small delta; 50% = weight or volume only; not-scored = no match. Bottom two force needs_manual_review'] needs_manual_review boolean [not null, default: false, note: 'Set by ingestion on weak match (50%), no match, consignee mapping failure, or L1 service-provider mapping failure. Record is RETAINED and routed to the VBS Admin queue - the data end of the "Correct HBL Hierarchy" ACFS role'] is_manually_updated boolean [not null, default: false, note: 'An ACFS admin has hand-corrected this HBL after manual review (prod HBLS.IsManuallyUpdated). Sync must not silently overwrite a human correction - relates to OQ-060'] exception_id int [null, note: 'Recorded ingestion exception (prod HBLS.ExceptionId -> ExceptionType). Kinds seen in the integration design: SiteMappingFailed, AccountMappingFailed, MissingCoreKey, DatasetMissing'] cargo_hold_status varchar(50) [null, note: 'Cargo-level hold summary carried on the HBL (prod HBLS.CargoHoldStatus -> AQISHoldStatus). customs_status is DERIVED from the underlying hold rows; per-hold history (code, description, hold date, release date, active flag) lives in the integration layer'] // FIXED v1.1: Audit fields last_edited_by_user_id int [null, note: 'ACFS user who last edited HBL details (acfs:r12)'] version int [not null, default: 1, note: 'Optimistic locking'] created_at timestamp [not null, default: `now()`] updated_at timestamp [not null, default: `now()`] indexes { hbl_number [name: 'idx_hbls_number'] alt_hbl_reference [name: 'idx_hbls_alt_ref'] container_id [name: 'idx_hbls_container_id'] assigned_company_id [name: 'idx_hbls_assigned_company'] pickup_site_id [name: 'idx_hbls_pickup_site'] (milestone, hbl_status) [name: 'idx_hbls_dual_dimension'] consignee_account_code [name: 'idx_hbls_consignee'] maximus_hbl_id [name: 'idx_hbls_maximus_id'] cargo_key [name: 'idx_hbls_cargo_key'] } } Table hbl_custody_chain { Note: ''' Each delegation hop in chain of custody. ACFS sees full chain; LSPs see hop-by-hop per C-009. ''' id int [pk, increment] hbl_id int [not null] from_company_id int [null, note: 'NULL when ACFS initiates first hop'] to_company_id int [not null, note: 'Company receiving custody'] hop_sequence int [not null, note: 'Ordinal: 1 = first hop from ACFS'] delegation_method delegation_method [note: 'existing_lsp or one_off_p4tc'] assigned_at timestamp [not null, default: `now()`] created_at timestamp [not null, default: `now()`] indexes { hbl_id [name: 'idx_custody_hbl_id'] (hbl_id, hop_sequence) [unique, name: 'uq_custody_hbl_hop'] from_company_id [name: 'idx_custody_from_company'] to_company_id [name: 'idx_custody_to_company'] } } Table delegations { Note: ''' First-class delegation entity for tracking and management. Allows ACFS to revoke delegations per acfs:r13. Multiple HBLs via delegation_hbls junction. ''' id int [pk, increment] delegator_company_id int [not null, note: 'Company initiating delegation'] delegatee_company_id int [null, note: 'NULL for P4TC delegations'] delegatee_email varchar(255) [null, note: 'For P4TC - receives magic link'] delegation_method delegation_method [not null] status delegation_status [not null, default: 'active'] created_at timestamp [not null, default: `now()`] revoked_at timestamp [null] revoked_by_user_id int [null, note: 'ACFS user who revoked'] indexes { delegator_company_id [name: 'idx_delegations_delegator'] delegatee_company_id [name: 'idx_delegations_delegatee'] status [name: 'idx_delegations_status'] } } Table delegation_hbls { Note: ''' Junction table linking delegations to HBLs. FIXED v1.1: Protected by BR-004 trigger - cannot delegate booked HBLs ''' delegation_id int [not null] hbl_id int [not null] indexes { (delegation_id, hbl_id) [pk, name: 'pk_delegation_hbls'] hbl_id [name: 'idx_delegation_hbls_hbl_id'] } } Table delivery_orders { Note: ''' Delivery Order - release authority document (one per delegation tier per BR-002). DO validation is HBL-centric per BR-018. FIXED v1.1: Added uploaded_by_user_id for audit trail ''' id int [pk, increment] hbl_id int [not null] custody_chain_id int [null, note: 'Specific custody hop that triggered this DO'] issued_by_company_id int [not null, note: 'Company issuing DO'] issued_to_company_id int [not null, note: 'For final hop, this is end customer'] do_number varchar(100) [null, note: 'DO reference number if present'] document_url text [null, note: 'URL to uploaded document in object storage'] tier_level int [note: 'Which level in HBL hierarchy'] validation_status do_validation_status [not null, default: 'not_provided'] // FIXED v1.1: Added upload tracking uploaded_by_user_id int [null, note: 'User who uploaded DO'] validated_by_user_id int [null, note: 'ACFS user who validated'] validated_at timestamp [null] invalidation_reason text [null, note: 'When status=flagged - triggers email to booking party'] created_at timestamp [not null, default: `now()`] updated_at timestamp [not null, default: `now()`] indexes { hbl_id [name: 'idx_dos_hbl_id'] (hbl_id, tier_level) [name: 'idx_dos_hbl_tier'] custody_chain_id [name: 'idx_dos_custody_chain'] validation_status [name: 'idx_dos_validation_status'] issued_to_company_id [name: 'idx_dos_issued_to'] } } Table release_authorities { Note: ''' v1.2: NEW TABLE (BR-041, FR-LSP-25) Authoritative LOG of release-authority acts (who/when/which form) provided by an LSP against an HBL. hbls.release_status is the DERIVED rollup over these acts. Two forms by HBL level (hbls.hbl_level_indicator, BR-040): - digital_release for intermediate HBLs (flag, no document - BR-041) - do_based for lowest-level HBLs (backed by a validated DO or verified waiver) ''' id int [pk, increment] hbl_id int [not null] release_mechanism release_mechanism [not null, note: 'digital_release (intermediate) or do_based (lowest-level)'] acting_lsp_id int [not null, note: 'LSP/NVOCC (e.g. AGS) that provided the release - references companies'] release_timestamp timestamp [not null, default: `now()`, note: 'When the release was provided'] status release_authority_status [not null, default: 'provided', note: 'provided | superseded'] created_at timestamp [not null, default: `now()`] indexes { hbl_id [name: 'idx_release_authorities_hbl_id'] (hbl_id, status) [name: 'idx_release_authorities_hbl_status'] acting_lsp_id [name: 'idx_release_authorities_acting_lsp'] } } // ───────────────────────────────────────────────────────────────────────────── // TABLE GROUP: Bookings & Payments // ───────────────────────────────────────────────────────────────────────────── Table trucks { Note: ''' v1.2: NEW TABLE (BR-039, FR-LSP-30) A vehicle assigned to collect one or more HBLs within a single booking. A booking may contain multiple trucks, each with its own driver details and its own assigned HBL list (via booking_hbls.truck_id). Driver details are entered fresh per truck per booking for Phase 1 (saved drivers deferred - A-025). CoR compliance fields live here now (was on bookings). **DIVERGENCE 2026-08-20:** production Truck has NO booking_id. It is standalone (Id, UUID, TruckRego, DriverName, DriverLicense, SiteInductionFlag, TCAcceptanceTime plus audit columns), reachable from a booking only through booking_hbls.truck_id. That is the shape needed for REUSABLE trucks and drivers, which our model defers to Phase 2 (A-025) and which Matt lists under "Feature to Add Later: Store Truck Drivers & Trucks info". So either the build has laid the groundwork for saved drivers, or trucks are created per booking anyway and the FK was simply omitted. The difference matters: with no booking_id, nothing prevents 2 bookings sharing a truck row, and "driver details entered fresh each booking" stops being enforceable. See OQ-084. ''' id int [pk, increment] booking_id int [not null, note: 'NOT present in production - see divergence note above'] // Driver and vehicle (CoR compliance) - moved from bookings v1.2 truck_rego varchar(20) [not null, note: 'Truck Registration - CoR compliance'] driver_name varchar(255) [not null] driver_license varchar(50) [not null, note: 'Driver licence number - verified by warehouse staff'] // Per-truck site induction (BR-039) site_induction_flag boolean [not null, default: false, note: 'Whether the driver has completed site induction (Yes/No)'] tc_acceptance_timestamp timestamp [null, note: 'When the driver site induction T&C was accepted for this truck'] created_at timestamp [not null, default: `now()`] updated_at timestamp [not null, default: `now()`] indexes { booking_id [name: 'idx_trucks_booking_id'] truck_rego [name: 'idx_trucks_truck_rego'] driver_license [name: 'idx_trucks_driver_license'] } } Table bookings { Note: ''' Pickup booking for one or more HBLs at specific slot. **FIXED v1.1 Changes:** - ADDED: cutoff_override_by_user_id, cutoff_override_reason, cutoff_override_at for BR-015 - ADDED: version for optimistic locking - ADDED: Check constraints on GST calculations - ADDED: Check constraints on fee non-negativity **v1.2 (BR-039):** Multi-truck booking - driver/vehicle fields moved to trucks; added truck_count. Each truck has its own driver details and assigned HBL list. **Lifecycle (BRD V2.0 §6.1):** draft → open → processed → complete → cancelled **BR-028:** 'complete' status is DERIVED when all HBLs reach the collected milestone (Automated via background job, not manual) ''' id int [pk, increment] booking_reference varchar(50) [not null, unique, note: 'System-generated reference'] // Slot details // DIVERGENCE 2026-08-20: production does NOT hold slot on Bookings. The booking ERD // puts slot_id + slot_date on BookingHBLS instead, i.e. per booked HBL rather than per // booking. That structurally permits different slots inside one booking, which BR-005 // and the booking entity ("one slot applies to the entire booking") do not. Kept here // and mirrored onto booking_hbls below until resolved - see OQ-083. slot_id int [not null] slot_date date [not null, note: 'Calendar date of pickup. NOT present on Bookings in production - see divergence note above'] // Booking party booked_by_company_id int [not null] booked_by_user_id int [null, note: 'NULL for P4TC bookings (deferred)'] booked_by_email varchar(255) [null, note: 'For P4TC - receives confirmations'] // Status status booking_status [not null, default: 'draft'] // v1.2 (BR-039): multi-truck booking - driver/vehicle details moved to trucks table truck_count int [not null, default: 1, note: 'Number of trucks assigned to this booking (BRD V2.0 §6.1)'] // moved to trucks (v0.14.0) — driver/vehicle details are now per-truck (BR-039, FR-LSP-30): // driver_name varchar(255) [not null] // driver_licence_number varchar(50) [not null, note: 'Verified by warehouse staff'] // driver_phone varchar(20) [note: 'Optional contact'] // truck_rego varchar(20) [not null, note: 'Vehicle registration - CoR compliance'] // truck_details text [note: 'Additional vehicle description'] // T&Cs (BR-017) // NOTE (BR-039): driver site induction is now acknowledged PER TRUCK (see trucks.site_induction_flag); booking T&Cs accepted once per booking terms_accepted_at timestamp [null, note: 'When booking T&Cs accepted (per-truck site induction lives on trucks)'] // Fees (GST compliance per A New Tax System (GST) Act 1999) // FIXED v1.1: Protected by check constraints total_fee_excl_gst decimal(10,2) [not null, note: 'Total before GST'] gst_amount decimal(10,2) [not null, note: '10% GST - CONSTRAINT: ABS(gst_amount - total_fee_excl_gst*0.10) < 0.01'] total_fee_incl_gst decimal(10,2) [not null, note: 'Final amount - CONSTRAINT: ABS(total_fee_incl_gst - (total_fee_excl_gst+gst_amount)) < 0.01'] // Modifications (BR-015) is_late_change boolean [not null, default: false, note: 'Set when change made after cutoff'] late_change_fee decimal(10,2) [not null, default: 0, note: 'Additional fee for late modifications'] last_modified_at timestamp [null] last_modified_by_user_id int [null] // FIXED v1.1: Cutoff override tracking for BR-015 cutoff_override_by_user_id int [null, note: 'ACFS admin who authorized override'] cutoff_override_reason text [null, note: 'Required reason when admin overrides cutoff'] cutoff_override_at timestamp [null, note: 'When override was granted'] // Administrative notes text [null, note: 'Internal ACFS notes'] cancelled_at timestamp [null] cancelled_by_user_id int [null] cancellation_reason text [null, note: 'Required when ACFS cancels (BR-022)'] processed_at timestamp [null, note: 'When ACFS warehouse finalised'] processed_by_user_id int [null] // FIXED v1.1: Optimistic locking version int [not null, default: 1, note: 'Increment on update'] created_at timestamp [not null, default: `now()`] updated_at timestamp [not null, default: `now()`] indexes { booking_reference [name: 'idx_bookings_reference'] (slot_id, slot_date) [name: 'idx_bookings_slot'] booked_by_company_id [name: 'idx_bookings_company_id'] status [name: 'idx_bookings_status'] // v1.2: idx_bookings_truck_rego / idx_bookings_driver_licence moved to trucks (BR-039) } } Table booking_hbls { Note: ''' Junction linking bookings to HBLs. **Per-HBL Fee Breakdown (BR-019):** Stores snapshot of chargeable_weight, rate, fee at booking time. FIXED v1.1: - Protected by BR-004 trigger - cannot book delegated HBLs - Check constraint on fee calculation: per_hbl_fee = chargeable_weight × rate ''' id int [pk, increment] booking_id int [not null] hbl_id int [not null] // v1.2 (BR-039): which truck within the booking collects this HBL truck_id int [not null, note: 'Truck assigned to collect this HBL (BRD V2.0 §6.1). NOTE production Truck has NO booking_id, so this junction is the ONLY link between a truck and a booking - see the trucks table note'] // ADDED 2026-08-20 to match the production booking ERD (BookingHBLS.slot_id, .slot_date). // Production holds the slot HERE, per booked HBL, not on the booking. See OQ-083. slot_id int [null, note: 'prod BookingHBLS.slot_id. Per booked HBL in production. If one slot per booking is the intended rule, this must be enforced in application logic because the schema does not enforce it'] slot_date date [null, note: 'prod BookingHBLS.slot_date. Calendar date of pickup for this HBL'] // Fee calculation snapshot at booking time chargeable_weight decimal(10,3) [not null, note: 'max(weight_kg, volume_m3) at booking time'] rate decimal(10,4) [not null, note: 'Rate from pricing_zones at booking time'] per_hbl_fee decimal(10,2) [not null, note: 'CONSTRAINT: ABS(per_hbl_fee - chargeable_weight*rate) < 0.01'] created_at timestamp [not null, default: `now()`] indexes { booking_id [name: 'idx_booking_hbls_booking_id'] hbl_id [name: 'idx_booking_hbls_hbl_id'] truck_id [name: 'idx_booking_hbls_truck_id'] (booking_id, hbl_id) [unique, name: 'uq_booking_hbl'] } } Table payments { Note: ''' Payment record for each booking. FIXED v1.1: payment_gateway now enum (stripe | compay) FIXED v1.1: Check constraint on GST calculation ''' id int [pk, increment] booking_id int [not null] amount_excl_gst decimal(10,2) [not null] gst_amount decimal(10,2) [not null, note: '10% GST - CONSTRAINT: ABS(gst_amount - amount_excl_gst*0.10) < 0.01'] total_amount decimal(10,2) [not null, note: 'CONSTRAINT: ABS(total_amount - (amount_excl_gst+gst_amount)) < 0.01'] payment_gateway payment_gateway [note: 'FIXED v1.1: Now enum instead of varchar'] payment_gateway_ref varchar(255) [note: 'Transaction reference from gateway'] transaction_reference varchar(255) [note: 'Internal transaction reference'] status payment_status [not null, default: 'pending'] payment_timestamp timestamp [null, note: 'When payment completed'] processed_at timestamp [null] created_at timestamp [not null, default: `now()`] indexes { booking_id [name: 'idx_payments_booking_id'] payment_gateway_ref [name: 'idx_payments_gateway_ref'] status [name: 'idx_payments_status'] } } // ───────────────────────────────────────────────────────────────────────────── // TABLE GROUP: Pricing // ───────────────────────────────────────────────────────────────────────────── Table pricing_zones { Note: ''' Rate zone configuration per ACFS site. FIXED v1.1: Check constraint on gst_rate (0 <= gst_rate <= 1) **Fee Formula:** per_hbl_fee = chargeable_weight_kg × rate_per_kg total_booking_fee = MAX(SUM(per_hbl_fee), minimum_charge) final_amount = total_booking_fee × (1 + gst_rate) ''' id int [pk, increment] site_id int [not null] zone_name varchar(100) [note: 'Zone identifier'] rate_per_kg decimal(10,4) [not null, note: 'Rate per chargeable kilogram'] minimum_charge decimal(10,2) [not null, note: 'Minimum booking fee'] gst_rate decimal(5,4) [not null, default: 0.1000, note: 'CONSTRAINT: 0 <= gst_rate <= 1'] effective_from date [not null] effective_to date [null, note: 'NULL = currently active'] is_active boolean [not null, default: true] created_at timestamp [not null, default: `now()`] updated_at timestamp [not null, default: `now()`] indexes { site_id [name: 'idx_pricing_site_id'] (site_id, effective_from, effective_to) [name: 'idx_pricing_temporal'] is_active [name: 'idx_pricing_is_active'] } } // ───────────────────────────────────────────────────────────────────────────── // TABLE GROUP: Driver Records (BR-016) // ───────────────────────────────────────────────────────────────────────────── Table driver_records { Note: ''' Saved driver details scoped per LSP company (BR-016). FIXED v1.1: Added archived_at for soft-delete consistency ''' id int [pk, increment] company_id int [not null, note: 'LSP company - drivers NOT shared across companies'] driver_name varchar(255) [not null] driver_licence_number varchar(50) [not null, note: 'Unique per company'] driver_phone varchar(20) [note: 'Optional contact'] default_truck_rego varchar(20) [note: 'Default vehicle, can be overridden'] site_induction_completed boolean [not null, default: false, note: 'ACFS site induction completed'] site_induction_date date [null, note: 'When induction completed'] is_active boolean [not null, default: true] archived_at timestamp [null, note: 'FIXED v1.1: Soft delete for consistency'] created_at timestamp [not null, default: `now()`] updated_at timestamp [not null, default: `now()`] indexes { company_id [name: 'idx_driver_records_company_id'] (company_id, driver_licence_number) [unique, name: 'uq_driver_company_licence'] is_active [name: 'idx_driver_records_is_active'] } } // ───────────────────────────────────────────────────────────────────────────── // TABLE GROUP: Notifications // ───────────────────────────────────────────────────────────────────────────── Table email_notifications { Note: ''' Audit log of system-triggered email notifications. FIXED v1.1: related_entity_type now enum for type safety FIXED v1.1: Added retry_count, next_retry_at, max_retries for email retry logic ''' id int [pk, increment] recipient_email varchar(255) [not null] recipient_user_id int [null, note: 'NULL for P4TC or unregistered parties'] recipient_company_id int [null, note: 'Company if known'] notification_type notification_type [not null] related_entity_type entity_type [null, note: 'FIXED v1.1: Now enum (booking|hbl|delegation|user|slot)'] related_entity_id int [null, note: 'ID of related entity'] subject varchar(500) [note: 'Email subject'] body text [note: 'Email body'] secure_link_token varchar(512) [null, note: 'For P4TC magic links (deferred)'] otp_code varchar(10) [null, note: 'For P4TC OTP (deferred)'] sent_at timestamp [null, note: 'When successfully sent'] status notification_status [not null, default: 'pending'] error_message text [null, note: 'If status=failed'] // FIXED v1.1: Email retry mechanism retry_count int [not null, default: 0, note: 'Number of send attempts'] next_retry_at timestamp [null, note: 'When to retry if failed'] max_retries int [not null, default: 3, note: 'Maximum retry attempts'] created_at timestamp [not null, default: `now()`] indexes { recipient_email [name: 'idx_notifications_recipient_email'] recipient_user_id [name: 'idx_notifications_recipient_user_id'] (notification_type, status) [name: 'idx_notifications_type_status'] (related_entity_type, related_entity_id) [name: 'idx_notifications_entity'] next_retry_at [name: 'idx_notifications_retry'] } } // ───────────────────────────────────────────────────────────────────────────── // TABLE GROUP: Audit Trail (NEW in v1.1) // ───────────────────────────────────────────────────────────────────────────── Table entity_audit_log { Note: ''' FIXED v1.1: NEW TABLE Comprehensive audit trail for critical entities. Tracks who changed what and when for compliance and debugging. ''' id int [pk, increment] entity_type entity_type [not null, note: 'booking | hbl | slot | user | delegation | delivery_order'] entity_id int [not null, note: 'ID of the entity that changed'] action audit_action [not null, note: 'created | updated | deleted'] changed_fields json [null, note: 'Before/after values for updated fields'] changed_by_user_id int [not null, note: 'User who made the change'] changed_at timestamp [not null, default: `now()`] indexes { (entity_type, entity_id) [name: 'idx_audit_entity'] changed_by_user_id [name: 'idx_audit_user'] changed_at [name: 'idx_audit_timestamp'] (entity_type, entity_id, changed_at) [name: 'idx_audit_entity_timeline'] } } // ============================================================================= // RELATIONSHIPS // ============================================================================= // Companies & Users Ref: users.company_id > companies.id Ref: user_invitations.user_id > users.id // Sites & Slots Ref: slots.site_id > sites.id // Containers & HBLs Ref: containers.site_id > sites.id Ref: containers.customer_company_id > companies.id Ref: hbls.container_id > containers.id Ref: hbls.assigned_company_id > companies.id Ref: hbls.pickup_site_id > sites.id Ref: hbls.last_edited_by_user_id > users.id // Custody Chain & Delegations Ref: hbl_custody_chain.hbl_id > hbls.id Ref: hbl_custody_chain.from_company_id > companies.id Ref: hbl_custody_chain.to_company_id > companies.id Ref: delegations.delegator_company_id > companies.id Ref: delegations.delegatee_company_id > companies.id Ref: delegations.revoked_by_user_id > users.id Ref: delegation_hbls.delegation_id > delegations.id Ref: delegation_hbls.hbl_id > hbls.id // Delivery Orders Ref: delivery_orders.hbl_id > hbls.id Ref: delivery_orders.custody_chain_id > hbl_custody_chain.id Ref: delivery_orders.issued_by_company_id > companies.id Ref: delivery_orders.issued_to_company_id > companies.id Ref: delivery_orders.uploaded_by_user_id > users.id Ref: delivery_orders.validated_by_user_id > users.id // Release Authority (v1.2 - BR-041) Ref: release_authorities.hbl_id > hbls.id Ref: release_authorities.acting_lsp_id > companies.id // Trucks (v1.2 - BR-039) Ref: trucks.booking_id > bookings.id // Bookings Ref: bookings.slot_id > slots.id Ref: bookings.booked_by_company_id > companies.id Ref: bookings.booked_by_user_id > users.id Ref: bookings.last_modified_by_user_id > users.id Ref: bookings.cancelled_by_user_id > users.id Ref: bookings.processed_by_user_id > users.id Ref: bookings.cutoff_override_by_user_id > users.id // Booking HBLs Junction Ref: booking_hbls.booking_id > bookings.id Ref: booking_hbls.hbl_id > hbls.id Ref: booking_hbls.truck_id > trucks.id // Payments Ref: payments.booking_id > bookings.id // Pricing Ref: pricing_zones.site_id > sites.id // Driver Records Ref: driver_records.company_id > companies.id // Notifications Ref: email_notifications.recipient_user_id > users.id Ref: email_notifications.recipient_company_id > companies.id // Audit Trail Ref: entity_audit_log.changed_by_user_id > users.id // ============================================================================= // SCHEMA CONSTRAINTS (To be implemented in SQL) // ============================================================================= Note schema_constraints: ''' # Critical Constraints (Not expressible in DBML - implement in SQL migration) ## 1. GST Calculation Constraints ```sql -- Bookings ALTER TABLE bookings ADD CONSTRAINT chk_bookings_gst_calculation CHECK ( ABS(gst_amount - (total_fee_excl_gst * 0.10)) < 0.01 AND ABS(total_fee_incl_gst - (total_fee_excl_gst + gst_amount)) < 0.01 ); ALTER TABLE bookings ADD CONSTRAINT chk_bookings_fees_non_negative CHECK ( total_fee_excl_gst >= 0 AND gst_amount >= 0 AND total_fee_incl_gst >= 0 AND late_change_fee >= 0 ); -- Payments ALTER TABLE payments ADD CONSTRAINT chk_payments_gst_calculation CHECK ( ABS(gst_amount - (amount_excl_gst * 0.10)) < 0.01 AND ABS(total_amount - (amount_excl_gst + gst_amount)) < 0.01 ); -- Booking HBLs ALTER TABLE booking_hbls ADD CONSTRAINT chk_booking_hbls_fee_calculation CHECK (ABS(per_hbl_fee - (chargeable_weight * rate)) < 0.01); ``` ## 2. Data Validation Constraints ```sql -- Slot times ALTER TABLE slots ADD CONSTRAINT chk_slots_time_order CHECK (end_time > start_time); -- Australian postcode ALTER TABLE sites ADD CONSTRAINT chk_sites_postcode_format CHECK (postcode ~ '^[0-9]{4}$'); -- ABN format ALTER TABLE companies ADD CONSTRAINT chk_companies_abn_format CHECK (abn IS NULL OR abn ~ '^[0-9]{11}$'); -- GST rate bounds ALTER TABLE pricing_zones ADD CONSTRAINT chk_pricing_gst_rate_valid CHECK (gst_rate >= 0 AND gst_rate <= 1); ``` ## 3. Generated Columns (PostgreSQL syntax) ```sql -- hbls.do_waived ALTER TABLE hbls ALTER COLUMN do_waived SET GENERATED ALWAYS AS ((release_type = 'free_release' AND free_release_verified) OR (under_bond AND under_bond_verified)) STORED; ``` ## 4. BR-004 Enforcement Trigger ```sql CREATE OR REPLACE FUNCTION check_hbl_booking_rules() RETURNS TRIGGER AS $$ BEGIN IF TG_TABLE_NAME = 'booking_hbls' THEN IF EXISTS (SELECT 1 FROM hbls WHERE id = NEW.hbl_id AND hbl_status = 'delegated') THEN RAISE EXCEPTION 'BR-004: Cannot book delegated HBL (hbl_id=%)', NEW.hbl_id; END IF; END IF; IF TG_TABLE_NAME = 'delegation_hbls' THEN IF EXISTS (SELECT 1 FROM hbls WHERE id = NEW.hbl_id AND hbl_status = 'booked') THEN RAISE EXCEPTION 'BR-004: Cannot delegate booked HBL (hbl_id=%)', NEW.hbl_id; END IF; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_booking_hbls_br004 BEFORE INSERT ON booking_hbls FOR EACH ROW EXECUTE FUNCTION check_hbl_booking_rules(); CREATE TRIGGER trg_delegation_hbls_br004 BEFORE INSERT ON delegation_hbls FOR EACH ROW EXECUTE FUNCTION check_hbl_booking_rules(); ``` ''' // ============================================================================= // VERSION HISTORY // ============================================================================= Note version_history: ''' # Schema Version History ## v1.2 (2026-06-16) - INTENT MODEL v0.14.0 (BRD V2.0 import) **Status:** PRODUCTION READY ### Multi-Truck Booking (BR-039, FR-LSP-30): 1. **New `trucks` table** - one row per truck per booking; carries truck_rego, driver_name, driver_license, site_induction_flag, tc_acceptance_timestamp. 2. **`bookings`** - driver_name/driver_licence_number/driver_phone/truck_rego/truck_details REMOVED (moved to trucks, commented out in place); ADDED truck_count. Indexes idx_bookings_truck_rego / idx_bookings_driver_licence moved to trucks. 3. **`booking_hbls`** - ADDED truck_id (which truck collects each HBL). ### Release Authority (BR-040, BR-041): 4. **New `release_authorities` table** - log of release acts (digital_release | do_based); release_status on hbls is the derived rollup. New enums release_mechanism, release_authority_status. 5. **`hbls`** - ADDED ffo_flag, hbl_level_indicator (hbl_level enum), release_status (hbl_release_status enum). last_free_storage_date already present (LFD, BR-042). ### Booking Status (BRD V2.0 §6.1): 6. **`booking_status` enum** re-valued: draft | open | processed | complete | cancelled. 'complete' is DERIVED when all HBLs reach the collected milestone (BR-028). HBL/milestone 'collected' value UNCHANGED. ### Site Mapping (BR-043): 7. **`sites.branch_code`** retained as the branch -> site mapping key (already present). ### Migration Notes: - Additive except: DROP driver/vehicle columns on bookings (data moves to trucks); booking_status enum re-value (booked/pending_processing/collected -> open/complete). --- ## v1.1 (2026-03-26) - CRITICAL FIXES **Status:** PRODUCTION READY ### Fixed Critical Issues: 1. **BR-004 Enforcement** - Added trigger to prevent booking delegated HBLs / delegating booked HBLs 2. **BR-001 Validation** - Application layer (see booking-readiness.ts) 3. **Derived Fields** - Removed chargeable_weight, storage_fee_applicable; do_waived now generated column 4. **GST Constraints** - Added check constraints on all GST calculations (±1¢ tolerance) 5. **BR-015 Audit** - Added cutoff_override_by_user_id, reason, timestamp to bookings 6. **BR-028 Automation** - Background job (see update-booking-collected-status.ts) ### Other Improvements: - Added entity_audit_log table for comprehensive audit trail - Added optimistic locking (version columns) to bookings, slots, hbls - Added email retry mechanism (retry_count, next_retry_at, max_retries) - Converted payment_gateway to enum (stripe | compay) - Converted related_entity_type to enum for type safety - Added soft-delete (archived_at) to companies, driver_records for consistency - Added uploaded_by_user_id to delivery_orders - Added last_edited_by_user_id to hbls (acfs:r12) ### Migration Notes: - Safe to run on empty database (no data migration needed) - All changes are additive except: - DROP COLUMN hbls.chargeable_weight - DROP COLUMN hbls.storage_fee_applicable - ALTER COLUMN hbls.do_waived (make generated) - ALTER COLUMN payments.payment_gateway (varchar → enum) - ALTER COLUMN email_notifications.related_entity_type (varchar → enum) --- ## v1.0 (2026-03-25) - Initial Production Schema - Merged Intent Model v0.8.0 + VBS_DBML operational enhancements - Implements all BR-001 through BR-033 - Australian GST and CoR compliance --- Generated: 2026-03-26 Last Updated: 2026-03-26 Status: PRODUCTION READY ✅ '''