apps/backend/src/infrastructure/database/migrations/0005_outbox_and_audit_foundation.sqlSQL
-- Sprint B completion — durable outbox and audit foundation.
create table if not exists outbox_events (
id uuid primary key default gen_random_uuid(),
organization_id uuid references organizations(id) on delete restrict,
aggregate_type text not null,
aggregate_id uuid,
event_type text not null,
payload jsonb not null,
occurred_at timestamptz not null default now(),
available_at timestamptz not null default now(),
processed_at timestamptz,
attempt_count integer not null default 0,
last_error text
);
create index if not exists idx_outbox_unprocessed
on outbox_events(available_at, occurred_at)
where processed_at is null;
create table if not exists audit_events (
id uuid primary key default gen_random_uuid(),
organization_id uuid references organizations(id) on delete restrict,
actor_user_id uuid references user_accounts(id) on delete set null,
event_type text not null,
entity_type text not null,
entity_id uuid,
correlation_id text,
metadata jsonb not null default '{}'::jsonb,
occurred_at timestamptz not null default now()
);
create index if not exists idx_audit_org_time
on audit_events(organization_id, occurred_at desc);
create index if not exists idx_audit_entity
on audit_events(entity_type, entity_id, occurred_at desc);