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

16.0 KB
Raw Blame
/**
 * Committed-restore recovery for snapshot database restores (#9725).
 *
 * restorePostgres publishes a durable journal (server/lib/databaseRestoreRecovery.js)
 * with the ORIGINAL feed positions before its destructive replay, and the replay
 * writes a receipt row for that operation inside its own transaction. From then
 * on ordinary database work stays fenced until this module finishes repair:
 * forced schema upgrades, ordered migrations, feed-sequence flooring at the
 * recorded positions and the inbound cursor rewind. A committed replay is never
 * replayed again; an unknown outcome is resolved only from the receipt.
 */
import { databaseRestoreRecovery, ensureSchema, query, withDatabaseMaintenance } from '../lib/db.js';
import { syncFeedTables, syncFeedSequenceName } from '../lib/db/schema/syncFeed.js';
import { restoreReceiptsDdl } from '../lib/db/schema/core.js';
import { acquireBackupSnapshotCut } from '../lib/backupSnapshotBoundary.js';

// Ties the replay's psql session to its operation, so recovery can prove no
// session of that replay is still able to commit before reading "no receipt"
// as a rollback.
export const restoreApplicationName = (id) => `portos-restore-${id}`;

// Appended after the dump inside the same --single-transaction replay. The
// id/digest come from the validated journal (uuid / lowercase hex), never from
// the snapshot. pg_dump empties search_path, so restore the application schema
// first (ensureSchema creates the table there too).
export const restoreReceiptSql = ({ id, dumpSha256 }) =>
  `SET search_path = public;\n${restoreReceiptsDdl.join(';\n')};\nINSERT INTO restore_receipts (operation_id, dump_sha256) VALUES ('${id}', '${dumpSha256}');`;

const SESSION_SETTLE_ATTEMPTS = 10;
const SESSION_SETTLE_MS = 200;

const MESSAGES = {
  restore_commit_unknown: 'The restore could not confirm whether the database dump was committed. Ordinary database work stays paused and the dump will NOT be replayed; retry recovery from Settings > Backup once PostgreSQL is reachable.',
  restore_schema_reconciliation: 'The database dump was applied, but schema recovery is incomplete. It was not rolled back and will not be replayed. Ordinary database work stays paused: retry recovery from Settings > Backup, or restart PortOS to retry automatically. If it keeps failing, check the server logs.',
  restore_execution_reconciliation: 'Execution request consumption and unresolved ownership are awaiting reconciliation. Database work stays paused; the dump will not be replayed. Recovery retries automatically at startup and can also be retried from Settings > Backup.',
  restore_catalog_reconciliation: 'The database dump was applied, but catalog payload or universe-tag repair is incomplete. It will not be replayed. Ordinary database work stays paused: retry recovery from Settings > Backup, or restart PortOS to retry automatically.',
  restore_media_reconciliation: 'The database dump was applied, but the media index could not be rebuilt from local files. It will not be replayed. Ordinary database work stays paused: retry recovery from Settings > Backup, or restart PortOS.',
  restore_sync_resync: 'The database dump was applied, but peer sync could not be reset yet. It will not be replayed. Ordinary database work stays paused: retry recovery from Settings > Backup, or restart PortOS to retry automatically.',
  restore_recovery_release: 'The database dump was applied and repaired, but the recovery journal could not be cleared. Ordinary database work stays paused: retry recovery from Settings > Backup.',
  restore_recovery_pending: 'A previous database restore is awaiting recovery. Finish it from Settings > Backup before starting another restore.',
  restore_recovery_damaged: 'The database restore recovery journal is unreadable, so the database stays fenced. Inspect data/database-restore-recovery.json and the server logs; never delete it unless you have confirmed how the restore ended.',
};

/** Bounded status projection — never feed positions, paths or digests. */
function recoveryProjection(record) {
  return { id: record.id, stage: record.stage, snapshotId: record.snapshotId, createdAt: record.createdAt };
}

