Architecture Discussion: Cross-Application Migration Ordering Across MJ + BAC + BCSaaS + SaaS Products

# Cross-Application Migration Ordering — Problem Statement and Candidate Solutions

**Date:** 2026-04-28
**Status:** Open for discussion

---

## TL;DR

Our applications no longer live in isolation. A single database now hosts MJ + Biz Apps Common (BAC) + BCSaaS + a SaaS product (Skip, Izzy, MJC) all stacked on top of each other, each with its own migration history, each evolving on its own cadence. Flyway applies migrations in version (timestamp) order *within a schema*, but it has no concept of cross-application dependencies. When one application's migration depends on the schema state created by another application's migration, ordering becomes critical — and we have no mechanism today that enforces or even describes that ordering across application boundaries.

This is becoming an active blocker as we lift our SaaS products onto BAC. It will continue to be a blocker every time an upstream application makes a non-backwards-compatible change, and it will compound as we publish open apps (Committees, Tasks, Payments, etc.) for installation into customer databases.

We need an architectural decision on how to address this. This document lays out the problem and four candidate solutions for discussion. The options are not all mutually exclusive — some pair naturally to cover gaps that no single option addresses on its own — and the doc flags those pairings explicitly so the discussion can be about *which combination* as much as *which option*.

---

## Primer: How Migrations Work Today

A short refresher so we're all working from the same picture:

- We use **Flyway** for SQL migrations, with date-time-stamped version numbers (e.g. `V202604281200__some_change.sql`).
- Each application owns a **schema** (`__mj`, `__mj_BizAppsCommon`, `__BCSaaS`, `Izzy`, `Skip`, etc.) and each application's migrations live in its own folder/repo.
- Flyway maintains a **`flyway_schema_history`** table per schema. When `mj migrate` runs, it walks each schema's pending migrations in version order and applies them.
- A **baseline** in Flyway is a snapshot of a schema at a point in time, used to initialize a fresh database without replaying historical migrations. Once a baseline is set, Flyway applies version migrations *after* the baseline timestamp.
- Today, baselines are something MJ uses for its own clean install, but downstream applications (BAC, BCSaaS, our SaaS products) don't ship baselines — they replay their full history every time.

We have **two distinct categories of migration** that both flow through this system:

- **DDL migrations** — hand-authored `CREATE TABLE`, `ALTER TABLE`, foreign key, etc. scripts. These are what most of this document is about.
- **Metadata migrations** — codegen-generated scripts that populate `__mj.Entity`, `__mj.EntityField`, run stored procedures like `spUpdateExistingEntitiesAfterCreate`, etc. They are generated against the MJ stored-procedure signatures and metadata schema *that existed at codegen time*. If MJ later changes a stored procedure signature, renames a metadata column, or restructures how entities are registered, those generated scripts can break when replayed against a newer MJ — even if the DDL they accompany is fine.

Metadata migrations make the cross-application problem materially worse: a downstream application's migration history is full of point-in-time codegen output that assumed an older MJ, and there's no way to "regenerate" history without editing it.

The model works well when applications are independent. It breaks down when one application's migration depends on the schema state of another, and it breaks down further when the dependency is on the upstream application's *internal codegen contracts* rather than just its tables.

---

## The Core Problem

Our application stack is no longer a single product. A SaaS product like Skip or Izzy is composed of layers:

```
MJ → Biz Apps Common → BCSaaS → SaaS Product (Skip / Izzy / MJC)
```

Each layer:
- Has its own migration history
- Has its own release cadence
- Can introduce breaking changes (rename a table, move a table to another schema, change a stored procedure signature)
- Can add foreign keys that reference tables owned by another layer

When a downstream layer's migrations were authored against a *specific point-in-time version* of an upstream layer, and the upstream layer later removes or restructures something the downstream migration relied on, you get a contradiction. This applies to both DDL dependencies (a downstream FK references an upstream table that's been dropped) and metadata dependencies (a downstream codegen migration calls an MJ stored procedure whose signature has changed):

