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

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

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.