Migrate from Airtable to PostgreSQL: practical translation patterns

Why Airtable patterns feel different in a production database

Airtable works well when you need something that feels like a spreadsheet, but with structure. The trouble starts when the base becomes “the system” and more people depend on it every day. A clever setup of linked records, rollups, and formulas can turn slow, hard to control, and easy to change by accident.

A production PostgreSQL-backed app is built around different expectations. Data is shared. Rules are enforced all the time (not only in a view). Changes need to be traceable. That’s why “migrate from Airtable to PostgreSQL” is usually less about copying tables and more about translating behavior.

Production use usually means a few concrete requirements:

• Reliability: the app behaves the same way for every user, every time.
• Access control: people only see and edit what they’re allowed to.
• Auditability: you can answer “who changed what, and when?”
• Performance at scale: more records and more users don’t break daily work.
• Clear ownership: updates happen through the app’s rules, not manual edits scattered across views.

In Airtable, many rules are “view-time.” A rollup shows a total, a formula shows a computed value, and a filtered view hides records. In PostgreSQL, those behaviors usually turn into relationships, aggregate queries, and application logic that runs consistently no matter where a user is in the app.

Some Airtable behavior won’t map 1-to-1. A link field that “just works” may become a join table with stricter rules. A formula that mixes text, dates, and lookups might turn into a SQL expression, a database view, or backend logic.

A simple example: in Airtable, a manager might see “Total Pipeline” via a rollup in a view. In a production app, that same number needs to respect permissions (which deals can they see?), refresh predictably, and be reproducible in reports.

Start with an Airtable audit that matches real workflows

Before you migrate from Airtable to PostgreSQL, write down how the base is actually used day to day. Airtable often starts as a “living spreadsheet,” so the same table can end up doing reporting, approvals, and quick edits all at once. A database-backed app needs clearer rules.

Inventory what exists, including the parts people forget, like “temporary” views and one-off scripts that quietly keep things running.

• Tables (including hidden or archived ones)
• Views and filters teams rely on (especially “My work” views)
• Interfaces, forms, and who uses each one
• Automations, scripts, and integrations
• Manual routines (copy/paste imports, weekly cleanup)

Next, label fields as either source of truth or derived.

• Source of truth fields are entered by a person or a trusted system (customer email, contract signed date).
• Derived fields are rollups, formulas, lookups, and status flags driven by other data.

This matters because some derived values should be stored (for history and auditing), while others should be computed when needed.

A useful rule: if people need to know “what it was at the time” (like commission at the moment a deal closed), store it. If it’s only for display (like “Days since last activity”), compute it.

Capture pain points in plain language. Examples: “The Deals view takes 20 seconds to load,” “Managers can see salary fields,” “We keep fixing broken links after imports.” These become real requirements for permissions, performance, and data checks in the new app.

Data model translation: tables, fields, and IDs

When you migrate from Airtable to PostgreSQL, the biggest mindset shift is that the database needs rules that stay true even when labels and layouts change. Airtable can tolerate “whatever is in the cell today.” PostgreSQL should not.

Start by translating each Airtable table into a real entity with a stable primary key. Don’t use a human name (like “Acme, Inc.”) as the ID. Names change, get misspelled, and sometimes collide. Use an internal ID (often a UUID or numeric ID) and keep names as editable attributes.

Field types deserve a second look because Airtable’s “number” and “text” can hide important differences:

• If a field has a small known set of values, treat it like a controlled choice (status, priority, tier).
• If it holds money, store it as a numeric type designed for currency math (and decide the currency).
• For time, decide between a date (no time) and a timestamp (exact moment).

Blanks also need a clear policy. Airtable often mixes “empty,” “zero,” and “unknown” in ways that look fine in a grid. In PostgreSQL you have to decide what each state means:

• Use NULL when “we truly do not know yet.”
• Use a default when “there is a normal value” (for example, status = "new").
• Convert empty strings to NULL when empty really means “missing.”
• Keep empty strings only when empty is meaningful.
• Add basic checks (for example, amount >= 0) to catch bad imports.

