UmbraTrans/umbratrans_web/migrations/0001_phase1_identity_rollback.sql

-- =====================================================================
-- Rollback for: UmbraTrans phase-1 identity and tenancy migration
--             (users.username, user_credentials, organizations,
--              additive session columns)
-- =====================================================================
--
-- STATUS: PROPOSED / NOT APPLIED / NOT APPROVED
--
-- This file has never been executed against any database.
--
-- DESTRUCTIVE. Read the warnings before running anything.
--
--   * DROP TABLE public.user_credentials DESTROYS every credential row that
--     exists at rollback time. Accounts created or re-enrolled after the
--     forward migration lose their only authentication material and cannot
--     sign in until an operator re-provisions them through the operator
--     path. There is no recovery short of restoring a backup.
--   * DROP TABLE public.organizations DESTROYS every tenant row. The DROP is
--     guarded below so it only fires when the forward migration actually
--     created the table, because the control plane very likely already had
--     one. If organizations pre-existed, its rows are left alone.
--   * DROP CONSTRAINT users_username_key / the backing index removes case
--     insensitive uniqueness. The username VALUES are kept by this rollback;
--     drop the column only if the mapping must be discarded.
--   * Dropping the additive session columns discards MFA, idle deadline, and
--     last-seen history. An application build that still writes those columns
--     will fail after the rollback, so roll the application back first.
--
-- PREREQUISITES
--   [ ] Forward migration 0001 confirmed applied (see its section 5 output)
--   [ ] Fresh pg_dump -Fc backup taken and checksummed AFTER the forward
--       migration, so the post-migration state is recoverable
--   [ ] Restore rehearsed on a scratch database
--   [ ] Application rolled back to a build that does not use the new columns
--   [ ] Maintenance window agreed (the sessions DDL takes ACCESS EXCLUSIVE)
--
-- ---------------------------------------------------------------------
-- 0. Transaction guard rails (same limits as the forward migration)
-- ---------------------------------------------------------------------

BEGIN;

SET LOCAL lock_timeout = '3s';
SET LOCAL statement_timeout = '60s';
SET LOCAL idle_in_transaction_session_timeout = '30s';

-- ---------------------------------------------------------------------
-- 1. Record what will be lost, before it is lost
-- ---------------------------------------------------------------------

-- SELECT count(*) AS credentials_to_be_dropped FROM public.user_credentials;
-- SELECT credential_type, count(*) FROM public.user_credentials GROUP BY 1;
-- SELECT count(*) AS live_sessions FROM public.sessions WHERE revoked_at IS NULL;
-- SELECT count(*) AS organizations FROM public.organizations;

-- ---------------------------------------------------------------------
-- 2. Additive session columns
-- ---------------------------------------------------------------------
-- Drop the indexes first; they are not required to drop the columns, but
-- removing them explicitly keeps the rollback symmetric with the forward file.

DROP INDEX IF EXISTS public.sessions_idle_deadline_at_idx;
DROP INDEX IF EXISTS public.sessions_user_id_idx;

ALTER TABLE public.sessions
    DROP COLUMN IF EXISTS idle_deadline_at,
    DROP COLUMN IF EXISTS mfa_verified_at,
    DROP COLUMN IF EXISTS last_seen_at;

-- ---------------------------------------------------------------------
-- 3. user_credentials
-- ---------------------------------------------------------------------
-- The table and its constraints disappear with it. Any credential that is not
-- recoverable from a backup is gone: re-provision affected accounts through
-- umbratrans-bootstrap-admin (operator path) after the rollback is verified.

DROP TABLE IF EXISTS public.user_credentials;

-- ---------------------------------------------------------------------
-- 4. organizations
-- ---------------------------------------------------------------------
-- Dropped only when this migration created the table. The forward file uses
-- CREATE TABLE IF NOT EXISTS, so in the expected case the control plane owned
-- organizations first and its rows, slug constraint, and dependent projects
-- rows must survive the rollback untouched.
--
-- Drop the second guard's condition to force the drop. Do not do that without
-- a backup: projects.organization_id and user_roles.organization_id reference
-- this table.

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint c
        JOIN pg_class t ON t.oid = c.conrelid
        JOIN pg_namespace n ON n.oid = t.relnamespace
        WHERE n.nspname = 'public'
          AND t.relname = 'organizations'
          AND c.conname = 'organizations_status_check'
          AND pg_get_constraintdef(c.oid) LIKE '%archived%'
    ) THEN
        -- The forward migration's own check constraint is absent, which means
        -- the table was not created by this migration.
        RAISE NOTICE 'organizations was not created by 0001; leaving it in place';
    ELSE
        DROP INDEX IF EXISTS public.organizations_status_idx;
        DROP TABLE IF EXISTS public.organizations;
    END IF;
END
$$;

-- ---------------------------------------------------------------------
-- 5. users.username
-- ---------------------------------------------------------------------
-- Values are preserved by default. Uncomment the second statement only when
-- the mapping must be discarded as well.

ALTER TABLE public.users DROP CONSTRAINT IF EXISTS users_username_key;
DROP INDEX IF EXISTS public.users_username_key;

-- ALTER TABLE public.users DROP COLUMN IF EXISTS username;

-- citext is left installed: dropping an extension can cascade to dependent
-- objects, and an unused extension is harmless. Remove it only in a separate,
-- explicitly reviewed change.

-- ---------------------------------------------------------------------
-- 6. POST-ROLLBACK VERIFICATION
-- ---------------------------------------------------------------------
--
--   \d+ public.users
--   \d+ public.sessions
--
--   SELECT to_regclass('public.user_credentials') AS user_credentials;  -- NULL
--   SELECT count(*) FROM pg_policies WHERE schemaname = 'public'
--     AND tablename = 'user_credentials';                                -- 0
--   SELECT count(*) FROM public.sessions WHERE idle_deadline_at IS NOT NULL;  -- 0
--   -- either organizations is gone, or it is exactly as it was before the
--   -- forward migration; compare against the pre-flight row count
--   SELECT to_regclass('public.organizations') AS organizations;

--   -- the pre-existing schema must be unchanged apart from this rollback
--   SELECT column_name, data_type FROM information_schema.columns
--    WHERE table_schema = 'public' AND table_name IN ('users', 'sessions')
--    ORDER BY table_name, ordinal_position;
--
--   -- re-run the control-plane integrity checks that were captured before the
--   -- forward migration and compare row counts for organizations, projects,
--   -- roles, permissions, user_roles, invitations, and audit_events
--
-- Roll forward again only after a new review of 0001_phase1_identity.sql.

COMMIT;