Database Schema¶

Subscribe Flow uses PostgreSQL 15+ as its primary database. All tenant data is isolated via organization_id foreign keys (multi-tenant, shared-database model).

Entity Relationship Diagram¶

erDiagram
    ORGANIZATIONS ||--o{ ORGANIZATION_MEMBERS : has
    USERS ||--o{ ORGANIZATION_MEMBERS : belongs_to
    ORGANIZATIONS ||--o{ SUBSCRIBERS : owns
    ORGANIZATIONS ||--o{ TAGS : owns
    ORGANIZATIONS ||--o{ API_KEYS : has
    ORGANIZATIONS ||--o{ WEBHOOK_ENDPOINTS : configures
    ORGANIZATIONS ||--o{ EMAIL_TEMPLATES : owns
    ORGANIZATIONS ||--o{ CAMPAIGNS : owns
    ORGANIZATIONS ||--o{ EMAIL_TRIGGERS : owns
    ORGANIZATIONS ||--o{ AUDIT_LOGS : records
    SUBSCRIBERS ||--o{ SUBSCRIBER_TAGS : has
    TAGS ||--o{ SUBSCRIBER_TAGS : has
    SUBSCRIBERS ||--o{ SUBSCRIPTION_HISTORY : has
    TAGS ||--o{ SUBSCRIPTION_HISTORY : involves
    SUBSCRIBERS ||--o{ SUBSCRIBER_NOTES : has
    SUBSCRIBERS ||--o{ EMAIL_SENDS : receives
    CAMPAIGNS ||--o{ EMAIL_SENDS : generates
    WEBHOOK_ENDPOINTS ||--o{ WEBHOOK_DELIVERIES : delivers

    ORGANIZATIONS {
        uuid id PK
        string name
        string slug UK
        boolean is_platform_admin
        string stripe_customer_id UK
        string stripe_subscription_id UK
        string subscription_tier
        string subscription_status
        timestamp current_period_end
        int max_subscribers
        int max_emails_per_month
        int emails_sent_this_month
        string billing_email
        string email_from_name
        string email_from_address
        string custom_domain
        string custom_domain_status
        string resend_domain_id
        timestamp created_at
        timestamp updated_at
    }

    USERS {
        uuid id PK
        string email UK
        string name
        timestamp last_login_at
        timestamp created_at
        timestamp updated_at
    }

    ORGANIZATION_MEMBERS {
        uuid id PK
        uuid organization_id FK
        uuid user_id FK
        string role
        timestamp created_at
    }

    MAGIC_LINK_TOKENS {
        uuid id PK
        string email
        string token_hash UK
        timestamp expires_at
        timestamp used_at
        timestamp created_at
    }

    SUBSCRIBERS {
        uuid id PK
        uuid organization_id FK
        string email
        string first_name
        string last_name
        string status
        string resend_contact_id
        jsonb metadata
        timestamp created_at
        timestamp updated_at
    }

    TAGS {
        uuid id PK
        uuid organization_id FK
        string name
        string display_name
        string description
        string category
        boolean is_active
        int sort_order
        timestamp created_at
        timestamp updated_at
    }

    SUBSCRIBER_TAGS {
        uuid id PK
        uuid subscriber_id FK
        uuid tag_id FK
        timestamp subscribed_at
        string source
    }

    SUBSCRIPTION_HISTORY {
        uuid id PK
        uuid subscriber_id FK
        uuid tag_id FK
        string action
        string source
        string ip_address
        string user_agent
        timestamp changed_at
    }

    API_KEYS {
        uuid id PK
        uuid organization_id FK
        string name
        string key_hash
        string prefix
        jsonb permissions
        timestamp last_used_at
        timestamp expires_at
        timestamp created_at
    }

    WEBHOOK_ENDPOINTS {
        uuid id PK
        uuid organization_id FK
        string url
        string secret
        jsonb events
        boolean is_active
        timestamp created_at
    }

    WEBHOOK_DELIVERIES {
        uuid id PK
        uuid organization_id FK
        uuid webhook_endpoint_id FK
        string event_type
        string status
        int status_code
        int response_time_ms
        timestamp created_at
    }

    EMAIL_TEMPLATES {
        uuid id PK
        uuid organization_id FK
        string name
        string subject
        text body
        timestamp created_at
        timestamp updated_at
    }

    CAMPAIGNS {
        uuid id PK
        uuid organization_id FK
        string name
        string status
        uuid template_id FK
        timestamp scheduled_at
        timestamp sent_at
        timestamp created_at
        timestamp updated_at
    }

    EMAIL_SENDS {
        uuid id PK
        uuid organization_id FK
        uuid subscriber_id FK
        uuid campaign_id FK
        string status
        timestamp sent_at
        timestamp created_at
    }

    EMAIL_TRIGGERS {
        uuid id PK
        uuid organization_id FK
        string name
        string event_type
        uuid template_id FK
        boolean is_active
        timestamp created_at
        timestamp updated_at
    }

    AUDIT_LOGS {
        uuid id PK
        uuid organization_id FK
        string action
        string entity_type
        uuid entity_id
        jsonb changes
        timestamp created_at
    }

    SUBSCRIBER_NOTES {
        uuid id PK
        uuid subscriber_id FK
        uuid organization_id FK
        text content
        timestamp created_at
        timestamp updated_at
    }

Table Details¶

Multi-Tenant Core Tables¶

organizations¶

Central tenant table. Each organization has its own isolated data set, billing configuration, and email sending settings.

users¶

Dashboard users who authenticate via magic links.

organization_members¶

Junction table linking users to organizations with roles.

1
2

-- Unique constraint: one membership per user per org
UNIQUE (organization_id, user_id)

magic_link_tokens¶

One-time-use tokens for passwordless authentication.

Subscriber Management Tables¶

subscribers¶

Stores all subscribers of an organization.

tags¶

Defines available tags for subscription preferences.

subscriber_tags¶

Junction table for subscriber-tag relationships.

1
2

-- Unique Constraint
UNIQUE (subscriber_id, tag_id)

subscription_history¶

Complete audit trail of all preference changes.

subscriber_notes¶

Notes attached to subscribers by team members.

Email & Campaign Tables¶

All of these tables include organization_id FK for tenant isolation.

• email_templates -- Reusable email templates
• campaigns -- Email campaigns with scheduling
• email_sends -- Individual email send records
• email_triggers -- Event-triggered email automations

Webhook Tables¶

• webhook_endpoints -- Configured outgoing webhook URLs with events and signing secrets
• webhook_deliveries -- Delivery log with status, response time, retry tracking

Other Tables¶

• api_keys -- Organization-scoped API keys with hashed storage and scopes
• audit_logs -- Organization-scoped audit trail of all admin actions

Indexes¶

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- Performance-critical indexes
CREATE INDEX idx_subscribers_org_email ON subscribers(organization_id, email);
CREATE INDEX idx_subscribers_status ON subscribers(status);
CREATE INDEX idx_subscriber_tags_subscriber ON subscriber_tags(subscriber_id);
CREATE INDEX idx_subscriber_tags_tag ON subscriber_tags(tag_id);
CREATE INDEX idx_subscription_history_subscriber ON subscription_history(subscriber_id, changed_at DESC);
CREATE INDEX idx_tags_org_category ON tags(organization_id, category);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_magic_link_tokens_email ON magic_link_tokens(email);
CREATE INDEX idx_org_members_org ON organization_members(organization_id);
CREATE INDEX idx_org_members_user ON organization_members(user_id);
CREATE INDEX idx_organizations_slug ON organizations(slug);

Multi-Tenancy Pattern¶

All data tables include an organization_id column that references the organizations table. This ensures:

1. Data Isolation: Queries always filter by organization_id
2. Cascading Deletes: Organization deletion removes all associated data
3. Unique Constraints: Email uniqueness, tag names, etc. are scoped per organization (UNIQUE (organization_id, email))

The organization context is resolved at the API layer:

• API Key auth: Organization is determined from the API key record
• Session auth: Organization is provided via X-Organization-Id header and verified against user membership

Migrations¶

Migrations are managed with Alembic:

1
2
3
4
5
6
7
8

# Create a new migration
uv run alembic revision --rev-id 003 -m "add_webhooks"

# Run migrations
uv run alembic upgrade head

# Downgrade
uv run alembic downgrade -1

Further Documentation¶

• API Design
• Architecture Overview