NetSuite and Postgres for manufacturing: model production status without losing order identity

What this looks like in manufacturing

A manufacturer keeps subcontract milestones and inspection tasks in a Postgres application while finance and customer orders live in NetSuite. Three operations contribute to one shipped assembly. A simple last-update-wins design marks the whole order complete when the first outside processor reports finished. The correct model stores each operation independently and evaluates readiness only after the required evidence is present. Customer service needs the derived readiness and its explanation; finance needs confirmed transactions, not a copied interpretation of a shop-floor note.

Records, ownership, and update rules

Work through the process

1. 01
   Define the operational grain

   Choose whether the application tracks jobs, operations, lots or service tasks. Draw the relationships to ERP order lines, including one-to-many cases. Avoid flattening several operations into one mutable status column when the customer commitment depends on all of them.
2. 02
   Build durable references

   Use generated local primary keys and a separate cross-reference to the external record. Add a uniqueness rule for the business identity being synchronized. Review existing rows before backfill so historical duplicates are reconciled rather than preserved as competing versions of the same order.
3. 03
   Derive readiness transparently

   List the required operations, quality decisions and quantities for each completion rule. Preserve the underlying evidence and time of evaluation. Show an unknown or blocked state when required inputs are missing instead of assuming that the most recent positive update represents the entire job.
4. 04
   Validate return operations separately

   If the workflow needs to update NetSuite, prove the exact writable record and action with the administrator. Compare the approved request with the destination response, store the accepted reference and reconcile after an interrupted request before retrying. A successful database write alone is not ERP acceptance.

Handle the exceptions explicitly

An operation reports completion twice

Identify the same logical event or completion state and retain one effective result while keeping enough history to explain the retry.

One job supplies several order lines

Use explicit allocation relationships. Do not assign the full completed quantity independently to every linked customer line.

A late quality rejection follows completion

Re-evaluate readiness and alert the responsible planner. Keep the previous decision in history rather than deleting evidence that the job was once considered ready.

What to verify before expanding

• One ERP line can be traced to all required operations without multiplying its quantity.
• Repeated delivery retains stable external references and local primary keys.
• A missing quality decision produces an unresolved state rather than a completed order.
• ERP acceptance is recorded only after the permitted destination operation succeeds.

Technical references

• Oracle NetSuite: using external IDs