template-saas/apps/database/ddl/schemas/users/tables/02-roles.sql

-- ============================================
-- TEMPLATE-SAAS: Roles & Permissions
-- Schema: users
-- Version: 1.0.0
-- ============================================

-- Roles table
CREATE TABLE users.roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,

    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    description TEXT,

    -- System role (cannot be deleted)
    is_system BOOLEAN DEFAULT FALSE,

    -- Permissions (JSONB array)
    permissions JSONB DEFAULT '[]'::jsonb,
    -- Example: ["users:read", "users:write", "billing:read"]

    -- Hierarchy
    parent_role_id UUID REFERENCES users.roles(id),
    level INT DEFAULT 0,  -- 0 = lowest, higher = more permissions

    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    created_by UUID,

    -- Constraints
    CONSTRAINT unique_role_slug_per_tenant UNIQUE (tenant_id, slug),
    CONSTRAINT valid_role_slug CHECK (slug ~ '^[a-z][a-z0-9_]*$')
);

-- User-Role junction
CREATE TABLE users.user_roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users.users(id) ON DELETE CASCADE,
    role_id UUID NOT NULL REFERENCES users.roles(id) ON DELETE CASCADE,

    -- Optional expiration
    expires_at TIMESTAMPTZ,

    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    created_by UUID,

    -- Constraints
    CONSTRAINT unique_user_role UNIQUE (user_id, role_id)
);

-- Permissions reference table (optional, for UI)
CREATE TABLE users.permissions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Permission definition
    code VARCHAR(100) UNIQUE NOT NULL,  -- e.g., "users:write"
    name VARCHAR(200) NOT NULL,
    description TEXT,
    category VARCHAR(100),  -- e.g., "users", "billing", "settings"

    -- Metadata
    is_sensitive BOOLEAN DEFAULT FALSE,  -- Requires extra confirmation
    requires_owner BOOLEAN DEFAULT FALSE,  -- Only tenant owner

    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

-- Indexes
CREATE INDEX idx_roles_tenant ON users.roles(tenant_id);
CREATE INDEX idx_roles_slug ON users.roles(tenant_id, slug);
CREATE INDEX idx_user_roles_user ON users.user_roles(user_id);
CREATE INDEX idx_user_roles_role ON users.user_roles(role_id);
CREATE INDEX idx_user_roles_tenant ON users.user_roles(tenant_id);

-- RLS for roles
ALTER TABLE users.roles ENABLE ROW LEVEL SECURITY;

CREATE POLICY roles_tenant_isolation ON users.roles
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- RLS for user_roles
ALTER TABLE users.user_roles ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_roles_tenant_isolation ON users.user_roles
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- Triggers
CREATE TRIGGER trg_roles_updated_at
    BEFORE UPDATE ON users.roles
    FOR EACH ROW
    EXECUTE FUNCTION users.update_updated_at();

-- Comments
COMMENT ON TABLE users.roles IS 'Role definitions per tenant';
COMMENT ON TABLE users.user_roles IS 'User-Role assignments';
COMMENT ON TABLE users.permissions IS 'Permission registry (global reference)';
COMMENT ON COLUMN users.roles.permissions IS 'Array of permission codes';
COMMENT ON COLUMN users.roles.is_system IS 'System roles cannot be modified or deleted';