Permission Caching

Permission caching is a critical performance optimization in the PostgreSQL Permissions Model that dramatically reduces the overhead of permission checks. The system uses intelligent caching strategies with automatic invalidation to maintain both performance and consistency.

Caching Architecture

The permission caching system operates at multiple levels to optimize different access patterns:

graph TB
    subgraph "Application Layer"
        APP["Application Request<br/>has_permission(tenant, user, perm)"]
    end

    subgraph "Cache Layers"
        L1["L1: Function-Level Cache<br/>Session-scoped<br/>Immediate repeated calls"]
        L2["L2: Database Cache<br/>auth.permission_cache<br/>Cross-session persistence"]
        L3["L3: Materialized Views<br/>auth.user_effective_permissions<br/>Pre-computed results"]
    end

    subgraph "Data Sources"
        DIRECT["Direct Assignments<br/>auth.user_permission"]
        GROUPS["Group Assignments<br/>Via group membership"]
        HIERARCHY["Hierarchical Permissions<br/>Via ltree inheritance"]
        SETS["Permission Sets<br/>auth.permission_set_item"]
    end

    APP --> L1
    L1 -->|Cache Miss| L2
    L2 -->|Cache Miss| L3
    L3 -->|Cache Miss| DIRECT
    L3 --> GROUPS
    L3 --> HIERARCHY
    L3 --> SETS

Cache Strategy Benefits: - L1 Cache: Eliminates repeated function calls within same transaction - L2 Cache: Reduces computation across sessions and users - L3 Cache: Pre-computes complex permission resolution for bulk operations - Automatic Invalidation: Maintains consistency when permissions change

Database Cache Implementation

Core Cache Table

-- Main permission cache table
CREATE TABLE auth.permission_cache (
    cache_key text PRIMARY KEY,
    user_id uuid NOT NULL REFERENCES auth.user_info(user_id),
    tenant_id uuid NOT NULL REFERENCES auth.tenant(tenant_id),
    perm_code text NOT NULL REFERENCES auth.permission(perm_code),

    -- Cache data
    has_permission boolean NOT NULL,
    computed_at timestamptz NOT NULL DEFAULT now(),
    expires_at timestamptz NOT NULL DEFAULT (now() + interval '15 minutes'),

    -- Performance metadata
    computation_ms integer,
    cache_hits integer DEFAULT 0,

    -- Invalidation tracking
    last_invalidated timestamptz,
    invalidation_reason text
);

-- Indexes for cache performance
CREATE INDEX CONCURRENTLY idx_permission_cache_user_tenant
    ON auth.permission_cache(user_id, tenant_id, expires_at)
    WHERE expires_at > now();

CREATE INDEX CONCURRENTLY idx_permission_cache_expiry
    ON auth.permission_cache(expires_at)
    WHERE expires_at > now();

CREATE INDEX CONCURRENTLY idx_permission_cache_perm_lookup
    ON auth.permission_cache(tenant_id, perm_code, expires_at)
    WHERE expires_at > now() AND has_permission = true;

-- Automatic cleanup of expired cache entries
CREATE INDEX CONCURRENTLY idx_permission_cache_cleanup
    ON auth.permission_cache(expires_at)
    WHERE expires_at <= now();

Cache Management Functions

-- Get cached permission result
CREATE OR REPLACE FUNCTION auth.get_cached_permission(
    _tenant_id uuid,
    _user_id uuid,
    _perm_code text
) RETURNS boolean AS $$
DECLARE
    v_cache_key text;
    v_cached_result boolean;
    v_cache_record record;
BEGIN
    v_cache_key := _tenant_id::text || ':' || _user_id::text || ':' || _perm_code;

    SELECT
        has_permission,
        cache_hits,
        user_permission_cache_id
    INTO v_cache_record
    FROM auth.permission_cache
    WHERE cache_key = v_cache_key
      AND expires_at > now()
    LIMIT 1;

    IF v_cache_record.has_permission IS NOT NULL THEN
        -- Update hit counter
        UPDATE auth.permission_cache
        SET
            cache_hits = cache_hits + 1,
            last_accessed = now()
        WHERE cache_key = v_cache_key;

        RETURN v_cache_record.has_permission;
    END IF;

    RETURN NULL; -- Cache miss
