Voice2Query

AI-powered Speech-to-SQL for interactive database exploration Final project — Data Management, 2026 @ UNINA

Voice2Query lets a non-technical user query a PostgreSQL database using natural spoken language. It applies the concept to a satellite-based infrastructure and climate-risk monitoring domain: sites, satellite acquisitions, change detections, and risk alerts, modeled after the kind of geospatial analytics platform a company like Latitudo 40 might operate.

A spoken (or typed) question is transcribed, translated into SQL by an LLM with schema-aware prompting, executed safely against the database, and the results are shown alongside a map and — when available — a live satellite image of the relevant site.

---

1. Architecture

 Voice / text input
        |
        v
 [ ASR: faster-whisper ]  --transcribes-->  natural language question
        |
        v
 [ Text-to-SQL: Gemini + schema-aware prompt ]  -->  SQL query
        |
        v
 [ Query executor: read-only guard + parameterized execution ]
        |
        v
 [ PostgreSQL (Supabase) ]  -->  result rows
        |
        +--> table (Streamlit dataframe)
        +--> map (site coordinates, if site_id present in results)
        +--> live satellite image (Sentinel Hub, with static fallback)

Modules:

Folder	Responsibility
db/	Schema, seed data, query execution (with safety checks)
nlp2sql/	Schema-aware prompting and SQL generation via Gemini
asr/	Speech-to-text via faster-whisper
remote_sensing/	Live and static satellite imagery
pipeline/	Terminal-based end-to-end voice pipeline
dashboard/	Streamlit web UI
utils/	Shared formatting/theme helpers

---

2. Tech stack and why

Layer	Choice	Rationale
Database	PostgreSQL, hosted on Supabase (free tier, Transaction Pooler)	Real Postgres, no local install friction, web dashboard for inspection. Direct connection host is IPv6-only on the free tier — the Transaction Pooler (IPv4-compatible, port 6543) is required on networks without IPv6 egress.
Speech-to-Text	faster-whisper (small model, device="cpu", compute_type="int8", language="it" fixed)	Cross-platform requirement (developed on macOS, demoed on Windows) ruled out Apple-Silicon-specific implementations. faster-whisper is lighter and faster than openai-whisper with identical accuracy. Fixed Italian language avoids misdetection on short clips; explicit int8 quantization keeps CPU inference fast without material accuracy loss.
Text-to-SQL	Gemini (gemini-flash-latest) via google-genai	google-generativeai (originally planned) was found deprecated during implementation; switched to the actively supported SDK. The -latest alias tracks Google's recommended Flash model automatically, reducing the risk of breakage from model retirement (which had already happened once with gemini-1.5-flash during development).
Live microphone capture	sounddevice (terminal pipeline) / st.audio_input (dashboard)	sounddevice (PortAudio-based) is cross-platform for the CLI demo path. The Streamlit dashboard uses the native st.audio_input widget instead, since it handles browser-based recording without custom threading.
Dashboard	Streamlit	Fast to build an interactive web UI in pure Python; native audio input widget; simple to theme and deploy (Streamlit Community Cloud).
Satellite imagery	Sentinel Hub Process API (live, OAuth2 client-credentials) with static fallback	Live imagery is fetched for the actual site(s) returned by a query when possible; any failure (network, quota, no recent cloud-free scene) falls back silently to a pre-processed static image, so a live-demo failure never surfaces a raw error to the user.

---

3. Database schema

Eight tables, PostgreSQL:

• sites — monitored locations (name, type, coordinates, city/country)
• clients — organizations monitoring sites (PA, infrastructure managers, enterprises)
• site_clients — many-to-many bridge (site ↔ client), with contract dates
• satellites — data sources (Sentinel-2, Landsat-8, PlanetScope)
• satellite_acquisitions — individual image acquisitions per site/satellite/date
• change_detections — detected changes between two acquisitions (self-referencing FK: baseline + current acquisition), typed by change_type and severity
• operators — analysts who manage alerts
• risk_alerts — alerts generated from detections, with status lifecycle (open → acknowledged → resolved)

Constraint types used: PRIMARY KEY (single-column and composite), FOREIGN KEY with three delete behaviors (CASCADE, RESTRICT, SET NULL), CHECK (enum-style, numeric range, and multi-column table-level), UNIQUE, NOT NULL, DEFAULT.

Seed data: 18 real Italian sites (hydrogeological-risk and infrastructure locations), synthetically generated acquisitions/detections/alerts with realistic, non-uniform severity distributions (via db/seed_data.py).

Example queries

The query set demonstrates joins, aggregation/GROUP BY, subqueries with HAVING, and window functions (ROW_NUMBER() OVER (PARTITION BY ...)) — see db/ for the full set and results.

---

4. Security

