How I Built a Civic Traffic App on DynamoDB + Vercel — ChowkChakra (#H0Hackathon)

omkar

Posted on Jun 29 • Edited on Jul 1

The Problem I Was Trying to Solve

The numbers above are the everyday reality in Pune. Officers manage dozens of junctions with zero live data. Citizens spot broken signals, congestion, or accidents — but have nowhere to formally report it. WhatsApp groups, Twitter complaints, helpline calls that log nothing.

I wanted to close that loop. ChowkChakra is the result: a two-interface system where citizens report problems in real time, officers act on them through a command center, and citizens get notified when the issue is actually resolved — with a verification mechanism to confirm it.

The name: Chowk = intersection in Marathi/Hindi. Chakra = cycle. The cycle from report → officer action → citizen verification → resolution.

The Stack

• Frontend/backend: Next.js 16 (App Router), deployed on Vercel
• Database: Amazon DynamoDB — primary data store (10 tables), AWS SDK v3; Amazon Aurora PostgreSQL for RAG/vector search (pgvector)
• Storage: AWS S3 for media uploads (photos, videos)
• AI: Google Gemini 2.5 Flash (text + vision classification via REST API)
• Maps: Mapbox GL JS
• Live data: Google Maps Routes API (jam factor), Open-Meteo (weather penalty)
• Email: Resend
• Push notifications: Web Push API / VAPID

Why DynamoDB?

This was a deliberate call, not a default.

ChowkChakra is fundamentally a high-throughput write system with mostly key-based reads. Citizens submitting reports need instant acknowledgment — no joins, no query planning. Officers querying a junction’s tags need single-digit millisecond reads, not a SELECT ... JOIN that can degrade under concurrent load.

The access patterns are well-defined and mostly PK-based. That’s the DynamoDB sweet spot.

Here’s what I actually did with it:

10 tables, each with a purpose

cc-junctions          → junction registry + risk scores
cc-tags               → active incident tag aggregates + verification lists
cc-tags-history       → resolved/archived tags (cold table)
cc-reports            → raw citizen report submissions (TTL: 90 days)
cc-push-subscriptions → push subscriber endpoints (TTL: 90 days)
cc-sessions           → user session cookies (TTL: 24 hours)
cc-media-queue        → async AI media processing log (TTL: 7 days)
cc-social-posts       → automated/manual X posts audit logs
cc-web-evidence       → scanned traffic news from online feeds
cc-citizens           → user credentials and profiles

Enter fullscreen mode Exit fullscreen mode

Separating cc-tags from cc-tags-history was the most important schema decision. The active tags table stays lean. Historical queries only touch the archive table.

Read-time SLA penalty (no background jobs)

Different incident types carry different SLA windows — accidents: 1 hour, signal failure: 24 hours, others: 48 hours.

Instead of running a cron that writes updated penalty scores to the database every few minutes, I calculate the penalty at read-time. When a junction is queried, I check currentTime > slaDeadline and add a flat +15 penalty to the chainRiskScore on the fly.

Zero write overhead. The DB stays clean. The overdue status is always accurate because it’s computed from the actual current time, not a batch job’s last run.

Atomic updates with TransactWriteCommand

When an officer resolves a tag, we update the status to VERIFYING and recalculate the junction’s overall risk score. We run these operations atomically using a TransactWriteCommand so the tag state and junction statistics never diverge. If any one operation fails, the whole transaction rolls back.

GSI for ward-level queries

The dashboard allows filtering junctions by ward. Instead of scanning the entire cc-junctions table and filtering client-side, I set up a GSI (ward-score-index) with ward as the partition key and chainRiskScore as the sort key. Each ward gets its own query, sorted by risk score descending. No table scan.

DynamoDB TTL for automatic cleanup

Instead of writing cron jobs to expire stale data, I set TTL on six tables:

• cc-sessions: 24 hours (user sessions)
• cc-media-queue: 7 days (async media processing queue)
• cc-web-evidence: 30 days (scanned news articles)
• cc-reports: 90 days (raw report submissions)
• cc-push-subscriptions: 90 days (push endpoints)
• cc-social-bot-config: 1 year (bot configuration)

DynamoDB purges these automatically. No Lambda, no scheduled job, no ops overhead.

System Architecture

The flow is straightforward: the Citizen PWA and Officer Dashboard both talk to Next.js API routes deployed on Vercel. Those routes read and write to the primary transactional store, sync vectors to Aurora for AI chat, and call external APIs as needed.

Primary transactional store: Amazon DynamoDB (10 tables)

• cc-junctions — junction registry + risk scores (GSI ward-score-index for ward filtering)
• cc-tags — active incident tag aggregates + verification votes
• cc-tags-history — archived resolved tags
• cc-reports — raw citizen report submissions (TTL: 90 days)
• cc-push-subscriptions — push subscriber endpoints (TTL: 90 days)
• cc-sessions — user session cookies (TTL: 24 hours)
• cc-media-queue — async AI media queue log (TTL: 7 days)
• cc-social-posts — bot/manual X posts audit logs
• cc-web-evidence — scanned traffic news from web feeds
• cc-citizens — user profiles

Vector search: Amazon Aurora PostgreSQL (pgvector)

• reports — vector-embedded citizen incident reports for AI chat RAG
• web_evidence — vector-embedded news articles for AI chat RAG

External services

• AWS S3 — citizen photo/video uploads
• Google Gemini 2.5 Flash — transcription, classification, chat RAG, insights
• Google Gemini Live API — real-time voice stream via a Python FastAPI relay on Render
• Mapbox — maps and geocoding
• Google Maps Routes API — live jam factor
• Open-Meteo API — weather-based risk penalty
• Resend — email dispatch PDF to assigned officer
• Web Push API / VAPID — citizen push notifications for verification loop

Key DynamoDB access patterns

• Load active tags for a junction → Query on cc-tags by junctionId
• Filter junctions by ward + risk → GSI ward-score-index (ward PK, chainRiskScore SK)
• Atomic tag resolve / update → TransactWriteCommand across cc-tags + cc-junctions
• Atomic archive on consensus → TransactWriteCommand moves resolved tag to cc-tags-history and deletes active
• Auto-purge stale data → DynamoDB TTL on sessions, reports, push subscriptions, media queue

The Citizen PWA

The citizen-facing PWA has four main sections: live map & search, smart reporting (Commute Mode + Manual Report), AI parsing with 200m deduplication, and the closed-loop verification vote.

When an officer resolves a tag, it transitions to VERIFYING. Contributors get push notifications and a verification card in their Alerts tab. Once at least 3 votes come in, a majority Fixed vote archives the tag to cc-tags-history via TransactWriteCommand. A majority Still Broken vote reopens it and adds a +15 risk penalty.

The Officer Command Center

The officer dashboard has five sections: Live Heatmap & KPIs, Reports Review, Analytics Dashboard, Chakra AI Assistant, and Social Bot Controls. Ward filters use the ward-score-index GSI; live risk factors pull from Google Maps Routes API and Open-Meteo; AI chat combines DynamoDB function calling with RAG semantic search over Aurora PostgreSQL.

What I Learned About DynamoDB

The biggest shift: design for access patterns, not for storage.

In a relational database, you design tables to represent entities and add indexes later. In DynamoDB, you start with the question “how will this data be queried?” and design the keys around the answer.

Every design decision — separating hot tags from cold history, utilizing the ward-score-index GSI, and using list arrays on tags for vote deduplication — came from mapping out the access patterns first.

The TTL feature in particular is underrated. For any data with a natural expiry (sessions, reports, push subscriptions), TTL is just free cleanup. The only cost is setting the attribute at write time.

What’s Next

• Multi-city support: The junction registry and ward system are parameterized. Adding Nashik or Mumbai means importing junction data, not rewriting code.
• Predictive risk scoring: Right now the Gridlock Risk Score is computed from active tag counts and types. Historical pattern analysis would allow predictive alerts.
• PMC SCOOT/ATCS integration: Pune’s Smart City project has signal controller data. Feeding that in would make the risk score much more precise.

If you have questions about the DynamoDB schema design, the Gemini Live API streaming implementation, or the citizen verification loop — drop them in the comments. Happy to go deeper on any of it.

This post was created as part of my submission to the H0 Hackathon — #H0Hackathon