How to Sync Airtable to PostgreSQL Without Third-Party Connectors (Step-by-Step Blueprint)

Synchronizing business data from Airtable to PostgreSQL is a foundational architectural pattern for modern SaaS companies. While third-party synchronization tools like Whalesync or Zapier charge hefty monthly subscription markups, building your own native synchronization engine using Airtable Webhooks, Python, and PostgreSQL JSONB provides real-time event updates, sub-second latency, zero record caps, and complete data ownership.

In this technical implementation guide, we construct a production-ready webhook listener in Python, manage the 7-day webhook expiration refresh cycle, handle schema drift using PostgreSQL JSONB hybrid models, and implement atomic upsert operations.

1. Architectural Blueprint: Airtable Webhooks + PostgreSQL

Airtable webhooks operate on a Notification Cursor paradigm rather than pushing raw row data directly in the POST body. Below is how the real-time event loop functions:

Step Component Technical Action
1. Change Event Airtable UI / API User edits, creates, or deletes a record in an Airtable Base.
2. Notification Ping Airtable Webhooks Service Dispatches POST request containing transaction cursor ID to our server.
3. Payload Fetch Python Listener Engine Queries Airtable /webhooks/payloads endpoint using the cursor.
4. Atomic Upsert PostgreSQL Database Executes INSERT ... ON CONFLICT (airtable_id) DO UPDATE.

2. Production Python Webhook Receiver Implementation

Below is the complete, production-hardened webhook listener using FastAPI and psycopg2:

# airtable_sync_engine.py
import os
import requests
import psycopg2
from psycopg2.extras import Json
from fastapi import FastAPI, Request, BackgroundTasks, HTTPException

app = FastAPI()

AIRTABLE_TOKEN = os.environ["AIRTABLE_PAT"]
AIRTABLE_BASE_ID = os.environ["AIRTABLE_BASE_ID"]
WEBHOOK_ID = os.environ["AIRTABLE_WEBHOOK_ID"]
DB_URL = os.environ["DATABASE_URL"]

def sync_cursor_payloads():
    # Polls Airtable Webhooks API using the cursor and writes diffs to Postgres.
    headers = {"Authorization": f"Bearer {AIRTABLE_TOKEN}"}
    url = f"https://api.airtable.com/v0/bases/{AIRTABLE_BASE_ID}/webhooks/{WEBHOOK_ID}/payloads"
    
    res = requests.get(url, headers=headers)
    if res.status_code != 200:
        return

    data = res.json()
    payloads = data.get("payloads", [])
    
    conn = psycopg2.connect(DB_URL)
    cur = conn.cursor()

    for p in payloads:
        changed_tables = p.get("changedTablesById", {})
        for table_id, table_data in changed_tables.items():
            # Handle Created / Updated Records
            created_or_updated = table_data.get("createdRecordsById", {})
            created_or_updated.update(table_data.get("changedRecordsById", {}))
            
            for rec_id, rec in created_or_updated.items():
                fields = rec.get("current", {}).get("fields", {})
                cur.execute(
                    "INSERT INTO airtable_synced_records (airtable_record_id, fields_payload, updated_at) "
                    "VALUES (%s, %s, NOW()) "
                    "ON CONFLICT (airtable_record_id) "
                    "DO UPDATE SET fields_payload = EXCLUDED.fields_payload, updated_at = NOW();",
                    (rec_id, Json(fields))
                )

            # Handle Deleted Records
            destroyed_records = table_data.get("destroyedRecordIds", [])
            for rec_id in destroyed_records:
                cur.execute("DELETE FROM airtable_synced_records WHERE airtable_record_id = %s;", (rec_id,))

    conn.commit()
    cur.close()
    conn.close()

@app.post("/webhooks/airtable")
async def airtable_webhook_handler(req: Request, background_tasks: BackgroundTasks):
    # Acknowledge immediately with 200 OK to prevent Airtable retry penalties
    background_tasks.add_task(sync_cursor_payloads)
    return {"status": "queued"}

3. Handling Schema Drift with PostgreSQL JSONB

In Airtable, business users frequently add new columns, rename fields, or change single-select options. If you map Airtable columns strictly to static PostgreSQL column types (e.g. ALTER TABLE ADD COLUMN), every frontend change breaks your sync pipeline.

The Solution: Hybrid JSONB Storage:

CREATE TABLE airtable_synced_records (
    airtable_record_id VARCHAR(50) PRIMARY KEY,
    fields_payload JSONB NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Fast GIN index for deep querying inside JSON fields
CREATE INDEX idx_airtable_payload ON airtable_synced_records USING gin (fields_payload);

-- Generated virtual column for mission-critical relational joins
ALTER TABLE airtable_synced_records 
ADD COLUMN email VARCHAR(255) GENERATED ALWAYS AS (fields_payload->>'Email') STORED;

4. The 7-Day Webhook Expiration Gotcha

Airtable webhooks automatically expire 7 days after creation if not actively refreshed. To ensure continuous operation, schedule a daily cron job that executes a refresh ping:

# Daily cron job refreshing Airtable webhook subscription
0 2 * * * curl -X POST "https://api.airtable.com/v0/bases/$BASE_ID/webhooks/$WEBHOOK_ID/refresh"   -H "Authorization: Bearer $AIRTABLE_PAT"   -H "Content-Type: application/json"

Frequently Asked Questions (FAQs)

What is the API rate limit for Airtable Webhooks?

Standard Airtable API requests are limited to 5 requests per second per base. Webhook notifications bypass this limit because Airtable pushes notifications to your server. However, your cursor polling logic must respect the 5 req/sec limit when fetching large payload batches.

How do I backfill existing records before turning on webhooks?

Webhooks only capture mutations made after registration. To backfill existing records, write a one-off Python script using the Airtable REST API pagination endpoint (pageSize=100 with offset) to bulk-insert all current records prior to listening for live webhook notifications.


Mamun Hossain

Mamun Hossain

Systems Architect

Founder of Zelobiz & Enterprise Infrastructure Architect. Specializing in autonomous software engineering workflows, server-side tracking pipelines, and high-performance self-hosted infrastructure.

✓ 100% Independent Technical Evaluation ✓ Benchmarked on Dedicated Hardware Follow Zelo Biz on Facebook ↗

Editorial Disclosure: Zelobiz publishes independent engineering benchmarks and architectural evaluations. We may receive affiliate compensation when you subscribe to partner hosting services through our links at no additional cost to you.