MIGRATION_PLAN — billing-service

Two migration tracks: (a) per-tenant schema migrations for the tenant_<uuid>_billing schemas, and (b) legacy folio importer for tenants migrating from a prior PMS / legacy billing.

1. Per-tenant schema migrations

1.1 Tooling

• DDL bundle templated at db/tenants/billing.tmpl.sql (substitutes :tenant_id).
• Versioned migrations live under db/tenants/migrations/<seq>__<name>.sql and <seq>__<name>.down.sql.
• The billing-tenant-migrator (Cloud Run Job) iterates tenants WHERE active = true, takes a per-tenant Postgres advisory lock (pg_advisory_lock(hashtext('billing.migrate.<tenantId>'))), reads tenant_<uuid>_billing._migrations, applies pending forward migrations in order, then releases the lock.
• A second job (billing-tenant-migrator-verify) compares the tenant schema's pg_catalog.pg_attribute snapshot to the expected hash; alerts on drift.

1.2 Lifecycle

• On melmastoon.tenant.created.v1: the migrator creates tenant_<uuid>_billing, runs every migration up to HEAD, seeds cash_drawers placeholder. Acks within 30 s.
• On release: the deploy pipeline runs the migrator for all tenants before flipping billing-api traffic.
• On melmastoon.tenant.deleted.v1 (Day 0): rename schema tenant_<uuid>_billing__archived_<yyyymmdd>; revoke writes.
• Day +90: archive job drops the renamed schema and deletes the per-tenant invoice bucket.

1.3 Backward compatibility (expand → contract)

For any non-trivial change:

1. Release N (expand): add new column / table / index; fill via backfill SQL; deploy API that reads new and old, writes both.
2. Release N+1 (cleanup): flip API to read/write new only; drop old after 14 days of green metrics.

This matches the platform-wide pattern referenced in 02 §11.

1.4 Backfill jobs

Backfills run as Cloud Run Jobs with the same per-tenant fanout pattern as the migrator. Backfills are:

• Idempotent (every row checked against post-state).
• Resumable (cursor stored in _backfill_runs table).
• Throttled (SET LOCAL statement_timeout = '60s', batch size capped at 1,000 rows).

1.5 Rollback

down migrations exist for every forward migration; rollback is per tenant and runs under the same advisory lock. Data-loss-prone migrations (e.g., dropping a column) carry a data-loss=true annotation that requires explicit operator confirmation.

2. Legacy folio importer

For tenants migrating from a prior PMS or homegrown billing (the typical Ghasi Melmastoon launch path), the importer ingests historical folios into the new per-tenant schema with a read-only "imported" provenance.

2.1 Inputs

The importer accepts two formats:

• CSV bundle (one zip per tenant) with files: folios.csv, charges.csv, payments.csv, refunds.csv, invoices.csv, cash_sessions.csv (optional).
• JSON-lines (*.jsonl) for tenants with a programmatic export.

A schema validator (zod) rejects rows that fail constraints; rejects are written to a per-tenant *.rejects.csv for the tenant to review.

2.2 Mapping rules

Legacy field	Mapped to	Notes
legacy_folio_id	folios.id = 'fol_imp_' + ulid(legacy_folio_id)	deterministic id keeps re-import idempotent
total (signed decimal)	sum of imported folio_charges.gross_micro and folio_payments.amount_micro	reconciled at end of import per folio
tax_rate (per folio)	per-charge tax_amount_micro reconstructed by uniform allocation	flagged with source.kind='import'
currency	folios.currency (ISO4217 normalized)	rejects non-ISO codes
closed_at	folios.closed_at and status='closed'	open folios disallowed in import; tenant must close in legacy first
invoice_number	invoices.number (preserved)	uniqueness per tenant enforced
pdf_url (legacy)	invoices.pdf_uri if it begins with gs://; otherwise re-uploaded by the importer to billing-invoices-<tenantId>

Imported rows carry metadata.imported = true and metadata.legacyId = <legacy_folio_id> for provenance.

2.3 Process

1. Tenant uploads bundle to gs://billing-imports-<tenantId>/<batchId>/.
2. Tenant admin POSTs /api/v1/imports/legacy with the bundle URI, gets a jobId.
3. The legacy-import-job (Cloud Run Job) runs:
   • Validate; write rejects.
   • Apply per-tenant schema (idempotent re-import).
   • Reconcile per-folio totals; flag mismatches.
   • Emit a single melmastoon.billing.imports.completed.v1 (internal topic; not part of the public catalog) with counts.
4. Tenant reviews report at /api/v1/imports/:jobId/report.

2.4 Constraints

• Imports do not trigger folio.opened.v1 / folio.closed.v1 / invoice.generated.v1 (no downstream notifications fire for historical data).
• Imports do not call the tax engine (taxes are imported as-is); flagged with tax_jurisdiction = 'IMPORTED'.
• Imports cannot touch subscription billing (subscription is a forward-only relationship).
• Cash sessions can be imported as closed only.

2.5 Verification

After import:

• per-folio total parity check vs. legacy export;
• per-day cash totals parity check against legacy daily reports;
• invoice-number sequence continuity check (informational; some legacy systems have gaps).

3. Subscription central schema migrations

Run by the standard drizzle-kit migrate against the central schema in the same release pipeline as billing-api. Same expand → contract discipline. Backfills for usage_records partitions handled by the partition-management job that pre-creates next-month partition on the 25th of every month.

4. ID-prefix introductions (one-time)

The first release ships with the new ID prefixes added in this bundle:

• fpm_, frd_, cnt_, cnl_, set_, cdr_, cds_, dcr_, sub_, sin_, sil_, usg_, pln_, ln_.

These are added to docs/standards/NAMING.md §6 in the same PR; CI validates.

5. Cross-references

• Per-tenant schema definitions: DATA_MODEL §2.
• Multi-tenancy lifecycle: ADR-0002 §3.
• Release & deployment pipeline: DEPLOYMENT_TOPOLOGY §7.
• Reconciliation patterns invoked during import: 10 Payments §12.