latticesql

v1.15.0

Published

a day ago

Persistent structured memory for AI agent systems — pluggable SQLite or Postgres backend, LLM context bridge

0High
0Medium
0Low

bmmayer

ai agents llm context sqlite memory sync

latticesql

Persistent memory for AI agents. Keeps a SQLite or Postgres database and a set of context files in sync — so every agent session starts with accurate state, and agent output becomes permanent data.

latticeSQL.com — docs, examples, and guides

What it does

Every AI agent session starts cold — no memory of what happened yesterday, what state the system is in, or what other agents have done. Lattice solves this with a minimal, generic engine that:

Renders DB rows into agent-readable text files (Markdown, JSON, or any format you define)
Watches for DB changes and re-renders automatically
Ingests agent-written output back into the DB via the writeback pipeline
Manages state with full CRUD, natural-key operations, seeding, and soft-delete
Optimizes context with token budgets, relevance filtering, enrichment pipelines, and reward-scored memory
Searches semantically via bring-your-own embeddings and cosine similarity

Lattice has no opinions about your schema, your agents, or your file format. You define the tables. You control the rendering. Lattice runs the sync loop.

Installation

npm install latticesql

Requires Node.js 18+. The default backend is SQLite (better-sqlite3) — no external database process needed.

To use the Postgres backend (for Supabase, Neon, RDS, or any other Postgres-compatible database), install the optional dependency:

npm install latticesql pg

Then pass a connection string instead of a file path:

import { Lattice } from 'latticesql';

const lattice = new Lattice('postgres://user:pass@host:5432/db');
// rest of your setup is identical to the SQLite path

See Pluggable backends below for full details.

Quick start

import { Lattice } from 'latticesql';

const db = new Lattice('./state.db');

db.define('agents', {
  columns: {
    id: 'TEXT PRIMARY KEY',
    name: 'TEXT NOT NULL',
    persona: 'TEXT',
    active: 'INTEGER DEFAULT 1',
  },
  render(rows) {
    return rows
      .filter((r) => r.active)
      .map((r) => `## ${r.name}\n\n${r.persona ?? ''}`)
      .join('\n\n---\n\n');
  },
  outputFile: 'AGENTS.md',
});

await db.init();

await db.insert('agents', { name: 'Alpha', persona: 'You are Alpha, a research assistant.' });
await db.insert('agents', { name: 'Beta', persona: 'You are Beta, a code reviewer.' });

// Render DB → context files
await db.render('./context');
// Writes: context/AGENTS.md

// Watch for changes, re-render every 5 seconds
const stop = await db.watch('./context', { interval: 5000 });

// Later:
stop();
db.close();

YAML config form (v0.4+) — declare your schema in a file instead:

const db = new Lattice({ config: './lattice.config.yml' });
await db.init();
// Tables and render functions are wired automatically from the config

The sync loop

Your DB (SQLite)
     │  Lattice reads rows → render functions → text
     ▼
Context files (Markdown, JSON, etc.)
     │  LLM agents read these at session start
     ▼
Agent output files
     │  Lattice writeback pipeline parses these
     ▼
Your DB (rows inserted/updated)

Lattice reads your database for rendering, provides a full CRUD API for managing state, and persists agent output back to the DB via the writeback pipeline.

API reference

Constructor

new Lattice(path: string, options?: LatticeOptions)
new Lattice(config: LatticeConfigInput, options?: LatticeOptions)

| Overload | Description | | ------------------------------------------------- | --------------------------------------------- | | new Lattice('./app.db') | Open a SQLite file at the given path | | new Lattice(':memory:') | In-memory database (useful for tests) | | new Lattice({ config: './lattice.config.yml' }) | Read schema + DB path from a YAML config file |

LatticeOptions

interface LatticeOptions {
  wal?: boolean; // WAL journal mode (default: true — recommended for concurrent reads)
  busyTimeout?: number; // SQLite busy_timeout in ms (default: 5000)
  security?: {
    sanitize?: boolean; // Strip control characters from string inputs (default: true)
    auditTables?: string[]; // Tables that emit 'audit' events on write
    fieldLimits?: Record<string, number>; // Max characters per named column
  };
}

const db = new Lattice('./app.db', {
  wal: true,
  busyTimeout: 10_000,
  security: {
    sanitize: true,
    auditTables: ['users', 'credentials'],
    fieldLimits: { notes: 50_000, bio: 2_000 },
  },
});

`define()`

db.define(table: string, definition: TableDefinition): this

TableDefinition

interface TableDefinition {
  /** Column name → SQLite type spec */
  columns: Record<string, string>;

  /**
   * How rows become context text.
   * - A render function: (rows: Row[]) => string
   * - A built-in template name: 'default-list' | 'default-table' | 'default-detail' | 'default-json'
   * - A template spec with hooks: { template: BuiltinTemplateName, hooks?: RenderHooks }
   * Optional (v0.17+) — omit render and outputFile for schema-only tables.
   */
  render?: RenderSpec;

  /** Output file path, relative to the outputDir passed to render()/watch(). Optional (v0.17+). */
  outputFile?: string;

  /** Optional row filter applied before rendering */
  filter?: (rows: Row[]) => Row[];

  /**
   * Primary key column name or [col1, col2] for composite PKs.
   * Defaults to 'id'. When 'id' is the PK and the field is absent on insert,
   * a UUID v4 is generated automatically.
   * Composite PKs (v0.17+): auto-generates a PRIMARY KEY(...) constraint —
   * no need to add it manually via tableConstraints.
   */
  primaryKey?: string | string[];

  /** Additional SQL constraints (e.g., UNIQUE, CHECK). No longer required for composite PKs (v0.17+). */
  tableConstraints?: string[];

  /** Declared relationships used by template rendering */
  relations?: Record<string, Relation>;
}

Basic example:

db.define('tasks', {
  columns: {
    id: 'TEXT PRIMARY KEY',
    title: 'TEXT NOT NULL',
    status: 'TEXT DEFAULT "open"',
    due: 'TEXT',
  },
  render(rows) {
    const open = rows.filter((r) => r.status === 'open');
    return (
      `# Open Tasks (${open.length})\n\n` +
      open.map((r) => `- [ ] ${r.title}${r.due ? ` — due ${r.due}` : ''}`).join('\n')
    );
  },
  outputFile: 'TASKS.md',
});

Custom primary key:

db.define('pages', {
  columns: {
    slug: 'TEXT NOT NULL',
    title: 'TEXT NOT NULL',
    content: 'TEXT',
  },
  primaryKey: 'slug', // <-- tell Lattice which column is the PK
  render: 'default-list',
  outputFile: 'pages.md',
});

// get/update/delete now use the slug value directly
const page = await db.get('pages', 'about-us');
await db.update('pages', 'about-us', { title: 'About' });
await db.delete('pages', 'about-us');

Schema-only table (v0.17+):

Tables without render and outputFile get full schema support (columns, indexes, constraints, CRUD) but produce no output files during render() or watch(). Useful for junction tables, internal tracking tables, or any table that doesn't need a context file.

db.define('agent_skills', {
  columns: {
    agent_id: 'TEXT NOT NULL',
    skill_id: 'TEXT NOT NULL',
    proficiency: 'TEXT DEFAULT "basic"',
  },
  primaryKey: ['agent_id', 'skill_id'],
  // No render, no outputFile — schema-only
});

Composite primary key:

db.define('event_seats', {
  columns: {
    event_id: 'TEXT NOT NULL',
    seat_no: 'INTEGER NOT NULL',
    holder: 'TEXT',
  },
  primaryKey: ['event_id', 'seat_no'],
  render: 'default-table',
  outputFile: 'seats.md',
});