END;
$$ LANGUAGE plpgsql;

-- Store permission result in cache
CREATE OR REPLACE FUNCTION auth.cache_permission(
    _tenant_id uuid,
    _user_id uuid,
    _perm_code text,
    _has_permission boolean,
    _computation_ms integer DEFAULT NULL
) RETURNS void AS $$
DECLARE
    v_cache_key text;
    v_expires_at timestamptz;
    v_cache_duration interval;
BEGIN
    v_cache_key := _tenant_id::text || ':' || _user_id::text || ':' || _perm_code;

    -- Determine cache duration based on permission type and result
    SELECT
        CASE
            WHEN p.perm_level = 'system' THEN interval '5 minutes'
            WHEN p.perm_level = 'admin' THEN interval '10 minutes'
            WHEN _has_permission = false THEN interval '5 minutes'  -- Cache denials shorter
            ELSE interval '15 minutes'
        END
    INTO v_cache_duration
    FROM auth.permission p
    WHERE p.perm_code = _perm_code;

    v_expires_at := now() + COALESCE(v_cache_duration, interval '15 minutes');

    INSERT INTO auth.permission_cache (
        cache_key,
        user_id,
        tenant_id,
        perm_code,
        has_permission,
        expires_at,
        computation_ms
    )
    VALUES (
        v_cache_key,
        _user_id,
        _tenant_id,
        _perm_code,
        _has_permission,
        v_expires_at,
        _computation_ms
    )
    ON CONFLICT (cache_key)
    DO UPDATE SET
        has_permission = _has_permission,
        computed_at = now(),
        expires_at = v_expires_at,
        computation_ms = _computation_ms,
        cache_hits = 0;
END;
$$ LANGUAGE plpgsql;

Cache-Aware Permission Functions

Optimized Permission Check

-- Permission check with intelligent caching
CREATE OR REPLACE FUNCTION auth.has_permission_cached(
    _tenant_id uuid,
    _user_id uuid,
    _perm_code text,
    _throw_err boolean DEFAULT true
) RETURNS boolean AS $$
DECLARE
    v_cached_result boolean;
    v_computed_result boolean;
    v_start_time timestamptz;
    v_computation_ms integer;
BEGIN
    v_start_time := clock_timestamp();

    -- 1. Check cache first
    v_cached_result := auth.get_cached_permission(_tenant_id, _user_id, _perm_code);

    IF v_cached_result IS NOT NULL THEN
        RETURN v_cached_result;
    END IF;

    -- 2. Cache miss - compute permission
    v_computed_result := auth.has_permission_compute(_tenant_id, _user_id, _perm_code, _throw_err);

    -- 3. Store in cache
    v_computation_ms := extract(milliseconds FROM clock_timestamp() - v_start_time)::integer;
    PERFORM auth.cache_permission(_tenant_id, _user_id, _perm_code, v_computed_result, v_computation_ms);

    RETURN v_computed_result;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Core permission computation (without caching)
CREATE OR REPLACE FUNCTION auth.has_permission_compute(
    _tenant_id uuid,
    _user_id uuid,
    _perm_code text,
    _throw_err boolean DEFAULT true
) RETURNS boolean AS $$
DECLARE
    v_has_permission boolean := false;
