Schema Design

The data architecture spans three layers, each with its own storage and query characteristics. The commerce schema is the source of truth for the domain layer. Analytics and measurement data live on R2 as Apache Iceberg tables. Pipeline orchestration is handled by Cloudflare Workflows — no pipeline state tables in PG.

Architecture overview

┌─────────────────────────────────────────────────────────────┐
│ Layer 1 — Domain (PlanetScale PostgreSQL)                    │
│ 153 tables across 25 modules                                 │
│ Transactional OLTP: orders, products, payments, publishers   │
│ Source of truth for all domain entities                       │
└──────────────────────┬──────────────────────────────────────┘
                       │ events via Worker enrichment
                       ▼
┌─────────────────────────────────────────────────────────────┐
│ Layer 2 — Analytics (Cloudflare Pipelines → R2 Data Catalog) │
│ Three telemetry streams → Iceberg/Parquet on R2              │
│ Sales measurement, performance metrics, classification       │
│ Long-term retention, no sampling                             │
└──────────────────────┬──────────────────────────────────────┘
                       │ Iceberg REST catalog
                       ▼
┌─────────────────────────────────────────────────────────────┐
│ Layer 3 — Query (DuckDB over R2)                             │
│ Sliding windows, aggregations, nominal classifications       │
│ Deployment: WASM in Workers / CF Container / MotherDuck      │
└─────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────┐
│ Real-time — Workers Analytics Engine                         │
│ High-frequency counters: clicks, impressions, page views     │
│ 3-month retention, auto-sampling, Grafana integration        │
└─────────────────────────────────────────────────────────────┘

See ADR-006 through ADR-009 for the rationale behind this layering.

Layer 1 — Domain (PlanetScale)

The commerce schema defines 153 tables across 25 modules, organized into three passes:

Pass	Modules	Tables	Focus
Commerce Core	01–13, 15	77	Products, orders, payments, fulfillment, promotions, regions
Creator Commerce	16–25	52	Publishers, ad servers, memberships, ledger, invoices, disputes, leads, identity, governance
Platform	24–25	7	Identity + Governance
Links	14	24	Cross-module join tables

Full API specifications for all 153 tables are available in the API Reference.

Key design choices:

• TEXT primary keys with prefixed UIDs (ord_xxx, pubi_xxx, mda_xxx)
• K8s-style resource model: status JSONB (phase + conditions), labels JSONB, annotations JSONB
• _ts suffix on all timestamps (create_ts, update_ts, provision_timer_ts)
• NUMERIC + raw_* JSONB for monetary amounts (FIBO MonetaryAmount pattern)
• Explicit link tables, no polymorphic associations
• CHECK constraints on all enum-like columns
• No stored procedures, no materialized views — all logic in application code (ADR-003)
• Foreign keys documented but not enforced (ADR-004)

Key domain modules

Module	Tables	What it covers
02 — Product	10	Catalog hierarchy: products, variants, options, categories, tags, images
06 — Order	12	order_header + line items, shipping, summaries, changes, transactions
09 — Fulfillment	10	Providers, sets, zones, options, fulfillments, items, labels
16 — Publisher & Ad Server	15	Publishers, ad servers, publications, telemetry, referrals, conversions, commissions
23 — Macrodata	2	macrodata_catalog + macrodata_artifact — k8s-style artifact registry

Layer 2 — Analytics (Pipelines → R2)

Commerce events are streamed from Workers to Cloudflare Pipelines, which applies lightweight SQL transforms and lands data as Apache Iceberg tables on R2 Data Catalog.

Telemetry streams

Stream	Events	Source
COMMERCE_TELEMETRY	order_created, order_completed, payment_captured, fulfillment_shipped, return_requested	Workers, after PG write
PUBLICATION_TELEMETRY	click, impression, view, engagement, conversion	Platform worker
SYNC_TELEMETRY	procedure completions, record counts, watermark positions	CF Workflows

Workers perform PG reads/writes (domain operations, enrichment) before pushing enriched events to streams (ADR-007). Events arrive on R2 already denormalized — analytical queries don’t need to join back to PG.

