Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
DROP TABLE IF EXISTS dept_emp,
dept_manager,
title,
salary,
employee,
department,
audit CASCADE;
CREATE TABLE employee (
emp_no SERIAL NOT NULL,
birth_date DATE NOT NULL,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
gender TEXT NOT NULL CHECK (gender IN('M', 'F')) NOT NULL,
hire_date DATE NOT NULL,
PRIMARY KEY (emp_no)
);
CREATE INDEX idx_employee_hire_date ON employee (hire_date);
CREATE TABLE department (
dept_no TEXT NOT NULL,
dept_name TEXT NOT NULL,
PRIMARY KEY (dept_no),
UNIQUE (dept_name)
);
CREATE TABLE dept_manager (
emp_no INT NOT NULL,
dept_no TEXT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
FOREIGN KEY (emp_no) REFERENCES employee (emp_no) ON DELETE CASCADE,
FOREIGN KEY (dept_no) REFERENCES department (dept_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no, dept_no)
);
CREATE TABLE dept_emp (
emp_no INT NOT NULL,
dept_no TEXT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
FOREIGN KEY (emp_no) REFERENCES employee (emp_no) ON DELETE CASCADE,
FOREIGN KEY (dept_no) REFERENCES department (dept_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no, dept_no)
);
CREATE TABLE title (
emp_no INT NOT NULL,
title TEXT NOT NULL,
from_date DATE NOT NULL,
to_date DATE,
FOREIGN KEY (emp_no) REFERENCES employee (emp_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no, title, from_date)
);
CREATE TABLE salary (
emp_no INT NOT NULL,
amount INT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
FOREIGN KEY (emp_no) REFERENCES employee (emp_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no, from_date)
);
CREATE INDEX idx_salary_amount ON salary (amount);
CREATE TABLE audit (
id SERIAL PRIMARY KEY,
operation TEXT NOT NULL,
query TEXT,
user_name TEXT NOT NULL,
changed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_audit_operation ON audit (operation);
CREATE INDEX idx_audit_username ON audit (user_name);
CREATE INDEX idx_audit_changed_at ON audit (changed_at);
CREATE OR REPLACE FUNCTION log_dml_operations() RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO audit (operation, query, user_name)
VALUES ('INSERT', current_query(), current_user);
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO audit (operation, query, user_name)
VALUES ('UPDATE', current_query(), current_user);
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
INSERT INTO audit (operation, query, user_name)
VALUES ('DELETE', current_query(), current_user);
RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- only log update and delete, otherwise, it will cause too much change.
CREATE TRIGGER salary_log_trigger
AFTER UPDATE OR DELETE ON salary
FOR EACH ROW
EXECUTE FUNCTION log_dml_operations();
CREATE OR REPLACE VIEW dept_emp_latest_date AS
SELECT
emp_no,
MAX(
from_date) AS from_date,
MAX(
to_date) AS to_date
FROM
dept_emp
GROUP BY
emp_no;
-- shows only the current department for each employee
CREATE OR REPLACE VIEW current_dept_emp AS
SELECT
l.emp_no,
dept_no,
l.from_date,
l.to_date
FROM
dept_emp d
INNER JOIN dept_emp_latest_date l ON d.emp_no = l.emp_no
AND d.from_date = l.from_date
AND l.to_date = d.to_date;
-- for Prior Backup
CREATE SCHEMA bbdataarchive;