Owner: TBD | Last updated: 2026-01-09 | Status: Draft

Data Model & Schemas

Purpose

Document ERD-level entities, migration strategy, and seed data.

Data DB Entities

• locations: Holds addresses of relevant locations

  • location_id
  • name
  • type
  • address_street
  • city
  • state
  • zip
  • phone
  • latitude
  • longitude
  • avg_load_minutes
  • avg_unload_minutes

• demand

  • demand_id
  • location_id
  • product_code
  • quantity_needed
  • max_price

• vehicles: Holds vehicle information

  • vehicle_id
  • name
  • mpg
  • depreciation_per_mile
  • annual_insurance_cost
  • annual_maintenance_cost
  • max_weight_lbs
  • max_volume_cubic_ft
  • storage_capability

• products_master: Junction table to organize supply and demand by product type

  • product_code
  • name
  • storage_type

• manifest_items: Items loaded on a single shipment

  • manifest_item_id
  • scenario_id
  • supply_id
  • demand_id
  • quantity_loaded
  • snapshot_cost_per_item
  • snapshot_items_per_unit
  • snapshot_unit_weight

• supply: Available goods at warehouse (ignored for MVP, used in optimization)

  • supply_id
  • entity_id
  • location_id
  • product_code
  • quantity_available
  • unit_weight_lbs
  • unit_volume_cu_ft
  • items_per_handling_unit
  • cost_per_item

• entities: Specific farm info (ignored in MVP, used for potential profit share)

  • entity_id
  • name
  • entity_min_profit

• routes: Holds information for a specific route to facilitate different product mixes to and from the same locations.

  • route_id
  • name
  • origin_location_id
  • dest_location_id

• drivers: Holds information on driver wages

  • driver_id
  • name
  • hourly_drive_wage
  • hourly_load_wage

• scenarios: Holds information on one specific run of a route.

  • scenario_id
  • route_id
  • vehicle_id
  • driver_id
  • run_date
  • created_at
  • snapshot_driver_wage
  • snapshot_driver_load_wage
  • snapshot_vehicle_mpg
  • snapshot_gas_price
  • snapshot_daily_insurance
  • snapshot_planned_load_minutes
  • snapshot_planned_unload_minutes
  • actual_load_minutes
  • actual_unload_minutes

Relationships (ERD notes)

• TBD

Migrations strategy

• Tool: TBD
• Rules: TBD

Seed data

Seed Data: Synthetic Dataset Generation

This procedure (generate_synthetic_data) initializes the database with a clean, realistic synthetic dataset for development, testing, and demos. It fully resets all core tables and repopulates them with consistent, interrelated data representing a small regional food logistics network.

---

Reset Behavior

• Temporarily disables foreign key checks
• Truncates all core tables to ensure a clean state:
  • manifest_items
  • scenarios
  • routes
  • demand
  • supply
  • entities
  • vehicles
  • drivers
  • locations
  • products_master
• Re-enables foreign key checks after reset

---

1. Products

Defines a small catalog of products with differing storage requirements:

• Broccoli Crowns (Refrigerated)
• Russet Potatoes (Dry)
• Apples (Refrigerated)
• Ground Beef (Frozen)

These storage types are later used to validate vehicle compatibility.

---

2. Locations

Creates a realistic geographic network with operational metadata:

• Hub
  • Portland Central Hub (fast load/unload times)
• Farms
  • Green River Farm (vegetables)
  • Red Barn Orchards (fruit)
  • Valley Beef Ranch (meat)
• Stores
  • City Market Downtown
  • Suburb Grocer
  • Co-op Eastside

Each location includes:

• Full address and contact info
• Latitude/longitude for routing
• Average load and unload times (minutes), reflecting operational differences between farms, hubs, and stores

---

3. Entities

Represents supplier organizations with minimum profit constraints:

• Green River Co-op (no minimum profit)
• Red Barn Orchards LLC (moderate minimum profit)
• Valley Ranchers Inc (higher minimum profit)

These constraints are intended to influence optimization logic.

---

4. Assets

Drivers

Creates a small driver pool with distinct wage rates:

• Separate hourly wages for driving vs. loading/unloading

Vehicles

Defines a mixed fleet with realistic tradeoffs:

• Refrigerated sprinter van (small, efficient)
• Dry box truck (medium capacity)
• Large refrigerated/frozen truck (high capacity, low MPG)

Each vehicle includes:

• Fuel efficiency
• Depreciation cost per mile
• Insurance cost
• Weight and volume limits
• Storage capability (Dry / Ref / Frz)

---

5. Supply

Links entities, locations, and products into available supply:

• Quantities represent handling units (e.g., pallets, bins)
• Includes per-unit weight and volume
• Items-per-handling-unit enables conversion to item-level counts
• Cost per item is used for margin and profitability calculations

Examples:

• Broccoli and potatoes supplied by Green River Farm
• Apples supplied by Red Barn Orchards
• Beef supplied by Valley Beef Ranch

---

6. Demand

Creates downstream demand at store locations:

• Specifies product, quantity needed, and maximum acceptable price
• Enables matching supply to demand under pricing constraints

---

7. Routes

Defines directional logistics routes:

• Farm → Hub routes for inbound aggregation
• Hub → Store routes for outbound distribution

Routes form the backbone of scenario generation and optimization.

---

Completion

On successful execution, the procedure returns:

• SUCCESS: Synthetic Data Generated

This confirms that all seed data has been inserted correctly and the database is ready for scenario modeling.