export function pendingRecoveryResult(reason, record) {
  return { status: 'failed', reason, error: MESSAGES[reason], recovery: recoveryProjection(record) };
}

/**
 * Pending restore recovery, for the API/UI. A damaged journal still fences the
 * database and is reported as such rather than as "nothing pending".
 */
export function getDatabaseRestoreRecoveryStatus() {
  if (!databaseRestoreRecovery.isFenced()) return { pending: false };
  let record;
  try {
    record = databaseRestoreRecovery.read();
  } catch {
    return { pending: true, damaged: true };
  }
  return record ? { pending: true, ...recoveryProjection(record) } : { pending: false };
}

/** A restore refusal while recovery is pending, or null when none is. */
export function restoreRecoveryRefusal() {
  const status = getDatabaseRestoreRecoveryStatus();
  if (!status.pending) return null;
  return {
    status: 'failed',
    reason: 'restore_recovery_pending',
    error: status.damaged ? MESSAGES.restore_recovery_damaged : MESSAGES.restore_recovery_pending,
    recovery: status.damaged ? { damaged: true } : recoveryProjection(status),
  };
}

// Feed sequences present before the replay (a pre-#8315 install has none).
// pg_sequences reports a NULL last_value for a never-drawn sequence, which
// holds no position worth preserving.
export async function captureSyncFeedPositions() {
  const { rows } = await query(
    `SELECT sequencename, last_value::text AS last_value FROM pg_sequences
      WHERE schemaname = current_schema() AND sequencename = ANY($1::text[]) AND last_value IS NOT NULL`,
    [syncFeedTables.map(syncFeedSequenceName)],
  );
  return rows.map(({ sequencename, last_value }) => ({ sequencename, last_value }));
}

/**
 * Resolve a replay whose outcome the restoring process did not observe
 * (non-zero/killed psql, lost response, crash) from its transactional receipt.
 *   'committed'   — this operation's receipt row exists
 *   'rolled_back' — no session of the replay remains and no receipt exists
 *   'uncertain'   — a replay session is still live, or the database could not
 *                   be inspected; nothing may reopen or replay
 */
async function inspectReplayOutcome(record) {
  const inspect = async () => {
    // A replay backend that is still running could yet commit: the receipt's
    // absence proves a rollback only once no session of that replay remains.
    let sessions = 1;
    for (let attempt = 0; attempt < SESSION_SETTLE_ATTEMPTS && sessions > 0; attempt++) {
      if (attempt) await new Promise(resolve => setTimeout(resolve, SESSION_SETTLE_MS));
      const { rows } = await query(
        `SELECT (SELECT count(*) FROM pg_stat_activity WHERE application_name = $1)::int AS sessions,
           to_regclass('public.restore_receipts') IS NOT NULL AS has_receipts`,
        [restoreApplicationName(record.id)],
      );
      sessions = rows[0]?.sessions ?? 1;
      if (!sessions && !rows[0]?.has_receipts) return 'rolled_back';
    }
    if (sessions > 0) return 'uncertain';
    const { rows } = await query('SELECT dump_sha256 FROM restore_receipts WHERE operation_id = $1', [record.id]);
    if (!rows.length) return 'rolled_back';
    // A receipt for this unique id with another digest is not ours to trust.
    return rows[0].dump_sha256 === record.dumpSha256 ? 'committed' : 'uncertain';
  };
  return inspect().catch((err) => {
    console.error(`❌ DB restore ${record.id}: replay outcome could not be inspected: ${err.message}`);
    return 'uncertain';
  });
}

/**
 * Repair both directions of peer sync after a committed replay (#8710).
 *
 * Outbound: the dump `setval`s each feed sequence back to the dump's maximum,
 * so new rows would reuse positions peers already passed and never be pulled.
 * Floor every sequence at its ORIGINAL pre-restore value (from the journal,
 * never re-captured) so post-restore positions land above any peer cursor.
 *
 * Inbound: our per-peer memory/Catalog cursors still point past rows the
 * restore discarded. Rewind them so the next sync replays each peer's streams
 * through the idempotent LWW / ON CONFLICT apply paths. Both steps are
 * idempotent, so a retried recovery repeats them safely.
 * @returns {Promise<number>} peers whose cursors were rewound
 */
