Built for the H0: Hack the Zero Stack hackathon with Vercel and AWS Databases.
The Problem
Every year, roughly 15% of last-mile deliveries fail on the first attempt. The driver shows up, nobody's home, there's no safe drop location, or the community is gated. The carrier tries again the next day — costing $5-15 per re-attempt. Multiply that across millions of packages and the cost is staggering.
From the homeowner's side, you track a package across the country only to see "delivery attempted" — then wait days for the next attempt or drive to a pickup point.
Existing solutions like pickup lockers require the customer to go to the package. But what if instead of making you drive to a locker, a local hub held your package and delivered it to your door — on your schedule?
That's what I built.
What I Built
Hold·My·Package is a B2B SaaS platform for neighborhood delivery hubs. Local businesses (dry cleaners, convenience stores, co-working spaces) operate as hubs. When a carrier can't deliver, the package routes to the nearest hub. The hub holds it and redelivers when the homeowner is actually home.
The platform has four portals:
Hub Operators — intake packages, dispatch geo-optimized batches, track analytics
Homeowners — schedule redelivery, get real-time notifications
Carriers — see rerouting stats and cost savings
Network Admins — monitor multiple hubs across a city
The stack: Next.js on Vercel (frontend + API), Aurora PostgreSQL Serverless v2 (PostGIS, Row-Level Security, materialized views), DynamoDB (single-table design, Streams, conditional writes), Lambda, and Pusher for real-time push.
Why Two Databases?
The two databases solve genuinely different problems that would be painful to force into one system.
Aurora PostgreSQL is the source of truth. Users, packages, hubs, delivery schedules, spatial data, analytics — all relational and all benefiting from PostgreSQL's extension ecosystem. PostGIS gives me spatial intelligence at the query layer. Row-Level Security gives me tenant isolation at the database layer. Materialized views give me pre-computed analytics.
DynamoDB handles the event stream. Every status change — package received, scheduled, dispatched, delivered — writes an event. These events need to be queried four different ways (package timeline, homeowner feed, hub live view, carrier report), written at high throughput, and automatically trigger real-time notifications. DynamoDB's single-table design with GSIs, Streams integration, and TTL makes this natural.
The split is clean: Aurora owns state, DynamoDB owns events.
PostGIS: Database-Layer Delivery Intelligence
Three spatial operations power the core product logic — all running as SQL queries, not application code.
Nearest-Hub Routing
When a carrier reports a failed delivery, the system needs to find the closest hub with available 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;
One query. The database handles distance calculation, coverage boundary enforcement, and capacity filtering together.
Batch Delivery Clustering
This is the feature that makes hub operators efficient. When it's time to dispatch, the system groups nearby deliveries into batches:
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;
ST_ClusterDBSCAN finds packages within 500 meters of each other and groups them. The operator sees "3 packages on Market St — one batch, one trip" without any external optimization service. The database is the intelligence layer.
Coverage Enforcement
Homeowner onboarding uses ST_DWithin to discover which hubs cover their address. The carrier webhook uses the same query to auto-route failed deliveries. One spatial primitive, multiple use cases.
DynamoDB: Single-Table Design for Four Access Patterns
One table. Composite keys. Three Global Secondary Indexes. Four completely different query patterns served efficiently.
Key Structure
PK: PKG#{package_id}
SK: EVENT#{timestamp}#{event_type}
GSI1 (HomeownerFeed): homeowner_id + timestamp
GSI2 (HubFeed): hub_id + timestamp
GSI3 (CarrierDaily): carrier#date + timestamp
Query the main table → full event timeline for a specific package.
Query GSI1 → homeowner's activity feed across all their packages.
Query GSI2 → hub operator's live view of everything happening at their hub.
Query GSI3 → carrier's daily report of all packages they routed.
Conditional Writes
Status transitions use conditional writes to prevent conflicts:
ConditionExpression: 'attribute_not_exists(PK) AND attribute_not_exists(SK)'
If two requests try to write the same event simultaneously, one succeeds and one gets a ConditionalCheckFailedException. No distributed locks needed. The application handles the conflict gracefully.
TTL
Every event gets a TTL set to 90 days from creation. DynamoDB automatically deletes expired items. Built-in lifecycle management without cron jobs.
The Real-Time Pipeline
This is where the two databases work together. Aurora handles the status transition (optimistic locking on a version column). The same API call writes the event to DynamoDB. Then the magic:
DynamoDB write → Stream (automatic) → Lambda → Pusher → All connected clients
Lambda Function
Zero npm dependencies. 5KB of code. Reads Pusher credentials from environment variables and signs requests using native Node.js crypto. It:
- Receives DynamoDB Stream records (batch of up to 10)
- Extracts event data (package_id, status, homeowner, hub, carrier)
- Determines which channels need the event (hub-{id}, homeowner-{id}, carrier-{name})
- Publishes to Pusher REST API in batches
Frontend Integration
React components subscribe to their relevant Pusher channel on mount. When an event arrives:
- Dashboard metric cards update without refetch
- Toast notification appears ("Your package is on its way!")
- Event feed prepends the new event with a slide-in animation
- Notification badge increments
The operator intakes a package → the homeowner sees it appear in their portal within 2 seconds. No refresh button. No polling. The architecture makes real-time the default behavior.
Lessons Learned
A few things that weren't obvious going in:
Aurora Data API can't serialize geography columns. SELECT * from a table with PostGIS geography columns throws UnsupportedResultException. The fix: use ST_Y(location::geometry) and ST_X(location::geometry) to extract lat/lng as floats instead of returning the raw geography value.
RLS with Data API requires transactions. SET LOCAL app.current_user_id doesn't persist between separate Data API calls. You need to wrap it in a transaction: BEGIN → set_config(...) → your query → COMMIT. Otherwise RLS policies silently do nothing.
Advisory locks prevent scheduling race conditions. Two homeowners booking the last delivery slot simultaneously? pg_advisory_xact_lock(hashtext(hub_id || slot_hour)) serializes the capacity check + insert within a transaction. The lock auto-releases on commit.
DynamoDB conditional write failures aren't errors. If a duplicate event write fails, that's fine — the event already exists. Log it, don't retry, don't block the response. Aurora is the source of truth for status; DynamoDB is the notification layer.
Check out
#H0Hackathon