Soul-driven AI agent with permission-hardened tools, token budgets, and multi-channel access. Runs 24/7 from CLI, Telegram or More.
import { existsSync, mkdirSync } from 'node:fs';
import { dirname } from 'node:path';
import { logger } from '../utils/logger.js';
import { isSqliteAvailable, openSqlite, type SqliteDatabase } from '../utils/sqlite-driver.js';
import { exclusiveGroup, normalizeRelationship } from './person-standing.js';
/**
* True when any SQLite engine is available (better-sqlite3, bun:sqlite or
* node:sqlite — see `src/utils/sqlite-driver.ts`).
* @deprecated The name predates the shared driver; prefer `isSqliteAvailable()`.
*/
export function isBetterSqlite3Available(): boolean {
return isSqliteAvailable();
}
export const SECOND_BRAIN_UNAVAILABLE_MESSAGE =
'No SQLite engine is available — second brain memory requires one. ' +
'Use Node >= 22.13 (built-in node:sqlite), a standalone Mercury binary (bun:sqlite), ' +
'or install build tools (make, gcc/g++, python3) and run `npm rebuild better-sqlite3`. ' +
'Run `mercury doctor --storage` for details.';
export interface MemoryRow {
id: string;
user_key: string;
type: string;
categories: string;
shareable: number;
summary: string;
detail: string | null;
scope: string;
evidence_kind: string;
source: string;
confidence: number;
importance: number;
durability: number;
evidence_count: number;
provenance: string | null;
dismissed: number;
superseded_by: string | null;
created_at: number;
updated_at: number;
last_seen_at: number;
last_used_at: number | null;
last_used_query: string | null;
}
export interface PersonRow {
id: string;
user_key: string;
canonical_name: string;
display_name: string;
relationship_to_user: string | null;
description: string | null;
confidence: number;
first_seen_at: number;
last_seen_at: number;
created_at: number;
updated_at: number;
/** 'person' | 'pet' | 'unverified' (never 'not_person': those are not stored). */
kind?: string;
/** Who decided `kind`: 'owner' | 'model' | 'rule' | null. */
verified_by?: string | null;
/** Contact card: JSON { phone, email, birthday, location, company, notes, links[] }. */
meta?: string;
/** 1 = the owner edited this person; rebuilds never overwrite or delete it. */
owner_edited?: number;
}
export type PersonKind = 'person' | 'pet' | 'unverified';
export interface PersonGraph {
persons: Array<{ id: string; name: string; kind: string; relationship: string | null; memoryCount: number }>;
memories: Array<{ id: string; summary: string; type: string; importance: number; updatedAt: number }>;
memoryPersons: Array<{ memoryId: string; personId: string; role: string }>;
relationships: Array<{ source: string; target: string; type: string; strength: number }>;
}
export interface PersonListRow extends PersonRow {
memory_count: number;
}
export interface PersonConnectionRow {
id: string;
display_name: string;
relation_type: string;
strength: number;
}
/** Most distinct terms a memory search uses; keeps queries far below SQLite's
* expression-depth limit (1000), which a long pasted message used to blow
* through in both the FTS query and its LIKE fallback, failing the turn. */
const MAX_SEARCH_TERMS = 32;
/**
* Search terms for a free-text query: distinct lowercase words of 2+ letters
* or digits (punctuation and FTS operators can't leak in), first occurrence
* order, capped at MAX_SEARCH_TERMS.
*/
export function ftsSearchTerms(query: string): string[] {
const seen = new Set<string>();
for (const match of query.toLowerCase().matchAll(/[\p{L}\p{N}_]+/gu)) {
const term = match[0];
if (term.length < 2 || seen.has(term)) continue;
seen.add(term);
if (seen.size >= MAX_SEARCH_TERMS) break;
}
return [...seen];
}
export class SecondBrainDB {
private db: SqliteDatabase;
constructor(dbPath: string) {
const dir = dirname(dbPath);
if (!existsSync(dir)) {
mkdirSync(dir, { recursive: true });
}
const db = openSqlite(dbPath);
if (!db) throw new Error(SECOND_BRAIN_UNAVAILABLE_MESSAGE);
this.db = db;
this.db.pragma('journal_mode = WAL');
this.db.pragma('synchronous = NORMAL');
}
/** Engine backing this database: `better-sqlite3`, `bun:sqlite` or `node:sqlite`. */
get backend(): string {
return this.db.backend;
}
/** The shared connection, for stores that keep their own tables in this file (beliefs). */
get connection(): SqliteDatabase {
return this.db;
}
init(): void {
this.db.exec(`
CREATE TABLE IF NOT EXISTS memories (
id TEXT PRIMARY KEY,
user_key TEXT NOT NULL,
type TEXT NOT NULL,
categories TEXT NOT NULL DEFAULT '[]',
shareable INTEGER NOT NULL DEFAULT 0,
summary TEXT NOT NULL,
detail TEXT,
scope TEXT NOT NULL DEFAULT 'durable',
evidence_kind TEXT NOT NULL DEFAULT 'inferred',
source TEXT NOT NULL DEFAULT 'conversation',
confidence REAL NOT NULL,
importance REAL NOT NULL,
durability REAL NOT NULL,
evidence_count INTEGER NOT NULL DEFAULT 1,
provenance TEXT,
dismissed INTEGER NOT NULL DEFAULT 0,
superseded_by TEXT,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
last_seen_at INTEGER NOT NULL,
last_used_at INTEGER,
last_used_query TEXT
);
CREATE VIRTUAL TABLE IF NOT EXISTS memories_fts USING fts5(
summary, detail, content=memories, content_rowid=rowid
);
CREATE TABLE IF NOT EXISTS second_brain_meta (
key TEXT PRIMARY KEY,
value TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS persons (
id TEXT PRIMARY KEY,
user_key TEXT NOT NULL,
canonical_name TEXT NOT NULL,
display_name TEXT NOT NULL,
relationship_to_user TEXT,
description TEXT,
confidence REAL NOT NULL DEFAULT 0.6,
first_seen_at INTEGER NOT NULL,
last_seen_at INTEGER NOT NULL,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
UNIQUE(user_key, canonical_name)
);
CREATE TABLE IF NOT EXISTS person_aliases (
id TEXT PRIMARY KEY,
person_id TEXT NOT NULL,
alias TEXT NOT NULL,
normalized_alias TEXT NOT NULL,
source TEXT NOT NULL DEFAULT 'memory',
created_at INTEGER NOT NULL,
UNIQUE(person_id, normalized_alias),
FOREIGN KEY (person_id) REFERENCES persons(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS memory_persons (
memory_id TEXT NOT NULL,
person_id TEXT NOT NULL,
role TEXT NOT NULL DEFAULT 'mentioned',
confidence REAL NOT NULL DEFAULT 0.7,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
PRIMARY KEY(memory_id, person_id),
FOREIGN KEY (memory_id) REFERENCES memories(id) ON DELETE CASCADE,
FOREIGN KEY (person_id) REFERENCES persons(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS person_relationships (
id TEXT PRIMARY KEY,
user_key TEXT NOT NULL,
source_person_id TEXT NOT NULL,
target_person_id TEXT NOT NULL,
relation_type TEXT NOT NULL,
strength REAL NOT NULL DEFAULT 0.5,
evidence_memory_id TEXT,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
UNIQUE(user_key, source_person_id, target_person_id, relation_type),
FOREIGN KEY (source_person_id) REFERENCES persons(id) ON DELETE CASCADE,
FOREIGN KEY (target_person_id) REFERENCES persons(id) ON DELETE CASCADE,
FOREIGN KEY (evidence_memory_id) REFERENCES memories(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_memories_user_type ON memories(user_key, type);
CREATE INDEX IF NOT EXISTS idx_memories_user_dismissed ON memories(user_key, dismissed);
CREATE INDEX IF NOT EXISTS idx_memories_user_updated ON memories(user_key, updated_at);
CREATE INDEX IF NOT EXISTS idx_memories_user_scope ON memories(user_key, scope);
CREATE INDEX IF NOT EXISTS idx_memories_user_evidence_kind ON memories(user_key, evidence_kind);
CREATE INDEX IF NOT EXISTS idx_persons_user_name ON persons(user_key, canonical_name);
CREATE INDEX IF NOT EXISTS idx_person_aliases_person ON person_aliases(person_id);
CREATE INDEX IF NOT EXISTS idx_memory_persons_person ON memory_persons(person_id);
CREATE INDEX IF NOT EXISTS idx_person_relationships_source ON person_relationships(source_person_id);
CREATE INDEX IF NOT EXISTS idx_person_relationships_target ON person_relationships(target_person_id);
CREATE TRIGGER IF NOT EXISTS memories_ai AFTER INSERT ON memories BEGIN
INSERT INTO memories_fts(rowid, summary, detail) VALUES (new.rowid, new.summary, new.detail);
END;
CREATE TRIGGER IF NOT EXISTS memories_ad AFTER DELETE ON memories BEGIN
INSERT INTO memories_fts(memories_fts, rowid, summary, detail) VALUES('delete', old.rowid, old.summary, old.detail);
END;
CREATE TRIGGER IF NOT EXISTS memories_au AFTER UPDATE ON memories BEGIN
INSERT INTO memories_fts(memories_fts, rowid, summary, detail) VALUES('delete', old.rowid, old.summary, old.detail);
INSERT INTO memories_fts(rowid, summary, detail) VALUES (new.rowid, new.summary, new.detail);
END;
`);
this.db.pragma('foreign_keys = ON');
// ── Migrations for existing DBs ──────────────────────────────────────────
// Add `categories` and `shareable` columns if the table predates them.
// Existing memories get categories='[]' and shareable=0 (private).
const columns = this.db.prepare('PRAGMA table_info(memories)').all() as { name: string }[];
if (!columns.some((c) => c.name === 'categories')) {
this.db.exec("ALTER TABLE memories ADD COLUMN categories TEXT NOT NULL DEFAULT '[]'");
logger.info('Added categories column to memories table');
}
if (!columns.some((c) => c.name === 'shareable')) {
this.db.exec('ALTER TABLE memories ADD COLUMN shareable INTEGER NOT NULL DEFAULT 0');
logger.info('Added shareable column to memories table');
}
// Index for incremental fetch (shareable + updated_at — the cloud pull query).
this.db.exec('CREATE INDEX IF NOT EXISTS idx_memories_shareable_updated ON memories(shareable, updated_at)');
// People: what an entity is, who decided, the contact card, and owner edits.
const personColumns = this.db.prepare('PRAGMA table_info(persons)').all() as { name: string }[];
const addPersonColumn = (name: string, ddl: string) => {
if (!personColumns.some((c) => c.name === name)) this.db.exec(`ALTER TABLE persons ADD COLUMN ${ddl}`);
};
addPersonColumn('kind', "kind TEXT NOT NULL DEFAULT 'unverified'");
addPersonColumn('verified_by', 'verified_by TEXT');
addPersonColumn('meta', "meta TEXT NOT NULL DEFAULT '{}'");
addPersonColumn('owner_edited', 'owner_edited INTEGER NOT NULL DEFAULT 0');
this.db.exec(`
CREATE TABLE IF NOT EXISTS person_overrides (
user_key TEXT NOT NULL,
canonical_name TEXT NOT NULL,
action TEXT NOT NULL,
target_person_id TEXT,
created_at INTEGER NOT NULL,
PRIMARY KEY (user_key, canonical_name)
);
CREATE TABLE IF NOT EXISTS belief_persons (
belief_id TEXT NOT NULL,
person_id TEXT NOT NULL,
role TEXT NOT NULL DEFAULT 'mentioned',
confidence REAL NOT NULL DEFAULT 0.7,
created_at INTEGER NOT NULL,
PRIMARY KEY (belief_id, person_id),
FOREIGN KEY (person_id) REFERENCES persons(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_belief_persons_person ON belief_persons(person_id);
CREATE TABLE IF NOT EXISTS person_checks (
person_id TEXT PRIMARY KEY,
user_key TEXT NOT NULL,
name TEXT NOT NULL,
context TEXT,
queued_at INTEGER NOT NULL,
FOREIGN KEY (person_id) REFERENCES persons(id) ON DELETE CASCADE
);
`);
logger.info({ backend: this.db.backend }, 'Second brain database initialized');
}
insert(row: Omit<MemoryRow, 'rowid'> & { rowid?: never }): void {
const stmt = this.db.prepare(`
INSERT INTO memories (
id, user_key, type, categories, shareable, summary, detail, scope, evidence_kind, source,
confidence, importance, durability, evidence_count, provenance,
dismissed, superseded_by, created_at, updated_at,
last_seen_at, last_used_at, last_used_query
) VALUES (
@id, @user_key, @type, @categories, @shareable, @summary, @detail, @scope, @evidence_kind, @source,
@confidence, @importance, @durability, @evidence_count, @provenance,
@dismissed, @superseded_by, @created_at, @updated_at,
@last_seen_at, @last_used_at, @last_used_query
)
`);
stmt.run({
id: row.id,
user_key: row.user_key,
type: row.type,
categories: row.categories ?? '[]',
shareable: row.shareable ?? 0,
summary: row.summary,
detail: row.detail ?? null,
scope: row.scope ?? 'durable',
evidence_kind: row.evidence_kind ?? 'inferred',
source: row.source ?? 'conversation',
confidence: row.confidence,
importance: row.importance,
durability: row.durability,
evidence_count: row.evidence_count ?? 1,
provenance: row.provenance ?? null,
dismissed: row.dismissed ?? 0,
superseded_by: row.superseded_by ?? null,
created_at: row.created_at,
updated_at: row.updated_at,
last_seen_at: row.last_seen_at,
last_used_at: row.last_used_at ?? null,
last_used_query: row.last_used_query ?? null,
});
}
update(row: Partial<MemoryRow> & { id: string }): void {
const fields: string[] = [];
const values: Record<string, unknown> = { id: row.id };
const allowedFields = [
'summary', 'detail', 'scope', 'evidence_kind', 'source',
'confidence', 'importance', 'durability', 'evidence_count',
'provenance', 'dismissed', 'superseded_by',
'updated_at', 'last_seen_at', 'last_used_at', 'last_used_query',
'categories', 'shareable',
] as const;
for (const field of allowedFields) {
if (row[field] !== undefined) {
fields.push(`${field} = @${field}`);
values[field] = row[field];
}
}
if (fields.length === 0) return;
const stmt = this.db.prepare(`UPDATE memories SET ${fields.join(', ')} WHERE id = @id`);
stmt.run(values);
}
getActive(userKey: string): MemoryRow[] {
const stmt = this.db.prepare(`SELECT * FROM memories WHERE user_key = ? AND dismissed = 0 AND scope IN ('active', 'durable') ORDER BY updated_at DESC`);
return stmt.all(userKey) as MemoryRow[];
}
getById(id: string): MemoryRow | undefined {
const stmt = this.db.prepare('SELECT * FROM memories WHERE id = ?');
return stmt.get(id) as MemoryRow | undefined;
}
/**
* Incremental fetch query — shareable memories newer than the cursor.
* Used by the cloud `memory.fetch` handler to pull only memories created or
* updated since the last fetch (watermark stored in MemorySyncState).
* Excludes dismissed rows. Ordered by `updated_at ASC` so the cloud can
* advance its cursor to the max `updated_at` safely.
*/
getShareableSince(userKey: string, since: number, limit: number = 500): MemoryRow[] {
const stmt = this.db.prepare(`
SELECT * FROM memories
WHERE user_key = ? AND shareable = 1 AND dismissed = 0
AND updated_at > ?
ORDER BY updated_at ASC
LIMIT ?
`);
return stmt.all(userKey, since, limit) as MemoryRow[];
}
/** Count shareable memories (for display / config). */
countShareable(userKey: string): number {
const stmt = this.db.prepare('SELECT COUNT(*) as count FROM memories WHERE user_key = ? AND shareable = 1 AND dismissed = 0');
return (stmt.get(userKey) as { count: number }).count;
}
/** List all shareable memories (for the /memory menu "Shared Memories" view). */
getShareable(userKey: string, limit: number = 50): MemoryRow[] {
const stmt = this.db.prepare(`SELECT * FROM memories WHERE user_key = ? AND shareable = 1 AND dismissed = 0 ORDER BY updated_at DESC LIMIT ?`);
return stmt.all(userKey, limit) as MemoryRow[];
}
/** Toggle a memory's shareable flag on/off. */
setShareable(id: string, shareable: boolean): void {
const stmt = this.db.prepare('UPDATE memories SET shareable = ?, updated_at = ? WHERE id = ?');
stmt.run(shareable ? 1 : 0, Date.now(), id);
}
getByType(userKey: string, type: string): MemoryRow[] {
const stmt = this.db.prepare(`SELECT * FROM memories WHERE user_key = ? AND type = ? AND dismissed = 0 AND scope IN ('active', 'durable') ORDER BY updated_at DESC`);
return stmt.all(userKey, type) as MemoryRow[];
}
findMergeCandidate(userKey: string, type: string, normalizedTerms: string[]): MemoryRow | undefined {
const stmt = this.db.prepare(`
SELECT * FROM memories WHERE user_key = ? AND type = ? AND dismissed = 0
AND (summary LIKE ? OR ${normalizedTerms.map(() => 'summary LIKE ?').join(' OR ')})
LIMIT 5
`);
const likeAny = normalizedTerms.map(t => `%${t}%`);
const rows = stmt.all(userKey, type, `%${normalizedTerms.slice(0, 3).join('%')}%`, ...likeAny) as MemoryRow[];
return rows.find(row => !this.rowHasNegationMismatch(row.summary, normalizedTerms));
}
findConflictCandidate(userKey: string, type: string, summaryTerms: string[]): MemoryRow | undefined {
const stmt = this.db.prepare(`
SELECT * FROM memories WHERE user_key = ? AND type = ? AND dismissed = 0
AND (${summaryTerms.map(() => 'summary LIKE ?').join(' OR ')})
LIMIT 5
`);
const likes = summaryTerms.map(t => `%${t}%`);
const rows = stmt.all(userKey, type, ...likes) as MemoryRow[];
return rows.find(row => this.rowHasNegationMismatch(row.summary, summaryTerms));
}
private rowHasNegationMismatch(existingSummary: string, incomingTerms: string[]): boolean {
const lower = existingSummary.toLowerCase();
const negationWords = ['not', 'never', 'no longer', 'avoid', 'against', 'disabled'];
const hasNegation = negationWords.some(w => lower.includes(w));
const incomingLower = incomingTerms.join(' ');
const incomingHasNegation = negationWords.some(w => incomingLower.includes(w));
return hasNegation !== incomingHasNegation;
}
searchRelevant(userKey: string, query: string, limit: number = 10): MemoryRow[] {
const tokens = ftsSearchTerms(query);
if (tokens.length === 0) {
const stmt = this.db.prepare(`SELECT * FROM memories WHERE user_key = ? AND dismissed = 0 AND scope IN ('active', 'durable') ORDER BY updated_at DESC LIMIT ?`);
return stmt.all(userKey, limit) as MemoryRow[];
}
// Quoted: punctuation in a term can't break FTS5 query syntax.
const ftsQuery = tokens.map((term) => `"${term}"`).join(' OR ');
const ftsStmt = this.db.prepare(`
SELECT m.* FROM memories m
JOIN memories_fts fts ON m.rowid = fts.rowid
WHERE memories_fts MATCH ? AND m.user_key = ? AND m.dismissed = 0 AND m.scope IN ('active', 'durable')
ORDER BY rank
LIMIT ?
`);
try {
return ftsStmt.all(ftsQuery, userKey, limit) as MemoryRow[];
} catch {
const likeClauses = tokens.map(() => '(summary LIKE ? OR detail LIKE ?)').join(' OR ');
const stmt = this.db.prepare(`SELECT * FROM memories WHERE user_key = ? AND dismissed = 0 AND scope IN ('active', 'durable') AND (${likeClauses}) ORDER BY updated_at DESC LIMIT ?`);
const likes = tokens.flatMap(t => [`%${t}%`, `%${t}%`]);
return stmt.all(userKey, ...likes, limit) as MemoryRow[];
}
}
searchSubconscious(userKey: string, query: string, limit: number = 10): MemoryRow[] {
const tokens = ftsSearchTerms(query);
if (tokens.length === 0) {
const stmt = this.db.prepare(`SELECT * FROM memories WHERE user_key = ? AND dismissed = 0 AND scope = 'subconscious' ORDER BY updated_at DESC LIMIT ?`);
return stmt.all(userKey, limit) as MemoryRow[];
}
// Quoted: punctuation in a term can't break FTS5 query syntax.
const ftsQuery = tokens.map((term) => `"${term}"`).join(' OR ');
const ftsStmt = this.db.prepare(`
SELECT m.* FROM memories m
JOIN memories_fts fts ON m.rowid = fts.rowid
WHERE memories_fts MATCH ? AND m.user_key = ? AND m.dismissed = 0 AND m.scope = 'subconscious'
ORDER BY rank
LIMIT ?
`);
try {
return ftsStmt.all(ftsQuery, userKey, limit) as MemoryRow[];
} catch {
const likeClauses = tokens.map(() => '(summary LIKE ? OR detail LIKE ?)').join(' OR ');
const stmt = this.db.prepare(`SELECT * FROM memories WHERE user_key = ? AND dismissed = 0 AND scope = 'subconscious' AND (${likeClauses}) ORDER BY updated_at DESC LIMIT ?`);
const likes = tokens.flatMap(t => [`%${t}%`, `%${t}%`]);
return stmt.all(userKey, ...likes, limit) as MemoryRow[];
}
}
searchFTS(userKey: string, query: string, limit: number = 10): MemoryRow[] {
return this.searchRelevant(userKey, query, limit);
}
softDelete(id: string): boolean {
const stmt = this.db.prepare('UPDATE memories SET dismissed = 1, updated_at = ? WHERE id = ?');
const result = stmt.run(Date.now(), id);
return result.changes > 0;
}
clearByType(userKey: string, type?: string): number {
if (type) {
const stmt = this.db.prepare('UPDATE memories SET dismissed = 1, updated_at = ? WHERE user_key = ? AND type = ? AND dismissed = 0');
const result = stmt.run(Date.now(), userKey, type);
return result.changes;
}
const stmt = this.db.prepare('UPDATE memories SET dismissed = 1, updated_at = ? WHERE user_key = ? AND dismissed = 0');
const result = stmt.run(Date.now(), userKey);
return result.changes;
}
hardDeleteDismissed(userKey: string): number {
const stmt = this.db.prepare('DELETE FROM memories WHERE user_key = ? AND dismissed = 1');
const result = stmt.run(userKey);
return result.changes;
}
promoteToDurable(userKey: string): number {
const stmt = this.db.prepare(`
UPDATE memories SET scope = 'durable', updated_at = ?
WHERE user_key = ? AND scope = 'active' AND dismissed = 0
AND evidence_count >= 3 AND evidence_kind IN ('direct', 'manual')
`);
const result = stmt.run(Date.now(), userKey);
return result.changes;
}
moveToSubconscious(userKey: string): number {
const SUBCONSCIOUS_THRESHOLD_DAYS = 30;
const threshold = SUBCONSCIOUS_THRESHOLD_DAYS * 24 * 60 * 60 * 1000;
const cutoff = Date.now() - threshold;
const stmt = this.db.prepare(`
UPDATE memories SET scope = 'subconscious', updated_at = ?
WHERE user_key = ? AND dismissed = 0
AND scope IN ('active', 'durable')
AND last_seen_at < ? AND last_seen_at > 0
`);
const result = stmt.run(Date.now(), userKey, cutoff);
return result.changes;
}
promoteToConscious(id: string, newScope: 'active' | 'durable'): boolean {
const now = Date.now();
const stmt = this.db.prepare(
`UPDATE memories SET scope = ?, updated_at = ?, last_seen_at = ? WHERE id = ? AND scope = 'subconscious'`
);
const result = stmt.run(newScope, now, now, id);
return result.changes > 0;
}
getSubconscious(userKey: string): MemoryRow[] {
const stmt = this.db.prepare(`SELECT * FROM memories WHERE user_key = ? AND dismissed = 0 AND scope = 'subconscious' ORDER BY updated_at DESC`);
return stmt.all(userKey) as MemoryRow[];
}
countSubconscious(userKey: string): number {
const stmt = this.db.prepare(`SELECT COUNT(*) as count FROM memories WHERE user_key = ? AND dismissed = 0 AND scope = 'subconscious'`);
const row = stmt.get(userKey) as { count: number };
return row.count;
}
getMemoriesByIds(ids: string[]): MemoryRow[] {
if (ids.length === 0) return [];
const placeholders = ids.map(() => '?').join(',');
const stmt = this.db.prepare(`SELECT * FROM memories WHERE id IN (${placeholders})`);
return stmt.all(...ids) as MemoryRow[];
}
upsertPerson(params: {
userKey: string;
name: string;
relationshipToUser?: string | null;
confidence?: number;
description?: string | null;
personId?: string;
/**
* Context for a model check. A check is queued only when an unreviewed
* name RECURS (its second sighting), never on first sight and never in
* rebuilds: one-off names cost no tokens.
*/
queueCheck?: { context: string } | null;
}): PersonRow | null {
const now = Date.now();
const normalized = this.normalizePersonName(params.name);
if (!normalized) return null;
// The owner's decisions win over every extraction, including rebuilds.
const override = this.db.prepare('SELECT action, target_person_id FROM person_overrides WHERE user_key = ? AND canonical_name = ? LIMIT 1')
.get(params.userKey, normalized) as { action: string; target_person_id: string | null } | undefined;
if (override?.action === 'not_person') return null;
const existingStmt = this.db.prepare('SELECT * FROM persons WHERE user_key = ? AND canonical_name = ? LIMIT 1');
let existing = existingStmt.get(params.userKey, normalized) as PersonRow | undefined;
if (!existing && override?.action === 'merge' && override.target_person_id) existing = this.getPersonById(override.target_person_id);
if (!existing) {
// A name the owner gave another person (rename, merge) resolves to that person.
existing = this.db.prepare(`
SELECT p.* FROM person_aliases a JOIN persons p ON p.id = a.person_id
WHERE p.user_key = ? AND a.normalized_alias = ? ORDER BY p.owner_edited DESC LIMIT 1
`).get(params.userKey, normalized) as PersonRow | undefined;
}
if (existing && existing.owner_edited === 1) {
this.db.prepare('UPDATE persons SET last_seen_at = ?, updated_at = ? WHERE id = ?').run(now, now, existing.id);
return this.getPersonById(existing.id) ?? existing;
}
if (existing) {
const confidence = Math.max(existing.confidence, params.confidence ?? existing.confidence);
// A plain mention ("known") never erases a real relationship ("wife").
const incoming = this.allowedRelationship(params.userKey, existing.id, params.relationshipToUser);
// A specific relationship already stored wins over a vaguer one ("wife" over "spouse").
const current = existing.relationship_to_user;
const currentSpecific = !!current && !['known', 'spouse', 'family'].includes(current);
const relationshipToUser = incoming && incoming !== 'known' && !currentSpecific ? incoming : current ?? incoming;
const description = params.description ?? existing.description;
this.db.prepare(`
UPDATE persons
SET display_name = ?, relationship_to_user = ?, description = ?, confidence = ?, last_seen_at = ?, updated_at = ?
WHERE id = ?
`).run(params.name.trim(), relationshipToUser, description, confidence, now, now, existing.id);
const updated = this.getPersonById(existing.id);
if (!updated) throw new Error('Failed to update person record');
if (params.queueCheck && !updated.verified_by && (updated.kind ?? 'unverified') === 'unverified') {
this.db.prepare('INSERT OR IGNORE INTO person_checks (person_id, user_key, name, context, queued_at) VALUES (?, ?, ?, ?, ?)')
.run(updated.id, params.userKey, updated.display_name, params.queueCheck.context.slice(0, 400), now);
}
return updated;
}
const id = params.personId || `per_${Date.now().toString(36)}${Math.random().toString(36).slice(2, 8)}`;
const confidence = params.confidence ?? 0.7;
params = { ...params, relationshipToUser: this.allowedRelationship(params.userKey, id, params.relationshipToUser) ?? (params.relationshipToUser ? 'known' : null) };
this.db.prepare(`
INSERT INTO persons (
id, user_key, canonical_name, display_name, relationship_to_user,
description, confidence, first_seen_at, last_seen_at, created_at, updated_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`).run(
id,
params.userKey,
normalized,
params.name.trim(),
params.relationshipToUser ?? null,
params.description ?? null,
confidence,
now,
now,
now,
now,
);
const created = this.getPersonById(id);
if (!created) throw new Error('Failed to create person record');
return created;
}
/**
* Cardinality: a one-to-one relation (wife, mother, manager…) belongs to one
* person at a time. An extracted claim never takes it from someone who
* already holds it; only the owner can move it (see moveExclusiveRelationship).
* Returns the relationship to store, or null to keep what is there.
*/
private allowedRelationship(userKey: string, personId: string, relationship: string | null | undefined): string | null {
const rel = normalizeRelationship(relationship);
if (!rel) return null;
const group = exclusiveGroup(rel);
if (!group) return rel;
const holder = this.exclusiveHolder(userKey, group, personId);
return holder ? null : rel;
}
/** Who holds a one-to-one relation group (spouse, mother…), other than `exceptId`. */
exclusiveHolder(userKey: string, group: string, exceptId?: string): PersonRow | undefined {
const rows = this.db.prepare(`SELECT * FROM persons WHERE user_key = ? AND relationship_to_user IS NOT NULL AND relationship_to_user != 'known' AND id != ?`)
.all(userKey, exceptId ?? '') as PersonRow[];
return rows.find((r) => exclusiveGroup(r.relationship_to_user) === group);
}
/** Owner moves a one-to-one relation: the previous holder becomes "known". */
releaseExclusiveRelationship(userKey: string, group: string, exceptId: string): PersonRow | undefined {
const holder = this.exclusiveHolder(userKey, group, exceptId);
if (holder) this.db.prepare(`UPDATE persons SET relationship_to_user = 'known', updated_at = ? WHERE id = ?`).run(Date.now(), holder.id);
return holder;
}
/** Find a person by name or alias without creating one. */
resolvePersonId(userKey: string, name: string): string | null {
const normalized = this.normalizePersonName(name);
if (!normalized) return null;
const direct = this.db.prepare('SELECT id FROM persons WHERE user_key = ? AND canonical_name = ? LIMIT 1').get(userKey, normalized) as { id: string } | undefined;
if (direct) return direct.id;
const alias = this.db.prepare('SELECT p.id FROM person_aliases a JOIN persons p ON p.id = a.person_id WHERE p.user_key = ? AND a.normalized_alias = ? LIMIT 1').get(userKey, normalized) as { id: string } | undefined;
return alias?.id ?? null;
}
/**
* The evidence behind each person, for standing (person-standing.ts):
* mentions in live beliefs and the archive, how many are social, the days
* they span, and links to other people. Grouped queries, one pass.
*/
personEvidence(userKey: string): Array<{ id: string; relationship: string | null; kind: string | null; verified_by: string | null; owner_edited: number; mentions: number; social: number; days: number; plinks: number }> {
return this.db.prepare(`
WITH links AS (
SELECT bp.person_id AS pid, b.kind AS kind, b.created_at AS ts FROM belief_persons bp
JOIN beliefs b ON b.id = bp.belief_id AND b.status IN ('candidate','held','confirmed','contested')
UNION ALL
SELECT mp.person_id, m.type, m.created_at FROM memory_persons mp
JOIN memories m ON m.id = mp.memory_id AND m.dismissed = 0
),
agg AS (
SELECT pid, COUNT(*) AS mentions, SUM(CASE WHEN kind IN ('relationship','episode') THEN 1 ELSE 0 END) AS social,
COUNT(DISTINCT date(ts / 1000, 'unixepoch', 'localtime')) AS days
FROM links GROUP BY pid
),
rel AS (
SELECT pid, COUNT(*) AS n FROM (
SELECT source_person_id AS pid FROM person_relationships WHERE user_key = ?
UNION ALL SELECT target_person_id FROM person_relationships WHERE user_key = ?
) GROUP BY pid
)
SELECT p.id, p.relationship_to_user AS relationship, p.kind, p.verified_by, p.owner_edited,
COALESCE(agg.mentions, 0) AS mentions, COALESCE(agg.social, 0) AS social, COALESCE(agg.days, 0) AS days, COALESCE(rel.n, 0) AS plinks
FROM persons p LEFT JOIN agg ON agg.pid = p.id LEFT JOIN rel ON rel.pid = p.id
WHERE p.user_key = ?
`).all(userKey, userKey, userKey) as Array<{ id: string; relationship: string | null; kind: string | null; verified_by: string | null; owner_edited: number; mentions: number; social: number; days: number; plinks: number }>;
}
// ── People in beliefs (the live brain since the Owner Model) ─────────────
linkBeliefPerson(beliefId: string, personId: string, role: string, confidence: number): void {
this.db.prepare(`INSERT INTO belief_persons (belief_id, person_id, role, confidence, created_at) VALUES (?, ?, ?, ?, ?)
ON CONFLICT(belief_id, person_id) DO UPDATE SET role = excluded.role, confidence = excluded.confidence`).run(beliefId, personId, role, confidence, Date.now());
}
clearBeliefPersons(beliefId: string): void {
this.db.prepare('DELETE FROM belief_persons WHERE belief_id = ?').run(beliefId);
}
clearAllBeliefPersons(userKey: string): void {
this.db.prepare('DELETE FROM belief_persons WHERE person_id IN (SELECT id FROM persons WHERE user_key = ?)').run(userKey);
}
/** Live beliefs created after `since` (ms), oldest first, for incremental person indexing. */
/** Beliefs after a (created_at, rowid) cursor, in the order they were made: two made in the same millisecond are both seen. */
beliefsSince(userKey: string, since: number, limit = 500, sinceSeq = -1): Array<{ id: string; statement: string; detail: string | null; kind: string; confidence: number; created_at: number; seq: number }> {
return this.db.prepare(`SELECT id, statement, detail, kind, MIN(1, 0.5 + support / 10.0) AS confidence, created_at, rowid AS seq FROM beliefs
WHERE owner_key = ? AND (created_at > ? OR (created_at = ? AND rowid > ?)) AND status IN ('candidate', 'held', 'confirmed', 'contested') ORDER BY created_at ASC, rowid ASC LIMIT ?`)
.all(userKey, since, since, sinceSeq, limit) as Array<{ id: string; statement: string; detail: string | null; kind: string; confidence: number; created_at: number; seq: number }>;
}
// ── Owner curation of people ─────────────────────────────────────────────
updatePerson(userKey: string, id: string, fields: { displayName?: string; relationship?: string | null; description?: string | null; kind?: PersonKind; meta?: Record<string, unknown> }): PersonRow | null {
const row = this.getPersonById(id);
if (!row || row.user_key !== userKey) return null;
const now = Date.now();
const meta = fields.meta !== undefined ? JSON.stringify(fields.meta) : row.meta ?? '{}';
const kind = fields.kind ?? row.kind ?? 'unverified';
const displayName = fields.displayName?.trim() || row.display_name;
this.db.prepare(`
UPDATE persons SET display_name = ?, relationship_to_user = ?, description = ?, kind = ?, verified_by = ?, meta = ?, owner_edited = 1, updated_at = ?
WHERE id = ?
`).run(
displayName,
fields.relationship !== undefined ? normalizeRelationship(fields.relationship) : row.relationship_to_user,
fields.description !== undefined ? fields.description : row.description,
kind,
fields.kind !== undefined ? 'owner' : row.verified_by ?? null,
meta,
now,
id,
);
// The id never changes; a new name becomes an alias, so future mentions link here.
if (fields.displayName && fields.displayName.trim() !== row.display_name) this.addPersonAlias(id, fields.displayName.trim(), 'owner');
this.db.prepare('DELETE FROM person_checks WHERE person_id = ?').run(id);
return this.getPersonById(id) ?? null;
}
/** "This isn't a person": forget the entity, and never create it again. */
markNotPerson(userKey: string, id: string): boolean {
const row = this.getPersonById(id);
if (!row || row.user_key !== userKey) return false;
const names = [row.canonical_name, ...(this.db.prepare('SELECT normalized_alias FROM person_aliases WHERE person_id = ?').all(id) as Array<{ normalized_alias: string }>).map((a) => a.normalized_alias)];
const now = Date.now();
const put = this.db.prepare(`INSERT INTO person_overrides (user_key, canonical_name, action, target_person_id, created_at) VALUES (?, ?, 'not_person', NULL, ?)
ON CONFLICT(user_key, canonical_name) DO UPDATE SET action = 'not_person', target_person_id = NULL, created_at = excluded.created_at`);
for (const n of new Set(names)) put.run(userKey, n, now);
this.db.prepare('DELETE FROM persons WHERE id = ?').run(id);
return true;
}
/** Merge `fromId` into `intoId`: links, relationships and names move; `intoId` keeps its id. */
mergePersons(userKey: string, fromId: string, intoId: string, opts: { byOwner?: boolean } = {}): boolean {
const byOwner = opts.byOwner ?? true;
if (fromId === intoId) return false;
const from = this.getPersonById(fromId);
const into = this.getPersonById(intoId);
if (!from || !into || from.user_key !== userKey || into.user_key !== userKey) return false;
const now = Date.now();
// The driver runs the function inside BEGIN/COMMIT right away.
this.db.transaction(() => {
this.db.prepare(`INSERT INTO memory_persons (memory_id, person_id, role, confidence, created_at, updated_at)
SELECT memory_id, ?, role, confidence, created_at, ? FROM memory_persons WHERE person_id = ?
ON CONFLICT(memory_id, person_id) DO NOTHING`).run(intoId, now, fromId);
this.db.prepare(`INSERT INTO belief_persons (belief_id, person_id, role, confidence, created_at)
SELECT belief_id, ?, role, confidence, created_at FROM belief_persons WHERE person_id = ?
ON CONFLICT(belief_id, person_id) DO NOTHING`).run(intoId, fromId);
this.db.prepare('UPDATE OR IGNORE person_relationships SET source_person_id = ? WHERE source_person_id = ?').run(intoId, fromId);
this.db.prepare('UPDATE OR IGNORE person_relationships SET target_person_id = ? WHERE target_person_id = ?').run(intoId, fromId);
this.db.prepare('DELETE FROM person_relationships WHERE source_person_id = target_person_id').run();
const aliases = [from.display_name, ...(this.db.prepare('SELECT alias FROM person_aliases WHERE person_id = ?').all(fromId) as Array<{ alias: string }>).map((a) => a.alias)];
for (const a of aliases) this.addPersonAlias(intoId, a, byOwner ? 'owner' : 'memory');
const put = this.db.prepare(`INSERT INTO person_overrides (user_key, canonical_name, action, target_person_id, created_at) VALUES (?, ?, 'merge', ?, ?)
ON CONFLICT(user_key, canonical_name) DO UPDATE SET action = 'merge', target_person_id = excluded.target_person_id, created_at = excluded.created_at`);
put.run(userKey, from.canonical_name, intoId, now);
this.db.prepare('DELETE FROM persons WHERE id = ?').run(fromId);
if (byOwner) this.db.prepare('UPDATE persons SET owner_edited = 1, updated_at = ? WHERE id = ?').run(now, intoId);
});
return true;
}
/** Every person's names: the canonical one and each alias, normalised. */
personNames(userKey: string): Map<string, string[]> {
const out = new Map<string, string[]>();
const rows = this.db.prepare(`
SELECT p.id AS id, p.canonical_name AS name FROM persons p WHERE p.user_key = ?
UNION SELECT a.person_id, a.normalized_alias FROM person_aliases a JOIN persons p ON p.id = a.person_id WHERE p.user_key = ?
`).all(userKey, userKey) as Array<{ id: string; name: string }>;
for (const r of rows) {
const list = out.get(r.id) ?? [];
if (r.name && !list.includes(r.name)) list.push(r.name);
out.set(r.id, list);
}
return out;
}
/**
* A consistent copy of the whole database (recent writes in the WAL
* included) at `path`. Never overwrites an existing file. False if it
* could not be made.
*/
backupTo(path: string): boolean {
if (existsSync(path)) return true;
try {
this.db.exec(`VACUUM INTO '${path.replace(/'/g, "''")}'`);
return true;
} catch (err) {
logger.warn({ err, path }, 'Second Brain: backup failed');
return false;
}
}
// ── The model check queue (new names only) ────────────────────────────────
pendingPersonChecks(userKey: string, limit: number): Array<{ person_id: string; name: string; context: string | null }> {
return this.db.prepare('SELECT person_id, name, context FROM person_checks WHERE user_key = ? ORDER BY queued_at ASC LIMIT ?')
.all(userKey, Math.max(1, Math.min(limit, 50))) as Array<{ person_id: string; name: string; context: string | null }>;
}
/** Owner opt-in: queue existing unreviewed people (not owner-edited, not already decided). */
queueExistingPersonChecks(userKey: string): number {
const rows = this.db.prepare(`
SELECT p.id, p.display_name,
COALESCE(
(SELECT b.statement FROM belief_persons bp JOIN beliefs b ON b.id = bp.belief_id WHERE bp.person_id = p.id ORDER BY b.updated_at DESC LIMIT 1),
(SELECT m.summary FROM memory_persons mp JOIN memories m ON m.id = mp.memory_id WHERE mp.person_id = p.id ORDER BY m.updated_at DESC LIMIT 1),
''
) AS context
FROM persons p
WHERE p.user_key = ? AND p.owner_edited = 0 AND COALESCE(p.verified_by, '') = '' AND p.kind = 'unverified'
`).all(userKey) as Array<{ id: string; display_name: string; context: string }>;
const put = this.db.prepare('INSERT OR IGNORE INTO person_checks (person_id, user_key, name, context, queued_at) VALUES (?, ?, ?, ?, ?)');
const now = Date.now();
for (const r of rows) put.run(r.id, userKey, r.display_name, (r.context ?? '').slice(0, 400), now);
return rows.length;
}
countPersonChecks(userKey: string): number {
return (this.db.prepare('SELECT COUNT(*) AS n FROM person_checks WHERE user_key = ?').get(userKey) as { n: number }).n;
}
applyPersonCheck(userKey: string, personId: string, verdict: 'person' | 'pet' | 'not_person' | 'unsure'): void {
this.db.prepare('DELETE FROM person_checks WHERE person_id = ?').run(personId);
const row = this.getPersonById(personId);
if (!row || row.user_key !== userKey || row.owner_edited === 1) return;
if (verdict === 'not_person') {
// A model's "no" hides it; only the owner's "no" is permanent (markNotPerson).
this.db.prepare("UPDATE persons SET kind = 'unverified', verified_by = 'model-rejected', updated_at = ? WHERE id = ?").run(Date.now(), personId);
return;
}
if (verdict === 'unsure') return;
this.db.prepare('UPDATE persons SET kind = ?, verified_by = ?, updated_at = ? WHERE id = ?').run(verdict, 'model', Date.now(), personId);
}
// ── The brain graph: real links only ─────────────────────────────────────
personGraph(userKey: string, maxMemories: number): PersonGraph {
// Nodes: the live brain (beliefs) and the people in it; edges: who appears
// in which belief, and person-to-person relationships. Legacy archive
// links still count toward a person's weight.
const persons = (this.db.prepare(`
SELECT p.id, p.display_name AS name, p.kind, p.relationship_to_user AS relationship,
(SELECT COUNT(*) FROM belief_persons bp JOIN beliefs b ON b.id = bp.belief_id AND b.status IN ('candidate','held','confirmed','contested') WHERE bp.person_id = p.id)
+ (SELECT COUNT(*) FROM memory_persons mp JOIN memories m ON m.id = mp.memory_id AND m.dismissed = 0 WHERE mp.person_id = p.id) AS memoryCount
FROM persons p
WHERE p.user_key = ? AND COALESCE(p.verified_by, '') != 'model-rejected'
ORDER BY memoryCount DESC
LIMIT 400
`).all(userKey) as PersonGraph['persons']).filter((p) => p.memoryCount > 0 || p.relationship);
const memories = (this.db.prepare(`
SELECT id, statement AS summary, kind AS type, MIN(1, 0.3 + support / 8.0) AS importance, updated_at AS updatedAt FROM beliefs
WHERE owner_key = ? AND status IN ('candidate', 'held', 'confirmed', 'contested')
ORDER BY (status = 'confirmed') DESC, (status = 'held') DESC, updated_at DESC
LIMIT ?
`).all(userKey, Math.max(10, Math.min(maxMemories, 2000))) as PersonGraph['memories'])
.map((m) => ({ ...m, summary: m.summary.length > 140 ? `${m.summary.slice(0, 137)}…` : m.summary }));
const personIds = new Set(persons.map((p) => p.id));
const memoryIds = new Set(memories.map((m) => m.id));
const memoryPersons = (this.db.prepare(`
SELECT bp.belief_id AS memoryId, bp.person_id AS personId, bp.role FROM belief_persons bp
JOIN persons p ON p.id = bp.person_id WHERE p.user_key = ?
`).all(userKey) as PersonGraph['memoryPersons']).filter((l) => personIds.has(l.personId) && memoryIds.has(l.memoryId));
const relationships = (this.db.prepare(`
SELECT source_person_id AS source, target_person_id AS target, relation_type AS type, strength FROM person_relationships WHERE user_key = ?
`).all(userKey) as PersonGraph['relationships']).filter((r) => personIds.has(r.source) && personIds.has(r.target));
return { persons, memories, memoryPersons, relationships };
}
getPersonById(id: string): PersonRow | undefined {
const stmt = this.db.prepare('SELECT * FROM persons WHERE id = ? LIMIT 1');
return stmt.get(id) as PersonRow | undefined;
}
addPersonAlias(personId: string, alias: string, source: string = 'memory'): void {
const normalizedAlias = this.normalizePersonName(alias);
if (!normalizedAlias) return;
const id = `pal_${Date.now().toString(36)}${Math.random().toString(36).slice(2, 8)}`;
const stmt = this.db.prepare(`
INSERT OR IGNORE INTO person_aliases (id, person_id, alias, normalized_alias, source, created_at)
VALUES (?, ?, ?, ?, ?, ?)
`);
stmt.run(id, personId, alias.trim(), normalizedAlias, source, Date.now());
}
linkMemoryPerson(memoryId: string, personId: string, role: string = 'mentioned', confidence: number = 0.7): void {
const now = Date.now();
const stmt = this.db.prepare(`
INSERT INTO memory_persons (memory_id, person_id, role, confidence, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT(memory_id, person_id)
DO UPDATE SET role = excluded.role, confidence = excluded.confidence, updated_at = excluded.updated_at
`);
stmt.run(memoryId, personId, role, confidence, now, now);
}
clearMemoryPersons(memoryId: string): void {
const stmt = this.db.prepare('DELETE FROM memory_persons WHERE memory_id = ?');
stmt.run(memoryId);
}
clearRelationshipsByMemory(memoryId: string): void {
const stmt = this.db.prepare('DELETE FROM person_relationships WHERE evidence_memory_id = ?');
stmt.run(memoryId);
}
clearUserPersonGraph(userKey: string): void {
const deleteRelationships = this.db.prepare('DELETE FROM person_relationships WHERE user_key = ?');
deleteRelationships.run(userKey);
const deleteMemoryPersons = this.db.prepare(`
DELETE FROM memory_persons
WHERE person_id IN (SELECT id FROM persons WHERE user_key = ?)
`);
deleteMemoryPersons.run(userKey);
}
deleteOrphanPersons(userKey: string): void {
const stmt = this.db.prepare(`
DELETE FROM persons
WHERE user_key = ?
AND owner_edited = 0
AND id NOT IN (SELECT DISTINCT person_id FROM memory_persons)
AND id NOT IN (SELECT DISTINCT person_id FROM belief_persons)
`);
stmt.run(userKey);
}
upsertPersonRelationship(params: {
userKey: string;
sourcePersonId: string;
targetPersonId: string;
relationType: string;
strength?: number;
evidenceMemoryId?: string | null;
}): void {
if (params.sourcePersonId === params.targetPersonId) return;
const now = Date.now();
const strength = params.strength ?? 0.6;
const relationType = params.relationType || 'related';
const existingStmt = this.db.prepare(`
SELECT * FROM person_relationships
WHERE user_key = ? AND source_person_id = ? AND target_person_id = ? AND relation_type = ?
LIMIT 1
`);
const existing = existingStmt.get(
params.userKey,
params.sourcePersonId,
params.targetPersonId,
relationType,
) as { id: string; strength: number } | undefined;
if (existing) {
const stmt = this.db.prepare(`
UPDATE person_relationships
SET strength = ?, evidence_memory_id = ?, updated_at = ?
WHERE id = ?
`);
stmt.run(Math.max(existing.strength, strength), params.evidenceMemoryId ?? null, now, existing.id);
return;
}
const id = `rel_${Date.now().toString(36)}${Math.random().toString(36).slice(2, 8)}`;
const stmt = this.db.prepare(`
INSERT INTO person_relationships (
id, user_key, source_person_id, target_person_id, relation_type,
strength, evidence_memory_id, created_at, updated_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
`);
stmt.run(
id,
params.userKey,
params.sourcePersonId,
params.targetPersonId,
relationType,
strength,
params.evidenceMemoryId ?? null,
now,
now,
);
}
listPersons(userKey: string, query?: string, limit: number = 100): PersonListRow[] {
const cappedLimit = Math.min(Math.max(limit, 1), 5000);
// Counts both the legacy archive and live beliefs; owner-edited people
// stay listed even with nothing linked yet.
const q = query && query.trim() ? `%${query.trim().toLowerCase()}%` : null;
const stmt = this.db.prepare(`
SELECT * FROM (
SELECT p.*, (SELECT COUNT(*) FROM memory_persons mp JOIN memories m ON m.id = mp.memory_id AND m.dismissed = 0 WHERE mp.person_id = p.id)
+ (SELECT COUNT(*) FROM belief_persons bp JOIN beliefs b ON b.id = bp.belief_id AND b.status IN ('candidate','held','confirmed','contested') WHERE bp.person_id = p.id) AS memory_count
FROM persons p
WHERE p.user_key = ?
${q ? 'AND (LOWER(p.display_name) LIKE ? OR LOWER(p.canonical_name) LIKE ? OR p.id IN (SELECT person_id FROM person_aliases WHERE normalized_alias LIKE ?))' : ''}
)
WHERE memory_count > 0 OR owner_edited = 1
ORDER BY memory_count DESC, updated_at DESC
LIMIT ?
`);
return (q ? stmt.all(userKey, q, q, q, cappedLimit) : stmt.all(userKey, cappedLimit)) as PersonListRow[];
}
getPersonWithCount(userKey: string, personId: string): PersonListRow | undefined {
const stmt = this.db.prepare(`
SELECT p.*, (SELECT COUNT(*) FROM memory_persons mp JOIN memories m ON m.id = mp.memory_id AND m.dismissed = 0 WHERE mp.person_id = p.id)
+ (SELECT COUNT(*) FROM belief_persons bp JOIN beliefs b ON b.id = bp.belief_id AND b.status IN ('candidate','held','confirmed','contested') WHERE bp.person_id = p.id) AS memory_count
FROM persons p
WHERE p.user_key = ? AND p.id = ?
LIMIT 1
`);
return stmt.get(userKey, personId) as PersonListRow | undefined;
}
/** Live beliefs that mention a person, newest first. */
getBeliefIdsForPerson(personId: string, limit: number = 50, offset = 0): string[] {
return (this.db.prepare(`SELECT bp.belief_id AS id FROM belief_persons bp JOIN beliefs b ON b.id = bp.belief_id
WHERE bp.person_id = ? AND b.status IN ('candidate','held','confirmed','contested') ORDER BY b.updated_at DESC LIMIT ? OFFSET ?`)
.all(personId, Math.max(1, limit), Math.max(0, offset)) as Array<{ id: string }>).map((r) => r.id);
}
/** How many live beliefs and legacy memories mention a person (for paging). */
countPersonMemories(userKey: string, personId: string): { beliefs: number; memories: number } {
const beliefs = (this.db.prepare(`SELECT COUNT(*) AS n FROM belief_persons bp JOIN beliefs b ON b.id = bp.belief_id
WHERE bp.person_id = ? AND b.status IN ('candidate','held','confirmed','contested')`).get(personId) as { n: number }).n;
const memories = (this.db.prepare(`SELECT COUNT(*) AS n FROM memory_persons mp JOIN memories m ON m.id = mp.memory_id
WHERE mp.person_id = ? AND m.user_key = ? AND m.dismissed = 0`).get(personId, userKey) as { n: number }).n;
return { beliefs, memories };
}
getPersonConnections(userKey: string, personId: string, limit: number = 30): PersonConnectionRow[] {
const cappedLimit = Math.min(Math.max(limit, 1), 100);
const stmt = this.db.prepare(`
SELECT
p.id,
p.display_name,
pr.relation_type,
pr.strength
FROM person_relationships pr
JOIN persons p ON p.id = pr.target_person_id
WHERE pr.user_key = ? AND pr.source_person_id = ?
ORDER BY pr.strength DESC, p.display_name ASC
LIMIT ?
`);
return stmt.all(userKey, personId, cappedLimit) as PersonConnectionRow[];
}
getMemoriesForPerson(userKey: string, personId: string, limit: number = 50, offset = 0): MemoryRow[] {
const stmt = this.db.prepare(`
SELECT m.*
FROM memory_persons mp
JOIN memories m ON m.id = mp.memory_id
WHERE mp.person_id = ? AND m.user_key = ? AND m.dismissed = 0
ORDER BY m.updated_at DESC
LIMIT ? OFFSET ?
`);
return stmt.all(personId, userKey, Math.max(1, limit), Math.max(0, offset)) as MemoryRow[];
}
getRelationshipMemoryRows(userKey: string): MemoryRow[] {
const stmt = this.db.prepare(`
SELECT * FROM memories
WHERE user_key = ? AND dismissed = 0 AND type IN ('relationship', 'episode', 'identity', 'project')
ORDER BY updated_at DESC
`);
return stmt.all(userKey) as MemoryRow[];
}
private normalizePersonName(input: string): string {
return input
.toLowerCase()
.replace(/[^a-z0-9@\s'-]+/g, ' ')
.replace(/\s+/g, ' ')
.trim();
}
/** The legacy archive as map notes (live rows only). */
archiveNotes(userKey: string, limit = 50000): Array<{ id: string; text: string; kind: string; at: number; confidence: number }> {
return this.db.prepare(`SELECT id, summary AS text, type AS kind, updated_at AS at, confidence FROM memories
WHERE user_key = ? AND dismissed = 0 AND superseded_by IS NULL ORDER BY updated_at DESC LIMIT ?`).all(userKey, limit) as Array<{ id: string; text: string; kind: string; at: number; confidence: number }>;
}
/** Changes when any live belief or archive row changes: count + newest update. */
knowledgeSignature(userKey: string, ownerKey: string, archive: boolean): string {
const b = this.db.prepare(`SELECT COUNT(*) AS n, COALESCE(MAX(updated_at), 0) AS t FROM beliefs WHERE owner_key = ? AND status IN ('candidate','held','confirmed','contested')`).get(ownerKey) as { n: number; t: number };
if (!archive) return `${b.n}:${b.t}`;
const m = this.db.prepare(`SELECT COUNT(*) AS n, COALESCE(MAX(updated_at), 0) AS t FROM memories WHERE user_key = ? AND dismissed = 0 AND superseded_by IS NULL`).get(userKey) as { n: number; t: number };
return `${b.n}:${b.t}|${m.n}:${m.t}`;
}
setMeta(key: string, value: string): void {
const stmt = this.db.prepare('INSERT OR REPLACE INTO second_brain_meta (key, value) VALUES (@key, @value)');
stmt.run({ key, value });
}
getMeta(key: string): string | null {
const stmt = this.db.prepare('SELECT value FROM second_brain_meta WHERE key = ?');
const row = stmt.get(key) as { value: string } | undefined;
return row?.value ?? null;
}
deleteMeta(key: string): void {
const stmt = this.db.prepare('DELETE FROM second_brain_meta WHERE key = ?');
stmt.run(key);
}
countByType(userKey: string): Record<string, number> {
const stmt = this.db.prepare(`SELECT type, COUNT(*) as count FROM memories WHERE user_key = ? AND dismissed = 0 AND scope IN ('active', 'durable') GROUP BY type`);
const rows = stmt.all(userKey) as Array<{ type: string; count: number }>;
const result: Record<string, number> = {};
for (const row of rows) {
result[row.type] = row.count;
}
return result;
}
totalActive(userKey: string): number {
const stmt = this.db.prepare(`SELECT COUNT(*) as count FROM memories WHERE user_key = ? AND dismissed = 0 AND scope IN ('active', 'durable')`);
const row = stmt.get(userKey) as { count: number };
return row.count;
}
/** Rows the belief migration set aside as junk (ADR-026 §3.7): hidden from prompts, shown on the dashboard for review. */
listQuarantined(userKey: string, limit: number = 500): MemoryRow[] {
const stmt = this.db.prepare(`SELECT * FROM memories WHERE user_key = ? AND scope = 'quarantine' AND dismissed = 0 ORDER BY updated_at DESC LIMIT ?`);
return stmt.all(userKey, limit) as MemoryRow[];
}
/** The owner reviewed a quarantined row and wants it back: it becomes a durable memory again. */
restoreQuarantined(id: string): boolean {
const now = Date.now();
const stmt = this.db.prepare(`UPDATE memories SET scope = 'durable', dismissed = 0, provenance = 'restored', updated_at = ?, last_seen_at = ? WHERE id = ? AND scope = 'quarantine'`);
return stmt.run(now, now, id).changes > 0;
}
close(): void {
this.db.close();
}
}