DynamoDB Tables

The Subscription Service uses three DynamoDB tables. All three are legacy tables created before the current deployment structure was adopted, and are accessed via the DYNAMO_LEGACY_REGION (the dynamo_legacy_region Terraform variable). The primary subscription table (billing-activity) and its history mirror (billing-activity-history) store every subscription record written or updated by the service. The third table (locks) provides distributed locking used by all collection Lambdas to prevent concurrent billing attempts on the same user.

Region for all tables: dynamo_legacy_region Terraform variable (passed to Lambdas as DYNAMO_DB_REGION / DYNAMO_REGION / DYNAMO_LOCK_REGION env vars).

`billing-activity`

Region: DYNAMO_LEGACY_REGION (env: DYNAMO_DB_REGION / DYNAMO_REGION)

The primary subscription table. Each record represents one billing period for a user — from the moment a new subscription is created through its final terminal state (COMPLETED, WAIVED, CANCELLED, or STALE). A new record is written when the previous billing period has been attempted at least once, advancing the user to the next cycle. Records are mutated in place as the collection engine progresses through states; every write is also mirrored to billing-activity-history before the primary write, so the history table preserves a full audit trail of every state change.

Primary key: user_id (PK) + billing_date (SK)

Attribute DynamoDB Tag Go Type Description

Attribute	DynamoDB Tag	Go Type	Description
`user_id`	PK	`string`	Owning user’s ID.
`billing_date`	SK	`time.Time`	The subscription due date (RFC3339Nano string in DynamoDB). Acts as the sort key. Updated records that change the due date require a delete-then-put because the SK is immutable once written.
`subscription_id`	`subscription_id`	`string`	UUID that uniquely identifies this subscription billing record. Used as the key for the `subscription_id-index` GSI.
`billing_amount`	`billing_amount`	`string`	Dollar amount charged for this subscription period (e.g. `"4.99"` or `"39.99"`).
`billing_status`	`billing_status`	`string`	Current status of the subscription. See Billing Status Values.
`billing_period`	`billing_period`	`string`	`MM/YYYY` representation of `billing_date`. Informational; not indexed.
`transaction_id`	`transaction_id`	`string`	Payment processor confirmation ID for the most recent collection attempt. On a completed record this is the final confirmation ID. On in-progress records each new attempt overwrites the previous value.
`usio_error`	`usio_error`	`string`	Payment error message from the most recent failed attempt (from JPM or USIO). Cleared to empty when the record reaches `COMPLETED`.
`initial_run_date`	`initial_run_date`	`time.Time`	Timestamp of the first collection attempt for this billing period.
`completion_date`	`completion_date`	`time.Time`	Timestamp when the subscription reached a terminal status (`COMPLETED`, `WAIVED`, etc.).
`last_run_date`	`last_run_date`	`time.Time`	Timestamp of the most recent update or collection attempt. Must be set before writing so that `billing-activity-history` records are distinct.
`created_date`	`created_date`	`time.Time`	Timestamp when this billing record was first created.
`process`	`process`	`string`	The collection stage or process that last updated this record. See Process Values.
`updated_event`	`updated_event`	`string`	Internal event tag describing a specific lifecycle update. See Updated Event Values.
`term`	`term`	`string`	Billing term. `MONTHLY` or `YEARLY`.
`receipt_account_mask`	`receipt_account_mask`	`string`	Last four digits of the bank account used for this collection. Stored for receipt display.
`receipt_tier_name`	`receipt_tier_name`	`string`	Membership tier and version at time of collection, encoded as `"TierName:Version"` (e.g. `"Plus:v2"`). Split with `GetTier()` / `GetVersion()` helpers in the `subscriptions` package.
`receipt_payment_type`	`receipt_payment_type`	`string`	Payment method type used for this collection (e.g. `ACH`). Stored for receipt display.
`billing_week`	`billing_week`	`*int`	Optional. When set, indicates the user is on Friday billing and records which Friday of the month (1–5) their billing falls on.
`pause_duration_months`	`pause_duration_months`	`int`	Number of months the subscription is paused. Non-zero when `billing_status` is `PAUSED` or `PAUSED_SKIPPED`.
`is_pending_downgrade`	`is_pending_downgrade`	`bool`	`true` when a downgrade has been requested but not yet applied (omitted from DynamoDB when `false` via `omitzero`).

user_id

string