// Pass a Record for get/update/delete
const seat = await db.get('event_seats', { event_id: 'evt-1', seat_no: 12 });
await db.update('event_seats', { event_id: 'evt-1', seat_no: 12 }, { holder: 'Alice' });
await db.delete('event_seats', { event_id: 'evt-1', seat_no: 12 });

Relationship declarations:

db.define('comments', {
  columns: {
    id: 'TEXT PRIMARY KEY',
    post_id: 'TEXT NOT NULL',
    author_id: 'TEXT NOT NULL',
    body: 'TEXT',
  },
  relations: {
    post: { type: 'belongsTo', table: 'posts', foreignKey: 'post_id' },
    author: { type: 'belongsTo', table: 'users', foreignKey: 'author_id' },
    // hasMany: the other table holds the FK
    likes: { type: 'hasMany', table: 'comment_likes', foreignKey: 'comment_id' },
  },
  render: {
    template: 'default-detail',
    hooks: { formatRow: '{{author.name}}: {{body}}' },
  },
  outputFile: 'comments.md',
});

`defineMulti()`

db.defineMulti(name: string, definition: MultiTableDefinition): this

Produces one output file per anchor entity — useful for per-agent or per-project context files.

db.defineMulti('agent-context', {
  // Returns the anchor entities (one file will be created per agent)
  keys: () => db.query('agents', { where: { active: 1 } }),

  // Derive the output file path from the anchor entity
  outputFile: (agent) => `agents/${agent.slug as string}/CONTEXT.md`,

  // Extra tables to query and pass into render
  tables: ['tasks', 'notes'],

  render(agent, { tasks, notes }) {
    const myTasks = tasks.filter((t) => t.assigned_to === agent.id);
    const myNotes = notes.filter((n) => n.agent_id === agent.id);
    return [
      `# ${agent.name} — context`,
      '',
      '## Pending tasks',
      myTasks.map((t) => `- ${t.title}`).join('\n') || '_none_',
      '',
      '## Notes',
      myNotes.map((n) => `- ${n.body}`).join('\n') || '_none_',
    ].join('\n');
  },
});

`defineEntityContext()` (v0.5+)

db.defineEntityContext(table: string, def: EntityContextDefinition): this

Generate a parallel file-system tree for an entity type — one subdirectory per row, one file per declared relationship, and an optional combined context file. Can be called before or after init().

db.defineEntityContext('agents', {
  // Derive the subdirectory name for each entity
  slug: (row) => row.slug as string,

  // Default query options for all relationship sources (v0.6+)
  sourceDefaults: { softDelete: true },

  // Global index file listing all entities
  index: {
    outputFile: 'agents/AGENTS.md',
    render: (rows) => `# Agents\n\n${rows.map((r) => `- ${r.name as string}`).join('\n')}`,
  },

  // Files inside each entity's directory
  files: {
    'AGENT.md': {
      source: { type: 'self' }, // entity's own row
      render: ([r]) => `# ${r.name as string}\n\n${(r.bio as string) ?? ''}`,
    },
    'TASKS.md': {
      source: {
        type: 'hasMany',
        table: 'tasks',
        foreignKey: 'agent_id',
        orderBy: 'created_at',
        orderDir: 'desc',
        limit: 20,
      },
      render: (rows) => rows.map((r) => `- ${r.title as string}`).join('\n'),
      omitIfEmpty: true, // skip if no tasks
      budget: 4000, // truncate at 4 000 chars
    },
    'SKILLS.md': {
      source: {
        type: 'manyToMany',
        junctionTable: 'agent_skills',
        localKey: 'agent_id',
        remoteKey: 'skill_id',
        remoteTable: 'skills',
        orderBy: 'name', // softDelete inherited from sourceDefaults
      },
      render: (rows) => rows.map((r) => `- ${r.name as string}`).join('\n'),
      omitIfEmpty: true,
    },
  },

  // Concatenate all files into one combined context file per entity
  combined: { outputFile: 'CONTEXT.md', exclude: [] },

  // Files agents may write — Lattice never deletes these during cleanup
  protectedFiles: ['SESSION.md'],
});

On each render() / reconcile() call this produces:

context/
├── agents/
│   └── AGENTS.md               ← global index
├── agents/alpha/
│   ├── AGENT.md
│   ├── TASKS.md                ← omitted when empty
│   ├── SKILLS.md               ← omitted when empty
│   └── CONTEXT.md              ← AGENT.md + TASKS.md + SKILLS.md combined
└── agents/beta/
    ├── AGENT.md
    └── CONTEXT.md

Source types:

| Type | What it queries | | ------------------------------------------------------------------------------ | ---------------------------------------------------------------- | | { type: 'self' } | The entity row itself | | { type: 'hasMany', table, foreignKey, ... } | Rows in table where foreignKey = entityPk | | { type: 'manyToMany', junctionTable, localKey, remoteKey, remoteTable, ... } | Remote rows via a junction table | | { type: 'belongsTo', table, foreignKey, ... } | Single parent row via FK on this entity (null FK → empty) | | { type: 'enriched', include: { ... } } | Entity row + related data attached as _key JSON fields (v0.7+) | | { type: 'custom', query: (row, adapter) => Row[] } | Fully custom synchronous query |

Source query options (v0.6+)

hasMany, manyToMany, and belongsTo sources accept optional query refinements:

{
  type: 'hasMany',
  table: 'tasks',
  foreignKey: 'agent_id',
  // Query options (all optional):
  softDelete: true,          // exclude rows where deleted_at IS NULL
  filters: [                 // additional WHERE clauses (uses existing Filter type)
    { col: 'status', op: 'eq', val: 'active' },
  ],
  orderBy: 'created_at',    // ORDER BY column
  orderDir: 'desc',         // 'asc' (default) or 'desc'
  limit: 20,                // LIMIT N
}

The softDelete: true shorthand is equivalent to filters: [{ col: 'deleted_at', op: 'isNull' }].

Junction column projection (v0.8+)

manyToMany sources can include columns from the junction table in results:

{
  type: 'manyToMany',
  junctionTable: 'agent_projects',
  localKey: 'agent_id',
  remoteKey: 'project_id',
  remoteTable: 'projects',
  junctionColumns: [
    'source',                            // included as-is
    { col: 'role', as: 'agent_role' },   // aliased
  ],
}
// Each result row includes both remote table columns AND junction columns

Multi-column ORDER BY (v0.8+)

orderBy accepts an array for multi-column sorting:

{
  type: 'hasMany',
  table: 'events',
  foreignKey: 'project_id',
  orderBy: [
    { col: 'severity' },                  // ASC by default
    { col: 'timestamp', dir: 'desc' },    // DESC
  ],
  limit: 20,
}

The string form (orderBy: 'name') still works for single-column sorting.

sourceDefaults (v0.6+)

Set default query options for all relationship sources in an entity context:

db.defineEntityContext('agents', {
  slug: (row) => row.slug as string,
  sourceDefaults: { softDelete: true }, // applied to all hasMany/manyToMany/belongsTo
  files: {
    'TASKS.md': {
      // softDelete: true is inherited from sourceDefaults
      source: { type: 'hasMany', table: 'tasks', foreignKey: 'agent_id', orderBy: 'created_at' },
      render: (rows) => rows.map((r) => `- ${r.title as string}`).join('\n'),
    },
  },
});

Per-file source options override defaults. custom, self, and enriched sources are unaffected.

Enriched source (v0.7+)

