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

6.5 KB
Raw Blame
/** Full restore resets known application objects, never arbitrary CASCADE targets. */
import { readFile } from 'node:fs/promises';

let resetPlan;

export async function getDatabaseResetPlan() {
  if (resetPlan) return resetPlan;
  const { buildUpgradeDdl, buildCatalogDdl } = await import('../lib/db/schema/index.js');
  const base = await readFile(new URL('../scripts/init-db.sql', import.meta.url), 'utf8');
  const ddl = [base, ...buildUpgradeDdl(), ...buildCatalogDdl()].join('\n');
  // These are trusted, shipped DDL declarations, never names from a backup or
  // the live database. Fail closed on objects not declared by this version.
  const names = (pattern) => [...new Set([...ddl.matchAll(pattern)].map(match => match[1]))];
  const tables = names(/CREATE TABLE IF NOT EXISTS ([a-z_][a-z_0-9]*)/g);
  // Recognize only the signatures currently shipped by the schema: trigger
  // functions without arguments, the catalog resolver's named TEXT input and
  // the catalog change clock's named TIMESTAMPTZ input (#12500).
  // Unsupported future declarations remain excluded and fail preflight closed.
  const functions = names(/CREATE OR REPLACE FUNCTION ([a-z_][a-z_0-9]*\((?:[a-z_][a-z_0-9]* (?:TEXT|TIMESTAMPTZ))?\))/g)
    .map(signature => `public.${signature.replace(/\([a-z_][a-z_0-9]* (TEXT|TIMESTAMPTZ)\)/, (_, type) => `(${type.toLowerCase()})`)}`);
  const views = names(/CREATE OR REPLACE VIEW ([a-z_][a-z_0-9]*)/g);
  const sequences = names(/CREATE SEQUENCE IF NOT EXISTS ([a-z_][a-z_0-9]*)/g);
  const literals = values => values.map(value => `'${value}'`).join(', ');
  const preflight = `DO $portos_restore$
  BEGIN
    IF EXISTS (
      SELECT 1 FROM pg_namespace n WHERE n.nspname = 'public'
      AND NOT (n.nspowner = (SELECT oid FROM pg_roles WHERE rolname = current_user)
        OR (pg_get_userbyid(n.nspowner) = 'pg_database_owner' AND
          (SELECT datdba FROM pg_database WHERE datname = current_database()) =
          (SELECT oid FROM pg_roles WHERE rolname = current_user)))
    ) THEN RAISE EXCEPTION 'Restore preflight: application schema is not owned by the database user'; END IF;

    IF EXISTS (
      SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
      WHERE n.nspname = 'public' AND c.relkind NOT IN ('i', 'I', 't')
      AND NOT EXISTS (SELECT 1 FROM pg_depend d WHERE d.classid = 'pg_class'::regclass
        AND d.objid = c.oid AND d.deptype = 'e')
      AND (c.relowner <> (SELECT oid FROM pg_roles WHERE rolname = current_user)
        OR NOT ((c.relkind = 'r' AND c.relname IN (${literals(tables)}))
          OR (c.relkind = 'v' AND c.relname IN (${literals(views)}))
          OR (c.relkind = 'S' AND (c.relname IN (${literals(sequences)}) OR EXISTS (
            SELECT 1 FROM pg_depend d JOIN pg_class t ON t.oid = d.refobjid
            WHERE d.classid = 'pg_class'::regclass AND d.objid = c.oid
            AND d.refclassid = 'pg_class'::regclass AND d.deptype IN ('a', 'i')
            AND t.relnamespace = n.oid AND t.relname IN (${literals(tables)})
          )))))
    ) THEN RAISE EXCEPTION 'Restore preflight: unexpected application-schema relation or owner'; END IF;

    IF EXISTS (
      SELECT 1 FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
      WHERE n.nspname = 'public'
      AND NOT EXISTS (SELECT 1 FROM pg_depend d WHERE d.classid = 'pg_proc'::regclass
        AND d.objid = p.oid AND d.deptype = 'e')
      AND (NOT EXISTS (SELECT 1 FROM unnest(ARRAY[${literals(functions)}]) AS allowed(signature)
          WHERE p.oid = to_regprocedure(allowed.signature))
        OR p.proowner <> (SELECT oid FROM pg_roles WHERE rolname = current_user))
    ) THEN RAISE EXCEPTION 'Restore preflight: unexpected application-schema function or owner'; END IF;

    IF EXISTS (
      SELECT 1 FROM pg_type t JOIN pg_namespace n ON n.oid = t.typnamespace
      WHERE n.nspname = 'public' AND t.typrelid = 0 AND t.typelem = 0
      AND NOT EXISTS (SELECT 1 FROM pg_depend d WHERE d.classid = 'pg_type'::regclass
        AND d.objid = t.oid AND d.deptype = 'e')
    ) OR EXISTS (
      SELECT 1 FROM pg_extension e JOIN pg_namespace n ON n.oid = e.extnamespace
      WHERE n.nspname = 'public' AND e.extname NOT IN ('vector', 'pgcrypto')
    ) OR EXISTS (
      SELECT 1 FROM pg_depend d JOIN pg_namespace n ON n.oid = d.refobjid
      WHERE d.refclassid = 'pg_namespace'::regclass AND n.nspname = 'public'
      AND d.classid NOT IN ('pg_class'::regclass, 'pg_proc'::regclass, 'pg_type'::regclass, 'pg_extension'::regclass)
      AND NOT EXISTS (SELECT 1 FROM pg_depend e WHERE e.classid = d.classid
        AND e.objid = d.objid AND e.deptype = 'e')
    ) THEN RAISE EXCEPTION 'Restore preflight: unexpected application-schema object'; END IF;

    IF EXISTS (
      SELECT 1 FROM pg_constraint c JOIN pg_class parent ON parent.oid = c.confrelid
      JOIN pg_namespace pn ON pn.oid = parent.relnamespace
      JOIN pg_class child ON child.oid = c.conrelid
      JOIN pg_namespace cn ON cn.oid = child.relnamespace
      WHERE c.contype = 'f' AND pn.nspname = 'public' AND cn.nspname <> 'public'
    ) OR EXISTS (
      SELECT 1 FROM pg_depend d JOIN pg_rewrite r ON r.oid = d.objid
      JOIN pg_class v ON v.oid = r.ev_class JOIN pg_namespace vn ON vn.oid = v.relnamespace
      JOIN pg_class t ON t.oid = d.refobjid JOIN pg_namespace tn ON tn.oid = t.relnamespace
      WHERE d.classid = 'pg_rewrite'::regclass AND d.refclassid = 'pg_class'::regclass
      AND tn.nspname = 'public' AND vn.nspname <> 'public'
    ) THEN RAISE EXCEPTION 'Restore preflight: non-PortOS dependency on application schema'; END IF;
  END $portos_restore$;`;

  // A single DROP list permits dependencies BETWEEN approved tables. RESTRICT
  // remains the database-enforced backstop for every unanticipated dependency,
  // including changes between preflight and replay. Never use CASCADE here.
  const reset = `${preflight}
    DROP VIEW IF EXISTS ${views.map(name => `public.${name}`).join(', ')} RESTRICT;
    DROP TABLE IF EXISTS ${tables.map(name => `public.${name}`).join(', ')} RESTRICT;
    DROP FUNCTION IF EXISTS ${functions.join(', ')} RESTRICT;
    DROP SEQUENCE IF EXISTS ${sequences.map(name => `public.${name}`).join(', ')} RESTRICT;
    CREATE SCHEMA IF NOT EXISTS public;
    CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA public;
    CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;`;
  // Keep the namespace (including its owner/ACLs) and installed extensions.
  // pg_dump's own clean statements can recreate extensions before their types
  // are consumed. All application tables, functions and sequence state reset.
  resetPlan = { preflight, reset };
  return resetPlan;
}