Owning user’s ID.

billing_date

time.Time

The subscription due date (RFC3339Nano string in DynamoDB). Acts as the sort key. Updated records that change the due date require a delete-then-put because the SK is immutable once written.

subscription_id

string

UUID that uniquely identifies this subscription billing record. Used as the key for the subscription_id-index GSI.

billing_amount

string

Dollar amount charged for this subscription period (e.g. "4.99" or "39.99").

billing_status

string

Current status of the subscription. See Billing Status Values.

billing_period

string

MM/YYYY representation of billing_date. Informational; not indexed.

transaction_id

string

Payment processor confirmation ID for the most recent collection attempt. On a completed record this is the final confirmation ID. On in-progress records each new attempt overwrites the previous value.

usio_error

string

Payment error message from the most recent failed attempt (from JPM or USIO). Cleared to empty when the record reaches COMPLETED.

initial_run_date

time.Time

Timestamp of the first collection attempt for this billing period.

completion_date

time.Time

Timestamp when the subscription reached a terminal status (COMPLETED, WAIVED, etc.).

last_run_date

time.Time

Timestamp of the most recent update or collection attempt. Must be set before writing so that billing-activity-history records are distinct.

created_date

time.Time

Timestamp when this billing record was first created.

process

string

The collection stage or process that last updated this record. See Process Values.

updated_event

string

Internal event tag describing a specific lifecycle update. See Updated Event Values.

term

string

Billing term. MONTHLY or YEARLY.

receipt_account_mask

string

Last four digits of the bank account used for this collection. Stored for receipt display.

receipt_tier_name

string

Membership tier and version at time of collection, encoded as "TierName:Version" (e.g. "Plus:v2"). Split with GetTier() / GetVersion() helpers in the subscriptions package.

receipt_payment_type

string

Payment method type used for this collection (e.g. ACH). Stored for receipt display.

billing_week

*int

Optional. When set, indicates the user is on Friday billing and records which Friday of the month (1–5) their billing falls on.

pause_duration_months

int

Number of months the subscription is paused. Non-zero when billing_status is PAUSED or PAUSED_SKIPPED.

is_pending_downgrade

bool

true when a downgrade has been requested but not yet applied (omitted from DynamoDB when false via omitzero).

Access Patterns

Add a new subscription record (also writes to history table):

Add(ctx, subscription)
  → PutItem on billing-activity-history (full record including data_ fields)
  → PutItem on billing-activity (data_ prefixed fields stripped via RemoveDataFields)

Get a subscription by its UUID (via GSI):

GetByID(ctx, subscriptionID)
  → Query(
      table=billing-activity,
      index=subscription_id-index,
      key=subscription_id = subscriptionID
    )

List all subscriptions for a user (table scan of user’s partition):

ListByUserID(ctx, userID)
  → Query(
      table=billing-activity,
      key=user_id = userID,
      ScanIndexForward=true,   // ascending by billing_date
      paginated until LastEvaluatedKey is empty
    )

Get recent subscriptions (reverse order, single page):

GetRecentSubscriptions(ctx, userID)
  → Query(
      table=billing-activity,
      key=user_id = userID,
      ScanIndexForward=false   // descending — most recent first
    )

Get a page of scheduled subscriptions due before date (via GSI, used by collections-job):

GetScheduledSubscriptionPage(ctx, date, startKey)
  → Query(
      table=billing-activity,
      index=billing_status-index,
      key=billing_status = "SCHEDULED",
      filter=billing_date < date
    )

Get a page of subscriptions in ERROR status with billing_date after date (retry collections):

GetRetrySubscriptionPage(ctx, date, startKey)
  → Query(
      table=billing-activity,
      index=billing_status-index,
      key=billing_status = "ERROR",
      filter=billing_date > date
    )

Get a page of PAUSED subscriptions with billing_date before date:

GetPausedSubscriptionPage(ctx, date, startKey)
  → Query(
      table=billing-activity,
      index=billing_status-index,
      key=billing_status = "PAUSED",
      filter=billing_date < date
    )

Get a page of subscriptions by status within a billing date range (used by notifier-scheduler):

GetNotificationSubscriptionPage(ctx, status, billingDate, billingEndDate, startKey)
  → Query(
      table=billing-activity,
      index=billing_status-index,
      key=billing_status = status,
      filter=billing_date >= billingDate AND billing_date < billingEndDate
    )

