Feature: SQL Insights Lab - NL→SQL charts, saved queries & alerts (DuckDB/Postgres)

Summary Add a first-class SQL Insights Lab that lets users (and power users on the team) explore market trends with SQL, auto-generate charts from results, and set saved queries + threshold alerts. Queries can be written manually or created from natural language (NL→SQL) against safe, read-only views. Backed by DuckDB (embedded, zero-ops) to start, with an optional Postgres/Timescale path for production scale.

Why

• Fill the gap between “chatty recs” and verifiable, explorable analytics (time trends, comps, neighborhood deltas, affordability what-ifs).
• Unlock fast aggregations on 30k+ listings without overloading Mongo or Pinecone.
• NL→SQL makes insights discoverable to non-SQL users; saved queries + alerts create stickiness.

---

Scope

User stories

• As a buyer, I can ask, “show median price per sq ft in Southern Village vs Meadowmont last 12 months” and get a chart + downloadable table.
• As a power user, I can craft SQL, save it, and receive alerts (email/in-app) when a metric crosses a threshold (e.g., median DOM < 10 days).
• As an analyst, I can join graph context (Neo4j) and vector cohorts (Pinecone) via pre-materialized views to explain “why” alongside numbers.

Acceptance criteria

• New /insights/sql page with: editor, NL→SQL prompt box, result table, and auto-chart (Chart.js).
• Backend /api/sql/run executes parameterized, read-only SQL against approved views only.
• Saved Queries: create, run, rename, delete; owner-scoped with optional sharing flag.
• Alerts: threshold rules (>, <, %, delta vs previous period) on any saved query; schedule (hourly/daily).
• Templates gallery: one-click queries (examples below).
• Role guard for who can run raw SQL vs NL→SQL only.
• Prometheus metrics: query latency, rows returned, cache hit rate, NL→SQL success rate.
• E2E tests cover NL→SQL → run → chart, and alert firing path.
• OpenAPI updated; README + TECH_DOCS sections added.

---

High-level design

Engine choice

• Phase 1 (default): DuckDB (Node bindings)

  • Pros: blazing fast columnar analytics, no infra, supports Parquet & spatial extension for polygons/geo joins.
  • Ideal for nightly materialized views, ad-hoc aggregates.

• Phase 2 (optional): Postgres/Timescale/PostGIS

  • For multi-tenant scale + native time-series & geospatial indexing.
  • Keep identical logical schema so we can flip via config.

Data flow

1. ETL (nightly + on-demand incremental):

   • Extract canonical property facts from Mongo; export to Parquet.
   • Build materialized views: mv_prices_by_month, mv_dom, mv_affordability, mv_zip_stats, mv_graph_links, mv_vector_cohorts.

2. Query execution:

   • API validates query → rewrites to target whitelisted views → binds params → runs on DuckDB (or Postgres).
   • Results cached (Redis) keyed by normalized SQL + params.

3. NL→SQL:

   • Use Gemini to generate SQL against a schema prompt (views only).
   • Parse with SQL AST (e.g., pgsql-ast-parser) and reject disallowed nodes (DDL/DML, functions not in allowlist).

4. Alerts service:

   • Cron job evaluates saved rules, compares to thresholds, emits notifications (in-app + email), records history.

---

Proposed logical schema (read-only views)

• vw_properties — denormalized snapshot (zpid, price, beds, baths, sqft, year_built, list_date, status, lat, lon, neighborhood, zip, school_rating, …)
• mv_prices_by_month — (zip, neighborhood, month, median_price, p25_price, p75_price, count)
• mv_psf_by_month — (zip/neighborhood, month, median_psf, count)
• mv_dom — (zip/neighborhood, month, median_days_on_market, count)
• mv_affordability — (zip, month, median_price, est_mortgage_pmt_30y, est_pmt_15y, taxes_est, insurance_est)
• mv_graph_links — (zpid, neighbor_zpid, reason ENUM['IN_NEIGHBORHOOD','IN_ZIP','SIMILAR_TO']) from Neo4j snapshots
• mv_vector_cohorts — (cohort_id, label, zpid) from Pinecone similarity clusters
• vw_user_prefs — (user_id, max_price, min_beds, commute_minutes_to_work, …) read-only for personalization joins

