Consignment Tracking and COD Remittance System for a Courier Franchise (React, Node.js, PostgreSQL)

TrackParcel is a web application built for a fictional courier booking franchise in Suryapet, Telangana — an agent of a national courier network booking 120–180 consignments a day: documents, e-commerce returns and cash-on-delivery (COD) parcels. Today the work runs on a paper counterfoil book, a laminated rate card, a calculator and three Excel sheets — and the sheets are where the money leaks.

The system has four roles: the franchise manager, the counter clerk, the delivery agent and the accounts staff. The clerk books consignments with shipper, consignee, weight and dimensions, and the built-in rate engine computes freight automatically — service type, zone pair, 500-gram weight slabs, volumetric weight (L×B×H ÷ 5000), fuel surcharge, out-of-delivery-area (ODA) charges and the COD fee. Each booking mints a unique AWB number with a mod-7 check digit, so a mistyped number is caught before a parcel is misrouted.

The workflow side is where it earns its marks: consignments move through a scan-driven status chain (Booked → Picked up → In transit → Out for delivery → Delivered / Attempted → Returned to shipper), agents work from a daily delivery run sheet and record structured undelivered reasons, and every rupee of COD is tracked from collection to bank remittance with a daily shortage report — the reconciliation the franchise does nightly by hand. Account customers are billed monthly against their rate contracts. Built with React, Node.js/Express and PostgreSQL, it is a workflow-heavy JNTUH MCA R22 Semester-4 project with a defensible reason for every table.

A tier-2 courier franchise books and delivers hundreds of consignments daily, but its own operations run on counterfoil books, a laminated rate card, phone calls to the hub and night-time cash tallies. Rating arithmetic — weight slabs, volumetric weight, zone pairs, fuel and ODA surcharges, COD fees — is done by hand, so quotes are inconsistent and undercharging is common. Consignment status is invisible locally, so the counter absorbs every customer call. The sharpest problem is cash: COD amounts collected by delivery agents are tallied against a notebook, and remittances are recorded as lump-sum bank deposits with no link to specific consignments, so a shortage surfaces days late, disputed and unproven. Account-customer bills are compiled manually from paper. There is a need for a role-based web application that rates bookings from a versioned rate table, issues check-digited AWB numbers, keeps an immutable scan history per consignment, structures delivery attempts and proofs of delivery, and reconciles every rupee of COD from collection to bank remittance — while storing no government identity numbers.

Abstract

This project delivers a consignment-booking, tracking and COD-reconciliation web application for a tier-2 courier franchise. Counter staff book consignments on a database-driven rate engine — weight slabs, volumetric weight, zone pairs, surcharges, COD fees — and every booking receives a check-digited AWB number. Scan events advance each consignment; delivery agents work from daily run sheets and record proof of delivery or structured undelivered reasons; the accounts module tracks COD collections per agent and reconciles them against bank remittances, producing a daily shortage report. Account customers are billed monthly. The stack is React, Node.js/Express and PostgreSQL.

Introduction

A courier franchise sits between two systems it does not control: the national network and its local customers. The network tracks parcels only after the hub; the franchise's own work — booking, rating, cash collection, remittance — happens before that, and that is where the paper lives. An e-commerce pickup of 60 COD parcels means ₹27,000 leaving in an agent's pocket; collected cash is known only by night-time counterfoil tallying. The project digitises precisely this local half of the business.

Existing System and Its Limitations

• Bookings live in a counterfoil book; no structured copy exists.
• Rates come from a laminated card; slab, volumetric and ODA arithmetic is manual, so quotes vary.
• Status is known only by phoning the hub; the counter absorbs every customer call.
• COD cash is tallied in a notebook; remittances are lump-sum bank deposits with no link to consignments.
• Account bills are assembled from counterfoils at month-end and provoke disputes.
• Limitations: no single source of truth, rating errors, untraceable COD leakage, no analytics.

Proposed System

• Booking and rating: a versioned rate table by service type and zone pair — 500 g slabs, volumetric weight (÷5000), fuel surcharge, ODA flag, COD fee — effective-dated, so past bookings re-compute at their then-current rates.
• AWB integrity: a sequence-backed generator with a mod-7 check digit and a unique index rejects duplicates and typos at entry.
• Scan-driven tracking: every status change is a scan-event row with location, actor and timestamp; the tracking page renders this immutable history.
• Run sheets and POD: the agent gets a daily run sheet; delivery records receiver name and an OTP proof; failed attempts record a structured reason, driving re-attempts and return-to-origin after the third failure.
• COD ledger and reconciliation: collections accumulate per agent; each bank deposit references the entries it covers; the daily shortage (collected − remitted) is reported.
• Account billing: per-account rate contracts, monthly invoices and CSV exports.

