sc-site-sync/README.md

# site-sync

A Saltcorn plugin for **one-way, desired-state deployment**: make a *target* site's
current design state converge to a *source* site's current state. Unlike its sibling
[`dev-deploy`](../dev-deploy) (which records an append-only ops journal and replays
it), site-sync keeps **no edit log** -- it snapshots the two sites' *current* state,
diffs them, previews a plan, and applies the difference. "Just make prod match dev."

## How it works

site-sync leans on Saltcorn's own machinery and adds only what's missing:

1. **Capture** -- a site's desired state is Saltcorn's own
   `backup.create_pack_json(false, true)`: a name-keyed JSON pack of the metadata
   (tables, fields, constraints, views, pages, page groups, triggers, workflow
   steps, roles, tags, libraries, models, code pages) plus an allow-listed config
   map. site-sync
   *sanitizes* it (see Security). Reference-table **row data** travels separately in
   an opt-in manifest.
2. **Diff / plan** -- the target pulls the source's desired state and diffs it against
   its own current state, entity-by-entity (by **name**, except roles which are keyed
   by their numeric id). Present-in-source-only => create, differing => update,
   present-in-target-only => delete (mirror mode). Tables get a nested field- and
   constraint-level diff. Probable TABLE renames are detected by structural signature
   (a view/page rename is planned as create + delete). Code pages (server-side JS) are
   a first-class kind, flagged with a warning on the preview.
3. **Preview** -- the plan is rendered (counts, per-entity changes, and a highlighted
   list of **destructive** operations) and persisted under a `plan_id` with a
   *precondition hash* of the target's current state.
4. **Apply** -- takes the site's apply lock (see "One apply at a time" below),
   atomically claims the plan (a double-submit is refused), re-checks the
   precondition (refuses if the target drifted since preview), snapshots for rollback,
   applies the **renames** the admin confirmed, upserts via `install_pack`, prunes via
   the surgical **delete engine** (constraint removals, model deletes and view
   re-bindings included), then syncs reference rows. Every non-fatal problem is listed
   on the result page. Re-applying an unchanged source is a no-op ("Already in sync").

Identity is **name-based** -- exactly "current state, no log". A table rename with no
journal would look like drop+create; site-sync detects a probable rename structurally,
shows it on the preview, and -- only if the admin ticks it there -- applies a
data-preserving `Table.rename` (on Postgres) instead, carrying the table's data mode
along. An unticked rename is applied as create + (gated) drop.

## Install & pair

Install the plugin, then open **Settings -> Plugins -> site-sync** (the gear bounces
straight to `/admin/site-sync/`).

- **Peers** tab: copy this instance's `env_id` into the other instance's peer form and
  vice-versa; a shared secret is generated once (sealed at rest -- see Security).
  **Rotate** generates a new secret and shows it once; paste it on the other instance
  under **Replace a peer's secret** (until then the pairing cannot authenticate).
- **Dashboard**: set the environment label and policies (below).

Topology is **pull-primary**: the admin on the *target* pulls a *source* peer's state
and previews it locally, so every safety gate runs on the mutating side and never
trusts the sender. A push path (`POST /site-sync/api/plan` + `/apply`) also exists but
remote-triggered apply is gated behind `on_apply=auto`.

## Policies (Dashboard)

| Policy | Default | Meaning |
| --- | --- | --- |
| `sync_mode` | `mirror` | `mirror` prunes entities the source dropped; `additive` never prunes. |
| `on_delete` | `confirm` | Governs **data-bearing** drops (a table with rows, or a field on a populated table): `confirm` drops only the tables whose names the admin **types** into the apply form (never pre-filled) **and** ticks the confirmation; `refuse` never drops them; `auto` drops the typed tables without the tick. Metadata-only drops (0 rows) prune freely under mirror. Table renames need their own tick on the apply form. |
| `on_apply` | `confirm` | Remote apply policy. `confirm` = only the target's admin UI applies (preview, then apply by `plan_id`); `auto` = paired peers may ALSO push-apply remotely (`POST /site-sync/api/apply`, only plans they pushed themselves). A remote apply never drops data-bearing tables/fields and never renames tables. The pull path always previews. |
| `require_tls` | off | Reject inbound peer traffic that didn't arrive over HTTPS (as seen by Express: a TLS socket, or `X-Forwarded-Proto` from a proxy Saltcorn's `trust proxy` trusts). |