DuckDB: store Parquet in /data/warehouse/*.parquet; register as tables; create views over them. Postgres: identical schema via CREATE MATERIALIZED VIEW … refreshed nightly.

---

API

POST /api/sql/run

• Body:

{
  "mode": "nl" | "sql",
  "query": "median psf by month for Southern Village since 2023",
  "params": { "neighborhood": "Southern Village", "from": "2023-01-01" },
  "chart": true
}

• Behavior:

  • mode=nl → NL→SQL compile → validate AST → execute.
  • mode=sql → validate against allowlist views & functions → execute.
  • Returns { columns, rows, inferredChart: { type, x, y, series } }.

POST /api/sql/saved

Create saved query (name, sql, params, visibility).

POST /api/sql/alerts

Create alert { savedQueryId, rule: { metricColumn, op, value, lookback }, schedule }.

---

Frontend (/insights/sql)

• Monaco editor with SQL linting and param chips.
• NL prompt box (“What would you like to see?”).
• Results table (virtualized) + auto-chart (Chart.js) with smart defaults (time on X if present).
• “Save query”, “Create alert”, “Add to dashboard”.
• Template gallery (cards) with one-click run.

---

Security & safety

• Read-only DB user; no DDL/DML.
• AST validator: block ;, CTE only over whitelisted views, allowed functions list.
• Row-level privacy: no PII in views; user-scoped joins restricted to vw_user_prefs using server-side injected user_id.
• Rate limiting + query timeouts; max rows with pagination.

---

Performance & ops

• Redis cache (5–30 min TTL) for popular queries.
• DuckDB: vectorized scans; partition Parquet by month/zip.
• Prometheus: sql_run_duration_seconds, sql_rows_returned, nl_to_sql_failures_total, alerts_fired_total.

---

Example templates

1. Median Price per SqFt by Month (Neighborhood)

SELECT month, neighborhood, median_psf
FROM mv_psf_by_month
WHERE neighborhood = $neighborhood AND month >= $from
ORDER BY month;

2. Days on Market vs. Cohort

SELECT m.month, c.label AS cohort, AVG(m.median_days_on_market) AS dom
FROM mv_dom m
JOIN mv_vector_cohorts c USING (zpid)
WHERE m.month >= $from
GROUP BY 1,2
ORDER BY 1,2;

3. Affordability Index (est. P&I / median HH income)

SELECT a.month, a.zip,
       a.est_mortgage_pmt_30y / NULLIF(d.median_income,0) AS affordability_idx
FROM mv_affordability a
JOIN vw_demographics d USING (zip)
WHERE a.month >= $from
ORDER BY a.month;

---

Phased plan

M1 — Foundations (backend + ETL)

• Add DuckDB dependency and service wrapper.
• ETL job Mongo→Parquet (+Neo4j & Pinecone snapshots).
• Create views and materialized views; seed sample Parquet.
• /api/sql/run + AST validator + Redis cache.
• Prometheus metrics.

M2 — UI & NL→SQL

• /insights/sql page + editor/table/chart.
• NL→SQL compiler w/ schema prompt + guardrails.
• Templates gallery.

M3 — Saved queries & alerts

• CRUD for saved queries; role guards.
• Alert evaluator job; notifications (in-app + email).
• Docs, OpenAPI, tests (unit/integration/E2E).

---

Risks / mitigations

• LLM hallucinated SQL → strict schema prompt + AST allowlist + dry-run EXPLAIN + small sandbox before prod.
• Cost/latency on NL→SQL → cache compiled SQL per prompt; client-side debounce.
• Infra sprawl → start with DuckDB; optional Postgres behind a feature flag.

---

Nice-to-have (later)

• PostGIS polygons for “draw on map → query” workflows.
• Dashboard mode: pin multiple saved queries into a personal board.
• VS Code extension command: “Run in SQL Lab” via MCP.
• What-if simulator: param sliders (rates, taxes) that rewrite the SQL with CTEs.

---

Open questions

• Do we want user sharing of saved queries (public gallery), or keep private until moderation exists?
• Which neighborhood name canonicalization should the views adopt (Zillow/GeoJSON/Neo4j nodes)?
• Minimum viable alert transport (email via backend SMTP vs. in-app only for M3)?

---