Newsletter Service — Database Reference · Newsletter

Last Updated: 2026-06-28 Status: Active Source of truth: apps/newsletter-service/src/db/schema.ts ORM: Drizzle ORM · DB: Neon PostgreSQL (NL_DATABASE_URL)

1. Tables at a Glance

Table	Rows represent	Multi-tenant
`nl_subscribers`	Email addresses opted in to a tenant's newsletter	`tenant_id NOT NULL`
`nl_campaigns`	Newsletter campaigns (draft → scheduled → sending → sent)	`tenant_id NOT NULL`
`nl_campaign_sends`	Per-subscriber send records (one per campaign × subscriber)	`tenant_id NOT NULL`
`nl_templates`	Reusable email templates with variable contracts	`tenant_id NOT NULL`
`nl_template_sections`	Reusable block fragments (Phase D visual editor)	`tenant_id NOT NULL`
`nl_segments`	Subscriber filter rules (tags, status, date range)	`tenant_id NOT NULL`
`nl_suppression_list`	Global cross-tenant bounce/complaint registry	(no tenant scope)
`nl_subscriber_events`	Append-only audit log of all subscriber lifecycle events	`tenant_id NOT NULL`
`nl_blog_binding`	Per-tenant blog-to-newsletter dispatch config	`tenant_id PK`

2. `nl_subscribers`

Column	Type	Constraints	Notes
`id`	`text`	PK	Format: `sub_{uuid}`
`tenant_id`	`text`	NOT NULL	Multi-tenant isolation key
`email`	`text`	NOT NULL, UNIQUE per tenant	Lowercased at insert
`name`	`text`	nullable	Display name; `{{ subscriber.name }}` falls back to "there" if null
`status`	`text`	NOT NULL, default `'active'`	`active` \| `unsubscribed` \| `bounced` \| `complained`
`source`	`text`	NOT NULL, default `'manual'`	`manual` \| `import` \| `form` \| `api`
`tags`	`text[]`	nullable	Array; used for segment filtering
`metadata`	`jsonb`	nullable	Custom fields; accessible via `{{ subscriber.metadata.* }}`
`confirmed_at`	`timestamp`	nullable	Null = pending double opt-in. Set on `/public/confirm`.
`unsubscribed_at`	`timestamp`	nullable	Set when `status` → `unsubscribed`
`bounced_at`	`timestamp`	nullable	Set on provider hard bounce
`created_at`	`timestamp`	NOT NULL, `defaultNow()`
`updated_at`	`timestamp`	NOT NULL, `defaultNow()`

Indexes

Index	Columns	Type
`nl_sub_tenant_idx`	`tenant_id`	B-tree
`nl_sub_tenant_email_idx`	`(tenant_id, email)`	UNIQUE
`nl_sub_tenant_status_idx`	`(tenant_id, status)`	B-tree

Business Rules

Campaign recipient query: WHERE status = 'active' AND confirmed_at IS NOT NULL AND email NOT IN (suppression_list)
Manual adds and imports skip double opt-in: confirmed_at = NOW() set immediately
Form signups start with confirmed_at = NULL until email link clicked
Re-subscribing a previously unsubscribed email: resets status = 'active', clears unsubscribed_at, sets confirmed_at = NOW()
Bounced/complained emails are cross-tenant suppressed via nl_suppression_list

3. `nl_campaigns`

Column	Type	Constraints	Notes
`id`	`text`	PK	Format: `camp_{uuid}`
`tenant_id`	`text`	NOT NULL
`name`	`text`	NOT NULL	Internal label
`subject`	`text`	NOT NULL	Email subject line (rendered with `{{ tokens }}` at send time)
`html_body`	`text`	NOT NULL	Rendered HTML
`text_body`	`text`	nullable	Plaintext fallback; auto-derived if not supplied
`preview_text`	`text`	nullable	Email preview snippet
`blocks`	`jsonb`	nullable	Visual editor source (`BlockNode[]`); null = legacy HTML-only
`document_opts`	`jsonb`	nullable	Email width/font/color options snapshot
`template_id`	`text`	nullable	Soft attribution link (not enforced FK; not read at send time)
`segment_id`	`text`	nullable	Segment to target; null = all confirmed subscribers
`source_kind`	`text`	nullable	null \| `'blog_post'`
`source_post_id`	`text`	nullable	Blog post ID (unique with tenant when set — prevents duplicate campaigns per post)
`status`	`text`	NOT NULL, default `'draft'`	`draft` \| `scheduled` \| `sending` \| `sent` \| `cancelled`
`scheduled_at`	`timestamp`	nullable	Alarm time for DO scheduler
`sent_at`	`timestamp`	nullable	Set when drain check completes
`total_recipients`	`int`	default 0	Set before enqueue
`total_sent`	`int`	default 0	Successfully sent to comms-service
`total_delivered`	`int`	default 0	Provider confirmed delivery (from `/internal/event`)
`total_opened`	`int`	default 0	First-touch open (from `/internal/event`)
`total_clicked`	`int`	default 0	First-touch click (from `/internal/event`)
`total_bounced`	`int`	default 0	Provider hard bounces
`total_failed`	`int`	default 0	Queue/comms delivery errors
`total_unsubscribed`	`int`	default 0	Complaints via `/internal/event`
`open_rate`	`real`	default 0	Computed: total_opened / total_recipients
`click_rate`	`real`	default 0	Computed: total_clicked / total_recipients
`created_at`	`timestamp`	NOT NULL, `defaultNow()`
`updated_at`	`timestamp`	NOT NULL, `defaultNow()`

