Marketing App - Database Models Documentation

Overview

This document provides comprehensive documentation for all database models in the Marketing App. The application follows a B2B (Business-to-Business) multi-tenant architecture where organizations manage their marketing campaigns and contacts.

API Quick Start

Create and activate venv (Windows PowerShell)

python -m venv venv
./venv/Scripts/Activate.ps1
pip install -U pip
pip install django djangorestframework drf-spectacular djangorestframework-simplejwt Pillow

Apply migrations and run

python manage.py migrate
python manage.py runserver

API schema and docs (drf-spectacular)

OpenAPI JSON: /api/schema/
Swagger UI: /api/docs/
ReDoc: /api/redoc/

Twilio Setup (Required for Conversations & SMS)

The conversations and SMS features require Twilio credentials. If you see "Twilio credentials not configured" errors, follow these steps:

Quick Setup (run this in the marketing_app directory):

python setup_twilio.py

Manual Setup:
- Create a .env file in the marketing_app directory
- Add your Twilio credentials (see TWILIO_SETUP.md for details)
- Get credentials from https://console.twilio.com/
Required Environment Variables:

TWILIO_ACCOUNT_SID=your_account_sid_here
TWILIO_AUTH_TOKEN=your_auth_token_here
TWILIO_CONVERSATIONS_SERVICE_SID=your_service_sid_here
TWILIO_PHONE_NUMBER=your_twilio_phone_number

📖 For detailed setup instructions, see TWILIO_SETUP.md

Core Models

Organization

Purpose: Central tenant model for multi-tenancy. Each organization is a separate business entity with its own data, users, and settings.

Key Features:

Multi-tenant isolation
Subscription management integration
Customizable contact types per organization
Timezone and branding support

class Organization(models.Model):
    name = models.CharField(max_length=200)                    # Organization name
    domain = models.CharField(max_length=100, unique=True)     # Custom domain (optional)
    logo = models.ImageField(upload_to='organizations/logos/') # Brand logo
    timezone = models.CharField(max_length=50, default='UTC')   # Organization timezone
    
    # Organization profile
    organization_type = models.ForeignKey(OrganizationType)    # Church, E-commerce, etc.
    auto_seed_contact_types = models.BooleanField(default=True) # Auto-create contact types
    
    # Legacy subscription fields (deprecated - use payment app)
    subscription_plan = models.CharField(max_length=50)         # DEPRECATED
    subscription_expires_at = models.DateTimeField()           # DEPRECATED

Relationships:

users → OneToMany with User
contacts → OneToMany with Contact
contact_lists → OneToMany with ContactList
contact_types → OneToMany with ContactType
subscription → OneToOne with OrganizationSubscription

Convenience Properties:

# Subscription info (reads from payment app)
org.current_subscription     # OrganizationSubscription instance
org.current_plan           # SubscriptionPlan instance  
org.subscription_status     # 'active', 'trialing', 'past_due', etc.
org.has_active_subscription # Boolean
org.subscription_expires_at # DateTime

Business Logic:

Auto-creates contact types on creation (if auto_seed_contact_types=True)
Uses OrganizationSetupService for initialization
Supports custom domains for white-labeling

User

Purpose: Application users who can log in and manage marketing campaigns. Each user belongs to an organization.

Key Features:

Extended Django AbstractUser
Role-based permissions
Organization-scoped access
Profile management

class User(AbstractUser):
    organization = models.ForeignKey(Organization)              # Tenant isolation
    role = models.CharField(choices=[                          # Permission level
        ('admin', 'Admin'),        # Full access
        ('manager', 'Manager'),    # Manage campaigns, users
        ('marketer', 'Marketer'), # Create campaigns, manage contacts
        ('viewer', 'Viewer'),     # Read-only access
    ])
    phone = models.CharField(max_length=20)                    # Contact number
    avatar = models.ImageField(upload_to='users/avatars/')     # Profile picture
    is_verified = models.BooleanField(default=False)           # Email verification
    last_login_ip = models.GenericIPAddressField()             # Security tracking

Roles & Permissions:

Admin: Full system access, user management, billing
Manager: Campaign management, team oversight, reporting
Marketer: Create campaigns, manage contacts, send messages
Viewer: Read-only access to reports and data

Security Features:

IP tracking for security audits
Email verification system
Organization-scoped data access

Contact

Purpose: Marketing audience/recipients. These are the people organizations communicate with through campaigns.

Key Features:

Comprehensive contact information
Flexible contact type system
Marketing preferences
Subscription management
Custom fields support