Admin update (delete-then-put, used when billing_date changes):

AdminUpdate(ctx, original, updated)
  → DeleteItem(key={user_id, billing_date} of original)
  → Add(ctx, updated)  // PutItem to both tables

Batch write multiple subscriptions (upgrade/downgrade processing):

AddAll(ctx, subscriptions)
  → BatchWriteItem on both billing-activity and billing-activity-history
    (SK collision jiggle: non-SCHEDULED subs with duplicate billing_date
     get +N hours added to billing_date to avoid DynamoDB key conflicts)

GSIs

`subscription_id-index`

Projection: all attributes.

Key Description

Key	Description
`subscription_id` (PK)	Lookup a single subscription by UUID. Returns at most one item. Used by `GetByID` and by the `kinesis-feeder` Lambda when processing DynamoDB stream events.

subscription_id (PK)

Lookup a single subscription by UUID. Returns at most one item. Used by GetByID and by the kinesis-feeder Lambda when processing DynamoDB stream events.

`billing_status-index`

Projection: all attributes.

Key Description

Key	Description
`billing_status` (PK)	Scan all subscriptions with a given status. Used by `collections-job` to paginate over `SCHEDULED`, `ERROR`, and `PAUSED` subscriptions, and by `notifier-scheduler` to find `SCHEDULED` subscriptions within an upcoming billing date range.

billing_status (PK)

Scan all subscriptions with a given status. Used by collections-job to paginate over SCHEDULED, ERROR, and PAUSED subscriptions, and by notifier-scheduler to find SCHEDULED subscriptions within an upcoming billing date range.

billing_status-index returns all items globally for a given status, so callers apply a filter expression on billing_date to narrow results. Filter expressions do not reduce consumed capacity — they are applied after read.

`billing_status` Values

Value Description

Value	Description
`SCHEDULED`	The subscription is awaiting its first collection attempt. All new subscriptions start in this state.
`ACHSENT`	An ACH debit has been submitted to the payment processor and is pending settlement confirmation from the `site-payments` Kinesis stream.
`COMPLETED`	The collection succeeded. Terminal state.
`ERROR`	The most recent collection attempt failed (e.g. insufficient balance, ACH return). Eligible for retry collection.
`WAIVED`	The subscription was manually waived (forgiven) without payment. Terminal state.
`CANCELLED`	The subscription was cancelled before a collection attempt completed. Terminal state.
`PAUSED`	The subscription’s billing is paused for `pause_duration_months` months. Pause collections check for past-due PAUSED records and advance them.
`PAUSED_SKIPPED`	The subscription was in a PAUSED state whose pause window has expired; it was skipped and a new subscription record was created. Terminal state for this record.
`REFUNDED`	The subscription payment was refunded after successful ACH collection. Set by the ach-handler on a `SUBSCRIPTION_REFUNDED` event. Terminal state.
`STALE`	The subscription is too old to collect (past the `OLD_SUBSCRIPTION_DAYS` threshold). Terminal state.
`INACTIVE`	Internal sentinel value used to represent the absence of an active subscription.

SCHEDULED

The subscription is awaiting its first collection attempt. All new subscriptions start in this state.

ACHSENT

An ACH debit has been submitted to the payment processor and is pending settlement confirmation from the site-payments Kinesis stream.

COMPLETED

The collection succeeded. Terminal state.

ERROR

The most recent collection attempt failed (e.g. insufficient balance, ACH return). Eligible for retry collection.

WAIVED

The subscription was manually waived (forgiven) without payment. Terminal state.

CANCELLED

The subscription was cancelled before a collection attempt completed. Terminal state.

PAUSED

The subscription’s billing is paused for pause_duration_months months. Pause collections check for past-due PAUSED records and advance them.

PAUSED_SKIPPED

The subscription was in a PAUSED state whose pause window has expired; it was skipped and a new subscription record was created. Terminal state for this record.

REFUNDED

The subscription payment was refunded after successful ACH collection. Set by the ach-handler on a SUBSCRIPTION_REFUNDED event. Terminal state.

STALE

The subscription is too old to collect (past the OLD_SUBSCRIPTION_DAYS threshold). Terminal state.

INACTIVE

Internal sentinel value used to represent the absence of an active subscription.

`process` Values

Value Description

