airtable-participants

从 Ceremonia Airtable 基地读取和查询静修参与者数据。 当被问及参与者、订户数量、撤退时使用此技能 出席情况、联系方式、电话号码、电子邮件或捐赠状态。 也被其他技能（电子邮件新闻通讯、短信外展）用来检索 收件人列表。默认为只读。

# Airtable Participants Skill

## Purpose

Query retreat participant data from the Ceremonia Airtable base. This is the
authoritative source of truth for who receives emails and SMS messages.
Access is read-only by default — record modifications require Austin's
explicit instruction per change.

## Required Setup

Ensure AIRTABLE_API_KEY is set in .env.

You will also need:
- **Base ID:** [VERIFY — find in Airtable API docs at airtable.com/developers or ask Austin]
- **Table name:** [VERIFY — confirm the participant table name with Austin]

Store confirmed values in TOOLS.md and MEMORY.md once verified.

## Expected Data Structure

Participant records are expected to have at minimum these fields:

| Field | Type | Description |
|-------|------|-------------|
| name | Text | Full name |
| email | Email | Primary email address |
| phone | Phone | E.164 format preferred (+1XXXXXXXXXX) |
| retreat_status | Select | e.g., active, alumni, prospective, unsubscribed |
| tags | Multi-select | e.g., february-2026, guide-circle, donor |
| last_contact | Date | Most recent outreach date |
| donation_status | Select | e.g., donor, non-donor |

[VERIFY actual field names with Austin on first use — update this section when confirmed]

## Common Query Patterns

### Get all active participants (for newsletter sends)
```bash
curl -s "https://api.airtable.com/v0/{BASE_ID}/{TABLE_NAME}?filterByFormula={retreat_status}='active'&fields[]=name&fields[]=email" \
  -H "Authorization: Bearer $AIRTABLE_API_KEY" | jq '.records[].fields'
```

### Get participants with phone numbers (for SMS campaigns)
```bash
curl -s "https://api.airtable.com/v0/{BASE_ID}/{TABLE_NAME}?filterByFormula=AND({retreat_status}='active',{phone}!='')&fields[]=name&fields[]=phone" \
  -H "Authorization: Bearer $AIRTABLE_API_KEY" | jq '.records[].fields'
```

### Get participant count by status
```bash
curl -s "https://api.airtable.com/v0/{BASE_ID}/{TABLE_NAME}?fields[]=retreat_status" \
  -H "Authorization: Bearer $AIRTABLE_API_KEY" | jq '[.records[].fields.retreat_status] | group_by(.) | map({status: .[0], count: length})'
```

### Get participants by tag
```bash
curl -s "https://api.airtable.com/v0/{BASE_ID}/{TABLE_NAME}?filterByFormula=FIND('february-2026',ARRAYJOIN({tags}))" \
  -H "Authorization: Bearer $AIRTABLE_API_KEY" | jq '.records[].fields'
```

Note: Airtable paginates at 100 records. Use the offset parameter from the response to fetch subsequent pages:
```bash
curl -s "https://api.airtable.com/v0/{BASE_ID}/{TABLE_NAME}?offset={OFFSET_TOKEN}" \
  -H "Authorization: Bearer $AIRTABLE_API_KEY" | jq .
```
Always paginate fully before reporting totals or building recipient lists.

## Behavior Rules

- Read-only by default — never PATCH, POST, or DELETE Airtable records without Austin's explicit instruction per operation
- When building a recipient list for email or SMS: always filter out records where retreat_status is 'unsubscribed'
- Never include email addresses or phone numbers in Slack messages — summarize counts and segments only
- If a query returns 0 results unexpectedly: report the issue to Austin rather than sending to an empty list
- Paginate all list queries fully — do not report partial counts or build partial recipient lists
- If Airtable API returns an error: surface it to Austin immediately with the error code and message

## Record Modification (Requires Austin Approval)

When Austin instructs a record change (e.g., marking someone unsubscribed, updating last_contact):
1. Confirm the specific change with Austin before executing
2. Execute the PATCH request
3. Log the change in memory/logs/crm-writes/YYYY-MM-DD.md with: record name/email, field changed, old value, new value, Austin's instruction timestamp

## Example Invocations

- "How many active participants do we have?"
- "Get the email list for the February retreat attendees"
- "Who attended the last three retreats?"
- "How many people have phone numbers in the system?"
- "Mark [name] as unsubscribed" (requires Austin approval)
- "Pull the full active participant list for the newsletter"
- "How many people joined since January?"

