Skip to content

Garry's Opinionated OpenClaw/Hermes Agent Brain

TypeScript
6.0 KB
Raw Blame
/**
 * #550 — pages(source_id, slug) unique-arbiter schema-drift self-heal.
 *
 * Every `putPage` upsert (both engines) infers ON CONFLICT (source_id, slug),
 * which requires a non-partial UNIQUE index covering exactly those columns.
 * When the `pages_source_slug_key` constraint vanishes (partial restore,
 * manual DDL, a failed/renumbered migration), EVERY page write brain-wide
 * fails with "no unique or exclusion constraint matching the ON CONFLICT
 * specification" — and neither re-running `initSchema` nor the version
 * counter can see the drift (migrations v21/v23 guarded the ADD by
 * constraint NAME only, and the version counter is already stamped past
 * them).
 *
 * Mirrors timeline-dedup-repair.ts (#2038): the check keys off the actual
 * index SHAPE (any non-partial unique index whose column set is exactly
 * {source_id, slug}, regardless of name) and the repair runs on every
 * migrate pass. The repair only ever ADDs the constraint — it never deletes
 * rows. If duplicate (source_id, slug) rows already snuck in through the
 * unprotected window, it refuses and reports them for manual resolution.
 */

import type { BrainEngine } from './engine.ts';

const CONSTRAINT_NAME = 'pages_source_slug_key';
/** Sorted column set the ON CONFLICT (source_id, slug) inference needs. */
const EXPECTED_COLUMN_SET = ['slug', 'source_id'];

/** Parse the column list out of a pg_indexes `indexdef` string. */
function parseIndexColumns(indexdef: string): string[] {
  const open = indexdef.indexOf('(');
  const close = indexdef.indexOf(')', open);
  if (open < 0 || close < 0) return [];
  return indexdef
    .slice(open + 1, close)
    .split(',')
    .map(c => c.trim().split(/\s+/)[0]) // drop any "col DESC"/opclass suffix
    .filter(Boolean);
}

/** True when the indexdef is a non-partial unique index on exactly {source_id, slug}. */
function isArbiterShape(indexdef: string): boolean {
  if (!/^CREATE UNIQUE INDEX/i.test(indexdef)) return false;
  if (/\)\s+WHERE\s+/i.test(indexdef)) return false; // partial indexes can't arbitrate
  const cols = parseIndexColumns(indexdef).sort();
  return (
    cols.length === EXPECTED_COLUMN_SET.length &&
    EXPECTED_COLUMN_SET.every((c, i) => cols[i] === c)
  );
}

export interface PagesUpsertArbiterStatus {
  /** The pages table exists (nothing to check if not). */
  tablePresent: boolean;
  /** Some non-partial unique index on exactly (source_id, slug) exists — any name. */
  arbiterPresent: boolean;
  /** Missing arbiter: every putPage upsert is failing. */
  needsRepair: boolean;
  /** Duplicate (source_id, slug) groups that block adding the constraint. */
  duplicateGroups: number;
}

export async function checkPagesUpsertArbiter(engine: BrainEngine): Promise<PagesUpsertArbiterStatus> {
  const tbl = await engine.executeRaw<{ reg: string | null }>(
    `SELECT to_regclass('pages')::text AS reg`,
  );
  const tablePresent = !!tbl[0]?.reg;
  if (!tablePresent) {
    return { tablePresent: false, arbiterPresent: false, needsRepair: false, duplicateGroups: 0 };
  }
  // Catalog-anchored candidate set (#550 residual): pg_indexes was a bare
  // text scan on tablename, which false-passed on exactly the drift shapes
  // this module exists to catch — an INVALID index (a failed CREATE INDEX
  // CONCURRENTLY remnant renders a normal-looking indexdef but cannot
  // arbitrate), a DEFERRABLE unique (indimmediate = false, unusable for ON
  // CONFLICT), and a same-named table in ANOTHER schema (tablename has no
  // schema qualifier, while the table probe above resolved via search_path).
  // Anchoring on the SAME to_regclass('pages') plus the catalog validity
  // flags closes all three; indpred IS NULL keeps the existing
  // partial-index exclusion at the catalog level too.
  const rows = await engine.executeRaw<{ indexdef: string }>(
    `SELECT pg_get_indexdef(x.indexrelid) AS indexdef
       FROM pg_index x
      WHERE x.indrelid = to_regclass('pages')
        AND x.indisunique
        AND x.indisvalid
        AND x.indisready
        AND x.indimmediate
        AND x.indpred IS NULL`,
  );
  const arbiterPresent = rows.some(r => isArbiterShape(r.indexdef));
  let duplicateGroups = 0;
  if (!arbiterPresent) {
    const dup = await engine.executeRaw<{ n: string }>(
      `SELECT COUNT(*)::text AS n FROM (
         SELECT 1 FROM pages GROUP BY source_id, slug HAVING COUNT(*) > 1
       ) d`,
    );
    duplicateGroups = parseInt(dup[0]?.n ?? '0', 10);
  }
  return { tablePresent, arbiterPresent, needsRepair: !arbiterPresent, duplicateGroups };
}

export interface PagesUpsertArbiterRepairResult {
  repaired: boolean;
  duplicateGroups: number;
  reason: 'already_correct' | 'no_table' | 'duplicates' | 'restored';
}

/**
 * Restore the UNIQUE(source_id, slug) arbiter if it's missing. ADD-only:
 * never deletes rows. When duplicate (source_id, slug) groups exist the
 * constraint cannot be added — refuse and report so the operator resolves
 * the duplicates deliberately (each duplicate is a real page row).
 */
export async function repairPagesUpsertArbiter(engine: BrainEngine): Promise<PagesUpsertArbiterRepairResult> {
  const status = await checkPagesUpsertArbiter(engine);
  if (!status.tablePresent) {
    return { repaired: false, duplicateGroups: 0, reason: 'no_table' };
  }
  if (!status.needsRepair) {
    return { repaired: false, duplicateGroups: 0, reason: 'already_correct' };
  }
  if (status.duplicateGroups > 0) {
    return { repaired: false, duplicateGroups: status.duplicateGroups, reason: 'duplicates' };
  }
  // A misshapen constraint/index may be squatting on the canonical name (the
  // by-shape probe above already established it is NOT a valid arbiter).
  // Free the name before re-adding. Constraint-drop also drops its index.
  await engine.executeRaw(`ALTER TABLE pages DROP CONSTRAINT IF EXISTS ${CONSTRAINT_NAME}`);
  await engine.executeRaw(`DROP INDEX IF EXISTS ${CONSTRAINT_NAME}`);
  await engine.executeRaw(
    `ALTER TABLE pages ADD CONSTRAINT ${CONSTRAINT_NAME} UNIQUE (source_id, slug)`,
  );
  return { repaired: true, duplicateGroups: 0, reason: 'restored' };
}