BOM, Material & Labor Intelligence — H&J Fence Supply

Do Not Use When

• Working on UI layout/styling unrelated to BOM or production
• General Drizzle ORM questions (use drizzle-orm-patterns skill instead)
• Jobber API sync issues (use Jobber integration knowledge item)
• Quote Builder features (use sketch-quote-builder knowledge item)

The Three Connected Problems

These three problems form a data pipeline — each one feeds the next. They must be understood together even when working on just one.

Problem 1: BOM Recipes Are Empty

What's broken: The system has 617 BOM recipe headers but zero component lines (bom_items table is empty). The product_variant_id on BOM headers is NULL — they use a legacy finished_product_id string instead of linking to actual product variants.

Why it matters: Without BOM recipes, the system cannot:

• Calculate what raw materials are needed for an order
• Track inventory consumption from historical sales
• Generate purchase orders or reorder alerts
• Tell the shop floor what to cut, route, and assemble

Root cause: BOM headers were auto-generated during an early import. Nobody populated the component lines or linked them to the 5,816 product variants.

Current state:

bill_of_materials: 617 headers (product_variant_id = NULL)
bom_items: 0 rows (completely empty)
product_variants: 5,816 variants
product_mapping_overrides: 7,159 items (116 linked to variants)

Problem 2: No Material Usage Reports

What's broken: Joe can't see consumption trends. Aluminum has a 3-month supplier lead time — if he runs out, production halts for 3 months. He's also the lowest-priced fence supplier in the market and needs data to decide whether to raise prices.

Why it matters: Without consumption data, Joe:

• Can't forecast when to reorder aluminum (3-month lead!)
• Can't calculate true cost per product for pricing decisions
• Can't see margin erosion if supplier costs change
• Is literally guessing on the most critical purchasing decisions

Root cause: The raw data EXISTS in jobber_visits (historical invoice line items synced from Jobber). But nobody built aggregation reports on top of it.

Problem 3: Production Labor Times Are Hardcoded

What's broken: The Production Labor Dashboard calculates hours using hardcoded constants in the code. These are estimates, not measured values:

Why it matters: Inaccurate labor times mean inaccurate staffing, inaccurate cost allocation, and inability to track actual vs. planned performance.

Root cause: The database tables are well-designed but completely empty. The dashboard was built with hardcoded values as a quick MVP.

Data Pipeline Architecture

┌──────────────────────────────────────────────────────────────┐
│                    DATA PIPELINE                              │
├──────────────────────────────────────────────────────────────┤
│                                                              │
│  Jobber Invoice Line Items (raw text names)                  │
│       ↓                                                      │
│  product_mapping_overrides.linked_product_variant_id         │
│       ↓                                                      │
│  Product Variants (5,816 records)                            │
│       ↓                                                      │
│  bill_of_materials → bom_items (raw material components)     │
│       ↓                                                      │
│  Material Usage Reports (consumption, trends, pricing)       │
│       ↓                                                      │
│  production_process_templates → production_process_steps     │
│       ↓                                                      │
│  Production Labor Dashboard (projected vs actual hours)      │
│                                                              │
└──────────────────────────────────────────────────────────────┘

Database Schema — BOM Tables

bill_of_materials (Recipe Headers)

Table: bill_of_materials
Key Columns:
  id                    varchar PK (UUID)
  finished_product_id   varchar       — legacy string ID like "PROD-6FT-WHT-PRIV-PANEL"
  finished_product_type varchar       — 'panel', 'gate', 'post', 'cap', 'hardware'
  product_configuration jsonb         — { color, style, height, materialType }
  product_variant_id    varchar FK    — Links to product_variants.id (CURRENTLY NULL)
  manufacturing_spec_id varchar FK    — Links to manufacturing_specs.id
  component_role        varchar       — Role in assembly
  raw_material_id       varchar FK    — Links to raw_materials.id (for each component)
  quantity_required     numeric       — How many of this raw material
  unit_of_measure       varchar       — 'each', 'feet', 'inches'
  waste_percentage      numeric       — Material waste % (e.g., 5.00)
  is_optional           boolean       — Optional component flag
  assembly_sequence     integer       — Order of assembly
  assembly_notes        text          — Free-form build instructions
  bom_version           integer       — Version tracking
  effective_date        timestamp
  obsolete_date         timestamp

