MIGRATION_PLAN — reservation-service

Sibling: DATA_MODEL · LOCAL_DEV_SETUP · SERVICE_RISK_REGISTER R-RSV-12

Strategic anchor: 02 Enterprise architecture §Onboarding · standards/SERVICE_TEMPLATE

This document covers two distinct kinds of migration:

1. Schema migrations for the reservation.* Postgres schema (rolling DDL via node-pg-migrate, applied per release).
2. Data migration / onboarding importer that lifts existing tenant bookings out of Excel, CSV, or legacy PMS exports into Melmastoon, mapping legacy statuses, currencies, and channels to the canonical model.

Both flows are tenant-scoped, idempotent, and never notify the guest unless the tenant explicitly opts in.

---

1. Schema migration policy

1.1 Tooling

• node-pg-migrate v6, files under services/reservation-service/migrations/.
• File naming: YYYYMMDDHHmmss_<short_kebab_name>.{up,down}.sql.
• Each migration runs inside a transaction unless it touches large indexes (CREATE INDEX CONCURRENTLY).

1.2 Compatibility rules

• A release introducing a new column must default it (or allow NULL) so the previous application version still runs against the new schema.
• A release that removes a column must follow the expand → contract pattern:
  • Release N: stop reading/writing column.
  • Release N+1: drop column.
• No migration may rewrite an existing row in reservation_modifications (append-only).
• Every new table inherits the standard preamble:
  • tenant_id text NOT NULL
  • created_at timestamptz NOT NULL DEFAULT now()
  • updated_at timestamptz NOT NULL DEFAULT now()
  • version int NOT NULL DEFAULT 0
  • ALTER TABLE … ENABLE ROW LEVEL SECURITY
  • Policy <table>_tenant_isolation
  • Index (tenant_id, …)

1.3 Pre-deploy gate

The CI job migrations-check runs:

1. pg_dump --schema-only of the previous release's schema.
2. The new migrations applied on top.
3. The previous release's app image started against the new schema, smoke test passes (/internal/health + a sample read).
4. The new release's app image started against the new schema, all integration tests green.

Both must succeed for the deploy job to proceed.

---

2. Onboarding importer (legacy bookings)

2.1 Sources supported

• Microsoft Excel .xlsx (most common in Afghan/Iranian small hotels).
• CSV (RFC 4180), UTF-8 with optional BOM.
• Legacy PMS exports (Opera light, Cloudbeds, Hotelogix) via per-vendor adapter.

A reusable column map is supplied per source. Tenants upload via the backoffice "Import bookings" wizard, which calls bff-backoffice-service which calls reservation-service POST /api/v1/admin/imports.

2.2 Required and optional columns

Source column	Maps to	Required?	Notes
booking_ref	legacy_external_id	yes	Used for idempotency
arrival_date	stay_start	yes	ISO or dd/MM/yyyy; tenant tz applied
departure_date	stay_end	yes	Must be > arrival
room_number or room_type	resolved to roomTypeId via property mapping	yes	One of the two
guest_first_name	guests.first_name	yes
guest_last_name	guests.last_name	yes
guest_email	guests.email (encrypted + hashed)	recommended	Skipped if invalid
guest_phone	guests.phone_e164 (encrypted + hashed)	recommended	Normalized to E.164
nationality	guests.nationality	optional	ISO-3166 alpha-2
total_amount	grand_total_micro	yes	Currency × 1_000_000
currency	currency	yes	ISO-4217
payment_method_legacy	payment_method	optional	Lookup table per tenant
channel_legacy	channel	optional	Default direct
notes	special_requests[].text	optional	Free text
status_legacy	status	yes	See §2.3 mapping

2.3 Status mapping

legacy → canonical
─────────────────────
"pending"            → quoted          (then auto-expired if past)
"tentative" / "hold" → held            (with 0-second TTL → expires immediately)
"confirmed"          → confirmed
"checked-in"         → checked_in
"in-house"           → checked_in      (treated as same canonical)
"checked-out"        → checked_out
"completed"          → checked_out
"no-show"            → no_show
"cancelled"          → cancelled
"voided"             → cancelled       (cancellation_reason='voided')
unknown / blank      → IMPORT_FAIL row, surfaced in report

For each imported reservation:

• created_by_type='system', created_by_id='migration_importer'.
• pending_saga_step=NULL (no sagas run for migrated rows).
• Outbox event published as melmastoon.reservation.imported.v1 (operational, 1 y) only; lifecycle events (held, confirmed, etc.) are not synthesized so downstream services don't double-fire notifications, billing, or housekeeping.
• A row in reservation_modifications is appended with kind='import' and the original payload hashed.

2.4 Idempotency

(tenant_id, legacy_external_id) is UNIQUE. Re-uploading the same file produces zero new rows; the importer report shows duplicate=N instead.

2.5 Dry-run vs commit

Wizard always runs dry-run first:

• Validates every row.
• Returns a per-row report (row_no, status: ok | warn | error, messages[]).
• Shows aggregated counts: to_insert, to_update, to_skip, errors.

Tenant ops reviews the report. Commit is a second call with the same upload reference and a confirmation token. Commit is transactional per chunk of 500 rows with progress checkpoints in reservation.import_jobs.

2.6 Report and rollback

• Full report is stored in Cloud Storage (gs://melmastoon-imports/<tenant>/<job>/report.jsonl) for 1 y, then archived.
• Rollback by job ID deletes only rows where created_by_id='migration_importer' AND import_job_id=<id> AND status is still in {quoted, held, confirmed} (never deletes anything that has been operated on after import).

2.7 Suppressed side-effects during import

• notification-service filters events with metadata.source='migration'.
• billing-service does not auto-create folios for migrated rows; tenant can opt-in per job.
• analytics-service ingests but tags is_migrated=true so historical comparisons can exclude.

---

3. Backfill scenarios

3.1 Adding a new column

When a release adds, e.g., reservations.dietary_summary text, a backfill job:

1. New column added NULLable in release N.
2. Background script scripts/backfill-2026-05-dietary-summary.ts runs in a Cloud Run job, pages 1000 rows at a time, computes the value from guest.preferences, writes via UPDATE.
3. Job is restartable (uses last-processed id as cursor).
4. Release N+1 may make the column NOT NULL once backfill is verified.

3.2 Adding a new event version

Producing melmastoon.reservation.confirmed.v2 does not require backfilling v1 events. v1 remains in the schema registry and is still emitted in parallel during the transition window (≥ 30 d) per 04 Event-driven §versioning.

---

4. Tenant offboarding (data export and erasure)

A tenant leaving the platform receives:

1. A full Cloud SQL pg_dump of reservation schema rows where tenant_id=<theirs>.
2. Event archive from BigQuery (SELECT … WHERE metadata.tenantId=<theirs>).
3. An exported CSV of reservations in the same column shape as the importer accepted.

After the contractual export window, GDPR erasure runs:

• Hard delete from reservation.* tables.
• Tombstone rows inserted in audit-service so the deletion is itself auditable.
• Encrypted fields: per-tenant DEKs revoked in KMS; ciphertext that survives in any backup becomes unreadable.

---

5. Acceptance checklist

• Importer dry-run produces an actionable report (no silent failures).
• Re-uploading the same file is a no-op.
• Migrated reservations do not trigger guest notifications.
• Status mapping matches §2.3 for all sample fixtures.
• Rollback by job ID restores prior state for unrolled-back rows.
• Schema expand → contract pattern enforced by CI for every column drop.