Phase A migration runbook

Phase A introduces five master-DB migrations. They are additive and safe to apply on a live system: no existing column is dropped, no existing constraint is loosened, and the only data-mutation step is an idempotent backfill of tenant_memberships.

Migrations in this phase

#	File	What it does
108	108_create_tenant_memberships.sql	New table for operator memberships. Enum-checked status / membership_type / source. Composite FK to users.
109	109_create_tenant_end_user_states.sql	New table for end-user (consumer) state. Primary key (tenant_id, user_id).
110	110_create_user_groups.sql	The missing user_groups table. Composite PK (tenant_id, user_id, group_id).
111	111_alter_role_bindings_add_group_id.sql	Adds group_id to role_bindings; replaces the principal CHECK constraint to require exactly one of user_id / group_id / service_account_id.
112	112_backfill_tenant_memberships.sql	One tenant_memberships row per existing user (status mirrors users.active, type=member, source=migration).

Apply order

The runner sorts by version number, so just drop the files into migrations/master/ and run:

POST /authsec-migration/migrations/master/run

(Or restart the service if you've configured boot-time migration.)

Pre-flight checks

Before applying:

1. No write traffic to role_bindings during the ALTER. Migration 111 drops and recreates the check_principal constraint; concurrent INSERTs racing with the recreation may see a transient constraint violation. Run during a low-traffic window or wrap in your own transaction guard.
2. groups has tenant_id. Migration 019 added it. If you're on a fork older than migration 019, apply that first.
3. Verify users(tenant_id, id) is unique. Migration 054 added this composite key; the new tenant_memberships and user_groups tables depend on it.

Post-flight verification

-- Row counts
SELECT 'tenant_memberships' AS table, COUNT(*) FROM tenant_memberships
UNION ALL SELECT 'tenant_end_user_states', COUNT(*) FROM tenant_end_user_states
UNION ALL SELECT 'user_groups',            COUNT(*) FROM user_groups
UNION ALL SELECT 'role_bindings (group_id NOT NULL)',
                 COUNT(*) FROM role_bindings WHERE group_id IS NOT NULL;

-- Confirm the new check_principal CHECK is in place
SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'role_bindings'::regclass AND conname = 'check_principal';
-- expected:
-- check_principal | CHECK (((user_id IS NOT NULL)::int + (group_id IS NOT NULL)::int + (service_account_id IS NOT NULL)::int) = 1)

-- Confirm every existing user has a membership
SELECT u.id
FROM users u LEFT JOIN tenant_memberships tm
  ON tm.tenant_id = u.tenant_id AND tm.user_id = u.id
WHERE u.deleted_at IS NULL AND tm.user_id IS NULL;
-- expected: 0 rows

Rollback

The migrations are non-destructive, but rollback is possible:

-- 112: nothing to undo (data-only backfill)
DELETE FROM tenant_memberships WHERE source = 'migration';

-- 111: restore the original principal CHECK and drop group_id
ALTER TABLE role_bindings DROP CONSTRAINT check_principal;
ALTER TABLE role_bindings ADD CONSTRAINT check_principal CHECK (
  (user_id IS NOT NULL AND service_account_id IS NULL) OR
  (user_id IS NULL AND service_account_id IS NOT NULL)
);
ALTER TABLE role_bindings DROP CONSTRAINT fk_rb_group;
ALTER TABLE role_bindings DROP COLUMN group_id;

-- 110, 109, 108: drop the tables
DROP TABLE user_groups;
DROP TABLE tenant_end_user_states;
DROP TABLE tenant_memberships;

This will revert membership-status preflight checks; any session relying on tenant_memberships.status='active' will fail open (i.e. proceed without that gate). The scope_resolver and PDP changes need to be reverted in code alongside.

What changes for clients

• Admin UIs hitting the new /uflow/v2/... endpoints get real data immediately after migration 112 backfill.
• Existing /uflow/admin/... endpoints are unchanged.
• Existing role_bindings with user_id or service_account_id continue to work — the new group_id column is nullable and only adds capability.

Known limitation

The Phase A backfill marks every existing user as a member. In reality some of them are end users (consumers) of your published Applications. Phase D's EndUser unification migrates them out of tenant_memberships and into tenant_end_user_states. Until then, expect every user to show up in both Settings → Team (Members) and (after their first new consent) in End Users.