How PostGIS and DynamoDB Streams enable real-time delivery hubs

In the United States alone, failed first-attempt deliveries cost carriers an estimated $1.5 billion annually. When a driver scans a label as "attempted" without a signature, the package often circles back days later—if it returns at all. Customers lose visibility, drivers waste time on repeat trips, and businesses absorb the overhead. A new platform built for the H0: Hack the Zero Stack hackathon is flipping that model by turning local businesses into micro-hubs that reroute packages in real time, on demand.

Hold·My·Package transforms corner stores, dry cleaners, and coworking spaces into neighborhood delivery hubs. When a carrier can’t complete a drop, the system instantly locates the nearest hub with capacity, notifies the homeowner, and schedules a redelivery at their convenience. Behind the scenes, the platform combines PostGIS for spatial routing and DynamoDB Streams for event-driven notifications—delivering intelligence and responsiveness without external services.

Solving the last-mile gap with spatial data

Failed deliveries aren’t just an operational headache; they’re a customer-experience failure. Current solutions like pickup lockers force customers to travel to retrieve packages, adding friction and waiting time. The alternative—letting packages sit unclaimed at a carrier depot—leaves both sides in the dark.

The platform addresses this with a four-portal system:

• Hub operators intake packages, dispatch geo-optimized batches, and monitor performance analytics.
• Homeowners schedule redeliveries and receive real-time push notifications.
• Carriers view rerouting statistics and cost savings per route.
• Network admins oversee multiple hubs across a city from a unified dashboard.

Built on Next.js and Vercel, the frontend and API layer routes requests efficiently. But the real intelligence lives in the database stack: Aurora PostgreSQL Serverless v2 handles relational state, while DynamoDB manages high-throughput events.

Separate databases, unified logic

The stack splits responsibilities cleanly. Aurora PostgreSQL serves as the source of truth for users, hubs, delivery schedules, and spatial data. With PostGIS, the database performs spatial queries natively—no external geocoding APIs or third-party services. Row-Level Security isolates tenant data, and materialized views pre-compute analytics for dashboards.

DynamoDB, in contrast, owns the event stream. Every status change—received, scheduled, dispatched, delivered—generates an immutable event. These events power four distinct views:

• A package timeline for tracking history
• A homeowner feed showing all activity
• A live hub dashboard updating in real time
• A carrier’s daily report

The system writes up to 1,000 events per second during peak hours. DynamoDB’s single-table design, Global Secondary Indexes, Streams, and TTL manage this load without additional infrastructure.

PostGIS: The spatial engine under the hood

Three core spatial operations run entirely in SQL, reducing application complexity and latency.

Finding the nearest available hub

When a carrier reports a failed delivery, the system runs a single PostGIS query to locate the closest hub with capacity:

SELECT id, name, ST_Distance(location, ST_MakePoint(-104.99, 39.74)::geography) as distance_m 
FROM hubs 
WHERE ST_DWithin(location, ST_MakePoint(-104.99, 39.74)::geography, coverage_radius_m) 
  AND current_load < capacity 
ORDER BY distance_m 
LIMIT 1;

The query filters by coverage radius, checks capacity, and ranks results by distance—all in one round trip.

Clustering packages into efficient routes

Before dispatching, the system groups nearby scheduled packages into batches using ST_ClusterDBSCAN:

SELECT ST_ClusterDBSCAN(p.delivery_address::geometry, eps := 500, minpoints := 2) OVER() as cluster_id,
       p.id, p.tracking_number, hp.address
FROM packages p 
JOIN delivery_schedules ds ON ds.package_id = p.id
JOIN homeowner_profiles hp ON hp.id = p.homeowner_id
WHERE p.hub_id = $1 
  AND p.status = 'scheduled'
  AND ds.scheduled_window_start BETWEEN $2 AND $3;

Operators see clusters like "3 packages on Market St—one batch, one trip" without needing external routing software. The database becomes the routing engine.

Enforcing coverage boundaries

Homeowners are automatically assigned to hubs based on proximity using the same ST_DWithin logic. Failed deliveries trigger the same spatial query to reroute to the nearest valid hub—consistent, fast, and reliable.

DynamoDB: Event-driven architecture at scale

A single DynamoDB table supports four access patterns through composite keys and Global Secondary Indexes.

Key structure:

• PK: PKG#{package_id}
• SK: EVENT#{timestamp}#{event_type}

With three GSIs:

• GSI1 (HomeownerFeed): homeowner_id + timestamp
• GSI2 (HubFeed): hub_id + timestamp
• GSI3 (CarrierDaily): carrier#date + timestamp

This design allows:

• Querying a full event timeline for a package via the main table
• Fetching a homeowner’s feed across all packages via GSI1
• Viewing a hub’s live operations via GSI2
• Generating a carrier’s daily report via GSI3

Status transitions use conditional writes to prevent race conditions:

ConditionExpression: 'attribute_not_exists(PK) AND attribute_not_exists(SK)'

If two concurrent requests try to write the same event, one succeeds and the other receives a ConditionalCheckFailedException. The app handles the conflict gracefully without distributed locks.

DynamoDB also sets a TTL of 90 days on each event. Items expire automatically, eliminating the need for scheduled cleanup jobs.

Real-time pipeline: Where databases meet users

The magic happens when both databases collaborate. An API call updates a package’s status in Aurora with optimistic locking. Simultaneously, the same call appends an event to DynamoDB. The write triggers a DynamoDB Stream, which invokes a lightweight Lambda function.

The Lambda—just 5KB with zero dependencies—signs Pusher requests using Node.js’s built-in crypto module and broadcasts updates to all connected clients. Within milliseconds, homeowners see "Package rerouted to Main St Hub," hub operators refresh their dashboard, and carriers review their revised route.

This architecture delivers sub-second responsiveness across thousands of concurrent users without dedicated message brokers or external queues. The databases do the heavy lifting, and the platform scales with demand.

The future of neighborhood logistics

Real-time rerouting isn’t just a feature—it’s a paradigm shift for last-mile delivery. By embedding spatial intelligence in the database and event processing in a managed stream, the platform reduces failed attempts, lowers carrier costs, and improves customer trust—all without new infrastructure. As more local businesses adopt micro-hubs, the model could reshape urban logistics, turning every corner store into a potential delivery node. The next step? Integrating predictive analytics to preempt reroutes before failures occur, turning reactive systems into proactive networks.