Design Brief — Snapshot/Restore (Full-State Export + Restore)

Status: Draft for build review · Roadmap: Phase 2 (UPGRADE_ROADMAP.md “Snapshot/restore: one-click full-state export (tasks + runs + config) and restore, builds on export-routes” — Phase 2 opener per roadmap review #2) Evidence base: existing machinery verified in-repo 2026-08-24: routes/export-routes.js (GET /api/export covers only projects, tasks, workflows, audit_log LIMIT 500 + raw settings; POST /api/import/preview counts rows only — no diff; POST /api/import merge/replace with per-row upserts in one transaction), state_snapshots table (migration 20260428_add_state_snapshots.sql) = per-entity point-in-time rows for the Time Travel feature (routes/history-routes.js) — a different concept from full-state snapshots, json_snapshot storage mode (storage/asana-json-snapshot.js) = read-only fallback when PostgreSQL is down — also a different concept, both disambiguated in §1, lib/settings-store.js SCHEMA (typed settings incl. five type:'password' keys — see §5), action_receipts (migration 024, kind CHECK-constrained to the five action catalog kinds → NOT reusable for restore idempotency, §4.4), SSE fan-out (routes/sse-routes.js /api/events/stream), house degradation contract (503 {available:false} without PostgreSQL). Order: docs only. No .js/.mjs/.sql/.yml changes in this commit. Concurrent-lane guard: coder is wiring actions slice 2 (src/shell/native-views/tasks-view.mjs, agent-queue-view.mjs, approvals-view.mjs, src/shell/action-client.mjs) — the build phase of THIS brief must not touch those files (§8 R3).

1. Purpose & Value Proposition

The dashboard’s entire state lives in one PostgreSQL database plus a handful of config files, and today there is exactly one escape hatch: GET /api/export. It exports four tables and — worse — embeds settingsStore.getAll() raw, which includes live values for DASHBOARD_AUTH_TOKEN, POSTGRES_PASSWORD, OPENCLAW_GATEWAY_PASSWORD, OPENCLAW_GATEWAY_TOKEN, and BING_WEBMASTER_API_KEY (§5 R1). Its import side has no real preview (row counts only), no schema compatibility check, no idempotent replay, and no resume: a failed import mid-transaction rolls back everything and the operator starts over blind.

Snapshot/restore v1 replaces “export/import” with a named, versioned, downloadable artifact and a governed restore path:

  1. Create — one click produces a full-state snapshot artifact: every dashboard table + non-secret settings, wrapped in a manifest carrying row counts and the exact set of applied migrations.
  2. Restore — upload (or pick a server-side snapshot) → dry-run diff preview (rows added / updated / conflicted per table, computed before anything is written) → confirm → apply with progress, resumable after partial failure.

Why it matters: single-operator deployments upgrade schemas weekly (sequential migrations are a working rule), experiment with budgets/dispatcher behavior, and run long-lived content pipelines. Today a botched migration or a bad bulk edit is unrecoverable except by hand. Paperclip’s revisioned rollback (79k★, market scan 2026-08-24) set the expectation; this is our scoped version of it — not per-entity undo (that’s Time Travel + receipts’ rollback hints), but whole-state insurance.

Disambiguation (three things called “snapshot” in this repo):

Name What it actually is Relationship to this brief
state_snapshots table Per-entity row history for Time Travel (history-routes.js), written on every mutation Excluded from artifacts (§2); unaffected by restore
json_snapshot storage mode Read-only fallback serving tasks/projects from a JSON file when PostgreSQL is unreachable Unaffected; snapshots require DB to create/apply (§4.5)
This feature Full-state export artifact + governed restore —

2. Scope Matrix v1

A snapshot is a JSON artifact, not database rows (decision in §6). Contents fixed at v1:

2.1 IN — captured per snapshot

Manifest always records counts[table] (exact row count at creation) and schema_version (the full list of applied migration names from schema_migrations + generator version).

Tier Tables Why
A — core state workflows, projects, tasks, workspaces, saved_views, departments, agent_profiles The board an operator would need to reconstruct first
B — workflow engine workflow_runs, workflow_steps, workflow_approvals, workflow_templates, workflow_agent_routing, workflow_artifacts (rows only — uri points at files on disk, files themselves OUT, §2.2) Roadmap says “tasks + runs”; runs without steps/approvals is half a state
C — governance & money budgets, budget_events, action_receipts, audit_log (complete — no LIMIT 500 truncation) Budget rules and the audit trail are state; losing them defeats “full-state”
D — service & metrics service_catalog, service_requests, department_daily_metrics, task_runs, cron_job_runs, agent_heartbeats Small tables; completeness beats special-casing
E — settings Non-secret UI/runtime preferences ONLY: every settings-store.js SCHEMA key with source:'config' (theme, accentColor, wallpaper, windowSnap, rememberWindowPositions, fontSizeBase, showClock, clock24h, showWidgets, taskbarOpacity, disabledApps, quickLaunchApps, CHAT_RATE_LIMIT, MAX_MESSAGE_LENGTH, SSE_MAX_CLIENTS, API_LOG_LEVEL, SSE_HEARTBEAT_INTERVAL, MESSAGE_PAGINATION_LIMIT) Config per roadmap. Env-source keys excluded wholesale: machine-specific AND secret-adjacent (§5)

2.2 OUT — explicitly excluded, with reasons

Excluded Reason
Secrets of any kind — all type:'password' settings keys, any key matching the deny-regex, nested secret-looking keys inside JSONB Hard policy, §5. Never serialized, not even redacted placeholders for password-type settings keys (absence, not [REDACTED], for settings; [REDACTED] only for incidental matches found in JSONB data)
.env and dashboard-config.json as files Contain credentials; env keys are machine-specific anyway — restoring them cross-instance would be silent misconfiguration
Gateway session transcripts (~/.openclaw/agents/<agent>/sessions/*.jsonl) Belong to OpenClaw, not the dashboard DB; huge; already have their own retention. Session replay reads them live — no backup claim
Files referenced by workflow_artifacts.uri Binary/blob backup is a non-goal (§7); rows carry the metadata + pointer
state_snapshots rows Internal time-travel machinery; regenerated by future mutations; porting them would corrupt as-of-t semantics and dominate artifact size
schema_migrations rows Never restored — read at restore time for the compat check (§4.3); target owns its own migration state

3. UX Flow

All operator surfaces live in a new “Snapshots & Restore” panel inside the existing Settings app — no new windowed app (app-registry count, README count, views-reference table stay frozen; same call one-click-actions made for its tray).

3.1 Create snapshot

  1. Settings → Snapshots & Restore → name input (default snapshot-YYYYMMDD-HHmm) → Create snapshot button.
  2. Button disabled while generating; server serializes all tiers in one pass, writes the artifact atomically (tmp + rename into storage/snapshots/<snapshot_id>.json), returns the manifest.
  3. Panel lists server-side snapshots newest-first (name, created_at, total rows, size). Each row: Download (artifact JSON as attachment) and Restore… (pre-fills the upload flow with the server-side copy).
  4. Toast on success shows total row count; failure keeps prior list untouched (zero-throw panel states: loading / empty / error-retry, house pattern).

3.2 Restore

  1. Upload: file picker (or “use server-side snapshot”). Client enforces size cap pre-upload (§4.5); server re-checks pre-parse.
  2. Dry-run diff preview (nothing written yet): manifest validation result, schema-compat verdict (§4.3), and a per-table grid — added / updated / conflicts / unchanged counts, expandable to sample PKs. Conflicts defined in §4.2. Mode selector: Merge (default) vs Replace.
  3. Choosing Replace flips the modal to the HOLD_CONFIRM pattern from the one-click-actions brief §3.2 (press-and-hold ≥1.2 s, progress ring, release early = nothing fires; keyboard hold parity + typed-confirm fallback pinned as AC12). Merge confirms with a plain PREVIEW_MODAL-style Confirm. The preview must also show the rollback move before confirming: “Re-create a snapshot of the current state first” — the create flow is one click away and is the honest rollback hint for a destructive restore.
  4. Apply with progress: SSE restore-progress frames on the existing /api/events/stream channel ({restoreId, table, doneRows, totalRows} per completed table batch) drive a determinate progress bar; closing the panel does not cancel the apply. On completion: summary card (rows upserted/deleted/skipped per table) persisted with the restore record; on resume-after-refresh the client reattaches by restoreId.

4. Data Contract

4.1 Endpoints (new routes/snapshot-routes.js, registered in task-server.js)

Endpoint Does Degradation (no PostgreSQL)
POST /api/snapshots {name} Serialize all §2.1 tiers + redaction pass (§5) → atomic write → 201 {snapshot_id, manifest} 503 {available:false, reason:'no_database'}
GET /api/snapshots Disk index scan of storage/snapshots/*.json, manifests newest-first Works — disk-only
GET /api/snapshots/:id/download Artifact stream, Content-Disposition: attachment; 404 unknown id Works — disk-only
POST /api/restore/preview {artifact} or {snapshot_id} Validate manifest → version-compat check → diff vs live DB (read-only) → {schema_compat, warnings, tables:{<name>:{added,updated,conflicts,unchanged}}, totals} 503 {available:false}
POST /api/restore/apply {artifact \| snapshot_id, mode:'merge'\|'replace', restoreId, confirm?} Latch + checkpoint (§4.4) → per-table transactional apply → summary 503 {available:false}, zero writes

Plus additive SSE event restore-progress on the existing stream. No new REST surface beyond the five endpoints. Size cap: requests larger than RESTORE_MAX_BYTES (default 100 MB) rejected 413 before JSON.parse (AC9).

4.2 Artifact format (v1)

{
  "manifest": {
    "artifact_version": 1,
    "snapshot_id": "<uuid>",
    "name": "snapshot-20260824-1536",
    "created_at": "<ISO>",
    "actor": "dashboard-operator",
    "generator": "openclaw-project-webos <version>",
    "schema_version": { "migrations_applied": ["001_add_workflow_runs", "..."], },
    "counts": { "tasks": 123, "workflow_runs": 45, "...": 0 },
    "content_hash": "sha256(canonicalJSON(tables+settings))" // integrity check at preview
  },
  "tables": { "workflows": [ /* full rows */ ], "projects": [ /* ... */ ] },
  "settings": { "theme": "dark" /* config-source keys only, post-redaction */ }
}

