# Core-server IAM Data Migration

## Summary

This runbook migrates IAM roles, policies, permissions, role bindings, policy
bindings, and API keys from `devops_staging` to `core_server_staging`.

The source and target models use the same table names and columns. IDs, time
fields, role tenant scopes, permission operation sets, and API key hashes are
copied without transformation. Users, user groups, resource tags, and audit
logs are not migrated by this runbook.

Run the SQL interactively in one MySQL session. The SQL intentionally leaves
the transaction open so that the operator can inspect the verification results
and then execute either `COMMIT` or `ROLLBACK` in the same session.

The migration is idempotent for matching records. Existing target rows with the
same source key and identical field values are skipped; source rows missing from
the target are inserted. Existing target rows with the same key but different
field values, or with a conflicting unique business key, must be resolved before
the migration runs.

## Preconditions

1. Confirm `devops_staging` and `core_server_staging` are on the same MySQL instance.
2. Stop IAM writes in both devops and core-server.
3. Run the core-server schema migration before starting this data migration.
4. Complete user and custom-group migration or synchronization first, so that
   user and user-group RoleBinding targets already exist in core-server.
5. Keep a database backup or snapshot until post-migration verification is
   complete.

## Preflight checks

### Source and incremental row counts

Record the source counts and pending incremental rows for comparison after
migration. `rows_to_insert` is the amount of new data this run will copy.

```sql
SELECT 'iam_roles' AS table_name,
       (SELECT COUNT(*) FROM `devops_staging`.`iam_roles`) AS source_rows,
       (SELECT COUNT(*)
        FROM `devops_staging`.`iam_roles` AS source_role
        JOIN `core_server_staging`.`iam_roles` AS target_role
          ON target_role.id = source_role.id) AS already_migrated_rows,
       (SELECT COUNT(*)
        FROM `devops_staging`.`iam_roles` AS source_role
        LEFT JOIN `core_server_staging`.`iam_roles` AS target_role
          ON target_role.id = source_role.id
        WHERE target_role.id IS NULL) AS rows_to_insert
UNION ALL
SELECT 'iam_policies',
       (SELECT COUNT(*) FROM `devops_staging`.`iam_policies`),
       (SELECT COUNT(*)
        FROM `devops_staging`.`iam_policies` AS source_policy
        JOIN `core_server_staging`.`iam_policies` AS target_policy
          ON target_policy.id = source_policy.id),
       (SELECT COUNT(*)
        FROM `devops_staging`.`iam_policies` AS source_policy
        LEFT JOIN `core_server_staging`.`iam_policies` AS target_policy
          ON target_policy.id = source_policy.id
        WHERE target_policy.id IS NULL)
UNION ALL
SELECT 'iam_permissions',
       (SELECT COUNT(*) FROM `devops_staging`.`iam_permissions`),
       (SELECT COUNT(*)
        FROM `devops_staging`.`iam_permissions` AS source_permission
        JOIN `core_server_staging`.`iam_permissions` AS target_permission
          ON target_permission.id = source_permission.id),
       (SELECT COUNT(*)
        FROM `devops_staging`.`iam_permissions` AS source_permission
        LEFT JOIN `core_server_staging`.`iam_permissions` AS target_permission
          ON target_permission.id = source_permission.id
        WHERE target_permission.id IS NULL)
UNION ALL
SELECT 'iam_role_bindings',
       (SELECT COUNT(*) FROM `devops_staging`.`iam_role_bindings`),
       (SELECT COUNT(*)
        FROM `devops_staging`.`iam_role_bindings` AS source_binding
        JOIN `core_server_staging`.`iam_role_bindings` AS target_binding
          ON target_binding.id = source_binding.id),
       (SELECT COUNT(*)
        FROM `devops_staging`.`iam_role_bindings` AS source_binding
        LEFT JOIN `core_server_staging`.`iam_role_bindings` AS target_binding
          ON target_binding.id = source_binding.id
        WHERE target_binding.id IS NULL)
UNION ALL
SELECT 'iam_policy_bindings',
       (SELECT COUNT(*) FROM `devops_staging`.`iam_policy_bindings`),
       (SELECT COUNT(*)
        FROM `devops_staging`.`iam_policy_bindings` AS source_binding
        JOIN `core_server_staging`.`iam_policy_bindings` AS target_binding
          ON target_binding.id = source_binding.id),
       (SELECT COUNT(*)
        FROM `devops_staging`.`iam_policy_bindings` AS source_binding
        LEFT JOIN `core_server_staging`.`iam_policy_bindings` AS target_binding
          ON target_binding.id = source_binding.id
        WHERE target_binding.id IS NULL)
UNION ALL
SELECT 'iam_api_keys',
       (SELECT COUNT(*) FROM `devops_staging`.`iam_api_keys`),
       (SELECT COUNT(*)
        FROM `devops_staging`.`iam_api_keys` AS source_api_key
        JOIN `core_server_staging`.`iam_api_keys` AS target_api_key
          ON target_api_key.id = source_api_key.id),
       (SELECT COUNT(*)
        FROM `devops_staging`.`iam_api_keys` AS source_api_key
        LEFT JOIN `core_server_staging`.`iam_api_keys` AS target_api_key
          ON target_api_key.id = source_api_key.id
        WHERE target_api_key.id IS NULL);
```

