odoo-postgres-sync

A small, dependable incremental sync connector that pulls data from an Odoo ERP over JSON-RPC and lands it as JSONB in Postgres — the raw foundation for an analytics warehouse. It's built to run on a cron: deterministic, resumable, and idempotent, so a crashed run or an overlapping run never loses or double-counts data.

What it does

For each Odoo model you declare in config.yml, the connector:

1. Authenticates to Odoo with an API key and queries the model via search_read.
2. Walks forward from a write_date cursor with a configurable overlap window, so records changed between runs are never missed.
3. Pages through results deterministically and upserts each page into odoo_raw_records, keyed by (model, odoo_id) — duplicates from the overlap window collapse harmlessly.
4. Commits cursor state after every page, so an interrupted run resumes exactly where it stopped instead of restarting.
5. Records per-run audit metadata (status, counts, error) in odoo_sync_runs.

The result is a faithful, continuously-updated JSONB mirror of the Odoo models you care about, in Postgres — on top of which you build SQL views / fact tables and point a BI tool (e.g. Metabase) at a read-only role. See ARCHITECTURE.md for that layering.

Why it's built this way

• Idempotent by construction — (model, odoo_id) upserts make re-running always safe; there's no dedup bookkeeping to get wrong.
• Resumable — cursor checkpoints land per page, not per run, so a failure costs minutes, not a full re-sync.
• Config over code — adding a model or a field is a config.yml edit, not a deploy. A model with no prior state automatically runs a full-history backfill.
• Observable — every run writes a row to odoo_sync_runs; pair that with a cron heartbeat for free alerting.

Stack

Node.js + TypeScript (strict) · Odoo JSON-RPC · Postgres (pg) · JSONB storage · YAML-driven model config.

Quick start

npm ci
cp .env.example .env                          # set ODOO_API_KEY and DATABASE_URL
psql "$DATABASE_URL" -f sql/001_init.sql       # raw store + sync state/runs tables
npm run build
node dist/cli.js sync --config config.yml

The connector is idempotent, so repeated runs against a shared database are safe.

Configuration

config.yml (tracked, non-secret) declares the Odoo connection and the models to sync — each with its field list, an optional Odoo domain filter, and per-model cursor/paging overrides. Secrets (ODOO_API_KEY, DATABASE_URL) come from the environment; see .env.example.

License

MIT