Tenant Merges and Splits in Multi‑Tenant SaaS

At some point, a customer will ask you to merge two of their accounts after an acquisition, or split a large account into multiple subsidiaries. If your multi‑tenant SaaS wasn’t designed for tenant “reparenting,” you’ll face a weekend of ad‑hoc scripts, broken permissions, and subtle data corruption. If it was designed with these operations in mind, the request is a predictable, low‑risk workflow you can complete with confidence.

This guide explains how to design, implement, and safely execute tenant merges and splits in a multi‑tenant product. We’ll cover data modeling, permissions, unique constraints, performance, operational safety, billing, SSO, search indexes, and a practical runbook. We’ll also include examples of how you could build an internal merge/split tool with YeshBe, an AI‑powered Vibe Coding platform for building software by describing what you want.

Define the operations: merge vs split

Tenant reparenting means changing which top‑level account (organization, workspace, company) owns a set of records. Two common operations:

Merge: Move all records from SourceOrg(s) into TargetOrg. You’ll reconcile conflicts (names, slugs, entitlements), unify users and groups, and end up with one combined tenant.
Split: Move a subset of records from one tenant into a new or existing tenant. You’ll decide what to move, how to handle shared resources, and how permissions, billing, and SSO should apply after the split.

Both operations are a controlled change of each row’s org_id (or equivalent) and the systems that depend on it.

Design for reparenting from day one

Most pain during merges/splits comes from data models that accidentally bind records to a tenant in ways that are hard to change. The following patterns make reparenting straightforward:

1) Make `org_id` a first‑class column on tenant‑owned tables

Every table whose rows “belong” to a tenant should have an org_id column, with composite indexes and constraints using (org_id, ...). Do not hide tenant ownership behind derived joins.

-- Example CREATE TABLE project ( id UUID PRIMARY KEY, org_id UUID NOT NULL REFERENCES org(id), name TEXT NOT NULL, slug TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (org_id, slug) ); CREATE INDEX project_org_id_idx ON project(org_id);

This ensures reparenting is an indexed update and that uniqueness is evaluated per tenant, not globally.

2) Use global resource IDs; scope uniqueness by org

Keep id globally unique (UUID/ULID). Don’t create tenant‑scoped IDs that leak into other references; reparenting should not force you to regenerate IDs.
Evaluate uniqueness and business keys per tenant: UNIQUE(org_id, slug), UNIQUE(org_id, external_id), UNIQUE(org_id, email) (for membership), etc.

3) Separate “user identity” from “membership”

Users often exist in multiple tenants. Model them as:

CREATE TABLE app_user ( id UUID PRIMARY KEY, email CITEXT NOT NULL, -- global identity, not necessarily unique if you support aliases status TEXT NOT NULL ); CREATE TABLE membership ( id UUID PRIMARY KEY, org_id UUID NOT NULL REFERENCES org(id), user_id UUID NOT NULL REFERENCES app_user(id), role TEXT NOT NULL, UNIQUE (org_id, user_id) );

On a merge, you map memberships across tenants and resolve role conflicts without duplicating user identities.

4) Prefer soft cross‑tenant references via `org_id`

If a table references another tenant‑owned table, include org_id on both and keep referential integrity scoping consistent. When reparenting, you update org_id consistently across the graph.

5) Implement Row‑Level Security (RLS) with `org_id`

RLS policies like USING (org_id = current_setting('app.org_id')::uuid) make data access safe and simplify admin tooling. During a merge, you can elevate or temporarily disable RLS only for the controlled job user.

6) Keep external integrations tenant‑aware

For storage, search, analytics, and billing, use keys and naming that incorporate org_id. Avoid designs that require moving objects between buckets or reassigning global resource owners if a tenant changes.

Before you begin: a practical checklist

Inventory tenant‑owned tables. Keep a registry (code or metadata) of all tables with org_id, including row counts and approximate storage size.
Map uniqueness and naming collisions you might hit when combining tenants: slugs, project keys, custom fields, tags, external IDs, invoice prefixes, etc.
Understand user and group models. Will memberships be unioned? Are there conflicting roles or group names?
Clarify entitlement and plan rules. Are limits combined, maxed, or moved to a new plan?
Align SSO settings. Domain restrictions, OIDC app IDs, SCIM groups—choose which becomes canonical.
Decide on operational posture. Online with partial read‑only, full maintenance window, or dual‑write shims?
Backups and rollback plan. Have a verified snapshot and a tested revert strategy.

Designing the merge workflow