Current state: 617 rows, product_variant_id is NULL everywhere

bom_items (Component Lines)

Table: bom_items
Key Columns:
  id              varchar PK (UUID)
  bom_id          varchar FK → bill_of_materials.id
  raw_material_id varchar FK → raw_materials.id
  quantity         numeric
  unit_of_measure  varchar
  waste_factor     numeric
  assembly_step    integer
  notes            text

Current state: 0 rows (EMPTY)

raw_materials (Inventory Items)

Table: raw_materials
Key Columns:
  id          varchar PK (UUID)
  sku         varchar     — Internal SKU
  name        varchar     — "94\" White Sun Rail", "6' White Picket"
  category    varchar     — 'picket', 'rail', 'post', 'u_channel', 'hardware', 'fastener'
  materialType varchar    — 'vinyl', 'aluminum', 'wood'
  unitCost    numeric     — Cost per unit
  unitOfMeasure varchar   — 'each', 'feet', 'box'

Database Schema — Production Process Tables

production_process_templates (Process Definitions)

Table: production_process_templates
Key Columns:
  id                      varchar PK (UUID)
  name                    varchar     — "6ft Solid Privacy Panel Build"
  product_type            varchar     — 'panel', 'gate', 'post', 'cap', 'hardware', 'railing'
  fence_style             varchar     — 'solid_privacy', 'open_picket', 'semi_privacy', etc.
  fence_height            varchar     — "3'", "4'", "5'", "6'"
  gate_width              varchar     — "34", "46", "58", "70" (gates only)
  material_type           varchar     — 'vinyl', 'aluminum', 'wood', 'chain_link'
  total_standard_seconds  integer     — Cached sum of all step times
  step_count              integer     — Total steps
  is_active               boolean

Current state: 0 rows (EMPTY — schema designed, no data)

production_process_steps (Individual Tasks)

Table: production_process_steps
Key Columns:
  id                    varchar PK (UUID)
  template_id           varchar FK → production_process_templates.id
  step_number           integer     — Sequence order
  step_name             varchar     — "Cut Posts to Height", "Route Picket Holes"
  station_category      varchar     — 'cutting', 'routing', 'assembly', 'packing', 'hardware', 'quality'
  station_terminal_id   varchar FK  — Links to KDS station
  standard_time_seconds integer     — Per-unit time for this step
  setup_time_seconds    integer     — One-time setup per batch
  parallel_group        varchar     — Steps that can run simultaneously
  can_batch_ahead       boolean     — Can be done ahead of time as WIP
  wip_storage_type      varchar     — 'pallet', 'rack', 'bin', 'floor'
  wip_label             varchar     — "Pre-cut 6ft pickets - White"
  labor_category        varchar     — 'skilled', 'general', 'machine_operator'
  tool_required         varchar     — "CNC Router", "Chop Saw"
  router_program_id     varchar FK  — Links to CNC program
  quality_checkpoint    boolean     — QC inspection after this step

Current state: 0 rows (EMPTY)

variant_process_overrides (Per-Variant Exceptions)

