Airtable Operations for MI Platform

Base Configuration

code

Base ID: appEEWaGtGUwOyOhm
API: https://api.airtable.com/v0/{baseId}/{tableIdOrName}
Auth: Authorization: Bearer {AIRTABLE_API_KEY}

Table IDs (Use IDs, Not Names)

Table	ID	Primary Field
Forces	`tblbAjBEdpv42Smpw`	name
Contacts	`tbl0u9vy71jmyaDx1`	force (linked)
Signals	`tblez9trodMzKKqXq`	title
Opportunities	`tblJgZuI3LM2Az5id`	name

Rate Limits (Critical)

•5 requests/second per base — ALWAYS add delays
•Batch create/update: max 10 records per request
•List records: max 100 records per page

javascript

// Required delay between requests
const sleep = (ms) => new Promise(r => setTimeout(r, 200));

// Batch helper
const chunk = (arr, size) => {
  const chunks = [];
  for (let i = 0; i < arr.length; i += size) {
    chunks.push(arr.slice(i, i + size));
  }
  return chunks;
};

CRUD Operations

Create Records (Batch)

javascript

async function createRecords(tableId, records) {
  const url = `https://api.airtable.com/v0/appEEWaGtGUwOyOhm/${tableId}`;
  const batches = chunk(records, 10);
  const results = [];

  for (const batch of batches) {
    const response = await fetch(url, {
      method: 'POST',
      headers: {
        'Authorization': `Bearer ${process.env.AIRTABLE_API_KEY}`,
        'Content-Type': 'application/json'
      },
      body: JSON.stringify({
        records: batch.map(r => ({ fields: r })),
        typecast: true  // Auto-convert field values
      })
    });

    const data = await response.json();
    if (data.error) throw new Error(data.error.message);
    results.push(...data.records);
    await sleep(200);
  }

  return results;
}

Read Records with Pagination

javascript

async function listAllRecords(tableId, options = {}) {
  const { filterByFormula, fields, sort, view } = options;
  const records = [];
  let offset = null;

  do {
    const params = new URLSearchParams();
    if (filterByFormula) params.append('filterByFormula', filterByFormula);
    if (fields) fields.forEach(f => params.append('fields[]', f));
    if (sort) params.append('sort[0][field]', sort.field);
    if (view) params.append('view', view);
    if (offset) params.append('offset', offset);

    const url = `https://api.airtable.com/v0/appEEWaGtGUwOyOhm/${tableId}?${params}`;
    const response = await fetch(url, {
      headers: { 'Authorization': `Bearer ${process.env.AIRTABLE_API_KEY}` }
    });

    const data = await response.json();
    if (data.error) throw new Error(data.error.message);

    records.push(...data.records);
    offset = data.offset;

    if (offset) await sleep(200);
  } while (offset);

  return records;
}

Update Records (Batch)

javascript

async function updateRecords(tableId, records) {
  // records = [{ id: 'recXXX', fields: { ... } }, ...]
  const url = `https://api.airtable.com/v0/appEEWaGtGUwOyOhm/${tableId}`;
  const batches = chunk(records, 10);
  const results = [];

  for (const batch of batches) {
    const response = await fetch(url, {
      method: 'PATCH',  // PATCH for partial update, PUT for full replace
      headers: {
        'Authorization': `Bearer ${process.env.AIRTABLE_API_KEY}`,
        'Content-Type': 'application/json'
      },
      body: JSON.stringify({ records: batch, typecast: true })
    });

    const data = await response.json();
    if (data.error) throw new Error(data.error.message);
    results.push(...data.records);
    await sleep(200);
  }

  return results;
}

Upsert Pattern (G-011 Compliant)

Never use find → delete → create loops. Use upsert instead:

javascript