Starts with the entity's own row and attaches related data as JSON string fields. Each key in include becomes a _key field containing JSON.stringify(resolvedRows).

'PROFILE.md': {
  source: {
    type: 'enriched',
    include: {
      // Declarative sub-lookups (support all query options)
      skills:   { type: 'manyToMany', junctionTable: 'agent_skills',
                  localKey: 'agent_id', remoteKey: 'skill_id',
                  remoteTable: 'skills', softDelete: true },
      projects: { type: 'hasMany', table: 'projects', foreignKey: 'org_id',
                  softDelete: true, orderBy: 'name' },
      // Custom sub-lookup for complex queries
      stats:    { type: 'custom', query: (row, adapter) =>
                    adapter.all('SELECT COUNT(*) as cnt FROM events WHERE actor_id = ?', [row.id]) },
    },
  },
  render: ([row]) => {
    const skills = JSON.parse(row._skills as string);
    const projects = JSON.parse(row._projects as string);
    return `# ${row.name}\n\nSkills: ${skills.length}\nProjects: ${projects.length}`;
  },
}

Entity render templates (v0.9+)

EntityFileSpec.render accepts declarative template objects in addition to functions. Three built-in templates:

entity-table — heading + GFM table:

render: {
  template: 'entity-table',
  heading: 'Skills',
  columns: [
    { key: 'name', header: 'Name' },
    { key: 'level', header: 'Level', format: (v) => String(v || '—') },
  ],
  emptyMessage: '*No skills assigned.*',
  beforeRender: (rows) => rows.filter(r => r.active),  // optional
}

entity-profile — heading + field-value pairs + enriched JSON sections:

render: {
  template: 'entity-profile',
  heading: (r) => r.name as string,
  fields: [
    { key: 'status', label: 'Status' },
    { key: 'role', label: 'Role' },
  ],
  sections: [
    { key: 'skills', heading: 'Skills', render: 'table',
      columns: [{ key: 'name', header: 'Name' }] },
    { key: 'projects', heading: 'Projects', render: 'list',
      formatItem: (p) => `${p.name} (${p.status})` },
  ],
  frontmatter: (r) => ({ agent: r.name as string }),
}

entity-sections — per-row sections with metadata + body:

render: {
  template: 'entity-sections',
  heading: 'Rules',
  perRow: {
    heading: (r) => r.title as string,
    metadata: [
      { key: 'scope', label: 'Scope' },
      { key: 'category', label: 'Category' },
    ],
    body: (r) => r.rule_text as string,
  },
  emptyMessage: '*No rules defined.*',
}

All templates auto-prepend a read-only header and YAML frontmatter. Functions still work — the union type is backward compatible.

See docs/entity-context.md for the complete guide.

`defineWriteHook()` (v0.10+)

db.defineWriteHook(hook: WriteHook): this

Register a post-write lifecycle hook that fires after insert(), update(), or delete() operations. Useful for denormalization, fan-out, computed fields, and audit logging.

db.defineWriteHook({
  table: 'agents',
  on: ['insert', 'update'],
  watchColumns: ['team_id', 'division'], // only fire when these change
  handler: (ctx) => {
    // ctx.table, ctx.op, ctx.row, ctx.pk, ctx.changedColumns
    console.log(`${ctx.op} on ${ctx.table}: ${ctx.pk}`);
    denormalizeRelatedData(ctx.pk, ctx.row);
  },
});

Options:

| Field | Type | Description | | -------------- | ----------------------------------------- | ---------------------------------------------- | | table | string | Table to watch | | on | Array<'insert' \| 'update' \| 'delete'> | Operations that trigger the hook | | watchColumns | string[] (optional) | Only fire on update when these columns changed | | handler | (ctx: WriteHookContext) => void | Synchronous handler |

Hook errors are caught and routed to error handlers — they never crash the caller. Multiple hooks per table are supported.

`defineWriteback()`

db.defineWriteback(definition: WritebackDefinition): this

Register an agent-output file for parsing and DB ingestion. Lattice tracks file offsets and handles rotation (truncation) automatically.

db.defineWriteback({
  // Path or glob to agent-written output files
  file: './context/agents/*/SESSION.md',

  parse(content, fromOffset) {
    // Parse new content since last read
    const newContent = content.slice(fromOffset);
    const entries = parseMarkdownItems(newContent);
    return { entries, nextOffset: content.length };
  },

  async persist(entry, filePath) {
    await db.insert('events', {
      source_file: filePath,
      ...(entry as Row),
    });
  },

  // Optional: skip entries with the same dedupeKey seen before
  dedupeKey: (entry) => (entry as { id: string }).id,
});

Generic CRUD (v0.11+)

Methods that work on any table — including tables created via raw DDL (not define()). Uses PRAGMA introspection to discover columns at runtime.

// Upsert by natural key (not just UUID). Auto-handles org_id, updated_at, deleted_at.
const id = await db.upsertByNaturalKey(
  'agents',
  'name',
  'Alice',
  {
    role: 'engineer',
    status: 'active',
  },
  { sourceFile: 'agents.md', orgId: 'org-1' },
);

// Sparse update — only writes non-null fields.
await db.enrichByNaturalKey('agents', 'name', 'Alice', { title: 'Senior Engineer' });

// Soft-delete records NOT in a set (reconciliation).
const deleted = await db.softDeleteMissing('agents', 'name', 'agents.md', ['Alice', 'Bob']);

// Query helpers
const agents = await db.getActive('agents', 'name');
const count = await db.countActive('agents');
const alice = await db.getByNaturalKey('agents', 'name', 'Alice');

Junction table helpers (v0.11+)

// Link (INSERT OR IGNORE — idempotent)
await db.link('agent_skills', { agent_id: 'a1', skill_id: 's1', proficiency: 'expert' });

// Link with upsert (INSERT OR REPLACE — updates existing)
await db.link(
  'agent_projects',
  { agent_id: 'a1', project_id: 'p1', role: 'lead' },
  { upsert: true },
);

// Unlink (DELETE matching rows)
await db.unlink('agent_projects', { agent_id: 'a1', project_id: 'p1' });

`seed()` (v0.13+)

db.seed(config: SeedConfig): Promise<SeedResult>

Bulk seed records from structured data (YAML, JSON, etc.). Upserts by natural key, links to related entities via junction tables, and optionally soft-deletes removed entries.

import { parse } from 'yaml';
import { readFileSync } from 'fs';

const rules = parse(readFileSync('rules.yaml', 'utf8'));

await db.seed({
  data: rules,
  table: 'rules',
  naturalKey: 'title',
  sourceFile: 'rules.yaml',
  orgId: 'org-1',
  linkTo: {
    targetAgents: {
      junction: 'rule_agents',
      foreignKey: 'agent_id',
      resolveBy: 'name',
      resolveTable: 'agents',
    },
  },
  softDeleteMissing: true,
});

A junction link whose target row doesn't resolve is never silently dropped. SeedResult.unresolvedLinks lists every such link (source record, field, target name, junction). Pass onUnresolvedLink: 'throw' to abort with a SeedReconciliationError instead — for pipelines that must never leave a record citing a relationship that has no link in the graph:

const result = await db.seed({ ...config, onUnresolvedLink: 'collect' });
if (result.unresolvedLinks.length) {
  // create the missing targets, then re-seed
  console.warn('unresolved links:', result.unresolvedLinks);
}

`buildReport()` (v0.14+)

db.buildReport(config: ReportConfig): Promise<ReportResult>

Declarative report builder — queries data within a time window, groups into sections, formats for output.