Feasibility Study

• Technical: the open-source stack runs on a modest desktop; PostgreSQL transactions and unique indexes provide the AWB and COD guarantees.
• Economic: zero licence cost; the system replaces nightly tallying and month-end bill assembly.
• Operational: the clerk's screen mirrors the counterfoil, the run sheet prints A4, and training is a morning. Seed data with one deliberate remittance gap makes the viva demonstration realistic.

In scope

• One franchise location of a fictional national network, four roles (manager, counter clerk, delivery agent, accounts staff) and one seeded month of operations.
• Booking with rate computation, AWB generation, scan-event tracking, run sheets, POD, undelivered handling, COD ledger and remittance reconciliation, account-customer billing and CSV reports.
• Inward handling of hub returns (RTO) so returned parcels close the loop at the counter.

Out of scope (v1)

• The network's own hub-to-hub linehaul tracking (the franchise records scans it can see; a network API is assumed for hub scans and simulated in the seed).
• Payment-gateway or UPI collection of freight — payments are recorded with a reference number.
• GPS tracking of agents and map-based routing; a customer mobile app (the tracking page is responsive instead).
• Multi-franchise deployment and integration with the network's central billing system.
• Storage of government identity numbers (Aadhaar, PAN) for KYC — shippers are referenced by name, phone and an internal code.

The project follows an iterative and incremental SDLC model over 14 weeks, with a demo to the guide every two weeks. Rating and the COD ledger are built first because they carry the domain risk; the two-member team split the work as Member A (back end: rate engine, AWB, COD ledger, billing) and Member B (front end: booking screens, tracking, run sheets, dashboards), reviewing each other's modules.

Testing approach: Vitest unit tests for the rate engine (slab boundaries at exactly 500 g multiples, volumetric-vs-actual comparison, surcharge stacking), the check digit and the shortage computation; Supertest integration tests for booking, status transitions (illegal transitions rejected), OTP proof and remittance overlap cases; a concurrency test that fires parallel bookings to prove no duplicated AWB; black-box test-case tables per screen; and a user-acceptance walkthrough of one agent's day from run sheet to remittance.

TrackParcel is a three-tier application with all business rules on the server.

1. Presentation tier: a React SPA (Vite, Tailwind CSS) with role-aware routing — the clerk's booking console, the agent's run-sheet screen (mobile-first), the accounts officer's reconciliation view and the manager's dashboard all talk to one REST API. A public tracking page requires no login, only the AWB number.
2. Application tier: Express routes pass through JWT verification and role middleware into services — the rate engine, the AWB generator, the status-transition guard, the run-sheet builder and the reconciliation job. Zod validates every payload before it touches SQL.
3. Data tier: PostgreSQL with a strict schema: foreign keys, check constraints on states and amounts, and unique indexes where business rules must hold (one active run-sheet item per consignment, unique AWB, unique remittance-item pair). Rate tables are versioned with effective dates; scan events are append-only.

flowchart LR
  CL["Counter clerk"] -->|"booking forms"| API["Express REST API"]
  AG["Delivery agent (phone)"] -->|"run sheet, POD, COD handover"| API
  AC["Accounts staff"] -->|"remittances, billing"| API
  MG["Manager"] -->|"dashboards, rates"| API
  CU(["Customer (public)"]) -->|"AWB number"| TRK["Tracking page"]
  API --> MW["JWT verify + role middleware"]
  MW --> RE["Rate engine (versioned tables)"]
  MW --> AWB["AWB generator: sequence + mod-7 check digit"]
  MW --> SG["Status-transition guard"]
  SG --> SE[("scan_events: append-only")]
  RE --> PG[("PostgreSQL 16")]
  AWB --> PG
  SE --> PG
  MW --> COD["COD ledger + reconciliation job"]
  COD --> PG
  API --> CSV["CSV exports"]
  TRK --> SE

ER diagram (core entities)

