reflexion.storeEpisode() does not INSERT into episodes/episode_embeddings SQL tables

[PogeystickJoe]

Summary

reflexion.storeEpisode() writes to the in-memory HNSW vector index but does not insert rows into the episodes or episode_embeddings SQLite tables. The tables exist with correct schemas but remain empty after storeEpisode calls. This is distinct from #48 (which fixed sql.js not flushing to disk).

Package: agentdb@3.0.0-alpha.10
Backend: ruvector (384-dim, Xenova/all-MiniLM-L6-v2)
Platform: win32-x64

Reproduction

const { AgentDB } = await import('agentdb');
const db = new AgentDB({ path: './test.db', vectorBackend: 'ruvector', dimensions: 384 });
await db.initialize();

// Store an episode
await db.reflexion.storeEpisode({
  sessionId: 'test-1',
  task: 'HVAC permit research for Tacoma WA',
  reward: 0.85,
  success: true,
  input: 'HVAC permit research for Tacoma WA',
  output: 'Tacoma WA uses Accela portal, mechanical permit required'
});

// Verify: episodes table is empty
const Database = require('better-sqlite3');
const raw = new Database('./test.db', { readonly: true });
console.log(raw.prepare('SELECT COUNT(*) as c FROM episodes').get());
// → { c: 0 }  ← Expected: 1
console.log(raw.prepare('SELECT COUNT(*) as c FROM episode_embeddings').get());
// → { c: 0 }  ← Expected: 1
raw.close();

// But in-memory HNSW has it (same process only):
const results = await db.reflexion.retrieveRelevant({ task: 'Tacoma WA', k: 3 });
console.log(results.length); // → 1 (works in same process)

// After process restart: HNSW is empty, episodes table is empty → data is gone

Expected behavior

storeEpisode() should persist episodes to both the vector index AND the SQL tables, so data survives process restarts.

Actual behavior

• episodes table: 0 rows after storeEpisode calls
• episode_embeddings table: 0 rows
• In-memory HNSW: has the data (but only until process exits)

Workaround

We dual-write using better-sqlite3 alongside storeEpisode:

// 1. HNSW (in-memory, for same-process searches)
await db.reflexion.storeEpisode({ sessionId, task, reward, success, input: task, output });

// 2. SQLite (persistent)
const { pipeline } = await import('@xenova/transformers');
const embedder = await pipeline('feature-extraction', 'Xenova/all-MiniLM-L6-v2');
const result = await embedder(task, { pooling: 'mean', normalize: true });
const embedding = Buffer.from(new Float32Array(result.data).buffer);

const sqlite = new Database(DB_PATH);
const info = sqlite.prepare(
  'INSERT INTO episodes (session_id, task, reward, success, input, output, critique, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
).run(sessionId, task, reward, success ? 1 : 0, task, output, '', new Date().toISOString());

sqlite.prepare(
  'INSERT OR REPLACE INTO episode_embeddings (episode_id, embedding, embedding_model) VALUES (?, ?, ?)'
).run(info.lastInsertRowid, embedding, 'all-MiniLM-L6-v2');

On startup, we rebuild HNSW from SQLite:

const rows = sqlite.prepare(
  'SELECT e.id, e.session_id, e.task, e.reward, e.success, e.input, e.output, ee.embedding ' +
  'FROM episodes e JOIN episode_embeddings ee ON e.id = ee.episode_id'
).all();

const inner = db.vectorBackend.getInner(); // bypass GuardedBackend
for (const row of rows) {
  const vector = new Float32Array(row.embedding.buffer, row.embedding.byteOffset, row.embedding.byteLength / 4);
  await inner.insert(String(row.id), Array.from(vector), {
    sessionId: row.session_id, task: row.task, reward: row.reward
  });
}

This has been running reliably in production with 86+ episodes across 58 jurisdictions.

Related

• AgentDB persistence problem #48 — Original persistence issue (sql.js save fix — different layer)
• AgentDB status command shows 0 records — wrong table names in status.js #127 — Status command queries wrong table names (same schema mismatch pattern)
• Memory List Displays "null" Instead of Content #66 — Memory list shows null (similar SQL field name mismatch)

Impact

This is the core persistence path for reflexion learning. Without the workaround, any application using storeEpisode loses all learned data on restart. The episodes and episode_embeddings tables appear to be defined but never written to by the reflexion controller.