materialized_view.sql

-- First, create a recursive CTE function to handle role inheritance
CREATE OR REPLACE FUNCTION get_inherited_roles(role_uuid UUID)
RETURNS TABLE (role_id UUID) AS $$
WITH RECURSIVE role_hierarchy AS (
  -- Base case: the role itself
  SELECT id, parent_role
  FROM role
  WHERE id = role_uuid AND active = TRUE
  
  UNION
  
  -- Recursive case: parent roles
  SELECT r.id, r.parent_role
  FROM role r
  INNER JOIN role_hierarchy rh ON r.id = rh.parent_role
  WHERE r.active = TRUE
)
SELECT role_hierarchy.id FROM role_hierarchy;
$$ LANGUAGE sql STABLE;

-- Create the materialized view
CREATE MATERIALIZED VIEW user_permissions_cache AS
WITH user_roles AS (
  -- Get all active roles for each user, including inherited roles
  SELECT DISTINCT 
    u.id AS user_id,
    u.username,
    r.role_id
  FROM public."user" u
  INNER JOIN has_role hr ON u.id = hr.user_id AND hr.active = TRUE
  CROSS JOIN LATERAL get_inherited_roles(hr.role_id) r
  WHERE NOT u.disabled
),
user_abilities AS (
  -- Combine user roles with their ability rules
  SELECT 
    ur.user_id,
    ur.username,
    ar.doctype,
    ar.action,
    ar.subject,
    ar.conditions,
    ar.inverted,
    array_agg(DISTINCT ur.role_id) AS granting_roles
  FROM user_roles ur
  INNER JOIN ability_rule ar ON ur.role_id = ar.role_id
  WHERE ar.active = TRUE
  GROUP BY 
    ur.user_id,
    ur.username,
    ar.doctype,
    ar.action,
    ar.subject,
    ar.conditions,
    ar.inverted
)
SELECT 
  user_id,
  username,
  jsonb_object_agg(
    doctype,
    jsonb_build_object(
      'actions', jsonb_object_agg(
        action,
        jsonb_build_object(
          'subjects', array_agg(DISTINCT subject),
          'conditions', array_agg(DISTINCT conditions) FILTER (WHERE conditions IS NOT NULL),
          'inverted', bool_or(inverted),
          'granting_roles', array_agg(DISTINCT unnest(granting_roles))
        )
      )
    )
  ) AS permissions
FROM user_abilities
GROUP BY user_id, username;

-- Create indexes on the materialized view
CREATE UNIQUE INDEX idx_user_permissions_cache_user_id 
ON user_permissions_cache (user_id);

CREATE INDEX idx_user_permissions_cache_username 
ON user_permissions_cache (username);

-- Create a function to refresh the materialized view
CREATE OR REPLACE FUNCTION refresh_permissions_cache()
RETURNS TRIGGER AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY user_permissions_cache;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Create triggers to refresh the materialized view
CREATE TRIGGER refresh_permissions_cache_user
AFTER INSERT OR UPDATE OR DELETE ON public."user"
FOR EACH STATEMENT EXECUTE FUNCTION refresh_permissions_cache();

CREATE TRIGGER refresh_permissions_cache_role
AFTER INSERT OR UPDATE OR DELETE ON role
FOR EACH STATEMENT EXECUTE FUNCTION refresh_permissions_cache();

CREATE TRIGGER refresh_permissions_cache_ability
AFTER INSERT OR UPDATE OR DELETE ON ability_rule
FOR EACH STATEMENT EXECUTE FUNCTION refresh_permissions_cache();

CREATE TRIGGER refresh_permissions_cache_has_role
AFTER INSERT OR UPDATE OR DELETE ON has_role
FOR EACH STATEMENT EXECUTE FUNCTION refresh_permissions_cache();

user_schema.sql

-- function to generate timestamp on creation
CREATE OR REPLACE FUNCTION GENERATED_TIMESTAMP()
RETURNS TIMESTAMP WITH TIME ZONE AS $$
BEGIN
  RETURN timezone('UTC', current_timestamp);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Base table for audit fields
