Warehouse marts and reporting contract

Warehouse marts provide rebuildable operational and analytical views. They do not own workflow state, approvals, mappings, policy, source evidence, or legal accounting state. Dashboards query marts and projections; they do not synchronously fan out to Shopify or client Odoo databases.

The post-Odoo implementation artifacts provide the executable pilot reference for confirmed readback ingestion, warehouse facts, initial marts, quality controls, reporting OpenAPI, and stale-data monitoring. This page remains authoritative for the complete warehouse product.

Layer contract

flowchart LR Raw[Raw source evidence] Stage[Source staging] Core[Canonical commerce] Odoo[Odoo readback] Recon[Settlement reconciliation] Dims[Conformed dimensions] Facts[Atomic facts] Ops[Operational marts] BI[Analytical marts] API[Reporting API] Raw --> Stage --> Core Core --> Dims Core --> Facts Odoo --> Facts Recon --> Facts Dims --> Ops Facts --> Ops Dims --> BI Facts --> BI Ops --> API BI --> API

Layer	Purpose	Mutability
raw	Encrypted source evidence and ingestion metadata	Append-only plus governed deletion/restriction
stg_*	Source-specific typing, flattening, signs, timestamps, IDs	Rebuildable per adapter version
core_*	Canonical entities/events and transactional projections	Durable operational state under domain contracts
dim_*	Conformed descriptive entities	Type-1 by default; Type-2 only where historical reporting requires it
fct_*	Atomic, additive or explicitly semi-additive facts	Incremental and rebuildable from core/readback/reconciliation
mart_ops_*	Current queues, health, exceptions, freshness	Frequent incremental refresh
mart_finance_*	Governed sales, refunds, fees, settlements, margins	Partitioned refresh and close controls

Raw and staging schemas are not exposed to standard dashboard users.

Universal mart columns

Every tenant-owned dimension, fact, and mart row includes:

tenant_id
source_connection_id where applicable
odoo_connection_id and company_id where applicable
business_date
source_timezone
currency_code for monetary facts
source_updated_at
warehouse_loaded_at
warehouse_batch_id
contract_version
adapter_version
source_hash or lineage_key

Odoo, source, and canonical IDs remain scoped composite references. A numeric Odoo record ID or source object ID is never a warehouse-wide key.

Conformed dimensions

Dimension	Grain and key	Required attributes
dim_tenant	one tenant version	tenant status, reporting timezone, base currency; no secrets
dim_date	one calendar date	day/week/month/quarter/fiscal labels approved for reporting
dim_channel	tenant + source connection + channel/market	platform, shop/account, market, active status
dim_company	tenant + Odoo connection + company ID	company name, base currency, country
dim_product	tenant + canonical product ID	source variant/SKU, mapped Odoo product, category; no unrestricted source payload
dim_customer_reporting	tenant + reporting customer token	pseudonymous token, country/region, B2B/B2C flags; no name/email/address
dim_currency	ISO 4217 code + effective precision metadata	code, minor units, active period
dim_mapping_version	tenant + mapping set version	status, effective range, approval timestamp
dim_policy_version	tenant + posting policy version	profile, effective range, approval timestamp
dim_operation_status	status/error code	terminal/retryable/manual-review classification

Changes to company, product, mapping, or policy history use Type-2 rows only when reports must reproduce the attributes effective at the fact date. The fact always retains the exact policy/mapping version used.

Atomic fact contracts

Fact	Grain	Required unique key	Additivity
fct_order_line	canonical order line at current accepted source version	tenant + canonical order-line ID	Amounts additive by currency; quantities additive by unit
fct_payment_transaction	canonical payment transaction	tenant + canonical payment ID	Additive with signed transaction type
fct_fulfillment_line	fulfillment + order line	tenant + fulfillment ID + order-line ID	Quantity additive by unit
fct_refund_line	refund + source/canonical line or explicit adjustment	tenant + refund ID + line/adjustment ID	Signed amounts additive by currency
fct_return_line	return event + fulfilled line + disposition	tenant + return ID + line ID + event version	Quantity additive by disposition/unit
fct_dispute_event	dispute lifecycle event	tenant + dispute ID + event ID	Amount semi-additive; use latest state for exposure
fct_settlement_line	normalized provider settlement line	tenant + source connection + source settlement-line ID	Signed amount additive by currency/type
fct_payout	provider payout	tenant + canonical payout ID	Amount additive by currency/date
fct_odoo_document	Odoo document readback version	tenant + connection + company + model + record ID + readback version	Latest state for balances; document amount additive only at one version
fct_posting_operation	integration operation	tenant + operation ID	Counts/durations additive; monetary payload excluded
fct_inventory_snapshot	product + location + observation timestamp	tenant + product + location + observed_at	Semi-additive across time; use last observation

