sc-dev-deploy/docs/multitenancy.md

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 for the core ops-journal / entity-UUID / wrap / apply model that this builds on.

Contents

• Per-tenant schema qualification
• Tenant-keyed environment identity
• The per-tenant installer
• Postgres-portability rules
• 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).