• Parameterized queries everywhere (%s placeholders via psycopg2) — never string-concatenated SQL, preventing SQL injection in both hand-written and generated queries.
• Read-only enforcement: db/query_executor.py rejects any LLM-generated query that doesn't start with SELECT/WITH.
• Statement-stacking protection: queries containing more than one ;-delimited statement are rejected, since "starts with SELECT" alone doesn't prevent a concatenated destructive command (e.g. SELECT ...; DROP TABLE ...;).
• Secrets: never hardcoded or committed. Local development uses .env (git-ignored); deployment uses Streamlit Community Cloud's Secrets manager. Sentinel Hub credentials use OAuth2 client-credentials (short-lived access tokens, not long-lived keys, cached in memory with expiry margin).

---

5. Methodology — required comparisons

5.1 Speech recognition: Whisper vs. Speechmatics

	Whisper (faster-whisper)	Speechmatics
Deployment	Local, fully offline once the model is downloaded	Cloud API
Cost	Free, open-source (MIT)	Paid, usage-based
Latency	Depends on local hardware; CPU inference with int8 quantization used here	Consistently low, optimized cloud infrastructure
Multilingual support	Broad (~99 languages), quality varies by language/dialect	Strong multilingual support with enterprise-grade tuning
Speaker diarization	Not built-in to the base pipeline used here	Native diarization support
Integration effort	Python package, no account/API key needed	Requires API key, network dependency

Choice made: faster-whisper, local. For this project, cost (zero), offline reliability during an oral exam demo (no dependency on a paid external service on the critical path), and adequate accuracy for short Italian voice commands outweighed Speechmatics' diarization and cloud-grade latency, neither of which is needed for a single-speaker query interface.

5.2 Text-to-SQL: custom Gemini prompting vs. WrenAI

WrenAI is an open-source "GenBI" (Generative BI) platform: natural-language questions are turned into SQL, charts, and dashboards through three components — a UI, an AI service that retrieves context from a vector database, and an engine that maps business terms to the underlying data source via a "Modeling Definition Language." It connects to 20+ data sources (including PostgreSQL), is self-hosted (Docker-based), and is designed so raw data is never sent to the LLM — only schema/metadata is used for semantic retrieval.

	WrenAI	This project (Gemini + custom prompt)
Setup	Docker-based, multiple services (UI, AI service, engine, vector DB)	Single Python module, one API key
Schema context method	Semantic layer with a dedicated modeling language, vector-retrieval of relevant context	Compact static schema description embedded directly in the prompt
Scope	Full GenBI: SQL + chart/dashboard generation, multi-source	SQL generation only, single PostgreSQL database
Best fit	Larger organizations with multiple data sources and recurring business-term ambiguity, where an explicit semantic layer earns its complexity	A small, single-schema project where the full schema fits comfortably in a prompt and a dedicated semantic layer would be disproportionate

Choice made: direct schema-aware prompting with Gemini. WrenAI's semantic-layer architecture is well-suited to large, multi-source enterprise data estates; for a single 8-table schema, embedding the schema directly in the prompt achieves comparable correctness with far less operational complexity, while still demonstrating the underlying schema-aware prompting technique WrenAI itself relies on.

---

6. Robustness notes

• ASR noise tolerance: tested with a deliberately garbled transcription ("Quali Sidi Hanno Alert Critici Ankora Perti" instead of "Quali siti hanno alert critici ancora aperti") — Gemini produced the identical correct SQL query, showing resilience to word-level transcription errors without a dedicated correction module (cf. DBATI, [5] in Related Work).
• Ambiguous questions: e.g. "Quali sono i siti più a rischio?" (no literal "risk" column) — the model inferred a reasonable proxy (unresolved alert count, weighted toward critical severity) rather than failing.
• Security boundary: destructive intents (e.g. "cancella tutti i siti con alert critici") are blocked at the execution layer regardless of what SQL the LLM produces, since the read-only/statement-stacking checks operate independently of model behavior.
• Known limitation: queries returning many distinct site_ids currently attempt live satellite image retrieval per site sequentially, which is slow and quota-inefficient for large result sets — a good candidate for future improvement (e.g. limit to the top result, or batch/cache).

---

7. Setup

python3 -m venv venv
source venv/bin/activate   # Windows: venv\Scripts\activate
pip install -r requirements.txt

Create .env with:

DATABASE_URL=postgresql://...   # Supabase Transaction Pooler connection string
GEMINI_API_KEY=...
COPERNICUS_CLIENT_ID=...
COPERNICUS_CLIENT_SECRET=...

Run the dashboard:

streamlit run dashboard/app.py

Run the terminal voice pipeline:

python pipeline/voice_pipeline.py

---

8. Deliverables checklist (per assignment)

• Relational database design (PostgreSQL, 8 tables, full constraint set)
• Meaningful SQL queries (joins, aggregation, subqueries, window functions)
• Speech-to-SQL pipeline (ASR → NL2SQL → execution)
• Interactive results dashboard (table, map, satellite imagery)
• Tool comparisons (ASR tools; WrenAI vs. custom approach)
• Discussion of cascaded vs. end-to-end architecture trade-offs (this project is cascaded; see Related Work [2][3][4] for end-to-end alternatives)
• Error-handling/robustness discussion

---

References

See the assignment's Related Work section [1]–[8] for the academic context (SpeechSQLNet, Wav2SQL, DBATI, Cyrus, and others) that motivated the architectural choices above.