Documentation

# lattice.config.yml
db: ./data/app.db

entities:
  user:
    fields:
      id: { type: uuid, primaryKey: true }
      name: { type: text, required: true }
      email: { type: text }
      role: { type: text, default: member }
    render: default-table
    outputFile: context/USERS.md

  task:
    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 }
    render:
      template: default-list
      formatRow: '{{title}} ({{status}}) — {{assignee.name}}'
    outputFile: context/TASKS.md

  agent_skill:
    fields:
      agent_id: { type: uuid }
      skill_id: { type: uuid }
    primaryKey: [agent_id, skill_id]

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',
});

// Schema-only — no rendered output
db.define('session', {
  columns: {
    id: 'TEXT PRIMARY KEY',
    agent_id: 'TEXT NOT NULL',
    started_at: 'TEXT',
    token_count: 'INTEGER DEFAULT 0',
  },
});

// Full CRUD works as normal
const id = await db.insert('session', { agent_id: 'a1', started_at: new Date().toISOString() });
const row = await db.get('session', id);

// From YAML config (recommended)
const db = new Lattice({ config: './lattice.config.yml' });

// From a database path (define tables in code)
const db = new Lattice('./app.db');

// In-memory for tests
const db = new Lattice(':memory:');

// With options
const db = new Lattice('./app.db', {
  wal: true,           // WAL journal mode (default: true)
  busyTimeout: 10_000, // ms to wait on locked DB (default: 5000)
  security: {
    sanitize: true,
    auditTables: ['users', 'credentials'],
    fieldLimits: { notes: 50_000, bio: 2_000 },
  },
});

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 run once each — safe to call init() on every process start.

db.close(); // call on shutdown

await db.init();

// Apply migrations after init
await db.migrate([
  { version: 3, sql: 'ALTER TABLE tasks ADD COLUMN due_date TEXT' },
  { version: '2026-04-01', sql: 'ALTER TABLE tasks ADD COLUMN priority INTEGER DEFAULT 0' },
]);

// Create a record — returns the generated id
const id = await db.insert('task', { title: 'Write docs', status: 'open' });

// Update or create by primary key
await db.upsert('task', { id: 'task-001', title: 'Updated title', status: 'done' });

// Update or create by any column
await db.upsertBy('user', 'email', 'alice@example.com', { name: 'Alice' });

// Update specific fields on an existing record
await db.update('task', 'task-001', { status: 'done' });

// Fetch a single record by primary key
const task = await db.get('task', 'task-001');

// Delete a record
await db.delete('task', 'task-001');

// Insert and return the full row
const newTask = await db.insertReturning('task', { title: 'Write docs', status: 'open' });
// newTask = { id: 'generated-uuid', title: 'Write docs', status: 'open', ... }

// Update and return the updated row
const updated = await db.updateReturning('task', 'task-001', { status: 'done' });
// updated = { id: 'task-001', title: 'Write docs', status: 'done', ... }

// Find open tasks with priority >= 3, newest first
const urgent = await db.query('task', {
  where: { status: 'open' },
  filters: [{ col: 'priority', op: 'gte', val: 3 }],
  orderBy: 'created_at',
  orderDir: 'desc',
  limit: 20,
});

// Count matching records
const openCount = await db.count('task', { where: { status: 'open' } });

// Create or update a record by its name
await db.upsertByNaturalKey('agent', 'name', 'Alice', {
  role: 'engineer', status: 'active',
});

// Update only the fields you pass (leaves others untouched)
await db.enrichByNaturalKey('agent', 'name', 'Alice', { title: 'Senior Engineer' });

// Look up a record by name
const alice = await db.getByNaturalKey('agent', 'name', 'Alice');

// Get all non-deleted records
const agents = await db.getActive('agent', 'name');
const count = await db.countActive('agent');

// Soft-delete records that are no longer in a source file
await db.softDeleteMissing('agent', 'name', 'agents.yaml', ['Alice', 'Bob']);

// Link / unlink records in a junction table
await db.link('agent_skill', { agent_id: 'a1', skill_id: 's1' });
await db.unlink('agent_skill', { agent_id: 'a1', skill_id: 's1' });

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

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

await db.seed({
  data: rules,
  table: 'rule',
  naturalKey: 'title',
  sourceFile: 'rules.yaml',
  linkTo: {
    targetAgents: {
      junction: 'rule_agent',
      foreignKey: 'agent_id',
      resolveBy: 'name',
      resolveTable: 'agent',
    },
  },
  softDeleteMissing: true,
});

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

// Render + process agent output
await db.sync('./context');

// Watch with auto-cleanup
const stop = await db.watch('./context', {
  interval: 5_000,
  cleanup: { removeOrphanedDirectories: true, protectedFiles: ['SESSION.md'] },
});

// Render + clean up orphaned directories
await db.reconcile('./context', {
  removeOrphanedDirectories: true,
  protectedFiles: ['SESSION.md'],
  dryRun: false,   // set to true to preview without deleting
});

db.defineEntityContext('agent', {
  // How to name each directory (e.g. agents/alice/)
  slug: (row) => row.slug as string,

  // Apply to all relationship sources
  sourceDefaults: { softDelete: true },

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

  // Files inside each agent's directory
  files: {
    'AGENT.md': {
      source: { type: 'self' },
      render: ([r]) => `# ${r.name}\n\n${r.persona ?? ''}`,
    },
    'TASKS.md': {
      source: { type: 'hasMany', table: 'task', foreignKey: 'agent_id',
                orderBy: 'created_at', orderDir: 'desc', limit: 20 },
      render: (rows) => rows.map((r) => `- ${r.title}`).join('\n'),
      omitIfEmpty: true,
      budget: 4000,
    },
    'SKILLS.md': {
      source: {
        type: 'manyToMany',
        junctionTable: 'agent_skill',
        localKey: 'agent_id',
        remoteKey: 'skill_id',
        remoteTable: 'skill',
      },
      render: (rows) => rows.map((r) => `- ${r.name}`).join('\n'),
      omitIfEmpty: true,
    },
  },

  // Merge all files into one combined file
  combined: { outputFile: 'CONTEXT.md', exclude: [] },
  // These files (written by agents) are never deleted during cleanup
  protectedFiles: ['SESSION.md'],
});

