RBAC System

-- Organization-scoped groups
CREATE TABLE org_groups (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  is_default BOOLEAN DEFAULT FALSE,
  created_by UUID REFERENCES users(id),
  UNIQUE(organization_id, name)
);

-- Group membership
CREATE TABLE org_group_members (
  group_id UUID NOT NULL REFERENCES org_groups(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  PRIMARY KEY (group_id, user_id)
);

-- Resource-level permissions (assigned to groups OR users)
CREATE TABLE resource_permissions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  group_id UUID REFERENCES org_groups(id) ON DELETE CASCADE,  -- WHO (group)
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,        -- WHO (user)
  resource_type VARCHAR(50) NOT NULL,                         -- WHAT type
  resource_id UUID,                                            -- NULL = wildcard
  actions TEXT[] NOT NULL,                                     -- WHAT actions
  CHECK (group_id IS NOT NULL OR user_id IS NOT NULL),
  CHECK (NOT (group_id IS NOT NULL AND user_id IS NOT NULL))
);

-- Admin audit log
CREATE TABLE admin_audit_log (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  actor_id UUID NOT NULL REFERENCES users(id),
  actor_role VARCHAR(50) NOT NULL,
  organization_id UUID REFERENCES organizations(id),
  action VARCHAR(100) NOT NULL,
  target_type VARCHAR(50),
  target_id UUID,
  details JSONB DEFAULT '{}',
  ip_address INET,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Resolve effective permissions for a user on a resource
CREATE OR REPLACE FUNCTION resolve_resource_access(
  p_user_id UUID,
  p_organization_id UUID,
  p_resource_type VARCHAR,
  p_resource_id UUID
) RETURNS TEXT[] AS $$
DECLARE
  v_platform_role VARCHAR;
  v_org_role VARCHAR;
BEGIN
  -- 1. Superadmin bypass
  SELECT platform_role INTO v_platform_role FROM users WHERE id = p_user_id;
  IF v_platform_role = 'superadmin' THEN RETURN ARRAY['*']; END IF;

  -- 2. Org role check
  SELECT role INTO v_org_role FROM organization_members
    WHERE user_id = p_user_id AND organization_id = p_organization_id;
  IF v_org_role IN ('owner', 'admin') THEN RETURN ARRAY['*']; END IF;

  -- 3. Direct user + group permissions (specific + wildcard)
  -- Returns merged action array or empty array
END;
$$ LANGUAGE plpgsql STABLE;

// Rejects with 403 if user.platformRole !== role
// Used on: /admin/* routes, platform-wide endpoints

// Checks organization_members.role for current org
// Role hierarchy: owner > admin > member > viewer
// Superadmin bypasses this check
// Used on: org settings, team management, billing

// Calls resolve_resource_access() or in-memory equivalent
// Returns 403 if user lacks the required action
// Superadmin and org owner/admin bypass
// Used on: agent CRUD, KB operations, analytics views

// Writes to admin_audit_log after successful state-changing operations
// Required for: all admin actions, permission changes, user management