Canonical commerce model¶

The canonical layer isolates Accountify from source and Odoo schema changes. It stores immutable observed events plus current entity projections. It is not a second legal ledger.

Boundary serialization, event names, compatibility, consumer deduplication, and HTTP exposure follow the API and event contract. Reporting projections follow the warehouse marts contract.

Data conventions¶

IDs are UUIDv7 or another time-sortable UUID generated by Accountify.
Timestamps are timestamptz in UTC. Source-local business dates are stored separately with their IANA timezone.
Money uses numeric(20,6) internally and decimal strings at API boundaries. Binary floating-point is prohibited.
Currency is an ISO 4217 code. Amounts with different currencies are never summed without an explicit foreign-exchange record.
Quantities use numeric(20,6) and units are explicit.
Source payloads are referenced by hash and opaque storage reference rather than duplicated throughout canonical tables.
Unknown source enum values are preserved as source values and map to canonical unknown; they do not default to a business state.

Canonical event envelope¶

create table commerce_events (
  tenant_id uuid not null,
  id uuid not null default gen_random_uuid(),
  source_connection_id uuid not null,
  source_event_id text not null,
  event_type text not null,
  schema_version text not null,
  aggregate_type text not null,
  aggregate_id uuid not null,
  source_object_type text not null,
  source_object_id text not null,
  source_sequence text,
  occurred_at timestamptz not null,
  observed_at timestamptz not null,
  payload_sha256 char(64) not null,
  payload_ref text not null,
  causation_event_id uuid,
  correlation_id text not null,
  created_at timestamptz not null default now(),
  primary key (tenant_id, id),
  unique (tenant_id, source_connection_id, source_event_id),
  foreign key (tenant_id, source_connection_id)
    references source_connections(tenant_id, id),
  foreign key (tenant_id, causation_event_id)
    references commerce_events(tenant_id, id)
);

When a source does not provide a stable event ID, the adapter derives one from (source_object_type, source_object_id, source_updated_at, payload_sha256, adapter_version) and records that it is derived. Event order is not trusted. Projections apply the newest accepted source version under adapter-specific ordering rules and retain conflicting observations for review.

Core entities¶

All tables contain tenant_id, source_connection_id, source_object_id, source_created_at, source_updated_at, adapter_version, payload_sha256, created_at, and updated_at unless the table is Accountify-native.

Entity	Required business content	Natural source uniqueness
`commerce_orders`	order number, channel, customer ref, order currency, prices include tax, business dates	connection + source order ID
`commerce_order_lines`	product/SKU refs, quantity, unit price, discounts, tax, fulfillment quantity	order + source line ID
`commerce_customers`	source customer ref, billing/shipping refs, privacy classification	connection + source customer ID
`commerce_products`	source product/variant/SKU refs, title, unit	connection + source variant ID
`commerce_payments`	transaction/provider ref, type, status, amount, currency, occurred date	connection + transaction ID
`commerce_payment_allocations`	payment-to-order/refund allocation and amount	payment + target + allocation type
`commerce_fulfillments`	location, carrier, shipped quantities, event date	connection + fulfillment ID
`commerce_refunds`	refund transaction, refunded amounts, reason, source line refs	connection + refund ID
`commerce_returns`	authorization/receipt status and physical quantities	connection + return ID
`commerce_disputes`	disputed amount, stage, reason, evidence deadlines	connection + dispute ID
`commerce_tax_lines`	jurisdiction/source tax name, rate, base, amount, inclusive flag	parent + source tax-line ID
`commerce_adjustment_lines`	discount, shipping, fee, reserve, withholding, rounding, other	parent + source adjustment ID
`commerce_settlements`	settlement period, account, gross/net totals, currency	connection + settlement ID
`commerce_settlement_lines`	typed financial line linked to source objects when possible	settlement + source line ID
`commerce_payouts`	payout amount/currency/status/date/bank reference	connection + payout ID

Order projection¶

create table commerce_orders (
  tenant_id uuid not null,
  id uuid not null default gen_random_uuid(),
  source_connection_id uuid not null,
  source_object_id text not null,
  source_order_number text not null,
  order_currency_code char(3) not null,
  presentment_currency_code char(3),
  order_date date not null,
  order_timezone text not null,
  prices_include_tax boolean not null,
  subtotal_amount numeric(20,6) not null,
  discount_amount numeric(20,6) not null,
  shipping_amount numeric(20,6) not null,
  tax_amount numeric(20,6) not null,
  total_amount numeric(20,6) not null,
  commercial_state text not null,
  payment_state text not null,
  fulfillment_state text not null,
  accounting_state text not null,
  reconciliation_state text not null,
  policy_version text,
  adapter_version text not null,
  payload_sha256 char(64) not null,
  source_created_at timestamptz not null,
  source_updated_at timestamptz not null,
  resource_version bigint not null default 1,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  primary key (tenant_id, id),
  unique (tenant_id, source_connection_id, source_object_id),
  check (subtotal_amount - discount_amount + shipping_amount + tax_amount = total_amount)
);

Source-specific adjustments that do not fit the control equation are explicit adjustment rows and must be incorporated by a versioned adapter equation. They are never hidden in total_amount.

Independent state machines¶

One combined order lifecycle is prohibited. Each aggregate advances independently.

Commercial order¶

open -> partially_cancelled -> closed
open -> cancelled

Payment¶

pending -> authorized -> partially_captured -> captured
captured -> partially_refunded -> refunded
captured -> disputed -> dispute_won | dispute_lost

Fulfillment¶

unfulfilled -> partially_fulfilled -> fulfilled
partially_fulfilled | fulfilled -> partially_returned -> returned

Accounting¶

not_ready -> ready -> posting -> posted
ready | posting -> blocked
posted -> partially_reversed -> reversed
posting -> verification_required -> posted | repair_required

Settlement reconciliation¶

unmatched -> partially_allocated -> allocated -> payout_matched -> bank_matched -> closed
any non-closed state -> exception

Transitions are append-only events with actor, reason, old state, new state, policy version, correlation ID, and evidence. Projection rows use optimistic concurrency through resource_version.

Identity and matching¶

Matching priorities are deterministic and versioned:

Exact source/provider object ID already mapped.
Exact provider transaction or payout reference within the same connection and currency.
Explicit source relationship from the payload.
Approved composite match under a named rule version.
Manual review.

Email, customer name, amount alone, or fuzzy text alone cannot establish financial identity. A manual match stores the reviewer, evidence, reason, and replaced suggestion.

Canonicalization contract¶

For each accepted inbox record the canonicalizer:

Loads the immutable source payload and verified connection context.
Validates the adapter input schema.
Produces canonical events with deterministic source event IDs.
Verifies currency, signs, quantity, tax, and order control totals.
Upserts current projections using source ordering and optimistic concurrency.
Writes transformation lineage from input payload hash to every output event.
Commits projection changes and an outbox event in one transaction.

The same adapter version and input payload must produce byte-equivalent normalized values after canonical JSON ordering. Golden fixtures enforce this property.

Corrections and deletion¶

Source corrections create new events and update projections; previous evidence remains immutable.
Deletion signals create a tombstone event. They do not delete confirmed accounting evidence or Odoo mappings.
Recanonicalization under a new adapter version writes a new derivation and a diff. Confirmed posting intents are not changed in place.
A financial difference after posting creates a reversal or adjustment intent under the accounting policy; it never rewrites a confirmed operation.