AI Schema Indexing & Retrievals
Relevant source files
The following files were used as context for generating this wiki page:
KamehaDB implements an advanced AI-driven schema indexing and retrieval system to provide contextual awareness for its SQL generation and chat features. This system moves away from full-schema injection in system prompts, which can be token-intensive and inefficient, and instead utilizes semantic search to provide only the most relevant database metadata to the AI assistant.
The core of this system is the integration with Qdrant, a vector database that stores embedded representations of table DDLs and enriched metadata. When a user asks a question, the system performs a similarity search to retrieve the relevant schema context, which is then injected into the AI's system prompt for accurate query generation.
Sources: CHANGELOG.md:14-17, README.md:10-14, apps/sidecar/src/ai/qdrant-store.ts:1-5
Architecture Overview
The indexing and retrieval pipeline involves the Desktop frontend, the Sidecar backend (acting as the orchestrator), various Database Adapters, and Qdrant for vector storage.
The diagram shows the flow from a user query in the desktop app to the retrieval of relevant schema from Qdrant via the sidecar orchestrator.
Sources: apps/sidecar/src/routes/ai.ts:121-170, apps/sidecar/src/ai/qdrant-store.ts:1-10
Schema Indexing Process
The indexing process transforms raw database structures into searchable vector representations. This involves proactive schema indexing at startup or first use.
1. Metadata Enrichement
To improve search accuracy, the system doesn't just index raw SQL. It builds "enriched text" for each table, which includes:
- Table Purpose: Inferred based on table name and column structure (e.g., detecting junction tables).
- Column Purpose: Inferred based on name, type, and constraints (e.g., identifying primary keys, foreign keys, emails, or monetary values).
- Full DDL: The standard
CREATE TABLEandCREATE INDEXstatements.
Sources: apps/sidecar/src/ai/qdrant-store.ts:31-110, CHANGELOG.md:18-20
2. Hash-based Incremental Sync
To maintain efficiency, the system uses a SHA-256 hashing mechanism. It computes a hash of the table structure (columns, types, nullability, defaults, keys, and indexes).
- If the hash matches the one stored in Qdrant, the record is skipped.
- If the hash differs, the embedding is regenerated and updated.
- Orphaned points (tables that no longer exist in the database) are cleaned up during the sync.
Sources: apps/sidecar/src/ai/qdrant-store.ts:112-132, apps/sidecar/src/ai/qdrant-store.ts:228-235, CHANGELOG.md:18-20
3. Vectorization & Storage
Enriched text is converted into embeddings using the configured AI provider (e.g., OpenAI, Ollama). These vectors are then stored in connection-specific Qdrant collections.
| Data Component | Format / Logic | Source |
|---|---|---|
| Collection Name | schema_{connectionId} (sanitized) | qdrant-store.ts:7 |
| Vector Config | Cosine Distance | qdrant-store.ts:153 |
| Payload | tableId, ddl (enriched), hash | qdrant-store.ts:269 |
Sources: apps/sidecar/src/ai/qdrant-store.ts:7-10, apps/sidecar/src/ai/qdrant-store.ts:145-155, apps/sidecar/src/ai/qdrant-store.ts:265-275
Semantic Schema Retrieval
When a chat request arrives at /ai/chat, the Sidecar performs the following steps to retrieve the context:
- Extract Query: The latest user message is extracted from the chat history.
- Generate Embedding: The user message is converted into a vector.
- Similarity Search: Qdrant is queried to find the top $K$ (default 5) most relevant tables based on the user's intent.
- Prompt Injection: The DDLs of the retrieved tables are concatenated and injected into the system prompt.
The sequence diagram illustrates how a user query triggers a semantic search against Qdrant to retrieve only relevant table definitions before communicating with the LLM.
Sources: apps/sidecar/src/routes/ai.ts:160-200, apps/sidecar/src/ai/qdrant-store.ts:291-314, CHANGELOG.md:21-25
Key Implementation Details
Provider Configuration
The indexing and retrieval rely on the configured AI provider for embeddings. The system supports multiple providers.
| Provider | Default Model | Notes |
|---|---|---|
| Ollama (local) | llama3.2 | Default URL http://localhost:11434 |
| OpenAI | gpt-4o | Requires API Key |
| 9Router | Custom | Requires Base URL and API Key |
Sources: README.md:21-28, packages/shared/src/index.ts:267-277
Fail-safe Mechanisms
The system includes robust error handling for indexing:
- Fallback: If Qdrant is unreachable or the search fails, the sidecar falls back to full-DDL injection or proceeds without schema context so the chat remains functional.
- Validation: The
/ai/embedand/ai/chatroutes validate provider configurations before attempting vector operations, returningAI_CONFIG_ERRORif settings are missing.
Sources: CHANGELOG.md:14-17, CHANGELOG.md:65-70, apps/sidecar/src/routes/ai.ts:135-155
Summary
AI Schema Indexing and Retrieval in KamehaDB creates a "semantic map" of the user's database. By proactively indexing metadata into Qdrant using enriched descriptions and structural hashes, the system ensures that the AI assistant operates with precise, high-density context. This results in more accurate SQL generation while significantly reducing token consumption compared to naive schema injection methods.
Sources: CHANGELOG.md:14-25, apps/sidecar/src/ai/qdrant-store.ts