{
  "$type": "site.standard.document",
  "bskyPostRef": {
    "cid": "bafyreib5nbotuj523y7hrfqpqahmmjtdryskz4fobp2xon6p34kxftwso4",
    "uri": "at://did:plc:25rdn5elo5izoxrmtis34zuk/app.bsky.feed.post/3mphbdjko2472"
  },
  "coverImage": {
    "$type": "blob",
    "ref": {
      "$link": "bafkreiafadygm2bccaon3bq7nkzpo5uwm27wcj7nl6bvsvca3wwcevudpa"
    },
    "mimeType": "image/webp",
    "size": 66248
  },
  "path": "/np_hacks_code/how-postgis-and-dynamodb-streams-power-a-real-time-delivery-hub-platform-5719",
  "publishedAt": "2026-06-29T19:37:04.000Z",
  "site": "https://dev.to",
  "tags": [
    "h0hackathon",
    "H0: Hack the Zero Stack",
    "View demo on Youtube →",
    "View on Devpost project →"
  ],
  "textContent": "_Built for the H0: Hack the Zero Stack hackathon with Vercel and AWS Databases._\n\n##  The Problem\n\nEvery 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.\n\nFrom 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.\n\nExisting 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?\n\nThat's what I built.\n\n##  What I Built\n\nHold·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.\n\nThe platform has four portals:\n\n  * **Hub Operators** — intake packages, dispatch geo-optimized batches, track analytics\n  * **Homeowners** — schedule redelivery, get real-time notifications\n  * **Carriers** — see rerouting stats and cost savings\n  * **Network Admins** — monitor multiple hubs across a city\n\n\n\nThe 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.\n\n##  Why Two Databases?\n\nThe two databases solve genuinely different problems that would be painful to force into one system.\n\n**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.\n\n**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.\n\nThe split is clean: Aurora owns state, DynamoDB owns events.\n\n##  PostGIS: Database-Layer Delivery Intelligence\n\nThree spatial operations power the core product logic — all running as SQL queries, not application code.\n\n###  Nearest-Hub Routing\n\nWhen a carrier reports a failed delivery, the system needs to find the closest hub with available capacity:\n\n\n\n    SELECT id, name,\n      ST_Distance(location, ST_MakePoint(-104.99, 39.74)::geography) as distance_m\n    FROM hubs\n    WHERE ST_DWithin(location, ST_MakePoint(-104.99, 39.74)::geography, coverage_radius_m)\n      AND current_load < capacity\n    ORDER BY distance_m\n    LIMIT 1;\n\n\nOne query. The database handles distance calculation, coverage boundary enforcement, and capacity filtering together.\n\n###  Batch Delivery Clustering\n\nThis is the feature that makes hub operators efficient. When it's time to dispatch, the system groups nearby deliveries into batches:\n\n\n\n    SELECT\n      ST_ClusterDBSCAN(p.delivery_address::geometry, eps := 500, minpoints := 2) OVER() as cluster_id,\n      p.id, p.tracking_number, hp.address\n    FROM packages p\n    JOIN delivery_schedules ds ON ds.package_id = p.id\n    JOIN homeowner_profiles hp ON hp.id = p.homeowner_id\n    WHERE p.hub_id = $1 AND p.status = 'scheduled'\n      AND ds.scheduled_window_start BETWEEN $2 AND $3;\n\n\n`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.\n\n###  Coverage Enforcement\n\nHomeowner 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.\n\n##  DynamoDB: Single-Table Design for Four Access Patterns\n\nOne table. Composite keys. Three Global Secondary Indexes. Four completely different query patterns served efficiently.\n\n###  Key Structure\n\n\n    PK: PKG#{package_id}\n    SK: EVENT#{timestamp}#{event_type}\n\n    GSI1 (HomeownerFeed):  homeowner_id + timestamp\n    GSI2 (HubFeed):        hub_id + timestamp\n    GSI3 (CarrierDaily):   carrier#date + timestamp\n\n\n**Query the main table** → full event timeline for a specific package.\n**Query GSI1** → homeowner's activity feed across all their packages.\n**Query GSI2** → hub operator's live view of everything happening at their hub.\n**Query GSI3** → carrier's daily report of all packages they routed.\n\n###  Conditional Writes\n\nStatus transitions use conditional writes to prevent conflicts:\n\n\n\n    ConditionExpression: 'attribute_not_exists(PK) AND attribute_not_exists(SK)'\n\n\nIf 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.\n\n###  TTL\n\nEvery event gets a TTL set to 90 days from creation. DynamoDB automatically deletes expired items. Built-in lifecycle management without cron jobs.\n\n##  The Real-Time Pipeline\n\nThis 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:\n\n\n\n    DynamoDB write → Stream (automatic) → Lambda → Pusher → All connected clients\n\n\n###  Lambda Function\n\nZero npm dependencies. 5KB of code. Reads Pusher credentials from environment variables and signs requests using native Node.js crypto. It:\n\n  1. Receives DynamoDB Stream records (batch of up to 10)\n  2. Extracts event data (package_id, status, homeowner, hub, carrier)\n  3. Determines which channels need the event (hub-{id}, homeowner-{id}, carrier-{name})\n  4. Publishes to Pusher REST API in batches\n\n\n\n###  Frontend Integration\n\nReact components subscribe to their relevant Pusher channel on mount. When an event arrives:\n\n  * Dashboard metric cards update without refetch\n  * Toast notification appears (\"Your package is on its way!\")\n  * Event feed prepends the new event with a slide-in animation\n  * Notification badge increments\n\n\n\nThe 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.\n\n##  Lessons Learned\n\nA few things that weren't obvious going in:\n\n**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.\n\n**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.\n\n**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.\n\n**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.\n\n##  Check out\n\nView demo on Youtube →\n\nOr View on Devpost project →\n\n_#H0Hackathon_",
  "title": "How PostGIS and DynamoDB Streams Power a Real-Time Delivery Hub Platform"
}