HocTapAZ Docs

Appearance

IAM/Profile Backfill Loader Task ​

Agent workflow: follow README.md for Audit -> Investigate -> Code -> Test -> Fix; keep this pack's writable scope and verification commands authoritative.

Dispatch type: orchestrator

Owner: orchestrator with iam-service, profile-service, and migration owners.

Status: Preflight, dry-run plan, target run-state schema, and confirmed migration_runs checkpoint writer in place. The write-side slices load legacy User base rows into IAM identities and Profile users, legacy RefreshToken, AuthProviderSetting, and UserAuthProvider rows into IAM owner tables, derived IAM sessions, roles, user-role assignments, IAM permission catalog rows, role-permission assignments, organization/member access projections, parent-student access links, teacher subject scopes, and an explicit zero-source staff_permission_scopes reconciliation because legacy has no per-account staff permission scope table. It also loads derived Profile email/phone contacts into profile_contacts, teacher/student/parent education profiles, teacher KYC rows, and parent-child read models into Profile owner tables. Postload source/target count and hash reconciliation is emitted for loaded tables. A hermetic populated fixture smoke now applies IAM/Profile migrations, runs the confirmed loader, and checks UUID shadow validation views against representative rows. UUID primary-key promotion is implemented in the target schema; real/live populated DB validation, browser proof, rollback evidence, final text-id cleanup, and route cutover are still pending.

Problem:

IAM/Profile currently has a read-only legacy backfill report and a write-side loader for legacy User base rows, IAM refresh tokens, IAM sessions, IAM provider settings/links, IAM roles/user-role assignments, IAM permission catalog/role-permission assignments, IAM organization membership projections, IAM parent-student access links, IAM teacher subject scopes, Profile-owned email/phone contacts, teacher/student/parent profile rows, teacher KYC rows, and parent-child read models. The remaining loader work must stay explicit so agents do not accidentally mix schema migrations, legacy reads, and target database writes in one unsafe flow.

Goal:

Implement restartable IAM/Profile backfill tooling that reads legacy data read-only, writes only to hoctapaz_iam_db and hoctapaz_profile_db, records validation evidence, and keeps runtime route cutover separate from data load.

