Data Migration Wiki

This page is the living technical document for migration scope, decisions, limitations, and open tasks. Update it for every migration wave.

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.

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

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.
20260403_file_manager_file_lifecycle_phase1.sql
- Normalizes legacy file lifecycle states (pending/uploading) into newer states (validated/processing).
- Carries upload_error into failure_reason.
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).

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.
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.
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.

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

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 SQS 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

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