PrePitched Network — ERD v6

Schema v6 · 11 tables · 26 March 2026 · S-19: Supplier_Stock table, glamping fields on Events, capacity rollups on Suppliers

⚡ Schema change v2: Contacts — Full_Name replaced by First_Name + Last_Name. Email templates use First_Name for salutation.
⚡ Schema change v3 (23 Mar 2026): Added Contact_Stage to Contacts. Added Organiser_Portal_Email, Postcode_Raw, Max_Quotes_Allowed, Quotes_Sent_Count to Events. Added Introduction_Date + Protection_End_Date to Referrals.
⚡ Schema change v4–v5 (25 Mar 2026): Suppliers — added Referral_Code, Coverage_Regions, Structure_Types, Max_Capacity, Trial + Stripe + Dry_Hire fields. Events — added Referral_Code_Used, Event_County, Event_Region, Dedup_Reviewed, Winning_Quote. Quotes — added Applied_Commission_Rate, ITQ_Sent_At, Has_Referral lookup. Payment_Schedules — Has_Referral lookup. Subscriptions — Stripe IDs + Next_Billing_Date. [Ref] Outcodes — County formalised.
⚡ Opus audit fixes (25 Mar 2026): Suppliers — added WhatsApp_Opted_In, Supplier_Score placeholder formula. Events — added Event_Name. Quotes — added 3 Lookups (Event_ID, Event_Date, Referred_By_Supplier_Name); Has_Referral corrected from Lookup to Formula. Payment_Schedules — added Applied_Commission_Rate Lookup, Commission_Paid_Amount helper formula. Activity_Log — added Supplier_ID Lookup. Subscriptions — Referral_Credits renamed to Referral_Credits_Applied. Relationships — Outcode → Outcode_Link for both Suppliers and Events.
⚡ Schema change v6 (26 Mar 2026, S-19): New table: Supplier_Stock (one record per tent type per supplier, Layer 2 stub). Suppliers — added Capacity_From rollup, Capacity_To rollup, Offers_Booking_System stub. Events — added Glamping_Tent_Preference, Glamping_Tent_Types_Requested, Glamping_Units_Requested, Glamping_Spec_Notes, Payment_Arrangement. Existing flat glamping fields on Suppliers retained as interim matching source.
Suppliers + Contacts
Events
Quotes + Payments
Referrals
Reviews
Activity Log
Reference + Billing
PK Primary key
FK Foreign key
Schema change ★
Core entities
Suppliers
stringSupplier_IDPK
stringBusiness_Name
selectSupplier_Category
stringReferral_Code ★
selectCoverage_Type
intMax_Range_Miles
multiCoverage_Counties
multiCoverage_Regions ★
multiTent_Types
multiTent_Sizes
intMin_Contract_Tents
multiStructure_Types ★
intMax_Capacity ★
multiEvent_Types_Served
multiEvent_Types_Excluded
selectSubscription_Status
intTrial_Months_Granted ★
dateTrial_End_Date ★
selectStd_Payment_Structure
intLate_Threshold_Days
phoneWhatsApp_Number
selectComms_Preference
boolWhatsApp_Opted_In ★
boolOffers_Dry_Hire
textDry_Hire_Notes ★
stringStripe_Connect_ID ★
boolBank_Details_Confirmed ★
selectPayout_Method ★
currencyReferral_Credits_Balance ★
formulaSupplier_Score ★
formulaQuality_Flag
rollupCapacity_From ★
rollupCapacity_To ★
boolOffers_Booking_System ★
stringPostcode_Raw
formulaOutcode
linkOutcode_LinkFK
boolActive
Contacts
stringContact_IDPK
stringFirst_Name ★
stringLast_Name ★
emailEmail
phonePhone
selectRole
selectPortal_Role
boolPrimary_Contact
boolPortal_Enabled
linkSupplier_IDFK
selectContact_Stage ★
Events
stringEvent_IDPK
stringEvent_Name ★
stringOrganiser_Company
stringOrganiser_Name
emailOrganiser_Email
phoneOrganiser_Phone ★
selectEvent_Type
selectStructure_Category
dateEvent_Date
dateEvent_End_Date
intGuest_Count
selectGlamping_Tent_Preference ★
multiGlamping_Tent_Types_Requested ★
intGlamping_Units_Requested ★
textGlamping_Spec_Notes ★
selectPayment_Arrangement ★
stringPostcode_Raw
formulaOutcode
linkOutcode_LinkFK
lookupEvent_County ★
lookupEvent_Region ★
selectStatus
selectQuote_Tier
formulaMax_Quotes_Allowed
countQuotes_Sent_Count
formulaDuplicate_Check_Key
boolDuplicate_Flag
boolDedup_Reviewed ★
linkDuplicate_OfFK
linkReferred_By_SupplierFK
stringReferral_Code_Used ★
textHow_Did_You_Find_Us ★
linkWinning_Quote ★FK
emailOrganiser_Portal_Email
boolPortal_Enabled
[Ref] Outcodes
stringOutcodePK
decimalLatitude
decimalLongitude
stringTown
stringCounty ★
stringRegion
stringCountry
Transactional
Quotes
stringQuote_IDPK
linkEvent_IDFK
linkSupplier_IDFK
lookupEvent_ID (Lookup) ★
lookupEvent_Date ★
lookupLate_Threshold_Days
lookupReferred_By_Supplier_Name ★
formulaCC_Reference_Code
formulaIs_Late_Request
datetimeITQ_Sent_At ★
currencyQuote_Amount
numberApplied_Commission_Rate ★
formulaCommission_Total
formulaPPN_Overhead (2%)
formulaHas_Referral ★
formulaReferral_Supplier_Share (4%)
formulaPPN_Net
selectApplied_Structure
selectStatus
selectHonesty_Loop_Stage
selectDid_It_Happen
currencyFinal_Agreed_Price
Payment_Schedules
stringPayment_IDPK
linkQuote_IDFK
lookupSupplier_IDFK
selectPayment_Type
dateDue_Date
currencyPayment_Amount
lookupHas_Referral ★
lookupApplied_Commission_Rate ★
formulaCommission_Due
formulaReferral_Share
formulaPPN_Net
selectStatus
boolCommission_Paid
formulaCommission_Paid_Amount ★
Referrals
stringReferral_IDPK
linkReferring_SupplierFK
linkReferred_EventFK
linkResulting_QuoteFK
selectStatus
lookupCommission_Split (4%)
boolApplied_To_Subscription
dateIntroduction_Date ★
dateProtection_End_Date ★
Subscriptions
stringBilling_IDPK
linkSupplier_IDFK
datePeriod_Start
datePeriod_End
currencyGross_Fee
currencyReferral_Credits_Applied
formulaNet_Amount_Due
selectPayment_Status
stringStripe_Customer_ID ★
stringStripe_Subscription_ID ★
dateNext_Billing_Date ★
Supporting
Reviews
stringReview_IDPK
linkEvent_IDFK
linkSupplier_IDFK
lookupSupplier_ID (Lookup)
intOverall_Rating (1–5)
boolWould_Recommend
textReview_Text
boolConsent_To_Publish
selectInternal_Flag
Activity_Log
stringLog_IDPK
linkSupplier_IDFK
lookupSupplier_ID (Lookup) ★
linkEvent_IDFK
linkQuote_IDFK
selectType
stringMessage
boolRead
datetimeCreated_At
Supplier_Stock
stringStock_ID ★PK
linkSupplier ★FK
lookupSupplier_ID ★
selectTent_Type ★
intTent_Size_m ★
intUnits_Available ★
intPersons_Per_Unit_From ★
intPersons_Per_Unit_To ★
boolBeds_Included ★
selectBed_Type ★
formulaCapacity_From_This_Type ★
formulaCapacity_To_This_Type ★
textNotes ★
Relationships
FromCardinalityToVia field
Suppliers1 → manyContactsSupplier_ID
Suppliers1 → manyQuotesSupplier_ID
Events1 → manyQuotesEvent_ID
Quotes1 → manyPayment_SchedulesQuote_ID
Suppliers1 → manyReferralsReferring_Supplier
Events0–1 → manyReferralsReferred_Event
Events1 → manyReviewsEvent_ID
Suppliers1 → manyReviewsSupplier_ID
Suppliers1 → manyActivity_LogSupplier_ID
Suppliers1 → manySubscriptionsSupplier_ID
Suppliersmany → 1[Ref] OutcodesOutcode_Link
Eventsmany → 1[Ref] OutcodesOutcode_Link
Events0–1 → 1Suppliers (self)Referred_By_Supplier
Events0–1 → 1Quotes (self)Winning_Quote
Suppliers1 → manySupplier_StockSupplier
Events0–1 → 1Events (self)Duplicate_Of