# Recipe, Process, And Knowledge Schema

This is the first-pass data model for a scientist-facing R&D workbench. It is intentionally practical: capture enough structure to make experiments searchable and reusable, without forcing scientists into a heavy PLM/LIMS replacement.

## Core Objects

### Product

Represents the commercial or R&D product context.

- `product_id`
- `brand`
- `product_family` such as soap, shampoo, deodorant, laundry
- `market`
- `format` such as bar, liquid, gel, powder
- `target_claims`
- `constraints` such as cost, regulatory, ingredient availability

### Recipe Version

Represents the formulation scientists start from.

- `recipe_id`
- `version`
- `product_id`
- `status` such as draft, trial, approved, archived
- `batch_scale`
- `target_batch_size`
- `base_recipe_reference`
- `ingredient_lines`
- `process_template_id`
- `quality_targets`
- `known_risks`
- `owner`
- `created_at`

### Ingredient Line

Represents one material in the recipe.

- `ingredient_id`
- `material_code`
- `display_name`
- `functional_role` such as surfactant, binder, fragrance, colorant, preservative
- `supplier`
- `supplier_lot`
- `quantity`
- `unit`
- `concentration_percent`
- `spec_range`
- `substitution_group`
- `regulatory_notes`
- `cost_notes`
- `known_sensitivities`

### Process Template

Represents the standard process that can be reused across recipes.

- `process_template_id`
- `name`
- `site`
- `equipment_train`
- `steps`
- `critical_process_parameters`
- `normal_operating_ranges`
- `cleaning_or_changeover_notes`

### Process Step

Represents one operation in the process.

- `step_id`
- `process_template_id`
- `sequence`
- `operation_type` such as charge, heat, mix, hold, cool, mill, dry, pack
- `description`
- `input_materials`
- `equipment_id`
- `planned_parameters`
- `actual_parameters`
- `control_strategy`
- `operator_notes`
- `start_condition`
- `end_condition`

### Process Parameter

Represents a value controlled or observed during a process step.

- `parameter_id`
- `step_id`
- `name` such as temperature, pressure, RPM, residence time, pH, vacuum
- `planned_setpoint`
- `planned_range_min`
- `planned_range_max`
- `actual_value`
- `actual_range_min`
- `actual_range_max`
- `unit`
- `source` such as manual entry, sensor, historian, calculated
- `criticality` such as low, medium, high
- `rationale`

### Equipment

Represents process hardware.

- `equipment_id`
- `equipment_type`
- `site`
- `capacity`
- `geometry_notes`
- `control_capabilities`
- `sensor_tags`
- `calibration_status`
- `known_equipment_effects`

### Experiment Run

Represents a scientist's planned or completed trial.

- `experiment_id`
- `recipe_id`
- `objective`
- `hypothesis`
- `reason_for_trial` such as vendor change, cost saving, quality issue, new claim
- `scientist`
- `operator`
- `site`
- `planned_batch_ids`
- `actual_batch_ids`
- `changed_variables`
- `controlled_variables`
- `deviations`
- `measurements`
- `outcome_summary`
- `decision`
- `linked_rules`
- `status`

### Deviation Or Observation

Represents something that happened outside the clean plan.

- `observation_id`
- `experiment_id`
- `step_id`
- `timestamp`
- `type` such as deviation, note, issue, operator feedback
- `description`
- `suspected_cause`
- `action_taken`
- `impact`
- `severity`
- `follow_up`

### Quality Measurement

Represents a measured result.

- `measurement_id`
- `experiment_id`
- `batch_id`
- `sample_id`
- `metric_name` such as hardness, viscosity, pH, moisture, color, stability
- `method`
- `instrument`
- `sample_time`
- `value`
- `unit`
- `target_min`
- `target_max`
- `pass_fail`
- `raw_file_reference`
- `analyst_notes`

### Knowledge Rule

Represents reusable empirical know-how.

- `rule_id`
- `scope` such as product family, recipe, ingredient, equipment, site
- `condition`
- `recommendation`
- `expected_impact`
- `confidence`
- `evidence_experiments`
- `exceptions`
- `owner`
- `last_validated_at`
- `status`

Example:

```json
{
  "scope": "Soap bars / mixer MX-12 / surfactant vendor change",
  "condition": "Hardness below target after supplier lot change",
  "recommendation": "Increase mixer jacket temperature by 5 deg C during the hold step",
  "expected_impact": "Improves hardness while keeping viscosity in range",
  "confidence": "medium",
  "evidence_experiments": ["EXP-2026-014", "EXP-2026-021"],
  "exceptions": "Do not apply when fragrance load exceeds 1.2 percent"
}
```

### External Data Link

Represents a read-only pointer into an existing enterprise system.

- `external_link_id`
- `system` such as SAP, PLM, LIMS, data lake, historian, Power BI
- `object_type`
- `external_id`
- `display_name`
- `sync_method` such as API, export, replica, manual
- `last_synced_at`
- `linked_object_id`

## Search Questions The Schema Must Answer

- Which experiments changed this ingredient, supplier, or lot?
- What happened to hardness when temperature, RPM, pressure, or residence time changed?
- Which previous batches used the same recipe and equipment?
- Which process parameters were different between a passing and failing batch?
- What empirical rules exist for this product family or equipment?
- Which rules are backed by which experiments?
- What quality tests were run, with what methods and instruments?
- What external SAP/LIMS/data lake records support this answer?
- Which scientists or teams previously worked on similar issues?

## Visual Editor Blocks

The editor should expose the schema as domain blocks, not database tables.

- Recipe Block: product, version, scale, status, targets
- Ingredient Block: material, supplier, quantity, function, sensitivity
- Process Step Block: operation, equipment, input materials, sequence
- Parameter Block: temperature, RPM, pressure, time, pH, range, criticality
- Measurement Block: metric, method, target, result, pass/fail
- Observation Block: deviation, issue, action taken, impact
- Rule Block: condition, recommendation, evidence, confidence
- External Data Block: SAP/LIMS/data lake/historian reference

The scientist-facing model should feel like arranging and editing these blocks. The database model can stay hidden underneath.