Skip to content

Self-hosted, Self-Improving, Federated Everything App — dev management, agent orchestration, persistent mind, digital twin, second brain, and creative pipeline from a single dashboard over Tailscale to port the user into the OS

47.0 KB
Raw Blame
/**
 * Memory Service — PostgreSQL + pgvector Backend
 *
 * Drop-in replacement for memory.js (file-based) using PostgreSQL with pgvector
 * for vector similarity search and tsvector for full-text search.
 *
 * Same exported interface as memory.js so routes/consumers don't change.
 */

import { ServerError } from '../lib/errorHandler.js';
import { PERSISTENT_MIND_MEMORY_PROTECTION_TAGS } from '../lib/persistentMindMemory.js';
import { v4 as uuidv4 } from '../lib/uuid.js';
import { query, withTransaction, pgvectorToArray, arrayToPgvector } from '../lib/db.js';
import { UUID_RE } from '../lib/fileCore.js';
import { cosEvents } from './cosEvents.js';
import * as notifications from './notifications.js';
import { DEFAULT_MEMORY_CONFIG, generateSummary, decrementAgentPendingApproval } from './memoryConfig.js';
import { getInstanceId } from './instanceIdentity.js';
import { DEFAULT_MEMORY_LINK_TYPE, isSymmetricMemoryLinkType } from '../lib/memoryLinkTypes.js';
import { toPgJson } from '../lib/pgJson.js';

const protectedMindMemoryTags = Object.values(PERSISTENT_MIND_MEMORY_PROTECTION_TAGS);

/**
 * Convert a database row to the memory object format matching the file-based API
 */
function rowToMemory(row) {
  return {
    id: row.id,
    version: row.version,
    archiveReason: row.archive_reason,
    type: row.type,
    content: row.content,
    summary: row.summary,
    category: row.category,
    tags: row.tags || [],
    sourceTaskId: row.source_task_id,
    sourceAgentId: row.source_agent_id,
    sourceAppId: row.source_app_id,
    embedding: row.embedding ? pgvectorToArray(row.embedding) : null,
    embeddingModel: row.embedding_model,
    confidence: row.confidence,
    importance: row.importance,
    accessCount: row.access_count,
    lastAccessed: row.last_accessed?.toISOString() ?? null,
    createdAt: row.created_at.toISOString(),
    updatedAt: row.updated_at.toISOString(),
    expiresAt: row.expires_at?.toISOString() ?? null,
    status: row.status,
    originInstanceId: row.origin_instance_id
  };
}

/**
 * Convert a database row to index-style metadata (lightweight)
 */
function rowToMeta(row) {
  return {
    id: row.id,
    type: row.type,
    category: row.category,
    tags: row.tags || [],
    summary: row.summary,
    importance: row.importance,
    createdAt: row.created_at.toISOString(),
    status: row.status,
    sourceAppId: row.source_app_id,
    sourceAgentId: row.source_agent_id,
  };
}

// =============================================================================
// CRUD Operations
// =============================================================================

// The `embedding` column is a fixed-dimension pgvector (768). Now that the
// embedding provider/model is user-configurable, a mismatched model can yield a
// vector of a different length — Postgres would then throw "expected 768
// dimensions" on EVERY insert, aborting the whole bridge resync. Store NULL with
// one clear warning instead; the record stays un-searchable until it's
// re-embedded with a matching-dimension model. Returns `{ value, model }` for
// the embedding + embedding_model columns so both stay consistent.
function embeddingColumns(embedding) {
  if (!embedding) return { value: null, model: null };
  const expected = DEFAULT_MEMORY_CONFIG.embeddingDimension;
  if (embedding.length !== expected) {
    console.warn(`⚠️ Skipping memory embedding: got ${embedding.length} dims, column expects ${expected} — check the Brain embedding model`);
    return { value: null, model: null };
  }
  return { value: arrayToPgvector(embedding), model: DEFAULT_MEMORY_CONFIG.embeddingModel };
}

/**
 * Create a new memory
 */
async function insertMemory(client, data, embedding) {
  const id = uuidv4();
  const summary = data.summary || generateSummary(data.content);
  const now = new Date().toISOString();

  const originInstanceId = await getInstanceId();
  const embeddingCol = embeddingColumns(embedding);

  const result = await client.query(
    `INSERT INTO memories (
      id, type, content, summary, category, tags,
      embedding, embedding_model, confidence, importance,
      source_task_id, source_agent_id, source_app_id,
      expires_at, status, created_at, updated_at, origin_instance_id
    ) VALUES (
      $1, $2, $3, $4, $5, $6,
      $7, $8, $9, $10,
      $11, $12, $13,
      $14, $15, $16, $17, $18
    ) RETURNING *`,
    [
      id, data.type, data.content, summary, data.category || 'other', data.tags || [],
      embeddingCol.value,
      embeddingCol.model,
      data.confidence ?? 0.8, data.importance ?? 0.5,
      data.sourceTaskId || null, data.sourceAgentId || null, data.sourceAppId || null,
      data.expiresAt || null, data.status || 'active', now, now, originInstanceId
    ]
  );

  const mem = rowToMemory(result.rows[0]);
  // Attach the original embedding array (pgvector may return string
  // representation) — but ONLY when it was actually stored. A
  // wrong-dimension embedding is dropped to NULL by embeddingColumns(), and
  // echoing the rejected array back would tell the caller the record is
  // embedded when it isn't (embedding_model is already NULL), hiding it from
  // re-embed logic that gates on the returned value. Mirrors
  // updateMemoryEmbedding().
  mem.embedding = embeddingCol.value ? embedding : null;
  // Store related memories as links
  if (data.relatedMemories?.length > 0) {
    for (const relId of data.relatedMemories) {
      await client.query(
        'INSERT INTO memory_links (source_id, target_id) VALUES ($1, $2) ON CONFLICT DO NOTHING',
        [id, relId]
      );
    }
    mem.relatedMemories = data.relatedMemories;
  }

  return mem;
}

export async function createMemory(data, embedding = null) {
  const memory = await withTransaction(client => insertMemory(client, data, embedding));
  console.log(`🧠 Memory created: ${memory.type} - ${memory.summary.substring(0, 50)}...`);
  cosEvents.emit('memory:created', { id: memory.id, type: memory.type, summary: memory.summary });
  return memory;
}

