UmbraTrans/umbratrans_web/migrations/0005_tenant_schemas_rollback.sql

-- 0005 rollback: put the four tenant tables back in `public`, one shared table again.
--
-- This file **runs**. It is not the commented-out sketch at the end of a
-- migration, because a rollback for a data migration that exists only as a comment
-- is a rollback nobody runs.
--
--     psql -d <database> -v ON_ERROR_STOP=1 -f 0005_tenant_schemas_rollback.sql
--
-- It undoes 0005 completely:
--
--   1. recreate `public.projects`, `public.project_authors`,
--      `public.project_publishers` and `public.invitations` with their
--      `organization_id` columns, constraints, indexes, policies and trigger;
--   2. copy every organization's rows back, recovering `organization_id` from the
--      schema name - the name was derived from the identifier, so this is exact and
--      not a guess;
--   3. restore the three foreign keys that pointed into `projects`, and the two
--      `app` functions that read it;
--   4. drop the tenant schemas, the trigger and the two provisioning functions;
--   5. remove the ledger row.
--
-- ## What it cannot restore
--
-- Three things, stated rather than discovered afterwards:
--
--   * **Rows written into a tenant schema since the migration.** They are copied
--     back, so they survive; but a row whose `created_by` names a user deleted in
--     the meantime will fail the foreign key and stop the copy. That is the
--     constraint doing its job, and the fix is to resolve the user, not to drop the
--     constraint.
--   * **The column order and any constraint added by a later migration** that
--     referenced the tenant tables. This rollback is written against 0001-0005 and
--     nothing after them.
--   * **The gate it reopens.** The shared tables have no `organization_id`
--     filtering unless the policies below are enforced, and they are not enforced
--     for the table owner, which is the application. Rolling back returns the
--     deployment to gate **G2** and to shared-tables-plus-RLS, both of which §5.0
--     records as measured failures. Rolling forward is the direction; this file
--     exists so that the decision is reversible, not because it is better.

BEGIN;

-- ---------------------------------------------------------------------------
-- 1. The shared tables, as they were
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.projects (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id uuid NOT NULL REFERENCES public.organizations(id),
    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(),
    -- Referenced by `invitations` and `user_roles` as `(project_id,
    -- organization_id)`, so the pair has to be unique for those foreign keys to
    -- exist again.
    CONSTRAINT projects_id_organization_id_key UNIQUE (id, organization_id),
    CONSTRAINT projects_organization_id_slug_key UNIQUE (organization_id, slug)
);

CREATE TABLE IF NOT EXISTS public.project_authors (
    project_id uuid NOT NULL REFERENCES public.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)
);

CREATE TABLE IF NOT EXISTS public.project_publishers (
    project_id uuid NOT NULL REFERENCES public.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)
);

