# IASF Event Producers — Shared Project Handbook

**For:** Marcy (registration admin) and her Claude assistant
**Maintained by:** Hanako
**Status snapshot:** 2026-07-26 (evening — includes the pipeline restructure)
**Visual walkthrough:** [flow.html](flow.html) — the whole process in EP-view and Marcy-view, stage by stage
**Airtable base:** IASF - Event Producers (`appqGy7olfqgdORWu`)

> **Marcy — how to use this doc with Claude:** Paste this into a Claude chat: *"Read https://iasf-dev-hq.pages.dev/handbook.md and answer my questions about the IASF system."* Claude can read it directly from the web. You can also connect your **Airtable** account to Claude (Settings → Connectors) so it can look at live records in the base while it answers. Ask anything — "what does the Conflict Flags automation do?", "what do I do when a privilege letter comes back?", "why is this field empty?"

---

## 1. What this system is (outline)

The base runs the **Event Producer (EP) registration lifecycle** for IASF Cheer & Dance Worlds. **As of 2026-07-26, the pipeline is tracked per application year on the Applications table** — because an EP can have 2027 and 2028 cycles open at once:

```
(link sent — date stamp on EP) → For Review → Approved →
Privilege Sent → Privilege Returned  (+ Denied / Tentative / Other)
```