Facts store canonical signed amounts. Refund, fee, chargeback, withholding, and discount signs follow the settlement control equation; dashboards do not reverse signs independently.

Governed marts

Operational marts

Mart	Grain	Purpose	Refresh target
mart_ops_tenant_health	tenant + source/Odoo connection	Last successful sync/readback, capability/drift, SLO status	5 minutes
mart_ops_mapping_exceptions	open mapping exception	Review queue, age, owner, affected workflow	5 minutes
mart_ops_posting_health	tenant + hour + operation type/status	Queue, success, unknown, retry, repair metrics	5 minutes
mart_ops_settlement_exceptions	open settlement/payout exception	Imbalance and bank/clearing review queue	5 minutes
mart_ops_inventory_differences	open inventory reconciliation case	Source/Odoo quantity differences and age	15 minutes

Finance and commerce marts

Mart	Grain	Primary measures	Refresh target
mart_finance_sales_daily	tenant + date + channel + company + currency	gross sales, discounts, shipping, tax, net order value	Hourly
mart_finance_refunds_daily	tenant + date + channel + company + currency	refund amount, credit-note amount, unreconciled refund amount	Hourly
mart_finance_fees_daily	tenant + date + provider + fee type + currency	provider/marketplace fees, fee tax, unmapped fees	After settlement ingest
mart_finance_settlements	tenant + settlement + currency	control-equation components, expected payout, difference, close state	After each settlement update
mart_finance_payouts	tenant + payout + currency	expected/provider/bank/Odoo amounts and match state	After each payout/readback update
mart_finance_clearing	tenant + provider + company + currency + date	source/provider/Odoo clearing movement and residual	Hourly plus close refresh
mart_finance_margin_daily	tenant + date + channel + product + currency	net sales, refunds, fees, Odoo COGS where available, gross margin	Daily; only after valuation evidence
mart_commerce_orders	canonical order	commercial/payment/fulfillment/accounting/reconciliation states	15 minutes
mart_commerce_inventory_daily	tenant + date + product + location	source/Odoo on-hand, available, in-transit, difference	Daily plus exception refresh

No mart named gross_margin may publish a value when COGS or valuation evidence is missing. It exposes metric_status = incomplete and the missing components.

Metric dictionary

Metrics are versioned. Every API response includes metric_contract_version.

Metric	Definition
gross_merchandise_value	Source line gross before merchant-funded discounts; excludes tax unless the tenant metric policy explicitly defines tax-inclusive GMV
discounts	Merchant-funded discount allocations as a positive reporting magnitude
net_sales_before_returns	Gross merchandise value minus merchant-funded discounts
shipping_revenue	Customer shipping amount excluding shipping tax
tax_collected	Source tax lines under the approved tax mapping; not revenue
refunds	Absolute value of confirmed source refund principal allocated to sales/shipping/tax components
net_revenue	Net sales before returns plus shipping revenue minus sales/shipping refund components; tax excluded
provider_fees	Confirmed settlement fee lines excluding recoverable fee tax where policy separates it
expected_payout	Signed sum of settlement components under the settlement control equation
payout_difference	Provider payout amount minus expected payout in the same currency
bank_difference	Matched bank amount minus provider payout amount in the same currency
odoo_clearing_residual	Authoritative Odoo provider-clearing residual after confirmed reconciliation readback
posting_success_rate	Confirmed operations divided by terminal operations for the selected operation cohort; queued work excluded
posting_latency_seconds	Confirmed readback timestamp minus durable operation acceptance timestamp
gross_margin	Net revenue minus confirmed Odoo COGS minus approved directly attributable fees; only when all components are complete

Reports never aggregate across currency without an explicit requested reporting currency, approved FX source, rate date, and separately exposed translation difference.

Incremental refresh

Each model stores a high watermark and overlap window:

