IAM/Profile UUID Cutover Design

Status: additive UUID shadow-column migrations, database-enforced dual-write triggers, UUID primary-key readiness views, owner-local UUID foreign-key validation constraints, and UUID primary-key promotion migrations are implemented. Public ids remain string-compatible, and default route promotion still requires production-like target validation plus browser/rollback proof.

Purpose

The pasted IAM/Profile requirements call for UUID primary keys. The Go platform keeps public id fields as strings for legacy/backfill compatibility, but the promoted IAM/Profile target schemas now enforce service-owned PostgreSQL primary keys on UUID columns.

This document is the migration design for editing existing IAM/Profile primary keys while preserving text ids for public route contracts and backfill upserts.

Non-Goals

• Do not recreate auth-service; identity work stays in iam-service.
• Do not remove deprecated user-service until default route cutover and backfill evidence are complete.
• Do not add cross-database foreign keys between Profile and IAM.
• Do not change public OpenAPI id fields from string to a non-string type.
• Do not rewrite legacy ids in the legacy database.

Current Schema

IAM text primary keys and references:

• identities.id
• identity_memberships.id, identity_memberships.user_id
• refresh_tokens.id, refresh_tokens.user_id
• roles.id
• permissions.id
• role_permissions.role_id, role_permissions.permission_id
• user_roles.account_id, user_roles.role_id
• organizations.id
• organization_members.id, organization_members.organization_id, organization_members.account_id
• sessions.id, sessions.account_id, sessions.refresh_token_id
• invites.id, invites.organization_id, invites.accepted_by_account_id, invites.revoked_by_account_id
• parent_student_links.id, parent_student_links.parent_account_id, parent_student_links.student_account_id
• teacher_subject_scopes.id, teacher_subject_scopes.account_id, teacher_subject_scopes.organization_id
• staff_permission_scopes.id, staff_permission_scopes.account_id, staff_permission_scopes.organization_id
• login_audit_logs.id, login_audit_logs.account_id
• security_events.id, security_events.account_id
• iam_membership_outbox.id, iam_membership_outbox.aggregate_id
• password_reset_tokens.id, password_reset_tokens.account_id
• auth_provider_settings.id, auth_provider_settings.updated_by_account_id
• user_auth_providers.id, user_auth_providers.account_id
• migration_runs.id, migration_id_map.migration_run_id

Profile text primary keys and references:

• users.id
• teacher_profiles.id, teacher_profiles.user_id
• student_profiles.id, student_profiles.user_id
• parent_profiles.id, parent_profiles.user_id
• staff_profiles.id, staff_profiles.user_id
• profile_contacts.id, profile_contacts.account_id
• teacher_kyc.id, teacher_kyc.user_id, teacher_kyc.verified_by_id
• parent_students.id, parent_students.parent_id, parent_students.student_id
• migration_runs.id, migration_id_map.migration_run_id

Compatibility views expose ids as strings:

• IAM accounts, credentials
• Profile profiles, profile_public_views

Required Invariants

• New service-generated ids remain UUIDv4.
• Old legacy/backfill ids remain available as legacy_id or explicit mapping rows until all public routes and reports are cut over.
• profile-service.users.id remains the logical IAM account_id. It must not become a physical cross-database foreign key.
• OpenAPI response id fields stay type: string; downstream clients should not depend on PostgreSQL storage type.
• Event contracts keep source ids as strings. sourceEventId, aggregateId, accountId, and entityId remain string fields even when the underlying canonical id is a UUID column.
• Rollback must be possible before dropping legacy text columns or views.

Proposed Columns

Use additive columns before primary-key promotion:

• id_uuid UUID for each canonical primary-key table that currently has id TEXT PRIMARY KEY.
• account_id_uuid UUID, user_id_uuid, organization_id_uuid, and similar reference columns for foreign-key relationships inside the same service database.
• legacy_text_id TEXT only when the existing id column must be converted in place later and the old string must remain queryable.

Profile-specific rule:

• users.id_uuid is Profile's storage primary key.
• users.account_id TEXT UNIQUE shadows the IAM logical account id. It is backfilled by the shadow-column migration and maintained by the Profile dual-write trigger. Compatibility views use COALESCE(account_id, id) so current API behavior remains stable.
• Role-specific profile tables should reference users.id_uuid internally while still exposing userId or accountId as strings in DTOs.

IAM-specific rule:

• identities.id_uuid is IAM's storage primary key.
• Existing public/account route ids remain string-compatible through the API.
• Directory compatibility views continue to expose id as the canonical string account id until gateway/browser cutover approves a stricter UUID-only contract.

Migration Order

