Skip to main content

Trigger System

The F4E AppSheet system relies on a sophisticated trigger system to maintain data consistency and automate complex business processes. This document explains how the trigger system works and the business logic it implements.

Overview​

The system uses 11 critical trigger functions that automatically execute when specific database events occur. These triggers ensure that data remains consistent across all tables and that business rules are enforced automatically.

Trigger Functions Overview​

Trigger FunctionTableEventPurpose
sync_plan_from_meal_planmeal_planUPDATESyncs meal plans to individual kitchen plans
create_or_update_projectionplanUPDATECreates/updates projections when plan status changes
update_projection_on_plan_status_changeplanUPDATEUpdates projection status based on plan status
manage_picking_on_plan_confirmplanUPDATECreates picking lists when plan becomes active
update_plan_from_projectionprojectionUPDATEAggregates projection data to update plan totals
manage_consumption_on_projection_status_changeprojectionUPDATECreates consumption records when projection becomes active
close_projection_on_plan_closeplanUPDATECloses projections when plan is closed
manage_plan_reopenplanUPDATEReopens projections when plan is reopened
handle_plan_status_change_recipe_logplanUPDATELogs recipe changes for audit purposes
handle_school_bom_updateschoolUPDATEUpdates school BOM configuration
set_projection_to_hold_on_plan_changeplanUPDATESets projections to hold status

Core Business Workflows​

1. Meal Plan to Plan Creation Workflow​

When a meal plan is created or updated, the system automatically creates individual plans for each kitchen:

Key Business Rules:

  • Only creates plans when meal plan status is 'Active'
  • Deletes existing plans for the same date and kitchen type
  • Generates unique plan IDs: {kitchen_id}-{date}
  • Sets initial status to 'Planned' and state to 'Running'
  • Meal plan closure sets status to 'Complete'

2. Plan Status Change Workflow​

When a plan status changes, multiple triggers fire to maintain data consistency:

Viewing the Diagram

For better viewing, you can:

  • Right-click and "Open image in new tab" to view larger
  • Use browser zoom (Ctrl/Cmd + Plus) to zoom in
  • View the interactive version by copying the diagram code below

Status Transition Details​

Planned → Open

  • Trigger: create_or_update_projection
  • Actions: Create or update projections for all active schools associated with the plan
  • Business Rule: Only processes schools where active = 'true'

Open → Active

  • Triggers: Multiple automated triggers fire simultaneously:
    • update_projection_on_plan_status_change - Updates projection status to Active
    • manage_picking_on_plan_confirm - Creates picking lists for kitchen operations
    • handle_plan_status_change_recipe_log - Logs recipe changes for audit trail
  • Actions:
    • Update projections to active status
    • Create picking lists for kitchen operations
    • Log any recipe changes for audit trail

Active → Open (Reversal)

  • Triggers: Reversal triggers to undo active state changes
  • Actions:
    • Set projections back to planned status
    • Delete picking lists (no longer needed for inactive plans)

Active → Closed

  • Trigger: close_projection_on_plan_close
  • Actions: Close all associated projections and finalize the plan

Closed → Open (Reopen)

  • Trigger: manage_plan_reopen
  • Actions: Reopen projections and recreate picking lists to restore operational state

3. Projection Creation Workflow​

When a plan changes from 'Planned' to 'Open', projections are created for all active schools:

Key Business Rules:

  • Only processes schools where active = 'true'
  • Generates projection ID: {school_id}-{plan_date}
  • Sets initial status to 'Planned'
  • Updates meal, kitchen_id, and plan_id references

4. Consumption Creation Workflow​

When a projection changes from 'Planned' to 'Active', consumption records are created:

Key Calculations:

  • Total People: projected_students + projected_staff + projected_teachers
  • Planned Quantity: (ingredient_qty × total_people) ÷ 1000
  • BOM Type: Determined by school_bom.bom field

5. Picking List Generation Workflow​

When a plan becomes 'Active', picking lists are generated:

Key Business Rules:

  • Utility items: Always created
  • Fruit items: Only if has_fruit = 'true'
  • Vegetable items: Only if has_vegetable = 'true'
  • Initial quantity: Set to '0', updated by consumption calculations

Critical Calculations​

1. Projection Aggregation​

The update_plan_from_projection function aggregates all projections for a plan:

-- Sum up all projection values
SELECT
    COALESCE(SUM(CAST(projected_students AS INTEGER)), 0),
    COALESCE(SUM(CAST(projected_staff AS INTEGER)), 0),
    COALESCE(SUM(CAST(projected_teachers AS INTEGER)), 0),
    COALESCE(SUM(CAST(actual_students AS INTEGER)), 0),
    COALESCE(SUM(CAST(actual_staff AS INTEGER)), 0),
    COALESCE(SUM(CAST(actual_teachers AS INTEGER)), 0)
FROM projection
WHERE plan_id = NEW.plan_id;

2. Consumption Quantity Calculation​

The manage_consumption_on_projection_status_change function calculates ingredient quantities:

-- Calculate planned quantity per ingredient
COALESCE(CAST(i.qty AS NUMERIC), 0) * (
    COALESCE(CAST(NULLIF(NEW.projected_students, '') AS NUMERIC), 0) +
    COALESCE(CAST(NULLIF(NEW.projected_staff, '') AS NUMERIC), 0) +
    COALESCE(CAST(NULLIF(NEW.projected_teachers, '') AS NUMERIC), 0)
) / 1000 AS planned_qty

3. Picking Quantity Aggregation​

The system aggregates consumption quantities for picking lists:

-- Sum consumption quantities for picking
SELECT CEIL(SUM(c.planned_qty::NUMERIC))
FROM consumption c
WHERE c.plan_id = pk.plan_id
  AND c.item = pk.product_id
  AND c.status = 'Open'

Data Integrity Rules​

1. Status Flow Validation​

The system enforces strict status flows:

  • Plan Status: Planned → Open → Active → Closed
  • Projection Status: Planned → Active → Closed
  • Consumption Status: Open (created when projection becomes Active)
  • Picking Status: Open (created when plan becomes Active)

2. Business Rule Enforcement​

  • School Eligibility: Only active schools (active = 'true') are processed
  • Unique IDs: All records have unique, generated IDs
  • Referential Integrity: Foreign key constraints ensure data consistency
  • Audit Trails: All changes tracked with timestamps and user attribution

3. Cascade Prevention​

The system includes cascade prevention to avoid infinite loops:

-- Prevent cascade: skip if this is a recursive call
IF TG_LEVEL > 1 THEN RETURN NEW; END IF;

Error Handling​

1. Data Validation​

  • NULL Handling: Uses COALESCE and NULLIF for safe data conversion
  • Type Conversion: Safely converts VARCHAR to NUMERIC with error handling
  • Constraint Validation: Database constraints prevent invalid data

2. Transaction Management​

  • Atomic Operations: All trigger operations are atomic
  • Rollback Capability: Failed operations are automatically rolled back
  • Error Logging: Errors are logged for debugging and monitoring

Performance Considerations​

1. Trigger Optimization​

  • Bulk Operations: Uses INSERT ... SELECT and UPDATE ... FROM for efficiency
  • Index Usage: Leverages database indexes for fast lookups
  • Cascade Prevention: Prevents infinite recursive calls

2. Query Optimization​

  • Materialized Views: Pre-calculated aggregations for performance
  • Strategic Indexing: Indexes on frequently queried columns
  • Query Planning: Optimized query execution plans

This trigger system ensures that the F4E AppSheet system maintains data consistency and enforces business rules automatically, providing a robust foundation for kitchen operations management.