Value	Description
`INITIAL`	First scheduled collection attempt, triggered by `collections-job` via the `billing_status-index` (SCHEDULED scan).
`RETRY`	Daily retry attempt on an ERROR subscription, triggered by `collections-job` via the `billing_status-index` (ERROR scan).
`PAUSE`	Pause collection run, triggered by `collections-job` to advance a past-due PAUSED subscription.
`WEBHOOK`	Collection triggered by an income-detection event (webhook-worker Lambda consuming EventBridge income events).
`WEBHOOK_BALANCE`	Collection triggered by a new-account balance event (webhook-balance-worker Lambda).
`BATCH`	Collection triggered by the `batch-worker` Lambda (manual or operational batch run via SQS).
`REACTIVATION`	Subscription record written as part of a user reactivation flow.
`MANUAL_REPAYMENT`	Manual repayment triggered via the `POST /{user_id}/subscriptions/{subscription_id}/pay` API endpoint.
`NOT_IDENTIFIED`	Process could not be determined from the available context.

INITIAL

First scheduled collection attempt, triggered by collections-job via the billing_status-index (SCHEDULED scan).

RETRY

Daily retry attempt on an ERROR subscription, triggered by collections-job via the billing_status-index (ERROR scan).

PAUSE

Pause collection run, triggered by collections-job to advance a past-due PAUSED subscription.

WEBHOOK

Collection triggered by an income-detection event (webhook-worker Lambda consuming EventBridge income events).

WEBHOOK_BALANCE

Collection triggered by a new-account balance event (webhook-balance-worker Lambda).

BATCH

Collection triggered by the batch-worker Lambda (manual or operational batch run via SQS).

REACTIVATION

Subscription record written as part of a user reactivation flow.

MANUAL_REPAYMENT

Manual repayment triggered via the POST /{user_id}/subscriptions/{subscription_id}/pay API endpoint.

NOT_IDENTIFIED

Process could not be determined from the available context.

`updated_event` Values

Value Description

Value	Description
`pause-skipped`	The subscription was in a PAUSED state that was skipped because the pause window expired.
`pause-pending-resume`	The subscription is paused and approaching its resume date; a notification or state transition is pending.
`pause-resume`	The subscription has been resumed from a paused state.
`user-reactivated`	The subscription record was updated as part of a user reactivation event from the memberships Kinesis consumer.

pause-skipped

The subscription was in a PAUSED state that was skipped because the pause window expired.

pause-pending-resume

The subscription is paused and approaching its resume date; a notification or state transition is pending.

pause-resume

The subscription has been resumed from a paused state.

user-reactivated

The subscription record was updated as part of a user reactivation event from the memberships Kinesis consumer.

`term` Values

Value Description

Value	Description
`MONTHLY`	Subscription renews monthly. Default term. `GetNextSubscriptionDate` advances `billing_date` by one month.
`YEARLY`	Subscription renews annually. `GetNextSubscriptionDate` advances `billing_date` by 12 months.

MONTHLY

Subscription renews monthly. Default term. GetNextSubscriptionDate advances billing_date by one month.

YEARLY

Subscription renews annually. GetNextSubscriptionDate advances billing_date by 12 months.

`billing-activity-history`

Region: DYNAMO_LEGACY_REGION (env: DYNAMO_DB_REGION / DYNAMO_REGION)

An append-only audit log of every subscription state change. Uses the same Subscription struct and the same attribute names as billing-activity, but with a different primary key structure: user_id (PK) + last_run_date (SK). This means each update to a subscription — which advances last_run_date before writing — creates a new history row rather than overwriting the previous state. When bulk-writing via AddAll, both tables receive the same BatchWriteItem request.

Primary key: user_id (PK) + last_run_date (SK)

All attributes are identical to billing-activity. The data_ prefixed fields (stripped from billing-activity before write via RemoveDataFields) are retained in billing-activity-history when written by the Add method.

Access Patterns

Get all history entries for a subscription UUID (via GSI):

GetByID(ctx, subscriptionID)
  → Query(
      table=billing-activity-history,
      index=subscription_id-index,
      key=subscription_id = subscriptionID,
      paginated until LastEvaluatedKey is empty
    )

Append a single history record:

Add(ctx, subscription)
  → PutItem on billing-activity-history

Batch-append multiple history records:

AddAll(ctx, subscriptions)
  → BatchWriteItem on billing-activity-history

GSIs

`subscription_id-index`

