Reverse Engineering Airtable's Internal MsgPack Format

At my day job, we build heavily used software systems that handle significant operational workloads. A core piece of our workflow is Airtable—it serves as the primary application interface and UI for our users, which then synchronizes down to our internal SQL databases.

We had long known about occasional drifts and mismatches, particularly on older records that were years old and still actively worked on inside Airtable. However, we always assumed these were isolated, rare edge cases that should almost never happen.

After noticing a few anomalies, we decided to dedicate some time to run an audit. We wanted to get an accurate picture of how bad the situation really was, measure the scale of the drift, and see if our "edge case" hypothesis held up.

But we immediately hit a wall: the API rate limit.

---

The 100-Row API Bottleneck

The standard Airtable REST API has two severe constraints for this type of scale:

1. Rate Limits: It is strictly capped at 5 requests per second (300 requests per minute). This quota is shared globally across every single script, service, and automation interacting with our Airtable base.
2. Page Limits: It returns a maximum of 100 rows per request.

With our base hovering at 340,000 rows, running a full audit would require at least 3,400 API requests. Running this sequentially would take several minutes, but more importantly, executing it at full speed would instantly saturate our API quota, causing production sync jobs and active webhooks to fail—which is a phenomenal way to get paged on a Friday evening.

Our team needed a different approach. The goal was simple: get a full copy of the Airtable base data locally, dump our own SQL database, and run a high-performance local join to identify discrepancies.

For local analysis of large, structured datasets, DuckDB is the absolute best tool on the market. But how could we extract all 340,000 rows out of Airtable in a single operation?

---

Bypassing the API via DevTools