Indexes

Index	Columns	Type
`nl_camp_tenant_idx`	`tenant_id`	B-tree
`nl_camp_tenant_status_idx`	`(tenant_id, status)`	B-tree
`nl_camp_scheduled_idx`	`scheduled_at`	B-tree
`nl_campaigns_source_post_unique`	`(tenant_id, source_post_id) WHERE source_post_id IS NOT NULL`	UNIQUE partial

Business Rules

template_id is attribution only — campaign body is snapshotted at create time
Status transitions: draft ↔ scheduled, draft|scheduled → sending, sending → sent|cancelled
Once status = scheduled, body is locked (subject, html_body, blocks cannot be edited)
Drain check: when (total_sent + total_failed) >= total_recipients → status flips to sent
source_post_id partial unique index prevents duplicate blog campaigns on re-publish

4. `nl_campaign_sends`

One row per (campaign × subscriber). The send queue jobs reference this table's id as their idempotency key.

Column	Type	Notes
`id`	`text` PK	Format: `send_{uuid}`. Also the queue job idempotency key sent to comms.
`campaign_id`	`text` NOT NULL	References `nl_campaigns.id` (not FK-enforced)
`subscriber_id`	`text` NOT NULL	References `nl_subscribers.id` (not FK-enforced)
`tenant_id`	`text` NOT NULL	Denormalised for query performance
`status`	`text` NOT NULL default `'queued'`	`queued` \| `sent` \| `delivered` \| `bounced` \| `failed` \| `dead_letter`
`provider_message_id`	`text`	Resend message ID (from comms response)
`opened_at`	`timestamp`	First-touch open
`clicked_at`	`timestamp`	First-touch click
`bounced_at`	`timestamp`	Hard bounce time
`unsubscribed_at`	`timestamp`	Complaint time
`error`	`text`	Classification code only: `provider_4xx` \| `provider_5xx` \| `provider_unreachable` \| `provider_unknown`
`sent_at`	`timestamp`	Time comms-service accepted the send
`created_at`	`timestamp` NOT NULL, `defaultNow()`

Indexes

Index	Columns
`nl_sends_campaign_idx`	`campaign_id`
`nl_sends_subscriber_idx`	`subscriber_id`
`nl_sends_tenant_idx`	`tenant_id`
`nl_sends_campaign_status_idx`	`(campaign_id, status)`

5. `nl_templates`

Column	Type	Notes
`id`	`text` PK
`tenant_id`	`text` NOT NULL
`name`	`text` NOT NULL
`subject`	`text` NOT NULL
`html_body`	`text` NOT NULL
`text_body`	`text`	Auto-derived from `html_body` if not supplied
`variables`	`text[]`	Extracted `{{ token }}` names — stored for dashboard warning UI
`preview_text`	`text`
`render_version`	`int` NOT NULL default 1	Pins to versioned renderer contract
`blocks`	`jsonb`	Visual editor source (`BlockNode[]`); null = legacy HTML-only template
`thumbnail_url`	`text`	Reserved for Phase E server-rendered preview; currently null
`category`	`text`	Gallery filter tag; no enum yet (being shaped)
`document_opts`	`jsonb`	Email render options snapshot
`created_at`	`timestamp`
`updated_at`	`timestamp`

6. `nl_template_sections` (Phase D)

Reusable block fragments that authors can insert into templates.

Column	Type	Notes
`id`	`text` PK
`tenant_id`	`text` NOT NULL
`name`	`text` NOT NULL
`blocks`	`jsonb` NOT NULL	`BlockNode[]` array (one or more blocks)
`thumbnail_url`	`text`	Reserved for Phase E; currently null
`created_at`	`timestamp`
`updated_at`	`timestamp`

7. `nl_segments`

Column	Type	Notes
`id`	`text` PK
`tenant_id`	`text` NOT NULL
`name`	`text` NOT NULL
`description`	`text`
`filter_rules`	`jsonb` NOT NULL default `'{}'`	`{ tags: string[], status: "active", subscribedAfter: "2026-01-01" }`
`subscriber_count`	`int` default 0	Cached; may drift between saves
`created_at`	`timestamp`
`updated_at`	`timestamp`