/**
 * Import legacy links without replacing an authoritative DB link, then return
 * only live links. A purged row must not make bulk sync skip its brain record.
 * The table is machine-local; memory ids differ across installs.
 */
export async function getBrainMemoryLinks(legacyMap = {}, { readOnly = false, recover = false } = {}) {
  return withTransaction(async client => {
    if (recover) {
      const unmigrated = await client.query(`
        SELECT 1 WHERE EXISTS (SELECT 1 FROM memories WHERE source_app_id = 'brain')
        AND NOT EXISTS (SELECT 1 FROM brain_memory_links)`);
      if (unmigrated.rows.length) {
        throw new Error('Brain bridge cache is corrupt and no durable links exist yet; restore the legacy map from backup before syncing');
      }
    }
    if (!readOnly && Object.keys(legacyMap).length) {
      await client.query(`
        INSERT INTO brain_memory_links (bridge_key, memory_id)
        SELECT legacy.key, m.id FROM jsonb_each_text($1::jsonb) legacy
        JOIN memories m ON m.id::text = legacy.value AND m.source_app_id = 'brain'
        ORDER BY legacy.key
        ON CONFLICT (bridge_key) DO NOTHING`, [toPgJson(legacyMap)]);
    }
    const result = await client.query(`
      SELECT l.bridge_key, l.memory_id FROM brain_memory_links l
      JOIN memories m ON m.id = l.memory_id
      UNION ALL
      SELECT legacy.key, m.id FROM jsonb_each_text($1::jsonb) legacy
      JOIN memories m ON m.id::text = legacy.value AND m.source_app_id = 'brain'
      WHERE NOT EXISTS (SELECT 1 FROM brain_memory_links l WHERE l.bridge_key = legacy.key)`,
    [toPgJson(legacyMap)]);
    return Object.fromEntries(result.rows.map(row => [row.bridge_key, row.memory_id]));
  });
}

/**
 * The link row is also the per-key transaction lock, including the first insert.
 * No FK: retaining a dangling link lets the next upsert heal a purged memory.
 * All memory/history/feed writes and the link replacement commit together.
 */
export async function upsertBrainMemory(key, data, embedding = null, legacyId = null) {
  const result = await withTransaction(async client => {
    await client.query(
      'INSERT INTO brain_memory_links (bridge_key, memory_id) VALUES ($1, $2) ON CONFLICT DO NOTHING',
      [key, UUID_RE.test(legacyId ?? '') ? legacyId : uuidv4()]
    );
    const link = await client.query(
      'SELECT memory_id FROM brain_memory_links WHERE bridge_key = $1 FOR UPDATE', [key]
    );
    const id = link.rows[0].memory_id;
    let memory = await updateMemoryWithClient(client, id, { ...data, status: 'active' });
    const created = !memory;
    if (created) {
      memory = await insertMemory(client, data, embedding);
      await client.query('UPDATE brain_memory_links SET memory_id = $2 WHERE bridge_key = $1', [key, memory.id]);
    } else if (embedding) {
      const columns = embeddingColumns(embedding);
      await client.query('UPDATE memories SET embedding = $1, embedding_model = $2 WHERE id = $3',
        [columns.value, columns.model, id]);
      memory.embedding = columns.value ? embedding : null;
      memory.embeddingModel = columns.model;
    }
    return { memory, created };
  });
  const { memory, created } = result;
  cosEvents.emit(created ? 'memory:created' : 'memory:updated', created
    ? { id: memory.id, type: memory.type, summary: memory.summary }
    : { id: memory.id, updates: data });
  return memory;
}

/**
 * Get a memory by ID
 */
export async function peekMemory(id) {
  const result = await query('SELECT * FROM memories WHERE id = $1', [id]);
  if (result.rows.length === 0) return null;
  return rowToMemory(result.rows[0]);
}

export async function getMemory(id) {
  const result = await query('SELECT * FROM memories WHERE id = $1', [id]);
  if (result.rows.length === 0) return null;

  // Update access stats
  await query(
    'UPDATE memories SET access_count = access_count + 1, last_accessed = NOW() WHERE id = $1',
    [id]
  );

  const memory = rowToMemory(result.rows[0]);
  memory.accessCount += 1;
  memory.lastAccessed = new Date().toISOString();

  // Load related memory IDs from links table
  const links = await query(
    'SELECT target_id FROM memory_links WHERE source_id = $1',
    [id]
  );
  memory.relatedMemories = links.rows.map(r => r.target_id);
  const replacements = await query(
    "SELECT source_id AS id FROM memory_links WHERE target_id = $1 AND link_type = 'supersedes' ORDER BY created_at",
    [id]
  );
  memory.supersededBy = replacements.rows.map(row => row.id);

  return memory;
}

// Historical rows describe the text replaced at createdAt; current lives in memories.
export async function getMemoryVersions(id, { limit = 20, offset = 0 } = {}) {
  const result = await query(
    'SELECT version, changed_by AS "changedBy", change_reason AS "changeReason", created_at AS "createdAt" FROM memory_versions WHERE memory_id = $1 ORDER BY version DESC LIMIT $2 OFFSET $3',
    [id, limit, offset]
  );
  return result.rows;
}

export async function getMemoryVersion(id, version) {
  const current = await peekMemory(id);
  if (!current) return null;
  if (current.version === version) return current;
  const result = await query(
    'SELECT memory_id AS id, version, content, summary, type, category, tags, changed_by AS "changedBy", change_reason AS "changeReason", created_at AS "createdAt" FROM memory_versions WHERE memory_id = $1 AND version = $2',
    [id, version]
  );
  return result.rows[0] ?? null;
}

/**
 * Build shared filters for memory list/count queries.
 */
