61 lines
2.2 KiB
SQL
61 lines
2.2 KiB
SQL
CREATE SCHEMA IF NOT EXISTS thoth_sessions;
|
|
REVOKE ALL ON SCHEMA thoth_sessions FROM PUBLIC;
|
|
|
|
CREATE TABLE IF NOT EXISTS thoth_sessions.principals (
|
|
id bigserial PRIMARY KEY,
|
|
issuer text NOT NULL,
|
|
subject text NOT NULL,
|
|
display_name text,
|
|
created_at timestamptz NOT NULL DEFAULT pg_catalog.now(),
|
|
updated_at timestamptz NOT NULL DEFAULT pg_catalog.now(),
|
|
UNIQUE (issuer, subject)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS thoth_sessions.principal_preferences (
|
|
principal_id bigint PRIMARY KEY REFERENCES thoth_sessions.principals(id) ON DELETE CASCADE,
|
|
preferences jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
updated_at timestamptz NOT NULL DEFAULT pg_catalog.now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS thoth_sessions.sessions (
|
|
id uuid PRIMARY KEY,
|
|
principal_id bigint NOT NULL REFERENCES thoth_sessions.principals(id),
|
|
manifest jsonb NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT pg_catalog.now(),
|
|
updated_at timestamptz NOT NULL DEFAULT pg_catalog.now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS sessions_principal_id_created_at_idx
|
|
ON thoth_sessions.sessions (principal_id, created_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS thoth_sessions.session_artifacts (
|
|
session_id uuid NOT NULL REFERENCES thoth_sessions.sessions(id) ON DELETE CASCADE,
|
|
artifact_key text NOT NULL,
|
|
content text NOT NULL,
|
|
updated_at timestamptz NOT NULL DEFAULT pg_catalog.now(),
|
|
PRIMARY KEY (session_id, artifact_key)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS thoth_sessions.review_decisions (
|
|
session_id uuid NOT NULL REFERENCES thoth_sessions.sessions(id) ON DELETE CASCADE,
|
|
seq integer NOT NULL CHECK (seq > 0),
|
|
ts timestamptz NOT NULL,
|
|
phase integer,
|
|
type text NOT NULL,
|
|
subject text NOT NULL,
|
|
detail text NOT NULL DEFAULT '',
|
|
rationale text NOT NULL DEFAULT '',
|
|
retracts integer,
|
|
PRIMARY KEY (session_id, seq)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS thoth_sessions.audit_log (
|
|
id bigserial PRIMARY KEY,
|
|
occurred_at timestamptz NOT NULL DEFAULT pg_catalog.now(),
|
|
action text NOT NULL,
|
|
session_id uuid NOT NULL,
|
|
actor_issuer text NOT NULL,
|
|
actor_subject text NOT NULL,
|
|
owner_issuer text NOT NULL,
|
|
owner_subject text NOT NULL
|
|
);
|