Blueprint — Newsletter & Mailing Lists

Status: planned · Slug: newsletter · Kind: Cross-cutting · Module #18 Sits on top of Comms — uses Comms for delivery, owns editorial workflow + subscriber lifecycle.

1. Module summary

Newsletter is the periodical-publishing module: editorial calendar, drafts, internal review/approval, multi-channel issue dispatch, double-opt-in subscriber management, engagement analytics, and searchable archive. It is distinct from Comms (transactional one-off messages) by being editorial — issues are composed, reviewed, scheduled, and sent as broadcasts. Subscribers manage their own preferences via public sign-up forms, double-opt-in confirmation, and a preference-centre / unsubscribe page. Issue delivery delegates entirely to comms.send_to_segment — Newsletter never touches a provider directly. Engagement events (open, click, unsubscribe) are mirrored from Comms back into Newsletter's newsletter_engagement table for issue-level analytics.

2. Data model

# src/aayojana/newsletter/models.py
from datetime import datetime
from sqlalchemy import (
    JSON, Boolean, DateTime, ForeignKey, Integer, String, Text,
    UniqueConstraint, Index,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship

from aayojana.models.base import AuditMixin, Base, TenantMixin, uuid4_str


class MailingList(Base, TenantMixin, AuditMixin):
    """A named subscriber roster. Each list maps to a Comms audience segment
    so issue dispatch is `comms.send_to_segment(segment_name=list.segment_name)`.
    """
    __tablename__ = "mailing_lists"
    __table_args__ = (
        UniqueConstraint("tenant_id", "slug", name="uq_mailing_list_tenant_slug"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    name: Mapped[str] = mapped_column(String(160), nullable=False)
    slug: Mapped[str] = mapped_column(String(80), nullable=False)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)

    # Public sign-up
    subscribe_page_slug: Mapped[str] = mapped_column(String(80), nullable=False)
    # Public URL: /newsletter/subscribe/<subscribe_page_slug>
    double_opt_in_required: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    welcome_template_name: Mapped[str | None] = mapped_column(String(120), nullable=True)
    # Comms template sent on confirm.
    confirm_template_name: Mapped[str | None] = mapped_column(String(120), nullable=True)
    # Comms template sent on initial subscribe (contains confirm link).
    unsubscribe_link_template: Mapped[str] = mapped_column(
        String(255), nullable=False,
        default="/newsletter/unsubscribe/{token}",
    )

    # Comms hookup — newsletter delegates all delivery here.
    segment_name: Mapped[str] = mapped_column(String(120), nullable=False)
    # Auto-managed Comms AudienceSegment scoped to "active subscribers of this list".

    default_channels: Mapped[list] = mapped_column(JSON, nullable=False, default=lambda: ["email"])
    # ['email'] | ['email','whatsapp'] | ['email','postal']
    archive_public: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    # Whether issues archive at /newsletter/archive/<slug> is publicly visible.

    active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)


class MailingListSubscription(Base, TenantMixin, AuditMixin):
    """Per-(list, subscriber) state. Subscriber may be a Member (FK) or an
    external email (no Member row). Double-opt-in tokens are SHA-256 hashed
    in DB; the original token only exists in the confirm-link emailed."""
    __tablename__ = "mailing_list_subscriptions"
    __table_args__ = (
        UniqueConstraint("list_id", "subscriber_kind", "subscriber_key",
                         name="uq_mlsub_list_subscriber"),
        Index("ix_mlsub_token", "double_opt_in_token_hash"),
        Index("ix_mlsub_list_status", "list_id", "status"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    list_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("mailing_lists.id"), nullable=False
    )
    subscriber_kind: Mapped[str] = mapped_column(String(16), nullable=False)
    # 'member' | 'external'
    subscriber_key: Mapped[str] = mapped_column(String(120), nullable=False)
    # member.id (UUID) or normalised lower-case email
    member_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("members.id"), nullable=True
    )
    external_email: Mapped[str | None] = mapped_column(String(255), nullable=True)
    external_name: Mapped[str | None] = mapped_column(String(160), nullable=True)

    status: Mapped[str] = mapped_column(String(20), nullable=False, default="pending")
    # 'pending' (sent confirm, awaiting click) | 'active' | 'unsubscribed' | 'bounced'
    double_opt_in_token_hash: Mapped[str | None] = mapped_column(String(64), nullable=True)
    confirm_sent_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    subscribed_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    # The moment status flipped from 'pending' → 'active' (or direct subscribe if
    # double_opt_in_required=False).
    unsubscribed_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    unsubscribe_reason: Mapped[str | None] = mapped_column(String(120), nullable=True)
    # 'user_clicked' | 'hard_bounce' | 'admin_removal' | 'spam_complaint'
    source: Mapped[str | None] = mapped_column(String(40), nullable=True)
    # 'public_form' | 'admin_import' | 'qr_code' | 'event_signup' | 'donor_auto'
    preferences: Mapped[dict | None] = mapped_column(JSON, nullable=True)
    # {'frequency': 'monthly', 'language': 'sa', 'topics': ['festivals','dharma']}


class NewsletterIssue(Base, TenantMixin, AuditMixin):
    """One issue of a newsletter. Goes through draft → review → approved → sent."""
    __tablename__ = "newsletter_issues"
    __table_args__ = (
        Index("ix_issues_list_status", "list_id", "status"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    list_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("mailing_lists.id"), nullable=False
    )
    issue_number: Mapped[int | None] = mapped_column(Integer, nullable=True)
    # Optional sequential per-list (e.g. "Issue 47").
    title: Mapped[str] = mapped_column(String(255), nullable=False)
    subject: Mapped[str] = mapped_column(String(255), nullable=False)
    summary: Mapped[str | None] = mapped_column(Text, nullable=True)
    cover_image_url: Mapped[str | None] = mapped_column(String(500), nullable=True)

    body_template_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("comms_templates.id"), nullable=True
    )
    # Pointer to a Comms Template (which holds the actual Jinja body).

    scheduled_for: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    sent_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    comms_job_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("comms_jobs.id"), nullable=True
    )
    # Set when issue is dispatched; ties analytics back to comms_job_recipients.

    status: Mapped[str] = mapped_column(String(20), nullable=False, default="draft")
    # 'draft' | 'in_review' | 'approved' | 'scheduled' | 'sent' | 'cancelled'

    editor_user_id: Mapped[int | None] = mapped_column(
        Integer, ForeignKey("users.id"), nullable=True
    )
    approver_user_id: Mapped[int | None] = mapped_column(
        Integer, ForeignKey("users.id"), nullable=True
    )

    public_archive_slug: Mapped[str | None] = mapped_column(String(120), nullable=True)
    # If list.archive_public, this is the slug under /newsletter/archive/<list>/<slug>.


