sherlock

Inspiration Exposing government corruption today requires journalists and watchdogs to manually cross-reference thousands of public records — contracts, campaign donations, business registrations — a process that can take weeks per investigation. We asked: what if an AI agent could traverse a knowledge graph of real government data and surface pay-to-play schemes, shell company networks, and conflicts of interest in seconds?

What it does Commons is an AI-powered investigative intelligence platform that autonomously detects corruption patterns in San Francisco government data. Users submit a query (e.g., "Investigate Recology contracts"), and an AI agent traverses a knowledge graph of 186K+ entities and 245K+ edges — spanning contracts, campaign finance, business registrations, people, companies, and addresses — to uncover red flags like donor-contractor overlap, contract concentration, shell company indicators, and address clustering. Results stream in real-time with interactive graph visualizations, a geographic globe view, structured findings with confidence scores, and a narrative briefing with evidence chains. It also supports anonymous tip submission with one-time-retrieval tokens for secure whistleblower intake.

How we built it The backend is a Python agent powered by Google Gemini Flash 2.0 with autonomous function calling — the LLM decides which graph query tools to invoke with no hardcoded investigation flow. The knowledge graph lives in SQLite (locally) and Turso/LibSQL (in production), built from SF's SODA open data API with fuzzy entity matching for deduplication. The frontend is a Preact + TypeScript SPA with D3.js force-directed graphs, Globe.gl geographic visualization, and Vis Network for entity exploration. Real-time streaming uses Server-Sent Events. Auth0 handles authentication with role-based access. The whole stack deploys to Render via a single blueprint.

Challenges we ran into Entity deduplication was a major challenge — company names appear in dozens of variations across datasets ("Recology Inc" vs "Recology, Inc." vs "RECOLOGY INC"). We solved this with fuzzy matching, name normalization, and stable hashed entity IDs. Running a 500MB SQLite database on Render's free tier (512MB RAM) required careful query optimization. Getting the AI agent to reliably conduct multi-hop graph traversals without hallucinating connections required extensive prompt engineering and tool design. Streaming SSE events from the agent through to the frontend while maintaining parseable JSON was trickier than expected.

Accomplishments that we're proud of Built a fully deployed, end-to-end investigative tool in a hackathon — not just a prototype 186K+ entities and 245K+ edges ingested from real SF government data AI agent autonomously adapts its investigation strategy per query Real-time streaming lets users watch the investigation unfold live Anonymous tip system with cryptographic one-time tokens (SHA-256 hashed, burned after retrieval) 20 end-to-end tests covering the full stack What we learned We learned that agentic AI shines when given well-designed tools over a structured knowledge graph — the LLM's reasoning ability combined with graph traversal is far more powerful than either alone. We also learned that entity resolution is the hardest part of building a knowledge graph from messy government data, and that SSE is a much simpler alternative to WebSockets for streaming AI responses.

What's next for sherlock Scale to all 50 US states and federal contract data (10M+ entities) Add Aerospike as the production graph database for sub-millisecond traversals at scale Integrate Overmind for LLM call tracing and prompt optimization Add collaborative investigation features for newsroom teams Build a public API so civic tech organizations can query the graph programmatically Partner with investigative journalism outlets for real-world deployments

Built With

• airbyte
• auth0
• d3.js
• globe.gl
• google-gemini-flash-2.0
• preact
• python
• render
• server-sent-events
• soda-api
• sqlite
• thefuzz
• truefoundry
• turso/libsql
• typescript
• vis-network
• vite