Filter Rules Shape

{
  "tags": ["vip", "early-access"],        // subscriber must have ALL listed tags
  "status": "active",                      // active | unsubscribed | bounced | complained
  "subscribedAfter": "2026-01-01"          // ISO date string; filters by created_at
}

Any field omitted = no filter on that dimension. Empty tags: [] = no tag filter.

WARNING

subscriber_count is cached at save time. It can drift when subscribers bounce or unsubscribe between segment edits. Use GET /segments/:id/preview or GET /segments/sendable-count for live counts before sending.

8. `nl_suppression_list` (Global, Cross-Tenant)

Column	Type	Notes
`email`	`text` PK	Lowercase. One row per suppressed address — global, not per-tenant.
`reason`	`text` NOT NULL	`hard_bounce` \| `complaint` \| `manual`
`added_by_tenant`	`text`	Which tenant's send first triggered suppression
`source_campaign_id`	`text`	Campaign that caused the bounce/complaint
`notes`	`text`	Manual notes for ops review
`created_at`	`timestamp`

Business Rules

Every campaign recipient query excludes emails in this table
Suppression cannot be reversed via subscriber management — only ops can manually remove rows
Cross-tenant: if tenant_A@example.com bounces on tenant A, it is suppressed for ALL tenants

9. `nl_subscriber_events` (Append-Only Audit Log)

Column	Type	Notes
`id`	`text` PK
`subscriber_id`	`text`	Nullable — set to NULL on GDPR delete (row stays for audit)
`email`	`text` NOT NULL	Denormalised — survives subscriber row deletion
`tenant_id`	`text` NOT NULL
`kind`	`text` NOT NULL	`confirmed` \| `unsubscribed` \| `bounced` \| `complained` \| `resubscribed` \| `suppressed` \| `imported` \| `manual_add` \| `deleted`
`source`	`text` NOT NULL	`form` \| `dashboard` \| `import` \| `webhook:bounce` \| `webhook:complaint` \| `api` \| `internal` \| `system`
`detail`	`jsonb`	`{ campaignId, bounceReason, ... }` — event-specific context
`occurred_at`	`timestamp` NOT NULL default `now()`

Indexes

Index	Columns
`nl_sub_evt_tenant_occurred_idx`	`(tenant_id, occurred_at)`
`nl_sub_evt_subscriber_idx`	`subscriber_id`
`nl_sub_evt_email_idx`	`email`
`nl_sub_evt_kind_idx`	`kind`

NOTE

This table is append-only. Rows are never updated or deleted in normal operation. GDPR erase sets subscriber_id = NULL but leaves the row. No retention policy is currently enforced — see backlog.md G5.

10. `nl_blog_binding` (One Row Per Tenant)

Column	Type	Notes
`tenant_id`	`text` PK	One row per tenant
`auto_send_toggle`	`boolean` default `false`	Must be `true` for blog publish → newsletter to trigger
`default_segment_id`	`text`	nullable — null = all confirmed+active subscribers
`default_template_id`	`text` NOT NULL	Required; no implicit default
`default_from_name`	`text`	nullable — overrides sender settings
`subject_template`	`text` default `'{{title}}'`	`{{ title }}` and `{{ excerpt }}` substituted at dispatch time
`created_at`	`timestamp`
`updated_at`	`timestamp`

11. Migration History

#	File	What it added
0	`0000_quiet_starbolt.sql`	Initial: `nl_subscribers`, `nl_templates`, `nl_campaigns`, `nl_campaign_sends`, `nl_segments`
1	`0001_add_total_failed.sql`	`nl_campaigns.total_failed` column
2	`0002_template_render_version.sql`	`nl_templates.render_version` column
3	`0003_template_blocks.sql`	`nl_templates.blocks` + `nl_campaigns.blocks` columns
4	`0004_template_sections.sql`	New `nl_template_sections` table
5	`0005_suppression_list.sql`	New `nl_suppression_list` table
6	`0006_subscriber_events.sql`	New `nl_subscriber_events` table
7	`0007_document_opts.sql`	`document_opts` on templates + campaigns
8	`0008_blog_binding.sql`	New `nl_blog_binding` table; `source_kind` + `source_post_id` on campaigns; partial unique index

12. Environment Variables

Variable	Purpose
`NL_DATABASE_URL`	Neon connection string for newsletter tables
`CORE_DATABASE_URL`	Neon connection string for core DB (`tenant_credits`, `credit_transactions`)
`GATEWAY_SECRET`	Comma-separated valid gateway keys (validates `x-gateway-key`)
`INTERNAL_KEY`	Shared secret for `/internal/*` worker-to-worker calls
`UNSUBSCRIBE_SECRET`	HMAC-SHA256 key for confirm/unsubscribe URL tokens (must match website worker)