46 lines
1.4 KiB
MySQL
46 lines
1.4 KiB
MySQL
|
-- Migration 024: Cron Execution Logs
|
||
|
|
-- Tracks cron job execution history for monitoring
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS public.cron_execution_logs (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
job_name TEXT NOT NULL,
|
||
|
|
status TEXT NOT NULL DEFAULT 'running' CHECK (status IN ('running', 'completed', 'failed')),
|
||
|
|
started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
completed_at TIMESTAMPTZ,
|
||
|
|
duration_ms INTEGER,
|
||
|
|
result JSONB,
|
||
|
|
error_message TEXT,
|
||
|
|
triggered_by TEXT DEFAULT 'cron'
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Index for quick lookups
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_cron_logs_job_name ON public.cron_execution_logs (job_name, started_at DESC);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_cron_logs_started ON public.cron_execution_logs (started_at DESC);
|
||
|
|
|
||
|
|
-- Enable RLS
|
||
|
|
ALTER TABLE public.cron_execution_logs ENABLE ROW LEVEL SECURITY;
|
||
|
|
|
||
|
|
-- Only admins can read cron logs
|
||
|
|
CREATE POLICY "Admins can read cron logs"
|
||
|
|
ON public.cron_execution_logs
|
||
|
|
FOR SELECT
|
||
|
|
TO authenticated
|
||
|
|
USING (
|
||
|
|
EXISTS (
|
||
|
|
SELECT 1 FROM public.members
|
||
|
|
WHERE members.user_id = auth.uid()
|
||
|
|
AND members.role = 'admin'
|
||
|
|
)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Service role can insert/update (for cron endpoints)
|
||
|
|
CREATE POLICY "Service role can manage cron logs"
|
||
|
|
ON public.cron_execution_logs
|
||
|
|
FOR ALL
|
||
|
|
TO service_role
|
||
|
|
USING (true)
|
||
|
|
WITH CHECK (true);
|
||
|
|
|
||
|
|
-- Auto-cleanup: keep only 90 days of logs
|
||
|
|
COMMENT ON TABLE public.cron_execution_logs IS 'Tracks cron job execution history. Entries older than 90 days should be periodically purged.';
|