async function upsertRecords(tableId, records, matchField) {
  // records = [{ fields: { external_id: 'xxx', ... } }, ...]
  const url = `https://api.airtable.com/v0/appEEWaGtGUwOyOhm/${tableId}`;
  const batches = chunk(records, 10);
  const results = [];

  for (const batch of batches) {
    const response = await fetch(url, {
      method: 'PATCH',
      headers: {
        'Authorization': `Bearer ${process.env.AIRTABLE_API_KEY}`,
        'Content-Type': 'application/json'
      },
      body: JSON.stringify({
        performUpsert: {
          fieldsToMergeOn: [matchField]  // e.g., 'external_id'
        },
        records: batch.map(r => ({ fields: r })),
        typecast: true
      })
    });

    const data = await response.json();
    if (data.error) throw new Error(data.error.message);
    results.push(...data.records);
    await sleep(200);
  }

  return results;
}

filterByFormula Patterns

javascript

// Exact match
`{name}="Hampshire Constabulary"`

// Contains (case-insensitive)
`SEARCH("police", LOWER({title}))`

// Multiple conditions (AND)
`AND({status}="new", {type}="job_posting")`

// Multiple conditions (OR)
`OR({status}="new", {status}="enriched")`

// Not empty
`{force}!=""`

// Date comparisons
`IS_AFTER({created_at}, "2025-01-01")`
`DATETIME_DIFF(NOW(), {last_contact}, 'days') > 30`

// Linked record lookup (by record ID)
`FIND("rec123", ARRAYJOIN({force}))`

Field Types Reference

Type	API Format	Notes
Single Line Text	`"value"`
Long Text	`"multiline\nvalue"`
Number	`123` or `123.45`
Checkbox	`true` / `false`
Single Select	`"Option Name"`	With typecast: auto-creates
Multiple Select	`["Opt1", "Opt2"]`
Date	`"2025-01-23"`	ISO 8601
DateTime	`"2025-01-23T10:30:00.000Z"`	ISO 8601 with time
Linked Record	`["recXXX", "recYYY"]`	Array of record IDs
Attachment	`[{url: "..."}]`

MI Platform Table Schemas

Signals Table

Field	Type	Notes
title	Single Line Text	Job title / signal name
type	Single Select	`job_posting`, `competitor`, `news`, etc.
source	Single Select	`indeed`, `competitor`, `bright_data`
force	Linked Record → Forces
status	Single Select	`new`, `classified`, `archived`
external_id	Single Line Text	For deduplication (upsert key)
role_category	Single Select	`investigator`, `forensic`, `intelligence`, etc.
ai_confidence	Number	0-100
raw_data	Long Text	JSON blob

Opportunities Table

Field	Type	Notes
name	Single Line Text	Generated from force + signals
force	Linked Record → Forces
signals	Linked Record → Signals	Multiple
status	Single Select	`new`, `enriched`, `sent`, `won`, `lost`
priority	Single Select	`P1`, `P2`, `P3`
contact	Linked Record → Contacts
draft_subject	Single Line Text
draft_body	Long Text
why_now	Long Text	AI context summary

Error Handling

javascript

async function safeAirtableRequest(fn) {
  try {
    return await fn();
  } catch (error) {
    if (error.message.includes('RATE_LIMIT')) {
      // Back off and retry
      await sleep(1000);
      return await fn();
    }
    if (error.message.includes('INVALID_PERMISSIONS')) {
      console.error('Check API key permissions');
    }
    throw error;
  }
}

Common Patterns

Find Force by Name

javascript

const forces = await listAllRecords('tblbAjBEdpv42Smpw', {
  filterByFormula: `{name}="${forceName}"`
});
return forces[0]?.id;

Get Signals for Force

javascript

const signals = await listAllRecords('tblez9trodMzKKqXq', {
  filterByFormula: `AND({force}="${forceRecordId}", {status}="new")`,
  sort: { field: 'created_at', direction: 'desc' }
});

Create Opportunity from Signals

javascript

await createRecords('tblJgZuI3LM2Az5id', [{
  name: `${forceName} - ${new Date().toISOString().slice(0,10)}`,
  force: [forceRecordId],  // Linked record = array
  signals: signalIds,       // Array of signal record IDs
  status: 'new',
  priority: hasCompetitor ? 'P1' : 'P2'
}]);