async function resyncFederationAfterRestore(feedPositions) {
  if (feedPositions.length) {
    // GREATEST ignores the NULL of a sequence the dump left undrawn.
    await query(
      `SELECT setval(format('%I', s.sequencename)::regclass, GREATEST(c.captured::bigint, s.last_value))
        FROM pg_sequences s
        JOIN unnest($1::text[], $2::text[]) AS c(name, captured) ON c.name = s.sequencename
        WHERE s.schemaname = current_schema()`,
      [feedPositions.map((p) => p.sequencename), feedPositions.map((p) => p.last_value)],
    );
  }
  const { rewindPostgresSyncCursors } = await import('./syncOrchestrator.js');
  const peers = await rewindPostgresSyncCursors();
  console.log(`🔄 DB restore: floored ${feedPositions.length} sync feed sequences, rewound memory/Catalog cursors for ${peers} peers`);
  return peers;
}

/**
 * Finish a COMMITTED replay. Must run inside the maintenance context that owns
 * this operation. Releases admission only after every step succeeded.
 * @returns {Promise<{status:'ok', syncCursorsRewound:number}|object>} ok, or a pending-recovery failure
 */
export async function repairCommittedRestore(record) {
  // Reapply this version's upgrades even when readiness was cached before the
  // restore, then honor the restored migration ledger.
  const reconciliationError = await (async () => {
    await ensureSchema({ force: true });
    const { runDbMigrations } = await import('../scripts/run-db-migrations.js');
    await runDbMigrations();
  })().then(() => null, (err) => err);
  if (reconciliationError) {
    console.error(`❌ DB restore ${record.id}: schema reconciliation failed: ${reconciliationError.message}`);
    return pendingRecoveryResult('restore_schema_reconciliation', record);
  }
  // The on-disk markers describe the pre-restore database, so an older snapshot
  // would otherwise look already migrated on every later boot. Force both walks
  // (each also re-stamps its marker) so catalog rows match the restored state.
  const catalogError = await (async () => {
    const { migrateCatalogPayload } = await import('../scripts/migrateCatalogPayload.js');
    const { repairUniverseTags } = await import('../scripts/repairUniverseTags.js');
    await migrateCatalogPayload({ force: true });
    await repairUniverseTags({ force: true });
  })().then(() => null, (err) => err);
  if (catalogError) {
    console.error(`❌ DB restore ${record.id}: catalog reconciliation failed: ${catalogError.message}`);
    return pendingRecoveryResult('restore_catalog_reconciliation', record);
  }
  // Old snapshots may contain stale mirror rows; new snapshots omit them.
  // Either way disk is authoritative, including for a DB-only restore.
  const mediaError = await (async () => {
    const { reconcileMediaAssets } = await import('./mediaAssetIndex/db.js');
    await reconcileMediaAssets({ rebuild: true });
  })().then(() => null, error => error);
  if (mediaError) {
    console.error(`❌ DB restore ${record.id}: media reconciliation failed: ${mediaError.message}`);
    return pendingRecoveryResult('restore_media_reconciliation', record);
  }
  const executionError = await (async () => {
    const { finishPeerExecutionRestore } = await import('./peerExecutionRestore.js');
    await finishPeerExecutionRestore(record.id);
  })().then(() => null, error => error);
  if (executionError) {
    console.error(`❌ DB restore ${record.id}: execution reconciliation failed: ${executionError.message}`);
    return pendingRecoveryResult('restore_execution_reconciliation', record);
  }
  const resync = await resyncFederationAfterRestore(record.feedPositions).then(
    (syncCursorsRewound) => ({ syncCursorsRewound }),
    (err) => ({ err }),
  );
  if (resync.err) {
    console.error(`❌ DB restore ${record.id}: federation resync failed: ${resync.err.message}`);
    return pendingRecoveryResult('restore_sync_resync', record);
  }
  const releaseError = await Promise.resolve().then(() => databaseRestoreRecovery.release(record.id)).then(() => null, (err) => err);
  if (releaseError) {
    console.error(`❌ DB restore ${record.id}: recovery journal could not be cleared: ${releaseError.message}`);
    return pendingRecoveryResult('restore_recovery_release', record);
  }
  console.log(`💾 DB restore ${record.id}: recovery complete, database admission reopened`);
  return { status: 'ok', syncCursorsRewound: resync.syncCursorsRewound };
}

