Sending leads to Odoo without losing one: a transactional outbox in ASP.NET Core

Two writes, and one of them will fail

Saving the lead and then calling Odoo looks fine until the call fails after the save: the lead sits in your database and nowhere in sales. Calling Odoo first fails the other way, when the save breaks after the CRM already has the record. Retrying inside the request only makes the page slow while the CRM is down.

The outbox moves the second write out of the request. The business row and a message describing it commit in the same transaction, so either both exist or neither does. Delivering the message becomes a separate job that can take as long as the CRM needs.

// The enquiry and its outbox row commit together, or not at all.
db.Inquiries.Add(inquiry);
db.OutboxMessages.Add(new OutboxMessage
{
    Type = "InquiryCreated",
    AggregateId = inquiry.Id,
    PayloadJson = JsonSerializer.Serialize(InquiryPayload.From(inquiry)),
    OccurredUtc = now,
    Status = OutboxStatus.Pending,
    NextAttemptUtc = now,
});
await db.SaveChangesAsync(ct);

What a message carries

Keep the row boring and complete. The dispatcher should never have to guess what a message is, whose it is, or why it last failed.

• Type. What happened: an enquiry created, a supplier approved, an email to send.
• Record and payload. The id of the record it is about, and a JSON snapshot of what the receiver needs.
• Status. Pending, Processing, Processed or DeadLettered. Nothing in between.
• Attempts. How many tries so far, and when the next one is due.
• Lease. Which worker holds the message, and until when.
• Last error. A code and a short reason, never the payload or a stack trace full of personal data.

Claim each message with a lease

A Hangfire recurring job runs every 30 seconds and works through due messages, oldest first, for at most 20 seconds or 500 messages. It claims one row at a time with a guarded update: only a due pending row, or a processing row whose lease has run out, can move to processing, with a five-minute lease and the worker’s id.

If the update touched no row, another worker got there first and this one moves on. Marking a message done or failed only works while the worker still holds the lease, so a worker that stalled past its lease cannot overwrite the one that took over. A crashed worker’s message comes back by itself when the lease expires.

var claimed = await db.OutboxMessages
    .Where(m => m.Id == id &&
        ((m.Status == OutboxStatus.Pending && m.NextAttemptUtc <= now) ||
         (m.Status == OutboxStatus.Processing && m.LeaseExpiresUtc < now)))
    .ExecuteUpdateAsync(s => s
        .SetProperty(m => m.Status, OutboxStatus.Processing)
        .SetProperty(m => m.LeaseExpiresUtc, now.AddMinutes(5))
        .SetProperty(m => m.LockedBy, workerId), ct);

if (claimed == 0) continue; // someone else has it

Make the CRM write safe to repeat

A message can be delivered and the worker can still die before it records that. The next attempt must not create a second lead. The record’s own GUID goes into a custom field on the Odoo lead, and every delivery searches by it first: update if it exists, create if it does not. A delivery table with a unique key per provider, operation and record keeps one row per logical send.

Search then create is still two calls. If two messages for the same record ever run at the same time, both can miss and both can create. Process messages for one record in order, or put a unique constraint on that field in Odoo; the field alone does not make it atomic.

Retry what can heal, dead-letter what cannot

Every failure from the Odoo client is classified once, where it happens, so the dispatcher only has to read the answer.

• Add jitter to the delays, so a batch of failures does not retry on the same minute.
• Treat 408 and 429 as transient, and honour Retry-After. They are easy to file as permanent by accident.
• Let a manual retry reset the attempt count, or a dead letter gets one try and dies again.

Two Odoo APIs behind one interface

Odoo 14 to 18 speak JSON-RPC with a session; Odoo 19 adds a JSON-2 API with a bearer key. One interface hides both, and a factory picks the client at startup from a config override or a version probe, falling back to JSON-RPC. When the integration is switched off, a no-op client marks messages as not applicable instead of failing them.

Make the queue visible

• Metrics for the dead-letter count and for the age of the oldest pending message, with an alert on each. Age catches a stuck dispatcher that a count never will.
• Logs carry ids and error codes, not names and phone numbers.
• Staff can retry one lead’s sync from the leads inbox. The retry endpoint for any message needs MFA and is audited.

Test it against something that fails

The integration tests run the dispatcher against a real SQL Server 2022 container and a fake Odoo that can be told to fail. If the container will not start, the suite fails instead of skipping.

• A delivered message ends processed, with exactly one lead in Odoo; running the dispatcher again changes nothing.
• A 500 leaves the message pending with a retry scheduled; a 401 dead-letters it.
• An expired lease is reclaimed and a live one is left alone; an email is not sent twice after a crash.
• Four dispatchers draining twenty messages send each one exactly once.
• A k6 run with Odoo down fails unless 99.5% of form submissions succeed and p95 stays under a second.

Before you trust it

• Does the business row commit with its outbox row, in one transaction?
• Can two workers ever send the same message, and is there a test with several?
• Does a crashed worker’s message come back when its lease runs out?
• Is every external write keyed by your own id and safe to repeat?
• Which errors retry, which dead-letter, and who hears about a dead letter?
• Can anyone see how old the oldest pending message is?
• The payload holds personal data. When is it purged? Decide before launch, not after.