0pt/docs/03-data-model.md

03 · Data model

Postgres is the backbone. Two halves: data entities (what we sell) and platform tables (orders, users, jobs). Every fact links to bridge_provenance.

Migrations are plain SQL files in sql/, applied in filename order. There is no Alembic (or any other Python migration tool) anywhere in this repo, and no src/workers/db.py — that file does not exist (the Python engine's workers live in services/api/workers/ — adaptive_router.py, pipeline.py, queue.py, resolve.py, run.py — none of them named db.py, and there is no TypeScript src/workers/ directory either; src/ only has adapters/, clients/, services/, types/). infra/docker-compose.yml mounts ../sql as /docker-entrypoint-initdb.d on the postgres service, so Postgres auto-applies every .sql file in that directory, in name order, the first time that container initializes its data volume.

Only two migration files exist in this repo, ever: sql/001_init.sql and sql/004_iceberg_write_boundary.sql (verified via find sql/ — no 002 or 003). Three other docs in this repo (docs/ICEBERG_WRITE_BOUNDARY.md, docs/11-lakehouse-write-plan.md, docs/P0_QUERY_SOURCE_FLOW.md) mention sql/002_p0_dataset_quality.sql and sql/003_*.sql by name — they don't exist, on any branch, ever (git history checked) — but all three have been corrected to say exactly that (each carries its own "never existed" callout, none claims the files are real as of this pass). This file only asserts what's true of sql/001 and sql/004, which are real.

Conventions

• snake_case, UTC timestamptz, soft-delete via deleted_at where useful.
• Natural keys for registries (org_nr), surrogate uuid PKs for platform tables.
• created_at / updated_at on every table.
• All monetary amounts in öre (integer) to avoid float errors; currency SEK default.

Data entities

Mirrors sql/001_init.sql (verified against the file this pass; the migration additionally wraps every statement in CREATE TABLE IF NOT EXISTS for idempotent re-runs, and enables pgcrypto alongside vector — needed for gen_random_uuid()):

-- A company (the core registry entity)
CREATE TABLE dim_company (
  org_nr        text PRIMARY KEY,            -- national reg. number (per market format)
  market        text NOT NULL,               -- ISO country code: SE/NO/DK/FI/...
  lei           text,                         -- GLEIF cross-border key
  name          text NOT NULL,
  status        text,                         -- active/dissolved/...
  nace          text,                         -- NACE/SNI code
  registered_at date,
  address       jsonb,
  website       text,
  updated_at    timestamptz NOT NULL DEFAULT now(),
  created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE fact_financials (
  org_nr      text REFERENCES dim_company(org_nr),
  fiscal_year int  NOT NULL,
  revenue     bigint,                          -- öre
  result      bigint,
  employees   int,
  source_id   text REFERENCES dim_source(source_id),
  PRIMARY KEY (org_nr, fiscal_year)
);

CREATE TABLE dim_contact (
  contact_id  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  org_nr      text REFERENCES dim_company(org_nr),
  name        text,
  role        text,
  email       text,
  phone       text,
  linkedin    text,
  -- PII: subject to GDPR; lawful basis recorded per delivery
  updated_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE dim_ubo (                          -- ultimate beneficial owner
  ubo_id   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  org_nr   text REFERENCES dim_company(org_nr),
  owner    text,
  share    numeric(5,2)
);

CREATE TABLE dim_vehicle (                       -- VehIQ synergy
  vin         text PRIMARY KEY,
  market      text NOT NULL,
  reg_plate   text,
  make        text, model text, year int,
  owner_org   text REFERENCES dim_company(org_nr),
  specs       jsonb,
  updated_at  timestamptz NOT NULL DEFAULT now()
);

Provenance model (the signature — every fact links here)

CREATE TABLE dim_source (
  source_id   text PRIMARY KEY,                 -- 'gleif','brreg','companies_house',...
  name        text NOT NULL,
  market      text,                              -- or 'global'
  access      text,                              -- 'api'|'bulk'|'authorized_web'|'licensed'
  licence     text,                              -- short licence note
  cadence     text,                              -- 'daily'|'weekly'|'annual'|...
  url         text
);

-- One row per (entity field) we deliver, linking value → where it came from + confidence
CREATE TABLE bridge_provenance (
  field_id    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  entity_type text NOT NULL,                     -- 'company'|'contact'|'vehicle'|'financials'
  entity_key  text NOT NULL,                     -- org_nr / vin / contact_id
  field_name  text NOT NULL,                     -- 'revenue','email',...
  source_id   text REFERENCES dim_source(source_id),
  freshness   date,                              -- when this value was confirmed
  confidence  numeric(4,3) NOT NULL,             -- 0.000–1.000
  level       smallint NOT NULL,                 -- 0 unavailable/licensed, 1 single, 2 partial, 3 corroborated
  agreed_sources int DEFAULT 1,                  -- # independent sources that agree
  UNIQUE (entity_type, entity_key, field_name)
);
CREATE INDEX ON bridge_provenance (entity_type, entity_key);

Rule: no value ships without a bridge_provenance row. Level 0 = delivered as "not available / licensed", never a guessed value.

dim_source is a different, older entity than the per-tenant Source registry described below (src/types/source.ts) that 0pt.query_source acts against — same name-ish concept, two separate schemas. src/types/source.ts's own doc comment calls this out, but its "~9-row list of national registries served today by GET /api/sources" phrasing is easy to misread: dim_source itself, as a Postgres table, has zero rows — sql/001_init.sql only creates the table, there is no seed/INSERT statement anywhere in this repo's SQL or Python (grep-verified for INSERT INTO dim_source). The actual 9-entry national-registry list that comment is pointing at lives in a third, distinct artifact: the edge-native GET /api/sources route (app/api/sources/route.ts), a hardcoded array with a confidence field. That's different again from the FastAPI engine's sources.yaml (8 entries, no confidence field — see docs/04-api.md's Sources section). So there are three separate "source list" artifacts in this repo — the empty dim_source SQL table, sources.yaml (8), and app/api/sources/route.ts (9) — none of which currently share data with each other; see "TypeScript-side P0 entities" below for the fourth, per-tenant Source type.

Platform tables

Mirrors sql/001_init.sql (verified this pass — same tables, same columns):

CREATE TABLE orgs (
  org_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL, created_at timestamptz DEFAULT now()
);
CREATE TABLE users (
  user_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id uuid REFERENCES orgs(org_id),
  email text UNIQUE NOT NULL,
  role text NOT NULL DEFAULT 'member',           -- 'owner'|'admin'|'member'|'siax_admin'
  created_at timestamptz DEFAULT now()
);

CREATE TABLE orders (
  order_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id uuid REFERENCES orgs(org_id),
  flow text NOT NULL,                             -- 'catalog'|'custom'|'subscription'
  status text NOT NULL DEFAULT 'draft',           -- see lifecycle in docs/01
  config jsonb NOT NULL,                          -- configurator state (markets, fields, ...)
  total_ore bigint, deposit_ore bigint, balance_ore bigint,
  est_days int, est_confidence numeric(4,3),
  created_at timestamptz DEFAULT now(), updated_at timestamptz DEFAULT now()
);

CREATE TABLE order_items (                         -- resolved line items / dataset refs
  item_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id uuid REFERENCES orders(order_id),
  kind text, ref text, qty int, meta jsonb
);

CREATE TABLE jobs (                                -- one production run per order
  job_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id uuid REFERENCES orders(order_id),
  stage text,                                      -- sourcing|extraction|structuring|enrichment|qa
  pct numeric(5,2) DEFAULT 0,
  state text DEFAULT 'queued',                     -- queued|running|done|failed
  metrics jsonb,                                   -- rows, confidence, per-market progress
  started_at timestamptz, finished_at timestamptz
);

CREATE TABLE deliveries (
  delivery_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id uuid REFERENCES orders(order_id),
  formats text[], destinations text[],
  record_count int, avg_confidence numeric(4,3),
  quality_report jsonb, provenance_export_url text,
  files jsonb,                                     -- [{format,url,bytes}]
  created_at timestamptz DEFAULT now()
);

CREATE TABLE subscriptions (
  sub_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id uuid REFERENCES orders(order_id),
  cadence text,                                    -- weekly|daily
  webhook_url text, status text DEFAULT 'active',
  next_run_at timestamptz
);

CREATE TABLE files (                               -- customer uploads
  file_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id uuid REFERENCES orgs(org_id),
  order_id uuid REFERENCES orders(order_id),
  name text, mime text, bytes bigint, r2_key text,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE db_grants (                           -- customer read-only DB access (encrypted)
  grant_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id uuid REFERENCES orgs(org_id),
  order_id uuid REFERENCES orders(order_id),
  conn_encrypted bytea NOT NULL,                   -- encrypted connection string
  scope text, expires_at timestamptz, revoked_at timestamptz,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE audit_log (
  id bigserial PRIMARY KEY,
  actor text, action text, target text, meta jsonb,
  at timestamptz DEFAULT now()
);

CREATE TABLE payments (
  payment_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id uuid REFERENCES orders(order_id),
  kind text,                                       -- 'deposit'|'balance'
  amount_ore bigint, stripe_ref text,
  status text,                                     -- 'pending'|'captured'|'released'|'failed'
  created_at timestamptz DEFAULT now()
);

Gap, verified this pass: sql/001_init.sql does not contain order_items. It is documented here (and was already documented before this pass) as part of the target platform-table set, but there is no CREATE TABLE order_items in the migration file — check before relying on it existing in any real database.

Gap, verified this pass: this schema is real and (if infra/docker-compose.yml's postgres service is actually run) gets created — but the FastAPI engine's own code never connects to it. services/api/orchestrator/main.py has no asyncpg/psycopg/sqlalchemy import anywhere (grep-verified across services/api/), and its orders/jobs state lives entirely in two module-level Python dicts (_ORDERS, _JOBS) that reset on every process restart and are never written to Postgres. The one DATABASE_URL reference in main.py (GET /health) only reports whether the env var is set — it never opens a connection. See docs/04-api.md's implementation-status section for the full picture. Until that wiring lands, this table set is schema-only, not a live system of record.

P0 registry tables (sql/004_iceberg_write_boundary.sql)

A second, newer migration adds two tables for the lakehouse write-path and crawl-compliance capabilities (0pt.write_iceberg_table, 0pt.source_rate_policy_manage — see the new edge-API doc, docs/12-edge-capability-api.md). Verified against the file this pass:

CREATE TABLE opt_dataset_iceberg_mappings (
  dataset_iceberg_mapping_id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  dataset_id TEXT NOT NULL REFERENCES opt_datasets (dataset_id),
  iceberg_table_id TEXT NOT NULL,        -- reference into ST0RE's table registry, NOT a foreign key
  namespace TEXT,
  table_name TEXT,
  mapping_status TEXT NOT NULL DEFAULT 'active',
  mapped_by TEXT,
  correlation_id TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ
);

CREATE TABLE opt_source_rate_policies (
  source_rate_policy_id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  source_id TEXT NOT NULL REFERENCES opt_sources (source_id),
  rate_limit_per_minute NUMERIC NOT NULL,
  crawl_budget NUMERIC NOT NULL,
  compliance_notes TEXT,
  domain TEXT,
  robots_ok BOOLEAN,
  access TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  correlation_id TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ,
  created_by TEXT
);

iceberg_table_id is deliberately not a foreign key — it references ST0RE's own st0re_iceberg_tables.table_id, a different service's database. 0pt holds only the reference, never a copy of ST0RE's table/catalog/snapshot state (see the module doc comment on src/clients/st0re-lakehouse-client.ts, and docs/ICEBERG_WRITE_BOUNDARY.md for the full ownership boundary — that doc is outside this file's scope to otherwise correct).

Confirmed bug, found this pass — flagging plainly, not silently fixing it: both CREATE TABLE statements above have a REFERENCES clause pointing at tables that do not exist anywhere in this repo's SQL: opt_dataset_iceberg_mappings.dataset_id REFERENCES opt_datasets (dataset_id) and opt_source_rate_policies.source_id REFERENCES opt_sources (source_id). Neither opt_datasets nor opt_sources is created by sql/001_init.sql, sql/004_iceberg_write_boundary.sql, or any other file in sql/ (grep-verified — there are only two migration files, full stop). If sql/004_iceberg_write_boundary.sql is ever actually run against a fresh database (e.g. via infra/docker-compose.yml's docker-entrypoint-initdb.d mount), it will fail with relation "opt_datasets" does not exist. This is not a naming coincidence: src/types/dataset.ts and src/types/source.ts define exactly the Dataset/Source shapes those table names imply, and DatasetService/SourceService (src/services/) are their in-memory-only implementations — but no migration ever creates the SQL tables to back them. Open question for Simon: either author a migration that creates opt_datasets/opt_sources before sql/004 (so the FK resolves), or drop those two REFERENCES clauses and enforce the dataset/source-exists check purely in the TypeScript service layer (which already does the check today, redundantly, in DatasetIcebergMappingService.createMapping and SourceRatePolicyService.createSourceRatePolicy) — this file does not choose between those for you.

TypeScript-side P0 entities without SQL backing

Newer P0 capabilities (docs/12-edge-capability-api.md) introduced their own typed entities under src/types/, each with an in-memory-only service under src/services/. Only two of these (above) have a real migration. The rest have no SQL table at all — every instance of the owning service starts with an empty in-memory Map and loses all state on restart (or even between two new XService() calls in the same process — see docs/12-edge-capability-api.md's "in-memory isolation" findings for exactly how badly this bites in practice today):

Type	File	Service (in-memory only)	SQL table
Source	src/types/source.ts	src/services/source-service.ts	none
Dataset	src/types/dataset.ts	src/services/dataset-service.ts	none
DocumentRecord	src/types/document.ts	src/services/document-capture-service.ts	none
DocumentExtractionRecord	src/types/document-extraction.ts	src/services/document-extraction-service.ts	none
ExternalWriteRecord	src/types/external-writeback.ts	src/services/external-writeback-service.ts	none
DatasetIcebergMapping	src/types/dataset-iceberg-mapping.ts	src/services/dataset-iceberg-mapping-service.ts	opt_dataset_iceberg_mappings (sql/004)
SourceRatePolicy	src/types/source-rate-policy.ts	src/services/source-rate-policy-service.ts	opt_source_rate_policies (sql/004)

pgvector

CREATE EXTENSION vector; — this is real (sql/001_init.sql line 3, along with pgcrypto). Everything past the extension is target only: there is no embeddings table, or any other table using the vector type, anywhere in sql/001_init.sql or sql/004_iceberg_write_boundary.sql. The intended use (entity matching/dedup, AI-assist semantic match of NL request → catalog/config) is a real design goal, not yet a real table.

Row-level security & catalog

Target only — none of this exists in sql/ today, verified by grep for ROW LEVEL SECURITY, CREATE POLICY, and catalog across both migration files (no matches beyond a comment). There is no catalog table or view, and no Postgres RLS policy anywhere in this repo's SQL. Per-customer isolation is intended to be org_id filtering in the API plus RLS where practical — but as of this pass the FastAPI engine (services/api/orchestrator/main.py) has no org_id concept in any request or response at all (see docs/04-api.md's implementation-status section: GET /orders returns every order in the process, unfiltered). This is the same platform-wide "no auth/session system exists yet" gap noted across this doc corpus — not a data-model-specific problem to solve here, but the schema above cannot be considered tenant-isolated until it is.