787 lines
25 KiB
PL/PgSQL
787 lines
25 KiB
PL/PgSQL
-- Monaco USA Portal 2026 - Initial Database Schema
|
|
-- Run this migration to set up all tables, views, triggers, and RLS policies
|
|
|
|
-- ============================================
|
|
-- MEMBERSHIP STATUSES (Admin-configurable)
|
|
-- ============================================
|
|
CREATE TABLE public.membership_statuses (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name TEXT NOT NULL UNIQUE,
|
|
display_name TEXT NOT NULL,
|
|
color TEXT NOT NULL DEFAULT '#6b7280',
|
|
description TEXT,
|
|
is_default BOOLEAN DEFAULT FALSE,
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Default statuses
|
|
INSERT INTO public.membership_statuses (name, display_name, color, description, is_default, sort_order) VALUES
|
|
('pending', 'Pending', '#eab308', 'New member, awaiting dues payment', true, 1),
|
|
('active', 'Active', '#22c55e', 'Dues paid, full access', false, 2),
|
|
('inactive', 'Inactive', '#6b7280', 'Lapsed membership or suspended', false, 3),
|
|
('expired', 'Expired', '#ef4444', 'Membership terminated', false, 4);
|
|
|
|
-- ============================================
|
|
-- MEMBERSHIP TYPES (Admin-configurable pricing)
|
|
-- ============================================
|
|
CREATE TABLE public.membership_types (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name TEXT NOT NULL UNIQUE,
|
|
display_name TEXT NOT NULL,
|
|
annual_dues DECIMAL(10,2) NOT NULL,
|
|
description TEXT,
|
|
is_default BOOLEAN DEFAULT FALSE,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Default membership types
|
|
INSERT INTO public.membership_types (name, display_name, annual_dues, description, is_default, sort_order) VALUES
|
|
('regular', 'Regular Member', 50.00, 'Standard individual membership', true, 1),
|
|
('student', 'Student', 25.00, 'For students with valid ID', false, 2),
|
|
('senior', 'Senior (65+)', 35.00, 'For members 65 years and older', false, 3),
|
|
('family', 'Family', 75.00, 'Household membership', false, 4),
|
|
('honorary', 'Honorary Member', 0.00, 'Granted by the board', false, 5);
|
|
|
|
-- ============================================
|
|
-- MEMBERS TABLE
|
|
-- ============================================
|
|
CREATE TABLE public.members (
|
|
id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
member_id TEXT UNIQUE NOT NULL,
|
|
|
|
-- Personal Info
|
|
first_name TEXT NOT NULL,
|
|
last_name TEXT NOT NULL,
|
|
email TEXT UNIQUE NOT NULL,
|
|
phone TEXT NOT NULL,
|
|
date_of_birth DATE NOT NULL,
|
|
address TEXT NOT NULL,
|
|
nationality TEXT[] NOT NULL DEFAULT '{}',
|
|
|
|
-- Membership
|
|
role TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('member', 'board', 'admin')),
|
|
membership_status_id UUID REFERENCES public.membership_statuses(id),
|
|
membership_type_id UUID REFERENCES public.membership_types(id),
|
|
member_since DATE DEFAULT CURRENT_DATE,
|
|
|
|
-- Profile
|
|
avatar_url TEXT,
|
|
|
|
-- Admin
|
|
notes TEXT,
|
|
|
|
-- Timestamps
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Auto-generate member_id trigger
|
|
CREATE OR REPLACE FUNCTION generate_member_id()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
next_num INTEGER;
|
|
BEGIN
|
|
SELECT COALESCE(MAX(CAST(SUBSTRING(member_id FROM 6) AS INTEGER)), 0) + 1
|
|
INTO next_num
|
|
FROM public.members;
|
|
|
|
NEW.member_id := 'MUSA-' || LPAD(next_num::TEXT, 4, '0');
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER set_member_id
|
|
BEFORE INSERT ON public.members
|
|
FOR EACH ROW
|
|
WHEN (NEW.member_id IS NULL)
|
|
EXECUTE FUNCTION generate_member_id();
|
|
|
|
-- Update timestamp trigger
|
|
CREATE OR REPLACE FUNCTION update_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER members_updated_at
|
|
BEFORE UPDATE ON public.members
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at();
|
|
|
|
-- ============================================
|
|
-- DUES PAYMENTS
|
|
-- ============================================
|
|
CREATE TABLE public.dues_payments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
member_id UUID NOT NULL REFERENCES public.members(id) ON DELETE CASCADE,
|
|
|
|
amount DECIMAL(10,2) NOT NULL,
|
|
currency TEXT DEFAULT 'EUR',
|
|
payment_date DATE NOT NULL,
|
|
due_date DATE NOT NULL,
|
|
payment_method TEXT DEFAULT 'bank_transfer',
|
|
reference TEXT,
|
|
notes TEXT,
|
|
|
|
recorded_by UUID NOT NULL REFERENCES public.members(id),
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Auto-calculate due_date (1 year from payment)
|
|
CREATE OR REPLACE FUNCTION calculate_due_date()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF NEW.due_date IS NULL THEN
|
|
NEW.due_date := NEW.payment_date + INTERVAL '1 year';
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER set_due_date
|
|
BEFORE INSERT ON public.dues_payments
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION calculate_due_date();
|
|
|
|
-- Auto-update member status to active after payment
|
|
CREATE OR REPLACE FUNCTION update_member_status_on_payment()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
active_status_id UUID;
|
|
BEGIN
|
|
SELECT id INTO active_status_id
|
|
FROM public.membership_statuses
|
|
WHERE name = 'active';
|
|
|
|
UPDATE public.members
|
|
SET membership_status_id = active_status_id,
|
|
updated_at = NOW()
|
|
WHERE id = NEW.member_id;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER activate_member_on_payment
|
|
AFTER INSERT ON public.dues_payments
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_member_status_on_payment();
|
|
|
|
-- ============================================
|
|
-- EVENT TYPES (Admin-configurable)
|
|
-- ============================================
|
|
CREATE TABLE public.event_types (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name TEXT NOT NULL UNIQUE,
|
|
display_name TEXT NOT NULL,
|
|
color TEXT NOT NULL DEFAULT '#3b82f6',
|
|
icon TEXT,
|
|
description TEXT,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Default event types
|
|
INSERT INTO public.event_types (name, display_name, color, icon, sort_order) VALUES
|
|
('social', 'Social Event', '#10b981', 'party-popper', 1),
|
|
('meeting', 'Meeting', '#6366f1', 'users', 2),
|
|
('fundraiser', 'Fundraiser', '#f59e0b', 'heart-handshake', 3),
|
|
('workshop', 'Workshop', '#8b5cf6', 'graduation-cap', 4),
|
|
('gala', 'Gala/Formal', '#ec4899', 'sparkles', 5),
|
|
('other', 'Other', '#6b7280', 'calendar', 6);
|
|
|
|
-- ============================================
|
|
-- EVENTS
|
|
-- ============================================
|
|
CREATE TABLE public.events (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
-- Basic Info
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
event_type_id UUID REFERENCES public.event_types(id),
|
|
|
|
-- Date/Time
|
|
start_datetime TIMESTAMPTZ NOT NULL,
|
|
end_datetime TIMESTAMPTZ NOT NULL,
|
|
all_day BOOLEAN DEFAULT FALSE,
|
|
timezone TEXT DEFAULT 'Europe/Monaco',
|
|
|
|
-- Location
|
|
location TEXT,
|
|
location_url TEXT,
|
|
|
|
-- Capacity
|
|
max_attendees INTEGER,
|
|
max_guests_per_member INTEGER DEFAULT 1,
|
|
|
|
-- Pricing
|
|
is_paid BOOLEAN DEFAULT FALSE,
|
|
member_price DECIMAL(10,2) DEFAULT 0,
|
|
non_member_price DECIMAL(10,2) DEFAULT 0,
|
|
pricing_notes TEXT,
|
|
|
|
-- Visibility
|
|
visibility TEXT NOT NULL DEFAULT 'members'
|
|
CHECK (visibility IN ('public', 'members', 'board', 'admin')),
|
|
status TEXT NOT NULL DEFAULT 'published'
|
|
CHECK (status IN ('draft', 'published', 'cancelled', 'completed')),
|
|
|
|
-- Media
|
|
cover_image_url TEXT,
|
|
|
|
-- Meta
|
|
created_by UUID NOT NULL REFERENCES public.members(id),
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TRIGGER events_updated_at
|
|
BEFORE UPDATE ON public.events
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at();
|
|
|
|
-- ============================================
|
|
-- EVENT RSVPs (Members)
|
|
-- ============================================
|
|
CREATE TABLE public.event_rsvps (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
event_id UUID NOT NULL REFERENCES public.events(id) ON DELETE CASCADE,
|
|
member_id UUID NOT NULL REFERENCES public.members(id) ON DELETE CASCADE,
|
|
|
|
status TEXT NOT NULL DEFAULT 'confirmed'
|
|
CHECK (status IN ('confirmed', 'declined', 'maybe', 'waitlist', 'cancelled')),
|
|
guest_count INTEGER DEFAULT 0,
|
|
guest_names TEXT[],
|
|
notes TEXT,
|
|
|
|
-- Payment
|
|
payment_status TEXT DEFAULT 'not_required'
|
|
CHECK (payment_status IN ('not_required', 'pending', 'paid')),
|
|
payment_reference TEXT,
|
|
payment_amount DECIMAL(10,2),
|
|
|
|
-- Attendance
|
|
attended BOOLEAN DEFAULT FALSE,
|
|
checked_in_at TIMESTAMPTZ,
|
|
checked_in_by UUID REFERENCES public.members(id),
|
|
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
UNIQUE(event_id, member_id)
|
|
);
|
|
|
|
CREATE TRIGGER event_rsvps_updated_at
|
|
BEFORE UPDATE ON public.event_rsvps
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at();
|
|
|
|
-- ============================================
|
|
-- EVENT RSVPs (Public/Non-members)
|
|
-- ============================================
|
|
CREATE TABLE public.event_rsvps_public (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
event_id UUID NOT NULL REFERENCES public.events(id) ON DELETE CASCADE,
|
|
|
|
full_name TEXT NOT NULL,
|
|
email TEXT NOT NULL,
|
|
phone TEXT,
|
|
|
|
status TEXT NOT NULL DEFAULT 'confirmed'
|
|
CHECK (status IN ('confirmed', 'declined', 'maybe', 'waitlist', 'cancelled')),
|
|
guest_count INTEGER DEFAULT 0,
|
|
guest_names TEXT[],
|
|
|
|
-- Payment
|
|
payment_status TEXT DEFAULT 'not_required'
|
|
CHECK (payment_status IN ('not_required', 'pending', 'paid')),
|
|
payment_reference TEXT,
|
|
payment_amount DECIMAL(10,2),
|
|
|
|
-- Attendance
|
|
attended BOOLEAN DEFAULT FALSE,
|
|
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
UNIQUE(event_id, email)
|
|
);
|
|
|
|
CREATE TRIGGER event_rsvps_public_updated_at
|
|
BEFORE UPDATE ON public.event_rsvps_public
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at();
|
|
|
|
-- ============================================
|
|
-- DOCUMENT CATEGORIES
|
|
-- ============================================
|
|
CREATE TABLE public.document_categories (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name TEXT NOT NULL UNIQUE,
|
|
display_name TEXT NOT NULL,
|
|
description TEXT,
|
|
icon TEXT,
|
|
sort_order INTEGER DEFAULT 0,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Default categories
|
|
INSERT INTO public.document_categories (name, display_name, icon, sort_order) VALUES
|
|
('meeting_minutes', 'Meeting Minutes', 'file-text', 1),
|
|
('governance', 'Governance & Bylaws', 'scale', 2),
|
|
('legal', 'Legal Documents', 'briefcase', 3),
|
|
('financial', 'Financial Reports', 'dollar-sign', 4),
|
|
('member_resources', 'Member Resources', 'book-open', 5),
|
|
('forms', 'Forms & Templates', 'clipboard', 6),
|
|
('other', 'Other Documents', 'file', 7);
|
|
|
|
-- ============================================
|
|
-- DOCUMENTS
|
|
-- ============================================
|
|
CREATE TABLE public.documents (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
category_id UUID REFERENCES public.document_categories(id),
|
|
|
|
-- File Info
|
|
file_path TEXT NOT NULL,
|
|
file_name TEXT NOT NULL,
|
|
file_size INTEGER NOT NULL,
|
|
mime_type TEXT NOT NULL,
|
|
|
|
-- Visibility
|
|
visibility TEXT NOT NULL DEFAULT 'members'
|
|
CHECK (visibility IN ('public', 'members', 'board', 'admin')),
|
|
allowed_member_ids UUID[],
|
|
|
|
-- Version tracking
|
|
version INTEGER DEFAULT 1,
|
|
replaces_document_id UUID REFERENCES public.documents(id),
|
|
|
|
-- Meeting-specific fields
|
|
meeting_date DATE,
|
|
meeting_attendees UUID[],
|
|
|
|
-- Meta
|
|
uploaded_by UUID NOT NULL REFERENCES public.members(id),
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TRIGGER documents_updated_at
|
|
BEFORE UPDATE ON public.documents
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at();
|
|
|
|
-- ============================================
|
|
-- APP SETTINGS (Unified key-value store)
|
|
-- ============================================
|
|
CREATE TABLE public.app_settings (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
category TEXT NOT NULL,
|
|
setting_key TEXT NOT NULL,
|
|
setting_value JSONB NOT NULL,
|
|
setting_type TEXT NOT NULL DEFAULT 'text'
|
|
CHECK (setting_type IN ('text', 'number', 'boolean', 'json', 'array')),
|
|
display_name TEXT NOT NULL,
|
|
description TEXT,
|
|
is_public BOOLEAN DEFAULT FALSE,
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_by UUID REFERENCES public.members(id),
|
|
|
|
UNIQUE(category, setting_key)
|
|
);
|
|
|
|
-- Default settings
|
|
INSERT INTO public.app_settings (category, setting_key, setting_value, setting_type, display_name, description, is_public) VALUES
|
|
-- Organization
|
|
('organization', 'association_name', '"Monaco USA"', 'text', 'Association Name', 'Official name of the association', true),
|
|
('organization', 'tagline', '"Americans in Monaco"', 'text', 'Tagline', 'Association tagline shown on login', true),
|
|
('organization', 'contact_email', '"contact@monacousa.org"', 'text', 'Contact Email', 'Public contact email', true),
|
|
('organization', 'primary_color', '"#dc2626"', 'text', 'Primary Color', 'Brand primary color (hex)', true),
|
|
|
|
-- Dues
|
|
('dues', 'payment_iban', '"MC58 1756 9000 0104 0050 1001 860"', 'text', 'Payment IBAN', 'Bank IBAN for dues', false),
|
|
('dues', 'payment_account_holder', '"ASSOCIATION MONACO USA"', 'text', 'Account Holder', 'Bank account holder name', false),
|
|
('dues', 'payment_bank_name', '"Credit Foncier de Monaco"', 'text', 'Bank Name', 'Name of the bank', false),
|
|
('dues', 'reminder_days_before', '[30, 7, 1]', 'array', 'Reminder Days', 'Days before due to send reminders', false),
|
|
('dues', 'grace_period_days', '30', 'number', 'Grace Period', 'Days after due before auto-inactive', false),
|
|
('dues', 'auto_inactive_enabled', 'true', 'boolean', 'Auto Inactive', 'Auto set inactive after grace period', false),
|
|
|
|
-- System
|
|
('system', 'maintenance_mode', 'false', 'boolean', 'Maintenance Mode', 'Put portal in maintenance mode', false),
|
|
('system', 'max_upload_size_mb', '50', 'number', 'Max Upload Size', 'Maximum file upload size in MB', false);
|
|
|
|
-- ============================================
|
|
-- EMAIL TEMPLATES
|
|
-- ============================================
|
|
CREATE TABLE public.email_templates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
template_key TEXT UNIQUE NOT NULL,
|
|
template_name TEXT NOT NULL,
|
|
category TEXT NOT NULL,
|
|
subject TEXT NOT NULL,
|
|
body_html TEXT NOT NULL,
|
|
body_text TEXT,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
is_system BOOLEAN DEFAULT FALSE,
|
|
variables_schema JSONB,
|
|
preview_data JSONB,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_by UUID REFERENCES public.members(id)
|
|
);
|
|
|
|
CREATE TRIGGER email_templates_updated_at
|
|
BEFORE UPDATE ON public.email_templates
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at();
|
|
|
|
-- ============================================
|
|
-- EMAIL LOGS
|
|
-- ============================================
|
|
CREATE TABLE public.email_logs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
recipient_id UUID REFERENCES public.members(id),
|
|
recipient_email TEXT NOT NULL,
|
|
recipient_name TEXT,
|
|
template_key TEXT,
|
|
subject TEXT NOT NULL,
|
|
email_type TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'queued'
|
|
CHECK (status IN ('queued', 'sent', 'delivered', 'opened', 'clicked', 'bounced', 'failed')),
|
|
provider TEXT,
|
|
provider_message_id TEXT,
|
|
opened_at TIMESTAMPTZ,
|
|
clicked_at TIMESTAMPTZ,
|
|
error_message TEXT,
|
|
retry_count INTEGER DEFAULT 0,
|
|
template_variables JSONB,
|
|
sent_by UUID REFERENCES public.members(id),
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
sent_at TIMESTAMPTZ,
|
|
delivered_at TIMESTAMPTZ
|
|
);
|
|
|
|
-- ============================================
|
|
-- VIEWS
|
|
-- ============================================
|
|
|
|
-- Members with dues status
|
|
CREATE VIEW public.members_with_dues AS
|
|
SELECT
|
|
m.*,
|
|
ms.name as status_name,
|
|
ms.display_name as status_display_name,
|
|
ms.color as status_color,
|
|
mt.display_name as membership_type_name,
|
|
mt.annual_dues,
|
|
dp.last_payment_date,
|
|
dp.current_due_date,
|
|
CASE
|
|
WHEN dp.current_due_date IS NULL THEN 'never_paid'
|
|
WHEN dp.current_due_date < CURRENT_DATE THEN 'overdue'
|
|
WHEN dp.current_due_date < CURRENT_DATE + INTERVAL '30 days' THEN 'due_soon'
|
|
ELSE 'current'
|
|
END as dues_status,
|
|
CASE
|
|
WHEN dp.current_due_date < CURRENT_DATE
|
|
THEN (CURRENT_DATE - dp.current_due_date)::INTEGER
|
|
ELSE NULL
|
|
END as days_overdue,
|
|
CASE
|
|
WHEN dp.current_due_date >= CURRENT_DATE
|
|
THEN (dp.current_due_date - CURRENT_DATE)::INTEGER
|
|
ELSE NULL
|
|
END as days_until_due
|
|
FROM public.members m
|
|
LEFT JOIN public.membership_statuses ms ON m.membership_status_id = ms.id
|
|
LEFT JOIN public.membership_types mt ON m.membership_type_id = mt.id
|
|
LEFT JOIN LATERAL (
|
|
SELECT
|
|
payment_date as last_payment_date,
|
|
due_date as current_due_date
|
|
FROM public.dues_payments
|
|
WHERE member_id = m.id
|
|
ORDER BY due_date DESC
|
|
LIMIT 1
|
|
) dp ON true;
|
|
|
|
-- Events with attendee counts
|
|
CREATE VIEW public.events_with_counts AS
|
|
SELECT
|
|
e.*,
|
|
et.display_name as event_type_name,
|
|
et.color as event_type_color,
|
|
et.icon as event_type_icon,
|
|
COALESCE(member_rsvps.confirmed_count, 0) +
|
|
COALESCE(member_rsvps.guest_count, 0) +
|
|
COALESCE(public_rsvps.confirmed_count, 0) +
|
|
COALESCE(public_rsvps.guest_count, 0) as total_attendees,
|
|
COALESCE(member_rsvps.confirmed_count, 0) as member_count,
|
|
COALESCE(public_rsvps.confirmed_count, 0) as non_member_count,
|
|
COALESCE(member_rsvps.waitlist_count, 0) +
|
|
COALESCE(public_rsvps.waitlist_count, 0) as waitlist_count,
|
|
CASE
|
|
WHEN e.max_attendees IS NULL THEN FALSE
|
|
WHEN (COALESCE(member_rsvps.confirmed_count, 0) +
|
|
COALESCE(member_rsvps.guest_count, 0) +
|
|
COALESCE(public_rsvps.confirmed_count, 0) +
|
|
COALESCE(public_rsvps.guest_count, 0)) >= e.max_attendees THEN TRUE
|
|
ELSE FALSE
|
|
END as is_full
|
|
FROM public.events e
|
|
LEFT JOIN public.event_types et ON e.event_type_id = et.id
|
|
LEFT JOIN LATERAL (
|
|
SELECT
|
|
COUNT(*) FILTER (WHERE status = 'confirmed') as confirmed_count,
|
|
COALESCE(SUM(guest_count) FILTER (WHERE status = 'confirmed'), 0) as guest_count,
|
|
COUNT(*) FILTER (WHERE status = 'waitlist') as waitlist_count
|
|
FROM public.event_rsvps
|
|
WHERE event_id = e.id
|
|
) member_rsvps ON true
|
|
LEFT JOIN LATERAL (
|
|
SELECT
|
|
COUNT(*) FILTER (WHERE status = 'confirmed') as confirmed_count,
|
|
COALESCE(SUM(guest_count) FILTER (WHERE status = 'confirmed'), 0) as guest_count,
|
|
COUNT(*) FILTER (WHERE status = 'waitlist') as waitlist_count
|
|
FROM public.event_rsvps_public
|
|
WHERE event_id = e.id
|
|
) public_rsvps ON true;
|
|
|
|
-- ============================================
|
|
-- ROW LEVEL SECURITY
|
|
-- ============================================
|
|
|
|
-- Enable RLS on all tables
|
|
ALTER TABLE public.members ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.dues_payments ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.events ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.event_rsvps ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.event_rsvps_public ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.documents ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.app_settings ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.email_templates ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.email_logs ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- MEMBERS POLICIES
|
|
CREATE POLICY "Members viewable by authenticated users"
|
|
ON public.members FOR SELECT
|
|
TO authenticated
|
|
USING (true);
|
|
|
|
CREATE POLICY "Users can update own profile"
|
|
ON public.members FOR UPDATE
|
|
TO authenticated
|
|
USING (auth.uid() = id);
|
|
|
|
CREATE POLICY "Admins can insert members"
|
|
ON public.members FOR INSERT
|
|
TO authenticated
|
|
WITH CHECK (
|
|
EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin')
|
|
OR auth.uid() = id
|
|
);
|
|
|
|
CREATE POLICY "Admins can delete members"
|
|
ON public.members FOR DELETE
|
|
TO authenticated
|
|
USING (
|
|
EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin')
|
|
);
|
|
|
|
-- DUES PAYMENTS POLICIES
|
|
CREATE POLICY "Own payments viewable"
|
|
ON public.dues_payments FOR SELECT
|
|
TO authenticated
|
|
USING (
|
|
member_id = auth.uid()
|
|
OR EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
|
|
);
|
|
|
|
CREATE POLICY "Board can record payments"
|
|
ON public.dues_payments FOR INSERT
|
|
TO authenticated
|
|
WITH CHECK (
|
|
EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
|
|
);
|
|
|
|
-- EVENTS POLICIES
|
|
CREATE POLICY "Events viewable based on visibility"
|
|
ON public.events FOR SELECT
|
|
TO authenticated
|
|
USING (
|
|
visibility = 'members'
|
|
OR visibility = 'public'
|
|
OR (visibility = 'board' AND EXISTS (
|
|
SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin')
|
|
))
|
|
OR (visibility = 'admin' AND EXISTS (
|
|
SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin'
|
|
))
|
|
);
|
|
|
|
CREATE POLICY "Public events viewable by anyone"
|
|
ON public.events FOR SELECT
|
|
TO anon
|
|
USING (visibility = 'public' AND status = 'published');
|
|
|
|
CREATE POLICY "Board can manage events"
|
|
ON public.events FOR ALL
|
|
TO authenticated
|
|
USING (
|
|
EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
|
|
);
|
|
|
|
-- EVENT RSVPs POLICIES
|
|
CREATE POLICY "RSVPs viewable by member and board"
|
|
ON public.event_rsvps FOR SELECT
|
|
TO authenticated
|
|
USING (
|
|
member_id = auth.uid()
|
|
OR EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
|
|
);
|
|
|
|
CREATE POLICY "Members can manage own RSVPs"
|
|
ON public.event_rsvps FOR ALL
|
|
TO authenticated
|
|
USING (member_id = auth.uid())
|
|
WITH CHECK (member_id = auth.uid());
|
|
|
|
CREATE POLICY "Board can manage all RSVPs"
|
|
ON public.event_rsvps FOR ALL
|
|
TO authenticated
|
|
USING (
|
|
EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
|
|
);
|
|
|
|
-- PUBLIC RSVPs POLICIES
|
|
CREATE POLICY "Public RSVPs viewable by board"
|
|
ON public.event_rsvps_public FOR SELECT
|
|
TO authenticated
|
|
USING (
|
|
EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
|
|
);
|
|
|
|
CREATE POLICY "Anyone can create public RSVP"
|
|
ON public.event_rsvps_public FOR INSERT
|
|
TO anon, authenticated
|
|
WITH CHECK (true);
|
|
|
|
CREATE POLICY "Board can manage public RSVPs"
|
|
ON public.event_rsvps_public FOR ALL
|
|
TO authenticated
|
|
USING (
|
|
EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
|
|
);
|
|
|
|
-- DOCUMENTS POLICIES
|
|
CREATE POLICY "Documents viewable based on visibility"
|
|
ON public.documents FOR SELECT
|
|
TO authenticated
|
|
USING (
|
|
visibility = 'members'
|
|
OR visibility = 'public'
|
|
OR (visibility = 'board' AND EXISTS (
|
|
SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin')
|
|
))
|
|
OR (visibility = 'admin' AND EXISTS (
|
|
SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin'
|
|
))
|
|
OR (allowed_member_ids IS NOT NULL AND auth.uid() = ANY(allowed_member_ids))
|
|
);
|
|
|
|
CREATE POLICY "Board can upload documents"
|
|
ON public.documents FOR INSERT
|
|
TO authenticated
|
|
WITH CHECK (
|
|
EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
|
|
);
|
|
|
|
CREATE POLICY "Admin can manage all documents"
|
|
ON public.documents FOR ALL
|
|
TO authenticated
|
|
USING (
|
|
EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin')
|
|
);
|
|
|
|
-- APP SETTINGS POLICIES
|
|
CREATE POLICY "Public settings viewable by anyone"
|
|
ON public.app_settings FOR SELECT
|
|
USING (is_public = true);
|
|
|
|
CREATE POLICY "All settings viewable by admin"
|
|
ON public.app_settings FOR SELECT
|
|
TO authenticated
|
|
USING (
|
|
EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin')
|
|
);
|
|
|
|
CREATE POLICY "Admin can manage settings"
|
|
ON public.app_settings FOR ALL
|
|
TO authenticated
|
|
USING (
|
|
EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin')
|
|
);
|
|
|
|
-- EMAIL TEMPLATES POLICIES
|
|
CREATE POLICY "Admin can manage email templates"
|
|
ON public.email_templates FOR ALL
|
|
TO authenticated
|
|
USING (
|
|
EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin')
|
|
);
|
|
|
|
-- EMAIL LOGS POLICIES
|
|
CREATE POLICY "Own email logs viewable"
|
|
ON public.email_logs FOR SELECT
|
|
TO authenticated
|
|
USING (
|
|
recipient_id = auth.uid()
|
|
OR EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin')
|
|
);
|
|
|
|
CREATE POLICY "Admin can manage email logs"
|
|
ON public.email_logs FOR ALL
|
|
TO authenticated
|
|
USING (
|
|
EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin')
|
|
);
|
|
|
|
-- ============================================
|
|
-- INDEXES
|
|
-- ============================================
|
|
CREATE INDEX idx_members_email ON public.members(email);
|
|
CREATE INDEX idx_members_member_id ON public.members(member_id);
|
|
CREATE INDEX idx_members_role ON public.members(role);
|
|
CREATE INDEX idx_members_status ON public.members(membership_status_id);
|
|
|
|
CREATE INDEX idx_dues_payments_member ON public.dues_payments(member_id);
|
|
CREATE INDEX idx_dues_payments_date ON public.dues_payments(payment_date DESC);
|
|
|
|
CREATE INDEX idx_events_start ON public.events(start_datetime);
|
|
CREATE INDEX idx_events_visibility ON public.events(visibility);
|
|
CREATE INDEX idx_events_status ON public.events(status);
|
|
|
|
CREATE INDEX idx_event_rsvps_event ON public.event_rsvps(event_id);
|
|
CREATE INDEX idx_event_rsvps_member ON public.event_rsvps(member_id);
|
|
|
|
CREATE INDEX idx_documents_category ON public.documents(category_id);
|
|
CREATE INDEX idx_documents_visibility ON public.documents(visibility);
|
|
|
|
CREATE INDEX idx_app_settings_category ON public.app_settings(category, setting_key);
|
|
|
|
CREATE INDEX idx_email_logs_recipient ON public.email_logs(recipient_id);
|
|
CREATE INDEX idx_email_logs_status ON public.email_logs(status);
|
|
CREATE INDEX idx_email_logs_created ON public.email_logs(created_at DESC);
|