UmbraTrans/umbratrans_web/migrations/0005_tenant_schemas.sql

-- 0005: one PostgreSQL schema per organization.
--
-- This is §5.0 of Umbratools_2.md: **an organization gets its own schema, and no
-- table of tenant data is shared between organizations.** It replaces shared-tables-
-- plus-RLS because that model was measured to be unenforceable twice over: the
-- application connects as the table owner, so the policies did not apply to it (gate
-- G2), and one of the thirteen `app` functions was PUBLIC-executable until it was
-- closed by hand against one database (defect B4), which means correctness depended on
-- a grant somebody remembered to make.
--
-- With no shared table there is nothing for a policy to fail to protect:
--
--     SELECT email FROM users;   -- (users is platform; but for a tenant table)
--     SELECT name FROM projects; -- ERROR: relation "projects" does not exist
--
-- because in a context with no tenant in hand there is no `projects` relation in the
-- search path at all. The strongest possible mistake now fails.
--
-- ## What moves and what does not
--
-- Four tables move: `projects`, `project_authors`, `project_publishers`,
-- `invitations`. Twenty-six stay in `public`, and the reasoning for each of the
-- thirty is in `docs/tenant-schema-classification.md`. The three that are worth
-- repeating here because they are the ones that look wrong:
--
--   * `user_roles` stays. It is the index that resolves a session to an organization
--     (rule 2 of the classification). Putting it in a tenant schema means finding the
--     tenant requires searching every tenant schema, on the login path.
--   * `authors` and `publishers` stay. Their read policies admit any authenticated
--     principal and their owner column is nullable: they are shared reference data,
--     not one organization's content.
--   * The compute surface stays. A node is not owned by an organization, it serves
--     organizations; `jobs`, `artifacts` and `model_registry` must stay visible to a
--     controller that is not pointed at any one tenant's schema.
--
-- ## What this costs, which the plan did not price
--
-- Three foreign keys point into `projects` from tables that stay in `public`:
-- `artifacts_project_id_fkey`, `jobs_project_id_fkey` and
-- `user_roles_project_id_organization_id_fkey`. A foreign key cannot name a table
-- whose schema depends on the row, so all three are dropped. After this migration
-- nothing in the database refuses a `public.jobs` row that names another tenant's
-- project. The application still refuses it; the server no longer does. That is a real
-- loss and it is recorded rather than papered over.
--
-- ## Shape
--
--   1. `app.tenant_schema_name(uuid)` and `app.provision_tenant_schema()` - the name
--      is derived from the organization id, so it cannot be out of step with a stored
--      column, and provisioning is `CREATE SCHEMA IF NOT EXISTS`.
--   2. A trigger on `public.organizations` so an organization created by any writer,
--      including one added after this migration, has a schema before anything can be
--      written into it.
--   3. The move itself: per organization, create the four tables in its schema and
--      copy that organization's rows out of `public`.
--   4. Drop the three foreign keys described above, then drop the four `public` tables
--      — which is what removes the shared copy, and what removes the policies with it.
--   5. Re-express `app.can_access_project` and `app.is_organization_admin_for_project`,
--      which read `projects.organization_id` and cannot after this.
--
-- Additive where it can be, idempotent throughout: applying this file twice is a
-- no-op, and applying it to a database where it has already run is not an error. It
-- runs in one transaction, so a failure part-way leaves nothing moved.
--
-- Rollback: `0005_tenant_schemas_rollback.sql`. That file runs; it is not a comment
-- block, because a rollback for a data migration that only exists as a comment is a
-- rollback nobody runs.

BEGIN;

-- ---------------------------------------------------------------------------
-- 1. Naming and provisioning a tenant schema
-- ---------------------------------------------------------------------------
-- The name is derived, not stored.
--
-- A stored `organizations.schema_name` column would be one more thing that can
-- disagree with reality, and there is a specific failure for it: an organization
-- whose stored name does not match the schema its rows are in has data that is
-- unreachable by any name the code computes. Deriving the name from the primary key
-- makes that state unrepresentable. The cost is that the derivation exists in two
-- languages, which is why `PostgresAccountRepository.verify_tenant_schema_contract()`
-- compares this function with the Python one at startup and refuses to serve if they
-- differ — the same treatment `app.user_id` gets in `verify_rls_contract`.
CREATE OR REPLACE FUNCTION app.tenant_schema_name(target_organization_id uuid)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $body$
    SELECT 'tenant_' || replace(target_organization_id::text, '-', '')