Finally, add a few indexes based on real usage. If people filter by account, status, and created date every day, those columns are good candidates. Avoid fancy indexing until you have real performance data, but don’t skip the obvious ones.

Example: a “Deals” table might become deals(id, account_id, stage, amount, close_date, created_at). That structure stays stable regardless of which UI you put on top.

Linked records: turning links into relations and join tables

Airtable makes relationships feel simple: you add a linked record field and you’re done. In PostgreSQL, you need to decide what that link means.

Start with cardinality: can each record have one match or many?

• One-to-many: one Company has many Contacts, but each Contact belongs to one Company.
• Many-to-many: one Contact can work with many Deals, and one Deal can include many Contacts.

In PostgreSQL:

• A one-to-many link is usually a single column on the “many” side (for example, contacts.company_id).
• A many-to-many link usually becomes a join table, like deal_contacts(deal_id, contact_id).

That join table can also hold extra details people often sneak into the relationship, like role_on_deal or added_by.

Keep links safe with referential integrity

Airtable will let links get messy over time. In a database-backed app, you can prevent that with foreign keys and clear delete rules.

Decide:

• Should deletes cascade, be restricted, or set the link to null?
• Should orphan rows be blocked (for example, deal_contacts without a real deal or contact)?

IDs vs display names

Airtable shows a friendly “primary field” as the link label. PostgreSQL should store stable keys (numeric ID or UUID), and the app should display friendly names.

A practical pattern: store company_id everywhere, keep companies.name (and possibly companies.code) for display and search.

Rollups: from view-time math to database aggregates

In Airtable, a rollup is “math across related records.” It looks like a single field, but it’s really a summary of many rows: counts, sums, min/max dates, averages, or lists pulled through a link.

In PostgreSQL, the same idea becomes an aggregate query. You join related tables, group by the parent record, and calculate totals with built-in functions. When you migrate from Airtable to PostgreSQL, rollups stop being spreadsheet-like fields and become questions the database can answer.

Translating common rollups into SQL thinking

Common patterns include:

• “Total invoice amount for this customer” -> SUM(amount) grouped by customer
• “Number of open tasks on this project” -> COUNT(*) with a status filter
• “Latest activity date” -> MAX(activity_date)
• “Average deal size for this rep” -> AVG(deal_value)

Airtable rollups often include filters like “only Active items” or “only last 30 days.” In a database, that becomes a WHERE clause. Be explicit about time zones and what “last 30 days” means, because production reporting gets questioned.

Computed vs stored rollups

You have two options:

• Compute rollups on demand (always fresh, simpler to maintain).
• Store them (faster screens, but you must keep them updated).

A practical rule: compute for dashboards and lists; store only when you need speed at scale or stable snapshots.

Formulas: deciding what becomes SQL and what becomes app logic

When you migrate from Airtable to PostgreSQL, formulas often need the most careful translation. In Airtable, a formula can quietly power a view, a filter, and a workflow at the same time. In a production app, you want results that are consistent, fast, and identical across every screen.

Sort formulas by what they really do:

• Formatting: turning values into labels like "Q1 2026" or "High priority"
• Conditional flags: TRUE/FALSE checks like "Overdue" or "Needs review"
• Calculations: totals, margins, date differences, scores
• Lookups: pulling values across linked records
• Business rules: anything that changes what users can do (eligibility, approvals)

Simple calculations and flags often belong in SQL (query expressions, views, or computed fields). That keeps every screen consistent and avoids re-implementing the same math in multiple places.

If a formula is really a rule (for example, “Discount allowed only if the account is active and the deal is above $5,000”), it should usually move into backend logic. That way it can’t be bypassed by a different client, a CSV import, or a new report.

Keep formatting close to the UI. Display labels can be built in the web and mobile interface without hard-coding them into the database.

Before you finalize, pick a few outputs that must always match (like Status, Amount Due, SLA Breach) and decide where they live. Then test them from every client so the number someone sees in the app matches what finance exports later.

Permissions redesign: roles, record access, and audit trails

