Skip to main content

Knowledge > Runbooks > Technical Ops > Refresh Supabase Views and Cached Aggregates

Refresh Supabase Views and Cached Aggregates

Refresh stale data in Supabase materialized views or cached aggregates when query results are outdated.

Prerequisites​

  • Access to Supabase SQL editor or the mcp__plugin_supabase_supabase__execute_sql MCP tool
  • Knowledge of which view needs refreshing and why (stale data complaint, scheduled refresh)

Key Views in the System​

ViewTypeOwnerPurpose
dir_illustrationsRegular viewunified_rag_contentRead-only illustration snapshot for ITW

Important: dir_illustrations is a regular view (not materialized) — it always reflects live data. Refreshing it is not necessary; if data appears stale, the underlying unified_rag_content table has not been updated.

Steps​

  1. Determine if the view is regular or materialized

    SELECT schemaname, viewname, definition
    FROM pg_views
    WHERE viewname = 'dir_illustrations';

    -- For materialized views:
    SELECT schemaname, matviewname, ispopulated
    FROM pg_matviews
    WHERE matviewname = 'your_view_name';
    • Regular view: always live — no refresh needed. If data is wrong, fix the underlying table.
    • Materialized view: cached snapshot — requires explicit refresh.

  2. Refresh a materialized view

    For non-blocking refresh (allows reads during refresh):

    REFRESH MATERIALIZED VIEW CONCURRENTLY your_view_name;

    Note: CONCURRENTLY requires a unique index on the materialized view. If none exists:

    REFRESH MATERIALIZED VIEW your_view_name;
    -- This locks reads during refresh — use only for small views or off-peak hours

  3. Verify the view returns fresh data

    After refreshing, confirm the data is up to date:

    -- Check record count matches expected
    SELECT count(*) FROM your_view_name;

    -- Check most recent record timestamp
    SELECT max(updated_at) FROM your_view_name;

    -- Compare to the source table
    SELECT max(updated_at) FROM unified_rag_content WHERE content_type = 'illustration';

  4. Investigate stale data in a regular view

    If dir_illustrations returns outdated content, the issue is in unified_rag_content:

    -- Check if the source data was updated
    SELECT id, title, updated_at FROM unified_rag_content
    WHERE content_type = 'illustration'
    ORDER BY updated_at DESC LIMIT 5;

    -- Check if the view definition is correct
    SELECT definition FROM pg_views WHERE viewname = 'dir_illustrations';

  5. Schedule recurring refresh if needed

    For materialized views that need regular refresh, use a Supabase cron job (via pg_cron extension) or the existing ops cron infrastructure at /api/ops/collect (runs every 15 minutes).

    Example pg_cron entry (run in SQL editor):

    -- Refresh every hour at :00
    SELECT cron.schedule('refresh-my-view', '0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY my_view');

  6. Never write directly to dir_illustrations

    dir_illustrations is a read-only view. Any attempt to INSERT, UPDATE, or DELETE through it will fail. Always write to unified_rag_content directly.

Verification​

  • SELECT count(*) FROM view_name returns the expected row count
  • SELECT max(updated_at) FROM view_name matches the source table's most recent update
  • The application layer (ITW illustration pages, chatbot RAG) returns fresh content

See Also​