ERP Migration: Reverse-Engineer the Source Schema

You have been handed a source system with hundreds of tables, an undocumented database schema, and nobody left who built it. The migration is already scoped, the client is waiting, and your first job is to figure out what this database actually contains before you can map a single field to the target.

This is the least glamorous phase of any ERP migration and the one that decides whether the rest goes well. An ERP integrator we interviewed put data mapping at 10 to 15 percent of total migration project effort and told us it was the hardest, riskiest part of the job. Here is a complete working method for reverse-engineering that schema, Postgres-first, with real queries you can run today. Everything here ports to MySQL, SQL Server, or Oracle: the catalog names change, the method does not.

Why "just look at the tables" fails

Opening a database browser and reading table names feels like progress. It is not, for three reasons.

Naming drift. A table named clients was renamed in the application to "Accounts" five years ago, but the database never followed. A column called temp_flag has driven invoicing logic since 2019. Names record what things meant at creation time, not what they mean now.

Dead tables. Legacy databases accumulate corpses: abandoned features, one-off imports, backups someone made with CREATE TABLE orders_bak_2021. In a decade-old system, it is common to find tables that receive no reads or writes at all. Mapping them wastes effort; worse, migrating them pollutes the target.

Business logic hidden in application code. The database says status is a varchar. Only the application knows that status = '4' means "credit hold" and that records with type_id = 99 are soft-deleted. The schema is half the story; usage is the other half.

So the method is: inventory, infer relationships, decode columns, then validate against usage. In that order.

Four steps, in order. Each one narrows what the next step has to guess at.

Step 1: Inventory what exists, and what is alive

Start with a full census. information_schema is portable across engines:

SELECT table_name, count(*) AS column_count
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_name
ORDER BY column_count DESC;

Then separate live tables from corpses. In Postgres, pg_stat_user_tables tells you which tables actually see activity:

SELECT relname,
       n_live_tup AS approx_rows,
       seq_scan + idx_scan AS reads_since_reset,
       n_tup_ins + n_tup_upd + n_tup_del AS writes_since_reset,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

Three signals matter here:

• Row counts. n_live_tup is an estimate, which is fine; you want orders of magnitude, not audit-grade numbers. Empty and near-empty tables go straight to the "probably dead" pile.
• Read and write counters. A table with millions of rows but zero reads since the last statistics reset is a strong candidate for exclusion. Check when the counters were reset (pg_stat_get_db_stat_reset_time) so you do not misread a fresh reset as inactivity.
• Recency. If the table has a created_at or updated_at column, SELECT max(updated_at) tells you when it last mattered. A "current" table whose newest row is from 2020 is telling you something.

Classify every table into live, archive, or dead. Only the first category gets full treatment; the second gets a business decision (migrate as history or leave behind); the third gets documented and excluded.

Step 2: Recover relationships that were never declared

Legacy systems, especially ones built on ORMs or by teams that "handled integrity in the app," frequently have few or no declared foreign keys. First, list what is declared, then find the gap. This query surfaces columns that look like foreign keys but have no constraint:

SELECT c.table_name, c.column_name
FROM information_schema.columns c
WHERE c.table_schema = 'public'
  AND c.column_name LIKE '%\_id'
  AND (c.table_name, c.column_name) NOT IN (
    SELECT tc.table_name, kcu.column_name
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu
      ON tc.constraint_name = kcu.constraint_name
    WHERE tc.constraint_type = 'FOREIGN KEY'
  );

Everything that comes back is a hypothesis, not a fact. You confirm hypotheses three ways.

Naming conventions, and where they betray you

customer_id in orders probably points at customers.id. Probably. Naming inference fails in predictable places:

• Polymorphic references. A column pair like owner_id plus owner_type points at different tables depending on the row. No single join target exists, and Odoo's res_id plus res_model pattern is exactly this. Treat any *_id column that sits next to a *_type or *_model column as polymorphic until proven otherwise.
• Denormalized copies. customer_id in an invoice_lines table may be a cached copy that drifted from the header. Same name, weaker guarantee.
• Renamed targets. client_id pointing at a table now called accounts, because the rename never propagated.

Value-overlap profiling, without hammering production

The real test of a hypothesized relationship is referential: do the values in the child column exist in the candidate parent? Do not run this as a full-table anti-join on a busy production system. Sample instead:

SELECT count(*) FILTER (WHERE c.id IS NULL) AS orphans,
       count(*) AS sampled
FROM (
  SELECT customer_id
  FROM orders TABLESAMPLE SYSTEM (1)
  WHERE customer_id IS NOT NULL
) s
LEFT JOIN customers c ON c.id = s.customer_id;

