DynamoDB Schema

Two tables: a main table (single-table design) for all subscriber state, and an events table for engagement tracking.

Main table

All items are keyed by subscriber email, sequence ID, tag name, broadcast partition, or stats partition. No GSIs - reverse lookups (e.g. "all subscribers in sequence X") are served by inverted partner items rather than indexes.

Key structure

Key helper functions

The @mailshot/shared package exports these helpers:

subscriberPK("user@example.com"); // → "SUB#user@example.com"
executionSK("onboarding"); // → "EXEC#onboarding"
sentSK("2026-03-17T10:30:00.000Z"); // → "SENT#2026-03-17T10:30:00.000Z"
tagPK("product-updates"); // → "TAG#product-updates"
broadcastSK("2026-04-02T10:00:00.000Z", "product-update-april"); // → "2026-04-02T10:00:00.000Z#product-update-april"

Item schemas

PROFILE

{
  PK: "SUB#user@example.com",
  SK: "PROFILE",
  email: "user@example.com",
  firstName: "Jane",
  platform: "kajabi",              // custom attributes are top-level columns
  country: "ZA",
  tags: ["product-updates", "beta-users"],  // string array for tag membership
  unsubscribed: false,
  suppressed: false,
  createdAt: "2026-03-01T00:00:00.000Z",
  updatedAt: "2026-03-17T10:00:00.000Z",
}

EXEC#<sequenceId> (subscriber-side)

{
  PK: "SUB#user@example.com",
  SK: "EXEC#onboarding",
  sequenceId: "onboarding",
  executionArn: "arn:aws:states:...",
  startedAt: "2026-03-01T00:00:00.000Z",
}

EXEC#<sequenceId> (sequence-side, inverted partner)

Written transactionally alongside the subscriber-side row by putExecution and deleted together by deleteExecution. Lets you Query "who is currently in sequence X?" without a GSI or scan — same pattern as the tag inverted index below.

{
  PK: "EXEC#onboarding",
  SK: "SUB#user@example.com",
  email: "user@example.com",
  executionArn: "arn:aws:states:...",
  startedAt: "2026-03-01T00:00:00.000Z",
}

The two rows are kept consistent by wrapping every put and delete in a TransactWriteItemsCommand — ACID guarantees at the DynamoDB layer mean the inspector view can never drift out of sync with the subscriber's view. The MCP list_sequence_subscribers tool reads these rows directly via Query(PK = "EXEC#<sequenceId>"), and list_sequences derives activeExecutionCount per sequence via a Select: COUNT query against the same partition.

SENT#<timestamp>

{
  PK: "SUB#user@example.com",
  SK: "SENT#2026-03-17T10:30:00.000Z",
  templateKey: "onboarding/welcome",
  subject: "Welcome!",
  sequenceId: "onboarding",
  sentAt: "2026-03-17T10:30:00.000Z",
  ttl: 1752710400, // optional, set via DATA_TTL_DAYS
}

SUPPRESSION

{
  PK: "SUB#user@example.com",
  SK: "SUPPRESSION",
  type: "Permanent",     // or "Complaint"
  reason: "General",
  suppressedAt: "2026-03-17T10:30:00.000Z",
}

Tag items (inverted index)

Tag items enable efficient queries like "give me all subscribers tagged product-updates". Each tag assignment creates one item:

{
  PK: "TAG#product-updates",
  SK: "SUB#user@example.com",
  email: "user@example.com",
  taggedAt: "2026-03-17T10:00:00.000Z",
}

The inverted index is maintained automatically by syncTags() when a subscriber's tags are set via subscribe events. The PROFILE item's tags array and the inverted index items are always kept in sync.

Broadcast records

Every broadcast writes a persistent log record:

{
  PK: "BROADCAST",
  SK: "2026-04-02T10:00:00.000Z#product-update-2026-04",
  broadcastId: "product-update-2026-04",
  templateKey: "broadcasts/product-update-april",
  subject: "What's new in April",
  sender: { fromEmail: "updates@example.com", fromName: "My SaaS" },
  filters: { tags: ["product-updates"], attributes: { plan: "pro" } },
  audienceSize: 342,

  sentAt: "2026-04-02T10:00:00.000Z",
}

