Schema Design

The target schema runs on PlanetScale PostgreSQL, with table and column names grounded in the ontology stack. Each table is categorized by its BFO type — Continuant tables hold things that persist, Occurrent tables record things that happen. See the Domain Ontology Mapping for the full entity-to-ontology mapping.

Key design choices:

UUID primary keys with gen_random_uuid() defaults
JSONB for structured data (tags, config, raw payloads)
TIMESTAMPTZ for all timestamps (UTC)
No stored procedures — all logic in application code
No materialized views — mart tables are regular tables rebuilt by background jobs
No enforced foreign keys — documented but not enforced
Table/column names follow ontological naming conventions

Raw layer

[BFO: Occurrent records | IAO: Information Bearing Entities carrying payloads]

Per ODR-004, raw-layer tables store Information Bearing Entities — faithful records of what external systems sent. Measurement typing happens at the core and mart layers.

-- Procedure execution state tracking [PKO: ExecutionStatus]
-- Stores cursor positions (watermarks) for restartable operations
CREATE TABLE procedure_execution_state (
  key            VARCHAR(255) PRIMARY KEY,
  value_json     JSONB NOT NULL,
  last_modified_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Procedure execution records [PKO: ProcedureExecution | CCO: Act by EngineeredSystem]
-- Each row represents one ingest run — a ProcedureExecution with ordered Steps
CREATE TABLE procedure_execution_record (
  run_id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  process_initiated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  process_completed_at TIMESTAMPTZ,
  step               VARCHAR(64) NOT NULL DEFAULT 'initiated',
  watermark          TIMESTAMPTZ,
  file_path          TEXT,
  orders_count       INT DEFAULT 0,
  line_items_count   INT DEFAULT 0,
  error_text         TEXT
);

-- Commercial transaction records [BFO: Process (recorded) | IAO: Report of Process]
-- Orders are Occurrents (ODR-005) — these are Reports of commercial transactions
CREATE TABLE commercial_transaction_record (
  order_id           VARCHAR(64) PRIMARY KEY,
  order_number       VARCHAR(64),
  process_initiated_at TIMESTAMPTZ NOT NULL,
  last_modified_at   TIMESTAMPTZ,
  financial_status   VARCHAR(64),
  currency           VARCHAR(8),
  total_price        DECIMAL(12,2),
  tags               JSONB,
  raw_json           JSONB
);

-- Transaction participation records [BFO: Process Part (recorded)]
-- How MaterialArtifacts (products) participated in commercial transactions
CREATE TABLE transaction_participation_record (
  order_id           VARCHAR(64) NOT NULL,
  line_item_id       VARCHAR(64) NOT NULL,
  artifact_identifier VARCHAR(64) NOT NULL,
  title              TEXT,
  status             VARCHAR(64),
  quantity           INT NOT NULL,
  order_initiated_at TIMESTAMPTZ NOT NULL,
  raw_json           JSONB,
  PRIMARY KEY (order_id, line_item_id)
);

CREATE INDEX idx_participation_artifact_day
  ON transaction_participation_record (artifact_identifier, order_initiated_at);

Core layer

[BFO: Independent Continuants + Measurement Data | IAO: typed ICEs]

Core-layer tables contain typed entities — MaterialArtifacts with Qualities, and Measurement Data on ratio scales.

-- Material artifacts [BFO: Independent Continuant | IOF: MaterialArtifact]
-- Products (phone cases) — physical artifacts with causal unity
CREATE TABLE material_artifact (
  artifact_identifier VARCHAR(64) PRIMARY KEY,
  title               TEXT,
  status              VARCHAR(64),
  vendor              VARCHAR(255),
  product_type        VARCHAR(255),
  tags                JSONB,
  first_seen          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_seen           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  is_active           BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE INDEX idx_material_artifact_active ON material_artifact (is_active);

-- Sales measurement data [IAO: Measurement Datum | CCO: Ratio Measurement]
-- Daily sales aggregates anchored to Temporal Region (calendar day)
CREATE TABLE sales_measurement_dataset (
  artifact_identifier VARCHAR(64) NOT NULL,
  day                 DATE NOT NULL,
  units               INT NOT NULL DEFAULT 0,
  units_net           INT NOT NULL DEFAULT 0,
  PRIMARY KEY (artifact_identifier, day)
);

CREATE INDEX idx_sales_measurement_artifact_day
  ON sales_measurement_dataset (artifact_identifier, day);

Mart layer

[IAO: Dataset of Measurement Data | CCO: Nominal + Ratio Measurements]

Mart tables serve the dashboard — they combine ratio measurements with nominal classifications derived from Objective Specifications.

-- Performance measurement dataset [IAO: Dataset | CCO: mixed Nominal + Ratio]
-- Aggregated measurements with classification governed by objective_specification
CREATE TABLE performance_measurement_dataset (
  artifact_identifier    VARCHAR(64) PRIMARY KEY,
  product_name           TEXT,
  product_status         VARCHAR(64),
  is_active              BOOLEAN,
  vendor                 VARCHAR(255),
  product_type           VARCHAR(255),
  tags                   JSONB,
  first_sale_date        TIMESTAMPTZ,
  launch_date            TIMESTAMPTZ,
  aggregate_measurement_value INT DEFAULT 0,
  sales_last_30_days     INT DEFAULT 0,
  sales_last_7_days      INT DEFAULT 0,
  sales_first_7_days     INT DEFAULT 0,
  sales_first_14_days    INT DEFAULT 0,
  nominal_classification VARCHAR(32) DEFAULT 'none',
  last_refreshed         TIMESTAMPTZ
);

CREATE INDEX idx_dataset_classification
  ON performance_measurement_dataset (nominal_classification);
CREATE INDEX idx_dataset_aggregate
  ON performance_measurement_dataset (aggregate_measurement_value);

-- Objective specifications [IAO: Objective Specification]
-- Thresholds that govern nominal classification — the "policy" layer (REA Typification)
CREATE TABLE objective_specification (
  id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  top_riser_7_day_threshold   INT DEFAULT 10,
  top_riser_14_day_threshold  INT DEFAULT 20,
  winner_30_day_threshold     INT DEFAULT 50,
  banana_count                INT DEFAULT 6,
  last_modified_at            TIMESTAMPTZ DEFAULT NOW(),
  temporal_origin             TIMESTAMPTZ DEFAULT NOW()
);

Application tables — Continuants

[BFO: Generically Dependent Continuants | IAO: ICEs, Documents, Plan Specifications]

These tables hold persistent information entities — things the system creates and manages.

-- Creative act reports [IAO: Report of Act (CCO)]
-- Records of completed creative testing acts; realizes a Plan Specification
CREATE TABLE creative_act_report (
  id                        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  plan_specification_identifier VARCHAR(128) NOT NULL UNIQUE,
  type                      VARCHAR(8) NOT NULL,
  product_count             INT NOT NULL,
  audience                  VARCHAR(128) NOT NULL,
  template_id               VARCHAR(128),
  cases_included            JSONB NOT NULL,
  first_tested_date         DATE NOT NULL,
  temporal_origin            TIMESTAMPTZ DEFAULT NOW(),
  last_modified_at          TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_report_type ON creative_act_report (type);
CREATE INDEX idx_report_audience ON creative_act_report (audience);
CREATE INDEX idx_report_tested_date ON creative_act_report (first_tested_date);

-- Suggestion content entities [IAO: ICE with measurement_confidence]
-- Generated combo candidates with ratio-scale confidence measurement
CREATE TABLE suggestion_content_entity (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  suggested_combos      JSONB NOT NULL,
  performance_context   TEXT,
  measurement_confidence DECIMAL(3,2),
  combo_size            INT NOT NULL,
  status                VARCHAR(32) DEFAULT 'pending',
  temporal_origin       TIMESTAMPTZ DEFAULT NOW()
);

-- Plan specifications [IAO: Plan Specification]
-- Combo templates prescribing future creative testing acts
CREATE TABLE plan_specification (
  id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name           VARCHAR(255) NOT NULL,
  config         JSONB NOT NULL,
  temporal_origin TIMESTAMPTZ DEFAULT NOW(),
  last_modified_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tag content entities [IAO: ICE | is_about → product collections]
-- Classifications that are information about product groups
CREATE TABLE tag_content_entity (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  category_name VARCHAR(255) NOT NULL,
  tag_name      VARCHAR(255) NOT NULL,
  temporal_origin TIMESTAMPTZ DEFAULT NOW(),
  last_modified_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (category_name, tag_name)
);

CREATE INDEX idx_tag_ice_category ON tag_content_entity (category_name);

-- Information entity collections [IAO: ICE Collection]
-- Named groupings of tag ICEs (member_part_of relation)
CREATE TABLE information_entity_collection (
  id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name           VARCHAR(255) NOT NULL,
  tag_names      JSONB NOT NULL,
  temporal_origin TIMESTAMPTZ DEFAULT NOW(),
  last_modified_at TIMESTAMPTZ DEFAULT NOW()
);

-- Agent role assignments [CCO: agent_in | BFO: has_role]
-- Persons (designers) bearing Roles in relation to product tag attribution
CREATE TABLE agent_role_assignment (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  designer_name   VARCHAR(255) NOT NULL,
  tag_name        VARCHAR(255) NOT NULL,
  assigned_at     TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (designer_name, tag_name)
);

CREATE INDEX idx_agent_role_designer ON agent_role_assignment (designer_name);

-- Information content entities [IAO: ICE | concretized_by → R2 IBE]
-- Design assets — generically dependent information carried by R2 objects
CREATE TABLE information_content_entity (
  id                     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  content_hash_identifier VARCHAR(64) NOT NULL UNIQUE,
  original_filename      TEXT NOT NULL,
  bearer_entity_key      VARCHAR(512) NOT NULL,
  bearer_entity_url      TEXT NOT NULL,
  dropbox_url            TEXT,
  file_size              INT NOT NULL,
  content_type           VARCHAR(128),
  temporal_origin        TIMESTAMPTZ DEFAULT NOW(),
  last_modified_at       TIMESTAMPTZ DEFAULT NOW()
);

-- Denotation relations [IAO: denotes]
-- ICE (design asset) denotes MaterialArtifact (product)
CREATE TABLE denotation_relation (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  artifact_identifier VARCHAR(64) NOT NULL,
  asset_id            UUID NOT NULL,
  temporal_origin     TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (artifact_identifier, asset_id)
);

CREATE INDEX idx_denotation_asset ON denotation_relation (asset_id);

-- Document artifacts [IAO: Document]
-- Generated CSV exports — standalone information artifacts carried by R2 IBEs
CREATE TABLE document_artifact (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  status          VARCHAR(32) NOT NULL DEFAULT 'pending',
  file_url        TEXT,
  total_orders    INT,
  metadata        JSONB,
  error_message   TEXT,
  generated_at    TIMESTAMPTZ,
  temporal_origin TIMESTAMPTZ DEFAULT NOW()
);

Layer provenance

Every table belongs to exactly one pipeline layer. The ontological type determines the layer assignment:

Layer	Tables	Ontological Character
Raw	`procedure_execution_state`, `procedure_execution_record`, `commercial_transaction_record`, `transaction_participation_record`	IBEs carrying process records; ProcedureExecution tracking
Core	`material_artifact`, `sales_measurement_dataset`	Typed Continuants (MaterialArtifact) + typed Measurement Data (ratio scale)
Mart	`performance_measurement_dataset`, `objective_specification`	Aggregated Dataset + governing Objective Specifications
Application	All remaining tables	Domain ICEs (Plan Specs, Reports, Collections), Agent relations, Documents

Naming cross-reference

For engineers migrating from the old schema names:

Old table name	New table name	Ontological type
`raw_ingest_state`	`procedure_execution_state`	PKO state tracking
`raw_ingest_runs`	`procedure_execution_record`	ProcedureExecution (PKO)
`raw_shopify_orders`	`commercial_transaction_record`	Report of Process (IAO)
`raw_shopify_line_items`	`transaction_participation_record`	Report of Process Part (IAO)
`dim_products`	`material_artifact`	MaterialArtifact (IOF)
`fact_sales_daily`	`sales_measurement_dataset`	Measurement Datum (IAO)
`mart_performance_metrics`	`performance_measurement_dataset`	Dataset (IAO)
`classification_settings`	`objective_specification`	Objective Specification (IAO)
`combo_logs`	`creative_act_report`	Report (IAO) of Act (CCO)
`combo_suggestions`	`suggestion_content_entity`	ICE (IAO)
`combo_templates`	`plan_specification`	Plan Specification (IAO)
`tag_classifications`	`tag_content_entity`	ICE (IAO)
`tag_groups`	`information_entity_collection`	ICE Collection (IAO)
`designer_assignments`	`agent_role_assignment`	agent_in (CCO) / has_role (BFO)
`assets`	`information_content_entity`	ICE (IAO)
`product_asset_mappings`	`denotation_relation`	denotes (IAO)
`csv_exports`	`document_artifact`	Document (IAO)