Duplicate invoice processing triggered by RPA robot via Integration Hub callbacks

BPM Mendix
, , , , , , , ,
1

Our RPA robot processes invoices and calls back to Mendix via Integration Hub REST endpoints to create invoice records. We’re seeing duplicate invoices being created when the robot retries the callback after network hiccups or timeout errors.

Current callback microflow:


CREATE Invoice
SET $Invoice/Number = $CallbackData/invoiceNumber
SET $Invoice/Amount = $CallbackData/amount
COMMIT $Invoice
RETURN 'Success'

The issue is if the robot doesn’t receive the success response due to network issues, it retries the callback, and we end up with duplicate invoice records. This causes major financial reconciliation problems. We need idempotency in our microflows but aren’t sure how to implement it properly. The invoice reference tracking from the robot should be our deduplication key, but we’re not checking for existing records before creating new ones. Any guidance on proper callback error handling patterns?

2

We store transaction IDs as attributes on the invoice entity with a unique constraint. But there’s another important aspect - your error handling. If the COMMIT fails due to a duplicate key violation, you need to catch that exception and still return a success response to the robot. Otherwise, the robot will keep retrying thinking the request failed, even though the invoice was actually created on a previous attempt.

3

Good points. The unique constraint makes sense for database-level protection. How do you handle the transaction ID approach? Do you store it in a separate entity or as an attribute on the invoice?

4

Sofia’s absolutely right about the exception handling. The robot needs to receive a 200 OK response even when it’s a duplicate, otherwise you’re just moving the problem around. Kumar, also consider logging all callback attempts with their transaction IDs for audit purposes. This helps troubleshoot when reconciliation issues occur.

5

Let me provide a comprehensive solution covering all three aspects: idempotency in microflows, callback error handling, and invoice reference tracking.

1. Idempotency in Microflows: Implement a check-first pattern that makes your callback endpoint idempotent:


RETRIEVE Invoice WHERE InvoiceNumber = $CallbackData/invoiceNumber
  AND TransactionID = $CallbackData/transactionID
IF $ExistingInvoice != empty THEN
  RETURN CreateSuccessResponse($ExistingInvoice)
ELSE
  // Create new invoice
END

The key is returning the exact same response structure whether you’re creating a new invoice or finding an existing one. The RPA robot cannot and should not distinguish between these scenarios.

2. Callback Error Handling: Wrap your invoice creation in proper exception handling that returns success even on duplicate key violations:


TRY
  CREATE Invoice
  SET $Invoice/Number = $CallbackData/invoiceNumber
  SET $Invoice/TransactionID = $CallbackData/transactionID
  SET $Invoice/Amount = $CallbackData/amount
  COMMIT $Invoice
  RETURN CreateSuccessResponse($Invoice)
CATCH
  IF $Error contains 'duplicate key' THEN
    RETRIEVE Invoice WHERE InvoiceNumber = $CallbackData/invoiceNumber
    RETURN CreateSuccessResponse($ExistingInvoice)
  ELSE
    RETURN CreateErrorResponse($Error)
  END
END

This ensures that duplicate key violations (from database unique constraints) are handled gracefully and return success to prevent robot retries.

3. Invoice Reference Tracking: Implement a composite key strategy using both invoice number and transaction ID:

  • Add TransactionID attribute to your Invoice entity (String, required)
  • Create unique constraint on combination of InvoiceNumber + TransactionID
  • Store all callback attempts in a separate CallbackLog entity for audit trail

CallbackLog entity should track:

  • TransactionID (from robot)
  • InvoiceNumber
  • Timestamp
  • ResponseStatus
  • RequestPayload (JSON string)

Complete Implementation Pattern:

Create a reusable microflow structure:

  1. Log incoming callback request immediately
  2. Validate required fields (invoice number, transaction ID, amount)
  3. Check for existing invoice by composite key
  4. If exists, return success with existing invoice ID
  5. If not exists, create within try-catch block
  6. On duplicate key error, retrieve and return existing
  7. On other errors, return proper error response with retry guidance
  8. Log final response status

Database Configuration: In your domain model, set validation rules:

  • InvoiceNumber: required, unique index
  • TransactionID: required, unique index on (InvoiceNumber, TransactionID) combination

Integration Hub Configuration: Set proper timeout values in your REST endpoint configuration (we use 30 seconds). Configure the endpoint to return consistent JSON structure:


{
  "status": "success",
  "invoiceId": "12345",
  "message": "Invoice processed"
}

Return this structure whether it’s a new invoice or duplicate. This makes your API truly idempotent and prevents the robot from generating duplicates through retries. The combination of application-level checks and database constraints provides defense in depth against duplicate invoice creation.

6

Anna’s approach works, but you should also consider using a unique constraint on the invoice number field at the database level. This provides a safety net if somehow two requests slip through simultaneously. Also, make sure you’re returning the same response structure whether it’s a new invoice or an existing one - the robot shouldn’t be able to tell the difference. We use a transaction ID from the robot as an additional deduplication key alongside invoice number.

7

Classic idempotency problem. You need to check if an invoice with that number already exists before creating a new one. Add a RETRIEVE action at the start of your microflow to search for existing invoices with the same number. If found, return success without creating a duplicate. This makes your endpoint idempotent.