x0 Home Postgres / warehouse

Postgres: platform DB vs customer warehouse

Two separate products. Do not conflate them.

Track Purpose Who connects Config
A. Platform DB Django/FastAPI ORM (users, tokens, JSON caches, logs) x0 processes only DATABASE_URL
B. Customer warehouse Optional relational SQL for BI tools Customer read-only role X0_WAREHOUSE_* 
flowchart TB
  subgraph trackA [Track A platform]
    App[Django + FastAPI] --> PlatformDB[(SQLite or Postgres)]
  end
  subgraph trackB [Track B optional]
    Materialize[Materializer] --> Warehouse[(Per-customer schema)]
    Warehouse --> CustomerSQL[Customer SQL / Power BI]
  end
  Sync[Xero sync] --> PlatformDB
  Sync --> Materialize

Never give customers credentials to Track A. That is a multi-tenant leak and still would not look like normal tables (cache is JSON blobs).

Track A — platform deploy option

  • Default: SQLite file under shim/db.sqlite3 (canoe, pytest, small on-site / docker sqlite profile).
  • Terrace: DATABASE_URL=postgresql://x0:…@10.1.41.1:5433/x0_platform via Docker x0-postgres (postgres:16-alpine) + pip install -r requirements-postgres.txt. Django 5.2 needs Postgres 14+.
  • Parser: config/db.py. Deploy: deploy.md.

SQLite remains supported indefinitely for tests and lean bundles.

Track B — customer SQL (Professional+)

Public hostname: db.x0.co.nz (not sql. — avoids implying SQL Server).

Opt-in warehouse: “Connect your BI tool to a read-only Postgres role scoped to your x0 cache.”

How data gets into the warehouse (not replication)

Application materialize — not Postgres logical/physical replication.

  1. Xero → x0 sync fills Track A CustomerTableCache.payload (JSON) as usual.
  2. After a successful refresh_table_cache, the materializer upserts rows into the customer’s warehouse schema (portal/services/warehouse_materializer.py).
  3. Customers query Track B only; OData still reads Track A.

Why not replication?

Approach Fit
Materialize Chosen — per-customer schema, typed/flattened rows, plan quotas, no exposing platform tables
Streaming replication Wrong — copies whole cluster; would leak Track A
Logical replication of cache tables Awkward — JSON blobs, multi-tenant filtering still needed

Staff can force a materialize from admin / provision flow once X0_WAREHOUSE_DATABASE_URL is set.

Isolation (chosen model)

  • One database x0_warehouse (shared), one schema per customer: c_<customer_id>.
  • Multiple SCRAM logins per customer (CustomerWarehouseCredential) — Excel, Power BI, Metabase, etc. Not tied to the portal user.
  • Role x0_c_<id> (primary) or x0_c_<id>_<hex> for extras; group x0_warehouse_readers
  • Long random password (shown once / reveal / rotate)
  • USAGE + SELECT only; search_path set to their schema
  • Gated by option postgresql_read_access on Professional (PricingTierOption + seed_options).
  • PUBLIC cannot CONNECT; admin DSN is X0_WAREHOUSE_DATABASE_URL (x0_wh_admin).

Indexes (postgres_indexes)

Catalogue table + seed (portal/services/warehouse_indexes.py) so a wiped warehouse can be rebuilt:

Kind Example
primary x0_pk (table DDL)
btree x0_tenant_id, ((data->>'Code')) on accounts, invoice number/status, …

Applied on every ensure_table / materialize (CREATE INDEX IF NOT EXISTS).

TLS + SCRAM (force encryption — no client certs)

Client certs are not viable for Excel / ODBC Power Query. Do not require them.

Instead, reject plaintext TCP server-side so Excel cannot silently downgrade:

hostnossl all all 0.0.0.0/0 reject
hostssl   x0_warehouse x0_wh_admin           0.0.0.0/0 scram-sha-256
hostssl   x0_warehouse +x0_warehouse_readers 0.0.0.0/0 scram-sha-256
  • password_encryption = scram-sha-256 (no MD5 storage).
  • ssl_min_protocol_version = TLSv1.2.
  • Customers connect with sslmode=require (Excel: Encrypt=Yes / Use SSL).
  • Config: deploy/warehouse/pg_hba.conf; recreate via scripts/run_warehouse_postgres.sh.
  • LE cert for db.x0.co.nz → shim/data/warehouse-certs/; renew: scripts/sync_warehouse_certs.sh.
  • Platform Track A stays on 10.1.41.1:5433 (VPN / localhost only).

