Legacy Database Explorer

Legacy Database Explorer is a local-first application designed to ground Large Language Models (LLMs) in existing relational databases (Postgres, MySQL, MSSQL) without granting the model unconstrained database access or relying entirely on zero-shot inference.

The core design principle is that the LLM should infer less over time. Initial inferences regarding schema names, mappings, and relationships are treated as suggestions. Only user-approved business rules and definitions are persisted in a Semantic Catalog and used as trusted context for answering natural-language queries. It connects to existing Postgres, MySQL, or MSSQL databases, introspects the schema, lets users approve business semantics, and then uses that approved context to generate, validate, execute, and explain read-only SQL.

Use Cases

• Explore legacy databases where table names, status codes, and business rules are poorly documented.
• Help analysts ask natural-language questions while still seeing the SQL, evidence, caveats, and confidence behind every answer.
• Build an approved semantic catalog for concepts like revenue, active customer, churn, completed order, soft delete rules, tenant scoping, and canonical joins.
• Keep database access read-only, bounded, traceable, and local-first.

Current Capabilities

• FastAPI backend and Streamlit UI.
• Local SQLite app database for projects, schema snapshots, semantic catalog entries, question runs, evidence, and traces.
• Read-only connection testing and live schema introspection for Postgres, MySQL, and MSSQL connection URLs.
• SQL DDL upload for offline or supplemental schema context.
• MySQL-oriented rich metadata collection: views, indexes, unique/check constraints, column/table comments, enum/set values, defaults, approximate sizes, samples, nullable ratios, distinct counts, min/max values, and frequent categorical values where available.
• Persistent semantic catalog with approval states for:
  • glossary terms
  • metric definitions
  • table and column descriptions
  • value mappings
  • canonical joins and filters
  • soft-delete rules
  • tenant/account scoping rules
  • date field meanings
  • warnings and constraints
  • approved SQL snippets
  • previous successful questions
• Semantic review UI for approving, rejecting, or creating catalog entries.
• Targeted context retrieval instead of dumping the full schema into every prompt.
• Multi-stage agent workflow: intent classification, term resolution, table and column selection, join/filter/grain planning, SQL generation, validation, bounded preview execution, result inspection, bounded repair, and final answer composition.
• SQL safety validator that blocks writes, DDL, procedures, comments, multi-statement SQL, and unbounded execution.
• Answer provenance with SQL, tables, columns, business definitions, filters, row count, assumptions, caveats, confidence, and warnings.

Architecture

Streamlit UI
    |
FastAPI API
    |
SQLite app store  <---- schema snapshots, semantic catalog, question history
    |
Agent workflow
    |
LLM provider abstraction: Ollama, OpenAI, OpenAI-compatible
    |
Read-only SQLAlchemy database access

The backend is the stable application boundary. The Streamlit app is a thin client over the API, so another frontend can reuse the same project, schema, semantic catalog, and question endpoints later.

See docs/architecture.md for the workflow and module layout.

Quick Start

python3 -m venv .venv
. .venv/bin/activate
pip install -e ".[dev]"
./scripts/dev.sh

Open the UI at http://127.0.0.1:8501.

The UI is organized around the product workflow:

• Projects: create/select a project and see the current state.
• Connect: build a connection URL from guided fields, use advanced URL mode, upload DDL, and run schema introspection.
• Schema: browse tables/views, columns, keys, samples, profiling, and likely semantic fields.
• Knowledge Sources: ingest docs, SQL, code, configs, or snippets as untrusted source material for semantic suggestions.
• Semantic Catalog: review suggestions, approve/reject/edit entries, and add typed business definitions.
• Ask: preview the context that will be used, choose live data or SQL-only mode, and ask questions.
• History: reopen previous question runs and save useful SQL as approved reusable context.

Fastest local smoke test:

1. Click Create demo project.
2. Go to Ask.
3. Click Ask agent.

The demo creates a local SQLite database, introspects it through the same schema path, uses the deterministic demo provider, validates generated SQL, executes the bounded read-only query, and returns evidence.

Live Database Setup

Install the driver extras you need:

pip install -e ".[dev,postgres,mysql,mssql]"

Use SQLAlchemy connection URLs:

postgresql+psycopg://readonly:password@host:5432/database
mysql+pymysql://readonly:password@host:3306/database
mssql+pyodbc://readonly:password@host/database?driver=ODBC+Driver+18+for+SQL+Server

Use a database account that is read-only. The app validates generated SQL, but database permissions are still the strongest safety boundary.

Live flow:

1. Create a project with the right dialect.
2. Open Connect.
3. Use guided connection fields or the advanced SQLAlchemy URL expander.
4. Confirm the credentials are read-only.
5. Test the connection.
6. Introspect the schema.
7. Browse tables and profiling in Schema.
8. Optionally add docs, SQL reports, or code snippets in Knowledge Sources.
9. Review suggestions in Semantic Catalog.
10. Approve or create the business semantics you trust.
11. Ask questions in Ask.
12. Review SQL, evidence, assumptions, caveats, and confidence.
13. Reopen successful runs in History and save useful SQL patterns.

