Where a Postgres data model for a factory app should begin

A production team wants a small application that explains why jobs are waiting. The ERP knows when an order was released, but setup, inspection and handoff at the machine are recorded elsewhere. The request sounds like a few screens and a status selector. The harder question is what those screens will write to, and whether anyone can reconstruct what happened a week later.

Supabase gives a custom application a Postgres database. This article focuses on that database, not on every service in the Supabase stack. An earlier piece covers Supabase as a combined backend. Here the narrower problem is designing a place for job records and shop-floor events without confusing the two.

A job is not its latest status

Consider a hypothetical sheet metal shop. Its ERP releases job 4821 for cutting. The job includes a target quantity and due date. At the machine, an operator records setup, first-article inspection and a wait for material. Those entries describe progress, but none is the job itself.

A single status = waiting field is tempting. It makes the current screen easy to build. Then a supervisor asks when the wait began and what preceded it. If each status overwrote the last one, the answer is gone. A more useful starting point keeps the ERP job identity and its stable attributes apart from timestamped events on the shop floor. The current state can be derived from those events, while the history remains available for investigation.

This does not mean logging every tap on a tablet forever. A display preference and a quality approval serve different purposes and may need different retention rules. Decide what records answer actual operational questions. Collecting data without a reason makes later corrections and access control more difficult, not easier.

Make identities explicit before connecting systems

A first sketch might have jobs, operations and operation_events tables. One job has several operations; one operation can have several events. An event points to an operation and records its type, time and origin. That origin could be an operator action or an ERP update. This is a discussion model, not a ready-made schema for every plant.

Do not assume the printed job number is a permanent primary key. An ERP may reuse numbers in different business units or change an identifier after a revision. Give local records their own identities and store external identifiers together with the source system. When an integration retries the same job update, a defined uniqueness rule can prevent a duplicate. Events need an equivalent rule: a network retry should not turn a single stop into two stops.

There are also two useful times for an event: when it happened at the station and when the server received it. These can diverge when a tablet is offline. Compressing both into one timestamp conceals delayed data. Store times consistently and render them in the plant’s local time zone. State which time zone and shift cutoff a daily report uses, especially for work around midnight. A database can preserve facts, but it cannot guess the business meaning of a shift boundary.

What belongs in the database?

Postgres is good at enforcing relationships close to the data. A foreign key can prevent an event from referring to an operation that does not exist. A uniqueness constraint can reject an integration retry that would create a second copy. Required fields can stop incomplete rows. These protections apply even when a second client or a new connector starts writing to the same tables.

They do not decide every production rule. If the ERP cancels a job while the operator still has an operation open, which system wins? Who may reopen an operation after inspection? What happens when an offline update arrives after a newer one? Those are workflow and ownership decisions. Application code, integration logic and database constraints each implement a part of the answer. Putting all responsibility on the database, or all responsibility on the front end, misses the boundary.

Supabase makes data accessible through generated interfaces, which is convenient for a custom app but not a reason to expose every table to every signed-in user. Supabase documentation recommends Row Level Security and least-privilege grants for tables accessed from clients. A machine operator should not be able to edit another area’s approvals merely because both users have accounts. Server secrets should remain on trusted servers, not in a browser or a shared shop-floor tablet. Test denial cases as deliberately as success cases: can someone outside the relevant area read or change a record they should not see?

A small trial with real failure cases

Start with one operation rather than importing the whole plant. Bring in one job from the ERP, record a handful of events against its operation and show one shift view. Ask three questions of the records: what is waiting now, when did the wait begin, and who or what reported it? If the answers differ between the interface and the stored history, fix the data model before building another chart.

The trial should exercise failure, not just the happy path. Send the same ERP update twice. Record an event while a tablet is offline, then retry it. Attempt an edit from a user assigned to another work area. Write down the expected result for each case before testing. That turns “it works” into something a production lead and a developer can check together. Include a backup and restore rehearsal; the existence of a backup file alone says nothing about whether operations can recover from a bad change.

There is no requirement to replace the ERP. It can remain the owner of planning data while the Postgres-backed application records finer shop-floor detail tied to ERP identities. The useful outcome is not simply having a database. It is being able to explain the history of a job without losing earlier events, duplicating retries or crossing access boundaries.

Sources: Luke van Enkhuizen’s overview of open-source tools for metal fabricators, Supabase database documentation and Supabase data security guidance. The shop example is hypothetical.