---

## 中文说明

# Airtable Participants Skill

## 用途

从 Ceremonia Airtable 基地查询静修参与者数据。这是关于谁会收到邮件和短信的权威真实来源。访问默认为只读——修改记录需要 Austin 针对每次更改给出明确指示。

## 必需设置

确保在 .env 中设置了 AIRTABLE_API_KEY。

你还需要：
- **Base ID：** [VERIFY — 在 airtable.com/developers 的 Airtable API 文档中查找，或询问 Austin]
- **Table name：** [VERIFY — 向 Austin 确认参与者表的名称]

确认值后将其存入 TOOLS.md 和 MEMORY.md。

## 预期数据结构

参与者记录预期至少包含以下字段：

| Field | Type | Description |
|-------|------|-------------|
| name | Text | 全名 |
| email | Email | 主要电子邮件地址 |
| phone | Phone | 推荐 E.164 格式（+1XXXXXXXXXX） |
| retreat_status | Select | 例如 active、alumni、prospective、unsubscribed |
| tags | Multi-select | 例如 february-2026、guide-circle、donor |
| last_contact | Date | 最近一次外联日期 |
| donation_status | Select | 例如 donor、non-donor |

[首次使用时向 Austin 确认实际字段名称 — 确认后更新本节]

## 常见查询模式

### 获取所有活跃参与者（用于发送新闻通讯）
```bash
curl -s "https://api.airtable.com/v0/{BASE_ID}/{TABLE_NAME}?filterByFormula={retreat_status}='active'&fields[]=name&fields[]=email" \
  -H "Authorization: Bearer $AIRTABLE_API_KEY" | jq '.records[].fields'
```

### 获取有电话号码的参与者（用于短信活动）
```bash
curl -s "https://api.airtable.com/v0/{BASE_ID}/{TABLE_NAME}?filterByFormula=AND({retreat_status}='active',{phone}!='')&fields[]=name&fields[]=phone" \
  -H "Authorization: Bearer $AIRTABLE_API_KEY" | jq '.records[].fields'
```

### 按状态获取参与者数量
```bash
curl -s "https://api.airtable.com/v0/{BASE_ID}/{TABLE_NAME}?fields[]=retreat_status" \
  -H "Authorization: Bearer $AIRTABLE_API_KEY" | jq '[.records[].fields.retreat_status] | group_by(.) | map({status: .[0], count: length})'
```

### 按标签获取参与者
```bash
curl -s "https://api.airtable.com/v0/{BASE_ID}/{TABLE_NAME}?filterByFormula=FIND('february-2026',ARRAYJOIN({tags}))" \
  -H "Authorization: Bearer $AIRTABLE_API_KEY" | jq '.records[].fields'
```

注意：Airtable 每 100 条记录分页。使用响应中的 offset 参数获取后续页面：
```bash
curl -s "https://api.airtable.com/v0/{BASE_ID}/{TABLE_NAME}?offset={OFFSET_TOKEN}" \
  -H "Authorization: Bearer $AIRTABLE_API_KEY" | jq .
```
在报告总数或构建收件人列表之前，始终完整地翻页。

## 行为规则

- 默认只读——未经 Austin 针对每次操作的明确指示，绝不对 Airtable 记录执行 PATCH、POST 或 DELETE
- 在为邮件或短信构建收件人列表时：始终过滤掉 retreat_status 为 'unsubscribed' 的记录
- 绝不在 Slack 消息中包含电子邮件地址或电话号码——仅汇总数量和分组
- 如果某查询意外返回 0 条结果：向 Austin 报告该问题，而不是向空列表发送
- 完整翻页所有列表查询——不要报告部分计数或构建部分收件人列表
- 如果 Airtable API 返回错误：立即将错误代码和消息呈报给 Austin

## 记录修改（需要 Austin 批准）

当 Austin 指示更改记录时（例如将某人标记为退订、更新 last_contact）：
1. 在执行前向 Austin 确认具体更改
2. 执行 PATCH 请求
3. 在 memory/logs/crm-writes/YYYY-MM-DD.md 中记录此次更改，包含：记录名称/邮箱、更改的字段、旧值、新值、Austin 指示的时间戳

## 调用示例

- "How many active participants do we have?"
- "Get the email list for the February retreat attendees"
- "Who attended the last three retreats?"
- "How many people have phone numbers in the system?"
- "Mark [name] as unsubscribed"（需要 Austin 批准）
- "Pull the full active participant list for the newsletter"
- "How many people joined since January?"