Database Schema Caching

Relevant source files

The following files were used as context for generating this wiki page:

• apps/sidecar/src/lib/cache.ts
• apps/sidecar/src/routes/sql.ts
• apps/sidecar/src/routes/ai.ts
• apps/sidecar/src/ai/qdrant-store.ts
• apps/desktop/src/hooks/use-schema.ts
• packages/shared/src/index.ts

Database Schema Caching in KamehaDB is a multi-layered system designed to optimize performance and reduce database load by persisting metadata such as table structures, column definitions, and AI-optimized schema contexts. The system operates primarily within the sidecar Hono server, utilizing an In-Memory LRU (Least Recently Used) cache and a vector-based semantic store for AI context.

The caching mechanism ensures that frequent UI operations—such as browsing the schema tree, utilizing SQL autocomplete, or generating AI queries—remain responsive without constantly re-querying the target database.

Architecture and Cache Layers

KamehaDB implements caching at three distinct levels: the Client-side (Desktop app hooks), the Sidecar API layer (In-memory), and the AI Semantic Layer (Vector Database).

Cache Layer Summary

Layer	Implementation	Purpose	TTL / Expiry
Client Hook	TanStack Query	Minimizes network requests from Desktop to Sidecar	30s to 5m
In-Memory	lru-cache	Stores raw SQL metadata (tables, columns, indexes)	30s to 5m
Semantic	Qdrant	Stores DDL and enriched text for AI relevance search	Persistent / Manual Sync

Sources: apps/sidecar/src/lib/cache.ts:5-9, apps/desktop/src/hooks/use-schema.ts:4-5

Data Flow Diagram

The following diagram illustrates how a schema request is resolved through the various cache layers.

In-Memory Metadata Caching

The Sidecar uses a centralized LRUCache to store results from database adapters. This cache is keyed by a string combining the operation type, connection ID, and specific identifiers (like table names or schemas).

Key Functions

• getCached<T>(key: string, ttl): Retrieves data if the entry exists and has not exceeded its TTL.
• setCache(key: string, data: unknown): Persists data with a current timestamp.
• clearSchemaCache(connectionId: string): Targeted deletion of schema-related keys for a specific connection.

Sources: apps/sidecar/src/lib/cache.ts:13-33

Cache TTL Configuration

Constant	Value	Applied To
CACHE_TTL.SCHEMA	5 minutes	Databases, Schemas, Tables, Columns, Indexes
CACHE_TTL.STATS	30 seconds	Table stats, Index usage, Database sizes
CACHE_TTL.AI_SCHEMA	5 minutes	Generated DDL strings for LLM context

Sources: apps/sidecar/src/lib/cache.ts:5-9

AI Schema Context and Semantic Indexing

To support AI features, KamehaDB caches more than just raw metadata. It generates "Enriched Text" including DDL and inferred purposes for tables and columns, which is then stored in a Qdrant vector database.

Semantic Storage Logic

The system computes a hash for every table based on its columns, types, and constraints. If the hash changes, the cache is invalidated and the table is re-indexed.

Enriched Metadata Structures

The semantic cache stores an "enriched text" representation of the schema, which includes:

• Table Purpose: Inferred based on naming conventions (e.g., singular vs plural) and column counts.
• Column Purpose: Inferred based on names (e.g., created_at identified as a timestamp).
• DDL: The raw CREATE TABLE statement.

Sources: apps/sidecar/src/ai/qdrant-store.ts:47-118

Cache Invalidation

Cache entries are invalidated through several mechanisms to ensure the UI does not show stale data:

1. TTL Expiry: Automatically handled by the getCached logic comparing current time against the stored timestamp.
2. Manual Clear: Users can trigger /ai/clear-schema-cache/:connectionId which invokes clearSchemaCache in the library.
3. Hash Mismatch: During AI indexing, computeTableHash is used to detect structural changes in tables; if the hash differs from the one in Qdrant, the point is updated.

Sources: apps/sidecar/src/lib/cache.ts:35-46, apps/sidecar/src/ai/qdrant-store.ts:141-160, apps/sidecar/src/routes/ai.ts:318-323

Summary

Database Schema Caching in KamehaDB is essential for maintaining local-first performance. By utilizing a 5-minute TTL for structural metadata and a semantic vector store for AI context, the application balances responsiveness with data accuracy. The integration of TanStack Query on the frontend and an LRU cache on the backend provides a robust fallback system that protects target databases from excessive traffic while enabling advanced features like schema-aware AI chat.

Sources: apps/desktop/src/hooks/use-schema.ts, apps/sidecar/src/lib/cache.ts, apps/sidecar/src/ai/qdrant-store.ts