# FellaHost Company Limited **Project Developers & Software Engineering Specialists** **Document Reference:** Section 24 Deliverables for Software Development Team **System Designation:** Integrated Clearing, Forwarding & Logistics System (ICFLS) **Corporate Entity:** FellaHost Company Limited **TIN NO:** 189-723-717 | **B.L.No:** 20000105272 **Headquarters:** P.O. Box 80079, Dar es Salaam, Tanzania **Support Desk:** support@fellahost.co.tz | **Web:** www.fellahost.co.tz **System Deployment Host:** `logistics.domain.co.tz` | `api.domain.co.tz` **Publication Date:** September 2026 | **Version:** 1.0 (Production Master Engineering Pack) --- # 1. Entity Relationship Diagram (ERD) & Relational Data Architecture ## Executive Overview The Integrated Clearing, Forwarding & Logistics System (ICFLS) data architecture is designed as a high-throughput, ACID-compliant relational enterprise model. The database orchestrates international supply chain lifecycles, multimodal freight haulage across East and Central Africa, automated customs clearance via the Tanzania Revenue Authority (TRA) TANCIS system, port demurrage mitigation, and segregated client escrow custody accounting. This document details the complete theoretical and practical database architecture in accordance with modern enterprise standards (referencing foundational ERD principles as detailed in the London School of Emerging Technology ERD architectural framework). --- ## 1.1 Foundations of Entity-Relationship Modeling ### 1.1.1 Core ERD Building Blocks 1. **Entities**: Represent independent business concepts, real-world actors, transactions, or domain artifacts with unique identity. - **Strong Entities**: Exist independently (e.g., `customers`, `users`, `vehicles`, `geo_countries`). - **Weak Entities**: Existence is dependent upon a parent strong entity (e.g., `job_milestones`, `declaration_items`, `invoice_items`). - **Associative / Junction Entities**: Resolve Many-to-Many relationships into discrete transactional pairs with metadata (e.g., `task_workflows`, `manifest_items`). 2. **Attributes**: Characteristic data elements describing an entity. - **Key Attributes**: Uniquely identify records (e.g., Primary Key `id`, Unique Tracking Reference `tracking_ref`). - **Composite Attributes**: Conceptual groupings mapped to discrete relational columns (e.g., Physical Address decomposed into `address_line_1`, `ward_id`, `district_id`, `region_id`, `country_code`). - **Single-Valued vs. Multi-Valued Attributes**: Normalized via dedicated child tables (e.g., a Customer having multiple telephone numbers is normalized into `customer_contacts`). - **Derived Attributes**: Computed on-demand or maintained via triggers/events for indexing performance (e.g., `total_amount_tzs = subtotal_tzs + vat_tzs`). 3. **Relationships & Cardinality**: Describe structural and transactional associations between entities: - **One-to-One (1:1)**: E.g., `trips` to `vehicles` active operational binding. - **One-to-Many (1:N)**: E.g., One `customer` places many `jobs`; One `job` incurs many `job_expenses`. - **Many-to-Many (M:N)**: E.g., `declarations` to `containers` resolved via `declaration_items` and manifest tracking. --- ### Visual Classical ERD (Peter Chen Standard) Both the foundational Academic Reference ERD (LSET standard) and the comprehensive ICFLS Multimodal Logistics Domain ERD are graphically rendered in the companion **01_Entity_Relationship_Diagram_ERD.html** executive document. ``` +------------------+ +-------------------------+ | CUSTOMER | | CUSTOMS_DECLARATION | +--------+---------+ +------------+------------+ | | | (1) | (1) < PLACES > < LODGES_FOR > | | | (N) | (N) +--------v-------------------------------------------v------------+ | JOB_ORDER | +--------+---------------------+---------------------+------------+ | | | | (1) | (1) | (1) < MANIFESTS > < DISPATCHES > < GENERATES > | | | | (N) | (N) | (N) +--------v---------+ +-------v-----------+ +-----v------------+ | CONTAINER | | MULTIMODAL_TRIP | | COMMERCIAL_INVOICE +------------------+ +-------------------+ +------------------+ ``` ## 1.2 Classical Conceptual ERD (Peter Chen Notation) for ICFLS Logistics Architecture The conceptual data model for the Integrated Clearing, Forwarding & Logistics System (ICFLS) is strictly engineered using classical Peter Chen notation: Rectangles represent strong and weak Entities, Ovals represent single-valued and key Attributes (with Primary Keys explicitly underlined), and Diamonds define operational Relationships with formal 1:N and M:N cardinalities. ### 1.2.1 Core Entity Sets & Key Attributes (Peter Chen Schema) ``` +--------------------------------------------------------------------------------------------------+ | CUSTOMER [Entity Rectangle] | | Attributes (Ovals): (customer_id [PK_Underlined]), (company_name), (tin_number), (phone_number) | +--------------------------------------------------------------------------------------------------+ | < PLACES > [Diamond (1:N)] v +--------------------------------------------------------------------------------------------------+ | JOB_FILE [Entity Rectangle] | | Attributes (Ovals): (job_id [PK_Underlined]), (job_number), (service_type), (direction), (status)| +--------------------------------------------------------------------------------------------------+ | | | < LODGES > (1:N) < CARRIES > (1:N) < BILLED > (1:N) v v v +-----------------------+ +-----------------------+ +-----------------------+ | DECLARATION [Entity] | | CONTAINER [Entity] | | INVOICE [Entity] | | (declaration_id [PK]) | | (container_id [PK]) | | (invoice_id [PK]) | | (tancis_reference) | | (container_no) | | (invoice_number) | | (assessed_tax) | | (free_days) | | (total_amount) | +-----------------------+ +-----------------------+ +-----------------------+ | | < CONTAINS > (1:N) < DISPATCHED_ON > (N:1) v v +-----------------------+ +-----------------------+ | DECLARATION_ITEM | | TRANSPORT_TRIP | | (item_id [PK]) | | (trip_id [PK]) | | (hs_code) | | (trip_number) | | (cif_tzs) | | (destination) | +-----------------------+ +-----------------------+ | +-----------------+-----------------+ | | < ASSIGNED_TO > (N:1) < DRIVEN_BY > (N:1) v v +-------------------+ +-------------------+ | VEHICLE [Entity] | | DRIVER [Entity] | | (vehicle_id [PK]) | | (driver_id [PK]) | | (plate_number) | | (full_name) | +-------------------+ +-------------------+ ``` ### 1.2.2 Operational Cardinality Matrix - **CUSTOMER (1) -- < PLACES > -- (N) JOB_FILE:** One customer commissions multiple consignment jobs. - **JOB_FILE (1) -- < LODGES > -- (N) DECLARATION:** One job file lodges customs clearance entries with TRA TANCIS. - **DECLARATION (1) -- < CONTAINS > -- (N) DECLARATION_ITEM:** One declaration contains multiple tariff items. - **JOB_FILE (1) -- < CARRIES > -- (N) CONTAINER:** One consignment job transports multiple ISO containers. - **CONTAINER (N) -- < DISPATCHED_ON > -- (1) TRANSPORT_TRIP:** Containers move on assigned haulage trips. - **TRANSPORT_TRIP (N) -- < ASSIGNED_TO > -- (1) VEHICLE:** Trips utilize registered heavy-duty trucks. - **TRANSPORT_TRIP (N) -- < DRIVEN_BY > -- (1) DRIVER:** Trips are operated by licensed commercial drivers. - **JOB_FILE (1) -- < BILLED_UNDER > -- (N) INVOICE:** Operational fees and disbursements are billed on invoices. --- ## 1.3 ICFLS Enterprise Relational Architecture (31 Tables) The ICFLS production database (`domain.co.tz`) is partitioned across 7 logical domains: ```mermaid erDiagram CUSTOMERS ||--o{ JOBS : "places" CUSTOMERS ||--o{ CUSTOMER_CONTACTS : "maintains" CUSTOMERS ||--o{ CUSTOMER_DEPOSITS : "funds_escrow" CUSTOMERS ||--o{ INVOICES : "billed_to" JOBS ||--o{ DECLARATIONS : "originates" JOBS ||--o{ CONTAINERS : "manifests" JOBS ||--o{ TRIPS : "dispatches" JOBS ||--o{ JOB_EXPENSES : "incurs" JOBS ||--o{ JOB_MILESTONES : "tracks" JOBS ||--o{ JOB_DOCUMENTS : "stores" JOBS ||--o{ INVOICES : "generates" DECLARATIONS ||--o{ DECLARATION_ITEMS : "contains" DECLARATIONS ||--o{ TANCIS_LOGS : "transmits" TRIPS }o--|| VEHICLES : "assigned_truck" TRIPS }o--|| DRIVERS : "operated_by" TRIPS ||--o{ TRIP_WAYPOINTS : "logs_telemetry" TRIPS ||--o{ FUEL_LOGS : "consumes" INVOICES ||--o{ INVOICE_ITEMS : "itemizes" INVOICES ||--o{ PAYMENT_RECEIPTS : "reconciled_by" USERS ||--o{ AUDIT_LOGS : "triggers" ``` ### 1.3.1 Relational Data Dictionary by Domain #### Domain A: Identity, Authentication & Audit 1. `users`: Corporate staff, operational officers, and portal users (`id`, `name`, `email`, `password_hash`, `role`, `two_factor_enabled`, `two_factor_code`, `status`, `last_login_at`, `last_login_ip`). 2. `password_reset_tokens`: Transient single-use OTP verification (`email` PK, `token`, `otp_code`, `created_at`). 3. `system_settings`: Corporate configuration key-value storage (`id`, `key` UK, `value`, `category`). 4. `audit_logs`: Non-repudiation ledger (`id`, `user_id` FK, `action`, `entity_type`, `entity_id`, `description`, `ip_address`, `geo_location`, `isp`). #### Domain B: Commercial CRM & Segregated Customer Custody 5. `customers`: Commercial clients and consignees (`id`, `code` UK, `company_name`, `tin_number`, `vrn_number`, `email`, `phone`, `credit_limit`, `balance_tzs`, `status`). 6. `customer_contacts`: Key personnel at customer organization (`id`, `customer_id` FK, `name`, `title`, `phone`, `email`). 7. `customer_deposits`: Segregated customer escrow funds (`id`, `customer_id` FK, `job_id` FK, `amount_tzs`, `bank_reference`, `purpose`, `verified_by`). #### Domain C: Operations & Consignment Management 8. `jobs`: Master operational container (`id`, `job_number` UK, `customer_id` FK, `bl_number`, `vessel_name`, `cargo_description`, `port_of_entry`, `destination_city`, `status`). 9. `job_milestones`: Tracking milestones (`id`, `job_id` FK, `milestone_code`, `milestone_name`, `completed_at`, `completed_by`). 10. `job_documents`: Digital archive (`id`, `job_id` FK, `document_type`, `file_path`, `ocr_status`, `uploaded_by`). 11. `task_workflows`: SLA state triggers (`id`, `job_id` FK, `task_name`, `assigned_to` FK, `due_date`, `status`). #### Domain D: Customs Clearance & TANCIS Integration 12. `declarations`: TRA customs entries (`id`, `job_id` FK, `tancis_reference` UK, `customs_office`, `total_cif_value`, `total_duties_tzs`, `assessment_status`). 13. `declaration_items`: Tariff line items (`id`, `declaration_id` FK, `hs_code`, `goods_description`, `cif_tzs`, `import_duty_tzs`, `vat_tzs`). 14. `tancis_logs`: SOAP/REST electronic message exchange (`id`, `declaration_id` FK, `message_type`, `payload`, `response_status`). 15. `hs_code_tariffs`: Tariff reference database (`id`, `hs_code` UK, `description`, `duty_rate`, `vat_rate`). #### Domain E: Fleet Logistics & Regional Haulage 16. `vehicles`: Trucks and trailers (`id`, `plate_number` UK, `trailer_number`, `make`, `payload_capacity_tons`, `status`). 17. `drivers`: Licensed commercial drivers (`id`, `full_name`, `license_number` UK, `phone`, `passport_number`, `status`). 18. `trips`: Haulage manifests (`id`, `trip_number` UK, `job_id` FK, `vehicle_id` FK, `driver_id` FK, `origin`, `destination`, `border_port`, `status`). 19. `trip_waypoints`: GPS telematics pings (`id`, `trip_number`, `latitude`, `longitude`, `speed_kmh`, `recorded_at`). 20. `fuel_logs`: Fuel vouchers and consumption (`id`, `trip_id` FK, `fuel_station`, `liters`, `cost_per_liter`, `total_cost_tzs`). #### Domain F: Container Demurrage & Depots 21. `containers`: Physical maritime containers (`id`, `container_number` UK, `job_id` FK, `size_type`, `shipping_line`, `discharge_date`, `free_days`, `demurrage_start_date`, `status`). 22. `container_trackings`: Physical interchange movements (`id`, `container_id` FK, `location`, `depot_name`, `event_timestamp`). 23. `shipping_lines`: Shipping carriers and free-day terms (`id`, `code` UK, `name`, `contact_email`, `standard_free_days`). #### Domain G: Finance & Commercial Billing 24. `invoices`: Customer commercial bills (`id`, `invoice_number` UK, `customer_id` FK, `job_id` FK, `currency`, `subtotal`, `vat`, `total_amount`, `payment_status`). 25. `invoice_items`: Itemized charges (`id`, `invoice_id` FK, `charge_code`, `description`, `amount`). 26. `job_expenses`: Internal direct disbursement accruals (`id`, `job_id` FK, `expense_category`, `vendor_name`, `amount_tzs`, `is_rechargeable`). 27. `payment_receipts`: Reconciled payments (`id`, `invoice_id` FK, `payment_date`, `amount_received`, `payment_channel`). 28. `exchange_rates`: Official currency conversions (`id`, `currency_code` UK, `rate_to_tzs`, `effective_date`). #### Domain H: Geographic Hierarchy 29. `geo_countries`: ISO country master (`id`, `iso_code` UK, `country_name`). 30. `geo_regions`: Provincial regions (`id`, `country_code` FK, `region_name`). 31. `geo_districts`: Administrative districts (`id`, `region_id` FK, `district_name`). --- ## 1.4 Database Normalization Analysis - **First Normal Form (1NF)**: All column values are atomic. Multi-valued telephone contacts and container numbers are decoupled into dedicated rows. - **Second Normal Form (2NF)**: All non-key attributes are fully functionally dependent on the entire Primary Key. Composite PKs in associative tables are strictly mapped. - **Third Normal Form (3NF)**: Transitive functional dependencies are eliminated (e.g., Driver phone number is maintained strictly on `drivers`, not duplicated on `trips`). --- ## 1.5 Indexing & Integrity Constraints - Unique indexes on `job_number`, `tancis_reference`, `container_number`, `invoice_number`, `plate_number`. - Compound B-Tree indexes on `(customer_id, status)` and `(job_id, created_at)`. - Foreign key constraints with `ON DELETE RESTRICT` for financial and customs tables to prevent orphan transactions. --- ### Regulatory Notice > **We're Top leading Company Registering under Commercial & Technical ICT Services TIN NO: 189-723-717** > *FellaHost Company Limited — Project Developers & Engineering Specialists.* > *Confidential & Proprietary Enterprise Asset — All Rights Reserved.*