-- +goose Up
-- The tenant directory is the CONTROL PLANE catalog that maps each organization to the isolated
-- database that holds its business data, under the database per tenant model (AWS SaaS Lens silo
-- isolation; Azure multitenant catalog pattern). After auth resolves a request to a verified org against
-- the control plane identity store, the router looks the org up here to find its tenant database, then
-- acquires that tenant's own pool. This table is CROSS TENANT by design (it lists every tenant), so it
-- is NOT under the per request org GUC RLS that scopes the data plane: its access control is role based,
-- the migrator owns it and writes it during provisioning, the app role reads it to route. It is created
-- in the shared db/migrations set so the schema stays identical across the control plane and every
-- tenant database (the migration is harmless and empty in a tenant database, the directory is only
-- populated and read in the control plane). This is migration 0020 on the v19 head.
--
-- The directory stores only a REFERENCE to the tenant's database credential (connection_secret_ref), a
-- logical key into the secrets store, NEVER a plaintext password (CLAUDE.md rule 3, keys stay on the
-- server; a directory dump must not be a credential dump). The live credential is resolved at pool open
-- time through internal/platform/secrets.
CREATE TABLE 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(),
    CONSTRAINT uq_tenant_directory_db_name UNIQUE (db_name)
);
CREATE INDEX idx_tenant_directory_status ON tenant_directory (status);

-- The app role ROUTES with this table (read only); the migrator PROVISIONS into it (write). No RLS: a
-- control plane table is cross tenant and must never be scoped to one org by the request GUC.
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;