At a high level, a merge moves data from one or more source tenants into a target tenant and reconciles conflicts.

Step 1: Preflight and simulation

Build a job that scans source and target to produce a merge plan:

Row counts per table to move.
Projected uniqueness conflicts (e.g., duplicate (org_id, slug) after reparent).
Membership overlaps for users and groups.
Entitlement differences and potential breaches (e.g., projects would exceed plan caps).

The job should output a deterministic plan that can be reviewed, approved, and replayed in staging.

Step 2: Freeze, quiesce, or dual‑write

Options for controlling writes during the merge:

Maintenance window: Block writes on source tenants; simplest and safest for large moves.
Partial read‑only: Allow safe reads and non‑conflicting writes. Riskier; requires deep knowledge of write paths.
Dual‑write shim: Temporarily write to both source and target with a reconciliation log. Complex, but can enable near‑zero downtime.

Step 3: Reconcile names and keys

Handle conflicts before moving rows. For example, if both tenants have a project slug = "design", rename one according to a rule:

Append a suffix like -acquired or map via a provided rename list.
For user‑visible names, communicate changes; for technical keys, log and record mappings.

Step 4: Reparenting data in batches

Perform updates in predictable chunks with progress tracking. Update children before parents only if constraints demand it, or disable/deferrable FKs during the operation and validate after.

-- Track a reparent job CREATE TABLE org_reparent_job ( id UUID PRIMARY KEY, source_orgs UUID[] NOT NULL, target_org UUID NOT NULL, started_at TIMESTAMPTZ, finished_at TIMESTAMPTZ, status TEXT NOT NULL, details JSONB ); -- Chunked update pattern UPDATE project SET org_id = :target_org WHERE org_id = :source_org AND id IN ( SELECT id FROM project WHERE org_id = :source_org ORDER BY id LIMIT 10000 );

Prefer indexed filters: keep org_id leading in composite indexes; it accelerates range scans and updates.

Step 5: Reconcile memberships, roles, and groups

Memberships: For users present in both tenants, unify into a single membership row on the target, applying a conflict policy (e.g., max privilege or admin‑review list).
Groups/Teams: Merge groups with identical names or add a namespace. Update group membership references accordingly.

Step 6: External systems and caches

Reindex search documents under the new org_id, clear caches keyed by org_id, and update analytics pipelines or materialized views. Emit idempotent events like org.reparented with old and new tenant IDs.

Step 7: Billing and entitlements

Pick the canonical billing account; move subscriptions from sources to target or cancel and prorate according to policy.
Recalculate usage and quotas; enforce the target plan or create a temporary merged plan.

Step 8: SSO, domain restrictions, and invites

Choose the target SSO app. If you enforce email domain restrictions per tenant, update allowed domains and ensure users from the source can still sign in. For conflicts (two tenants enforcing different IdPs), communicate cutover instructions clearly.

Step 9: Validate, unfreeze, and monitor

Count checks: rows moved match preflight counts.
Foreign key validation if temporarily disabled.
Access checks: random sample of resources from the sources are now visible in the target for expected users.
Monitor errors, performance, and support tickets closely for 48–72 hours.

Designing the split workflow

Splitting is often trickier because you’re defining a subset to move and resolving shared resources.

Step 1: Define the selection boundary

Your split criteria should be explicit and machine‑evaluated. Examples:

All projects whose department_id IN (...)
All customers with account_manager_id IN (...)
All records created after a certain date for a product line

Encode the criteria in a selection artifact the system can log and replay.

Step 2: Decide move vs copy semantics

Move: Rows change org_id and no longer belong to the source. Simpler for ownership, harder for reporting continuity.
Copy (fork): Duplicate rows for the new tenant. Requires new IDs for copied rows and careful handling of references, attachments, and audit trails. Secure PII if the copy is for a third party.

Step 3: Handle shared resources

Some records span the selection boundary:

Users: Add memberships for the new tenant; don’t duplicate app_user identities.
Files: If storage is tenant‑scoped, move object prefixes; if global, update metadata and access controls.
Comments/Threads: If a thread touches moved and unmoved entities, either duplicate the thread or leave it in source and detach comments referencing moved entities. Communicate behavior to stakeholders.

Step 4: Entitlements and billing

Create a subscription for the destination tenant and recalculate usage. Consider grace periods for newly split tenants to avoid immediate overage noise.

Step 5: SSO and domains

If the new tenant needs its own SSO, configure it before unfreezing. Update allowed email domains appropriately.

Step 6: Validate and confirm

