98 lines
3.5 KiB
PL/PgSQL
98 lines
3.5 KiB
PL/PgSQL
-- 053_clan_event_store.sql
|
|
-- Minimal CLAN event store: consent_events (immutable), artifacts (versioned),
|
|
-- outbox (pending/done), state_transitions (immutable)
|
|
|
|
begin;
|
|
|
|
create schema if not exists clan;
|
|
|
|
-- 1) Immutable ConsentEvents
|
|
create table if not exists clan.clan_consent_events (
|
|
consent_event_id text primary key,
|
|
payload jsonb not null,
|
|
decision_type text not null,
|
|
target_artifact_ids text[] not null,
|
|
request_id text not null,
|
|
created_ts timestamptz not null default now(),
|
|
constraint chk_consent_decision_type check (decision_type in ('approve', 'reject', 'revoke'))
|
|
);
|
|
|
|
create index if not exists idx_clan_consent_events_created_ts
|
|
on clan.clan_consent_events(created_ts desc);
|
|
|
|
-- 2) Versioned Artifacts (CAS by version)
|
|
create table if not exists clan.clan_artifacts (
|
|
artifact_id text primary key,
|
|
artifact_type text not null,
|
|
status text not null,
|
|
visibility_level text not null,
|
|
payload jsonb not null default '{}'::jsonb,
|
|
provenance jsonb not null default '[]'::jsonb,
|
|
version bigint not null default 1,
|
|
created_ts timestamptz not null default now(),
|
|
updated_ts timestamptz not null default now(),
|
|
constraint chk_artifact_visibility check (visibility_level in ('public', 'interclan', 'incircle', 'soulsafe', 'sacred')),
|
|
constraint chk_artifact_status check (
|
|
status in (
|
|
'draft',
|
|
'waiting_for_consent',
|
|
'needs_confirmation',
|
|
'approved_for_execution',
|
|
'confirmed',
|
|
'rejected',
|
|
'revoked'
|
|
)
|
|
)
|
|
);
|
|
|
|
create index if not exists idx_clan_artifacts_status
|
|
on clan.clan_artifacts(status);
|
|
|
|
create index if not exists idx_clan_artifacts_updated_ts
|
|
on clan.clan_artifacts(updated_ts desc);
|
|
|
|
-- 3) Outbox apply_consent (idempotent by outbox_id)
|
|
create table if not exists clan.clan_outbox (
|
|
outbox_id text primary key, -- outbox_{consent_event_id}
|
|
event_type text not null, -- apply_consent
|
|
consent_event_id text not null references clan.clan_consent_events(consent_event_id) on delete restrict,
|
|
target_artifact_ids text[] not null,
|
|
request_id text not null,
|
|
status text not null default 'pending', -- pending|done
|
|
attempts int not null default 0,
|
|
last_error text,
|
|
created_ts timestamptz not null default now(),
|
|
updated_ts timestamptz not null default now(),
|
|
constraint chk_outbox_status check (status in ('pending', 'done')),
|
|
constraint chk_outbox_event_type check (event_type in ('apply_consent'))
|
|
);
|
|
|
|
create index if not exists idx_clan_outbox_pending
|
|
on clan.clan_outbox(status, created_ts);
|
|
|
|
-- 4) Immutable Transition Log
|
|
create table if not exists clan.clan_state_transitions (
|
|
transition_id text primary key,
|
|
ts timestamptz not null default now(),
|
|
artifact_id text not null references clan.clan_artifacts(artifact_id) on delete restrict,
|
|
artifact_type text not null,
|
|
from_status text not null,
|
|
to_status text not null,
|
|
op text not null,
|
|
consent_event_id text not null references clan.clan_consent_events(consent_event_id) on delete restrict,
|
|
decision_type text not null,
|
|
request_id text not null,
|
|
visibility_level text not null,
|
|
versions jsonb not null default '{}'::jsonb,
|
|
constraint chk_transition_decision_type check (decision_type in ('approve', 'reject', 'revoke')),
|
|
constraint chk_transition_visibility check (visibility_level in ('public', 'interclan', 'incircle', 'soulsafe', 'sacred'))
|
|
);
|
|
|
|
create index if not exists idx_clan_transitions_artifact
|
|
on clan.clan_state_transitions(artifact_id, ts desc);
|
|
|
|
create index if not exists idx_clan_transitions_consent
|
|
on clan.clan_state_transitions(consent_event_id, ts desc);
|
|
|
|
commit;
|