Diff classification (pure function, §6): per table, keyed by PK column —

Apply semantics: merge (default) upserts added+updated+conflict rows by PK, never deletes; replace additionally deletes rows absent from the artifact, per table, in FK-safe reverse order — destructive, gated (§3.2 step 3). Insert/update order follows the dependency chain workflows → projects → tasks → workflow_runs → workflow_steps → workflow_approvals → workflow_artifacts → … (fixture-pinned, AC10).

4.3 Schema-version compatibility

Rule: refuse restore from newer, warn into older.

4.4 Idempotency + resume on partial failure

action_receipts was considered and rejected: its kind CHECK enumerates only the five catalog kinds; widening it needs a migration and couples two features. Instead, restore idempotency is file-backed and therefore DB-free-testable:

4.5 Progress

Per-table batches (~500 rows) inside one transaction per table; after each table the checkpoint advances and one restore-progress SSE frame fans out. Clients that miss frames degrade to the final summary (progress bar is convenience, not correctness).

5. Secrets Policy (hard)

  1. Dynamic exclusion at the source: settings section of an artifact is built from settings-store.js SCHEMA keys where source === 'config' ONLY. Every type:'password' key (DASHBOARD_AUTH_TOKEN, POSTGRES_PASSWORD, OPENCLAW_GATEWAY_PASSWORD, OPENCLAW_GATEWAY_TOKEN, BING_WEBMASTER_API_KEY) and every other env-source key is structurally absent — derived from the SCHEMA at runtime so future settings additions can’t silently leak.
  2. Defense-in-depth deny-regex pass over the whole artifact before serialization (lib/snapshot-redact.js, pure): recursive walk over every JSONB cell; any object key matching (?<![a-z0-9])(password|passwd|secret|token|api[_-]?key|apikey|auth[_-]?token|credential)(?![a-z0-9]) (case-insensitive; letters/digits count as word continuation so keyboard/monkey don’t trip, underscore counts as a boundary so snake_case names like db_password/access_token DO — widened 2026-08-25 per debt D3, fixture-pinned both directions, AC3) has its value replaced with "[REDACTED]". Keys keep their names (structure stays restorable); values die.
  3. Import side: restoring settings skips absent keys silently and never accepts a settings section containing password-type keys — if one appears (hand-edited artifact), the whole settings section is dropped and a warning surfaced, rather than partially trusting it.
  4. Invariant test (AC13): generated fixture artifact containing known marker strings (hunter2, sk-live-…) greps clean.