### Referential and enum integrity

Every `problem_rows` value must be zero. Resolve any nonzero result before
starting the transaction.

```sql
SELECT 'permission_without_policy' AS problem, COUNT(*) AS problem_rows
FROM `devops_staging`.`iam_permissions` AS source_permission
LEFT JOIN `devops_staging`.`iam_policies` AS source_policy
  ON source_policy.name = source_permission.policy_name
WHERE source_policy.id IS NULL
UNION ALL
SELECT 'role_binding_without_role', COUNT(*)
FROM `devops_staging`.`iam_role_bindings` AS source_binding
LEFT JOIN `devops_staging`.`iam_roles` AS source_role
  ON source_role.name = source_binding.role_name
WHERE source_role.id IS NULL
UNION ALL
SELECT 'policy_binding_without_role_or_policy', COUNT(*)
FROM `devops_staging`.`iam_policy_bindings` AS source_binding
LEFT JOIN `devops_staging`.`iam_roles` AS source_role
  ON source_role.name = source_binding.role_name
LEFT JOIN `devops_staging`.`iam_policies` AS source_policy
  ON source_policy.name = source_binding.policy_name
WHERE source_role.id IS NULL OR source_policy.id IS NULL
UNION ALL
SELECT 'user_target_missing_from_core_server', COUNT(*)
FROM `devops_staging`.`iam_role_bindings` AS source_binding
LEFT JOIN `core_server_staging`.`users` AS target_user
  ON target_user.email = source_binding.target_name
WHERE source_binding.target_type = 'user'
  AND target_user.email IS NULL
UNION ALL
SELECT 'user_group_target_missing_from_core_server', COUNT(*)
FROM `devops_staging`.`iam_role_bindings` AS source_binding
LEFT JOIN `core_server_staging`.`user_groups` AS target_group
  ON target_group.name = source_binding.target_name
WHERE source_binding.target_type = 'user_group'
  AND target_group.name IS NULL
UNION ALL
SELECT 'api_key_target_missing_from_source', COUNT(*)
FROM `devops_staging`.`iam_role_bindings` AS source_binding
LEFT JOIN `devops_staging`.`iam_api_keys` AS source_api_key
  ON source_api_key.name = source_binding.target_name
WHERE source_binding.target_type = 'api_key'
  AND source_api_key.id IS NULL
UNION ALL
SELECT 'invalid_role_binding_target_type', COUNT(*)
FROM `devops_staging`.`iam_role_bindings`
WHERE `target_type` NOT IN ('user', 'user_group', 'api_key')
UNION ALL
SELECT 'invalid_policy_type', COUNT(*)
FROM `devops_staging`.`iam_policies`
WHERE `type` NOT IN ('custom', 'inline');
```

If `permission_without_policy` is nonzero, identify the orphaned records with:

```sql
SELECT
    source_permission.id,
    source_permission.policy_name,
    HEX(source_permission.policy_name) AS policy_name_hex,
    source_permission.resource,
    source_permission.operations,
    source_permission.description
FROM `devops_staging`.`iam_permissions` AS source_permission
LEFT JOIN `devops_staging`.`iam_policies` AS source_policy
  ON source_policy.name = source_permission.policy_name
WHERE source_policy.id IS NULL;
```

Restore the missing Policy, correct the Permission's `policy_name`, or delete a
confirmed obsolete Permission before rerunning all preflight checks. Do not
silently omit orphaned rows from the migration.

### Target conflicts

Every `conflict_rows` value must be zero. Existing target rows with identical
source data are allowed and will be skipped; rows with the same key but
different field values, or conflicting unique names/bindings with different IDs,
must be resolved before starting the transaction.

```sql
SELECT 'iam_role_id_mismatch' AS conflict, COUNT(*) AS conflict_rows
FROM `devops_staging`.`iam_roles` AS source_role
JOIN `core_server_staging`.`iam_roles` AS target_role
  ON target_role.id = source_role.id
WHERE NOT (
  target_role.created_at <=> source_role.created_at
  AND target_role.updated_at <=> source_role.updated_at
  AND target_role.name <=> source_role.name
  AND target_role.description <=> source_role.description
  AND target_role.allowed_tenant_ids <=> source_role.allowed_tenant_ids
)
UNION ALL
SELECT 'iam_role_name_conflict', COUNT(*)
FROM `devops_staging`.`iam_roles` AS source_role
JOIN `core_server_staging`.`iam_roles` AS target_role
  ON target_role.name = source_role.name
WHERE target_role.id <> source_role.id
UNION ALL
SELECT 'iam_policy_id_mismatch', COUNT(*)
FROM `devops_staging`.`iam_policies` AS source_policy
JOIN `core_server_staging`.`iam_policies` AS target_policy
  ON target_policy.id = source_policy.id
WHERE NOT (
  target_policy.created_at <=> source_policy.created_at
  AND target_policy.updated_at <=> source_policy.updated_at
  AND target_policy.name <=> source_policy.name
  AND target_policy.type <=> source_policy.type
  AND target_policy.description <=> source_policy.description
)
UNION ALL
SELECT 'iam_policy_name_conflict', COUNT(*)
FROM `devops_staging`.`iam_policies` AS source_policy
JOIN `core_server_staging`.`iam_policies` AS target_policy
  ON target_policy.name = source_policy.name
WHERE target_policy.id <> source_policy.id
UNION ALL
SELECT 'iam_permission_id_mismatch', COUNT(*)
FROM `devops_staging`.`iam_permissions` AS source_permission
JOIN `core_server_staging`.`iam_permissions` AS target_permission
  ON target_permission.id = source_permission.id
WHERE NOT (
  target_permission.resource <=> source_permission.resource
  AND target_permission.operations <=> source_permission.operations
  AND target_permission.description <=> source_permission.description
  AND target_permission.policy_name <=> source_permission.policy_name
)
UNION ALL
SELECT 'iam_role_binding_id_mismatch', COUNT(*)
FROM `devops_staging`.`iam_role_bindings` AS source_binding
JOIN `core_server_staging`.`iam_role_bindings` AS target_binding
  ON target_binding.id = source_binding.id
WHERE NOT (
  target_binding.created_at <=> source_binding.created_at
  AND target_binding.target_type <=> source_binding.target_type
  AND target_binding.target_name <=> source_binding.target_name
  AND target_binding.role_name <=> source_binding.role_name
)
UNION ALL
SELECT 'iam_role_binding_unique_conflict', COUNT(*)
FROM `devops_staging`.`iam_role_bindings` AS source_binding
JOIN `core_server_staging`.`iam_role_bindings` AS target_binding
  ON target_binding.target_type = source_binding.target_type
 AND target_binding.target_name = source_binding.target_name
 AND target_binding.role_name = source_binding.role_name
WHERE target_binding.id <> source_binding.id
UNION ALL
SELECT 'iam_policy_binding_id_mismatch', COUNT(*)
FROM `devops_staging`.`iam_policy_bindings` AS source_binding
JOIN `core_server_staging`.`iam_policy_bindings` AS target_binding
  ON target_binding.id = source_binding.id
WHERE NOT (
  target_binding.created_at <=> source_binding.created_at
  AND target_binding.policy_name <=> source_binding.policy_name
  AND target_binding.role_name <=> source_binding.role_name
)
UNION ALL
SELECT 'iam_policy_binding_unique_conflict', COUNT(*)
FROM `devops_staging`.`iam_policy_bindings` AS source_binding
JOIN `core_server_staging`.`iam_policy_bindings` AS target_binding
  ON target_binding.policy_name = source_binding.policy_name
 AND target_binding.role_name = source_binding.role_name
WHERE target_binding.id <> source_binding.id
UNION ALL
SELECT 'iam_api_key_id_mismatch', COUNT(*)
FROM `devops_staging`.`iam_api_keys` AS source_api_key
JOIN `core_server_staging`.`iam_api_keys` AS target_api_key
  ON target_api_key.id = source_api_key.id
WHERE NOT (
  target_api_key.created_at <=> source_api_key.created_at
  AND target_api_key.name <=> source_api_key.name
  AND target_api_key.comment <=> source_api_key.comment
  AND target_api_key.hash <=> source_api_key.hash
  AND target_api_key.owner <=> source_api_key.owner
)
UNION ALL
SELECT 'iam_api_key_name_conflict', COUNT(*)
FROM `devops_staging`.`iam_api_keys` AS source_api_key
JOIN `core_server_staging`.`iam_api_keys` AS target_api_key
  ON target_api_key.name = source_api_key.name
WHERE target_api_key.id <> source_api_key.id;
```