const report = await db.buildReport({
  since: '8h', // or '24h', '7d', or ISO timestamp
  sections: [
    {
      name: 'tasks',
      query: { table: 'tasks', orderBy: 'created_at', orderDir: 'desc' },
      format: 'count_and_list',
    },
    { name: 'events', query: { table: 'activity', groupBy: 'type' }, format: 'counts' },
    {
      name: 'alerts',
      query: { table: 'activity', filters: [{ col: 'severity', op: 'lte', val: 2 }] },
      format: 'list',
    },
  ],
});

for (const section of report.sections) {
  console.log(`${section.name}: ${section.count} items`);
  console.log(section.formatted);
}

Writeback persistence (v0.12+)

WritebackDefinition now accepts an optional stateStore for persistent offset/dedup tracking across restarts:

import { createSQLiteStateStore } from 'latticesql';

db.defineWriteback({
  file: './agents/*/SESSION.md',
  stateStore: createSQLiteStateStore(db.db), // persists offsets in SQLite
  parse: (content, offset) => myParser(content, offset),
  persist: async (entry, filePath) => {
    /* ... */
  },
  dedupeKey: (entry) => entry.id,
  onArchive: (filePath) => archiveFile(filePath), // lifecycle hook
});

Built-in implementations: InMemoryStateStore (default), SQLiteStateStore (persistent).

`init()` / `close()`

await db.init(options?: InitOptions): Promise<void>
db.close(): void

init() opens the SQLite file, runs CREATE TABLE IF NOT EXISTS for all defined tables, and applies any migrations. Must be called once before any CRUD or render operations.

await db.init({
  migrations: [
    { version: 1, sql: 'ALTER TABLE tasks ADD COLUMN due_date TEXT' },
    { version: 2, sql: 'ALTER TABLE tasks ADD COLUMN priority INTEGER DEFAULT 0' },
  ],
});

Migrations are idempotent — each version number is applied exactly once, tracked in a __lattice_migrations internal table.

close() closes the SQLite connection. Call it when the process shuts down.

`migrate()` (v0.17+)

await db.migrate(migrations: Migration[]): Promise<void>

Run migrations after init(). Works exactly like init({ migrations }) but callable any time — useful when migrations are loaded dynamically or added by plugins after startup.

await db.init();

// Later — e.g., after loading a plugin that needs new columns
await db.migrate([
  { version: 'plugin-v1', sql: 'ALTER TABLE tasks ADD COLUMN tags TEXT' },
  { version: 'plugin-v2', sql: 'CREATE INDEX IF NOT EXISTS idx_tasks_tags ON tasks (tags)' },
]);

Migration.version accepts number | string — use numbers for sequential migrations, or strings for named/namespaced versions (e.g., 'plugin-v1'). Each version is applied at most once, tracked in the same __lattice_migrations table used by init().

CRUD operations

All CRUD methods return Promises and are safe to await.

`insert()`

await db.insert(table: string, row: Row): Promise<string>

Insert a row. Returns the primary key value (as a string). For the default id column, a UUID is auto-generated when absent.

const id = await db.insert('tasks', { title: 'Write docs', status: 'open' });
// id → 'f47ac10b-58cc-4372-a567-0e02b2c3d479'

// With a custom PK — caller must supply the value
await db.insert('pages', { slug: 'about', title: 'About Us' });

// With explicit id
await db.insert('tasks', { id: 'task-001', title: 'Specific task' });

`insertReturning()` (v0.17+)

await db.insertReturning(table: string, row: Row): Promise<Row>

Insert a row and get the full row back — including the auto-generated id, defaults, and any other columns. Equivalent to insert() + get() in a single call.

const task = await db.insertReturning('tasks', { title: 'Write docs', status: 'open' });
// task → { id: 'f47ac10b-...', title: 'Write docs', status: 'open', priority: 0, ... }

// Useful when you need the generated id or default values immediately
const agent = await db.insertReturning('agents', { name: 'Gamma' });
console.log(agent.id); // auto-generated UUID
console.log(agent.active); // default value from schema

`upsert()`

await db.upsert(table: string, row: Row): Promise<string>

Insert or update a row by primary key (ON CONFLICT DO UPDATE). All PK columns must be present in row.

await db.upsert('tasks', { id: 'task-001', title: 'Updated title', status: 'done' });

`upsertBy()`

await db.upsertBy(table: string, col: string, val: unknown, row: Row): Promise<string>

Upsert by an arbitrary column — looks up the row by col = val, updates if found, inserts if not. Useful for email-keyed users, slug-keyed posts, etc.

await db.upsertBy('users', 'email', '[email protected]', { name: 'Alice' });

`update()`

await db.update(table: string, id: PkLookup, row: Partial<Row>): Promise<void>

Update specific columns on an existing row.

await db.update('tasks', 'task-001', { status: 'done' });

// Composite PK
await db.update('event_seats', { event_id: 'e-1', seat_no: 3 }, { holder: 'Bob' });

`updateReturning()` (v0.17+)

await db.updateReturning(table: string, id: PkLookup, row: Partial<Row>): Promise<Row>

Update specific columns and get the full updated row back. Equivalent to update() + get() in a single call.

const task = await db.updateReturning('tasks', 'task-001', { status: 'done' });
// task → { id: 'task-001', title: 'Write docs', status: 'done', priority: 3, ... }

// Composite PK
const seat = await db.updateReturning(
  'event_seats',
  { event_id: 'e-1', seat_no: 3 },
  { holder: 'Bob' },
);
// seat → { event_id: 'e-1', seat_no: 3, holder: 'Bob' }

`delete()`

await db.delete(table: string, id: PkLookup): Promise<void>

await db.delete('tasks', 'task-001');
await db.delete('event_seats', { event_id: 'e-1', seat_no: 3 });

`get()`

await db.get(table: string, id: PkLookup): Promise<Row | null>

Fetch a single row by PK. Returns null if not found.

const task = await db.get('tasks', 'task-001');
// { id: 'task-001', title: 'Write docs', status: 'open' } | null

`query()`

await db.query(table: string, opts?: QueryOptions): Promise<Row[]>

interface QueryOptions {
  where?: Record<string, unknown>; // Equality shorthand
  filters?: Filter[]; // Advanced operators (see below)
  orderBy?: string;
  orderDir?: 'asc' | 'desc';
  limit?: number;
  offset?: number;
}

// Simple equality filter
const open = await db.query('tasks', { where: { status: 'open' } });

// Sorted + paginated
const page1 = await db.query('tasks', {
  where: { status: 'open' },
  orderBy: 'created_at',
  orderDir: 'desc',
  limit: 20,
  offset: 0,
});

// All rows
const all = await db.query('tasks');

`count()`

await db.count(table: string, opts?: CountOptions): Promise<number>

const n = await db.count('tasks', { where: { status: 'open' } });

Query operators

The filters array supports operators beyond equality. where and filters are combined with AND.

interface Filter {
  col: string;
  op: 'eq' | 'ne' | 'gt' | 'gte' | 'lt' | 'lte' | 'like' | 'in' | 'isNull' | 'isNotNull';
  val?: unknown; // not needed for isNull / isNotNull
}

Examples:

// Comparison
const highPriority = await db.query('tasks', {
  filters: [{ col: 'priority', op: 'gte', val: 4 }],
});

// Pattern match
const search = await db.query('tasks', {
  filters: [{ col: 'title', op: 'like', val: '%refactor%' }],
});

// IN list
const active = await db.query('tasks', {
  filters: [{ col: 'status', op: 'in', val: ['open', 'in-progress'] }],
});