function buildMemoryFilterWhere(options = {}) {
  const conditions = [];
  const params = [];
  let paramIdx = 1;

  // Filter by status
  const status = options.status || 'active';
  conditions.push(`status = $${paramIdx++}`);
  params.push(status);

  // Filter by types
  if (options.types?.length > 0) {
    conditions.push(`type = ANY($${paramIdx++})`);
    params.push(options.types);
  }

  // Filter by categories
  if (options.categories?.length > 0) {
    conditions.push(`category = ANY($${paramIdx++})`);
    params.push(options.categories);
  }

  // Filter by tags (any match)
  if (options.tags?.length > 0) {
    conditions.push(`tags && $${paramIdx++}`);
    params.push(options.tags);
  }

  // Filter by app
  if (options.appId === '__not_brain') {
    conditions.push(`(source_app_id IS NULL OR source_app_id != 'brain')`);
  } else if (options.appId) {
    conditions.push(`source_app_id = $${paramIdx++}`);
    params.push(options.appId);
  }


  if (options.sourceAgentId) {
    conditions.push(`source_agent_id = $${paramIdx++}`);
    params.push(options.sourceAgentId);
  }

  const where = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
  return { where, params };
}

export async function countMemories(options = {}) {
  const { where, params } = buildMemoryFilterWhere(options);
  const countResult = await query(`SELECT COUNT(*) as total FROM memories ${where}`, params);
  return parseInt(countResult.rows[0].total, 10);
}

/**
 * Get memories with filters
 */
export async function getMemories(options = {}) {
  const { where, params } = buildMemoryFilterWhere(options);
  let paramIdx = params.length + 1;

  // Count total
  const countResult = await query(`SELECT COUNT(*) as total FROM memories ${where}`, params);
  const total = parseInt(countResult.rows[0].total, 10);

  // Sort and paginate
  const sortBy = {
    createdAt: 'created_at',
    updatedAt: 'updated_at',
    importance: 'importance',
    accessCount: 'access_count'
  }[options.sortBy] || 'created_at';
  const sortOrder = options.sortOrder === 'asc' ? 'ASC' : 'DESC';
  const offset = options.offset || 0;
  const limit = options.limit || 50;

  const dataResult = await query(
    `SELECT id, type, category, tags, summary, importance, created_at, status, source_app_id, source_agent_id
     FROM memories ${where}
     ORDER BY ${sortBy} ${sortOrder}
     LIMIT $${paramIdx++} OFFSET $${paramIdx++}`,
    [...params, limit, offset]
  );

  const memories = dataResult.rows.map(rowToMeta);
  return { total, memories };
}

/**
 * Update a memory
 */
async function updateMemoryWithClient(client, id, updates) {
  const existing = await client.query('SELECT * FROM memories WHERE id = $1 FOR UPDATE', [id]);
  if (!existing.rows.length) return null;
  const row = existing.rows[0];
  if (updates.expectedVersion !== undefined && updates.expectedVersion !== row.version) {
    throw new ServerError('Memory changed since it was loaded. Reload before saving.', {
      status: 409, code: 'MEMORY_VERSION_CONFLICT'
    });
  }
  await client.query(
    "SELECT set_config('portos.memory_changed_by', $1, true), set_config('portos.memory_change_reason', $2, true)",
    [updates.changedBy ?? '', updates.changeReason ?? '']
  );
  const values = { ...updates };
  if (values.status === 'archived' && values.changeReason !== undefined) values.archiveReason = values.changeReason;
  if (typeof values.content === 'string' && values.summary === undefined) {
    values.summary = generateSummary(values.content);
  }
  const fieldMap = {
    content: 'content', summary: 'summary', type: 'type', category: 'category',
    tags: 'tags', confidence: 'confidence', importance: 'importance', status: 'status',
    expiresAt: 'expires_at', sourceAppId: 'source_app_id', archiveReason: 'archive_reason'
  };
  const params = [];
  const fields = Object.entries(fieldMap).filter(([key]) => values[key] !== undefined)
    .map(([key, column]) => { params.push(values[key]); return `${column} = $${params.length}`; });
  // The row trigger saves OLD and increments version in this same transaction,
  // including writes from sync. Operational-only writes do not create history.
  let updated = fields.length ? (await client.query(
    `UPDATE memories SET ${fields.join(', ')} WHERE id = $${params.length + 1} RETURNING *`,
    [...params, id]
  )).rows[0] : row;
  if (updates.relatedMemories) {
    // Legacy edits replace only the symmetric relation; preserve typed provenance.
    await client.query("DELETE FROM memory_links WHERE source_id = $1 AND link_type = 'related'", [id]);
    if (updates.relatedMemories.length) {
      const slots = updates.relatedMemories.map((_, i) => `($1, $${i + 2})`).join(', ');
      await client.query(`INSERT INTO memory_links (source_id, target_id) VALUES ${slots} ON CONFLICT DO NOTHING`,
        [id, ...updates.relatedMemories]);
    }
    // Ask the trigger for a local clock; explicit clocks belong to peer replay.
    updated = (await client.query('UPDATE memories SET updated_at = NULL WHERE id = $1 RETURNING *', [id])).rows[0];
  }
  const links = await client.query('SELECT target_id FROM memory_links WHERE source_id = $1', [id]);
  return { ...rowToMemory(updated), relatedMemories: links.rows.map(link => link.target_id) };
}

export async function updateMemory(id, updates) {
  const memory = await withTransaction(client => updateMemoryWithClient(client, id, updates));
  if (!memory) return null;

  console.log(`🧠 Memory updated: ${id}`);
  cosEvents.emit('memory:updated', { id, updates });

  return memory;
}

/**
 * Update a memory's embedding
 */
export async function updateMemoryEmbedding(id, embedding) {
  const embeddingCol = embeddingColumns(embedding);
  const result = await query(
    `UPDATE memories SET embedding = $1, embedding_model = $2 WHERE id = $3 RETURNING *`,
    [embeddingCol.value, embeddingCol.model, id]
  );

  if (result.rows.length === 0) return null;

  const memory = rowToMemory(result.rows[0]);
  memory.embedding = embeddingCol.value ? embedding : null;

  console.log(`🧠 Memory embedding updated: ${id}`);
  return memory;
}

/**
 * Archive a memory: mark it archived; the row and embedding stay, so it is restorable.
 */