1. Add UUID columns and unique indexes.
2. Backfill UUID columns:
   • If the existing text id parses as UUID, copy it into id_uuid.
   • If it does not parse as UUID, generate a new UUID and preserve the old value in legacy_id or legacy_text_id.
3. Add nullable reference UUID columns.
4. Backfill reference UUID columns by joining within the same service database.
5. Add validation queries and reports for:
   • row counts before/after;
   • null UUID columns;
   • duplicate UUIDs;
   • broken reference mappings;
   • legacy text ids without mapping rows.
6. Keep repositories on existing string-ID interfaces while the service-owned database triggers dual-write UUID shadow columns for every repository write.
7. Run dual-write for one release: new rows get both UUID storage ids and string API ids, with repository integration tests proving clean validation views.
8. Promote UUID foreign keys and constraints.
9. Promote service-owned primary-key constraints to UUID columns while keeping legacy text ids unique for string-id APIs and backfill upserts.
10. Drop old text id columns only in a final irreversible cleanup migration after rollback window closes.

Implemented additive slices:

• services/iam-service/migrations/000009_iam_uuid_shadow_columns.sql adds id_uuid, legacy_text_id, reference UUID columns, deterministic parse-or-hash UUID backfill, indexes, and iam_uuid_shadow_validation.
• services/profile-service/migrations/000006_profile_uuid_shadow_columns.sql adds id_uuid, nullable users.account_id, legacy_text_id, reference UUID columns, deterministic parse-or-hash UUID backfill, indexes, compatibility view fallback, and profile_uuid_shadow_validation.
• services/iam-service/migrations/000010_iam_uuid_shadow_dual_write.sql adds hoctapaz_iam_uuid_shadow_dual_write() and per-table triggers so repository writes maintain IAM id_uuid, legacy_text_id, and owner-local reference UUID columns. It also tightens nullable IAM audit validation so null account_id logs do not create false UUID issues.
• services/profile-service/migrations/000007_profile_uuid_shadow_dual_write.sql adds hoctapaz_profile_uuid_shadow_dual_write() and per-table triggers so repository writes maintain Profile id_uuid, users.account_id, and owner-local reference UUID columns.
• services/iam-service/migrations/000011_iam_uuid_primary_key_readiness.sql adds iam_uuid_primary_key_readiness, a read-only view for UUID primary-key promotion gates. It checks every IAM owner-local id_uuid candidate for nulls and duplicates, verifies rollback legacy_text_id coverage, and checks UUID composite-key readiness for role_permissions and user_roles.
• services/profile-service/migrations/000008_profile_uuid_primary_key_readiness.sql adds profile_uuid_primary_key_readiness, a read-only view for Profile UUID promotion gates. It checks every Profile owner-local id_uuid candidate, rollback legacy_text_id, and the logical users.account_id one-to-one mapping.
• services/iam-service/migrations/000012_iam_uuid_foreign_key_constraints.sql adds owner-local IAM UUID foreign keys as NOT VALID constraints plus the iam_uuid_foreign_key_constraints status view for validation and ON DELETE policy proof.
• services/profile-service/migrations/000009_profile_uuid_foreign_key_constraints.sql adds owner-local Profile UUID foreign keys as NOT VALID constraints plus the profile_uuid_foreign_key_constraints status view for validation and ON DELETE policy proof.
• services/iam-service/migrations/000013_iam_uuid_primary_key_promotion.sql validates IAM UUID FKs, adds non-null UUID key columns, preserves text-id uniqueness, removes legacy owner-local text FKs, promotes IAM primary keys to id_uuid or UUID composite keys, and exposes iam_uuid_primary_key_constraints.
• services/profile-service/migrations/000010_profile_uuid_primary_key_promotion.sql validates Profile UUID FKs, adds non-null UUID key columns, preserves text-id uniqueness, removes legacy owner-local text FKs, promotes Profile primary keys to id_uuid, and exposes profile_uuid_primary_key_constraints.

Still pending before default route promotion and final cleanup:

• production-like populated database proof that UUID shadow validation, primary-key readiness, foreign-key validation, and primary-key promotion views are clean after migration and backfill;
• final irreversible cleanup that drops old text id columns;
• public gateway/browser promotion evidence. Static rollback rehearsal exists through make test-iam-profile-route-rollback, but it does not promote the default route table.

Validation Queries

Each migration slice must include service-owned validation SQL equivalent to:

SELECT count(*) FROM identities WHERE id_uuid IS NULL;
SELECT id_uuid, count(*) FROM identities GROUP BY id_uuid HAVING count(*) > 1;
SELECT count(*) FROM refresh_tokens rt
LEFT JOIN identities i ON i.id_uuid = rt.account_id_uuid
WHERE rt.account_id_uuid IS NOT NULL AND i.id_uuid IS NULL;