BEGIN
    -- Direct user permissions check
    SELECT true INTO v_has_permission
    FROM auth.user_permission up
    WHERE up.tenant_id = _tenant_id
      AND up.user_id = _user_id
      AND up.granted = true
      AND (up.expires_at IS NULL OR up.expires_at > now())
      AND (
          up.perm_code = _perm_code
          OR
          (up.perm_set_code IS NOT NULL
           AND EXISTS (
               SELECT 1 FROM auth.permission_set_item psi
               WHERE psi.perm_set_code = up.perm_set_code
                 AND psi.tenant_id = _tenant_id
                 AND psi.perm_code = _perm_code
                 AND psi.granted = true
           ))
          OR
          EXISTS (
              SELECT 1 FROM auth.permission p
              WHERE p.perm_code = up.perm_code
                AND _perm_code::ltree <@ p.perm_path
          )
      )
    LIMIT 1;

    IF v_has_permission THEN
        RETURN true;
    END IF;

    -- Group permissions check
    SELECT true INTO v_has_permission
    FROM auth.resolve_user_group_permissions(_tenant_id, _user_id) ugp
    WHERE ugp.perm_code = _perm_code
       OR _perm_code::ltree <@ ugp.perm_path
    LIMIT 1;

    IF NOT COALESCE(v_has_permission, false) AND _throw_err THEN
        PERFORM auth.throw_permission_denied(_tenant_id, _user_id, _perm_code);
    END IF;

    RETURN COALESCE(v_has_permission, false);
END;
$$ LANGUAGE plpgsql;

Bulk Permission Caching

-- Cache multiple permissions at once
CREATE OR REPLACE FUNCTION auth.cache_user_permissions_bulk(
    _tenant_id uuid,
    _user_id uuid,
    _perm_codes text[]
) RETURNS integer AS $$
DECLARE
    v_perm_code text;
    v_result boolean;
    v_cached_count integer := 0;
    v_start_time timestamptz;
    v_computation_ms integer;
BEGIN
    v_start_time := clock_timestamp();

    FOREACH v_perm_code IN ARRAY _perm_codes
    LOOP
        -- Skip if already cached
        IF auth.get_cached_permission(_tenant_id, _user_id, v_perm_code) IS NOT NULL THEN
            CONTINUE;
        END IF;

        -- Compute and cache
        v_result := auth.has_permission_compute(_tenant_id, _user_id, v_perm_code, false);
        PERFORM auth.cache_permission(_tenant_id, _user_id, v_perm_code, v_result);
        v_cached_count := v_cached_count + 1;
    END LOOP;

    v_computation_ms := extract(milliseconds FROM clock_timestamp() - v_start_time)::integer;

    -- Log bulk caching
    PERFORM auth.create_auth_event(
        _tenant_id,
        _user_id,
        '50040', -- BULK_PERMISSIONS_CACHED
        'Bulk permission caching completed',
        jsonb_build_object(
            'permissions_cached', v_cached_count,
            'total_requested', array_length(_perm_codes, 1),
            'computation_ms', v_computation_ms
        )
    );

    RETURN v_cached_count;
END;
$$ LANGUAGE plpgsql;

Cache Invalidation Strategies

Smart Invalidation Functions

-- Clear cache for specific user
CREATE OR REPLACE FUNCTION auth.clear_permission_cache(
    _user_id uuid,
    _reason text DEFAULT 'Permission change'
) RETURNS integer AS $$
DECLARE
    v_cleared_count integer;
BEGIN
    UPDATE auth.permission_cache
    SET
        expires_at = now() - interval '1 second',
        last_invalidated = now(),
        invalidation_reason = _reason
    WHERE user_id = _user_id
      AND expires_at > now();

    GET DIAGNOSTICS v_cleared_count = ROW_COUNT;

    -- Log cache invalidation
    IF v_cleared_count > 0 THEN
        PERFORM auth.create_auth_event(
            NULL, -- System event
            _user_id,
            '50041', -- PERMISSION_CACHE_CLEARED
            'Permission cache cleared: ' || _reason,
            jsonb_build_object('cleared_count', v_cleared_count)
        );
    END IF;

    RETURN v_cleared_count;
END;
$$ LANGUAGE plpgsql;

-- Clear cache for all members of a group
CREATE OR REPLACE FUNCTION auth.clear_group_members_cache(
    _user_group_id uuid,
    _reason text DEFAULT 'Group permission change'
) RETURNS integer AS $$
DECLARE
    v_cleared_count integer;