// NULL checks
const unassigned = await db.query('tasks', {
  filters: [{ col: 'assignee_id', op: 'isNull' }],
});

// Combine where + filters (ANDed)
const results = await db.query('tasks', {
  where: { project_id: 'proj-1' },
  filters: [
    { col: 'priority', op: 'gte', val: 3 },
    { col: 'deleted_at', op: 'isNull' },
  ],
  orderBy: 'priority',
  orderDir: 'desc',
});

// count() supports filters too
const n = await db.count('tasks', {
  filters: [{ col: 'status', op: 'ne', val: 'done' }],
});

Render, sync, watch, and reconcile

`render()`

await db.render(outputDir: string): Promise<RenderResult>

Render all tables to text files in outputDir. Files are written atomically (write to temp, rename). Files whose content hasn't changed are skipped.

const result = await db.render('./context');
// { filesWritten: ['context/TASKS.md'], filesSkipped: 2, durationMs: 12 }

`sync()`

await db.sync(outputDir: string): Promise<SyncResult>

render() + writeback pipeline in one call.

const result = await db.sync('./context');
// { filesWritten: [...], filesSkipped: 0, durationMs: 18, writebackProcessed: 3 }

`watch()`

await db.watch(outputDir: string, opts?: WatchOptions): Promise<StopFn>

Poll the DB every interval ms and re-render when content changes.

const stop = await db.watch('./context', {
  interval: 5_000, // default: 5000 ms
  onRender: (r) => console.log('rendered', r.filesWritten.length, 'files'),
  onError: (e) => console.error('render error:', e.message),
});

// Stop the loop later
stop();

With automatic orphan cleanup (v0.5+):

const stop = await db.watch('./context', {
  interval: 10_000,
  cleanup: {
    removeOrphanedDirectories: true, // delete dirs for deleted entities
    removeOrphanedFiles: true, // delete stale relationship files
    protectedFiles: ['SESSION.md'], // never delete these
    dryRun: false,
  },
  onCleanup: (r) => {
    if (r.directoriesRemoved.length > 0) {
      console.log('removed orphaned dirs:', r.directoriesRemoved);
    }
  },
});

`reconcile()` (v0.5+)

await db.reconcile(outputDir: string, options?: ReconcileOptions): Promise<ReconcileResult>

One-shot reverse-sync + render + orphan cleanup. Reads the previous manifest, detects external file edits (reverse-sync), renders all tables and entity contexts (writing a new manifest), then removes orphaned directories and files.

Reverse-sync (v0.16+): If any EntityFileSpec defines a reverseSync function, Lattice detects files modified since the last render (via SHA-256 hashes in the manifest) and sweeps those changes back into the database before re-rendering. See docs/entity-context.md.

const result = await db.reconcile('./context', {
  removeOrphanedDirectories: true,
  removeOrphanedFiles: true,
  protectedFiles: ['SESSION.md'],
  reverseSync: true, // default; set false to skip, 'dry-run' to preview
  dryRun: false, // set true to preview without deleting
  onOrphan: (path, kind) => console.log(`would remove ${kind}: ${path}`),
});

console.log(result.filesWritten); // files written this cycle
console.log(result.cleanup.directoriesRemoved); // orphaned dirs removed
console.log(result.cleanup.warnings); // dirs left in place (user files)
console.log(result.reverseSync); // { filesScanned, filesChanged, updatesApplied, errors }

ReconcileResult extends RenderResult with cleanup and reverseSync fields:

interface ReconcileResult {
  filesWritten: string[];
  filesSkipped: number;
  durationMs: number;
  cleanup: {
    directoriesRemoved: string[];
    filesRemoved: string[];
    directoriesSkipped: string[];
    warnings: string[];
  };
  reverseSync: {
    filesScanned: number;
    filesChanged: number;
    updatesApplied: number;
    errors: Array<{ file: string; error: string }>;
  } | null;
}

Events

db.on('audit',     ({ table, operation, id, timestamp }) => void)
db.on('render',    ({ filesWritten, filesSkipped, durationMs }) => void)
db.on('writeback', ({ filePath, entriesProcessed }) => void)
db.on('error',     (err: Error) => void)

audit events fire on every insert/update/delete for tables listed in security.auditTables. Use them to build an audit log.

db.on('audit', ({ table, operation, id, timestamp }) => {
  console.log(`[AUDIT] ${operation} on ${table}#${id} at ${timestamp}`);
});

Raw DB access

db.db: Database.Database  // better-sqlite3 instance

Escape hatch for queries Lattice doesn't cover (JOINs, aggregates, etc.):

const rows = db.db
  .prepare(
    `
  SELECT t.*, u.name AS assignee_name
  FROM tasks t
  LEFT JOIN users u ON u.id = t.assignee_id
  WHERE t.status = ?
`,
  )
  .all('open');

Context optimization (v1.3+)

Lattice provides several options on TableDefinition to optimize what gets rendered into context files.

Token budget

Limit the token count of rendered output. When content exceeds the budget, rows are pruned by priority:

db.define('tickets', {
  columns: { id: 'TEXT PRIMARY KEY', title: 'TEXT', updated_at: 'TEXT' },
  render: (rows) => rows.map((r) => `- ${r.title}`).join('\n'),
  outputFile: 'TICKETS.md',
  tokenBudget: 4000, // max estimated tokens (~4 chars/token)
  prioritizeBy: 'updated_at', // keep most recent rows when pruning
});

A truncation footer is appended: [truncated: 47 of 123 rows rendered, ~3800 tokens]

Relevance filtering

Dynamically filter rows based on a task context string:

db.define('knowledge', {
  columns: { id: 'TEXT PRIMARY KEY', topic: 'TEXT', body: 'TEXT' },
  render: (rows) => rows.map((r) => `## ${r.topic}\n${r.body}`).join('\n\n'),
  outputFile: 'KNOWLEDGE.md',
  relevanceFilter: (row, ctx) =>
    ctx ? String(row.body).toLowerCase().includes(ctx.toLowerCase()) : true,
});

// Set the current task context — only matching rows are rendered
db.setTaskContext('deployment');
await db.render('./context');

Enrichment pipeline

Transform rows between filtering and rendering — add computed fields, cluster, summarize:

db.define('incidents', {
  columns: { id: 'TEXT PRIMARY KEY', severity: 'TEXT', title: 'TEXT', created_at: 'TEXT' },
  render: (rows) => JSON.stringify(rows, null, 2),
  outputFile: 'incidents.json',
  enrich: [
    (rows) =>
      rows.map((r) => ({
        ...r,
        _age_hours: Math.round((Date.now() - new Date(r.created_at as string).getTime()) / 3600000),
      })),
    (rows) => (rows.length > 100 ? [{ _summary: `${rows.length} incidents` }] : rows),
  ],
});

Reward-scored memory

Track which data is useful. High-reward rows are prioritized in rendering; low-scoring rows can be auto-pruned:

db.define('tips', {
  columns: { id: 'TEXT PRIMARY KEY', tip: 'TEXT', deleted_at: 'TEXT' },
  render: (rows) => rows.map((r) => `- ${r.tip}`).join('\n'),
  outputFile: 'TIPS.md',
  rewardTracking: true, // auto-adds _reward_total, _reward_count columns
  pruneBelow: 0.3, // soft-delete rows with reward < 0.3 (requires deleted_at column)
});

await db.init();
const id = await db.insert('tips', { tip: 'Use batch inserts for bulk data' });

// After the agent confirms this tip was useful:
await db.reward('tips', id, { relevance: 0.9, accuracy: 1.0 });