- **Fresh database:** Replays all migrations in date order. The downstream migration may run *after* an upstream migration that removed the table it expected, causing failure. Or it may run *before* an upstream migration that drops the table it just FK'd to, leaving a logical inconsistency.
- **Existing database:** The downstream FKs were created long ago and physically exist. The upstream migration now wants to drop the referenced table, but SQL Server refuses because of the incoming FK. The migration fails on every existing database that has the downstream layer installed.

Today there is no mechanism that says: *"this downstream migration must run between these two upstream migrations"*, *"this downstream migration depends on upstream version X"*, or *"play all migrations across all schemas in one global order rather than schema-by-schema."*

In the past, when we hit this kind of cross-layer ordering problem, we have worked around it by editing historical migrations in place. That is not a sustainable approach: it breaks Flyway checksum validation, it loses the audit trail, and it requires every existing database to be specially handled.

### Why this is getting worse, not better

Three forces are compounding the problem:

1. **Pace of change.** Due to the speed at which we currently iterate on MJ and our shared layers, upstream breaking changes happen frequently. A SaaS product team can't realistically pin to a long-lived upstream version because they need new functionality.
2. **The number of layers is growing.** What used to be MJ + product is now MJ + BAC + BCSaaS + product. Open apps will add more layers (Committees, Tasks, Payments, etc.), each with their own migrations, installed by customers in arbitrary combinations.
3. **Open apps change the customer story.** Today our SaaS products are existing databases that we upgrade in place. Once we publish open apps for customer installation, fresh-database installation onto an arbitrary MJ version becomes a first-class scenario — not the niche local-development case it is for us today.

### The two affected populations

Any solution has to address both:

- **Existing databases** — already have data and FKs from prior migrations. Cannot be wiped. Need an in-place path forward when an upstream layer makes a breaking change.
- **Fresh databases** — replay migrations from zero. Need a consistent ordering or a pre-baked snapshot that doesn't contradict itself.

Solutions that only fix one population aren't actually solutions.

---

## Constraints

- We do not want to edit historical migrations in place. Flyway checksum validation, audit trail, and the operational risk of retroactive changes all argue against it.
- We do not want to require every product team to manually orchestrate cross-layer migration ordering on every upgrade. That's where we are today and it doesn't scale.
- The solution must work for both fresh and existing databases. Patching one without the other just moves the problem. (Note: not every individual option below satisfies this on its own — some only meet the constraint when paired with another option. Those pairings are called out explicitly.)
- We need to keep moving fast on upstream layers (MJ in particular). A solution that requires upstream layers to slow down or hold breaking changes is a non-starter for our current operating environment.

---

## Candidate Solutions

Four distinct directions are described below. They are not all mutually exclusive — some pair naturally and address each other's weaknesses, while others overlap enough that picking both wouldn't make sense. Each option's section ends with a note on how it interacts with the others. The "Combinations Worth Considering" section after the four options summarizes the pairings that look most promising.

---

### Option 1 — Holistic cross-schema migration ordering

**The idea.** Change `mj migrate` so that, instead of walking each schema's migrations independently in version order, it collects migrations from *all* schemas (MJ, BAC, BCSaaS, the product schema) into a single global list, sorts them by version (timestamp), and applies them in one merged stream.

This means a downstream migration with a timestamp between two upstream migrations would actually run between them, regardless of which schema it belongs to.

**What's required.**
- Update the migrate CLI to discover migrations across all participating schemas and merge them.
- Add CI/build-time validation that catches mis-dated migrations (a downstream migration dated before its upstream dependency, for example) before they merge to a release branch. We do this for MJ today; we'd extend the pattern across the stack.
- Possibly extend migration file headers to declare upstream-version dependencies, so the validator can check "this BCSaaS migration was authored against MJ ≥ X" and warn if the actual MJ migration with that version isn't present.

