DAF Operations
Database Schema
Seven tables. One source of truth for activations, program managers, governance, and compliance.
Every dashboard — the PM's working checklist, the ED's operations view, the Box Office tracker — reads from and writes to this single Airtable base. No tool maintains its own data. The base is the record. The dashboards are views into it.
The schema is designed to scale from Year One's 16 activations to whatever Year Two and beyond require, without restructuring. New activations, new PMs, and new governance obligations are rows, not columns.
Seven tables. T1 (Activations) is the spine of the entire base — every other table links to it. T7 (Purchasers) already exists and connects the Box Office to the activation record. The new tables add operational and governance layers on top of the financial infrastructure already built.
T1 Activations
Links to: T3, T4, T5, T7
Primary spine of the base
T2 Program Managers
Links to: T3
Master PM roster
T3 PM Assignments
Links to: T1, T2, T4
One row per PM per activation
T4 Responsibility Progress
Links to: T1, T3
One row per responsibility per assignment
T5 Workshops
Links to: T1
Five rows per activation
T6 Governance Events
Standalone
Board, compliance, reporting
T7 Purchasers
Links to: T1
Existing Box Office table
One record per activation. This is the primary record every other table references. It holds the canonical identity, pipeline stage, financial parameters, CII inputs, and post-activation scores for each program.
| Field Name | Type | Notes |
|---|---|---|
| Activation ID | Text | Primary field. Format: ACT-YYYY-NNN. Never auto-generated — assigned manually to ensure intentional sequencing.Used in all external references: Stripe metadata, PM Dashboard URL, board reports. |
| Program Name | Text | Full public-facing program name. Example: Third Wednesday Jazz Series. |
| Proposer | Text | Individual or organization proposing the program. |
| Proposer Email | Primary proposer contact. Used for go/no-go notifications and refund communications. | |
| Pipeline Stage | Single Select | Options: Intake • Develop • Validate • Activate • Graduate • Archived. Updated manually by ED.This is the field the ED Dashboard uses for the pipeline view. Accuracy matters. |
| Validation Status | Single Select | Options: Campaigning • Validated • Did Not Validate • Cancelled.Distinct from Pipeline Stage. A program can be in Validate stage with status Campaigning. |
| Fiscal Year | Single Select | Options: FY2026 • FY2027 • FY2028. DAF fiscal year is October through September. |
| Discipline | Single Select | Options: Music • Visual Art • Theater • Dance • Literary • Culinary • Film • Multi-Discipline • Other. |
| Activation Date | Date | The program night. All deadline calculations derive from this field. |
| Venue | Text | Default: Arts Junction at 202 West Center. Free text to accommodate future venues. |
| Validation Deadline | Formula | DATEADD({Activation Date}, -35, 'days')Campaign must close 35 days before activation. This is the go/no-go deadline date. |
| Pre-Activation Opens | Formula | DATEADD({Activation Date}, -21, 'days') |
| Post-Activation Due | Formula | DATEADD({Activation Date}, 7, 'days') |
| Days to Activation | Formula | DATETIME_DIFF({Activation Date}, TODAY(), 'days')Negative after activation night. ED Dashboard uses this for sorting and urgency flags. |
| Field Name | Type | Notes |
|---|---|---|
| Capacity | Number | Maximum seats. Used as Stripe Payment Link quantity cap. |
| Cost to Activate | Currency | Total projected costs. Source of F1 denominator. |
| Ticket Price — General | Currency | Standard admission. |
| Ticket Price — Supporter | Currency | Premium tier. Leave blank if not offered. |
| Tickets Required to Validate | Formula | CEILING({Cost to Activate} / {Ticket Price - General}) |
| Stripe Product ID | Text | Populated when Stripe product is created. Used for Zapier webhook routing. |
| Purchasers | Linked (T7) | Auto-populated by Zapier from Stripe webhook. |
| Total Committed | Rollup | COUNT of linked Purchasers where Refund Status = Active. |
| Total Revenue Committed | Rollup | SUM of Amount Paid (dollars) from linked Purchasers where Refund Status = Active. |
| Validation Progress % | Formula | ROUND({Total Committed} / {Tickets Required to Validate} * 100, 0) |
| Field Name | Type | Notes |
|---|---|---|
| Actual Attendance | Number | Final reconciled door count from Resp. 23. |
| F1 Score | Number (0–100) | Pre-commitment rate. Populated by Activation Report Generator. |
| F2 Score | Number (0–100) | Resident share. Derived from Purchasers rollup. |
| F3 Score | Number (0–100) | Substitution rate. From survey dataset (Resp. 24). |
| F4 Score | Number (0–100) | Repeat participation. Zero on first activation by design. |
| F5 Score | Number (0–100) | Adjacent business lift. From baseline comparison (Resp. 26). |
| CII Composite | Formula | ({F1 Score}*0.30)+({F2 Score}*0.25)+({F3 Score}*0.20)+({F4 Score}*0.15)+({F5 Score}*0.10) |
| CII Threshold | Formula | IF({CII Composite}>=70,"Graduate",IF({CII Composite}>=50,"Develop","Redesign/Retire")) |
| Survey Response Rate | Number (%) | From Resp. 24 submission. PM performance standard field. |
| ZIP Capture Rate | Number (%) | From Resp. 25 submission. PM performance standard field. |
| Start Time Variance (min) | Number | Positive = late, negative = early. ±15 is the standard. |
| Debrief Submitted | Checkbox | Marked true when Resp. 27 is received. |
| Financial Settlement Submitted | Checkbox | Marked true when Resp. 28 is received and accepted. |
| Activation Notes | Long Text | ED field. Narrative notes on the activation for board reporting and future reference. |
Master roster of all Program Managers engaged by the Foundation. One record per person. A PM may have multiple assignments across multiple activations; those live in T3, not here.
| Field Name | Type | Notes |
|---|---|---|
| PM Display Name | Text | Primary field. Format: Last-First. Example: Thompson-Ron. This is the reference that appears on all activation records and the ED Dashboard.Formatted Last-First to sort correctly in Airtable views and ED Dashboard tables. |
| First Name | Text | |
| Last Name | Text | |
| Used to send PM Dashboard URL on assignment. | ||
| Phone | Phone | |
| Contract Status | Single Select | Options: Active • Inactive • Prospective. |
| Year One Activations | Number | Count of activations in FY2026. Manual entry; used for workload tracking. |
| Assignments | Linked (T3) | All PM assignments. Auto-populated as T3 records are created. |
| Notes | Long Text | ED field. Performance observations, re-engagement notes, anything not captured in the performance standard fields on T4. |
One record per PM per activation. This is the join table between T1 and T2, and the record the PM Dashboard URL is built from. Creating a T3 record is the act of assigning a PM to an activation. It generates the dashboard URL and scopes all of the PM's Responsibility Progress records in T4.
| Field Name | Type | Notes |
|---|---|---|
| Assignment ID | Text | Primary field. Format: ASSIGN-ACT-YYYY-NNN-LAST. This value is the URL parameter.Manually assigned. Copy-paste into PM Dashboard URL to generate the shareable link. |
| Activation | Linked (T1) | The activation this PM is assigned to. |
| Program Manager | Linked (T2) | The PM assigned. |
| PM Display Name | Formula | Lookup from T2. Displays Last-First for all ED Dashboard views. |
| Program Name | Formula | Lookup from T1. Populates PM Dashboard header automatically. |
| Activation Date | Formula | Lookup from T1. All deadline calculations in the PM Dashboard source from this. |
| Contract Issued | Checkbox | ED marks true when activation contract is sent to PM. |
| Contract Signed | Checkbox | ED marks true when signed contract is received. |
| Dashboard URL | URL | The full shareable PM Dashboard URL. Format: daf.netlify.app/pm?id={Assignment ID}. Copy and send to PM on assignment. |
| Date Assigned | Date | When the assignment was created. |
| Responsibility Progress | Linked (T4) | All 28 responsibility records for this assignment. Auto-generated when assignment is created. |
| Responsibilities Complete | Rollup | COUNTIF of linked T4 records where Completed = true. |
| Progress % | Formula | ROUND({Responsibilities Complete} / 28 * 100, 0) |
One record per responsibility per assignment. When an assignment is created in T3, a script creates 28 T4 records automatically — one for each numbered responsibility in the Scope of Work. The PM Dashboard reads and writes these records. When a PM checks a box, it updates the Completed field on the corresponding T4 record.
| Field Name | Type | Notes |
|---|---|---|
| Record ID | Text | Auto-generated by Airtable. Format: {Assignment ID}-R{NN}. Example: ASSIGN-ACT-2026-001-MARTINEZ-R09. |
| Assignment | Linked (T3) | The PM assignment this responsibility belongs to. |
| Activation | Linked (T1) | Direct link to the activation. Allows ED to filter all responsibility records by activation. |
| Responsibility Number | Text | Two-digit zero-padded. Values: 01 through 28. |
| Phase | Single Select | Options: Pre-Activation • Activation Day • Post-Activation. Pre-populated on record creation. |
| Responsibility Title | Text | Short title. Pre-populated on record creation from responsibility definitions. |
| Completed | Checkbox | The PM Dashboard writes to this field when a checkbox is ticked. This is the primary field the ED Dashboard reads for progress tracking. |
| Completed Date | Date | Timestamp set automatically when Completed is changed to true. |
| Notes | Long Text | Optional. PM can add notes on a responsibility — used for documenting Resp. 20 incidents and Resp. 27 debrief observations. |
| Deadline | Formula | Calculated from Activation Date lookup and the responsibility's deadline offset. The PM Dashboard reads this to display deadline dates and urgency flags. |
| Status | Formula | IF(Completed, "Done", IF(TODAY() > Deadline, "Overdue", IF(DATETIME_DIFF(Deadline, TODAY(), 'days') <= 3, "Due Soon", "Pending"))) |
Five records per activation — one for each workshop in the proposer curriculum. Created when an activation enters the Develop stage. The ED Dashboard displays workshop status alongside PM progress. The PM Dashboard shows workshops as read-only awareness rows.
| Field Name | Type | Notes |
|---|---|---|
| Workshop ID | Text | Format: {Activation ID}-W{N}. Example: ACT-2026-001-W3. |
| Activation | Linked (T1) | |
| Workshop Number | Number | 1 through 5. |
| Title | Text | Audience Mapping • Pricing Strategy • Commitment Campaign • Production Planning • Financial Management. |
| Scheduled Date | Date | Set when the proposer enters the Develop stage. Calculated from activation date and workshop interval. |
| Deliverable | Text | The required deliverable for this workshop. Pre-populated from the CIS curriculum. |
| Status | Single Select | Options: Scheduled • Completed • Missed • Rescheduled. |
| Deliverable Received | Checkbox | ED marks true when the workshop deliverable is submitted and accepted. Stage 3 authorization requires all five true. |
| Notes | Long Text | Foundation observations on deliverable quality. Used for proposer development support. |
Every board meeting, compliance filing, reporting deadline, and operational obligation that belongs to the Foundation as an institution rather than to a specific activation. This table does not link to T1. It is the ED's governance calendar — standalone, comprehensive, and growing.
| Field Name | Type | Notes |
|---|---|---|
| Event Name | Text | Primary field. Descriptive and specific. Example: Q1 FY2027 Board Meeting • Form 990 Filing Deadline FY2026 • CIS Q2 Summary Draft Due. |
| Event Type | Single Select | Options: Board Meeting • IRS Compliance • State Compliance • CIS Reporting • Financial • Operational • Grant • Other.Grant type is placeholder for Year Three. Add now so the field exists when needed. |
| Date | Date | The hard deadline or scheduled event date. |
| Preparation Deadline | Date | Optional. Date by which preparation must begin. Example: Board meeting on March 15 requires board packet by March 8. |
| Fiscal Year | Single Select | FY2026 • FY2027 • FY2028. Allows multi-year filtering in ED Dashboard. |
| Recurrence | Single Select | Options: One-time • Annual • Quarterly • Monthly.Airtable does not auto-generate recurring events. Create each instance as a separate record. This field is for documentation and filtering only. |
| Status | Single Select | Options: Upcoming • In Preparation • Complete • Overdue • Waived. |
| Owner | Single Select | Options: Executive Director • Board • CPA • Foundation (joint). For Year One, most will be Executive Director. |
| External Deadline | Checkbox | True if the deadline is set by an external authority (IRS, State of Texas, board bylaws). These are hard. Internal deadlines are soft. |
| Board Packet Required | Checkbox | True for board meetings. Triggers a preparation reminder 7 days before the event date. |
| Notes | Long Text | Filing instructions, relevant statutes, CPA contacts, prior year notes. This field grows over time into an institutional knowledge base. |
| Related Documents | URL | Link to Google Drive folder, IRS.gov page, or Squarespace page where supporting materials live. |
| Event Name | Type | Date | Hard Deadline |
|---|---|---|---|
| Q1 FY2027 Board Meeting | Board Meeting | TBD — Saturday, Dec 2026 | No |
| Q2 FY2027 Board Meeting | Board Meeting | TBD — Saturday, Mar 2027 | No |
| Q3 FY2027 Board Meeting | Board Meeting | TBD — Saturday, Jun 2027 | No |
| Q4 FY2027 Board Meeting & Annual Review | Board Meeting | TBD — Saturday, Sep 2027 | No |
| CIS Q1 Summary — Draft Due | CIS Reporting | January 15, 2027 | No |
| CIS Q2 Summary — Draft Due | CIS Reporting | April 15, 2027 | No |
| CIS Q3 Summary — Draft Due | CIS Reporting | July 15, 2027 | No |
| CIS Q4 Annual Summary — Draft Due | CIS Reporting | October 15, 2027 | No |
| Form 990 Filing Deadline — FY2026 | IRS Compliance | February 15, 2027 | Yes |
| Form 8868 Extension Deadline (if filed) | IRS Compliance | August 15, 2027 | Yes |
| Texas Franchise Tax Report — FY2026 | State Compliance | May 15, 2027 | Yes |
| Texas Public Information Report — FY2026 | State Compliance | May 15, 2027 | Yes |
| Engage CPA / Tax Preparer | Operational | November 1, 2026 | No |
| Annual Budget Approval | Board Meeting | TBD — before Oct 1, 2027 | No |
This table already exists in the Box Office base. No structural changes are required. The only addition is ensuring the linked record field to T1 (Activations) uses the new Activation ID field as the join key rather than the old Programs record ID. Verify the Zapier webhook still writes to the correct linked record field after the Programs table is renamed to Activations.
Create one Airtable Personal Access Token with read/write scopes on the DAF base. Store it in the Netlify environment variables under the key AIRTABLE_API_KEY — exactly where the CIS Toolkit proxy already stores the Anthropic key. The existing serverless function handles all API calls. The dashboards never receive the token directly.
The PM Dashboard URL contains the Assignment ID. Knowledge of the URL is the access credential. This is appropriate for a small-team Year One operation. The PM cannot access any other PM's dashboard because they do not have the other URL. The ED Dashboard is Netlify password-protected and not accessible via URL alone.
| Dashboard | Reads | Writes |
|---|---|---|
| PM Dashboard | T3 (assignment config), T4 (responsibility status) | T4 only (Completed field and Completed Date) |
| ED Dashboard | T1, T2, T3, T4, T5, T6, T7 | T1 (stage, status, CII scores), T6 (event status). All other tables read-only from ED Dashboard. |
| Box Office (existing) | T1 (validation progress) | T7 (via Zapier, not directly) |