Designing Shohnaat Logistics: Double-Entry Financial Accounting & Event-Driven Courier Dispatch in PostgreSQL

Logistics platforms are fundamentally financial systems that move physical boxes. When dealing with millions in Cash-on-Delivery (COD) collections, merchant payouts, hub return charges, and delivery rider incentives, standard database updates like `user.balance += amount` will inevitably lead to financial drift and reconciliation nightmares.

For **Shohnaat Logistics**, I built a multi-tenant enterprise architecture engineered around an immutable double-entry bookkeeping ledger and distributed event queues.

## Why Traditional CRUD Fails in Courier Logistics

In standard CRUD, if a rider marks a ৳2,500 parcel as delivered and collects cash:
- The merchant ledger must be credited ৳2,500 minus COD fee.
- The rider cash-in-hand account must be debited ৳2,500.
- The company revenue account must be credited the service charge.

If any server crash or network hiccup occurs halfway through simple balance updates, the books will fail audit.

## Implementing Immutable Double-Entry Ledger in PostgreSQL

Every single cent that moves through Shohnaat is recorded as paired debit and credit journal entries:

```sql
-- Enforcing transaction balance at the database engine level
CREATE TABLE ledger_entries (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
transaction_id UUID NOT NULL REFERENCES transactions(id),
account_id UUID NOT NULL REFERENCES accounts(id),
direction VARCHAR(6) CHECK (direction IN ('DEBIT', 'CREDIT')),
amount NUMERIC(12, 2) NOT NULL CHECK (amount > 0),
created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Constraint verification function
CREATE OR REPLACE FUNCTION verify_transaction_balance()
RETURNS TRIGGER AS $$
DECLARE
balance NUMERIC;
BEGIN
SELECT COALESCE(SUM(CASE WHEN direction = 'DEBIT' THEN amount ELSE -amount END), 0)
INTO balance
FROM ledger_entries
WHERE transaction_id = NEW.transaction_id;

IF balance != 0 THEN
RAISE EXCEPTION 'Transaction unbalanced: debits must equal credits.';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
```

## High-Throughput Queue Processing with Redis & BullMQ

Parcels undergo frequent state transitions: `Consigned` → `Hub Received` → `In Transit` → `Out for Delivery` → `Delivered` or `Returned`.

Each scan triggers SMS dispatch to customers, merchant dashboard push updates via Server-Sent Events (SSE), and inventory tracking. Instead of executing these synchronously in HTTP route handlers, we offload all side effects to **BullMQ** running on Redis 7:

- Concurrency control per merchant ensures rate limits aren't violated.
- Automatic retry strategies with exponential backoff prevent dropped webhook notifications.
- Worker separation allows logistics scanning devices in physical warehouses to experience sub-50ms API response times.

This architecture handles peak holiday delivery rushes effortlessly without financial discrepancy.