CREATE TABLE IF NOT EXISTS public.invitations (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id uuid NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
    project_id uuid,
    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(),
    CONSTRAINT invitations_project_id_organization_id_fkey
        FOREIGN KEY (project_id, organization_id)
        REFERENCES public.projects(id, organization_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS projects_organization_idx ON public.projects (organization_id);
CREATE INDEX IF NOT EXISTS invitations_email_idx ON public.invitations (email);
CREATE UNIQUE INDEX IF NOT EXISTS project_authors_order_idx
    ON public.project_authors (project_id, credit_role, COALESCE(lang, ''), "position");
CREATE UNIQUE INDEX IF NOT EXISTS project_publishers_once_idx
    ON public.project_publishers (project_id, publisher_id, COALESCE(lang, ''));

-- ---------------------------------------------------------------------------
-- 2. The rows, back into the shared tables
-- ---------------------------------------------------------------------------
-- `organization_id` is recovered by parsing the schema name, which is the
-- definition `app.tenant_schema_name` used to build it. That is why the name was
-- derived rather than stored: a rollback can rebuild the column from the schema,
-- and a stored name could have disagreed with the rows.
DO $restore$
DECLARE
    tenant record;
    organization_id uuid;
BEGIN
    FOR tenant IN SELECT nspname FROM pg_namespace WHERE nspname LIKE 'tenant\_%' ORDER BY nspname LOOP
        BEGIN
            organization_id := substring(tenant.nspname FROM 8)::uuid;
        EXCEPTION WHEN others THEN
            -- A schema that matches the pattern but is not one of ours. Skipped
            -- loudly rather than dropped: something else made it, and guessing at
            -- its contents is how a rollback destroys data.
            RAISE WARNING 'skipping %: its name is not an organization identifier', tenant.nspname;
            CONTINUE;
        END;

        EXECUTE format(
            'INSERT INTO public.projects (id, organization_id, name, slug, status, created_by, created_at, updated_at) '
            'SELECT id, %L, name, slug, status, created_by, created_at, updated_at FROM %I.projects '
            'ON CONFLICT DO NOTHING',
            organization_id, tenant.nspname
        );
        EXECUTE format(
            'INSERT INTO public.project_authors (project_id, author_id, credit_role, lang, "position", created_at, created_by) '
            'SELECT project_id, author_id, credit_role, lang, "position", created_at, created_by FROM %I.project_authors '
            'ON CONFLICT DO NOTHING',
            tenant.nspname
        );
        EXECUTE format(
            'INSERT INTO public.project_publishers (project_id, publisher_id, lang, created_at, created_by) '
            'SELECT project_id, publisher_id, lang, created_at, created_by FROM %I.project_publishers '
            'ON CONFLICT DO NOTHING',
            tenant.nspname
        );
        EXECUTE format(
            'INSERT INTO public.invitations (id, organization_id, project_id, email, role_id, token_hash, invited_by, '
            'expires_at, accepted_at, revoked_at, created_at) '
            'SELECT id, %L, project_id, email, role_id, token_hash, invited_by, expires_at, accepted_at, revoked_at, created_at '
            '  FROM %I.invitations ON CONFLICT DO NOTHING',
            organization_id, tenant.nspname
        );
    END LOOP;
END
$restore$;

-- ---------------------------------------------------------------------------
-- 3. The policies, and the trigger
-- ---------------------------------------------------------------------------
-- Restored verbatim from the 0001-0004 schema. They are not decoration: without
-- them the four tables sit in `public` with no tenant filter at all, which is
-- strictly worse than either state - the tables are shared *and* unprotected.
ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS projects_select ON public.projects;
CREATE POLICY projects_select ON public.projects FOR SELECT USING (app.can_access_project(id));
DROP POLICY IF EXISTS projects_insert ON public.projects;
CREATE POLICY projects_insert ON public.projects FOR INSERT
    WITH CHECK (app.is_platform_admin() OR app.is_organization_admin(organization_id));
DROP POLICY IF EXISTS projects_update ON public.projects;
CREATE POLICY projects_update ON public.projects FOR UPDATE
    USING (app.is_platform_admin() OR app.is_organization_admin(organization_id))
    WITH CHECK (app.is_platform_admin() OR app.is_organization_admin(organization_id));
DROP POLICY IF EXISTS projects_delete ON public.projects;
CREATE POLICY projects_delete ON public.projects FOR DELETE
    USING (app.is_platform_admin() OR app.is_organization_admin(organization_id));

ALTER TABLE public.project_authors ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS project_authors_tenant ON public.project_authors;
CREATE POLICY project_authors_tenant ON public.project_authors
    USING (app.is_platform_admin() OR EXISTS (
        SELECT 1 FROM public.projects p
        WHERE p.id = project_authors.project_id
          AND app.is_organization_member(p.organization_id)));

ALTER TABLE public.project_publishers ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS project_publishers_tenant ON public.project_publishers;
CREATE POLICY project_publishers_tenant ON public.project_publishers
    USING (app.is_platform_admin() OR EXISTS (
        SELECT 1 FROM public.projects p
        WHERE p.id = project_publishers.project_id
          AND app.is_organization_member(p.organization_id)));

ALTER TABLE public.invitations ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS invitations_manage ON public.invitations;
CREATE POLICY invitations_manage ON public.invitations
    USING (app.is_platform_admin() OR app.is_organization_admin(organization_id))
    WITH CHECK (app.is_platform_admin() OR app.is_organization_admin(organization_id));
DROP POLICY IF EXISTS invitations_select ON public.invitations;
CREATE POLICY invitations_select ON public.invitations FOR SELECT USING (
    app.is_platform_admin()
    OR app.is_organization_admin(organization_id)
    OR (email = (SELECT u.email FROM public.users u WHERE u.id = app.current_user_id())));

DO $$
BEGIN
    EXECUTE 'DROP TRIGGER IF EXISTS projects_set_updated_at ON public.projects';
    EXECUTE 'CREATE TRIGGER projects_set_updated_at BEFORE UPDATE ON public.projects '
            'FOR EACH ROW EXECUTE FUNCTION app.set_updated_at()';
END
$$;

-- ---------------------------------------------------------------------------
-- 4. The foreign keys and functions that 0005 removed
-- ---------------------------------------------------------------------------
-- Guarded on the catalogue rather than written plainly, because PostgreSQL has no
-- `ADD CONSTRAINT IF NOT EXISTS`. It is not decoration: every other statement in
-- this file is safe to run twice, and a rollback that fails on the second attempt
-- is one that cannot be used to recover from a failure part-way through itself -
-- and a rollback of a per-tenant migration on a database with a few hundred
-- tenants is exactly where that happens.
DO $keys$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'artifacts_project_id_fkey') THEN
        EXECUTE 'ALTER TABLE public.artifacts ADD CONSTRAINT artifacts_project_id_fkey '
                'FOREIGN KEY (project_id) REFERENCES public.projects(id) ON DELETE CASCADE';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'jobs_project_id_fkey') THEN
        EXECUTE 'ALTER TABLE public.jobs ADD CONSTRAINT jobs_project_id_fkey '
                'FOREIGN KEY (project_id) REFERENCES public.projects(id)';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'user_roles_project_id_organization_id_fkey') THEN
        EXECUTE 'ALTER TABLE public.user_roles ADD CONSTRAINT user_roles_project_id_organization_id_fkey '
                'FOREIGN KEY (project_id, organization_id) REFERENCES public.projects(id, organization_id) '
                'ON DELETE CASCADE';
    END IF;