$body$;

COMMENT ON FUNCTION app.tenant_schema_name(uuid) IS
    'The schema holding one organization''s tables. Derived from the organization id so '
    'it cannot disagree with a stored value. The Python side is '
    'umbratrans_web.tenancy.tenant_schema_name and the two are compared at startup.';

CREATE OR REPLACE FUNCTION app.provision_tenant_schema(target_organization_id uuid)
RETURNS text
LANGUAGE plpgsql
AS $body$
DECLARE
    target_schema text := app.tenant_schema_name(target_organization_id);
BEGIN
    EXECUTE format('CREATE SCHEMA IF NOT EXISTS %I', target_schema);
    -- The four tenant tables, created here rather than by copying a template, so
    -- that this is the only place their shape is written down.
    --
    -- `projects` has no `organization_id`: it is the schema. Everything the shared
    -- table used to say with that column is now said by where the table is, which is
    -- why `UNIQUE (organization_id, slug)` becomes `UNIQUE (slug)` here — identical
    -- inside one organization — and `UNIQUE (id, organization_id)` disappears,
    -- because nothing outside this schema references a project any more.
    EXECUTE format($ddl$
        CREATE TABLE IF NOT EXISTS %I.projects (
            id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
            name text NOT NULL,
            slug public.citext NOT NULL,
            status text NOT NULL DEFAULT 'active'
                CHECK (status IN ('active', 'on_hold', 'released', 'archived')),
            created_by uuid REFERENCES public.users(id),
            created_at timestamptz NOT NULL DEFAULT now(),
            updated_at timestamptz NOT NULL DEFAULT now(),
            -- Inside one organization a slug is unique. This was the meaning of
            -- `UNIQUE (organization_id, slug)` and it is unchanged.
            CONSTRAINT projects_slug_key UNIQUE (slug)
        )
    $ddl$, target_schema);
    EXECUTE format($ddl$
        CREATE TABLE IF NOT EXISTS %I.project_authors (
            project_id uuid NOT NULL REFERENCES %1$I.projects(id) ON DELETE CASCADE,
            author_id uuid NOT NULL REFERENCES public.authors(id),
            credit_role text NOT NULL
                CHECK (credit_role IN ('author', 'translator', 'editor', 'narrator',
                                       'cover_artist', 'illustrator')),
            lang text NOT NULL,
            "position" smallint NOT NULL DEFAULT 1,
            created_at timestamptz NOT NULL DEFAULT now(),
            created_by uuid REFERENCES public.users(id),
            CONSTRAINT project_authors_pkey PRIMARY KEY (project_id, author_id, credit_role, lang)
        )
    $ddl$, target_schema);
    EXECUTE format($ddl$
        CREATE TABLE IF NOT EXISTS %I.project_publishers (
            project_id uuid NOT NULL REFERENCES %1$I.projects(id) ON DELETE CASCADE,
            publisher_id uuid NOT NULL REFERENCES public.publishers(id),
            lang text NOT NULL,
            created_at timestamptz NOT NULL DEFAULT now(),
            created_by uuid REFERENCES public.users(id),
            CONSTRAINT project_publishers_pkey PRIMARY KEY (project_id, publisher_id, lang)
        )
    $ddl$, target_schema);
    EXECUTE format($ddl$
        CREATE TABLE IF NOT EXISTS %I.invitations (
            id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
            -- The project reference was `(project_id, organization_id)` because the
            -- tenant had to be named to be sure. Here the schema names it.
            project_id uuid REFERENCES %1$I.projects(id) ON DELETE CASCADE,
            email public.citext NOT NULL,
            role_id uuid NOT NULL REFERENCES public.roles(id) ON DELETE RESTRICT,
            token_hash text NOT NULL UNIQUE,
            invited_by uuid NOT NULL REFERENCES public.users(id),
            expires_at timestamptz NOT NULL,
            accepted_at timestamptz,
            revoked_at timestamptz,
            created_at timestamptz NOT NULL DEFAULT now()
        )
    $ddl$, target_schema);
    EXECUTE format($ddl$
        CREATE INDEX IF NOT EXISTS invitations_email_idx ON %1$I.invitations (email)
    $ddl$, target_schema);
    EXECUTE format($ddl$
        CREATE UNIQUE INDEX IF NOT EXISTS project_authors_order_idx
            ON %1$I.project_authors (project_id, credit_role, COALESCE(lang, ''), "position")
    $ddl$, target_schema);
    EXECUTE format($ddl$
        CREATE UNIQUE INDEX IF NOT EXISTS project_publishers_once_idx
            ON %1$I.project_publishers (project_id, publisher_id, COALESCE(lang, ''))
    $ddl$, target_schema);
    -- The same trigger the shared table had, so `updated_at` keeps meaning what it
    -- meant. `app.set_updated_at` is unchanged and still in `public`'s reach.
    --
    -- Guarded on the trigger rather than `DROP ... IF EXISTS` then `CREATE`, because a
    -- `DROP IF EXISTS` that finds nothing is a NOTICE, and one NOTICE per organization
    -- buries a real error in a run of a hundred harmless ones.
    IF NOT EXISTS (
        SELECT 1
        FROM pg_trigger
        WHERE tgrelid = format('%I.projects', target_schema)::regclass
          AND tgname = 'projects_set_updated_at'
    ) THEN
        EXECUTE format(
            'CREATE TRIGGER projects_set_updated_at BEFORE UPDATE ON %I.projects '
            'FOR EACH ROW EXECUTE FUNCTION app.set_updated_at()',
            target_schema
        );
    END IF;
    RETURN target_schema;