/**
 * Settle an operation whose replay outcome was not observed as a commit.
 * Inside the owning maintenance context. Returns 'rolled_back' after releasing
 * admission, 'committed' (with the record now at `repairing`) after recording
 * the commit, or 'uncertain'.
 * @returns {Promise<{outcome: string, record: object}>}
 */
export async function settleReplayOutcome(record) {
  const outcome = await inspectReplayOutcome(record);
  if (outcome === 'rolled_back') {
    try {
      const { finishPeerExecutionRestore } = await import('./peerExecutionRestore.js');
      await finishPeerExecutionRestore(record.id, { rolledBack: true });
    } catch (err) {
      console.error(`❌ DB restore ${record.id}: rollback execution reconciliation failed: ${err.message}`);
      return { outcome: 'uncertain', record, reason: 'restore_execution_reconciliation' };
    }
    databaseRestoreRecovery.release(record.id);
    console.log(`💾 DB restore ${record.id}: replay rolled back; database admission reopened without changes`);
    return { outcome, record };
  }
  if (outcome === 'committed') return { outcome, record: databaseRestoreRecovery.markCommitted(record.id) };
  console.error(`❌ DB restore ${record.id}: replay outcome unknown; database stays fenced`);
  return { outcome, record };
}

/**
 * Resume the pending restore recovery: resolve an unknown replay outcome from
 * its receipt, then repair a committed replay. Never resets or replays.
 *   { status: 'ok', outcome: 'repaired', syncCursorsRewound }
 *   { status: 'ok', outcome: 'rolled_back' }   (proven rollback, nothing changed)
 *   { status: 'ok', outcome: 'none' }          (nothing pending)
 *   { status: 'failed', reason, error, recovery }  (still pending)
 * @param {string} [id] - operation to resume; omitted = whichever is pending
 */
export async function resumeDatabaseRestore(id) {
  const record = databaseRestoreRecovery.read();
  if (!record) return { status: 'ok', outcome: 'none' };
  if (id && record.id !== id) {
    throw Object.assign(new Error('A different database restore operation is pending.'), { status: 409, code: 'RESTORE_RECOVERY_MISMATCH' });
  }
  // Initial replay holds this boundary, but a failed repair releases it while
  // retaining the same-operation DB fence. Reacquire before DB maintenance:
  // draining publications may still need the DB to finish their settlement.
  // Keep file-backed authorities stable through the media rebuild on retries
  // and restart, and release only this recovery attempt's cut on every outcome.
  const releaseSnapshotCut = await acquireBackupSnapshotCut();
  try {
    return await withDatabaseMaintenance(async () => {
      // Re-read inside maintenance: another resume may have finished meanwhile.
      let current = databaseRestoreRecovery.read();
      if (current?.id !== record.id) return { status: 'ok', outcome: 'none' };
      if (current.stage === 'replaying') {
        const settled = await settleReplayOutcome(current);
        if (settled.outcome === 'rolled_back') return { status: 'ok', outcome: 'rolled_back' };
        if (settled.outcome === 'uncertain') return pendingRecoveryResult(settled.reason ?? 'restore_commit_unknown', current);
        current = settled.record;
      }
      const repaired = await repairCommittedRestore(current);
      return repaired.status === 'ok' ? { ...repaired, outcome: 'repaired' } : repaired;
    }, { restoreRecoveryId: record.id });
  } finally {
    releaseSnapshotCut();
  }
}