Most teams we talk to aren’t trying to escape Airtable. The operations team likes it, the views and automations work, and nobody wants a migration project. What they want is something Airtable can’t give them: a portal where each client signs in and sees their own sites and jobs, and nothing else.
Our migration article covers moving off Airtable for good. This one keeps Airtable as the place people work and copies the data, one way, into Supabase, which serves the portal. We built and ran it against a real Airtable base and a real Supabase project, using a fictional Tampa facilities company with 16 clients, 36 sites and 360 maintenance jobs. Every screenshot below is from those runs.
Sync or migrate?
| Keep Airtable and sync | Move off Airtable |
|---|---|
| The team is happy editing in Airtable | Airtable itself is the problem: limits, speed, formulas nobody understands |
| You need an app or portal that reads the data | Several apps and systems need to write the data |
| A few minutes’ delay in the portal is fine | Everything must be up to date the moment it’s saved |
| Airtable plan limits aren’t close | You’re near the record or API limits of your plan |
A sync is also a good first step towards a migration. The tables you design for the portal are the tables you’d move to, and the sync has been running on real data for months by the time you switch.
How it fits together
The sync is a short Python script that runs every few minutes from a scheduler: cron on a small server, a GitHub Actions schedule or a background worker. It reads from the Airtable API and writes to the Supabase database over a normal Postgres connection, so it isn’t limited by the Supabase API and doesn’t need a service key in the browser.
Keeping it one-way is a deliberate choice. Two-way syncs need rules for who wins when both sides change the same record, and those rules are where most sync bugs live. If clients need to submit something, such as a new job request, the portal can create the record in Airtable through its API and let the next sync bring it back.
Step 1: Tables in Supabase, with row level security on
Each Airtable table becomes a Postgres table that keeps the Airtable record id. Links become foreign keys, single selects become enums, and a deleted_at column lets the sync hide records instead of removing them.
create type job_status as enum ('Requested', 'Scheduled', 'In progress', 'Done', 'Invoiced');
create type job_priority as enum ('Low', 'Normal', 'Urgent');
create table jobs (
id bigint generated always as identity primary key,
airtable_id text unique not null,
ref text not null,
site_id bigint references sites,
status job_status not null,
priority job_priority not null default 'Normal',
scheduled_for date,
quote numeric(10, 2),
notes text,
modified_at timestamptz not null, -- Airtable's last modified time for the record
deleted_at timestamptz -- set when the record disappears from Airtable
);
-- Which signed-in users belong to which client
create table client_members (
user_id uuid not null references auth.users on delete cascade,
client_id bigint not null references clients on delete cascade,
primary key (user_id, client_id)
);
-- Bookkeeping for the sync lives outside the API-exposed schema
create schema sync;
create table sync.state (
base_id text primary key,
synced_from timestamptz not null -- next run asks Airtable for changes after this
);Anything in the public schema can be read through Supabase’s API, so row level security goes on before the first sync. The policies only allow reading. Nobody but the sync job writes these tables, and it connects as the database owner, which bypasses RLS.
alter table clients enable row level security;
alter table sites enable row level security;
alter table jobs enable row level security;
alter table client_members enable row level security;
-- The clients the signed-in user belongs to, for clients with portal access switched on.
create schema private;
create function private.my_client_ids() returns setof bigint
language sql stable security definer set search_path = '' as $$
select m.client_id
from public.client_members m
join public.clients c on c.id = m.client_id
where m.user_id = (select auth.uid())
and c.portal_enabled
and c.deleted_at is null
$$;
grant usage on schema private to authenticated;
create policy "members read their client" on clients for select to authenticated
using (id in (select private.my_client_ids()));
create policy "members read their sites" on sites for select to authenticated
using (deleted_at is null and client_id in (select private.my_client_ids()));
create policy "members read their jobs" on jobs for select to authenticated
using (deleted_at is null
and site_id in (select s.id from sites s where s.client_id in (select private.my_client_ids())));The Portal access checkbox in Airtable flows through to portal_enabled, so the operations team can switch a client’s access off without touching Supabase.
Step 2: Ask Airtable only for what changed
Airtable formulas can call LAST_MODIFIED_TIME() without a field for it, so the API can filter on it directly. The sync stores the time each run started and, on the next run, asks for records changed after that, minus two minutes in case clocks disagree.
def fetch(table, since=None, only_field=None):
"""Records changed after `since` (or all of them), following Airtable's 100-record pages."""
params = {'pageSize': 100, 'returnFieldsByFieldId': 'true'}
if since:
params['filterByFormula'] = f"IS_AFTER(LAST_MODIFIED_TIME(), '{since:%Y-%m-%dT%H:%M:%SZ}')"
if only_field:
params['fields[]'] = only_field
records = []
while True:
r = session.get(f'https://api.airtable.com/v0/{BASE}/{table}', params=params)
if r.status_code == 429: # rate limited: Airtable asks for a 30 second pause
time.sleep(30)
continue
r.raise_for_status()
page = r.json()
records += page['records']
if 'offset' not in page:
return records
params['offset'] = page['offset']
time.sleep(0.2) # stay under 5 requests per second per basereturnFieldsByFieldId makes the API key each field by its id, such as fldo6lvmOfeTyBirC, instead of its name. It looks less friendly in code, but it’s what lets someone rename Scheduled for to Visit date on a Tuesday afternoon without breaking the portal. We tested exactly that.
Step 3: Write changes in batches, and refuse what doesn’t fit
Each table is written with one INSERT … ON CONFLICT per 100 rows. The WHERE on the update skips rows whose Airtable modified time hasn’t moved, and returning xmax = 0 tells inserts apart from updates, so every run reports what it actually changed.
def sync_jobs(cur, records, stats, issues):
rows = []
for rec in records:
f = rec['fields']
ok, site = one_link(rec, JOBS['site'], 'site', issues, f.get(JOBS['ref']))
status = f.get(JOBS['status'])
if ok and status not in STATUSES:
issues.append((rec['id'], f"{f.get(JOBS['ref'])}: unknown status '{status}'; not synced until fixed"))
ok = False
if not ok:
stats['skipped'] += 1
continue
if site is None:
issues.append((rec['id'], f"{f.get(JOBS['ref'])}: no site yet, so no client can see it"))
rows.append((rec['id'], f.get(JOBS['ref']), site, status, f.get(JOBS['priority'], 'Normal'),
f.get(JOBS['scheduled']), f.get(JOBS['quote']), f.get(JOBS['notes']), f[JOBS['modified']]))
upsert(cur, """
insert into jobs (airtable_id, ref, site_id, status, priority, scheduled_for, quote, notes, modified_at)
values %s
on conflict (airtable_id) do update
set ref = excluded.ref, site_id = excluded.site_id, status = excluded.status, priority = excluded.priority,
scheduled_for = excluded.scheduled_for, quote = excluded.quote, notes = excluded.notes,
modified_at = excluded.modified_at, deleted_at = null
where jobs.modified_at is distinct from excluded.modified_at or jobs.deleted_at is not null
returning xmax = 0""",
"(%s, %s, (select id from sites where airtable_id = %s), %s::job_status, %s::job_priority, %s::date, %s, %s, %s::timestamptz)",
rows, stats)Airtable lets people do things a database shouldn’t accept. A link field can hold two sites even when a job belongs to one. A new option can be typed into a single select. The script doesn’t guess. It leaves those records out, reports them, and loads everything else.
The first run took 10.8 seconds, most of it waiting on Airtable’s 100-record pages. Our first version wrote one row at a time and took 71 seconds for the same data, because every row was a round trip to the database. Batching fixed that.
Step 4: Deletions, without the panic
The Airtable REST API has no list of deleted records, so the sync asks for every record id, one field per record to keep it light, and compares. Rows that are gone get a deleted_at date, which hides them from the portal through the policies above.
def soft_delete_missing(cur, name, spec, primary_field, label, issues):
"""The REST API has no feed of deleted records, so compare ids. Cheap: one field, 100 ids per request."""
live = [r['id'] for r in fetch(spec['table'], only_field=primary_field)]
cur.execute(f'select count(*) from {name} where deleted_at is null')
current = cur.fetchone()[0]
cur.execute(f'select airtable_id, {label} from {name} where deleted_at is null and airtable_id <> all(%s)', (live,))
gone = cur.fetchall()
if current and len(gone) > current * MAX_DELETE_SHARE:
raise RuntimeError(f'{name}: {len(gone)} of {current} rows missing from Airtable. Not deleting; check the token and base.')
cur.execute(f'update {name} set deleted_at = now() where airtable_id = any(%s)', ([g[0] for g in gone],))
for airtable_id, what in gone:
issues.append((airtable_id, f'{what}: deleted in Airtable, hidden from the portal'))
return len(live), len(gone)MAX_DELETE_SHARE is set to 10%. If a token loses access to a table, or someone points the job at a copy of the base, a large share of records will seem to vanish at once. The run stops instead of hiding every client’s jobs, and because each run is one transaction, nothing it did before that point is kept.
A day of real edits
To test it, we made the kind of changes an operations team makes in a day: five jobs moved on, two new requests, one job deleted, BW-1058 fixed, one client’s portal access switched off, a job set to a new On hold status, and the Scheduled for field renamed to Visit date. Then we ran the sync again.
BW-1058 is one of the three inserts. It was skipped on the first run, so this is the first time it reaches Supabase; the other two are the new requests.
Two things in this output are worth knowing about. The four sites show up as changed even though nobody edited them: adding a job to a site updates the site’s reverse Jobs link field, and that counts as a modification in Airtable. The WHERE clause in the upsert catches it, so they’re counted as unchanged and nothing is written.
The other is On hold. Someone typed a new option into the Status field, and the database only has five statuses. The job is reported, not loaded, so the portal keeps showing its last good version. If On hold is a real status, add it to the enum, then run a full sync once: the record won’t come up again as changed until someone edits it.
Step 5: Check what each client can see
Enabling RLS isn’t the same as testing it. We created three portal users in Supabase Auth and linked them to clients: Dana at Harbourside Dental Group, Marco at Palmetto Fitness Studios, and Priya at Lumen Optometry, the client whose portal access was switched off in Airtable. Then we ran the same queries as each of them, with the role and JWT claims Supabase’s API sets for a signed-in request.
Run a check like this in CI or before every release. A policy that’s one condition too loose shows every client everyone else’s jobs, and nothing in the portal will look wrong to the person testing it with their own account.
What about attachments?
Airtable’s attachment links expire after a few hours, so a portal can’t link to them. If clients need photos or documents, the sync should download each new file and upload it to Supabase Storage, keyed by the Airtable attachment id so it’s copied only once, and the portal should serve it with a short-lived signed URL. Storage policies then decide who can read each file, the same way RLS does for rows.
Mistakes we’d avoid
Sources
Questions we often hear
How often can Airtable sync to Supabase?
Every few minutes is easy, because each run only reads records changed since the last one. Our test runs took about 11 seconds for 412 records, almost all of it waiting on the Airtable API.
Can we use Airtable webhooks instead of polling?
Yes. Airtable’s webhooks tell you which records changed, including deletions, and you still read the changes through the API. They need more setup and their own monitoring, so we start with polling and switch when a few minutes’ delay is too much.
Does this work with Airtable’s free plan?
The API is available on every plan, but monthly API call limits differ by plan. Check your plan’s limit against how often you want to sync before choosing an interval.
Can we move off Airtable later?
Yes, and this makes it easier. The Supabase tables are already the tables you’d move to. On the day you switch, stop editing in Airtable, run the sync one last time and point people at the new screens.
