Connecting an External AI Dashboard to Odoo with Chat-With-Data

Odoo is unusual among ERPs in that self-hosted deployments give you direct access to the PostgreSQL database underneath. That creates a genuine architectural choice, and choosing without understanding the trade-off is where these projects go wrong.

The ORM route uses Odoo's external API — XML-RPC or JSON-RPC — calling model methods such as search_read through the standard endpoints. Everything the ORM does on behalf of a normal user still applies: record rules, access rights, multi-company boundaries and computed fields all behave as Odoo intends. The cost is throughput. Large extracts through the ORM are slow, and they consume application-server resources that production users are also relying on.

The direct SQL route reads from a streaming replica of the database. It is dramatically faster for bulk extraction and imposes no load on the application server. What it gives up is everything the ORM was providing: computed and related fields that are not stored do not exist in the tables, record-level access rules are not applied, and multi-company separation becomes something your queries must reimplement correctly.

The pattern that holds up in practice uses both — the replica for bulk historical extraction where volume dominates, and the ORM for models where computed fields or access semantics matter more than speed. What does not hold up is reading production's primary database directly, which puts analytical load on the instance running the business.

Most production Odoo installations are not stock. Custom modules add fields and models, third-party modules from the app store alter behaviour, and studio customisations create fields that exist in one database and not another. An integration written against a documented stock schema will work in a demo instance and break on the customer's system.

The correct approach is runtime discovery. Odoo describes its own data model through ir.model and ir.model.fields, which can be queried to establish what models and fields actually exist in this specific database, including custom ones. The integration reads that description and adapts, rather than carrying assumptions.

This matters at upgrade time too. Odoo's major versions change models, and the difference between an integration that discovers the schema and one that assumes it is the difference between a configuration review and a rewrite.

Odoo maintains write_date on records, which is the basis for incremental synchronisation: read only what changed since the last successful run, and advance the watermark on completion. Full extracts remain available for backfill and post-schema-change rebuilds, but they are not the routine path.

Deletions again need explicit handling. Odoo can be configured to log them, and where that is unavailable the practical alternative is periodic reconciliation of identifier sets between source and warehouse. Skipping this produces a warehouse that slowly diverges upward from reality.

A subtlety specific to Odoo: some operations update related records without touching write_date on the record you are watching. Accounting entries and stock moves are the usual culprits. Where a model has this behaviour, the sync should key off the record that actually changes rather than the one that is conceptually interesting.

The reasoning here is identical to any other ERP, and worth restating because it is the step most often skipped. A language model pointed at extracted Odoo tables will generate queries that run and return numbers. It does not know which journals your organisation excludes from revenue, how you treat intercompany movements, or that a particular product category is reported separately for management purposes. The answers will be confident and wrong.

A semantic layer states those rules explicitly — entities, metrics, relationships, fiscal calendar — and the model generates queries against that model rather than raw tables. Odoo's flexibility makes this more important rather than less: two Odoo deployments in the same industry can represent the same business concept quite differently, so a definition layer is the only place the organisation's actual meaning can live.

The by-product is a written definition of the company's own metrics, which most organisations discover they have never had. The disagreements this surfaces during implementation are typically the most valuable output of the first phase.

The query layer's credential should be read-only against the analytics store and should have no path to Odoo at all. Row and column permissions belong in the database, enforced against the identity of the person asking, so entitlement does not depend on model behaviour. Generated queries should be validated before execution, with statement types restricted and cost bounded.

Answers should expose the query that produced them and the definitions applied. Beyond audit requirements, this is what makes the tool usable: a surprising number that can be inspected gets investigated, while a surprising number that cannot gets ignored — and with it, eventually, the whole system.

Because Odoo is frequently self-hosted, organisations running it have often already decided that owning their stack matters. An external analytics layer should follow the same principle: the pipeline, the semantic model and the dashboards are source code you hold, deployable in your own environment.

Practically, this means the historical record survives an Odoo major-version upgrade, a migration between hosting arrangements, or a decision to move off Odoo entirely. The analytics store is not a view into the ERP — it is an independent record that happens to be fed by it.