DevOps Services
Migrating Nearly One Million Records to a Multi-Tenant SaaS Architecture
Restructuring a mature single-tenant CRM into an isolated, multi-tenant SaaS platform — without losing a relationship across close to a million production records.
0
Records Migrated
0
Pipeline Stages
PostgreSQL
Database Engine
01
The Challenge
The CRM had originally been built as a single-tenant application, with all data, users, and relationships stored in one database. As the platform grew into a SaaS product, it needed to support multiple organisations with strict data separation — without disrupting the roughly one million records, users, and relationships already in production.
This wasn't a simple database import. Years of usage had left the PostgreSQL database full of foreign-key relationships, circular dependencies, and historical records that couldn't be casually restructured. Exporting the data and dropping it into a new tenant structure outright wasn't an option.
This wasn't a simple database import. Years of usage had left the PostgreSQL database full of foreign-key relationships, circular dependencies, and historical records that couldn't be casually restructured. Exporting the data and dropping it into a new tenant structure outright wasn't an option.
02
The Migration Pipeline
The migration was run as a controlled, dependency-aware pipeline rather than a one-shot import — each stage had to finish cleanly before the next could safely begin.
1
Export Existing Data
Extracted single-tenant data into structured Excel files, giving a validation reference for later.
2
Analyse Dependencies
Mapped every foreign-key relationship, parent-child dependency, and cross-table reference in PostgreSQL.
3
Identify Blocking Relationships
Found the constraints preventing the old tenant structure from being removed, once the first attempt failed.
4
Remove Required Constraints
Temporarily disconnected the blocking foreign keys so data could be extracted and reorganised freely.
5
Resolve Circular Dependencies
Traced and broke dependency loops (A → B → C → A) that PostgreSQL couldn't resolve on its own.
6
Prepare Multi-Tenant Structure
Rebuilt the app around a shared/public layer plus isolated per-tenant data on one codebase.
7
Migrate Existing Users
Moved user accounts while preserving their links to years of historical records.
8
Import Data in Controlled Order
Inserted records least-dependent first, restoring links only once the referenced data existed.
9
Restore Relationships
Reconstructed foreign-key constraints and integrity checks across the new tenant structure.
10
Validate Migrated Data
Checked record counts, user mappings, and relationships against the original Excel exports.
The Circular Dependency Problem
Table A
→
Table B
→
Table C
→
Table A
The loop PostgreSQL couldn't resolve on its own — broken temporarily to allow restructuring.
The Resulting Architecture
SaaS Application
Shared / Public Layer
- Tenant Management
- Shared Configuration
- Common App Data
Tenant AIsolated Data
Tenant BIsolated Data
Tenant CIsolated Data
+ More
03
Where the Complexity Lived
Large-Scale Data Migration
Roughly a million existing records had to move into a new architecture without manual correction.
Architecture Transformation
The platform itself was converted from single-tenant to multi-tenant while the data moved.
Complex Foreign-Key Relationships
Years of interconnected PostgreSQL relationships had to be mapped before anything could be touched.
Circular Dependencies
Dependency loops blocked normal deletion and restructuring until identified and temporarily broken.
User Migration
Existing accounts needed to stay correctly linked to their historical records throughout.
Relationship Reconstruction
Every constraint removed during migration had to be safely restored once the data landed.
04
The Outcome
The platform now runs on a true multi-tenant architecture, with each organisation's data fully isolated while sharing a single codebase. Close to a million existing records — and the users attached to them — carried across without losing the relationships or historical integrity built up over years of production use.
Key Engineering Lesson
Large database migrations are dependency migrations, not simple data transfers. Once the dependency structure is understood — and circular relationships are resolved — a mature application can move to a fundamentally different architecture while keeping its history intact.
© Copyright 2024
Ajish
Stephen