async function retireMemory(id, { reason, supersededBy }, expectedStatus) {
  if (supersededBy === id) throw new ServerError('A memory cannot replace itself', { status: 400 });
  return withTransaction(async client => {
    const ids = supersededBy ? [id, supersededBy].sort() : [id];
    const locked = await client.query('SELECT id, status, source_agent_id FROM memories WHERE id = ANY($1::uuid[]) ORDER BY id FOR UPDATE', [ids]);
    const row = locked.rows.find(item => item.id === id);
    if (!row) return { success: false, error: 'Memory not found' };
    if (expectedStatus && row.status !== expectedStatus) return { success: false, error: 'Memory is not pending approval' };
    if (locked.rows.length !== ids.length) throw new ServerError('Replacement memory not found', { status: 404 });
    await client.query("UPDATE memories SET status = 'archived', archive_reason = $2 WHERE id = $1", [id, reason ?? null]);
    if (supersededBy) {
      await client.query(
        "INSERT INTO memory_links (source_id, target_id, link_type, note) VALUES ($1, $2, 'supersedes', $3) ON CONFLICT DO NOTHING",
        [supersededBy, id, reason ?? null]
      );
    }
    return { success: true, sourceAgentId: row.source_agent_id };
  });
}

export async function archiveMemory(id, options = {}) {
  const result = await retireMemory(id, options);
  if (!result.success) throw new ServerError(result.error, { status: 404 });
  console.log(`🧠 Memory archived: ${id}`);
  cosEvents.emit('memory:deleted', { id, hard: false });
  return { success: true, id };
}

/**
 * Purge a memory: DELETE the row (cascades to memory_links) and its embedding. Not restorable.
 */
export async function purgeMemory(id) {
  await query('DELETE FROM memories WHERE id = $1', [id]);

  console.log(`🧠 Memory purged: ${id}`);
  cosEvents.emit('memory:deleted', { id, hard: true });

  return { success: true, id };
}

/**
 * Approve a pending memory
 */
export async function approveMemory(id) {
  const result = await query("UPDATE memories SET status = 'active' WHERE id = $1 AND status = 'pending_approval' RETURNING *", [id]);
  if (!result.rows.length) {
    const existing = await peekMemory(id);
    return { success: false, error: existing ? 'Memory is not pending approval' : 'Memory not found' };
  }
  const row = result.rows[0];

  console.log(`🧠 Memory approved: ${id}`);
  const memory = rowToMemory({ ...row, status: 'active' });
  cosEvents.emit('memory:approved', { id, memory });

  await notifications.removeByMetadata('memoryId', id);
  await decrementAgentPendingApproval(row.source_agent_id);

  return { success: true, memory };
}

/**
 * Reject a pending memory, retaining its text and rejection reason
 */
export async function rejectMemory(id, options = {}) {
  const result = await retireMemory(id, { ...options, reason: options.reason ?? 'Rejected' }, 'pending_approval');
  if (!result.success) return result;
  const { sourceAgentId } = result;

  console.log(`🧠 Memory rejected: ${id}`);
  cosEvents.emit('memory:rejected', { id });

  await notifications.removeByMetadata('memoryId', id);
  await decrementAgentPendingApproval(sourceAgentId);

  return { success: true, id };
}

// =============================================================================
// Search Operations
// =============================================================================

/**
 * Search memories semantically using pgvector cosine similarity
 */
export async function searchMemories(queryEmbedding, options = {}) {
  if (!queryEmbedding) return { total: 0, memories: [] };

  const minRelevance = options.minRelevance || 0.7;
  const limit = options.limit || 20;

  const conditions = ["status = 'active'"];
  const params = [arrayToPgvector(queryEmbedding), minRelevance, limit];
  let paramIdx = 4;

  if (options.types?.length > 0) {
    conditions.push(`type = ANY($${paramIdx++})`);
    params.push(options.types);
  }
  if (options.categories?.length > 0) {
    conditions.push(`category = ANY($${paramIdx++})`);
    params.push(options.categories);
  }
  if (options.tags?.length > 0) {
    conditions.push(`tags && $${paramIdx++}`);
    params.push(options.tags);
  }
  if (options.appId === '__not_brain') {
    conditions.push(`(source_app_id IS NULL OR source_app_id != 'brain')`);
  } else if (options.appId) {
    conditions.push(`source_app_id = $${paramIdx++}`);
    params.push(options.appId);
  }

  const where = conditions.join(' AND ');

  // Use pgvector cosine distance operator <=>
  // Cosine distance = 1 - cosine_similarity, so similarity = 1 - distance
  const result = await query(
    `SELECT id, type, category, tags, summary, importance, created_at, status, source_app_id,
            1 - (embedding <=> $1) AS similarity
     FROM memories
     WHERE embedding IS NOT NULL AND ${where}
       AND 1 - (embedding <=> $1) >= $2
     ORDER BY embedding <=> $1
     LIMIT $3`,
    params
  );

  const memories = result.rows.map(row => ({
    ...rowToMeta(row),
    similarity: parseFloat(row.similarity)
  }));

  return { total: memories.length, memories };
}

/**
 * Hybrid search combining full-text (tsvector) and vector similarity
 * Uses Reciprocal Rank Fusion (RRF) to merge rankings
 */
