Skip to content

Instantly share code, notes, and snippets.

@nielsberglund

nielsberglund/plan.md

Created December 27, 2025 15:27
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save nielsberglund/c0b16d2a83b81956b0e1a59c957fc928 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save nielsberglund/c0b16d2a83b81956b0e1a59c957fc928 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
plan.md

AI-Driven Event Contact Management System - Architecture Plan

Executive Summary

Is the LLM/MCP approach feasible?

✅ YES - This is an IDEAL use case for an LLM-driven architecture with MCP servers.

Your vision is not only doable, it's actually a better architectural choice than a traditional API-driven system for your specific needs. Here's why this approach excels:

  1. Natural Language is Your Primary Interface: Your example queries ("Is Jane Smith registered?", "How many attended last year?") are exactly what LLMs excel at interpreting
  2. Structured Data + Unstructured Queries: The underlying data is highly structured (contacts, events, ratings), but your queries are conversational - LLMs bridge this gap perfectly
  3. Scale: For events with ~300 attendees and ~20-30 speakers, this architecture handles it effortlessly
  4. Contextual Intelligence: LLMs provide rich, context-aware responses ("Jane attended 2023 and 2024 as an attendee")

High-Level Architecture

Architectural Layers

┌─────────────────────────────────────────────────────────────┐
│                    USER INTERFACE LAYER                      │
│                                                               │
│  Phase 1: Claude.ai / Claude Code (MCP-enabled)              │
│  Phase 2: Web App with Chat Interface → Claude API           │
│                                                               │
└───────────────────────────┬─────────────────────────────────┘
                            │ Natural Language Queries
                            ▼
┌─────────────────────────────────────────────────────────────┐
│                      LLM ORCHESTRATION                       │
│                     (Claude Sonnet/Opus)                     │
│                                                               │
│  • Interprets natural language queries                       │
│  • Calls appropriate MCP server tools                        │
│  • Composes intelligent, context-aware responses             │
│  • Handles multi-step workflows with confirmations           │
│                                                               │
└───────────────────────────┬─────────────────────────────────┘
                            │ MCP Protocol (Tool Calls)
                            ▼
┌─────────────────────────────────────────────────────────────┐
│                    MCP SERVER LAYER                          │
│                                                               │
│  ┌─────────────────┐  ┌──────────────┐  ┌────────────────┐ │
│  │ Contact/DB MCP  │  │  Email MCP   │  │  Import MCP    │ │
│  │                 │  │              │  │                │ │
│  │ • Contact CRUD  │  │ • Send emails│  │ • Parse CSVs   │ │
│  │ • Event CRUD    │  │ • Preview    │  │ • Validate data│ │
│  │ • Participation │  │   recipients │  │ • Bulk inserts │ │
│  │ • Speaker       │  │ • Templates  │  │ • Quicket fmt  │ │
│  │   sessions      │  │ • Email log  │  │ • Sessionize   │ │
│  │ • Ratings       │  │              │  │   format       │ │
│  │ • Analytics     │  │              │  │ • Rating files │ │
│  │   queries       │  │              │  │                │ │
│  └─────────────────┘  └──────────────┘  └────────────────┘ │
│                                                               │
└───────────────────────────┬─────────────────────────────────┘
                            │ SQL / SMTP
                            ▼
┌─────────────────────────────────────────────────────────────┐
│                   DATA & SERVICES LAYER                      │
│                                                               │
│  ┌──────────────────┐         ┌──────────────────────────┐  │
│  │  PostgreSQL      │         │  Email Service           │  │
│  │                  │         │  (SMTP/SendGrid/Brevo)   │  │
│  │  • contacts      │         │                          │  │
│  │  • events        │         └──────────────────────────┘  │
│  │  • participation │                                        │
│  │  • sessions      │                                        │
│  │  • ratings       │                                        │
│  │  • email_log     │                                        │
│  └──────────────────┘                                        │
│                                                               │
└─────────────────────────────────────────────────────────────┘

Core Components

