# Supabase Log Cleanup: Edge Functions and Cron Jobs

After working on several Supabase-backed projects, I kept hitting the same silent disk problem — log tables that grow forever. Here is the cleanup_logs() function and how to run it safely.

## Context: how I got here

Over the past few months I've worked on several projects whose backend runs on Supabase. In many of them, Edge Functions are a core piece — webhooks, async tasks, third-party integrations — and some also rely on pg\_cron jobs. Everything worked fine until I started noticing a pattern: over time, the database kept approaching its disk limit even though business data volume didn't explain it.

The cause was **log accumulation**: execution records that Supabase writes directly into PostgreSQL and that **are not automatically purged** from the Dashboard.

Official Supabase documentation confirms:

- **Edge Functions:** logs live in the edge\_logs schema and in net.\_http\_response.
- **Cron Jobs (pg\_cron):** each run appends rows to cron.job\_run\_details.

Without active maintenance, those tables grow indefinitely. In the worst case, the database enters **read-only mode** due to disk exhaustion — a severe service interruption.

This document records the problem, the cleanup\_logs() function we implemented as a fix, and how to operate it safely in any Supabase project using Edge Functions or Cron Jobs.

## The problem

- **cron.job\_run\_details** · pg\_cron — Run history: start\_time, status, command, return\_message, etc.
- **net.\_http\_response** · pg\_net — HTTP responses from Edge Function invocations: created, status, headers, body, etc.

Every Edge Function call and every cron tick adds rows. There is no "clear logs" button in the Supabase UI. Growth is silent until disk runs out.

**Observed consequences:**

- Progressive consumption of the project's allocated disk space.
- Query performance degradation on those tables.
- Read-only mode risk when disk hits the limit.

## The solution: cleanup\_logs()

A PL/pgSQL function with SECURITY DEFINER that deletes records older than a configurable threshold (default: **12 hours**).

```sql
CREATE OR REPLACE FUNCTION cleanup_logs()
RETURNS void
SET search_path = 'public'
AS $$
BEGIN
    -- Clean cron job logs
    DELETE FROM cron.job_run_details
    WHERE start_time < NOW() - INTERVAL '12 hours';

    -- Clean HTTP response logs (Edge Functions)
    DELETE FROM net._http_response
    WHERE created < NOW() - INTERVAL '12 hours';

    RAISE LOG 'Log cleanup completed at %', NOW();
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

COMMENT ON FUNCTION cleanup_logs() IS
  'Removes old cron job and Edge Function logs to prevent uncontrolled disk growth';
```

### Technical characteristics

- **Security** — SECURITY DEFINER — runs with owner privileges to access cron and net schemas
- **Scope** — search\_path = 'public' — avoids name resolution ambiguity
- **Retention** — 12 hours by default; adjust by changing the interval in the function
- **Atomicity** — Runs in a transaction; both DELETE statements are consistent
- **Observability** — RAISE LOG leaves a trace in PostgreSQL logs

## Step-by-step implementation

### 1. Migration

```bash
npx supabase migration new create_cleanup_logs_function
```

Add the function SQL to the generated file and apply:

```bash
# Local
npx supabase db reset

# Production
npx supabase db push
```

### 2. Maintenance cron job

From **Supabase Dashboard → Database → Cron Jobs → Create Cron Job**:

- **Name** — cleanup-logs-maintenance
- **Schedule** — \*/5 \* \* \* \* (every 5 minutes)
- **Command** — SELECT cleanup\_logs();
- **Active** — ✅ Enabled

**Note:** The \*/5 \* \* \* \* schedule keeps tables bounded in high-traffic projects. For lighter loads, 0 \*/6 \* \* \* (every 6 hours) may suffice.

### 3. Verification

```sql
-- Scheduled job
SELECT jobname, schedule, active, command
FROM cron.job
WHERE jobname = 'cleanup-logs-maintenance';

-- Recent runs
SELECT start_time, status, return_message, command
FROM cron.job_run_details
WHERE command LIKE '%cleanup_logs%'
ORDER BY start_time DESC
LIMIT 10;
```

## Monitoring

### Diagnostic queries

```sql
-- Current cron log size
SELECT
    COUNT(*) AS total_records,
    pg_size_pretty(pg_total_relation_size('cron.job_run_details')) AS table_size
FROM cron.job_run_details;

-- Current HTTP log size
SELECT
    COUNT(*) AS total_records,
    pg_size_pretty(pg_total_relation_size('net._http_response')) AS table_size
FROM net._http_response;

-- Temporal distribution (last 24 h)
SELECT
    DATE_TRUNC('hour', start_time) AS hour,
    COUNT(*) AS log_count
FROM cron.job_run_details
WHERE start_time > NOW() - INTERVAL '24 hours'
GROUP BY hour
ORDER BY hour DESC;
```

### Optional threshold alert

```sql
CREATE OR REPLACE FUNCTION check_log_size_alert()
RETURNS void AS $$
DECLARE
    cron_count INTEGER;
    http_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO cron_count FROM cron.job_run_details;
    SELECT COUNT(*) INTO http_count FROM net._http_response;

    IF cron_count > 10000 OR http_count > 50000 THEN
        RAISE WARNING 'Log tables are growing too large: cron=%, http=%',
            cron_count, http_count;
    END IF;
END;
$$ LANGUAGE plpgsql;
```

Schedule check\_log\_size\_alert() on a separate cron or call it from the same maintenance job if early visibility is needed.

## Considerations

### ⚠️ Warnings

- **Irreversibility** — Deleted logs cannot be recovered. Export first if needed for auditing.
- **Performance** — Bulk DELETE may briefly impact the database. Tune retention and frequency to match load.
- **Dependencies** — Requires pg\_cron and pg\_net extensions enabled (standard on Supabase).

### ✅ Best practices

- **Gradual retention:** start with 24–48 h and reduce only if disk pressure demands it.
- **Low-traffic windows:** on sensitive projects, schedule cleanup during quieter periods.
- **Proactive monitoring:** review table sizes weekly until growth patterns stabilize.
- **Backups:** consider periodic export of critical logs before purge if compliance requires it.

## Quick reference

```text
Function:   cleanup_logs()
Retention:  12 hours (configurable)
Cron:       cleanup-logs-maintenance → SELECT cleanup_logs();
Tables:     cron.job_run_details, net._http_response
```