END
$keys$;

-- The original bodies, which read `projects.organization_id` and could not survive
-- the table leaving `public`. Restored verbatim so that a rolled-back database is
-- byte-for-byte the schema 0005 found rather than an approximation of it.
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 projects p
            WHERE p.id = target_project_id
              AND app.is_organization_admin(p.organization_id)
        )
        OR EXISTS (
            SELECT 1
            FROM user_roles ur
            WHERE ur.user_id = app.current_user_id()
              AND ur.project_id = target_project_id
              AND ur.scope = 'project'
              AND ur.status = 'active'
        )
$body$;

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 projects p
            WHERE p.id = target_project_id
              AND app.is_organization_admin(p.organization_id)
        )
$body$;

-- ---------------------------------------------------------------------------
-- 5. The tenant schemas, the trigger, the functions, the ledger row
-- ---------------------------------------------------------------------------
-- Dropped last, one schema per statement, because each of the four tables in a
-- schema holds a foreign key and a few hundred of them in one transaction will
-- exhaust the lock table - which is a failure that looks like a PostgreSQL defect
-- rather than a rollback that got too big.
--
-- When it does happen, the answer is to **run this file again**. Every statement
-- above it is safe to run twice, the constraints are guarded on the catalogue
-- precisely so that a re-run does not stop at the first one, and the schema drops
-- use `IF EXISTS`. So the recovery is another run, not a hand-written fix.
DO $drop$
DECLARE
    tenant text;
BEGIN
    FOR tenant IN SELECT nspname FROM pg_namespace WHERE nspname LIKE 'tenant\_%' LOOP
        EXECUTE format('DROP SCHEMA IF EXISTS %I CASCADE', tenant);
    END LOOP;
END
$drop$;

DROP TRIGGER IF EXISTS organizations_provision_tenant_schema ON public.organizations;
DROP FUNCTION IF EXISTS app.provision_tenant_schema_for_new_organization();
DROP FUNCTION IF EXISTS app.provision_tenant_schema(uuid);
COMMENT ON FUNCTION app.tenant_schema_name(uuid) IS NULL;

DELETE FROM public.schema_migrations WHERE version = '0005_tenant_schemas';

COMMIT;