# SKILL — lfcs-airtable-sync

## Purpose

Universal vault-markdown → Airtable upsert adapter. **Single source of write logic.** Every other LFCS skill calls this as the final step after vault writes commit. Centralises:
- Frontmatter / body parsing per target table schema
- Idempotency (search by primary field → update-or-create)
- Failure isolation (Airtable failure NEVER blocks the calling skill)
- Audit logging (every batch logged to `_System/_AgentLog/airtable-sync.log`)

Created 2026-05-04 as part of the LFCS-Operations base adoption (see plan `_System/Investigations/LFCS-Bid-System-Plan-2026-05-03.md` Section 5 revision block).

## Trigger

**Programmatic only** — invoked by every other lfcs-* skill at the end of its Behaviour. Not a user-facing skill. Rocky should not need to call it directly.

User-facing equivalents (if Rocky needs to manually re-sync something):
- "Sync 2626 bid into Airtable"
- "Re-upsert 2602 drawings"
- "Audit Airtable against vault for {job_no}"

## Inputs

Required:
- `vault_path` — file or folder. If folder, walks it for any file matching `target_table` schema.
- `target_table` — one of: `Jobs`, `Drawings`, `Drawing Elements`, `Revisions`, `Specifications`, `Suppliers`, `Bids`, `Bid Line Items`, `RFIs`, `Variations`, `Rates`. May be a comma-separated list.
- `mode` — one of:
  - `upsert` (default) — search by primary field, update existing or create new
  - `audit-only` — read vault + read Airtable, report diffs, write nothing

Optional:
- `job_no` — scope writes to a specific job (filters which records get touched)
- `dry_run` — boolean (default false), report what would happen without writing

## Behaviour

1. **Boot context:** read `_System/CLAUDE.md` Airtable section + `11_OpsMan_LFCS/_Brain/CLAUDE.md` schema cheatsheet. Cache table IDs + primary field IDs. (Skip if already cached this session.)
2. **Resolve `vault_path`:**
   - If file, parse it directly.
   - If folder, walk for files matching the conventional path patterns per `target_table` (e.g. for `Bids`: `*/02 - Scope and Pricing/02a - Quotes and Estimates/*-Tender-Summary-*.md`).
3. **Parse markdown:**
   - Extract YAML frontmatter into a dict
   - Extract body sections (per markdown headings) into structured fields
   - Map vault field names → Airtable field names per per-table mapping (defined in this SKILL.md per-table appendix below)
4. **Validate mapping:** every Airtable field that's required (primary, link FKs) must resolve from the parsed source. If not, mark the row `Status = Needs Review` (where applicable) and continue; log to `_System/Conflicts.md` for Rocky.
5. **Search Airtable for existing record** — query by primary field value via MCP `search_records` or `list_records_for_table` filter.
6. **Upsert** via MCP `update_records_for_table` with `performUpsert: true` + `fieldsToMergeOn: ["{primary_field_name}"]`. Single API call per batch (up to 10 records per batch per Airtable API limit).
7. **For computed fields** (e.g. `Bid Line Items.Total ex GST` is a currency field, not formula — Airtable API rejects programmatic formula creation), compute the value before upsert. If Rocky has converted the field to formula in the UI, our currency-write will silently fail and that's fine — formula recalculates on the underlying field updates.
8. **Resolve link FKs** — for any `multipleRecordLinks` field, the value is an array of Airtable record IDs. Skill must search the linked table by primary field value and resolve to record ID before upsert.
9. **Log result** to `_System/_AgentLog/airtable-sync.log` (append-only):
   ```
   YYYY-MM-DD HH:MM — {calling_skill or user} — table={target_table} job={job_no} mode={mode}
     vault_path: {abs path}
     rows-created: {n}
     rows-updated: {n}
     rows-skipped: {n} (reasons: {list})
     rows-failed: {n} (reasons: {list})
     wall-clock: {seconds}
   ```
10. **Return to caller:** `{created, updated, skipped, failed, errors[]}`. Caller decides what to do — but per hard rules, caller MUST treat failures as non-blocking.

## Files to read (in order)

```
HQ-Vault/_System/CLAUDE.md                                       ← Airtable section
HQ-Vault/11_OpsMan_LFCS/_Brain/CLAUDE.md                         ← schema cheatsheet (table IDs + primary fields)
HQ-Vault/11_OpsMan_LFCS/Operations/lfcs-standards/element-taxonomy.md  ← controlled vocab for Element Type validation
HQ-Vault/11_OpsMan_LFCS/Operations/lfcs-standards/folder-spec.md ← path patterns for vault discovery
{vault_path}                                                      ← input file(s) being synced
```

## Airtable behaviour