class NewsletterDraft(Base, TenantMixin, AuditMixin):
    """One row per draft revision — every save is a new row, immutable.
    Allows rollback + reviewer comments on specific versions."""
    __tablename__ = "newsletter_drafts"
    __table_args__ = (
        Index("ix_drafts_issue_version", "issue_id", "version"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    issue_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("newsletter_issues.id"), nullable=False
    )
    version: Mapped[int] = mapped_column(Integer, nullable=False)
    body_html: Mapped[str | None] = mapped_column(Text, nullable=True)
    body_markdown: Mapped[str | None] = mapped_column(Text, nullable=True)
    saved_by_user_id: Mapped[int | None] = mapped_column(
        Integer, ForeignKey("users.id"), nullable=True
    )

    review_status: Mapped[str | None] = mapped_column(String(20), nullable=True)
    # 'pending' | 'approved' | 'changes_requested'
    reviewer_user_id: Mapped[int | None] = mapped_column(
        Integer, ForeignKey("users.id"), nullable=True
    )
    review_comments: Mapped[str | None] = mapped_column(Text, nullable=True)
    reviewed_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)


class NewsletterEngagement(Base, TenantMixin, AuditMixin):
    """Per-(issue, recipient) engagement events mirrored from Comms delivery
    log. Denormalised for fast issue-level analytics queries.

    Source of truth: communication_log.opened_at/clicked_at/bounced_at.
    This table is derived (refreshed on delivery webhook fire)."""
    __tablename__ = "newsletter_engagement"
    __table_args__ = (
        UniqueConstraint("issue_id", "recipient_type", "recipient_id",
                         name="uq_engagement_issue_recipient"),
        Index("ix_engagement_issue_status", "issue_id", "delivery_status"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    issue_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("newsletter_issues.id"), nullable=False
    )
    recipient_type: Mapped[str] = mapped_column(String(16), nullable=False)
    recipient_id: Mapped[str] = mapped_column(String(64), nullable=False)
    communication_log_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("communication_log.id"), nullable=True
    )

    delivery_status: Mapped[str] = mapped_column(String(20), nullable=False, default="queued")
    sent_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    delivered_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    opened_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    open_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    clicked_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    click_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    clicked_links: Mapped[list | None] = mapped_column(JSON, nullable=True)
    # [{'url': '...', 'clicked_at': '...'}]
    bounced: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    unsubscribed: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)

