Database Schema

Complete database structure for the FlowCampaign email campaign platform.

Database: SQLite 3.x (compatible with Turso/Cloudflare D1)
ORM: Drizzle ORM with TypeScript types
Last Updated: July 13, 2026

All tables support full CRUD operations through the API layer with appropriate Row Level Security (RLS) policies in production.

Entity Relationship Diagram

Tables Overview

1. campaigns

Stores email campaign definitions and metadata.

Column	Type	Nullable	Default	Description
id	text	NO	-	Primary key (UUID)
name	text	NO	-	Campaign display name
subject	text	NO	-	Email subject line
from_name	text	NO	-	Sender display name
reply_to	text	NO	''	Reply-to email address
provider_id	text	NO	-	FK → providers.id
status	text	NO	'draft'	Campaign status
delay_seconds	integer	NO	0	Delay between sends (seconds)
retry_count	integer	NO	3	Max retry attempts
html_content	text	NO	-	Email HTML content
total_recipients	integer	NO	0	Total recipients count
sent_recipients	integer	NO	0	Sent recipients count
created	integer	NO	-	Creation timestamp (Unix)
updated	integer	NO	-	Update timestamp (Unix)

Status Values: draft, scheduled, sending, sent, failed, cancelled

Indexes:

• idx_campaigns_status ON (status)
• idx_campaigns_created ON (created)

Foreign Keys:

• campaigns_provider_id_fkey → providers(id)

---

2. campaign_recipients

Tracks individual email recipients and delivery status.

Column	Type	Nullable	Default	Description
id	text	NO	-	Primary key (UUID)
campaign_id	text	NO	-	FK → campaigns.id
contact_id	text	NO	-	FK → contacts.id
email	text	NO	-	Recipient email address
first_name	text	NO	''	Recipient first name
last_name	text	NO	''	Recipient last name
company	text	NO	''	Recipient company
custom_fields	text	NO	'{}'	JSON custom field data
status	text	NO	'queued'	Delivery status
delivery_id	text	YES	-	Provider delivery ID
error_message	text	YES	-	Delivery error message
sent_at	integer	YES	-	Sent timestamp (Unix)
opened_at	integer	YES	-	Opened timestamp (Unix)
clicked_at	integer	YES	-	Clicked timestamp (Unix)

Status Values: queued, sending, sent, delivered, opened, clicked, bounced, complained, failed

Indexes:

• idx_recipients_campaign ON (campaign_id)
• idx_recipients_status ON (status)

Foreign Keys:

• campaign_recipients_campaign_id_fkey → campaigns(id)
• campaign_recipients_contact_id_fkey → contacts(id)

---

3. contacts

Stores contact information for email recipients.

Column	Type	Nullable	Default	Description
id	text	NO	-	Primary key (UUID)
first_name	text	NO	''	Contact first name
last_name	text	NO	''	Contact last name
email	text	NO	-	Contact email (unique)
company	text	NO	''	Company name
notes	text	NO	''	Internal notes
custom_fields	text	NO	'{}'	JSON custom field data
status	text	NO	'active'	Contact status
created	integer	NO	-	Creation timestamp (Unix)
updated	integer	NO	-	Update timestamp (Unix)

Status Values: active, unsubscribed, bounced, complained, invalid

Indexes:

• contacts_email_unique UNIQUE ON (email)
• idx_contacts_email_uniq UNIQUE ON (email)
• idx_contacts_company ON (company)

Constraints:

• Email must be unique across all contacts

---

4. providers

Stores email provider configurations with encrypted credentials.

Column	Type	Nullable	Default	Description
id	text	NO	-	Primary key (UUID)
nickname	text	NO	-	Display name for provider
type	text	NO	-	Provider type
encrypted_credentials	text	NO	-	AES-256 encrypted credentials
iv	text	NO	-	Initialization vector for decryption
is_default	integer	NO	false	Default provider flag
status	text	NO	'unverified'	Provider status
monthly_limit	integer	NO	100000	Monthly email limit
daily_limit	integer	NO	5000	Daily email limit
created	integer	NO	-	Creation timestamp (Unix)
updated	integer	NO	-	Update timestamp (Unix)

