✓ build@food4education.org
Skip to main content

System Architecture

The F4E AppSheet system is built on a robust, trigger-driven architecture that ensures data consistency and automates complex business processes. This document provides a comprehensive overview of the system's technical architecture.

High-Level Architecture​

Database Schema Overview​

The system is built around 16 core tables that handle different aspects of kitchen operations:

Core Tables​

TablePurposeKey Relationships
public.kitchenKitchen master dataLinks to plan, projection
public.schoolSchool master dataLinks to projection, school_bom
meal_planCentral meal planningLinks to plan
planKitchen-specific plansCentral table, links to all others
projectionSchool-level projectionsLinks to plan, consumption
consumptionIngredient consumptionLinks to projection, picking
pickingPicking list managementLinks to plan, consumption
product_masterProduct catalogLinks to consumption, picking
ingredientsRecipe ingredientsLinks to consumption
school_bomSchool BOM configurationLinks to school, ingredients

Supporting Tables​

TablePurposeKey Relationships
stock_adjustmentInventory adjustmentsLinks to kitchen
stock_countPhysical stock countsLinks to kitchen
stock_summaryInventory summariesLinks to kitchen
plan_recipe_logRecipe execution logsLinks to plan
kitchen_mappingKitchen managementLinks to kitchen
mealsMeal catalogLinks to ingredients

⚡ Trigger System Architecture​

The system uses 11 critical trigger functions to maintain data consistency and automate business processes:

Plan Status Triggers​

Projection Triggers​

Data Sync Triggers​

Data Flow Architecture​

1. Meal Plan Creation Flow​

2. Plan Status Change Flow​

Database Design Principles​

1. Normalization​

  • 3NF Compliance: All tables follow third normal form
  • Referential Integrity: Foreign key constraints ensure data consistency
  • Atomic Values: Each field contains a single, atomic value

2. Performance Optimization​

  • Strategic Indexing: Indexes on frequently queried columns
  • Materialized Views: Pre-calculated aggregations for performance
  • Trigger Optimization: Efficient trigger functions with cascade prevention

3. Data Integrity​

  • Audit Trails: All tables include created_at, updated_at, created_by, updated_by
  • Status Validation: Business rules enforced through triggers
  • Data Validation: Input validation at both application and database levels

Security Architecture​

1. Access Control​

  • Role-Based Access: Different access levels for different user roles
  • Data Partitioning: Users only see data relevant to their role
  • Audit Logging: All changes tracked with user attribution

2. Data Protection​

  • Encryption: Sensitive data encrypted in transit and at rest
  • Backup Strategy: Regular automated backups
  • Recovery Procedures: Point-in-time recovery capabilities

Integration Architecture​

1. External System Integration​

2. Sync Processes​

  • Real-time Sync: Critical data synchronized immediately
  • Batch Sync: Non-critical data synchronized in batches
  • Conflict Resolution: Automated conflict resolution strategies

Scalability Considerations​

1. Performance​

  • Database Optimization: Query optimization and indexing
  • Caching Strategy: Materialized views for frequently accessed data
  • Load Balancing: Distributed processing for high-volume operations

2. Growth​

  • Horizontal Scaling: Database partitioning strategies
  • Vertical Scaling: Resource allocation optimization
  • Monitoring: Performance monitoring and alerting

Automation & Bots​

The F4E AppSheet system includes several automated bots that handle critical business processes and notifications. These bots ensure timely communication and document generation without manual intervention.

Bot Architecture​

Bot Functions​

1. Plan to Active - Generate PDF Bot​

  • Trigger: When plan status changes from "Open" to "Active"
  • Action: Generates PDF documents for meal plans
  • Template: Custom Google Doc template (DocId: 1e5igEZvAdoSlATJ0jAq2fy-H2bKD4BtmcuE77bBLs_A)
  • File Storage: appsheet-recipes file store
  • Naming Convention: \{Kitchen_Name\}_Plan_\{Plan_ID\}_\{Date\}_\{Meal_Type\}.pdf

2. Generate Delivery Note Bot​

  • Trigger: DNote Generation event
  • Action: Creates delivery notes for orders
  • Template: Custom Google Doc template (DocId: 1H4nlXJIbC8stY7vcDWaoZsVyLiPJS-qzIgQlRX5_aCA)
  • File Storage: Default file store in /appsheet/data/kitchen_ops_training-357142272/Files/orders
  • Naming Convention: \{Kitchen_Name\}- Order\{Order_Number\} - \{Date\}

3. Notify Warehouse Bot​

  • Trigger: Order posted event
  • Action: Sends email notification to warehouse managers
  • Recipients: Warehouse managers (role = "WHSE")
  • Email Template: Order details with ingredient quantities
  • Subject: "Order {order_no} Created"

4. Notify Kitchen Bot​

  • Trigger: Order issued event
  • Action: Sends email notification to kitchen managers
  • Recipients: Kitchen managers for specific kitchen
  • Email Template: Order details with issued quantities
  • Subject: "Order {order_no} Issued"

Email Configuration​

All bot-generated emails and support communications are configured with centralized email management:

Bot Security & Permissions​

  • Trigger Security: Bots bypass security filters when necessary for system operations
  • Data Access: Bots have appropriate permissions to access required tables
  • Audit Trail: All bot actions are logged with timestamps and user attribution
  • Error Handling: Failed bot operations are logged and can be retried

Technical Stack​

ComponentTechnologyPurpose
Mobile AppAppSheetCross-platform mobile application
DatabasePostgreSQL 13+Primary data storage
BackendAppSheet PlatformBusiness logic and API
IntegrationGoogle WorkspaceAuthentication and collaboration
MonitoringBuilt-in AnalyticsPerformance and usage tracking
AutomationAppSheet BotsAutomated workflows and notifications

Deployment Architecture​

1. Environment Strategy​

  • Development: Local development environment (Google sheets)
  • Staging: Pre-production testing environment (Postgres DB)
  • Production: Live system environment

2. Data Migration​

  • Version Control: Database schema versioning
  • Migration Scripts: Automated database migrations
  • Rollback Procedures: Safe rollback capabilities

This architecture ensures the F4E AppSheet system is robust, scalable, and maintainable while providing excellent performance for all user roles and business processes.