Product Hierarchy

ops product hierarchy

The product data model follows a two-level hierarchy within each Site:

graph TD
    Site["Site (oee_sites)"] --> PF["Product Families (ops_product_families)"]
    PF --> P["Products (ops_products)"]
    P -.->|used by| Job["OPS Jobs (ops_jobs)"]
    PF -.->|fallback standard_hours| Job

Level 1: Product Families

Product Families group related products and define site-wide defaults:

• family_code + family_name — identification
• standard_hours — fallback standard hours (used when product-level is not set)
• default_quantity — default target quantity when posting a job
• target_scrap_rate — expected scrap rate for this product type
• unit_cost — cost per unit

Scope: Per-site. Each family code is unique within a site.

Level 2: Products

Products are specific items within a family:

• product_code + product_name — identification
• standard_hours — product-specific standard hours (overrides family-level)
• is_active — can be deactivated without deletion

Scope: Per-family. Each product code is unique within its family.

Standard Hours Cascade

The Efficiency Calculation uses this priority:

ops_products.standard_hours  (if set)
  -> ops_product_families.standard_hours  (fallback)
    -> 0  (no standard hours defined)

This cascade is implemented in SQL as:

COALESCE(p.standard_hours, pf.standard_hours, 0)

Per-Station Standard Hours

The ops_station_product_standards table allows defining different standard hours for the same product family at different stations:

Column	Type	Description
product_family_id	UUID (FK)	Which Product Family
station_id	UUID (FK)	Which Station
cycle_time	NUMERIC	Expected cycle time at this station
scrap_rate	NUMERIC	Expected scrap rate at this station
standard_hours	NUMERIC	Station-specific standard hours

Unique constraint: (product_family_id, station_id)

Relationship to Jobs

When posting a job, the operator selects:

1. A Product (which implicitly selects its family)
2. Optionally a Product Family directly (for legacy compatibility)

The job stores both product_id and product_family_id. The product’s standard hours drive efficiency calculations.

Management UI

Products and families are managed via the OEE Settings modal (gear icon on the OEE dashboard, /oee/stations):

• Product Families tab: CRUD operations on families
• Products sub-section within each family: CRUD operations on products

Codebase Paths

• Product Families table: database/sql_scripts/tables/ops_product_families.sql
• Products table: database/sql_scripts/tables/ops_products.sql
• Station standards: database/sql_scripts/tables/ops_station_product_standards.sql
• Product functions: database/sql_scripts/functions/ops/ops_product_functions.sql
• Family functions: database/sql_scripts/functions/ops/ops_product_family_functions.sql

See Also

• Product
• Product Family
• Station Product Standards
• Efficiency Calculation