BEGIN
    UPDATE auth.permission_cache
    SET
        expires_at = now() - interval '1 second',
        last_invalidated = now(),
        invalidation_reason = _reason
    WHERE user_id IN (
        SELECT DISTINCT ugm.user_id
        FROM auth.user_group_member ugm
        WHERE ugm.user_group_id = _user_group_id
          AND ugm.is_active = true
    )
    AND expires_at > now();

    GET DIAGNOSTICS v_cleared_count = ROW_COUNT;

    RETURN v_cleared_count;
END;
$$ LANGUAGE plpgsql;

-- Clear cache for specific permission across all users
CREATE OR REPLACE FUNCTION auth.clear_permission_cache_by_permission(
    _perm_code text,
    _tenant_id uuid DEFAULT NULL
) RETURNS integer AS $$
DECLARE
    v_cleared_count integer;
BEGIN
    UPDATE auth.permission_cache
    SET
        expires_at = now() - interval '1 second',
        last_invalidated = now(),
        invalidation_reason = 'Permission definition changed: ' || _perm_code
    WHERE perm_code = _perm_code
      AND (_tenant_id IS NULL OR tenant_id = _tenant_id)
      AND expires_at > now();

    GET DIAGNOSTICS v_cleared_count = ROW_COUNT;

    -- Also clear hierarchical children
    UPDATE auth.permission_cache
    SET
        expires_at = now() - interval '1 second',
        last_invalidated = now(),
        invalidation_reason = 'Parent permission changed: ' || _perm_code
    WHERE perm_code::ltree <@ _perm_code::ltree
      AND perm_code != _perm_code
      AND (_tenant_id IS NULL OR tenant_id = _tenant_id)
      AND expires_at > now();

    GET DIAGNOSTICS v_cleared_count = v_cleared_count + ROW_COUNT;

    RETURN v_cleared_count;
END;
$$ LANGUAGE plpgsql;

Automatic Cache Invalidation Triggers

-- Trigger to invalidate cache when permissions change
CREATE OR REPLACE FUNCTION auth.trigger_invalidate_permission_cache()
RETURNS trigger AS $$
BEGIN
    -- Handle INSERT/UPDATE/DELETE of user_permission
    IF TG_TABLE_NAME = 'user_permission' THEN
        IF TG_OP IN ('INSERT', 'UPDATE') THEN
            -- Clear cache for affected user/group
            IF NEW.user_id IS NOT NULL THEN
                PERFORM auth.clear_permission_cache(NEW.user_id, 'Permission assignment changed');
            ELSE
                PERFORM auth.clear_group_members_cache(NEW.user_group_id, 'Group permission changed');
            END IF;
        ELSIF TG_OP = 'DELETE' THEN
            IF OLD.user_id IS NOT NULL THEN
                PERFORM auth.clear_permission_cache(OLD.user_id, 'Permission assignment removed');
            ELSE
                PERFORM auth.clear_group_members_cache(OLD.user_group_id, 'Group permission removed');
            END IF;
        END IF;
    END IF;

    -- Handle group membership changes
    IF TG_TABLE_NAME = 'user_group_member' THEN
        IF TG_OP IN ('INSERT', 'UPDATE') THEN
            PERFORM auth.clear_permission_cache(NEW.user_id, 'Group membership changed');
        ELSIF TG_OP = 'DELETE' THEN
            PERFORM auth.clear_permission_cache(OLD.user_id, 'Group membership removed');
        END IF;
    END IF;

    -- Handle permission set changes
    IF TG_TABLE_NAME = 'permission_set_item' THEN
        -- Clear cache for all users with this permission set
        UPDATE auth.permission_cache
        SET expires_at = now() - interval '1 second'
        WHERE user_id IN (
            SELECT DISTINCT COALESCE(up.user_id, ugm.user_id)
            FROM auth.user_permission up
            LEFT JOIN auth.user_group_member ugm ON up.user_group_id = ugm.user_group_id
            WHERE up.perm_set_code = COALESCE(NEW.perm_set_code, OLD.perm_set_code)
              AND up.tenant_id = COALESCE(NEW.tenant_id, OLD.tenant_id)
        );
    END IF;

    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Create triggers