Quotas

  • X0_WAREHOUSE_MAX_SIZE_MB soft cap (enforced at materialize time / reported in portal).
  • Refresh cadence follows cache TTL / portal Refresh (same Xero egress economics).
  • Enabled plans: X0_WAREHOUSE_ENABLED_PLANS (default professional).

Xero / commercial risk

  • Bulk SQL of accounting data resembles ELT / migration tooling — the same class of use-case Xero resists for GL Journals approval (xero-quirks.md).
  • Warehouse reads do not increase Xero egress (data is already cached), but App Partner review may still object to redistribution framing.
  • Position as: customer’s books via their OAuth grant; x0 is a cache/query plane; disable if partner review forbids it.
  • Legal/ToS review before marketing Track B.

Capacity / terrace

  • Track B needs disk. Watch Ops → Volume before enabling.
  • Do not run unbounded warehouses on a tiny VM; upgrade when margin covers it (volume-margin.md).

Materializer design (typed schema — phase 1)

Keep Track A CustomerTableCache.payload as OData source of truth. After a successful
refresh_table_cache, optionally upsert rows into warehouse tables.

Platform metadata (Track A)

Table Role
XeroFieldCatalog 650-field origin list (data/xero-fields.json) — types, descriptions, examples
warehouse_relations Planned SQL tables (header + line children)
warehouse_columns Typed columns, PK/FK flags, catalogue path
postgres_indexes Index catalogue for recreate

Seed:

python manage.py seed_xero_fields
python manage.py seed_warehouse_schema
# or: python manage.py seed_all
python manage.py seed_warehouse_schema --ddl-only --print-ddl c_1   # preview SQL

Warehouse DDL (Track B)

Per customer schema c_<id>:

  • Header tables (accounts, invoices, …): typed columns from the field catalogue + x0_tenant_id, x0_fetched_at, x0_raw jsonb
  • Line / child tables (invoices_lines, journals_lines, …): parent FK + line_seq composite PK + line fields
  • Foreign keys: line → header ON DELETE CASCADE
  • Indexes: tenant, PK, FK, Code/Status/Name-style filters
  • _x0_field_catalog: copy of the 650-field list inside the customer schema for BI reference

Phase 1 upsert filled PK + x0_raw. Phase 2 projects catalogue columns from
the JSON payload and writes nested arrays (LineItems, JournalLines, …) into
child tables (invoices_lines, …) with parent FK + line_seq.

Cache table Warehouse table Primary key
accounts accounts account_id
invoices invoices invoice_id
(nested LineItems) invoices_lines (invoice_id, line_seq)
… … …

Implementation: portal/services/warehouse_schema.py,
portal/services/warehouse_materializer.py.

OData does not need to read from the warehouse.

Provision flow (Phase 3)

  1. Customer on Professional (postgresql_read_access) enables SQL in the dashboard (or staff provisions).
  2. If X0_WAREHOUSE_DATABASE_URL unset → “not available on this install.”
  3. Create schema c_<id>, first credential (label=default), materialize tables + apply postgres_indexes.
  4. Customer creates more SQL logins (label, reveal, rotate, revoke) for each BI tool — not portal login.
  5. Disable warehouse drops schema + all roles.

Future: remote customer warehouses (Phase 5)

Customers who outgrow shared db.x0.co.nz can run their own Postgres and still get x0 data:

Mode Source of truth How updates flow
A. Replicate off Track B x0 warehouse schema Logical replication / publication per customer schema → their subscriber
B. Replicate off Track A Platform cache (or change feed) Publish materialize events / CDC into their DB
C. Pull materialize Same as today Their agent calls x0 API / webhook and upserts locally

We trickle updates; they pound their own hardware for dashboards. Not built yet — catalogue indexes + multi-credential auth are prerequisites so remote targets stay consistent.

Docker (Phase 4)

Warehouse on terrace: scripts/run_warehouse_postgres.sh + deploy/warehouse/pg_hba.conf.

Local / x0-dev (no TLS, 127.0.0.1:5435):

# in shim/.env — see .env.example Track B local block
./scripts/run_warehouse_postgres.sh --dev
python manage.py shell -c "from portal.services.warehouse_provision import ensure_warehouse_security; ensure_warehouse_security()"
# Mission Control → Postgres → Start pgAdmin (needs PGADMIN_* + PGADMIN_BIND)

Uses deploy/warehouse/pg_hba.dev.conf. Platform DB stays on x0-dev-postgres :5432.

Compose profiles (platform only):

  • Profile sqlite — no Postgres service; platform SQLite volume.
  • Profile postgres — platform Postgres + app services.

Track B warehouse is a separate container (x0-warehouse-postgres), not the platform DB.

Sign in to see connection status