# CloddsBot Database Architecture: How SQLite, LanceDB, and PostgreSQL Work Together

> Explore CloddsBot's polyglot database architecture. Discover how SQLite, LanceDB, and PostgreSQL work together for local state, vector search, and production scaling.

- Repository: [AL/CloddsBot](https://github.com/alsk1992/CloddsBot)
- Tags: architecture
- Published: 2026-09-11

---

**CloddsBot uses a polyglot persistence layer that defaults to SQLite for local state, leverages LanceDB for semantic vector search, and scales to PostgreSQL in production via a unified Database interface.**

The open-source trading bot `alsk1992/CloddsBot` implements a hybrid database architecture designed to balance ease of local development with production scalability. Rather than forcing a single storage engine, the CloddsBot database architecture abstracts three distinct backends—SQLite, LanceDB, and PostgreSQL—behind a common interface, allowing the same codebase to run on a developer laptop or a distributed server cluster without modification.

## The Three-Engine Storage Strategy

CloddsBot partitions its data across three specialized engines, each selected for specific performance characteristics and operational requirements.

### SQLite for Local File-Based Persistence

By default, CloddsBot stores all runtime state in a local SQLite database using [`sql.js`](https://github.com/alsk1992/CloddsBot/blob/main/sql.js) (the SQLite WebAssembly implementation). In [`src/db/index.ts`](https://github.com/alsk1992/CloddsBot/blob/main/src/db/index.ts), the `initDatabase` function initializes this file at `${stateDir}/clodds.db`, creating a portable, zero-configuration data store that requires no external server.

The SQLite schema supports a comprehensive trading data model including **Users**, **Alerts**, **Positions**, **Portfolio snapshots**, **Stop-loss triggers**, **Market cache**, **Sessions**, **Cron jobs**, and **Trading credentials**. It also maintains exchange-specific tables for Hyperliquid, Binance-Futures, Bybit-Futures, MEXC-Futures, Polymarket, Drift, and Jupiter swaps. This file-based approach ensures that self-hosted bots can run immediately without infrastructure setup.

### LanceDB for Semantic Vector Search

For semantic search capabilities on market indices, CloddsBot integrates **LanceDB**, a modern on-disk vector store. The system stores dense vector embeddings in the `market_index_embeddings` table within SQLite as JSON-encoded TEXT fields, then leverages LanceDB's ANN (Approximate Nearest Neighbor) indexing for fast similarity queries.

The embedding pipeline uses the `@xenova/transformers` library with the `Xenova/all-MiniLM-L6-v2` model. When `upsertMarketIndexEmbedding` is called in [`src/db/index.ts`](https://github.com/alsk1992/CloddsBot/blob/main/src/db/index.ts), it serializes the embedding vector to JSON, stores it in the `vector` column, and LanceDB builds the index for cosine-similarity searches. This architecture enables the "search market" skill to perform semantic matching against market questions and descriptions without requiring a separate vector database server.

### PostgreSQL for Production Scale

When deployed to server environments, CloddsBot switches to **PostgreSQL** via the `pg` driver (version `^8.17.2`). The [`src/db/postgres.ts`](https://github.com/alsk1992/CloddsBot/blob/main/src/db/postgres.ts) module creates a `pg.Pool` using the `DATABASE_URL` environment variable, while [`src/db/migrations.ts`](https://github.com/alsk1992/CloddsBot/blob/main/src/db/migrations.ts) executes the identical schema against the PostgreSQL server.

This backend supports TimescaleDB extensions for high-performance time-series analytics on trade history tables. By setting `DB_BACKEND=postgres`, operators gain PostgreSQL's durability, concurrent connection handling, and advanced indexing while maintaining the same logical data model used in SQLite.

## How the Database Abstraction Works

The CloddsBot database architecture relies on a runtime abstraction that decouples business logic from storage implementation.

### The Unified Database Interface

All database operations flow through a single `Database` interface defined in [`src/db/index.ts`](https://github.com/alsk1992/CloddsBot/blob/main/src/db/index.ts). Whether using SQLite or PostgreSQL, the bot calls `db.run(sql, params)` for writes and `db.query<T>(sql, params)` for reads. The `initDatabase` function returns the appropriate implementation based on the `process.env.DB_BACKEND` flag, defaulting to `sqlite` if unspecified.

This abstraction allows trading skills—such as those in [`src/agents/handlers/virtuals.ts`](https://github.com/alsk1992/CloddsBot/blob/main/src/agents/handlers/virtuals.ts)—to call `db.getCachedMarket` or `db.getMarketIndexEmbedding` without knowing which engine handles the request.

### Schema Management and Migration

The complete database schema lives in a single location: a `CREATE TABLE` block starting at line 1002 in [`src/db/index.ts`](https://github.com/alsk1992/CloddsBot/blob/main/src/db/index.ts). This block defines every table, index, and constraint for the entire application, ensuring schema consistency across SQLite and PostgreSQL backends.

The migration system executes these definitions against the active connection, creating tables like `market_index` (for market metadata) and `market_index_embeddings` (for vector storage) regardless of the underlying engine.

### Runtime Backend Selection

Switching between engines requires only environment variable changes:

- **SQLite (default)**: No configuration needed; creates `clodds.db` in the state directory.
- **PostgreSQL**: Set `DB_BACKEND=postgres` and provide `DATABASE_URL=postgres://user:pwd@host:5432/clodds`.

The [`src/db/index.ts`](https://github.com/alsk1992/CloddsBot/blob/main/src/db/index.ts) module lazily instantiates the correct driver pool, allowing the bot to boot with file-based storage for local testing or connection-pooled PostgreSQL for production.

## Implementation Examples

### Initializing the Database

```typescript
import { initDatabase } from './db';

async function main() {
  // Returns Database interface; backend determined by DB_BACKEND env var
  const db = await initDatabase();
  await db.run('INSERT INTO users (id) VALUES (?)', [userId]);
}

```

### Storing Market Embeddings

```typescript
// Generate embedding using Xenova transformers
const pipeline = await import('@xenova/transformers');
const embedder = await pipeline('feature-extraction', 'Xenova/all-MiniLM-L6-v2');
const embedding = await embedder.embed(market.question);

// Store in SQLite/PostgreSQL for LanceDB indexing
await db.run(
  `INSERT INTO market_index_embeddings 
   (platform, market_id, content_hash, vector, updated_at)
   VALUES (?, ?, ?, ?, ?)`,
  ['polymarket', market.id, contentHash, JSON.stringify(embedding), Date.now()]
);

```

### Performing Semantic Search

```typescript
// Retrieve stored vectors from relational store
const rows = await db.query<{ market_id: string; vector: string }>(
  `SELECT market_id, vector FROM market_index_embeddings WHERE platform = ?`,
  ['polymarket']
);

// Convert for LanceDB query
const vectors = rows.map(r => Float32Array.from(JSON.parse(r.vector)));
const similar = await lanceDb.query(queryEmbedding, { k: 5, vectors });

```

### Switching to PostgreSQL

```bash
export DB_BACKEND=postgres
export DATABASE_URL=postgres://user:password@localhost:5432/cloddsbot
npm run start

```

## Summary

- **CloddsBot employs a polyglot architecture** using SQLite for local development, LanceDB for vector search, and PostgreSQL for production scalability.
- **A unified `Database` interface** in [`src/db/index.ts`](https://github.com/alsk1992/CloddsBot/blob/main/src/db/index.ts) abstracts connection management, allowing `db.run` and `db.query` calls to work identically across all backends.
- **Semantic search** combines SQLite/PostgreSQL storage for JSON vectors with LanceDB's on-disk ANN indexing, using the `@xenova/transformers` MiniLM model.
- **Runtime configuration** via `DB_BACKEND` and `DATABASE_URL` environment variables enables zero-code switching between file-based and server-based storage.
- **Schema consistency** is maintained through a single source of truth in [`src/db/index.ts`](https://github.com/alsk1992/CloddsBot/blob/main/src/db/index.ts) (line 1002), ensuring tables like `market_index_embeddings` and `users` exist regardless of the active engine.

## Frequently Asked Questions

### What database does CloddsBot use by default?

By default, CloddsBot uses **SQLite** via the [`sql.js`](https://github.com/alsk1992/CloddsBot/blob/main/sql.js) WebAssembly implementation. The `initDatabase` function in [`src/db/index.ts`](https://github.com/alsk1992/CloddsBot/blob/main/src/db/index.ts) creates a local file at `${stateDir}/clodds.db` containing all tables including users, positions, market data, and embeddings. This requires no external database server and is optimal for local development and self-hosted deployments.

### How does CloddsBot handle vector search for market indices?

CloddsBot stores vector embeddings in the `market_index_embeddings` table as JSON-encoded TEXT, then leverages **LanceDB** to build an on-disk ANN index for similarity queries. The system uses the `@xenova/transformers` library with the MiniLM model to generate embeddings, and the `upsertMarketIndexEmbedding` function handles synchronization between the relational store and LanceDB's search index.

### Can I use PostgreSQL instead of SQLite with CloddsBot?

Yes. Set the environment variable `DB_BACKEND=postgres` and provide a `DATABASE_URL` connection string. The [`src/db/postgres.ts`](https://github.com/alsk1992/CloddsBot/blob/main/src/db/postgres.ts) module creates a `pg.Pool` connection pool, and [`src/db/migrations.ts`](https://github.com/alsk1992/CloddsBot/blob/main/src/db/migrations.ts) executes the same schema against PostgreSQL. This enables production features like connection pooling, concurrent access, and optional TimescaleDB extensions for time-series trade analysis.

### Where is the database schema defined in the CloddsBot codebase?

The complete schema is defined in [`src/db/index.ts`](https://github.com/alsk1992/CloddsBot/blob/main/src/db/index.ts) starting at line 1002. This single block contains all `CREATE TABLE` statements for entities such as users, alerts, positions, market indices, and exchange-specific trade tables. The same schema is applied to both SQLite and PostgreSQL backends, ensuring structural consistency across deployment modes.