The second retail ERP build I did went smoothly almost entirely because of mistakes I made on the first one. Same product category, same general industry, two different clients, both on a legacy open-source ERP. The first taught me what a schema does to you when you get it wrong. The second was me refusing to repeat it.
Here is the lesson compressed to one line: you do not choose a database on day one. You choose which automations will be possible in year two, and you do it before you understand the business well enough to choose wisely. That is the cruelty of it.
Build one: the shortcut that cost a year
The first client sold goods across a physical store and an online channel. The platform modeled a lot of this out of the box, but they had quirks — bundles, a loyalty scheme, channel-specific pricing — and quirks are where you make schema decisions.
I made the fast choice. I let the point-of-sale side and the inventory side stay loosely coupled, syncing through a nightly reconciliation rather than sharing one canonical notion of a stock unit. It shipped weeks earlier than the clean version would have. The client was happy. I was happy.
Eighteen months later they asked for the obvious next thing: automatic reorder suggestions based on real sell-through, per channel, accounting for bundles. A reasonable ask. It was nearly impossible to deliver, because the POS and inventory sides had never actually agreed on what a “unit” was. A bundle sold at the register decremented stock one way; the same bundle online decremented it another. The nightly sync papered over the disagreement well enough for reporting and not at all for automation.
To build the reorder logic correctly I would have had to retroactively define the shared unit and backfill every historical transaction to it. That is not a feature. That is a migration with a business-continuity risk attached, and the client, sensibly, did not want to gamble Q4 on it. So the automation didn’t get built. The schema had already voted, eighteen months earlier, and the vote was final.
A loosely coupled schema is a loan against your future. The interest is paid in automations you can describe but cannot build.
Build two: treat the schema as the product
On the second build I inverted my priorities. I spent the first two weeks doing almost no UI work and instead nailing down the canonical entities and their relationships. One definition of a sellable unit. One source of truth for stock. Channels and pricing modeled as attributes of that canonical core, not as parallel universes that sync.
It was slower to first demo. The client noticed, and I had to defend it. The defense was simple: every clever thing you will want later is a query against this core if I build it now, or a migration if I don’t.
That prediction held. When the same automation request came up — reorder suggestions from real sell-through — it was a query, not a project. Because POS and online both decremented the same canonical unit, sell-through was just a fact already sitting in the data, waiting to be read. We shipped the suggestion engine in days.
Here is the contrast in the terms that actually mattered to the businesses:
| Capability needed in year two | Build one (loose schema) | Build two (canonical core) |
|---|---|---|
| Cross-channel sell-through | Required backfill migration | A query |
| Auto-reorder suggestions | Effectively blocked | Shipped in days |
| Unified margin per product | Approximate, untrustworthy | Exact |
| AI agent reading inventory | Ambiguous units, unsafe | One unit, safe to act on |
That last row is the bridge to everything I build now. You cannot let an AI act on inventory if the system itself disagrees about what a unit is. Ambiguous data is not just a reporting headache. It is a safety problem the moment a non-human actor starts pressing buttons against it.
What a good model actually buys you
The value of a clean data model is not elegance. Nobody pays for elegance. It is optionality. A canonical schema means the set of questions you can answer cheaply keeps growing as the business asks new ones, because the facts were stored at the right grain from the start.
Three practical tests I now apply before I write a line of UI:
- One concept, one definition. Is there exactly one place that defines what a unit, a customer, a payment is? If two subsystems each have their own, you have already taken the loan.
- The right grain. Am I storing facts at the finest grain the business might ever need, or am I pre-aggregating for convenience? Pre-aggregation is irreversible information loss. You can always roll up later. You can never roll down.
- Money is explicit. Is currency and unit unambiguous at every boundary? The cents-as-dollars failure — $340 read as $34,000 — is a data-model failure, and it is the kind that ends quarters.
The uncomfortable timing problem
The honest difficulty is that you make the most consequential schema decisions when you understand the business the least. Day one, before you have seen the edge cases, you are committing to the structure that will either enable or block year two.
There is no clean escape from this. What there is, is humility about it. On build two I assumed the business would want automations I could not yet name, and I structured the data to keep those doors open even at the cost of a slower start. That bet — that the future will demand more of your data than the present does — is almost always right. Businesses do not get simpler over time.
This is why, in Vontra, the AI-native ERP I’m building now, the data model is the first artifact and the UI is the last. The interface is a skin you can replace in an afternoon. The schema is a decision you live with for years, and by the time you feel its weight, changing it means betting the quarter on a migration. Get the model right while it is still cheap, because it never gets cheaper. It only gets more expensive and more load-bearing, until one day a client asks for something obvious and you have to tell them the schema decided against it eighteen months ago.