# Core-server User Data Migration

## Summary

This runbook migrates user-owned API tokens, API token IP access lists, custom
user groups, and their mappings from `devops_staging` to `core_server_staging`.

The migration does not copy users, Lark groups, IAM data, resource tags, or
audit logs. It also does not compare or copy any legacy physical `id` column on
`user_groups`; the group name is the business key in both services.

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 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 and User writes in both devops and core-server.
3. Run the core-server schema migration before starting this data migration.
4. Confirm core-server user synchronization has populated all token owners and
   custom-group members in `core_server_staging.users`.
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 the
migration. `rows_to_insert` is the amount of new data this run will copy.

```sql
SELECT 'custom_user_groups' AS data_set,
       (SELECT COUNT(*)
        FROM `devops_staging`.`user_groups`
        WHERE `source` = 'custom') AS source_rows,
       (SELECT COUNT(*)
        FROM `devops_staging`.`user_groups` AS source_group
        JOIN `core_server_staging`.`user_groups` AS target_group
          ON target_group.name = source_group.name
        WHERE source_group.source = 'custom') AS already_migrated_rows,
       (SELECT COUNT(*)
        FROM `devops_staging`.`user_groups` AS source_group
        LEFT JOIN `core_server_staging`.`user_groups` AS target_group
          ON target_group.name = source_group.name
        WHERE source_group.source = 'custom'
          AND target_group.name IS NULL) AS rows_to_insert
UNION ALL
SELECT 'custom_user_group_mappings',
       (SELECT COUNT(*)
        FROM `devops_staging`.`user_group_mappings` AS source_mapping
        JOIN `devops_staging`.`user_groups` AS source_group
          ON source_group.name = source_mapping.group_name
        WHERE source_group.source = 'custom'),
       (SELECT COUNT(*)
        FROM `devops_staging`.`user_group_mappings` AS source_mapping
        JOIN `devops_staging`.`user_groups` AS source_group
          ON source_group.name = source_mapping.group_name
         AND source_group.source = 'custom'
        JOIN `core_server_staging`.`user_group_mappings` AS target_mapping
          ON target_mapping.user_email = source_mapping.user_email
         AND target_mapping.group_name = source_mapping.group_name),
       (SELECT COUNT(*)
        FROM `devops_staging`.`user_group_mappings` AS source_mapping
        JOIN `devops_staging`.`user_groups` AS source_group
          ON source_group.name = source_mapping.group_name
         AND source_group.source = 'custom'
        LEFT JOIN `core_server_staging`.`user_group_mappings` AS target_mapping
          ON target_mapping.user_email = source_mapping.user_email
         AND target_mapping.group_name = source_mapping.group_name
        WHERE target_mapping.user_email IS NULL)
UNION ALL
SELECT 'user_api_tokens',
       (SELECT COUNT(*) FROM `devops_staging`.`user_api_tokens`),
       (SELECT COUNT(*)
        FROM `devops_staging`.`user_api_tokens` AS source_token
        JOIN `core_server_staging`.`user_api_tokens` AS target_token
          ON target_token.id = source_token.id),
       (SELECT COUNT(*)
        FROM `devops_staging`.`user_api_tokens` AS source_token
        LEFT JOIN `core_server_staging`.`user_api_tokens` AS target_token
          ON target_token.id = source_token.id
        WHERE target_token.id IS NULL)
UNION ALL
SELECT 'user_api_token_ip_access_lists',
       (SELECT COUNT(*) FROM `devops_staging`.`user_api_token_ip_access_lists`),
       (SELECT COUNT(*)
        FROM `devops_staging`.`user_api_token_ip_access_lists` AS source_access_list
        JOIN `core_server_staging`.`user_api_token_ip_access_lists` AS target_access_list
          ON target_access_list.user_email = source_access_list.user_email),
       (SELECT COUNT(*)
        FROM `devops_staging`.`user_api_token_ip_access_lists` AS source_access_list
        LEFT JOIN `core_server_staging`.`user_api_token_ip_access_lists` AS target_access_list
          ON target_access_list.user_email = source_access_list.user_email
        WHERE target_access_list.user_email IS NULL);
```

### Source and target integrity

Every `problem_rows` value must be zero. A nonzero result must be resolved
before starting the transaction.

