40 lines
1.7 KiB
SQL
40 lines
1.7 KiB
SQL
-- 0pt durable AUD0 evidence outbox (§70)
|
|
-- AUD0 remains canonical evidence; this is only a durable producer queue.
|
|
|
|
CREATE TABLE IF NOT EXISTS opt_aud0_evidence_outbox (
|
|
outbox_id text PRIMARY KEY,
|
|
tenant_id text NOT NULL,
|
|
app_id text NOT NULL DEFAULT '0pt',
|
|
evidence_kind text NOT NULL CHECK (evidence_kind IN ('event', 'openlineage')),
|
|
idempotency_key text NOT NULL,
|
|
payload jsonb NOT NULL,
|
|
status text NOT NULL DEFAULT 'pending'
|
|
CHECK (status IN ('pending', 'delivered', 'dead')),
|
|
attempts integer NOT NULL DEFAULT 0 CHECK (attempts >= 0),
|
|
next_attempt_at timestamptz NOT NULL DEFAULT now(),
|
|
leased_by text,
|
|
leased_until timestamptz,
|
|
last_error text,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
delivered_at timestamptz,
|
|
dead_at timestamptz,
|
|
CONSTRAINT opt_aud0_evidence_outbox_idempotency
|
|
UNIQUE (tenant_id, evidence_kind, idempotency_key),
|
|
CONSTRAINT opt_aud0_evidence_outbox_delivery_consistency CHECK (
|
|
(status = 'delivered' AND delivered_at IS NOT NULL AND dead_at IS NULL)
|
|
OR (status = 'dead' AND dead_at IS NOT NULL AND delivered_at IS NULL)
|
|
OR (status = 'pending' AND delivered_at IS NULL AND dead_at IS NULL)
|
|
)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS opt_aud0_evidence_outbox_pending_idx
|
|
ON opt_aud0_evidence_outbox (next_attempt_at, created_at)
|
|
WHERE status = 'pending';
|
|
|
|
CREATE INDEX IF NOT EXISTS opt_aud0_evidence_outbox_tenant_idx
|
|
ON opt_aud0_evidence_outbox (tenant_id, created_at DESC);
|
|
|
|
COMMENT ON TABLE opt_aud0_evidence_outbox IS
|
|
'Durable local delivery queue for 0pt -> AUD0 evidence. Not canonical evidence.';
|