Data Model

A single Prisma schema — prisma/schema.prisma — defines the entire domain: 40 models and 26 enums, with 47 tracked migrations. PostgreSQL (Supabase) is the only primary datastore; GeoJSON polygons are stored as JSONB (no PostGIS in Phase 1).

Entity relationships (core)

Model groups

Users & auth

User, Device, FarmerProfile, TraderProfile, Farm, FarmPhoto, Warehouse, WarehousePhoto.

• A User starts as a Light User (profileComplete: false, nullable name/roles/ activeRole) and becomes Active on profile completion, which assigns the membershipNumber (CRP-YYYY-NNNNN).
• Denormalised trust-score counters live on User (successful deals, cancellations, offers received/replied, overcommitment grace count).
• Device holds the device-bound refresh token plus the rotation-grace fields (previousRefreshToken, refreshTokenRotatedAt).

Leads, offers & bids

SellLead, BuyLead, Offer, BuyBid.

• A lead carries product taxonomy, quantity, price, location, and a status (ACTIVE / CLOSED / FULFILLED / EXPIRED / DIRECT_BOOKED / CANCELLED).
• Partial multi-acceptance is tracked via quantityAccepted + acceptanceCount.
• Offer/BuyBid share a status enum (PENDING, COUNTERED, ACCEPTED, REJECTED, WITHDRAWN, AUTO_REJECTED, CANCELLED) and carry counter-offer fields + a sellerBatchId (a bid names the seller's lot at bid time).

Products & inventory

Product, Batch, BatchPhoto, ProductPhoto, InventoryEvent.

• The Batch is the source of truth for quantity, cost (acquired + production), sale price, quality metrics, GI status, and photos. Product.* equivalents are derived display snapshots.
• Product aggregate identity is (userId, taxonomyLeafId, packingMode, isGiTagged) as a partial unique index (WHERE deletedAt IS NULL) — soft-deleted rows release the key.
• InventoryEvent is an append-only, batch-anchored ledger (CREATE, MANUAL_EDIT, OFF_PLATFORM_SELL/BUY, CORRECTION, SELL_ACCEPTED, BUY_ACCEPTED, MERGE_FROM_ORDER, …) carrying realised { salePricePerKg, costPerKg } on sale events.

Deals, orders & fulfilment

Order, OrderDocument, OrderEvent, Settlement, Shipment, Grievance.

• Accepting an engagement materialises an Order. It has three orthogonal axes: status (commercial), fulfilmentStatus (dispatch/delivery), paymentStatus.
• OrderDocument is a polymorphic registry (SOC / Dispatch Note / GRN / Grievance / Resolution PDFs…) so new document kinds are data, not migrations.
• OrderEvent is the append-only transition ledger feeding the deal timeline.
• Grievance models the ONDC-IGM-style redressal lifecycle with SLA timers.

Messaging & inquiries

DealThread, DealMessage, Inquiry.

• A DealThread is XOR-anchored to exactly one of offerId, bidId, or inquiryId.
• The send path is gated on ACCEPTED (offer/bid threads); inquiry threads bypass the gate (pre-engagement signal layer).

Discovery & quotas

LeadQuota, LeadView, LeadShortlist, SearchEvent, AdminSearchAuditLog.

• LeadQuota holds all 3 axes (posts / views / engagements) per IST calendar day.
• LeadView records a deduped lifetime view (XOR sell/buy) — the gate for engagement.

Taxonomy & lookups

TaxonomyNode, MetricField, TaxonomySuggestion, MakhanaVariety, GroupAffiliation, Crop, ContactEnquiry, DashboardSnapshot, Notification.

• TaxonomyNode is a 3-level tree (4 L1 / 12 L2 / 42 L3 Makhana nodes) carrying the GST bracket (hsnCode, gstRate as a fraction) and destination (E_MANDI / MARKETPLACE).
• MetricField defines the per-leaf quality-metric shape (ENUM/NUMBER/YEAR/TEXT/ DATE).

Conventions

• Every model has createdAt @default(now()) and updatedAt @updatedAt.
• Leads, offers, and bids are never hard-deleted — status transitions only.
• Products/batches are soft-deleted (deletedAt/deletedBy); hard removal is the admin retention purge alone.
• Schema changes go through prisma migrate dev --name <desc>; migration files are never hand-edited. Some pooler-hostile migrations were applied via prisma db execute + migrate resolve --applied.

Working with the schema

npx prisma studio  --schema=prisma/schema.prisma   # browse data
npx prisma migrate dev --schema=prisma/schema.prisma --name my_change
npx prisma generate --schema=prisma/schema.prisma  # regenerate the client

Regenerate after pulling

If you see type errors on fields like previousRefreshToken, your local generated Prisma client is stale — run npx prisma generate.