class Contact(models.Model):
    organization = models.ForeignKey(Organization)            # Tenant isolation
    
    # Basic Information
    email = models.EmailField()                               # Primary identifier
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)
    phone = models.CharField(max_length=20, validators=[...]) # Validated phone format
    
    # Demographics
    date_of_birth = models.DateField()
    gender = models.CharField(choices=[('male', 'Male'), ('female', 'Female')])
    location = models.CharField(max_length=200)
    country = models.CharField(max_length=100)
    timezone = models.CharField(max_length=50)
    
    # Marketing Preferences
    contact_type = models.ForeignKey(ContactType)             # Flexible type system
    is_subscribed = models.BooleanField(default=True)         # Opt-in status
    subscription_source = models.CharField(max_length=100)    # How they subscribed
    tags = models.JSONField(default=list)                     # Custom tags
    custom_fields = models.JSONField(default=dict)           # Flexible attributes
    
    # Status & Tracking
    status = models.CharField(choices=ContactStatus.choices)   # Active, Unsubscribed, etc.
    created_at = models.DateTimeField(auto_now_add=True)
    last_contacted_at = models.DateTimeField()

Contact Statuses:

active - Can receive communications
unsubscribed - Opted out of communications
bounced - Email address invalid
complained - Marked as spam
blocked - Manually blocked

Custom Fields Example:

contact.custom_fields = {
    'company': 'Acme Corp',
    'industry': 'Technology',
    'lead_score': 85,
    'preferred_contact_time': 'morning'
}

Business Logic:

Auto-assigns default contact type on creation
Unique email per organization
Comprehensive indexing for performance

ContactList

Purpose: Organize contacts into groups for targeted campaigns.

Key Features:

Static and dynamic lists
JSON-based filtering criteria
Multi-list membership support

class ContactList(models.Model):
    organization = models.ForeignKey(Organization)
    name = models.CharField(max_length=200)
    description = models.TextField()
    contacts = models.ManyToManyField(Contact, related_name='lists')
    
    # Dynamic List Features
    is_dynamic = models.BooleanField(default=False)          # Auto-updating list
    filter_criteria = models.JSONField(default=dict)         # Filter rules
    
    created_by = models.ForeignKey(User)
    created_at = models.DateTimeField(auto_now_add=True)

Dynamic List Example:

# Auto-include all contacts from California with 'premium' tag
list.filter_criteria = {
    'location__icontains': 'California',
    'tags__contains': 'premium',
    'status': 'active'
}

Flexible Contact Type System

OrganizationType

Purpose: Predefined organization categories with associated contact type templates.

class OrganizationType(models.Model):
    name = models.CharField(max_length=100)                    # 'Church', 'E-commerce'
    slug = models.SlugField(max_length=50)                    # 'church', 'ecommerce'
    description = models.TextField()
    icon = models.CharField(max_length=50)                    # UI icon class
    color = models.CharField(max_length=7)                    # Brand color
    is_active = models.BooleanField(default=True)
    sort_order = models.PositiveIntegerField(default=0)

Predefined Types:

Church: New Member, Visitor, Member, Volunteer, Donor
E-commerce: Lead, Prospect, Customer, VIP Customer, Churned
SaaS: Trial User, Subscriber, Enterprise, Churned
Non-profit: Supporter, Volunteer, Donor, Board Member

ContactTypeTemplate

Purpose: Template contact types for each organization type.

class ContactTypeTemplate(models.Model):
    organization_type = models.ForeignKey(OrganizationType)
    name = models.CharField(max_length=50)                    # 'new_member'
    display_name = models.CharField(max_length=100)           # 'New Member'
    description = models.TextField()
    color = models.CharField(max_length=7)                     # UI color
    is_default = models.BooleanField(default=False)          # Default for new contacts
    sort_order = models.PositiveIntegerField(default=0)
    is_active = models.BooleanField(default=True)

ContactType

Purpose: Organization-specific contact types created from templates.

class ContactType(models.Model):
    organization = models.ForeignKey(Organization)
    name = models.CharField(max_length=50)                    # Inherited from template
    display_name = models.CharField(max_length=100)           # Can be customized
    description = models.TextField()
    color = models.CharField(max_length=7)
    is_default = models.BooleanField(default=False)
    is_active = models.BooleanField(default=True)
    sort_order = models.PositiveIntegerField(default=0)

Seeding Process:

Organization created with organization_type
OrganizationSetupService.seed_contact_types() called
Templates copied to organization-specific ContactTypes
Can be customized after creation

Payment & Subscription Models

SubscriptionPlan

Purpose: Catalog of available subscription plans with pricing and features.

