open-cluster-management.io/governance-policy-propagator@v0.13.0/controllers/complianceeventsapi/migrations/000001_compliance_history_initial_tables.up.sql (about)

     1  BEGIN;
     2  
     3  CREATE TABLE IF NOT EXISTS clusters(
     4     id serial PRIMARY KEY,
     5     name TEXT NOT NULL,
     6     cluster_id TEXT UNIQUE NOT NULL,
     7     UNIQUE (name, cluster_id)
     8  );
     9  
    10  CREATE INDEX IF NOT EXISTS idx_clusters_name ON clusters (name);
    11  
    12  CREATE TABLE IF NOT EXISTS parent_policies(
    13     id serial PRIMARY KEY,
    14     name TEXT NOT NULL,
    15     namespace TEXT NOT NULL,
    16     categories TEXT [],
    17     controls TEXT [],
    18     standards TEXT [],
    19     UNIQUE (name, namespace, categories, controls, standards)
    20  );
    21  
    22  -- This is required until we only support Postgres 15+ to utilize NULLS NOT DISTINCT.
    23  -- Partial indexes with 1 nullable unique field provided (e.g. A, B, C)
    24  CREATE UNIQUE INDEX IF NOT EXISTS parent_policies_null1 ON parent_policies (name, namespace, controls, standards) WHERE categories IS NULL;
    25  CREATE UNIQUE INDEX IF NOT EXISTS parent_policies_null2 ON parent_policies (name, namespace, categories, standards) WHERE controls IS NULL;
    26  CREATE UNIQUE INDEX IF NOT EXISTS parent_policies_null3 ON parent_policies (name, namespace, categories, controls) WHERE standards IS NULL;
    27  
    28  -- Partial indexes with 2 nullable unique field provided (e.g. AB AC BC)
    29  CREATE UNIQUE INDEX IF NOT EXISTS parent_policies_null4 ON parent_policies (name, namespace, standards) WHERE categories IS NULL AND controls IS NULL;
    30  CREATE UNIQUE INDEX IF NOT EXISTS parent_policies_null5 ON parent_policies (name, namespace, controls) WHERE categories IS NULL AND standards IS NULL;
    31  CREATE UNIQUE INDEX IF NOT EXISTS parent_policies_null6 ON parent_policies (name, namespace, categories) WHERE controls IS NULL AND standards IS NULL;
    32  
    33  -- Partial index with no nullable unique fields provided (e.g. ABC)
    34  CREATE UNIQUE INDEX IF NOT EXISTS parent_policies_null7 ON parent_policies (name, namespace) WHERE categories IS NULL AND controls IS NULL AND standards IS NULL;
    35  
    36  CREATE TABLE IF NOT EXISTS policies(
    37     id serial PRIMARY KEY,
    38     kind TEXT NOT NULL,
    39     api_group TEXT NOT NULL,
    40     name TEXT NOT NULL,
    41     namespace TEXT,
    42     spec JSONB NOT NULL,
    43     severity TEXT,
    44     UNIQUE (kind, api_group, name, namespace, spec, severity)
    45  );
    46  
    47  -- This is required until we only support Postgres 15+ to utilize NULLS NOT DISTINCT.
    48  -- Partial indexes with 1 nullable unique field provided (e.g. A, B)
    49  CREATE UNIQUE INDEX IF NOT EXISTS policies_null1 ON policies (kind, api_group, name, spec, severity) WHERE namespace IS NULL;
    50  CREATE UNIQUE INDEX IF NOT EXISTS policies_null2 ON policies (kind, api_group, name, namespace, spec) WHERE severity IS NULL;
    51  
    52  -- Partial index with no nullable unique fields provided (e.g. AB)
    53  CREATE UNIQUE INDEX IF NOT EXISTS policies_null3 ON policies (kind, api_group, name, spec) WHERE namespace IS NULL AND severity IS NULL;
    54  
    55  CREATE INDEX IF NOT EXISTS idx_policies_spec ON policies (spec);
    56  
    57  CREATE TABLE IF NOT EXISTS compliance_events(
    58     id serial PRIMARY KEY,
    59     cluster_id INT NOT NULL,
    60     policy_id INT NOT NULL,
    61     parent_policy_id INT,
    62     compliance TEXT NOT NULL,
    63     message TEXT NOT NULL,
    64     timestamp TIMESTAMP NOT NULL,
    65     metadata JSONB,
    66     reported_by TEXT,
    67     CONSTRAINT fk_policy_id
    68        FOREIGN KEY(policy_id) 
    69  	  REFERENCES policies(id),
    70     CONSTRAINT fk_parent_policy_id
    71        FOREIGN KEY(parent_policy_id)
    72  	  REFERENCES parent_policies(id),
    73     CONSTRAINT fk_cluster_id
    74        FOREIGN KEY(cluster_id) 
    75  	  REFERENCES clusters(id),
    76     UNIQUE (cluster_id, policy_id, parent_policy_id, compliance, message, timestamp)
    77  );
    78  
    79  -- This is required until we only support Postgres 15+ to utilize NULLS NOT DISTINCT.
    80  -- Partial indexes with 1 nullable unique field provided (e.g. A, B)
    81  CREATE UNIQUE INDEX IF NOT EXISTS compliance_events_null1 ON compliance_events (cluster_id, policy_id, compliance, message, timestamp) WHERE parent_policy_id IS NULL;
    82  
    83  CREATE INDEX IF NOT EXISTS idx_compliance_events_compliance ON compliance_events (compliance);
    84  CREATE INDEX IF NOT EXISTS idx_compliance_events_timestamp ON compliance_events (timestamp);
    85  CREATE INDEX IF NOT EXISTS idx_compliance_events_reported_by ON compliance_events (reported_by);
    86  
    87  COMMIT;