We noticed an interesting behavior: when you open the Airtable base in your browser, the web client loads all 340,000 rows into local memory "almost instantly" (which, in frontend speak, means you have just enough time to take a sip of lukewarm coffee, but it's still under a minute—incredibly fast for that volume of data). It doesn't fetch them page-by-page as you scroll; the initial load pulls the entire grid.

We opened browser DevTools, clicked the Network tab, and reloaded the page. Expecting a massive, standard JSON response, we were instead greeted by a wall of raw binary data. We had no expectation of what kind of format we'd get, but looking closely at the byte patterns and header signature, it felt like MessagePack (MsgPack).

Sure enough, a closer look at the GET request query parameters confirmed our suspicion—the client was calling an internal endpoint:

GET /v0.3/table/<TABLE_ID>/readData?...&allowMsgpackOfResult=true&...

The server response had a Content-Type header of application/octet-stream and delivered a single, compact binary blob of 410 MB (which would easily exceed 1.2 GB as raw JSON text). We copied the network request as a fetch command, ran it in a local Node.js script, and saved the raw buffer to disk as response.msgpack.

---

Decoding the Top-Level Structure

Before we could load this data into DuckDB, we had to decode the MsgPack format.

Naively treating this binary stream as a sequence of independent MsgPack messages using standard streaming decoders (like @msgpack/msgpack's decodeMulti) produces 8.3 million individual tokens—a complete dead end.

Airtable's binary payload is structured as a single, heavily nested MsgPack object. To decode it properly, we need to load the entire buffer into memory and unpack it using a library that handles the object graph in one pass, such as msgpackr.

import { unpack } from "msgpackr";
import fs from "node:fs";

// Read and unpack the raw binary payload
const payload = unpack(fs.readFileSync("response.msgpack"));

The decoded top-level payload has a simple shape:

{
  "msg": "SUCCESS",
  "data": {
    "table": {
      "id": "tbl2abKsBVhcwm43l",
      "rows": [ ...340,000 rows... ],
      "views": [ ...view metadata... ]
    },
    "applicationTransactionNumber": 100
  }
}

---

The Internal Row Structure

Each entry inside data.rows is a structured object. However, it looks nothing like the standard JSON returned by the public API:

type Row = {
  id:                   string;   // e.g. "reciKk7RYEjEiHx1W"
  createdTime:          string;   // ISO 8601 UTC
  cellValuesByColumnId: Record<string, CellValue>;
};

Instead of using developer-friendly column names, the key-value map inside cellValuesByColumnId uses opaque Airtable field IDs (e.g. fldiOwSo7G8Eb2PuH). To make sense of the rows, we also had to fetch the table's schema (containing field definitions) and build a translation map.

Decoding Choices, Selects, and the Bloated Schema

To translate these opaque field IDs (like fldvYQmBd...) and cell values into human-readable columns, we need to understand a core piece of Airtable's architecture: the table schema object.

Under the hood, every Airtable table is defined by a massive schema metadata configuration. This object contains definitions for every column, including:

• Single and Multiple Selects: Comprehensive arrays listing every available option, complete with option IDs (e.g. selcsXPE3tw8f2cvE), name labels, colors, and sort orders.
• Formulas, Lookups, and Rollups: The raw formula syntax trees, formatting options, and dependency references mapping back to other tables.
• Link to Another Record Fields: Relational configurations mapping columns to foreign tables.

Because these schema files explicitly define all choices, colors, and formula structures in detail, they can get incredibly bloated—often reaching several megabytes of raw JSON configuration on their own for complex bases.

This schema architecture also explains why interacting with Airtable schemas programmatically has historically been a point of friction for developers. You cannot directly write or patch a table's schema in a single, bulk update call. If you want to modify a schema, you have to query the metadata API and update each field individually. Even then, your options are heavily restricted: the Airtable API places strict limitations on what changes are allowed programmatically (for example, converting between certain field types or modifying complex formulas is frequently blocked in the API, forcing you to go into the UI manually).

This design is crucial for referential integrity: cell values in the rows payload do not store the raw text labels for select options (like "In Progress"). Instead, they store only the stable, internal option ID ("selcsXPE3tw8f2cvE").

If a user goes into the Airtable UI and renames "In Progress" to "Active", or changes its background color, the row cells don't change at all—they still reference the exact same ID. Only the schema metadata registry gets updated. This makes renames instantaneous and non-destructive in the database (though, in a classic Airtable twist, it will still trigger a webhook event for every single row referencing that option, flooding your handler with thousands of redundant payloads—but that's a story for another day).

To resolve these IDs, we had to parse the massive table schema first and construct a translation map:

// Table Schema Choice Registry
const choices = {
  "fldvYQmBdTzk1WHqB": {
    "selcsXPE3tw8f2cvE": "In Progress",
    "selcsABC123xyz789": "Completed"
  }
}

---

Airtable's Custom MsgPack Compression

If we map the cell values naively, we find that the binary payload uses custom MsgPack extension (ext) tags to achieve its high compression ratio. MsgPack extensions use a header consisting of a type tag and a binary payload:

┌────────┬──────────┬─────────────┐
│ ext    │ type_tag │ binary_data │
│ marker │ (uint8)  │ (N bytes)   │
└────────┴──────────┴─────────────┘

Airtable relies on two specific extension tags to keep its wire size small:

1. Ext Type 0 (Null Sentinels)

Airtable bases are highly sparse—in our table, ~73% of cells were null. Standard MsgPack represents nulls using a 0xc0 byte. Airtable instead uses ext(type=0, data=0x00) as a null sentinel. While this only saves a few bits per empty cell, across millions of null fields it prevents megabytes of overhead.

2. Ext Type 114 (Compressed Object Schema)

This is the core compression mechanism. In standard JSON, every single row repeats the column keys:

[
  {"record_id": "rec1", "createdTime": "...", "fldiOwSo7": 120.45},
  {"record_id": "rec2", "createdTime": "...", "fldiOwSo7": 98.20}
]

Repeating these long field ID strings for 340,000 rows would waste around 380 MB of payload space.

Airtable avoids this by using ext(type=114) as a stateful schema registry. The first time the client encounters a specific row shape, the payload registers the key array under a marker byte (e.g., 0x42). For all subsequent rows sharing that shape, the payload omits the key list entirely, sending only the marker byte and the raw values in order:

Because standard streaming decoders do not maintain this stateful schema lookup table during the unpacking pass, they fail to decode the payload.

---

Writing the Decoder Script

Below is our complete TypeScript script. It reads the raw response.msgpack stream, maps the schema choices, flattens the Airtable extension objects, and outputs a clean NDJSON file containing one flat JSON row per line.

import fs from "node:fs";
import { unpack, addExtension } from "msgpackr";

// Register custom extension for Airtable's null sentinel (type 0)
addExtension({
  type: 0,
  unpack: () => null,
});

interface RawPayload {
  msg: string;
  data: {
    table: {
      id: string;
      rows: Array<{
        id: string;
        createdTime: string;
        cellValuesByColumnId: Record<string, unknown>;
      }>;
    };
  };
}

// 1. Decode the entire binary payload in one pass
const payload = unpack(fs.readFileSync("response.msgpack")) as RawPayload;
console.assert(payload.msg === "SUCCESS");
console.log(`Successfully unpacked table: ${payload.data.table.id} (${payload.data.table.rows.length} rows)`);

// 2. Load schema to map Column IDs and select options
const schema = JSON.parse(fs.readFileSync("schema.json", "utf8"));
const table = schema.tables.find((t: any) => t.id === payload.data.table.id);
const columnNameMap = new Map<string, string>(
  table.fields.map((f: any) => [f.id, f.name])
);

// 3. Build a lookup index for select choice labels
const choiceMap = new Map<string, Map<string, string>>();
for (const field of table.fields) {
  if (field.options?.choices) {
    choiceMap.set(
      field.id,
      new Map(field.options.choices.map((c: any) => [c.id, c.name]))
    );
  }
}

// 4. Flatten row fields and resolve IDs
const flatRows = payload.data.table.rows.map((row) => {
  const output: Record<string, any> = {
    record_id: row.id,
    created_time: row.createdTime,
  };

  for (const [fieldId, value] of Object.entries(row.cellValuesByColumnId ?? {})) {
    const columnName = columnNameMap.get(fieldId) ?? fieldId;
    const choiceLookup = choiceMap.get(fieldId);

    output[columnName] =
      choiceLookup && typeof value === "string" ? choiceLookup.get(value) ?? value :
      choiceLookup && Array.isArray(value)      ? value.map(v => typeof v === "string" ? choiceLookup.get(v) ?? v : v) :
      value ?? null;
  }
  return output;
});

// 5. Write NDJSON stream to disk
const writeStream = fs.createWriteStream("rows.ndjson");
for (const row of flatRows) {
  writeStream.write(JSON.stringify(row) + "\n");
}
writeStream.end();
console.log("NDJSON conversion complete.");

Run the compiler using tsx:

npx tsx decode.ts

---

Loading and Auditing with DuckDB

Now that we have rows.ndjson, we can feed it directly to DuckDB to perform our sync audit.

First, we load the NDJSON file into a local table and export it as an optimized Parquet file (compressing it down to 48 MB due to columnar compression on sparse fields):

-- Create table from flat NDJSON
CREATE TABLE airtable_dump AS 
SELECT * FROM read_ndjson_auto('rows.ndjson');

-- Export to Parquet for high-performance querying
COPY airtable_dump TO 'airtable_data.parquet' (FORMAT PARQUET);

Next, we export our internal relational database records as a CSV or parquet dump (internal_db.parquet) and connect them in DuckDB to find the mismatches:

-- Find sync discrepancies between Airtable and our DB
SELECT 
  a.record_id,
  a."Email Address" AS airtable_email,
  i.email AS db_email,
  a."Subscription Status" AS airtable_status,
  i.status AS db_status
FROM read_parquet('airtable_data.parquet') a
JOIN read_parquet('internal_db.parquet') i 
  ON a.record_id = i.airtable_record_id
WHERE a."Subscription Status" != i.status 
   OR a."Email Address" != i.email;

---

Conclusion

By reverse engineering the internal MsgPack payload used by Airtable's web app, we successfully bypassed API rate limits and avoided generating hundreds of network requests that would impact our production system.

With the help of msgpackr to handle Airtable's stateful schema compression and DuckDB to run the local joins, we were able to map our data drift. We discovered that what we initially thought were rare, isolated edge cases actually affected several hundred historical records—giving us the clear picture we needed to plan a systematic database repair.

If you are dealing with large-scale sync audits or bulk exports from Airtable, look at the DevTools network stream—it is often far faster and cleaner than fighting public REST limits.

Tested against Airtable web client version c65e0aeb.