generated from coulomb/repo-seed
79 lines
2.5 KiB
SQL
79 lines
2.5 KiB
SQL
-- USER-WP-0016 durable-store bootstrap for provider-backed Postgres adapters.
|
|
-- Provider repositories may apply this file directly or translate it into
|
|
-- their migration framework while preserving the table semantics.
|
|
|
|
CREATE TABLE IF NOT EXISTS user_engine_schema_versions (
|
|
version text PRIMARY KEY,
|
|
name text NOT NULL,
|
|
applied_at timestamptz NOT NULL DEFAULT now(),
|
|
checksum text
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_engine_records (
|
|
record_type text NOT NULL,
|
|
record_key text NOT NULL,
|
|
tenant text,
|
|
user_id text,
|
|
application_id text,
|
|
scope_type text,
|
|
scope_id text,
|
|
payload jsonb NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (record_type, record_key)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS user_engine_records_tenant_idx
|
|
ON user_engine_records (tenant, record_type);
|
|
|
|
CREATE INDEX IF NOT EXISTS user_engine_records_user_idx
|
|
ON user_engine_records (user_id, record_type);
|
|
|
|
CREATE INDEX IF NOT EXISTS user_engine_records_application_idx
|
|
ON user_engine_records (application_id, record_type);
|
|
|
|
CREATE INDEX IF NOT EXISTS user_engine_records_scope_idx
|
|
ON user_engine_records (scope_type, scope_id, record_type);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_engine_audit_records (
|
|
audit_id text PRIMARY KEY,
|
|
tenant text NOT NULL,
|
|
actor_issuer text NOT NULL,
|
|
actor_subject text NOT NULL,
|
|
action text NOT NULL,
|
|
subject text NOT NULL,
|
|
correlation_id text NOT NULL,
|
|
summary text,
|
|
payload jsonb NOT NULL,
|
|
recorded_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS user_engine_audit_records_tenant_idx
|
|
ON user_engine_audit_records (tenant, recorded_at);
|
|
|
|
CREATE INDEX IF NOT EXISTS user_engine_audit_records_subject_idx
|
|
ON user_engine_audit_records (subject, recorded_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_engine_outbox_events (
|
|
event_id text PRIMARY KEY,
|
|
tenant text NOT NULL,
|
|
event_type text NOT NULL,
|
|
aggregate_id text NOT NULL,
|
|
correlation_id text NOT NULL,
|
|
payload jsonb NOT NULL,
|
|
occurred_at timestamptz NOT NULL DEFAULT now(),
|
|
claimed_at timestamptz,
|
|
claimed_by text,
|
|
delivered_at timestamptz,
|
|
failed_at timestamptz,
|
|
failure_reason text
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS user_engine_outbox_events_pending_idx
|
|
ON user_engine_outbox_events (occurred_at)
|
|
WHERE claimed_at IS NULL AND delivered_at IS NULL;
|
|
|
|
INSERT INTO user_engine_schema_versions (version, name)
|
|
VALUES ('0001_initial', 'initial durable store')
|
|
ON CONFLICT (version) DO NOTHING;
|