@fractal-synapse/sqlite-database-plugin

A SQLite database plugin for the Fractal Synapse agent ecosystem. Provides a persistent, shared database connection to all agents via the DatabaseInterface, with built-in support for the sqlite-vec extension for vector similarity search and a schema migration system for consumer plugins.

Features

• Persistent SQLite database using better-sqlite3 (synchronous API)
• sqlite-vec extension for KNN vector similarity search (float32, int8, bit vectors)
• Schema migration system — consumers register their own tables before the DB opens
• Single shared connection across all agents (initialized once at startup)
• Injectable DatabaseInterface — other plugins depend on the interface, not this package
• WAL mode enabled by default for better performance

Installation

npm install @fractal-synapse/sqlite-database-plugin

Initialization Order

The plugin uses an explicit initialize() method instead of lazy per-agent initialization. The DB opens once at startup and all agents share the same connection.

import { SqliteDatabasePlugin } from '@fractal-synapse/sqlite-database-plugin';
import { pluginRegistry } from '@fractal-synapse/agent-core';

// 1. Create the plugin
const db = new SqliteDatabasePlugin({
  dbPath: '/path/to/data/app.db',
  logger  // optional
});

// 2. Register schemas from consumer plugins (before the DB opens)
db.registerMigration({
  name: '001_memory_nodes',
  sql: `CREATE TABLE IF NOT EXISTS memory_nodes (
    id   TEXT PRIMARY KEY,
    text TEXT NOT NULL
  )`
});

// 3. Open the DB and run pending migrations
db.initialize();

// 4. Register with the plugin registry
pluginRegistry.register(db);

Schema Migrations

Consumer plugins register their own table schemas via registerMigration(). Each migration has a unique name used as an idempotency key — it only ever runs once per database file, even across restarts.

db.registerMigration({
  name: '001_cache',
  sql: `CREATE TABLE IF NOT EXISTS cache (
    key        TEXT PRIMARY KEY,
    value      TEXT NOT NULL,
    expires_at INTEGER
  )`
});

All registerMigration() calls must happen before db.initialize(). The migration window closes when the DB opens.

Querying

// INSERT / UPDATE / DELETE
db.run('INSERT INTO cache (key, value) VALUES (?, ?)', ['foo', 'bar']);

// SELECT — all rows
const rows = db.all<{ key: string; value: string }>('SELECT * FROM cache');

// SELECT — first row
const row = db.get<{ value: string }>('SELECT value FROM cache WHERE key = ?', ['foo']);

Vector Search

Vector tables are created using sqlite-vec's vec0 virtual table. Declare the dimension in the schema:

db.registerMigration({
  name: '001_embeddings',
  sql: `CREATE VIRTUAL TABLE IF NOT EXISTS embeddings
          USING vec0(embedding float[1536])`
});

Insert vectors using the run() method, binding a Float32Array buffer:

const vector = new Float32Array(myEmbedding); // number[] of length 1536
db.run('INSERT INTO embeddings VALUES (?)', [Buffer.from(vector.buffer)]);

Perform KNN search with vectorSearch():

const results = db.vectorSearch({
  table: 'embeddings',
  vectorColumn: 'embedding',
  queryVector: myQueryEmbedding,  // number[]
  limit: 5                        // default: 10
});

// results: Array<{ row: T, distance: number }>
for (const { row, distance } of results) {
  console.log(distance, row);
}

For cosine distance, declare the column with distance_metric=cosine in your migration:

CREATE VIRTUAL TABLE IF NOT EXISTS embeddings
  USING vec0(embedding float[1536] distance_metric=cosine)

Dependency Injection

The DatabaseInterface is defined in @fractal-synapse/agent-core, so consumer plugins can type-depend on it without depending on this package directly:

import { DatabaseInterface } from '@fractal-synapse/agent-core';

export interface MemoryGraphConfig {
  db: DatabaseInterface;
  logger?: LoggingInterface;
}

export class MemoryGraphPlugin implements AgentPlugin {
  constructor(private config: MemoryGraphConfig) {}
  // ...
}

Then wire it up at the application level:

const db = new SqliteDatabasePlugin({ dbPath, logger });
db.registerMigration({ name: '001_nodes', sql: '...' });
db.initialize();
pluginRegistry.register(db);

const memoryGraph = new MemoryGraphPlugin({ db, logger });
pluginRegistry.register(memoryGraph);

API Reference

new SqliteDatabasePlugin(config)

Option	Type	Required	Description
dbPath	string	Yes	Absolute path to the .db file
logger	LoggingInterface	No	Logger instance (falls back to getLogger())

db.registerMigration(migration)

Enqueues a schema migration. Must be called before initialize().

Field	Type	Description
name	string	Unique migration identifier
sql	string	SQL to execute (CREATE TABLE, etc.)

db.initialize()

Opens the database, loads the sqlite-vec extension, enables WAL mode, and runs all pending migrations. Safe to call multiple times — subsequent calls are no-ops.

db.run(sql, params?)

Executes a statement with no return value (INSERT, UPDATE, DELETE).

db.all<T>(sql, params?)

Executes a SELECT and returns all matching rows as T[].

db.get<T>(sql, params?)

Executes a SELECT and returns the first row as T, or undefined if not found.

db.vectorSearch<T>(options)

Performs a KNN vector similarity search.

Option	Type	Default	Description
table	string	—	Name of the vec0 virtual table
vectorColumn	string	—	Name of the vector column
queryVector	number[]	—	Query vector (must match table dimension)
limit	number	10	Number of nearest neighbours to return
where	string	—	Optional extra WHERE condition
whereParams	unknown[]	[]	Parameters for the extra WHERE condition

Returns Array<{ row: T, distance: number }> ordered by ascending distance.

db.cleanup()

Closes the database connection. Called automatically by the agent lifecycle.