Legacy source evidence:

  • /Users/velikho/Desktop/WORKING/HOCTAPAZ/node-platform/apps/api/prisma/schema.prisma
  • /Users/velikho/Desktop/WORKING/HOCTAPAZ/node-platform/apps/api/src/modules/auth/**
  • /Users/velikho/Desktop/WORKING/HOCTAPAZ/node-platform/apps/api/src/modules/app-data/**
  • Existing report SQL:
    • migrations/legacy-to-go/002_migrate_auth.sql
    • migrations/legacy-to-go/003_migrate_users.sql

Writable files:

  • tools/migrator/**
  • scripts/migration/iam-profile-*.sh
  • scripts/test/iam-profile-*-coverage.sh
  • migrations/legacy-to-go/002_migrate_auth.sql
  • migrations/legacy-to-go/003_migrate_users.sql
  • docs/migration/**
  • docs/microservices/iam-profile-*.md
  • docs/agents/service-tasks/iam-profile-backfill-loader.md
  • docs/agents/service-tasks/iam-profile-cutover-hardening.md

Orchestrator-owned files:

  • Service schema migrations, Compose/K8s/Helm runtime wiring, default gateway route tables, VitePress navigation, and final user-service retirement.

Tasks:

  • [x] Keep the existing report path read-only against LEGACY_DATABASE_URL. make test-iam-profile-backfill-report guards the SQL as SELECT/WITH-only and the wrapper as legacy-DSN-only with BEGIN READ ONLY/ROLLBACK.
  • [x] Add an explicit no-write preflight contract with separate IAM_DATABASE_URL and PROFILE_DATABASE_URL target DSNs: go run ./tools/migrator/cmd/migrator validate --service iam-profile-backfill.
  • [x] Add a no-write dry-run loader plan: go run ./tools/migrator/cmd/migrator dry-run --service iam-profile-backfill. The plan must validate the same DSNs, emit ordered owner-database steps, include migration_runs/migration_id_map checkpoints for both target databases, and keep default route cutover blocked.
  • [x] Add the first write-side loader command for legacy User base rows, IAM refresh tokens, IAM provider settings/links, Profile contacts, and Profile teacher/student/parent rows; it reads legacy data in pgx read-only transactions and writes only to IAM/Profile target databases.
  • [x] Load IAM refresh-token rows only into hoctapaz_iam_db.
  • [x] Load IAM provider setting and provider-link rows only into hoctapaz_iam_db.
  • [x] Load IAM session rows derived from legacy RefreshToken rows only into hoctapaz_iam_db.
  • [x] Load IAM role and user-role rows derived from legacy User.role only into hoctapaz_iam_db.
  • [x] Load IAM permission catalog and role-permission rows into hoctapaz_iam_db from current gateway/service role policy; this is an IAM-owned seed, not a claim that legacy stored per-staff scopes.
  • [x] Load IAM organization/member projection rows derived from legacy Organization and OrganizationMember only into hoctapaz_iam_db.
  • [x] Load IAM parent-student access links derived from legacy ParentStudent only into hoctapaz_iam_db; Profile still owns the parent-child read model.
  • [x] Load IAM teacher subject scopes derived from legacy TeacherProfile.subjects only into hoctapaz_iam_db.
  • [x] Complete IAM staff per-account permission source mapping as an explicit zero-source legacy table: legacy stores coarse User.role and OrganizationMember.role, while per-account staff_permission_scopes remain post-cutover IAM administration data and must not be inferred by the backfill.
  • [x] Load Profile email/phone contact rows only into hoctapaz_profile_db.
  • [x] Load Profile teacher/student/parent education profile rows only into hoctapaz_profile_db.
  • [x] Load KYC and parent-child rows only into hoctapaz_profile_db.
  • [x] Add service-owned migration_runs schema and migration_id_map.migration_run_id evidence columns in both IAM and Profile target databases.
  • [x] Make migrator run --service iam-profile-backfill --confirm prepare idempotent migration_runs checkpoints in each owning target database.
  • [x] Make the base legacy User, RefreshToken, AuthProviderSetting, and UserAuthProvider row loaders plus derived Profile contact, education profile, KYC, and parent-child loaders populate row-level migration_id_map evidence in the owning target database.
  • [x] Extend row-level migration_id_map evidence to IAM sessions, user-role assignments, organization/member projections, identity memberships, parent-student access links, and teacher subject scopes.
  • [x] Extend row-level migration_id_map evidence to IAM permission catalog rows and role-permission assignments.
  • [x] Record IAM staff per-account permission scope evidence as iam_reconcile_staff_permission_scopes with zero explicit legacy source rows; no migration_id_map rows are written because there are no legacy row ids to map.
  • [x] Emit postload reconciliation items with source rows, target rows, source hash, target hash, matched flag, issue count, and rejected row count for each loaded IAM/Profile owner table.
  • [x] Add an opt-in populated fixture smoke that applies IAM/Profile target migrations, seeds representative legacy auth/profile rows, runs migrator run --service iam-profile-backfill --confirm, asserts reconciliation rows including zero-source staff_permission_scopes, and requires iam_uuid_shadow_validation plus profile_uuid_shadow_validation to stay clean after loader writes.
  • [x] Harden read-only live populated validation so cutover candidates can attach the captured migrator run --service iam-profile-backfill --confirm JSON report and require every postload-reconciliation item to have matched=true, issueCount=0, rejectedRows=0, and source/target hashes. In human review terms, the report must show zero issues and zero rejected rows. The same validation now checks owner-local orphan references in IAM/Profile target DBs without writing to either target.
  • [ ] Promote reconciliation to cutover proof only after the hardened live DB evidence is run against production-like targets with the run report attached, plus browser proof and rollback evidence.
  • [x] Add a hermetic coverage script and Make target before any real loader is used against developer or production-like databases.

Acceptance:

  • The loader never writes to the legacy database and never runs inside schema migration commands.
  • Dry-run output is JSON with deterministic item order, ownerDatabase boundaries, restart checkpoints, and a cutover gate; dry-run success is not data-load completion.
  • Target writes are idempotent and can resume from recorded run/map state.
  • IAM and Profile data stay split by owner database; no cross-database foreign keys or joins are introduced.
  • Password hashes, refresh token hashes, provider links, and provider settings are IAM-owned; profile display/KYC/role-profile rows are Profile-owned.
  • Validation output includes source counts, target counts, hashes, orphan checks, rejected rows, and rollback/freeze instructions.
  • The populated fixture smoke now also proves completed migration_runs rows with source/target hash evidence. make test-iam-profile-live-populated-validation is the read-only validation path for an already-populated IAM/Profile target; set IAM_PROFILE_LIVE_VALIDATION_RUN_REPORT and IAM_PROFILE_LIVE_VALIDATION_REQUIRE_RUN_REPORT=1 when using it as cutover-grade reconciliation evidence.
  • Fixture validation is still representative only; production-like populated DB validation remains a separate cutover gate until run against the real target DSNs.
  • Same-target fixture validation was refreshed on 2026-07-09 under output/qa/iam-profile-target-validation-20260709-114607/. The fixture target stayed online after backfill, and the live populated validation plus UUID readiness, FK validation, and PK-promotion checks all ran against the same IAM/Profile DSNs. The summaries recorded 22 matched postload-reconciliation items, IAM/Profile map rows 63/17, zero orphan references, readiness rows 81/39 with no blocking issues, validated FK constraints 26/10, and promoted UUID PK metadata for 21/9 tables. This is still fixture-only; repeat the same commands on production-like target DSNs before route cutover approval.
  • UUID primary-key readiness is a separate read-only target validation gate: after applying IAM/Profile migrations and loading target rows, run make test-iam-profile-uuid-primary-key-readiness with live target DSNs to prove UUID primary-key candidates are non-null, unique, and retain rollback legacy_text_id coverage before primary-key promotion validation.
  • UUID foreign-key validation is the next target validation gate after clean primary-key readiness: after applying IAM/Profile migrations and loading target rows, run make test-iam-profile-uuid-foreign-key-validation with live target DSNs to validate the NOT VALID owner-local UUID constraints and their ON DELETE policies before primary-key promotion validation.
  • UUID primary-key promotion validation is the next metadata gate after the promotion migrations are applied: run make test-iam-profile-uuid-primary-key-promotion with live target DSNs to prove service-owned primary keys use UUID columns, text ids remain unique for string-id APIs/upserts, legacy text FKs are gone, and UUID FKs are healthy.
  • Default /api/auth/* or profile route promotion remains blocked until loader verification, browser proof, and rollback evidence are complete.

Verification:

  • make test-iam-profile-backfill-report
  • make test-iam-profile-backfill-loader
  • IAM_PROFILE_BACKFILL_POPULATED_SELF_TEST=1 make test-iam-profile-backfill-populated
  • IAM_PROFILE_LIVE_VALIDATION_SELF_TEST=1 make test-iam-profile-live-populated-validation
  • Optional fixture smoke: IAM_PROFILE_BACKFILL_POPULATED_CONFIRM=populated-fixture make test-iam-profile-backfill-populated
  • Latest local fixture proof: IAM_PROFILE_BACKFILL_POPULATED_CONFIRM=populated-fixture IAM_PROFILE_BACKFILL_POPULATED_ARTIFACT_DIR=output/qa/iam-profile-backfill-populated-20260709-113538 make test-iam-profile-backfill-populated passed on 2026-07-09. The artifact report captured 4 legacy user rows, IAM rowsWritten=63, Profile rowsWritten=17, matched identity/profile reconciliation, zero explicit staff permission scope source rows, and clean IAM/Profile UUID shadow validation views.
  • Latest same-target validation fixture proof: output/qa/iam-profile-target-validation-20260709-114607/ contains the kept-container backfill, live populated validation, UUID readiness, UUID FK validation, and UUID PK-promotion artifacts for the same IAM/Profile DSNs.
  • Optional read-only populated target smoke; not sufficient for cutover: IAM_PROFILE_LIVE_VALIDATION_CONFIRM=live-populated IAM_DATABASE_URL=postgres://.../hoctapaz_iam_db?sslmode=disable PROFILE_DATABASE_URL=postgres://.../hoctapaz_profile_db?sslmode=disable make test-iam-profile-live-populated-validation
  • Cutover gate for populated target validation: IAM_PROFILE_LIVE_VALIDATION_CONFIRM=live-populated IAM_PROFILE_LIVE_VALIDATION_RUN_REPORT=/path/to/migrator-run.json IAM_PROFILE_LIVE_VALIDATION_REQUIRE_RUN_REPORT=1 IAM_DATABASE_URL=postgres://.../hoctapaz_iam_db?sslmode=disable PROFILE_DATABASE_URL=postgres://.../hoctapaz_profile_db?sslmode=disable make test-iam-profile-live-populated-validation
  • Optional UUID primary-key readiness validation: 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
  • Optional UUID foreign-key validation: 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
  • Optional UUID primary-key promotion validation: 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
  • sh scripts/test/service-task-pack-coverage.sh
  • GOTOOLCHAIN=go1.25.11 go test ./tools/migrator/...

Go-platform documentation is generated from repository Markdown.

Copyright 2026 HocTapAZ