Backfill MongoDB data into PostgreSQL and verify ongoing parity

Commencez par le contrat de domaine et le schéma d’expansion de Room/RaceSession issus de #200 et #203, puis examinez le contexte de dual-write et de deployment dans PR #171 et #176, ainsi que l’historique des migrations de schéma. Retracez les mappings requis et les contraintes de rollback avant de définir le backfill reprenable. Le travail est considéré comme terminé lorsque les vérifications de reconciliation, de retry et de concurrency, les tests ciblés et la documentation opérationnelle couvrent les critères d’acceptation listés.

Part of #199.

Goal

Turn PostgreSQL dual writes into a trustworthy migration target by copying historical MongoDB data and continuously checking parity.

For the competition-room foundation, this backfill must target the new Room/RaceSession model rather than preserving the current assumption that a room is itself one racing session.

RaceSession mapping

• Convert each existing MongoDB room into one PostgreSQL Room plus one RaceSession for its current event.
• Attach every embedded attempt to that RaceSession while preserving deterministic attempt and solve IDs.
• Populate session participant state according to the approved domain contract.
• Preserve room identity, access code, ownership, privacy, timestamps, and deletion/expiration state.
• Make the mapping explicit and repeatable; do not attempt to infer event sessions whose attempts were already deleted by the legacy event-change behavior.
• Verify concurrent live writes are neither lost nor duplicated during the backfill.

Acceptance criteria

• Implement an idempotent, resumable backfill for users/preferences, rooms, race sessions, participant state, attempts, durable solve results, and sanitized metrics.
• Support dry-run, bounded batches, progress checkpoints, and safe retries.
• Do not copy OAuth access tokens or prohibited metric fields.
• Produce reconciliation output for record counts, missing identifiers, duplicate identifiers, room/session relationships, penalty fields, and representative aggregates.
• Verify every attempt belongs to the expected RaceSession and every RaceSession belongs to the expected Room.
• Verify writes arriving during the backfill are not lost or duplicated.
• Document operational invocation, expected runtime/load, alerts, failure recovery, and how to rerun only failed ranges.
• Add focused tests for deterministic IDs, cursor boundaries, retry behavior, session mapping, and reconciliation failures.

Dependencies

• Stable production dual writes from PR #171, deployed through #176.
• The Room/RaceSession domain contract and expansion schema from #200 and #203.
• The schema and migration history must remain compatible with rollback requirements.

Unlocks

• #202
• #174
• #160