Data Integrity Is the Moat: Why AI Can't Shortcut 20 Years of ERP Schema Design

Key Takeaways: A foreign key constraint in Odoo isn’t a database implementation detail; it’s a codified business rule about what can and cannot exist. Two decades of ERP schema design have encoded fiscal year boundaries, concurrent write protections, audit trail requirements, and multi-company isolation into the data layer itself. Generating a database schema from a promptThe input text provided to an LLM to guide its response. Prompt design — choosing words, structure, and examples — significantly affects output quality. Also referred to as the user message or query. produces something that looks structurally similar but carries none of this accumulated logic. The promptThe input text provided to an LLM to guide its response. Prompt design — choosing words, structure, and examples — significantly affects output quality. Also referred to as the user message or query. gets the tables right. It misses what the constraints are doing.

The Difference Between a Schema and a System

There’s a common confusion in how people talk about ERP design. The tables are visible. The constraints are legible. You can dump the schema for an Odoo instance and read it: account.move, account.move.line, res.company, ir.rule. A language modelA mathematical function trained on data that maps inputs to outputs. In ML, a model is the artifact produced after training — it encapsulates learned patterns and is used to make predictions or… can look at that schema and reproduce something that resembles it.

What doesn’t survive that reproduction is the why behind each constraint.

Consider account.move. An accounting entry in Odoo has a company_id field that’s required, indexed, and foreign-keyed to res.company. That sounds like ordinary database hygiene. It isn’t. That constraint enforces multi-company data isolation at the row level, not through application code that could be bypassed, but through the data modelA mathematical function trained on data that maps inputs to outputs. In ML, a model is the artifact produced after training — it encapsulates learned patterns and is used to make predictions or… itself. The constraint means a journal entry can never, at the database layer, reference a company that doesn’t exist. The check happens before the ORM, before the Python, before the controller. A generated schema might include the foreign key. It probably won’t enforce it the same way across every related table.

We explored this in more depth in Can an ERP Built with Claude Replace Odoo?, but the data integrity question deserves its own treatment, because it’s where the gap is most concrete and most consequential.

Foreign Keys as Business Rules

Foreign key constraints in a mature ERP schema are not primarily about referential integrity in the computer science sense. They’re about which states are legal to enter.

In Odoo, you can’t create an account.move.line (a journal entry line) without a valid account_id pointing to a live chart of accounts entry. That constraint prevents orphaned accounting lines. But it also prevents booking revenue to a deleted account, something that happens easily in manual workarounds and breaks reconciliation for the lifetime of the data.

The same logic applies to res.currency. A move line’s currency must reference an active currency. That’s not database hygiene either; it’s making sure the system can always compute an exchange rate for the transaction, now and in five years when someone runs a historical profit-and-loss report.

These constraints weren’t designed in one session. They evolved across releases as teams encountered the edge cases: deleted accounts with open transactions, inactive currencies referenced in archived invoices, tax lines pointing to deprecated tax configurations. Each constraint represents a class of error that burned someone.

A generated schema that looks like Odoo doesn’t carry this history. The constraints that are missing are the ones that prevent the most obscure, most expensive breakages.

Concurrent Writes and What Happens Without Locking

Accounting software runs under concurrent load. Multiple users post journal entries simultaneously. Numbering sequences need to be unique. Balances need to be consistent.

Odoo handles this with FOR UPDATE NOWAIT, a PostgreSQL locking pattern that acquires a row-level lock before modifying a sequence counter, and immediately raises an error if the row is already locked rather than waiting. This is the mechanism behind sequence numbers on invoices, vendor bills, and journal entries.

SELECT id FROM ir_sequence WHERE code = 'account.move' FOR UPDATE NOWAIT;

The pattern matters because accounting sequences are legally significant in most jurisdictions. In Vietnam, sequential invoice numbering is required under the regulations governing hóa đơn điện tử (e-invoices). A gap in the sequence, or a duplicate number, can trigger a tax audit finding.

A web application scaffolded from an LLMA neural network trained on vast amounts of text data to understand and generate human language. LLMs use the Transformer architecture and can perform a wide range of tasks — summarization,… promptThe input text provided to an LLM to guide its response. Prompt design — choosing words, structure, and examples — significantly affects output quality. Also referred to as the user message or query. will handle sequence generation the way most web applications do: pull the current max, add one, write it back. Under concurrent load, this produces duplicate numbers. The first deployment looks fine. The third month in production has problems. By month twelve, reconciliation is a mess.

The lock strategy isn’t complicated. But it has to be there from the beginning, applied consistently across every modelA mathematical function trained on data that maps inputs to outputs. In ML, a model is the artifact produced after training — it encapsulates learned patterns and is used to make predictions or… that generates a legal document number. That consistency takes time to build and is easy to miss in the initial implementation.

Fiscal Year Locks Are Not Optional

Most accounting systems have a concept of a closed period. Once a fiscal year is closed, posted transactions shouldn’t be editable, not because a developer decided to add a validation, but because modifiable historical data is incompatible with auditable accounting.

In Odoo, the lock date on res.company (period_lock_date, fiscalyear_lock_date) prevents posting to closed periods. These checks fire in the ORM before a write completes. They’re not UI-level validations that a developer could bypass by hitting the API directly. The modelA mathematical function trained on data that maps inputs to outputs. In ML, a model is the artifact produced after training — it encapsulates learned patterns and is used to make predictions or… itself refuses the write.