class SubscriptionPlan(models.Model):
    name = models.CharField(max_length=100)                    # 'Basic', 'Premium'
    description = models.TextField()
    
    # Pricing
    price_monthly = models.DecimalField(max_digits=10, decimal_places=2)
    price_yearly = models.DecimalField(max_digits=10, decimal_places=2)
    currency = models.CharField(max_length=3, default='USD')
    
    # Limits
    max_contacts = models.IntegerField()                      # null = unlimited
    max_emails_per_month = models.IntegerField()
    max_sms_per_month = models.IntegerField()
    max_whatsapp_per_month = models.IntegerField()
    max_campaigns = models.IntegerField()
    
    # Features
    features = models.JSONField(default=list)                # ['analytics', 'automation']
    is_active = models.BooleanField(default=True)
    is_popular = models.BooleanField(default=False)           # Highlight in UI

Example Plans:

# Free Plan
{
    'name': 'Free',
    'price_monthly': 0,
    'max_contacts': 1000,
    'max_emails_per_month': 1000,
    'features': ['basic_analytics']
}

# Premium Plan  
{
    'name': 'Premium',
    'price_monthly': 99.00,
    'max_contacts': None,  # Unlimited
    'max_emails_per_month': 50000,
    'features': ['advanced_analytics', 'automation', 'a_b_testing']
}

OrganizationSubscription

Purpose: Active subscription for each organization.

class OrganizationSubscription(models.Model):
    organization = models.OneToOneField(Organization)
    plan = models.ForeignKey(SubscriptionPlan)
    
    # Status
    status = models.CharField(choices=SubscriptionStatus.choices)
    billing_cycle = models.CharField(choices=BillingCycle.choices)
    
    # Periods
    started_at = models.DateTimeField(auto_now_add=True)
    current_period_start = models.DateTimeField()
    current_period_end = models.DateTimeField()
    cancelled_at = models.DateTimeField(null=True)
    trial_end = models.DateTimeField(null=True)
    
    # Payment
    amount = models.DecimalField(max_digits=10, decimal_places=2)
    currency = models.CharField(max_length=3, default='USD')
    
    # External Integration
    stripe_subscription_id = models.CharField(max_length=200)
    stripe_customer_id = models.CharField(max_length=200)

Subscription Statuses:

active - Currently active and paid
trialing - In trial period
past_due - Payment failed, grace period
cancelled - Cancelled but may still have access
unpaid - Payment failed, no access

Payment

Purpose: Individual payment records.

class Payment(models.Model):
    organization = models.ForeignKey(Organization)
    subscription = models.ForeignKey(OrganizationSubscription)
    
    amount = models.DecimalField(max_digits=10, decimal_places=2)
    currency = models.CharField(max_length=3, default='USD')
    status = models.CharField(choices=PaymentStatus.choices)
    
    payment_method = models.CharField(choices=PaymentMethod.choices)
    created_at = models.DateTimeField(auto_now_add=True)
    paid_at = models.DateTimeField(null=True)
    
    # External References
    stripe_payment_intent_id = models.CharField(max_length=200)
    stripe_charge_id = models.CharField(max_length=200)
    
    description = models.TextField()
    metadata = models.JSONField(default=dict)

Invoice

Purpose: Billing invoices for payments.

class Invoice(models.Model):
    organization = models.ForeignKey(Organization)
    subscription = models.ForeignKey(OrganizationSubscription)
    payment = models.OneToOneField(Payment, null=True)
    
    invoice_number = models.CharField(max_length=100, unique=True)
    amount = models.DecimalField(max_digits=10, decimal_places=2)
    tax_amount = models.DecimalField(max_digits=10, decimal_places=2, default=0)
    
    status = models.CharField(choices=InvoiceStatus.choices)
    created_at = models.DateTimeField(auto_now_add=True)
    due_date = models.DateTimeField()
    paid_at = models.DateTimeField(null=True)
    
    stripe_invoice_id = models.CharField(max_length=200)
    description = models.TextField()
    line_items = models.JSONField(default=list)

UsageRecord

Purpose: Track usage for billing and limits.

class UsageRecord(models.Model):
    organization = models.ForeignKey(Organization)
    
    service_type = models.CharField(choices=ServiceType.choices)  # email, sms, whatsapp
    count = models.IntegerField()                                 # Usage count
    period_start = models.DateTimeField()                        # Billing period
    period_end = models.DateTimeField()
    
    unit_cost = models.DecimalField(max_digits=8, decimal_places=4)
    total_cost = models.DecimalField(max_digits=10, decimal_places=2)
    
    created_at = models.DateTimeField(auto_now_add=True)

