OEFF 2026 Data Architecture

Spec v2 — Updated March 27, 2026
For the OEFF core team · Airtable Base appvUfaPd9ejb5fHb

See also: Architecture Overview — visual map of the data model, decisions, and data flow

1. Principles

• Events are the relational key. Each Event links a Venue, a Film, and a Season.
• Roles live in Assignments, not on Contacts. A Contact is a name and email. The Assignments table connects them to a context with a role.
• Films and Venues persist across years. Events are seasonal.
• Sheets are canonical. Airtable is downstream. Tracker sheets and the OEC Roadmap are sources of truth. Airtable adds relational queries and powers automation.

2. Canonical Data Sources

Four sources own the data. Everything else derives from them.

3. Data Flow

Sync is one-way and idempotent. The enrichment script matches records by name or email before creating, so it is safe to run daily. Venue name aliases handle naming drift between sheets and Airtable.

4. Schema: 14 Tables Across 4 Layers

The base also contains Sponsors (legacy from v1, may be consolidated into Organizations) and Host Submissions (form responses from hosts). These sit outside the 14-table architecture.

Layer 1: Core

Persistent across festival years. Records accumulate knowledge over time.

Layer 2: Relational

Season-specific. Created fresh each year.

Layer 3: Asset Pipeline

Populated during operations. Tracks files from request through delivery.

Layer 4: Supporting

Operational memory. Depends on Layer 1 (Contacts) but not Layer 2 (Events). Persistent across seasons.

5. The Junction Pattern (Assignments)

Assignments replaced four separate tables from the old base (Host Contacts, Film Contacts, Participants, Partners) with a single junction table.

Example records:

What this enables:

• "Who are all the contacts for Triton College?" → Filter Assignments by Venue
• "What roles does Colleen Thurston have?" → Filter Assignments by Contact
• "Who's the AV lead for each venue?" → Filter Assignments by Role = AV Contact
• "Show me everyone involved in the April 24 events" → Filter by Event date

6. Field Reference

Events Layer 2

• Name Text e.g., "Drowned Land at Film Row Cinema"
• Season Link Which festival edition
• Venue Link Physical location
• Film Link Primary film (blank for non-screening events)
• Event Type Select Screening, Panel Discussion, Action Fair, Workshop, Concert, Reception, Special Event
• Date Date Event date
• Start Time Text e.g., "7:00 PM"
• Doors Open Text e.g., "6:30 PM"
• Ticket Price Text e.g., "Free", "$10", "Suggested $5"
• Ticket URL URL Eventbrite link
• Pipeline Status Select Concept, Confirmed, In Prep, Ready, Complete, Cancelled
• Notes Long text OEFF POC, event tier, host fee, operational notes

Assignments Layer 2

• Label Text Summary: "Nadia → Lincoln Univ → Host Contact"
• Contact Link Who — always populated
• Organization Link Context: populate ONE of Org / Venue / Film / Event
• Venue Link (same — one context per assignment)
• Film Link (same)
• Event Link (same)
• Role Select Host Contact, Venue Contact, AV Contact, Filmmaker, Distributor, Marketing Contact, Volunteer Lead, Panelist, Board Liaison, Sponsor Contact, Festival Staff
• Priority Select Primary or Secondary
• Notes Long text Context-specific: availability, preferences, handoff notes

Venues Layer 1

• Name Text Venue name
• Address Text Full street address
• City Text Geography / neighborhood
• Capacity Number Seating or standing
• ADA Accessible Checkbox Meets wheelchair accessibility
• AV Equipment Long text Projector, Screen, Sound, Computer, WiFi, Microphone, AV Lead + notes
• Parking Long text Accessible parking info
• Transit Long text Public transit access
• Classification Select Flagship, Community, Partner
• Technical Tier Select T1 (complex) through T4 (minimal)
• Readiness Select On Track, Ready, Needs Attention — shown as badge on host page
• Notes Long text Venue description + accessibility notes, persists year over year

Films Layer 1

• Title Text Film title
• Runtime (minutes) Number Critical for scheduling
• Director Text Director name
• Year Number Release year
• Format Select Feature, Short, Documentary, Animation, Experimental
• Caption Status Select None Available, SRT Available, Burned In, Audio Description Available, Pending
• Licensing Notes Long text Status, fee, type, screening count, restrictions
• Screening Copy URL URL Current approved screening file
• Press Kit URL URL Press materials

7. Sync Pipeline

One script handles all synchronization: oeff-v2-enrich.py

# Typical usage
python3 ~/tools/oeff-v2-enrich.py                     # dry run all steps
python3 ~/tools/oeff-v2-enrich.py --apply              # sync everything
python3 ~/tools/oeff-v2-enrich.py --step comms --apply # just comms
python3 ~/tools/oeff-v2-enrich.py --step filmmaker --apply  # push film updates

8. Record Counts (as of March 16, 2026)

9. Airtable API Limitations

Determined through implementation. Formulas, rollups, and lookups require manual UI work.

10. What We Didn't Migrate

The old Airtable base had 16 tables. Several were replaced by the junction pattern or the asset pipeline. Their data is preserved in CSV backups but not carried forward structurally.

Updated March 27, 2026. Airtable base appvUfaPd9ejb5fHb.
Sync scripts: oeff-v2-build.py, oeff-v2-populate.py, oeff-v2-enrich.py