TL;DR — Assigning a scarce physical resource (a rider) to work is a concurrency problem, not a routing problem. Two requests that both read “this rider has capacity” before either writes will both assign — and you have double-booked a person. The fix is to make the read and the write one atomic step:
SELECT ... FOR UPDATEinside a transaction, so the second request blocks until the first commits and then re-reads the truth. On top of that, enforce an explicit state transition map so an order can never skip frompendingstraight todelivered, and store proof-of-delivery in JSONB so the evidence schema can evolve without a migration. Validate capacity in application code and you are validating a value that is already stale.
Building a logistics and last-mile delivery dispatcher involves more than calculating routes or sending SMS updates. In the real world, you are managing scarce physical resources (riders) operating under strict constraints (weight/capacity limits) in real-time.
When multiple orders enter the system at peak hours, a logistics backend must guarantee that:
- Riders are never assigned orders exceeding their physical carrying capacity.
- A rider is never double-booked onto multiple concurrent delivery batches.
- Orders move through a strict, deterministic state progression.
Below is an engineering walkthrough on how we built a concurrency-safe delivery dispatch engine in TypeScript using PostgreSQL row-level locking.
1. Concurrency Control: Locking with SELECT ... FOR UPDATE
See the PostgreSQL documentation on explicit row-level locking for the exact semantics of each lock strength.
When assigning a batch of orders to a rider, the system must check the rider’s active load to ensure the additions do not exceed their maximum capacity limit.
In a standard Node.js environment, querying the database to calculate current load, checking the limits in application memory, and then issuing an UPDATE leaves a gap for race conditions. If two different order managers attempt to assign batches to the same rider simultaneously, both operations might read the rider’s load as “available” before either write completes, resulting in over-allocation.
To solve this, we use PostgreSQL row-level locking (SELECT ... FOR UPDATE) inside an explicit transaction block.
It is the lock doing this, not the isolation level. Under Read Committed — the Postgres default — an unlocked SELECT gives each transaction its own snapshot, so both readers happily see the same spare capacity. Taking the row lock is what forces the second transaction to wait and then re-read committed truth.
import { PoolClient } from 'pg';
interface RiderAssignmentResult {
success: boolean;
message: string;
}
async function assignRiderToBatch(
client: PoolClient,
batchId: string,
riderId: string
): Promise<RiderAssignmentResult> {
try {
// Start transaction boundary
await client.query('BEGIN');
// 1. Lock the rider row for modification
const riderRes = await client.query(
`SELECT rider_id, availability_status, max_capacity, type
FROM delivery_riders
WHERE rider_id = $1
FOR UPDATE`,
[riderId]
);
if (riderRes.rowCount === 0) {
throw new Error('RIDER_NOT_FOUND');
}
const rider = riderRes.rows[0];
if (rider.availability_status !== 'AVAILABLE') {
throw new Error('RIDER_UNAVAILABLE');
}
// 2. Fetch order count in the proposed batch
const batchRes = await client.query(
`SELECT count(*) as order_count, status FROM delivery_batches WHERE batch_id = $1`,
[batchId]
);
const batch = batchRes.rows[0];
const incomingOrderCount = parseInt(batch.order_count, 10);
if (batch.status !== 'CREATED') {
throw new Error('INVALID_BATCH_STATUS');
}
// 3. Calculate rider's current active order load
const activeLoadRes = await client.query(
`SELECT COALESCE(SUM(order_count), 0) as current_load
FROM delivery_batches
WHERE assigned_rider_id = $1 AND status IN ('ASSIGNED', 'PICKED', 'OUT_FOR_DELIVERY')`,
[riderId]
);
const currentLoad = parseInt(activeLoadRes.rows[0].current_load, 10);
// 4. Validate capacity constraints
if (currentLoad + incomingOrderCount > rider.max_capacity) {
throw new Error('CAPACITY_EXCEEDED');
}
// 5. Update batch and assign rider
await client.query(
`UPDATE delivery_batches
SET assigned_rider_id = $1, status = 'ASSIGNED', assigned_at = NOW()
WHERE batch_id = $2`,
[riderId, batchId]
);
// 6. Log assignment audit log
await client.query(
`INSERT INTO rider_assignment_history (batch_id, new_rider_id, reason)
VALUES ($1, $2, $3)`,
[batchId, riderId, 'System Auto-Dispatch']
);
await client.query('COMMIT');
return { success: true, message: 'Rider assigned successfully' };
} catch (error: any) {
await client.query('ROLLBACK');
return { success: false, message: error.message || 'Transaction failed' };
}
}The Locking Mechanics
When the transaction runs SELECT ... FOR UPDATE on the target rider, Postgres places an exclusive write lock on that specific row. Any other concurrent transaction attempting to read or update that same rider row is forced to queue until the first transaction calls COMMIT or ROLLBACK. This guarantees that capacity limits are computed against deterministic, sequential state.
2. Enforcing State Transitions via State Machines
A delivery batch must follow a strict path to prevent “orphaned” orders or invalid logic (like marked delivered before they are picked up).
The lifecycle follows this state map:
[CREATED]
│
▼ (Rider Assigned)
[ASSIGNED]
│
▼ (Rider Picked Up Order)
[PICKED]
│
▼ (Transit Started)
[OUT_FOR_DELIVERY]
┌────┴────────────────────────┐
▼ (Successful Delivery) ▼ (Delivery Failed)
[COMPLETED] [FAILED]To enforce this, we build a transition map in TypeScript that acts as our guardrail:
type BatchStatus = 'CREATED' | 'ASSIGNED' | 'PICKED' | 'OUT_FOR_DELIVERY' | 'COMPLETED' | 'FAILED' | 'CANCELLED';
const VALID_TRANSITIONS: Record<BatchStatus, BatchStatus[]> = {
CREATED: ['ASSIGNED', 'CANCELLED'],
ASSIGNED: ['PICKED', 'CREATED', 'CANCELLED'], // Can reassign back to CREATED
PICKED: ['OUT_FOR_DELIVERY', 'FAILED'],
OUT_FOR_DELIVERY: ['COMPLETED', 'FAILED'],
COMPLETED: [], // Terminal State
FAILED: [], // Terminal State
CANCELLED: [] // Terminal State
};
function validateStateTransition(current: BatchStatus, next: BatchStatus): boolean {
const allowed = VALID_TRANSITIONS[current];
return allowed ? allowed.includes(next) : false;
}By verifying transitions before executing any database changes, the engine blocks invalid states at the API boundaries. For example, if a client tries to patch the status of a CREATED batch to OUT_FOR_DELIVERY, the application throws a 400 Bad Request error immediately.
3. Storing Rich Tracking Data with JSONB Proof of Delivery
When a driver completes a delivery, the app captures rich proof of delivery (PoD): photo URLs (uploaded to S3), coordinate telemetry, customer signatures, notes, and timestamps.
Instead of writing a rigid relational schema with child tables for tracking metadata, we opted for a Postgres JSONB column on our batch order mapping table.
CREATE TABLE delivery_batch_orders (
batch_order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
batch_id UUID REFERENCES delivery_batches(batch_id),
order_id UUID REFERENCES orders(order_id),
sequence INTEGER NOT NULL,
status VARCHAR(50) DEFAULT 'PENDING',
-- JSONB document holding unstructured proof of delivery
proof_of_delivery JSONB NULL,
completed_at TIMESTAMP WITH TIME ZONE
);Why JSONB instead of structured relational columns?
JSONB stores a decomposed binary form rather than raw text, so it is queryable and indexable — which is what makes this a real modelling choice rather than a shortcut around writing a migration.
- Dynamic Metadata: Different delivery types require different proofs. A corporate drop-off might require a text note (“Left with receptionist”), while a residential drop-off requires an S3 photo link and GPS bounds. JSONB handles this schema variance without migrations.
- Reduced Join Overhead: When fetching delivery details for the administration portal, the application reads a single row without executing expensive relational joins to retrieve metadata.
- Indexing Support: Postgres allows indexing JSONB fields using GIN (Generalized Inverted Index) keys. If we need to search for deliveries verified within specific coordinates, we can index the JSONB path directly:
CREATE INDEX idx_pod_coordinates ON delivery_batch_orders USING GIN ((proof_of_delivery->'coordinates'));4. Real-Time Telemetry Updates
Delivery is highly interactive. Customers, outlet merchants, and platform dispatchers need to see location changes and status updates instantly.
To achieve this, we link our database transactions directly with a real-time event broker (Socket.IO), emitting into rooms so an update reaches exactly the clients entitled to it:
import { Server } from 'socket.io';
interface DeliveryEventPayload {
batchId: string;
status: BatchStatus;
outletId: string;
riderId?: string;
updatedAt: string;
}
class DeliveryNotificationService {
private io: Server;
constructor(io: Server) {
this.io = io;
}
public broadcastBatchStatus(payload: DeliveryEventPayload): void {
// 1. Notify the merchant outlet dashboard
this.io.to(`outlet:${payload.outletId}`).emit('delivery_batch_updated', payload);
// 2. Notify the specific rider's device
if (payload.riderId) {
this.io.to(`rider:${payload.riderId}`).emit('my_batch_updated', payload);
}
// 3. Notify the customer room tracking this order
this.io.to(`batch:${payload.batchId}`).emit('tracking_updated', {
status: payload.status,
timestamp: payload.updatedAt
});
}
}This pub/sub routing model ensures that the instant database row updates commit successfully, the web socket layer broadcasts the changes to the respective clients in sub-100ms.
Summary of Dispatcher Core Patterns
- Serialize Resource Allocation: Use row locks (
FOR UPDATE) inside isolated database transactions to prevent capacity overloading. - Defensive State Logic: Define a clear, immutable transitions map to enforce linear lifecycle paths on orders.
- Leverage Document Fields for Audits: Use
JSONBfor storing flexible metadata profiles, keeping reads fast and schema migrations minimal. - Link DB Events to Websockets: Keep clients synchronized by coupling transactional success hooks directly to your real-time notification brokers.
What actually broke in production
The lock was correct and the scope of it was not. My first version held the row lock for the whole assignment routine — including a call out to a mapping API to estimate the route. When that third-party call got slow one evening, every dispatch attempt queued behind a lock held by a request that was doing nothing but waiting on someone else’s server. Throughput collapsed without a single error being logged. Now anything that touches the network happens before the transaction opens; the locked section does arithmetic and writes, nothing else.
What I’d still improve
Capacity is currently a single number per rider. Real constraints are multi-dimensional — volume, weight, whether an order needs to stay hot — and I collapse them into one value that is right on average and wrong at the edges.
FAQ
How do you stop a rider being double-booked?
Take a row lock on the rider with SELECT ... FOR UPDATE inside a transaction, then do the
capacity check and the assignment before committing. The second request blocks until the
first commits and re-reads the real load. Checking capacity in application code checks a
number that is already stale.
Why isn’t a capacity check in application code enough? Because the read and the write are two separate operations with a gap between them. Two requests both read “has capacity”, both pass the check, and both assign. No amount of validation logic closes that gap — only holding a lock across the read and the write does.
Does a higher isolation level fix the double-booking? Serializable would, at the cost of retries on serialisation failures across your whole workload. An explicit row lock is the narrower tool: it constrains exactly the contended resource and leaves everything else on the default isolation level.
Why enforce order states with an explicit transition map? Because without one, “which status can follow which” is implied by whichever handlers happen to exist, and nothing prevents an order jumping from pending straight to delivered. A declared map is checkable, testable offline, and has exactly one place to change.
Why store proof of delivery as JSONB instead of columns? Because the evidence you capture changes — a photo today, a signature and geolocation next quarter — and each change would otherwise be a migration on a large table. JSONB stays queryable and indexable while letting the shape evolve.
Should the real-time broadcast be inside the transaction? No. Emit after the commit succeeds. Broadcasting inside the transaction means clients can be told about a state that then rolls back, and it holds the lock for the duration of a network call to a system that may be slow.
The one idea to take away
Hold the lock for the write, never for the wait. A row lock is a correctness tool with a throughput cost, and that cost is proportional to how long you hold it. Every concurrency bug I have shipped since has been one of two things: not taking the lock, or taking it and then doing something slow inside it.
Related: why the notification side lives in a Postgres outbox, the same atomic-update pattern applied to budgets, and what the real-time layer gets wrong.