- **Reads:** all 11 tables (search by primary field for upsert idempotency); FK targets when resolving link fields.
- **Writes:** any of the 11 tables, depending on `target_table` input.
- **Idempotency:** every upsert uses `performUpsert: true` + `fieldsToMergeOn: ["{primary_field}"]`. Re-running with identical inputs = identical Airtable state. No duplicates.

## Per-table mapping appendix

Each calling skill specifies its `target_table` and this appendix documents the vault → Airtable field mapping.

### Jobs (`tblcL2EX2w34c1VPe`)
Source: `{job_folder}/00-brief.md` frontmatter.
Mapping:
- frontmatter `job_no` → Job Number (primary)
- frontmatter `site` → Name
- frontmatter `client` → Client
- frontmatter `status` → Status (single-select; allowed: Bid, Awarded, On-site, Closed, Lost)
- frontmatter `bid_due` → Bid Due (ISO date)
- vault path → Vault Path (singleLineText, e.g. `Operations/Jobs/Upcoming/2602 - ...`)
- frontmatter `drive_folder_url` → Drive Folder URL (if present)
- body summary → Notes

### Drawings (`tblZPi452zZKvLliE`)
Source: `{job_folder}/03 - Plans and Specifications/03a - Issued for Construction/Drawing-Index.md` per-drawing YAML blocks.
Mapping:
- per-drawing YAML `drawing_no` → Drawing Number (primary)
- per-drawing YAML `title` → Title
- per-drawing YAML `discipline` → Discipline (single-select)
- per-drawing YAML `rev` → Current Revision Number
- per-drawing YAML `date_issued` → Revision Date
- per-page count → PDF Page
- Drive URL of source PDF → Source PDF URL
- vault MD path → Vault MD Path
- per-drawing summary block → AI Summary
- linked job (resolved by job_no) → Job

### Drawing Elements (`tbl3wYZTpiuNxaRvH`)
Source: `Drawing-Index.md` per-drawing `elements:` arrays (text-pass + vision-pass).
Mapping:
- element label → Element Name (primary)
- element type tag → Element Type (single-select; mirror existing choices)
- element quantity → Quantity
- element unit → Unit (single-select; mirror existing choices)
- element spec/material → Spec / Material
- sheet ref → Sheet Reference
- linked drawing → Drawing
- linked job (denormalised) → Job
- extraction confidence → Confidence (single-select; `Needs review` lowercase r)

### Bids (`tblbDMfYrPHOE3wOX`)
Source: `{job_folder}/02 - Scope and Pricing/02a - Quotes and Estimates/{job_no}-Tender-Summary-*.md` frontmatter + body.
Mapping:
- generated as `{job_no}-bid-v{N}` → Bid ID (primary)
- linked job → Job
- frontmatter `status` → Status
- body Pricing Basis → Pricing Basis
- frontmatter `bid_due` → Bid Due
- frontmatter `submitted` → Submitted Date
- body total ex GST → Total ex GST
- computed (Total ex GST × 0.10) → GST
- body Total inc GST → Total inc GST
- frontmatter `margin_pct` → Margin %
- Drive URL of submission PDF → Submission File URL
- Drive URL of source xlsx → Source xlsx URL
- body Win/Loss → Win/Loss reason
- body Notes → Notes

### Bid Line Items (`tbllVlBA0X0pwoCdv`)
Source: `{job_folder}/02 - Scope and Pricing/02b - BOQ and Rate Schedules/{job_no}-Rate-Buildup-*.md` body line tables.
Mapping:
- generated as `{bid_id}-L{n}` → Line ID (primary)
- linked bid → Bid
- table row item # → Item #
- table row description → Description
- table row unit → Unit (single-select; mirror existing choices)
- table row qty → Qty
- table row rate ex GST → Rate ex GST
- computed (Qty × Rate ex GST) → Total ex GST [computed; if Rocky converts to formula in UI, this write becomes silent no-op]
- linked rate (resolved by Activity primary field) → Source Rate
- linked drawing element (if traced) → Element
- table row notes → Notes

### Rates (`tbl9fewvyRmL18UrA`)
Source: `lfcs-standards/rate-card.md` + per-job `{job_folder}/02b/Rate-Buildup-*.md` (line items become Rates rows for cross-job reuse).
Mapping per Rate row:
- activity description → Activity (primary)
- category → Category (single-select)
- element type (from element-taxonomy mapping) → Element Type (single-select)
- unit → Unit (single-select)
- base rate → Base Rate
- crew size → Crew Size
- productivity → Productivity
- productivity unit → Productivity Unit
- linked source job → Source Job
- linked source spec (if applicable) → Source Spec
- timestamp → Last Updated
- confidence rating → Confidence (single-select; `Needs review` lowercase r)
- notes → Notes

