CatWing ERD
100%
Fit
1:1
Global Schema
Products & Categories
Suppliers
Locations
Pricing
Customers & External Data
Sales Orders
Purchase & Receiving
Store & Transfer
Behaviors
Data Quality & Health
Staging
Demand & Embeddings
Assortments & Inventory
Recommendations
countries
id
int
PK
name
string
UNQ
iso_code_2
string
UNQ
iso_code_3
string
UNQ
regions
id
int
PK
country_id
int
FK
name
string
municipalities
id
int
PK
region_id
int
FK
name
string
cities
id
int
PK
municipality_id
int
FK
name
string
postal_code
string
latitude
float
longitude
float
languages
id
int
PK
code
string UK -- en | bg | de | fr
UNQ
name
string
currencies
id
int
PK
code
string UK -- EUR | BGN | USD | CHF
UNQ
name
string
symbol
string
currency_rates
id
int
PK
from_currency_id
int
FK
to_currency_id
int
FK
rate
float
effective_date
date
roles
id
int
PK
name
string UK -- admin | analyst | viewer | etc.
UNQ
users
id
int
PK
email
string
UNQ
display_name
string
is_active
boolean
x_user_roles
id
int
PK
user_id
int
FK
role_id
int
FK
weather_observations
id
int
PK
city_id
int
FK
observation_date
date
temperature_max
float
temperature_min
float
temperature_mean
float
apparent_temperature_max
float
apparent_temperature_min
float
apparent_temperature_mean
float
precipitation_sum
float
rain_sum
float
snowfall_sum
float
precipitation_hours
float
windspeed_max
float
windgusts_max
float
wind_direction_dominant
float
shortwave_radiation_sum
float
weather_code
int
product_translations
id
int
PK
product_id
int
FK
field_name
string -- e.g. product_name
language_id
int
FK
value
string
product_category_translations
id
int
PK
product_category_id
int
FK
field_name
string
language_id
int
FK
value
string
location_translations
id
int
PK
location_id
int
FK
field_name
string
language_id
int
FK
value
string
supplier_translations
id
int
PK
supplier_id
int
FK
field_name
string
language_id
int
FK
value
string
client_config
id
int
PK
client_id
string
UNQ
client_name
string
base_currency_id
int
FK
config
jsonb
products
id
int
PK
external_id
string
UNQ
product_sku
string
UNQ
product_vendor_sku
string
product_name
string
brand_id
int nullable
FK
product_category_id
int
FK
supplier_id
int
FK
cost
float cached -- authoritative: x_supplier_products.unit_cost
price
float cached -- authoritative: pricing_lists by priority + date
currency_id
int
FK
birth_date
date
gender_id
int
FK
product_status_id
int
FK
price_tier_id
int
FK
is_active
boolean
brands
id
int
PK
name
string
UNQ
country_of_origin
string nullable
product_attributes
id
int
PK
product_id
int
FK
attribute_name
string
attribute_value
string
product_images
id
int
PK
product_id
int
FK
s3_key
string
image_type_id
int
FK
image_types
id
int
PK
name
string UK -- main | gallery | thumbnail | etc.
UNQ
product_statuses
id
int
PK
name
string UK -- active | discontinued | draft | etc.
UNQ
genders
id
int
PK
name
string UK -- Male | Female | Unisex
UNQ
price_tiers
id
int
PK
name
string UK -- per-client: e.g. Entry | Mid | Premium | Luxury
UNQ
product_settings
id
int
PK
product_id
int
FK
enabled_for_processing
boolean
first_order_qty
float nullable
package_qty
float nullable
minimum_order_qty
float nullable
optimal_order_qty
float nullable
product_categories
id
int
PK
product_category_type_id
int
FK
parent_product_category_id
int nullable, self-ref
FK
name
string
code
string nullable
sort_order
int
product_category_types
id
int
PK
type_name
string
UNQ
is_hierarchical
boolean
product_category_type_source_id
int
FK
product_category_type_sources
id
int
PK
name
string UK -- erp | config | computed
UNQ
suppliers
id
int
PK
external_id
string
UNQ
supplier_name
string
city_id
int
FK
direct_shipment
boolean
supplier_status_id
int
FK
is_active
boolean
supplier_statuses
id
int
PK
name
string UK -- active | inactive | suspended | etc.
UNQ
supplier_settings
id
int
PK
supplier_id
int
FK
operate
boolean
reorder_time_days
int nullable
lead_time_min_days
int nullable
lead_time_max_days
int nullable
min_order_amount
float nullable
currency_id
int
FK
free_limit_transport
float nullable
transport_cost_min
float nullable
transport_cost_max
float nullable
backorder_expiry_days
int nullable
payment_terms
string nullable
x_supplier_products
id
int
PK
supplier_id
int
FK
product_id
int
FK
is_primary
boolean
supplier_sku
string
unit_cost
float
currency_id
int
FK
x_supplier_brands
id
int
PK
supplier_id
int
FK
brand_id
int
FK
locations
id
int
PK
external_id
string
location_name
string
address
string
location_type_id
int
FK
city_id
int
FK
latitude
float
longitude
float
is_central
boolean
is_buffer
boolean
is_ephemeral
boolean
is_active
boolean
location_types
id
int
PK
name
string UK -- warehouse | dc | store | store_buffer | online | external
UNQ
location_relationships
id
int
PK
parent_location_id
int
FK
child_location_id
int
FK
location_relationship_type_id
int
FK
lead_time_days
int
transfer_cost
float nullable
currency_id
int nullable
FK
effective_from
date
effective_to
date nullable
location_relationship_types
id
int
PK
name
string
UNQ
location_settings
id
int
PK
location_id
int
FK
office_id
int nullable
is_main_warehouse
boolean
monitor
boolean
recommend_orders
boolean
product_location_settings
id
int
PK
product_id
int
FK
location_id
int
FK
enabled_for_location
boolean
location_pos_terminals
id
int
PK
location_id
int
FK
pos_id
string
UNQ
inventory_quantities
id
int
PK
product_id
int
FK
location_id
int
FK
snapshot_date
date
qty_available
float
qty_blocked
float
qty_reserved
float
qty_total
float
batch_id
string nullable
batch_expiry_date
date nullable
pricing_lists
id
int
PK
external_id
string
UNQ
product_id
int
FK
price_kind_id
int
FK
price_value
float
currency_id
int
FK
from_date
date
thru_date
date
price_kinds
id
int
PK
name
string
UNQ
rank
int UK -- priority ordering: Phase-out(2) > Monthly Discount(1) > Base(0)
UNQ
product_discount_authorizations
id
int
PK
product_id
int
FK
discount_pct
float -- supplier-authorized discount ceiling
markdown_pct
float -- permanent markdown (UnitPrice vs StandardUnitPrice)
authorization_source_id
int
FK
effective_from
date
effective_to
date nullable -- open-ended if still active
authorization_sources
id
int
PK
name
string UK -- supplier | internal | clearance
UNQ
customers
id
int
PK
external_id
string UK -- ERP party/customer number
UNQ
customer_type_id
int
FK
name
string nullable
customer_types
id
int
PK
name
string UK -- Individual | Business | Wholesale | etc.
UNQ
product_market_observations
id
int
PK
product_id
int
FK
market_observation_source_id
int
FK
observation_date
date
market_share
float nullable
competitor_price
float nullable
currency_id
int
FK
metadata
jsonb
category_market_observations
id
int
PK
product_category_id
int
FK
market_observation_source_id
int
FK
observation_date
date
market_share
float nullable
category_growth_rate
float nullable
currency_id
int
FK
metadata
jsonb
market_observation_sources
id
int
PK
name
string UK -- nielsen_jfk | kolkostruva
UNQ
product_web_analytics_daily
id
int
PK
product_id
int
FK
observation_date
date
sessions
int
page_views
int
add_to_cart
int
transactions
int
revenue
float
currency_id
int
FK
bounce_rate
float
conversion_rate
float
location_web_analytics_daily
id
int
PK
location_id
int
FK
observation_date
date
sessions
int
page_views
int
add_to_cart
int
transactions
int
revenue
float
currency_id
int
FK
bounce_rate
float
conversion_rate
float
product_social_ad_daily
id
int
PK
product_id
int
FK
social_platform_id
int
FK
observation_date
date
spend
float
impressions
int
clicks
int
conversions
int
revenue
float
currency_id
int
FK
ctr
float
roas
float
category_social_ad_daily
id
int
PK
product_category_id
int
FK
social_platform_id
int
FK
observation_date
date
spend
float
impressions
int
clicks
int
conversions
int
revenue
float
currency_id
int
FK
ctr
float
roas
float
social_platforms
id
int
PK
name
string UK -- facebook | instagram
UNQ
sales_orders
id
int
PK
external_id
string
UNQ
location_id
int
FK
order_date
date
sales_order_status_id
int
FK
sales_order_lines
id
int
PK
external_id
string
UNQ
sales_order_id
int
FK
product_id
int
FK
quantity
float
standard_unit_price
float -- MSRP/RRP catalog list price
unit_price
float -- actual selling price (may include ERP markdown)
line_discount_pct
float -- transaction-level discount applied at POS
revenue
float -- unit_price * qty * (1 - line_discount_pct)
unit_cost
float -- cost from ST receipt or PO invoice
line_cost
float -- unit_cost * quantity
currency_id
int
FK
customer_id
int nullable
FK
location_pos_terminal_id
int
FK
sales_order_statuses
id
int
PK
name
string UK -- open | completed | cancelled
UNQ
sales_order_update_types
id
int
PK
name
string UK -- cancellation | amendment | return | price_adjustment
UNQ
sales_order_line_updates
id
int
PK
sales_order_line_id
int
FK
sales_order_update_type_id
int
FK
update_date
date
quantity
float
revenue
float
unit_cost
float
line_discount_pct
float
sales_order_status_id
int
FK
reason
string
purchase_orders
id
int
PK
external_id
string
UNQ
supplier_id
int
FK
location_id
int
FK
order_date
date
purchase_order_status_id
int
FK
purchase_order_lines
id
int
PK
external_id
string
UNQ
purchase_order_id
int
FK
product_id
int
FK
qty_requested
float
qty_confirmed
float
qty_delivered
float
unit_cost
float
expected_delivery
date
qty_cancelled
float
qty_pending
float
currency_id
int
FK
purchase_order_statuses
id
int
PK
name
string
UNQ
purchase_order_update_types
id
int
PK
name
string UK -- confirmation | partial_delivery | cancellation | amendment | price_adjustment
UNQ
receiving_order_statuses
id
int
PK
name
string UK -- pending | partial | complete | cancelled
UNQ
purchase_order_line_updates
id
int
PK
purchase_order_line_id
int
FK
purchase_order_update_type_id
int
FK
update_date
date
qty_requested
float
qty_confirmed
float
qty_delivered
float
qty_cancelled
float
qty_pending
float
purchase_order_status_id
int
FK
reason
string
receiving_orders
id
int
PK
external_id
string
UNQ
supplier_id
int
FK
location_id
int
FK
document_date
date
receiving_order_status_id
int
FK
receiving_order_lines
id
int
PK
external_id
string
UNQ
receiving_order_id
int
FK
product_id
int
FK
purchase_order_line_id
int
FK
quantity
float
unit_cost
float
currency_id
int
FK
store_orders
id
int
PK
external_id
string
UNQ
from_location_id
int
FK
to_location_id
int
FK
purchase_order_id
int nullable
FK
order_date
date
store_order_status_id
int
FK
store_order_lines
id
int
PK
external_id
string
UNQ
store_order_id
int
FK
product_id
int
FK
quantity
float
store_order_statuses
id
int
PK
name
string
UNQ
transfer_orders
id
int
PK
external_id
string
UNQ
from_location_id
int
FK
to_location_id
int
FK
document_date
date
transfer_type_id
int
FK
transfer_order_status_id
int
FK
transfer_order_lines
id
int
PK
external_id
string
UNQ
transfer_order_id
int
FK
product_id
int
FK
quantity
float
transfer_order_statuses
id
int
PK
name
string UK -- pending | in_transit | received | cancelled
UNQ
transfer_types
id
int
PK
name
string
UNQ
fmr_classes
id
int
PK
code
string UK -- F | M | R
UNQ
name
string
abc_classes
id
int
PK
code
string UK -- A | B | C
UNQ
name
string
xyz_classes
id
int
PK
code
string UK -- X | Y | Z
UNQ
name
string
freq_classes
id
int
PK
code
string UK -- Frequent | Normal | Rare
UNQ
name
string
location_behaviors
id
int
PK
location_id
int
FK
snapshot_date
date
fill_rate
float
stockout_rate
float
avg_inventory_value
float
revenue
float
currency_id
int
FK
active_products
int
product_behaviors
id
int
PK
product_id
int
FK
snapshot_date
date
velocity
float
trend
float
hotness_score
float
fmr_class_id
int
FK
abc_class_id
int
FK
xyz_class_id
int
FK
freq_class_id
int
FK
demand_cv
float
seasonality_strength
float
shelf_time_days
float
lead_time_days
int
reorder_time_days
int
n_stores
int
n_stores_optimized
int
min_inv_qty
int
demand_coverage_scale
float
supplier_availability_score
float nullable, 0-100
is_supplier_availability_confirmed
boolean
phase_out_recommendation_score
float nullable, 0-100
is_phase_out_recommendation_confirmed
boolean
phase_out_execution_score
float nullable, 0-100
is_phase_out_execution_confirmed
boolean
phase_out_efficiency_score
float nullable, 0-100, confirmed PHASE-OUT only
product_location_behaviors
id
int
PK
product_id
int
FK
location_id
int
FK
snapshot_date
date
demand_qty
float
demand_revenue
float nullable
demand_cost
float nullable
stockout_qty
float nullable
is_oos_imputed
boolean
velocity
float
trend
float
fmr_class_id
int
FK
abc_class_id
int
FK
xyz_class_id
int
FK
freq_class_id
int
FK
hotness_score
float
supplier_behaviors
id
int
PK
supplier_id
int
FK
snapshot_date
date
avg_lead_time_days
float
lead_time_p90
float
fill_rate
float
on_time_rate
float
open_po_count
int
category_behaviors
id
int
PK
product_category_id
int
FK
snapshot_date
date
product_count
int
active_product_count
int
total_revenue
float
total_inventory_value
float
currency_id
int
FK
avg_hotness_score
float
avg_velocity
float
growth_rate
float
supplier_available_products
id
int
PK
product_id
int
FK
supplier_id
int
FK
supplier_ref
string
code
string
is_available
boolean
match_method_id
int
FK
file_date
date
match_methods
id
int
PK
name
string
UNQ
data_quality_run_statuses
id
int
PK
name
string UK -- running | passed | failed | error
UNQ
data_quality_check_categories
id
int
PK
name
string UK -- completeness | consistency | freshness | statistical_anomaly
UNQ
data_quality_check_severities
id
int
PK
name
string UK -- critical | warning
UNQ
data_quality_runs
id
int
PK
data_quality_run_status_id
int
FK
gate_passed
boolean
overall_score
float -- 0-100
critical_count
int
warning_count
int
checks_total
int
checks_passed
int
started_at
timestamp
completed_at
timestamp
created_at
timestamp
data_quality_checks
id
int
PK
data_quality_run_id
int
FK
data_quality_check_category_id
int
FK
data_quality_check_severity_id
int
FK
endpoint
string
check_name
string
passed
boolean
expected_value
string nullable
actual_value
string nullable
detail
string nullable
fix_instructions
string nullable
data_quality_check_reports
id
int
PK
endpoint
string
check_name
string
run_date
date
metric_value
float
baseline
float nullable
deviation_pct
float nullable
created_at
timestamp
data_health_reports
id
int
PK
run_date
date
step_name
string
checks_passed
int
checks_failed
int
quality_score
float
anomalies
jsonb nullable
created_at
timestamp
staging_products
product_id
string
PK
vendor_sku
string
name
string
brand
string
supplier_id
string nullable
cost
float nullable
price
float nullable
staging_locations
location_id
string
PK
name
string
type
string nullable
status
string nullable
staging_suppliers
supplier_id
string
PK
name
string
staging_purchase_orders
po_id
string
PK
product_id
string
supplier_id
string nullable
qty_ordered
float nullable
qty_confirmed
float nullable
qty_delivered
float nullable
date
date nullable
staging_sales_orders
so_id
string
PK
product_id
string
location_id
string nullable
qty
float nullable
revenue
float nullable
unit_price
float nullable
date
date nullable
staging_inventory_balances
product_id
string
location_id
string
qty_available
float nullable
qty_blocked
float nullable
qty_reserved
float nullable
qty_all
float nullable
date
date nullable
demand_forecasts
id
int
PK
forecast_date
date
demand_forecast_status_id
int
FK
metadata
jsonb nullable
created_at
timestamp
demand_forecast_statuses
id
int
PK
name
string UK -- pending | running | completed | failed
UNQ
demand_forecast_products
id
int
PK
demand_forecast_id
int
FK
product_id
int
FK
location_id
int
FK
demand_source_type_id
int
FK
horizon_days
int
prob_demand
float
predicted_qty
float
critical_ratio
float
confidence
float
demand_source_types
id
int
PK
name
string UK -- actual | imputed_local_global | imputed_global_only | dead_product | online_only | dropship | unknown
UNQ
embedding_types
id
int
PK
name
string
UNQ
embeddings
id
int
PK
product_id
int
FK
embedding_type_id
int
FK
vector
vector(n) -- pgvector; n depends on model
model_version
string
assortments
id
int
PK
assortment_type_id
int
FK
brand_id
int nullable
FK
location_id
int nullable
FK
effective_date
date
assortment_workflow_status_id
int
FK
assortment_types
id
int
PK
name
string UK -- global | local
UNQ
assortment_workflow_statuses
id
int
PK
name
string UK -- in_review | confirmed | locked
UNQ
assortment_zones
id
int
PK
name
string
UNQ
assortment_products
id
int
PK
assortment_id
int
FK
product_id
int
FK
cw_product_assortment_status_id
int
FK
client_product_assortment_status_id
int
FK
assortment_workflow_status_id
int
FK
target_stock
float
assortment_zone_id
int
FK
ranking_score
float
rank
int
is_launch
boolean
is_phase_out
boolean
is_retired
boolean
phase_out_execution_score
float nullable 0-100
note
string
assortment_reason_id
int
FK
assortment_product_updates
id
int
PK
assortment_product_id
int
FK
update_date
date
cw_product_assortment_status_id
int
FK
client_product_assortment_status_id
int
FK
assortment_workflow_status_id
int
FK
target_stock
float
assortment_zone_id
int
FK
ranking_score
float
rank
int
is_launch
boolean
is_phase_out
boolean
is_retired
boolean
phase_out_execution_score
float nullable 0-100
note
string
assortment_reason_id
int
FK
assortment_reasons
id
int
PK
name
string UK -- new_launch | overstock_rebalance | phase_out | demand_signal | etc.
UNQ
assortment_product_statuses
id
int
PK
name
string UK -- STOCK | NOT-STOCK
UNQ
inventory_strategies
id
int
PK
name
string UK -- fmr_abc_quantile | product_level | service_level | newsvendor | seasonal_trend | moving_average | abc_xyz_matrix | dynamic_safety | margin_weighted | forecast_based | downstream_aggregate | periodic_review | base_stock | buffer_min_qty
UNQ
echelon
int -- 1=store | 2=DC | 3=warehouse | 4=central
inventory_targets
id
int
PK
product_id
int
FK
location_id
int
FK
snapshot_date
date
inventory_strategy_id
int
FK
target_stock
float
reorder_point
float
safety_stock
float
fill_rate_simulated
float
inventory_target_params
id
int
PK
inventory_target_id
int
FK
param_name
string -- e.g. fa_quantile, safety_multiplier, service_level
param_value
float
po_recommendations
id
int
PK
product_id
int
FK
supplier_id
int
FK
location_id
int
FK
recommendation_date
date
recommended_qty
float
predicted_qty
float
order_buffer
float
unit_cost
float -- snapshot cost assumed at recommendation time
currency_id
int
FK
confidence
float
confidence_reasons
string
po_recommendation_priority_id
int
FK
priority_reasons
string
po_recommendation_status_id
int
FK
is_respected
boolean nullable, set by UpliftMonitor
po_recommendation_statuses
id
int
PK
name
string UK -- pending | accepted | rejected | ordered
UNQ
po_recommendation_priorities
id
int
PK
name
string UK -- critical | high | medium | low
UNQ
purchase_order_recommendation_impacts
id
int
PK
po_recommendation_id
int
FK
purchase_order_recommendation_impact_type_id
int
FK
measurement_date
date
baseline_value
float
actual_value
float
impact_value
float -- actual - baseline
impact_pct
float -- percentage change
measurement_method_id
int nullable
FK
purchase_order_recommendation_impact_types
id
int
PK
name
string UK -- revenue_uplift | fill_rate_improvement | inventory_optimization | stockout_reduction | overstock_reduction | cost_savings
UNQ
to_recommendations
id
int
PK
product_id
int
FK
from_location_id
int
FK
to_location_id
int
FK
recommendation_date
date
recommended_qty
float
to_recommendation_reason_id
int
FK
to_recommendation_status_id
int
FK
is_respected
boolean nullable, set by UpliftMonitor
to_recommendation_statuses
id
int
PK
name
string
UNQ
to_recommendation_reasons
id
int
PK
name
string
UNQ
transfer_order_recommendation_impacts
id
int
PK
to_recommendation_id
int
FK
transfer_order_recommendation_impact_type_id
int
FK
measurement_date
date
baseline_value
float
actual_value
float
impact_value
float -- actual - baseline
impact_pct
float -- percentage change
measurement_method_id
int nullable
FK
transfer_order_recommendation_impact_types
id
int
PK
name
string UK -- revenue_uplift | fill_rate_improvement | inventory_optimization | stockout_reduction | overstock_reduction | cost_savings
UNQ
measurement_methods
id
int
PK
name
string
UNQ
parent
parent
child
from
to
from
to
from
to
cw_status
client_status
cw_status
client_status
from
to
base
Scroll
zoom
Drag
pan
+
/
−
zoom
Home
fit
0
reset
/
search