# From account/models/account_move.py (simplified)
if move.date <= company.fiscalyear_lock_date:
    raise UserError(_("You cannot modify a posted entry prior to the lock date."))

This seems like a small detail. Auditors don’t think so. A system without fiscal year locks lets a careless or deliberate edit change historical revenue figures. For a business with external auditors, that’s not a compliance risk in the abstract. It’s a showstopper.

A generated system might implement a “closed period” flag. It probably won’t implement the lock at the modelA mathematical function trained on data that maps inputs to outputs. In ML, a model is the artifact produced after training — it encapsulates learned patterns and is used to make predictions or… level, with the same consistency across invoice posting, payment reconciliation, and year-end close. Those are exactly the moments that break.

Multi-Company Isolation Is a Data Architecture Problem

Running two legal entities out of one database requires that their data never accidentally mix. Odoo’s approach is to embed company_id into every financially significant modelA mathematical function trained on data that maps inputs to outputs. In ML, a model is the artifact produced after training — it encapsulates learned patterns and is used to make predictions or… and enforce visibility through ir.rule, which are record rules that filter queries by the current user’s active company.

# ir.rule restricts access at the ORM level
[('company_id', 'in', company_ids)]

This is enforced at the ORM layer, not the UI layer. Code that reads account.move through the Odoo ORM gets company-filtered results automatically. The risk is code that bypasses the ORM: raw SQL queries, direct cursor calls, poorly written external API integrations that read across company boundaries without realizing it.

In practice, Vietnamese companies running multi-entity structures (holding company plus operating subsidiaries) need this working correctly before go-live, not discovered during the first inter-company reconciliation. The VAT (GTGT) filings for each entity are separate legal documents. Mixed data means wrong tax filings, and fixing it retroactively means amending declarations.

Building multi-company isolation correctly requires deciding, for every modelA mathematical function trained on data that maps inputs to outputs. In ML, a model is the artifact produced after training — it encapsulates learned patterns and is used to make predictions or…, whether records belong to a single company (most operational data) or are shared across companies (product catalog, chart of accounts in certain configurations). That decision affects the schema, the record rules, and the UI. Getting it right after go-live is a significant migration project. Getting it wrong quietly corrupts inter-company reporting for months before anyone notices.

What This Means for AI-Assisted ERP Development

None of this argues against using AI in ERP work. LLMs are genuinely useful for writing business logic, drafting field-level validation code, and generating views. The productivity gains are real.

They are not useful as the source of schema design decisions for financial data.

The reason is simple: the constraints that matter in ERP schemas are the ones that prevent states that have never happened in your specific system, but have happened somewhere, to someone, in a way that was expensive to fix. That knowledge doesn’t come from trainingThe process of exposing a machine learning model to labeled or unlabeled data so it can learn patterns. During training, the model adjusts its internal parameters (weights) to minimize a loss… data. It comes from running accounting software at scale and building in the guards after the edge cases appear.

A promptThe input text provided to an LLM to guide its response. Prompt design — choosing words, structure, and examples — significantly affects output quality. Also referred to as the user message or query. can produce a schema that looks right. It will miss the FOR UPDATE NOWAIT on sequence generation. It might miss the fiscal year lock at the modelA mathematical function trained on data that maps inputs to outputs. In ML, a model is the artifact produced after training — it encapsulates learned patterns and is used to make predictions or… level. It will probably get multi-company isolation mostly right in the happy path and wrong in the edge cases. The gaps are invisible until they’re expensive.

ERP data integrity is accumulated institutional knowledge encoded in constraints. You don’t generate that. You inherit it, or you spend years rebuilding it.

Key Takeaways

• Foreign key constraints in a mature ERP schema encode business rules, not just referential integrity. Each one represents a class of error that burned someone.
• Concurrent write protection (like FOR UPDATE NOWAIT on sequences) is legally significant in jurisdictions with sequential invoice numbering requirements.
• Fiscal year locks must be enforced at the modelA mathematical function trained on data that maps inputs to outputs. In ML, a model is the artifact produced after training — it encapsulates learned patterns and is used to make predictions or… layer. A modelA mathematical function trained on data that maps inputs to outputs. In ML, a model is the artifact produced after training — it encapsulates learned patterns and is used to make predictions or… that refuses the write cannot be bypassed by an API call or a direct database connection.
• Multi-company data isolation is a data architecture problem that has to be decided modelA mathematical function trained on data that maps inputs to outputs. In ML, a model is the artifact produced after training — it encapsulates learned patterns and is used to make predictions or…-by-modelA mathematical function trained on data that maps inputs to outputs. In ML, a model is the artifact produced after training — it encapsulates learned patterns and is used to make predictions or… before go-live, not retrofitted after the first inter-company reconciliation.
• An AI-generated schema can look structurally similar to a mature ERP schema and still be missing the constraints that matter most.

At Trobz, we work with Odoo’s data layer every day: extending it, integrating with it, and occasionally explaining to clients why a seemingly minor schema decision from year one is costing them work in year three. If you’re evaluating whether an Odoo customization or an AI-built component can handle financial data correctly, we’re happy to take a look.