```sql
SELECT 'token_owner_missing_from_target_users' AS problem,
       COUNT(*) AS problem_rows
FROM `devops_staging`.`user_api_tokens` AS token
LEFT JOIN `core_server_staging`.`users` AS target_user
  ON target_user.email = token.owner
WHERE target_user.email IS NULL
UNION ALL
SELECT 'custom_mapping_user_missing_from_target_users', COUNT(*)
FROM `devops_staging`.`user_group_mappings` AS mapping
JOIN `devops_staging`.`user_groups` AS user_group
  ON user_group.name = mapping.group_name
 AND user_group.source = 'custom'
LEFT JOIN `core_server_staging`.`users` AS target_user
  ON target_user.email = mapping.user_email
WHERE target_user.email IS NULL
UNION ALL
SELECT 'access_list_user_missing_from_target_users', COUNT(*)
FROM `devops_staging`.`user_api_token_ip_access_lists` AS access_list
LEFT JOIN `core_server_staging`.`users` AS target_user
  ON target_user.email = access_list.user_email
WHERE target_user.email IS NULL
UNION ALL
SELECT 'custom_group_parent_missing_from_target_and_source', COUNT(*)
FROM `devops_staging`.`user_groups` AS user_group
LEFT JOIN `devops_staging`.`user_groups` AS source_parent
  ON source_parent.name = user_group.parent_name
 AND source_parent.source = 'custom'
LEFT JOIN `core_server_staging`.`user_groups` AS target_parent
  ON target_parent.name = user_group.parent_name
WHERE user_group.source = 'custom'
  AND user_group.parent_name <> ''
  AND source_parent.name IS NULL
  AND target_parent.name IS NULL
UNION ALL
SELECT 'invalid_api_token_status', COUNT(*)
FROM `devops_staging`.`user_api_tokens`
WHERE `status` NOT IN ('active', 'inactive');
```

### 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 must be resolved before starting the transaction.

```sql
SELECT 'custom_user_group_mismatch' AS conflict, COUNT(*) AS conflict_rows
FROM `devops_staging`.`user_groups` AS source_group
JOIN `core_server_staging`.`user_groups` AS target_group
  ON target_group.name = source_group.name
WHERE source_group.source = 'custom'
  AND NOT (
    target_group.name <=> source_group.name
    AND target_group.created_at <=> source_group.created_at
    AND target_group.parent_name <=> source_group.parent_name
    AND target_group.description <=> source_group.description
    AND target_group.source <=> source_group.source
  )
UNION ALL
SELECT 'custom_user_group_mapping_mismatch', COUNT(*)
FROM `devops_staging`.`user_group_mappings` AS source_mapping
JOIN `devops_staging`.`user_groups` AS source_group
  ON source_group.name = source_mapping.group_name
 AND source_group.source = 'custom'
JOIN `core_server_staging`.`user_group_mappings` AS target_mapping
  ON target_mapping.user_email = source_mapping.user_email
 AND target_mapping.group_name = source_mapping.group_name
WHERE NOT (
  target_mapping.created_at <=> source_mapping.created_at
  AND target_mapping.user_email <=> source_mapping.user_email
  AND target_mapping.group_name <=> source_mapping.group_name
)
UNION ALL
SELECT 'user_api_token_mismatch', COUNT(*)
FROM `devops_staging`.`user_api_tokens` AS source_token
JOIN `core_server_staging`.`user_api_tokens` AS target_token
  ON target_token.id = source_token.id
WHERE NOT (
  target_token.id <=> source_token.id
  AND target_token.created_at <=> source_token.created_at
  AND target_token.name <=> source_token.name
  AND target_token.comment <=> source_token.comment
  AND target_token.status <=> source_token.status
  AND target_token.expire_at <=> source_token.expire_at
  AND target_token.hash <=> source_token.hash
  AND target_token.salt <=> source_token.salt
  AND target_token.owner <=> source_token.owner
)
UNION ALL
SELECT 'user_api_token_ip_access_list_mismatch', COUNT(*)
FROM `devops_staging`.`user_api_token_ip_access_lists` AS source_access_list
JOIN `core_server_staging`.`user_api_token_ip_access_lists` AS target_access_list
  ON target_access_list.user_email = source_access_list.user_email
WHERE NOT (
  target_access_list.user_email <=> source_access_list.user_email
  AND target_access_list.created_at <=> source_access_list.created_at
  AND target_access_list.updated_at <=> source_access_list.updated_at
  AND target_access_list.allow_all <=> source_access_list.allow_all
  AND target_access_list.rules <=> source_access_list.rules
);
```

