[Refactor] Outbox Architecture Overhaul: PostgreSQL Support, Mixin Models, and UUID v7

[Dioneya]

Problem

The current implementation of the cqrs.outbox.sqlalchemy module has several architectural limitations:

1. MySQL Vendor-lock: Reliance on the func.UUID_TO_BIN function makes the library incompatible with PostgreSQL or SQLite.
2. Migration Issues (Alembic): The OutboxModel is bound to the library's internal Base. This complicates migration generation in user applications since Alembic cannot detect this table in the target_metadata.
3. Index Performance: Using random UUID v4 as a Primary Key (or part of a composite index) causes B-Tree index fragmentation, degrading INSERT performance over time.
4. Hard Dependency: SQLAlchemy is currently a mandatory dependency, even if the user intends to use a different backend or strictly in-memory implementation.

Solution

This PR proposes a comprehensive refactoring of the Outbox module:

1. Transition to Mixin Architecture

• Replaced the hardcoded OutboxModel with OutboxModelMixin.
• Users now define the model themselves by inheriting from their own DeclarativeBase and the provided mixin.
• This resolves Alembic integration issues and allows customization of tablename.

2. Cross-Database Compatibility (PostgreSQL support)

• Removed dependency on the MySQL-specific UUID_TO_BIN function.
• Implemented a custom BinaryUUID type (TypeDecorator).
  • PostgreSQL: Uses the native UUID type.
  • MySQL/SQLite: Uses optimized BINARY(16) storage.

3. Performance Optimization (UUID v7)

• Added uuid6 dependency.
• Switched default event_id generation from uuid4 to uuid7.
• UUID v7 is time-ordered (monotonically increasing), ensuring sequential writes to the B-Tree index and eliminating fragmentation.

4. Optional SQLAlchemy Dependency

• Moved sqlalchemy to optional-dependencies (pip install python-cqrs[sqlalchemy]).
• Wrapped internal library imports in try-except blocks to prevent crashes when the driver is not installed.

New Usage Example

from sqlalchemy.orm import DeclarativeBase
from cqrs.outbox import OutboxModelMixin

# Define the model in the user application
class Base(DeclarativeBase):
    pass

class MyOutbox(Base, OutboxModelMixin):
    __tablename__ = "outbox_events"

# Initialize the repository
repo = SqlAlchemyOutboxedEventRepository(
    session=session,
    outbox_model=MyOutbox  # Pass the model class
)

Checklist

• Implement OutboxModelMixin.
• Implement BinaryUUID for PostgreSQL and MySQL compatibility.
• Switch ID generation to uuid7.
• Make sqlalchemy an optional dependency.
• Update tests (using testcontainers or mocks).
• Update documentation (README).

[Dioneya]

#33

[Dioneya]

Proposal: Extract Transactional Outbox Pattern into a Separate Library

I propose extracting the Transactional Outbox implementation (currently in src/cqrs/outbox) into a standalone library (e.g., python-transactional-outbox). Below are the key architectural and practical reasons for this decision.

1. Single Responsibility Principle (SRP) Violation

The core responsibility of python-cqrs is to provide abstractions for the Mediator pattern, Command/Query handling, and Event Dispatching.
The Transactional Outbox pattern is a persistence and reliability mechanism. While it complements CQRS, it is a distinct architectural concern. Coupling them tightly forces the core library to manage database schemas, ORM integrations (SQLAlchemy), and background workers, which dilutes its primary focus.

2. Hard Coupling to Infrastructure

Currently, the Outbox implementation is tightly coupled with:

• SQLAlchemy: Users who want to use python-cqrs with MongoDB, Tortoise ORM, or raw SQL drivers are forced to pull in SQLAlchemy dependencies or deal with dead code.
• Specific Database Dialects: The current implementation contains MySQL-specific optimizations (e.g., UUID_TO_BIN), which hinders portability to PostgreSQL or SQLite.

By extracting it, python-cqrs becomes infrastructure-agnostic, while the new library can focus on providing robust, database-specific implementations (adapters) for the Outbox pattern.

3. Independent Lifecycle and Scalability

The Outbox pattern often requires complex features that are irrelevant to the core CQRS mediator:

• Debezium Integration (CDC): Advanced Outbox implementations often rely on Change Data Capture rather than polling.
• Message Relay/Worker: The logic for polling, locking, retrying, and publishing messages to a broker (Kafka/RabbitMQ) is complex and deserves its own lifecycle, configuration, and scaling strategy.
• Observability: Metrics for outbox lag, publishing rates, and failure handling are specific to the Outbox domain.

Developing these features within python-cqrs bloats the library. A separate library allows for independent versioning and faster iteration on these specific reliability features.

4. Reusability

The Transactional Outbox pattern is useful even for applications that do not use CQRS. A developer might want to ensure atomic event publishing in a standard CRUD application.
Currently, they cannot use your Outbox implementation without installing the entire python-cqrs framework. Extracting it makes the solution reusable across the entire Python ecosystem.

5. Dependency Management

The current setup forces a heavy dependency tree (sqlalchemy, drivers, pydantic, etc.) on all users. Separating the libraries allows for a cleaner dependency graph:

• python-cqrs: Lightweight, focuses on dispatching logic.
• python-outbox: Focuses on persistence guarantees.
• python-outbox-sqlalchemy: Optional adapter.

Conclusion

Extracting the Outbox pattern aligns python-cqrs with the Hexagonal Architecture (Ports and Adapters) approach. It reduces the core library's complexity, increases flexibility for users to choose their persistence stack, and allows the Outbox implementation to evolve into a mature, standalone product-grade reliability tool independent of the CQRS framework.