85 lines
3.9 KiB
SQL
85 lines
3.9 KiB
SQL
|
CREATE TABLE IF NOT EXISTS ops.compliance_dictionaries (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
code VARCHAR(64) NOT NULL,
|
||
|
|
name VARCHAR(64) NOT NULL,
|
||
|
|
platform_scope VARCHAR(16) NOT NULL DEFAULT 'all',
|
||
|
|
applicable_platforms TEXT[],
|
||
|
|
default_level VARCHAR(16) NOT NULL DEFAULT 'block',
|
||
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
||
|
|
version INT NOT NULL DEFAULT 1,
|
||
|
|
description TEXT,
|
||
|
|
created_by BIGINT,
|
||
|
|
updated_by BIGINT,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
CONSTRAINT uk_compliance_dictionaries_code UNIQUE (code),
|
||
|
|
CONSTRAINT ck_compliance_dictionary_platform_scope CHECK (platform_scope IN ('all', 'specific')),
|
||
|
|
CONSTRAINT ck_compliance_dictionary_platforms CHECK (
|
||
|
|
(platform_scope = 'all' AND (applicable_platforms IS NULL OR cardinality(applicable_platforms) = 0))
|
||
|
|
OR
|
||
|
|
(platform_scope = 'specific' AND cardinality(applicable_platforms) >= 1)
|
||
|
|
),
|
||
|
|
CONSTRAINT ck_compliance_dictionary_default_level CHECK (default_level IN ('block', 'high', 'medium', 'info'))
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_compliance_dictionaries_enabled_version
|
||
|
|
ON ops.compliance_dictionaries (enabled, version, id);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS ops.compliance_dictionary_terms (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
dictionary_id BIGINT NOT NULL REFERENCES ops.compliance_dictionaries(id) ON DELETE CASCADE,
|
||
|
|
match_type VARCHAR(24) NOT NULL DEFAULT 'exact',
|
||
|
|
pattern TEXT NOT NULL,
|
||
|
|
level_override VARCHAR(16),
|
||
|
|
hint TEXT,
|
||
|
|
reference_law TEXT,
|
||
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
CONSTRAINT ck_compliance_term_match_type CHECK (match_type IN ('exact', 'regex')),
|
||
|
|
CONSTRAINT ck_compliance_term_level CHECK (level_override IS NULL OR level_override IN ('block', 'high', 'medium', 'info')),
|
||
|
|
CONSTRAINT ck_compliance_term_pattern CHECK (char_length(trim(pattern)) > 0)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_compliance_terms_dictionary_enabled
|
||
|
|
ON ops.compliance_dictionary_terms (dictionary_id, enabled, id);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS ops.compliance_dictionary_versions (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
dictionary_id BIGINT NOT NULL REFERENCES ops.compliance_dictionaries(id) ON DELETE CASCADE,
|
||
|
|
version INT NOT NULL,
|
||
|
|
snapshot JSONB NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
published_by BIGINT,
|
||
|
|
published_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
CONSTRAINT uk_compliance_dictionary_versions UNIQUE (dictionary_id, version)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_compliance_dictionary_versions_latest
|
||
|
|
ON ops.compliance_dictionary_versions (dictionary_id, version DESC);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS ops.compliance_policies (
|
||
|
|
id BIGINT PRIMARY KEY DEFAULT 1,
|
||
|
|
master_enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
||
|
|
enforcement_mode VARCHAR(24) NOT NULL DEFAULT 'mandatory',
|
||
|
|
enabled_dictionaries JSONB NOT NULL DEFAULT '[]'::jsonb,
|
||
|
|
llm_judge_enabled BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
llm_categories JSONB NOT NULL DEFAULT '[]'::jsonb,
|
||
|
|
revision BIGINT NOT NULL DEFAULT 1,
|
||
|
|
updated_by BIGINT,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
CONSTRAINT ck_compliance_policy_singleton CHECK (id = 1),
|
||
|
|
CONSTRAINT ck_compliance_policy_mode CHECK (enforcement_mode IN ('mandatory', 'advisory', 'disabled'))
|
||
|
|
);
|
||
|
|
|
||
|
|
INSERT INTO ops.compliance_policies (
|
||
|
|
id,
|
||
|
|
master_enabled,
|
||
|
|
enforcement_mode,
|
||
|
|
enabled_dictionaries,
|
||
|
|
llm_judge_enabled,
|
||
|
|
llm_categories
|
||
|
|
)
|
||
|
|
VALUES (1, TRUE, 'mandatory', '[]'::jsonb, FALSE, '[]'::jsonb)
|
||
|
|
ON CONFLICT DO NOTHING;
|