Provider Types: zeptomail, smtp, mock

Status Values: unverified, active, paused, exhausted, failed

Indexes:

• idx_providers_type ON (type)

Security Note: Credentials are encrypted using AES-256-GCM with a unique IV per provider.

---

5. campaign_templates

Reusable email templates for campaigns.

Column	Type	Nullable	Default	Description
id	text	NO	-	Primary key (UUID)
name	text	NO	-	Template display name
subject	text	NO	''	Default subject line
html_content	text	NO	-	HTML template content
created	integer	NO	-	Creation timestamp (Unix)
updated	integer	NO	-	Update timestamp (Unix)

Template Variables: Templates support {{variable}} syntax for personalization.

Built-in Templates:

1. Welcome Email - Indigo accent banner
2. Password Reset - Slate/Charcoal security block
3. Monthly Usage Report - Teal metrics table
4. Billing Receipt - Slate payment itemization receipt
5. Trial Expiration Warning - Amber alert box
6. Feature Announcement - Violet visual automation outline

---

6. campaign_events

Tracks all email events (opens, clicks, bounces, etc.).

Column	Type	Nullable	Default	Description
id	text	NO	-	Primary key (UUID)
campaign_id	text	NO	-	FK → campaigns.id
recipient_id	text	YES	-	FK → campaign_recipients.id
event_type	text	NO	-	Type of event
metadata	text	NO	'{}'	Event metadata JSON
created_at	integer	NO	-	Event timestamp (Unix)

Event Types: sent, delivered, opened, clicked, bounced, complained, unsubscribed

Indexes:

• idx_events_campaign ON (campaign_id)

Foreign Keys:

• campaign_events_campaign_id_fkey → campaigns(id)
• campaign_events_recipient_id_fkey → campaign_recipients(id)

Metadata Examples:

• opened: {"user_agent": "iPhone Mail", "ip_address": "192.168.1.1"}
• clicked: {"url": "https://example.com", "link_text": "Learn More"}
• bounced: {"reason": "mailbox full", "code": "5.2.2"}

---

7. tags

Contact segmentation tags for audience targeting.

Column	Type	Nullable	Default	Description
id	text	NO	-	Primary key (UUID)
name	text	NO	-	Tag name (unique)

Indexes:

• tags_name_unique UNIQUE ON (name)
• idx_tags_name_uniq UNIQUE ON (name)

Common Tags: customer, prospect, newsletter, vip, inactive, beta, enterprise

---

8. contact_tags

Junction table for contact-tag relationships.

Column	Type	Nullable	Default	Description
contact_id	text	NO	-	FK → contacts.id
tag_id	text	NO	-	FK → tags.id

Primary Key: Composite (contact_id, tag_id)

Foreign Keys:

• contact_tags_contact_id_fkey → contacts(id)
• contact_tags_tag_id_fkey → tags(id)

---

9. provider_usage

Tracks daily email usage per provider for quota management.

Column	Type	Nullable	Default	Description
id	text	NO	-	Primary key (UUID)
provider_id	text	NO	-	FK → providers.id
date	text	NO	-	Date in YYYY-MM-DD format
emails_sent	integer	NO	0	Emails sent on this date

Indexes:

• idx_usage_provider_date ON (provider_id, date)

Foreign Keys:

• provider_usage_provider_id_fkey → providers(id)

Usage Reset: Daily counters reset at midnight UTC, monthly counters reset on 1st of month.

---

10. activity_logs

Audit trail for system actions and user activities.

Column	Type	Nullable	Default	Description
id	text	NO	-	Primary key (UUID)
type	text	NO	-	Activity type
description	text	NO	-	Human-readable description
created	integer	NO	-	Activity timestamp (Unix)

Activity Types: campaign_created, campaign_sent, contact_imported, provider_added, template_created, user_login, user_logout, settings_updated

