A concrete data modeling blueprint for construction ERP systems that need traceable cost flow from BOQ to PO to invoice.
Why generic ERP schemas fail in construction
Many mid-market construction firms try to build their operations around generic ERP data models. This is a common mistake. In standard retail or manufacturing, a purchase order (PO) represents a simple transaction: you buy a set number of items at a fixed cost, receive them, and pay the invoice. In construction, procurement is tightly coupled to the Bill of Quantities (BOQ), project-specific cost codes, and progressive subcontractor invoices.
A construction data model must track change-order lineage, partial deliveries, and invoice variances while ensuring that every transaction is mapped back to a specific budget line. This post outlines the PostgreSQL schema blueprint we deployed for Bau Nord GmbH to enforce cost traceability across the procurement lifecycle.
Entity relationship flow
The diagram below illustrates how a budget line propagates from the initial project estimate down to the final ledger posting:
Required schema constraints
To prevent cost overruns, the database schema must enforce specific constraints at the table level:
- Direct BOQ association: Every line item on a purchase request or PO must reference a valid `boq_item_id`. This prevents site teams from ordering materials not scoped in the project plan.
- Cumulative cost limits: The database prevents the insertion of PO line items if the cumulative PO value for a BOQ item exceeds the allocated budget limit, unless a signed change order is present.
- Three-Way match validation: When an invoice is received, a database trigger verifies that the invoice value matches both the PO value and the goods receipt quantity within a 2% variance threshold. Anything exceeding this threshold is blocked and flagged for review.
- Immutability of posted costs: Once a cost ledger record is created, edits are disabled. Changes must be recorded as revision events.
Migration checklist for legacy ERP data
When migrating legacy spreadsheets or database tables to this schema, follow these guidelines:
- Normalize cost codes: Map legacy cost identifiers to a standardized, hierarchical structure (e.g., Labor, Materials, Subcontracting, Equipment).
- Preserve original IDs: Store original legacy transaction IDs in a metadata column to support audit tracking and historic reconciliation.
- Reconcile variance history: Run the three-way validation script against historical invoices for the last 12 months. This identifies existing vendor over-billing before going live.
Our take
A reliable construction ERP starts with the relational data model, not the user interface. If you do not enforce budget boundaries and invoice validations in your database schemas, cost leakage will continue to occur, regardless of how clean your frontend UI looks. Build with strict foreign keys, trigger validations, and write-once ledgers from the start.
