Product Vision & Backend Goal
What the platform must solve
The system must remove fragmented cargo tracking, manual agent follow-ups, spreadsheet-based shipment updates, missing customs visibility, delayed customer communication, and weak profitability control. It should become the single source of truth for shipment movement from origin enquiry to final proof of delivery.
Core backend principle
Every shipment is treated as one operational entity connected to parties, agents, bookings, containers, customs, documents, delivery tasks, events, invoices, vendor bills, exceptions, and audit records. All modules publish and consume shipment events through a shared lifecycle engine.
Required user groups
Super AdminCompany AdminOperations TeamCustomer / ShipperConsigneeOrigin AgentDestination AgentCustoms BrokerCarrier / Shipping LineWarehouse / CFSDriver / CourierFinance TeamManagementEnd-to-End Cargo Visibility Lifecycle
The backend lifecycle should be event-driven so every milestone can update customer portal, agent portal, admin dashboard, notification engine, finance, SLA and BI dashboards automatically.
01 EnquiryCustomer request / rate
02 BookingCarrier / agent booking
03 PickupOrigin collection
04 ExportPort / airport / CFS
05 TransitAIS / carrier / container
06 CustomsDeclaration / duty / release
07 DeliveryDoor-to-door POD
Enterprise Backend Architecture
Actors
Customer, shipper, consignee, admin, sales, operations, freight team, customs broker, origin agent, destination agent, warehouse, driver, finance, management, external API partners.
Presentation Layer
Customer Portal, Admin Portal, Agent Portal, Customs Workspace, Driver App, Public Tracking Page, Management Dashboard.
API & Service Layer
API Gateway, Identity, Shipment, Tracking, Customs, Booking, DMS, Delivery, Finance, Notification, Workflow, Reporting and Integration services.
Data Layer
Relational DB for operational records, event store for tracking events, object storage for files, cache for live visibility, analytics warehouse for BI.
Event Layer
RabbitMQ/Kafka style message bus for ShipmentCreated, BookingConfirmed, VesselDeparted, CustomsReleased, OutForDelivery, Delivered, InvoicePosted and ExceptionRaised events.
External Systems
Carrier APIs, AIS providers, customs portals, port systems, payment gateway, map/GPS APIs, WhatsApp/SMS/email providers, accounting ERP and customer webhooks.
Required Modules & Database Tables
The following schema blueprint is intentionally enterprise-ready. Each table includes recommended key columns and the purpose of the table.
PlatformAdmin.API / TenantService
01. Platform, Tenant & Company Administration
Controls SaaS/enterprise instance, companies, branches, departments, subscription plans, tenant isolation and master settings.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| tenants | tenant_id PK, tenant_code, legal_name, trade_name, status, primary_country, base_currency, timezone | Root tenant for group or multi-company deployment. |
| companies | company_id PK, tenant_id FK, company_code, legal_name, tax_registration_no, license_no, address_id FK, status | Legal operating entity within tenant. |
| branches | branch_id PK, company_id FK, branch_code, branch_name, city_id FK, airport_code, port_code, is_head_office, status | Operational office, station, port branch or country office. |
| departments | department_id PK, company_id FK, name, department_type, cost_center_id FK, status | Operations, customs, finance, customer service, sales, warehouse, delivery. |
| subscription_plans | plan_id PK, plan_name, shipment_limit, user_limit, module_flags JSON, price, billing_cycle, status | Controls SaaS plan and enabled modules. |
| tenant_settings | setting_id PK, tenant_id FK, setting_key, setting_value, data_type, effective_from | Tenant-specific configuration. |
| numbering_sequences | sequence_id PK, tenant_id FK, entity_type, prefix, year_format, current_no, reset_policy | Generates shipment/job/tracking/declaration/invoice numbers. |
| feature_flags | flag_id PK, tenant_id FK, feature_key, is_enabled, rollout_percentage, notes | Controls phased release of features. |
| system_announcements | announcement_id PK, tenant_id FK, title, message, audience_role, start_at, end_at, status | Admin notices shown in portals. |
| audit_log_retention_policies | policy_id PK, tenant_id FK, module_name, retention_days, archive_strategy | Retention rules for audit and compliance records. |
Identity.API / AccessControlService
02. Identity, RBAC, Security & Access Control
Manages users, roles, permissions, login policies, agent/customer access, API keys, MFA, sessions and auditability.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| users | user_id PK, tenant_id FK, company_id FK, branch_id FK, full_name, email, phone, user_type, status, last_login_at | All platform users including staff, agents, customers and vendors. |
| user_profiles | profile_id PK, user_id FK, avatar_url, language, timezone, notification_preferences JSON | User personalization. |
| roles | role_id PK, tenant_id FK, role_name, role_scope, is_system_role, status | Admin, operations, customs, finance, agent, customer, driver. |
| permissions | permission_id PK, module_code, permission_key, action, description | Granular permissions such as shipment.view, customs.approve. |
| role_permissions | role_permission_id PK, role_id FK, permission_id FK, allowed, conditions JSON | Maps roles to permissions. |
| user_roles | user_role_id PK, user_id FK, role_id FK, branch_scope JSON, effective_from, effective_to | Assigns roles to users. |
| login_sessions | session_id PK, user_id FK, device_id, ip_address, user_agent, issued_at, expires_at, revoked_at | Tracks active sessions and revocation. |
| mfa_methods | mfa_id PK, user_id FK, method_type, secret_ref, is_verified, last_used_at | OTP, authenticator app, email, SMS. |
| api_clients | client_id PK, tenant_id FK, client_name, client_type, api_key_hash, scopes JSON, status | External integrations and partner systems. |
| api_access_logs | log_id PK, client_id FK, endpoint, method, status_code, latency_ms, requested_at | API usage tracking. |
| security_events | event_id PK, user_id FK, event_type, severity, ip_address, metadata JSON, created_at | Failed login, suspicious access, privilege changes. |
| data_access_logs | access_id PK, user_id FK, entity_type, entity_id, action, field_mask JSON, accessed_at | Tracks sensitive record access. |
MasterData.API
03. Master Data & Reference Library
Stores reusable reference data for freight forwarding, customs, tracking, routing and commercial calculation.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| countries | country_id PK, iso2, iso3, country_name, customs_zone, status | Country reference. |
| cities | city_id PK, country_id FK, city_name, timezone, latitude, longitude, status | City reference. |
| ports | port_id PK, country_id FK, city_id FK, unlocode, port_name, port_type, latitude, longitude, status | Sea, dry, inland and river ports. |
| airports | airport_id PK, country_id FK, city_id FK, iata_code, icao_code, airport_name, status | Air freight route reference. |
| carriers | carrier_id PK, carrier_type, carrier_code, carrier_name, scac_code, api_enabled, status | Shipping lines, airlines, road carriers. |
| vessels | vessel_id PK, imo_no, mmsi_no, vessel_name, call_sign, flag_country_id FK, capacity_teu, status | Vessel master for tracking. |
| container_types | container_type_id PK, iso_code, size_ft, type_name, max_weight_kg, max_volume_cbm | 20GP, 40HC, reefer, open top etc. |
| cargo_types | cargo_type_id PK, cargo_type_name, handling_requirements, is_hazardous, status | General, DG, reefer, oversized, fragile. |
| incoterms | incoterm_id PK, code, name, risk_transfer_point, cost_responsibility JSON | EXW, FOB, CIF, DDP etc. |
| hs_codes | hs_code_id PK, hs_code, description, duty_rate, vat_rate, restricted_flag, country_id FK | Customs HS code catalogue. |
| currencies | currency_id PK, iso_code, name, symbol, decimal_places, status | Currency master. |
| exchange_rates | rate_id PK, base_currency, quote_currency, rate, source, effective_date | FX rates for costing and billing. |
| measurement_units | unit_id PK, unit_code, unit_name, unit_type, conversion_factor | KG, CBM, PCS, LTR, pallet. |
| charge_codes | charge_code_id PK, code, name, charge_category, taxable_flag, default_currency | Freight, THC, DO, customs, delivery, fuel surcharge. |
Party.API / VendorPortal.API
04. Party, Customer, Agent & Vendor Management
Manages shippers, consignees, notify parties, customers, multi-agent network, brokers, carriers, truckers and warehouses.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| parties | party_id PK, tenant_id FK, party_type, legal_name, trade_name, tax_no, registration_no, status | Unified party profile. |
| party_contacts | contact_id PK, party_id FK, full_name, designation, email, phone, whatsapp, is_primary | Contact persons. |
| party_addresses | address_id PK, party_id FK, address_type, line1, line2, city_id FK, postal_code, geo_lat, geo_lng | Pickup, billing, warehouse, delivery addresses. |
| customers | customer_id PK, party_id FK, customer_code, customer_segment, credit_limit, payment_terms_days, account_manager_id FK | Customer-specific commercial data. |
| agents | agent_id PK, party_id FK, agent_code, agent_role, coverage_countries JSON, settlement_terms, portal_enabled | Origin/destination/co-loader/customs/delivery agents. |
| agent_network_lanes | lane_id PK, agent_id FK, origin_country_id FK, destination_country_id FK, service_modes JSON, priority_rank | Agent lane coverage. |
| vendors | vendor_id PK, party_id FK, vendor_type, service_categories JSON, rating, compliance_status | Carrier, transporter, broker, warehouse, surveyor. |
| customs_brokers | broker_id PK, party_id FK, license_no, customs_authority, valid_until, status | Customs clearance service providers. |
| carrier_accounts | carrier_account_id PK, carrier_id FK, party_id FK, contract_no, api_credentials_ref, payment_terms | Customer/vendor account with carrier. |
| party_kyc_documents | kyc_id PK, party_id FK, document_type, document_no, file_id FK, expiry_date, verification_status | Trade license, VAT, importer code, ID documents. |
| party_credit_reviews | review_id PK, customer_id FK, reviewed_by, credit_score, approved_limit, remarks, reviewed_at | Credit governance. |
| vendor_service_rates | vendor_rate_id PK, vendor_id FK, charge_code_id FK, origin_id, destination_id, rate_basis, buy_rate, currency | Vendor buy rates. |
CustomerPortal.API
05. Customer Portal & Self-Service
Enables customers to create shipments, upload documents, track cargo, approve quotes, message operations and access invoices.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| customer_portal_accounts | portal_account_id PK, customer_id FK, primary_user_id FK, portal_status, onboarding_stage | Customer portal access. |
| customer_user_access | access_id PK, customer_id FK, user_id FK, access_role, allowed_branches JSON, allowed_accounts JSON | Controls customer-side roles. |
| customer_saved_addresses | saved_address_id PK, customer_id FK, address_id FK, label, usage_type, is_default | Reusable pickup/delivery locations. |
| customer_shipment_drafts | draft_id PK, customer_id FK, created_by FK, draft_payload JSON, current_step, updated_at | Incomplete booking drafts. |
| customer_document_uploads | upload_id PK, customer_id FK, shipment_id FK, file_id FK, document_type, validation_status | Uploaded files from customer. |
| customer_tracking_subscriptions | subscription_id PK, customer_id FK, shipment_id FK, channel, milestone_filter JSON, status | Milestone notification preferences. |
| customer_messages | message_id PK, customer_id FK, shipment_id FK, sender_user_id, message_text, attachment_file_id, created_at | Shipment conversations. |
| customer_quote_approvals | approval_id PK, quotation_id FK, customer_user_id FK, decision, remarks, decided_at | Quote approval/rejection by customer. |
| customer_portal_activity | activity_id PK, customer_id FK, user_id FK, activity_type, metadata JSON, created_at | Portal audit trail. |
| public_tracking_links | tracking_link_id PK, shipment_id FK, token_hash, expires_at, access_count, status | No-login tracking link. |
Quotation.API / PricingEngine
06. Enquiry, Quotation & Rate Management
Captures enquiries, calculates multi-vendor buy/sell pricing, manages tariffs, applies margins, approvals and quote-to-job conversion.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| enquiries | enquirie_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Enquiry, Quotation & Rate Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| quotation_headers | quotation_header_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Enquiry, Quotation & Rate Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| quotation_routes | quotation_route_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Enquiry, Quotation & Rate Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| quotation_cargo_lines | quotation_cargo_line_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Enquiry, Quotation & Rate Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| quotation_charge_lines | quotation_charge_line_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Enquiry, Quotation & Rate Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| rate_cards | rate_card_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Enquiry, Quotation & Rate Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| rate_card_lines | rate_card_line_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Enquiry, Quotation & Rate Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| margin_rules | margin_rule_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Enquiry, Quotation & Rate Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| quotation_approvals | quotation_approval_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Enquiry, Quotation & Rate Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| quote_revisions | quote_revision_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Enquiry, Quotation & Rate Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| competitor_rate_notes | competitor_rate_note_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Enquiry, Quotation & Rate Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| quote_conversion_logs | quote_conversion_log_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Enquiry, Quotation & Rate Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
Shipment.API / ShipmentLifecycleEngine
07. Shipment / Job Management Core
Central operational entity connecting customer, vendors, agents, customs, tracking, delivery, finance and document records.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| shipments | shipment_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Shipment / Job Management Core domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| shipment_parties | shipment_partie_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Shipment / Job Management Core domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| shipment_references | shipment_reference_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Shipment / Job Management Core domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| shipment_cargo | shipment_cargo_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Shipment / Job Management Core domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| shipment_packages | shipment_package_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Shipment / Job Management Core domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| shipment_status_history | shipment_status_history_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Shipment / Job Management Core domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| shipment_assignments | shipment_assignment_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Shipment / Job Management Core domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| shipment_checklists | shipment_checklist_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Shipment / Job Management Core domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| shipment_sla_tracking | shipment_sla_tracking_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Shipment / Job Management Core domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| shipment_exceptions | shipment_exception_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Shipment / Job Management Core domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| shipment_tags | shipment_tag_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Shipment / Job Management Core domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| shipment_closure | shipment_closure_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Shipment / Job Management Core domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
AgentCollaboration.API
08. Multi-Agent Workflow & Collaboration
Coordinates origin agents, destination agents, customs brokers, co-loaders and delivery partners with task ownership and settlement visibility.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| agent_work_orders | agent_work_order_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Multi-Agent Workflow & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| agent_work_order_tasks | agent_work_order_task_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Multi-Agent Workflow & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| agent_milestones | agent_milestone_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Multi-Agent Workflow & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| agent_document_requests | agent_document_request_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Multi-Agent Workflow & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| agent_messages | agent_message_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Multi-Agent Workflow & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| agent_cost_estimates | agent_cost_estimate_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Multi-Agent Workflow & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| agent_invoice_submissions | agent_invoice_submission_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Multi-Agent Workflow & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| agent_performance_scores | agent_performance_score_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Multi-Agent Workflow & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| agent_access_tokens | agent_access_token_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Multi-Agent Workflow & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| agent_settlement_accounts | agent_settlement_account_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Multi-Agent Workflow & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
Booking.API / CarrierIntegration.API
09. Booking, Carrier, Container & Transport Management
Manages carrier bookings, schedules, voyages, containers, equipment, cut-offs, co-loading and multimodal transport legs.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| carrier_bookings | carrier_booking_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Booking, Carrier, Container & Transport Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| booking_leg_segments | booking_leg_segment_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Booking, Carrier, Container & Transport Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| containers | container_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Booking, Carrier, Container & Transport Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| shipment_containers | shipment_container_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Booking, Carrier, Container & Transport Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| equipment_requests | equipment_request_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Booking, Carrier, Container & Transport Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| cutoff_dates | cutoff_date_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Booking, Carrier, Container & Transport Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| co_load_bookings | co_load_booking_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Booking, Carrier, Container & Transport Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| voyages | voyage_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Booking, Carrier, Container & Transport Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| schedule_search_logs | schedule_search_log_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Booking, Carrier, Container & Transport Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| booking_amendments | booking_amendment_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Booking, Carrier, Container & Transport Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| booking_confirmations | booking_confirmation_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Booking, Carrier, Container & Transport Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| transport_leg_costs | transport_leg_cost_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Booking, Carrier, Container & Transport Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
DocumentOps.API / BLService
10. Bill of Lading, AWB & Transport Documents
Controls HBL, MBL, AWB, delivery order, arrival notice, shipping instruction, amendments, releases and surrender workflows.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| transport_documents | transport_document_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Bill of Lading, AWB & Transport Documents domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| hbl_records | hbl_record_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Bill of Lading, AWB & Transport Documents domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| mbl_records | mbl_record_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Bill of Lading, AWB & Transport Documents domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| awb_records | awb_record_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Bill of Lading, AWB & Transport Documents domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| shipping_instructions | shipping_instruction_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Bill of Lading, AWB & Transport Documents domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| bl_draft_reviews | bl_draft_review_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Bill of Lading, AWB & Transport Documents domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| bl_amendments | bl_amendment_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Bill of Lading, AWB & Transport Documents domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| document_release_orders | document_release_order_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Bill of Lading, AWB & Transport Documents domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| arrival_notices | arrival_notice_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Bill of Lading, AWB & Transport Documents domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| delivery_orders | delivery_order_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Bill of Lading, AWB & Transport Documents domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| document_version_history | document_version_history_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Bill of Lading, AWB & Transport Documents domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| document_compliance_checks | document_compliance_check_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Bill of Lading, AWB & Transport Documents domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
Customs.API / ComplianceEngine
11. Customs Clearance & Compliance
Manages importer/exporter docs, declarations, HS classification, duty/VAT, inspections, holds, broker tasks and release triggers.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| customs_cases | customs_case_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Customs Clearance & Compliance domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| customs_declarations | customs_declaration_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Customs Clearance & Compliance domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| customs_declaration_lines | customs_declaration_line_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Customs Clearance & Compliance domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| customs_duty_calculations | customs_duty_calculation_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Customs Clearance & Compliance domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| customs_payments | customs_payment_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Customs Clearance & Compliance domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| customs_inspections | customs_inspection_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Customs Clearance & Compliance domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| customs_holds | customs_hold_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Customs Clearance & Compliance domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| customs_release_certificates | customs_release_certificate_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Customs Clearance & Compliance domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| compliance_screening_results | compliance_screening_result_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Customs Clearance & Compliance domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| restricted_goods_checks | restricted_goods_check_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Customs Clearance & Compliance domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| customs_broker_tasks | customs_broker_task_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Customs Clearance & Compliance domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| customs_status_polling_logs | customs_status_polling_log_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Customs Clearance & Compliance domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
Warehouse.API / YardService
12. Warehouse, CFS, Yard & Inventory Visibility
Tracks cargo receipt, warehouse stock, CFS/CY activities, stuffing, de-stuffing, damage notes and inventory movement.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| warehouses | warehouse_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Warehouse, CFS, Yard & Inventory Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| warehouse_zones | warehouse_zone_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Warehouse, CFS, Yard & Inventory Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| warehouse_locations | warehouse_location_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Warehouse, CFS, Yard & Inventory Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| goods_receipts | goods_receipt_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Warehouse, CFS, Yard & Inventory Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| goods_receipt_lines | goods_receipt_line_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Warehouse, CFS, Yard & Inventory Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| stock_items | stock_item_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Warehouse, CFS, Yard & Inventory Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| inventory_movements | inventory_movement_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Warehouse, CFS, Yard & Inventory Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| cfs_operations | cfs_operation_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Warehouse, CFS, Yard & Inventory Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| stuffing_plans | stuffing_plan_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Warehouse, CFS, Yard & Inventory Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| stuffing_plan_lines | stuffing_plan_line_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Warehouse, CFS, Yard & Inventory Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| damage_reports | damage_report_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Warehouse, CFS, Yard & Inventory Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| yard_movements | yard_movement_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Warehouse, CFS, Yard & Inventory Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
Tracking.API / VisibilityEngine
13. Tracking, Event Ingestion & Supply Chain Visibility
Provides multi-vendor cargo tracking, AIS/container/carrier API events, ETA prediction, geofencing, exceptions and customer visibility.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| tracking_numbers | tracking_number_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Tracking, Event Ingestion & Supply Chain Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| tracking_sources | tracking_source_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Tracking, Event Ingestion & Supply Chain Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| tracking_source_credentials | tracking_source_credential_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Tracking, Event Ingestion & Supply Chain Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| tracking_events | tracking_event_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Tracking, Event Ingestion & Supply Chain Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| tracking_event_raw_logs | tracking_event_raw_log_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Tracking, Event Ingestion & Supply Chain Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| milestone_definitions | milestone_definition_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Tracking, Event Ingestion & Supply Chain Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| shipment_milestones | shipment_milestone_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Tracking, Event Ingestion & Supply Chain Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| eta_predictions | eta_prediction_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Tracking, Event Ingestion & Supply Chain Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| delay_events | delay_event_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Tracking, Event Ingestion & Supply Chain Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| geofences | geofence_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Tracking, Event Ingestion & Supply Chain Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| geofence_events | geofence_event_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Tracking, Event Ingestion & Supply Chain Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| container_tracking_snapshots | container_tracking_snapshot_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Tracking, Event Ingestion & Supply Chain Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| vessel_tracking_snapshots | vessel_tracking_snapshot_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Tracking, Event Ingestion & Supply Chain Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| gps_device_positions | gps_device_position_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Tracking, Event Ingestion & Supply Chain Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| tracking_visibility_rules | tracking_visibility_rule_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Tracking, Event Ingestion & Supply Chain Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| tracking_webhook_subscriptions | tracking_webhook_subscription_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Tracking, Event Ingestion & Supply Chain Visibility domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
Delivery.API / DriverApp.API
14. Last-Mile, Door-to-Door Delivery & POD
Controls pickup, delivery scheduling, driver assignment, route planning, live GPS, delivery attempts, POD and redelivery.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| delivery_orders | delivery_order_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Last-Mile, Door-to-Door Delivery & POD domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| delivery_tasks | delivery_task_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Last-Mile, Door-to-Door Delivery & POD domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| drivers | driver_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Last-Mile, Door-to-Door Delivery & POD domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| vehicles | vehicle_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Last-Mile, Door-to-Door Delivery & POD domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| driver_vehicle_assignments | driver_vehicle_assignment_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Last-Mile, Door-to-Door Delivery & POD domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| route_plans | route_plan_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Last-Mile, Door-to-Door Delivery & POD domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| route_stops | route_stop_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Last-Mile, Door-to-Door Delivery & POD domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| delivery_attempts | delivery_attempt_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Last-Mile, Door-to-Door Delivery & POD domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| proof_of_delivery | proof_of_delivery_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Last-Mile, Door-to-Door Delivery & POD domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| delivery_reschedule_requests | delivery_reschedule_request_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Last-Mile, Door-to-Door Delivery & POD domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| driver_app_sessions | driver_app_session_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Last-Mile, Door-to-Door Delivery & POD domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| delivery_costs | delivery_cost_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Last-Mile, Door-to-Door Delivery & POD domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
Exception.API / Claims.API
15. Exception, Claims, Damage & Risk Management
Manages operational exceptions, cargo damage, delays, claims, insurance cases, disputes and corrective action.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| exception_cases | exception_case_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Exception, Claims, Damage & Risk Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| exception_actions | exception_action_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Exception, Claims, Damage & Risk Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| customer_complaints | customer_complaint_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Exception, Claims, Damage & Risk Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| cargo_claims | cargo_claim_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Exception, Claims, Damage & Risk Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| claim_documents | claim_document_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Exception, Claims, Damage & Risk Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| insurance_policies | insurance_policie_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Exception, Claims, Damage & Risk Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| insurance_claims | insurance_claim_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Exception, Claims, Damage & Risk Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| risk_rules | risk_rule_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Exception, Claims, Damage & Risk Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| shipment_risk_scores | shipment_risk_score_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Exception, Claims, Damage & Risk Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| root_cause_analysis | root_cause_analysi_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Exception, Claims, Damage & Risk Management domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
Finance.API / Billing.API
16. Finance, Billing, Settlement & Profitability
Handles customer invoicing, vendor bills, duty payments, agent settlement, AR/AP, cost accruals, profitability and financial closure.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| shipment_charge_actuals | shipment_charge_actual_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Finance, Billing, Settlement & Profitability domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| customer_invoices | customer_invoice_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Finance, Billing, Settlement & Profitability domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| customer_invoice_lines | customer_invoice_line_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Finance, Billing, Settlement & Profitability domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| vendor_bills | vendor_bill_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Finance, Billing, Settlement & Profitability domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| vendor_bill_lines | vendor_bill_line_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Finance, Billing, Settlement & Profitability domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| payment_receipts | payment_receipt_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Finance, Billing, Settlement & Profitability domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| payment_allocations | payment_allocation_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Finance, Billing, Settlement & Profitability domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| vendor_payments | vendor_payment_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Finance, Billing, Settlement & Profitability domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| agent_settlements | agent_settlement_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Finance, Billing, Settlement & Profitability domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| profitability_snapshots | profitability_snapshot_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Finance, Billing, Settlement & Profitability domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| cost_accruals | cost_accrual_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Finance, Billing, Settlement & Profitability domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| credit_notes | credit_note_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Finance, Billing, Settlement & Profitability domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| debit_notes | debit_note_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Finance, Billing, Settlement & Profitability domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| tax_ledgers | tax_ledger_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Finance, Billing, Settlement & Profitability domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
Notification.API / CommunicationService
17. Notification, Communication & Collaboration
Sends milestone alerts, exception notifications, document reminders, WhatsApp/SMS/email/in-app messages and maintains history.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| notification_templates | notification_template_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Notification, Communication & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| notification_rules | notification_rule_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Notification, Communication & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| notification_queue | notification_queue_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Notification, Communication & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| notification_delivery_logs | notification_delivery_log_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Notification, Communication & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| email_threads | email_thread_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Notification, Communication & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| email_messages | email_message_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Notification, Communication & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| whatsapp_messages | whatsapp_message_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Notification, Communication & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| in_app_notifications | in_app_notification_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Notification, Communication & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| communication_preferences | communication_preference_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Notification, Communication & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| broadcast_campaigns | broadcast_campaign_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Notification, Communication & Collaboration domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
FileStorage.API / DMS.API
18. Document Management, Files & Digital Archive
Central repository for shipment, customs, invoices, POD, KYC, compliance and operational records with versioning and access control.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| files | file_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Document Management, Files & Digital Archive domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| file_metadata | file_metadata_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Document Management, Files & Digital Archive domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| file_versions | file_version_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Document Management, Files & Digital Archive domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| document_requirements | document_requirement_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Document Management, Files & Digital Archive domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| shipment_documents | shipment_document_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Document Management, Files & Digital Archive domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| customs_documents | customs_document_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Document Management, Files & Digital Archive domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| document_review_tasks | document_review_task_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Document Management, Files & Digital Archive domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| document_ocr_results | document_ocr_result_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Document Management, Files & Digital Archive domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| document_access_rules | document_access_rule_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Document Management, Files & Digital Archive domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| document_expiry_alerts | document_expiry_alert_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Document Management, Files & Digital Archive domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
Reporting.API / AnalyticsWarehouse
19. Analytics, BI, Dashboard & Reporting
Provides dashboards, customer visibility, agent/vendor KPIs, customs cycle time, delivery performance, profitability and reporting.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| dashboard_widgets | dashboard_widget_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Analytics, BI, Dashboard & Reporting domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| user_dashboards | user_dashboard_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Analytics, BI, Dashboard & Reporting domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| report_definitions | report_definition_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Analytics, BI, Dashboard & Reporting domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| scheduled_reports | scheduled_report_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Analytics, BI, Dashboard & Reporting domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| report_execution_logs | report_execution_log_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Analytics, BI, Dashboard & Reporting domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| kpi_snapshots | kpi_snapshot_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Analytics, BI, Dashboard & Reporting domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| shipment_performance_facts | shipment_performance_fact_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Analytics, BI, Dashboard & Reporting domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| agent_performance_facts | agent_performance_fact_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Analytics, BI, Dashboard & Reporting domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| customs_performance_facts | customs_performance_fact_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Analytics, BI, Dashboard & Reporting domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| delivery_performance_facts | delivery_performance_fact_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Analytics, BI, Dashboard & Reporting domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| customer_visibility_views | customer_visibility_view_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Analytics, BI, Dashboard & Reporting domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| data_export_jobs | data_export_job_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Analytics, BI, Dashboard & Reporting domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
IntegrationHub.API / EDIService
20. Integration Hub, APIs, Webhooks & EDI
Connects carriers, AIS, customs, payment gateways, maps, WhatsApp, customer systems, agent portals and accounting systems.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| integration_connectors | integration_connector_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Integration Hub, APIs, Webhooks & EDI domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| connector_endpoints | connector_endpoint_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Integration Hub, APIs, Webhooks & EDI domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| connector_credentials | connector_credential_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Integration Hub, APIs, Webhooks & EDI domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| integration_jobs | integration_job_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Integration Hub, APIs, Webhooks & EDI domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| integration_job_runs | integration_job_run_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Integration Hub, APIs, Webhooks & EDI domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| webhook_inbound_events | webhook_inbound_event_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Integration Hub, APIs, Webhooks & EDI domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| webhook_outbound_events | webhook_outbound_event_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Integration Hub, APIs, Webhooks & EDI domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| edi_messages | edi_message_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Integration Hub, APIs, Webhooks & EDI domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| edi_acknowledgements | edi_acknowledgement_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Integration Hub, APIs, Webhooks & EDI domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| integration_error_logs | integration_error_log_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Integration Hub, APIs, Webhooks & EDI domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| mapping_rules | mapping_rule_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Integration Hub, APIs, Webhooks & EDI domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| idempotency_keys | idempotency_key_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Integration Hub, APIs, Webhooks & EDI domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
Workflow.API / AutomationEngine
21. Workflow, Automation, SLA & Task Engine
Automates lifecycle transitions, task creation, approval chains, escalation, SLA monitoring and exception triggers.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| workflow_definitions | workflow_definition_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Workflow, Automation, SLA & Task Engine domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| workflow_steps | workflow_step_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Workflow, Automation, SLA & Task Engine domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| workflow_transitions | workflow_transition_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Workflow, Automation, SLA & Task Engine domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| workflow_instances | workflow_instance_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Workflow, Automation, SLA & Task Engine domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| workflow_instance_steps | workflow_instance_step_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Workflow, Automation, SLA & Task Engine domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| task_templates | task_template_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Workflow, Automation, SLA & Task Engine domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| tasks | task_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Workflow, Automation, SLA & Task Engine domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| task_comments | task_comment_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Workflow, Automation, SLA & Task Engine domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| sla_policies | sla_policie_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Workflow, Automation, SLA & Task Engine domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| escalation_rules | escalation_rule_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Workflow, Automation, SLA & Task Engine domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| automation_rules | automation_rule_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Workflow, Automation, SLA & Task Engine domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| automation_execution_logs | automation_execution_log_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Workflow, Automation, SLA & Task Engine domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
Governance.API / AuditService
22. Audit, Data Governance, Compliance & Enterprise Controls
Provides enterprise-grade auditability, data governance, privacy, approvals, retention, backup controls and regulatory readiness.
| Table | Key Columns | Purpose / Notes |
|---|---|---|
| audit_logs | audit_log_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Audit, Data Governance, Compliance & Enterprise Controls domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| approval_policies | approval_policie_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Audit, Data Governance, Compliance & Enterprise Controls domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| approval_requests | approval_request_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Audit, Data Governance, Compliance & Enterprise Controls domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| approval_decisions | approval_decision_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Audit, Data Governance, Compliance & Enterprise Controls domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| data_retention_rules | data_retention_rule_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Audit, Data Governance, Compliance & Enterprise Controls domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| data_archive_jobs | data_archive_job_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Audit, Data Governance, Compliance & Enterprise Controls domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| privacy_requests | privacy_request_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Audit, Data Governance, Compliance & Enterprise Controls domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| backup_runs | backup_run_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Audit, Data Governance, Compliance & Enterprise Controls domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| restore_tests | restore_test_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Audit, Data Governance, Compliance & Enterprise Controls domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| configuration_change_logs | configuration_change_log_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Audit, Data Governance, Compliance & Enterprise Controls domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| data_quality_rules | data_quality_rule_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Audit, Data Governance, Compliance & Enterprise Controls domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
| data_quality_issues | data_quality_issue_id PK, tenant_id FK, shipment_id FK where applicable, status, created_at, updated_at | Supports Audit, Data Governance, Compliance & Enterprise Controls domain. Add FKs to customer, agent, vendor, document, finance or tracking entities as required. |
Start Controlled MVP, Design for Full Enterprise Scale
MVP Phase
Launch with identity, customer portal, shipment/job core, tracking events, document upload, agent work orders, customs case tracking, delivery tasks, notifications and basic dashboard. Keep table design enterprise-ready from day one.
Scale Phase
Add carrier API integrations, AIS polling, customs portal integration, agent settlements, advanced finance, automated SLA, workflow engine, mobile driver app, webhooks and BI warehouse.
Enterprise Phase
Enable multi-country setup, data warehouse, event streaming, AI ETA prediction, anomaly detection, customer webhooks, audit retention, backup governance, high availability and Kubernetes deployment.
Important: Even in MVP, use enterprise-grade tenant isolation, role-based access, immutable audit logs, normalized shipment lifecycle events, document versioning, and scalable integration architecture. This avoids rebuilding the foundation later.