create table mart_refresh_state (
  tenant_id uuid not null,
  mart_name text not null,
  partition_key text not null,
  source_high_watermark timestamptz,
  source_generation bigint not null default 0,
  last_batch_id uuid,
  last_success_at timestamptz,
  row_count bigint,
  control_hash char(64),
  status text not null check (status in ('idle','running','failed','paused','stale')),
  primary key (tenant_id, mart_name, partition_key)
);

Refresh algorithm:

1. Claim a fenced lease for tenant, mart, and partition.
2. Freeze source high watermarks for canonical, Odoo readback, and reconciliation inputs.
3. Reprocess the configured overlap and every partition affected by corrections or late events.
4. Build into a shadow relation or transactionally isolated partition.
5. Run uniqueness, relationship, accepted-value, not-null, currency, and control-total tests.
6. Compare source/fact/mart counts and amounts by tenant/date/currency.
7. Publish the partition and watermark atomically.
8. Emit warehouse.mart.refreshed.v1 with batch ID, counts, controls, and lineage.

A failed quality gate retains the previously published partition, marks the mart stale, and opens an incident/work item. It never publishes partial results.

Reconciliation gates

Gate	Required equality/control
Orders	Canonical order line/control totals equal fct_order_line by tenant/date/currency
Refunds	Canonical refund allocations equal refund facts and do not exceed approved cumulative bounds
Settlements	Signed settlement facts equal provider settlement total and expected payout equation
Payouts	Expected, provider, bank, and Odoo amounts expose every difference explicitly
Posting	Every confirmed operation has one matching confirmed readback fact
Inventory	Snapshot facts expose source/Odoo observation times and timing adjustments
Tenant isolation	Every fact/dimension relationship joins on tenant and negative cross-tenant tests return zero rows

Control differences are never hidden by a dashboard filter or default tolerance.

Reporting API

GET /api/v1/tenants/{tenant_id}/reports/sales
GET /api/v1/tenants/{tenant_id}/reports/refunds
GET /api/v1/tenants/{tenant_id}/reports/fees
GET /api/v1/tenants/{tenant_id}/reports/settlements
GET /api/v1/tenants/{tenant_id}/reports/posting-health
GET /api/v1/tenants/{tenant_id}/reports/inventory
GET /api/v1/tenants/{tenant_id}/reports/data-freshness

Required query dimensions are allowlisted: date range, channel, company, currency, product/category, provider, operation type/status, and exception status. The server resolves tenant scope and never accepts raw SQL, table names, arbitrary dimensions, or cross-tenant identifiers.

Responses include:

contract_version
metric_contract_version
tenant_id
filters and reporting timezone
data_freshness_at
warehouse_batch_ids
is_complete
incomplete_reasons
currency/FX policy
rows or series

Financial exports also include immutable export ID, requester, purpose, query hash, generated timestamp, row count, and content hash.

Isolation, privacy, and retention

• Warehouse runtime roles use forced RLS or physically isolated authorized-serving views; table owners are not runtime roles.
• Every join between tenant-owned models includes tenant_id.
• Staff portfolio marts are separately authorized projections and cannot bypass operational RLS.
• Customer reporting dimensions are pseudonymous and exclude name, email, street address, phone, and raw payment data.
• Row-level drill-through requires the same tenant/role authorization as the underlying operational resource.
• Mart retention, deletion, subject restrictions, exports, and backups follow the data governance contract.

Performance and scale gates

Pilot targets:

• p95 dashboard query under two seconds for a 90-day tenant view;
• p95 export acceptance under two seconds with asynchronous generation;
• 95% of operational marts within their refresh target;
• no dashboard request triggers source or Odoo network calls;
• partition rebuild can replay one tenant-year without blocking other tenants.

Move beyond PostgreSQL only when measured storage, scan cost, concurrent query, refresh contention, retention, restore, or SLO evidence justifies migration. Client count alone is not a trigger.

Acceptance criteria

• Every fact and mart has documented grain, unique key, owner, freshness target, lineage, and metric version.
• Duplicate source delivery and mart replay produce identical published controls.
• Late refund, settlement correction, Odoo readback correction, and mapping/policy version changes rebuild affected partitions without double counting.
• Cross-tenant joins, cache keys, exports, and drill-through fail negative isolation tests.
• Source-to-fact-to-mart control totals reconcile by tenant, date, and currency.
• Stale/failed marts remain visible as stale and do not publish partial data.
• Metric fixtures prove tax, discounts, shipping, refunds, fees, payouts, FX exclusion, and incomplete-margin behavior.