LLM Providers

Configuration is environment-variable based. A starter file is provided at .env.example.

DBEXPLORER_PROVIDER=ollama
DBEXPLORER_MODEL=llama3.1
DBEXPLORER_OLLAMA_BASE_URL=http://localhost:11434

For local Ollama:

ollama pull llama3.1
DBEXPLORER_PROVIDER=ollama DBEXPLORER_MODEL=llama3.1 ./scripts/dev.sh

Supported providers:

• demo: deterministic local demo provider for smoke tests.
• ollama: local Ollama chat models.
• openai: OpenAI chat models through langchain-openai.
• openai_compatible: OpenAI-compatible local or hosted endpoints.
• echo: debugging provider that returns the prompt instead of generating SQL.

API Summary

• POST /projects
• GET /projects
• POST /demo/projects
• POST /projects/{id}/connections/test
• POST /projects/{id}/schema/introspect
• POST /projects/{id}/schema/upload
• GET /projects/{id}/schema
• GET /projects/{id}/semantics
• POST /projects/{id}/semantics
• PUT /projects/{id}/semantics/{entry_id}
• PATCH /projects/{id}/semantics/{entry_id}/status
• POST /projects/{id}/semantics/suggest
• POST /projects/{id}/knowledge-sources
• GET /projects/{id}/knowledge-sources
• GET /projects/{id}/knowledge-sources/{source_id}
• POST /projects/{id}/knowledge-sources/{source_id}/reingest
• PATCH /projects/{id}/knowledge-sources/{source_id}/status
• DELETE /projects/{id}/knowledge-sources/{source_id}
• POST /projects/{id}/questions
• GET /projects/{id}/questions
• GET /projects/{id}/questions/{question_id}

Connection testing, introspection, and live SQL execution require read_only_confirmed: true. Connection URLs are accepted per request and are not persisted.

Safety Model

• Requires users to confirm supplied credentials are read-only.
• Allows only SELECT, WITH, and dialect-safe EXPLAIN.
• Blocks writes, DDL, stored procedures, multi-statement SQL, and comments.
• Wraps executable read queries with a row limit.
• Applies best-effort dialect-specific timeouts.
• Does not persist raw database passwords or connection URLs.
• Treats inferred semantics as suggestions until the user approves them.
• Parses uploaded knowledge sources as untrusted text; it does not execute code or SQL during ingestion.

This is still a local single-user tool. It does not include authentication yet.

Semantic Catalog

The semantic catalog is how the app moves from schema awareness to business understanding. It stores both suggested and approved context. The agent only treats approved entries as reliable.

Examples:

{
  "kind": "metric_definition",
  "name": "paid revenue",
  "description": "Revenue is the sum of paid, non-deleted order totals.",
  "status": "approved",
  "payload": {
    "canonical_sql": "SUM(orders.total)"
  },
  "related_tables": ["orders"],
  "related_columns": ["orders.total", "orders.status", "orders.deleted_at"]
}

{
  "kind": "soft_delete_rule",
  "name": "active orders",
  "description": "Ignore soft-deleted orders.",
  "status": "approved",
  "payload": {
    "active_expression": "orders.deleted_at IS NULL"
  },
  "related_tables": ["orders"],
  "related_columns": ["orders.deleted_at"]
}

More detail is in docs/semantic-catalog.md.

Knowledge Sources

Knowledge Sources let the app extract candidate semantics from external material where database meaning often lives:

• markdown/text documentation
• SQL reports
• JSON/YAML config
• Python/TypeScript source
• pasted snippets

Extracted findings always become suggested semantic catalog entries with source attribution. They are not trusted until approved. Answers include knowledge-source provenance when approved knowledge-derived semantics are used.

More detail is in docs/knowledge-sources.md.

Development

Run tests and lint:

ruff check .
python3 -m pytest -q

Optional live database integration checks are skipped unless URLs are provided:

DBEXPLORER_TEST_POSTGRES_URL=postgresql+psycopg://readonly:... pytest -m integration
DBEXPLORER_TEST_MYSQL_URL=mysql+pymysql://readonly:... pytest -m integration
DBEXPLORER_TEST_MSSQL_URL=mssql+pyodbc://readonly:... pytest -m integration

The semantic eval harness in dbexplorer.eval seeds a local SQLite database, stores approved semantic rules, runs questions through the agent, and checks expected SQL fragments and answer text. It is covered by tests/test_eval.py.

See docs/development.md for project structure and common maintenance tasks.

Known Limitations

• Live database integration tests require external read-only test databases.
• Rich metadata support varies by database and driver.
• Query timeout controls are best effort because each database handles them differently.
• The DDL parser extracts useful table/column context but is not a full SQL parser.
• Retrieval currently uses deterministic lexical scoring. This keeps behavior reviewable, but large schemas may benefit from embeddings or a better ranker later.
• The LLM output contract is JSON-oriented but not yet enforced with a strict structured-output API.