The Governed Data Layer for AI Agents and Analysts
Every query β human or AI-generated β passes through the same access controls, validation, and audit pipeline.
Most teams give AI assistants raw database credentials. SQLatte sits in between: short-lived tokens, schema-scoped access, full audit trail, one-click revocation. Analysts get a chat interface; AI agents get MCP tools. Both go through the same validation and audit pipeline.
The core guarantee: whether a query is typed by a human or generated by an AI agent, it goes through the same intent detection β SQL generation β validation β audit trail. There is no separate, unaudited path for AI agents.
- Data teams running Trino, BigQuery, or Postgres who want analysts asking questions in plain English instead of filing SQL tickets
- Platform and security teams who need governed, audited AI agent access to the data warehouse without distributing raw credentials
- SaaS builders embedding a conversational data interface into their product via the embeddable widget or multi-tenant auth plugin
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β Frontend Layer β
β β’ Chat Interface β’ Admin Panel β’ Embeddable Widgets β
ββββββββββββββββββββββ¬βββββββββββββββββββββββββββββββββββββββββ
β
ββββββββββββββββββββββΌβββββββββββββββββββββββββββββββββββββββββ
β API Layer (FastAPI) β
β β’ Query Routes β’ Admin Routes β’ Analytics β’ Scheduler β
ββββββββββββββββββββββ¬βββββββββββββββββββββββββββββββββββββββββ
β
ββββββββββββββββββββββΌβββββββββββββββββββββββββββββββββββββββββ
β Core Processing Layer β
β β’ Intent Detection β’ SQL Generation β’ Query Execution β
β β’ Insights Engine β’ Dashboard Generator β
ββββββββββββββββββββββ¬βββββββββββββββββββββββββββββββββββββββββ
β
ββββββββββββββββββββββΌβββββββββββββββββββββββββββββββββββββββββ
β Database Provider Factory β
β (Trino β PostgreSQL β MySQL β BigQuery) β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
Every request β whether from the chat UI, an embedded widget, or an MCP client β executes through the same pipeline. There is no separate path for AI agents.
SQLatte is a native MCP server. Any MCP-compatible client β Claude Desktop, Claude Code, or a custom agent β gets controlled, audited access to your data warehouse without holding raw credentials.
- Short-lived tokens tied to specific catalogs and schemas
- Every generated query validated before execution
- Every call logged with token counts and source (UI / widget / MCP)
- One-click revocation β no credential rotation needed
pip install mcp httpxEnable in config.yaml to serve MCP over HTTP. Users connect with just a URL β no local script or Python installation needed.
mcp:
sse:
enabled: true{
"mcpServers": {
"sqlatte": {
"url": "http://your-sqlatte-server:8000/mcp/sse",
"headers": { "x-mcp-token": "<token>" }
}
}
}Claude Desktop doesn't speak HTTP/SSE directly β bridge it with mcp-remote:
{
"mcpServers": {
"sqlatte": {
"command": "npx",
"args": [
"mcp-remote@latest",
"http://your-sqlatte-server:8000/mcp/sse",
"--header",
"x-mcp-token: <token>",
"--allow-http"
]
}
}
}--allow-http is only needed for plain HTTP endpoints β drop it once the server is behind TLS.
Multiple users connect to the same server concurrently β each connection is isolated by token.
Some clients β notably Microsoft Copilot Studio β only speak the newer Streamable HTTP transport, not SSE. Enable it alongside (or instead of) SSE; both can run at the same time and share the same tools, token auth, masking rules and audit trail:
mcp:
sse:
enabled: true
streamable_http:
enabled: trueThe endpoint is POST /mcp/http. It is stateless β every request carries the token and is validated on its own, so revoking a token takes effect on the next call.
Copilot Studio: add an MCP tool with server URL https://your-sqlatte-server/mcp/http, authentication API key, type Header, header name x-mcp-token. Pick End user credentials so each user connects with their own token (per-user audit trail and daily limits); Maker-provided credentials shares one token β and one audit identity β across everyone. Copilot Studio connects from Microsoft's cloud, so the URL must be reachable from the internet over HTTPS.
Keeps real database credentials out of MCP config. Generate a token from the SQLatte UI; it stores the connection details server-side.
1. Generate a token:
Login to SQLatte β open the chat widget β click π API Tokens β Generate. Copy the token (shown only once).
2. Add to Claude config:
{
"mcpServers": {
"sqlatte": {
"command": "python3",
"args": ["/path/to/sqlatte/sqlatte_mcp_server.py"],
"env": {
"SQLATTE_URL": "http://localhost:8000",
"SQLATTE_TOKEN": "<token>"
}
}
}
}Token TTL is configurable (24h default). By default each token carries the catalog/schema chosen at sign-in. With plugins.auth.enable_discovery_tokens: true (Trino only), the token screen drops the catalog/schema picker entirely β sign-in is username/password only, and every token is instead scoped by the server's allowed_catalogs (see Configuration), letting an agent query and describe any table across every allowed catalog instead of being locked to one.
{
"mcpServers": {
"sqlatte": {
"command": "python3",
"args": ["/path/to/sqlatte/sqlatte_mcp_server.py"],
"env": {
"SQLATTE_URL": "http://localhost:8000",
"TRINO_HOST": "your-trino-host",
"TRINO_PORT": "443",
"TRINO_USER": "your-username",
"TRINO_PASSWORD": "your-password",
"TRINO_CATALOG": "hive",
"TRINO_SCHEMA": "default",
"TRINO_HTTP_SCHEME": "https"
}
}
}
}Available tools: ask_database (natural language β SQL β results), list_tables, get_schema, and discover_tables (Trino only, cross-catalog table/column search by partial name β opt-in via plugins.auth.enable_discovery_tokens: true, off by default). With discovery enabled, a catalog-less token's list_tables and ask_database/get_schema are all automatically scoped to allowed_catalogs β list_tables returns fully-qualified catalog.schema.table names across every allowed catalog instead of erroring for having no default one.
Sensitive columns can be masked before results reach the AI agent. Rules are managed in the Admin Panel under MCP Masking β no code or restart required. Three strategies:
| Strategy | Example output |
|---|---|
hash |
a3f8bc2d1e4f9a07 (SHA-256, 16-char β groupable) |
partial |
j**@company.com |
redact |
[REDACTED] |
Field patterns support wildcards (*email*, *_phone). Rules can be toggled on/off individually.
- Task-Based LLM Routing β Different models for different tasks (intent detection, SQL generation, insights) to balance cost and accuracy
- Conversation Memory β Context-aware follow-up questions
- Multi-Table JOINs β Automatic relationship detection
- Query History & Favorites β Save and replay queries
- SQL Syntax Highlighting β Code display with copy functionality
- CSV Export β Export results to spreadsheet format
Transform raw table names into business-friendly metadata that improves AI SQL accuracy:
- Business-Friendly Names β "Customer Master" instead of
cust_tbl_v2 - Automatic JOINs β Define relationships once; the AI uses them automatically
- Calculated Metrics β Centralized business logic so every query uses the same definition of "revenue"
- Auto-Discovery β Scan database and get instant entity suggestions
- Visual Admin UI β Browser-based entity, relationship, and metric builder
- Enhanced AI Context β Richer metadata produces more accurate SQL
- Auto Chart Generation β Line, bar, pie charts from query results
- Metric Cards β KPI displays with automatic formatting
- Chart Configuration β Customize chart types and settings
- Dashboard Persistence β Save dashboards to PostgreSQL or in-memory
- One-Click Refresh β Re-run queries and update visualizations
- Context-Aware Analysis β Considers temporal patterns (daily, weekly, monthly)
- Trend Detection β Growth, decline, anomaly identification
- Smart Recommendations β Actionable insights from your data
- Flexible Modes β
llm_only,statistical_only, orhybrid - Query-Specific Context β Insights tailored to your question
Operational automation for BigQuery environments, accessible at /ops-agent:
- Cost Analysis β Identify expensive queries, forecast monthly spend, analyze storage compression, detect unpartitioned tables, compare on-demand vs flat-rate billing
- Security Audits β Find public datasets, review table permissions, audit service account usage
- Performance Diagnostics β Surface slow queries, data skew, slot saturation, full table scans, partition recommendations
- Governance β Track unused tables and recent table access
- AI Insights β Optional AI-generated findings per operation, configurable via
ops_insights_generationinconfig.yaml - Multi-Project Support β Switch between GCP projects at runtime
- Cost Alarms β Schedule threshold-based alarms with cron triggers, email and Jira notifications, and test-on-demand
ops_agent:
enabled: true
ai_insights: true
ai_insights_max: 5
config:
projects:
- project_id: "my-project"
region: "europe-west1"
credentials_path: "/path/to/service-account.json"Every LLM call is recorded for observability and cost tracking:
- Full Tracing β Logs intent detection, SQL generation, chat, and insights calls with input/output token counts
- Filtering β Filter by session, intent type, date range, or user
- Summary Stats β Aggregated token usage and call counts
- CSV Export β Download audit data for billing or compliance analysis
- Widget Source Tracking β Distinguishes calls from default UI, auth widget, and MCP
- Flexible Scheduling β Hourly, daily, weekly, monthly, or custom cron
- Email Delivery β Automated report distribution with CSV/Excel/HTML attachments
- AI-Generated Insights β Include analysis in scheduled reports
- Execution History β Track all scheduled query runs
- Rate Limiting β Prevent runaway execution and manage API costs
- SQL Injection Protection β Multi-layer validation with risk scoring and admin override for high-risk queries
- Credential Isolation β Database credentials stored server-side; MCP clients hold tokens, not passwords
- Session-Based Auth β Token-based authentication for admin endpoints
- Optional LDAP / Active Directory Login β Admin panel and the SQLatte Assistant can require LDAP auth instead of (or as a fallback to) local credentials
- Multi-Tenant Support β Per-user database connections via auth plugin
- Catalog/Schema Restrictions β Limit user access to specific databases
- Rate Limiting β Configurable per-minute and per-hour request caps
Access at /admin:
- Dashboard β Overview, stats, quick actions
- Prompts β Edit AI behavior (intent, personality, SQL generation, insights)
- Tables β View database schema
- Semantic Layer β Entity/relationship/metric builder (5 sub-tabs)
- Email & SMTP β Email configuration
- Scheduler β Scheduled query management
- Insights β Insights engine settings
- Ops Agent β BigQuery Ops Console toggles and cost alarm configuration
- Export β Configuration export formats
- History β Configuration change log
- Snapshots β Backup and restore
- Audit Logs β LLM call history with token usage, filterable by widget source
- MCP Masking β Field-level masking rules for MCP responses (hash / partial / redact, wildcard patterns, per-rule toggle)
- LDAP / SSO β Configure LDAP/Active Directory auth for admin login and the Assistant login gate
Hot Reload β all changes apply immediately without restart.
Two variants for different use cases:
- Standard Widget (
sqlatte-badge.js) β Public analytics interface - Auth Widget (
sqlatte-badge-auth.js) β User-specific database connections for multi-tenant SaaS
git clone https://github.com/osmanuygar/sqlatte.git
cd sqlatte
pip install -r requirements.txtCopy the template and edit it:
cp config/config.yaml.example config/config.yamlconfig/config.yaml is gitignored (it'll hold real credentials) β only
config.yaml.example is tracked. Edit the copy:
# ============================================
# LLM CONFIGURATION
# ============================================
llm:
provider: "anthropic" # anthropic | gemini | vertexai
anthropic:
api_key: "sk-ant-your-key-here"
model: "claude-sonnet-4-20250514"
max_tokens: 4096
# ============================================
# DATABASE CONFIGURATION
# ============================================
database:
provider: "trino" # trino | postgresql | mysql | bigquery
trino:
host: "your-trino-host.com"
port: 443
user: "your-username"
password: "your-password"
catalog: "hive"
schema: "default"
http_scheme: "https"
# ============================================
# TASK-BASED LLM ROUTING (Optional)
# ============================================
# Use cheaper/faster models for simple tasks
model_routing:
enabled: true
tasks:
intent_detection:
provider: "anthropic"
model: "claude-haiku-3-5-20241022"
max_tokens: 500
sql_generation:
provider: "anthropic"
model: "claude-sonnet-4-20250514"
max_tokens: 4096
insights:
provider: "anthropic"
model: "claude-sonnet-4-20250514"
max_tokens: 2000
chat:
provider: "anthropic"
model: "claude-haiku-3-5-20241022"
max_tokens: 1000
# ============================================
# FEATURES (All Optional)
# ============================================
analytics:
enabled: false # Set true for PostgreSQL query history
scheduler:
enabled: false # Set true for scheduled queries
timezone: "UTC"
email:
enabled: false # Set true for real email delivery
smtp:
host: "smtp.gmail.com"
port: 587
user: "your-email@gmail.com"
password: "your-app-password"
from_name: "SQLatte Analytics"
insights:
enabled: true
mode: hybrid # llm_only | statistical_only | hybrid
max_insights: 3
# ============================================
# CONFIGURATION DATABASE (Optional)
# ============================================
config_db:
enabled: false # Enable for runtime config persistence
type: "postgresql"
postgresql:
host: "localhost"
port: 5432
database: "sqlatte_config"
user: "postgres"
password: "password"
# ============================================
# PLUGINS (Optional)
# ============================================
plugins:
auth:
enabled: false # Enable for multi-tenant auth
session_ttl_minutes: 480
max_workers: 40
db_provider: "trino"
db_host: "trino_hostname"
db_port: 443
allowed_catalogs: [] # Empty = allow all. Or per-catalog schemas:
# - name: "s3_access_logs"
# allowed_schemas: ["web"]
allowed_schemas: []
allowed_db_types: ["trino"]
enforce_catalog_lock: true # Trino only, on by default β see MCP Server section
enable_discovery_tokens: false # Trino only β catalog-less tokens scoped by allowed_catalogs; see MCP Server sectionNote β no PostgreSQL init script. SQLatte doesn't ship an
init.sqlor migration step foranalytics.postgresql/config_db.postgresql. Each module creates its own tables on first connect (CREATE TABLE IF NOT EXISTS), so just point it at an existing database β the user just needsCREATE/CREATE TABLEprivileges on that database, no manual schema setup required.docker-compose.ymldoesn't provision a Postgres container either; bring your own instance and pass its connection details inconfig.yaml.
# Development
python -m src.api.app
# Production (with Gunicorn) β keep -w 1. Sessions (auth_plugin.py's
# SessionManager, admin_auth.py) live in-memory, per-process; more than one
# worker splits that state and users get random "session expired" errors on
# whichever worker doesn't have theirs. Same reason k8s pins replicas: 1 β
# see docs/kubernetes-deployment.md#why-replicas-1. Don't raise this without
# first moving sessions to a shared store (Redis/Postgres).
gunicorn src.api.app:app -w 1 -k uvicorn.workers.UvicornWorker -b 0.0.0.0:8000- Main Interface: http://localhost:8000
- Admin Panel: http://localhost:8000/admin
- Widget Demo: http://localhost:8000/demo
- API Docs: http://localhost:8000/docs
# 1. Edit config/config.yaml with your credentials
vi config/config.yaml
# 2. Start services
docker-compose up -d
# 3. Open browser
open http://localhost:8000# Build image
docker build -t sqlatte .
# Run container
docker run -d -p 8000:8000 \
-e ANTHROPIC_API_KEY="sk-ant-your-key" \
-e TRINO_HOST="your-trino-host" \
-e TRINO_USER="username" \
-e TRINO_PASSWORD="password" \
--name sqlatte \
sqlatteManifests in k8s/ deploy SQLatte plus an in-cluster Postgres (StatefulSet) for analytics/config_db. Single replica by design β see docs/kubernetes-deployment.md for the architecture, deploy order, and why it doesn't scale past 1 replica yet.
| Database | Status | Configuration Required |
|---|---|---|
| β Trino | Stable | host, port, catalog, schema |
| β PostgreSQL | Stable | host, port, database, schema |
| β MySQL | Stable | host, port, database |
| β BigQuery | Stable | project_id, credentials |
Trino Configuration Example
database:
provider: "trino"
trino:
host: "trino.example.com"
port: 443
user: "username"
password: "password"
catalog: "hive"
schema: "default"
http_scheme: "https"PostgreSQL Configuration Example
database:
provider: "postgresql"
postgresql:
host: "localhost"
port: 5432
database: "analytics"
user: "postgres"
password: "password"
schema: "public"BigQuery Configuration Example
database:
provider: "bigquery"
bigquery:
project_id: "my-gcp-project"
dataset: "analytics"
location: "US"
credentials_path: "/path/to/service-account.json"
# OR: credentials_json: '{"type": "service_account", ...}'| Provider | Models | Notes |
|---|---|---|
| β Anthropic Claude | Opus, Sonnet, Haiku | Default; all models supported |
| β Google Gemini | gemini-pro | Free tier available |
| β Google Vertex AI | gemini-pro | Enterprise GCP |
<!DOCTYPE html>
<html>
<body>
<h1>My Website</h1>
<!-- Load widget from SQLatte backend -->
<script src="http://your-sqlatte-server:8000/static/js/sqlatte-badge.js"></script>
<!-- Configure (optional) -->
<script>
window.addEventListener('load', () => {
window.SQLatteWidget.configure({
position: 'bottom-right',
fullscreen: true,
apiBase: 'http://your-sqlatte-server:8000'
});
});
</script>
</body>
</html><!DOCTYPE html>
<html>
<body>
<h1>My SaaS Application</h1>
<!-- Load auth widget -->
<script src="http://your-sqlatte-server:8000/static/js/sqlatte-badge-auth.js"></script>
<!-- Configure -->
<script>
window.addEventListener('load', () => {
window.SQLatteAuthWidget.configure({
position: 'bottom-left',
fullscreen: true,
apiBase: 'http://your-sqlatte-server:8000'
});
});
</script>
</body>
</html>If embedding on a different domain:
cors:
allow_origins:
- "https://your-website.com"
- "http://localhost:3000"
allow_credentials: true
allow_methods: ["*"]
allow_headers: ["*"]User: "Show me top 10 customers by revenue this year"
SQLatte:
π‘ Generated SQL:
SELECT customer_name, SUM(order_total) as revenue
FROM orders
WHERE YEAR(order_date) = YEAR(CURRENT_DATE)
GROUP BY customer_name
ORDER BY revenue DESC
LIMIT 10
π Results: [Interactive table with 10 rows]
π§ Insights:
- Top customer generated $1.2M (23% of total revenue)
- Revenue concentration in top 3 customers indicates dependency risk
- Consider diversification strategy
User: "What about last year?"
SQLatte: [Automatically understands context, modifies WHERE clause]
User: "Create a dashboard for this query"
SQLatte: [Generates line chart + metric cards + saves to favorites]
Define business metadata once, use everywhere:
# Example: Define a "Customer" entity
Entity: customer_master
Display Name: Customer
Description: Core customer data
Columns:
- cust_id (Primary Key)
- full_name (Display Name: Customer Name)
- registration_date
- ltv (Display Name: Lifetime Value)
# Define relationship
Relationship: customer_to_orders
From: customer_master.cust_id
To: orders.customer_id
Type: one-to-many
# Define metric
Metric: total_revenue
SQL: SUM(orders.amount)
Description: Total revenue across all ordersNow ask: "Show me customers with high lifetime value"
SQLatte automatically:
- Uses "Customer" display name
- Finds the correct table (customer_master)
- Interprets "lifetime value" as the
ltvcolumn - Generates accurate SQL with proper column references
Schedule recurring reports:
Schedule Name: Weekly Revenue Report
Frequency: Weekly (Every Monday 9 AM)
Recipients: analytics-team@company.com
Format: Excel with AI insightsRoute each task to the right model to balance cost and accuracy:
intent_detection: claude-haiku # fast, cheap β classifies intent only
sql_generation: claude-sonnet # more capable β generates the actual SQL
insights: claude-sonnet # analysis of results
chat: claude-haiku # conversational responsesAsk the user before running generated SQL, instead of just showing it or running it silently:
query:
execute_generated_sql: false # generate SQL without running it
confirm_before_execute: true # ...but ask the user first (Evet/HayΔ±r) instead of only displaying it
require_session: false # require a logged-in session for the unauthenticated /query endpoint
bigquery:
price_per_tb_usd: 6.25 # optional β adds a $ estimate next to the confirm prompt (BigQuery only)Applies to the interactive Assistant and Authenticated Agent widgets only β programmatic access (MCP tools, API tokens) always executes immediately, since there's no one to click a confirmation button.
SQLatte is self-hosted β your data never leaves your own infrastructure. Docker and Docker Compose files are included for straightforward deployment.
For production:
- Run behind a reverse proxy (nginx, Caddy) with TLS termination
- Use Gunicorn with Uvicorn workers for concurrency
- Point
config_dbat a managed PostgreSQL instance for config persistence and audit log durability
Multi-layer validation on every query:
- Keyword Blacklist β Block dangerous SQL patterns
- Syntax Validation β Parse and validate SQL structure
- Risk Scoring β Assign risk level to each query
- Admin Override β Manual approval for high-risk queries
Database credentials are stored server-side. MCP clients and embedded widgets authenticate with short-lived tokens β they never hold raw credentials. Revoking access means invalidating the token, not rotating passwords.
Enable config_db (see Configuration) to move secrets out of the plaintext config.yaml file:
config_db:
enabled: true
type: "postgresql"
encryption_key: "${CONFIG_DB_ENCRYPTION_KEY}" # generate with Fernet.generate_key()- Sensitive fields (
api_key,password,secret,tokenβ LLM keys, warehouse credentials, SMTP password, analytics/audit DB password) are encrypted with Fernet before being written to Postgres. - The encryption key itself is meant to come from an environment variable, never committed to the file.
- Once bootstrapped, values stored in
config_dbtake priority overconfig.yamlβ the file's copies can be blanked out or left stale, since the database is the live source of truth. Only theconfig_db.postgresqlconnection itself (and its encryption key) has to exist outside the database, as env vars.
rate_limiting:
enabled: true
requests_per_minute: 10
requests_per_hour: 100- User-specific credentials
- Catalog/schema restrictions per user
- Session management with TTL
- Thread-safe connection pooling
Disabled by default. When enabled, LDAP is used for admin login (tried first, falling back to admin.username/password if the directory is unreachable) and, optionally, an LDAP login gate in front of the SQLatte Assistant β which otherwise has no login at all.
ldap:
enabled: true
server: "ldaps://ldap.example.com:636"
use_ssl: true
# Direct bind (simplest β no service account needed):
user_dn_template: "DOMAIN\\{username}" # Active Directory
# user_dn_template: "uid={username},ou=people,dc=example,dc=com" # OpenLDAP
# Or search+bind with a service account β see config.yaml.example
plugins:
assistant_login:
enabled: true # require LDAP login before the Assistant can be used
ttl_hours: 8Both sections are also editable from the Admin Panel's LDAP / SSO tab, with optional persistence to config_db.
- Async Processing β FastAPI with async/await; thread pool for blocking operations
- Connection Pooling β Reusable database connections with automatic cleanup and thread-safe multi-user support
- Caching β Query result caching, dashboard persistence, session-based conversation memory
Contributions welcome!
- Fork the repository
- Create feature branch (
git checkout -b feature/AmazingFeature) - Commit changes (
git commit -m 'Add AmazingFeature') - Push to branch (
git push origin feature/AmazingFeature) - Open Pull Request
This project is licensed under the MIT License β see LICENSE file.
- GitHub: @osmanuygar
- Project: https://github.com/osmanuygar/sqlatte
Built with:
- FastAPI β Modern Python web framework
- Anthropic Claude β AI-powered query generation
- Chart.js β Data visualization
- PostgreSQL β Data persistence
- Trino, BigQuery β Analytics engines
- Docs: Documentation