export async function hybridSearchMemories(queryText, queryEmbedding, options = {}) {
  const { limit = 20, minRelevance = 0.5, ftsWeight = options.bm25Weight ?? 0.4, vectorWeight = 0.6 } = options;
  const RRF_K = 60;

  const filterConditions = ["status = 'active'"];
  const filterParams = [];
  let paramIdx = 1;

  if (options.types?.length > 0) {
    filterConditions.push(`type = ANY($${paramIdx++})`);
    filterParams.push(options.types);
  }
  if (options.categories?.length > 0) {
    filterConditions.push(`category = ANY($${paramIdx++})`);
    filterParams.push(options.categories);
  }
  if (options.tags?.length > 0) {
    filterConditions.push(`tags && $${paramIdx++}`);
    filterParams.push(options.tags);
  }
  if (options.appId === '__not_brain') {
    filterConditions.push(`(source_app_id IS NULL OR source_app_id != 'brain')`);
  } else if (options.appId) {
    filterConditions.push(`source_app_id = $${paramIdx++}`);
    filterParams.push(options.appId);
  }

  const filterWhere = filterConditions.join(' AND ');
  const fetchLimit = limit * 2;

  // Get full-text search results (replaces BM25)
  let ftsResults = [];
  if (queryText) {
    const ftsResult = await query(
      `SELECT id, type, category, tags, summary, importance, created_at, status, source_app_id,
              ts_rank(search_tsv, websearch_to_tsquery('english', $${paramIdx})) AS fts_score
       FROM memories
       WHERE ${filterWhere}
         AND search_tsv @@ websearch_to_tsquery('english', $${paramIdx})
       ORDER BY fts_score DESC
       LIMIT $${paramIdx + 1}`,
      [...filterParams, queryText, fetchLimit]
    );
    ftsResults = ftsResult.rows;
  }

  // Get vector similarity results
  let vectorResults = [];
  if (queryEmbedding) {
    const vecParamStart = filterParams.length + 1;
    const vectorResult = await query(
      `SELECT id, type, category, tags, summary, importance, created_at, status, source_app_id,
              1 - (embedding <=> $${vecParamStart}) AS similarity
       FROM memories
       WHERE embedding IS NOT NULL AND ${filterWhere}
         AND 1 - (embedding <=> $${vecParamStart}) >= $${vecParamStart + 1}
       ORDER BY embedding <=> $${vecParamStart}
       LIMIT $${vecParamStart + 2}`,
      [...filterParams, arrayToPgvector(queryEmbedding), minRelevance * 0.5, fetchLimit]
    );
    vectorResults = vectorResult.rows;
  }

  // Apply Reciprocal Rank Fusion
  const rrfScores = new Map();

  ftsResults.forEach((row, rank) => {
    const current = rrfScores.get(row.id) || { row, ftsRank: null, vectorRank: null, rrfScore: 0 };
    current.row = row;
    current.ftsRank = rank + 1;
    current.rrfScore += ftsWeight / (RRF_K + rank + 1);
    rrfScores.set(row.id, current);
  });

  vectorResults.forEach((row, rank) => {
    const current = rrfScores.get(row.id) || { row, ftsRank: null, vectorRank: null, rrfScore: 0 };
    if (!current.row) current.row = row;
    current.vectorRank = rank + 1;
    current.rrfScore += vectorWeight / (RRF_K + rank + 1);
    rrfScores.set(row.id, current);
  });

  const results = Array.from(rrfScores.values())
    .map(data => ({
      ...rowToMeta(data.row),
      rrfScore: data.rrfScore,
      ftsRank: data.ftsRank,
      vectorRank: data.vectorRank,
      searchMethod: data.ftsRank && data.vectorRank ? 'hybrid' :
                   data.ftsRank ? 'fts' : 'vector'
    }))
    .sort((a, b) => b.rrfScore - a.rrfScore)
    .slice(0, limit);

  return { total: results.length, memories: results };
}

/**
 * Rebuild the BM25 index — no-op for PostgreSQL (tsvector is automatic)
 */
export async function rebuildBM25Index() {
  // tsvector indexes are maintained automatically by PostgreSQL
  // This function exists for API compatibility
  const result = await query("SELECT COUNT(*) as count FROM memories WHERE status = 'active'");
  return { rebuilt: true, documents: parseInt(result.rows[0].count, 10) };
}

/**
 * Get BM25/FTS stats
 */
export async function getBM25Stats() {
  const totalResult = await query("SELECT COUNT(*) as total FROM memories WHERE status = 'active'");

  return {
    documentCount: parseInt(totalResult.rows[0].total, 10),
    backend: 'postgresql-tsvector'
  };
}

// =============================================================================
// Aggregation & Timeline
// =============================================================================

/**
 * Get timeline data (memories grouped by date)
 */
export async function getTimeline(options = {}) {
  const conditions = ["status = 'active'"];
  const params = [];
  let paramIdx = 1;

  if (options.startDate) {
    conditions.push(`created_at >= $${paramIdx++}`);
    params.push(options.startDate);
  }
  if (options.endDate) {
    conditions.push(`created_at <= $${paramIdx++}`);
    params.push(options.endDate);
  }
  if (options.types?.length > 0) {
    conditions.push(`type = ANY($${paramIdx++})`);
    params.push(options.types);
  }
  if (options.appId === '__not_brain') {
    conditions.push(`(source_app_id IS NULL OR source_app_id != 'brain')`);
  } else if (options.appId) {
    conditions.push(`source_app_id = $${paramIdx++}`);
    params.push(options.appId);
  }

  const where = conditions.join(' AND ');
  const limit = options.limit || 100;

  const result = await query(
    `SELECT id, type, category, tags, summary, importance, created_at, status, source_app_id
     FROM memories WHERE ${where}
     ORDER BY created_at DESC
     LIMIT $${paramIdx}`,
    [...params, limit]
  );

  // Group by date
  const timeline = {};
  for (const row of result.rows) {
    const meta = rowToMeta(row);
    const date = meta.createdAt.split('T')[0];
    if (!timeline[date]) timeline[date] = [];
    timeline[date].push(meta);
  }

  return timeline;
}

/**
 * Get all unique categories with counts
 */
export async function getCategories() {
  const result = await query(`
    SELECT category AS name, COUNT(*) AS count
    FROM memories WHERE status = 'active'
    GROUP BY category
    ORDER BY count DESC
  `);
  return result.rows.map(r => ({ name: r.name, count: parseInt(r.count, 10) }));
}

/**
 * Get all unique tags with counts
 */
export async function getTags() {
  const result = await query(`
    SELECT tag AS name, COUNT(*) AS count
    FROM memories, LATERAL unnest(tags) AS tag
    WHERE status = 'active'
    GROUP BY tag
    ORDER BY count DESC
  `);
  return result.rows.map(r => ({ name: r.name, count: parseInt(r.count, 10) }));
}

// =============================================================================
// Relationships
// =============================================================================

/**
 * Get related memories (by links + embedding similarity)
 */