1. PostgreSQL Database (Data Foundation)

Core Tables:

  • contacts: Unified person records (attendees + speakers)
    • id, email, name, phone, company, contact_type, created_date
  • events: Event definitions
    • id, name, event_date, location, description, year
  • participation: Who registered/attended which events
    • id, contact_id, event_id, registration_date, checked_in, attendance_status
  • speaker_sessions: Speaker presentations
    • id, event_id, contact_id (speaker), topic, session_title, track
  • speaker_ratings: Session ratings
    • id, session_id, topic_rating, presentation_rating, overall_score, comments
  • email_log: Communication history
    • id, sent_date, recipient_count, subject, template_used, sent_by

Why PostgreSQL:

  • Excellent relational data modeling (contacts ↔ events ↔ participation)
  • Strong analytics capabilities (aggregations, window functions)
  • JSON support for flexible data (Quicket/Sessionize import metadata)
  • Rock-solid for your data volume (~300-500 contacts)

2. MCP Server Layer (Tool Providers)

A. Contact/Database MCP Server

The primary data interface - provides tools for:

Contact Management:

  • search_contacts(query) - Find contacts by name/email/company
  • get_contact(id) - Get full contact details + history
  • create_contact(...) - Add new contact
  • update_contact(...) - Modify contact info
  • merge_contacts(...) - Resolve duplicates

Event Management:

  • list_events(year?) - Get events
  • get_event(id) - Event details + statistics
  • create_event(...) - New event

Participation Tracking:

  • record_registration(contact_id, event_id, ...)
  • record_attendance(contact_id, event_id)
  • get_participation_history(contact_id)

Speaker Management:

  • record_session(event_id, speaker_id, topic, ...)
  • record_rating(session_id, ratings, ...)
  • get_speaker_history(contact_id)

Analytics Queries:

  • get_attendance_stats(event_id) - Attendance vs registration
  • get_speaker_rankings(event_id, limit) - Top speakers
  • get_retention_analysis(year1, year2) - Attendee retention
  • get_first_time_attendees(event_id) - New attendees count
  • get_topic_popularity() - Topic trends

B. Email MCP Server

Handles all communications:

Tools:

  • preview_recipients(filter) - Show who will receive email
    • Example: filter="speakers_from_year:2024"
  • send_email(recipient_filter, subject, body, template?) - Send to group
  • send_individual_email(contact_id, subject, body) - Send to one
  • get_email_templates() - List available templates
  • get_email_history(event_id?, contact_id?) - Past emails

Features:

  • Template support (CFP announcement, event reminder, thank you)
  • Preview-before-send workflow (safety)
  • Email history logging
  • Unsubscribe handling

C. Import MCP Server

Batch data ingestion:

Tools:

  • import_quicket_registrations(file_path) - Parse Quicket CSV → contacts + participation
  • import_quicket_checkins(file_path) - Update attendance status
  • import_sessionize_speakers(file_path) - Parse Sessionize JSON → speakers + sessions
  • import_speaker_ratings(file_path) - Internal rating system → ratings table
  • preview_import(file_path, import_type) - Show what would be imported
  • validate_import_data(file_path, import_type) - Check for errors/conflicts

Features:

  • Data validation (required fields, email format)
  • Duplicate detection (match on email)
  • Conflict resolution strategies (update vs skip vs merge)
  • Import summary reporting

Conversation Flow Examples

Example 1: Registration Query

User: "Is Jane Smith registered for the upcoming conference?"

Flow:

  1. LLM interprets query → needs contact lookup + event lookup
  2. Calls search_contacts(query="Jane Smith")
  3. Calls list_events() to find "upcoming" event
  4. Calls get_participation_history(jane_id)
  5. LLM composes response:

Response: "Yes, Jane Smith (jane.smith@example.com) registered on December 15, 2024 for the 2025 conference. She previously attended the 2023 and 2024 conferences as an attendee."

Example 2: Bulk Email Workflow

User: "Send an email to all 2024 speakers announcing the call for papers for 2025."

