AstraSQL

The living data language of a project: ask in plain English, see whether an answer is certified / taught / guessed, and export the project's definitions as an Astra Pack. Chat is the interface; the pack is the product.

Security: AstraSQL ships with a local admin account. Sign in as admin / AstraSQL-change-me and change the password on first use. Prefer TLS at a reverse proxy; do not expose the UI or API on the public internet without HTTPS.

What it does

• Auth — local admin login (HttpOnly session cookie), forced password change after first setup
• Chat — natural-language questions with SSE streaming, SQL preview, results table/charts
• Connections — connect to PostgreSQL, test, and scan schema metadata
• Context — enrichments, business rules, golden Q→SQL records (FAISS), and Astra Pack export/import
• Feedback — thumbs up/down promotes useful answers into golden records; thumbs-down can also add a business rule
• Trust — answers show Certified / Taught / Guessed / Clarifying instead of a fake “Verified” badge
• History — review past queries, re-run, export CSV/JSON/XLSX

Requirements

• Docker (recommended) or Python 3.11+ and Node.js 20+
• An OpenAI API key
• A PostgreSQL database you can connect to (MySQL / MSSQL are on the roadmap)

Quick start (Docker)

cp .env.example .env
# set OPENAI_API_KEY and a strong ENCRYPTION_KEY (required when DEBUG=false)

docker compose up --build

1. Open the UI and sign in as admin / AstraSQL-change-me.
2. Set a new password (12+ characters, not the default).
3. Add a PostgreSQL connection and start chatting.

• UI: http://localhost:3000
• API: http://localhost:8000
• Health: http://localhost:8000/health

Persistent data (SQLite + FAISS indexes) lives in the astrasql_data volume at /app/data.

Local development

Backend

Requires Python 3.11+ and uv.

cd backend
uv sync --extra dev
cp ../.env.example ../.env   # or place .env in backend/
uv run uvicorn src.api.app:app --reload --host 0.0.0.0 --port 8000

Frontend

cd frontend
npm install
npm run dev

Vite proxies /api to the backend in development. Open the printed local URL (typically http://localhost:5173).

Adding a database connection

1. Open Connections in the UI.
2. Create a PostgreSQL connection (host, port, database, user, password, SSL).
3. Test connectivity, then Scan to ingest table metadata for the context layer.
4. Use Chat with that connection selected.

Supported today: PostgreSQL only. MySQL and Microsoft SQL Server providers are stubs on the roadmap.

Context enrichment, golden records, and feedback

• Enrichments — human descriptions, aliases, and example values for tables/columns so the agent links schema more accurately.
• Business rules — free-text constraints (e.g. “active customers means status = 'A'”) injected into generation.
• Golden records — curated question → SQL pairs; indexed with FAISS and retrieved as few-shot examples.
• Feedback loop — thumbs-up promotes that Q/SQL into golden records; thumbs-down can save corrected SQL and/or a new business rule.
• Astra Pack — export enrichments, rules, and goldens from Context as astra-pack.json and merge them into another connection. The project's language travels with the repo.

When a term has two meanings (gross vs net revenue) and no taught definition, AstraSQL asks instead of guessing.

Measuring the agent

Gold-file eval uses the same run_query path as chat (no second agent). Company-like fixture: backend/eval/datasets/project_shop/.

cd backend
uv run python -m src.eval.run --gold eval/datasets/project_shop/gold.json \
  --connection-id <id> --model gpt-4o --out eval/results/baseline.json

Scores: execution match on expect=answer, clarify-hit and silent-wrong on expect=clarify. Re-run the same file after a model or graph change. Optional --pack astra-pack.json is the taught pass. This is not a Spider score.

Manage context under Context; review past runs under History.

Architecture

Layer	Role
Frontend	React + Vite UI (Chat, Connections, Context, History, Settings)
API	FastAPI (/api/*), SSE streaming for query runs
Agent	LangGraph pipeline: intent → schema link → SQL generate → validate → execute → format
Context Layer	Schema enrichments, business rules, golden Q→SQL records, FAISS retrieval
Providers	Pluggable LLM (OpenAI) and database drivers (PostgreSQL today)
Storage	SQLite metadata (connections, history, context) + encrypted credentials

Question → LangGraph agent → Context retrieval → Dialect SQL → Readonly execute → Answer + confidence
                                    ↑
                         enrichments / rules / golden records / ratings

Security

• Local admin auth — the API and UI require a session cookie. First boot creates admin with DEFAULT_ADMIN_PASSWORD (AstraSQL-change-me) and blocks the rest of the app until that password is changed.
• Five failed logins lock the account for 15 minutes.
• Set a unique ENCRYPTION_KEY before production-like use. The app refuses to start when DEBUG=false and the default placeholder key is still set.
• Prefer TLS termination at a reverse proxy if you leave localhost. Session cookies are Secure when DEBUG=false.
• See SECURITY.md for reporting vulnerabilities.

Environment variables

Variable	Description	Default
OPENAI_API_KEY	OpenAI API key	(empty)
OPENAI_MODEL	Chat model	gpt-5.6-luna
LLM_PROVIDER	LLM provider key	openai
ENCRYPTION_KEY	Secret used to derive Fernet key for DB passwords	(must set when DEBUG=false)
SQLITE_URL	Async SQLAlchemy URL for app metadata	sqlite+aiosqlite:///./data/astrasql.db
DATABASE_URL	Optional alias of SQLITE_URL (future Postgres metadata)	(empty → use SQLITE_URL)
DEFAULT_ADMIN_USERNAME	Bootstrap admin username	admin
DEFAULT_ADMIN_PASSWORD	Bootstrap admin password (must change on first login)	AstraSQL-change-me
SESSION_TTL_DAYS	Sliding session lifetime	7
DATA_DIR	Data directory (SQLite path relative, FAISS indexes)	./data
CORS_ORIGINS	Comma-separated browser origins	http://localhost:5173,http://localhost:3000
MAX_RESULT_ROWS	Cap on query result rows	500
DEBUG	SQL echo / allow default encryption key / insecure cookies	false

Copy .env.example to .env before docker compose up. Never commit real API keys.

Adding a new database provider

Three steps:

1. Implement a class extending BaseDatabaseProvider in backend/src/providers/database/ (set available = True, dialect prompts, sqlglot dialect, async engine, schema introspection, readonly execute, EXPLAIN, test).
2. Register it in backend/src/providers/database/registry.py (_REGISTRY and optional aliases).
3. Export from backend/src/providers/database/__init__.py if you want a public import path.

The new type appears in GET /api/settings/public → database_types and in the Connections UI once it is marked available.

Roadmap

• Multi-user access control, SSO/OIDC, and API keys
• MySQL and Microsoft SQL Server connectors
• Additional LLM providers (Azure OpenAI, Anthropic, local models)
• Stronger governance (audit log, row/column policies)

Project layout

AstraSQL/
├── backend/          # FastAPI + LangGraph + providers
├── frontend/         # React SPA (nginx in Docker)
├── docker-compose.yml
├── .env.example
├── LICENSE
└── README.md

Contributing

See CONTRIBUTING.md. Bug reports and pull requests are welcome.

License

Copyright 2026 AstraSQL contributors.

Licensed under the Apache License, Version 2.0.