Database Model Documentation

This document provides a comprehensive overview of the Sharing Excess food rescue platform's database model. It is designed to help LLMs and agentic systems translate non-technical questions into accurate SQL queries that align with the application's data patterns.

Overview

Sharing Excess is a food rescue organization that coordinates the collection and distribution of surplus food between donors (restaurants, farms, retailers, etc.) and recipients (food banks, shelters, community organizations, etc.). The platform tracks rescues (food collection events), transfers (individual pickups/deliveries), partners, locations, and users.

Core Entities and Relationships

Primary Entities

Partners - Organizations that donate or receive food
Locations - Physical addresses where food is picked up or delivered
Rescues - Food collection events that coordinate multiple transfers
Transfers - Individual pickups or deliveries of food
Users - People who use the platform
Wholesale Markets - Special locations for wholesale food rescues

Key Relationships

Partners have multiple Locations (one-to-many)
Rescues contain multiple Transfers (one-to-many)
Transfers connect Partners and Locations (many-to-many through transfers)
Users can be associated with Partners (many-to-one)
Users can handle Rescues and Transfers (many-to-many)

Detailed Schema

Partners Table (partners)

Purpose: Organizations that participate in food rescue (donors and recipients)

Key Fields:

id (text, primary key) - Unique identifier
name (text, unique) - Organization name
type (enum) - Either 'donor' or 'recipient'
donor_category (enum, nullable) - Required when type='donor'
recipient_category (enum, nullable) - Required when type='recipient'
about (text, nullable) - Description of the organization
contact_name, contact_email, contact_phone (text, nullable) - Contact information
website_url, facebook_handle, instagram_handle (text, nullable) - Social media
internal_notes (text, nullable) - Internal staff notes
is_deleted (boolean) - Soft delete flag

Enums:

partner_type: ['donor', 'recipient']
donor_category: ['farm', 'retailer', 'wholesaler', 'manufacturer', 'restaurant', 'holding', 'other']
recipient_category: ['community_organization', 'food_bank', 'shelter', 'school', 'chapter', 'pop_up', 'healthcare_center', 'community_fridge', 'religious_organization', 'home_delivery_provider', 'holding', 'compost', 'other']

Business Rules:

Donor partners must have a donor_category
Recipient partners must have a recipient_category
Partner names must be unique

Locations Table (locations)

Purpose: Physical addresses where food is picked up or delivered

Key Fields:

id (text, primary key) - Unique identifier
partner_id (text, foreign key) - References partners.id
nickname (text) - Human-readable name for the location
address1, address2, city, state, zip (text) - Address components
country (enum) - Country code (ISO 3166-1 alpha-2)
lat, lng (real) - GPS coordinates
timezone (text) - IANA timezone identifier
contact_name, contact_email, contact_phone (text, nullable) - Location-specific contacts
notes (text, nullable) - Additional information
is_deleted (boolean) - Soft delete flag

Business Rules:

Each location belongs to exactly one partner
GPS coordinates are required
Timezone must be a valid IANA timezone

Rescues Table (rescues)

Purpose: Food collection events that coordinate multiple transfers

Key Fields:

id (text, primary key) - Unique identifier
type (enum) - Type of rescue operation
status (enum) - Current status of the rescue
scheduled_at (timestamp) - When the rescue is scheduled
completed_at (timestamp, nullable) - When the rescue was completed
handler_email (text, nullable) - Email of person handling the rescue
handler_id (text, nullable) - User ID of person handling the rescue
wholesale_market_id (text, nullable) - References wholesale_markets.id
notes (text, nullable) - Additional information

Enums:

rescue_type: ['retail', 'wholesale', 'direct_link']
rescue_status: ['scheduled', 'active', 'completed', 'cancelled']

Business Rules:

Active/completed rescues must have a handler
Completed rescues must have a completed_at timestamp
Scheduled rescues cannot have a completed_at timestamp

Transfers Table (transfers)

Purpose: Individual pickups or deliveries of food within a rescue

Key Fields:

id (text, primary key) - Unique identifier
rescue_id (text, foreign key) - References rescues.id
partner_id (text, foreign key) - References partners.id
location_id (text, foreign key) - References locations.id
type (enum) - Either 'collection' or 'distribution'
status (enum) - Current status of the transfer
sequence (integer) - Order within the rescue
total_weight (real, nullable) - Total weight in pounds
categorized_weight (json, nullable) - Weight breakdown by food category
percent_of_total_dropped (real, nullable) - Percentage of total food dropped
handler_email (text, nullable) - Email of person handling the transfer
handler_id (text, nullable) - User ID of person handling the transfer
completed_at (timestamp, nullable) - When the transfer was completed
notes (text, nullable) - Additional information

Enums:

transfer_type: ['collection', 'distribution']
transfer_status: ['scheduled', 'active', 'completed', 'cancelled']

JSON Structure for categorized_weight:

{
  "dairy": 0.0,
  "bakery": 0.0,
  "produce": 0.0,
  "meat_fish": 0.0,
  "non_perishable": 0.0,
  "prepared_frozen": 0.0,
  "mixed": 0.0,
  "other": 0.0
}

Business Rules:

Completed transfers must have total_weight, categorized_weight, and a handler
total_weight must equal the sum of categorized_weight values
percent_of_total_dropped only applies to distribution transfers
Collection transfers cannot have percent_of_total_dropped

Users Table (users)

Purpose: People who use the platform

Key Fields:

email (text, primary key) - User's email address
id (text, unique) - Internal user ID
name (text) - User's full name
permission (enum) - User's permission level
partner_id (text, nullable) - References partners.id
phone (text, nullable) - Phone number
icon (text, nullable) - Profile picture path

