Outbox pattern reliable publish in Python with SQLite

import sqlite3
from contextlib import contextmanager
from datetime import datetime, timezone


class Outbox:
    def __init__(self, db_path=":memory:"):
        self.conn = sqlite3.connect(db_path)
        self.conn.execute("""
            CREATE TABLE IF NOT EXISTS outbox (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                payload TEXT NOT NULL,
                status TEXT NOT NULL DEFAULT 'pending',
                created_at TEXT NOT NULL
            )
        """)
        self.conn.commit()

    @contextmanager
    def transaction(self):
        try:
            yield
            self.conn.commit()
        except Exception:
            self.conn.rollback()
            raise

    def enqueue(self, payload: str) -> int:
        with self.transaction():
            cur = self.conn.execute(
                "INSERT INTO outbox (payload, created_at) VALUES (?, ?)",
                (payload, datetime.now(timezone.utc).isoformat()),
            )
            return cur.lastrowid

    def publish_pending(self) -> list[tuple[int, str]]:
        with self.transaction():
            rows = self.conn.execute(
                "SELECT id, payload FROM outbox WHERE status = 'pending'"
            ).fetchall()
            self.conn.execute(
                "UPDATE outbox SET status = 'published' WHERE status = 'pending'"
            )
            return [(row_id, payload) for row_id, payload in rows]


if __name__ == "__main__":
    outbox = Outbox()
    outbox.enqueue('{"type": "order.created", "id": 1}')
    outbox.enqueue('{"type": "order.created", "id": 2}')
    published = outbox.publish_pending()
    print("Published messages:", published)
    print("Remaining pending:", outbox.conn.execute(
        "SELECT COUNT(*) FROM outbox WHERE status = 'pending'"
    ).fetchone()[0])