ChainAlight Data Model

Version: 1.0 Date: September 6, 2025

1. Introduction

This document serves as the single source of truth for the ChainAlight database schema. The model is designed to support the "S&OP Intelligence Sheets" concept, providing a structured, relational foundation for the entire S&OP continuous intelligence cycle.

The schema is divided into three main categories:

• Core S&OP Flow Tables: These tables store the time-series data for demand, supply, inventory, and finance, versioned by scenarios.
• Dimension Tables: These tables store the master data for entities like products, customers, and locations.
• AI & Application Tables: These tables support the application's intelligence layer, including storing insights, transcripts, and key decisions.

2. Entity-Relationship Diagram (ERD)

The following schema is written in DBML (Database Markup Language) and can be pasted into dbdiagram.io to generate a visual ERD.

//// -----------------------------------------
//// ChainAlight Data Model
//// Paste into https://dbdiagram.io to visualize
//// -----------------------------------------

// --- Dimension Tables ---

Table products {
  id integer [pk, increment]
  sku varchar [unique, not null]
  product_name varchar [not null]
  product_family varchar
}

Table regions {
  id integer [pk, increment]
  region_name varchar [unique, not null]
}

Table plants {
  id integer [pk, increment]
  plant_name varchar [unique, not null]
  location varchar
}

Table locations {
  id integer [pk, increment]
  location_name varchar [unique, not null]
  location_type varchar // e.g., 'WAREHOUSE', 'DC'
}

Table customers {
  id integer [pk, increment]
  customer_name varchar [unique, not null]
  segment varchar
}

Table suppliers {
  id integer [pk, increment]
  supplier_name varchar [unique, not null]
  tier integer
}

// --- AI & Application Tables ---

Table scenarios {
  id integer [pk, increment]
  scenario_name varchar [not null]
  description text
  parent_scenario_id integer
  status varchar // 'DRAFT', 'RECOMMENDED', 'LOCKED'
  created_at timestamp
}

Table users {
  id integer [pk, increment]
  name varchar
  email varchar [unique, not null]
}

Table decision_log {
  id integer [pk, increment]
  decision_type varchar
  description text
  status varchar
  scenario_id integer [ref: > scenarios.id]
  user_id integer [ref: > users.id]
  created_at timestamp
}

Table documents {
  id integer [pk, increment]
  content text [not null]
  embedding vector(1536)
  source_type varchar // 'insight', 'golden_corpus', 'decision_log'
  metadata jsonb // Flexible storage for tags, entities, scores, severity, etc.
  created_at timestamp
}

Table transcripts {
  id integer [pk, increment]
  -- This could also be merged into the 'documents' table
  -- with source_type = 'transcript'
  meeting_id varchar
  speaker varchar
  text text
  timestamp timestamp
}

// --- Core S&OP Flow Tables ---
// These tables are versioned by scenario_id

Table demand_flow {
  id integer [pk, increment]
  product_id integer [ref: > products.id]
  region_id integer [ref: > regions.id]
  period date [not null]
  forecast_units integer
  actual_units integer
  bookings_units integer
  shipments_units integer
  backlog_units integer
  unconstrained_demand_plan_units integer
  pricing decimal
  scenario_id integer [ref: > scenarios.id]
  version integer
}

Table supply_flow {
  id integer [pk, increment]
  plant_id integer [ref: > plants.id]
  product_id integer [ref: > products.id]
  period date [not null]
  production_plan_units integer
  actual_production_units integer
  capacity_units integer
  capacity_utilization float
  yield_rate float
  scenario_id integer [ref: > scenarios.id]
version integer
}

Table inventory_flow {
  id integer [pk, increment]
  product_id integer [ref: > products.id]
  location_id integer [ref: > locations.id]
  period date [not null]
  on_hand_units integer
  in_transit_units integer
  available_units integer
  safety_stock_units integer
  days_of_cover float
  inventory_value decimal
  scenario_id integer [ref: > scenarios.id]
  version integer
}

Table shipment_flow {
  id integer [pk, increment]
  product_id integer [ref: > products.id]
  customer_id integer [ref: > customers.id]
  period date [not null]
  shipped_units integer
  on_time_delivery_rate float
  fill_rate float
  scenario_id integer [ref: > scenarios.id]
  version integer
}

Table financial_integration {
  id integer [pk, increment]
  period date [not null]
  product_id integer [ref: > products.id]
  region_id integer [ref: > regions.id]
  revenue decimal
  cogs decimal
  gross_margin decimal
  gross_margin_percent float
  inventory_value decimal
  working_capital decimal
  scenario_id integer [ref: > scenarios.id]
  version integer
}

