konnosDashboard
CronJobsTab.useCleanupActions.ts208 lines · main
1import {
2 getDeleteOldCronJobRunDetailsByCtidSql,
3 getJobRunDetailsPageCountSql,
4} from '@supabase/pg-meta'
5import { useCallback, useRef, useState } from 'react'
6import { toast } from 'sonner'
7
8import { CLEANUP_INTERVALS } from './CronJobsTab.constants'
9import type { ConnectionVars } from '@/data/common.types'
10import {
11 CTID_BATCH_PAGE_SIZE,
12 validatePageNumber,
13} from '@/data/database-cron-jobs/database-cron-jobs.utils'
14import {
15 getDeleteOldCronJobRunDetailsByCtidKey,
16 getJobRunDetailsPageCountKey,
17} from '@/data/database-cron-jobs/keys'
18import { useScheduleCronJobRunDetailsCleanupMutation } from '@/data/database-cron-jobs/schedule-clean-up-mutation'
19import { useExecuteSqlMutation } from '@/data/sql/execute-sql-mutation'
20
21// Delay between batches to allow other queries to proceed (in milliseconds)
22const BATCH_DELAY_MS = 100
23
24type UseCronJobsCleanupActionsOptions = ConnectionVars
25
26export interface BatchDeletionProgress {
27 currentBatch: number
28 totalBatches: number
29 totalRowsDeleted: number
30}
31
32export type CleanupState =
33 | { status: 'idle' }
34 | { status: 'deleting'; progress: BatchDeletionProgress }
35 | { status: 'delete-success'; totalRowsDeleted: number }
36 | { status: 'delete-error'; error: string }
37
38export const useCronJobsCleanupActions = ({
39 projectRef,
40 connectionString,
41}: UseCronJobsCleanupActionsOptions) => {
42 const [cleanupInterval, setCleanupInterval] = useState(CLEANUP_INTERVALS[0].value)
43 const [cleanupState, setCleanupState] = useState<CleanupState>({ status: 'idle' })
44
45 // Ref to track cancellation
46 const cancelledRef = useRef(false)
47
48 const { mutateAsync: executeSql } = useExecuteSqlMutation({
49 onError: () => {}, // Error handled inline
50 })
51
52 const {
53 mutate: scheduleCronJobCleanup,
54 isPending: isScheduling,
55 isSuccess: isScheduleSuccess,
56 } = useScheduleCronJobRunDetailsCleanupMutation()
57
58 /**
59 * Run batched deletion using ctid ranges.
60 * This approach scans the table in page chunks to avoid:
61 * - Buffer cache pollution from full table scans
62 * - Long-running transactions that block vacuum
63 * - Lock accumulation from deleting millions of rows at once
64 */
65 const runBatchedDeletion = useCallback(
66 async (interval: string) => {
67 if (!projectRef) {
68 console.error('[CronJobsTab > batch deletion] Project reference is required')
69 toast.error('There was an error running the cleanup. Please try again.')
70 return
71 }
72
73 cancelledRef.current = false
74
75 try {
76 // Step 1: Get the total number of pages in the table
77 setCleanupState({
78 status: 'deleting',
79 progress: { currentBatch: 0, totalBatches: 0, totalRowsDeleted: 0 },
80 })
81
82 const pageCountResult = await executeSql({
83 projectRef,
84 connectionString,
85 sql: getJobRunDetailsPageCountSql(),
86 queryKey: getJobRunDetailsPageCountKey(projectRef),
87 })
88
89 const rawTotalPages = pageCountResult.result?.[0]?.num_pages ?? 0
90 const totalPages = Number(rawTotalPages)
91 if (!Number.isFinite(totalPages) || totalPages < 0) {
92 throw new Error(
93 `[CronJobs > cleanup actions] Invalid page count returned: ${rawTotalPages}`
94 )
95 }
96
97 if (totalPages === 0) {
98 setCleanupState({ status: 'delete-success', totalRowsDeleted: 0 })
99 toast.success('The job_run_details table is empty.')
100 return
101 }
102
103 const totalBatches = Math.ceil(totalPages / CTID_BATCH_PAGE_SIZE)
104 let totalRowsDeleted = 0
105
106 // Step 2: Iterate through pages in batches
107 for (let batch = 0; batch < totalBatches; batch++) {
108 // Check for cancellation
109 if (cancelledRef.current) {
110 setCleanupState({ status: 'idle' })
111 toast.info('Deletion cancelled.')
112 return
113 }
114
115 const startPage = batch * CTID_BATCH_PAGE_SIZE
116 const endPage = Math.min((batch + 1) * CTID_BATCH_PAGE_SIZE, totalPages + 1)
117
118 validatePageNumber(startPage, 'startPage')
119 validatePageNumber(endPage, 'endPage')
120
121 setCleanupState({
122 status: 'deleting',
123 progress: {
124 currentBatch: batch + 1,
125 totalBatches,
126 totalRowsDeleted,
127 },
128 })
129
130 const deleteResult = await executeSql({
131 projectRef,
132 connectionString,
133 sql: getDeleteOldCronJobRunDetailsByCtidSql(interval, startPage, endPage),
134 queryKey: getDeleteOldCronJobRunDetailsByCtidKey(projectRef, interval, startPage),
135 })
136
137 const deletedCount = deleteResult.result?.[0]?.deleted_count ?? 0
138 totalRowsDeleted += deletedCount
139
140 if (cancelledRef.current) {
141 setCleanupState({ status: 'idle' })
142 toast.info('Deletion cancelled.')
143 return
144 }
145
146 if (batch < totalBatches - 1) {
147 await new Promise((resolve) => setTimeout(resolve, BATCH_DELAY_MS))
148 }
149 }
150
151 setCleanupState({ status: 'delete-success', totalRowsDeleted })
152 toast.success(
153 `Deleted ${totalRowsDeleted.toLocaleString()} cron job runs older than ${interval}.`
154 )
155 } catch (error) {
156 console.error('[CronJobs] Batch deletion failed with error: %O', error)
157 const errorMessage = error instanceof Error ? error.message : 'Unknown error'
158 setCleanupState({ status: 'delete-error', error: errorMessage })
159 toast.error('Running the cleanup failed. Please try again.')
160 }
161 },
162 [projectRef, connectionString, executeSql]
163 )
164
165 /**
166 * Schedule a daily cleanup job.
167 * This should only be called after a successful initial deletion.
168 */
169 const scheduleCleanup = useCallback(
170 async ({ interval, onSuccess }: { interval: string; onSuccess?: () => void }) => {
171 if (!projectRef) {
172 console.error('[CronJobsTab > schedule cleanup] Project reference is required')
173 toast.error('There was an error scheduling the cleanup. Please try again.')
174 return
175 }
176
177 scheduleCronJobCleanup(
178 { projectRef, connectionString, interval },
179 {
180 onSuccess: () => {
181 toast.success('Scheduled daily cleanup job.')
182 onSuccess?.()
183 },
184 }
185 )
186 },
187 [connectionString, projectRef, scheduleCronJobCleanup]
188 )
189
190 /**
191 * Cancel an in-progress deletion.
192 */
193 const cancelDeletion = useCallback(() => {
194 cancelledRef.current = true
195 setCleanupState({ status: 'idle' })
196 }, [])
197
198 return {
199 cleanupInterval,
200 cleanupState,
201 isScheduling,
202 isScheduleSuccess,
203 setCleanupInterval,
204 runBatchedDeletion,
205 scheduleCleanup,
206 cancelDeletion,
207 }
208}