--- title: "MES Data Architecture: Work Orders & Genealogy" description: "Model MES data (work orders, routings, lot genealogy) in Postgres by extending your existing historian schema instead of a new system." section: "Postgres for IoT" published: 2026-10-14T17:01:42.788Z updated: 2026-10-14T00:00:00.000Z --- *Updated at Oct 14, 2026* > **TimescaleDB is now Tiger Data.** *Updated October 2026* ## What Is MES Data? MES data is the execution-layer data a manufacturing execution system manages: work orders, routings, material consumption and backflush records, and lot or batch genealogy. It's distinct from the raw sensor and tag data a [historian](https://www.tigerdata.com/learn/what-is-a-data-historian) or SCADA system captures, which measures what a machine is doing. MES data describes what a machine is supposed to be doing, and tracks its progress against that plan. That distinction is relational and event-based, not continuous. A temperature reading is a single value tied to a timestamp. A work order is a record with a start, an end, a status, and line items: what's being produced, how much, and where it currently stands. Modeling MES data means modeling records with a lifecycle, not readings on a stream. We should say upfront where we're coming from: Tiger Data sells a database. This is a data-modeling guide for engineers who need to store and query MES-specific data, not a comparison of MES platforms like SAP, Siemens Opcenter, or Rockwell FactoryTalk, and not an argument that a database replaces one. Two companion guides cover adjacent ground. The [Manufacturing Analytics Database guide](https://www.tigerdata.com/learn/manufacturing-analytics-database) models OEE, downtime, and quality telemetry, but explicitly scopes MES out. [Plant Historian](https://www.tigerdata.com/learn/plant-historian) models raw tag capture, but treats MES and OEE software as an opaque application layer. This page picks up where both leave off: the work orders, routings, and genealogy records neither one models. ## Where Does MES Data Fit in the ISA-95 Hierarchy? ISA-95 is the standard reference model for manufacturing operations, and it draws a clean boundary around where MES data lives. Level 2 covers SCADA and supervisory control: monitoring and supervising the process, with PLCs and other controllers one level below at Level 1. Level 3 covers MES (formally, manufacturing operations management): work orders, routings, scheduling, and execution tracking. Level 4 covers ERP: business planning, procurement, and finance. [B2MML](https://github.com/MESAInternational/B2MML-BatchML) (Business To Manufacturing Markup Language), maintained by MESA International, is the XML implementation of ISA-95's information models. You won't need to implement it to model MES data in Postgres, but it's the term you'll see in vendor documentation and integration specs written in ISA-95 vocabulary. | **ISA-95 Level** | **System** | **Data Type** | **Example Fields** | | --- | --- | --- | --- | | Level 2 | SCADA / supervisory control | Tag and telemetry data | Temperature, pressure, machine state | | Level 3 | MES | Execution data | Work orders, routings, genealogy, material consumption | | Level 4 | ERP | Business data | Purchase orders, cost centers, finance | A [Unified Namespace](https://www.tigerdata.com/blog/unified-namespace-historian-schema) sits alongside this hierarchy rather than inside one level of it: it's the real-time publish layer every level reads from and writes into. [Ask Your Factory Floor Anything](https://www.tigerdata.com/blog/ask-factory-floor-anything-structuring-industrial-data-ai-agents), [Unify Your Plant-Floor Data with Claude Code and TimescaleDB](https://www.tigerdata.com/blog/unify-plant-floor-data-claude-code-timescaledb), and both companion guides already cover UNS mechanics in depth. The schema section below extends that same namespace upward into the MES layer. ## Is MES the Same as SCADA? Resolving a Common Conflation No. SCADA operates at ISA-95 Level 2: real-time monitoring and supervisory control of the process and machines. MES operates at Level 3: work-order execution and scheduling. SCADA tells you what a machine is doing right now. MES tells you what job that machine is supposed to be running, and tracks its progress against a plan. [Plant Historian](https://www.tigerdata.com/learn/plant-historian) already flags the same underlying pattern for historian-versus-OEE/MES software: two adjacent layers that look similar from the outside but answer different questions. See that guide's section on where the historian layer ends and MES execution data begins for the specific boundary. Is MES the same as ERP? Also no. ERP operates at Level 4, business planning, and typically consumes summarized execution data from MES rather than managing shop-floor execution itself. ## What MES-Specific Data Types Does a Plant-Floor Database Need to Model? Four types matter: work orders and routings, parent-child lot or batch genealogy, material consumption and backflush records, and execution-layer OEE, computed against a specific work order or routing step rather than just a shift or line. None of these are readings; they're relational records with a lifecycle, a parent-child graph, or an event tied to a lot, and none fit naturally into a historian or time-series schema. ### Work Orders and Routings A work order is the unit of execution: what's being produced, how much, on which line, and its current status (released, in-progress, complete). A routing is the ordered sequence of operations, or steps, a work order moves through to get built, each tied to a specific line or cell. One work order has one routing; a routing has many ordered steps. That's where this ties back into the existing uns_namespace hierarchy: a routing step's workcenter points to a namespace row on the line or cell where the step runs.  ### Lot and Batch Genealogy (Parent-Child Traceability) Genealogy is a data-lineage problem: which raw-material lot, or lots, went into which finished-good lot, modeled as a parent-child relationship. It's a graph query, not a rollup: trace a finished lot back through every component lot that fed it, or forward from a raw-material lot to every finished good it touched. The schema section below models this as a self-referencing table, queried with a recursive query rather than a time-series aggregation. ### Material Consumption and Backflush Backflush is the practice of deducting consumed raw-material quantities from inventory automatically when a work order step completes, rather than recording each withdrawal manually. Each consumption record ties a work order and a material lot together with a quantity and a timestamp. Because backflush deducts standard bill-of-materials quantities, lot-controlled materials need the actual lot recorded at issue rather than inferred, and that recorded lot is what feeds the genealogy table.  ## How Do You Model This Data in Postgres? Extending the Unified Namespace Pattern These tables don't replace `uns_namespace` and `tag_history`; they extend the same schema upward, so execution data joins against the telemetry and OEE data the companion guides already model, in one Postgres database instead of point-to-point exports between an MES system, a historian, and an analytics tool. [United Manufacturing Hub](https://www.tigerdata.com/case-studies/united-manufacturing)'s open-source stack is a directional example of that pattern in production, though not an MES-specific deployment. The foundation is the `uns_namespace` and `tag_history` pair, reused exactly as published on the [Plant Historian](https://www.tigerdata.com/learn/plant-historian) guide: `CREATE TABLE uns_namespace ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, enterprise TEXT NOT NULL, site TEXT NOT NULL, area TEXT NOT NULL, line TEXT NOT NULL, cell TEXT NOT NULL, tag_name TEXT NOT NULL, uns_path TEXT GENERATED ALWAYS AS ( enterprise || '/' || site || '/' || area || '/' || line || '/' || cell || '/' || tag_name ) STORED, UNIQUE (enterprise, site, area, line, cell, tag_name) ); CREATE TABLE tag_history ( ts TIMESTAMPTZ NOT NULL, tag_id INT NOT NULL REFERENCES uns_namespace (id), value DOUBLE PRECISION NOT NULL, quality_code SMALLINT DEFAULT 0 ) WITH ( tsdb.hypertable, tsdb.partition_column = 'ts', tsdb.chunk_interval = '1 day' );` (The Manufacturing Analytics Database guide renders `uns_namespace` slightly differently, with `namespace_id` as a text primary key and `tag_name` living inside `tag_history`. Same pattern; this page extends the Plant Historian version.) `work_orders` and `routings` are ordinary relational tables. A routing step's `workcenter_id` references a `uns_namespace` row on the line or cell it runs on. Because each namespace row is one tag, any tag on that cell identifies it:  `CREATE TABLE work_orders ( work_order_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, item_code TEXT NOT NULL, line TEXT NOT NULL, quantity_ordered INT NOT NULL, status TEXT NOT NULL DEFAULT 'released', scheduled_start TIMESTAMPTZ, scheduled_end TIMESTAMPTZ ); CREATE TABLE routings ( routing_step_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, work_order_id INT NOT NULL REFERENCES work_orders (work_order_id), operation_seq INT NOT NULL, workcenter_id INT NOT NULL REFERENCES uns_namespace (id), standard_cycle_time INTERVAL );` `lot_genealogy` models the parent-child relationship directly, as a self-referencing table rather than a tree stored in application code: `CREATE TABLE lot_genealogy ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, child_lot_id TEXT NOT NULL, parent_lot_id TEXT NOT NULL, work_order_id INT NOT NULL REFERENCES work_orders (work_order_id), quantity NUMERIC NOT NULL, ts TIMESTAMPTZ NOT NULL );` This is the query that makes the table worth having: a recursive CTE that traces every raw-material lot feeding into a given finished-good lot, no matter how many steps separate them. `WITH RECURSIVE lot_trace AS ( SELECT child_lot_id, parent_lot_id, work_order_id, quantity, ts FROM lot_genealogy WHERE child_lot_id = 'FG-10294' UNION ALL SELECT lg.child_lot_id, lg.parent_lot_id, lg.work_order_id, lg.quantity, lg.ts FROM lot_genealogy lg JOIN lot_trace lt ON lg.child_lot_id = lt.parent_lot_id ) SELECT * FROM lot_trace;` Run it forward instead, by swapping which side of the join you anchor on, and it answers the opposite question: every finished good a given raw-material lot touched. If rework can feed a lot back into its own history, add PostgreSQL's CYCLE clause (PostgreSQL 14 and later) so the recursion stops at a loop.  `material_consumption` is different from the other three: it's time-stamped and event-based, like the pillar's `quality_events` table, so it belongs as a [hypertable](https://www.tigerdata.com/docs/learn/hypertables/understand-hypertables), a TimescaleDB feature available self-hosted, in Tiger Cloud, and in TimescaleDB Enterprise, even though work orders, routings, and genealogy are ordinary relational tables: `CREATE TABLE material_consumption ( ts TIMESTAMPTZ NOT NULL, work_order_id INT NOT NULL REFERENCES work_orders (work_order_id), material_lot_id TEXT NOT NULL, quantity_consumed NUMERIC NOT NULL ) WITH ( tsdb.hypertable, tsdb.partition_column = 'ts' ); ` Here's the "one database" pitch made concrete: pull the `tag_history` readings for the specific cell a work order's routing step ran on, during the work order's scheduled window. The query resolves the step's cell first, then pulls every tag on it. `SELECT th.ts, ns.tag_name, th.value FROM routings r JOIN work_orders wo ON wo.work_order_id = r.work_order_id JOIN uns_namespace wc ON wc.id = r.workcenter_id JOIN uns_namespace ns ON (ns.enterprise, ns.site, ns.area, ns.line, ns.cell) = (wc.enterprise, wc.site, wc.area, wc.line, wc.cell) JOIN tag_history th ON th.tag_id = ns.id WHERE r.work_order_id = 48213 AND th.ts BETWEEN wo.scheduled_start AND COALESCE(wo.scheduled_end, now());` Execution-layer OEE, computed per work order or routing step rather than per shift, uses the same [continuous aggregate](https://www.tigerdata.com/docs/learn/continuous-aggregates) pattern the [Manufacturing Analytics Database guide](https://www.tigerdata.com/learn/manufacturing-analytics-database) builds for shift-level OEE, grouped by `work_order_id` within each time bucket instead of by shift and line. A continuous aggregate always groups by a time bucket, and if it joins work_orders, later changes to that table aren't picked up by refreshes. ## How Do You Get Data from a PLC to an Upper-Level MES/ERP System Reliably and Securely? Publish PLC and SCADA data into a Unified Namespace once, and let every upper-level system subscribe to it instead of wiring a separate point-to-point integration for each pair of systems. PLCs (Level 1) and SCADA systems (Level 2) publish via OPC UA or MQTT into a Unified Namespace. MES (Level 3) consumes from that namespace to drive execution, and writes work-order and genealogy data back into the same hierarchy. ERP (Level 4) consumes summarized execution data from MES, typically on a slower cadence. The failure mode this replaces is familiar: MES-to-ERP integration commonly still runs on manual or batch CSV exports on a daily or weekly cadence, leaving cost and production data stale by days or weeks. It's the same data-fragmentation problem [IIoT in Manufacturing](https://www.tigerdata.com/blog/top-5-iot-manufacturing-industry-trends-and-their-data-challenges) covers more broadly: point-to-point integrations with no shared data layer. "Reliably" means the UNS publish-subscribe pattern survives tag renames and line reorganizations without rewriting history, an argument that this article - [A Unified Namespace Determines Your Historian Schema](https://www.tigerdata.com/blog/unified-namespace-historian-schema) makes in full. "Securely" means standard network segmentation and protocol-level security, OPC UA's built-in authentication, for example, at the SCADA/MES boundary. One myth worth addressing: a historian or time-series database already captures the tag data moving through this pipeline, but it doesn't natively model the work-order and genealogy records MES manages. Those are relational records that need a home alongside the hypertables, which is exactly what the schema above builds. For the protocol and infrastructure layer underneath this pipeline, see [SCADA Data Management at Scale](https://www.tigerdata.com/learn/scada-data-management-at-scale-architecture-historians-and-the-modern-database). ## Illustrative Example: ACME Manufacturing's MES Schema in Action Continuing the ACME Manufacturing example from the Manufacturing Analytics Database guide (same stamping line, same press-2 cell): a work order is released for a batch of sheet-metal brackets, with a routing that has a single step at press-2. As the work order runs, `machine_events` and `tag_history` already capture the press-cycle and vibration data. The new piece: a `material_consumption` record logs the raw-material coil lot issued at that step, and a `lot_genealogy` row ties that coil lot to the finished-bracket lot it became.  Given a finished-bracket lot number, this traces back to the exact coil lot and work order that consumed it: `WITH RECURSIVE lot_trace AS ( SELECT child_lot_id, parent_lot_id, work_order_id, quantity, ts FROM lot_genealogy WHERE child_lot_id = 'BRACKET-55021' UNION ALL SELECT lg.child_lot_id, lg.parent_lot_id, lg.work_order_id, lg.quantity, lg.ts FROM lot_genealogy lg JOIN lot_trace lt ON lg.child_lot_id = lt.parent_lot_id ) SELECT * FROM lot_trace; ` One row back: `BRACKET-55021` traces to `COIL-88107`, consumed under work order 48213. That's a parent lot, a quantity, and a timestamp, not a spreadsheet lookup or a call to a second system. ## Decision Framework: Where Should MES Data Live? Whether you think of it as standing up a dedicated MES database or simply extending the Postgres schema you already have, the choice comes down to four scenarios: **Extend your existing Postgres schema with MES execution tables if:** you already store telemetry, OEE, or quality data in one of Tiger Data’s products ([TimescaleDB](https://www.tigerdata.com/timescaledb), [Tiger Cloud](https://www.tigerdata.com/cloud), or [TimescaleDB Enterprise](https://www.tigerdata.com/timescaledb-enterprise)) and want work-order, routing, or genealogy data joinable against it in SQL, for dashboards, AI-agent access, or cross-system reporting your MES platform's own tools don't cover. **Keep MES data inside your MES platform's native database only if:** your only requirement is running the platform itself, routing, scheduling, execution tracking, with no cross-system analytics or genealogy-join use case pulling on that data. **Run your MES platform as the system of record and extend Postgres alongside it if:** you need its execution logic and workflow, it isn't being replaced, but also want SQL-native joins between execution data and the telemetry and quality data already in TimescaleDB, Tiger Cloud, or TimescaleDB Enterprise. This is the common middle path, the same "run in parallel" pattern the companion guides describe for historians. **Don't model MES data in a time-series-only store if:** that store has no natural home for relational, event-based records like work orders and genealogy. This applies even to time-series databases with SQL join support, since the underlying data is still relational business data, not time-series readings. See [data historian vs. time-series database](https://www.tigerdata.com/learn/moving-past-legacy-systems-data-historian-vs-time-series-database) for the fuller keep-replace-or-run-parallel framework. ## Migration Paths Into an MES-Aware Data Architecture **From manual CSV/ERP exports.** Teams commonly start with a daily or weekly manual export. The trigger to move off this path is usually that cost or production reporting is running days or weeks stale. **From spreadsheet-based genealogy or OEE tracking.** Manual genealogy tracking tends to break down as SKU counts and lot volumes grow, much as spreadsheet-based OEE tracking does in the Manufacturing Analytics Database guide.  **From an MES platform with no analytics layer.** This shows up when a team needs to join work-order or genealogy data against quality or telemetry data, and finds the platform's own reporting tools don't support that kind of cross-system query. See the [migration paths section](https://www.tigerdata.com/learn/manufacturing-analytics-database) of the Manufacturing Analytics Database guide for the historian and legacy-system paths.