The Spectrum
These aren't competing options — they're points on a continuum. Every path to the right includes everything from the paths to its left. Start where it makes sense, evolve when you need to.
Current State
Ad-hoc API scripts between PostgreSQL and Airtable. No contracts, no sync tracking, no governance.
Effort: Zero
Path A: Declarative Sync
A lightweight sync service configured via YAML. No new query layer — each system stays native.
Effort: Weeks
Path B: Federated DAL
Sync + a unified API layer (Hasura). All consumers query one interface. Governance built in.
Effort: Months
Path C: Consolidation
Migrate everything to PostgreSQL. Single source of truth. Airtable becomes read-only UI.
Effort: Quarters
Key insight: You don't need to choose the final destination upfront. Path A's sync service carries forward into Path B. Path B's API layer carries forward into Path C. Each step delivers value on its own and de-risks the next one.
Side-by-Side Comparison
| Dimension | Current State | A: Declarative Sync | B: Federated DAL | C: Consolidation |
|---|---|---|---|---|
| Complexity | Lowest | Low | Medium | High |
| What changes | Nothing | Add sync service | Add Hasura + sync | Migrate everything |
| Consumers change? | No | No | Yes New API | Yes New schema |
| Sync reliability | Fragile | Governed | Governed | N/A (one system) |
| Time to value | Immediate | 2–4 weeks | 2–4 months | 5–6 months |
| Team disruption | None | None | Low | High |
| Query simplicity | Ad-hoc | Native per DB | Unified API | One database |
| Vendor lock-in | High | High | Medium | None |
| Formula debt | Untouched | Untouched | Reduced | Resolved |
| Long-term cost | Airtable licensing + fragile scripts | Airtable licensing | Airtable + Hasura hosting | PostgreSQL only |
| Upgrade path | → Any path | → B or C | → C | Final state |
Common Ground: What Every Path Needs
Regardless of which path EFI chooses, PostgreSQL needs structural improvements first. This is a prerequisite for any sync — ad-hoc or governed.
Add FKs to static reference tables (ports, countries, units, distro centers). Add check constraints,
enums for status fields, and partial indexes on meta_deleted = false. Create views
like current_product_pricing and current_freight_rates.
After Phase 1, you choose: Stay with ad-hoc scripts (current state), add declarative sync (Path A), go further with a unified API (Path B), or plan for full consolidation (Path C).
Current State: Ad-Hoc API Scripts
The baseline. One-off Node scripts move data between PostgreSQL and Airtable on schedules or manual triggers. No contracts, no error handling, no audit trail.
How It Works Today
- No contract or schema
- No versioning
- No error handling
- No sync tracking
- No conflict resolution
Why It's Worked So Far
The ad-hoc approach works when the overlap is small and changes are infrequent. With only 19 overlapping entities out of 145 total tables, most data lives in one system and doesn't need syncing. The scripts break when someone changes a field name or adds a column — but that happens slowly enough that someone notices and fixes it.
Where It Breaks Down
Silent failures
A script fails at 3am, nobody notices until someone asks why a dashboard is stale. No alerting, no retry logic, no audit log.
No conflict resolution
If the same customer record is edited in both systems within the same sync window, one write silently overwrites the other.
Tribal knowledge
Only one or two people understand which scripts sync what, when, and in which direction. That's a bus-factor problem.
Scaling pain
Every new sync relationship requires writing a new script from scratch. There's no reuse, no shared patterns, no configuration.
Path A: Declarative Sync
Replace ad-hoc scripts with a single sync service configured via YAML. Each system stays native — consumers don't change how they query. The sync just works, reliably, with logging, conflict resolution, and monitoring baked in.
Philosophy
The current pain isn't "we need a unified query layer" — it's "data that lives in one system needs to show up in the other, and the scripts that do this are fragile." Declarative sync solves that directly. You define what syncs, the service handles how.
How It Works
webhooks / polling
YAML-configured
LISTEN/NOTIFY
# sync-config.yaml — defines all entity sync relationships
entities:
customers:
owner: airtable
airtable_table: Customers
pg_table: customers
direction: bidirectional
conflict: owner_wins
poll_interval: 60s
fields:
- at: "Company Name" pg: company_name
- at: "Rep" pg: rep_id transform: lookup
- at: "Status" pg: status
- at: "Region" pg: region
products:
owner: postgresql
pg_table: products
airtable_table: Products
direction: pg_to_at
fields:
- pg: name at: "Product Name"
- pg: sku at: "SKU"
- pg: category at: "Category"
- pg: active at: "Active" transform: boolean
suppliers:
owner: postgresql
pg_table: suppliers
airtable_table: Suppliers
direction: pg_to_at
fields:
- pg: name at: "Supplier Name"
- pg: country at: "Country"
- pg: status at: "Status"
# ... 16 more overlapping entities
Phases
Build the sync runtime — YAML parser, ID mapping table, change detection (Airtable polling + PG LISTEN/NOTIFY), conflict resolution engine, and audit logging. Start with one entity to prove the pattern.
Products, Customers, Suppliers, Sales Reps, Warehouses, Ports. These change rarely, have clear ownership, and are referenced by everything else. Prove the pattern at low risk.
Supplier POs, Containers, Customer Contracts, Sales Orders. Higher change frequency, more complex field mappings, linked record resolution. Conflict resolution gets exercised here.
Once all 19 overlapping entities sync through the service, remove the old cron jobs and manual scripts. One service, one config file, one place to monitor.
The Sync Service Architecture
Change Detection
Airtable → PG: Poll Airtable on configurable intervals (30–120s per entity).
Compare lastModifiedTime against last sync timestamp.
PG → Airtable: Use LISTEN/NOTIFY on
trigger-instrumented tables, or poll a last_modified column.
ID Mapping
Every synced record gets an entry in sync_id_map linking PG integer IDs
to Airtable record IDs. The YAML config defines the entity type — the service manages the rest.
Conflict Resolution
Configurable per entity in YAML: owner_wins, last_write_wins,
pg_wins, at_wins, or flag_for_review.
Conflicts are logged and can trigger alerts.
-- The ID mapping table — needed by all paths from here forward CREATE TABLE sync_id_map ( id BIGSERIAL PRIMARY KEY, entity_type TEXT NOT NULL, -- from YAML: 'customers', 'products' pg_id BIGINT NOT NULL, airtable_id TEXT NOT NULL, -- 'recXXXXXXXXXXXX' airtable_tbl TEXT NOT NULL, owner_system TEXT NOT NULL DEFAULT 'pg', last_synced TIMESTAMP NOT NULL DEFAULT NOW(), sync_status TEXT DEFAULT 'ok', -- 'ok', 'conflict', 'error' UNIQUE(entity_type, pg_id), UNIQUE(entity_type, airtable_id) ); -- Sync audit log — every change is recorded CREATE TABLE sync_log ( id BIGSERIAL PRIMARY KEY, entity_type TEXT NOT NULL, direction TEXT NOT NULL, -- 'at_to_pg', 'pg_to_at' record_id TEXT NOT NULL, action TEXT NOT NULL, -- 'create', 'update', 'conflict' fields_changed JSONB, resolved_by TEXT, -- 'owner_wins', 'manual', etc. created_at TIMESTAMP DEFAULT NOW() );
Strengths
Zero consumer disruption
Sales keeps using Airtable. Supply chain keeps using PostgreSQL. Dashboards keep querying the same way. Nobody retrains, nobody changes workflow.
YAML is the documentation
The sync config is human-readable, version-controlled, and self-documenting. New team members can read it and understand what syncs where.
Fast time to value
You can have the first entity syncing in under a week. Full coverage of all 19 overlapping entities in 8–10 weeks.
Natural upgrade path
The sync service, ID mappings, and conflict resolution all carry forward if you later add Hasura (Path B) or consolidate (Path C). Nothing is throwaway.
Risks
No unified query interface
Consumers still query their own database natively. Cross-system joins require data to be synced first — you can't query across both in real time.
Airtable API rate limits
Airtable's API is capped at 5 requests/second. With 19 entities polling, you need to be smart about batching and intervals. Heavy sync bursts can hit limits.
Field transforms add complexity
Linked records, rollups, and lookup fields in Airtable don't map cleanly to relational columns. Each transform type needs handling in the sync service.
Doesn't address formula debt
The 2,455 computed fields in Airtable stay where they are. Sync keeps data fresh, but business logic remains distributed across both systems.
Best if: your main pain is unreliable sync between the two systems, and you want to fix that without introducing new infrastructure or changing how anyone queries data. You value simplicity and speed over architectural purity.
Path B: Federated + Data Access Layer
Everything from Path A, plus a unified API layer (Hasura) that gives all consumers a single query interface over both systems. Each system stays as the system of record for its domain. PostgreSQL owns supply chain. Airtable owns sales/ops.
Philosophy
Inspired by Data Mesh and polyglot persistence — the principle that different data types have different needs, and the teams closest to the data should own it. Instead of consolidating into one database, you build a governance layer above both. The Data Access Layer becomes the "single pane of glass" without requiring a single database.
What It Adds Over Path A
Unified API
Hasura gives every consumer — dashboards, report agent, apps — one GraphQL/REST endpoint. Where data physically lives becomes invisible.
Access Control
Row-level permissions, API keys, role-based access. No more direct database credentials shared across teams and scripts.
Event Triggers
Hasura event triggers replace polling for PG → Airtable sync. Changes fire immediately. The sync service from Path A becomes event-driven.
ops/sales UI
Hasura GraphQL
ERP database
Additional Phases (after Path A foundation)
Instant GraphQL + REST over PostgreSQL. Auto-reads FKs for relationship queries. Event triggers replace polling for PG-side change detection. All consumers migrate to the API.
Document which system owns which entities. Define conflict resolution rules. For each of the 19 overlapping entities, designate one system as authoritative.
Create a read-only PostgreSQL replica that contains both ERP data and synced copies of Airtable data. The report agent and dashboards query this — unified reads, distributed writes.
Move the heaviest computation (FIFO costing, availability rolls) to PostgreSQL views that sync back as read-only fields. Keep simple formulas in Airtable. Target the ~100 complex formulas.
Strengths
Minimal disruption
Sales and ops teams keep their Airtable workflows. No retraining, no UI changes, no write-path switches.
Unified query interface
The report agent, dashboards, and any new app query one API instead of stitching together two databases.
Respects domain expertise
The sales team knows their CRM data better than anyone. Keeping them as owners reduces data quality risk.
Incremental approach
Start with sync (Path A), add API layer, optimize formulas gradually. Each step delivers value independently.
Risks
Airtable remains a dependency
24 critical tables still live exclusively in Airtable. Pricing changes, API limits, or outages directly impact the business.
Eventual consistency
With two systems of record, there's always a sync lag. Conflicting updates on overlapping entities require careful handling.
Consumer migration
Everyone who currently queries PostgreSQL directly needs to switch to the Hasura API. That's an adoption project.
Governance requires discipline
Domain ownership only works if ownership is clearly documented and enforced. Without it, you get the current state — ad-hoc everything.
Best if: you need a unified query interface for the report agent and dashboards, want real-time event-driven sync, and are willing to invest in a proper API layer. You value governance and want to keep both teams in their preferred tools.
Path C: Consolidation
PostgreSQL becomes the single source of truth. Migrate all 24 Airtable-only tables into PostgreSQL. Move business logic from Airtable formulas to SQL views and functions. Airtable becomes a read-only UI.
Philosophy
One database, one truth, one place to query. Inspired by traditional enterprise architecture and the principle that business-critical data should live in a system you fully control. All writes flow through the API layer to PostgreSQL. Airtable reads from PostgreSQL via sync.
What It Adds Over Path B
Full Data Ownership
All 24 Airtable-only tables (Customer Contracts, Sales Orders, FIFO Cost Layers, etc.) move to PostgreSQL. No critical data in a vendor-controlled system.
Formula → SQL
2,455 computed fields become SQL views and functions — testable, version-controlled, deployable. The ~100 complex formulas get rethought, not just copied.
No Sync Layer
Once everything is in PostgreSQL, the sync service from Paths A and B becomes unnecessary. Airtable reads from PG via API — one direction only.
Additional Phases (after Path B foundation)
Build PostgreSQL equivalents for all 24 Airtable-only tables. Priority order: Sales & Contracts → Inventory & Costing → Reporting & Forecasts.
Migrate historical data. Run both systems in parallel with bidirectional sync. Validate data matches before switching writes. The sync service from Path A handles the bridge period.
Replace Airtable formulas with PostgreSQL views and functions. 82% are trivial (lookups, basic arithmetic) — focus effort on the ~100 complex formulas. Validate output matches for every table before switching.
Lock Airtable writes for synced tables. All writes go through API → PostgreSQL → sync to Airtable. Sales/ops teams continue using Airtable as their UI — they just can't break the data.
Strengths
Full SQL power
Complex queries, joins, window functions, CTEs across all business data. The report agent gets one unified schema.
Version-controlled logic
Business rules in SQL views and functions — testable, auditable, deployable via migrations.
No vendor lock-in
All critical data in open-source PostgreSQL. If Airtable changes pricing or limits, it doesn't matter.
Single query interface
Every dashboard, report, API call, and AI agent hits one database. No data reconciliation needed.
Risks
Formula migration is smaller than it looks
2,455 computed fields sounds massive, but 82% are trivial or simple arithmetic. Only ~100 unique formulas need real analysis. Still, validating outputs match takes time.
Team disruption
Sales and ops teams built their workflows around Airtable's UI and formulas. Changing write paths is organizational change, not just technical.
The "stuck in the middle" risk
During migration you're maintaining two systems AND a sync layer — arguably worse than the current state for a period.
Airtable's UI advantage
Airtable is genuinely good at what it does. Rebuilding that UI experience on top of PostgreSQL requires significant frontend work.
Best if: you want full control, SQL-native business logic, zero vendor dependency for critical data, and long-term simplicity. You're willing to invest months of migration work for a clean end state.
Recommended Approach
Start with Path A. Fix the immediate pain (unreliable sync) in weeks, not months. The sync service, ID mappings, and YAML configs all carry forward. Once sync is stable and you've lived with it for a cycle, you'll have much better data on whether you need the unified API of Path B or the full control of Path C — and the foundation will already be built.
FK constraints, YAML-configured sync for all 19 overlapping entities, decommission ad-hoc scripts.
If the report agent, dashboards, or new apps are struggling with native queries across two systems, add Hasura (Path B). If native queries are fine, stay on Path A — it's working.
If Airtable becomes a cost or reliability bottleneck, the sync service and API layer from Paths A and B provide the bridge for a full migration to PostgreSQL (Path C).
Technology Stack
| Layer | Tool | Path | Why |
|---|---|---|---|
| Database | PostgreSQL 16 (existing) | All | Already in place — add constraints, enums, views, partial indexes |
| Sync Service | Node.js + Airtable SDK | A+ | YAML-configured bidirectional sync with conflict resolution |
| Data Access Layer | Hasura v2 (self-hosted) | B+ | Instant GraphQL, event triggers, permissions |
| Schema Mgmt | Prisma Migrate | B+ | Version-controlled migrations, type-safe schema |
| Data Dictionary | This Gatsby site + dbt docs | All | Documents ownership, lineage, and entity mapping |
| Report Agent | Node.js + Anthropic SDK | All | Queries Hasura API (Path B+) or native PG (Path A) |
Hasura (Recommended for B+)
Instant GraphQL + REST over PostgreSQL. Auto-reads FKs for relationship queries. Real-time subscriptions. Event triggers for Airtable sync.
PostgREST (Alternative for B)
Lighter weight. Instant REST API from PostgreSQL. Simpler but less flexible — no subscriptions, no event triggers.
Prisma + tRPC (Max Control)
Full ORM with typed API. Most flexibility but most code to write. Best for complex business logic in Path C.
What Not to Do (Any Path)
Don't skip the hardening
FKs, constraints, and views on PostgreSQL are needed regardless of path. This is the foundation everything else builds on.
Don't try to sync everything
Start with the 19 overlapping entities. The 34 PG-only and 24 AT-only tables don't need sync — they're fine where they are.
Don't abandon Airtable prematurely
Even on Path C, sales and ops depend on it daily. The transition must be invisible to end users.
Don't replicate the formula mess
Those 2,455 computed fields are a symptom of business logic in the wrong layer. The ~100 complex formulas are the real targets — rethink them, don't just copy them.
Quick Wins — This Week
Set up Prisma
Run npx prisma db pull to generate a schema from existing PostgreSQL.
Add reference table FKs
FKs to ref_efi_distro_centers, ref_ports, ref_countries, ref_units — static tables that never soft-delete.
Create 2 views
current_product_pricing and current_freight_rates — the report agent needs these.
Draft sync YAML
Write the first version of sync-config.yaml for the 5 highest-value entities. This is the design doc for Path A.