supabase__supabase/e2e/studio/features/cron-jobs.spec.ts

import { expect, Page } from '@playwright/test'

import { query } from '../utils/db/client.js'
import { releaseFileOnceCleanup, withFileOnceSetup } from '../utils/once-per-file.js'
import { test, withSetupCleanup } from '../utils/test.js'
import { toUrl } from '../utils/to-url.js'

type CronJobRow = { jobid: number; schedule: string }

/**
 * Helper to navigate to the cron overview page
 */
const navigateToCronOverview = async (page: Page, ref: string) => {
  await page.goto(toUrl(`/project/${ref}/integrations/cron/overview`))
}

/**
 * Helper to navigate to the cron jobs page and wait for it to load.
 * Assumes pg_cron is already enabled.
 */
const navigateToCronJobsPage = async (page: Page, ref: string) => {
  await page.goto(toUrl(`/project/${ref}/integrations/cron/jobs`))
  await expect(page.getByRole('grid')).toBeVisible({ timeout: 30000 })
}

const createJobViaAPI = async (page: Page, ref: string, jobName: string) => {
  // Make the request in the page context so that the job belongs to supabase_admin
  // Otherwise, tests that edit or delete jobs would fail
  await page.request.post(toUrl(`/api/platform/pg-meta/${ref}/query`), {
    failOnStatusCode: true,
    data: {
      query: `select cron.schedule('${jobName}', '*/30 * * * *', $$select 'test';$$);`,
    },
  })
}

const deleteJobViaAPI = async (page: Page, ref: string, jobName: string) => {
  // Make the request in the page context so that the it can delete jobs that belongs to supabase_admin
  await page.request.post(toUrl(`/api/platform/pg-meta/${ref}/query`), {
    failOnStatusCode: true,
    data: {
      query: `select cron.unschedule('${jobName}');`,
    },
  })
}

// Creates a cron job with an empty name (as can happen when a job is scheduled directly via SQL)
// and returns its generated jobid. Such jobs can only be referenced by id, not name.
const createUnnamedJobViaAPI = async (
  page: Page,
  ref: string,
  command: string
): Promise<number> => {
  const response = await page.request.post(toUrl(`/api/platform/pg-meta/${ref}/query`), {
    failOnStatusCode: true,
    data: {
      query: `select cron.schedule('', '*/30 * * * *', $$${command}$$) as jobid;`,
    },
  })
  const rows = (await response.json()) as Array<{ jobid: number }>
  return rows[0].jobid
}

const unscheduleJobByIdViaAPI = async (page: Page, ref: string, jobId: number) => {
  await page.request.post(toUrl(`/api/platform/pg-meta/${ref}/query`), {
    failOnStatusCode: true,
    data: {
      query: `select cron.unschedule(${jobId});`,
    },
  })
}

// Mirrors CRON_CLEANUP_JOB_NAME in @supabase/pg-meta (not a dependency of this package).
// The name is hardcoded in the product, so tests touching the cleanup feature contend on
// this single global name.
const CLEANUP_JOB_NAME = 'delete-job-run-details'

// Runs in the page context (supabase_admin) because cron.unschedule by name only finds jobs
// the calling role can manage, and the UI schedules the job as supabase_admin. Deletes
// tolerantly since the job may or may not exist.
const unscheduleCleanupJobIfExists = async (page: Page, ref: string) => {
  await page.request.post(toUrl(`/api/platform/pg-meta/${ref}/query`), {
    failOnStatusCode: true,
    data: {
      query: `do $$ begin
        if exists (select 1 from cron.job where jobname = '${CLEANUP_JOB_NAME}') then
          perform cron.unschedule('${CLEANUP_JOB_NAME}');
        end if;
      end $$;`,
    },
  })
}