Usage Tracking Example:

# Track email usage for billing
UsageRecord.objects.create(
    organization=org,
    service_type='email',
    count=1500,
    period_start=datetime(2024, 1, 1),
    period_end=datetime(2024, 1, 31),
    unit_cost=0.01,
    total_cost=15.00
)

BillingAddress

Purpose: Billing information for organizations.

class BillingAddress(models.Model):
    organization = models.OneToOneField(Organization)
    
    # Address
    company_name = models.CharField(max_length=200)
    address_line_1 = models.CharField(max_length=200)
    address_line_2 = models.CharField(max_length=200)
    city = models.CharField(max_length=100)
    state = models.CharField(max_length=100)
    postal_code = models.CharField(max_length=20)
    country = models.CharField(max_length=100)
    
    # Contact
    contact_name = models.CharField(max_length=200)
    contact_email = models.EmailField()
    contact_phone = models.CharField(max_length=20)
    
    # Tax
    tax_id = models.CharField(max_length=100)
    vat_number = models.CharField(max_length=100)

Database Relationships

Entity Relationship Diagram

Organization (1) ←→ (M) User
Organization (1) ←→ (M) Contact
Organization (1) ←→ (M) ContactList
Organization (1) ←→ (M) ContactType
Organization (1) ←→ (1) OrganizationSubscription
Organization (1) ←→ (1) BillingAddress

OrganizationType (1) ←→ (M) Organization
OrganizationType (1) ←→ (M) ContactTypeTemplate

ContactTypeTemplate (1) ←→ (M) ContactType (via seeding)

ContactList (M) ←→ (M) Contact

OrganizationSubscription (1) ←→ (M) Payment
OrganizationSubscription (1) ←→ (M) Invoice
OrganizationSubscription (1) ←→ (1) SubscriptionPlan

Organization (1) ←→ (M) UsageRecord

Key Relationships

Multi-tenancy: All data scoped to Organization
User Management: Users belong to one organization
Contact Management: Contacts belong to one organization
Subscription: One subscription per organization
Flexible Types: Organization types define contact type templates

Business Logic

Organization Setup Flow

Organization Creation:

org = Organization.objects.create(
    name="Acme Church",
    organization_type=church_type,
    auto_seed_contact_types=True
)

Automatic Setup:
- OrganizationSetupService.setup_new_organization(org) called
- Contact types seeded from templates
- Default settings applied

User Onboarding:

user = User.objects.create_user(
    username="pastor@acmechurch.com",
    organization=org,
    role="admin"
)

Contact Type Customization

Template-based Seeding:

# Church gets: New Member, Visitor, Member, Volunteer, Donor
church_templates = ContactTypeTemplate.objects.filter(
    organization_type__slug='church'
)

Custom Modifications:

# Customize after creation
new_member_type = org.contact_types.get(name='new_member')
new_member_type.display_name = "New Believer"
new_member_type.color = "#28a745"
new_member_type.save()

Subscription Management

Plan Selection:

plan = SubscriptionPlan.objects.get(name='Premium')
subscription = OrganizationSubscription.objects.create(
    organization=org,
    plan=plan,
    status='active',
    billing_cycle='monthly',
    current_period_start=timezone.now(),
    current_period_end=timezone.now() + timedelta(days=30)
)

Usage Tracking:

# Track email sends
UsageRecord.objects.create(
    organization=org,
    service_type='email',
    count=campaign.emails_sent,
    period_start=period_start,
    period_end=period_end
)

API Usage Examples

Creating a Church Organization

# 1. Get church organization type
church_type = OrganizationType.objects.get(slug='church')

# 2. Create organization
org = Organization.objects.create(
    name="Grace Community Church",
    organization_type=church_type,
    domain="gracechurch.com",
    timezone="America/New_York"
)

# 3. Contact types automatically seeded:
# - New Member (default)
# - Visitor  
# - Member
# - Volunteer
# - Donor
# - Inactive

# 4. Create admin user
admin = User.objects.create_user(
    username="pastor@gracechurch.com",
    email="pastor@gracechurch.com",
    password="secure_password",
    organization=org,
    role="admin",
    first_name="John",
    last_name="Smith"
)

Managing Contacts

# Add new church member
contact = Contact.objects.create(
    organization=org,
    email="member@example.com",
    first_name="Jane",
    last_name="Doe",
    phone="+1234567890",
    contact_type=org.contact_types.get(name='new_member'),
    tags=['baptism_class', 'small_group'],
    custom_fields={
        'baptism_date': '2024-01-15',
        'small_group': 'Young Adults',
        'volunteer_interest': 'Children\'s Ministry'
    }
)