export async function getRelatedMemories(id, limit = 10) {
  const existing = await query('SELECT embedding FROM memories WHERE id = $1', [id]);
  if (existing.rows.length === 0) return [];

  const related = [];

  // Get explicitly linked memories
  // Symmetric links are stored as a reverse pair, so the outgoing row covers
  // them; a directed link is one row, so also read the ones pointing AT this
  // memory (direction 'incoming') or "B supersedes A" would be invisible from A.
  const links = await query(`
    SELECT m.id, m.type, m.category, m.tags, m.summary, m.importance, m.created_at, m.status, m.source_app_id,
           ml.id AS link_id, ml.link_type, ml.note, ml.created_by,
           CASE WHEN ml.source_id = $1 THEN 'outgoing' ELSE 'incoming' END AS direction
    FROM memory_links ml
    JOIN memories m ON m.id = CASE WHEN ml.source_id = $1 THEN ml.target_id ELSE ml.source_id END
    WHERE (ml.source_id = $1 OR (ml.target_id = $1 AND ml.link_type <> 'related'))
      AND m.status = 'active'
    ORDER BY ml.created_at
  `, [id]);

  for (const row of links.rows) {
    related.push({
      ...rowToMeta(row),
      relationship: 'linked',
      similarity: 1.0,
      linkId: row.link_id,
      linkType: row.link_type,
      direction: row.direction,
      note: row.note,
      createdBy: row.created_by
    });
  }

  // Get similar by embedding. Explicit links can already fill or exceed the
  // limit; Postgres rejects a negative LIMIT, so skip the similarity query then.
  const embedding = existing.rows[0].embedding;
  const remaining = limit - related.length;
  if (embedding && remaining > 0) {
    // Exclude already-linked ids in SQL (not after the query) so linked
    // memories ranking among the nearest neighbours can't under-fill (#12768).
    const linkedIds = related.map(r => r.id);

    const readSimilar = (exact = false) => query(`
      SELECT id, type, category, tags, summary, importance, created_at, status, source_app_id,
             1 - (embedding <=> $1) AS similarity
      FROM memories
      WHERE id != $2 AND embedding IS NOT NULL AND status = 'active'
        AND NOT (id = ANY($4::uuid[]))
      ORDER BY ${exact ? '(embedding <=> $1) + 0' : 'embedding <=> $1'}
      LIMIT $3
    `, [embedding, id, remaining, linkedIds]);
    let similar = await readSimilar();
    // ANN scans filter after their bounded candidate search. If they under-fill,
    // an expression sort bypasses the ANN index and scores all eligible rows.
    if (similar.rows.length < remaining) similar = await readSimilar(true);

    for (const row of similar.rows) {
      related.push({
        ...rowToMeta(row),
        relationship: 'similar',
        similarity: parseFloat(row.similarity)
      });
    }
  }

  return related.slice(0, limit);
}

// Optional caller scope for getGraphData: absent (undefined/null) means the
// whole CoS graph; an array — even an empty one — restricts output to edges
// whose BOTH endpoints are in it. A non-UUID id can never name a memory row, so
// dropping it before the ::uuid[] cast changes no result and avoids a cast error.
function graphScope(sourceIds) {
  if (sourceIds === undefined || sourceIds === null) return null;
  if (!Array.isArray(sourceIds)) throw new TypeError('getGraphData: sourceIds must be an array');
  return [...new Set(sourceIds.filter(id => typeof id === 'string' && UUID_RE.test(id)))];
}

/**
 * Get graph data for visualization.
 *
 * `sourceIds` (internal, optional) scopes the read to the records a caller can
 * actually display (#10953): nodes and explicit links are restricted to the
 * scope, and only scoped memories are scored as similarity SOURCES. The target
 * population, the global top-three cutoff, the threshold and the active-record
 * rules are unchanged, so an out-of-scope nearest neighbour still occupies its
 * slot — the scoped edges are exactly the full graph's edges with both
 * endpoints in scope. Omitted, the full graph is returned unchanged.
 */
export async function getGraphData({ sourceIds } = {}) {
  const scope = graphScope(sourceIds);
  if (scope && scope.length === 0) return { nodes: [], edges: [] };
  const scopeParams = scope ? [scope] : [];
  return withTransaction(async (client) => {
    // Admission and all graph reads must share a snapshot: a concurrent insert
    // cannot grow the optimized projection beyond the node count checked here.
    await client.query('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY');
    // Build nodes
    const nodesResult = await client.query(`
      SELECT id, type, category, summary, importance
      FROM memories WHERE status = 'active'${scope ? ' AND id = ANY($1::uuid[])' : ''}
    `, scopeParams);

    const nodes = nodesResult.rows.map(r => ({
      id: r.id,
      type: r.type,
      category: r.category,
      summary: r.summary,
      importance: r.importance
    }));

    // The materialization bound counts EVERY active record, scoped or not: the
    // CTE below still projects the whole active embedding population.
    const activeCount = scope
      ? (await client.query("SELECT count(*)::int AS count FROM memories WHERE status = 'active'")).rows[0].count
      : nodes.length;

    // Build edges from explicit links
    // A symmetric 'related' pair is stored twice; keep one row per pair. Directed
    // types are one row each and keep their source -> target orientation.
    const linksResult = await client.query(`
      SELECT ml.source_id AS source, ml.target_id AS target, ml.link_type
      FROM memory_links ml
      JOIN memories ms ON ms.id = ml.source_id AND ms.status = 'active'
      JOIN memories mt ON mt.id = ml.target_id AND mt.status = 'active'
      WHERE (ml.link_type <> 'related' OR ml.source_id < ml.target_id)
        ${scope ? 'AND ml.source_id = ANY($1::uuid[]) AND ml.target_id = ANY($1::uuid[])' : ''}
    `, scopeParams);

    const edges = linksResult.rows.map(r => ({
      source: r.source,
      target: r.target,
      type: 'linked',
      linkType: r.link_type,
      weight: 1.0
    }));

    // Add similarity edges (top 3 per node, >= 0.8 similarity)
    // Exact top-three scoring still does O(n²) distance work. For bounded graphs,
    // unpack each stored vector once, avoiding repeated TOAST reads per candidate.
    // A bare materialized embedding retains its external storage pointer; the
    // real[] round trip forces an inline vector without changing its float values.
    const materialize = activeCount <= 2048;
    if (materialize) await client.query("SET LOCAL work_mem = '32MB'");
    const relation = materialize ? 'active_embeddings' : 'memories';
    const seenEdges = new Set(edges.map(e => [e.source, e.target].sort().join('-')));

    // A scope restricts the OUTER sources before the lateral loop (so only M
    // sources are scored against all N candidates) and filters targets only
    // AFTER the LIMIT 3 — filtering candidates first would promote a fourth-
    // ranked in-scope memory into a slot an out-of-scope neighbour holds.
    const simResult = await client.query(`
      ${materialize ? `WITH active_embeddings AS MATERIALIZED (
        SELECT id, embedding::real[]::vector AS embedding
        FROM memories WHERE embedding IS NOT NULL AND status = 'active'
      )` : ''}
      SELECT a.id AS source_id, b.id AS target_id,
             1 - (a.embedding <=> b.embedding) AS similarity
      FROM ${relation} a
      CROSS JOIN LATERAL (
        SELECT id, embedding
        FROM ${relation}
        WHERE id != a.id
          ${materialize ? '' : "AND embedding IS NOT NULL AND status = 'active'"}
        ORDER BY embedding <=> a.embedding
        LIMIT 3
      ) b
      WHERE 1 - (a.embedding <=> b.embedding) >= 0.8
        ${materialize ? '' : "AND a.embedding IS NOT NULL AND a.status = 'active'"}
        ${scope ? 'AND a.id = ANY($1::uuid[]) AND b.id = ANY($1::uuid[])' : ''}
    `, scopeParams);

    for (const row of simResult.rows) {
      const edgeKey = [row.source_id, row.target_id].sort().join('-');
      if (!seenEdges.has(edgeKey)) {
        seenEdges.add(edgeKey);
        edges.push({
          source: row.source_id,
          target: row.target_id,
          type: 'similar',
          weight: parseFloat(row.similarity)
        });
      }
    }

    return { nodes, edges };
  });
}

