Janady13/missionmatch / db/migrations/0061_tenant_directory.sql

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / db/migrations/0061_tenant_directory.sql
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	db/migrations/0061_tenant_directory.sql
Original SHA-256	7ea6dd72eb816c6af182d02c5f33ae015d211ab4bcbabe76992fddb70912a2e1
Git blob SHA	bf04160b50e29a087c98694fdcce5075d828d8f1
Reading-copy SHA-256	7ea6dd72eb816c6af182d02c5f33ae015d211ab4bcbabe76992fddb70912a2e1

Original source ↗

Every recorded occurrence

These 2 records contain identical original source bytes. Their paths and revisions remain part of the archive.

Original record	Branch or archive	Revision / identifier	Original location
Janady13/missionmatch / db/migrations/0061_tenant_directory.sql	feature/opportunity-to-outcome	720d47fae476b0d38477ae4401697d372b1ea2bc	Open original
Janady13/missionmatch / db/migrations/0061_tenant_directory.sql	feature/aioe-upgrade	2178e7298d0e5d9af83afa12402fd587f0ba6ed8	Open original

Recovered source transcript

Download complete source text ↓73 lines · 4.7 KB

-- +goose Up
-- The tenant directory: the CONTROL PLANE catalog mapping each organization to the database that holds
-- its business data under the database per tenant model (the AWS SaaS Lens silo isolation pattern, and
-- the Azure multitenant catalog pattern).
--
-- Request flow: auth resolves a request to a verified organization against the control plane identity
-- store, the router looks that organization up here to find its tenant database, then acquires that
-- tenant's own pool. Isolation stops being only a WHERE clause enforced by Row Level Security and
-- becomes a physically separate database, with RLS still forced inside it as defense in depth.
--
-- This table is CROSS TENANT by design, since it lists every tenant, so it is deliberately NOT under the
-- per request org GUC that scopes the data plane. Putting it under that GUC would be circular: the
-- router must read this row in order to know which database to open, before any tenant transaction
-- exists to carry a GUC. Its access control is role based instead: the migrator owns it and writes it
-- during provisioning, the application role only reads it to route.
--
-- It is created in the shared migration set so the schema stays identical across the control plane and
-- every tenant database. In a tenant database the table is simply created and left empty, which is
-- harmless: the directory is only populated and read in the control plane.
--
-- The directory stores only a REFERENCE to the tenant's credential (connection_secret_ref), a logical
-- key into the secrets store, and NEVER a plaintext password (CLAUDE.md rule 3: keys stay on the server,
-- and a dump of this table must not be a dump of every tenant credential). The live credential is
-- resolved at pool open time through internal/platform/secrets.
CREATE TABLE IF NOT EXISTS tenant_directory (
    organization_id        uuid        PRIMARY KEY REFERENCES organizations (id),
    db_name                text        NOT NULL,
    db_host                text        NOT NULL,
    db_port                integer     NOT NULL DEFAULT 5432,
    connection_secret_ref  text        NOT NULL,
    status                 text        NOT NULL DEFAULT 'provisioning'
                           CHECK (status IN ('provisioning','active','suspended','migrating','offline')),
    cloud                  text        NOT NULL DEFAULT 'local'
                           CHECK (cloud IN ('local','aws_govcloud','azure_gov')),
    region                 text        NOT NULL DEFAULT '',
    last_migrated_version  bigint,
    last_migrated_at       timestamptz,
    created_at             timestamptz NOT NULL DEFAULT now(),
    updated_at             timestamptz NOT NULL DEFAULT now(),

    -- Two organizations must never resolve to the same physical database, which would silently collapse
    -- the isolation this whole model exists to provide. The identity of a physical database is the
    -- triple of host, port, and name, so that is what carries the uniqueness.
    --
    -- This is deliberately NOT a bare UNIQUE (db_name). A single unique name is simultaneously too
    -- strict and too loose once tenants live on more than one machine: it forbids the same database name
    -- on two different hosts, which is legitimate and expected here because tenants are split across the
    -- M4 cluster and the megamind-agi cluster, while saying nothing about the host that actually
    -- determines which database is being addressed.
    CONSTRAINT uq_tenant_directory_database UNIQUE (db_host, db_port, db_name),

    -- A port is a port. Catching a zero or negative here is cheaper than a confusing connection failure
    -- at pool open time, on a value that is written once at provisioning and read forever after.
    CONSTRAINT tenant_directory_port_chk CHECK (db_port BETWEEN 1 AND 65535),

    -- An empty host or name would produce a DSN that fails only when a request tries to use it.
    CONSTRAINT tenant_directory_host_chk   CHECK (length(btrim(db_host)) > 0),
    CONSTRAINT tenant_directory_dbname_chk CHECK (length(btrim(db_name)) > 0),
    CONSTRAINT tenant_directory_ref_chk    CHECK (length(btrim(connection_secret_ref)) > 0)
);

-- The cross tenant migrator and the operational views filter by status, and the table is read on the
-- routing path, so the status lookup is indexed.
CREATE INDEX IF NOT EXISTS idx_tenant_directory_status ON tenant_directory (status);

-- The application role ROUTES with this table and must never write it; the migrator PROVISIONS into it.
-- No RLS, for the circularity reason described above.
GRANT SELECT                 ON tenant_directory TO missionmatch_app;
GRANT SELECT, INSERT, UPDATE ON tenant_directory TO missionmatch_migrator;

-- +goose Down
DROP TABLE IF EXISTS tenant_directory;