Subscription emails

Overview

The feature consists of several components:

• A settings UI where users can manage their email preferences
• A database schema to store subscription preferences and status
• A cron-triggered API route that sends scheduled emails
• Email templates and content generation logic
• A type-safe email sending system with status tracking

Database structure

Tables

email_type

Stores the different types of emails that can be sent:

CREATE TABLE public.email_type (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  status email_type_status NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ,
  deleted_at TIMESTAMPTZ
);

email_subscription

Stores user preferences and status for each email type:

CREATE TABLE public.email_subscription (
  id UUID PRIMARY KEY,
  profile_id UUID NOT NULL,
  email_type_id UUID NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  frequency email_subscription_frequency NOT NULL,
  status email_subscription_status NOT NULL DEFAULT 'pending',
  error_message TEXT,
  last_sent_at TIMESTAMPTZ,
  next_send_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ,
  FOREIGN KEY (profile_id) REFERENCES public.profile (id),
  FOREIGN KEY (email_type_id) REFERENCES public.email_type (id),
  UNIQUE (profile_id, email_type_id, is_active)
);

Enums

CREATE TYPE public.email_subscription_frequency AS ENUM(
  '1D',   -- Daily
  '1W',   -- Weekly
  '1M',   -- Monthly
  'transactional'
);

CREATE TYPE public.email_subscription_status AS ENUM(
  'pending',
  'sent',
  'failed'
);

CREATE TYPE public.email_type_status AS ENUM(
  'active',
  'inactive',
  'deprecated'
);

Automatic subscription creation

The system automatically creates default email subscriptions in two scenarios:

1. When a new user joins a group (via handle_group_user_creation trigger)
2. During backfill for existing users (via migration scripts)

Default subscriptions are only created for regular users, not for advisors.

User interface

The email preferences UI is implemented in email-notifications-card.tsx. It provides:

• A toggle to enable/disable email subscriptions
• A frequency selector (daily/weekly/monthly)
• Display of the next scheduled email date
• Status indicators for subscription state
• Automatic saving of changes

Email sending system

Architecture

1. Trigger: A cron job calls /api/send-subscription-emails
2. Processing:
   • Fetches due subscriptions
   • Processes them in chunks of 5 to avoid overload
   • Updates last_sent_at, next_send_at, and status after sending
   • Records any error messages if sending fails

Period ranges

The system uses a unified range system with the following types:

export enum Range {
  OneDay = "1D",
  OneWeek = "1W",
  OneMonth = "1M",
  ThreeMonths = "3M",
  SixMonths = "6M",
  OneYear = "1Y",
  All = "ALL",
}

export type EmailRange = Range.OneDay | Range.OneWeek | Range.OneMonth;

Each range includes:

• Sampling intervals for data aggregation
• Display text for UI and emails
• Period calculation logic
• Validation through type guards

Email providers

The system supports two email providers:

• Production: Resend
• Development: Local Inbucket SMTP server

Configuration is determined by environment variables:

• VERCEL_ENV: Determines if in production
• MAIL_PROVIDER: Can be set to "inbucket" for local development

Type safety

The email system uses TypeScript to ensure type safety:

type EmailTemplate =
  | "waitlistInvite"
  | "welcomeEmail"
  | "userInvite"
  | "acceptedInvite"
  | "removedMember"
  | "cancelledInvite"
  | "leftSpace"
  | "netWorthReport";

interface EmailSubscription {
  id: string;
  profileId: string;
  emailTypeId: string;
  isActive: boolean;
  frequency: EmailSubscriptionFrequency;
  status: EmailSubscriptionStatus;
  errorMessage?: string;
  lastSentAt?: Date;
  nextSendAt: Date;
}

Net worth report implementation

The net worth report includes:

1. Period-based net worth variations
2. Top movers analysis showing biggest gainers and losers
3. Localized content using the user's preferred language
4. Formatted currency and percentage values

Top movers calculation

The system:

1. Retrieves holdings with enriched account and security information
2. Calculates variations for the specified period
3. Ranks holdings by absolute variation
4. Includes both positive and negative movers in the report

Admin client support

For system-generated emails, queries can use an admin client to ensure access to necessary data:

function getHoldingsData(spaceId: string, useAdminClient = false) {
  const client = useAdminClient ? createAdminClient() : createClient();
  // ... query logic
}

Maintenance and troubleshooting

Status tracking

Each email subscription maintains a status:

• pending: Awaiting next send
• sent: Successfully delivered
• failed: Delivery failed (includes error message)

Audit logging

All email sending attempts are logged with:

• Operation type: email_send
• Status: SUCCESS or ERROR
• Detailed metadata including template, recipients, and environment
• Full error message if failed
• Subscription status updates

Development setup

1. Ensure Inbucket is running locally (port 54325)
2. Set MAIL_PROVIDER=inbucket in environment
3. Emails will be captured by Inbucket instead of being sent

Adding new email types

1. Add new type to EmailType enum
2. Create migration to insert new type into email_type table with appropriate status
3. Implement email template component
4. Add template props interface to TemplateProps type
5. Create content generation logic if needed
6. Update documentation

Security considerations

• API route is protected by CRON_SECRET in production
• Email subscriptions are scoped to profile ID
• Admin client usage is restricted to system operations
• Sensitive data is not stored in email templates
• API keys are environment-specific
• Emails use notification-specific domains per environment