Codex
82e2c91f42
Publish documentation / publish (push) Successful in 1m27s
Add PostgreSQL-backed memory, editable evidence with source review and activation, and human-approved archive repairs across the harness, API, and UI. Include migrations, deployment support, regression coverage, and validation documentation. Refresh permissions from validated session roles so existing administrator logins can access newly deployed archive management features.
73 lines
3.0 KiB
SQL
73 lines
3.0 KiB
SQL
CREATE SCHEMA IF NOT EXISTS thoth_memory;
|
|
DO $$ BEGIN
|
|
IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'thoth_memory_runtime') THEN
|
|
CREATE ROLE thoth_memory_runtime NOLOGIN;
|
|
END IF;
|
|
IF EXISTS (SELECT FROM pg_roles WHERE rolname = 'thothii_catalog_runtime') THEN
|
|
GRANT thoth_memory_runtime TO thothii_catalog_runtime;
|
|
END IF;
|
|
END $$;
|
|
|
|
CREATE TABLE thoth_memory.cards (
|
|
workspace_id text NOT NULL,
|
|
id text NOT NULL,
|
|
family text NOT NULL,
|
|
subject text NOT NULL,
|
|
origin text NOT NULL CHECK (origin IN ('manual', 'workflow')),
|
|
data jsonb NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
revision text NOT NULL,
|
|
PRIMARY KEY (workspace_id, id)
|
|
);
|
|
CREATE INDEX ON thoth_memory.cards (workspace_id, updated_at, id);
|
|
CREATE INDEX ON thoth_memory.cards (workspace_id, family);
|
|
CREATE TABLE thoth_memory.links (
|
|
workspace_id text NOT NULL,
|
|
source_id text NOT NULL,
|
|
target_id text NOT NULL,
|
|
meaning text NOT NULL,
|
|
PRIMARY KEY (workspace_id, source_id, target_id),
|
|
CHECK (source_id <> target_id),
|
|
FOREIGN KEY (workspace_id, source_id) REFERENCES thoth_memory.cards ON DELETE CASCADE,
|
|
FOREIGN KEY (workspace_id, target_id) REFERENCES thoth_memory.cards ON DELETE CASCADE
|
|
);
|
|
CREATE TABLE thoth_memory.dependencies (
|
|
workspace_id text NOT NULL,
|
|
card_id text NOT NULL,
|
|
database_id text NOT NULL,
|
|
schema_name text NOT NULL,
|
|
table_name text NOT NULL,
|
|
column_name text NOT NULL,
|
|
PRIMARY KEY (workspace_id, card_id, database_id, schema_name, table_name, column_name),
|
|
FOREIGN KEY (workspace_id, card_id) REFERENCES thoth_memory.cards ON DELETE CASCADE
|
|
);
|
|
-- No FK to cards: deletion recovery and source receipts survive removal of the card.
|
|
CREATE TABLE thoth_memory.projections (
|
|
workspace_id text NOT NULL,
|
|
card_id text NOT NULL,
|
|
revision text NOT NULL,
|
|
action text NOT NULL CHECK (action IN ('upsert', 'delete')),
|
|
pending boolean NOT NULL DEFAULT true,
|
|
error text,
|
|
source_key text,
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (workspace_id, card_id),
|
|
UNIQUE (workspace_id, source_key)
|
|
);
|
|
DO $$ DECLARE t text; BEGIN
|
|
FOREACH t IN ARRAY ARRAY['cards', 'links', 'dependencies', 'projections'] LOOP
|
|
EXECUTE format('ALTER TABLE thoth_memory.%I ENABLE ROW LEVEL SECURITY', t);
|
|
EXECUTE format('ALTER TABLE thoth_memory.%I FORCE ROW LEVEL SECURITY', t);
|
|
EXECUTE format(
|
|
'CREATE POLICY workspace_isolation ON thoth_memory.%I USING '
|
|
'(workspace_id = current_setting(''thoth.memory_workspace'', true)) '
|
|
'WITH CHECK (workspace_id = current_setting(''thoth.memory_workspace'', true))', t);
|
|
EXECUTE format('GRANT SELECT, INSERT, UPDATE, DELETE ON thoth_memory.%I '
|
|
'TO thoth_memory_runtime', t);
|
|
END LOOP;
|
|
END $$;
|
|
GRANT USAGE ON SCHEMA thoth_memory TO thoth_memory_runtime;
|
|
GRANT SELECT ON thoth_memory.migrations TO thoth_memory_runtime;
|
|
REVOKE ALL ON SCHEMA thoth_memory FROM PUBLIC;
|