Natural language queries on system logs, powered by AlloyDB AI's
generate_sql.
Users type plain English questions like "Which service is failing the most?" and the system:
- Converts the question to SQL using AlloyDB AI
- Executes the query against a realistic ops database
- Displays the generated SQL and results in a clean dashboard
┌──────────────┐ POST /api/query ┌──────────────┐ SQL ┌──────────────┐
│ │ ───────────────────────> │ │ ──────────> │ │
│ Browser │ │ Flask App │ │ AlloyDB │
│ (HTML/JS) │ <─────────────────────── │ (Python) │ <────────── │ PostgreSQL │
│ │ JSON response │ │ Results │ │
└──────────────┘ └──────────────┘ └──────────────┘
┌──────────────┐
│ AlloyDB AI │
│ generate_sql │
└──────────────┘
| Layer | Technology |
|---|---|
| Database | Google AlloyDB for PostgreSQL |
| AI | AlloyDB AI (google_ml.generate_sql) |
| Backend | Python 3.10+, Flask, psycopg2 |
| Frontend | Vanilla HTML/CSS/JS (no frameworks) |
alloydb/
├── sql/
│ ├── 001_schema.sql # Table definitions, indexes, constraints
│ ├── 002_seed_data.sql # Realistic ops data (100+ logs, 20 incidents)
│ ├── 003_alloydb_ai_setup.sql # AlloyDB AI extension + table/column descriptions
│ ├── 004_deployments.sql # Deployments table + seed data (8 deploys)
│ ├── 005_enhanced_seed.sql # Latency spikes, clustered errors, overlapping incidents
│ └── 006_alloydb_ai_deployments.sql # AlloyDB AI metadata for deployments
├── app/
│ ├── config.py # Environment variable loading
│ ├── db.py # DB connection + NL→SQL + insight engine + SQL formatting
│ ├── app.py # Flask routes and API
│ ├── seed.py # Database setup script
│ ├── templates/
│ │ └── index.html # Single-page dashboard with insight box
│ └── static/
│ ├── style.css # Dark-theme ops styling + SQL highlighting
│ └── app.js # Frontend logic + insight display
├── .env.example # Environment variable template
├── .gitignore
├── requirements.txt
├── NL_QUERY_EXAMPLES.md # 11 example queries with expected SQL
├── DEMO.md # Judge-facing demo walkthrough
└── README.md
- Google Cloud account with AlloyDB cluster provisioned
- AlloyDB instance with Vertex AI integration enabled
- Network connectivity to AlloyDB (VPC, Auth Proxy, or public IP)
- Python 3.10+
# Create AlloyDB cluster (if not exists)
gcloud alloydb clusters create ops-intelligence \
--region=us-central1 \
--password=YOUR_PASSWORD \
--enable-google-ml-integration
# Create primary instance
gcloud alloydb instances create ops-primary \
--cluster=ops-intelligence \
--region=us-central1 \
--instance-type=PRIMARY \
--cpu-count=2# Connect to AlloyDB and create database
psql -h <ALLOYDB_IP> -U postgres -c "CREATE DATABASE ops_intelligence;"# Clone and enter project
cd alloydb
# Create virtual environment
python -m venv .venv
source .venv/bin/activate # Linux/Mac
# .venv\Scripts\activate # Windows
# Install dependencies
pip install -r requirements.txt
# Configure environment
cp .env.example .env
# Edit .env with your AlloyDB connection details# Create schema and load seed data
python app/seed.py
# Configure AlloyDB AI (table/column descriptions)
python app/seed.py --setup-aipython app/app.py
# Open http://localhost:5000| Natural Language | What It Does |
|---|---|
| "Which service needs immediate attention?" | Decision intelligence: ranks by incidents + errors + latency |
| "Did errors increase after the last deployment?" | Temporal correlation: compares pre/post deployment errors |
| "Which services have both high latency and high error rates?" | Multi-signal: filters on latency AND error rate thresholds |
| "Show recurring incidents for payment-service" | Pattern detection: incident history for a specific service |
| "Show critical errors in the last 24 hours" | Filters logs by CRITICAL level and recent timestamp |
| "Which service is failing the most this week?" | Aggregates errors by service, ranks by count |
| "What is the average response time per service?" | Calculates AVG(response_time_ms) grouped by service |
| "Show error spikes in payment-service" | Hourly error counts for a specific service |
See NL_QUERY_EXAMPLES.md for full SQL patterns.
1. Enter: "Show critical errors in the last 24 hours"
2. Observe: SQL uses WHERE log_level = 'CRITICAL' and timestamp filter
3. Result: List of critical log entries with service names and messages
1. Enter: "Which service is failing the most this week?"
2. Observe: SQL uses GROUP BY with COUNT and ORDER BY DESC
3. Result: payment-service at the top (seeded with high failure rate)
1. Enter: "Which service needs immediate attention?"
2. Observe: Complex SQL joining services, logs, and incidents
3. Result: Ranked list showing payment-service and search-service need attention
- Landing page with example queries
- Query input with natural language question
- Generated SQL display
- Results table with data
- Error state (invalid query)
- Empty results state
- Generated SQL is validated (SELECT-only, no destructive operations)
- Queries run in READ-ONLY transactions
- 5-second statement timeout prevents runaway queries
- 500-row result limit protects the browser
- Error messages are sanitized (no SQL internals exposed to UI)
| Problem | Solution |
|---|---|
generate_sql not found |
Enable google_ml_integration extension and grant permissions |
| Connection refused | Check VPC peering or use AlloyDB Auth Proxy |
| Empty results | Run python app/seed.py to load seed data |
| "Not a read-only query" | AlloyDB AI generated a non-SELECT query; rephrase your question |
MIT