An Airtable-to-ERP integration can work perfectly during testing and still create duplicate customers, orders, or inventory records in production. This is where well-designed ERP integration services need to solve more than API connectivity. The real challenge is keeping records consistent when webhooks are retried, workers fail, or two systems update the same record.
Suppose an integration sends an Airtable record to an ERP. The ERP accepts the request, but the network fails before the integration receives the response. The worker retries. If the ERP treats the second request as a new create operation, the same business record now exists twice.
We faced this type of synchronization problem while working on ERP integration workflows where Airtable acted as an operational workspace and the ERP remained the system of record.
For teams building ERP integration, this is an important distinction. An integration should not simply move data between systems. It should preserve data integrity when systems fail, retry, or change independently.
The solution was to stop treating synchronization as simple CRUD. We introduced stable external IDs, explicit field ownership, idempotent database writes, event-driven updates, and scheduled reconciliation.
That pattern is the focus of this article.
Why a simple Airtable-to-ERP sync breaks
A basic ERP integration workflow can initially look straightforward:
Airtable record
↓
API request
↓
ERP create/update
The problem starts when the same operation can happen more than once.
A webhook can be delivered again. A worker can retry after a timeout. A scheduled job can process a record that was already handled. A user can edit a record while an earlier synchronization is still running.
The integration therefore needs to answer one question before writing anything:
Is this the same business record I already processed?
Using email addresses or names for this check is unreliable. Names can change, and multiple customers can share an email address depending on the business workflow.
A reliable approach to ERP integration services instead uses a stable source identifier.
Step 1: Give every synchronized record an external ID
The Airtable record ID becomes the source identifier.
Our synchronization table keeps both the ERP identifier and the Airtable identifier:
CREATE TABLE customer_sync (
id BIGSERIAL PRIMARY KEY,
airtable_record_id TEXT NOT NULL UNIQUE,
erp_customer_id BIGINT,
last_synced_at TIMESTAMPTZ,
source_updated_at TIMESTAMPTZ
);
The important part is not the column name. It is the unique constraint.
The application can now receive the same Airtable record multiple times without creating multiple synchronization entries.
For an ERP integration, this distinction matters:
Airtable record ID ≠ ERP customer ID
The Airtable ID identifies the source record.
The ERP ID identifies the destination record.
The integration layer owns the relationship between them. This mapping is also useful when ERP integration connect several external applications to the same ERP instance.
Step 2: Replace read-then-create with an idempotent write
The common implementation is:
// Naive approach: the SELECT and INSERT can race when two workers run together.
const existing = await db.query(
"SELECT id FROM customer_sync WHERE airtable_record_id = $1",
[record.id]
);
if (existing.rowCount === 0) {
await db.query(
"INSERT INTO customer_sync (airtable_record_id) VALUES ($1)",
[record.id]
);
}
This looks safe but has a race condition.
Two workers can execute the SELECT at almost the same time. Both see no record. Both attempt the INSERT.
The database should enforce the invariant instead.
// ERP integration pattern: PostgreSQL makes the mapping idempotent through the unique constraint.
await db.query(
`
INSERT INTO customer_sync (
airtable_record_id,
erp_customer_id,
last_synced_at
)
VALUES ($1, $2, NOW())
ON CONFLICT (airtable_record_id)
DO UPDATE SET
erp_customer_id = EXCLUDED.erp_customer_id,
last_synced_at = NOW()
`,
[record.id, erpCustomerId]
);
PostgreSQL's ON CONFLICT DO UPDATE provides an atomic insert-or-update operation when backed by the appropriate unique constraint.
That gives us an important property: retrying the same synchronization does not create another mapping.
The same principle should be applied to the ERP side whenever its API supports an external reference or idempotency mechanism.
For ERP integration, this pattern becomes especially important when multiple workers process the same queue concurrently.
Step 3: Respect Airtable's API limits during synchronization
Idempotency solves duplicate writes, but it does not solve excessive API traffic.
Airtable's Web API currently limits requests to 5 requests per second per base. Record listing is also paginated, with up to 100 records returned per page.
That changes how a synchronization worker should be designed.
Do not fetch one record, process it, fetch another record, and repeat without controlling concurrency.
Instead, paginate deliberately and process bounded batches:
// Airtable integration note: pagination avoids loading an entire table into memory at once.
async function getAllRecords(baseId, tableId, token) {
const records = [];
let offset;
do {
const url = new URL(
`https://api.airtable.com/v0/${baseId}/${encodeURIComponent(tableId)}`
);
url.searchParams.set("pageSize", "100");
if (offset) {
url.searchParams.set("offset", offset);
}
const response = await fetch(url, {
headers: {
Authorization: `Bearer ${token}`
}
});
if (!response.ok) {
throw new Error(`Airtable API failed: ${response.status}`);
}
const page = await response.json();
records.push(...page.records);
offset = page.offset;
} while (offset);
return records;
}
If the API returns HTTP 429, the worker should back off instead of immediately retrying in a tight loop.
For large synchronization jobs, batching also matters. Airtable supports batches of up to 10 records for several write operations, reducing the number of HTTP requests required for the same amount of work.
The important trade-off is clear: more concurrency is not automatically more throughput when the upstream API has a fixed rate limit.
This is why ERP integration should consider upstream API constraints during architecture design rather than after deployment.
Step 4: Separate event-driven updates from reconciliation
Once the write path is idempotent, we can make synchronization faster without making it fragile.
Airtable's Webhooks API can notify an integration about changes such as new records and updated fields.
The event path can therefore look like this:
Airtable
↓
Webhook
↓
Queue / Worker
↓
Validate + Map
↓
ERP API
↓
PostgreSQL sync mapping
But webhooks should not be treated as the only source of truth.
We also run reconciliation.
For example:
Every 15 minutes
↓
Find records changed since last checkpoint
↓
Compare source_updated_at
↓
Repair missing or failed mappings
This protects the system against missed events, temporary outages, and manually corrected records.
The architecture becomes two complementary paths:
Fast path:
Webhook → ERP
Safety path:
Scheduled reconciliation → ERP
That combination is more useful than trying to make either mechanism responsible for everything.
For ERP integration, reconciliation provides an additional recovery mechanism when the event-driven path does not complete successfully.
Real-World Application
That trade-off between fast synchronization and safe retries became important in our ERP integration work with Airtable.
We treated Airtable as the operational workspace while keeping the ERP as the system of record. The initial risk was duplicate submissions and unintended overwrites when the same record entered the synchronization flow more than once.
Instead of relying on a worker-level check, we introduced a persistent mapping using the Airtable record ID as the external identifier. PostgreSQL enforced uniqueness, while the synchronization worker used an upsert path for repeat events.
We also separated field ownership. Fields maintained by the ERP were not blindly overwritten by Airtable updates. The integration mapped each field to a defined source of truth.
For the API layer, we used pagination, bounded processing, retry backoff, and webhook-triggered synchronization. A scheduled reconciliation process handled records that did not reach the normal event path.
This approach is also relevant when ERP integration involve CRM, accounting, inventory, or other business platforms. Each additional system introduces another synchronization boundary that needs explicit ownership and recovery behavior.
The measurable design outcome was that a repeated event became one idempotent database operation rather than another customer or transaction record. The API path also avoided making a separate existence-check request before every write.
We did not rely on a claimed latency improvement because that depends on the ERP API, network conditions, record volume, and deployment environment. Those values should be measured against the actual production workload rather than copied from a generic benchmark.
Connecting the integration to the wider ERP architecture
Once Airtable synchronization becomes part of a larger business workflow, the integration layer has to account for more than one API.
For example:
Airtable
↓
Integration Layer
├── ERP
├── CRM
├── Accounting
└── Reporting
This is where ERP integration services need clear ownership, transformation rules, error handling, logging, and observability.
Our broader engineering work at Oodles covers API integrations, middleware, webhooks, REST APIs, and ERP platforms.
The same architectural principles apply whether Airtable is connected to an ERP directly or becomes one component in a larger business data flow.
The important distinction is that every additional system introduces another possible failure boundary.
That makes centralized mapping and reconciliation increasingly valuable.
What the architecture looks like in production
By this point, the integration is no longer just an API connector.
It is a small synchronization system with explicit responsibilities:
┌──────────────┐
│ Airtable │
└──────┬───────┘
│
Webhook / API
│
▼
┌─────────────────┐
│ Integration │
│ Worker │
└───────┬─────────┘
│
Validate / Transform
│
┌───────────┴───────────┐
▼ ▼
┌──────────────┐ ┌──────────────┐
│ PostgreSQL │ │ ERP API │
│ Sync Mapping │ │ │
└──────────────┘ └──────────────┘
▲ │
└──── Reconciliation ──┘
The database is responsible for enforcing uniqueness.
The integration layer is responsible for transformation and orchestration.
The ERP remains the system of record.
Airtable remains the operational source only for fields that the integration explicitly assigns to it.
That ownership model prevents an integration from becoming an uncontrolled two-way overwrite engine.
For teams evaluating ERP integration services, the key architectural question is therefore not simply whether two APIs can communicate. It is whether the integration can maintain a predictable state when either side fails or changes independently.
FAQ
What are ERP integration services?
ERP integration services connect an ERP system with external applications, databases, platforms, and business tools. They can include API integration, middleware, data synchronization, webhooks, transformation, migration, and error handling.
How does Airtable integrate with an ERP?
Airtable can connect to an ERP through REST APIs, webhooks, middleware, or a custom integration service. A typical architecture uses Airtable for selected operational data, an integration layer for transformation, and the ERP as the system of record.
Why is idempotency important in ERP integration?
Idempotency prevents the same event from creating duplicate ERP records when a webhook or background worker retries an operation. A stable Airtable record ID combined with a unique database constraint provides a reliable way to identify previously processed records.
Can Airtable and an ERP be synchronized in real time?
Yes. Airtable webhooks can trigger an integration when relevant records change. The integration can validate the event, transform the data, and send the required update to the ERP. A scheduled reconciliation process can handle events that fail or are missed.
What database is suitable for Airtable ERP integration?
PostgreSQL is a strong option when the integration requires relational mappings, unique constraints, transactions, and audit information. A synchronization table can maintain the relationship between an Airtable record ID and its corresponding ERP record ID.
What should be considered before starting ERP integration services?
Define the systems involved, data ownership, synchronization direction, external identifiers, API limits, retry behavior, conflict resolution, error handling, and reconciliation strategy before implementation begins. These decisions prevent many integration problems from appearing after deployment.
When should an ERP use middleware for Airtable integration?
Middleware becomes useful when the ERP must communicate with multiple external systems or when transformation, queuing, authentication, retries, logging, and reconciliation need to be centralized. This is a common architectural requirement for larger ERP integration services projects.
Conclusion
- ERP integration services should use a stable Airtable record ID as the external identity instead of matching records by names or mutable business fields.
- Put uniqueness guarantees in PostgreSQL rather than relying only on application-level existence checks.
- Design retries as a normal operating condition. A repeated event should produce the same final state.
- Respect Airtable's 5 requests-per-second per-base rate limit and its paginated record API.
- Use webhooks for fast updates and reconciliation for recovery from missed or failed synchronization events.
- ERP integration services should define field ownership before implementing two-way synchronization. Otherwise, the integration can overwrite newer business data.
If you have built an Airtable-to-ERP synchronization layer, I would be interested to hear how you handle retries, conflict resolution, and reconciliation. You can also share your integration requirements with the Oodles team if you want to discuss a specific integration architecture.
Top comments (0)