Enums:

permission: ['none', 'standard', 'partner', 'admin', 'advisor']

Business Rules:

Only users with 'partner' permission can have a partner_id
Non-partner users must have partner_id as null

Supporting Tables

Location Hours (location_hours)

location_id → locations.id
day_of_week (0-6, where 0=Sunday)
type (enum: ['receiving', 'business'])
start_time, end_time (text, format: "HH:MM")

Location Tags (location_tags)

location_id → locations.id
label (text) - Tag name

Partner Images (partner_images)

partner_id → partners.id
image_file_pathname (text) - Path to image file

Partner Tags (partner_tags)

partner_id → partners.id
label (text) - Tag name

Wholesale Markets (wholesale_markets)

id (text, primary key)
name (text, unique) - Market name

Common Query Patterns

Finding Partners by Type

-- All donor partners SELECT * FROM partners WHERE type = 'donor' AND is_deleted = false;

-- All recipient partners SELECT * FROM partners WHERE type = 'recipient' AND is_deleted = false;

-- Partners by specific category SELECT * FROM partners WHERE donor_category = 'restaurant' AND is_deleted = false;

Finding Transfers by Status

-- Recent completed transfers SELECT * FROM transfers WHERE status = 'completed' ORDER BY completed_at DESC LIMIT 10;

-- Active transfers SELECT * FROM transfers WHERE status = 'active';

Weight and Impact Calculations

-- Total food rescued (completed transfers only) SELECT SUM(total_weight) FROM transfers WHERE status = 'completed';

-- Food rescued by category SELECT SUM(categorized_weight->>'produce') as produce_weight, SUM(categorized_weight->>'dairy') as dairy_weight FROM transfers WHERE status = 'completed';

Partner-Location Relationships

-- All locations for a specific partner SELECT l.* FROM locations l JOIN partners p ON l.partner_id = p.id WHERE p.name = 'Partner Name' AND l.is_deleted = false;

-- Partners with their location counts SELECT p.name, COUNT(l.id) as location_count FROM partners p LEFT JOIN locations l ON p.id = l.partner_id AND l.is_deleted = false WHERE p.is_deleted = false GROUP BY p.id, p.name;

Rescue-Transfer Relationships

-- Transfers for a specific rescue SELECT t.*, p.name as partner_name, l.nickname as location_name FROM transfers t JOIN partners p ON t.partner_id = p.id JOIN locations l ON t.location_id = l.id WHERE t.rescue_id = 'rescue_id_here';

-- Rescues with transfer counts SELECT r.*, COUNT(t.id) as transfer_count FROM rescues r LEFT JOIN transfers t ON r.id = t.rescue_id GROUP BY r.id;

Time-Based Queries

-- Transfers completed in the last 30 days SELECT * FROM transfers WHERE status = 'completed' AND completed_at >= NOW() - INTERVAL '30 days';

-- Rescues scheduled for today SELECT * FROM rescues WHERE DATE(scheduled_at) = CURRENT_DATE;

Common Question Translations

"How much food did we rescue last month?"

Translation: Sum of total_weight from completed transfers in the last month SELECT SUM(total_weight) as total_rescued FROM transfers WHERE status = 'completed' AND completed_at >= DATE_TRUNC('month', NOW() - INTERVAL '1 month') AND completed_at < DATE_TRUNC('month', NOW());

"Which restaurants donate the most food?"

Translation: Partners with type='donor' and donor_category='restaurant', ordered by total weight SELECT p.name, SUM(t.total_weight) as total_donated FROM partners p JOIN transfers t ON p.id = t.partner_id WHERE p.type = 'donor' AND p.donor_category = 'restaurant' AND t.status = 'completed' AND p.is_deleted = false GROUP BY p.id, p.name ORDER BY total_donated DESC;

"How many food banks do we work with?"

Translation: Count of partners with type='recipient' and recipient_category='food_bank' SELECT COUNT(*) as food_bank_count FROM partners WHERE type = 'recipient' AND recipient_category = 'food_bank' AND is_deleted = false;

"What's our most active rescue location?"

Translation: Location with the most completed transfers SELECT l.nickname, p.name as partner_name, COUNT(t.id) as transfer_count FROM locations l JOIN partners p ON l.partner_id = p.id JOIN transfers t ON l.id = t.location_id WHERE t.status = 'completed' AND l.is_deleted = false GROUP BY l.id, l.nickname, p.name ORDER BY transfer_count DESC LIMIT 1;

Important Notes for LLMs

Soft Deletes: Most tables use is_deleted flags rather than hard deletes. Always filter by is_deleted = false unless specifically looking for deleted records.
Timestamps: All tables have created_at and updated_at timestamps. Use these for time-based queries.
Foreign Keys: Always use proper JOINs rather than string concatenation for relationships.
Weight Units: All weights are stored in pounds (real numbers).
Status Transitions: Understand the business logic for status changes:
- Rescues: scheduled → active → completed
- Transfers: scheduled → active → completed
Handler Requirements: Active/completed rescues and transfers must have handlers assigned.
Categorized Weight: This JSON field contains detailed breakdowns but only for completed transfers.
Timezone Awareness: Locations have timezone information - consider this for time-based queries.
Sequence Numbers: Transfers within a rescue have sequence numbers indicating their order.
Partner Types: Partners are either donors or recipients, never both. The category fields are mutually exclusive based on type.

Security Considerations

Only SELECT queries are allowed in the AI chat interface
All queries are executed against a read replica
No write operations are permitted through the AI interface
Queries are validated for dangerous patterns before execution

This documentation should help LLMs understand the data model and write accurate, useful queries that provide meaningful insights about the food rescue operations.

On this page