/**
 * Ids of active memories with a NULL embedding — the backfill target for
 * "embed missing" (a record whose embedding generation failed or never ran).
 * Returned as a Set for O(1) membership tests by the bridge.
 */
export async function getMemoryIdsMissingEmbedding() {
  const result = await query(`
    SELECT id FROM memories WHERE embedding IS NULL AND status = 'active'
  `);
  return new Set(result.rows.map(r => r.id));
}

/**
 * Link two memories
 */
export async function linkMemories(sourceId, targetId, { linkType = DEFAULT_MEMORY_LINK_TYPE, note = null, createdBy = 'user' } = {}) {
  // Verify both exist
  const check = await query(
    'SELECT id FROM memories WHERE id = ANY($1)',
    [[sourceId, targetId]]
  );
  if (check.rows.length < 2) return { success: false, error: 'Memory not found' };

  // Only the symmetric 'related' type gets a reverse row; a directed type is one row.
  const values = isSymmetricMemoryLinkType(linkType)
    ? '($1, $2, $3, $4, $5), ($2, $1, $3, $4, $5)'
    : '($1, $2, $3, $4, $5)';
  await query(
    `INSERT INTO memory_links (source_id, target_id, link_type, note, created_by) VALUES ${values}
     ON CONFLICT DO NOTHING`,
    [sourceId, targetId, linkType, note, createdBy]
  );

  const stored = await query(
    'SELECT id FROM memory_links WHERE source_id = $1 AND target_id = $2 AND link_type = $3',
    [sourceId, targetId, linkType]
  );
  return { success: true, sourceId, targetId, linkType, linkId: stored.rows[0]?.id ?? null };
}

// =============================================================================
// Maintenance Operations
// =============================================================================

/**
 * Consolidate similar memories (merge duplicates)
 */
export async function consolidateMemories(threshold = 0.9, dryRun = false, { reason = 'Consolidated duplicate' } = {}) {
  // Use per-row KNN via HNSW index to find near-duplicates (avoids O(n²) self-join)
  const result = await query(`
    SELECT a.id AS id_a, a.summary AS summary_a, a.importance AS importance_a,
           b.id AS id_b, b.summary AS summary_b, b.importance AS importance_b,
           1 - (a.embedding <=> b.embedding) AS similarity
    FROM memories a
    CROSS JOIN LATERAL (
      SELECT id, summary, importance, embedding
      FROM memories
      WHERE id > a.id AND embedding IS NOT NULL AND status = 'active'
        AND NOT (COALESCE(tags, '{}'::text[]) && $2::text[])
      ORDER BY embedding <=> a.embedding
      LIMIT 5
    ) b
    WHERE a.embedding IS NOT NULL AND a.status = 'active'
      AND NOT (COALESCE(a.tags, '{}'::text[]) && $2::text[])
      AND 1 - (a.embedding <=> b.embedding) >= $1
    ORDER BY similarity DESC
  `, [threshold, protectedMindMemoryTags]);

  // Build clusters using union-find
  const parent = new Map();
  const find = (x) => {
    if (!parent.has(x)) parent.set(x, x);
    if (parent.get(x) !== x) parent.set(x, find(parent.get(x)));
    return parent.get(x);
  };
  const union = (a, b) => parent.set(find(a), find(b));

  for (const row of result.rows) {
    union(row.id_a, row.id_b);
  }

  // Group by cluster root
  const clusters = new Map();
  const allIds = new Set();
  for (const row of result.rows) {
    allIds.add(row.id_a);
    allIds.add(row.id_b);
  }

  const summaryMap = new Map();
  for (const row of result.rows) {
    summaryMap.set(row.id_a, row.summary_a);
    summaryMap.set(row.id_b, row.summary_b);
  }

  for (const id of allIds) {
    const root = find(id);
    if (!clusters.has(root)) clusters.set(root, []);
    clusters.get(root).push(id);
  }

  const duplicateClusters = Array.from(clusters.values()).filter(c => c.length > 1);

  if (dryRun) {
    return {
      dryRun: true,
      clustersFound: duplicateClusters.length,
      memoriesAffected: duplicateClusters.reduce((sum, c) => sum + c.length, 0),
      clusters: duplicateClusters.map(c => c.map(id => ({
        id,
        summary: summaryMap.get(id) || ''
      })))
    };
  }

  const archivedIds = await withTransaction(async client => {
    // Lock the complete candidate set in a stable order before choosing keepers.
    // Concurrent consolidation cannot retire a keeper selected from stale state.
    const locked = await client.query(
      "SELECT id, importance FROM memories WHERE id = ANY($1::uuid[]) AND status = 'active' AND NOT (COALESCE(tags, '{}'::text[]) && $2::text[]) ORDER BY id FOR UPDATE",
      [[...allIds], protectedMindMemoryTags]
    );
    const active = new Map(locked.rows.map(row => [row.id, row.importance]));
    const archived = [];
    for (const cluster of duplicateClusters) {
      const members = cluster.filter(id => active.has(id))
        .sort((a, b) => active.get(b) - active.get(a) || a.localeCompare(b));
      if (members.length < 2) continue;
      const [keeper, ...duplicates] = members;
      await client.query("UPDATE memories SET status = 'archived', archive_reason = $2 WHERE id = ANY($1::uuid[])", [duplicates, reason]);
      await client.query(
        "INSERT INTO memory_links (source_id, target_id, link_type, note) SELECT $1::uuid, unnest($2::uuid[]), 'supersedes', $3 ON CONFLICT DO NOTHING",
        [keeper, duplicates, reason]
      );
      archived.push(...duplicates);
    }
    return archived;
  });
  const merged = archivedIds.length;
  for (const id of archivedIds) cosEvents.emit('memory:deleted', { id, hard: false });

  console.log(`🧠 Consolidated ${merged} duplicate memories into ${duplicateClusters.length} clusters`);
  return { merged, clusters: duplicateClusters.length };
}

