Auto-Refresh with pg_cron and Triggers

Manual refresh calls are error-prone. Two automation approaches: pg_cron for time-based schedules, triggers for event-driven refresh.

-- pg_cron: schedule refreshes as cron jobs (requires pg_cron extension)
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Refresh mv_user_stats every hour
SELECT cron.schedule(
    'refresh-user-stats',          -- job name
    '0 * * * *',                   -- every hour at :00
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_user_stats$$
);

-- Refresh the full pipeline daily at 3am
SELECT cron.schedule(
    'refresh-tweet-pipeline',
    '0 3 * * *',
    $$SELECT refresh_tweet_pipeline()$$
);

-- List scheduled jobs
SELECT jobid, schedule, command, nodename, active
FROM cron.job;

-- Remove a job
SELECT cron.unschedule('refresh-user-stats');