-- mailer (scenario-harness test brick): what it sent, and the capability claim tables it must carry.
CREATE TABLE IF NOT EXISTS mailer_outbox (
message_id VARCHAR NOT NULL,
to_addr VARCHAR NOT NULL,
subject VARCHAR NOT NULL,
sent_at TIMESTAMP,
PRIMARY KEY (message_id)
);
-- Capability claims (erp_platform.capabilities, rule 1). A claim is a PLAIN INSERT: a duplicate key fails
-- with 23505 and the caller re-reads the winner. generation 1 is the first claim, and a takeover after the
-- lease expired inserts generation n+1, so a takeover race has one winner too. Only terminal results
-- (done, or failed with retryable=false) are kept in `result`, and a non-terminal outcome deletes its claim.
CREATE TABLE IF NOT EXISTS mailer_capability_claims (
capability VARCHAR NOT NULL,
idempotency_key VARCHAR NOT NULL,
generation INTEGER NOT NULL,
input_hash VARCHAR NOT NULL, -- SHA-256 of the canonical validated input: same key, other input = 409
attempt_id VARCHAR NOT NULL,
claimed_at TIMESTAMP NOT NULL,
lease_until TIMESTAMP NOT NULL, -- UTC; an unfinished claim past this is re-attempted
status VARCHAR, -- NULL while in progress; done | failed once terminal
result VARCHAR, -- the terminal RESULT, as JSON
finished_at TIMESTAMP,
PRIMARY KEY (capability, idempotency_key, generation)
);
-- One row per call (rule 5) — dry runs, replays and in_progress answers included. Secret inputs redacted.
CREATE TABLE IF NOT EXISTS mailer_capability_log (
capability VARCHAR NOT NULL,
idempotency_key VARCHAR NOT NULL,
attempt_id VARCHAR NOT NULL,
event_at TIMESTAMP NOT NULL,
actor VARCHAR,
dry_run BOOLEAN NOT NULL,
input VARCHAR NOT NULL, -- JSON, secret fields replaced by "[redacted]"
status VARCHAR NOT NULL, -- a RESULT status, or "error" when the handler crashed
retryable BOOLEAN,
provider_ref VARCHAR,
message VARCHAR,
replay BOOLEAN NOT NULL,
tries INTEGER NOT NULL, -- vendor attempts inside this call (rule 7)
error VARCHAR, -- an exception's TYPE, never its message
request_id VARCHAR,
PRIMARY KEY (capability, idempotency_key, attempt_id)
);