3. Reuse map

4. API surface

Mounted under /api/newsletter/ (admin) and /newsletter/ (public).

5. Service layer

# src/aayojana/newsletter/service.py
from sqlalchemy.ext.asyncio import AsyncSession


async def subscribe(
    db: AsyncSession, *,
    tenant_id: str, list_id: str,
    member_id: str | None = None,
    external_email: str | None = None,
    external_name: str | None = None,
    source: str = "public_form",
    preferences: dict | None = None,
) -> "MailingListSubscription":
    """Creates a subscription. If list.double_opt_in_required:
       - status='pending'
       - generate token, hash to db, send confirm email via comms.send()
       - return immediately (status check via separate endpoint)
    Else:
       - status='active', subscribed_at=now()
       - send welcome template if configured
    Idempotent on (list_id, subscriber_key) — re-subscribing an unsubscribed
    member flips status back to 'pending' (not 'active' — must re-confirm)."""


async def confirm_subscription(
    db: AsyncSession, *, tenant_id: str, token: str,
) -> bool:
    """Hashes token, finds matching pending subscription, flips to 'active'.
    Returns True on success, False on invalid/expired token."""


async def unsubscribe(
    db: AsyncSession, *, tenant_id: str, token: str,
    reason: str = "user_clicked",
) -> bool:
    """One-click unsub. Token is per-subscription (issued at subscribe time
    and refreshed each issue). Adds a comms_suppressions row so even a
    re-import doesn't accidentally re-mail them."""


async def update_preferences(
    db: AsyncSession, *, tenant_id: str, token: str,
    preferences: dict,
) -> "MailingListSubscription": ...


async def create_issue(
    db: AsyncSession, *,
    tenant_id: str, list_id: str,
    title: str, subject: str, summary: str | None = None,
    editor_user_id: int | None = None,
) -> "NewsletterIssue":
    """Creates a draft issue; allocates issue_number; returns ready for
    drafting."""


async def save_draft(
    db: AsyncSession, *, tenant_id: str, issue_id: str,
    body_html: str, body_markdown: str | None = None,
    saved_by_user_id: int | None = None,
) -> "NewsletterDraft": ...


async def submit_for_review(
    db: AsyncSession, *, tenant_id: str, issue_id: str,
) -> "NewsletterIssue": ...


async def approve_issue(
    db: AsyncSession, *, tenant_id: str, issue_id: str,
    approver_user_id: int,
) -> "NewsletterIssue":
    """draft → approved. Materialises the issue's latest draft into a Comms
    TemplateVersion (created via comms.register_template + new version)."""