Profile validation must also prove that account_id has a one-to-one mapping:

SELECT account_id, count(*) FROM users GROUP BY account_id HAVING count(*) > 1;
SELECT count(*) FROM users WHERE id_uuid IS NULL OR account_id IS NULL;

The read-only primary-key readiness target wraps those checks for migrated target databases:

IAM_PROFILE_UUID_PK_READINESS_CONFIRM=uuid-primary-key-readiness \
IAM_DATABASE_URL=postgres://.../hoctapaz_iam_db?sslmode=disable \
PROFILE_DATABASE_URL=postgres://.../hoctapaz_profile_db?sslmode=disable \
  make test-iam-profile-uuid-primary-key-readiness

Passing this target is prerequisite evidence for a future UUID primary-key swap. It does not itself promote constraints or change route contracts.

The UUID foreign-key validation target validates the owner-local NOT VALID constraints on migrated target databases:

IAM_PROFILE_UUID_FK_VALIDATION_CONFIRM=uuid-foreign-key-validation \
IAM_DATABASE_URL=postgres://.../hoctapaz_iam_db?sslmode=disable \
PROFILE_DATABASE_URL=postgres://.../hoctapaz_profile_db?sslmode=disable \
  make test-iam-profile-uuid-foreign-key-validation

This target scans the target tables and updates constraint metadata only. It does not write application rows, swap primary keys, or change route contracts.

The UUID primary-key promotion target validates the promoted target schema:

IAM_PROFILE_UUID_PK_PROMOTION_CONFIRM=uuid-primary-key-promotion \
IAM_DATABASE_URL=postgres://.../hoctapaz_iam_db?sslmode=disable \
PROFILE_DATABASE_URL=postgres://.../hoctapaz_profile_db?sslmode=disable \
  make test-iam-profile-uuid-primary-key-promotion

Passing this target proves the migrated IAM/Profile schemas use UUID-backed primary keys for service-owned tables, preserve text-id uniqueness for string-id APIs and ON CONFLICT (id) upserts, have no remaining owner-local legacy text FKs, and have clean UUID FK metadata.

Rollback Strategy

Before final text-id cleanup:

• Roll back the promotion migrations to restore text primary-key constraints.
• Keep existing string-id views and route contracts untouched.
• Keep legacy_text_id and text id columns until default routes and reports have production-like proof.

After final cleanup:

• Rollback requires restoring text id columns and primary-key constraints from backups or mapping tables.
• Do not enter this phase without a tested database dump restore, clean Compose/local K8s migration proof, and public route rollback notes.

Backfill Report Requirements

The legacy user backfill report must include:

• source legacy row counts for identity/profile/KYC rows;
• IAM inserted/replayed/failed counts;
• Profile inserted/replayed/failed counts;
• counts grouped by role/status;
• hash totals for stable identity/profile fields;
• warning rows for non-UUID legacy ids that require generated UUID mapping;
• proof that no legacy database writes occurred.

Acceptance For Implementation Slice

• IAM/Profile migrations preserve string-id APIs while promoting service-owned database primary keys to UUID columns.
• Repositories preserve existing API behavior while service-owned database triggers dual-write UUID columns.
• OpenAPI mirrors still expose ids as strings.
• IAM_SERVICE_POSTGRES_TEST_DATABASE_URL=... go test ./services/iam-service/internal/repository -run TestPostgresIAMUUIDShadowDualWriteIntegration -count=1 passes.
• PROFILE_SERVICE_POSTGRES_TEST_DATABASE_URL=... go test ./services/profile-service/internal/repository -run TestPostgresProfileUUIDShadowDualWriteIntegration -count=1 passes.
• make test-auth-routes test-profile-routes test-service-task-packs passes.
• IAM_PROFILE_UUID_PK_READINESS_SELF_TEST=1 make test-iam-profile-uuid-primary-key-readiness passes for the guard logic, and the same target passes with live target DSNs before UUID primary-key promotion validation.
• IAM_PROFILE_UUID_FK_VALIDATION_SELF_TEST=1 make test-iam-profile-uuid-foreign-key-validation passes for the guard logic, and the same target passes with live target DSNs before any UUID primary-key promotion validation.
• IAM_PROFILE_UUID_PK_PROMOTION_SELF_TEST=1 make test-iam-profile-uuid-primary-key-promotion passes for the guard logic, and the same target passes with live target DSNs after migration.
• GOTOOLCHAIN=go1.25.11 go test ./services/iam-service/... ./services/profile-service/... -count=1 passes.
• Clean Compose and local K8s migration smoke prove migration ordering before any default public route promotion.