Garry's Opinionated OpenClaw/Hermes Agent Brain
TypeScript
/**
* #2038 — idx_timeline_dedup schema-drift self-heal.
*
* Migration v102 (`timeline_entries_source_in_dedup`) widens the dedup index
* from (page_id, date, summary) to (page_id, date, summary, source). It was
* renumbered from v99 during a master merge, so a brain that ran the OLD v99
* variant has its version counter stamped PAST v102 while the index stayed
* 3-column. `runMigrations` then can't see the drift (it early-returns when no
* version is pending), and every `addTimelineEntry(esBatch)` fails with
* "no unique or exclusion constraint matching the ON CONFLICT specification"
* because both insert sites infer on the 4-column tuple — timeline writes
* silently break brain-wide.
*
* The version counter can't detect this, so the repair is keyed off the actual
* index SHAPE and runs on every migrate pass (including the no-pending path).
* Idempotent: a no-op when the index is already 4-column.
*/
import type { BrainEngine } from './engine.ts';
import { parseTimelineEntries, findTimelineSourceDelimiter } from './link-extraction.ts';
const INDEX_NAME = 'idx_timeline_dedup';
// #3737: the dedup tuple keys md5(summary) — the raw summary overflowed the
// btree v4 row cap (~2704 bytes) on long/incompressible summaries and aborted
// every timeline insert for that page. Both engines' insert sites infer
// ON CONFLICT (page_id, date, md5(summary), source) against this shape, so
// the self-heal MUST expect (and rebuild to) the md5 form — an
// EXPECTED_COLUMNS of the raw shape would make this repair revert migration
// v138 on every migrate pass.
const EXPECTED_COLUMNS = ['page_id', 'date', 'md5(summary)', 'source'];
export interface TimelineDedupStatus {
/** The timeline_entries table exists (nothing to repair if not). */
tablePresent: boolean;
/** The index exists. */
indexPresent: boolean;
/** Indexed columns in order (empty when the index is absent). */
columns: string[];
/** Index exists in the wrong (pre-v102) shape — needs a rebuild. */
needsRepair: boolean;
}
/**
* Parse the column list out of a pg_indexes `indexdef` string.
*
* #3737: the column list is the span from the FIRST `(` (the list opener
* after `USING btree`) to the LAST `)`. The previous `lastIndexOf('(')`
* pointed INSIDE an expression column — `... (page_id, date, md5(summary),
* source)` parsed as `['summary']` — so the shape check misread a healthy
* md5-keyed index as drifted and rebuilt it on every migrate pass. The
* split is paren-depth-aware so `md5(summary)` (or any future expression
* with internal commas) stays one entry.
*/
export function parseIndexColumns(indexdef: string): string[] {
const open = indexdef.indexOf('(');
const close = indexdef.lastIndexOf(')');
if (open < 0 || close < 0 || close < open) return [];
const list = indexdef.slice(open + 1, close);
const cols: string[] = [];
let depth = 0;
let cur = '';
for (const ch of list) {
if (ch === '(') { depth++; cur += ch; }
else if (ch === ')') { depth--; cur += ch; }
else if (ch === ',' && depth === 0) { cols.push(cur); cur = ''; }
else cur += ch;
}
cols.push(cur);
return cols
.map(c => {
const t = c.trim();
// Drop any "col DESC"/opclass suffix — but only for plain columns;
// an expression entry (contains '(') is kept whole.
return t.includes('(') ? t : t.split(/\s+/)[0];
})
.filter(Boolean);
}
export async function checkTimelineDedupIndex(engine: BrainEngine): Promise<TimelineDedupStatus> {
const tbl = await engine.executeRaw<{ reg: string | null }>(
`SELECT to_regclass('timeline_entries')::text AS reg`,
);
const tablePresent = !!tbl[0]?.reg;
if (!tablePresent) {
return { tablePresent: false, indexPresent: false, columns: [], needsRepair: false };
}
const rows = await engine.executeRaw<{ indexdef: string }>(
`SELECT indexdef FROM pg_indexes WHERE indexname = $1`,
[INDEX_NAME],
);
const indexPresent = rows.length > 0;
const columns = indexPresent ? parseIndexColumns(rows[0].indexdef) : [];
const correct =
columns.length === EXPECTED_COLUMNS.length &&
EXPECTED_COLUMNS.every((c, i) => columns[i] === c);
// An ABSENT index is also "needs repair" — the migration that creates it was
// skipped. (A fresh brain always has it, created by the migration chain.)
return { tablePresent, indexPresent, columns, needsRepair: !correct };
}
export interface TimelineDedupRepairResult {
repaired: boolean;
before: string[];
collapsedDuplicates: number;
reason: 'already_correct' | 'no_table' | 'rebuilt';
}
/**
* Heal the index if it's missing the canonical shape (v138: (page_id, date,
* md5(summary), source)). Dedupes FIRST — the loose 3-column index let rows
* differing only by `source` coexist, and `CREATE UNIQUE INDEX` would throw
* on those collisions otherwise. Keeps the earliest row (min id) of each
* 4-tuple group (raw-summary grouping is equivalent to md5 grouping —
* md5-equal ⟺ summary-equal modulo negligible collisions).
*/
export async function repairTimelineDedupIndex(engine: BrainEngine): Promise<TimelineDedupRepairResult> {
const status = await checkTimelineDedupIndex(engine);
if (!status.tablePresent) {
return { repaired: false, before: [], collapsedDuplicates: 0, reason: 'no_table' };
}
if (!status.needsRepair) {
return { repaired: false, before: status.columns, collapsedDuplicates: 0, reason: 'already_correct' };
}
// Keep the lowest `id` per 4-tuple group — deterministic and consistent with
// the existing v-migration dedup rule (`a.id > b.id`), unlike `ctid` which is
// a physical tuple location that can preserve an arbitrary duplicate.
const del = await engine.executeRaw<{ n: string }>(
`WITH d AS (
DELETE FROM timeline_entries t
USING (
SELECT page_id, date, summary, source, MIN(id) AS keep
FROM timeline_entries
GROUP BY page_id, date, summary, source
HAVING COUNT(*) > 1
) dup
WHERE t.page_id = dup.page_id
AND t.date = dup.date
AND t.summary = dup.summary
AND t.source IS NOT DISTINCT FROM dup.source
AND t.id <> dup.keep
RETURNING 1
)
SELECT COUNT(*)::text AS n FROM d`,
);
const collapsedDuplicates = parseInt(del[0]?.n ?? '0', 10);
await engine.executeRaw(`DROP INDEX IF EXISTS ${INDEX_NAME}`);
// #3737: rebuild to the md5-keyed shape (matches migration v138 + both
// engines' ON CONFLICT inference) — rebuilding the raw-summary shape here
// would revert the btree-overflow fix on the next migrate pass.
await engine.executeRaw(
`CREATE UNIQUE INDEX IF NOT EXISTS ${INDEX_NAME}
ON timeline_entries(page_id, date, md5(summary), source)`,
);
return { repaired: true, before: status.columns, collapsedDuplicates, reason: 'rebuilt' };
}
// ─── #3957 — legacy-row (source='') shape repair ─────────────────────────
export interface LegacyTimelineRepairResult {
/** Pages that carried at least one legacy (source='') row. */
pagesScanned: number;
/** Legacy rows rewritten in place to the split (source, summary) shape. */
rowsRewritten: number;
/** Legacy rows deleted because a new-shape duplicate already existed. */
rowsDeleted: number;
/** Legacy rows left untouched (no matching bullet in current content). */
rowsSkipped: number;
}
/**
* #3957 one-time repair: rewrite legacy DB-path timeline rows to the split
* (source, summary) shape the post-#3957 parser emits.
*
* Pre-#3957, `parseTimelineEntries` (the DB extract / put_page auto-timeline
* path) emitted no `source` — rows landed with the column default `''` and
* the UNSPLIT bullet rest as `summary` (`'meeting — Discussed X'`). The
* parser now splits pipe bullets into (source='meeting', summary=
* 'Discussed X') and labels the rest 'markdown', so the
* (page_id, date, md5(summary), source) dedup index can never collapse a
* re-extraction onto a legacy row — every re-extract would DUPLICATE it.
*
* The rewrite is CONTENT-ANCHORED, not a blind string split: for each page
* carrying legacy rows we re-parse its current content with the NEW parser
* and rewrite a legacy row ONLY when it provably corresponds to a candidate
* the next re-extract will emit — either verbatim (dash bullets, no-delimiter
* pipe bullets, citation rows: same summary, new source label) or via the
* shared link-aware `Source — Summary` split (pipe bullets). A blind split
* would corrupt dash-bullet rows whose text contains an interior ` - `.
* Legacy rows with no matching candidate (content edited/deleted since) are
* left as-is: a re-extract won't re-emit them, so they can't duplicate.
*
* Idempotent (rewritten rows no longer match `source = ''`); same SQL text on
* both engines. When the new-shape row ALREADY exists (a re-extract duplicated
* before this repair ran), the legacy row is deleted instead of rewritten.
*/
export async function repairLegacyTimelineSourceRows(
engine: BrainEngine,
): Promise<LegacyTimelineRepairResult> {
const result: LegacyTimelineRepairResult = {
pagesScanned: 0, rowsRewritten: 0, rowsDeleted: 0, rowsSkipped: 0,
};
const tbl = await engine.executeRaw<{ reg: string | null }>(
`SELECT to_regclass('timeline_entries')::text AS reg`,
);
if (!tbl[0]?.reg) return result;
// Soft-deleted pages are skipped: their rows are never re-extracted, so
// they cannot duplicate — and their content is not authoritative.
const pageIds = await engine.executeRaw<{ page_id: number }>(
`SELECT DISTINCT te.page_id
FROM timeline_entries te
JOIN pages p ON p.id = te.page_id AND p.deleted_at IS NULL
WHERE te.source = ''`,
);
if (pageIds.length === 0) return result;
const PAGE_BATCH = 100;
for (let i = 0; i < pageIds.length; i += PAGE_BATCH) {
const ids = pageIds.slice(i, i + PAGE_BATCH).map(r => Number(r.page_id));
const pages = await engine.executeRaw<{
id: number; compiled_truth: string | null; timeline: string | null;
}>(
`SELECT id, compiled_truth, timeline FROM pages WHERE id = ANY($1::int[])`,
[ids],
);
for (const page of pages) {
result.pagesScanned++;
// Same content assembly as the DB extract paths (extractStaleFromDB /
// extractTimelineFromDB): body + timeline column.
const fullContent = `${page.compiled_truth ?? ''}\n${page.timeline ?? ''}`;
const byDate = new Map<string, Array<{ source: string; summary: string }>>();
for (const c of parseTimelineEntries(fullContent)) {
const list = byDate.get(c.date) ?? [];
list.push({ source: c.source ?? '', summary: c.summary });
byDate.set(c.date, list);
}
const legacyRows = await engine.executeRaw<{
id: number; date: string; summary: string;
}>(
`SELECT id, to_char(date, 'YYYY-MM-DD') AS date, summary
FROM timeline_entries WHERE page_id = $1 AND source = ''
ORDER BY id`,
[page.id],
);
for (const row of legacyRows) {
const candidates = byDate.get(row.date) ?? [];
let target: { source: string; summary: string } | null = null;
// 1. Verbatim: the new parser emits the same summary under a real
// source label (dash bullets → 'markdown', citations → label).
target = candidates.find(c => c.source !== '' && c.summary === row.summary) ?? null;
// 2. Split: the legacy summary is the UNSPLIT `Source — Summary`
// text of a pipe bullet; the shared delimiter finder must yield
// exactly the candidate's (source, summary) pair.
if (!target) {
const at = findTimelineSourceDelimiter(row.summary);
if (at >= 0) {
const pre = row.summary.slice(0, at).trim();
const post = row.summary.slice(at + 1).trim();
target = candidates.find(c => c.source === pre && c.summary === post) ?? null;
}
}
if (!target) { result.rowsSkipped++; continue; }
// A new-shape duplicate may already exist (re-extract ran before this
// repair): drop the legacy row instead of colliding with the unique
// (page_id, date, md5(summary), source) index on UPDATE.
const del = await engine.executeRaw<{ id: number }>(
`DELETE FROM timeline_entries t
WHERE t.id = $1
AND EXISTS (
SELECT 1 FROM timeline_entries x
WHERE x.page_id = t.page_id AND x.date = t.date
AND md5(x.summary) = md5($2) AND x.source = $3 AND x.id <> t.id
)
RETURNING t.id`,
[row.id, target.summary, target.source],
);
if (del.length > 0) { result.rowsDeleted++; continue; }
await engine.executeRaw(
`UPDATE timeline_entries SET source = $2, summary = $3
WHERE id = $1 AND source = ''`,
[row.id, target.source, target.summary],
);
result.rowsRewritten++;
}
}
}
return result;
}