140 lines
8.5 KiB
Markdown
140 lines
8.5 KiB
Markdown
# Legacy MariaDB long-lived data migration
|
|
|
|
## Scope and safety boundary
|
|
|
|
`tools/legacy-db-migration` is the only supported importer. It has no HTTP
|
|
entrypoint, defaults to a read-only dry-run, and requires `--apply` for target
|
|
writes. Database URLs are accepted only through environment variables.
|
|
PostgreSQL advisory locks serialize an apply per profile, and every target row
|
|
uses a stable legacy key with `ON CONFLICT`, so an interrupted run is
|
|
repeatable.
|
|
|
|
The source of truth for eligibility is the checked ref schema, not every table
|
|
that happens to exist in a dump. Tables outside that schema remain only in the
|
|
recovery dump.
|
|
|
|
### Gateway
|
|
|
|
| Legacy table | Target | Policy |
|
|
| --------------- | ----------------------------- | ------------------------------------------------------------------------------- |
|
|
| `member` | `app_user` plus `legacy_data` | Preserve identity, roles/ACL, sanctions, OAuth metadata, password hash and salt |
|
|
| `member_log` | `legacy_member_log` | Preserve complete JSON action history |
|
|
| `banned_member` | `legacy_banned_member` | Preserve hashed-email ban |
|
|
| `storage` | `legacy_root_key_value` | Preserve raw namespace/key/JSON value |
|
|
| `system` | `system` | Preserve registration/login switches and notice |
|
|
| `login_token` | none | Exclude expired bearer tokens, IP addresses and obsolete PHP sessions |
|
|
|
|
Legacy member numbers map to deterministic UUIDs. Existing rows are updated by
|
|
that UUID, so references such as `ng_old_generals.owner` remain stable even
|
|
when an old account was deleted before the dump.
|
|
|
|
Kakao members retain `oauth_id`, email and metadata. Cutover sets
|
|
`kakao_verified_at` and `kakao_grace_started_at` to the migration time and starts
|
|
the verification grace period there. Source rows without an OAuth ID retain
|
|
their metadata, but the importer does not invent a provider identifier.
|
|
|
|
Legacy password hashes remain usable when gateway-api has
|
|
`GATEWAY_LEGACY_PASSWORD_GLOBAL_SALT`; a successful login upgrades the stored
|
|
value to Argon2id. A test-only account can instead be reset with the CLI and a
|
|
mode-0600 password file:
|
|
|
|
```sh
|
|
GATEWAY_DATABASE_URL=... pnpm migrate:legacy -- \
|
|
reset-password --login-id test-user --password-file /secure/password --apply
|
|
```
|
|
|
|
The password is never accepted as an argument or printed.
|
|
|
|
### Game profiles
|
|
|
|
| Legacy table | Target | Policy |
|
|
| ------------------------------- | ------------------------ | --------------------------------------------------------------------- |
|
|
| `ng_games` | `ng_games` | Preserve completed season metadata |
|
|
| `hall` | `hall` | Preserve hall-of-fame rows |
|
|
| `ng_old_generals` | `ng_old_generals` | Preserve full JSON snapshots and owner |
|
|
| `ng_old_nations` | `ng_old_nations` | Preserve all versions, including duplicate server/nation pairs |
|
|
| `emperior` | `emperior` | Preserve dynasty detail and legacy key |
|
|
| `inheritance_result` | `inheritance_result` | Preserve result JSON/string and legacy key |
|
|
| `user_record` | `inheritance_log` | Preserve complete long-lived user record |
|
|
| persistent `storage` namespaces | `legacy_game_storage` | Preserve raw `inheritance_*` and `user_*` rows before projection |
|
|
| `storage:inheritance_point` | `inheritance_point` | Project the numeric first tuple item; retain the tuple in raw storage |
|
|
| `storage:user` | `inheritance_user_state` | Project known current inheritance state; retain raw storage |
|
|
| `ng_history` | `yearbook_history` | Preserve map, nation, global history and global action snapshots |
|
|
|
|
The source contains legitimate duplicate `(server_id, nation)` old-nation rows
|
|
and `(server_id, year, month)` history rows. `source_id` is consequently part of
|
|
the archive unique keys. Runtime-generated rows use `source_id = 0`; migrated
|
|
rows use the legacy primary key. This avoids a lossy last-row-wins upsert.
|
|
|
|
Current-season actor/world/queue/lock/message/market/vote state is explicitly
|
|
excluded. In particular, `general`, `city`, `nation`, their turn queues,
|
|
`general_record`, `world_history`, `statistic`, `board`, `comment`,
|
|
`diplomacy`, `ng_diplomacy`, `event`, `message`, `rank_data`, `troop`,
|
|
`tournament`, `vote`, `vote_comment`, season-scoped/unknown `storage`,
|
|
`ng_auction`, `ng_auction_bid`,
|
|
`ng_betting`, `reserved_open`, `select_pool`, `select_npc_token` and `plock`
|
|
must not be used to reconstruct a running season.
|
|
|
|
### Current-season comparison fixture
|
|
|
|
The archive exclusion above remains the production migration contract. The
|
|
same CLI also provides a separately guarded `current-season-fixture` command for
|
|
an isolated Ref/Core comparison database only. It requires a Core template and
|
|
Ref source that already match the explicitly supplied scenario/year/month, and
|
|
an apply requires `--replace-current-season --apply` together.
|
|
|
|
Within one target transaction it truncates season-owned tables and imports the
|
|
Ref world clock, dynamic city fields, nations, generals, command queues,
|
|
diplomacy matrix, troops, ranks, messages, access log, events, betting, auctions,
|
|
yearbook, current storage audit rows and general/world logs. Static Core city
|
|
geometry and connection metadata remain from the cloned template. Legacy
|
|
message payload targets are renamed from `id`/`nation_id` to
|
|
`generalId`/`nationId` so the typed Core API can read them.
|
|
|
|
The command refuses an active turn-daemon lease. It intentionally omits process
|
|
locks, reservations and selection tokens, Redis-owned tournament state,
|
|
`statistic` aggregate text and `ng_diplomacy` letters; the result reports each
|
|
unsupported category. Owner binding for a browser-capture account is explicit
|
|
through `CURRENT_SEASON_CAPTURE_USER_ID` and
|
|
`CURRENT_SEASON_CAPTURE_SOURCE_OWNER`. This mode must not be used for a live
|
|
season or as a substitute for the long-lived archive cutover procedure.
|
|
|
|
## Archived play read model
|
|
|
|
`/past-plays` is an authenticated, read-only projection. The server derives the
|
|
archive owner from the game session and never accepts an owner ID from the
|
|
browser.
|
|
|
|
- `archive.myPastPlays` combines the owner's `ng_old_generals` rows with
|
|
`ng_games`, the latest matching `ng_old_nations` snapshot and an optional
|
|
`emperior` row. It returns summary fields and a link target for the existing
|
|
public dynasty/nation detail.
|
|
- `archive.myPastPlayDetail(serverId, generalNo)` includes the session owner in
|
|
the database predicate before returning `data.history`. A foreign or missing
|
|
record uses the same not-found response.
|
|
- Legacy `data.history` may be either an array or a `<br>`-joined string. The API
|
|
normalizes both to a newest-first string array, and the frontend renders plain
|
|
text rather than archived markup.
|
|
- Runtime death and unification archival writes the current general
|
|
`GENERAL/HISTORY` rows into the same `data.history` field, so newly completed
|
|
seasons remain compatible with imported rows.
|
|
|
|
## Cutover procedure
|
|
|
|
1. Keep the original compressed dumps immutable and restore each source to a
|
|
private MariaDB instance.
|
|
2. Deploy the gateway and game Prisma migrations to empty staging databases.
|
|
3. Run gateway and each non-empty profile without `--apply`; archive the JSON
|
|
counts and excluded-table reasons.
|
|
4. Compare source counts, malformed JSON checks and duplicate natural-key
|
|
counts. Stop on unexplained drift.
|
|
5. Put the affected target in maintenance mode, take a PostgreSQL backup, then
|
|
run the same commands with `--apply`.
|
|
6. Repeat each apply. Counts must remain unchanged.
|
|
7. Verify Kakao migration timestamps, password-hash shapes, archive ownership,
|
|
old-nation/history duplicate preservation, `/past-plays` list/detail access,
|
|
foreign-owner denial and the dynasty link.
|
|
8. Retain the MariaDB dumps as rollback evidence. Rollback restores the
|
|
pre-cutover PostgreSQL backup; it does not reverse individual importer
|
|
upserts.
|