# dev-deploy multi-tenancy

dev-deploy runs unchanged inside a multi-tenant Saltcorn instance, where each
tenant lives in its own Postgres schema. The plugin treats every tenant as a
fully independent dev-deploy environment: each tenant has its own `_dd_*`
service tables (schema-qualified), its own `env_id` and journal, and its own set
of peers. Nothing is shared across tenants.

This document covers how that isolation is achieved, the per-tenant installer,
and the Postgres-portability rules the code relies on. Code references are
`file:line` into the plugin source.

See also: [architecture.md](architecture.md) for the core ops-journal /
entity-UUID / wrap / apply model that this builds on.

## Contents

- [Per-tenant schema qualification](#per-tenant-schema-qualification)
- [Tenant-keyed environment identity](#tenant-keyed-environment-identity)
- [The per-tenant installer](#the-per-tenant-installer)
- [Postgres-portability rules](#postgres-portability-rules)
- [Tenant introspection: getTenantSchema vs current_schema](#tenant-introspection-gettenantschema-vs-current_schema)

## Per-tenant schema qualification

On Postgres, Saltcorn places each tenant in its own schema and does NOT use
`SET search_path`; instead it qualifies queries with a schema prefix. dev-deploy
follows the same rule for every raw SQL statement it issues against its own
`_dd_*` tables and against managed user tables.

The prefix comes from `db.getTenantSchemaPrefix()`. On SQLite it is empty; on
Postgres it is `"<schema>".`. Every `CREATE TABLE`, index, and raw query
interpolates it:

- All six `_dd_*` tables are created with `${schema}_dd_...` in `schema.js`
  (`schema.js`, `schema.js`, `schema.js`, `schema.js`,
  `schema.js`, `schema.js`).
- Their indexes are likewise prefixed (`schema.js`, `schema.js`).
- Raw reads in the routes layer build SQL against `${schema}_dd_ops` etc.
  (`routes.js`, `routes.js`, `routes.js`, `routes.js`,
  `routes.js`, `routes.js`, `routes.js`).
- Conflict detection in apply queries `${schema}_dd_ops` (`apply.js`).
- The hidden-column infrastructure qualifies the user table it alters via
  `tableSqlRef()`, which prepends the same prefix (`rowIdentity.js`).

Note: `db.insert` / `db.select` / `db.selectMaybeOne` / `db.updateWhere` /
`db.deleteWhere` take an unqualified logical table name (e.g. `"_dd_ops"`) and
Saltcorn applies the tenant prefix internally. The explicit
`getTenantSchemaPrefix()` interpolation is only needed where the plugin drops to
raw `db.query` SQL.

Because every statement is schema-scoped, a single Node process serving many
tenants reads and writes a different physical `_dd_*` table per tenant with no
code changes.

## Tenant-keyed environment identity

The environment identity (the singleton row in `_dd_env`) is schema-scoped, so a
process serving multiple tenants must not cache one identity row across them.
`env.js` keys its cache by tenant schema rather than using a module-level
singleton (`env.js`).

| Symbol | Behavior | Location |
| --- | --- | --- |
| `cachedEnvByTenant` | `Map` keyed by tenant schema, not a single cached row | `env.js` |
| `tenantKey()` | Returns `db.getTenantSchema()`, or `"public"` if unavailable | `env.js` |
| `getEnv()` | Looks up / populates the cache entry for the current tenant key | `env.js` |
| `refreshEnvCache()` | Deletes only the current tenant's cache entry, then reloads | `env.js` |
| `initEnvIfMissing()` | Inserts a fresh `_dd_env` row with a new `env_id` if none exists, caches it under the current tenant key | `env.js` |
| `markBootstrapped()` | Stamps `bootstrapped_at`; only mutates the cached row if its `env_id` matches | `env.js` |

Each tenant therefore gets:

- its own `env_id` (a fresh v4 UUID generated in `initEnvIfMissing`,
  `env.js`),
- its own `_dd_ops` journal (schema-qualified, above),
- its own `_dd_peers` rows and HMAC pairings.

Two tenants pairing with the same remote base URL are independent: each has a
distinct `env_id`, a distinct shared secret, and a distinct journal, so promote
/ pull between tenants never cross-contaminates.

## The per-tenant installer

The plugin is installed into each tenant schema by
`scripts/installDevDeployTenant.js`, driven by the wrapper
`scripts/installDevDeployTenant.sh`.

Prerequisites (`installDevDeployTenant.sh`): the tenants must already exist
(`saltcorn create-tenant <name>`), and the plugin must already be installed into
the Postgres public schema once via the normal `install-plugin -d ./dev-deploy`
path so the shared `plugins_folder` copy exists.

Usage (from project root, with the PG environment sourced from
`.dev-state-pg/env.sh`):

```
./dev-deploy/scripts/installDevDeployTenant.sh t1 t2     # named tenants
./dev-deploy/scripts/installDevDeployTenant.sh '*'        # all tenants
```

What the script does:

| Step | Detail | Location |
| --- | --- | --- |
| Re-root `@saltcorn/*` | Uses `createRequire` against the Saltcorn checkout's `node_modules` (up two dirs to project root, then `saltcorn/packages/...`) | `installDevDeployTenant.js` |
| Resolve tenants | Empty args or `*` means all tenants, read from `getAllTenants` run inside the default schema | `installDevDeployTenant.js` |
| Init per-tenant State | `init_multi_tenant(Plugin.loadAllPlugins, true, tenants)` so `getState()` resolves inside `runWithTenant`, without running migrations; this also re-runs each tenant's existing plugins' (idempotent) `onLoad` | `installDevDeployTenant.js` |
| Permit local plugin on tenants | Sets root config `tenants_unsafe_plugins = true` | `installDevDeployTenant.js` |
| Install per tenant | For each tenant, `installInto(tenant)` | `installDevDeployTenant.js` |

`installInto` (`installDevDeployTenant.js`) runs inside
`db.runWithTenant(tenant, ...)` and a transaction:

1. Deletes any prior `_sc_plugins` row for `dev-deploy` so it converges on
   exactly one row (no duplicate source of truth) (`installDevDeployTenant.js`).
2. Constructs a `Plugin` with `source: "local"`, `location: DEV_DEPLOY_DIR`, and
   calls `Plugin.loadAndSaveNewPlugin(plugin, true, false)` (`installDevDeployTenant.js`).
3. Verifies against dev-deploy's own table: confirms the `_sc_plugins` row
   exists AND `_dd_env` exists in this tenant's schema (via
   `information_schema.tables` filtered on `db.getTenantSchema()`), so a stale
   plugin row cannot pass; throws if `onLoad` did not run
   (`installDevDeployTenant.js`).

Why this script exists instead of the CLI: the CLI
`install-plugin -t <tenant> -d <dir>` cannot install a local (`-d`) plugin on a
non-root tenant. In this Saltcorn build `loadAndSaveNewPlugin` skips any
non-`npm` plugin on a non-root tenant before its `allowUnsafe` argument is
consulted; the supported lever is the root-only `tenants_unsafe_plugins` config,
which the CLI never sets and this script does
(`installDevDeployTenant.js`, `installDevDeployTenant.js`).

Running `onLoad` per tenant is what creates that tenant's `_dd_*` tables and
bootstraps its `env_id`; because both `createAllTables()` and
`initEnvIfMissing()` are idempotent, re-running the installer is safe.

## Postgres-portability rules

dev-deploy supports both SQLite and Postgres from one DDL/SQL codebase. The
following rules were learned to keep the `_dd_*` tables portable; deviating from
them breaks on Postgres specifically.

### Auto-increment primary key: integer vs serial

SQLite's `integer primary key` auto-assigns rowids; Postgres needs `serial`
(`AUTOINCREMENT` is SQLite-only syntax). The DDL picks the type at runtime:

```
const serial = db.isSQLite ? "integer" : "serial";
```

Used for `_dd_peers.peer_id` (`schema.js`, `schema.js`). `_dd_anchors`
uses `peer_id INTEGER` as a foreign reference, not an auto-increment, so it stays
`INTEGER` (`schema.js`).

### ADD COLUMN IF NOT EXISTS on Postgres to avoid transaction poisoning

For idempotent column migrations on already-installed instances, a bare `ALTER
TABLE ... ADD COLUMN` caught in a JS `try/catch` is NOT enough on Postgres: a
failed statement poisons the surrounding transaction. So the code branches on
`db.isSQLite`:

- SQLite lacks `ADD COLUMN IF NOT EXISTS`, so it runs the bare `ALTER` and
  swallows the "column already exists" error.
- Postgres uses `ADD COLUMN IF NOT EXISTS` so the statement never errors and the
  transaction is never poisoned.

This pattern appears for `_dd_ops.conflict_with_op_id` (`schema.js`) and
`_dd_table_modes.starter_shipped_at` (`schema.js`).

A related case: `createDdOps` creates a partial index with a `WHERE status =
'conflict'` predicate and `.catch(() => {})` (`schema.js`); that swallow is
fine because it is a standalone `db.query`, not part of a migration that must
keep a transaction alive.

### Every `_dd_*` insert needs `{ noid: true }`

The `_dd_*` tables have no column literally named `id` (they use named PKs:
`env_id`, `peer_id`, `uuid`, `op_id`, `table_uuid`, or a composite PK). Saltcorn's
`db.insert` assumes an `id` column unless told otherwise, so every insert into a
`_dd_*` table passes `{ noid: true }`:

| Insert target | Location |
| --- | --- |
| `_dd_env` | `env.js` |
| `_dd_ops` | `apply.js` |
| `_dd_anchors` (inbound/outbound) | `routes.js`, `routes.js` |
| `_dd_table_modes` | `routes.js`, `apply.js` |

(`_dd_peers` and `_dd_entity_ids` inserts live in `peers.js` / `entityIds.js`,
outside the files reviewed here, but follow the same `{ noid: true }` rule by the
same constraint.)

## Tenant introspection: getTenantSchema vs current_schema

NEVER use `current_schema()` to discover which tenant schema you are in.
Because Saltcorn qualifies queries with `getTenantSchemaPrefix()` rather than
issuing `SET search_path`, `current_schema()` returns `"public"` even while
serving inside a tenant. The correct source of truth is `db.getTenantSchema()`.

This bit the hidden-column check in `rowIdentity.js`. The Postgres branch of
`columnExists()` queries `information_schema.columns` filtered by
`table_schema = db.getTenantSchema()` (`rowIdentity.js`, `rowIdentity.js`)
-- the same schema that `tableSqlRef()` / the `ALTER` target use
(`rowIdentity.js`). An earlier version used `current_schema()`, which made the
check falsely report the `_dd_row_uuid` column missing on every call after the
first; the explicitly-qualified `ALTER` then failed with `column
"_dd_row_uuid" already exists`, breaking apply (which calls
`ensureManagedSchema` once per `set_table_mode` and per `insert_row`)
(`rowIdentity.js`).

Consistent introspection sources used throughout:

| Need | Use | Examples |
| --- | --- | --- |
| Qualify raw SQL | `db.getTenantSchemaPrefix()` | `schema.js`, `routes.js`, `rowIdentity.js` |
| Name the current schema (for `information_schema`, file paths) | `db.getTenantSchema()` | `rowIdentity.js`, `installDevDeployTenant.js`, `routes.js` |
| Cache key per tenant | `db.getTenantSchema()` (via `tenantKey()`) | `env.js` |

The same `db.getTenantSchema()` is used to locate per-tenant file storage in
the binary file endpoint: `path.join(file_store, db.getTenantSchema(),
relative_path)` (`routes.js`).