@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