Sidecar Route Architecture

Relevant source files

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

• apps/sidecar/src/index.ts (Referenced via AGENTS.md)
• apps/desktop/src/lib/api.ts
• packages/shared/src/index.ts
• AGENTS.md
• PLAN.md

The Sidecar Route Architecture forms the backend core of KamehaDB, implemented as a local Node.js HTTP server. Its primary purpose is to act as a secure, local-only bridge between the Tauri-based desktop interface and various database engines, including PostgreSQL, MySQL, SQLite, MongoDB, and Redis. By binding strictly to 127.0.0.1, the sidecar ensures that database credentials and raw data never leave the user's machine.

This architecture is built using the Hono web framework, providing a lightweight and high-performance routing layer. It manages connection pooling, metadata persistence via a local SQLite database, and provides a unified API for the frontend to perform schema introspection and query execution across different database protocols.

Sources: PLAN.md:65-71, AGENTS.md:58-61, AGENTS.md:144-148

Core Architectural Components

The sidecar operates through a series of specialized route groups, each dedicated to a specific database type or system function. It leverages a "Shared Contract" defined in the @kamehadb/shared package to ensure type safety between the frontend requests and backend responses.

Request Lifecycle and Validation

Every request entering the sidecar undergoes validation using Zod schemas. This ensures that the data structures for connection profiles, SQL queries, and AI configurations are consistent across the entire workspace.

The diagram shows the standard flow of a request from the UI through the sidecar's validation and adapter layers to the target database.
Sources: PLAN.md:104-111, AGENTS.md:88-92

Primary Route Groups

The sidecar organizes functionality into five major route categories. These are accessible via the api object in the desktop application.

1. Connection Management (/connections)

Handles the CRUD operations for database profiles. It uses a local SQLite metadata store (defaulting to kamehadb.db) to persist profile details, while actual secrets like passwords should be handled via the OS keychain.

Endpoint	Method	Description
/connections	GET	Lists all saved connection profiles.
/connections	POST	Creates a new connection profile.
/connections/:id	PATCH	Updates an existing profile.
/connections/test	POST	Performs a pre-save connection attempt.

Sources: AGENTS.md:67-68, PLAN.md:144-150, apps/desktop/src/lib/api.ts:41-59

2. SQL Operations (/sql)

Provides metadata and query execution for relational databases (Postgres, MySQL, SQLite). It includes specialized routes for PostgreSQL performance statistics.

• Metadata: Retrieves schemas, tables, columns, and indexes.
• Query Safety: Implements a safety guard that blocks destructive keywords (e.g., DROP, DELETE) when a connection is in read-only mode.
• Stats: Provides table and index-specific metrics, primarily for PostgreSQL.

Sources: AGENTS.md:69-70, packages/shared/src/index.ts:468-479, apps/desktop/src/lib/api.ts:98-112

3. NoSQL & Key-Value Routes (/mongo, /redis)

Unlike the generic SQL route, MongoDB and Redis have dedicated handlers due to their unique protocols and data structures.

• MongoDB: Supports database/collection listing, document find/update/delete operations, and aggregation commands.
• Redis: Focuses on key scanning, TTL lookups, value retrieval, and raw command execution.

Sources: AGENTS.md:71-72, apps/desktop/src/lib/api.ts:115-144

4. AI and Schema Context (/ai)

This group manages the "AI Chat" feature, which is schema-aware. It handles:

• Provider Config: Managing settings for OpenAI, Ollama, and 9Router.
• Streaming: Utilizes Server-Sent Events (SSE) via @tanstack/ai to stream chat responses to the UI.
• Context Generation: Caching and injecting database schemas into AI prompts to enable natural language to SQL generation.

Sources: AGENTS.md:73-74, apps/desktop/src/lib/api.ts:74-96, apps/desktop/src/lib/api.ts:167-175

Database Adapter Interface

The Sidecar uses a standardized interface for different database engines, ensuring the routing logic remains decoupled from specific driver implementations.

The diagram illustrates how the Sidecar routes use common interfaces to interact with various database engines.
Sources: packages/shared/src/index.ts:133-145, packages/shared/src/index.ts:211-220, packages/shared/src/index.ts:245-263

Technical Implementation Details

Configuration and Environment

The sidecar's behavior is influenced by environment variables, primarily for data persistence.

• KAMEHADB_DATA_DIR: Sets the directory for the SQLite metadata database.
• Port Selection: While the default is 3170, the sidecar can print KAMEHADB_SIDECAR_PORT=<port> to inform the Tauri parent of its location.

Security Guards

A critical part of the route architecture is the isQuerySafe utility. It checks outgoing SQL queries against a blacklist of destructive keywords.

// From packages/shared/src/index.ts
export const DESTRUCTIVE_KEYWORDS = [
  'DROP', 'TRUNCATE', 'ALTER', 'CREATE', 'INSERT', 
  'UPDATE', 'DELETE', 'MERGE', 'GRANT', 'REVOKE',
];

export function isQuerySafe(sql: string): { safe: boolean; reason?: string } {
  const normalized = sql.trim().toUpperCase();
  for (const kw of DESTRUCTIVE_KEYWORDS) {
    const regex = new RegExp(`\\b${kw}\\b`);
    if (regex.test(normalized)) {
      return { safe: false, reason: `${kw} statements are not allowed in read-only mode` };
    }
  }
  return { safe: true };
}

Sources: AGENTS.md:62-65, packages/shared/src/index.ts:468-490

Conclusion

The Sidecar Route Architecture provides a robust and secure foundation for KamehaDB's multi-database capabilities. By abstracting database-specific complexities into standardized routes and adapters, it allows the desktop application to interact with diverse data engines through a single, type-safe API. The integration of AI schema context and safety guards further enhances its role as a developer-centric local backend.