29 lines
2.2 KiB
PL/PgSQL
29 lines
2.2 KiB
PL/PgSQL
CREATE TABLE hp_meta(policy text PRIMARY KEY,composition text NOT NULL,source_hash char(64) NOT NULL);
|
|
CREATE TABLE hp_versions(
|
|
id bigserial PRIMARY KEY,tenant_id bigint NOT NULL,company_id bigint NOT NULL,unit_id bigint NOT NULL,
|
|
kind text NOT NULL CHECK(kind IN('contract','day','entitlement','leave','report')),record_key uuid NOT NULL,version bigint NOT NULL CHECK(version>0),previous_id bigint UNIQUE REFERENCES hp_versions(id),
|
|
action text NOT NULL CHECK(action IN('prepare','review')),state text NOT NULL CHECK(state IN('prepared','approved','refused')),
|
|
prepared_by bigint NOT NULL REFERENCES users(id),actor_id bigint NOT NULL REFERENCES users(id),
|
|
preparer_identity char(64) NOT NULL,data jsonb NOT NULL,source_basis char(64) NOT NULL,
|
|
impact jsonb NOT NULL,reason text NOT NULL CHECK(length(reason)>=10),created_at timestamptz NOT NULL,
|
|
UNIQUE(tenant_id,company_id,unit_id,record_key,version),
|
|
FOREIGN KEY(tenant_id,company_id,unit_id) REFERENCES units(tenant_id,company_id,id),
|
|
CHECK(jsonb_typeof(data)='object'),CHECK(jsonb_typeof(impact)='object'),
|
|
CHECK((action='prepare' AND state='prepared') OR (action='review' AND state IN('approved','refused'))),
|
|
CHECK(action<>'review' OR prepared_by<>actor_id)
|
|
);
|
|
CREATE TABLE hp_commands(
|
|
id bigserial PRIMARY KEY,tenant_id bigint NOT NULL,company_id bigint NOT NULL,unit_id bigint NOT NULL,
|
|
actor_id bigint NOT NULL REFERENCES users(id),command_key uuid NOT NULL,request_hash char(64) NOT NULL,
|
|
result jsonb NOT NULL,input jsonb NOT NULL,created_at timestamptz NOT NULL,
|
|
UNIQUE(tenant_id,company_id,unit_id,actor_id,command_key)
|
|
);
|
|
CREATE FUNCTION hp_append_only() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
BEGIN RAISE EXCEPTION 'SGF_HR_PERSONNEL_APPEND_ONLY'; END; $$;
|
|
CREATE TRIGGER hp_meta_immutable BEFORE UPDATE OR DELETE ON hp_meta FOR EACH ROW EXECUTE FUNCTION hp_append_only();
|
|
CREATE TRIGGER hp_versions_immutable BEFORE UPDATE OR DELETE ON hp_versions FOR EACH ROW EXECUTE FUNCTION hp_append_only();
|
|
CREATE TRIGGER hp_commands_immutable BEFORE UPDATE OR DELETE ON hp_commands FOR EACH ROW EXECUTE FUNCTION hp_append_only();
|
|
|
|
CREATE INDEX hp_versions_scope_latest ON hp_versions(tenant_id,company_id,unit_id,record_key,version DESC);
|
|
CREATE INDEX hp_versions_kind ON hp_versions(tenant_id,company_id,unit_id,kind,id DESC);
|