Apply the same validation rigor as merges: row counts, key integrity, representative access checks, and event emissions.

Performance and locking strategies

Large reparenting jobs can touch millions of rows. Avoid table‑wide locks and long transactions:

Chunked updates: 1k–10k rows per batch; commit between batches to reduce lock time and WAL bloat.
Order by stable keys: Use id ordering for deterministic progress, combined with WHERE org_id = ....
Disable triggers judiciously: If you have triggers that recalculate derived state, run them once at the end (via a backfill) instead of per row.
Deferrable constraints: Make foreign keys DEFERRABLE INITIALLY DEFERRED so they’re checked at commit, or validate after reparenting using NOT VALID then VALIDATE CONSTRAINT.
Reindex search asynchronously: Queue reindex jobs rather than blocking the merge.

Auditability, safety, and reversibility

Tenant reparenting must be fully auditable and, where feasible, reversible:

Journal every change: Record old and new org_id, table name, primary key, timestamp, and job ID.
Immutable event log: Emit org.reparenting.started, org.reparenting.progress, and org.reparenting.completed events to your bus.
Pre‑merge snapshot: Verified backup or database snapshot. Practice restoring in staging.
Rollback plan: A mechanical reversal that replays the journal to restore original org_id where possible. Communicate limits (e.g., emails sent, billing changes cannot be unsent/unbilled).
Approvals and access: Require multi‑party approval and time‑bounded admin tokens to start a job.

Edge cases and conflict resolution policies

Unique names and slugs: Resolve collisions with deterministic renames and a mapping table so URLs and API clients can follow redirects.
Sequence numbers: Invoices, tickets, or human‑readable IDs may need tenant‑scoped counters. Move the counter state or allocate a new range.
Cross‑tenant references: Ideally your model forbids these. If they exist (e.g., shared marketplace listings), maintain a separate shared_resource schema rather than cross‑referencing tenant tables.
Long‑running workflows: Pause or cancel active jobs tied to the source org_id and restart them under the target once reparented.
API tokens and webhooks: Regenerate or rebind tokens to the new tenant. Re‑sign webhook endpoints if scoped by tenant.

Testing strategy

Treat merges and splits as first‑class features with automated tests:

Unit tests: Conflict resolution rules for slugs, counters, and memberships.
Integration tests: End‑to‑end reparenting on a synthetic dataset with tens of thousands of rows.
Property tests: Validate invariants: row counts preserved, foreign keys intact, no orphaned rows.
Performance tests: Measure batch sizes, transaction times, and impact on p95/p99 latency.
Staging drills: Dry‑run entire jobs periodically to keep the runbook current.

Operational runbook: from request to completion

Intake: Capture business context, tenants involved, desired outcomes, and constraints (downtime allowed?).
Preflight: Run analyzer; produce counts, conflicts, entitlements deltas, SSO/billing diffs.
Plan: Decide rename rules, selection criteria (for splits), maintenance window, and communication timeline.
Approval: Multi‑party signoff. Create change ticket and job record.
Backup: Verified snapshot and rollback plan.
Execute: Freeze writes as needed; run chunked reparent with progress logs and metrics.
Rebuild secondary systems: Search reindex, cache invalidation, analytics refresh.
Validate: Invariants, sample access, entitlement rechecks.
Unfreeze and notify: Provide new login/SSO instructions if needed.
Monitor and review: 48–72 hour watch; post‑incident style review to improve the runbook.

SQL patterns that make life easier

Here are a few pragmatic patterns to adopt:

Make foreign keys deferrable

ALTER TABLE task ADD CONSTRAINT task_project_fk FOREIGN KEY (project_id) REFERENCES project(id) DEFERRABLE INITIALLY DEFERRED;

This lets you reparent children and parents within a transaction without immediate FK violations.

Scope uniqueness by `org_id`

ALTER TABLE label ADD CONSTRAINT label_unique_name_per_org UNIQUE (org_id, name);

Materialize your table inventory

CREATE VIEW tenant_owned_tables AS SELECT c.oid::regclass AS table_name FROM pg_class c JOIN pg_attribute a ON a.attrelid = c.oid AND a.attname = 'org_id' JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r n.nspname NOT IN ('pg_catalog','information_schema');

Use this to loop through tables programmatically during preflight and execution.

Communications: set expectations and reduce surprises

Timeline: When the freeze starts, expected duration, and what users can/can’t do.
Post‑merge changes: Renamed entities, new SSO flow, new billing owner.
Support: Provide a direct channel and response SLAs during and after the operation.