Flow:

  1. LLM interprets → needs speaker list + email send
  2. Calls preview_recipients(filter="speakers_from_year:2024")
  3. LLM responds: "I've identified 23 speakers from 2024. Would you like to review the list before sending?"
  4. User: "Yes, show me"
  5. LLM displays list with names/emails
  6. User: "Looks good, send it"
  7. Calls send_email(filter="speakers_from_year:2024", subject="...", body="...", template="cfp_announcement")
  8. LLM confirms: "Email sent to 23 speakers. Logged in email history."

Example 3: Analytics Query

User: "How many people attended last year's event compared to 2023?"

Flow:

  1. LLM interprets → needs attendance comparison
  2. Calls get_attendance_stats(event_id=2024_event)
  3. Calls get_attendance_stats(event_id=2023_event)
  4. LLM calculates percentage change
  5. LLM responds:

Response: "2024 had 287 attendees (92% of 312 registrations). 2023 had 245 attendees (88% of 278 registrations). That's a 17% increase in attendance and 12% increase in registrations year-over-year."

Example 4: Data Import

User: "Import this Quicket registration file" (attaches CSV)

Flow:

  1. LLM calls validate_import_data(file_path, type="quicket_registration")
  2. Import MCP validates, finds 145 rows, 3 potential duplicates
  3. LLM reports: "Found 145 registrations. 3 appear to be existing contacts. How should I handle duplicates?"
  4. User: "Update existing records"
  5. LLM calls import_quicket_registrations(file_path, duplicate_strategy="update")
  6. Import MCP processes: 142 new contacts, 3 updated, 145 participation records
  7. LLM confirms: "Import complete. Added 142 new contacts, updated 3 existing, created 145 registration records for Event 2025."

Key Advantages

1. Natural Language is Powerful

  • No need to remember SQL queries or API endpoints
  • Ask questions the way you think about them
  • LLM handles ambiguity ("last year" vs "2024", "Jane" vs "Jane Smith")

2. Context-Aware Responses

  • LLM enriches responses with relevant history
  • "Jane Smith attended 2023 and 2024" without being asked
  • Proactive insights ("That's a 17% increase")

3. Safe Bulk Operations

  • Preview-before-send for emails
  • Validation before data imports
  • LLM confirms destructive operations

4. Highly Extensible

  • Add new MCP servers easily (reporting, social media, surveys)
  • LLM automatically learns new tools
  • No frontend changes needed for new capabilities

5. Future-Proof

  • Same MCP servers work with Claude.ai, Claude Code, or web app
  • Gradual migration: start with Claude.ai, add web UI later
  • MCP protocol is vendor-neutral

Potential Challenges & Mitigations

Challenge 1: Multi-Step Workflows (Email Preview → Confirm → Send)

Issue: LLM conversations are stateless between turns

Solution:

  • MCP servers maintain short-term state (preview cache)
  • LLM conversation history provides context
  • For Phase 2 web app: session management

Challenge 2: Data Quality from Imports

Issue: Quicket/Sessionize exports may have duplicates, format variations

Solution:

  • Import MCP validates data before insertion
  • Fuzzy matching on email/name for duplicates
  • LLM asks user to resolve conflicts
  • Import preview mode (dry-run)

Challenge 3: Complex Analytics Queries

Issue: Some queries require complex SQL (retention cohorts, trend analysis)

Solution:

  • Pre-build common analytics queries in MCP server
  • LLM composes SQL for ad-hoc queries (with safeguards)
  • Gradually expand pre-built analytics based on usage

Challenge 4: Email Template Management

Issue: Need rich email templates (HTML, variables)

Solution:

  • Store templates in database or files
  • Email MCP supports variable substitution
  • LLM can customize templates on-the-fly
  • Phase 2: Template editor in web app

Challenge 5: LLM Costs for High-Volume Queries

Issue: Every query goes through Claude API (costs)

