@fairseal/store-sqlite

@fairseal/store-sqlite

Part of FairSeal — formerly OpenRNG.

Persistent SQLite storage for VEO-2 objects.

A drop-in replacement for @fairseal/auto's MemoryStore that survives a restart, plus indexed queries by agent, session, class, provider, model and time range.

npm install @fairseal/store-sqlite

No native dependency required. On Node 22.5+ this uses the built-in node:sqlite, so the package installs with nothing to compile. On older runtimes, install better-sqlite3 alongside it and the store picks it up automatically.

Quick start

import { auto } from '@fairseal/auto';
import { SQLiteStore } from '@fairseal/store-sqlite';

const store = new SQLiteStore('./veos.db');
const client = auto(new OpenAI(), { store });

// …later
store.query({ agent_id: 'support-bot', from: '2026-08-01' });

Standalone:

import { capture, signVEO } from '@fairseal/core';
import { SQLiteStore } from '@fairseal/store-sqlite';

const store = new SQLiteStore({ filename: './veos.db' });

store.save(signVEO(capture({ /* … */ }), privateKey));
store.get(objectId);
store.count({ signed: true });
store.close();

The full VEO is stored verbatim as JSON, so a record read back out still verifies byte-for-byte:

verifySignature(store.get(id)!, { trustedKeys: [publicKey] }); // true

Constructor

new SQLiteStore('./veos.db');   // shorthand for { filename }
new SQLiteStore();              // in-memory

new SQLiteStore({
  filename?: string;      // default ':memory:'
  migrate?: boolean;      // run pending migrations on open — default true
  driver?: 'node:sqlite' | 'better-sqlite3';  // default: auto-detect
  sqliteModule?: unknown; // pre-resolved module, for bundled apps
  defaultLimit?: number;  // default page size — default 1000
  quiet?: boolean;        // silence the truncation warning
  wal?: boolean;          // WAL journaling — default true for file databases
});

store.driver reports which backend was selected.

Queries

store.query({
  agent_id?: string;                  // metadata.agent_id
  session_id?: string;                // metadata.session_id
  object_class?: VEOClass | VEOClass[];
  provider_id?: string;               // provider.provider_id (who issued it)
  ai_provider?: string;               // execution.provider   (which AI vendor)
  model_id?: string;                  // execution.model_id
  from?: Date | string | number;      // issued_at >= (inclusive)
  to?: Date | string | number;        // issued_at <= (inclusive)
  signed?: boolean;
  anchored?: boolean;
  min_confidence?: number;

  limit?: number;
  offset?: number;
  order?: 'asc' | 'desc';             // by issued_at, default 'desc'
});

Filters combine with AND. Every filterable field is backed by an index.

store.query({ agent_id: 'support-bot', object_class: ['VEO-2A', 'VEO-2B'], limit: 50 });
store.count({ from: '2026-08-01', signed: false });
store.distinct('agent_id');   // ['support-bot', 'triage-agent', …]

Note that provider_id and ai_provider are different things: the first is who issued the VEO, the second is whose model ran. Both are indexed.

agent_id and session_id

These are not VEO-2 fields — they are a convention this store indexes. Put them in metadata and they become queryable:

capture({
  provider: 'my-app',
  prompt, output, model,
  metadata: { agent_id: 'support-bot', session_id: conversationId },
});

VEOs without them are still stored; those columns are simply NULL.

Paging is explicit

list() and query() return at most defaultLimit (1000) rows. If a defaulted limit actually cut the result short, the store says so once:

[openrng] SQLiteStore: returned 1000 of 48213 matching VEOs (default limit).
Pass an explicit { limit, offset } to page through results, set defaultLimit
on the store, or pass { quiet: true } to silence this.

Passing an explicit limit means you asked for a page, so no warning is emitted. A persistent store can hold far more than fits in memory, and silently returning a truncated slice is how audit gaps happen.

Other methods

Method	Notes
save(veo)	Insert or replace. Idempotent on object_id.
saveMany(veos)	One transaction; rolls back entirely on a bad record.
get(id)	The VEO, or undefined.
has(id)	Boolean.
list(options?)	query() with paging only.
query(filters?)	See above.
count(filters?)	Matching row count.
distinct(column)	Distinct values of an indexed column.
delete(id)	true if a row was removed.
clear()	Empties the table; keeps schema and migration history.
size	Row count. Mirrors MemoryStore.size.
close()	Idempotent. Later calls throw.

Migrations

The schema is versioned in an openrng_migrations table and brought up to date on open (unless migrate: false). Migrations are append-only, so an existing database upgrades in place rather than needing a rebuild.

import { migrate, MIGRATIONS, LATEST_VERSION } from '@fairseal/store-sqlite';

store.schemaVersion;                  // applied version
SQLiteStore.latestSchemaVersion;      // version this build ships
migrate(db);                          // → { from, to, applied[] }

Running migrations twice applies nothing the second time.

Schema (v1)

veos holds the canonical JSON in body, alongside derived columns used only for filtering: object_id (PK), standard, version, object_class, issued_at, issued_at_ms, provider_id, agent_id, session_id, model_id, ai_provider, confidence_score, confidence_grade, lifecycle_state, content_hash, signed, anchored, stored_at_ms.

Indexes cover issued_at_ms and each of agent_id, session_id, object_class, provider_id, model_id, ai_provider paired with issued_at_ms.

Because the derived columns are copies, they are never used to reconstruct a VEO — get() always parses body, so tampering with a column cannot change what a verifier sees.

Choosing a driver

Runtime	What happens
Node 22.5+	Built-in node:sqlite. No dependency. Emits Node's experimental-feature warning.
Node 18–22.4	Falls back to better-sqlite3 if installed.
Neither available	Constructor throws with instructions.

Force one with { driver: 'better-sqlite3' }, or hand over an already-resolved module when your bundler defeats resolution:

import Database from 'better-sqlite3';
new SQLiteStore({ filename: './veos.db', sqliteModule: Database });

License

MIT