Save an order and outbox event in one SQLAlchemy transaction

• Use one AsyncSession and transaction for both inserts.
• Flush the order before building the event when its generated ID is required.
• Build a small versioned payload, not an ORM graph.
• Generate one stable event ID before publisher retries.
• Assume delivery can happen more than once.

1. Open the request-owned transaction.
2. Add and flush the new order.
3. Build a JSON-serializable versioned event from committed intent.
4. Add a pending outbox row before leaving the transaction.
5. Let a separate bounded publisher claim, send, and mark rows.

import json
from datetime import datetime, timezone
from uuid import uuid4

async def place_order(session, command):
    async with session.begin():
        order = Order(customer_id=command.customer_id, status="pending")
        session.add(order)
        await session.flush()

        event_id = uuid4()
        payload = {
            "event_id": str(event_id),
            "order_id": order.id,
            "customer_id": command.customer_id,
        }
        session.add(OutboxMessage(
            event_id=event_id,
            aggregate_id=str(order.id),
            event_type="OrderPlaced",
            event_version=1,
            payload_json=json.dumps(payload, separators=(",", ":")),
            created_at=datetime.now(timezone.utc),
            status="pending",
        ))
    return order.id

The command performs two bounded inserts. Publisher scans need an index on pending status and ordering fields and remain proportional to claimed batch size.

• Serialization failure must occur before commit.
• A crash after publish but before marking completion causes duplicate delivery.
• Old pending payloads can outlive a deployment.
• Large payloads turn the outbox table into an accidental document store.

• Force outbox insertion to fail and assert the order rolls back.
• Assert one pending event contains the saved order ID.
• Retry publication and verify the event ID does not change.
• Run publisher and command in separate sessions.

• Publishing before commit can announce an order that rolls back.
• Publishing after commit without an outbox can lose the event.
• Generating a new ID on retry defeats consumer deduplication.
• Passing the request session to a background publisher violates ownership.