Database Schema

Relevant source files

The following files were used as context for generating this technical wiki page:

• src/lib/db.ts
• src/lib/openapi.ts
• src/app/app/page.tsx
• README.md
• src/app/api/workspaces/settings/route.ts
• src/lib/auth.ts

The database schema for Send Again is designed to support a multi-tenant email campaign platform. It manages core entities such as workspaces, users, contacts, email send jobs, and analytics events. The system leverages Supabase (PostgreSQL) as its primary data store, utilizing relational structures to maintain data integrity across complex workflows like campaign tracking and credit-based billing.

The schema is primarily defined through TypeScript interfaces that map to database tables, as well as the initialization requirements described in the project documentation. It supports scoped access via API keys and workspace memberships, ensuring that data is isolated between different sending domains.

Sources: README.md:105-107, src/lib/db.ts:1-10

Core Entities and Relationships

The system is organized around the Workspace as the primary tenant container. Every other entity, including contacts, API keys, and send jobs, is associated with a specific workspace ID.

Entity Relationship Overview

The following diagram illustrates the primary relationships between the core database tables.

The diagram shows the central role of workspace_settings in anchoring users, billing, and campaign data.
Sources: src/lib/db.ts:192-194, src/lib/db.ts:318-320, src/lib/db.ts:1003-1005

Workspace and Membership

Workspaces represent individual domains or organizations. Access is controlled through a membership table that links users to workspaces with specific roles.

Table: workspace_settings

Stores the configuration for a sending domain.

Field	Type	Description
id	String (Primary Key)	The workspace identifier (typically a domain).
from_address	String	The default email address used for sending.
from_name	String	The display name for the sender.
config_set	String	AWS SES configuration set for event tracking.
rate_limit	Number	Milliseconds/Delay between sends.
footer_html	String	Custom HTML appended to all emails.
website_url	String	The URL of the workspace website.

Sources: src/lib/db.ts:464-474, src/lib/openapi.ts:66-79

Table: workspace_memberships

Links Supabase users to workspaces.

Field	Type	Description
workspace_id	String	Foreign key to workspace_settings.
user_id	UUID	Foreign key to Supabase auth user.
role	String	User role (e.g., 'owner', 'member').
created_at	Timestamp	Membership creation time.

Sources: src/lib/db.ts:192-210

Contact Management

The contact system handles recipient data, metadata, and suppression lists (unsubscribes).

Table: contacts

Stores recipient information with arbitrary metadata.

Field	Type	Description
email	String (Composite PK)	Normalized lowercase email address.
workspace_id	String (Composite PK)	Workspace owner of the contact.
fields	JSONB / Record	Arbitrary key-value metadata (e.g., name, company).

Sources: src/lib/db.ts:318-323, src/lib/openapi.ts:25-33

Table: contact_unsubscribes

A suppression list used to filter recipients before sending.

Field	Type	Description
email	String	The unsubscribed email.
workspace_id	String	Workspace context for the unsubscribe.
unsubscribed_at	Timestamp	Time of the unsubscribe event.

Sources: src/lib/db.ts:413-421

Email Jobs and Tracking

Send jobs manage the asynchronous processing of large email campaigns, while events track the outcome of individual messages.

Table: send_jobs

Represents a bulk sending operation.

Field	Type	Description
id	UUID (PK)	Unique job identifier.
status	String	Status: queued, running, completed, failed.
payload	JSONB	Contains subject, html, from, etc.
total	Integer	Total number of recipients.
sent	Integer	Number of successful deliveries.
failed	Integer	Number of failed attempts.
heartbeat_at	Timestamp	Used to detect stale jobs.

Sources: src/lib/db.ts:1003-1022, src/lib/openapi.ts:80-97

Table: email_events

Stores granular tracking data for sent emails, typically populated via AWS SNS webhooks.

Field	Type	Description
message_id	String	The AWS SES message ID.
event_type	String	Type: delivery, open, click, bounce, complaint.
timestamp	Timestamp	When the event occurred.
detail	String	Additional data (e.g., the URL clicked).

Sources: src/lib/db.ts:742-750, src/app/app/page.tsx:288-300

Billing and Credits

The system implements a credit-based billing model, where workspaces must have a balance to send emails (if billing is enforced).

Table: workspace_billing

Manages the credit balance and external payment provider (Polar) integration.

Field	Type	Description
workspace_id	String (PK)	Workspace owner of the billing profile.
credit_balance	Integer	Current available credits.
billing_status	String	Status of the billing account (e.g., active).
polar_customer_id	String	ID for the Polar payment system.
lifetime_credits_consumed	Integer	Aggregate credits used since creation.

Sources: src/lib/db.ts:544-561, src/app/app/page.tsx:238-256

Security and API Keys

API keys allow programmatic access to the database. They are hashed before storage to ensure security.

Table: api_keys

Stores metadata and hashes of API keys.

Field	Type	Description
id	UUID (PK)	Key identifier.
workspace_id	String	Workspace the key belongs to.
key_hash	String	SHA-256 hash of the plaintext key.
key_prefix	String	First 10 characters for UI display.
scopes	Array	Scopes: contacts.read, contacts.write, send.read, send.write.

Sources: src/lib/db.ts:100-110, src/lib/openapi.ts:34-49

API Key Authentication Flow

This sequence shows how a hashed API key is used to resolve workspace access.

The flow ensures that plaintext keys are never stored and only their hashes are compared.
Sources: src/lib/db.ts:162-178, src/lib/auth.ts:100-120

Summary

The Send Again database schema provides a robust foundation for email operations, prioritizing workspace isolation and detailed event tracking. By combining core configuration tables with high-volume job and event logs, the schema enables both real-time dashboard updates and large-scale programmatic email delivery.

Sources: src/lib/db.ts:1-30, README.md:1-20