Semantic search (v1.3+)

Enable embedding-based search on any table. Bring your own embedding function:

db.define('docs', {
  columns: { id: 'TEXT PRIMARY KEY', title: 'TEXT', body: 'TEXT' },
  render: (rows) => rows.map((r) => `## ${r.title}\n${r.body}`).join('\n\n---\n\n'),
  outputFile: 'DOCS.md',
  embeddings: {
    fields: ['title', 'body'],
    embed: async (text) => {
      const res = await openai.embeddings.create({ input: text, model: 'text-embedding-3-small' });
      return res.data[0].embedding;
    },
  },
});

await db.init();
await db.insert('docs', { title: 'Deploy guide', body: 'How to deploy to production...' });

// Search by meaning, not keywords
const results = await db.search('docs', 'ship to prod', { topK: 5, minScore: 0.7 });
for (const { row, score } of results) {
  console.log(`${score.toFixed(2)} — ${row.title}`);
}

Embeddings are stored in a companion SQLite table and cosine similarity is computed in JS — no external vector database required.

Writeback validation (v1.3+)

Validate agent-written data before persisting. Reject low-quality or hallucinated writes:

db.defineWriteback({
  file: './agent-output/*.md',
  parse: (content, offset) => ({ entries: [content.slice(offset)], nextOffset: content.length }),
  persist: async (entry) => {
    /* save to DB */
  },
  validate: async (entry) => {
    const text = entry as string;
    const hasRequiredFields = text.includes('## Title') && text.includes('## Body');
    return {
      pass: hasRequiredFields,
      score: hasRequiredFields ? 0.9 : 0.1,
      reason: hasRequiredFields ? undefined : 'Missing required sections',
    };
  },
  rejectBelow: 0.5,
  onReject: (entry, result) => {
    console.warn(`Rejected write: ${result.reason} (score: ${result.score})`);
  },
});

Template rendering

Built-in templates

Pass a BuiltinTemplateName string as render to use a built-in template without writing a render function:

db.define('users', {
  columns: { id: 'TEXT PRIMARY KEY', name: 'TEXT', email: 'TEXT', role: 'TEXT' },
  render: 'default-table', // or 'default-list' | 'default-detail' | 'default-json'
  outputFile: 'USERS.md',
});

| Template | Output | | ---------------- | --------------------------------------------------- | | default-list | One bullet per row: - key: value, key: value, ... | | default-table | GitHub-flavoured Markdown table with a header row | | default-detail | ## <pk> section per row with key: value body | | default-json | JSON.stringify(rows, null, 2) |

All templates return empty string for zero rows.

Lifecycle hooks

Add a hooks object to customise any built-in template:

db.define('tasks', {
  columns: { id: 'TEXT PRIMARY KEY', title: 'TEXT', status: 'TEXT', priority: 'INTEGER' },
  render: {
    template: 'default-list',
    hooks: {
      // Transform or filter rows before rendering
      beforeRender: (rows) =>
        rows
          .filter((r) => r.status !== 'done')
          .sort((a, b) => (b.priority as number) - (a.priority as number)),

      // Customise how each row becomes a line
      formatRow: '{{title}} [priority {{priority}}]',
    },
  },
  outputFile: 'TASKS.md',
});

| Hook | Applies to | Type | | -------------------- | -------------------------------- | ---------------------------------- | | beforeRender(rows) | All templates | (rows: Row[]) => Row[] | | formatRow | default-list, default-detail | ((row: Row) => string) \| string |

formatRow can be a function or a {{field}} template string. When it's a string, belongsTo relation fields are resolved and available as {{relationName.field}}.

Field interpolation

Any formatRow string supports {{field}} tokens with dot-notation for related rows:

db.define('users', {
  columns: { id: 'TEXT PRIMARY KEY', name: 'TEXT', team: 'TEXT' },
  render: 'default-list',
  outputFile: 'USERS.md',
});

db.define('tickets', {
  columns: {
    id: 'TEXT PRIMARY KEY',
    title: 'TEXT',
    assignee_id: 'TEXT',
    status: 'TEXT',
  },
  relations: {
    assignee: { type: 'belongsTo', table: 'users', foreignKey: 'assignee_id' },
  },
  render: {
    template: 'default-list',
    hooks: {
      formatRow: '{{title}} → {{assignee.name}} ({{status}})',
    },
  },
  outputFile: 'TICKETS.md',
});
// Output line: "- Fix login → Alice (open)"

Rules:

{{field}} — value of field in the current row
{{relation.field}} — value of field in the related row (resolved via belongsTo)
Unknown paths, null, and undefined all render as empty string
Non-string values are coerced with String()
Leading/trailing whitespace in token names is trimmed: {{ name }} works

Markdown utilities (v0.6+)

Composable helper functions for building render functions. Use inside render: (rows) => ... callbacks to reduce boilerplate.

`frontmatter(fields)`

Generate a YAML-style frontmatter block. Automatically includes generated_at with the current ISO timestamp.

import { frontmatter } from 'latticesql';

const header = frontmatter({ agent: 'Alice', skill_count: 5 });
// ---
// generated_at: "2026-03-27T..."
// agent: "Alice"
// skill_count: 5
// ---

`markdownTable(rows, columns)`

Generate a GitHub-Flavoured Markdown table from rows with explicit column configuration and optional per-cell formatters.

import { markdownTable } from 'latticesql';

const md = markdownTable(rows, [
  { key: 'name', header: 'Name' },
  { key: 'status', header: 'Status', format: (v) => String(v || '—') },
  { key: 'name', header: 'Detail', format: (v, row) => `[view](${row.slug}/DETAIL.md)` },
]);
// | Name | Status | Detail |
// | --- | --- | --- |
// | Alice | active | [view](alice/DETAIL.md) |

Returns empty string for zero rows. The format callback receives (cellValue, fullRow).

`slugify(name)`

Generate a URL-safe slug from a display name — lowercases, strips diacritics, replaces non-alphanumeric runs with hyphens.

import { slugify } from 'latticesql';

slugify('My Agent Name'); // 'my-agent-name'
slugify('Jose Garcia'); // 'jose-garcia'

`truncate(content, maxChars, notice?)`

Truncate content at a character budget. Appends a notice when truncation occurs.

import { truncate } from 'latticesql';

const md = truncate(longContent, 4000);
// Appends: "\n\n*[truncated — context budget exceeded]*"

const md2 = truncate(longContent, 4000, '\n\n[...truncated]');
// Custom notice

Entity context directories (v0.5+)

defineEntityContext() is the high-level API for per-entity file generation — the pattern where each entity type gets its own directory tree, with a separate file for each relationship type.

Why use it instead of `defineMulti()`?

defineMulti() produces one file per anchor entity but you manage queries yourself. defineEntityContext() declares the structure — which tables to pull, how to render them, what budget to enforce — and Lattice handles all the querying, directory creation, hash-skip deduplication, and orphan cleanup.

Minimal example

db.defineEntityContext('projects', {
  slug: (r) => r.slug as string,
  files: {
    'PROJECT.md': {
      source: { type: 'self' },
      render: ([r]) => `# ${r.name as string}\n\n${(r.description as string) ?? ''}`,
    },
  },
});

After db.render('./ctx') this creates:

ctx/
└── projects/
    ├── my-project/
    │   └── PROJECT.md
    └── another-project/
        └── PROJECT.md

Lifecycle — orphan cleanup

When you delete an entity from the database the old directory becomes an orphan. Use reconcile() to clean it up:

await db.delete('projects', 'old-id');