/**
 * Apply importance decay to old memories
 */
export async function applyDecay(decayRate = 0.01) {
  // Set-based decay: archive old low-importance memories and decay the rest in bulk
  const archived = await withTransaction(async (client) => {
    // Archive memories that have decayed below threshold and are older than 30 days
    const archiveResult = await client.query(`
      UPDATE memories SET status = 'archived',
        importance = GREATEST(0.1,
          importance * (1 - $1 * sqrt(EXTRACT(EPOCH FROM (NOW() - created_at)) / 86400.0))
          + GREATEST(0, 0.1 - EXTRACT(EPOCH FROM (NOW() - COALESCE(last_accessed, created_at))) / 86400.0 * 0.001)
        )
      WHERE status = 'active'
        AND NOT (COALESCE(tags, '{}'::text[]) && $2::text[])
        AND created_at < NOW() - INTERVAL '30 days'
        AND GREATEST(0.1,
          importance * (1 - $1 * sqrt(EXTRACT(EPOCH FROM (NOW() - created_at)) / 86400.0))
          + GREATEST(0, 0.1 - EXTRACT(EPOCH FROM (NOW() - COALESCE(last_accessed, created_at))) / 86400.0 * 0.001)
        ) < 0.15
    `, [decayRate, protectedMindMemoryTags]);

    // Decay importance for remaining active memories where change exceeds threshold
    const decayResult = await client.query(`
      UPDATE memories SET importance = GREATEST(0.1,
        importance * (1 - $1 * sqrt(EXTRACT(EPOCH FROM (NOW() - created_at)) / 86400.0))
        + GREATEST(0, 0.1 - EXTRACT(EPOCH FROM (NOW() - COALESCE(last_accessed, created_at))) / 86400.0 * 0.001)
      )
      WHERE status = 'active'
        AND NOT (COALESCE(tags, '{}'::text[]) && $2::text[])
        AND abs(importance - GREATEST(0.1,
          importance * (1 - $1 * sqrt(EXTRACT(EPOCH FROM (NOW() - created_at)) / 86400.0))
          + GREATEST(0, 0.1 - EXTRACT(EPOCH FROM (NOW() - COALESCE(last_accessed, created_at))) / 86400.0 * 0.001)
        )) > 0.01
    `, [decayRate, protectedMindMemoryTags]);

    return archiveResult.rowCount + decayResult.rowCount;
  });

  console.log(`🧠 Decay applied to ${archived} memories`);
  return { updated: archived };
}

/**
 * Clear expired memories
 */
export async function clearExpired() {
  const result = await query(`
    UPDATE memories SET status = 'expired'
    WHERE status = 'active' AND expires_at IS NOT NULL AND expires_at < NOW()
      AND NOT (COALESCE(tags, '{}'::text[]) && $1::text[])
  `, [protectedMindMemoryTags]);

  const cleared = result.rowCount;
  console.log(`🧠 Cleared ${cleared} expired memories`);
  return { cleared };
}

// =============================================================================
// Stats
// =============================================================================

/**
 * Get memory stats
 */
export async function getStats() {
  const result = await query(`
    SELECT
      COUNT(*) AS total,
      COUNT(*) FILTER (WHERE status = 'active') AS active,
      COUNT(*) FILTER (WHERE status = 'archived') AS archived,
      COUNT(*) FILTER (WHERE status = 'expired') AS expired,
      COUNT(*) FILTER (WHERE status = 'pending_approval') AS pending_approval,
      COUNT(*) FILTER (WHERE embedding IS NOT NULL) AS with_embeddings,
      MAX(updated_at) AS last_updated
    FROM memories
  `);

  const row = result.rows[0];

  // Type breakdown
  const typeResult = await query(`
    SELECT type, COUNT(*) AS count FROM memories GROUP BY type
  `);
  const byType = {};
  for (const r of typeResult.rows) byType[r.type] = parseInt(r.count, 10);

  // Category breakdown
  const catResult = await query(`
    SELECT category, COUNT(*) AS count FROM memories GROUP BY category
  `);
  const byCategory = {};
  for (const r of catResult.rows) byCategory[r.category] = parseInt(r.count, 10);

  return {
    total: parseInt(row.total, 10),
    active: parseInt(row.active, 10),
    archived: parseInt(row.archived, 10),
    expired: parseInt(row.expired, 10),
    pendingApproval: parseInt(row.pending_approval, 10),
    withEmbeddings: parseInt(row.with_embeddings, 10),
    byType,
    byCategory,
    lastUpdated: row.last_updated?.toISOString() ?? null
  };
}

/**
 * Invalidate caches — no-op for PostgreSQL (no in-memory caches)
 */
export function invalidateCaches() {
  // PostgreSQL handles all caching internally
}

/**
 * Flush BM25 index — no-op for PostgreSQL (tsvector is always consistent)
 */
export async function flushBM25Index() {
  // No-op: tsvector indexes are maintained transactionally
}