Solution:

  • For simple lookups, MCP servers can cache results
  • Pre-compute common analytics (daily rollups)
  • Use Haiku for simple queries, Sonnet for complex
  • Your volume (~300 contacts, occasional queries) = minimal cost

Implementation Phases

Phase 1: MVP with Claude.ai/Claude Code (Recommended Starting Point)

Timeline: This is a planning document - no estimates provided

Deliverables:

  1. PostgreSQL database with schema
  2. Contact/Database MCP server (Python or TypeScript)
  3. Email MCP server
  4. Import MCP server
  5. MCP server configuration for Claude.ai/Claude Code
  6. Sample data for testing

Usage:

  • Use Claude.ai desktop app or Claude Code CLI
  • MCP servers run locally or on cloud VM
  • All interactions via chat

Advantages:

  • Fastest to build (no frontend needed)
  • Validate the approach with real usage
  • Iterate on MCP tool design based on feedback

Phase 2: Web Interface

Deliverables:

  1. Web application (React/Next.js or similar)
  2. Chat UI component
  3. Backend API that calls Claude API with MCP servers
  4. User authentication
  5. Email template management UI
  6. Data visualization dashboards

Usage:

  • Browser-based chat interface
  • Same MCP servers from Phase 1
  • Enhanced email composer
  • Visual analytics

Advantages:

  • Shareable with co-organizers
  • Better email template management
  • Visual dashboards for quick insights
  • User access controls

Phase 3: Advanced Features (Future)

Potential Additions:

  • Automated Quicket/Sessionize API integration
  • Scheduled reports (weekly attendance summaries)
  • SMS notifications
  • Survey integration (post-event feedback)
  • Social media analytics (mentions, engagement)
  • Multi-event comparison dashboards

Technical Considerations (Not Full Tech Stack)

Database Design Principles

  • Normalize core entities (contacts, events separate)
  • Use junction tables for many-to-many (participation, sessions)
  • Index frequently queried fields (email, event_date)
  • Store import metadata as JSONB (flexibility)

MCP Server Design Principles

  • Atomic tools: Each tool does ONE thing well
  • Composable: LLM chains tools together for complex workflows
  • Validated: Tools validate inputs before DB operations
  • Descriptive: Clear tool names and parameter descriptions
  • Idempotent where possible: Safe to retry

Security Considerations

  • Email MCP requires confirmation for bulk sends (>10 recipients)
  • Import MCP logs all operations (audit trail)
  • Database MCP uses parameterized queries (SQL injection prevention)
  • Contact data includes GDPR fields (consent, opt-out)
  • Email unsubscribe support

Data Privacy

  • Store only necessary contact data
  • Email preference management (opt-out)
  • GDPR compliance (data export, deletion)
  • Audit log for all email sends

Why This Beats a Traditional API-Driven System

Traditional Approach Would Require:

❌ Custom admin UI with forms for every operation ❌ Pre-defined report templates (inflexible) ❌ Complex filters/search interfaces ❌ Email campaign builder UI ❌ Analytics dashboard with fixed visualizations ❌ User manual/training

LLM/MCP Approach Provides:

✅ Natural language for everything ✅ Ad-hoc queries without new code ✅ Context-aware responses ✅ Flexible email workflows via conversation ✅ No training needed (just ask questions) ✅ Easy to extend (add MCP tools)

Conclusion

Your vision is not only feasible - it's architecturally superior for your use case.

The LLM/MCP approach gives you:

  • Flexibility: Ask any question, not just pre-built reports
  • Speed: No frontend development for Phase 1
  • Intelligence: Context-aware, conversational responses
  • Safety: Preview/confirmation workflows
  • Extensibility: Add capabilities without UI changes
  • Future-proof: Same backend works with any frontend

Recommended Next Step: Start with Phase 1 (Claude.ai/Claude Code + MCP servers). This lets you validate the approach, refine the data model, and understand your real usage patterns before investing in a web UI.

The database schema and MCP servers you build for Phase 1 will work unchanged in Phase 2 - you're not throwing away any work.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment