pub const OUTBOX_MIGRATION: &str = "-- Domain events (ADR-0018): written by puerto. The units of work insert into `outbox` in the same\n-- transaction as the entity; the worker relays it and records what it could not deliver.\n\nCREATE TABLE outbox (\n id UUID PRIMARY KEY,\n seq BIGSERIAL NOT NULL, -- relay order\n aggregate_type TEXT NOT NULL,\n aggregate_id UUID NOT NULL,\n event_type TEXT NOT NULL,\n schema_version INT NOT NULL,\n payload JSONB NOT NULL,\n headers JSONB NOT NULL DEFAULT \'{}\',\n occurred_at TIMESTAMPTZ NOT NULL,\n published_at TIMESTAMPTZ\n);\nCREATE INDEX outbox_unpublished ON outbox (seq) WHERE published_at IS NULL;\n\nCREATE TABLE event_deliveries ( -- db transport only\n event_id UUID NOT NULL REFERENCES outbox (id),\n consumer TEXT NOT NULL,\n status TEXT NOT NULL DEFAULT \'pending\', -- pending | done | dead\n attempts INT NOT NULL DEFAULT 0,\n next_attempt_at TIMESTAMPTZ NOT NULL DEFAULT now(),\n last_error TEXT,\n PRIMARY KEY (event_id, consumer)\n);\nCREATE INDEX event_deliveries_due ON event_deliveries (consumer, next_attempt_at) WHERE status = \'pending\';\n\nCREATE TABLE dead_letters ( -- every transport\n id UUID PRIMARY KEY,\n event_id UUID,\n consumer TEXT NOT NULL,\n event_type TEXT,\n attempts INT NOT NULL,\n reason TEXT NOT NULL,\n detail TEXT NOT NULL,\n payload TEXT NOT NULL, -- trimmed to 4000 chars\n recorded_at TIMESTAMPTZ NOT NULL DEFAULT now()\n);\n-- One dead letter per event and consumer, so a redelivered failure is not recorded twice. A\n-- message that was not an envelope has no event_id: NULLs never conflict.\nCREATE UNIQUE INDEX dead_letters_event_consumer ON dead_letters (event_id, consumer);\n";Expand description
The outbox, delivery and dead-letter tables (migrations/outbox.sql). Puerto’s CLI ships a
byte-for-byte copy of it as a project migration.