## Reference-data (Tables tab)

Row data is opt-in per table. Everything defaults to **user** mode (rows never
touched -- this is the only safe choice for end-user-entered data). Opt a
**reference/lookup** table into:

- **starter** -- rows are seeded once into a fresh table, then the target owns them.
- **managed** -- the source is canonical; rows are mirrored (add / update / remove),
  matched by a stable per-row `_ss_row_uuid` (a registered field, so it survives
  backup/restore; duplicates are re-keyed). Foreign keys are carried as the referenced
  row's uuid and resolved back to a local id, with an insert fixpoint that handles
  required cross-table FKs. Rows are written only when their values changed. A managed
  row the source dropped but that user data on the target still references is KEPT
  (with a warning), never cascaded; prune errors are reported, not fatal.

A starter table is marked shipped only once all of its rows are present.

**File fields** on managed/starter rows sync too, **content-addressed**: the manifest
carries each file's sha256 hash + name + mime, and the target fetches the bytes from
`GET /api/file/:hash` (a signed response), verifies the hash, and writes them into its
own store with `File.from_contents` (a required File field is materialized at insert).
Unchanged files are not re-fetched (dedup by hash); a failed fetch keeps the row's
existing file reference.

The `users` table is **hard-locked** to user mode and can never be flipped.

## Layout & logo files (always-on)

Files referenced *inside* page layouts, view configurations, and the `site_logo_id`
config are synced too -- **always on**, independent of the row data modes. On capture,
each such reference is replaced with a content-hash placeholder (so the diff is
content-based: an unchanged image doesn't churn, a changed one shows the page/logo as
an update). On apply, the target fetches the bytes over the same `/api/file/:hash`
transport, **verifies the hash**, enforces an **image-mime allow-list** (layout refs
are images; this stops a hostile peer planting a file served as HTML), and rewrites the
reference to its local copy -- deduping against files it already has. A materialized
layout image gets the most permissive `min_role` of the pages/views that use it (the
logo is public). This covers the
common cases (`fileid`, `bgFileId`, `image_id`, the site logo). Files embedded by
*other* means -- custom HTML/CSS bodies, theme assets, unrecognized keys -- are not
discovered; deploy those via a shared media store.

## Rollback

Before every apply, site-sync captures a **restore point**: the pre-apply metadata pack
plus exactly the rows the apply can change or destroy -- full rows of tables the delete
gate will drop, the primary key + doomed columns of tables losing fields, and all
managed/starter rows. The apply result page (and the failure page, when an apply broke
part-way) offers **"Rollback this apply"**, which re-syncs the metadata and config to
the pre-apply pack in mirror mode (whatever `sync_mode` says), applies the inverse of
any confirmed renames, removes rows the apply inserted, restores the captured rows by
primary key (no duplication), and un-marks starters the apply seeded. File references
revert for free: apply never deletes old file bytes, so restoring a row's old field
value points it back at the still-present original file. Each apply can be rolled back
at most once, and never while it is still in flight.

## One apply at a time

Only one apply or rollback runs on a site at a time -- across every worker process and
every server sharing the database, and including a peer's remote push-apply. Another
started meanwhile is refused (not queued), with what is running and since when. Each
tenant has its own lock.

The lock is held under a 60-second **lease** that the running apply renews every 15
seconds, timed by the database's clock (so servers whose clocks disagree still agree on
it). If the process running an apply or rollback dies -- crash, kill, restart -- its
lease lapses within a minute and the plan it left **applying** / **rolling back** is
reported as **interrupted**: on the dashboard (with a **Rollback this apply** button
when it has a restore point), in `sitesync_status` and `sitesync_get_plan`. The next
apply or rollback on the site takes the lapsed lock over and returns every interrupted
plan to the status it had before (an interrupted apply is pending again, so it can be
rolled back from its restore point or applied again; an interrupted rollback can be
retried), naming each in its warnings. An apply that stalls longer than its lease (the
lock taken over meanwhile) stops at its next phase and leaves the plan to the new
holder.

## Security model

Because a desired-state apply *overwrites and prunes* (a strictly larger blast radius
than an append-only journal), the trust boundary is enforced on the **target** side,
which never trusts a sender:

- **Admin gate**: every admin route requires an admin session created on **this**
  tenant (upstream `isAdmin` semantics). Saltcorn's Postgres session store is shared by
  all tenants, so another tenant's admin replaying their cookie here is refused.
