Duplicate Order Keys and Null Timestamps Broke Our Client Billing Sync

Duplicate Order Keys and Null Timestamps Broke Our Client Billing Sync

The Incident: Duplicate Orders and Null Timestamps in a Shopify-Odoo Billing Sync

The Setup

We were contracted by a mid-sized Indian e-commerce retailer to build a real-time billing sync between their Shopify store and an on-premise Odoo ERP instance. The integration was built using a Python-based webhook handler deployed on AWS Lambda, triggered by Shopify order creation webhooks. The handler parsed the incoming JSON payload, transformed it into an Odoo-compatible sales order structure, and pushed it via the Odoo XML-RPC API.

The handler lived at /opt/agentic/labs/shopify-odoo-sync/handler.py and was invoked by API Gateway. Each Shopify order webhook carried a payload with order_id, created_at, customer, line_items, and total_price. We assumed Shopify delivered each webhook exactly once. That assumption was wrong.

The Failure

Within two weeks of going live, the client reported that their Odoo instance was accumulating duplicate sales orders. Worse, some orders had null or malformed timestamps, causing downstream billing jobs to skip or misprocess them. The duplicates were not just cosmetic. They were inflating inventory reservations, generating duplicate invoices, and breaking reconciliation reports.

The first sign came from the finance team in Pune. Their daily invoice run at 2 AM IST started producing two invoices for the same Shopify order. The second sign came from the warehouse in Sikar. Stock levels for popular SKUs were dropping faster than actual sales, because each duplicate order reserved inventory again.

What We Tried First (And Why It Failed)

Our initial fix was naive. We added a uniqueness constraint on the customer name and order total in Odoo. This failed because:

• Customer names are not unique. Multiple customers share the same name.
• Order totals can legitimately be identical for different orders.
• The constraint caused Odoo to raise exceptions on legitimate new orders, leading to data loss.

We then tried matching on email address, assuming each customer had a unique email. This also failed because:

• The same customer placed multiple orders, all with the same email.
• Some orders had masked or missing email fields in the Shopify payload.

The constraint approach was a band-aid. It did not address the root cause: our handler was not idempotent.

The Root Cause

The root cause was twofold.

1. Duplicate Orders: Shopify webhooks are delivered at least once. Our handler assumed exactly-once delivery and called the Odoo sale.order.create API on every webhook event without checking if the order already existed. Retries from Shopify, network timeouts, or Lambda cold starts all produced a second call to the same endpoint with the same payload.

2. Null Timestamps: The Shopify webhook payload sometimes contained null or empty values for the created_at field, especially during retries or when the order was created via the Shopify mobile app. Our handler passed these null values directly to Odoo, which stored them as null timestamps. Downstream billing jobs that filtered on create_date IS NOT NULL silently skipped those records.

The BrainCuber team documents this exact failure mode: webhooks are delivered at least once, and an unguarded handler that calls create on each delivery produces duplicate orders, wrong stock, and double invoices Stop Duplicate Orders.

The Working Fix

We implemented a two-part solution.

Part 1: Idempotency Key on Shopify Order ID

We modified the handler to use the Shopify order ID (order_id field in the webhook payload) as the idempotency key. Before creating a new Odoo sales order, the handler now checks if an Odoo order already exists with that Shopify order ID stored in a custom field (x_shopify_order_id).

# Check if order already exists in Odoo
existing_order = odoo_client.call('sale.order', 'search', [[['x_shopify_order_id', '=', shopify_order_id]]])
if existing_order:
    # Update existing order instead of creating a new one
    odoo_client.call('sale.order', 'write', [existing_order[0], order_data])
else:
    # Create new order and store the Shopify order ID
    order_data['x_shopify_order_id'] = shopify_order_id
    new_order = odoo_client.call('sale.order', 'create', [order_data])