A 1 percent sample is usually enough to distinguish "this is a real relationship with a few orphans" from "these columns have nothing to do with each other." Run profiling against a restored backup or a read replica when one exists; when it does not, sample small, run during quiet hours, and set statement_timeout so a mistake cannot pile up.

Orphan rates matter beyond confirmation. A hypothesized relationship with 3 percent orphans is both a confirmed join and a data-quality finding you must resolve before load, because the target ERP will enforce the integrity the source never did.

Mine the joins the system actually performs

The most reliable witness to the real data model is the workload itself. If the pg_stat_statements extension is installed, it holds a record of the queries the application actually runs, including every join path the developers considered correct:

SELECT query, calls
FROM pg_stat_statements
WHERE query ILIKE '%join%'
ORDER BY calls DESC
LIMIT 50;

You are not reading this for query optimization. You are reading it as documentation: a join that runs thousands of times a day is a relationship the business depends on, whatever the constraints say. Application source code, if you have it, serves the same purpose. Search the ORM models and raw SQL for join conditions, and pay attention to joins on columns your naming inference never flagged, such as joins on composite business keys or on columns with unrelated names.

Assign a confidence level to every inferred relationship

This is the discipline that makes the map usable by the rest of the team. Every edge in your relationship map gets a grade:

High-confidence edges feed the field mapping directly. Medium ones get verified in the validation step below. Low ones are open questions for the client, not silent assumptions in your migration scripts. When something breaks during a trial load, the confidence column tells you where to look first.

Step 3: Infer what columns mean from the data itself

With the graph in place, decode the columns. Postgres keeps distribution statistics that answer most first questions without touching the table:

SELECT attname, n_distinct, most_common_vals
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'orders';

What to look for:

• Cardinality. A varchar with n_distinct = 7 is not free text, it is an enum hiding in a string column. GROUP BY it, count each value, and you have its domain.
• Magic codes. Numeric status columns with a handful of distinct values (1, 2, 3, 4, 99) encode workflow states defined only in application code. List them with row counts; the decoding happens by asking the people who use the system daily.
• Formats. Profile a sample with regex checks: is this "text" column actually always an email, an IBAN, a date stored as DD/MM/YYYY strings? Format drift within one column (two date formats, mixed casing of codes) is a transformation task you want in the estimate, not in the go-live weekend.
• Null patterns. A column that is null for 95 percent of rows but always populated for one type_id tells you two record types share one table, which usually means two target models in the ERP.

Step 4: Validate the map with the business, not the DBA

Here is the step most guides skip, and the one that saves migrations. The DBA (if one exists) can confirm structure. They usually cannot tell you what status = '4' means to the accounting team, because meaning lives in usage, not in the schema.

So take your draft map to the people who use the system daily, and walk key records end to end. Sit with an order administrator and trace one real order: the customer record, the lines, the delivery, the invoice, the weird correction entry from last March. Ask them to narrate what each screen shows, and match it against your tables. Walking a handful of well-chosen records with a daily user surfaces the corrections that solo profiling tends to miss: fields repurposed years ago, "everyone knows" conventions like negative quantities meaning returns, and the manual workaround that lives in a spreadsheet because the system never supported it.

Do this before you write mapping specs. Every medium-confidence relationship and every magic code list from step 3 goes on the agenda for these sessions.

Where this fits in the migration timeline

Schema reverse-engineering belongs at the very front, during scoping, before the quote is final if you can manage it. The output is a source data model document: table census with live/archive/dead classification, relationship map with confidence levels, column dictionary with decoded enums, and a data-quality findings list (orphans, format drift, duplicates). That document is what makes your source-to-target mapping document estimable, your transformation scripts testable, and your trial loads explainable when they fail. Skipping it does not remove the work; it moves the work into the load phase, where every surprise costs more.

Stop reverse-engineering undocumented schemas by hand

Everything above is doable manually, and experienced integrators do it on every project. It is also systematic enough that most of it should not be manual. This is the problem we are building Datapace for: a context layer that maps your database automatically, so your team, your integrators, and your AI agents all know exactly what's there, what it means, how it connects, and whether they can trust it. For migration teams, the aim is simple: auto-discover the source data model, so you scope every migration with confidence, with the relationship inference, confidence grading, and column profiling from this article done for you. The goal we are building toward is mapping a source system to Odoo in days, not weeks. We are building it as participants in Vector Institute's DaRMoD Summer 2026 cohort.

If you spend the first weeks of every migration rebuilding someone else's undocumented schema, we would like to compare notes with you. Read how we approach the reprise de données problem, or get in touch through datapace.ai and tell us about your worst source system.