# How holaOS Handles Data Processing and Management: A Deep Dive into the SQLite-First Architecture

> Discover how holaOS manages data with its SQLite-first architecture. Learn about versioned databases, workspace isolation, and vector-powered search for efficient data processing.

- Repository: [holaboss.ai/holaOS](https://github.com/holaboss-ai/holaOS)
- Tags: deep-dive
- Published: 2026-08-15

---

**holaOS stores all user-generated data in versioned SQLite databases managed by the `runtime-state-store` package, with workspace-isolated files, typed migrations, and vector-powered semantic search.**

The open-source holaOS project from Holaboss AI takes a deliberate **database-per-workspace approach** to data processing and management. Rather than relying on external services, the entire platform persists workspace files, interaction logs, semantic embeddings, and integration graphs in local SQLite databases with strict schema versioning. This article explores the three-layer storage architecture, the migration engine that enables safe evolution, and how the system surfaces data through typed APIs and channel gateways.

---

## The Three-Layer Storage Architecture

holaOS organizes data across three logical layers, each optimized for different access patterns and lifecycle requirements.

### Root State: Global Host Configuration

The **host-state.db** serves as the system-wide registry. Created on startup, it holds:

- Globally installed plugins and capabilities
- A **folded monolith** of legacy workspace rows consolidated from earlier versions
- The `HOST_STATE_MONOLITH_FOLDED_MARKER_KEY` constant (line 42 of [`runtime/state-store/src/store.ts`](https://github.com/holaboss-ai/holaOS/blob/main/runtime/state-store/src/store.ts)) that prevents re-processing deleted workspaces

This root database is intentionally minimal—most data lives in workspace-scoped files.

### Workspace Runtime: Isolated Per-Workspace Databases

Each workspace maintains its own SQLite file at `.holaboss/runtime.db`. The path is constructed by `workspaceRuntimeDbPathForWorkspacePath` (line 1463) using constants:

```typescript
WORKSPACE_RUNTIME_DIRNAME = ".holaboss"
WORKSPACE_RUNTIME_DB_FILENAME = "runtime.db"

```

These databases contain:

| Data Type | Stored In |
|-----------|-----------|
| Session history and turn results | `sessions`, `turn_results` tables |
| Semantic memory entries with embeddings | `semantic_memory_search_docs`, `semantic_memory_nodes` |
| Integration knowledge graphs | `integration_trees`, `integration_leaves`, `integration_node_embeddings` |
| Workspace artifacts and metadata | Various artifact tables |

Workspace identity is protected by a lock file mechanism (`workspace_id.lock`, line 27) with `WORKSPACE_IDENTITY_LOCK_RETRY_ATTEMPTS` (line 32) retries. Failures surface as `RuntimeStateStoreWorkspaceError` types (lines 88-95) for precise debugging.

### Control-Plane: Optional Cross-Workspace Analytics

When `HOLABOSS_CONTROL_PLANE_DB_PATH` is set, holaOS builds a **read-only control-plane.db** aggregating integration trees and other cross-workspace data. Migration [`030-integration-tables-control-plane-only.ts`](https://github.com/holaboss-ai/holaOS/blob/main/030-integration-tables-control-plane-only.ts) isolates these tables to prevent root database bloat while enabling global discovery queries.

---

## Database Engine and Migration System

### SQLite with Extensions

All databases open via **better-sqlite3** (line 1, [`store.ts`](https://github.com/holaboss-ai/holaOS/blob/main/store.ts)) with optional **sqlite-vec** (line 8) for vector operations. This combination provides:

- Synchronous, performant SQLite access
- Native vector similarity search without external services
- Portable, file-based storage suitable for local-first deployments

### Versioned Schema Migrations

The `MigrationRunner` (line 10) executes sequential migrations from `runtime/state-store/src/migrations/`. Each migration exports an `up` function that:

1. Modifies schema (CREATE TABLE, ALTER COLUMN, etc.)
2. Backfills existing data when necessary
3. Logs `MigrationLogEvent`s for audit trails

The runner **continues on best-effort failure** (line 16484) so partially migrated databases still start—a pragmatic choice for development environments.

```typescript
// Example migration structure (033-session-org-id.ts pattern)
export async function up(db: Database) {
  await db.exec(`
    ALTER TABLE sessions ADD COLUMN org_id TEXT;
    UPDATE sessions SET org_id = 'default' WHERE org_id IS NULL;
  `);
}

```

---

## Runtime Data Access via Typed APIs

The **`RuntimeStateStore`** interface (`@holaboss/runtime-state-store`) abstracts all database operations. Key methods consumed by the API server include:

- `listSemanticMemorySearchDocs()` – retrieves vector-indexed documents
- `searchSemanticMemorySearchDocs(query)` – executes hybrid vector+lexical search
- `getSemanticMemoryNode(nodeId)` – fetches specific semantic entities
- `listIntegrationTrees(workspaceId)` – reads integration knowledge graphs

The API server module [`runtime/api-server/src/workspace-memory.ts`](https://github.com/holaboss-ai/holaOS/blob/main/runtime/api-server/src/workspace-memory.ts) demonstrates typical usage:

```typescript
import type {
  IntegrationTreeRecord,
  InteractionEntityRecord,
  RuntimeStateStore,
  SemanticMemoryCategory,
} from "@holaboss/runtime-state-store";

export async function getWorkspaceMemory(
  store: RuntimeStateStore,
  workspaceId: string,
) {
  const trees = await store.listIntegrationTrees(workspaceId);
  const interactions = await store.listInteractionEntities(workspaceId);
  // Combine, rank, and return hybrid retrieval result
}

```

This abstraction ensures callers never write raw SQL while enabling the store to optimize queries internally.

---

## Semantic Memory and Hybrid Retrieval

holaOS implements **per-workspace semantic search** using `sqlite-vec` embeddings. The retrieval pipeline in [`workspace-memory.ts`](https://github.com/holaboss-ai/holaOS/blob/main/workspace-memory.ts) operates in four stages:

1. **Tokenization** (`tokenize`, line 86) normalizes the query
2. **Vector first pass** fetches candidates via `queryMemoryModelEmbedding`
3. **Lexical fallback** (`DIRECT_LEXICAL_SCAN_*`) supplements when vector coverage is insufficient
4. **Result combination** via `buildMemoryHybridRetrievalResult` (line 17 import)

Tunable constants control this behavior:

```typescript
VECTOR_FIRST_PASS_LIMIT_FLOOR = 20
VECTOR_FIRST_PASS_LIMIT_CEILING = 100
LEXICAL_SUPPORT_LIMIT_FLOOR = 10
LEXICAL_SUPPORT_LIMIT_CEILING = 50
SEMANTIC_MEMORY_PLANNER_STATS_MARKER = 29  // Bump to force ANALYZE

```

The `WorkspaceMemoryExecutionProfile` (lines 11-14) lets callers disable embeddings or LLM reranking for latency-sensitive paths.

---

## Integration Knowledge Graph

External service integrations are modeled as **trees and leaves** in dedicated tables:

- `integration_trees` – root nodes per integration instance
- `integration_leaves` – actionable endpoints or resources
- `integration_node_embeddings` – vector representations for semantic discovery

These tables are **excluded from root consolidation** via `INTEGRATION_GRAPH_ROOT_SKIP_TABLES` (line 81) because they can be rebuilt from workspace configuration. Migration 030 optionally migrates them to the control-plane database for cross-workspace analytics without duplicating into every workspace file.

---

## Channel Gateway: Formatting Data for External Services

The **channel-gateway** package (`runtime/channel-gateway`) transforms internal semantic nodes into platform-specific messages. Each connector implements a format module:

**Telegram HTML formatter** ([`runtime/channel-gateway/src/format/telegram-html.ts`](https://github.com/holaboss-ai/holaOS/blob/main/runtime/channel-gateway/src/format/telegram-html.ts)):

```typescript
export function formatTelegramHtml(node: WorkspaceSemanticNode): string {
  return `<b>${escapeHtml(node.title)}</b>\n${escapeHtml(node.body)}`;
}

```

**WeChat connector** ([`runtime/channel-gateway/src/connectors/wechat.ts`](https://github.com/holaboss-ai/holaOS/blob/main/runtime/channel-gateway/src/connectors/wechat.ts)) handles authentication, rate limiting, and message egress to WeChat servers.

This **format-aware gateway** ensures semantic memory retrieved via the store can reach users on any supported platform with appropriate rendering.

---

## Complete Data Flow Example

```typescript
import { RuntimeStateStore } from "@holaboss/runtime-state-store";
import { getWorkspaceMemory } from "runtime/api-server/src/workspace-memory.js";
import { formatTelegramHtml } from "runtime/channel-gateway/src/format/telegram-html.js";

// 1. Initialize store (creates DBs, runs pending migrations)
const store = new RuntimeStateStore();

// 2. Retrieve hybrid memory results for a workspace
const memory = await getWorkspaceMemory(store, "proj-2024-q3");

// 3. Format top result for Telegram delivery
const message = formatTelegramHtml(memory.hits[0].semanticNode);

// 4. Egress via platform connector (connector.send omitted)

```

All symbols trace to source files in [`runtime/state-store/src/store.ts`](https://github.com/holaboss-ai/holaOS/blob/main/runtime/state-store/src/store.ts), [`runtime/api-server/src/workspace-memory.ts`](https://github.com/holaboss-ai/holaOS/blob/main/runtime/api-server/src/workspace-memory.ts), and [`runtime/channel-gateway/src/format/telegram-html.ts`](https://github.com/holaboss-ai/holaOS/blob/main/runtime/channel-gateway/src/format/telegram-html.ts).

---

## Summary

- **holaOS uses SQLite as the primary data store**, with separate files for host state, each workspace, and optional control-plane analytics
- **Versioned migrations** in `runtime/state-store/src/migrations/` guarantee backward-compatible schema evolution
- **Workspace isolation** through file-per-workspace databases protects data boundaries and enables portability
- **Semantic search** combines `sqlite-vec` embeddings with lexical fallback for hybrid retrieval
- **Typed APIs** via `RuntimeStateStore` abstract all data access, consumed by API server and channel gateway modules
- **Channel formatters** transform internal data for Telegram, Slack, Discord, WeChat, and WeCom platforms

---

## Frequently Asked Questions

### What database does holaOS use for data storage?

holaOS uses **SQLite** for all persistent storage, managed through the `better-sqlite3` library with optional `sqlite-vec` extension for vector search. The system creates three types of database files: a global `host-state.db`, individual `runtime.db` files per workspace, and an optional `control-plane.db` for cross-workspace analytics.

### How does holaOS handle schema changes and database migrations?

The `MigrationRunner` class sequentially applies TypeScript migration files from `runtime/state-store/src/migrations/`. Each migration contains an `up` function that modifies schema and backfills data. The runner logs all operations and continues on partial failure, allowing the system to start even with incomplete migrations.

### Where is semantic search data stored in holaOS?

Semantic embeddings and searchable documents live in **per-workspace SQLite databases** under `.holaboss/runtime.db`, specifically in tables like `semantic_memory_search_docs` and `semantic_memory_nodes`. The `sqlite-vec` extension enables native vector similarity search without external vector databases.

### How does holaOS prevent data corruption during concurrent access?

Each workspace uses a **file-based lock** (`workspace_id.lock`) with configurable retry attempts (`WORKSPACE_IDENTITY_LOCK_RETRY_ATTEMPTS`). The `RuntimeStateStore` wraps errors in `RuntimeStateStoreWorkspaceError` types to provide specific failure diagnostics when lock acquisition fails.