Airtable Operations for MI Platform

Base Configuration

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

Table IDs (Use IDs, Not Names)

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

// 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)

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

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)

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:

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

// 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

MI Platform Table Schemas

Signals Table

Opportunities Table

Error Handling

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

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

Get Signals for Force

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

Create Opportunity from Signals

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'
}]);