**Pros.**
- Addresses the *ordering* dimension of the problem for both fresh and existing databases — same migration list, same order, deterministic. (Does not address cases where already-applied downstream migrations are incompatible with newer upstream state — see Cons.)
- No new artifact types. Migrations stay as they are; only the runner changes.
- Fits naturally with the per-app, per-team development model we already have.
- Composable — adding a new layer (an open app) just means its migrations join the global list.

**Cons.**
- Puts the ordering burden on developer discipline around timestamps. A mis-dated migration in any layer can break the global order, and the failure mode is downstream products breaking on upgrade.
- Requires CI gating across multiple repos to be effective. Without enforcement, the model degrades quickly.
- Does not help when the breaking change is genuinely incompatible with already-applied downstream migrations (e.g., upstream wants to drop a table that has a downstream FK on it). The downstream migration that originally created the FK still exists in history pointing at a now-dropped table — logically inconsistent on fresh installs even if the dates are right.
- Cross-repo coordination of release timing becomes significant. If the downstream team ships a migration dated 12:00 today expecting an upstream migration dated 11:00 today, both have to actually be available together at install time.

**How this handles metadata migrations.** Poorly. Reordering doesn't help a downstream codegen migration that calls an MJ stored procedure whose signature has since changed. The script is what it is; running it earlier in the global order doesn't change its assumptions about MJ internals. This is one of the strongest arguments for pairing Option 1 with Option 2 or Option 3 rather than relying on it alone.

**Interaction with other options.** Pairs naturally with Option 2 — holistic ordering keeps day-to-day migrations coherent, while periodic baselines reset accumulated history and absorb the metadata-migration version-skew problem that Option 1 alone can't fix. Compatible with Option 4 as a release-policy overlay.

---

### Option 2 — Composite baselines per SaaS product

**The idea.** Each SaaS product ships a single baseline script representing the entire database state — MJ + BAC + BCSaaS + the product itself — at a known-good moment. Fresh installs apply that one baseline, then receive incremental version migrations from there. No replaying full history.

This is fundamentally a *fresh-install* solution. It does nothing for existing databases on its own — they continue forward from wherever they are in their `flyway_schema_history`. To address both populations, Option 2 needs to be paired with another approach that handles the existing-database upgrade path.

This is roughly the pattern Skip and Izzy already use locally for fresh-database setup, formalized as a first-class delivery artifact.

**What's required.**
- Generate a baseline from a known-good database after each significant upstream version bump (or at fixed cadence).
- Move pre-baseline migrations to an archive folder so they aren't replayed.
- Update Flyway config to recognize the new baseline version.
- Tooling to produce baselines reproducibly (script extraction from a reference DB).

**Pros.**
- Fresh-database initialization becomes fast and deterministic. No risk of historical-migration contradictions because they're not replayed.
- Existing databases are unaffected — they're already past the baseline version in their `flyway_schema_history`, so Flyway just continues forward from where they are.
- Decouples fresh-install correctness from historical migration consistency. We can have messy history and still have clean fresh installs.

**Cons.**
- Generating a new baseline is non-trivial work, and due to the pace at which we currently make upstream changes, baselines would go stale quickly. Every time MJ ships a non-backwards-compatible change that the SaaS product needs, the SaaS product likely needs a new baseline.
- Does not solve the existing-database upgrade problem at all. If an upstream layer wants to drop a table that the downstream layer FK'd to, baselines don't help — those FKs physically exist in production and still need a real DDL transformation.
- Each SaaS product owns its own baseline, which means baseline generation becomes a per-product responsibility. Open apps would each need their own baseline strategy too, multiplying the work.
- "We don't know there's a problem until we try to install it" — a baseline can pass our internal testing and still fail at a customer site if their database has drifted from our reference.

**How this handles metadata migrations.** Strongly. Because the baseline is a snapshot of the *resulting database state*, the historical metadata migrations don't get replayed at all — their assumptions about old MJ stored-procedure signatures stop mattering. This is one of the more compelling arguments for baselines specifically: they neutralize the metadata-migration version-skew problem for fresh installs, even though they don't address it for existing ones.

**Interaction with other options.** Pairs naturally with Option 1 — Option 1 keeps in-flight migrations correctly ordered, Option 2 prevents the historical pile from growing forever and absorbs metadata-migration drift on fresh installs. Largely *replaced* by Option 3, which is essentially an automated baseline generator; choosing Option 3 supersedes the need for Option 2 as a separate effort.

---

### Option 3 — Composition tool / migration generator

**The idea.** A code-gen-style tool that, given a declared set of application versions (e.g., "Skip on MJ 5.30.1, BAC 1.0, BCSaaS 1.1"), emits a single composed migration set tailored to that combination. The tool understands each application's migration history and produces a coherent script set that lays down a working database for that exact version combination.

This is an evolution of Option 2 — instead of one hand-curated baseline per product, the baseline is *generated* from the version declarations.

**What's required.**
- Each application publishes its migrations and metadata in a form the tool can consume (likely including dependency declarations on upstream versions).
- A composition engine that resolves the dependency graph and produces an ordered, internally-consistent migration plan.
- Storage and versioning for the composed outputs.
- Integration with `mj migrate` to consume composed plans rather than (or alongside) raw migration folders.

**Pros.**
- Fully declarative — a SaaS product team says "I want to be on these versions" and gets a working install.
- Reproducible. Same inputs produce the same composed migration plan.
- Handles the open-app case naturally — a customer installing an open app declares the versions, the tool figures out the rest.
- Forces explicit, machine-readable dependency declarations, which is healthier than the implicit dependency model we have today.

**Cons.**
- Significant engineering investment. This is a new tool with non-trivial logic (dependency resolution, conflict detection, plan generation, plan validation).
- Still doesn't directly solve the existing-database upgrade problem unless we also have transformation migrations from "old composed state" to "new composed state." That's another whole set of artifacts.
- Adds a new layer of process between development and deployment. Teams have to publish in the format the tool expects.
- Risk of building something elaborate that we then have to maintain alongside everything else.

**How this handles metadata migrations.** Potentially well — if the composition step regenerates metadata against the target MJ version (rather than replaying historical codegen output), the version-skew problem largely goes away. This requires the composer to understand metadata migrations as a distinct, regenerable artifact, not just opaque scripts to be ordered. That's additional design work but it's where the real leverage is.

**Interaction with other options.** Largely subsumes Option 2 (the composer generates baselines as one of its outputs). Compatible with Option 1 — the composer produces the plan, Option 1's runner applies it in correct global order. Compatible with Option 4 as a way to formalize what a "supported pinned combination" actually means.

---

### Option 4 — Pinned-version delivery model

**The idea.** Treat each SaaS product as built on a specific, fixed combination of upstream versions, and only allow upstream upgrades through a deliberate migration project. The product team picks a target combination ("we're moving from MJ 5.26 + BAC 0.x + BCSaaS 1.0 → MJ 5.30 + BAC 1.0 + BCSaaS 1.1"), produces the migration scripts that walk an existing database from the old combination to the new one, and ships that as a unit.

In this model, upstream layers continue moving fast, but downstream products only adopt new upstream versions on their own schedule, with hand-authored upgrade paths.

**What's required.**
- Each SaaS product declares its current upstream-version pin.
- Upgrades to the pin are explicit projects with their own migrations.
- Upstream layers maintain "upgrade from X to Y" guides or migrations to support downstream uptake.
- Possibly separate migration folders per pinned-version bracket.

