Section 08
Database — Entity Relationship Diagram
Every table is RLS-protected. Foreign keys link domain entities to the owning user or farm; the has_role() security-definer function gates cross-role reads. The diagram below shows the core entities involved in identity, farm operations, marketplace, logistics, and intelligence.
Identity
users → user_roles → profiles. Roles are stored separately to prevent privilege escalation.
Farm core
farms own livestock and crops; each farm has a verifiable digital identity and QR code.
Commerce
marketplace_listings → buyer_requests / orders → deliveries (with riders), all auto-linked to finance.
Intelligence
analytics, disease_reports, performance scores power outbreak detection and government dashboards.
Auto-link triggers (Finance): harvest sales, egg sales, animal sales, completed deliveries, and paid buyer requests automatically post to the
transactions table — no duplicate entry, no missed income.