- **`AppStatus`** (Applications) = the one lifecycle field per cycle: review verdict AND pipeline position. "Application received" is implicit (the row exists).
- **EPs `2027 Stage` / `2028 Stage`** = rollups showing where each year's cycle stands, side by side on the EP.
- **EPs `2027 AppSent` / `2028 AppSent`** = date the application link was emailed (stamped by the Send buttons). Sent-date filled + no application for that year = the chase list.
- **`Ready for 2028 Link`** (EPs formula) = ✅ when the EP's 2027 event ended 7+ days ago, no 2028 link sent, no 2028 app yet. Marcy filters on it and clicks Send — the automation only flags, never emails on its own.
- The old EP-level **`ActivityStage`** and **`LatestAppStatus`** are deprecated (still visible during transition; don't rely on them).

### Tables (3 core + 2 support)

| Table | Role |
|---|---|
| **EPs** | Master record, one per event/company — permanent. Pipeline stage, prefilled-form links, privilege/agreement links, `ActivityDates` audit log. Mostly live reflections of linked records. |
| **Applications** | One row per event per year (`AppYear` 2024–2029). Raw submission, flags, suggested bid math, `AppStatus`. This is the review + bid-entry surface. |
| **People** | De-duplicated contacts (Primary / Main Contact / Contact 2), linked to EPs & Applications. Maintained automatically by the People Upsert script. |
| Duplicates Archive | Parking lot for duplicate/superseded submissions. |
| Templates | Email bodies with merge fields (`{{EP}}`, `{{APP_URL}}`). |

### The self-refreshing prefill loop (core trick)

Returning EPs get a **prefilled form link** (formula-built URL, ~40 fields prefilled, system fields hidden). Each submission overwrites the EP's "latest" data, so **next year's prefill is always current**. New EPs get the same form blank.

### Automations (live in the base)

| Automation | Trigger | What it does |
|---|---|---|
| **Send 2027/2028 App Link** | Button (Send Pre-filled Forms page) | Emails the prefilled form + stamps `2027/2028 AppSent` date |
| **Overwrite EPs Table** | Application created | Creates/updates the EP; maps dates & schedules into year columns. **Never writes prior-year date columns for returning EPs** (protects the cleaned-up dates history). |
| **People Upsert** ("New Application - Edit People", script) | Application matches `Link_EP` not empty | Upserts all three contacts; writes new links back. Live since 2026-06-03. Never blanks existing data. |
| **Conflict Flags** (script) | Application updated | Nearby-event proximity (250/350 km), YOY date variance, Season 2, No App/No Bids Last Season, date/proximity conflicts |
| **AppStatus - Stage Log** | `AppStatus` changes | Appends `timestamp — status` to the application's `StageLog` (per-cycle audit trail; replaced the old EP-level time log) |

### Key formulas

- **`PrivilegeCalculation`** — *suggested* bids from prior-season team counts. Dance: PP = teams÷30, AL = PP×2, FP = teams÷75. Cheer: PP = teams÷50, AL = PP×2, FP = teams÷75 (FP only if interest Yes/Unsure). **Advisory only — granted bids are always entered by hand.**
- **`Season_Suggestion`** — Season 2 if event falls 0–30 days before Worlds, else Season 1.
- **`Flag_DaysFromWorlds`**, **`NextSeason_VenueChange`**, **`EventZone`** — review aids.

### External piece: Make.com

- **Scenario 5210244 "Formstack → IASF EP Applications 2027"** (us2.make.com, IASF account) copies Formstack submissions into the Applications table. **Temporary** — the end state is Airtable-forms-only and Formstack goes away.
- Scenario 4818985 (IG/FB social poster) is separate and uses a different base (`appSZ41eY7EunZnlI`) — not part of this workflow.

---

## 2. What's been DONE

- **Base architecture built and live** — 5 tables, pipeline, prefill loop, flags, bid math (spring 2026).
- **`ActivityStage` renamed** to the current 6-stage pipeline names — rename-safe, confirmed (June 2026).
- **People Upsert script live** (2026-06-03) — replaced the two old People automations (both retired); fixed the Contact 2 bug that pointed at the Main Contact's record.
- **Conflict Flags working** — "No App Last Season" / "No Bids Last Season" options added to the `Flags` field so the script writes cleanly.
- **Registration Playbook + technical reference written** (`IASF_EP_Registration_Playbook.html`, `IASF_EP_Flow_Reference.md` in Hanako's project folder).
- **Make Formstack bridge repaired twice (July 2026):** (1) dead Formstack OAuth connection reauthorized after the scenario auto-deactivated and queued webhooks; (2) `InauguralYear` mapping made tolerant (extracts a 4-digit year from free text, else leaves empty) after number-field validation errors (fixed 2026-07-15).
- **Historical bid counts uploaded** (2026-07-16) — bid numbers from EP Bid Privilege letters entered into Applications: 37 records filled, ~45 already matched. A reusable EP-folder-name → Airtable-event-name mapping now exists for future seasons.
- **Event dates audit (DATES REVIEW)** — 4 years of event dates cross-checked against source tabs and raw applications; ~6 events corrected and push-ready, ~13 need EP confirmation (see §3).
- **Pipeline restructure (2026-07-26)** — stage tracking moved from the EP to the per-year application. `AppStatus` extended into the full lifecycle; per-year `Stage` rollups + `AppSent` dates + `Ready for 2028 Link` flag added on EPs; 12 EPs' 2027 apps backfilled to *Privilege Sent*; Overwrite EPs no longer clobbers prior-year dates; per-application `StageLog` audit trail.

---

## 3. What REMAINS

### Build items (Hanako, mostly)

1. **Apply the restructure in the Airtable UI (Hanako)** — click *Update* on the three edited automations (Send 2027/2028 App Link, Overwrite EPs Table); turn ON *AppStatus - Stage Log* / OFF *ActivityStage - Time Log*; add the "Privilege Returned" option to `AppStatus` and reorder choices; create Marcy's 2027 outreach view.
2. **Privilege automation** — when status is set to *Privilege Sent* (from the Review Apps interface), auto-send a **pre-filled, non-editable form** for the EP to return. Requires updating the flow from the Review Apps interface pages. **Letter template is now in hand** — this is buildable.
2b. **Chase-list process** — build out the view/automation for "link sent but application never returned" (AppSent date + no app for that year). Also consider: auto-email Marcy when an application is received.
3. **Rostering-system indicator** — decide how the application record marks "EP completed setup in the new rostering system" (replaces the old Drive-folder upload step). Design pending.
4. **Duplicate prevention design** — catch duplicate EP/People/Application records (known vector: returning EP uses the blank "new EP" form instead of their prefilled link).
5. **Business licence / police check validity review aid** — stage-3 checklist item is manual today; a "valid until" field or flag is not built yet.
6. **Season roll-forward guide** — the each-cycle checklist: new form page, new prefill formulas, year-column mapping in *Overwrite EPs Table*, plus (new) the `_StageYYYY` helper, `YYYY Stage` rollup, `YYYY AppSent`, and Ready-for-link formula.
7. **Interface suite cleanup** — delete the dead *2027/2028 For Review* interfaces; build a purpose-built set. (Use the **2027 Applications** data view meanwhile.)
8. **Retire Formstack entirely** — move fully to Airtable forms, then decommission Make scenario 5210244.
9. **(Parked — do not build yet)** Reporting between the new rostering system and Airtable (stage 5 of the flow).

### Data items (need review/confirmation)

- **Bid-count conflicts (from the 2026-07-16 upload)** — ~20 events where the number already in Airtable ≠ the privilege letter (suspected digit transpositions and column swaps, e.g. AASCF 2026, CheerCon 2026, Aussie Gold 2026, Live Your Legacy 2024, ECC 2025). These were deliberately **not overwritten**. Hanako holds the full conflict list — ask her, or ask her Claude, for "the bid privilege TO-REVIEW list."
- **Applications with no bid letter on file** — e.g. Canadian CFC 2026/27, Cheerfest BC 2026/27, Cheer Evolution Ontario 2026/27, ONP 2026, The Finals 2026, Abierto 2027, and most newer EPs (USA Japan, Kicksmas, Rock the Runway…). Bids still empty.
- **Event dates needing EP confirmation (~13 events)** — the dates audit cleared dates that had been copied in from the *wrong events* (mostly DCE Nationals dates). These EPs must confirm their real 2026/2027 dates: 21CC, Atlantic Showdown (2027), CCCHILE (2027), Cheer Festival Championship (MX), Cheerleader Classic, Dance Branz, Dance Classic, Dance Nationals (CL), Energize, Next Generation (PR), Pan Pacific (2027), Pink Championship (2027/28), Battle on the Island.

### Known fragile bits (watch, don't fix)

- **Attachment copy-by-URL** in People Upsert (passport/back-check) — most likely piece to fail; blast radius is small.
- **Conflict Flags proximity** depends on AI-geocoded lat/long — mitigated by collecting full addresses going forward.
- **Make scenario date fields** use strict `parseDate "MMMM D, YYYY"` and attachment fields fail on empty URLs — if the scenario errors, check the Make execution log, then per-field mappings.

---

## 4. Marcy — your next steps

1. **Work the send flags** — in the EPs table, `Ready for 2028 Link` (☑️ 2027 bid event complete) lists who's due for a 2028 link: filter and hit Send. `Ready for 2027 Link` (☑️ 2026 bid event complete) is the 2027 chase list — review with Hanako before sending, some may have been skipped on purpose.
2. **Confirm real event dates with the ~13 EPs** listed in §3 — their 2026/2027 dates in the base were cleared because they were another event's dates. (Per-record links on the dashboard's review list.)
3. **Review the bid-count conflict list with Hanako** — for each, decide letter vs. Airtable, then the base gets corrected. (Per-record links on the dashboard's review list.)
4. **Keep running the normal cycle** (see §5) — the system is live; day-to-day use doesn't wait on any of the above.
5. When a returning EP asks for a link, **always send their prefilled link**, never the blank form — the blank form creates duplicates (until duplicate-prevention is built, this is the main thing to be careful about).

---

## 5. How to USE the system (daily operations)

Full stage-by-stage detail with EP and Marcy views: **[flow.html](flow.html)**. The six stages in brief:

1. **Send the application link** — *Send Pre-filled Forms by Button* page → click Send. Returning EPs get their prefilled link; new EPs the blank form. `AppSent` date auto-stamps.
2. **EP applies** — everything after submit is automatic: Application row (For Review), EP created/refreshed, contacts synced, flags computed, StageLog started.
3. **Review → bid privilege** (Applications table): work the flags; check business licence + police check validity (manual for now); look at last event's team counts; compare `PrivilegeCalculation`; **type the granted bids** (Dance/Cheer × FP/PP/AL); set `AppStatus = Approved`; send the privilege letter + agreement, attach the letter PDF to `PrivilegeLetter`, set `Privilege Sent`.
4. **EP accepts** — signed agreement back → `Privilege Returned` = confirmed for Worlds. EP gets the bid & rostering rules; new-rostering-system indicator TBD.
5. **EP holds their event** — bid winners are reported in the rostering system; nothing happens in Airtable.
6. **Next season's link** — 7+ days after the event date, `Ready for 2028 Link` flags the EP; Marcy filters and clicks *Send 2028 App Link*. Loop restarts with a prefilled form.

**Links** (need Airtable login):

- Send Forms interface: `https://airtable.com/appqGy7olfqgdORWu/pagY5itBIBHm8mkpB`
- Review Apps interface: `https://airtable.com/appqGy7olfqgdORWu/pagtU5haBeD3Gglmj`
- Bid Review dashboard: `https://airtable.com/appqGy7olfqgdORWu/pagbgx9JYT6WzeHJV`
- 2027 Applications data view: `https://airtable.com/appqGy7olfqgdORWu/shrd44XKLlNPr2rl3`
- 2027 / 2028 new-EP blank forms: pages `pagE9ueDCTau1gCNw` / `pagLr6TG6HXWgnHzf`
- ⚠️ The old *2027/2028 For Review* interfaces are **dead — don't use them**.

**Things that are safe:** renaming select options like `AppStatus` choices (automations/views follow the rename — but scripts that read option names by string do not; check first); submitting a form with blank fields (People Upsert never wipes existing data with blanks).

**Things to avoid:** deleting + recreating select options (breaks dependencies); sending a returning EP the blank form (duplicates); building an automation with multiple conditional groups that each need to run (Airtable runs only the FIRST matching group — use combinatorial branches or a script).

---

## 6. FAQ seeds for your Claude

Good questions this doc can answer: *What happens automatically when a form is submitted? Why are bids entered by hand? What does each flag mean? What's the difference between ActivityStage and AppStatus? Why shouldn't I use the For Review interfaces? What's still manual? What was fixed in the Make scenario?*

For anything about **live data** (a specific EP's stage, this week's submissions), connect Airtable to Claude and ask against the base directly.

For anything not covered here — ask Hanako; her Claude keeps the deeper technical reference and change history.

---

*Update cadence: Hanako regenerates this page when the project moves. If the snapshot date at the top looks stale, ask her for a refresh.*