Identical definition to the subscription_id-index GSI on billing-activity. Projection: all attributes.

Key Description

Key	Description
`subscription_id` (PK)	Retrieve all history entries for a given subscription UUID, ordered by `last_run_date`. Used by `GET /{user_id}/subscriptions/{subscription_id}/history` via the `history.GetByID` repository method.

subscription_id (PK)

Retrieve all history entries for a given subscription UUID, ordered by last_run_date. Used by GET /{user_id}/subscriptions/{subscription_id}/history via the history.GetByID repository method.

`locks`

Region: DYNAMO_LEGACY_REGION (env: DYNAMO_LOCK_REGION)

Distributed locking table used by all collection Lambdas (collections-worker, batch-worker, webhook-worker, webhook-balance-worker) to prevent concurrent billing attempts on the same user. Backed by the cirello.io/dynamolock/v2 client. Attributes in this table are managed entirely by the dynamolock library; the service does not read them directly.

Primary key: PartitionKey (PK — string, no sort key)

Attribute DynamoDB Tag Description

Attribute	DynamoDB Tag	Description
`PartitionKey`	PK	Lock key in the format `subscription-billing:user_id:{userID}`. Constructed by `buildLockKey(userID)` in `pkg/dynamo/lock.go`.
`data`	—	Unix nanosecond timestamp (as bytes) of when the lock was acquired. Written by `buildLockData()`.
`deleteOn`	—	TTL attribute for automatic record expiry. Managed by the dynamolock library.
`leaseDuration`	—	Duration of the lock lease. Set to 60 seconds via `dynamolock.WithLeaseDuration`.
`owner`	—	Identifier of the lock owner (Lambda invocation). Managed by the dynamolock library.
`recordVersionNumber`	—	Version counter used for optimistic locking. Prevents split-brain when two invocations attempt to acquire the same lock simultaneously.

PartitionKey

Lock key in the format subscription-billing:user_id:{userID}. Constructed by buildLockKey(userID) in pkg/dynamo/lock.go.

data

—

Unix nanosecond timestamp (as bytes) of when the lock was acquired. Written by buildLockData().

deleteOn

—

TTL attribute for automatic record expiry. Managed by the dynamolock library.

leaseDuration

—

Duration of the lock lease. Set to 60 seconds via dynamolock.WithLeaseDuration.

owner

—

Identifier of the lock owner (Lambda invocation). Managed by the dynamolock library.

recordVersionNumber

—

Version counter used for optimistic locking. Prevents split-brain when two invocations attempt to acquire the same lock simultaneously.

Lock Behaviour

A lock is acquired with:

Lease duration: 60 seconds
Heartbeat period: 1 second (the Lambda refreshes the lease every second while holding it)
Fail-if-locked: yes — if the lock is already held by another invocation, LockSubscriptionForBilling returns ErrAlreadyLocked immediately rather than waiting

LockSubscriptionForBilling(ctx, userID)
  → AcquireLockWithContext(
      key  = "subscription-billing:user_id:" + userID,
      data = UnixNano timestamp bytes,
      FailIfLocked = true,
      ReplaceData  = true
    )
  → returns *dynamolock.Lock or ErrAlreadyLocked

ReleaseSubscription(ctx, lock)
  → ReleaseLockWithContext(lock)

When a lock cannot be acquired, the collection attempt is skipped without modifying the subscription record. This ensures only one Lambda processes a given user’s subscription at any time, even when scheduled and webhook collection paths fire simultaneously.

Architecture — DynamoDB tables in the system context
Collections Engine — How billing_status-index queries and locks are used during scheduled, retry, pause, and webhook collection flows
Subscription Lifecycle — Full status transition diagram and descriptions
ACH Processing — How ACH Kinesis events drive billing_status transitions from ACHSENT to COMPLETED / ERROR
Infrastructure — Terraform definitions for Lambda IAM permissions and table region configuration

DynamoDB Tables

billing-activity

Access Patterns

GSIs

subscription_id-index

billing_status-index

billing_status Values

process Values

updated_event Values

term Values

billing-activity-history

Access Patterns

GSIs

subscription_id-index

locks

Lock Behaviour

Related Pages

`billing-activity`

`subscription_id-index`

`billing_status-index`

`billing_status` Values

`process` Values

`updated_event` Values

`term` Values

`billing-activity-history`

`subscription_id-index`

`locks`