erDiagram
  USER ||--o{ SCAN_EVENT : records
  SHIPPER ||--o{ CONSIGNMENT : sends
  CONSIGNMENT ||--o{ SCAN_EVENT : "has"
  CONSIGNMENT ||--o| DELIVERY_PROOF : "ends in"
  RUN_SHEET ||--o{ RUN_SHEET_ITEM : contains
  CONSIGNMENT ||--o| RUN_SHEET_ITEM : "appears on"
  CONSIGNMENT ||--o| COD_ENTRY : "may carry"
  COD_ENTRY ||--o| REMITTANCE_ITEM : "settled by"
  REMITTANCE ||--o{ REMITTANCE_ITEM : contains
  ACCOUNT_CUSTOMER ||--o{ CONSIGNMENT : books
  ACCOUNT_CUSTOMER ||--o{ INVOICE : "is billed"

  USER {
    string id PK
    string email UK
    string passwordHash
    string role
  }
  SHIPPER {
    string id PK
    string name
    string phone
    string internal_code UK
    boolean is_account
  }
  CONSIGNMENT {
    string id PK
    string awb UK
    string shipper_id FK
    string service_type
    string origin_zone
    string dest_zone
    decimal actual_weight_g
    decimal chargeable_weight_g
    decimal freight
    decimal cod_amount
    string status
  }
  SCAN_EVENT {
    string id PK
    string consignment_id FK
    string status
    string location
    datetime scanned_at
    string actor_id FK
  }
  RUN_SHEET {
    string id PK
    string agent_id FK
    date run_date
  }
  DELIVERY_PROOF {
    string consignment_id PK
    string receiver_name
    string otp_reference
    datetime delivered_at
  }
  COD_ENTRY {
    string consignment_id PK
    decimal collected_amount
    string agent_id FK
    string state
  }
  REMITTANCE {
    string id PK
    string agent_id FK
    date deposit_date
    decimal amount
    string bank_reference
  }
  REMITTANCE_ITEM {
    string remittance_id FK
    string cod_entry_id FK
  }
  INVOICE {
    string id PK
    string account_id FK
    string month
    decimal total
  }

Consignment status flow

flowchart TD
  B["Booked"] --> P["Picked up"]
  P --> T["In transit (hub scan)"]
  T --> OFD["Out for delivery"]
  OFD -->|"OTP verified"| D["Delivered + POD"]
  OFD -->|"reason recorded"| A1["Attempted 1"]
  A1 -->|"re-attempt"| OFD
  A1 --> A2["Attempted 2"]
  A2 -->|"re-attempt"| OFD
  A2 -->|"third failure"| RTO["Return to origin"]
  RTO --> RI["Returned to shipper (counter)"]
  D -->|"COD parcel"| CL["COD collected → ledger"]

DFD level 1

flowchart TD
  CL(["Clerk"]) --> P1["1.0 Book & rate consignment"]
  P1 <--> D1[("D1 Rate tables")]
  P1 <--> D2[("D2 Consignments")]
  AG(["Agent"]) --> P2["2.0 Execute run sheet & record POD"]
  P2 <--> D3[("D3 Scan events / proofs")]
  P2 --> P3["3.0 Record COD collection"]
  P3 <--> D4[("D4 COD entries")]
  AC(["Accounts"]) --> P4["4.0 Record bank remittance"]
  P4 <--> D5[("D5 Remittances")]
  D4 --> P5["5.0 Reconcile & report shortage"]
  D5 --> P5
  P5 --> MG(["Manager"])
  D2 --> P6["6.0 Build account invoices"]
  P6 --> D6[("D6 Invoices")]

Privacy is a stated design stance: shippers and consignees are stored by name, address and phone only — no Aadhaar, PAN or other government identity numbers — and the public tracking page exposes status and city, never the full address.

• UPI collection of freight and COD through a payment-gateway sandbox, collapsing the cash-handling problem the COD ledger manages by hand.
• Customer self-service portal for account shippers: bulk CSV booking, label printing and invoice download.
• Network hub API integration (or a queue-based simulator) so hub scans arrive without manual entry.
• Agent route planning on the run sheet with stop ordering and distance-based ODA validation.
• Analytics: service-type-wise profitability, delivery-success trends per area, and rate-simulation what-ifs before a network revises its tariff.
• WhatsApp status notifications triggered by scan events, with delivery OTP sent to the consignee's phone.