DROP TRIGGER IF EXISTS trigger_user_permission_cache_invalidation ON auth.user_permission;
CREATE TRIGGER trigger_user_permission_cache_invalidation
    AFTER INSERT OR UPDATE OR DELETE ON auth.user_permission
    FOR EACH ROW EXECUTE FUNCTION auth.trigger_invalidate_permission_cache();

DROP TRIGGER IF EXISTS trigger_user_group_member_cache_invalidation ON auth.user_group_member;
CREATE TRIGGER trigger_user_group_member_cache_invalidation
    AFTER INSERT OR UPDATE OR DELETE ON auth.user_group_member
    FOR EACH ROW EXECUTE FUNCTION auth.trigger_invalidate_permission_cache();

DROP TRIGGER IF EXISTS trigger_permission_set_item_cache_invalidation ON auth.permission_set_item;
CREATE TRIGGER trigger_permission_set_item_cache_invalidation
    AFTER INSERT OR UPDATE OR DELETE ON auth.permission_set_item
    FOR EACH ROW EXECUTE FUNCTION auth.trigger_invalidate_permission_cache();

Materialized View Caching

Pre-computed Permission Views

-- Materialized view for frequently accessed permissions
CREATE MATERIALIZED VIEW auth.user_effective_permissions AS
WITH user_direct_permissions AS (
    SELECT
        up.tenant_id,
        up.user_id,
        up.perm_code,
        up.granted,
        'direct' as source_type,
        up.assigned_at,
        1 as priority
    FROM auth.user_permission up
    WHERE up.perm_code IS NOT NULL
      AND up.granted = true
      AND (up.expires_at IS NULL OR up.expires_at > now())

    UNION ALL

    -- Permission sets
    SELECT
        up.tenant_id,
        up.user_id,
        psi.perm_code,
        psi.granted,
        'permission_set' as source_type,
        up.assigned_at,
        2 as priority
    FROM auth.user_permission up
    JOIN auth.permission_set_item psi ON up.perm_set_code = psi.perm_set_code
        AND up.tenant_id = psi.tenant_id
    WHERE up.perm_set_code IS NOT NULL
      AND up.granted = true
      AND psi.granted = true
      AND (up.expires_at IS NULL OR up.expires_at > now())
),
group_permissions AS (
    SELECT
        ug.tenant_id,
        ugm.user_id,
        COALESCE(up.perm_code, psi.perm_code) as perm_code,
        COALESCE(up.granted, psi.granted) as granted,
        'group_' || CASE WHEN up.perm_code IS NOT NULL THEN 'direct' ELSE 'permission_set' END as source_type,
        up.assigned_at,
        10 + CASE ug.group_type
            WHEN 'internal' THEN 0
            WHEN 'hybrid' THEN 1
            WHEN 'external' THEN 2
            ELSE 5
        END as priority
    FROM auth.user_group_member ugm
    JOIN auth.user_group ug ON ugm.user_group_id = ug.user_group_id
    JOIN auth.user_permission up ON ug.user_group_id = up.user_group_id
    LEFT JOIN auth.permission_set_item psi ON up.perm_set_code = psi.perm_set_code
        AND up.tenant_id = psi.tenant_id
    WHERE ugm.is_active = true
      AND ug.is_active = true
      AND up.granted = true
      AND (up.expires_at IS NULL OR up.expires_at > now())
      AND (up.perm_code IS NOT NULL OR (psi.perm_code IS NOT NULL AND psi.granted = true))
),
all_permissions AS (
    SELECT * FROM user_direct_permissions
    UNION ALL
    SELECT * FROM group_permissions
),
effective_permissions AS (
    SELECT DISTINCT ON (tenant_id, user_id, perm_code)
        tenant_id,
        user_id,
        perm_code,
        granted,
        source_type,
        assigned_at,
        priority
    FROM all_permissions
    WHERE granted = true
    ORDER BY tenant_id, user_id, perm_code, priority
)
SELECT
    ep.tenant_id,
    ep.user_id,
    u.username,
    ep.perm_code,
    p.name as permission_name,
    p.perm_level,
    ep.source_type,
    ep.assigned_at,
    ep.priority,
    now() as computed_at
