fm_be/docs/data-migration-wiki.md

Data Migration Wiki

This page is the living technical document for migration scope, decisions, limitations, and open tasks. Update it for every migration wave. For CKEditor copy-paste, use the HTML version: docs/data-migration-wiki-ckeditor.html.

Scope

• Database schema migrations in migrations/.
• Data migration SQL (legacy to current domain model).
• Non-DB migration concerns: binary files (PDF/images), queue backlog, manual user actions.

Source of Truth

• Versioned DB migrations: migrations/*.sql.
• Legacy helicopter import flow:
  • migrations/goose/20260414_helicopter_import_staging.sql
  • migrations/goose/20260415_helicopter_import_apply.sql
  • Deprecated helper note: migrations/scripts/migration_helicopter.sql
• Queue migration notes: docs/queue-sqs-migration.md
• File manager APIs and attachment model:
  • docs/file-manager-upload.md
  • docs/file-manager-attachment.md

Implemented Data Migrations

1. 20260326_move_responsible_flight_rescuer_to_air_rescuer_profiles.sql

• Copies pilot_profiles.responsible_flight_rescuer into air_rescuer_profiles.responsible_flight_rescuer.
• Drops old column from pilot_profiles.

2. 20260403_file_manager_file_lifecycle_phase1.sql

• Normalizes legacy file lifecycle states (pending/uploading) into newer states (validated/processing) and carries upload_error into failure_reason.

3. 20260415_helicopter_import_apply.sql

• Imports rows from helicopter_old_import into helicopters.
• Maintains old-to-new ID mapping via helicopter_old_id_map.
• Explicitly non-reversible (Down keeps data as audit trail).

Known Migration Decisions and Gaps

Confirmed "Will Not Migrate" (Current Decision)

1. Legacy helicopter columns intentionally ignored:

• counter
• service
• disabled
• visible
• Decision is documented in migrations/scripts/migration_helicopter.sql.

Confirmed "Not Migrated Yet" (Open)

1. Legacy SQS queue backlog

• Current worker does not consume old SQS backlog.
• Required action: drain/discard SQS backlog before/at cutover.
• Source: docs/queue-sqs-migration.md.

2. Binary files (PDF/images) into new file manager storage

• Current migrations create file manager tables and attachment relations, but no SQL migration imports binary objects or bulk-inserts legacy metadata into file_files/attachments.
• This means legacy documents may need manual re-upload unless a dedicated import job is created.

3. Legacy photo_file_id references in contact profile tables

• Contact profile schemas still include photo_file_id string columns.
• No migration currently maps these into attachments + image_attachment_id/foto_attachment_id.

Manual Actions Required at Cutover

1. Validate which legacy PDFs/images must remain accessible on day 1.
2. For files without automated import, perform manual re-upload via file manager flow.
3. Re-link uploaded files to business references (attachments) and to entity-level attachment columns where relevant.
4. Obtain client sign-off for any files intentionally left unmigrated.

Migration TODO Register

ID	Item	Type	Status	Owner	Notes
MIG-001	Decide final strategy for legacy PDFs/images: automated import vs manual re-upload	Decision	Open	TBD	Blocks final cutover checklist
MIG-002	Produce inventory of all legacy file references and classify critical/non-critical	Analysis	Open	TBD	Include PDFs + photos
MIG-003	If automation chosen, implement importer to populate file_files and attachments safely	Engineering	Open	TBD	Prefer idempotent batch import with audit log
MIG-004	Define mapping from legacy contact photo_file_id to new attachment model	Engineering	Open	TBD	Covers pilot/doctor/air rescuer/technician/FA/staff
MIG-005	Document and execute Redis backlog drain/discard plan before production cutover	Ops	Open	TBD	See queue migration doc
MIG-006	Create client-facing exception list for data that cannot/will not be migrated	Governance	Open	TBD	Must be approved before go-live

Cutover Validation Checklist

1. Run DB migrations in target environment.
2. Run all required data migration scripts (including helicopter import flow if applicable).
3. Validate row counts and sample records for migrated domains.
4. Validate file accessibility for mandatory records (especially PDFs needed operationally).
5. Validate queue behavior post-cutover (pending to published, no legacy Redis dependency).
6. Record migration result, exceptions, and follow-up tickets in this page.

Change Log

• 2026-04-17: Initial wiki created from current migration scripts and docs.