### Suppliers (`tblPCBJeNz8XpDum0`)
Source: extracted from per-job `02b/Rate-Buildup-*.md` Materials sheet sources, or manual `Operations/lfcs-standards/suppliers.md` (future).

### Specifications (`tblLyH61zVNI9I2Zu`)
Source: extracted from per-job `03a/*.pdf` spec files (R-series, Sydney Water, AS/NZS) frontmatter or first-page metadata.

### RFIs (`tbl6cWepHgiVMpt40`)
Source: `{job_folder}/06 - Correspondence/06b - RFIs/RFI{NN}.pdf` (page 1 metadata) or markdown sibling.

### Variations (`tblpyDAPubw1V9cNO`)
Source: `{job_folder}/08 - Variations/VO-{NN}.md` (created by `lfcs-variation-drafter` v2 skill).

### Revisions (`tblC04YN5rtqlKvNK`)
Source: drawing revision block within `Drawing-Index.md` per-drawing entries.

## Output format

To caller (programmatic JSON):

```json
{
  "ok": true,
  "table": "Bid Line Items",
  "rows_created": 8,
  "rows_updated": 0,
  "rows_skipped": 0,
  "rows_failed": 0,
  "errors": [],
  "wall_clock_seconds": 2.4
}
```

To `_System/_AgentLog/airtable-sync.log` (append-only):
```
2026-05-04 15:30 — lfcs-bid-prep-doc — table=Bids job=2626 mode=upsert
  vault_path: HQ-Vault/.../2626-Tender-Summary-Draft-2026-05-04.md
  rows-created: 1, rows-updated: 0, rows-skipped: 0, rows-failed: 0
  wall-clock: 1.8s
```

## Hard rules

- **Never delete records.** Mark `Status = Superseded` instead per the cross-system no-delete rule. Mirrors vault `_Archive/` pattern.
- **Never block the calling skill.** If the Airtable write fails (network, rate limit, schema drift, anything), return `{ok: false, errors: [...]}`, log to `_System/Conflicts.md`, and let the caller continue. The vault commit is what matters; Airtable can be reconciled later.
- **On parse error** (frontmatter missing required field, body schema not matching expected shape): write to `_System/Conflicts.md` with the file path and parse error, mark intended Airtable row `Status = Needs Review` (where the table has a Status field), continue with other rows in the batch.
- **Idempotent always.** Every write is an upsert by primary field. Running this skill twice with identical inputs produces identical Airtable state.
- **Never write to a table not listed in the schema cheatsheet.** If `target_table` is unrecognised, abort with error before any API call.
- **Read-before-edit on Airtable** — fetch existing record first, diff-then-update where possible (cheaper API call + cleaner audit log).
- **Vault is source of truth.** If the Airtable record diverges from the vault file, vault wins. `audit-only` mode reports the diff; `upsert` mode overwrites Airtable to match vault.

## Dependencies

- Airtable MCP server (one of `mcp__5bf946e8-61e3-44f7-ad16-2e2bb27b2ea4__*` tools available)
- 2602 + 2626 vault outputs (regression fixtures)
- `_System/CLAUDE.md` Airtable section
- `11_OpsMan_LFCS/_Brain/CLAUDE.md` schema cheatsheet

## Regression fixture

Must be runnable against 2602 + 2626 vault outputs and produce idempotent Airtable state. Specifically:
1. Run `lfcs-airtable-sync vault_path={2602 folder} target_table=Jobs` — confirms 2602 Job row matches `00-brief.md`.
2. Run `lfcs-airtable-sync vault_path={2626 folder} target_table=Bids,Bid Line Items` — confirms 2626-bid-v1 + 8 line items match `Tender-Summary-Draft.md`.
3. Re-run step 2 — confirm zero new rows, zero updates (true idempotency check).

## Failure modes

| Failure | Detection | Recovery |
|---|---|---|
| Airtable API timeout / 5xx | Per-batch try/except | Return `{ok: false}`, log to Conflicts.md, caller continues |
| Schema drift (table renamed, field deleted) | Pre-flight `list_tables_for_base` cache check | Abort batch, log, alert Rocky in next sit-rep |
| Primary field value collision (two different vault sources, same Bid ID) | Pre-write search returns >1 match | Abort that row, mark `Status = Needs Review`, log, continue batch |
| FK target not found (link to a Job that doesn't exist in Airtable yet) | Pre-write FK resolution | Create the parent Job row first (recursive call to lfcs-airtable-sync), then retry FK |
| Rate limit (429) | Per-batch | Exponential backoff (1s, 2s, 4s, 8s — max 4 retries), then fail-the-batch behaviour |