| Field | Type | Logic / Description |
|---|---|---|
| Supplier_ID | Formula | PK "SUP-" & RECORD_ID() |
| Business_Name | Single Line | Primary display name |
| Supplier_Category | Single Select | Glamping / Marquee / Both — drives onboarding form branching |
| Coverage_Type | Single Select | Miles Radius / Named Counties / National |
| Max_Range_Miles | Number | Required only if Coverage_Type = Miles Radius |
| Coverage_Counties | Multi-Select | Required only if Coverage_Type = Named Counties |
| Is_National | Formula | Formula Coverage_Type = "National" — skips Haversine in Match Engine |
| Tent_Types | Multi-Select | Bell Tent / Tipi / Yurt / Safari / Geodome / Shepherd Hut / Other |
| Tent_Sizes | Multi-Select | 2 / 4 / 6 / 8 person / Custom |
| Min_Contract_Tents | Number | Minimum units per booking (e.g. 10). Used in Match Engine filter. |
| Beds_Included | Checkbox | |
| Bed_Type | Single Select | Real / Inflatable / Both |
| Decor_Level | Single Select | Rustic / Mid-Range / Boutique / High-End |
| Structure_Types | Multi-Select | Marquee suppliers: Traditional Pole / Frame / Stretch / Clearspan / Pagoda |
| Max_Structure_Capacity | Number | Marquee suppliers: max headcount |
| Event_Types_Served | Multi-Select | Wedding / Private Party / Festival / Pop-Up Campsite / Public Gathering |
| Event_Types_Excluded | Multi-Select | Explicit exclusions — checked in Match Engine alongside Served |
| Std_Payment_Structure | Single Select | Full Upfront / Deposit+Balance / Three-Stage |
| Std_Deposit_Pct | Number | % shown if Deposit applies |
| Std_Interim_Pct | Number | % shown if Three-Stage |
| Std_Balance_Timing | Single Select | On Booking / 30 Days Before / 14 Days Before / On Event Day / Post-Event 14 days |
| Late_Threshold_Days | Number | e.g. 30 — if event is within this many days, use Late structure |
| Late_Payment_Structure | Single Select | Same options as Std — often defaults to Full Upfront |
| Late_Deposit_Pct | Number | |
| Subscription_Status | Single Select | Active / Paused / Cancelled |
| Avg_Rating | Rollup | AVERAGE(Overall_Rating) from linked Reviews |
| Quality_Flag | Formula | Formula IF(Avg_Rating < 3, "Review Needed", IF(Avg_Rating < 4, "Monitor", "Good")) |
| Offers_Dry_Hire | Checkbox | Future use only — not used in matching v1 |
| Outcode | Formula | Formula Extracts first half of Postcode_Raw. FK → [Ref] Outcodes |
| Postcode_Raw | Single Line | Full postcode as entered, e.g. GL7 2BX |
| Referral_Code | Single Line | Unique code used in supplier referral URL e.g. prepitched.co.uk/refer?ref=SMITH24. Auto-generated on onboarding, customisable via Softr dashboard in Phase X. Captured as hidden field on Event Request form. |
| Active | Checkbox | Match Engine filter — only Active suppliers are matched |
| Field | Type | Logic / Description |
|---|---|---|
| Contact_ID | Formula | PK "CON-" & RECORD_ID() |
| Full_Name | Single Line | |
| Primary key for Softr login — must be unique across all contacts | ||
| Phone | Phone | |
| Supplier | Linked Record | FK → Suppliers. Multiple contacts can link to one Supplier. |
| Role | Single Select | Owner / Manager / Staff / Read-Only |
| Portal_Role | Single Select | Supplier Admin / Supplier Member / Organiser Admin / Organiser Member |
| Primary_Contact | Checkbox | Flags main billing/correspondence contact |
| Portal_Enabled | Checkbox | Activates Softr login for this email |
| Created_At | Created Time | Auto |
| Field | Type | Logic / Description |
|---|---|---|
| Event_ID | Formula | PK "EVT-" & RECORD_ID() |
| Organiser_Company | Single Line | Separate from Organiser_Name — required for Duplicate_Check_Key |
| Organiser_Name | Single Line | Primary contact at the organisation |
| Organiser_Email | Recipient for quotes and satisfaction survey | |
| Organiser_Portal_Email | Portal login — set only for repeat/multi-event organisers | |
| Portal_Enabled | Checkbox | Only checked for qualifying organisers |
| Event_Type | Single Select | Wedding / Private Party / Festival / Pop-Up Campsite / Public Gathering |
| Structure_Category | Single Select | Glamping / Marquee / Both — drives which suppliers are matched |
| Event_Date | Date | Used for Honesty Loop trigger and Late_Threshold check |
| Event_End_Date | Date | Honesty Loop fires +48hrs after this date |
| Guest_Count | Number | Matched against Supplier Max_Capacity |
| Postcode_Raw | Single Line | |
| Outcode | Formula | Formula Extracts first half of Postcode_Raw. FK → [Ref] Outcodes |
| Status | Single Select | New / Matching / Quotes Sent / Confirmed / Cancelled / Completed |
| Quote_Tier | Single Select | Free (up to 3) / Paid (TBC) |
| Max_Quotes_Allowed | Formula | Formula IF(Quote_Tier = "Free", 3, 5) |
| Quotes_Sent_Count | Count | Count of linked Quote records |
| Duplicate_Check_Key | Formula | Formula UPPER(TRIM(Organiser_Company)) & "-" & Outcode & "-" & TEXT(Event_Date,"YYYY-MM") |
| Duplicate_Flag | Checkbox | Set by Make when matching key found |
| Duplicate_Of | Linked Record | → Events. Manually linked by Lou/Katie after review. |
| Referred_By_Supplier | Linked Record | FK → Suppliers. Populated by Make on form submission using Referral_Code_Used lookup. Defaults to PPN House if no valid code. |
| Referral_Code_Used | Single Line | Raw code captured from hidden field on Event Request form (via ?ref= URL parameter). Make uses this to look up and link Referred_By_Supplier. |
| How_Did_You_Find_Us | Single Line | Open text field on Event Request form. Captures organic referral and marketing source where no referral link was used. For manual review and marketing insight — not used in automated matching. |
| Field | Type | Logic / Description |
|---|---|---|
| Quote_ID | Formula | PK "QTE-" & RECORD_ID() |
| Event | Linked Record | FK → Events |
| Supplier | Linked Record | FK → Suppliers |
| Supplier_ID | Lookup | Pulled from Supplier link — used as Softr filter anchor |
| CC_Reference_Code | Formula | Formula "PPN-" & Event_ID & "-" & Supplier_ID — supplier puts this in email subject when quoting |
| Quote_Submitted_Via_CC | Checkbox | Set by Make when CC alias email is detected |
| Days_Until_Event | Formula | Formula DATETIME_DIFF(Event_Date, TODAY(), "days") |
| Is_Late_Request | Formula | Formula IF(Days_Until_Event <= Supplier.Late_Threshold_Days, TRUE, FALSE) |
| Quote_Amount | Currency | Supplier's quoted price |
| Commission_Total | Formula | Formula Quote_Amount * 0.10 |
| Commission_Overhead | Formula | Formula Quote_Amount * 0.02 — always to PPN |
| Commission_Splittable | Formula | Formula Quote_Amount * 0.08 |
| Has_Referral | Formula | Formula IF(Event.Referred_By_Supplier != "PPN House", TRUE, FALSE) |
| Referral_Supplier_Share | Formula | Formula IF(Has_Referral, Commission_Splittable * 0.5, 0) — 4% if referral |
| PPN_Net | Formula | Formula Commission_Overhead + (Commission_Splittable - Referral_Supplier_Share) |
| Applied_Structure | Single Select | Full Upfront / Deposit+Balance / Three-Stage — set at acceptance |
| Structure_Was_Overridden | Checkbox | Set by Make if supplier changed from onboarding default |
| Total_Contract_Value | Rollup | SUM(Payment_Amount) from Payment_Schedules |
| Total_Commission_Due | Rollup | SUM(Commission_Due) from Payment_Schedules |
| Outstanding_Commission | Formula | Formula Total_Commission_Due - Total_Commission_Received |
| Status | Single Select | Invited / Submitted / Accepted / Rejected / Withdrawn |
| Honesty_Loop_Stage | Single Select | Not Started / Loop 1 / Loop 2 / Loop 3 / Escalated / Resolved / Voided |
| Did_It_Happen | Single Select | Yes / No / No Response |
| Final_Agreed_Price | Currency | Entered via Tally on Honesty Loop "Yes" — overrides Quote_Amount if set |
| Organiser_Contacted | Checkbox | Set when escalation email fires to organiser |
| Field | Type | Logic / Description |
|---|---|---|
| Payment_ID | Formula | PK "PAY-" & RECORD_ID() |
| Quote | Linked Record | FK → Quotes |
| Supplier | Lookup | Via Quote link — for Softr filtering |
| Supplier_ID | Lookup | Via Quote → Supplier — Softr filter anchor |
| Payment_Type | Single Select | Deposit / Interim / Balance / Settlement |
| Due_Date | Date | Computed by Make from supplier timing selection at acceptance |
| Payment_Amount | Currency | Amount of this instalment |
| Commission_Due | Formula | Formula Payment_Amount * 0.10 |
| Commission_Overhead | Formula | Formula Payment_Amount * 0.02 |
| Referral_Share | Formula | Formula IF(Has_Referral, Payment_Amount * 0.04, 0) |
| PPN_Net | Formula | Formula Commission_Due - Referral_Share + Overhead already included |
| Supplier_Receives | Formula | Formula Payment_Amount - Commission_Due |
| Status | Single Select | Pending / Confirmed / Overdue / Waived |
| Confirmed_Date | Date | When supplier reports payment received |
| Commission_Invoiced | Checkbox | PPN has raised invoice for this tranche |
| Commission_Paid | Checkbox | PPN has received payment — final reconciliation flag |
| Field | Type | Logic / Description |
|---|---|---|
| Referral_ID | Formula | PK "REF-" & RECORD_ID() |
| Referring_Supplier | Linked Record | FK → Suppliers. Defaults to "PPN House" if no external referral. |
| Referred_Event | Linked Record | FK → Events |
| Resulting_Quote | Linked Record | FK → Quotes. Set when a quote is accepted. |
| Status | Single Select | Pending / Active / Converted / Void |
| Commission_Split_To_Supplier | Lookup | Referral_Supplier_Share from linked Quote — 4% of contract value |
| Applied_To_Subscription | Checkbox | Has this credit reduced the next subscription invoice? |
| Field | Type | Logic / Description |
|---|---|---|
| Billing_ID | Formula | PK "BIL-" & RECORD_ID() |
| Supplier | Linked Record | FK → Suppliers |
| Period_Start / Period_End | Date | Billing period |
| Gross_Fee | Currency | Standard subscription fee |
| Referral_Credits_Applied | Currency | Pulled from Supplier's referral credit balance |
| Net_Amount_Due | Formula | Formula Gross_Fee - Referral_Credits_Applied |
| Payment_Status | Single Select | Unpaid / Paid / Overdue |
| Tally_Payment_ID | Single Line | Reference from Tally/Stripe |
| Field | Type | Logic / Description |
|---|---|---|
| Review_ID | Formula | PK "REV-" & RECORD_ID() |
| Event | Linked Record | FK → Events |
| Supplier | Linked Record | FK → Suppliers |
| Supplier_ID | Lookup | Via Supplier link — Softr filter anchor |
| Survey_Sent_Date | Date | When Tally survey link was emailed to organiser |
| Survey_Responded | Checkbox | |
| Overall_Rating | Number | 1–5 star score (from Tally) |
| Would_Recommend | Checkbox | |
| Review_Text | Long Text | Free-text organiser response |
| Consent_To_Publish | Checkbox | "May we share this as a testimonial?" — asked in Tally |
| Published | Checkbox | Lou/Katie marks when used on website/socials |
| Internal_Flag | Single Select | All Good / Monitor / Escalate |
| Internal_Notes | Long Text | Lou/Katie notes on poor experiences |
| Field | Type | Logic / Description |
|---|---|---|
| Log_ID | Formula | PK "LOG-" & RECORD_ID() |
| Supplier | Linked Record | FK → Suppliers |
| Event | Linked Record | FK → Events (optional) |
| Quote | Linked Record | FK → Quotes (optional) |
| Type | Single Select | New Match / Quote Submitted / Quote Accepted / Commission Due / Referral Converted / Payment Confirmed |
| Message | Single Line | Human-readable, e.g. "You've been matched to Midsummer Festival 2026" |
| Read | Checkbox | Supplier marks as read via Softr |
| Created_At | Created Time | Auto |
| Field | Type | Logic / Description |
|---|---|---|
| Outcode | Single Line | PK e.g. GL7, OX1, SW1A |
| Latitude | Number (decimal) | Centroid latitude for this outcode area |
| Longitude | Number (decimal) | Centroid longitude for this outcode area |
| Region | Single Line | e.g. South West, London — for manual filtering |
Referred_By_Supplier when no external referral exists. This means commission split logic runs identically for all quotes — no special-case code needed.
| Scenario | PPN Overhead (2%) | PPN Profit Share (4%) | Referring Supplier (4%) | PPN Total |
|---|---|---|---|---|
| No external referral | 2% | 8% | 0% | 10% |
| External referral exists | 2% | 4% | 4% | 10% |
Commission is tracked per payment instalment in Payment_Schedules, not as a single lump sum. PPN takes 10% of each tranche as it falls due.
Implemented in Make (Match Engine scenario)
IF Is_National → always include ELIF Coverage_Type = "Named Counties" → check Event county ∈ Supplier.Coverage_Counties ELIF Coverage_Type = "Miles Radius" → run Haversine, filter by Max_Range_Miles
Implemented in Make (Match Engine scenario)
Event.Event_Type ∈ Supplier.Event_Types_Served AND Event.Event_Type ∉ Supplier.Event_Types_Excluded
Formula field on Quotes
Is_Late_Request = IF( Days_Until_Event <= Supplier.Late_Threshold_Days, TRUE, FALSE )
Formula on Events + Make scenario
Duplicate_Check_Key = UPPER(TRIM(Organiser_Company)) & "-" & Outcode & "-" & TEXT(Event_Date, "YYYY-MM")
Make searches for matching key on creation. Sets Duplicate_Flag if found. Human review decides.
Formula on Quotes
Has_Referral = IF( Event.Referred_By_Supplier != "PPN House", TRUE, FALSE )
Formula on Quotes
Hours_Since_Invited = DATETIME_DIFF(NOW(), Submitted_At, "hours") Is_Overdue = IF(Hours_Since_Invited > 72, "Overdue", "Active")
action_base_url. Phase 1 points to Tally forms. Phase X (Softr) updates this single variable and all email links update automatically.
| # | Trigger | Recipient | Email Template | Data injected into email | Tool |
|---|---|---|---|---|---|
| 01 | New Event created | Internal | E2 — New Enquiry Alert | Event_Name, Date, Location, Guest_Count, Service_Required | Airtable |
| 01b | New Event created | Organiser | E1 — Event Form Submission Confirmation | Event_ID, all submitted fields echoed, quote tier, upgrade form link | Make |
| 02 | Duplicate_Flag → TRUE | Internal | E3 — Duplicate Event Flag | Event_ID, matched Event_ID, Duplicate_Check_Key | Airtable |
| 03 | Match Engine complete | Internal | Internal only — no template | Event_ID, list of matched Supplier names, count | Make |
| 04 | Status → Quotes Sent | Organiser | E5 — Quotes On The Way | Event_Name, number of quotes incoming, 72hr deadline | Airtable |
| 05 | Status → Quotes Sent (per supplier) | Supplier | Q1 — Invitation to Quote (ITQ) | Event_ID, Event_Date, Location Outcode, Guest_Count, Service_Required, CC_Reference_Code, Quote deadline | Make |
| 06 | CC alias email detected | Internal | Q3 — Quote Received (Internal) | Supplier name, Quote_Amount, Event_ID — Quote set to Submitted | Make |
| 06b | Supplier confirms intent (Yes on ITQ) | Supplier | Q2 — Full Event Details + Introduction | Full event brief, organiser contact, quote deadline, PPN intro record, 4-yr commission schedule | Make |
| 06c | Supplier declines ITQ | Supplier | Q4 — Declined ITQ | Event summary, soft referral nudge | Make |
| 06d | 72hrs after ITQ, Quote still = Invited | Both | Q5 — Quoting Deadline Check | Supplier: did you send? Organiser: did you receive? | Make |
| 07 | Winning_Quote set | Supplier | A1 — Booking Confirmed (Winning Supplier) | Event_Name, Date, Organiser contact, bank details prompt, 48hr window | Airtable |
| 08 | Winning_Quote set | Organiser | A2 — Booking Confirmed (Organiser) | Supplier name, Quote summary, ToS link | Airtable |
| 09 | Winning_Quote set | Losing Suppliers | A3 — Unsuccessful Quote Notification | Generic "not selected" message | Make |
| 09b | 2–3 weeks after intro, no outcome recorded | Both | A4 — No Response After Intent to Quote | Supplier: did this result in a booking? Organiser: have you confirmed a supplier? | Make |
| 10 | +48hrs after Event_End_Date | Supplier | H1 — Honesty Loop Initial Nudge | Event_Name, Quote_Amount, Commission_Amount, Did_It_Happen Tally link | Make |
| 11 | Did_It_Happen → Yes | Internal | H2 — Commission Ready (Internal) | Commission_Amount, PPN_Net — ready to invoice | Airtable |
| 12 | Did_It_Happen → Yes (+2 days) | Organiser | H3 — Post-Event Satisfaction Survey | Event_Name, Supplier_Name, Survey link (Tally) | Make |
| 13 | Honesty_Loop_Stage → Escalated | Organiser + Internal | H4 — Honesty Loop Escalation | Supplier_Name, Event_Name, Event_Date — combined survey + internal flag | Make |
| 14 | Subscription Payment_Status → Overdue | Supplier | B1 — Subscription Payment Overdue | Invoice amount, due date, payment link (Tally) | Make |
| 15 | Referral record created | Supplier | R1 — Referral Confirmation | Referral details, 4% commission note, bank detail prompt | Make |
| 16 | Referral.Status → Active | Supplier | R2 — Referral Matched | Referred event matched, commission update to follow | Make |
| 17 | Commission_Paid → TRUE | Supplier | R4 — Commission Ready to Pay | Commission amount, bank details link, 14-day window | Airtable |
| 18 | Scheduled — May & October | Supplier | R5 — 4-Year Commission Reminder | Introduction_Date, Protection_End_Date, current commission rate, booking prompt | Make |
Final_Agreed_Price is written to the Quote. Settlement Payment_Schedule record created dynamically if Three-Stage structure. Satisfaction survey fires to organiser +2 days later.
Suppliers declare two structures at onboarding: Standard (default) and Late Request (triggered when Days_Until_Event ≤ Late_Threshold_Days). At quote acceptance, Make pre-fills a Tally confirmation with the applicable structure. Supplier confirms or overrides.
Acceptance_Date · 30 Days Before → Event_Date - 30 · Post-Event 14 Days → Event_End_Date + 14. Every Payment_Schedule record has a concrete Due_Date the moment it's created.
IF(
FIND(" ", Postcode_Raw) > 0,
TRIM(LEFT(Postcode_Raw, FIND(" ", Postcode_Raw) - 1)),
LEFT(UPPER(TRIM(Postcode_Raw)), 3)
)
Examples: "GL7 2BX" → "GL7"
"SW1A 2AA" → "SW1A"
"B1 1BB" → "B1"
Trigger: Record created/updated in Suppliers or Events Action: Search [Ref] Outcodes where Outcode = extracted value Action: Update Outcode_Link field with matching record ID Runs once on creation. Re-runs only if postcode changes.
distance_km = 2 × 6371 × ASIN(SQRT(
SIN((lat2-lat1) × PI/180 / 2)² +
COS(lat1 × PI/180) × COS(lat2 × PI/180) ×
SIN((lon2-lon1) × PI/180 / 2)²
))
Filter: distance_miles = distance_km × 0.621371
KEEP IF distance_miles ≤ Supplier.Max_Range_Miles
SKIP IF Supplier.Is_National = TRUE
The schema is Softr-ready from day one. The following additions have been baked in now to avoid retrofitting later.
action_base_url Data Store variable in Make.