async def publish_issue(
    db: AsyncSession, *, tenant_id: str, issue_id: str,
    send_immediately: bool = True,
    scheduled_for: datetime | None = None,
) -> "CommsJob":
    """Approved → scheduled or sent. Delegates dispatch to comms.send_to_segment.
    Returns the CommsJob (so caller can show progress)."""


async def refresh_engagement(
    db: AsyncSession, *, tenant_id: str, issue_id: str,
) -> None:
    """Pulls the latest opens/clicks/bounces from communication_log into
    newsletter_engagement. Called by scheduler every 15 min while an issue
    is hot, then daily after 7 days."""

6. UI / Templates

src/aayojana/templates/newsletter/. Reuses frame.html for admin chrome; public pages use a dedicated lightweight chrome (no auth UI).

7. Migration plan

8. Cross-module dependencies

Newsletter consumes Comms (delivery), Members Suite (member subscribers + tag-based segmentation), Reports (issue analytics + archive exports).

# Issue dispatch
job = await comms.send_to_segment(
    db, tenant_id=ten, segment_name=mlist.segment_name,
    template_name=f"newsletter_issue_{issue.id}",
    channels=mlist.default_channels, vars={"issue": issue},
)

# Public subscribe (no auth) → confirm email via comms
await comms.send(
    db, tenant_id=ten, channel="email",
    recipient_type="external_email", recipient_id=external_email,
    template_name=mlist.confirm_template_name,
    vars={"confirm_url": f"https://{tenant.domain}/newsletter/confirm/{token}"},
    idempotency_key=f"newsletter:confirm:{sub.id}",
)

9. Implementation phases

Phase A — Subscribe + compose + send (2 weeks): 1. Land migration 0032. 2. Implement subscribe, confirm_subscription, unsubscribe (no preference centre yet). 3. Issue editor (basic textarea HTML; rich-text in Phase C). 4. publish_issue → comms.send_to_segment. 5. Public subscribe + confirm + unsub pages.

Phase B — Editorial workflow + segmentation (2 weeks): 6. Drafts table with version history + reviewer comments. 7. Approve/reject flow with role-gated approver. 8. Preference centre (frequency, topics). 9. Member-suite tag-based mailing lists (e.g. "Annadana Donors").

Phase C — Analytics + archive + rich editor (2 weeks): 10. Engagement materialisation from communication_log. 11. Issue analytics dashboard. 12. Public archive pages with full-text search. 13. TipTap rich-text editor. 14. Bounce-driven auto-unsubscribe (after 1 hard bounce per Comms config).

10. Open questions

1. Editor surface — plain HTML, Markdown, or rich-text WYSIWYG (TipTap)? Recommendation: Markdown source-of-truth + TipTap for visual editing (TipTap has a markdown plugin). Best of both.
2. Public subscribe form hosting — dedicated subdomain (newsletter.{tenant}.aayojana.dharmaposhanam.in) or per-tenant page? Recommendation: Per-tenant page at https://{tenant.domain}/newsletter/subscribe/{slug} so subscribers see tenant branding.
3. Bounced email handling — auto-unsubscribe after how many bounces? Recommendation: Defer to Comms config (1 hard / 3 soft).
4. Member auto-subscription on donate — when a Member donates, auto-add to "Donors" mailing list? Recommendation: Only if the donation form has an opt-in checkbox (DPDP compliance).
5. Issue archive SEO — index public issues in robots.txt, or noindex? Recommendation: Index — institutional content benefits from search reach.
6. Multi-language issues — separate issue per language, or single issue with translations? Recommendation: Separate issues; subscriber language preference selects via segment filter.
7. Issue-number reset — calendar year, FY, or never? Recommendation: Per-list config flag; default never (continuous).
8. Subscriber GDPR/DPDP export — give subscribers a "download my data" endpoint? Recommendation: Yes — export JSON of all subscriptions + engagement events for the email; required by DPDP §11 right-to-portability.
9. Coordination with Agent 3's member_subscription_lists — overlap risk. Recommendation: Treat Newsletter's mailing_lists as authoritative; Members' table becomes a view or migrates into Newsletter in 0032.