CREATE TABLE outbox (
    seq INTEGER PRIMARY KEY AUTOINCREMENT,
    event_type TEXT NOT NULL,
    partition_key TEXT NOT NULL,
    idempotency_key TEXT UNIQUE,
    payload TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE outbox_deliveries (
    seq INTEGER NOT NULL REFERENCES outbox(seq),
    consumer TEXT NOT NULL,
    partition_key TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    process_after TEXT,
    lease_until TEXT,
    completed_at TEXT,
    attempts INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (seq, consumer)
);

CREATE INDEX idx_deliveries_pending
ON outbox_deliveries (consumer, seq)
WHERE status = 'pending';

CREATE INDEX idx_deliveries_inflight
ON outbox_deliveries (consumer, partition_key)
WHERE status = 'processing';