# Create contact list for small group
small_group_list = ContactList.objects.create(
    organization=org,
    name="Young Adults Small Group",
    description="Members of the young adults ministry",
    created_by=admin,
    filter_criteria={
        'tags__contains': 'young_adults',
        'status': 'active'
    },
    is_dynamic=True
)

Subscription Management

# Get premium plan
premium_plan = SubscriptionPlan.objects.get(name='Premium')

# Create subscription
subscription = OrganizationSubscription.objects.create(
    organization=org,
    plan=premium_plan,
    status='active',
    billing_cycle='monthly',
    amount=99.00,
    current_period_start=timezone.now(),
    current_period_end=timezone.now() + timedelta(days=30)
)

# Check subscription status
if org.has_active_subscription:
    print(f"Plan: {org.current_plan.name}")
    print(f"Status: {org.subscription_status}")
    print(f"Expires: {org.subscription_expires_at}")

Usage Tracking

# Track email campaign usage
campaign = EmailCampaign.objects.get(id=1)
UsageRecord.objects.create(
    organization=org,
    service_type='email',
    count=campaign.recipients.count(),
    period_start=timezone.now().replace(day=1),
    period_end=timezone.now().replace(day=1) + timedelta(days=30),
    unit_cost=0.01,
    total_cost=campaign.recipients.count() * 0.01
)

Performance Considerations

Database Indexes

Contact Model:

(organization, email) - Unique constraint
(organization, status) - Status filtering
(created_at) - Time-based queries

UsageRecord Model:

(organization, service_type) - Usage queries
(period_start, period_end) - Period filtering

Query Optimization

# Efficient contact queries
contacts = Contact.objects.filter(
    organization=org,
    status='active'
).select_related('contact_type').prefetch_related('lists')

# Efficient subscription queries  
subscription = OrganizationSubscription.objects.select_related(
    'plan'
).get(organization=org)

Caching Strategy

# Cache organization subscription info
@cached_property
def subscription_info(self):
    return {
        'plan': self.current_plan.name,
        'status': self.subscription_status,
        'expires': self.subscription_expires_at
    }

Security Considerations

Multi-tenancy Isolation

All queries must filter by organization
No cross-tenant data access
User permissions scoped to organization

Data Privacy

Contact data encrypted at rest
GDPR compliance features (unsubscribe, data export)
Audit logging for sensitive operations

API Security

Organization-scoped API endpoints
Role-based permission checks
Rate limiting per organization

Migration Strategy

Legacy Data Migration

Organization Types: Create predefined types
Contact Type Templates: Seed templates for each type
Subscription Migration: Map legacy subscription_plan to SubscriptionPlan
Data Cleanup: Remove deprecated fields

Deployment Steps

Run migrations for new models
Seed organization types and templates
Migrate existing subscription data
Update application code to use new models
Remove legacy fields

This documentation provides a comprehensive overview of the Marketing App's database architecture. The flexible contact type system allows organizations to customize their contact management while maintaining a consistent data structure for the application.

Name		Name	Last commit message	Last commit date
Latest commit History 12 Commits
conversations		conversations
docs		docs
emails		emails
fixtures		fixtures
frontend_examples		frontend_examples
marketing_app		marketing_app
payment		payment
sms		sms
sms_proxy		sms_proxy
users		users
whatsapp		whatsapp
.gitignore		.gitignore
manage.py		manage.py
requirements.txt		requirements.txt

wisdompython/ReacherApp

Folders and files

Latest commit

History

Repository files navigation

Marketing App - Database Models Documentation

Overview

API Quick Start

Twilio Setup (Required for Conversations & SMS)

Table of Contents

Core Models

Organization

User

Contact

ContactList

Flexible Contact Type System

OrganizationType

ContactTypeTemplate

ContactType

Payment & Subscription Models

SubscriptionPlan

OrganizationSubscription

Payment

Invoice

UsageRecord

BillingAddress

Database Relationships

Entity Relationship Diagram

Key Relationships

Business Logic

Organization Setup Flow

Contact Type Customization

Subscription Management

API Usage Examples

Creating a Church Organization

Managing Contacts

Subscription Management

Usage Tracking

Performance Considerations

Database Indexes

Query Optimization

Caching Strategy

Security Considerations

Multi-tenancy Isolation

Data Privacy

API Security

Migration Strategy

Legacy Data Migration

Deployment Steps

About

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Uh oh!

Contributors

Uh oh!

Languages

Packages