Retention Policy: Logs retained for 90 days, then archived.

---

11. settings

Key-value store for system configuration.

Column	Type	Nullable	Default	Description
key	text	NO	-	Setting key (primary key)
value	text	NO	-	Setting value (JSON string)

Common Settings:

• default_from_email - Default sender email
• default_from_name - Default sender name
• unsubscribe_url - Unsubscribe page URL
• tracking_enabled - Global tracking toggle
• rate_limit_per_minute - API rate limit
• bounce_threshold - Bounce rate threshold
• complaint_threshold - Complaint rate threshold

---

Database Functions & Utilities

Encryption Functions

Encrypt Provider Credentials:

async function encryptCredentials(plainText: string, key: string): Promise<{encrypted: string, iv: string}> {
  // AES-256-GCM encryption with unique IV
}

Decrypt Provider Credentials:

async function decryptCredentials(encrypted: string, iv: string, key: string): Promise<string> {
  // AES-256-GCM decryption
}

Analytics Functions

Calculate Campaign Metrics:

-- Calculate open rate for campaign
SELECT 
  COUNT(*) as total_sent,
  COUNT(CASE WHEN opened_at IS NOT NULL THEN 1 END) as total_opened,
  ROUND((COUNT(CASE WHEN opened_at IS NOT NULL THEN 1 END) * 100.0 / COUNT(*)), 2) as open_rate
FROM campaign_recipients 
WHERE campaign_id = ? AND status IN ('sent', 'delivered', 'opened', 'clicked');

Get Provider Usage:

-- Get monthly usage for provider
SELECT 
  SUM(emails_sent) as monthly_usage
FROM provider_usage 
WHERE provider_id = ? AND date LIKE ? || '%';

Maintenance Functions

Cleanup Old Data:

-- Archive campaign events older than 90 days
DELETE FROM campaign_events WHERE created_at < ?;

-- Cleanup failed campaign recipients older than 30 days
DELETE FROM campaign_recipients WHERE status = 'failed' AND sent_at < ?;

Migration Strategy

Version Control

• All schema changes via Drizzle migrations
• Migration files stored in /drizzle/ directory
• Sequential migration numbering (0000_, 0001_, etc.)
• Rollback scripts for each migration

Migration Examples

Initial Schema (0000_bent_nekra.sql):

CREATE TABLE campaigns (...);
CREATE TABLE contacts (...);
-- etc.

Schema Extension (0001_dark_william_stryker.sql):

ALTER TABLE contacts ADD status text DEFAULT 'active' NOT NULL;

Production Migration

1. Test migrations in development environment
2. Create backup before applying migrations
3. Apply migrations during maintenance window
4. Verify data integrity post-migration
5. Update application code to match schema

Backup & Recovery

Backup Strategy

• Daily: Full database export
• Hourly: Incremental changes
• Real-time: WAL (Write-Ahead Logging) for point-in-time recovery

Recovery Procedures

1. Identify corruption or data loss
2. Restore most recent backup
3. Apply WAL logs up to point of failure
4. Verify data consistency
5. Resume normal operations

Performance Optimization

Indexing Strategy

Read-Optimized Indexes:

• Campaign lookups by status and date
• Recipient queries by campaign and status
• Contact searches by email and company
• Provider usage by date ranges

Write Optimization:

• Batch inserts for campaign recipients
• Asynchronous event logging
• Queue-based email sending
• Delayed index updates for bulk operations

Query Patterns

High-Frequency Queries:

-- Dashboard statistics
SELECT status, COUNT(*) FROM campaigns GROUP BY status;

-- Recent campaigns
SELECT * FROM campaigns ORDER BY created DESC LIMIT 10;

-- Provider usage today
SELECT emails_sent FROM provider_usage WHERE provider_id = ? AND date = ?;

-- Contact search
SELECT * FROM contacts WHERE email LIKE ? OR first_name LIKE ? OR last_name LIKE ?;

---

Navigation

← System Architecture | → Features & Workflows