- **Transport**: HMAC-SHA256 signed requests (timestamp + nonce + method + path +
  target-host + body-hash), with a +/-5-minute skew window, a nonce-replay store,
  host-binding (a request signed for tenant *t1* can't be replayed at *t2*; the host is
  Express's `req.host`, never a raw client-set `X-Forwarded-Host`), a request-body cap
  enforced *before* the signature check, and an optional TLS requirement. **Every
  response is signed too** (`X-SS-Response-Signature` over the request's nonce, the
  status and the body bytes) and the client verifies it -- and that the responder is
  the paired `env_id` -- before using anything it pulled, so an on-path attacker on an
  `http://` link cannot substitute a pack or a file. Outbound calls are bounded by a
  timeout and a response-size cap.
- **Sealed secrets**: peer secrets are sealed AES-256-GCM (full 16-byte tag) under a key
  derived from Saltcorn's `session_secret`, which must be set explicitly
  (`SALTCORN_SESSION_SECRET` or `session_secret` in the Saltcorn config file);
  Saltcorn's generated fallback secret is refused. Never stored in the DB.
- **Config allow-list**: only `menu_items`, `site_name`, `site_logo_id` can be
  converged. `install_pack`'s `setConfig` clobbers unconditionally, so this list is
  the sole guard against a compromised source pushing security config
  (`disable_csrf_routes`, headers, SMTP, ...).
- **Infra & locked tables**: tables AND fields whose name begins with `_` (plugin/system
  bookkeeping such as `_dd_*`, `_idp_*`, `_ss_*`, `_ss_row_uuid`) and the `users` table
  are excluded from the pack entirely (never synced, diffed, or offered a data mode),
  so a hostile peer can neither create/mutate them nor (under mirror) prune another
  plugin's tables.
- **Plugins**: presence + version **parity check only** -- plugin configuration and the
  secret `deploy_private_key` are stripped; a mismatch is a pre-flight warning, never
  a mid-apply action.
- **Data-loss gates**: row counts are re-counted **live at apply time** (not trusted
  from the preview), and remote push-apply can never satisfy the human data-drop
  confirmation, nor apply a plan another peer authored.

## HTTP endpoints

Admin (admin session of this tenant; POSTs CSRF-protected): `GET /admin/site-sync/`,
`POST .../settings`, `GET .../peers`, `POST .../peers/add`, `POST .../peers/rotate`
(generate, or install a pasted secret), `POST .../peers/delete`, `GET .../plan` (choose a
peer; read-only), `POST .../plan` (pull + preview; persists a plan), `POST .../apply`,
`POST .../rollback`, `GET .../tables`, `POST .../tables/set`. The same operations are
offered to MCP clients as tools (see MCP tools).

Machine (HMAC peer auth, CSRF-exempt, signed responses): `GET /site-sync/api/health`,
`GET /site-sync/api/state`, `GET /site-sync/api/file/:hash`, `POST /site-sync/api/plan`,
`POST /site-sync/api/apply`.

## MCP tools

With the project's [saltcorn-mcp](../mcp) plugin installed on the same site, an MCP
client (an AI assistant holding a scoped MCP token) can drive site-sync the way an
admin drives the UI: the plugin exports `mcp_tools` (contract:
[`mcp/docs/pluginTools.md`](../mcp/docs/pluginTools.md)). The tools are thin adapters
over the same operations the admin routes use (`lib/operations.js`), so every rule
(settings validation, data-drop gates, the drift check, the users-table lock) applies
identically. Peers calling peers (the machine API) are never tools, and there is no
push: pulling is the path.

| Tool | Scope | What it does |
| --- | --- | --- |
| `sitesync_status` | read | env_id, label, policies, version, peer count, managed / starter table counts, whether the session secret is configured. |
| `sitesync_list_peers` | read | Paired peers (never their secrets) and this site's env_id. |
| `sitesync_peer_health` | read | Signed health call to a peer: reachable / authenticated, version, env_id, plugin-parity warnings. Talks to the peer. |
| `sitesync_list_tables` | read | Every application table: rows, data mode, locked, starter_shipped_at. |
| `sitesync_list_plans` | read | Recent plans (filter by status): peer, direction, has_restore_point, can_apply, can_rollback, stale. |
| `sitesync_get_plan` | read | A plan, trimmed: summary, entity names per kind and change, destructive items, rename candidates, the tables an apply must name to drop data and whether the acknowledgement is needed, config changes, row counts, warnings, standing. With `entity: {kind, name}`: one entity's source and current definition, secret-looking keys masked. |
| `sitesync_local_state` | read | This site's desired-state hash, entity counts per kind, syncable config present, shipped row counts (read-only). |
| `sitesync_plan_pull` | write | Pull a peer's state and record a plan (changes nothing on either site); returns the `get_plan` view. Talks to the peer. |
| `sitesync_apply_plan` | admin | Apply a plan (`confirm_renames`, `prunable`, `confirm_data_drops` are the UI's gates; never forced). Installs code pages, i.e. server-side JavaScript. Destructive. |
| `sitesync_rollback` | admin | Revert an applied plan to its restore point (once). Destructive. |
| `sitesync_update_settings` | admin | Partial, validated change of `env_label`, `sync_mode`, `on_delete`, `on_apply` (auto lets peers apply remotely), `require_tls`. |
| `sitesync_add_peer` | admin | Pair with a peer; a generated shared secret is returned ONCE (as the UI shows it once), a given one never. |
| `sitesync_rotate_peer_secret` | admin | Generate (returned once) or install the peer's new secret. |
| `sitesync_delete_peer` | admin | Unpair; the peer's plans go, except applied plans that can still be rolled back. Destructive. |
| `sitesync_set_table_mode` | admin | user / starter / managed for a table (validated; users and `_` tables refused). |

"peer" arguments take a peer's id, env_id or label. A stored plan holds the source's
full configurations and managed rows, so plan views never include rows and show
definitions only one entity at a time, through the server's redaction. Only one apply
or rollback runs on a site at a time (across every server process; see "One apply at a
time"); a pull or apply can take minutes.

## Testing

```
./test/run.sh               # full in-process suite against a throwaway SQLite DB
./test/run.sh coreUnit.js   # core (capture/diff/apply/rows/rollback) regression checks
./test/runPg.sh             # the same suite against a throwaway Postgres DB
./test/runPg.sh pgGate.js   # multi-tenant isolation gate (two real tenants)
./test/run.sh mcpTools.js   # the MCP tools against a signed mock peer (also runPg.sh)
./test/run.sh applyLock.js  # the apply lock: worker processes racing, one SIGKILLed mid-apply (also runPg.sh: + per-tenant locks)
node test/compatVectors.js  # recorded vectors: peer secrets + signatures from earlier versions still work
node test/registeredTableTest.js  # lib/common/registeredTable.js on a throwaway DB (RT_TEST_PG=1: Postgres)
```

`coreUnit.js` ends by running `test/compatVectors.js` (the vectors in
`test/compatVectors.json` were recorded from earlier code with a fake session
secret; never regenerate them).

```
./test/runLivePull.sh   # live pull over real HTTP between two throwaway instances
```

The in-process runners spin up an **isolated** throwaway database (never touching
other instances), register the plugin by location so its real `onLoad` runs, and
keep every byte of runner state -- database, file store, sessions, and the
plugins folder Saltcorn copies a by-location plugin into -- in a `mktemp` directory
outside the plugin tree (`test/testEnv.sh`), so no test state can leak into an
installed copy of the plugin. They drive the real modules + route handlers. HMAC auth
is exercised both with async-iterable mock requests and over **real HTTP** (an Express app mounting the
plugin's own API routes, plus a deliberately tampering server for the client-side
response checks); convergence is tested by "capture desired state -> perturb ->
converge back". `runLivePull.sh` runs the **source as a real `saltcorn serve`** process
and pulls it in-process over the wire. Runners exit non-zero on any failure;
`runPg.sh` exits 77 with `SKIPPED (NOT green)` when Postgres is unreachable.

## Limitations / follow-ups

- **Renames on SQLite** fall back to drop+create (SQLite can't `ALTER ... RENAME`); the
  data-bearing old table is *retained* by the delete gate, so no data is lost. On
  Postgres the rename is applied in place.
- **Rollback of a managed-row prune** cannot bring back a file whose bytes were deleted
  with it (a File field configured `also_delete_file`); the row comes back, the file
  does not.

See [`docs/architecture.md`](./docs/architecture.md) for the module map and internals.