Table constraints {
  id integer [pk, increment]
  constraint_name varchar
  constraint_type varchar // 'PHYSICAL', 'FINANCIAL', 'RESOURCE', 'SUPPLIER'
  resource_id integer // FK to plants, suppliers etc.
  period date
  capacity decimal
  utilization decimal
  status varchar // 'OK', 'AT_RISK', 'VIOLATED'
  scenario_id integer [ref: > scenarios.id]
  version integer
}

3. Table Definitions

Core S&OP Flow Tables

These tables represent the core time-series data for the S&OP process. Each record is typically associated with a scenario_id to support what-if analysis.

demand_flow

Tracks all data related to customer demand and forecasting.

Column	Data Type	Description
id	integer	Primary Key
product_id	integer	Foreign Key to products table
region_id	integer	Foreign Key to regions table
period	date	The time period for the data (e.g., monthly)
forecast_units	integer	The projected demand for the period
actual_units	integer	The actual sales/orders for the period
bookings_units	integer	New orders received in the period
shipments_units	integer	Orders fulfilled in the period
backlog_units	integer	Open orders at the end of the period
unconstrained_demand_plan_units	integer	The consensus unconstrained demand
pricing	decimal	Average selling price for the period
scenario_id	integer	Foreign Key to scenarios table
version	integer	Version number for the record within a scenario

supply_flow

Tracks all data related to production and capacity.

Column	Data Type	Description
id	integer	Primary Key
plant_id	integer	Foreign Key to plants table
product_id	integer	Foreign Key to products table
period	date	The time period for the data
production_plan_units	integer	Planned production volume
actual_production_units	integer	Actual production volume
capacity_units	integer	Maximum theoretical production capacity
capacity_utilization	float	actual_production / capacity
yield_rate	float	The percentage of non-defective items produced
scenario_id	integer	Foreign Key to scenarios table
version	integer	Version number for the record

inventory_flow

Tracks all data related to on-hand and in-transit stock.

Column	Data Type	Description
id	integer	Primary Key
product_id	integer	Foreign Key to products table
location_id	integer	Foreign Key to locations (warehouses)
period	date	The time period for the data
on_hand_units	integer	Physical quantity available
in_transit_units	integer	Quantity shipped but not yet received
available_units	integer	Total stock available to promise
safety_stock_units	integer	The explicit buffer of inventory
days_of_cover	float	Days the current inventory can cover demand
inventory_value	decimal	The financial value of the on-hand inventory
scenario_id	integer	Foreign Key to scenarios table
version	integer	Version number for the record

(Other core flow tables like shipment_flow, financial_integration, and constraints follow a similar structure.)

---

Dimension Tables

These tables store master data for key business entities.

products

Column	Data Type	Description
id	integer	Primary Key
sku	varchar	Unique Stock Keeping Unit
product_name	varchar	The name of the product
product_family	varchar	The product family or category

scenarios

Column	Data Type	Description
id	integer	Primary Key
scenario_name	varchar	e.g., "Baseline", "Executive Override"
description	text	A brief description of the scenario's purpose
parent_scenario_id	integer	The scenario from which this one was branched
status	varchar	'DRAFT', 'RECOMMENDED', 'LOCKED'
created_at	timestamp	When the scenario was created

(Other dimension tables like regions, plants, customers, etc., follow a similar structure.)

---

AI & Application Tables

These tables support the application's intelligence, RAG, and logging capabilities.

documents

A unified table to store all text-based content for semantic search, including AI-generated insights, Golden Corpus exemplars, and potentially meeting transcripts.

Column	Data Type	Description
id	integer	Primary Key
content	text	The raw text content of the document or insight.
embedding	vector	The vector representation for semantic search
source_type	varchar	The origin of the content (e.g., 'insight', 'golden_corpus', 'human_feedback').
metadata	jsonb	Flexible JSON field for storing context like insight_type, severity, tags, entities, etc.
created_at	timestamp	When the insight was generated

decision_log

Provides an auditable record of all key decisions made in the platform.

Column	Data Type	Description
id	integer	Primary Key
decision_type	varchar	e.g., 'UNCONSTRAINED_PLAN_APPROVAL'
description	text	Includes the rationale for the decision
status	varchar	'LOCKED'
scenario_id	integer	The scenario that was approved
user_id	integer	The user who locked in the decision
created_at	timestamp	When the decision was made