Data Migration — How It Works

1. Scope & scale

The new platform holds 82 more tables than the legacy one. They fall into three groups:

• Capabilities the legacy system never had — external system integration, content AI analysis, read models, content management.
• Existing capabilities modelled in more detail — inspection grew from 11 tables to 34; reference data from 74 to 83.
• Extension tables — highly variable data such as “what the applicant typed into the form” moved out of fixed columns into a structured document held in an extension table, one per main table.

What happens to the 215 legacy tables

Why 116 tables are not migrated

2. The three databases

In production these three databases may sit on different servers with no direct link between them. That single constraint shapes the whole design: no query can join across them, so every cross-database movement goes through an export-file handover, and any rule that needs data from the other side must first bring a copy of that reference data across. It costs extra steps in the log, but it is invisible from a business point of view.

3. The seven steps

The whole migration runs as a single command with no manual intervention in the middle. In business terms it is seven stages.

1. Health check gate

   Before anything moves: can all three databases be reached, is there enough disk space, does the configuration match the files actually present, and does every extraction statement line up column-for-column with its destination table?

   The run is not allowed to start unless the health check is fully green. This is deliberate — discovering a configuration error three hours in costs those three hours.

2. Clear the floor

   The staging database is rebuilt from scratch and the new database is emptied, so every run starts from a genuinely clean state.

   The new platform’s database triggers are switched off at this point. Triggers exist to support day-to-day business automation; during a bulk load of millions of rows they are both very slow and capable of firing incorrect side effects. They are switched back on once loading completes.

3. Lay the foundation

   Reference data first: service catalogue, permission model, dictionaries, notification templates, fee configuration, inspection standards, report definitions.

   Order matters here. The service catalogue must be in place before anything else, because every application that follows depends on it to answer “what kind of business is this?”.

4. Move the people and the entities

   User accounts, user profiles, establishments, natural persons, addresses, establishment partners. Two business rules are worth calling out:

   • Profile de-duplication — the legacy system allowed one person to hold several profile records. These are merged under a fixed rule, and the profile’s type and status are recalculated from whether the user is an individual or an establishment and whether they have ever been approved.
   • Establishment profile cloning — the new platform requires one profile to belong to exactly one establishment. A legacy user managing several establishments is split into several establishment profiles.
5. Move the applications most complex

   The core of the migration and the longest-running stage. It breaks down into five actions, covered in sections 4 and 5 below: decide the service, rebuild the application chain, rebuild the workflow, restore the form content, and archive whatever genuinely cannot be migrated.

6. Move the remaining business areas

   Content library, enquiries, supervisor reports, inspection and payment. Each has its own remapping logic because each was redesigned in the new platform.

   These five share the cross-server constraint from section 2, so each runs as a three-leg operation: export the reference data it needs to the legacy side, do the work locally there, then bring the result back. No business impact — it just explains why these stages show more steps in the log.

7. Finishing and back-fill
   • Phone numbers split into country code and local number;
   • Establishment type derived from the user profile type;
   • Establishment partners de-duplicated;
   • Establishment emirate repointed to the new reference data;
   • Legacy attachments registered in the unified file table;
   • Remaining gap-filling, cleaning and dirty-data correction;
   • Triggers switched back on — the platform returns to normal operating state.

4. Deciding what each application is

The legacy system identified a service by a numeric service ID. The new platform uses a ServiceCode as its business identity anchor. The two are not a simple lookup: the same legacy service ID can map to different new service codes depending on who applied and what they applied for.

So there is a dedicated routing stage that decides, line by line, which service each application detail belongs to. It works in three tiers:

Once the base service code is known, the lifecycle is applied: whether this is a new, renewal or amendment request determines the final service code, because in the new platform the lifecycle is part of the service identity rather than a separate field.

5. Rebuilding the application chain

5.1 New numbering, with one deliberate exception

Applications, application details, licences, certificates and the licence index all changed both their numbering scheme and the way they link to each other. The migration builds an old-to-new number mapping table, and every relationship is reconnected through it.

One exception is kept on purpose: the application detail number is preserved exactly as it was in the legacy system. A staff member holding a legacy reference number can look it up directly in the new platform.

5.2 Rebuilding the workflow

The legacy system ran approvals on its own state machine. The new platform uses the Camunda process engine. Historical cases obviously cannot be re-executed through Camunda, so the migration takes a different approach: it synthesises the matching process instances and task nodes from the historical approval record, so what the new platform displays agrees with what actually happened.

The practical consequence: a historical case shows its full approval trail in the new platform, but the synthesised process cannot be pushed further through Camunda. For a completed case that is not a limitation. For an in-flight one it is — see section 9.

5.3 Restoring the form content

The legacy system scattered what the applicant filled in across many different columns and tables. The new platform stores it as one structured form document. The migration reassembles those scattered fields into a complete form per service template, so that opening a historical application in the new platform shows what was originally submitted.

5.4 Nothing disappears silently

Some details genuinely cannot be migrated — the service cannot be determined, or a mandatory link is missing in the source. These are not dropped quietly. Each one is written to a discard ledger with its reason, and the audit then reconciles the ledger against the source:

The discard ledger is part of the delivery. It needs a business review — the point is for the business to confirm “yes, these genuinely do not need to move”, rather than for that to be decided technically.

6. Before & after, by business area

This is the part that matters most day to day: the same thing, in a different shape.

6.1 Services and business identity

Impact: report definitions, permission configuration and fee rules all need to be realigned onto service codes. The legacy ID is retained for traceability but never drives a business decision.

6.2 Users and establishments

Impact: a user may now see several establishment identities after login and has to choose which one they are acting for. That is the design, not duplicated data.

