This runbook covers migration from an existing LOS, CRM, core lending database, spreadsheets, document stores, or mixed sources into the new LOS.
The source system is always treated as read-only. Migration runs are repeatable, checkpointed, idempotent, auditable, and reconciled before cutover.
| Track | Tooling | Purpose |
|---|---|---|
| LOS schema evolution | Alembic | Create/change new PostgreSQL structures |
| Legacy data migration | Manifest-driven ETL | Profile, map, transform, stage, validate, and reconcile existing records |
Never combine these tracks in one script or deployment transaction.
- PostgreSQL
- MySQL/MariaDB
- Microsoft SQL Server
- Oracle
- CSV/JSON exports
- Document/object-store inventory files
Each database requires its approved SQLAlchemy dialect/driver, a read-only account, network allowlisting, TLS, and a source-specific extraction adapter. The checked-in manifest validator supports these source labels; actual connectors are added only after the source inventory is approved.
- Identify every database, schema, file share, object store, and downstream report.
- Assign business owners and data stewards per entity.
- Record volume, growth, retention, PII classification, encryption, timezone, and availability window.
- Identify duplicate systems of record and select the authoritative source per field.
- Freeze undocumented schema changes during the migration window.
Deliverables: source inventory, ownership matrix, data classification, initial timeline.
Profile counts, nulls, uniqueness, formats, invalid values, orphan relationships, date ranges, duplicates, amount totals, stage distribution, and document references.
Profiling output must not contain raw PAN, Aadhaar, bank account, phone, email, or bureau data. Use aggregates and salted hashes where comparisons require identity.
Deliverables: profiling report, quality scorecard, remediation backlog.
Create a version-controlled manifest based on legacy-manifest.example.json.
The map defines:
- Source system/table/key
- Target table
- Field mappings
- Required fields
- Incremental checkpoint field
- Batch size
- Reconciliation rules
- Transformation and reference-data decisions documented alongside the manifest
Validate and view the execution plan:
python -m backend.app.legacy_migration.cli validate \
--manifest docs/migrations/examples/legacy-manifest.example.json
python -m backend.app.legacy_migration.cli plan \
--manifest docs/migrations/examples/legacy-manifest.example.jsonThe same validation is exposed without connecting to a source database:
curl -X POST http://localhost:8000/api/v1/migration-manifests/validate \
-H 'Content-Type: application/json' \
--data @docs/migrations/examples/legacy-manifest.example.jsonDefine transformations explicitly and test them independently:
- Legacy status to canonical workflow stage
- Product and branch reference-data mapping
- Currency/amount normalization
- Timezone and timestamp normalization to UTC
- Borrower/co-borrower deduplication
- Address normalization without overwriting source evidence
- Document URI and checksum mapping
- Consent, bureau, KYC, and audit-history treatment
- Closed/rejected/cancelled loan retention
Never fabricate missing regulated values. Route missing or conflicting data to a remediation queue with an error code and hashed source key.
Run the complete extract/transform/validate/reconcile pipeline into an isolated staging target.
Required evidence:
- Extracted, accepted, rejected, and duplicate counts
- Source-to-target count reconciliation
- Amount/control-total reconciliation
- Referential-integrity results
- Stage/product/branch distribution comparison
- Sample business-owner review
- Runtime, throughput, retry, and storage measurements
- Rejection inventory grouped by fixable reason
No dry-run data is promoted into production.
Repeat against production-sized sanitized or approved data until:
- All critical entities reconcile exactly
- Financial control totals meet the approved tolerance
- No unexplained orphan or duplicate records remain
- Critical field accuracy reaches the signed-off threshold
- Runtime fits the cutover window
- Rerunning the same batch produces no duplicates or unintended updates
Perform a bulk initial load, followed by checkpointed delta loads using an immutable sequence or reliable updated_at field.
If the source lacks a reliable change marker, use database change capture approved by the source owner or perform a controlled full comparison. Do not assume timestamps are complete.
Every target record retains source_system and legacy_id. The unique source/legacy constraint makes loads idempotent.
Example cutover sequence:
T-7d final rehearsal and sign-off
T-1d confirm backups, contacts, credentials, and abort thresholds
T-2h stop non-essential legacy batch jobs
T-30m place legacy system into controlled read-only mode
T0 capture final checkpoint and run final delta
T+ reconcile critical counts/totals and verify documents
T+ enable LOS writes for the approved cohort
T+ monitor and begin hypercare
Cutover approval requires business, data, engineering, security, compliance, operations, and release-owner sign-off.
Rollback is a business/data decision, not merely a code deployment.
Before cutover define:
- Decision owner and deadline
- Abort thresholds
- Whether legacy writes can safely resume
- Treatment of transactions created in the new LOS
- Reverse-sync policy, normally avoided unless explicitly designed and tested
- Communication and customer-impact process
If rollback occurs after new LOS writes begin, preserve those records in a sealed reconciliation set. Never delete them to make counts match.
- Monitor rejects, stage discrepancies, document access, task queues, provider references, reports, and complaints.
- Reconcile daily until the signed-off stability period ends.
- Retain migration manifests, code, run records, errors, approvals, and reports under the audit-retention policy.
- Decommission the legacy source only after legal retention, reporting, support, and restore obligations are satisfied.
A typical dependency-aware order is:
- Organisations, branches, users, and roles
- Reference data and products
- Borrowers and related parties
- Applications and loan terms
- Addresses, employment, income, and obligations
- Properties and collateral
- Documents and checksums
- Consents, KYC, bureau, and verification references
- Conditions, decisions, approvals, and overrides
- Tasks, communications, complaints, and audit history
- Closing, disbursement, and reporting records
| Control | Required result |
|---|---|
| Record count | Exact or approved/explained exclusions |
| Unique legacy keys | No unexplained duplicates |
| Financial totals | Exact or signed-off tolerance |
| Required fields | 100% or remediated rejection |
| Foreign keys | No unexplained orphans |
| Document checksum | Exact for migrated binary content |
| Status distribution | Differences explained by mapping rules |
| Sample accuracy | Business-owner sign-off |
| Rerun idempotency | No duplicates; deterministic result |
- Read-only source credentials stored in a secret manager
- TLS for source and target connections
- Least-privilege staging and target roles
- India-region processing/storage when applicable
- Encrypted temporary files with automatic expiry
- PII-redacted logs and errors
- Hashed source keys in rejection tables
- No production data on developer laptops
- Access and export activity audited
- Migration service account disabled after completion
Before a live migration can run, the team must provide source schemas, data dictionary, sample masked records, volumes, document-storage layout, change markers, code lists, data-quality findings, and reconciliation totals. The framework intentionally does not guess these details.