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

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 (the SQLite WebAssembly implementation). In 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, 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 module creates a pg.Pool using the DATABASE_URL environment variable, while 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. 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—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. 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 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

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

// 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

// 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

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 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 (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 WebAssembly implementation. The initDatabase function in 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 module creates a pg.Pool connection pool, and 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 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.

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