## Transactional migration

Start a repeatable-read transaction and copy only rows that are missing from
the target. Rows that already exist with identical data are skipped:

```sql
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;

INSERT INTO `core_server_staging`.`iam_roles`
    (`id`, `created_at`, `updated_at`, `name`, `description`, `allowed_tenant_ids`)
SELECT
    source_role.`id`,
    source_role.`created_at`,
    source_role.`updated_at`,
    source_role.`name`,
    source_role.`description`,
    source_role.`allowed_tenant_ids`
FROM `devops_staging`.`iam_roles` AS source_role
LEFT JOIN `core_server_staging`.`iam_roles` AS target_role
  ON target_role.id = source_role.id
WHERE target_role.id IS NULL;

INSERT INTO `core_server_staging`.`iam_policies`
    (`id`, `created_at`, `updated_at`, `name`, `type`, `description`)
SELECT
    source_policy.`id`,
    source_policy.`created_at`,
    source_policy.`updated_at`,
    source_policy.`name`,
    source_policy.`type`,
    source_policy.`description`
FROM `devops_staging`.`iam_policies` AS source_policy
LEFT JOIN `core_server_staging`.`iam_policies` AS target_policy
  ON target_policy.id = source_policy.id
WHERE target_policy.id IS NULL;

INSERT INTO `core_server_staging`.`iam_permissions`
    (`id`, `resource`, `operations`, `description`, `policy_name`)
SELECT
    source_permission.`id`,
    source_permission.`resource`,
    source_permission.`operations`,
    source_permission.`description`,
    source_permission.`policy_name`
FROM `devops_staging`.`iam_permissions` AS source_permission
LEFT JOIN `core_server_staging`.`iam_permissions` AS target_permission
  ON target_permission.id = source_permission.id
WHERE target_permission.id IS NULL;

INSERT INTO `core_server_staging`.`iam_role_bindings`
    (`id`, `created_at`, `target_type`, `target_name`, `role_name`)
SELECT
    source_binding.`id`,
    source_binding.`created_at`,
    source_binding.`target_type`,
    source_binding.`target_name`,
    source_binding.`role_name`
FROM `devops_staging`.`iam_role_bindings` AS source_binding
LEFT JOIN `core_server_staging`.`iam_role_bindings` AS target_binding
  ON target_binding.id = source_binding.id
WHERE target_binding.id IS NULL;

INSERT INTO `core_server_staging`.`iam_policy_bindings`
    (`id`, `created_at`, `policy_name`, `role_name`)
SELECT
    source_binding.`id`,
    source_binding.`created_at`,
    source_binding.`policy_name`,
    source_binding.`role_name`
FROM `devops_staging`.`iam_policy_bindings` AS source_binding
LEFT JOIN `core_server_staging`.`iam_policy_bindings` AS target_binding
  ON target_binding.id = source_binding.id
WHERE target_binding.id IS NULL;

INSERT INTO `core_server_staging`.`iam_api_keys`
    (`id`, `created_at`, `name`, `comment`, `hash`, `owner`)
SELECT
    source_api_key.`id`,
    source_api_key.`created_at`,
    source_api_key.`name`,
    source_api_key.`comment`,
    source_api_key.`hash`,
    source_api_key.`owner`
FROM `devops_staging`.`iam_api_keys` AS source_api_key
LEFT JOIN `core_server_staging`.`iam_api_keys` AS target_api_key
  ON target_api_key.id = source_api_key.id
WHERE target_api_key.id IS NULL;
```

Do not commit yet. Run all verification queries below in the same session.

## Verification before commit

### Row counts

For every row, `source_rows` must equal `migrated_rows`. The target counts use
source IDs, so unrelated target-only rows are not included:

```sql
SELECT 'iam_roles' AS table_name,
       (SELECT COUNT(*) FROM `devops_staging`.`iam_roles`) AS source_rows,
       (SELECT COUNT(*)
        FROM `devops_staging`.`iam_roles` AS source_role
        JOIN `core_server_staging`.`iam_roles` AS target_role
          ON target_role.id = source_role.id) AS migrated_rows
UNION ALL
SELECT 'iam_policies',
       (SELECT COUNT(*) FROM `devops_staging`.`iam_policies`),
       (SELECT COUNT(*)
        FROM `devops_staging`.`iam_policies` AS source_policy
        JOIN `core_server_staging`.`iam_policies` AS target_policy
          ON target_policy.id = source_policy.id)
UNION ALL
SELECT 'iam_permissions',
       (SELECT COUNT(*) FROM `devops_staging`.`iam_permissions`),
       (SELECT COUNT(*)
        FROM `devops_staging`.`iam_permissions` AS source_permission
        JOIN `core_server_staging`.`iam_permissions` AS target_permission
          ON target_permission.id = source_permission.id)
UNION ALL
SELECT 'iam_role_bindings',
       (SELECT COUNT(*) FROM `devops_staging`.`iam_role_bindings`),
       (SELECT COUNT(*)
        FROM `devops_staging`.`iam_role_bindings` AS source_binding
        JOIN `core_server_staging`.`iam_role_bindings` AS target_binding
          ON target_binding.id = source_binding.id)
UNION ALL
SELECT 'iam_policy_bindings',
       (SELECT COUNT(*) FROM `devops_staging`.`iam_policy_bindings`),
       (SELECT COUNT(*)
        FROM `devops_staging`.`iam_policy_bindings` AS source_binding
        JOIN `core_server_staging`.`iam_policy_bindings` AS target_binding
          ON target_binding.id = source_binding.id)
UNION ALL
SELECT 'iam_api_keys',
       (SELECT COUNT(*) FROM `devops_staging`.`iam_api_keys`),
       (SELECT COUNT(*)
        FROM `devops_staging`.`iam_api_keys` AS source_api_key
        JOIN `core_server_staging`.`iam_api_keys` AS target_api_key
          ON target_api_key.id = source_api_key.id);
```

### Field equality

Every `mismatch_rows` value must be zero. The row-count check above detects
missing rows; these queries compare every persisted field for matching IDs.

```sql
SELECT 'iam_roles' AS table_name, COUNT(*) AS mismatch_rows
FROM `devops_staging`.`iam_roles` AS source_role
JOIN `core_server_staging`.`iam_roles` AS target_role
  ON target_role.id = source_role.id
WHERE NOT (
  target_role.created_at <=> source_role.created_at
  AND target_role.updated_at <=> source_role.updated_at
  AND target_role.name <=> source_role.name
  AND target_role.description <=> source_role.description
  AND target_role.allowed_tenant_ids <=> source_role.allowed_tenant_ids
)
UNION ALL
SELECT 'iam_policies', COUNT(*)
FROM `devops_staging`.`iam_policies` AS source_policy
JOIN `core_server_staging`.`iam_policies` AS target_policy
  ON target_policy.id = source_policy.id
WHERE NOT (
  target_policy.created_at <=> source_policy.created_at
  AND target_policy.updated_at <=> source_policy.updated_at
  AND target_policy.name <=> source_policy.name
  AND target_policy.type <=> source_policy.type
  AND target_policy.description <=> source_policy.description
)
UNION ALL
SELECT 'iam_permissions', COUNT(*)
FROM `devops_staging`.`iam_permissions` AS source_permission
JOIN `core_server_staging`.`iam_permissions` AS target_permission
  ON target_permission.id = source_permission.id
WHERE NOT (
  target_permission.resource <=> source_permission.resource
  AND target_permission.operations <=> source_permission.operations
  AND target_permission.description <=> source_permission.description
  AND target_permission.policy_name <=> source_permission.policy_name
)
UNION ALL
SELECT 'iam_role_bindings', COUNT(*)
FROM `devops_staging`.`iam_role_bindings` AS source_binding
JOIN `core_server_staging`.`iam_role_bindings` AS target_binding
  ON target_binding.id = source_binding.id
WHERE NOT (
  target_binding.created_at <=> source_binding.created_at
  AND target_binding.target_type <=> source_binding.target_type
  AND target_binding.target_name <=> source_binding.target_name
  AND target_binding.role_name <=> source_binding.role_name
)
UNION ALL
SELECT 'iam_policy_bindings', COUNT(*)
FROM `devops_staging`.`iam_policy_bindings` AS source_binding
JOIN `core_server_staging`.`iam_policy_bindings` AS target_binding
  ON target_binding.id = source_binding.id
WHERE NOT (
  target_binding.created_at <=> source_binding.created_at
  AND target_binding.policy_name <=> source_binding.policy_name
  AND target_binding.role_name <=> source_binding.role_name
)
UNION ALL
SELECT 'iam_api_keys', COUNT(*)
FROM `devops_staging`.`iam_api_keys` AS source_api_key
JOIN `core_server_staging`.`iam_api_keys` AS target_api_key
  ON target_api_key.id = source_api_key.id
WHERE NOT (
  target_api_key.created_at <=> source_api_key.created_at
  AND target_api_key.name <=> source_api_key.name
  AND target_api_key.comment <=> source_api_key.comment
  AND target_api_key.hash <=> source_api_key.hash
  AND target_api_key.owner <=> source_api_key.owner
);
```

### Safe samples

Inspect representative records without printing API key hashes:

```sql
SELECT `id`, `name`, `description`, `allowed_tenant_ids`
FROM `core_server_staging`.`iam_roles`
ORDER BY `id`
LIMIT 20;

SELECT `id`, `name`, `type`, `description`
FROM `core_server_staging`.`iam_policies`
ORDER BY `id`
LIMIT 20;

SELECT `id`, `policy_name`, `resource`, `operations`, `description`
FROM `core_server_staging`.`iam_permissions`
ORDER BY `id`
LIMIT 20;

SELECT `id`, `target_type`, `target_name`, `role_name`
FROM `core_server_staging`.`iam_role_bindings`
ORDER BY `id`
LIMIT 20;

SELECT `id`, `policy_name`, `role_name`
FROM `core_server_staging`.`iam_policy_bindings`
ORDER BY `id`
LIMIT 20;

SELECT `id`, `name`, `comment`, `owner`
FROM `core_server_staging`.`iam_api_keys`
ORDER BY `id`
LIMIT 20;
```

## Commit or rollback

If all counts match, all mismatch counts are zero, and the samples are correct,
commit in the same MySQL session:

```sql
COMMIT;
```

If any insert or verification fails, roll back in the same session:

```sql
ROLLBACK;
```

After committing, start core-server and verify:

- Existing user, user-group, and API-key RoleBindings resolve the expected
  roles.
- Each role grants the expected policies and permissions.
- Role `allowed_tenant_ids` values are unchanged.
- An existing API key still authenticates and resolves its permissions.

Restore traffic and IAM writes only after these checks pass.