// In your entity context definition:
'AGENT.md': {
  source: { type: 'self' },
  render: ([r]) => `# ${r.name}\n**Role:** ${r.role}\n`,
  reverseSync: (content, entityRow) => {
    const match = content.match(/^# (.+)$/m);
    if (match && match[1] !== entityRow.name) {
      return [{ table: 'agent', pk: { id: entityRow.id }, set: { name: match[1] } }];
    }
    return [];
  },
}

// Control via reconcile options:
await db.reconcile(dir);                             // reverse-sync enabled (default)
await db.reconcile(dir, { reverseSync: 'dry-run' }); // detect but don't modify DB
await db.reconcile(dir, { reverseSync: false });      // skip entirely

db.defineWriteback({
  // Watch all SESSION.md files across agent directories
  file: './context/agents/*/SESSION.md',

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

  // Save each entry to the database
  async persist(entry, filePath) {
    await db.insert('event', {
      source_file: filePath,
      ...(entry as Record<string, unknown>),
    });
  },

  // Prevent duplicate processing
  dedupeKey: (entry) => (entry as { id: string }).id,
});

// db.sync() renders context AND processes writeback files
await db.sync('./context');

import { createSQLiteStateStore } from 'latticesql';

// Offsets and dedup keys survive process restarts
const stateStore = createSQLiteStateStore('./state.db');

db.defineWriteback({
  file: './context/agents/*/SESSION.md',
  stateStore,
  parse(content, fromOffset) { /* ... */ },
  async persist(entry) { /* ... */ },
});

---
type: write
timestamp: 2026-03-25T10:30:00Z
op: update
table: agent
target: agent-id-here
reason: Completed deployment task.
---
status: idle
last_task: deploy-api
===

import { parseSessionWrites, parseSessionMD, applyWriteEntry } from 'latticesql';

// Parse write entries only
const result = parseSessionWrites(sessionContent);
for (const entry of result.entries) {
  // entry.op, entry.table, entry.target, entry.fields, entry.reason
  const applied = applyWriteEntry(db.db, entry);  // pass raw SQLite connection
}

// Parse all entry types (events, learnings, writes, etc.)
const all = parseSessionMD(content, startOffset);
// all.entries: SessionEntry[], all.lastOffset: number

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

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
});
// Output: "- Fix auth bug\n- Deploy v2\n\n[truncated: 47 of 123 rows rendered, ~3800 tokens]"

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 — only matching rows are rendered
db.setTaskContext('deployment');
await db.render('./context');

// Clear context — all rows rendered again
db.setTaskContext('');

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: [
    // Add computed age field
    (rows) => rows.map((r) => ({
      ...r,
      _age_hours: Math.round((Date.now() - new Date(r.created_at).getTime()) / 3600000),
    })),
    // Summarize if too many rows
    (rows) => rows.length > 100
      ? [{ _summary: `${rows.length} incidents, ${rows.filter(r => r.severity === 'P0').length} critical` }]
      : rows,
  ],
});

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
});

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 });
// _reward_total = 0.95, _reward_count = 1

// Second signal — running average
await db.reward('tips', id, { relevance: 0.5 });
// _reward_total = 0.7, _reward_count = 2

import { Lattice } from 'latticesql';

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}`);
}

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 hasFields = text.includes('## Title') && text.includes('## Body');
    return {
      pass: hasFields,
      score: hasFields ? 0.9 : 0.1,
      reason: hasFields ? undefined : 'Missing required sections',
    };
  },
  rejectBelow: 0.5,
  onReject: (entry, result) => {
    console.warn(`Rejected: ${result.reason} (score: ${result.score})`);
  },
});

// Custom polling loop that skips redundant renders
setInterval(async () => {
  if (db.isDirty()) {
    await db.render(outputDir);
  }
}, 5000);

// After direct DB writes, mark dirty
db.db.prepare('UPDATE tasks SET status = ?').run('done');
db.markDirty('tasks');

await db.init({
  migrations: [
    { version: 1, sql: 'ALTER TABLE tasks ADD COLUMN due_date TEXT' },
  ],
  validateMigrationSQL: (sql) => {
    if (sql.trim().length === 0) return { valid: false, errors: ['Empty SQL'] };
    return { valid: true };
  },
});

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

report.sections.forEach(s => console.log(`${s.name}: ${s.count}`));

import { frontmatter, markdownTable, slugify, truncate } from 'latticesql';

// YAML frontmatter with auto timestamp
frontmatter({ agent: 'Alice', skill_count: 5 });

// Markdown table from rows
markdownTable(rows, [
  { key: 'name', header: 'Name' },
  { key: 'status', header: 'Status', format: (v) => String(v || '—') },
]);

// URL-safe slug
slugify('Alice Smith');   // 'alice-smith'

// Truncate to character budget
truncate(longContent, 4000);

import { autoUpdate } from 'latticesql';

// Call at app startup — checks npm, installs if outdated
const result = await autoUpdate();
if (result.restartRequired) {
  process.exit(0); // Let process manager restart
}

interface AutoUpdateResult {
  updated: boolean;
  packages: Array<{ name: string; from: string; to: string }>;
  restartRequired: boolean;
}

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