-- Monaco USA Portal 2026 - Onboarding Payment Tracking -- Track new member payment deadlines for the 30-day payment window -- ============================================ -- ADD PAYMENT TRACKING COLUMNS TO MEMBERS -- ============================================ -- Payment deadline for new signups (30 days from onboarding completion) ALTER TABLE public.members ADD COLUMN IF NOT EXISTS payment_deadline TIMESTAMPTZ; -- Track when onboarding was completed ALTER TABLE public.members ADD COLUMN IF NOT EXISTS onboarding_completed_at TIMESTAMPTZ; -- Index for efficient reminder queries (only index non-null deadlines) CREATE INDEX IF NOT EXISTS idx_members_payment_deadline ON public.members(payment_deadline) WHERE payment_deadline IS NOT NULL; -- ============================================ -- ADD ONBOARDING REMINDER TYPES TO LOGS TABLE -- ============================================ -- Update the check constraint on dues_reminder_logs to include onboarding types ALTER TABLE public.dues_reminder_logs DROP CONSTRAINT IF EXISTS dues_reminder_logs_reminder_type_check; ALTER TABLE public.dues_reminder_logs ADD CONSTRAINT dues_reminder_logs_reminder_type_check CHECK (reminder_type IN ( 'due_soon_30', 'due_soon_7', 'due_soon_1', 'overdue', 'grace_period', 'inactive_notice', 'onboarding_welcome', 'onboarding_reminder_7', 'onboarding_reminder_1', 'onboarding_expired' )); -- ============================================ -- ADD EMAIL TEMPLATES FOR ONBOARDING REMINDERS -- ============================================ -- Welcome email with payment instructions (sent immediately after onboarding) INSERT INTO public.email_templates (template_key, template_name, category, subject, body_html, body_text, is_active, is_system, variables_schema) VALUES ( 'onboarding_welcome', 'Welcome - Complete Your Membership', 'onboarding', 'Welcome to Monaco USA - Complete Your Membership', '

Dear {{first_name}},

Welcome to Monaco USA! We''re thrilled to have you join our community of Americans living in and connected to Monaco.

Your account has been created and you now have 30 days to complete your membership by paying your annual dues.

Your Membership Details:

Member ID: {{member_id}}

Annual Dues: {{amount}}

Payment Deadline: {{payment_deadline}}

Bank Transfer Details:

Account Holder: {{account_holder}}

Bank: {{bank_name}}

IBAN: {{iban}}

Reference: {{member_id}}

In the meantime, explore your new member dashboard and connect with our community:

Go to Dashboard

Questions? Contact us at contact@monacousa.org

', 'Dear {{first_name}}, Welcome to Monaco USA! We''re thrilled to have you join our community of Americans living in and connected to Monaco. Your account has been created and you now have 30 days to complete your membership by paying your annual dues. Your Membership Details: - Member ID: {{member_id}} - Annual Dues: {{amount}} - Payment Deadline: {{payment_deadline}} Bank Transfer Details: - Account Holder: {{account_holder}} - Bank: {{bank_name}} - IBAN: {{iban}} - Reference: {{member_id}} Visit your dashboard: {{portal_url}} Questions? Contact us at contact@monacousa.org', true, true, '{"first_name": "Member first name", "member_id": "Member ID", "amount": "Annual dues amount", "payment_deadline": "Payment deadline date", "account_holder": "Bank account holder", "bank_name": "Bank name", "iban": "IBAN number", "portal_url": "Portal URL"}' ) ON CONFLICT (template_key) DO NOTHING; -- 7 days left reminder INSERT INTO public.email_templates (template_key, template_name, category, subject, body_html, body_text, is_active, is_system, variables_schema) VALUES ( 'onboarding_reminder_7', 'Onboarding Reminder - 7 Days Left', 'onboarding', '7 Days Left to Complete Your Monaco USA Membership', '

Dear {{first_name}},

You have 7 days left to complete your Monaco USA membership by paying your annual dues.

Payment Due:

Amount: {{amount}}

Deadline: {{payment_deadline}}

Bank Transfer Details:

Account Holder: {{account_holder}}

Bank: {{bank_name}}

IBAN: {{iban}}

Reference: {{member_id}}

After the deadline, your account will be marked as inactive and you''ll lose access to member features.

View My Account

', 'Dear {{first_name}}, You have 7 days left to complete your Monaco USA membership by paying your annual dues. Payment Due: - Amount: {{amount}} - Deadline: {{payment_deadline}} Bank Transfer Details: - Account Holder: {{account_holder}} - Bank: {{bank_name}} - IBAN: {{iban}} - Reference: {{member_id}} After the deadline, your account will be marked as inactive. Visit: {{portal_url}}', true, true, '{"first_name": "Member first name", "member_id": "Member ID", "amount": "Annual dues amount", "payment_deadline": "Payment deadline date", "account_holder": "Bank account holder", "bank_name": "Bank name", "iban": "IBAN number", "portal_url": "Portal URL"}' ) ON CONFLICT (template_key) DO NOTHING; -- Last day reminder (urgent) INSERT INTO public.email_templates (template_key, template_name, category, subject, body_html, body_text, is_active, is_system, variables_schema) VALUES ( 'onboarding_reminder_1', 'Onboarding Reminder - Last Day', 'onboarding', 'URGENT: Last Day to Complete Your Monaco USA Membership', '

Dear {{first_name}},

Today is your last day to complete your Monaco USA membership payment.

Payment Required Today:

Amount: {{amount}}

Deadline: {{payment_deadline}}

Bank Transfer Details:

Account Holder: {{account_holder}}

Bank: {{bank_name}}

IBAN: {{iban}}

Reference: {{member_id}}

If we don''t receive your payment today, your account will be marked as inactive tomorrow.

Complete Payment Now

', 'Dear {{first_name}}, TODAY IS YOUR LAST DAY to complete your Monaco USA membership payment. Payment Required Today: - Amount: {{amount}} - Deadline: {{payment_deadline}} Bank Transfer Details: - Account Holder: {{account_holder}} - Bank: {{bank_name}} - IBAN: {{iban}} - Reference: {{member_id}} If we don''t receive your payment today, your account will be marked as inactive tomorrow. Visit: {{portal_url}}', true, true, '{"first_name": "Member first name", "member_id": "Member ID", "amount": "Annual dues amount", "payment_deadline": "Payment deadline date", "account_holder": "Bank account holder", "bank_name": "Bank name", "iban": "IBAN number", "portal_url": "Portal URL"}' ) ON CONFLICT (template_key) DO NOTHING; -- Account marked inactive (deadline passed) INSERT INTO public.email_templates (template_key, template_name, category, subject, body_html, body_text, is_active, is_system, variables_schema) VALUES ( 'onboarding_expired', 'Onboarding Expired - Account Inactive', 'onboarding', 'Your Monaco USA Account Has Been Marked Inactive', '

Dear {{first_name}},

Your 30-day payment window has expired and your Monaco USA account has been marked as INACTIVE.

Account Status:

Status: Inactive

Outstanding Amount: {{amount}}

As an inactive member, you no longer have access to:

To reactivate your membership, please complete your dues payment:

Bank Transfer Details:

Account Holder: {{account_holder}}

Bank: {{bank_name}}

IBAN: {{iban}}

Reference: {{member_id}}

Reactivate My Account

Questions? Contact us at contact@monacousa.org

', 'Dear {{first_name}}, Your 30-day payment window has expired and your Monaco USA account has been marked as INACTIVE. Account Status: - Status: Inactive - Outstanding Amount: {{amount}} As an inactive member, you no longer have access to member-only events, directory, and communications. To reactivate your membership, please pay your dues: Bank Transfer Details: - Account Holder: {{account_holder}} - Bank: {{bank_name}} - IBAN: {{iban}} - Reference: {{member_id}} Visit: {{portal_url}} Questions? Contact us at contact@monacousa.org', true, true, '{"first_name": "Member first name", "member_id": "Member ID", "amount": "Annual dues amount", "account_holder": "Bank account holder", "bank_name": "Bank name", "iban": "IBAN number", "portal_url": "Portal URL"}' ) ON CONFLICT (template_key) DO NOTHING;