## 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`.`user_groups`
    (`name`, `created_at`, `parent_name`, `description`, `source`)
SELECT
    source_group.`name`,
    source_group.`created_at`,
    source_group.`parent_name`,
    source_group.`description`,
    source_group.`source`
FROM `devops_staging`.`user_groups` AS source_group
LEFT JOIN `core_server_staging`.`user_groups` AS target_group
  ON target_group.name = source_group.name
WHERE source_group.`source` = 'custom'
  AND target_group.name IS NULL;

INSERT INTO `core_server_staging`.`user_group_mappings`
    (`created_at`, `user_email`, `group_name`)
SELECT
    source_mapping.created_at,
    source_mapping.user_email,
    source_mapping.group_name
FROM `devops_staging`.`user_group_mappings` AS source_mapping
JOIN `devops_staging`.`user_groups` AS user_group
  ON user_group.name = source_mapping.group_name
LEFT JOIN `core_server_staging`.`user_group_mappings` AS target_mapping
  ON target_mapping.user_email = source_mapping.user_email
 AND target_mapping.group_name = source_mapping.group_name
WHERE user_group.source = 'custom'
  AND target_mapping.user_email IS NULL;

INSERT INTO `core_server_staging`.`user_api_tokens`
    (`id`, `created_at`, `name`, `comment`, `status`, `expire_at`, `hash`, `salt`, `owner`)
SELECT
    source_token.`id`,
    source_token.`created_at`,
    source_token.`name`,
    source_token.`comment`,
    source_token.`status`,
    source_token.`expire_at`,
    source_token.`hash`,
    source_token.`salt`,
    source_token.`owner`
FROM `devops_staging`.`user_api_tokens` AS source_token
LEFT JOIN `core_server_staging`.`user_api_tokens` AS target_token
  ON target_token.id = source_token.id
WHERE target_token.id IS NULL;

INSERT INTO `core_server_staging`.`user_api_token_ip_access_lists`
    (`user_email`, `created_at`, `updated_at`, `allow_all`, `rules`)
SELECT
    source_access_list.`user_email`,
    source_access_list.`created_at`,
    source_access_list.`updated_at`,
    source_access_list.`allow_all`,
    source_access_list.`rules`
FROM `devops_staging`.`user_api_token_ip_access_lists` AS source_access_list
LEFT JOIN `core_server_staging`.`user_api_token_ip_access_lists` AS target_access_list
  ON target_access_list.user_email = source_access_list.user_email
WHERE target_access_list.user_email 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 business keys, so unrelated existing Lark groups and mappings are not
included.

```sql
SELECT 'custom_user_groups' AS data_set,
       (SELECT COUNT(*)
        FROM `devops_staging`.`user_groups`
        WHERE `source` = 'custom') AS source_rows,
       (SELECT COUNT(*)
        FROM `devops_staging`.`user_groups` AS source_group
        JOIN `core_server_staging`.`user_groups` AS target_group
          ON target_group.name = source_group.name
        WHERE source_group.source = 'custom') AS migrated_rows
UNION ALL
SELECT 'custom_user_group_mappings',
       (SELECT COUNT(*)
        FROM `devops_staging`.`user_group_mappings` AS source_mapping
        JOIN `devops_staging`.`user_groups` AS source_group
          ON source_group.name = source_mapping.group_name
        WHERE source_group.source = 'custom'),
       (SELECT COUNT(*)
        FROM `devops_staging`.`user_group_mappings` AS source_mapping
        JOIN `devops_staging`.`user_groups` AS source_group
          ON source_group.name = source_mapping.group_name
         AND source_group.source = 'custom'
        JOIN `core_server_staging`.`user_group_mappings` AS target_mapping
          ON target_mapping.user_email = source_mapping.user_email
         AND target_mapping.group_name = source_mapping.group_name)
UNION ALL
SELECT 'user_api_tokens',
       (SELECT COUNT(*) FROM `devops_staging`.`user_api_tokens`),
       (SELECT COUNT(*)
        FROM `devops_staging`.`user_api_tokens` AS source_token
        JOIN `core_server_staging`.`user_api_tokens` AS target_token
          ON target_token.id = source_token.id)
UNION ALL
SELECT 'user_api_token_ip_access_lists',
       (SELECT COUNT(*) FROM `devops_staging`.`user_api_token_ip_access_lists`),
       (SELECT COUNT(*)
        FROM `devops_staging`.`user_api_token_ip_access_lists` AS source_access_list
        JOIN `core_server_staging`.`user_api_token_ip_access_lists` AS target_access_list
          ON target_access_list.user_email = source_access_list.user_email);
```

### Field equality

Every `mismatch_rows` value must be zero. Group comparison deliberately excludes
any physical `id` column.

```sql
SELECT 'custom_user_groups' AS data_set, COUNT(*) AS mismatch_rows
FROM `devops_staging`.`user_groups` AS source_group
JOIN `core_server_staging`.`user_groups` AS target_group
  ON target_group.name = source_group.name
WHERE source_group.source = 'custom'
  AND NOT (
    target_group.name <=> source_group.name
    AND target_group.created_at <=> source_group.created_at
    AND target_group.parent_name <=> source_group.parent_name
    AND target_group.description <=> source_group.description
    AND target_group.source <=> source_group.source
  )
UNION ALL
SELECT 'custom_user_group_mappings', COUNT(*)
FROM `devops_staging`.`user_group_mappings` AS source_mapping
JOIN `devops_staging`.`user_groups` AS source_group
  ON source_group.name = source_mapping.group_name
 AND source_group.source = 'custom'
JOIN `core_server_staging`.`user_group_mappings` AS target_mapping
  ON target_mapping.user_email = source_mapping.user_email
 AND target_mapping.group_name = source_mapping.group_name
WHERE NOT (
  target_mapping.created_at <=> source_mapping.created_at
  AND target_mapping.user_email <=> source_mapping.user_email
  AND target_mapping.group_name <=> source_mapping.group_name
)
UNION ALL
SELECT 'user_api_tokens', COUNT(*)
FROM `devops_staging`.`user_api_tokens` AS source_token
JOIN `core_server_staging`.`user_api_tokens` AS target_token
  ON target_token.id = source_token.id
WHERE NOT (
  target_token.id <=> source_token.id
  AND target_token.created_at <=> source_token.created_at
  AND target_token.name <=> source_token.name
  AND target_token.comment <=> source_token.comment
  AND target_token.status <=> source_token.status
  AND target_token.expire_at <=> source_token.expire_at
  AND target_token.hash <=> source_token.hash
  AND target_token.salt <=> source_token.salt
  AND target_token.owner <=> source_token.owner
)
UNION ALL
SELECT 'user_api_token_ip_access_lists', COUNT(*)
FROM `devops_staging`.`user_api_token_ip_access_lists` AS source_access_list
JOIN `core_server_staging`.`user_api_token_ip_access_lists` AS target_access_list
  ON target_access_list.user_email = source_access_list.user_email
WHERE NOT (
  target_access_list.user_email <=> source_access_list.user_email
  AND target_access_list.created_at <=> source_access_list.created_at
  AND target_access_list.updated_at <=> source_access_list.updated_at
  AND target_access_list.allow_all <=> source_access_list.allow_all
  AND target_access_list.rules <=> source_access_list.rules
);
```

### Safe samples

Inspect representative records without printing token hashes or salts:

```sql
SELECT `name`, `created_at`, `parent_name`, `description`, `source`
FROM `core_server_staging`.`user_groups`
WHERE `source` = 'custom'
ORDER BY `name`
LIMIT 20;

SELECT mapping.`created_at`, mapping.`user_email`, mapping.`group_name`
FROM `core_server_staging`.`user_group_mappings` AS mapping
JOIN `core_server_staging`.`user_groups` AS user_group
  ON user_group.name = mapping.group_name
WHERE user_group.source = 'custom'
ORDER BY mapping.group_name, mapping.user_email
LIMIT 20;

SELECT `id`, `created_at`, `name`, `comment`, `status`, `expire_at`, `owner`
FROM `core_server_staging`.`user_api_tokens`
ORDER BY `created_at`, `id`
LIMIT 20;

SELECT `user_email`, `created_at`, `updated_at`, `allow_all`, `rules`
FROM `core_server_staging`.`user_api_token_ip_access_lists`
ORDER BY `user_email`
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:

- An existing active, unexpired API token still authenticates.
- Inactive and expired API tokens remain rejected.
- API token IP allow-all and CIDR restrictions behave as before.
- Custom groups and their memberships are returned correctly.

Restore traffic and User/IAM writes only after these checks pass.