test.describe('Cron Jobs', () => {
  test.beforeAll(async () => {
    await withFileOnceSetup(import.meta.url, async () => {
      await query('create extension if not exists pg_cron schema pg_catalog;')
    })
  })
  test.afterAll(async () => {
    await releaseFileOnceCleanup(import.meta.url)
  })
  test.describe('Cron Jobs Integration', () => {
    test.describe('Cron Jobs CRUD Operations', () => {
      test('can view cron jobs page', async ({ page, ref }) => {
        await navigateToCronJobsPage(page, ref)

        // Verify the page elements
        await expect(page.getByRole('button', { name: 'Create job' })).toBeVisible()
        await expect(page.getByRole('button', { name: 'Refresh' })).toBeVisible()
        await expect(page.getByPlaceholder('Search for a job')).toBeVisible()
      })

      test('can create a new cron job', async ({ page, ref }) => {
        const cronJobName = 'pw_cron_create_job'
        await navigateToCronJobsPage(page, ref)
        await using _ = await withSetupCleanup(
          async () => {
            // Nothing
          },
          async () => {
            await deleteJobViaAPI(page, ref, cronJobName)
          }
        )

        // Click create job button
        await page.getByRole('button', { name: 'Create job' }).click()

        // Wait for the dialog to open
        await expect(page.getByRole('heading', { name: 'Create a new cron job' })).toBeVisible()

        // Fill in job name using the input name attribute
        await page.locator('input[name="name"]').fill(cronJobName)

        // Click the "Every minute" preset button to set schedule
        await page.getByRole('button', { name: 'Every minute' }).click()

        // Fill in the SQL command using the Monaco editor
        await page.getByRole('code').click()
        await page.getByRole('textbox', { name: /Editor content/ }).fill("SELECT 'test';")

        // Save the job
        await page.getByRole('button', { name: 'Create cron job' }).click()

        // Wait for success toast
        await expect(page.getByText(/Successfully created cron job/)).toBeVisible({
          timeout: 10000,
        })

        // Verify the job appears in the list
        await expect(page.getByRole('row', { name: new RegExp(cronJobName) })).toBeVisible()
      })

      test('can search for cron jobs', async ({ page, ref }) => {
        const cronJobName = 'pw_cron_search_job'
        await using _ = await withSetupCleanup(
          async () => {
            await createJobViaAPI(page, ref, cronJobName)
          },
          async () => {
            await deleteJobViaAPI(page, ref, cronJobName)
          }
        )
        await navigateToCronJobsPage(page, ref)

        // Search for the test job
        const searchInput = page.getByPlaceholder('Search for a job')
        await searchInput.fill(cronJobName)
        await searchInput.press('Enter')

        // Should find the job
        await expect(page.getByRole('row', { name: new RegExp(cronJobName) })).toBeVisible()

        // Search for non-existent job
        await searchInput.fill('nonexistent_job_xyz_12345')
        await searchInput.press('Enter')

        // Should show empty state
        await expect(page.getByText('No cron jobs found')).toBeVisible()

        // Clear search by clearing the input
        await searchInput.clear()
        await searchInput.press('Enter')
      })

      test('can edit a cron job', async ({ page, ref }) => {
        const cronJobName = 'pw_cron_edit_job'
        await using _ = await withSetupCleanup(
          async () => {
            await createJobViaAPI(page, ref, cronJobName)
          },
          async () => {
            await deleteJobViaAPI(page, ref, cronJobName)
          }
        )
        await navigateToCronJobsPage(page, ref)

        // Find the test job row and right-click to open context menu
        const jobRow = page.getByRole('row', { name: new RegExp(cronJobName) })
        await jobRow.click({ button: 'right' })

        // Click "Edit job" in the context menu
        await page.getByRole('menuitem', { name: 'Edit job' }).click()

        // Wait for the edit sheet to open
        await expect(page.getByRole('heading', { name: `Edit ${cronJobName}` })).toBeVisible()

        // Note: Job names cannot be changed after creation, so we'll verify we can change the schedule
        // Click a different schedule preset
        await page.getByRole('button', { name: 'Every 5 minutes' }).click()

        // Save the changes
        await page.getByRole('button', { name: 'Save cron job' }).click()

        // Wait for success toast
        await expect(page.getByText(/Successfully updated cron job/)).toBeVisible({
          timeout: 10000,
        })
      })

      // Regression test for FE-3489: editing a cron job that has no name used to create a brand
      // new job (via cron.schedule) instead of updating the existing one. The edit now goes
      // through cron.alter_job by jobid, so the original job is updated in place.
      test('editing an unnamed cron job updates it in place instead of creating a new job', async ({
        page,
        ref,
      }) => {
        // Unique command so we can reliably find this job (it has no name to search by)
        const command = `select 'pw_unnamed_edit_job';`
        let jobId: number | undefined

        await using _ = await withSetupCleanup(
          async () => {
            jobId = await createUnnamedJobViaAPI(page, ref, command)
          },
          async () => {
            if (jobId !== undefined) await unscheduleJobByIdViaAPI(page, ref, jobId)
          }
        )

        // Unnamed jobs can't be edited from the list context menu, so edit from the detail page
        await page.goto(toUrl(`/project/${ref}/integrations/cron/jobs/${jobId}`))
        await page.getByRole('button', { name: 'Edit', exact: true }).click()

        // The sheet falls back to a generic title when the job has no name
        await expect(
          page.getByRole('heading', { name: 'Edit cron job' }),
          'Edit sheet should open for the unnamed job'
        ).toBeVisible({ timeout: 30000 })

        // Change the schedule and save
        await page.getByRole('button', { name: 'Every 5 minutes' }).click()
        await page.getByRole('button', { name: 'Save cron job' }).click()

        await expect(page.getByText(/Successfully updated cron job/)).toBeVisible({
          timeout: 10000,
        })

        // The original job should be updated in place: exactly one job with this command, and its
        // schedule should reflect the edit. Before the fix a second job would have been created.
        const jobs = await query<CronJobRow>(
          `select jobid, schedule from cron.job where command = $$${command}$$;`
        )
        expect(jobs, 'Editing should not create a duplicate job').toHaveLength(1)
        expect(jobs[0].jobid, 'The existing job should be the one updated').toBe(jobId)
        expect(jobs[0].schedule, 'The schedule should have been updated').toBe('*/5 * * * *')
      })

      test('can delete a cron job', async ({ page, ref }) => {
        const cronJobName = 'pw_cron_delete_job'
        let shouldCleanup = true
        await using _ = await withSetupCleanup(
          async () => {
            await createJobViaAPI(page, ref, cronJobName)
          },
          async () => {
            if (!shouldCleanup) return
            await deleteJobViaAPI(page, ref, cronJobName)
          }
        )
        await navigateToCronJobsPage(page, ref)

        // Find the row and right-click to open context menu
        const jobRow = page.getByRole('row', { name: new RegExp(cronJobName) })
        await jobRow.click({ button: 'right' })

        // Click "Delete job" in the context menu
        await page.getByRole('menuitem', { name: 'Delete job' }).click()

        // The modal requires typing the job name - fill it in
        await expect(page.getByRole('heading', { name: 'Delete this cron job' })).toBeVisible()
        await page.getByPlaceholder('Type in name of cron job').fill(cronJobName)

        // Click the delete button
        await page.getByRole('button', { name: `Delete cron job ${cronJobName}` }).click()

        // Wait for success toast and for the row to be removed from the grid
        await expect(page.getByText(/Successfully removed cron job/)).toBeVisible({
          timeout: 10000,
        })
        await expect(jobRow).not.toBeVisible({ timeout: 10000 })
        shouldCleanup = false
      })

      test('can view cron job run history', async ({ page, ref }) => {
        const cronJobName = 'pw_cron_history_job'
        await using _ = await withSetupCleanup(
          async () => {
            await createJobViaAPI(page, ref, cronJobName)
          },
          async () => {
            await deleteJobViaAPI(page, ref, cronJobName)
          }
        )
        await navigateToCronJobsPage(page, ref)

        // Click on the job row to view history (click on the name cell, not the action button)
        const jobRow = page.getByRole('row', { name: new RegExp(cronJobName) })
        await jobRow.getByRole('gridcell', { name: cronJobName }).click()

        // Should navigate to the job detail page
        await page.waitForURL(/.*\/integrations\/cron\/jobs\/\d+/)

        // Verify we're on the job history page - should show the job name in the heading
        await expect(page.getByRole('heading', { name: cronJobName })).toBeVisible()

        // Go back to jobs list
        await page.goBack()
        await page.waitForLoadState('networkidle')
      })
    })

    test.describe('Cron Jobs Page Features', () => {
      test('refresh button reloads cron jobs', async ({ page, ref }) => {
        await navigateToCronJobsPage(page, ref)

        // Click refresh - it should not throw an error
        await page.getByRole('button', { name: 'Refresh' }).click()

        // The grid should still be visible after refresh
        await expect(page.getByRole('grid')).toBeVisible()
      })

      test('navigation tabs work correctly', async ({ page, ref }) => {
        await navigateToCronOverview(page, ref)
        await navigateToCronJobsPage(page, ref)

        // Click on Jobs tab
        await page.getByRole('link', { name: 'Jobs' }).click()
        await page.waitForURL(/.*\/integrations\/cron\/jobs/)

        // Verify we're on the jobs page
        await expect(page.getByRole('button', { name: 'Create job' })).toBeVisible()

        // Click on Overview tab (use exact match to avoid ambiguity)
        await page.getByRole('link', { name: 'Overview', exact: true }).click()
        await page.waitForURL(/.*\/integrations\/cron\/overview/)

        // Verify we're on the overview page
        await expect(page.getByText('Schedule recurring Jobs in Postgres')).toBeVisible()
      })
    })
  })

  // Both groups below schedule the product-wide delete-job-run-details job, so they contend
  // on a single global job name. Opt out of fullyParallel for them (sequential, same worker)
  // to avoid cross-worker races on that name.
  test.describe('Cleanup Job Scheduling', () => {
    test.describe.configure({ mode: 'default' })

    // Regression test for FE-3724: the "Enable cleanup" header button schedules the daily
    // job_run_details cleanup job without waiting for the high-query-cost banner. Covers the
    // full cycle in one scenario because the states are causally chained: schedule via the
    // dialog (button hides), then delete the job and verify the button reappears without a
    // reload (the cache-invalidation regression).
    test.describe('Enable Cleanup Button', () => {
      test('schedules the cleanup job and reappears after the job is deleted', async ({
        page,
        ref,
      }) => {
        await using _ = await withSetupCleanup(
          async () => {
            await unscheduleCleanupJobIfExists(page, ref)
          },
          async () => {
            await unscheduleCleanupJobIfExists(page, ref)
          }
        )
        await navigateToCronJobsPage(page, ref)

        const enableCleanupButton = page.getByRole('button', { name: 'Enable cleanup' })
        await expect(
          enableCleanupButton,
          'Enable cleanup button should be visible when the cleanup job does not exist'
        ).toBeVisible({ timeout: 30000 })

        await enableCleanupButton.click()

        const dialog = page.getByRole('dialog')
        await expect(
          dialog.getByRole('heading', { name: 'Enable automatic cleanup' }),
          'Dialog should open with the enable cleanup title'
        ).toBeVisible()
        await expect(
          dialog.getByRole('combobox'),
          'Retention select should default to 7 days'
        ).toContainText('Older than 7 days')
        await expect(
          dialog.getByText(/cron\.schedule/),
          'Dialog should preview the SQL that will run'
        ).toBeVisible()

        await dialog.getByRole('button', { name: 'Enable cleanup' }).click()

        await expect(
          page.getByText('Scheduled daily cleanup job.'),
          'Success toast should appear after scheduling'
        ).toBeVisible({ timeout: 15000 })

        // The scheduled job shows up in the grid and the header button hides, both without a reload
        const jobRow = page.getByRole('row', { name: new RegExp(CLEANUP_JOB_NAME) })
        await expect(jobRow, 'Scheduled cleanup job should appear in the grid').toBeVisible({
          timeout: 10000,
        })
        await expect(
          enableCleanupButton,
          'Enable cleanup button should hide once the cleanup job exists'
        ).toHaveCount(0)

        // Delete the job from the grid and verify the button comes back without a reload
        await jobRow.click({ button: 'right' })
        await page.getByRole('menuitem', { name: 'Delete job' }).click()
        await expect(page.getByRole('heading', { name: 'Delete this cron job' })).toBeVisible()
        await page.getByPlaceholder('Type in name of cron job').fill(CLEANUP_JOB_NAME)
        await page.getByRole('button', { name: `Delete cron job ${CLEANUP_JOB_NAME}` }).click()

        await expect(page.getByText(/Successfully removed cron job/)).toBeVisible({
          timeout: 10000,
        })
        await expect(jobRow).not.toBeVisible({ timeout: 10000 })
        await expect(
          enableCleanupButton,
          'Enable cleanup button should reappear after the cleanup job is deleted'
        ).toBeVisible({ timeout: 10000 })
      })
    })

    test.describe('High Query Cost Banner', () => {
      test('shows banner and still displays cron jobs when query cost exceeds threshold', async ({
        page,
        ref,
      }) => {
        const testJobName = 'pw_high_cost_test_job'
        await navigateToCronJobsPage(page, ref)
        await using _ = await withSetupCleanup(
          async () => {
            await createJobViaAPI(page, ref, testJobName)
          },
          async () => {
            await deleteJobViaAPI(page, ref, testJobName)
          }
        )

        // Now set up the mock for the EXPLAIN query (preflight check) to return high cost
        // This simulates the scenario where cron.job_run_details is too large
        await page.route('**/pg-meta/*/query**', async (route) => {
          const request = route.request()
          const postData = request.postDataJSON()

          // Intercept EXPLAIN queries for the cron jobs query (the preflight check)
          if (
            postData?.query?.toLowerCase().startsWith('explain') &&
            postData?.query?.includes('cron.job')
          ) {
            // Return a mock EXPLAIN result with very high cost (over 100,000 threshold)
            await route.fulfill({
              status: 200,
              contentType: 'application/json',
              body: JSON.stringify([
                {
                  'QUERY PLAN':
                    'Nested Loop Left Join  (cost=0.00..500000.00 rows=1000000 width=100)',
                },
              ]),
            })
          } else {
            await route.continue()
          }
        })

        // Navigate to the cron jobs page - this will trigger the mocked preflight check
        await page.goto(toUrl(`/project/${ref}/integrations/cron/jobs`))

        // Wait for the grid to load - this verifies cron jobs are still visible in minimal mode
        await expect(page.getByRole('grid')).toBeVisible({ timeout: 30000 })

        // Should show the overflow banner (minimal mode indicator)
        await expect(
          page.getByText('Last run for each cron job omitted due to high query cost')
        ).toBeVisible({
          timeout: 15000,
        })

        // The test job should still be visible in the grid (minimal mode shows jobs without last run)
        await expect(page.getByRole('row', { name: new RegExp(testJobName) })).toBeVisible()

        // An alert about high query costs should be visible and contain a "Learn more" button
        await expect(
          page.getByRole('alert').getByRole('button', { name: 'Learn more' })
        ).toBeVisible()

        // Remove the route mock for subsequent tests
        await page.unroute('**/pg-meta/*/query**')
      })

      test('Learn more dialog shows cleanup options', async ({ page, ref }) => {
        // Set up the mock again for this test
        await page.route('**/pg-meta/*/query**', async (route) => {
          const request = route.request()
          const postData = request.postDataJSON()

          if (
            postData?.query?.toLowerCase().startsWith('explain') &&
            postData?.query?.includes('cron.job')
          ) {
            await route.fulfill({
              status: 200,
              contentType: 'application/json',
              body: JSON.stringify([
                {
                  'QUERY PLAN':
                    'Nested Loop Left Join  (cost=0.00..500000.00 rows=1000000 width=100)',
                },
              ]),
            })
          } else {
            await route.continue()
          }
        })

        await page.goto(toUrl(`/project/${ref}/integrations/cron/jobs`))

        // Wait for the banner to appear
        await expect(
          page.getByText('Last run for each cron job omitted due to high query cost')
        ).toBeVisible({
          timeout: 15000,
        })

        // Click the "Learn more" button in the alert to open the dialog
        await page.getByRole('alert').getByRole('button', { name: 'Learn more' }).click()

        // The dialog should open with the explanation
        await expect(
          page.getByRole('heading', { name: 'Last run for cron jobs omitted for overview' })
        ).toBeVisible()

        // Should explain the issue
        await expect(
          page.getByText(/the estimated query cost exceeds safety thresholds/)
        ).toBeVisible()

        // Should show Step 1 with cleanup options
        await expect(page.getByText('Step 1: Delete older entries')).toBeVisible()
        await expect(page.getByRole('button', { name: 'Delete rows now' })).toBeVisible()

        // Should show the interval selector
        await expect(page.getByRole('combobox')).toBeVisible()

        // Should show Step 2 (disabled until step 1 is complete)
        await expect(page.getByText('Step 2: Schedule an automated cleanup')).toBeVisible()
        await expect(
          page.getByText('Complete step 1 to enable scheduling a daily cleanup job')
        ).toBeVisible()

        // Remove the route mock
        await page.unroute('**/pg-meta/*/query**')
      })

      test('cleanup workflow: delete rows and schedule cleanup job', async ({ page, ref }) => {
        // This flow schedules the delete-job-run-details job; remove it afterwards so tests
        // that depend on the job's absence (Enable Cleanup Button) start clean. Deleting via
        // the UI here causes pointer events issues with Radix dialogs, hence via SQL.
        await using _ = await withSetupCleanup(
          async () => {},
          async () => {
            await unscheduleCleanupJobIfExists(page, ref)
          }
        )

        // Set up the mock for the high cost scenario
        await page.route('**/pg-meta/*/query**', async (route) => {
          const request = route.request()
          const postData = request.postDataJSON()

          if (
            postData?.query?.toLowerCase().startsWith('explain') &&
            postData?.query?.includes('cron.job')
          ) {
            await route.fulfill({
              status: 200,
              contentType: 'application/json',
              body: JSON.stringify([
                {
                  'QUERY PLAN':
                    'Nested Loop Left Join  (cost=0.00..500000.00 rows=1000000 width=100)',
                },
              ]),
            })
          } else {
            await route.continue()
          }
        })

        await page.goto(toUrl(`/project/${ref}/integrations/cron/jobs`))

        // Wait for the banner and click Learn more
        await expect(
          page.getByText('Last run for each cron job omitted due to high query cost')
        ).toBeVisible({
          timeout: 15000,
        })
        await page.getByRole('alert').getByRole('button', { name: 'Learn more' }).click()

        // Wait for dialog to open
        await expect(
          page.getByRole('heading', { name: 'Last run for cron jobs omitted for overview' })
        ).toBeVisible()

        // Step 1: Click "Delete rows now" to run the cleanup
        await page.getByRole('button', { name: 'Delete rows now' }).click()

        // Wait for Step 1 to complete - should show success message
        // Note: In a fresh DB, there might be 0 rows deleted, which is still success
        await expect(page.getByText(/Successfully deleted \d+ rows/)).toBeVisible({
          timeout: 30000,
        })

        // Step 2 should now be enabled - the "Schedule cleanup job" button should be visible
        await expect(page.getByRole('button', { name: 'Schedule cleanup job' })).toBeVisible()

        // Should show the SQL preview for the cleanup job
        await expect(page.getByText(/cron\.schedule/)).toBeVisible()

        // Click "Schedule cleanup job"
        await page.getByRole('button', { name: 'Schedule cleanup job' }).click()

        // Wait for Step 2 to complete - should show success message
        await expect(page.getByText('Daily cleanup job scheduled successfully')).toBeVisible({
          timeout: 15000,
        })

        // Clean up: remove the route mock; the scheduled delete-job-run-details job is
        // removed by the withSetupCleanup teardown at the top of this test.
        await page.unroute('**/pg-meta/*/query**')
      })
    })
  })
})