The engagement was to replace a long-lived customer platform with a current stack, including its integrations and its reporting. It was framed as a new build with a modern front end. What actually had to move was a decade and a half of customer data and the behaviour encoded in the database itself: rules that fire when a record changes, calculations that run inside the database, side effects that no screen shows. No specification described any of it, because nobody had ever written it down.
In a long-lived system the database is the specification, because that is where the rules ended up.
A stored procedure is a program that lives inside the database and runs there. A trigger is a stored procedure that runs automatically whenever a row is inserted, changed or deleted, without any application asking for it. When many long-lived business systems were first built, putting logic in the database was standard advice, and for sound reasons. Several applications often shared one database, and a rule enforced there was enforced for all of them at once. Logic close to the data ran faster. And the database tended to outlive every application written against it.
Over the years more rules settle there for less principled reasons. A report needed a field kept up to date, so someone added a trigger. A data problem appeared, and the quickest fix was a procedure run by whoever administered the database. A customer's status needed to change when an invoice was paid, and the easiest place to do that was where the invoice was written. None of these changes went through the application's code, so none of them appear in it, and most were never documented because each one was small. After a decade and a half, the database enforces a body of business behaviour that no document describes and no one person fully remembers.
This is why a replacement built from the application's visible behaviour comes out wrong. The team watches what the screens do, interviews the people who use them, and rebuilds that. A trigger that, say, quietly recalculates a balance or archives a record when a contract ends does not appear on any screen. The new system works, passes its tests, and lacks behaviour the business relied on without knowing it relied on it.
Two further problems travel with old data, and they share a nasty property: they load without errors. Some calendars in everyday use count years from a different starting point than the Gregorian calendar, so the same year carries numbers centuries apart. A date written in one convention and read as the other is still a perfectly valid date, so every check that asks whether a value is a date passes. Text behaves the same way. Older systems stored characters using code pages, tables that map each byte to a character, and different regions used different tables. Read with the wrong table, the bytes still map to characters, just the wrong ones. Nothing is rejected, because nothing is malformed. Loading checks form. Only a check that knows what a value is supposed to mean can catch either problem.
The old system knows things no one recorded, so the first job is proving that knowledge moves across, not assuming it will. Alongside that sit two locale traps that fail with no screen and no error. A calendar convention shifts dates by centuries if it is read as the wrong system. A legacy code page silently corrupts text in transfer. Both produce data that loads successfully and is wrong.
Legacy migrations fail on what loads cleanly, not on what crashes.
Migrations that crash are the lucky ones. A failed load, a rejected row or a broken integration is visible, and somebody fixes it. The expensive failures are the ones that complete. The data arrives, the counts match, the new system runs, and some of what it holds is wrong in ways nobody checks for: dates in the wrong era, names with characters replaced, records missing a side effect the old database used to apply on its own.
The second pattern is mistaking memory for specification. Requirements for a replacement are usually gathered from the people who use the old system and from what its screens show, and both describe the system's surface. The behaviour that matters most in a long-lived database is often the behaviour nobody sees, because it happens automatically. The first sign it is missing is usually a downstream figure that drifts some time after go-live, far from the cause.
The reflex, and the fix.
Build the replacement and migrate the data at the end
It is how the work was scoped, the new system is the deliverable, and moving the data looks like a final step.
The rules are not in the application. They are in the procedures and triggers, so a replacement built from visible behaviour reproduces what people remember about the system, which is less than what the system does.
Inventory the hidden rules on a copy first
A first gate that produces no new features at all, run against a copy of the database and touching nothing live.
A catalogue of the rules that actually exist, each one proven to reproduce, before anything is built that depends on it.
Prove the knowledge moves before building anything that assumes it did.
Inventory what the database does on its own
Stored procedures and triggers carry behaviour that no application code shows and no specification records. Finding them is a discovery exercise with a concrete output, a catalogue, and not an assumption written into a project plan.
Work on a copy, touch nothing live
The first gate runs entirely against a copy of the database. That makes it safe to be thorough, and it removes the pressure that turns discovery into guesswork.
Pin the locale traps explicitly
A calendar convention read as the wrong system moves every date by centuries, and text passing through a legacy code page corrupts without raising anything. Both load cleanly, which is why they are tested for on purpose and never left to be noticed.
Replace path by path
Replacement happens incrementally behind the existing system, which is also the vendor's own recommendation for production applications. A single cutover would deliver all of its value, and all of its risk, at the very end.
Idempotent intake at every integration
Inbound platform webhooks are delivered at least once, and there is no replay history. The intake has to tolerate duplicates, and it cannot assume it will be able to ask for anything it missed.
What each choice cost, and what it bought.
AA first gate with no features in it
The work opened with an inventory of what the database does on its own, and nothing else. That is a hard phase to sponsor, because it produces nothing anyone can click on, and effort is spent before there is visible progress. It bought knowledge of what the system actually does before anything was built to depend on it. Building first and moving data at the end would have discovered each missing rule after go-live, where every discovery costs a repair to live data and an explanation to the people affected.
BWorking on a copy of the database
Discovery ran against a copy and touched nothing live. Keeping a faithful copy is work in itself, and a copy can only reveal behaviour that its data happens to exercise. In return the inventory could be thorough: procedures could be run and triggers watched as they fired, with no risk to the business. Discovery against the live system is necessarily cautious, and caution in discovery means guessing at what a procedure does when it could have been observed.
CTesting for failures that raise no errors
The calendar and encoding traps were pinned with deliberate checks, comparing values against ones whose meaning is known independently. That requires someone to know what correct looks like, which is slower than trusting a successful load. It catches corruption before it reaches the new system. Waiting for these problems to announce themselves means waiting indefinitely, because they never raise an error. They surface later as wrong dates on customer records and garbled names on letters.
DPath by path, with the old system still present
Replacement proceeds incrementally behind the existing system, so two systems run side by side for the duration. That is more to operate than a single cutover. It also means a rule missing from the catalogue shows up as a difference on one path, with the old system still there to compare against and fall back to. A single cutover removes the only complete specification at exactly the moment it is most likely to be needed.
A single headline number hides where a system fails. This work was scored on the dimensions that actually decide whether it holds in production, measured on real, held-out cases rather than the demo path.
No measured figures are published for this engagement. Outcomes are described qualitatively.
A clean new system built from the behaviour people remember, missing the rules that live in triggers, with dates centuries out and text corrupted in ways that loaded without a single error.
An inventory of the rules the database actually enforces, each proven to reproduce against a copy, locale traps tested for explicitly, and replacement proceeding path by path behind the existing system.
What it owns, and what it hands to a person.
An inventory finds the rules it can reach. A rule that only fires on a condition absent from the copied data will not be found by inspection. That is why path-by-path replacement with the old system still present matters more than the completeness of the catalogue. The two work together, and neither is sufficient alone.
When a long-lived system is being replaced, the system itself is the specification. Inventory what it does, not what it is documented to do, prove each behaviour reproduces, and replace incrementally so the original is still there when something turns out to be missing.
In a long-lived system, business rules collect in stored procedures and triggers. Inventory the database, not only the application, before scoping a replacement.
Requirements gathered from users and screens describe the surface of a system. The behaviour that runs automatically is the part nobody remembers to mention.
Calendar and character-encoding errors load without complaint, because validation checks form and not meaning. Test for them deliberately, against values whose meaning you already know.
Discovery is safest and most thorough on a copy of the data. The pressure of a live system turns investigation into guesswork.
Replace incrementally with the old system still running. It is the only complete specification, and it will be needed on the day the catalogue turns out to be incomplete.