Bottom-Layer Storage Databases in TencentDB-Agent-Memory: SQLite and ClickHouse Architecture Explained

TencentDB-Agent-Memory uses SQLite as its primary asset store for code-graph and wiki data, and ClickHouse for high-throughput telemetry analytics, forming a dual-database persistence layer.

The TencentDB-Agent-Memory repository implements a hybrid storage architecture that separates durable asset persistence from high-volume analytics. According to the source code, the bottom-layer storage relies on two distinct database technologies: SQLite handles structured knowledge assets and fallback key-value operations, while ClickHouse manages telemetry ingestion and session analytics.

SQLite: The Primary Asset Store for Knowledge and Proxy Data

SQLite serves as the foundational persistent storage for the repository’s core data models. The implementation uses the better-sqlite3 driver wrapped with drizzle-orm to provide type-safe access patterns.

Core Implementation Files

The SQLite integration spans two main components:

• MemoryKnowledge/src/store/sqlite-store.ts – Implements SqliteKnowledgeStore for CRUD operations on wiki assets and code-graph entities
• MemoryKnowledge/src/db/client.ts – Initializes the database connection, runs schema migrations, and exports a typed Db handle
• MemoryProxy/src/storage/sqlite-storage.ts – Provides SqliteStorage, a fallback KV store for session state when object storage is unavailable

SqliteKnowledgeStore: Managing Wiki and Code-Graph Assets

The SqliteKnowledgeStore class encapsulates all asset persistence logic, including audit trails and recovery mechanisms. It stores service-specific wiki entries, team metadata, and graph relationships in normalized SQLite tables.

import { createDb } from "./MemoryKnowledge/src/db/client.ts";
import { SqliteKnowledgeStore } from "./MemoryKnowledge/src/store/sqlite-store.ts";

// Initialise an in-memory SQLite DB (for tests) or provide a file path.
const { db } = createDb({ path: ":memory:", autoMigrate: true });

// Build the knowledge store wrapper.
const knowledgeStore = new SqliteKnowledgeStore(db);

// ----- Create a wiki asset (idempotent) -----
const { row: wikiRow, existed } = knowledgeStore.createWiki({
  service_id: "svc-01",
  team_id: "team-alpha",
  name: "Project Overview",
  source_type: "markdown",
  source_url: null,
});
console.log(`Wiki ${existed ? "already existed" : "created"} – id: ${wikiRow.wiki_id}`);

// ----- Retrieve the same wiki by service & id -----
const fetched = knowledgeStore.getWiki("svc-01", "team-alpha", wikiRow.wiki_id);
console.log("Fetched wiki title:", fetched?.name);

SqliteStorage: Fallback KV Persistence in the Proxy

In the MemoryProxy component, SqliteStorage acts as a resilient fallback when primary object storage (COS) fails or is disabled. It implements a simple key-value interface for transient session data, injection states, and skill configurations.

import { openSqliteDb } from "./MemoryProxy/src/db/index.ts";
import { SqliteStorage } from "./MemoryProxy/src/storage/sqlite-storage.ts";

const db = openSqliteDb("/tmp/proxy.db");          // creates the file if missing
const kvStore = new SqliteStorage(db);

// Store a simple key/value pair.
await kvStore.putText("session:12345:data", JSON.stringify({ user: "alice" }));

// Retrieve it later.
const raw = await kvStore.getText("session:12345:data");
console.log("Session payload:", JSON.parse(raw!));

ClickHouse: High-Throughput Telemetry and Analytics Storage

While SQLite handles transactional asset storage, ClickHouse serves as the columnar database for telemetry ingestion. The repository uses the official @clickhouse/client SDK to batch-write high-frequency events without blocking the main application threads.

Telemetry Architecture

ClickHouse stores usage logs, session traces, and tool-call analytics in a separate database schema (tdai_telemetry by default). The telemetry subsystem operates independently from the SQLite asset store, allowing the system to handle bursty write loads without impacting knowledge retrieval latency.

