18 lines
1.8 KiB
PL/PgSQL
18 lines
1.8 KiB
PL/PgSQL
CREATE TABLE mi_meta(policy text PRIMARY KEY,composition text NOT NULL,source_hash char(64) NOT NULL);
|
|
CREATE TABLE mi_calculations(
|
|
id bigserial PRIMARY KEY,tenant_id bigint NOT NULL,company_id bigint NOT NULL,unit_id bigint NOT NULL,
|
|
draft_id bigint NOT NULL REFERENCES a_billing_drafts(id),actor_id bigint NOT NULL REFERENCES users(id),
|
|
fingerprint char(64) NOT NULL,snapshot jsonb NOT NULL,reason text NOT NULL CHECK(length(reason)>=10),created_at timestamptz NOT NULL,
|
|
UNIQUE(tenant_id,company_id,unit_id,draft_id,fingerprint),
|
|
FOREIGN KEY(tenant_id,company_id,unit_id) REFERENCES units(tenant_id,company_id,id)
|
|
);
|
|
CREATE TABLE mi_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,input jsonb NOT NULL,result jsonb NOT NULL,created_at timestamptz NOT NULL,UNIQUE(tenant_id,company_id,unit_id,actor_id,command_key));
|
|
CREATE FUNCTION mi_append_only() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION 'MULTILINE_INVOICE_APPEND_ONLY';END $$;
|
|
CREATE TRIGGER mi_meta_immutable BEFORE UPDATE OR DELETE ON mi_meta FOR EACH ROW EXECUTE FUNCTION mi_append_only();
|
|
CREATE TRIGGER mi_calculations_immutable BEFORE UPDATE OR DELETE ON mi_calculations FOR EACH ROW EXECUTE FUNCTION mi_append_only();
|
|
CREATE TRIGGER mi_commands_immutable BEFORE UPDATE OR DELETE ON mi_commands FOR EACH ROW EXECUTE FUNCTION mi_append_only();
|
|
CREATE INDEX mi_calculations_scope ON mi_calculations(tenant_id,company_id,unit_id,draft_id,id DESC);
|
|
REVOKE ALL ON TABLE mi_meta,mi_calculations,mi_commands FROM PUBLIC;
|
|
REVOKE ALL ON SEQUENCE mi_calculations_id_seq,mi_commands_id_seq FROM PUBLIC;
|
|
REVOKE ALL ON FUNCTION mi_append_only() FROM PUBLIC;
|