xinmi/migrations/versioned/000196_workbench_access.up.sql

CREATE TABLE IF NOT EXISTS workbench_access_roles (
 id varchar(36) PRIMARY KEY, tenant_id bigint NOT NULL, name varchar(100) NOT NULL,
 description text NOT NULL DEFAULT '', permissions jsonb NOT NULL DEFAULT '[]',
 version integer NOT NULL DEFAULT 1, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(),
 UNIQUE (tenant_id, name)
);
CREATE TABLE IF NOT EXISTS workbench_access_assignments (
 tenant_id bigint NOT NULL, user_id varchar(36) NOT NULL, role_id varchar(36) NOT NULL REFERENCES workbench_access_roles(id),
 PRIMARY KEY (tenant_id, user_id)
);
CREATE TABLE IF NOT EXISTS workbench_access_publications (
 tenant_id bigint NOT NULL, kind varchar(32) NOT NULL, resource_id varchar(64) NOT NULL,
 global boolean NOT NULL DEFAULT false, updated_at timestamptz NOT NULL DEFAULT now(),
 PRIMARY KEY (tenant_id, kind, resource_id)
);
CREATE TABLE IF NOT EXISTS workbench_access_grants (
 tenant_id bigint NOT NULL, kind varchar(32) NOT NULL, resource_id varchar(64) NOT NULL, user_id varchar(36) NOT NULL,
 updated_at timestamptz NOT NULL DEFAULT now(), PRIMARY KEY (tenant_id, kind, resource_id, user_id)
);
CREATE INDEX IF NOT EXISTS workbench_access_grants_user ON workbench_access_grants(tenant_id,user_id);