**Pros.**
- Conceptually simple — it's the standard "vendor + lock-in" model used by most enterprise software.
- Forces deliberate upgrade decisions, which tend to surface compatibility issues earlier.
- Reduces the surface area of "any combination of versions might happen" to "these specific combinations are supported."
- Existing-database upgrades become a known, planned event rather than an emergent surprise.

**Cons.**
- Slows down downstream uptake of upstream improvements significantly. Given how often we currently want new MJ functionality in our SaaS products, this is a hard pill.
- Still requires hand-authored upgrade migrations, which is exactly the work we're trying to avoid having to do reactively.
- Doesn't help with open apps installed into customer databases at arbitrary upstream versions — that's the opposite of pinned.
- Creates a combinatorial explosion if we want to support multiple upstream-version brackets simultaneously.

**How this handles metadata migrations.** The pinning makes the version-skew problem manageable but doesn't eliminate it — within a pinned bracket the metadata migrations are by definition compatible, but moving between brackets still requires hand-authoring an upgrade path that handles both DDL and metadata transformations.

**Interaction with other options.** Compatible with all three — pinning is a release-policy choice, not a tooling choice. It can sit on top of Option 1's holistic ordering, Option 2's baselines, or Option 3's composer to constrain *which* combinations are supported.

---

## Combinations Worth Considering

The four options aren't strictly mutually exclusive. Some pair naturally and address each other's weaknesses; others overlap enough that picking both wouldn't add value. Notable pairings:

- **Option 1 + Option 2.** Holistic ordering for in-flight migrations, periodic baselines to keep the historical pile bounded and absorb metadata-migration drift. This is probably the most promising near-term combination — neither alone is sufficient, but together they cover both fresh and existing databases reasonably well.
- **Option 3 alone (effectively replaces Option 2).** A composition tool generates baselines as part of its output, so building Option 3 makes a separate Option 2 effort largely redundant. Option 3 still benefits from Option 1's runner-level ordering.
- **Option 4 layered on top of any of the others.** Pinning is a release-policy decision, not a tooling decision. It can constrain which combinations any of the other three options have to support.

What does *not* combine usefully:

- **Option 2 + Option 3.** Option 3 is essentially Option 2 with the baseline-generation step automated. Building both means producing the same artifact (a per-version-combination snapshot of the stack) twice — once by hand and once by tool. If we go in this direction at all, pick one: Option 2 is faster to ship and lower-risk; Option 3 scales further but costs significantly more to build.

---

## What This Document Doesn't Cover

- Specific implementation details for any of the four options. If we pick a direction, that's the next document.
- The immediate tactical fix for the current Skip/BAC migration situation. That has its own writeup and is being handled separately as a short-term unblock; the architectural decision here is independent of that.

---

## Discussion Questions

1. Which of the four options (or which combination of ideas from them) feels closest to the right architectural direction, given how we actually work today?
2. Is there a fifth option we haven't considered? Industry patterns from other multi-app-on-shared-DB systems would be valuable input.
3. How much of the problem is solvable with tooling vs. how much requires changing how we develop and release? Some of these options ask developers to do new things; others ask the tooling to do new things.
4. What's the right scope for a first step? Do we want a small experiment in one of these directions before committing, or is the problem urgent enough to pick one and invest?
5. Open apps change the calculus — once customers are installing our open apps into their own databases, the "fresh install onto arbitrary upstream version" case stops being theoretical. How much does that future scenario factor into the decision now vs. later?

---

## Asks

- **Read and react.** Which direction resonates? What did we miss?
- **Identify blockers** to whichever direction looks most promising, especially ones that would only be visible to people working on the upstream layers.
- **Surface analogous prior art** — internal or external — for any of these patterns. We're probably not unique, even if we're unusual.

Start with the `mj migrate` entry point described in the issue and compare the four candidate solutions against the fresh- and existing-database constraints. Done means an architectural decision is recorded with implementation scope and a validation path; the payload names no files or tests to run.

Written by the indexing model from the issue text.