Building an internal merge/split tool with YeshBe

YeshBe is a platform for building software by describing what you want; it is not a pre‑built tenant merge system. However, you can use it to build the admin tools you need quickly and refine them through conversation with AI.

Example: a tenant merge wizard UI

Build an internal admin app called “Tenant Operations.” It needs a wizard for merging tenants: step 1 selects source and target tenants; step 2 runs a preflight analyzer showing row counts per table and potential conflicts; step 3 lets the admin specify rename rules for slug collisions; step 4 schedules or runs the merge with a maintenance window. Show live progress and a journal of changed rows. Require two admin approvals to start.

Example: preflight analyzer prompt

Goal: Create a server action "runPreflightMerge". Inputs: sourceOrgIds: UUID[], targetOrgId: UUID. Requirements: - Enumerate all tables with an org_id column. - Return row counts per table for each source tenant. - Detect projected UNIQUE(org_id, slug) conflicts by joining source slugs with target slugs. - Detect overlapping memberships and role conflicts. - Estimate duration by sampling 1k-row batch update timings. - Return a JSON plan suitable for review and approval.

Example: split plan generator

Extend the app with a split planner. Let me choose a tenant and define selection criteria (by team or department). Generate a dry‑run plan listing included projects, users to invite, files to move, and any cross‑boundary threads that need duplication. Show a downloadable CSV of the move set and a big red warning if entitlements will be exceeded in the destination tenant.

Example: safety controls

Implement: - Role-based access: only SuperAdmin can access Tenant Operations. - Two-person approval: approver must be different from the initiator. - Time-limited execution token: expires in 30 minutes. - Automatic backup verification before "Start Merge" becomes enabled. - Post-merge validation checklist with pass/fail gates.

You can iterate on these tools conversationally in YeshBe, adding progress dashboards, simulation reports, and runbook checklists as you learn from each operation.

Common pitfalls (and how to avoid them)

Global uniqueness for tenant keys: If you enforced UNIQUE(slug) globally, merges become rename storms. Always scope by org_id.
Leaky user models: Tying a user’s identity to a single tenant makes merging memberships painful. Separate identity from membership.
Hidden tenant ownership: Tenant ownership embedded via joins or views rather than explicit org_id columns makes it hard to update quickly and safely.
Unbounded transactions: Reparenting millions of rows in one transaction can lock tables and balloon WAL. Use chunked updates and track progress.
Ignoring secondary systems: Search indexes, caches, analytics, and webhooks must be updated or rebuilt.
No rollback story: Keep a journal and a verified backup so you can revert or at least repair deterministically.

Security and compliance considerations

Access control: Restrict merge tools to a small, audited set of operators. Log every action with user, time, and reason.
Data residency: If tenants are region‑sharded, merging across regions may require cross‑region moves or regional constraints; align with your data residency policy.
Privacy and least privilege: Don’t expose more data than necessary during preflight or validation. Redact PII in reports unless strictly required.
Legal holds: If any data is under legal hold, ensure it is preserved and tracked through the reparent.

A lightweight example: putting it all together

Suppose Acme acquires BetaCo. Both have tenants in your app, each with 200k projects and 2M tasks. You plan a 2‑hour maintenance window:

Run preflight: row counts, 423 slug collisions, 615 overlapping memberships, plan estimate 75 minutes.
Approve, snapshot, freeze writes for both tenants.
Apply deterministic slug renames: design at BetaCo becomes design-betaco. Publish mapping.
Run chunked reparent: 10k rows per batch per table. Monitor locks and latency.
Unify memberships with max privilege policy; flag 12 admin conflicts for manual review.
Reindex search asynchronously, clear caches, recompute analytics rollups.
Validate counts and random access checks; emit org.reparented event with the journal link.
Unfreeze and send onboarding email for the new combined tenant with SSO instructions.

Total elapsed time: 88 minutes. You keep the journal and mapping artifacts for support and compliance.

Make reparenting a product feature, not an incident

When tenant merges and splits are rare, it’s tempting to script them ad hoc. But a few careful design choices—org_id everywhere, scoped uniqueness, identity‑membership separation, and auditable operations—turn a stressful data surgery into an ordinary, well‑understood workflow. With internal tools you can build on a platform like YeshBe, your team can plan, simulate, execute, and validate these operations quickly and safely.

Customers will keep reorganizing; your product should treat that reality as a supported path, not an exception.

Tenant Merges and Splits in Multi‑Tenant SaaS: Models, Workflows, and Safety