Data model

This page gives you a conceptual map of the Surplus data model — enough to find your way around the schema. The source of truth is the Drizzle schema under packages/postgres/src/schema/; the narrative reference with design rationale lives in docs/data-model.md in the repository.

Ground rules

Every table follows the same conventions:

UUID primary keys generated with gen_random_uuid() — never auto-incrementing integers.
Unix epoch timestamps stored as integers, not Date objects.
camelCase table and column names.

The core entities

Organizations and people

Donors — organizations that give food (farms, retailers, restaurants, wholesalers), each with one or more donor locations.
Recipients — organizations that receive food (food banks, shelters, programs), each with one or more recipient locations.
Carriers — third-party logistics providers supplying vehicles and drivers.
Hubs — system-owned warehouses where food is stored, sorted, and redistributed.
Users — people who sign in, each with a permission level (admin, advisor, partner, driver, or suspended). Partners are linked to their organizations through association tables.

Reference data

Products — the catalog of food types.
Tags — labels applied to organizations and other entities.

Events: how food moves

Food movement is recorded as events, each tied to a stop on a route or an action at a hub:

Collection events — food entering the system from a donor.
Distribution events — food leaving the system to a recipient.
Load events — food moving from a hub onto a vehicle.
Unload events — food moving from a vehicle back into a hub.
Compost events — food removed from inventory as waste.

A route is a vehicle's physical journey, made of ordered stops; each stop corresponds to one of the events above. Routes are pure logistics — the events carry the food data.

Inventory: two layers

Inventory is the part of the model most worth understanding, because it uses two distinct layers:

Collection event items (collectionEventItems) — the receipt. Per-line record of what was collected: product, quantity, and weight. These are never created or destroyed by later operations like splits or allocations; they are the immutable record of what arrived.
Items (items) — the operational layer. Each row tracks a physical partition of inventory: its quantity, where it lives, its status (active, allocated, distributed, composted), and optional internal quality. This is the layer warehouse staff act on.

When inventory is split, the source items row shrinks and a new items row is inserted — there is no frozen parent row and no parent/child tree. Allocation, distribution, and composting all change an item's status and location, while the original collection receipt stays intact.

This separation is what lets Surplus track every pound from arrival to its final destination while keeping an honest record of what was originally donated.

Cost sharing agreements (CSAs) define the financial terms between Sharing Excess and a donor or recipient, including rates. Per-event cost share overrides adjust those terms for a specific collection or distribution.
Invoices are generated from this data and managed in Stripe.

Where to read more

docs/data-model.md (in the repo) — the full narrative: every table, relationship, and design decision.
docs/workflows.md — the exact database mutation sequences for each user action.
docs/rules.md — business rules, state machines, and validation.
packages/postgres/src/schema/ — the canonical Drizzle definitions.

On this page