DEV Community

Cover image for ERP Development Services: Idempotent Stock Sync in Python and PostgreSQL
Sanya Mittal
Sanya Mittal

Posted on

ERP Development Services: Idempotent Stock Sync in Python and PostgreSQL

A warehouse scanner posts a goods-receipt webhook. Your ERP takes 31 seconds to answer, the scanner times out at 30, and it retries. Now the ledger shows 200 units received instead of 100. This is a recurring bug in ERP development services that connect scanners, storefronts, and payment gateways to a stock module. It appears whenever an at-least-once sender meets a handler that is not idempotent. Below, we show how custom ERP development handles stock sync with an append-only ledger and a unique key, using Python 3.12, psycopg 3, and PostgreSQL.

Context and Setup

In ERP development services idempotency means processing the same event twice leaves the system in the same state as processing it once. For stock, that means one real receipt produces exactly one ledger row, no matter how many times the sender retries.

The scenario: an external system sends events over HTTP to a Python service, which writes to PostgreSQL. You need PostgreSQL 13 or later, psycopg 3, and a sender that supplies a stable event ID. PostgreSQL is a safe base for this. In the 2025 Stack Overflow Developer Survey, it ranked as the most admired database, with roughly 66 percent of its users wanting to keep using it.

Building Idempotent Stock Sync for ERP Development Services

The short version: let the database reject duplicates, and derive balances from history instead of mutating counters.

Step 1: Design the ledger for ERP stock sync

Start with what the table must guarantee, then write it. Three rules drive the schema:

Rows are only inserted, never updated.
Each real-world event maps to one unique key.
The unique constraint lives in the database, not in application code.
sql
-- Append-only ledger: history is the source of truth
CREATE TABLE stock_ledger (
id BIGSERIAL PRIMARY KEY,
source TEXT NOT NULL, -- which system sent the event
idem_key TEXT NOT NULL, -- sender's event ID, stable across retries
sku TEXT NOT NULL,
warehouse_id INT NOT NULL,
qty_delta NUMERIC(14,3) NOT NULL, -- signed: receipts positive, picks negative
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE (source, idem_key) -- Why: the index arbitrates concurrent retries
);
Step 2: Insert with ON CONFLICT

A retry should become a quiet no-op, and the sender should still get a success response. Otherwise it keeps retrying.

python
import psycopg

INSERT_SQL = """
INSERT INTO stock_ledger (source, idem_key, sku, warehouse_id, qty_delta)
VALUES (%(source)s, %(event_id)s, %(sku)s, %(warehouse_id)s, %(qty_delta)s)
ON CONFLICT (source, idem_key) DO NOTHING -- Why: duplicate becomes a no-op, not an exception
RETURNING id
"""

def record_movement(conn: psycopg.Connection, event: dict) -> bool:
# Why: ledger write and side effects commit or roll back together
with conn.transaction():
row = conn.execute(INSERT_SQL, event).fetchone()
if row is None:
return False # duplicate: caller still returns HTTP 200 so retries stop
# side effects (reservation release, alerts) go here, inside the same transaction
return True

Read balances from the ledger instead of storing a counter:

sql
-- Why: a derived balance cannot drift from the history that produced it
SELECT sku, warehouse_id, SUM(qty_delta) AS on_hand
FROM stock_ledger
GROUP BY sku, warehouse_id;
Step 3: Judge the trade-offs for ERP development services

Each alternative fails in a specific way:

SELECT, then INSERT: two concurrent retries can both see no row and both insert.
Redis key cache: keys can be evicted, and the cache write is not atomic with the ledger write.
Mutable on-hand counter: concurrent updates can overwrite each other, and you lose the audit trail.

The cost of this design is growth. The unique index and the ledger only get larger, and full-table SUM queries slow down. We partition the ledger by month and keep a summary table, updated in the same transaction, for hot reads.

Real-World Application

In one of our ERP development services projects at Oodles, we built an Odoo Community platform on PostgreSQL for a dairy products distributor, covering inventory, order processing, and sales commission tracking.

Key Takeaways
Put deduplication in a database unique constraint, because application-level checks race under concurrent retries.
Use the sender’s event ID as the key, since a payload hash collapses two legitimate identical events.
Return success on duplicates so the sender stops retrying.
Derive balances from an append-only ledger, and add a summary table only for hot reads.
Plan ledger partitioning early, because append-only tables only grow.

Questions or Edge Cases?
Share your retry and reconciliation stories in the comments. If you need a second opinion on your design, talk to us about ERP development services.

What is idempotency in ERP development services?
In ERP development services idempotency means handling the same event repeatedly leaves the system in the same state as handling it once. In ERP development services, it protects stock, invoices, and payments from duplicates caused by webhook retries, network timeouts, and double clicks.

Why use ON CONFLICT instead of SELECT before INSERT?
A SELECT followed by an INSERT has a race window: two concurrent retries can both see no row and both insert. ON CONFLICT DO NOTHING lets the unique index decide atomically, so exactly one insert succeeds regardless of concurrency.

Should the idempotency key be a payload hash?
Prefer the sender’s event ID. A payload hash treats two legitimate identical events, such as two receipts of 10 units, as duplicates. Hash the payload only when the sender provides no ID, and include a timestamp or sequence number.

How do I read stock quickly from an append-only ledger?
Keep a summary table keyed by SKU and warehouse, updated in the same transaction as the ledger insert, or refresh a materialized view on a schedule. Use the raw SUM query for reconciliation, not for every read.

How long should idempotency keys be kept?
Keep keys at least as long as your sender’s longest retry window, plus a margin. With a permanent ledger, the unique constraint covers this automatically. For separate key tables, expire rows only after that window passes.

Top comments (0)