Found during study (R1): the existing GET /api/export embeds settingsStore.getAll() raw — all five password values ship in plain text today. Fixing that route is a ~10-line retrofit of the same redaction helper; it belongs in this build’s slice 1 but touches routes/export-routes.js, which the concurrent lane does not own — sequencing in §8 R1/R3.

6. File Plan & Migration Decision

Migration needed: NONE. Decision: snapshots are artifacts, not rows — a registry table would duplicate what the filesystem already provides, add a migration + schema-reference/docs burden, and create a second source of truth. Server-side registry = directory listing of storage/snapshots/*.json (inside the repo tree — same-volume tmp+rename stays atomic on Windows/WSL mounts, §8 R6). Restore idempotency rides the checkpoint files (§4.4), not a table.

File New/Existing Role
lib/snapshot-manifest.js New Pure: buildManifest(rowsByTable, settings, migrationsApplied), validateManifest, compareSchemaVersions(artifactMigrations, targetMigrations) → {verdict:'ok'\|'too_new'\|'target_newer', missing[]}
lib/snapshot-diff.js New Pure: canonicalRowHash(row), classifyRows(artifactRows, currentRows, pkColumn, createdAt) → {added, updated, conflicts, unchanged}
lib/snapshot-redact.js New Pure: redactSettings(getAllOutput) (config-source filter), redactDeep(obj) (deny-regex walk)
routes/snapshot-routes.js New Five endpoints, degradation contract, size cap, checkpoint/resume orchestration, SSE emission
task-server.js Existing, 1 line Route registration (shared-file risk, §8 R3)
src/shell/native-views/settings-view.mjs Existing Snapshots & Restore panel (create/list/download/restore UX, §3) — shared-file risk, no overlap with coder’s current four files
tests/test-snapshot-lib.js New DB-free suites for all three pure libs (AC1–AC4, AC13)
tests/test-snapshot-routes.js New Route fixtures in the test-export-routes.js response-capture style (AC5–AC12)
Docs Existing docs/api-reference-complete.md Snapshots API section + TOC; docs/schema-reference.md gains a note explaining why NO new tables exist; admin-guide backup/restore section

Build sequence: slice 1 = three pure libs + tests (zero shared files, lands anywhere); slice 2 = routes + registration + route tests; slice 3 = settings-view panel + docs. QA gate: qa-auditor tests each slice against these ACs.

7. Explicit Non-Goals (v1)

8. Risks & Open Questions

9. Acceptance Criteria

Consolidated from the pins in §3–§5. qa-auditor tests each build slice against these. AC1–AC4 + AC13 run in tests/test-snapshot-lib.js (DB-free); AC5–AC12 in tests/test-snapshot-routes.js.