sc-dev-deploy/docs/managed-rows.md

# dev-deploy managed rows (row-data sync)

By default dev-deploy migrates only metadata (tables, fields, views, pages,
etc.) between environments and never touches the actual rows users entered. The
managed-rows feature is the opt-in extension that also synchronizes row *data*
for tables an admin explicitly marks. It does this with a per-table data mode,
a hidden cross-environment row identity column (`_dd_row_uuid`), row-level ops
in the journal (`insert_row` / `update_row` / `drop_row` / `set_table_mode`),
and UUID-based foreign-key and file translation so a row created on Dev lands
correctly on Test/Prod even though every instance assigns its own integer ids.

See also: [architecture.md](./architecture.md) for the ops journal, stable
entity UUIDs, the wrap/apply layers, and conflict handling;
[peering.md](./peering.md) for the HMAC transport and sync anchors;
[multitenancy.md](./multitenancy.md) for per-tenant isolation.

Code references below are `file:line` into the plugin source.

## Contents

- [Data modes: user / starter / managed](#data-modes-user--starter--managed)
- [The hidden `_dd_row_uuid` column](#the-hidden-_dd_row_uuid-column)
- [Switching a table's data mode](#switching-a-tables-data-mode)
- [Journaling row changes (the wrap layer)](#journaling-row-changes-the-wrap-layer)
- [Applying row ops (the apply layer)](#applying-row-ops-the-apply-layer)
- [Foreign-key handling](#foreign-key-handling)
- [File propagation](#file-propagation)
- [Tables and columns](#tables-and-columns)
- [Endpoints](#endpoints)

## Data modes: user / starter / managed

Each table has a data mode stored in `_dd_table_modes.data_mode`, keyed by the
table's stable UUID. The three valid values are defined in `constants.js`
(`DATA_MODES`): `user`, `starter`, `managed`. A table with no
`_dd_table_modes` row is treated as `user` (the default;
`rowPayload.js`, `rowPayload.js`).

| Mode | When rows sync | Who owns rows after | Intended use |
| --- | --- | --- | --- |
| `user` (default) | Never. Row CRUD passes through silently and is never journaled. | The local environment. | End-user-entered data; the only safe choice for it. |
| `starter` | Once, at the moment the table is switched to `starter` (the "initial ship"). After that, further row changes do not propagate. | The target, after first install. | Default roles, sample categories, template data the user is expected to customize. |
| `managed` | Always. Every insert/update/delete is journaled and replayed; source is canonical. | The source; target edits get overwritten or surface as conflicts. | Catalogs, lookup tables, anything dev-curated. |

The per-table decision of whether a given CRUD operation should be journaled is
made by `journalDecision(tableId)` in `rowPayload.js`:

- `managed` -> always journal (`shouldJournal: true`, `rowPayload.js`).
- `starter` -> journal only while the table has not yet been shipped, i.e.
  `_dd_table_modes.starter_shipped_at` is still NULL (`rowPayload.js`,
  `isStarterShipped` at `rowPayload.js`).
- `user` (or a table with no entity mapping) -> never journal
  (`rowPayload.js`, `rowPayload.js`).

The `users` table is hard-locked to `user` and cannot be changed
(`routes.js`, `routes.js`).

## The hidden `_dd_row_uuid` column

A row's integer `id` is local to one instance and cannot identify the same row
on a peer. The feature therefore adds a hidden TEXT column named `_dd_row_uuid`
(the constant `COLUMN_NAME` in `rowIdentity.js`) to the underlying SQL table
of every `managed` or `starter` table. This column is the row's
cross-environment identity.

Key properties (`rowIdentity.js`):

- It is added by raw `ALTER TABLE ... ADD COLUMN _dd_row_uuid TEXT`
  (`rowIdentity.js`) and is deliberately NOT registered in Saltcorn's
  `_sc_fields`, so Saltcorn's table builder and auto-generated views never
  display it (`rowIdentity.js`).
- Existing rows are backfilled with `crypto.randomUUID()` values immediately
  after the column is added (`rowIdentity.js`).
- A lookup index `"<table>_dd_row_uuid_idx"` is created on the column
  (best-effort; `rowIdentity.js`).
- The SQL table is referenced through `tableSqlRef()`, which schema-qualifies
  via `db.getTenantSchemaPrefix()` and `db.sqlsanitize()` (`rowIdentity.js`),
  so it works inside any tenant.

### SQLite PRAGMA vs Postgres information_schema introspection

`columnExists(tableName)` (`rowIdentity.js`) detects whether the column is
already present, and it branches on the backend:

- SQLite: `PRAGMA table_info("<table>")` and checks whether any returned row has
  `name === "_dd_row_uuid"` (`rowIdentity.js`).
- Postgres: a query against `information_schema.columns` filtered by
  `table_schema = db.getTenantSchema()`, `table_name`, and
  `column_name = "_dd_row_uuid"` (`rowIdentity.js`).

The Postgres path explicitly queries the tenant's own schema rather than relying
on `current_schema()`. The code comment at `rowIdentity.js` documents why:
Saltcorn qualifies queries with `getTenantSchemaPrefix()` instead of
`SET search_path`, so `current_schema()` returns `public` even inside a tenant.
Using it caused `columnExists` to falsely report the column missing on every
call after the first, after which the explicitly-qualified `ALTER` failed with
`column "_dd_row_uuid" already exists` -- breaking apply, which calls
`ensureManagedSchema` once per `set_table_mode` and once per `insert_row`.

`ensureManagedSchema(tableName)` (`rowIdentity.js`) is the idempotent
entry point: if the column already exists it returns `{ added: false }`;
otherwise it adds the column, backfills, indexes, and returns
`{ added: true, backfilled: <n> }`. `dropManagedSchema(tableName)`
(`rowIdentity.js`) reverses it with `ALTER TABLE ... DROP COLUMN` (relies on
SQLite 3.35+ or Postgres; older SQLite throws and the caller treats the drop as
best-effort, `routes.js`).

## Switching a table's data mode

An admin changes a table's mode at the `GET /admin/dev-deploy/tables` page
(`tablesView`, `routes.js`) and submits to
`POST /admin/dev-deploy/tables/set` (`tablesSet`, `routes.js`). The form
warns that switching to managed/starter adds the hidden column and ships current
rows (`routes.js`).

`tablesSet` (`routes.js`) performs, in order:

1. Validates `table_uuid` and that `data_mode` is one of `DATA_MODES`
   (`routes.js`); rejects the `users` table (`routes.js`).
2. Upserts the `_dd_table_modes` row, resetting `starter_shipped_at` to NULL
   (`routes.js`).
3. Journals a `set_table_mode` op FIRST, so a target's apply sees the mode
   change before any row ops (`routes.js`, payload
   `{ table_uuid, data_mode }`).
4. If the new mode is `managed` or `starter`: calls `ensureManagedSchema` on the
   local table (`routes.js`), then does the **initial ship** -- reads every
   row via `allRowsWithUuid` (`rowIdentity.js`), assigns a
   `_dd_row_uuid` to any row missing one (`routes.js`), converts the row to
   its portable form, and journals one `insert_row` op per row with payload
   `{ table_uuid, after: portable }` (`routes.js`).
5. If the new mode is `starter`, calls `markStarterShipped` (`routes.js`,
   `rowPayload.js`) so subsequent CRUD will not journal.
6. If reverting from a managed/starter mode back to `user`, drops the hidden
   column best-effort (`routes.js`).

`managed` tables intentionally do not set `starter_shipped_at`, so they always
journal going forward.

## Journaling row changes (the wrap layer)

Once a table is `managed` (or `starter` and not yet shipped), ongoing row CRUD
is captured by wrapping `Table.prototype.insertRow`, `updateRow`, and
`deleteRows` in `wrapTableRows()` (`wrap.js`). Each wrap:

- Passes through unchanged if journaling is suppressed (i.e. we are inside an
  apply, `wrap.js`) or if `journalDecision` says not to journal
  (`wrap.js`).
- Otherwise records a `table_row` op via `safeJournal` (`wrap.js`), which
  swallows errors so a journaling failure never breaks the user's write.

| CRUD method | Op type | Identity used | Payload key |
| --- | --- | --- | --- |
| `insertRow` (`wrap.js`) | `insert_row` | new `_dd_row_uuid` assigned via `setRowUuid` after insert (`wrap.js`) | `after` (portable row, `wrap.js`) |
| `updateRow` (`wrap.js`) | `update_row` | existing `_dd_row_uuid` read via `getRowUuid` (`wrap.js`); skipped if the row has none | `patch` (portable, `wrap.js`) |
| `deleteRows` (`wrap.js`) | `drop_row` | `_dd_row_uuid` read from each row captured BEFORE deletion (`wrap.js`, `wrap.js`) | `before` (portable, `wrap.js`) |

Every row op payload carries the table's stable UUID as `table_uuid` so the
target can find the corresponding local table. Portable conversion is done by
`rowToPortable` (`rowPayload.js`); see [Foreign-key handling](#foreign-key-handling).

## Applying row ops (the apply layer)

On the receiving instance, the row handlers live in `apply.js` and are dispatched
by op type (`apply.js`). They all resolve the table the same
way -- `findLocalTableByUuid` maps `payload.table_uuid` to the local table via
`_dd_entity_ids` (`apply.js`) -- and use `_dd_row_uuid` to map identity:

| Op | Handler | Behavior |
| --- | --- | --- |
| `insert_row` | `applyInsertRow` (`apply.js`) | `ensureManagedSchema`, then idempotency check via `findIdByRowUuid` (noop if the row UUID already exists, `apply.js`); converts `payload.after` with `portableToRow`, inserts, and stamps the row's `_dd_row_uuid` to the op's `entity_uuid` (`apply.js`). |
| `update_row` | `applyUpdateRow` (`apply.js`) | Finds the local id by row UUID; if absent, treats the update as an insert from `payload.patch` (`apply.js`); otherwise applies the patch via `updateRow` (skips an empty patch, `apply.js`). |
| `drop_row` | `applyDropRow` (`apply.js`) | Noop if the table or the row UUID is not present locally (`apply.js`); otherwise deletes by local id. |
| `set_table_mode` | `applySetTableMode` (`apply.js`) | For `managed`/`starter`, calls `ensureManagedSchema` on the local table (`apply.js`); upserts the local `_dd_table_modes` row (`apply.js`). |

A row's identity (`_dd_row_uuid`) is carried in the op's `entity_uuid` field,
not in the payload; the apply handlers read `op.entity_uuid` as the row UUID
(`apply.js`, `apply.js`). Because apply runs the model methods inside a
suppressed context (`apply.js`, `runSuppressed`), the row CRUD wraps do not
re-journal these changes.

## Foreign-key handling

A row may contain foreign keys whose integer values are meaningless on a peer.
`rowToPortable` (`rowPayload.js`) and `portableToRow` (`rowPayload.js`)
translate FK fields through row UUIDs. For each FK field
(`field.is_fkey && field.reftable_name`), the portable form stores the value
under `"<fieldname>__uuid"` (the `UUID_SUFFIX` constant, `rowPayload.js`) so
it does not collide with the original field name.

On the source (`rowToPortable`, `rowPayload.js`):

| Referenced table's mode | Action |
| --- | --- |
| `managed` or `starter` | Look up the referenced row's `_dd_row_uuid` via `getRowUuid` and store it as `<field>__uuid` (`rowPayload.js`). If the referenced row has no UUID yet, store `null` and attach a warning (`rowPayload.js`). |
| `user` | Cannot translate; store `null` and attach a warning that the FK will be null on the target (`rowPayload.js`). |

On the target (`portableToRow`, `rowPayload.js`):

- If the portable value is `null`, the local field is set to `null`
  (`rowPayload.js`).
- Otherwise, for an FK field, `findIdByRowUuid` resolves the referenced row's
  local id by its `_dd_row_uuid` (`rowPayload.js`). This may itself be
  `null` if the referenced row has not been applied to the target yet.

Both directions skip the `id` and `_dd_row_uuid` columns when building the
portable/local row (`rowPayload.js`, `rowPayload.js`). Warnings collected
on the source side ride along on the journaled op payload (`wrap.js`) so they
can surface in the admin UI.

## File propagation

Row payloads (and metadata payloads) can reference files. dev-deploy normalizes
file references so they survive transport between instances that have different
file-store roots.

Reference translation (`payloadRefs.js`): a recursive walker
(`transformFileRefs`, `payloadRefs.js`) visits payload keys named `fileid`,
`file_id`, `bgFileId`, or `image_id` (`FILE_REF_KEYS`, `payloadRefs.js`).

- On promote, `toPlaceholders` (`payloadRefs.js`) replaces a local file
  reference (numeric id or relative path) with a portable placeholder string
  `"__dd_file_ref::<uuid>"` (`PLACEHOLDER_PREFIX`, `payloadRefs.js`), looking
  the file up in `_dd_entity_ids` by `current_name` or `current_id`
  (`lookupFileByValue`, `payloadRefs.js`).
- On apply, `fromPlaceholders` (`payloadRefs.js`) resolves the placeholder
  back to the target's local relative path (`ent.current_name`). This runs in
  `applyBatch` before the op handler executes (`apply.js`),
  so handlers always see local paths. Writing the path back works because
  Saltcorn's `/files/serve` accepts either a numeric id or a relative path
  (`payloadRefs.js`, `payloadRefs.js`).

Binary transfer (`apply.js` + `routes.js`): the placeholder only carries the
file's identity, not its bytes. The bytes move via a dedicated `create_file`
op and a pull endpoint:

- `applyCreateFile` (`apply.js`) fetches the binary from the source peer
  with a signed `GET /dev-deploy/api/file/<entity_uuid>` request
  (`apply.js`), verifies the SHA-256 against `after.content_hash`
  (`apply.js`; `sha256Buffer` from `files.js`), writes it to the local
  absolute path, and creates the local `File` record (`apply.js`).
- The serving side is `apiFile` (`routes.js`, registered at
  `routes.js`): it requires peer auth, looks the file up in
  `_dd_entity_ids` by `uuid` and `kind = "file"` (`routes.js`), and
  `sendFile`s the bytes.

Tenant-scoped file paths: the absolute path is always reconstructed from the
file store root plus the tenant schema plus the relative serve path. On the
serving side `apiFile` builds it as
`path.join(db.connectObj.file_store, db.getTenantSchema(), mapping.current_name)`
(`routes.js`). On the applying side the same construction lives in
`toAbsolutePath(File, db, relPath)` (`files.js`), which joins
`db.connectObj.file_store`, `db.getTenantSchema()`, and the relative path. The
relative (serve) path is what is transported, because each instance has a
different file-store root (`files.js`); `toRelativePath` uses
`File.absPathToServePath` to produce it (`files.js`). `apiFile` passes
`{ dotfiles: "allow" }` to `sendFile` so paths containing dot-directories such
as `.dev-state` are not silently treated as not-found by Express
(`routes.js`).

## Tables and columns

| Table | Purpose | Key columns |
| --- | --- | --- |
| `_dd_table_modes` (`schema.js`) | One row per table that has a non-default mode. | `table_uuid` (PRIMARY KEY, the table's stable UUID), `data_mode`, `updated_at`, `starter_shipped_at` (`schema.js`). |
| `_dd_entity_ids` (`schema.js`) | Maps stable UUIDs to local integer ids for tables, files, etc. | `uuid`, `kind`, `current_name`, `current_id` (`schema.js`). Used to resolve `table_uuid` and file uuids on apply. |
| `<user table>` | The managed/starter table itself. | Hidden `_dd_row_uuid` TEXT column added by `ensureManagedSchema` (`rowIdentity.js`, `rowIdentity.js`). |

The `_dd_row_uuid` column name is a single source of truth: it is exported as
`COLUMN_NAME` from `rowIdentity.js` and re-imported wherever needed
(for example as `ROW_UUID_COL` in `apply.js`, `wrap.js`, and `routes.js`).

## Endpoints

| Method | Path | Handler | Auth | Role in row sync |
| --- | --- | --- | --- | --- |
| GET | `/admin/dev-deploy/tables` | `tablesView` (`routes.js`) | admin (`routes.js`) | Lists tables and their data modes. |
| POST | `/admin/dev-deploy/tables/set` | `tablesSet` (`routes.js`) | admin (`routes.js`) | Sets a table's data mode; journals `set_table_mode` + the initial-ship `insert_row` ops. |
| GET | `/dev-deploy/api/file/:uuid` | `apiFile` (`routes.js`) | peer HMAC (`noCsrf`, `routes.js`) | Serves a file's bytes to a pulling peer during `create_file` apply. |

The row ops themselves (`insert_row`, `update_row`, `drop_row`,
`set_table_mode`) are not standalone endpoints; they travel inside the normal
journal exchange (the `GET /dev-deploy/api/journal` and
`POST /dev-deploy/api/ingest` flow described in
[peering.md](./peering.md)) and are dispatched by the apply handler table
(`apply.js`).