Key implementation files include:

• MemoryKnowledge/src/clickhouse-telemetry.ts – Defines createKnowledgeTelemetry and event-specific loggers like logWikiEvent
• MemoryProxy/src/clickhouse.ts – Generic ClickHouse writer for proxy-level analytics and usage metrics

Writing Telemetry Events to ClickHouse

The telemetry client batches events and flushes them based on time intervals or record thresholds, optimizing for ClickHouse’s columnar ingestion strengths.

import { createKnowledgeTelemetry } from "./MemoryKnowledge/src/clickhouse-telemetry.ts";

// Example ClickHouse config (normally read from config.yaml)
const chConfig = {
  enabled: true,
  url: "http://clickhouse.example.com:8123",
  database: "tdai_telemetry",
  table: "knowledge_events",
  user: "admin",
  password: "*****",
  flushIntervalMs: 5_000,
  flushThreshold: 100,
};

// Initialise the telemetry client.
const telemetry = createKnowledgeTelemetry(chConfig);

// Log a wiki creation event.
await telemetry.logWikiEvent({
  wiki_id: "wiki-123",
  service_id: "svc-01",
  team_id: "team-alpha",
  action: "create",
  version: 1,
  user_id: "user-42",
  created_at: new Date().toISOString(),
});

Architecture Overview: How the Two Databases Complement Each Other

The bottom-layer storage design separates concerns by data access patterns:

• SQLite – Optimized for ACID compliance, relational joins between wiki assets and code-graph nodes, and single-record lookups. Used when data integrity and transactional consistency are required.
• ClickHouse – Optimized for append-only analytics, high-cardinality aggregations, and time-series queries. Used when append throughput and columnar compression outweigh transactional needs.

This dual-database approach ensures that knowledge retrieval remains fast and consistent via SQLite, while observability data scales horizontally through ClickHouse’s distributed architecture.

Summary

• Primary asset storage relies on SQLite accessed through better-sqlite3 and drizzle-orm, implemented in SqliteKnowledgeStore and SqliteStorage.
• Telemetry and analytics use ClickHouse via the @clickhouse/client SDK, with dedicated writers in clickhouse-telemetry.ts and clickhouse.ts.
• File locations are MemoryKnowledge/src/store/sqlite-store.ts for knowledge assets and MemoryProxy/src/storage/sqlite-storage.ts for proxy KV fallback.
• Fallback layers (COS, filesystem, in-memory) exist in the proxy component but do not constitute the bottom-layer persistent databases.

Frequently Asked Questions

Does TencentDB-Agent-Memory use only SQLite for storage?

No. While SQLite serves as the primary durable store for wiki assets, code-graph data, and proxy fallback state, the architecture deliberately uses ClickHouse for telemetry analytics. This separation prevents analytical query loads from impacting transactional asset performance.

Why does the repository use ClickHouse instead of SQLite for telemetry?

ClickHouse’s columnar storage engine and vectorized query execution handle high-throughput event ingestion more efficiently than SQLite’s row-based structure. The createKnowledgeTelemetry function batches writes to ClickHouse tables, supporting flush intervals and thresholds that SQLite cannot sustain under heavy load.

Can I run TencentDB-Agent-Memory without ClickHouse?

Yes, but with limited functionality. The telemetry subsystem checks the enabled flag in the configuration. If ClickHouse is disabled, the system continues operating using SQLite for assets, though you lose analytics, usage tracking, and session logging capabilities.

What is the difference between SqliteKnowledgeStore and SqliteStorage?

SqliteKnowledgeStore (in MemoryKnowledge/src/store/sqlite-store.ts) provides a rich domain-specific interface for wiki and code-graph CRUD operations with audit logging. SqliteStorage (in MemoryProxy/src/storage/sqlite-storage.ts) offers a generic key-value interface for transient proxy data, acting as a degradation fallback when object storage is unavailable.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}