45 lines
1.2 KiB
MySQL
45 lines
1.2 KiB
MySQL
|
-- Migration 025: Bulk Email Broadcasts
|
||
|
|
-- Tracks bulk email campaigns sent to members
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS public.bulk_emails (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
subject TEXT NOT NULL,
|
||
|
|
body TEXT NOT NULL,
|
||
|
|
recipient_filter JSONB DEFAULT '{"target": "all"}'::jsonb,
|
||
|
|
total_recipients INTEGER DEFAULT 0,
|
||
|
|
sent_count INTEGER DEFAULT 0,
|
||
|
|
failed_count INTEGER DEFAULT 0,
|
||
|
|
status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'sending', 'completed', 'failed')),
|
||
|
|
sent_by UUID REFERENCES auth.users(id),
|
||
|
|
sent_by_name TEXT,
|
||
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
|
sent_at TIMESTAMPTZ,
|
||
|
|
completed_at TIMESTAMPTZ
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Index for listing
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_bulk_emails_created ON public.bulk_emails (created_at DESC);
|
||
|
|
|
||
|
|
-- Enable RLS
|
||
|
|
ALTER TABLE public.bulk_emails ENABLE ROW LEVEL SECURITY;
|
||
|
|
|
||
|
|
-- Only admins can manage bulk emails
|
||
|
|
CREATE POLICY "Admins can manage bulk emails"
|
||
|
|
ON public.bulk_emails
|
||
|
|
FOR ALL
|
||
|
|
TO authenticated
|
||
|
|
USING (
|
||
|
|
EXISTS (
|
||
|
|
SELECT 1 FROM public.members
|
||
|
|
WHERE members.user_id = auth.uid()
|
||
|
|
AND members.role = 'admin'
|
||
|
|
)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE POLICY "Service role full access to bulk emails"
|
||
|
|
ON public.bulk_emails
|
||
|
|
FOR ALL
|
||
|
|
TO service_role
|
||
|
|
USING (true)
|
||
|
|
WITH CHECK (true);
|