MyClub/DOCS/NEWSLETTER_SYSTEM.md

Newsletter System Documentation

Overview

The comprehensive newsletter system automatically sends targeted emails to subscribers based on their preferences. It supports multiple newsletter types with category-based filtering and email analytics tracking.

Newsletter Types

1. Týdenní Přehled (Weekly Overview)

• When: Configurable day and hour (default: Sunday 9 AM)
• Content: Blogs, activities, matches, scores from the past week
• Preferences: User can select which content types to include
• Settings: EnableWeekly, NewsletterWeeklyDay, NewsletterWeeklyHour

2. Nadcházející Zápasy (Upcoming Matches)

• When:
  • 48 hours before match (default, configurable via NewsletterReminderLeadHours)
  • Day of match (0-6 hours before kickoff)
• Content: Match details (teams, date, time, competition, venue)
• Preferences: Filter by categories/competitions
• Settings: EnableMatchReminders, NewsletterReminderLeadHours

3. Nový Blog (Blog Release Notification)

• When: Immediately when article is published
• Content: Article title, excerpt, link to full article (tracked)
• Preferences: Filter by blog categories
• Trigger: Automatic when article published field changes from false to true

4. Výsledky Zápasů (Match Results)

• When: Within 6 hours after match finishes (respects quiet hours)
• Content: Final score, match details
• Preferences: Filter by categories/competitions
• Settings: EnableResults, NewsletterQuietStart, NewsletterQuietEnd

User Subscription Flow

Initial Subscription

1. User submits email via newsletter form
2. System creates NewsletterSubscription with default preferences:

   {
     "weekly": true,
     "matches": true,
     "blogs": true,
     "events": true
   }
   

3. Two emails sent:
   • Setup email: Token link to preference configuration page
   • Welcome email: Introduction to newsletter with manage/unsubscribe links

Preference Management

• Token-based (no login required)
• User can enable/disable:
  • Weekly digest
  • Match reminders
  • Blog notifications
  • Match results
  • Event notifications
• User can specify category filters (comma-separated)

Unsubscribe

• Link in every email footer
• Sets is_active = false in database
• No emails sent to inactive subscribers

Database Schema

Tables Added

newsletter_sent_log

Tracks all sent newsletters:

CREATE TABLE newsletter_sent_log (
    id SERIAL PRIMARY KEY,
    newsletter_type VARCHAR(50) NOT NULL,
    subject VARCHAR(500),
    content_ids TEXT,
    recipients_count INT DEFAULT 0,
    sent_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

match_notifications

Prevents duplicate match alerts:

CREATE TABLE match_notifications (
    id SERIAL PRIMARY KEY,
    match_id VARCHAR(255) NOT NULL,
    notification_type VARCHAR(50) NOT NULL,
    sent_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    recipients_count INT DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    UNIQUE(match_id, notification_type)
);

blog_notifications

Prevents duplicate blog alerts:

CREATE TABLE blog_notifications (
    id SERIAL PRIMARY KEY,
    article_id INT NOT NULL,
    sent_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    recipients_count INT DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    UNIQUE(article_id)
);

Existing Tables Used

newsletter_subscriptions

CREATE TABLE newsletter_subscriptions (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    is_active BOOLEAN DEFAULT TRUE,
    preferences JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

email_log & email_event

Track email delivery and user interactions (opens, clicks).

Admin Configuration

Settings (in settings table)

Field	Type	Description	Default
newsletter_enabled	boolean	Master toggle for all automated newsletters	false
enable_weekly	boolean	Enable weekly digest	false
enable_match_reminders	boolean	Enable upcoming match notifications	false
enable_results	boolean	Enable match result notifications	false
newsletter_weekly_day	string	Day for weekly digest (sun/mon/tue/wed/thu/fri/sat)	"sun"
newsletter_weekly_hour	int	Hour for weekly digest (0-23)	9
newsletter_reminder_lead_hours	int	Hours before match to send first reminder	48
newsletter_quiet_start	int	Quiet hours start (0-23)	22
newsletter_quiet_end	int	Quiet hours end (0-23)	8

Admin API Endpoints

• PATCH /api/v1/admin/newsletter/enable - Toggle automation on/off
• GET /api/v1/admin/newsletter/status - Get subscriber stats and schedule info
• POST /api/v1/admin/newsletter/test - Send test email
• POST /api/v1/admin/newsletter/send-digest - Manually send digest
• POST /api/v1/admin/newsletter/preview - Preview newsletter content
• GET /api/v1/admin/newsletter/subscribers - List subscribers
• PATCH /api/v1/admin/newsletter/subscribers/:id/status - Enable/disable subscriber
• DELETE /api/v1/admin/newsletter/subscribers/:id - Delete subscriber

Email Analytics & Tracking

Every newsletter email includes:

Open Tracking

• 1x1 transparent pixel: /api/v1/email/open.gif?m={email_log_id}&t={token}
• Logged in email_event table with event_type = "open"

Click Tracking

• All links wrapped: /api/v1/email/click?m={email_log_id}&t={token}&u={destination_url}
• Logged in email_event table with event_type = "click"
• Redirects user to actual destination

Unsubscribe Tracking

• Unsubscribe link logs event before deactivating subscription

Blog Links

• Direct links to articles with tracking parameters
• Includes subscriber token for personalization

Implementation Details

Service Architecture

newsletter_automation.go

Main automation service that:

• Runs every 15 minutes
• Checks settings to determine enabled newsletter types
• Loads match/blog data from cache (cache/prefetch/)
• Queries subscribers with matching preferences
• Sends personalized emails
• Records notifications to prevent duplicates

Key Functions

• Start() - Initializes automation loop
• RunCycle() - Checks all newsletter types
• checkWeeklyDigest() - Weekly newsletter logic
• checkUpcomingMatches() - Match reminder logic
• checkFinishedMatches() - Match result logic
• SendBlogNotification(article) - Blog notification (called from controller)

Blog Notification Trigger

When an article is created or updated:

// In base_controller.go CreateArticle/UpdateArticle
if art.Published {
    go bc.triggerBlogNotification(&art)
}

Content Generation

newsletter_content.go

• BuildNewsletterDigest() - Builds HTML digest from cached JSON
• Reads from: articles.json, events_upcoming.json, facr_club_info.json
• Respects user preferences and category filters
• Returns subject and HTML content

Preference Format

Stored in preferences JSONB field:

{
  "weekly": true,
  "matches": true,
  "blogs": true,
  "events": true,
  "scores": true,
  "categories": "MFS A,MFS B,Divize"
}

Email Templates

Templates Directory

templates/emails/

• base.html - Base template with branding
• newsletter.html - General newsletter template
• newsletter_welcome.html - Welcome email
• newsletter_welcome_back.html - Resubscribe email
• newsletter_setup.html - Initial setup email

Template Variables

All templates receive:

• ClubName, ClubLogoURL
• PrimaryColor, SecondaryColor, AccentColor
• FacebookURL, InstagramURL, YouTubeURL, TwitterURL
• UnsubscribeURL, ManageURL
• OpenPixelURL (for tracking)
• ContactEmail, ContactURL, WebsiteURL

Testing

Manual Test

# Send test newsletter
curl -X POST http://localhost:8080/api/v1/admin/newsletter/test \
  -H "Authorization: Bearer YOUR_ADMIN_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "email": "test@example.com",
    "type": "weekly"
  }'

Available Test Types

• newsletter - Basic test
• welcome - Welcome email
• welcome_back - Resubscribe email
• blogs - Blog digest
• events - Events digest
• matches - Upcoming matches
• scores - Recent results
• weekly - Full weekly digest

Migration

Running Migrations

# Set environment variable
export RUN_MIGRATIONS=true

# Start application (runs migrations automatically)
./main

Migration File

database/migrations/20250930000001_enhance_newsletter_system.up.sql

Creates tracking tables for newsletter automation.

Environment Variables

# Master toggle (can be overridden by DB settings)
NEWSLETTER_ENABLED=true

# Legacy interval for old scheduler (hours)
NEWSLETTER_INTERVAL_HOURS=24

Security Considerations

1. Token-Based Access: Subscriber tokens expire after 30 days
2. Rate Limiting: 200ms delay between emails to avoid SMTP throttling
3. Email Validation: Validates email format before subscription
4. Duplicate Prevention: Unique constraints on notification tracking tables
5. SMTP Authentication: Uses configured SMTP with TLS/SSL
6. Tracking Links: Uses secure tokens to prevent unauthorized access

Troubleshooting

No Emails Being Sent

1. Check newsletter_enabled in settings table:

   SELECT newsletter_enabled FROM settings LIMIT 1;
   

2. Check specific newsletter type enabled:

   SELECT enable_weekly, enable_match_reminders, enable_results FROM settings LIMIT 1;
   

3. Check active subscribers:

   SELECT COUNT(*) FROM newsletter_subscriptions WHERE is_active = true;
   

4. Check SMTP configuration:

   SELECT smtp_host, smtp_port, smtp_from FROM settings LIMIT 1;
   

Duplicate Emails

• Check notification tracking tables for existing entries
• Unique constraints should prevent duplicates automatically

Wrong Schedule

• Verify newsletter_weekly_day and newsletter_weekly_hour in settings
• Server timezone may differ from expected timezone

Future Enhancements

Potential improvements:

• A/B testing for subject lines
• Email template editor in admin UI
• Subscriber segmentation (active/inactive/engaged)
• Bounce handling and automatic cleanup
• Personalized content recommendations
• Mobile-responsive email templates (already inline CSS, but could improve)
• Newsletter archive on website
• RSS-to-email automation

API Reference

Public Endpoints (No Auth Required)

Subscribe

POST /api/v1/newsletter/subscribe
Content-Type: application/json

{
  "email": "user@example.com",
  "preferences": {
    "weekly": true,
    "matches": true,
    "blogs": true
  }
}

Get Preferences (with token)

GET /api/v1/newsletter/preferences?token={subscriber_token}

Save Preferences (with token)

POST /api/v1/newsletter/preferences
Content-Type: application/json

{
  "token": "{subscriber_token}",
  "preferences": {
    "weekly": true,
    "matches": false,
    "blogs": true,
    "categories": "MFS A,Divize"
  }
}

Unsubscribe (with token)

POST /api/v1/newsletter/unsubscribe-token
Content-Type: application/json

{
  "token": "{subscriber_token}"
}

Admin Endpoints (Requires Auth)

All admin endpoints require Authorization: Bearer {admin_jwt_token} header.

Enable/Disable Automation

PATCH /api/v1/admin/newsletter/enable
Content-Type: application/json

{
  "enabled": true
}

Get Status

GET /api/v1/admin/newsletter/status

Response:

{
  "total_subscribers": 150,
  "active_subscribers": 142,
  "sample_recipients": ["email1@example.com", "email2@example.com"],
  "interval_minutes": 1440,
  "next_approximate": "2025-10-01T09:00:00Z",
  "newsletter_enabled": true
}

Send Test Email

POST /api/v1/admin/newsletter/test
Content-Type: application/json

{
  "email": "admin@example.com",
  "type": "weekly"
}

Manual Digest Send

POST /api/v1/admin/newsletter/send-digest
Content-Type: application/json

{
  "type": "weekly",
  "competitions": "MFS A, MFS B"
}

Preview Newsletter

POST /api/v1/admin/newsletter/preview
Content-Type: application/json

{
  "preferences": {
    "blogs": true,
    "matches": true,
    "competitions": "MFS A"
  }
}

Response:

{
  "subject": "Fotbal Club – novinky a zápasy",
  "html": "<html>...</html>"
}

Conclusion

This newsletter system provides a complete, automated solution for keeping subscribers informed about club activities. It respects user preferences, tracks engagement, and integrates seamlessly with the existing article and match management systems.