6.3 Applications

6.4 Workflow and approvals

6.5 Certificates and licences

Impact: “which valid licences does this party currently hold?” is answered from the licence index from now on.

6.6 Inspection and payment

6.7 Content business

7. How data physically moves

Four mechanisms, chosen per table according to how much reshaping it needs.

8. How we prove it is correct

“It finished” is not the same as “it is right”. There are three layers of verification.

Layer 1 — In-flight guards

The row-count and statement-count checks described above. They run during the migration and stop it on the spot rather than letting a shortfall flow downstream.

Layer 2 — Deep audit at the end of the run

The final stage of every migration runs an automated audit: 11 groups, 60+ individual checks, ending with an N/M checks passed summary and the offending records listed for anything that fails.

(There is no group E — the lettering is historical.)

The three most important checks in group C:

• Nothing vanishes — every legacy application detail is either in the new database or in the discard ledger.
• Detail numbers are unchanged — a legacy reference number still resolves.
• Totals reconcile — the application count in the new database equals the row count of the old-to-new mapping table.

Group G deserves its own mention: the platform holds a large amount of Arabic content, and bulk import is precisely where character corruption appears. The audit samples Arabic fields specifically to confirm they arrived intact.

Layer 3 — Independent verification

9. Where it breaks most often

Two secondary areas worth attention during validation:

• Service routing edge cases — an application under the wrong service code looks perfectly healthy on screen. Spot-checking a sample per service is worth more here than checking totals.
• Historical dirty data now becoming visible — the legacy system hid some duplicates by filtering them only when reading. The new platform does not. Where that happens, the position taken is that dirty data is cleaned during migration and the application code stays simple — for example duplicate establishment-partner links are physically removed during the run rather than filtered at display time.

10. When something goes wrong

The run is interrupted

The migration supports resuming from the point of interruption — there is no need to repeat the hours of work already completed. Each resume path clears and reloads only its own scope, so resuming never doubles up data.

One business-relevant exception: resuming from the reference-data stage does not re-run the form restoration. If the interruption happened after form restoration, confirm whether the form data needs to be completed separately.

The data looks wrong

Suggested order of investigation:

1. Read the audit report first — a failed check names the exact record and the exact rule.
2. Then check the discard and unresolved ledgers — if a record “is not there”, it is most likely in one of these two, with a reason attached.
3. Only then question the migration logic — the first two steps locate the large majority of issues.

Just looking, without touching anything

There are read-only diagnostic commands: check space, print the execution plan, run the audit alone. None of them change any data, so they are safe to run against a live environment.

Timing and attribution

Every run reports how long each stage took and, if it failed, which stage it failed in and after how long. Failures are also classified — an environment-level problem such as a full disk volume is labelled as such, so it goes to the DBA rather than being investigated as a mapping defect.

11. What we need from the business

1. Confirm the service code mapping

   The highest-priority routing rule is the manually specified mapping. That table is owned by the business: when an automatic rule cannot judge an exception, the business confirms it and the row is entered. No code change is needed.

2. Review the discard ledger line by line

   The migration honestly lists everything it could not process. That list needs business confirmation that these records genuinely do not need to migrate — that call should not be made technically.

3. Raise any legacy reference values that must survive

   Dictionaries, the service catalogue, permissions, inspection standards and report definitions are built from standard seed data and do not inherit legacy history. If a specific legacy value must be preserved, it has to be raised in advance so it can be added to the seed.

12. Table by table — rebuilt by rule (17 tables)

These seventeen tables cannot be copied. Each one is produced by a transformation procedure inside the staging database, checked there, and only then loaded into the new platform. The right-hand column is the business rule that actually runs.

12.1 People and accounts

12.2 The application chain

12.3 Workflow and approvals

13. Table by table — rewritten in transit (20 tables)

These tables are structurally close on both sides, so no procedure is needed — but they cannot be copied verbatim either, because the new platform added columns, tightened data types or renumbered dictionary values. Each one is read through a custom projection that reshapes it on the way out.

The four patterns behind every rewrite

14. Table by table — the five business domains rebuilt separately

Content, enquiries, supervisor reports, inspection and payment are each rebuilt by their own procedure. They are handled apart from the application chain for one practical reason: legacy and new live on different database servers, and a rule that joins across servers row by row is unusably slow. So each domain runs in three moves — bring the new platform's reference tables next to the legacy data → apply the rules locally → send the finished result back.

14.1 Content library 12 output tables

14.2 Enquiries

Enquiry tickets, their detail lines and their action history are rebuilt against the new profile numbering. The heavy part is matching each ticket to the correct user profile — legacy holds a login id, the new platform holds a profile id, and where a user has several profiles the active one is preferred, then the oldest. Because that lookup runs for every ticket, the reference table is indexed on the staging side before the rules run.

14.3 Supervisor reports 1.5–2 h → minutes

Reports, their classifications and their notes. Three output tables, no business re-mapping beyond renumbering — this domain is listed mainly because it was the clearest performance case: the original version joined across servers for every report and took one and a half to two hours in UAT; rebuilt as a local, indexed join it now finishes in minutes. The output is identical.

14.4 Inspection 11 legacy tables → 34

The most heavily restructured domain. The legacy inspection module is not migrated as-is at all; it is re-expressed in the new, far more detailed model. Everything runs under a fixed system actor, so migrated records are clearly attributable to the migration rather than to a real inspector.

14.5 Payment 7 legacy tables → 15

Payment runs last of the five, because it consumes results produced by the earlier domains.

15. Table by table — finishing work after loading

A few things can only be corrected once the data is already in the new platform, because they depend on tables that arrive from different stages. These run at the end, against the new database, and every one of them can be re-run safely.