Table: variant_process_overrides
Purpose: When a specific variant differs from its template
Example: Almond 4ft panels always need picket cutting (White doesn't)

Key Columns:
  product_variant_id  varchar FK → product_variants.id
  template_id         varchar FK → production_process_templates.id
  override_type       varchar    — 'add_step', 'skip_step', 'modify_time', 'replace_template'
  target_step_number  integer    — Which step to modify
  override_time_seconds integer  — New time (for modify_time)
  reason              text       — "Khaki pickets come in 72in, needs cutting to 48in"

Current state: 0 rows (EMPTY)

Key Files Map

BOM Recipes — How They Should Work

Recipe Structure for a 6ft Solid White Vinyl Panel

Finished Product: "Solid Privacy - PANEL - 6' - White"
  Components (bom_items):
    ├── 15x White Picket (67" cut)    — raw_materials category: 'picket'
    ├── 2x  White Sun Rail (94")      — raw_materials category: 'rail'
    ├── 2x  White U-Channel (94")     — raw_materials category: 'u_channel'
    └── 1x  Hardware pack (screws)    — raw_materials category: 'hardware'
  
  Waste factor: 5% on pickets, 0% on rails/u-channels
  Assembly notes: "Standard 6ft white solid privacy panel"

Recipe Blueprint Engine

The system has an auto-recipe generator at GET /api/manufacturing/recipe-blueprint. Given a style + height, it looks up the manufacturing_specs table and returns:

• Exact picket count, cut length, and router program
• Rail size and quantity
• U-channel dimensions
• Gate kit if applicable

Use this to PRE-POPULATE recipe forms — don't make Joe enter everything manually.

Linking BOM to Product Variants

The bill_of_materials.product_variant_id column MUST be set when creating or updating recipes. This connects the recipe to the production pipeline.

-- Example: Link existing BOM header to a product variant
UPDATE bill_of_materials 
SET product_variant_id = 'variant-uuid-here'
WHERE finished_product_id = 'PROD-6FT-WHT-PRIV-PANEL';

Material Usage Reporting — Data Sources

Primary: Jobber Visit Line Items

The richest consumption data lives in jobber_visits:

-- Each visit has line items as JSONB
SELECT 
  jv.visit_date,
  jv.line_items,         -- JSONB array of products + quantities
  jv.client_name,
  jv.total_amount
FROM jobber_visits jv
WHERE jv.visit_type IN ('production', 'delivery');

Secondary: Product Mapping Links

To aggregate by material type, join through the mapping layer:

-- Get consumption by material type
SELECT 
  pmo.material_type,
  COUNT(*) as line_items,
  SUM(quantity) as total_quantity
FROM product_mapping_overrides pmo
JOIN jobber_invoice_line_items jili ON jili.mapping_id = pmo.id
GROUP BY pmo.material_type;

Aluminum Lead Time Alert

Aluminum has a 3-MONTH supplier lead time. Reports must:

• Show monthly aluminum consumption (units and dollars)
• Calculate current inventory / average monthly consumption = months of supply
• Alert when months of supply < 4 (need to order before 3-month lead time kicks in)

Pricing Analysis

H&J is currently the LOWEST-PRICED fence supplier in their market. Reports need:

• Cost per unit (from raw_materials.unitCost × BOM quantities)
• Current selling price (from product_variants pricing)
• Margin per product
• Historical cost trend (are supplier costs increasing?)
• Comparison framework for competitive positioning

Production Labor — Dashboard Architecture

Current Flow (Hardcoded)

1. GET /api/admin/reports/production-daily
2. Backend fetches jobber_visits for the date range
3. Normalizes product names via regexes
4. Categorizes: panel / gate / post / delivery / pickup
5. Applies HARDCODED time constants:
   - panelVinylQty × 3.0 min
   - gateVinylQty × 20 min
   - postVinylQty × 1.5 min
   - orderCount × 15 min (packing)
6. Returns laborTotals + laborAnalysis to frontend

Target Flow (Database-Driven)

1. GET /api/admin/reports/production-daily
2. Backend fetches jobber_visits for the date range
3. Links line items → product_mapping_overrides → product_variants
4. For each variant, looks up production_process_templates (matched by
   product_type + fence_style + fence_height + material_type)
5. Uses template.total_standard_seconds instead of hardcoded constant
6. Falls back to hardcoded constants if no template exists
7. Returns laborTotals + laborAnalysis to frontend

Labor Calculation Location

All labor math lives in server/routes/reports.routes.ts, starting at line 646. The hardcoded constants are around lines 742-833. When replacing with database lookups, KEEP the hardcoded values as fallbacks until all templates are populated.

Manufacturing Intelligence Integration

Existing Data (Already Populated)

How MIS Feeds BOM

The Recipe Blueprinting Engine (GET /api/manufacturing/recipe-blueprint) uses manufacturing specs to auto-suggest BOM components. This is the bridge between "how it's cut" and "what goes into it."

Example: For a 6ft solid privacy panel, the blueprint engine returns:

• 15 pickets at 67" (from manufacturing_specs)
• Router program 3OP31T (from router_programs)
• 2 rails at 94" (derived from style/height)
• 2 u-channels at 94"

Use this data to pre-fill the BOM recipe form instead of manual entry.

Critical Rules

DO's

• ✅ Link BOM recipes to product_variant_id — not just finished_product_id
• ✅ Use Recipe Blueprint Engine to pre-populate forms
• ✅ Keep hardcoded labor constants as fallbacks
• ✅ Show consumption trends over time, not just totals
• ✅ Alert on aluminum supply < 4 months
• ✅ Read FLIGHT_RULES.md before any schema changes
• ✅ Update HANDOFF.md when done

DON'Ts

• ❌ Do NOT populate production templates with fake or estimated data
• ❌ Do NOT remove hardcoded labor constants until DB has real data
• ❌ Do NOT revert changes to product-mapping.tsx — bulk ops were just fixed
• ❌ Do NOT build reports that require ALL 7,159 items to be linked first
• ❌ Do NOT make Joe enter raw material details he doesn't know

Product Mapping Status

The product mapping page was just fixed (April 13, 2026):

• Bulk "Link All" button is always visible
• Bulk "Not a Product" classification processes all selected items
• All not_a_product items are now status = 'reviewed' (not Needs Review)
• Auto-classifier now sets reviewed status for future not_a_product items

Current mapping status:

Total items:     7,159
not_a_product:   4,041 (all reviewed ✅)
finished_product: 1,803 (need variant linking)
raw_material:    1,315 (need variant linking)
Linked to variant: ~116 (need more linking)

H&J Fence Product Domain Knowledge

Material Types

• Vinyl — Primary product. White, Tan, Khaki, Clay, Almond, Driftwood colors. All panels are assembled in-house from raw components (rails, pickets, u-channels).
• Aluminum — Growing product line. 3-MONTH LEAD TIME from suppliers. Mostly sold as pre-made panels, not assembled in-house.
• Wood — Gates only. Cedar and pressure-treated.
• Chain Link — Small volume. Not assembled in-house.

Product Types

• Panel — Main product. Assembled from rails + pickets + u-channels.
• Post — Routed in-house. End posts, corner posts, line posts.
• Gate — Assembled in-house. Many sizes (34", 46", 58", 70" openings).
• Cap — Post caps. Sold as-is, not manufactured.
• Hardware — Gate kits, screws, hinges. Sold as-is or bundled.

Fence Styles

• solid_privacy (most common)
• semi_privacy
• open_picket (CST, OST variations)
• lattice
• ranch_rail

Standard Heights

• 3' (36")
• 4' (48")
• 5' (60")
• 6' (72") — Most common

Rail Types (Critical Business Context)

• Barrette Flat Rail — Universal rail system, works with most styles
• Sun Rail — Round/tube rail, specific tooling required
• Endura Rail — Premium rail with specific routing programs

Understanding rail type is essential for BOM recipes because different rail types require different router programs, u-channel sizes, and assembly steps.

Conversation: e419d1a5-7d5b-4de4-ade8-1d5a08a4ba65

Reference this conversation for:

• The full data inventory queries
• The "Needs Review" status fix
• The bulk mapping bug fixes
• The architecture analysis and strategic decisions