89 lines
4.7 KiB
SQL
89 lines
4.7 KiB
SQL
CREATE TABLE IF NOT EXISTS staff_outbox (
|
|
id VARCHAR(26) PRIMARY KEY CHECK (id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
event_type TEXT NOT NULL,
|
|
event_version INTEGER NOT NULL CHECK (event_version > 0),
|
|
subject_type TEXT NOT NULL,
|
|
subject_id VARCHAR(26) NOT NULL CHECK (subject_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
actor_type TEXT NOT NULL CHECK (actor_type IN ('user', 'service')),
|
|
actor_id VARCHAR(26) NOT NULL CHECK (actor_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
payload JSONB NOT NULL,
|
|
correlation_id VARCHAR(26) NOT NULL CHECK (correlation_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
causation_id VARCHAR(26) CHECK (causation_id IS NULL OR causation_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
occurred_at TIMESTAMPTZ NOT NULL,
|
|
published_at TIMESTAMPTZ,
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
|
last_error TEXT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS staff_outbox_pending_idx ON staff_outbox (tenant_id, occurred_at) WHERE published_at IS NULL;
|
|
|
|
CREATE TABLE IF NOT EXISTS staff_inbox (
|
|
event_id VARCHAR(26) NOT NULL CHECK (event_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
event_type TEXT NOT NULL,
|
|
event_version INTEGER NOT NULL,
|
|
correlation_id VARCHAR(26) NOT NULL CHECK (correlation_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
causation_id VARCHAR(26) CHECK (causation_id IS NULL OR causation_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
received_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
processed_at TIMESTAMPTZ,
|
|
PRIMARY KEY (tenant_id, event_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS staff_idempotency_keys (
|
|
tenant_id VARCHAR(26) NOT NULL,
|
|
key TEXT NOT NULL,
|
|
request_hash TEXT NOT NULL,
|
|
response_status INTEGER,
|
|
response_body JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
PRIMARY KEY (tenant_id, key)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS staff_audit_log (
|
|
id VARCHAR(26) PRIMARY KEY CHECK (id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
actor_id VARCHAR(26) NOT NULL CHECK (actor_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
action TEXT NOT NULL,
|
|
entity_type TEXT NOT NULL,
|
|
entity_id VARCHAR(26) CHECK (entity_id IS NULL OR entity_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
old_values JSONB,
|
|
new_values JSONB,
|
|
correlation_id VARCHAR(26) NOT NULL CHECK (correlation_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS staff_audit_tenant_time_idx ON staff_audit_log (tenant_id, created_at DESC);
|
|
|
|
ALTER TABLE staff_outbox ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE staff_outbox FORCE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS staff_outbox_tenant_isolation ON staff_outbox;
|
|
CREATE POLICY staff_outbox_tenant_isolation ON staff_outbox USING (tenant_id = current_setting('app.tenant_id', true)) WITH CHECK (tenant_id = current_setting('app.tenant_id', true));
|
|
ALTER TABLE staff_inbox ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE staff_inbox FORCE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS staff_inbox_tenant_isolation ON staff_inbox;
|
|
CREATE POLICY staff_inbox_tenant_isolation ON staff_inbox USING (tenant_id = current_setting('app.tenant_id', true)) WITH CHECK (tenant_id = current_setting('app.tenant_id', true));
|
|
ALTER TABLE staff_idempotency_keys ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE staff_idempotency_keys FORCE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS staff_idempotency_tenant_isolation ON staff_idempotency_keys;
|
|
CREATE POLICY staff_idempotency_tenant_isolation ON staff_idempotency_keys USING (tenant_id = current_setting('app.tenant_id', true)) WITH CHECK (tenant_id = current_setting('app.tenant_id', true));
|
|
ALTER TABLE staff_audit_log ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE staff_audit_log FORCE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS staff_audit_tenant_isolation ON staff_audit_log;
|
|
DROP POLICY IF EXISTS staff_audit_tenant_select ON staff_audit_log;
|
|
DROP POLICY IF EXISTS staff_audit_tenant_insert ON staff_audit_log;
|
|
CREATE POLICY staff_audit_tenant_select ON staff_audit_log FOR SELECT USING (tenant_id = current_setting('app.tenant_id', true));
|
|
CREATE POLICY staff_audit_tenant_insert ON staff_audit_log FOR INSERT WITH CHECK (
|
|
tenant_id = current_setting('app.tenant_id', true)
|
|
AND actor_id = current_setting('app.user_id', true)
|
|
);
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'krona_staff_app') THEN
|
|
GRANT USAGE ON SCHEMA public TO krona_staff_app;
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON staff_outbox, staff_inbox, staff_idempotency_keys TO krona_staff_app;
|
|
GRANT SELECT, INSERT ON staff_audit_log TO krona_staff_app;
|
|
REVOKE UPDATE, DELETE, TRUNCATE ON staff_audit_log FROM krona_staff_app;
|
|
END IF;
|
|
END
|
|
$$;
|