FROM effective_permissions ep
JOIN auth.user_info u ON ep.user_id = u.user_id
JOIN auth.permission p ON ep.perm_code = p.perm_code
WHERE u.is_active = true
  AND p.is_active = true;

-- Indexes for materialized view
CREATE UNIQUE INDEX idx_user_effective_permissions_lookup
    ON auth.user_effective_permissions (tenant_id, user_id, perm_code);

CREATE INDEX idx_user_effective_permissions_user
    ON auth.user_effective_permissions (user_id, tenant_id);

CREATE INDEX idx_user_effective_permissions_permission
    ON auth.user_effective_permissions (perm_code, tenant_id);

Materialized View Management

-- Refresh materialized view
CREATE OR REPLACE FUNCTION auth.refresh_user_effective_permissions(
    _concurrent boolean DEFAULT true
) RETURNS void AS $$
BEGIN
    IF _concurrent THEN
        REFRESH MATERIALIZED VIEW CONCURRENTLY auth.user_effective_permissions;
    ELSE
        REFRESH MATERIALIZED VIEW auth.user_effective_permissions;
    END IF;

    -- Log refresh
    PERFORM auth.create_auth_event(
        NULL, -- System event
        NULL,
        '50042', -- MATERIALIZED_VIEW_REFRESHED
        'User effective permissions view refreshed',
        jsonb_build_object('concurrent', _concurrent, 'refreshed_at', now())
    );
END;
$$ LANGUAGE plpgsql;

-- Fast permission check using materialized view
CREATE OR REPLACE FUNCTION auth.has_permission_materialized(
    _tenant_id uuid,
    _user_id uuid,
    _perm_code text
) RETURNS boolean AS $$
DECLARE
    v_result boolean := false;
BEGIN
    -- Direct permission check
    SELECT true INTO v_result
    FROM auth.user_effective_permissions uep
    WHERE uep.tenant_id = _tenant_id
      AND uep.user_id = _user_id
      AND uep.perm_code = _perm_code
    LIMIT 1;

    IF v_result THEN
        RETURN true;
    END IF;

    -- Hierarchical permission check
    SELECT true INTO v_result
    FROM auth.user_effective_permissions uep
    JOIN auth.permission p ON uep.perm_code = p.perm_code
    WHERE uep.tenant_id = _tenant_id
      AND uep.user_id = _user_id
      AND _perm_code::ltree <@ p.perm_path
    LIMIT 1;

    RETURN COALESCE(v_result, false);
END;
$$ LANGUAGE plpgsql;

Cache Performance Monitoring

Cache Statistics and Metrics

-- Cache performance analytics
CREATE OR REPLACE FUNCTION auth.get_cache_statistics(
    _time_range interval DEFAULT interval '24 hours'
) RETURNS TABLE (
    metric text,
    value bigint,
    percentage numeric
) AS $$
DECLARE
    v_total_requests bigint;
    v_cache_hits bigint;
    v_cache_misses bigint;
    v_total_cached bigint;
    v_expired_entries bigint;
BEGIN
    -- Get basic metrics
    SELECT COUNT(*) INTO v_total_cached
    FROM auth.permission_cache
    WHERE computed_at > now() - _time_range;

    SELECT SUM(cache_hits) INTO v_cache_hits
    FROM auth.permission_cache
    WHERE computed_at > now() - _time_range;

    SELECT COUNT(*) INTO v_expired_entries
    FROM auth.permission_cache
    WHERE expires_at <= now()
      AND computed_at > now() - _time_range;

    v_total_requests := v_cache_hits + v_total_cached;

    RETURN QUERY
    VALUES
        ('Total Permission Requests', v_total_requests, 100.0),
        ('Cache Hits', v_cache_hits,
         CASE WHEN v_total_requests > 0 THEN
              round((v_cache_hits::numeric / v_total_requests * 100), 2)
         ELSE 0 END),
        ('Cache Misses (New Computations)', v_total_cached,
         CASE WHEN v_total_requests > 0 THEN
              round((v_total_cached::numeric / v_total_requests * 100), 2)
         ELSE 0 END),
        ('Expired Cache Entries', v_expired_entries,
         CASE WHEN v_total_cached > 0 THEN
              round((v_expired_entries::numeric / v_total_cached * 100), 2)
         ELSE 0 END);