END
$body$;

-- ---------------------------------------------------------------------------
-- 2. Every organization has a schema, including ones created after this
-- ---------------------------------------------------------------------------
-- Without this, an organization could be inserted by any writer — the console, a
-- script, the next migration — and have no schema, which is not a state in which its
-- data has somewhere to live. The trigger makes the invariant structural: the schema
-- exists in the same transaction as the organization row.
CREATE OR REPLACE FUNCTION app.provision_tenant_schema_for_new_organization()
RETURNS trigger
LANGUAGE plpgsql
AS $body$
BEGIN
    PERFORM app.provision_tenant_schema(NEW.id);
    RETURN NEW;
END
$body$;

DROP TRIGGER IF EXISTS organizations_provision_tenant_schema ON public.organizations;
CREATE TRIGGER organizations_provision_tenant_schema
    AFTER INSERT ON public.organizations
    FOR EACH ROW EXECUTE FUNCTION app.provision_tenant_schema_for_new_organization();

-- ---------------------------------------------------------------------------
-- 3. The move
-- ---------------------------------------------------------------------------
-- Per organization: create the tables, then copy that organization's rows.
--
-- `ON CONFLICT DO NOTHING` on every copy is what makes re-running safe. Without it a
-- second run fails on the primary key, and a migration that cannot be applied twice
-- is one that will be applied once, hopefully.
DO $move$
DECLARE
    organization record;
    target_schema text;
    -- The shared tables are the *source*, and a second run finds them already gone:
    -- section 5 dropped them. Every copy is therefore guarded on its source still
    -- being there, which is what makes applying this file twice a no-op rather than an
    -- error on a migration that has already been applied.
    shared_projects constant boolean := to_regclass('public.projects') IS NOT NULL;
    shared_authors constant boolean := to_regclass('public.project_authors') IS NOT NULL;
    shared_publishers constant boolean := to_regclass('public.project_publishers') IS NOT NULL;
    shared_invitations constant boolean := to_regclass('public.invitations') IS NOT NULL;