const result = await db.reconcile('./ctx', {
  removeOrphanedDirectories: true,
  protectedFiles: ['NOTES.md'], // agents wrote these — keep them
});
// result.cleanup.directoriesRemoved → ['/.../ctx/projects/old-project']

Lattice writes a .lattice/manifest.json inside outputDir after every render cycle — this is what reconcile() uses to know which directories it owns and what it previously wrote in each.

Protected files

Declare files that agents write inside entity directories. Lattice will never delete them during cleanup:

db.defineEntityContext('agents', {
  slug: (r) => r.slug as string,
  protectedFiles: ['SESSION.md', 'NOTES.md'],
  files: {
    /* ... */
  },
});

If an entity is deleted and its directory still contains SESSION.md, Lattice removes only its own managed files, leaves the directory in place, and adds a warning to CleanupResult.warnings.

Reading the manifest

import { readManifest } from 'latticesql';

const manifest = readManifest('./ctx');
// manifest?.entityContexts.agents.entities['alpha']
// → ['AGENT.md', 'TASKS.md', 'CONTEXT.md']  (files written last cycle for agent 'alpha')

Protected entity contexts (v0.18+)

Mark an entity context as protected: true to prevent its data from leaking into other entities' context files:

db.defineEntityContext('agents', {
  slug: (r) => r.slug,
  protected: true,  // Other entity contexts cannot pull agent data
  files: { ... },
});

Protected entities render their own files normally, but sources from other entities referencing a protected table return empty results.

At-rest encryption (v0.18+)

Enable transparent AES-256-GCM encryption on entity context columns:

const db = new Lattice('./secrets.db', { encryptionKey: 'master-key' });

db.defineEntityContext('secrets', {
  slug: (r) => r.name,
  protected: true,
  encrypted: { columns: ['value'] },  // or true for all text columns
  files: { ... },
});

See docs/entity-context.md for the complete reference.

SESSION.md write pattern

When agents run in a directory-based context system (e.g., one directory per agent with generated Markdown files), SESSION.md provides a safe write interface that enforces a clean read/write separation:

READ:  Lattice DB → render() → object MDs (READ ONLY for agents)
WRITE: Agent → SESSION.md → processor → validates → Lattice DB

All generated context files carry a read-only header so agents know not to edit them directly. SESSION.md is the only writable file in the directory.

Write entry format

---
id: 2026-03-25T10:30:00Z-agent-abc123
type: write
timestamp: 2026-03-25T10:30:00Z
op: update
table: agents
target: agent-id-here
reason: Updating status after deployment completed.
---
status: active
last_task: api-deploy
===

| Header | Required | Description | | ----------- | ----------------- | ----------------------------------- | | type | Yes | Must be write | | timestamp | Yes | ISO 8601 | | op | Yes | create, update, or delete | | table | Yes | Target table name | | target | For update/delete | Record primary key | | reason | Encouraged | Human-readable reason (audit trail) |

Body: key: value pairs — one field per line. Field names are validated against the table schema before any write is applied.

Library support

latticesql exports a parser for the SESSION.md write format:

import { parseSessionWrites } from 'latticesql';

const result = parseSessionWrites(sessionFileContent);
// result.entries: SessionWriteEntry[]
// result.errors:  Array<{ line: number; message: string }>

for (const entry of result.entries) {
  console.log(entry.op, entry.table, entry.target, entry.fields);
}

SessionWriteEntry:

interface SessionWriteEntry {
  id: string; // content-addressed ID
  timestamp: string; // ISO 8601
  op: 'create' | 'update' | 'delete';
  table: string;
  target?: string; // required for update/delete
  reason?: string;
  fields: Record<string, string>; // empty for delete
}

The processor is responsible for applying the parsed entries to your DB and validating field names against your schema. The parseSessionWrites function is pure — no DB access, no side effects.

Full session parser (v0.5.2+)

For parsing all entry types (not just writes), use parseSessionMD:

import { parseSessionMD, parseMarkdownEntries } from 'latticesql';

// Parse YAML-delimited entries (--- header --- body ===)
const result = parseSessionMD(content, startOffset);
// result.entries: SessionEntry[]  — all types: event, learning, status, write, etc.
// result.errors:  ParseError[]
// result.lastOffset: number       — for incremental parsing

// Parse markdown heading entries (## timestamp — description)
const mdResult = parseMarkdownEntries(content, 'agent-name', startOffset);

Configurable entry types (v0.5.5+)

By default, the parser validates against a built-in set of entry types. Override via SessionParseOptions:

import { parseSessionMD, DEFAULT_ENTRY_TYPES, DEFAULT_TYPE_ALIASES } from 'latticesql';

// Accept any type (no validation)
parseSessionMD(content, 0, { validTypes: null });

// Custom type set
parseSessionMD(content, 0, {
  validTypes: new Set(['alert', 'todo', 'write']),
  typeAliases: { warning: 'alert', task: 'todo' },
});

Read-only header (v0.5.5+)

All generated context files should carry a read-only header. Use the default or create a custom one:

import { READ_ONLY_HEADER, createReadOnlyHeader } from 'latticesql';

// Default: "generated by Lattice"
const header = READ_ONLY_HEADER;

// Custom generator name and docs reference
const custom = createReadOnlyHeader({
  generator: 'my-sync-tool',
  docsRef: 'https://example.com/docs/sessions',
});

Write applicator (v0.5.2+)

Apply parsed write entries to a better-sqlite3 database with schema validation:

import { applyWriteEntry } from 'latticesql';

const result = applyWriteEntry(db, writeEntry);
if (result.ok) {
  console.log(`Applied to ${result.table}, record ${result.recordId}`);
} else {
  console.error(result.reason);
}

Validates table existence, field names against schema, and uses soft-delete when a deleted_at column exists.

YAML config (v0.4+)

Define your entire schema in a YAML file. Lattice reads it at construction time, creates all tables on init(), and wires render functions automatically.

`lattice.config.yml` reference

# Path to the SQLite database file (relative to this config file)
db: ./data/app.db

entities:
  # ── Entity name = table name ──────────────────────────────────────────────
  user:
    fields:
      id: { type: uuid, primaryKey: true } # auto-UUID on insert
      name: { type: text, required: true } # NOT NULL
      email: { type: text } # nullable
      score: { type: integer, default: 0 } # DEFAULT 0
    render: default-table
    outputFile: context/USERS.md

  ticket:
    fields:
      id: { type: uuid, primaryKey: true }
      title: { type: text, required: true }
      status: { type: text, default: open }
      priority: { type: integer, default: 1 }
      assignee_id: { type: uuid, ref: user } # creates belongsTo relation
    render:
      template: default-list
      formatRow: '{{title}} ({{status}}) — {{assignee.name}}'
    outputFile: context/TICKETS.md

Field types

| YAML type | SQLite type | TypeScript type | | ---------- | ----------- | --------------- | | uuid | TEXT | string | | text | TEXT | string | | integer | INTEGER | number | | int | INTEGER | number | | real | REAL | number | | float | REAL | number | | boolean | INTEGER | boolean | | bool | INTEGER | boolean | | datetime | TEXT | string | | date | TEXT | string | | blob | BLOB | Buffer |

Field options

| Option | Type | Description | | ------------ | ------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------- | | type | LatticeFieldType | Column data type (required) | | primaryKey | boolean | Primary key column (TEXT PRIMARY KEY for uuid/text) | | required | boolean | NOT NULL constraint | | default | string/number/bool | SQL DEFAULT value | | ref | string | Foreign-key reference to another entity. Creates a belongsTo relation; _id suffix is stripped from the relation name (assignee_id → assignee) |