END;
$$ LANGUAGE plpgsql;

-- Cache efficiency by user
CREATE OR REPLACE FUNCTION auth.get_cache_efficiency_by_user(
    _limit integer DEFAULT 10
) RETURNS TABLE (
    username text,
    total_cached_permissions integer,
    total_cache_hits bigint,
    hit_ratio numeric,
    avg_computation_ms numeric
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        u.username,
        COUNT(pc.cache_key)::integer as total_cached_permissions,
        SUM(pc.cache_hits) as total_cache_hits,
        CASE WHEN COUNT(pc.cache_key) > 0 THEN
            round((SUM(pc.cache_hits)::numeric / COUNT(pc.cache_key)), 2)
        ELSE 0 END as hit_ratio,
        round(AVG(pc.computation_ms), 2) as avg_computation_ms
    FROM auth.permission_cache pc
    JOIN auth.user_info u ON pc.user_id = u.user_id
    WHERE pc.computed_at > now() - interval '24 hours'
    GROUP BY u.user_id, u.username
    ORDER BY total_cache_hits DESC, hit_ratio DESC
    LIMIT _limit;
END;
$$ LANGUAGE plpgsql;

-- Most expensive permissions to compute
CREATE OR REPLACE FUNCTION auth.get_expensive_permissions(
    _limit integer DEFAULT 10
) RETURNS TABLE (
    perm_code text,
    permission_name text,
    avg_computation_ms numeric,
    total_computations bigint,
    total_computation_time_ms bigint
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        pc.perm_code,
        p.name as permission_name,
        round(AVG(pc.computation_ms), 2) as avg_computation_ms,
        COUNT(*) as total_computations,
        SUM(pc.computation_ms) as total_computation_time_ms
    FROM auth.permission_cache pc
    JOIN auth.permission p ON pc.perm_code = p.perm_code
    WHERE pc.computed_at > now() - interval '24 hours'
      AND pc.computation_ms IS NOT NULL
    GROUP BY pc.perm_code, p.name
    ORDER BY avg_computation_ms DESC, total_computations DESC
    LIMIT _limit;
END;
$$ LANGUAGE plpgsql;

Cache Maintenance

-- Automated cache cleanup
CREATE OR REPLACE FUNCTION auth.cleanup_expired_cache()
RETURNS integer AS $$
DECLARE
    v_deleted_count integer;
BEGIN
    DELETE FROM auth.permission_cache
    WHERE expires_at <= now() - interval '1 hour'; -- Keep expired entries for analysis

    GET DIAGNOSTICS v_deleted_count = ROW_COUNT;

    -- Log cleanup
    IF v_deleted_count > 0 THEN
        PERFORM auth.create_auth_event(
            NULL, -- System event
            NULL,
            '50043', -- CACHE_CLEANUP
            'Expired cache entries cleaned up',
            jsonb_build_object('deleted_count', v_deleted_count)
        );
    END IF;

    RETURN v_deleted_count;
END;
$$ LANGUAGE plpgsql;

-- Optimize cache configuration
CREATE OR REPLACE FUNCTION auth.optimize_cache_configuration()
RETURNS jsonb AS $$
DECLARE
    v_stats record;
    v_recommendations jsonb := '[]'::jsonb;
BEGIN
    -- Analyze cache performance
    SELECT
        AVG(cache_hits) as avg_hits,
        AVG(computation_ms) as avg_computation_ms,
        COUNT(*) as total_entries,
        COUNT(*) FILTER (WHERE expires_at <= now()) as expired_entries
    INTO v_stats
    FROM auth.permission_cache
    WHERE computed_at > now() - interval '24 hours';

    -- Generate recommendations
    IF v_stats.avg_hits < 2 THEN
        v_recommendations := v_recommendations || jsonb_build_object(
            'type', 'low_hit_ratio',
            'recommendation', 'Consider increasing cache duration or reviewing permission patterns',
            'metric', 'Average hits per entry: ' || round(v_stats.avg_hits, 2)
        );
    END IF;

    IF v_stats.avg_computation_ms > 50 THEN
        v_recommendations := v_recommendations || jsonb_build_object(
            'type', 'slow_computation',
            'recommendation', 'Consider optimizing permission resolution queries or adding indexes',
            'metric', 'Average computation time: ' || round(v_stats.avg_computation_ms, 2) || 'ms'
        );
    END IF;

    IF v_stats.expired_entries::numeric / v_stats.total_entries > 0.5 THEN
        v_recommendations := v_recommendations || jsonb_build_object(
            'type', 'high_expiration_rate',
            'recommendation', 'Consider longer cache durations for stable permissions',
            'metric', 'Expiration rate: ' || round((v_stats.expired_entries::numeric / v_stats.total_entries * 100), 1) || '%'
        );
    END IF;

    RETURN jsonb_build_object(
        'analysis_date', now(),
        'statistics', row_to_json(v_stats),
        'recommendations', v_recommendations
    );
END;
$$ LANGUAGE plpgsql;

Best Practices for Permission Caching

Cache Strategy Guidelines

1. Cache Duration Tuning:
2. System permissions: 5 minutes (change infrequently)
3. Admin permissions: 10 minutes (moderate changes)
4. Standard permissions: 15 minutes (balance performance/consistency)

5. Denied permissions: 5 minutes (fail fast, recheck sooner)

6. Selective Caching:

7. Cache frequently accessed permissions
8. Avoid caching one-time permissions

9. Cache hierarchical permissions more aggressively

10. Invalidation Strategy:

11. Immediate invalidation for security-critical changes
12. Batch invalidation for bulk operations
13. Background cleanup of expired entries

Performance Optimization Patterns

-- Pre-warm cache for new users
CREATE OR REPLACE FUNCTION auth.prewarm_user_cache(
    _tenant_id uuid,
    _user_id uuid
) RETURNS integer AS $$
DECLARE
    v_common_permissions text[] := ARRAY[
        'dashboard.view',
        'profile.update.own',
        'users.view.basic',
        'reports.view',
        'api.basic'
    ];
BEGIN
    RETURN auth.cache_user_permissions_bulk(_tenant_id, _user_id, v_common_permissions);
END;
$$ LANGUAGE plpgsql;

-- Warm cache during low-traffic periods
CREATE OR REPLACE FUNCTION auth.warm_cache_batch(
    _tenant_id uuid,
    _batch_size integer DEFAULT 100
) RETURNS integer AS $$
DECLARE
    v_total_warmed integer := 0;
    v_user_batch record;
BEGIN
    FOR v_user_batch IN
        SELECT u.user_id
        FROM auth.tenant_user tu
        JOIN auth.user_info u ON tu.user_id = u.user_id
        WHERE tu.tenant_id = _tenant_id
          AND tu.is_active = true
          AND u.is_active = true
        ORDER BY u.last_login_at DESC NULLS LAST
        LIMIT _batch_size
    LOOP
        v_total_warmed := v_total_warmed + auth.prewarm_user_cache(_tenant_id, v_user_batch.user_id);
    END LOOP;

    RETURN v_total_warmed;
END;
$$ LANGUAGE plpgsql;

What's Next

• Learn about Permission Functions Reference for complete API documentation
• Explore Permission Sets organization and caching strategies
• Review Permission Assignment and cache invalidation patterns
• Understand Performance Optimization examples