95 lines
5.0 KiB
Markdown
95 lines
5.0 KiB
Markdown
# 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/`](../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`](../migrations/).
|
|
- Legacy helicopter import flow:
|
|
- [`migrations/goose/20260414_helicopter_import_staging.sql`](../migrations/goose/20260414_helicopter_import_staging.sql)
|
|
- [`migrations/goose/20260415_helicopter_import_apply.sql`](../migrations/goose/20260415_helicopter_import_apply.sql)
|
|
- Deprecated helper note: [`migrations/scripts/migration_helicopter.sql`](../migrations/scripts/migration_helicopter.sql)
|
|
- Queue migration notes: [`docs/queue-sqs-migration.md`](./queue-sqs-migration.md)
|
|
- File manager APIs and attachment model:
|
|
- [`docs/file-manager-upload.md`](./file-manager-upload.md)
|
|
- [`docs/file-manager-attachment.md`](./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`](../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`](./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.
|