import type { ExportSchema, ExportSchemaProperty, ExportableItem, ExportRecordResult } from "../types"; import type { AirtableBases, AirtableExportConfig, AirtableFields, AirtableFieldType, AirtableTables, AirtableTarget, } from "./types"; /** Airtable caps record write/delete operations at 10 records per request. */ export const AIRTABLE_BATCH_SIZE = 10; const sleep = (ms: number): Promise => new Promise((resolve) => setTimeout(resolve, ms)); type ApiResponse = { status: number; success: boolean; data: R; }; type AirtableRecord = { id: string; fields: Record; }; type AirtableTableResponse = { id: string; name: string; description?: string; primaryFieldId?: string; fields: AirtableFields; }; type AirtableFieldDefinition = { name: string; type: AirtableFieldType; options?: Record; }; function getClient(token: string) { if (!token) { throw new Error("Missing Airtable API token"); } return { async callApi( path: string, method: "GET" | "POST" | "DELETE" | "PUT" | "PATCH" = "GET", body: Record | null = null, ): Promise> { const url = `https://api.airtable.com/${path}`; const maxRetries = 5; const retryDelay = 30000; // 30 seconds for (let attempt = 1; attempt <= maxRetries; attempt++) { try { const res = await fetch(url, { method, headers: { Authorization: `Bearer ${token}`, "Content-Type": "application/json", }, body: body ? JSON.stringify(body) : undefined, }); // 429 is rate limit exceeded (Airtable allows 5 requests/second/base) if (res.status === 429 && attempt < maxRetries) { console.warn( `Airtable rate limit hit (429) on attempt ${attempt}/${maxRetries}. Waiting ${retryDelay / 1000}s before retry...`, ); await sleep(retryDelay); continue; } const data = (await res.json()) as R; // For any other response (success or definitive error) return { status: res.status, success: res.ok, data, }; } catch (error) { // Network error: retry as well, except on the last attempt if (attempt < maxRetries) { console.warn( `Network error on attempt ${attempt}/${maxRetries}. Waiting ${retryDelay / 1000}s before retry...`, error, ); await sleep(retryDelay); continue; } console.error(`Error on attempt ${attempt}/${maxRetries}:`, error); throw error; } } throw new Error(`Airtable API rate limit exceeded after ${maxRetries} attempts`); }, }; } function describeError(data: unknown): string { if (data && typeof data === "object" && "error" in data) { const { error } = data as { error: unknown }; if (typeof error === "string") return error; if (error && typeof error === "object") { const { type, message } = error as { type?: string; message?: string }; return message ? `${type ?? "error"}: ${message}` : (type ?? JSON.stringify(error)); } } return JSON.stringify(data); } /** * Convert a value coming from the item data based on the Airtable field type. * Values are already typed (JSON), unlike form submissions which are all strings. */ export function convertValueForAirtableField(value: unknown, fieldType: AirtableFieldType): unknown { if (value === null || value === undefined) { return null; } switch (fieldType) { case "checkbox": if (typeof value === "boolean") return value; return ["true", "1", "on", "yes"].includes(String(value).toLowerCase()); case "number": { if (typeof value === "number") return value; const numValue = Number(value); return Number.isNaN(numValue) ? null : numValue; } case "date": case "dateTime": // Airtable expects ISO format strings return value instanceof Date ? value.toISOString() : String(value); case "multipleSelects": if (Array.isArray(value)) return value.map(String); if (typeof value === "string" && value.includes(",")) { return value .split(",") .map((v) => v.trim()) .filter((v) => v.length > 0); } return [String(value)]; default: // singleLineText, multilineText, email, url, singleSelect return typeof value === "string" ? value : JSON.stringify(value); } } /** * Build the Airtable `fields` payload for one item: apply the configured * property → field name mapping, then coerce each value to the remote field type. * Properties with no matching Airtable field are dropped (the remote table may * legitimately hold fewer columns than the datasource). */ export function mapItemToFields( data: Record, config: Pick, ): Record { const fieldsByName = new Map((config.fields ?? []).map((field) => [field.name, field])); const mapped: Record = {}; for (const [property, value] of Object.entries(data)) { if (value === undefined) continue; const fieldName = config.fieldMapping?.[property] ?? property; const field = fieldsByName.get(fieldName); // Unknown remote field: skip it rather than letting Airtable reject the whole batch. if (!field) continue; mapped[fieldName] = convertValueForAirtableField(value, field.type as AirtableFieldType); } return mapped; } /** * Map a JSON Schema property (as stored in `datasources.manifest.schema`) to an * Airtable field definition. */ function buildAirtableField(fieldName: string, property: ExportSchemaProperty): AirtableFieldDefinition { if (property.type === "boolean") { return { name: fieldName, type: "checkbox", options: { icon: "check", color: "grayBright" }, }; } if (property.type === "number" || property.type === "integer") { return { name: fieldName, type: "number", options: { precision: property.type === "integer" ? 0 : 8 }, }; } // Everything else is treated as text-ish if (property.format === "email") { return { name: fieldName, type: "email" }; } if (property.format === "uri") { return { name: fieldName, type: "url" }; } if (property.format === "date") { return { name: fieldName, type: "date", options: { dateFormat: { name: "local" } }, }; } if (property.format === "date-time") { return { name: fieldName, type: "dateTime", options: { dateFormat: { name: "local" }, timeFormat: { name: "24hour" }, timeZone: "client", }, }; } if (property.enum) { const choices = property.enum.map((value) => ({ name: value })); if (property.metadata?.["ui:widget"] === "checkbox") { return { name: fieldName, type: "multipleSelects", options: { choices } }; } return { name: fieldName, type: "singleSelect", options: { choices } }; } if (property.metadata?.["ui:multiline"]) { return { name: fieldName, type: "multilineText" }; } return { name: fieldName, type: "singleLineText" }; } /** * Build the Airtable field list from a datasource JSON Schema, honouring the * `metadata.order` of the properties when present. */ export function buildAirtableFields(schema: ExportSchema): AirtableFieldDefinition[] { const properties = Object.entries(schema.properties ?? {}); const sorted = properties.sort(([, a], [, b]) => { const orderA = a.metadata?.order; const orderB = b.metadata?.order; const hasA = typeof orderA === "number"; const hasB = typeof orderB === "number"; if (hasA && hasB) return (orderA as number) - (orderB as number); if (hasA) return -1; if (hasB) return 1; return 0; }); return sorted.map(([fieldName, property]) => buildAirtableField(fieldName, property)); } function toTarget(baseId: string, table: AirtableTableResponse): AirtableTarget { return { baseId, tableId: table.id, tableName: table.name, fields: table.fields, externalUrl: `https://airtable.com/${baseId}/${table.id}`, }; } /** List the bases the token gives access to. */ export async function fetchAirtableBases(accessToken: string): Promise { const client = getClient(accessToken); const response = await client.callApi<{ bases: AirtableBases }>("v0/meta/bases"); if (!response.success) { throw new Error(`Failed to fetch Airtable bases: ${describeError(response.data)}`); } return response.data.bases; } /** List the tables of a base, with their fields (used to map an existing table). */ export async function fetchAirtableTables(accessToken: string, baseId: string): Promise { const client = getClient(accessToken); const response = await client.callApi<{ tables: AirtableTables }>(`v0/meta/bases/${baseId}/tables`); if (!response.success) { throw new Error(`Failed to fetch Airtable tables: ${describeError(response.data)}`); } return response.data.tables; } /** Create a table in `baseId` mirroring the datasource schema. */ export async function createAirtableTable({ accessToken, baseId, name, schema, }: { accessToken: string; baseId: string; name: string; schema: ExportSchema; }): Promise { const client = getClient(accessToken); const payload = { name, description: `Table created by Upstart for ${name}`, fields: buildAirtableFields(schema), }; const response = await client.callApi( `v0/meta/bases/${baseId}/tables`, "POST", payload, ); if (!response.success) { throw new Error(`Failed to create Airtable table: ${describeError(response.data)}`); } return toTarget(baseId, response.data); } /** * Add to the remote table the fields present in the datasource schema but missing * on the Airtable side. Fields removed from the datasource are left untouched. * Returns the refreshed target (with the up-to-date field list). */ export async function syncAirtableTableFields({ accessToken, baseId, tableId, schema, fieldMapping = {}, }: { accessToken: string; baseId: string; tableId: string; schema: ExportSchema; fieldMapping?: Record; }): Promise { const client = getClient(accessToken); const tables = await fetchAirtableTables(accessToken, baseId); const table = tables.find((t) => t.id === tableId); if (!table) { throw new Error(`Airtable table ${tableId} not found in base ${baseId}`); } const existingNames = new Set(table.fields.map((field) => field.name)); const missing = buildAirtableFields(schema).filter( (field) => !existingNames.has(fieldMapping[field.name] ?? field.name), ); for (const field of missing) { const response = await client.callApi<{ id: string; name: string; type: string }>( `v0/meta/bases/${baseId}/tables/${tableId}/fields`, "POST", { ...field }, ); if (!response.success) { throw new Error( `Failed to add field "${field.name}" to Airtable table: ${describeError(response.data)}`, ); } } const refreshed = missing.length ? await fetchAirtableTables(accessToken, baseId) : tables; const finalTable = refreshed.find((t) => t.id === tableId) ?? table; return toTarget(baseId, { id: finalTable.id, name: finalTable.name, fields: finalTable.fields }); } function chunk(items: T[], size: number): T[][] { const chunks: T[][] = []; for (let i = 0; i < items.length; i += size) { chunks.push(items.slice(i, i + size)); } return chunks; } /** * Record ids Airtable names in a `ROW_DOES_NOT_EXIST` error. Deleting a row in * Airtable is permanent, and the error message quotes the offending id — which lets * us tell "this record is gone" apart from a batch-wide failure (renamed field, * missing table) where the ids must be kept. */ function extractMissingRecordIds(error: string): string[] { if (!error.includes("ROW_DOES_NOT_EXIST")) return []; return [...error.matchAll(/\brec[A-Za-z0-9]+\b/g)].map((match) => match[0]); } /** * Create or update the remote records for a batch of items. Items carrying an * `externalRecordId` are PATCHed, the others are POSTed. Airtable fails a whole batch * atomically, so a failed update batch is retried without the records it reported as * deleted; those are recreated instead. */ export async function upsertAirtableRecords({ accessToken, config, items, }: { accessToken: string; config: AirtableExportConfig; items: ExportableItem[]; }): Promise { if (!items.length) return []; const client = getClient(accessToken); const results: ExportRecordResult[] = []; const createRecords = async (toCreate: ExportableItem[]) => { for (const batch of chunk(toCreate, AIRTABLE_BATCH_SIZE)) { const response = await client.callApi<{ records: AirtableRecord[] }>( `v0/${config.baseId}/${config.tableId}`, "POST", { records: batch.map((item) => ({ fields: mapItemToFields(item.data, config) })), // Let Airtable coerce values it can (e.g. add a missing select choice) typecast: true, }, ); if (!response.success) { const error = describeError(response.data); results.push(...batch.map((item) => ({ itemId: item.itemId, error }))); continue; } batch.forEach((item, index) => { const record = response.data.records[index]; results.push( record ? { itemId: item.itemId, externalRecordId: record.id } : { itemId: item.itemId, error: "Airtable did not return a record id" }, ); }); } }; await createRecords(items.filter((item) => !item.externalRecordId)); for (const batch of chunk( items.filter((item) => item.externalRecordId), AIRTABLE_BATCH_SIZE, )) { let pending = batch; const deletedRemotely: ExportableItem[] = []; // Airtable only names the first missing record, so peel them off one round at a // time. Bounded by the batch size: every round removes at least one record. for (let round = 0; round <= AIRTABLE_BATCH_SIZE && pending.length; round++) { const response = await client.callApi<{ records: AirtableRecord[] }>( `v0/${config.baseId}/${config.tableId}`, "PATCH", { records: pending.map((item) => ({ // biome-ignore lint/style/noNonNullAssertion: filtered on externalRecordId above id: item.externalRecordId!, fields: mapItemToFields(item.data, config), })), typecast: true, }, ); if (response.success) { results.push( ...pending.map((item) => ({ itemId: item.itemId, externalRecordId: item.externalRecordId ?? undefined, })), ); break; } const error = describeError(response.data); const missingIds = new Set(extractMissingRecordIds(error)); if (!missingIds.size) { // Batch-wide failure with no culprit named: keep the stored ids, since // dropping them would duplicate up to ten records on the next run. results.push(...pending.map((item) => ({ itemId: item.itemId, error }))); break; } deletedRemotely.push(...pending.filter((item) => missingIds.has(item.externalRecordId as string))); pending = pending.filter((item) => !missingIds.has(item.externalRecordId as string)); } // The row was deleted in Airtable: recreate it now rather than failing this run. if (deletedRemotely.length) { await createRecords(deletedRemotely.map((item) => ({ ...item, externalRecordId: null }))); } } return results; } /** Delete remote records (items soft-deleted in Upstart). */ export async function deleteAirtableRecords({ accessToken, config, recordIds, }: { accessToken: string; config: Pick; recordIds: string[]; }): Promise { if (!recordIds.length) return; const client = getClient(accessToken); for (const batch of chunk(recordIds, AIRTABLE_BATCH_SIZE)) { const query = batch.map((id) => `records[]=${encodeURIComponent(id)}`).join("&"); const response = await client.callApi<{ records: { id: string; deleted: boolean }[] }>( `v0/${config.baseId}/${config.tableId}?${query}`, "DELETE", ); // A record already gone remotely (404) is not an error for us. if (!response.success && response.status !== 404) { throw new Error(`Failed to delete Airtable records: ${describeError(response.data)}`); } } }