CivicOS

Citizens face a labyrinth of bureaucracy when navigating government departments. Traditional search relies on exact keywords, ignoring context. We needed a system capable of mapping a plain-text citizen complaint directly to the correct hierarchical government department, citing the relevant laws, and escalating the issue appropriately.

Instead of relying purely on expensive LLM calls, I designed a hybrid approach. The core mapping relies on recursive PostgreSQL Common Table Expressions (CTEs). This allows the system to traverse the hierarchical chain of accountability—from a local municipal worker up to the state department—in a single database query, maintaining strict regional mapping without hallucinations.

Handling complex recursive CTEs and vector similarity searches introduced significant latency. The initial search API took ~900ms to resolve. By implementing a Redis caching layer for frequent regional queries and using Kafka-based event streaming to decouple search analytics from the critical path, I reduced the response time to 27ms (a 97% improvement).