Airtable permissions often feel simple because they’re mostly base, table, and view-based. In a production app, that’s rarely enough. Views are useful for workflow, but they are not a security boundary. When you migrate from Airtable to PostgreSQL, treat every “who can see this?” decision as an access rule you enforce everywhere: API, UI, exports, and background jobs.

Start by listing roles your app needs, not the tabs people click. A typical set:

• Admin: manage settings, users, and all data
• Manager: approve changes and see their team’s work
• Staff: create and update assigned records, limited reporting
• Customer: view their own requests, invoices, or status

Then define record-level rules (row-level access). Many real apps boil down to one of these patterns: “only my records,” “my team,” or “my organization.” Whether you enforce it in the database (row-level security) or in the API layer, the key is consistency: every query needs the rule, including exports and “hidden” screens.

Plan auditing from day one. Decide what you must record for each change:

• Who did it (user ID, role)
• What changed (field-level before/after when needed)
• When it happened (timestamp and timezone)
• Where it came from (UI, import, API)
• Why (optional note or reason code)

Step-by-step migration plan that avoids surprises

The safest migrations feel boring. You pick a date, reduce moving parts, and make it easy to compare the old base with the new app.

A week before the move, stop schema churn. Agree on a cutover date and set a rule: no new tables, no new fields, no renamed fields. Small tweaks can break imports and formulas in quiet ways.

A simple five-step plan:

1. Lock structure and define what “done” means (which screens, workflows, and reports must match).
2. Export data and clean it outside Airtable. Normalize multi-selects, split combined fields, and create stable IDs so links stay intact.
3. Create the PostgreSQL schema, then import in batches with checks. Validate row counts, required fields, uniqueness, and foreign keys.
4. Rebuild the daily essentials first: the few screens people use every day, plus create/update flows.
5. Run parallel for a short window, then cut over. Keep a rollback plan: read-only access to Airtable, a snapshot of PostgreSQL before cutover, and a clear stop rule if critical mismatches show up.

Example: for a sales ops base, run both systems for one week. Reps log activity in the new app, but the team checks pipeline totals against Airtable each morning until the numbers match consistently.

Data quality and testing: prove the new app matches reality

Most migration bugs aren’t “PostgreSQL bugs.” They’re mismatches between what Airtable meant and what your new tables now store. Treat testing as part of the data work, not a last-minute task.

Keep a simple mapping sheet. For every Airtable field, write the target Postgres column and where it’s used in the app (a screen, a report, a status rule). This prevents “we imported it” from turning into “we never use it.”

Start with fast sanity checks:

• Compare row counts per table before and after import.
• Check missing links (foreign keys that point to nothing).
• Find duplicates where values were “unique in practice” (emails, deal IDs).
• Spot empty required fields Airtable allowed through forms.

Then validate calculations people rely on. Pick real records and verify totals, statuses, and rollups against known examples. This is where formula replacements often drift, because blank, zero, and missing linked records behave differently.

Finally, test edge-case data on purpose: blanks, deleted links, long text, unusual characters, and line breaks. Names like "O'Neil" and notes with multiple lines are common sources of import and display issues.

Common traps when translating Airtable to PostgreSQL

The biggest trap is treating an Airtable base like a simple database export. Airtable blends storage, view logic, formulas, and sharing rules. PostgreSQL separates those concerns, which is healthier in production, but it forces you to choose where each behavior belongs.

Linked records are a classic example. Many teams assume every link is one-to-many because it looks like a single field. In practice, many Airtable links are many-to-many. If you model that as a single foreign key, you quietly lose relationships and end up with workarounds later.

Rollups can cause a different problem. If you import the current rollup number as stored truth, you also need to capture how it was computed. Otherwise you won’t be able to explain why the number changes later. Prefer recomputable aggregates (SUM/COUNT) with clear definitions, and decide whether you need caching and how it updates.

Views can mislead too. Teams sometimes rebuild Airtable views as fixed filters in the new app, then discover those views were personal workflows, not shared requirements. Before you lock in filters, ask who used the view, what action they took next, and whether they need saved filters, segments, or a dashboard.

A quick trap checklist:

