Usage Aggregation — Cron Scheduling Guide

​

Overview

​

Function

SELECT pf_aggregate_resource_usage();

1. Iterates all active quotas with reset_period = 'daily' or 'monthly'
2. Sums realtime usage rows for the current period
3. Upserts aggregated rows into pf_resource_usage with aggregation_period = 'daily' or 'monthly'

​

Scheduling via pg_cron

​

Prerequisites

​

Recommended Schedule

-- Run daily aggregation every hour
SELECT cron.schedule(
  'pf-43-usage-aggregation-hourly',
  '0 * * * *',
  $$SELECT pf_aggregate_resource_usage();$$
);

-- Run daily cleanup of old realtime rows (optional)
SELECT cron.schedule(
  'pf-43-realtime-cleanup-daily',
  '30 3 * * *',
  $$DELETE FROM pf_resource_usage
    WHERE aggregation_period = 'realtime'
    AND period_start < now() - interval '7 days';$$
);

​

Via Supabase Dashboard

1. Go to SQL Editor → New Query
2. Paste the cron.schedule(...) SQL above
3. Run the query

​

Verification

SELECT * FROM cron.job WHERE jobname LIKE 'pf-43%';

​

Notes

• The aggregation function is idempotent (uses UPSERT)
• No edge function needed — runs directly in Postgres
• Realtime rows older than 7 days can be safely pruned
• Monitor via pf_resource_usage row counts per aggregation_period