Analytics tables on R2

These tables exist as Iceberg tables on R2, not as PG tables. They are queried by DuckDB (or R2 SQL when it matures).

Iceberg table	Ontological type	What it holds
commerce.telemetry	Process Boundary (BFO)	Commerce lifecycle events, partitioned by day
publication.telemetry	Process Boundary (BFO)	Engagement events, partitioned by day
sync.telemetry	Process Record (IAO)	Pipeline execution audit trail
measurement.sales	Measurement Datum (IAO)	Daily sales aggregates by product × day (ratio scale)
measurement.performance	Dataset (IAO)	Aggregated metrics with sliding windows + nominal classification
configuration.objective_spec	Objective Specification (IAO)	Classification thresholds (macrodata_artifact with kind classification_spec)

The measurement.sales and measurement.performance tables replace the former PG tables sales_measurement_dataset and performance_measurement_dataset. Classification thresholds (objective_specification) are stored as a macrodata_artifact in PG and replicated to R2 for query co-location.

Pipeline orchestration

Per ADR-009, pipeline orchestration state (watermarks, run history, step tracking) is handled by Cloudflare Workflows — not PG tables. The former procedure_execution_state and procedure_execution_record tables are replaced by:

• Workflow execution state — managed by CF Workflows (automatic retries, replay, step tracking)
• Watermark/cursor state — stored in Durable Object persistent storage
• Execution audit trail — captured by the SYNC_TELEMETRY stream and landed on R2 for long-term retention

Migration from old schema

The original 18-table schema has been absorbed into the commerce schema and analytics layer:

Old table	New home	Layer
procedure_execution_state	CF Workflows / Durable Objects	Runtime
procedure_execution_record	CF Workflows + sync.telemetry on R2	Runtime + Analytics
commercial_transaction_record	order_header (Module 06)	Domain
transaction_participation_record	order_line_item (Module 06)	Domain
material_artifact	product (Module 02)	Domain
sales_measurement_dataset	measurement.sales on R2	Analytics
performance_measurement_dataset	measurement.performance on R2	Analytics
objective_specification	macrodata_artifact (kind: classification_spec)	Domain + Analytics
creative_act_report	macrodata_artifact (kind: combo_report)	Domain
suggestion_content_entity	macrodata_artifact (kind: combo_suggestion)	Domain
plan_specification	macrodata_artifact (kind: combo_template)	Domain
tag_content_entity	product_tag (Module 02)	Domain
information_entity_collection	product_category (Module 02)	Domain
agent_role_assignment	macrodata_artifact (kind: agent_assignment)	Domain
information_content_entity	macrodata_artifact (kind: design_asset)	Domain
denotation_relation	link_product_macrodata_artifact (Module 14)	Domain
document_artifact	macrodata_artifact (kind: document_export)	Domain

Old → new naming cross-reference

For engineers migrating from the original Supabase table names:

Supabase table	Ontology name (v1)	Commerce schema (v2)
raw_ingest_state	procedure_execution_state	CF Workflows
raw_ingest_runs	procedure_execution_record	CF Workflows + R2
raw_shopify_orders	commercial_transaction_record	order_header
raw_shopify_line_items	transaction_participation_record	order_line_item
dim_products	material_artifact	product
fact_sales_daily	sales_measurement_dataset	R2: measurement.sales
mart_performance_metrics	performance_measurement_dataset	R2: measurement.performance
classification_settings	objective_specification	macrodata_artifact
combo_logs	creative_act_report	macrodata_artifact
combo_suggestions	suggestion_content_entity	macrodata_artifact
combo_templates	plan_specification	macrodata_artifact
tag_classifications	tag_content_entity	product_tag
tag_groups	information_entity_collection	product_category
designer_assignments	agent_role_assignment	macrodata_artifact
assets	information_content_entity	macrodata_artifact
product_asset_mappings	denotation_relation	link_product_macrodata_artifact
csv_exports	document_artifact	macrodata_artifact
casestry_order_jobs	(not ontologized)	CF Workflows
casestry_order_job_items	(not ontologized)	CF Workflows