• Free-text statuses (“In progress”, “in-progress”, “IP”) without cleanup and controlled values
• Rollups imported as final answers with no definition or recalculation plan
• Link fields modeled without join tables when relationships are many-to-many
• Views rebuilt as fixed screens without confirming user intent
• Permissions added last, forcing painful rewrites

Example scenario: a sales ops base rebuilt as a real app

Picture a Sales Ops Airtable base with four tables: Accounts, Deals, Activities, and Owners (reps and managers). In Airtable, a Deal links to one Account and one Owner, and Activities link to a Deal (calls, emails, demos).

In PostgreSQL, this becomes a clear set of relations: deals.account_id points to accounts.id, deals.owner_id points to owners.id, and activities.deal_id points to deals.id. If you also need multiple owners per deal (rep + sales engineer), you add a join table like deal_owners.

A common Airtable metric is “Deal Value rollup by Account” (sum of linked deal values). In a database-backed app, that rollup becomes an aggregate query you can run on demand, cache, or materialize:

SELECT a.id, a.name,
       COALESCE(SUM(d.amount), 0) AS total_pipeline
FROM accounts a
LEFT JOIN deals d ON d.account_id = a.id
              AND d.stage NOT IN ('Closed Won', 'Closed Lost')
GROUP BY a.id, a.name;

Now consider a “Health score” formula. In Airtable it’s tempting to cram everything into one field. For production, keep the inputs stored and auditable (last_activity_at, next_step_date, open_deal_count, overdue_tasks_count). Then compute health_score in backend logic so you can change the rules without rewriting old records. You can still store the latest score for filtering and reporting.

Permissions usually need the biggest rethink. Instead of view filters, define explicit access rules:

• Reps can see and edit only their own deals and activities.
• Managers can see their team’s deals.
• Finance can see closed-won revenue, but not private notes.
• Sales Ops can manage stages and scoring rules.

Quick checklist before you ship the new PostgreSQL app

Before you go live, do one last pass to make sure the “Airtable feeling” has been translated into something stable, testable, and safe. This is where small gaps become real incidents.

If you’re trying to migrate from Airtable to PostgreSQL, focus on what Airtable used to “quietly handle” for you: relationships, computed values, and who can see or change what.

Pre-launch checks that catch most surprises

• Relationships: every former linked record has an explicit relationship type (one-to-many, many-to-many) and a clear key strategy (stable IDs, unique constraints, and delete rules).
• Aggregates: you’ve labeled which totals must always be correct (invoices, quotas, eligibility) vs which can be slightly delayed (dashboards).
• Decision logic: every formula that changes outcomes (approval, pricing, commissions, eligibility) is implemented and tested where it belongs.
• Permissions: for each role, you ran real user stories end-to-end (create, edit, export, delete, approve) and confirmed record-level access.
• Ownership and deployment: you’ve decided who owns schema changes, who reviews logic changes, how rollbacks work, and where the app runs.

A reality check: if a sales rep could edit “Account Tier” in Airtable and that tier drives discounts, you probably need both a permission change (only managers can edit) and an audit trail that records who changed it and when.

Next steps: build, launch, and keep improving

After you migrate from Airtable to PostgreSQL, the biggest risk is trying to rebuild everything at once. Start with a pilot that runs one real workflow end-to-end with real users. Pick something you can measure, like “create record - approve - notify - report,” and keep the scope tight.

Treat the pilot like a product. Write down the new data model and permission rules in plain language so non-technical owners can answer two questions quickly: “Where does this value come from?” and “Who can see or change it?”

Keep documentation lightweight. Most teams get far with:

• Key tables and what each represents
• Important relationships (and what deletes/archives should do)
• Which fields are computed (SQL vs app logic) and why
• Roles, record-level access rules, and who grants access
• Audit expectations (what must be logged)

If you want to move quickly without building everything from scratch, a no-code platform can work well as long as it produces a real backend and enforces rules consistently. For example, AppMaster (appmaster.io) is designed to build PostgreSQL-backed apps with business logic and role-based access, while still generating real source code for production use.

Roll out in phases so people can switch safely: pilot with one team, a short parallel run, a planned cutover with a rollback plan, then expand workflow by workflow.