Content
# Versa Database Agent
[](https://deepwiki.com/glaubercosta/versadatabaseagent)
A modular MCP server for intelligent database operations and semantic data cataloging. Built with **Clean Architecture**, **TDD**, and full **Model Context Protocol (MCP)** support.
Designed for large-scale environments (400+ tables, millions of records) with RAG-powered semantic search, relational intelligence, and hardened security.
## 📚 Documentation
This project has AI-generated documentation hosted on **DeepWiki**, a service by Cognition that automatically indexes public GitHub repositories and produces interactive, conversational documentation.
- **Browse the wiki**: [https://deepwiki.com/glaubercosta/versadatabaseagent](https://deepwiki.com/glaubercosta/versadatabaseagent)
- **Ask questions** directly to the indexed codebase using natural language via the "Ask DeepWiki" feature.
- DeepWiki re-indexes the repository periodically — pushes to the default branch are eventually reflected in the wiki.
The wiki covers:
- High-level architecture (Clean Architecture layers and MCP integration)
- Module-level breakdowns (`interface`, `application`, `infrastructure`)
- Data flow for schema discovery, semantic search (RAG), and join-path inspection
- Security model (identifier validation, query blocklist, scope enforcement)
For local/offline reference, continue using this `README.md` and the in-repo `quality-gates.md`.
## 🚀 Features
### 🔍 Schema Discovery & Navigation
- **Hierarchical schema browsing** — navigate by schema, then table
- **Rich metadata extraction** — PKs, FKs, nullability, column types
- **On-demand data sampling** — fetch sample rows without full queries
### 📊 Statistical Intelligence
- **Table statistics** — row counts, min/max ranges, null counts, cardinality
- **Stats TTL cache** — in-memory cache per `schema.table` to reduce repeated heavy stats queries on hot tables
- **Semantic data catalog (RAG)** — ChromaDB-powered vector search for natural language table discovery
- **Automatic catalog refresh** — indexes schema + stats into the semantic catalog
- **Canonical scoped snapshots** — catalog metadata can be stored in platform metadata DB by `workspace/project/connection`
- **Freshness traceability** — semantic entries expose `refreshed_at`, `age_seconds`, `stale`, and `source`
### 🗺️ Relational Intelligence
- **Explicit FK discovery** — extracts foreign key constraints from database metadata
- **Heuristic join detection** — discovers implicit relationships via naming patterns (`_id` columns, plural matching including `s`, `es`, `ies`)
- **Join path finder** — BFS-based shortest path between any two tables
- **Relational health alerts** — flags implicit joins that should be explicit constraints
### 🔒 Security
- **Identifier validation** — all schema/table/column names validated against real database metadata before SQL execution
- **Query blocklist** — blocks `DROP`, `DELETE`, `INSERT`, `INTO`, `OUTFILE`, `pg_read_file`, `COPY`, SQL comments (`--`, `/*`), and more
- **Error sanitization** — generic error messages to clients; details logged internally
- **Read-only enforcement** — only `SELECT` / `WITH` queries allowed, with automatic `LIMIT`
- **Role-based authorization (optional)** — workspace roles (`viewer`, `operator`, `admin`) enforced **per tool** via a single-source tool→role matrix; a caller lacking the minimum role gets a 403 *before* the tool runs (connection use/admin checks still apply)
- **Audit trail** — structured tool execution events (`actor`, `scope`, `tool`, `status`, `timestamp`) persisted for incident review
- **Runtime hooks (optional)** — in-memory metrics and fixed-window rate limits per workspace/connection
## 🛠️ MCP Tools
All database tools now require scope via one of these paths:
- explicit `workspace_id`, `project_id`, `connection_id` arguments in the tool call
- prior `select_active_context` followed by tool calls that omit explicit scope
- semantic tools (`search_semantic_catalog`, `refresh_semantic_catalog`) follow the same scope rule
| Tool | Description |
|:---|:---|
| `list_database_structure` | Lists tables, columns, PKs, FKs with optional search filter |
| `query_database` | Executes read-only SQL queries with security validation |
| `get_table_sample` | Returns sample rows from a validated table |
| `list_schemas` | Lists all non-system schemas for hierarchical navigation |
| `list_empty_tables` | Lists empty tables using live row-count checks |
| `get_table_stats` | Returns statistical metadata (rows, min/max, nulls, distinct) |
| `inspect_join_paths` | Suggests how to join two tables with health alerts |
| `get_connection_capabilities` | Returns adapter capabilities for scoped connection negotiation (`spatial`, etc.) |
| `check_connection` | Probes a scoped connection (`SELECT 1`) and returns `OK latency=Xms` / `FAIL phase=auth\|connect\|query` |
| `get_spatial_diagnostics` | Returns SRID/validity/extent diagnostics for spatial columns when supported |
| `list_workspace_projects` | Lists available projects for a workspace |
| `list_project_connections` | Lists available DB connections for a project |
| `select_active_context` | Selects active `workspace_id/project_id/connection_id` context |
| `get_active_context` | Returns the currently selected scope context |
| `list_audit_events` | Lists structured audit events with optional scope/actor filters |
| `list_runtime_metrics` | Lists runtime metric buckets (count/errors/avg latency) by tool |
| `search_semantic_catalog` | Semantic search over the indexed data catalog |
| `refresh_semantic_catalog` | Re-indexes all tables and stats into ChromaDB |
| `refresh_semantic_catalog_async` | Starts a background catalog refresh for large catalogs |
| `get_refresh_semantic_catalog_status` | Polls an async refresh job (`status`, `processed_tables`, `total_tables`) |
| `export_catalog_snapshot_json` | Exports scoped catalog snapshot entries as JSON |
### 🧠 Curation & Knowledge Tools
Curated descriptions and validated solutions are layered over automatic
inference to raise discovery quality (catalog *populated* ≠ *reliable*).
| Tool | Description |
|:---|:---|
| `attach_curated_description` | Attaches/edits a human-curated description for a table/column/relationship; takes precedence over inference |
| `list_curation_gaps` | Lists weakly-inferred entries lacking curation plus low-confidence discovery hits |
| `get_catalog_quality_readiness` | Aggregates a reliability signal (curated/boilerplate ratios, mean lift, low-confidence density, `populated_not_reliable`) |
| `add_validated_solution` | Stores a validated SQL solution (tiered, parametrized, zero-PII) into solution memory |
| `promote_solution` | Promotes a solution across tiers (`candidato → validado → confiavel`) |
### 💬 MCP Prompts (guided recipes)
Client-side recipes that orient the LLM to chain the right tools — they do not
resolve scope or pass through audit.
| Prompt | Description |
|:---|:---|
| `answer_data_question` | Q&A over the data: chains `select_active_context → search_semantic_catalog → inspect_join_paths → query_database` |
| `build_chart` | Same data flow, then hands off to the Superset MCP to render a chart (matches by `database_name` + `backend`) |
### Tool Versioning
- Canonical behavior is scope-aware for all DB tools.
- MVP exposes a single canonical tool per capability (no `*_v2` aliases).
- Legacy direct-engine fallback was removed; `query_database` now requires adapter-contract execution.
## 📦 Installation
### Install (Phase A — shared server via Docker, recommended for testers)
The recommended onboarding path for internal testers. No Python installation
required on the tester's machine — the admin deploys a single Docker container
and each tester configures only their Claude Desktop.
See [docs/TESTER_ONBOARDING.md](docs/TESTER_ONBOARDING.md) for the complete
step-by-step guide (admin deploy + tester registration + Claude Desktop config).
Quick start for admins:
```bash
# Clone, configure, deploy
cp src/templates/env_template.env .env
# Edit .env: set SECRET_ENCRYPTION_KEY and VERSA_API_KEYS
docker compose up -d
```
### Install (Phase 0 — `pipx`, dev / local setup)
Recommended for DBAs and end-users who do not need to modify the codebase.
Requires Python ≥ 3.11 and [`pipx`](https://pipx.pypa.io/) on the `PATH`.
For a one-shot Windows install that also wires Claude Desktop for you,
run `scripts/install-versa-db.ps1` from a clone (or download it
standalone — see [docs/TESTER_ONBOARDING.md](docs/TESTER_ONBOARDING.md)
for the URL):
```powershell
.\scripts\install-versa-db.ps1 -Source .
```
The script chains `pipx install`, `versa-db-agent init`, and
`versa-db-agent wire-claude` so the `versa-db` entry shows up in
`%APPDATA%\Claude\claude_desktop_config.json` without manual JSON
editing. `-DryRun` previews the steps without executing them.
For the manual flow:
```bash
pipx install git+https://github.com/glaubercosta/versadatabaseagent.git
versa-db-agent init # seeds connections.example.yaml + env_template.env into %LOCALAPPDATA%\Versa
versa-db-agent --version # confirms install
versa-db-agent wire-claude # patches claude_desktop_config.json idempotently
```
After `init`, edit the seeded files in `%LOCALAPPDATA%\Versa\` (Windows) or
`~/.local/share/Versa/` (Linux/macOS). If you skip `wire-claude`, point
Claude Desktop at the `versa-db-agent` binary in your MCP server config
by hand. The runtime resolves `.env` from the current working directory
when launched outside a clone.
Phase 0 also supports storing DB passwords in the OS keyring (Windows
Credential Manager) instead of as Fernet ciphertexts in
`platform_metadata.db`. Register your connections with the
`--use-keyring` flag and the `SECRET_ENCRYPTION_KEY` requirement
disappears:
```bash
python -m scripts.register_connections --use-keyring
# connections.yaml is resolved from ./ first, then %LOCALAPPDATA%\Versa\
```
### For integrators
Programmatic clients can rely on a stable error-code contract: every error
response carries an `error_code` field in the audit log and a `[ERR_X]`
prefix in tool string returns. The catalog and retry-safety table live at
[docs/error-codes.md](docs/error-codes.md).
> **Versa testers (MVP):** start at [docs/TESTER_ONBOARDING.md](docs/TESTER_ONBOARDING.md)
> for a 5-step quickstart that registers multiple DB connections via a
> declarative `connections.yaml` and prints a ready-to-paste Claude Desktop
> config. The numbered steps below are for **local development** (running
> the MCP server directly without the wrapper script).
1. **Clone the repository**:
```bash
git clone https://github.com/glaubercosta/versadatabaseagent.git
cd versadatabaseagent
```
2. **Set up the virtual environment**:
```bash
python -m venv .venv
.\.venv\Scripts\activate
```
3. **Install dependencies**:
```bash
pip install -r requirements.txt
```
4. **Configure Environment**:
Create a `.env` file based on `src/templates/env_template.env`:
```env
OPENAI_API_KEY=your_openai_api_key
DB_TYPE=postgres
DB_HOST=localhost
DB_NAME=your_database
DB_USER=your_user
DB_PASSWORD=your_password
DB_PORT=5432
STATS_CACHE_TTL_SECONDS=1800
CATALOG_SNAPSHOT_STALE_AFTER_SECONDS=86400
# 0 only for single-operator local dev; the template defaults to 1, and HTTP
# mode forces it to 1 regardless. See env_template.env.
AUTHORIZATION_ENFORCEMENT_ENABLED=0
DEFAULT_ACTOR_ID=
RATE_LIMIT_WORKSPACE_PER_MINUTE=0
RATE_LIMIT_CONNECTION_PER_MINUTE=0
SECRET_ENCRYPTION_KEY=your_fernet_key
EMBEDDING_MODEL=nomic-embed-text
PROMPT_MAX_BYTES=4096
RAG_HYBRID_RERANK_ENABLED=1
RAG_HYBRID_MAX_RERANK_CANDIDATES=20
RAG_WEIGHT_VECTOR=2.0
RAG_WEIGHT_LEXICAL=1.0
RAG_WEIGHT_JOIN=1.0
RAG_WEIGHT_FRESHNESS_PENALTY=1.0
RAG_LOW_CONFIDENCE_THRESHOLD=0.30
RAG_INCLUDE_VIEWS=0
# Optional: export operational metrics via metadata-only OTLP sink
VERSA_OTLP_ENDPOINT=
# Optional: Chat cost meter configuration (USD per 1M tokens)
OPENAI_CHAT_MODEL=gpt-4o-mini
OPENAI_INPUT_COST_PER_1M=0.15
OPENAI_OUTPUT_COST_PER_1M=0.60
```
Supported databases: **PostgreSQL** (`postgres`) and **MySQL** (`mysql`).
5. **(Sprint 002) Initialize platform metadata schema**:
```bash
python scripts/bootstrap_platform_metadata.py
```
This creates workspace/project/connection metadata tables used for multi-tenant, multi-connection support.
For the full tester flow (register connections from a YAML + auto-encrypt
passwords + emit Claude Desktop snippet), prefer:
```bash
python -m scripts.register_connections
```
See [docs/TESTER_ONBOARDING.md](docs/TESTER_ONBOARDING.md).
## 🧪 Usage
### 1. Interactive Test Client
```bash
python -m src.test_mcp_client
```
(Run as a module so absolute `src.*` imports resolve. The script will not start with `python src/test_mcp_client.py`.)
- **Option 1**: List all registered MCP tools.
- **Option 2**: Call tools manually with JSON arguments.
- **Option 3**: **Chat Mode** — Talk to your database using natural language (requires OpenAI).
- **Option 4**: Semantic search in the RAG catalog.
- **Option 5**: Refresh the semantic catalog index.
- **Option 6**: Inspect table details (stats + sample rows).
- **Option 15**: Export scoped catalog snapshot JSON.
### 1.1 Semantic Catalog Operations (Sprint 004)
- Recommended refresh cadence: every 24h (`CATALOG_SNAPSHOT_STALE_AFTER_SECONDS=86400`).
- Use `refresh_semantic_catalog_async` for large catalogs and monitor with `get_refresh_semantic_catalog_status`.
- Snapshot-first tools:
- `search_semantic_catalog` for business-term discovery.
- `list_database_structure` / `inspect_join_paths` reuse fresh snapshot and fallback to live DB only when stale/missing.
- For operational freshness questions, use live tools (`query_database`, `list_empty_tables`, `get_table_stats`).
Troubleshooting sequence:
1. Confirm scope with `get_active_context`.
2. Verify connection capabilities with `get_connection_capabilities`.
3. Run async refresh and inspect status payload (`status`, `processed_tables`, `total_tables`, `result`/`error`).
4. If refresh result warns about metadata catalog access, verify MCP access to platform metadata storage (snapshot persistence may fail even when vector index refresh succeeds).
5. Export current snapshot with `export_catalog_snapshot_json` to confirm scope and payload content.
### 2. Chat Cost Meter
- Chat mode displays token usage and estimated cost per interaction.
- On exit (`exit`, `quit`, `0`), it prints a session summary with total prompt/completion tokens and estimated total cost.
- Cost estimation is configurable via:
- `OPENAI_CHAT_MODEL`
- `OPENAI_INPUT_COST_PER_1M`
- `OPENAI_OUTPUT_COST_PER_1M`
### 3. Running Tests
```bash
pytest tests/ -v
```
The suite includes **900+ tests** covering unit, integration, and security scenarios.
### 4. Sprint 004 Benchmark Evidence (Semantic Tool Synergy)
```bash
$env:PYTHONPATH='.'
python scripts/benchmark_semantic_tool_synergy.py
```
Generated artifacts:
- `engineering-artifacts/benchmark-sprint-004-semantic-tool-synergy.json`
- `engineering-artifacts/benchmark-sprint-004-semantic-tool-synergy.md`
### 5. Engineering Workflow (Anti-Duplication)
- Repeated logic must be extracted when it appears a second time.
- PRs should include an anti-duplication checklist in the description:
- no new relevant duplication introduced
- repeated flow extracted to shared helper/module
- tests remain green
- CI includes a duplicate-code guard (`pylint duplicate-code`) and test execution.
Exception protocol (allowed only with explicit PR note):
- performance-critical path with measured justification
- temporary migration bridge with follow-up removal ticket
- minimal duplication to preserve test readability
### 6. Registering as MCP Server
To use with clients like Deep Chat or Claude Desktop:
```json
{
"mcpServers": {
"versadatabaseagent": {
"command": "C:\\path\\to\\project\\.venv\\Scripts\\python.exe",
"args": [
"C:\\path\\to\\project\\src\\main.py"
]
}
}
}
```
## 📂 Architecture
```
src/
├── interface/ # MCP protocol and controllers
│ └── controllers/ # DatabaseController, RAGController
├── application/ # Business logic and orchestration
│ └── services/ # DatabaseService, RAGService
├── infrastructure/ # External integrations
│ ├── database_handler.py # SQLAlchemy, schema inspection, stats, relationships
│ └── rag_handler.py # ChromaDB vector store
└── main.py # Entry point, wiring, logging config
tests/ # 900+ TDD-based tests (pytest + pytest-mock)
```
**Design principles**:
- Clean Architecture with dependency inversion
- Metadata-only RAG (no raw data in vector store)
- Validated identifiers for all dynamic SQL
- Structured logging (`logging` module)
## 🔐 Production hardening checklist
Defense-in-depth guidance for any deployment. Most items are off by default for
local development convenience; production deployments **must** flip them on.
### Database account
- Create a dedicated read-only role per connection. The application's defenses
(sqlparse-based query validation, `READ ONLY` session, MySQL cross-database
guard) are belt-and-braces — the *authoritative* control is what the DB
itself allows the user to do. Grant `SELECT` only.
- For MySQL, scope the user to a single database (`GRANT SELECT ON db.* TO ...`)
so the regex-based cross-database check in `MySQLDatabaseAdapter` becomes
redundant rather than load-bearing.
- For PostgreSQL, make the user owner of *no* objects and revoke `CREATE` on
every schema; the engine session already runs `default_transaction_read_only=on`
with a `statement_timeout`, configurable via `DB_STATEMENT_TIMEOUT_MS`.
### Platform secrets
- Set `SECRET_ENCRYPTION_KEY` (Fernet key) on every non-dev environment.
The provider fails closed at startup if it's missing — the public dev
fallback is opt-in via `VERSA_ALLOW_DEV_KEY=1`.
- Rotate the key periodically; re-encrypt persisted `connection_secrets`.
### Authorization & scoping
- Enable `AUTHORIZATION_ENFORCEMENT_ENABLED=1` so workspace-role checks run
on every connection resolution. With it off, *any* MCP caller can resolve
*any* `connection_id`. When on, the central seam also enforces the per-tool
tool→role matrix (`viewer`/`operator`/`admin`), returning a 403 before the
tool executes; curation, refresh, and audit-read tools are gated this way.
See [docs/transport-security-posture.md](docs/transport-security-posture.md)
for the stdio vs HTTP enforcement posture.
- Enable `STRICT_SCOPE_ENFORCEMENT=1` for any deployment serving more than
one MCP session per process. The active-context shortcut is process-global
and otherwise leaks scope across concurrent sessions.
- `CONTEXT_MISMATCH_STRICT` controls how the server reacts when a tool call's
explicit `workspace_id`/`project_id`/`connection_id` diverges from the
active context (a common chat-mode pattern when an LLM re-emits stale args
after a context switch). Default `0` is **warn mode**: the divergence is
emitted as a `context_mismatch_detected` audit/metrics event and execution
proceeds under the active scope (preserving legacy override behavior). Set
to `1` to **block** instead, returning an actionable error and emitting
`context_mismatch_blocked`. Recommended rollout: deploy with warn mode,
observe the mismatch rate per scope/tool, then promote to strict once the
rate is stable and acceptably low.
### Server mode (HTTP transport)
- `VERSA_TRANSPORT` — `stdio` (default, MCP stdio) or `http` (streamable-HTTP
server mode). Set to `http` for shared intranet deployments.
- `VERSA_HTTP_HOST` — bind address for HTTP mode (default `0.0.0.0`).
- `VERSA_HTTP_PORT` — port for HTTP mode (default `8000`).
- `VERSA_API_KEYS` — comma-separated `name:secret` pairs used for Bearer-token
authentication in HTTP mode. Each `name` becomes the `actor_id` in the audit
trail. Format: `alice:secret_abc123,bob:secret_xyz789`. Leave empty to reject
all requests (safe default; server still starts and logs a warning).
- When `VERSA_TRANSPORT=http`, `AUTHORIZATION_ENFORCEMENT_ENABLED` and
`STRICT_SCOPE_ENFORCEMENT` are **automatically forced to `1`** regardless of
the configured value. Override attempts are logged as warnings.
### RAG / embeddings
- Set `CHROMA_PATH` to an absolute, owned directory. The handler creates it
with mode `0o700` and warns when the configured path is relative.
- Configure `OLLAMA_BASE_URL` and `OLLAMA_REQUEST_TIMEOUT` so a hung Ollama
cannot pin the refresh thread indefinitely.
### Rate limiting & metrics
- The bundled `RateLimitService` is **in-memory and per-process**: deployments
with multiple workers must front the server with an external rate limiter
(Redis token bucket, NGINX `limit_req`, an API gateway, etc.). The in-memory
hooks remain useful as a last-line guard but cannot share counters across
workers.
- `MetricsService` is in-memory by default — scrape `list_runtime_metrics` per
worker, or set `VERSA_OTLP_ENDPOINT` to export via the metadata-only OTLP
sink (OpenTelemetry is an optional/lazy dependency; the export carries no
query text or PII).
### Audit log
- Audit messages are sanitized before persistence (DSN credentials, embedded
SQL, and `[parameters: ...]` are redacted; messages are capped to 256 bytes).
Tracebacks go to stderr only via `logger.exception`. Forward stderr to a
log pipeline you control — never to a third-party log aggregator without
reviewing what the operator side will see.
## 📄 License
Proprietary — © Versa Tecnologia. All rights reserved. Not licensed for
redistribution or external use without explicit written permission.
