Skip to main content

property-service — MIGRATION_PLAN

Companion: DATA_MODEL · DEPLOYMENT_TOPOLOGY · SECURITY_MODEL · SERVICE_READINESS

This document covers two related concerns:

  1. Schema migration discipline for the property Postgres schema (how we change tables without downtime).
  2. Tenant onboarding migration — how a new tenant's properties / rooms get into property-service from the source they previously used (spreadsheet, prior PMS, manual entry).

Both flows are gate-controlled; both are reversible up to a documented horizon.

1. Schema Migration Discipline​

1.1 Principles​

  • Expand → backfill → contract. Never combine a column add with a column drop in one release.
  • Backwards compatibility for one release. The new code must read both old and new shapes during the contract window.
  • Migrations are code-reviewed. Every migration ships with a paired down.sql and an integration test.
  • Run as a Cloud Run Job before traffic shifts. Advisory lock prevents concurrent execution.
  • No long-running locks. All ALTERs use safe patterns: CREATE INDEX CONCURRENTLY, ADD COLUMN ... DEFAULT NULL (avoid table rewrite), backfills batched with throttling.

1.2 Workflow per change​

  1. PR adds a forward migration (NNNN_add_x.sql) and a NNNN_add_x.down.sql.
  2. CI verifies: forward migration applies on a fresh copy of the schema; integration tests pass; tenant-isolation test exists for any new multi-tenant table.
  3. Cloud Deploy runs the migration job in staging. SLO check: 30 min of clean burn before promoting.
  4. Production deploy: migration job runs first; canary 10 % → 50 % → 100 % traffic shift with auto-rollback on SLO burn.
  5. After the release stabilizes, a contract migration removes the old shape (separate PR + same workflow).

1.3 Patterns​

  • Adding a non-null column. Add nullable → backfill in batches → add NOT NULL constraint with NOT VALID then VALIDATE CONSTRAINT.
  • Renaming a column. Add new column → backfill → write to both → flip readers → stop writing old → drop old.
  • Splitting a column (e.g., from a flat address to per-locale rows in property_translations). Same expand/backfill/contract pattern, with a transitional view exposing both.
  • PostGIS index changes. Always CREATE INDEX CONCURRENTLY; verify usage via EXPLAIN.
  • RLS changes. Always FORCE rls; ship the integration test that proves cross-tenant access returns 0 rows.

1.4 Rollback​

  • Auto-rollback at the deploy layer reverts code; the paired down.sql is applied only by an on-call runbook step, never automatically (a rolled-back release that already wrote new-shape data risks data loss on down.sql).
  • For destructive down.sql (e.g., drops a column with data), the runbook requires Cloud SQL PITR snapshot before applying.

2. Tenant Onboarding Migration​

2.1 Source patterns (in expected priority)​

  1. Spreadsheet (CSV / XLSX) export — most small tenants today.
  2. Prior PMS export — Cloudbeds, Mews, Hotelogix in known formats; mapping documented per provider.
  3. Manual entry — for tenants without any prior structured data; covered by the standard backoffice CRUD UI.

2.2 Tooling​

tools/property-onboarding/ (in the future application monorepo) ships:

  • mappers/<source>/index.ts — translates source rows to internal DTOs.
  • validate.ts — dry-run validator producing a per-row report (errors + warnings).
  • import.ts — performs the import via the public API (idempotent via Idempotency-Key).
  • report.ts — emits a per-tenant onboarding summary.

The tool runs as the operator's user (uses their JWT), so all created rows carry the operator as actor_user_id for audit.

2.3 Procedure​

  1. Discovery. Operator uploads source file; tool produces a dry-run report.
  2. Validation. Operator reviews:
    • Required fields present per DOMAIN_MODEL §3.
    • At least one locale per property; default locale present.
    • Geo coords if available; otherwise marked for geocoding pass.
    • Room number uniqueness per property.
    • Amenity codes mapped to canonical registry (unknown codes flagged; new codes require a separate platform-admin PR before import).
  3. Geocoding pass. For rows without geo, a batched call to geo-service; low-confidence results queued for HITL with optional AI fallback.
  4. Import. Idempotent batched POSTs (/properties, /properties/:id/room-types, /properties/:id/rooms/bulk). Each batch carries an Idempotency-Key derived from the source file checksum + row range.
  5. Photo seeding. Optional second-pass that uploads bulk photos via signed-URL flow.
  6. Publish. Operator reviews the imported draft properties in the desktop UI and publishes individually. The import tool never auto-publishes.

2.4 Reversibility​

  • Each onboarding session has a unique session id stored in the audit log.
  • An operator can request a roll-back of the entire session within 30 days: the platform tool deletes (soft-delete + then hard purge) all rows tagged with that session, after confirming no reservations reference any of the imported rooms.

2.5 Performance​

  • Default batch size: 50 properties or 200 rooms per HTTP call (whichever first).
  • Throughput target: 1000 rooms imported per minute per tenant on a clean run.
  • Long-running imports surface progress to the desktop UI via the standard async job mechanism (out of scope for this service; provided by bff-backoffice-service).

3. Cross-Service Considerations​

  • pricing-service. No price data is migrated by property-service. Operators set rate plans separately after import.
  • inventory-service. Generates inventory rows automatically from property.room.created.v1 events; no separate import needed.
  • housekeeping-service. Defaults all imported rooms to status dirty (operational), independent of the physical active status owned by property-service.
  • search-aggregation-service. Picks up imported properties via standard event subscriptions; no separate seeding.

4. Cutover Pattern (legacy system → Melmastoon for an existing tenant)​

For tenants moving from a live external PMS to Melmastoon while continuing operations:

  1. Shadow run for 1–2 weeks: import data nightly, compare drift, do not yet flip booking traffic.
  2. Read-only switchover: tenant booking site reads from Melmastoon; bookings still flow via the legacy system.
  3. Write switchover: bookings flow into reservation-service. The legacy system enters read-only.
  4. Decommission: after 30 days of clean operation and after compliance retention obligations are accounted for, the legacy export is archived.

Each step is documented in a per-tenant onboarding runbook owned by the customer success team.

Migration history is recorded permanently; the audit log lets us reconstruct the exact import that produced any property row.