Local Food AI CAPSTONE PROJECT

Local Food AI Platform

Complete Technical Architecture, Development Lifecycle, and Security Governance Overview

Audience IT & Technical Team

Lead Engineer Francois Lange

Version ID 2a8ed056 (main)

System Design IT REQUIREMENTS

Core Requirements Analysis & Assessment

100% Privacy & Data Sovereignty: Absolute enforcement of an air-gapped system. Patient parameters, query strings, and custom food plans must never escape the local subnet.
Sub-second Database Response: Fast SQL lookup queries against the 24GB OpenFoodFacts dataset (with over 100+ raw schema parameters).
Offline LLM Inference Capabilities: Local AI reasoning model supporting clinical tool-calling without internet-based APIs.

🛡️ HIPAA Guard

No external API leaks. Prompt parameters remain in internal RAM pools.

📈 High Efficiency

Sub-second database responses via optimized views and B-Tree indexing.

Architecture Diagram LOCAL INFRASTRUCTURE

Distributed Local Component Communication

Nginx Proxy routes all browser traffic to Streamlit.
Streamlit issues optimized view queries directly to MySQL.
RAG flows communicate locally with Ollama.
Zabbix monitors all health points locally via secure SNMPv3 channels.

Database Performance VERTICAL PARTITIONING

Database Design: Grouped Partitioning

Vertically Partitioned Schema: To split the massive OpenFoodFacts attributes and avoid InnoDB row size limits, the data is partitioned across 5 tables: core, allergens, macros, vitamins, minerals.
Unified SQL View: A single joined view presents records to the Streamlit app seamlessly.
FULLTEXT Search Indexes: Added to the `products_core` title column to bypass standard slow string matches.

Performance Comparison

Standard JOIN

> 4.20s

→

Optimized Index

< 0.04s

Sub-second response times achieved via strategic index bindings!

Artificial Intelligence LOCAL LLM / RAG

Local LLM Choice & RAG Tool-Calling

Quantized `llama3.2:3b` Model: Runs completely locally under Ollama daemon. Consumes only 2.2 GB of RAM, fitting within the 16 GB hardware constraints.
Chain-of-Thought (CoT): System prompts enforce XML-wrapped thinking tags (`...`) to explain dietary constraints.
Autonomous Tool Calling: Enables the LLM to trigger two local tools:
1. `search_nutrition_db` (direct SQL macro retrieval)
2. `local_web_search` (covert SearXNG search proxy)

# Ollama Tool Binding Example in app.py
response = ollama.chat(
    model='llama3.2:3b',
    messages=st.session_state.messages,
    tools=[search_nutrition_db, local_web_search]
)
# Returns structured arguments to 
# query vertical database views!

Development Operations ANTIGRAVITY AGENTS

AI Agent Orchestration: Antigravity Subagents

🎓 Expert Coach

Reviewed vertical partition schemas, verified dynamic formats, and checked code complexity indices.

📝 Doc Writer

Maintained operational guides and manuals in `/docs`, syncing them with codebase modifications.

🖇️ Git Commit

Linked local commits to Gogs and triggered automatic updates to the Taiga task board.

🛠️ SQL Optimizer

Created database indices, vertical division schemas, and optimized JOIN layouts on unified views.

🔍 Code Reviewer

Analyzed python scripts (`app.py`, `ingest_csv.py`) to resolve encoding issues and runtime errors.

Security Governance POLP BOUNDARIES

Agent Permissions & Security Boundaries

Principle of Least Privilege (PoLP): Configured tight boundaries to ensure the AI agent could not access sensitive host directory structures or execute unchecked network calls.
Path Lock constraints: Restricted file read and write operations exclusively to the local workspace folder.
Shell commands limits: Prevented shell command executes. Limited commands exclusively to `git`, `python`, `chmod`, `docker-compose`, and `Get-Content`.

⚠️ Network Guardrails

Network reads and executions were restricted solely to:

- 192.168.130.170 (Docker host)
- 192.168.130.161 (Taiga API)

Zero external DNS queries or telemetry leaks allowed!

Lessons Learned REFLECTIONS

Reflections: Engineering Struggles & Solutions

1. Regex Greediness

Struggle: The smudge regex was too greedy and corrupted python literals across lines.
Fix: Line-restricted the pattern (`[^\r\n$]+\$`) and split literals in the script.

2. Filter Self-Delete

Struggle: Git checkout deleted the smudge script first, causing subsequent smudges to crash.
Fix: Sequential checkout checking out the filter script before other files.

3. Accent Encoding

Struggle: French accents in git metadata logs broke code writers under Windows codepages.
Fix: Sanitizers processing python strings with `errors='replace'` writing to UTF-8.

DevOps Telemetry ZABBIX & SNMPv3

Enterprise Observability & Real-Time Alerts

Secure SNMPv3 Architecture: Set up encrypted monitoring with username `securityUser` utilizing SHA verification and AES encryption keys.
Dynamic Telemetry Daemon: `zabbix_telemetry.py` polls system status indices and forwards database states.
Application Traps (`snmp_notifier.py`): Directly triggers SNMP traps on major events:
- Failed credential attempts
- Password reset actions
- Database index delays

💬 Active Webhook Routing

Zabbix alerts parse the trap and automatically forward notification packets to Teams and Discord webhooks:

{
  "event": "SNMPv3 Trap Alert",
  "node": "WSL-Workstation",
  "status": "PROBLEM",
  "desc": "High CPU utilization detected"
}

Security Audit DATA PRIVACY VERIFIED

Data Privacy Audit: 100% Air-Gapped

Access Logs Verification: Audited Nginx reverse proxy configurations to confirm all user requests originate inside the local subnet range.
Container Network Separation: Database has no public port mappings. Runs in an isolated Docker bridge container network.
SearXNG Scraper Proxy: Strip tracking tags and user cookies prior to querying external domains.

🔒 TCPDump Network Sniffing Audit

Ran packet sniffing audits during intensive chat inference RAG queries:

$ tcpdump -i eth0 dst port not 80 \
  and dst port not 22 and dst port not 161
# Result: ZERO packets sent outbound to external IPs!

Audited verification confirms no data escapes the host server!

Production Maintenance FUTURE PLANNING

Future Work & Day 2 Operations

🔑 SSL/TLS Encryption

Upgrade the Nginx configuration to support HTTPS (Port 443) using the organization's root security authority to encrypt local workstation networks.

🧭 Database Migrations

Rely strictly on Alembic scripts to roll out subsequent tables, preventing manual schema modifications from breaking view joins.

⏱️ Rate Limiting

Implement a sliding-window throttling algorithm in `app.py` to prevent potential clinician local server resource exhaustion.

🧪 UAT Iteration Loops

Incorporate clinical dietitian feedback to adjust nutritional threshold recommendations and warning tags.