This mirrors the idempotency key pattern recommended by BillingPlatform: every event carries a stable identifier, and the system checks a processed-keys log before acting Event Deduplication in Billing.

Part 2: Timestamp Normalization to IST

We added a preprocessing step that normalizes all timestamps to IST before passing them to Odoo. If the created_at field is null or empty, we fall back to the webhook delivery timestamp, which is always present.

from datetime import datetime
import pytz

def normalize_timestamp(shopify_timestamp, webhook_received_at):
    if shopify_timestamp:
        dt = datetime.fromisoformat(shopify_timestamp.replace('Z', '+00:00'))
        return dt.astimezone(pytz.timezone('Asia/Kolkata')).isoformat()
    else:
        # Fallback to webhook received timestamp
        return webhook_received_at.astimezone(pytz.timezone('Asia/Kolkata')).isoformat()

This aligns with the AI Accountant guidance: normalize timestamps to IST, and treat missing upstream timestamps as a signal to fall back to a reliable local source Wallet and UPI Feed Normalisation.

Pitfalls We Would Warn an Intern About

1. Never trust webhook delivery semantics: Assume at-least-once delivery. Always implement idempotency checks before any write operation.
2. Do not match on soft identifiers: Customer name, email, or order total are not reliable keys. Always use the source system's native ID.
3. Null timestamps are silent killers: They do not raise errors but cause downstream jobs to silently skip records. Always validate and normalize timestamps before persistence.
4. Timezone handling is non-negotiable: If your client operates in IST, store all timestamps in IST. Mixing UTC and IST in the same dataset leads to reconciliation nightmares.
5. Custom fields in Odoo are your friend: Use them to store source system IDs. This makes dedup checks exact and auditable.

What We Would Do Differently Next Time

1. Implement idempotency from day one: We would have built the idempotency check into the initial design rather than treating it as a post-failure fix.
2. Add a dead-letter queue for failed webhooks: Instead of silently dropping failed events, we would route them to a DLQ for manual inspection.
3. Log raw payloads for audit: We would store the raw Shopify webhook payload in a separate audit table, linked by the Shopify order ID, to enable forensic analysis.
4. Use a proper event store: Instead of relying on Lambda function state, we would use a durable event store (e.g., PostgreSQL with a processed_events table) to track idempotency keys.
5. Automate timestamp validation in CI/CD: We would add unit tests that simulate null and malformed timestamps to catch these issues before deployment.

The Deeper Pattern: Billing Invariants

The Equily team writes about the same class of problem in subscription billing: billing period start must be earlier than period end, and invoice generation should be idempotent under retries HRMS Billing & Invoice Controls. Our fix enforces the same invariant: one Shopify order ID maps to exactly one Odoo sales order, regardless of how many times the webhook fires.

The FlowVerify team documents a parallel failure in GST e-invoicing: invoice number collisions cause the IRP to reject the second occurrence with a duplicate-hash error GST e-invoicing API. The IRN is a SHA-256 hash of supplier GSTIN, financial year, document type, and invoice number. Duplicate hashes are rejected outright. The lesson is the same: stable identifiers prevent silent duplication.

Conclusion

The fix reduced duplicate orders to zero within 48 hours of deployment. The timestamp normalization eliminated null values in the Odoo database. Most importantly, the client regained confidence in their automated billing pipeline.

The lesson: in data integrations, assume failure, design for idempotency, and never trust upstream data to be clean. Webhooks will retry. Timestamps will be null. Customer names will collide. The only defense is a stable key and a preprocessing layer that normalizes everything before it touches your database.

We now ship every webhook handler with an idempotency check and a timestamp normalizer as boilerplate. It adds 10 lines of code and saves weeks of forensic debugging.

---

Pratap Singh is founder-engineer at Agentic Academy Labs in Sikar, India. The lab ships custom AI and full-stack products and runs a hands-on developer internship. File bugs at git@gitlab.com:agentic-labs/shopify-odoo-sync.git.