BEGIN
    FOR organization IN SELECT id FROM public.organizations ORDER BY id LOOP
        target_schema := app.provision_tenant_schema(organization.id);

        IF shared_projects THEN
            EXECUTE format(
                'INSERT INTO %I.projects (id, name, slug, status, created_by, created_at, updated_at) '
                'SELECT id, name, slug, status, created_by, created_at, updated_at '
                '  FROM public.projects WHERE organization_id = %L '
                'ON CONFLICT DO NOTHING',
                target_schema, organization.id
            );
        END IF;
        IF shared_projects AND shared_authors THEN
            EXECUTE format(
                'INSERT INTO %I.project_authors (project_id, author_id, credit_role, lang, "position", created_at, created_by) '
                'SELECT pa.project_id, pa.author_id, pa.credit_role, pa.lang, pa."position", pa.created_at, pa.created_by '
                '  FROM public.project_authors pa '
                '  JOIN public.projects p ON p.id = pa.project_id '
                ' WHERE p.organization_id = %L '
                'ON CONFLICT DO NOTHING',
                target_schema, organization.id
            );
        END IF;
        IF shared_projects AND shared_publishers THEN
            EXECUTE format(
                'INSERT INTO %I.project_publishers (project_id, publisher_id, lang, created_at, created_by) '
                'SELECT pp.project_id, pp.publisher_id, pp.lang, pp.created_at, pp.created_by '
                '  FROM public.project_publishers pp '
                '  JOIN public.projects p ON p.id = pp.project_id '
                ' WHERE p.organization_id = %L '
                'ON CONFLICT DO NOTHING',
                target_schema, organization.id
            );
        END IF;
        IF shared_invitations THEN
            EXECUTE format(
                'INSERT INTO %I.invitations (id, project_id, email, role_id, token_hash, invited_by, '
                'expires_at, accepted_at, revoked_at, created_at) '
                'SELECT i.id, i.project_id, i.email, i.role_id, i.token_hash, i.invited_by, '
                '       i.expires_at, i.accepted_at, i.revoked_at, i.created_at '
                '  FROM public.invitations i WHERE i.organization_id = %L '
                'ON CONFLICT DO NOTHING',
                target_schema, organization.id
            );
        END IF;
    END LOOP;
END
$move$;

-- ---------------------------------------------------------------------------
-- 4. Verification, before anything is dropped
-- ---------------------------------------------------------------------------
-- A count comparison, and it raises rather than warns. The point of doing this
-- *here* is that after section 5 the shared copy is gone and there is nothing left to
-- compare against: a move that silently lost a row would be discovered by an
-- organization noticing a missing book.
DO $verify$
DECLARE
    tenant record;
    moved bigint;
    source bigint;
BEGIN
    -- Nothing left to compare against once the shared table is gone, which is why this
    -- runs before section 5 and not after it. A re-run returns here.
    IF to_regclass('public.projects') IS NULL THEN
        RETURN;
    END IF;
    FOR tenant IN SELECT o.id AS organization_id, app.tenant_schema_name(o.id) AS schema_name
                     FROM public.organizations o ORDER BY o.id
    LOOP
        CONTINUE WHEN NOT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = tenant.schema_name);
        EXECUTE format('SELECT count(*) FROM %I.projects', tenant.schema_name) INTO moved;
        EXECUTE format(
            'SELECT count(*) FROM public.projects WHERE organization_id = %L',
            tenant.organization_id
        ) INTO source;
        -- `<` and not `<>`: a re-run finds the rows already copied, and a schema that
        -- holds *more* than the shared table is not evidence of a loss.
        IF moved < source THEN
            RAISE EXCEPTION
                'tenant schema % holds % projects but the shared table holds % for organization %;'
                ' refusing to drop the shared copy',
                tenant.schema_name, moved, source, tenant.organization_id;
        END IF;
    END LOOP;
END
$verify$;

-- ---------------------------------------------------------------------------
-- 5. Drop the shared copy
-- ---------------------------------------------------------------------------
-- The three foreign keys first, because they are what makes the four tables
-- undroppable. Each is named so that the reader can see this is a decision and not an
-- oversight.
--
--   artifacts_project_id_fkey              artifacts is platform-level; see the header
--   jobs_project_id_fkey                   jobs is platform-level; see the header
--   user_roles_project_id_organization_id_fkey
--                                          user_roles stays platform-level, so it
--                                          cannot point into a per-tenant schema
ALTER TABLE public.artifacts DROP CONSTRAINT IF EXISTS artifacts_project_id_fkey;
ALTER TABLE public.jobs DROP CONSTRAINT IF EXISTS jobs_project_id_fkey;
ALTER TABLE public.user_roles DROP CONSTRAINT IF EXISTS user_roles_project_id_organization_id_fkey;