All broadcasts share a single partition key (BROADCAST), with the SK sorted by timestamp. This means list_broadcasts is a simple Query with ScanIndexForward: false — most recent first, no scan. Written by BroadcastFn after resolving subscribers. Query via get_broadcast or list_broadcasts MCP tools.

audienceSize is the count of subscribers resolved by the audience query at send time. It is not a delivery count — pre-send skips, SES failures, and bounces are tracked separately via the counters item below.

Stats counters

A separate counters item is updated by EngagementHandlerFn as SES delivery / open / click / bounce / complaint notifications arrive. The same item type is used for both broadcasts and sequences (broadcasts reuse sequenceId = broadcastId):

{
  PK: "STATS#product-update-2026-04",  // STATS#<sequenceId>
  SK: "COUNTERS",
  sequenceId: "product-update-2026-04",
  deliveryCount: 338,
  openCount: 142,
  clickCount: 27,
  bounceCount: 4,
  complaintCount: 0,
  updatedAt: "2026-04-02T11:14:03.221Z",
}

Counters use atomic UpdateItem ADD so concurrent updates are safe. They are eventually consistent — the EmailEvents table is the source of truth, and counter writes are best-effort (a failed counter update is logged but does not block the event write). get_broadcast and list_broadcasts automatically merge these counters into the records they return.

Access patterns

TTL

All items are permanent by default. Set DATA_TTL_DAYS in .env to enable automatic expiry on send logs (the ttl attribute is set to now + DATA_TTL_DAYS at write time). Profiles, executions, and suppression records are never TTL'd.

Events table

Stores engagement events (delivery, open, click, bounce, complaint, reply).

Key structure

Item schema

{
  PK: "SUB#user@example.com",
  SK: "EVT#2026-03-17T10:30:00.000Z#delivery",
  eventType: "delivery",
  templateKey: "onboarding/welcome",
  sequenceId: "onboarding",
  subject: "Welcome!",
  timestamp: "2026-03-17T10:30:00.000Z",
  ttl: 1784246400, // optional, set via DATA_TTL_DAYS
}

Reply events include additional fields:

{
  PK: "SUB#user@example.com",
  SK: "EVT#2026-03-20T14:00:00.000Z#reply",
  eventType: "reply",
  subject: "Re: Welcome!",
  body: "Thanks for reaching out!",
  templateKey: "onboarding/welcome",  // best-effort, from In-Reply-To correlation
  sequenceId: "onboarding",           // best-effort, from In-Reply-To correlation
  sesMessageId: "original-ses-id",    // original outbound message ID
  timestamp: "2026-03-20T14:00:00.000Z",
  ttl: 1784246400,
}

TemplateIndex GSI

Enables cross-subscriber queries by template:

Query: "Show me all opens for the welcome email in the last 7 days"

PK = "onboarding/welcome", SK begins_with "EVT#2026-03-10"

SequenceIndex GSI

Enables cross-subscriber queries by sequence or broadcast ID:

Query: "Show me all engagement events for the April broadcast"

PK = "product-update-2026-04", SK begins_with "EVT#"

This powers efficient broadcast stats (opens, clicks, bounces per broadcast) without scanning the entire events table. Also improves sequence event queries — previously these required a full table scan.

TTL

Events are permanent by default. Set DATA_TTL_DAYS in .env to enable automatic expiry (applies to both tables).

Why single-table design?

All subscriber data in one table means:

• A single Query on the PK retrieves everything about a subscriber (profile, executions, send history, suppression)
• No joins, no cross-table transactions
• Consistent performance regardless of table size
• Lower operational overhead (one table to manage, back up, monitor)

The events table is separate because:

• Different access patterns (cross-subscriber queries via GSI)
• Higher write volume (every open/click generates a record)