CREATE TABLE base_table (
  id UUID PRIMARY KEY DEFAULT GEN_RANDOM_UUID(),
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID,
  modified_at TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS (GENERATED_TIMESTAMP()) STORED,
  modified_by UUID
);

-- User table inheriting from base
CREATE TABLE IF NOT EXISTS public."user" (
  username TEXT NOT NULL UNIQUE,
  password_hash BYTEA,
  refresh_token BYTEA,
  disabled BOOLEAN NOT NULL DEFAULT FALSE
) INHERITS (base_table);

-- Role table inheriting from base
CREATE TABLE role (
  role_name TEXT NOT NULL UNIQUE,
  description TEXT,
  parent_role UUID,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  CONSTRAINT valid_parent CHECK (
    id != parent_role OR parent_role IS NULL
  )
) INHERITS (base_table);

-- Ability rules inheriting from base
CREATE TABLE ability_rule (
  role_id UUID NOT NULL,
  doctype TEXT NOT NULL,
  action TEXT NOT NULL,
  subject TEXT NOT NULL,
  conditions JSONB,
  inverted BOOLEAN NOT NULL DEFAULT FALSE,
  active BOOLEAN NOT NULL DEFAULT TRUE
) INHERITS (base_table);

-- Add foreign key constraints
ALTER TABLE base_table
  ADD CONSTRAINT fk_created_by FOREIGN KEY (created_by) REFERENCES public."user" (id),
  ADD CONSTRAINT fk_modified_by FOREIGN KEY (modified_by) REFERENCES public."user" (id);

-- User-role assignment table inheriting from base
CREATE TABLE has_role (
  user_id UUID NOT NULL,
  role_id UUID NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  CONSTRAINT pk_user_role PRIMARY KEY (user_id, role_id)
) INHERITS (base_table);

-- Add foreign key constraints
ALTER TABLE has_role
  ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES public."user" (id),
  ADD CONSTRAINT fk_role_id FOREIGN KEY (role_id) REFERENCES role (id);

-- Role collection table with array of role IDs
CREATE TABLE role_collection (
  id UUID PRIMARY KEY DEFAULT GEN_RANDOM_UUID(),
  collection_name TEXT NOT NULL UNIQUE,
  description TEXT,
  role_ids UUID[] NOT NULL DEFAULT '{}',
  active BOOLEAN NOT NULL DEFAULT TRUE,
  CONSTRAINT valid_role_ids CHECK (
    array_position(role_ids, NULL) IS NULL
  )
) INHERITS (base_table);

-- Add foreign key constraint for roles
ALTER TABLE role_collection
  ADD CONSTRAINT fk_role_ids FOREIGN KEY (unnest(role_ids)) REFERENCES role (id);

-- Indexes
CREATE INDEX idx_role_collection_active ON role_collection (active);
CREATE INDEX idx_role_collection_roles ON role_collection USING gin (role_ids);

-- Indexes
CREATE INDEX idx_user_role_user ON user_role (user_id) WHERE active = TRUE;
CREATE INDEX idx_user_role_role ON user_role (role_id) WHERE active = TRUE;

ALTER TABLE role
  ADD CONSTRAINT fk_parent_role FOREIGN KEY (parent_role) REFERENCES role (id);

ALTER TABLE ability_rule
  ADD CONSTRAINT fk_role_id FOREIGN KEY (role_id) REFERENCES role (id);

CREATE INDEX idx_role_active ON role (active);
CREATE INDEX idx_role_parent ON role (parent_role) WHERE parent_role IS NOT NULL;

CREATE INDEX idx_ability_role ON ability_rule (role_id) WHERE active = TRUE;
CREATE INDEX idx_ability_doctype ON ability_rule (doctype) WHERE active = TRUE;
CREATE INDEX idx_ability_conditions ON ability_rule USING gin (conditions) WHERE conditions IS NOT NULL;
CREATE INDEX idx_ability_role_doctype ON ability_rule (role_id, doctype) WHERE active = TRUE;