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

Headquarters: P.O. Box 80079, Dar es Salaam, Tanzania

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.

1.2 Classical Conceptual ERD (Peter Chen Notation) for ICFLS Logistics Architecture

In accordance with enterprise data modeling standards, the conceptual data model for the Integrated Clearing, Forwarding & Logistics System (ICFLS) is strictly illustrated 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.3 ICFLS Enterprise Relational Architecture (31 Tables)

The ICFLS production database (domain.co.tz) is partitioned across 7 logical domains:

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

1. customers: Commercial clients and consignees (id, code UK, company_name, tin_number, vrn_number, email, phone, credit_limit, balance_tzs, status).
2. customer_contacts: Key personnel at customer organization (id, customer_id FK, name, title, phone, email).
3. 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

1. jobs: Master operational container (id, job_number UK, customer_id FK, bl_number, vessel_name, cargo_description, port_of_entry, destination_city, status).
2. job_milestones: Tracking milestones (id, job_id FK, milestone_code, milestone_name, completed_at, completed_by).
3. job_documents: Digital archive (id, job_id FK, document_type, file_path, ocr_status, uploaded_by).
4. task_workflows: SLA state triggers (id, job_id FK, task_name, assigned_to FK, due_date, status).

Domain D: Customs Clearance & TANCIS Integration

1. declarations: TRA customs entries (id, job_id FK, tancis_reference UK, customs_office, total_cif_value, total_duties_tzs, assessment_status).
2. declaration_items: Tariff line items (id, declaration_id FK, hs_code, goods_description, cif_tzs, import_duty_tzs, vat_tzs).
3. tancis_logs: SOAP/REST electronic message exchange (id, declaration_id FK, message_type, payload, response_status).
4. hs_code_tariffs: Tariff reference database (id, hs_code UK, description, duty_rate, vat_rate).

Domain E: Fleet Logistics & Regional Haulage

1. vehicles: Trucks and trailers (id, plate_number UK, trailer_number, make, payload_capacity_tons, status).
2. drivers: Licensed commercial drivers (id, full_name, license_number UK, phone, passport_number, status).
3. trips: Haulage manifests (id, trip_number UK, job_id FK, vehicle_id FK, driver_id FK, origin, destination, border_port, status).
4. trip_waypoints: GPS telematics pings (id, trip_number, latitude, longitude, speed_kmh, recorded_at).
5. fuel_logs: Fuel vouchers and consumption (id, trip_id FK, fuel_station, liters, cost_per_liter, total_cost_tzs).

Domain F: Container Demurrage & Depots

1. containers: Physical maritime containers (id, container_number UK, job_id FK, size_type, shipping_line, discharge_date, free_days, demurrage_start_date, status).
2. container_trackings: Physical interchange movements (id, container_id FK, location, depot_name, event_timestamp).
3. shipping_lines: Shipping carriers and free-day terms (id, code UK, name, contact_email, standard_free_days).

Domain G: Finance & Commercial Billing

1. invoices: Customer commercial bills (id, invoice_number UK, customer_id FK, job_id FK, currency, subtotal, vat, total_amount, payment_status).
2. invoice_items: Itemized charges (id, invoice_id FK, charge_code, description, amount).
3. job_expenses: Internal direct disbursement accruals (id, job_id FK, expense_category, vendor_name, amount_tzs, is_rechargeable).
4. payment_receipts: Reconciled payments (id, invoice_id FK, payment_date, amount_received, payment_channel).
5. exchange_rates: Official currency conversions (id, currency_code UK, rate_to_tzs, effective_date).

Domain H: Geographic Hierarchy

1. geo_countries: ISO country master (id, iso_code UK, country_name).
2. geo_regions: Provincial regions (id, country_code FK, region_name).
3. 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.