Tenant DB Migration Console

The Tenant DB Migration Console is a platform-admin tool for synchronising tenant database schema and routines from a configured source tenant into selected target tenants.

This is not the spreadsheet Import Engine and it is not the go-live customer data migration wizard. It runs database-level DDL and routine refresh operations.

Access

The console requires platform-admin authentication and these permissions:

Permission	Allows
tenant_db_migrations:read	View source configuration, execution history, logs, and live console progress
tenant_db_migrations:configure	Change the source tenant used for comparisons
tenant_db_migrations:run	Queue dry-run and live migration jobs

Only grant run/configure permissions to staff allowed to modify tenant databases.

Migration Slices

Slice	Scope
Schema alignment	Forward-only table synchronisation. It creates missing tables and applies supported column/index/FK changes. It does not drop target-only tables or columns.
Procedure refresh	Compares procedures and replaces changed/missing procedures.
Database logic refresh	Refreshes procedures, functions, triggers, and events from the source tenant.

Procedure refresh and database logic refresh cannot be queued together in one run.

Safe Run Workflow

1. Confirm the source tenant in the console configuration.
2. Select one non-production clone target first.
3. Run Schema alignment as a dry run and inspect the live console output.
4. Run routine refresh dry runs only after schema dry run is clean.
5. Run the same slices live against the clone.
6. Run application smoke tests and import-template tests against the clone.
7. Repeat on production only during a maintenance window.

Live runs require the confirmation phrase RUN LIVE MIGRATION.

Operational Risks

DDL statements auto-commit in MySQL, so a failed live run can leave partial changes. The console records the failed statement and supports retrying failed items, but it is not a transaction rollback system.

Routine refresh can affect stored procedures, functions, triggers, and events. Verify tenant-specific routines before running Database logic refresh.

Test Data Walkthrough

For migration-engine validation, use a cloned tenant database and import representative onboarding data from the same templates users will use:

Area	Test
Trial balance / balance sheet	Import opening GL balances and verify debits equal credits by currency and business unit.
Deposits	Import deposit clients/accounts and opening balances, then verify account balances, GL control balances, and subledger balances.
Loans	Import loan clients/accounts with principal, interest, fees, penalties, and arrears cases. Verify schedules, account balances, and GL/subledger totals.
Catch-up transactions	Import repayments and deposits after the snapshot date and confirm they post through normal procedures.

Keep the clone isolated from production users and integrations. Disable external callbacks, scheduled events, and provider webhooks unless the test explicitly needs them.

Lab Runbook

Use this sequence for the current migration-engine QA pass.

1. Create an isolated tenant clone from the source tenant schema and data.
2. Register the clone in pinkappleadvance.allcompanies with a lab-only alias, for example migration-lab-20260520.
3. Enable migration mode on the clone and set the snapshot date.
4. Download each import template from the clone, not production.
5. Fill only template-supported columns and keep sheet names and headers unchanged.
6. Validate first, execute second, then reconcile GL, loan, and deposit balances.

Template-Aligned Rows

Use rows shaped like these when building the workbook or SQL-driven test fixtures:

Import	Sheet	Required columns to cover
TB / balance sheet opening balances	TB Opening Balances	coa_id, account_code, account_name, amount, direction, narration
Deposit account opening balances	Deposit Accounts	client_id, account_number, opening_balance, account_status, opened_on
Loan snapshot balances	Loan Snapshots	parent_loan_ref, loan_product_id, holder_type, client_id, group_id, disbursement_date, as_of_date, principal_amount_total, outstanding_balance_total, outstanding_principal, outstanding_interest, outstanding_fees, outstanding_penalty

Test Cases

Case	Input shape	Expected result
Balanced TB import	Debit asset rows equal credit liability/equity rows	Validation passes, batch posts to daily_coa_balance, reconciliation has zero imbalance
Unbalanced TB import	Debit total differs from credit total	Validation or reconciliation flags imbalance before sign-off
Active deposit opening balance	Active client, approved deposit product, positive opening_balance	Deposit account is created with migration opening transaction and GL/subledger posting
Zero-balance deposit	Active client, approved product, opening_balance = 0	Account imports without opening transaction and remains reconcilable
Performing loan snapshot	Active client, approved loan product, principal and interest outstanding	Loan account imports with expected outstanding components and GL opening balances
Arrears loan snapshot	days_in_arrears > 0, penalty/fee components present	Loan account imports with arrears state and reconciliation includes all balance components
Catch-up repayment	Repayment after snapshot date	Procedure posts through normal repayment path and reduces outstanding balance
Catch-up deposit	Deposit after snapshot date	Procedure posts through normal deposit path and updates deposit balance

Opening Balance Contra Account

Migration opening balances use the MIGRATION_OPENING_BALANCE_CONTRA system tag as the temporary offset for domain opening balances. This account is not meant to be a bank account or teller cash account. It should be bound before import execution to the institution's configured migration clearing account, or to the non-teller cash-at-hand account when the migration policy treats brought-forward balances as opening cash-backed positions.

The contra account is used whenever the migration engine posts an opening balance without importing the full opposite side of the entry in the same row set:

Domain opening posted	Migration debit/credit pattern
Loan snapshots	Debit loan principal, interest, fee, and penalty receivable/control accounts; credit the migration contra account
Deposit balances	Credit deposit liability accounts; debit the migration contra account
TB / BS rows	Post the imported row direction and auto-generate the opposite contra line

The contra account is reconciled during migration sign-off, not during normal daily operations. Run reconciliation after each import stage and again before locking migration:

1. Confirm every posted migration batch has the expected source totals and journal or balance audit.
2. Confirm loan/deposit source balances match the posted GL/component balances.
3. Review the contra balance. A remaining contra balance is expected while only one side of opening balances has been imported.
4. Once all opening domains are imported, the contra should match the intended net opening position. If the full trial balance already includes equity/cash/bank balances, do not duplicate the same loan/deposit balances through separate opening postings.
5. Revert or adjust incorrect migration batches before locking migration. After lock, any cleanup should be done through controlled GL adjustment journals.

If the contra is accidentally bound to a real bank or teller account, migration postings will contaminate operational cash reporting. Rebind the tag before posting, or reverse/adjust any batches already posted under the wrong binding.

API Flow

Step	Endpoint
Enable migration mode	POST /api/migration/enable
Download TB template	GET /api/migration/tb/template
Validate TB rows	POST /api/migration/tb/validate
Execute TB rows	POST /api/migration/tb/execute
Download deposit template	GET /api/deposit-accounts/import/template?deposit_product_id={id}
Validate deposit workbook	POST /api/deposit-accounts/import/validate
Execute deposit import	POST /api/deposit-accounts/import/execute
Download loan snapshot template	GET /api/loans/import/template?loan_product_id={id}
Validate loan snapshot workbook	POST /api/loans/import/validate
Execute loan snapshot import	POST /api/loans/import/execute
Reconcile migration batches	GET /api/migration/reconciliation/batches
Reconcile GL summary	GET /api/migration/reconciliation/gl-summary
Reconcile loans	GET /api/migration/reconciliation/loans
Reconcile deposits	GET /api/migration/reconciliation/deposits