Entity-level options

| Option | Type | Description | | ------------ | -------------------------- | ------------------------------------------------------------------ | | fields | Record<string, FieldDef> | Column definitions (required) | | render | string or object | Built-in template name, or { template, formatRow } | | outputFile | string | Render output path (relative to config file) | | primaryKey | string or string[] | Override PK — takes precedence over field-level primaryKey: true |

Render spec forms in YAML:

# String form — plain BuiltinTemplateName
render: default-table

# Object form — template + formatRow hook
render:
  template: default-list
  formatRow: "{{title}} ({{status}})"

Init from config

import { Lattice } from 'latticesql';

const db = new Lattice({ config: './lattice.config.yml' });
await db.init();

// All entities are available immediately
await db.insert('user', { name: 'Alice', email: '[email protected]' });
await db.insert('ticket', { title: 'Fix login', assignee_id: 'u-1' });

const tickets = await db.query('ticket', { where: { status: 'open' } });
await db.render('./context');

The { config } constructor reads the YAML file synchronously, extracts the db path, and calls define() for each entity. It is exactly equivalent to:

// Equivalent manual setup (no YAML)
const db = new Lattice('./data/app.db');
db.define('user', { columns: { ... }, render: 'default-table', outputFile: '...' });
db.define('ticket', { columns: { ... }, render: { ... }, outputFile: '...' });
await db.init();

Config API (programmatic)

Parse a config file or string without constructing a Lattice instance:

import { parseConfigFile, parseConfigString } from 'latticesql';

// From a file (throws on missing/invalid file or YAML parse error)
const { dbPath, tables } = parseConfigFile('./lattice.config.yml');

// From a YAML string — configDir is used to resolve relative outputFile paths
const { tables } = parseConfigString(yamlContent, '/project/root');

// Wire into any Lattice instance manually
const db = new Lattice(':memory:');
for (const { name, definition } of tables) {
  db.define(name, definition);
}
await db.init();

ParsedConfig:

interface ParsedConfig {
  dbPath: string; // Absolute path to the SQLite file
  tables: ReadonlyArray<{ name: string; definition: TableDefinition }>;
}

CLI — `lattice generate`

Generate TypeScript interfaces, an initial SQL migration file, and optional scaffold files from a YAML config.

npx lattice generate

# With options
npx lattice generate --config ./lattice.config.yml --out ./generated --scaffold

Options

| Flag | Default | Description | | --------------------- | ---------------------- | ---------------------------------------------------------------------------- | | --config, -c <path> | ./lattice.config.yml | Path to the config file | | --out, -o <dir> | ./generated | Output directory | | --scaffold | off | Create empty files at each entity's outputFile path (skips existing files) | | --help, -h | — | Show help | | --version, -v | — | Print version |

Output structure

generated/
├── types.ts               # TypeScript interface per entity
└── migrations/
    └── 0001_initial.sql   # CREATE TABLE IF NOT EXISTS statements

Example output — generated/types.ts:

// Auto-generated by `lattice generate`. Do not edit manually.

export interface User {
  id: string;
  name: string; // required: true → no ?
  email?: string;
  score?: number;
}

export interface Ticket {
  id: string;
  title: string;
  status?: string;
  priority?: number;
  assignee_id?: string; // → user
}

Example output — generated/migrations/0001_initial.sql:

-- Auto-generated by `lattice generate`. Do not edit manually.

CREATE TABLE IF NOT EXISTS "user" (
  "id" TEXT PRIMARY KEY,
  "name" TEXT NOT NULL,
  "email" TEXT,
  "score" INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS "ticket" (
  "id" TEXT PRIMARY KEY,
  "title" TEXT NOT NULL,
  "status" TEXT DEFAULT 'open',
  "priority" INTEGER DEFAULT 1,
  "assignee_id" TEXT
);

CLI — `lattice gui` (v1.11+)

Start a local-only browser GUI for exploring and editing the data in a Lattice database. The server binds to 127.0.0.1 and delegates straight to the same Lattice CRUD methods you call from code — no separate state, no schema duplication.

npx lattice gui

# With options
npx lattice gui --config ./lattice.config.yml --output ./context --port 4317

Options

| Flag | Default | Description | | --------------------- | ---------------------- | -------------------------------------------------------- | | --config, -c <path> | ./lattice.config.yml | Path to the config file | | --output <dir> | (auto-detected) | Output directory containing rendered context — see below | | --port <number> | 4317 | Localhost port; auto-increments when the port is busy | | --no-open | off | Print the URL without opening a browser |

Output-directory auto-detection (v1.13.1+). When --output is not passed explicitly, the GUI probes ./context, ., and ./generated in order and uses the first directory containing a .lattice/manifest.json (announced via a one-line auto-detected rendered context at "<dir>" log on stdout). Projects whose lattice render writes into the project root no longer need to pass --output . every time. An explicit --output is always honoured.

Published

Vulnerabilities

Links

Maintainers

Keywords

Readme

latticesql

What it does

Table of contents

Installation

Quick start

The sync loop

API reference

Constructor

define()

defineMulti()

defineEntityContext() (v0.5+)

Source query options (v0.6+)

Junction column projection (v0.8+)

Multi-column ORDER BY (v0.8+)

sourceDefaults (v0.6+)

Enriched source (v0.7+)

Entity render templates (v0.9+)

defineWriteHook() (v0.10+)

defineWriteback()

Generic CRUD (v0.11+)

Junction table helpers (v0.11+)

seed() (v0.13+)

buildReport() (v0.14+)

Writeback persistence (v0.12+)

init() / close()

migrate() (v0.17+)

CRUD operations

insert()

insertReturning() (v0.17+)

upsert()

upsertBy()

update()

updateReturning() (v0.17+)

delete()

get()

query()

count()

Query operators

Render, sync, watch, and reconcile

render()

sync()

watch()

reconcile() (v0.5+)

Events

Raw DB access

Context optimization (v1.3+)

Token budget

Relevance filtering

Enrichment pipeline

Reward-scored memory

Semantic search (v1.3+)

Writeback validation (v1.3+)

Template rendering

Built-in templates

Lifecycle hooks

Field interpolation

Markdown utilities (v0.6+)

frontmatter(fields)

markdownTable(rows, columns)

slugify(name)

truncate(content, maxChars, notice?)

Entity context directories (v0.5+)

Why use it instead of defineMulti()?

Minimal example

Lifecycle — orphan cleanup

Protected files

Reading the manifest

Protected entity contexts (v0.18+)

At-rest encryption (v0.18+)

SESSION.md write pattern

Write entry format

Library support

Full session parser (v0.5.2+)

Configurable entry types (v0.5.5+)

`define()`

`defineMulti()`

`defineEntityContext()` (v0.5+)

`defineWriteHook()` (v0.10+)

`defineWriteback()`

`seed()` (v0.13+)

`buildReport()` (v0.14+)

`init()` / `close()`

`migrate()` (v0.17+)

`insert()`

`insertReturning()` (v0.17+)

`upsert()`

`upsertBy()`

`update()`

`updateReturning()` (v0.17+)

`delete()`

`get()`

`query()`

`count()`

`render()`

`sync()`

`watch()`

`reconcile()` (v0.5+)

`frontmatter(fields)`

`markdownTable(rows, columns)`

`slugify(name)`

`truncate(content, maxChars, notice?)`

Why use it instead of `defineMulti()`?

`lattice.config.yml` reference

CLI — `lattice generate`

CLI — `lattice gui` (v1.11+)