AI-SQL Generation & Chat
Relevant source files
The following files were used as context for generating this wiki page:
The AI-SQL Generation & Chat system is a core feature of KamehaDB that provides a schema-aware assistant to help users write, explain, and debug queries. It supports multiple database engines including PostgreSQL, MySQL, SQLite, MongoDB, and Redis, adapting its output syntax based on the active connection.
The system is built on a multi-provider architecture, allowing users to connect to local LLMs via Ollama or cloud-based services like OpenAI and 9Router. It leverages a local vector store (Qdrant) for semantic schema retrieval, ensuring the AI has the necessary context to generate accurate queries without overwhelming the model's context window.
Sources: README.md, AGENTS.md, apps/sidecar/src/routes/ai.ts:114-140
System Architecture
The AI chat functionality is split between a React-based frontend in the desktop app and a Hono-powered backend in the sidecar.
Data Flow Overview
The system follows a specific flow to ensure schema awareness:
- Request: The desktop app sends a user message and connection metadata to the sidecar.
- Context Building: The sidecar retrieves the database schema (DDL or MongoDB structure).
- Semantic Search: For large SQL schemas, the system uses Qdrant to find only the most relevant tables based on the user's query.
- Prompt Construction: A system prompt is generated, combining the schema context, connection type rules, and the conversation history.
- LLM Execution: The request is forwarded to the configured AI provider.
- Streaming Response: Results are streamed back to the UI via Server-Sent Events (SSE).
Sources: apps/sidecar/src/routes/ai.ts:142-230, apps/desktop/src/lib/api.ts:178-220
The diagram shows the end-to-end flow from user input to the execution of generated SQL.
Context & Schema Awareness
The assistant's accuracy is derived from its "Schema-Aware Context." The system builds specialized prompts depending on the database engine.
SQL Schema Context
For SQL databases, the sidecar generates DDL statements. If a vector store is available, it performs a semantic search to inject only the top-K relevant tables.
Sources: apps/sidecar/src/ai/qdrant-store.ts:286-310, apps/sidecar/src/routes/ai.ts:193-214
MongoDB Context
For MongoDB, the system samples documents to infer fields and types. It lists collections and their document counts to provide structural context.
Sources: apps/sidecar/src/routes/ai.ts:24-85
Prompt Engineering Rules
The system prompt enforces strict rules for the AI:
| Rule Category | Description |
|---|---|
| Output Format | Use specific code blocks: ```sql```, ```javascript``` (Mongo), or ```redis```. |
| Safety | Default to read-only queries; warn users if write operations are requested. |
| Tool Identity | Identify as an assistant embedded in "kamehadb". |
| Language | Generate ONLY valid queries without excessive commentary. |
Sources: apps/sidecar/src/routes/ai.ts:88-112
Provider Management & Settings
KamehaDB supports a variety of AI backends. Settings are persisted in a local SQLite database and cached in memory.
Supported Providers
- Ollama (Local/Cloud): Connects to
http://localhost:11434by default for private, local-only inference. - OpenAI: Uses standard GPT models (e.g.,
gpt-4o). - 9Router: Unified API for various models.
Sources: apps/sidecar/src/db/metadata-store.ts:10-38, packages/shared/src/index.ts:503-518
Sources: packages/shared/src/index.ts:503-512
Data Persistence & History
Conversations are scoped to specific database connections and persisted in the chat_messages table of the local metadata store.
Chat History Schema
| Field | Type | Description |
|---|---|---|
id | TEXT | Primary Key (nanoid). |
connection_id | TEXT | Links message to a specific connection profile. |
mongo_database | TEXT | Optional scope for specific MongoDB databases. |
role | TEXT | 'user' or 'assistant'. |
content | TEXT | The message text. |
created_at | TEXT | ISO 8601 timestamp. |
Sources: apps/sidecar/src/db/metadata-store.ts:114-124, apps/sidecar/src/db/metadata-store.ts:376-417
Frontend Implementation
The AIChatPanel component handles the user interface, message rendering, and integration with the SQL editor.
Key UI Features
- Message Rendering: Supports Markdown and syntax highlighting for SQL, JavaScript, and Redis.
- Action Buttons: Generated SQL blocks include "Copy", "Insert into editor", and "Run SQL" buttons.
- Streaming UI: Uses
@tanstack/ai-reactfor real-time response rendering and "Stop" support. - Resizing: The chat panel is a resizable sidebar (280px to 600px).
Sources: apps/desktop/src/components/ai-chat-panel.tsx:84-135, apps/desktop/src/components/ai-chat-panel.tsx:322-351
Interaction Logic
Technical Summary
The AI system in KamehaDB transforms a standard database GUI into an intelligent environment. By combining local metadata persistence (apps/sidecar/src/db/metadata-store.ts), semantic vector search (apps/sidecar/src/ai/qdrant-store.ts), and a flexible provider abstraction (apps/sidecar/src/routes/ai.ts), it enables developers to interact with complex schemas using natural language while maintaining data privacy through local-first principles.