-- Now the shared tables. Their row-level-security policies go with the tables, which
-- is the point rather than a side effect: `projects_select` was `app.can_access_project`,
-- and there is no shared `projects` left for it to filter.
--
-- `DROP TABLE` rather than `DROP TABLE ... CASCADE`, deliberately: cascading would
-- silently drop whatever else had grown a dependency, which is exactly the class of
-- change that has to be written down to be reviewed.
DROP TABLE IF EXISTS public.invitations;
DROP TABLE IF EXISTS public.project_authors;
DROP TABLE IF EXISTS public.project_publishers;
DROP TABLE IF EXISTS public.projects;

-- ---------------------------------------------------------------------------
-- 6. The two functions that read a table which no longer exists
-- ---------------------------------------------------------------------------
-- `app.can_access_project` and `app.is_organization_admin_for_project` both resolved a
-- project to an organization by reading `projects.organization_id`. There is no shared
-- `projects` to read.
--
-- Both are re-expressed over `public.user_roles`, which is where a project's
-- organization is still recorded: `user_roles_check` requires a `scope = 'project'`
-- row to carry `organization_id NOT NULL`, so the project-scoped assignment of any
-- project names the tenant that project belongs to.
--
-- The narrowing this causes, stated rather than hidden: a project with no
-- project-scoped assignment at all now resolves to no organization, so an
-- organization administrator who is not assigned to it is not recognised by
-- `app.can_access_project`. Before this migration they were, because the answer came
-- from the project row rather than from an assignment. In practice the service
-- refuses an unassigned project anyway — `assign_account_to_project` creates the
-- assignment — and `app.can_access_project` is no longer used by any policy, because
-- the table it filtered has no policy left to filter.
CREATE OR REPLACE FUNCTION app.is_organization_admin_for_project(target_project_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE SECURITY DEFINER
SET search_path TO 'public', 'pg_temp'
AS $body$
    SELECT app.is_platform_admin()
        OR EXISTS (
            SELECT 1
            FROM public.user_roles theirs
            JOIN public.user_roles mine
              ON mine.organization_id = theirs.organization_id
             AND mine.user_id = app.current_user_id()
             AND mine.scope = 'organization'
             AND mine.status = 'active'
            JOIN public.roles r ON r.id = mine.role_id
            WHERE theirs.project_id = target_project_id
              AND theirs.scope = 'project'
              AND theirs.status = 'active'
              AND r.name = 'organization_admin'
        )
$body$;

CREATE OR REPLACE FUNCTION app.can_access_project(target_project_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE SECURITY DEFINER
SET search_path TO 'public', 'pg_temp'
AS $body$
    SELECT app.is_platform_admin()
        OR EXISTS (
            SELECT 1
            FROM public.user_roles mine
            JOIN public.roles r ON r.id = mine.role_id
            WHERE mine.user_id = app.current_user_id()
              AND mine.scope = 'organization'
              AND mine.status = 'active'
              AND r.name IN ('organization_admin', 'member')
              AND EXISTS (
                  SELECT 1
                  FROM public.user_roles theirs
                  WHERE theirs.project_id = target_project_id
                    AND theirs.scope = 'project'
                    AND theirs.status = 'active'
                    AND theirs.organization_id = mine.organization_id
              )
        )
        OR EXISTS (
            SELECT 1
            FROM public.user_roles mine
            WHERE mine.user_id = app.current_user_id()
              AND mine.project_id = target_project_id
              AND mine.scope = 'project'
              AND mine.status = 'active'
        )
$body$;

-- ---------------------------------------------------------------------------
-- 7. Ledger
-- ---------------------------------------------------------------------------
-- Written last, so a migration that fails above leaves no record that it ran.
INSERT INTO public.schema_migrations (version) VALUES ('0005_tenant_schemas')
ON CONFLICT (version) DO NOTHING;

COMMIT;