Database Schema

TailSpend uses Amazon DynamoDB with three tables, all on-demand billing. The data model is shaped around the access patterns of the HTTP API (see api.md) and the system architecture in architecture.md.

The three tables are: Users, Identities, and GroupData — a single-table layout that holds every per-group entity (group head, invite settings, join requests, participants, expenses, comments, and recurring-expense templates), all sharing the GroupId partition.

Table names follow the pattern: tailspend-{stage}-{table} (e.g. tailspend-prod-groups).

Why DynamoDB

• Per-route Lambda + DynamoDB scales to zero and absorbs spikes without capacity planning.
• All hot paths are point lookups or partition-bounded queries, which DynamoDB serves at single-digit millisecond latency.
• Item splits stay nested on the expense row (small, always read with the expense, never queried independently), but comments are first-class rows so concurrent writes, long threads, and comment-by-id access all behave well.
• Group participation lives as PARTICIPANT#<participantId> rows in the same GroupData partition. A guest is a row with Type = GUEST and no UserId; on claim, the same row flips to Type = USER with a UserId attached. Because expenses reference ParticipantId, claiming a guest never rewrites historical expenses — it's a single-row mutation.
• Everything per-group sharing the GroupId partition means "approve a join request" is a single TransactWriteItems touching the JOINREQ row, the participant row, and the lock row in one atomic operation; "delete a group" is a single bounded sweep over the partition.

Tables at a glance

The diagram is the logical model. Physically, four DynamoDB tables back the domain (plus the rate-limit counter table):

• Users and Identities are keyed by UserId / IdentityKey and stay separate. The Users partition also holds daily API usage aggregates (see API usage rows).
• DeveloperTokens holds personal access tokens and developer idempotency records (see DeveloperTokens Table).
• People holds discovery and social state, and Notifications holds push devices, preferences, history and the outbox (see Notifications Table).
• The GroupData table holds every per-group entity (group head, invite, join requests, participants, expenses, comments, recurring templates) in a single GroupId partition, distinguished by sort-key prefix and EntityType.

erDiagram
  USERS ||--o{ IDENTITIES : "has"
  USERS ||--o{ PARTICIPANTS : "claimed as"
  USERS ||--o{ JOINREQUESTS : "applicant of"
  GROUPS ||--|| INVITES : "has one"
  GROUPS ||--o{ PARTICIPANTS : "has participants"
  GROUPS ||--o{ JOINREQUESTS : "receives"
  GROUPS ||--o{ EXPENSES : "contains"
  PARTICIPANTS ||--o{ JOINREQUESTS : "takeover target"
  EXPENSES ||--o{ COMMENTS : "has"
  USERS {
    string UserId PK
  }
  IDENTITIES {
    string IdentityKey PK
    string UserId
  }
  GROUPS {
    string GroupId PK
    string SK PK
  }
  INVITES {
    string GroupId PK
    string SK PK
    bool Enabled
  }
  JOINREQUESTS {
    string GroupId PK
    string SK PK
    string RequestId
    string ApplicantUserId
    string Status
  }
  PARTICIPANTS {
    string GroupId PK
    string ParticipantId PK
    string UserId
  }
  EXPENSES {
    string GroupId PK
    string SK PK
    string ExpenseId
  }
  COMMENTS {
    string GroupId PK
    string SK PK
    string CommentId
  }

Each entity block lists only its keys and the foreign-key references that explain the relationships above it. Full attribute lists, types, and access patterns are documented in the per-section listings below.

Every per-group entity lives in the same physical DynamoDB table (GroupData) and is distinguished by the sort-key prefix and the EntityType attribute. They share a partition (GroupId) so a single Query can return a group and all of its rows, and a single TransactWriteItems can atomically touch any combination of them.

Conventions

These conventions apply uniformly to every key, attribute, and stored value in the schema. Violating them will silently break access patterns or GSI sort order.

• Entity-type prefixes in keys are UPPERCASE. EXPENSE#…, COMMENT#…, JOINREQ#…, META, INVITE. This matches the existing Expenses SK encoding and the community single-table-design convention. AWS examples sometimes use lowercase short prefixes (p#, c#); both are valid, and we pick UPPERCASE for readability and grep-ability.
• Roles and stored enum values are UPPERCASE. OWNER, MEMBER, VIEWER, GROUP, INVITE, JOINREQ, EXPENSE, COMMENT, USER, GUEST, SYSTEM, PENDING, APPROVED, DENIED, CANCELLED, TAKEOVER, NEW. These are stored tokens, not display strings.
• Identity-provider names are lowercase. Embedded in IdentityKey segments only (e.g. google#…, apple#…). Provider ids come from the IdP and are normalized to lowercase on write.
• UUIDs are lowercase v4 with hyphens, as emitted by standard libraries.
• Timestamps are ISO 8601 UTC with a literal Z suffix. Required wherever a timestamp is embedded in a sort key or sort-key segment so lex order matches chronological order.
• Reserved words. Items, Version, Comments, Currency, Name are DynamoDB reserved words. All access must use ExpressionAttributeNames (#items, #version, etc.).

Users Table

Profile data for authenticated users. Rows are written by the Cognito provisioning Lambda (wired to both the post-confirmation and post-authentication triggers) the first time a user signs in (alongside the identity rows in the Identities Table).

UserId is internal and stable across identity providers. It is referenced by Participants.UserId (set on USER participants) and by Owner and CreatedBy references in domain entities.

Test accounts

The api-integration-tests suite provisions throwaway Cognito users per run and deletes them in teardown, but teardown is best-effort — a cancelled CI job or a crashed worker leaves the account behind, and those leftovers used to sit in the admin user list alongside real people (issue #451).

So test accounts are marked instead of chased. isTestAccountEmail (in @repo/api-schema) matches the @integ.tailspend.kinesis.studio domain — one TailSpend owns and points at nothing, so no real user can hold an address under it — and:

• the Cognito provisioning trigger stamps IsTestAccount: true on the profile row, from the pool's verified email, so the flag is server-owned and no client can claim it;
• the services layer re-derives the mark from Email when the flag is absent, so rows written before the flag existed are recognised with no backfill;
• the integration-test harness builds its usernames from the same constant, so the marking rule and the harness can't drift apart.

GET /admin/users then excludes them unless includeTestAccounts=true. The attribute is omitted entirely for real users, so its presence alone means "test".

Access Patterns

API usage rows

Daily API usage aggregates (issue #632) share the user's partition. See core-api.md for semantics.

Read with UserId = <uid> AND SK BETWEEN <prefix><from> AND <prefix><to>. The rows never set RecoveryState/RecoveryDueAt, so they stay out of ScanRecoveryIndex; profile, balance and ledger reads are keyed or begins_with on their own prefixes and never see them.

DeveloperTokens Table

Personal access tokens and developer expense-create idempotency records (issue #632). Keys: TokenId (PK), SK.

Token expiry and revocation are enforced in code; token rows carry no TTL attribute, so revoked and expired tokens stay listed for their owner's usage history until account deletion removes them.

GSI OwnerTokensIndex (sparse): UserId / CreatedAt, only on TOKEN rows; projects every attribute except SecretHash. Lists one owner's tokens.

People Table

Discovery and social state (issue #643, see people.md). Keys: PK, SK.

GSI DirectoryIndex (sparse): DirKey / DirSort, INCLUDE UserId, DisplayName, Handle. Name/handle prefix search: DirKey = q[0:2] AND begins_with(DirSort, q).

GSI GroupInvitesIndex (sparse): GroupId / InviteeUserId, ALL. An owner's list of a group's pending invitations.

The handle and the search opt-in are mirrored on the PROFILE row as Handle and Discoverable, updated in the same transaction as the handle claim.

Notifications Table

Push devices, preferences, history and the outbox (issue #625, see notifications.md). Keys: PK, SK. Stream: NEW_IMAGE, filtered to new EVENT rows for the notification worker. TTL on ExpiresAt (unix seconds); reads check it too.

GSI PendingEventsIndex (sparse): PendingKey / CreatedAt, ALL. Outbox events not yet dispatched, for the maintenance job's recovery sweep.

EVENT rows are written in the same transaction as the expense, settlement, join-request, mention or People change they describe. Event ids are <type>#<groupId-or-actorUserId>#<resourceId>[#<revision>]. Revisions distinguish expense versions, comment edits, friendship/invitation timestamps and recurring slots. The hourly job conditionally creates each recurring reminder once, using a due-index range query for upcoming slots. Per-group mute updates atomically ADD/DELETE a single group id from MutedGroupIds; no table/index migration is required.

Identities Table

Maps each external identity (provider, subject) to an internal UserId. A single TailSpend account accumulates several rows: the durable upstream anchor (google#… / apple#…, parsed from Cognito's federated identities metadata on first sign-in) plus the request-time cognito#<pool-sub> mapping. Anchoring on the upstream provider identity — never on email — means a Cognito pool replacement (which issues fresh subs) reconnects the same human to the same account via their google#…/apple#… row.

This table sits on the JWT-verification hot path: every authenticated request resolves cognito#<sub> from the verified token to a UserId via a single GetItem. Caching that mapping in-Lambda is reasonable but not required.

Keys

Attributes

Key Format

GSI: UserIdentitiesIndex

Lists every identity linked to a given UserId (e.g. for an account-settings screen showing "you can sign in with Google, Apple").

Projection: ALL. The list-identities view wants Provider, Subject, and CreatedAt per row, so projecting everything keeps it a single read.

Access Patterns

The attribute_not_exists condition on link enforces the global uniqueness invariant: an external identity can be linked to at most one TailSpend user. If a user attempts to link an identity already attached to another account, the request fails with 409 Conflict.

The provisioning Lambda writes the new profile row and its identity rows (the upstream google#…/apple#… anchor and the cognito#<sub> mapping) in a single TransactWriteItems so a partial signup never leaves a user with no identities or an identity pointing at no user. A returning user with a new pool sub but a known upstream identity adds only the new cognito#<sub> row, linked to the existing UserId.

GroupData Table

Single-table layout: every per-group entity shares the GroupId partition, distinguished by sort-key prefix and EntityType.

Keys

Sort key encoding

A single Query against GroupId = <gid> returns every row in the group. Most reads only need a subset; partition-bounded begins_with(SK, "PARTICIPANT#") etc. narrows to a single entity kind. Heavy access patterns use the GSIs below.

Attributes

Common to every row kind:

Group-head-only attributes:

Invite-only attributes:

Join-request-only attributes:

GSI: PendingJoinRequestsIndex (sparse)

Lists pending join requests for a group, in chronological order, without scanning decided rows.

Sparse: only items with PendingKey set are indexed; setting Status to anything other than PENDING removes the attribute and drops the row from the index.

Projection: ALL. The OWNER review UI shows everything on the row.

GSI: UserPendingJoinRequestsIndex (sparse)

Inverts the table for the applicant: "do I already have a pending request to group X?", and "list all my pending requests across groups".

UserPendingKey is both the sparseness signal and the partition key — present only on Status = PENDING rows, where it holds the applicant's UserId. The PK/SK pair is then (UserPendingKey, GroupId), which keeps "do I have a pending request to this group?" a one-row Query.

Projection: ALL. The applicant view renders the request body without a follow-up base-table read.

Access Patterns

Expenses

Expenses and their comments live in the GroupData table — same partition (GroupId) as the group head, invite, participants, and join requests. Expenses occupy two SK shapes:

A single Query against GroupId = <gid> AND begins_with(SK, "EXPENSE#<eid>#") returns the expense first and then all of its comments in chronological order, in one round-trip.

Keys

Sort key encoding

The 0 / 1 segment between the expense id and the row kind is intentional. Lexicographic order on SK puts 0 before 1, so a Query with begins_with(SK, "EXPENSE#<eid>#") returns the expense head first, followed by all comments in chronological order, in a single round-trip.

Embedding createdAt in the comment sort key gives chronological ordering without a separate index, and the trailing commentId keeps it unique if two comments arrive in the same millisecond. All createdAt values in SK segments are required to be UTC with a Z suffix (see Invariants) so lexicographic order matches chronological order.

Attributes

Common to both row kinds:

Expense-only attributes:

Comment rows reference their parent expense purely through the sort key prefix (EXPENSE#<expenseId>#1#COMMENT#…); they do not duplicate ExpenseId as an attribute. This keeps ExpenseIdIndex sparse on expense rows.

Comment-only attributes:

GSI: GroupExpensesIndex (sparse)

Lists only expense rows in a group, so paginating "all expenses in this group" doesn't read comment rows. Sparse: only items with ExpenseSortKey set are indexed, and only expense rows set it.

ExpenseSortKey lets clients page through expenses by the selected occurrence date, preserving creation-time order within a day. Its shape remains lexicographically compatible with legacy <createdAt>#<expenseId> values.

Projection: ALL. The list-expenses endpoint returns full expense documents, so projecting all attributes avoids a follow-up base-table read per item.

GSI: ExpenseIdIndex (sparse)

Looks up an expense by id alone (e.g. when the caller does not know the group). Sparse on ExpenseId, which is set only on expense rows, so comment rows are not indexed.

Projection: KEYS_ONLY. Callers use this index to resolve ExpenseId → (GroupId, SK) and then GetItem on the base table for the full row. Keeping the projection minimal keeps storage cost and write amplification low.

Access Patterns

Deleting an expense fans out across its comment rows. The delete re-queries the partition (paging past the 1 MB Query limit) and deletes every page until the partition is empty, so a thread larger than one page can't leave orphaned comments behind (see #241). Pages of ≤ 100 keys go through an atomic TransactWriteItems; larger pages fall back to BatchWriteItem.

Participants

Tracks every entity that can appear in a group's expenses — authenticated users and unauthenticated guests alike. Participant rows live in the GroupData table at SK = "PARTICIPANT#<participantId>". Authorization is role-based at the group level: every expense and comment in a group inherits the actor's group role. There are no per-expense or per-comment ACLs.

A guest is a row with Type = GUEST and no UserId; on claim (see Claim flow invariants), the same row flips to Type = USER with a UserId attached. Because expense Payments and item splits reference ParticipantId, claiming a guest never rewrites historical expenses — it's a single-row mutation.

Keys

A begins_with(SK, "PARTICIPANT#") Query lists participants of a group; a point GetItem on (GroupId, "PARTICIPANT#<pid>") resolves "is this participant in this group, and what's their role?" in one read.

Attributes

Roles

The role set is closed and small. Adding a role is a deliberate, schema-level change.

GUEST participants have no Role capabilities until they're claimed — they can be referenced in expenses but cannot themselves authenticate. Their Role attribute records the role they will hold post-claim, so the OWNER's intent ("I'm inviting them as a VIEWER") is durable from the moment the guest is created.

A group always has at least one OWNER. Removing the last OWNER, demoting the sole OWNER, or deleting the participant row of the sole OWNER is rejected by the API tier. This is enforced atomically via an OwnerCount number on the group head row (SK = "META"): every owner add / remove / role-flip mutates OwnerCount in the same TransactWriteItems as the participant write, and demotions/removals condition the head write on OwnerCount > 1 so the guard can't be defeated by a read-then-write race (two concurrent demotions of different OWNERs when exactly two exist).

USER uniqueness row (USERMEMBER#<userId>)

To enforce one USER participant per group, every Type = USER participant write also writes a sibling uniqueness row at SK = "USERMEMBER#<userId>" under attribute_not_exists(SK), in the same TransactWriteItems as the participant PUT. The participant row's own SK is a fresh PARTICIPANT#<uuid>, so its attribute_not_exists(SK) guard can never collide on a repeat join — the uniqueness row, keyed on the stable userId, is the actual guard. A repeat join cancels the transaction (TransactionCanceledException), which the API tier maps to 409 Conflict. Removing a USER participant deletes its uniqueness row in the same transaction so the user can re-join. The USERMEMBER# prefix is distinct from PARTICIPANT#, so the roster begins_with(SK, "PARTICIPANT#") Query never surfaces these rows; the group cascade-delete sweeps them with the rest of the partition.

USER display-name sync

A USER participant row's DisplayName is a denormalized copy of the account's profile name — it is read on the group hot path (roster, expense rows, balances), so storing it on the row avoids a per-participant join against the UserData table on every read.

The copy is kept in step at the write that can invalidate it: PATCH /users/me walks UserParticipantsIndex for the caller and rewrites DisplayName on every participant row it finds (ParticipantsRepository.renameUserParticipants). Each row's update is conditioned on attribute_exists(SK), so a participant removed between the eventually-consistent GSI read and the write is skipped rather than resurrected.

The fan-out is best-effort: the profile row is the source of truth and is written first, so a throttled or partially-applied fan-out never fails the user's rename. It is logged (users.participant_name_sync_failed) and every write is idempotent, so the next rename re-converges. A guest's group-local name is untouched — only USER rows mirror an account.

GSI: UserParticipantsIndex (sparse)

Inverts the table for actor-centric queries: "what groups is this user a participant in?" — the home-screen list. Sparse on UserId, which is set only on USER participants, so guest rows are not indexed.

Projection: INCLUDE (Role, ParticipantId). The home-screen list needs the role to render group cards (e.g. show an "owner" badge or a settings button) and the ParticipantId so subsequent same-screen actions don't need to re-resolve it. CreatedAt / UpdatedAt aren't needed for this view, so we skip the cost of ALL.

Access Patterns

The claim is a single UpdateItem whose ConditionExpression ensures the participant is still a guest (so a double-claim races safely and the loser gets ConditionalCheckFailedException → 409 Conflict).

The authorization hot path resolves (authenticated UserId, requested GroupId) → ParticipantId, Role via Query on UserParticipantsIndex. The query is on a single-item collection (UserId partition + GroupId sort key), so it's a point-shaped read; the indirection through ParticipantId is what makes guest claim a single-row mutation instead of a fan-out across every expense in the group.

Claim flow invariants

These constraints govern the join-request approval flow end-to-end. They live here rather than in Invariants because they span join-request and participant rows in the GroupData table.

• Stable ParticipantId. A guest's ParticipantId is created on guest creation and never changes — including across TAKEOVER claim. All historical expense Payments and item splits remain valid.
• Atomic approve, transactionally. Approve is a single TransactWriteItems against the GroupData table whose shape depends on Mode:
  • TAKEOVER: UpdateItem flipping the join-request row from Status = "PENDING" to APPROVED (and removing the sparse keys), and UpdateItem on the participant row flipping Type from GUEST to USER, setting UserId, the OWNER's chosen Role, and ClaimedAt. The first item is conditioned on Status = "PENDING"; the second on Type = "GUEST". If either condition fails, the whole transaction fails.
  • NEW: UpdateItem on the join-request row (same shape as above) plus PutItem on a fresh participant row with attribute_not_exists(SK) for a newly minted UUID.
  • In both modes a partial success is impossible. Cancellation reasons map: index [0] failed → 409 Conflict "Request is no longer pending"; index [1] failed → 409 Conflict (target no longer a guest, or participant id collision).
• Already-a-member rejection. Submitting a join request pre-checks UserParticipantsIndex for an existing (UserId, GroupId) participant; if found, the submit returns 409 Conflict. The same check is repeated on approve to defend against an OWNER racing with a parallel approve in another tab.
• At most one pending request per (UserId, GroupId). Submitting pre-checks UserPendingJoinRequestsIndex for an existing row at (UserPendingKey = applicant UserId, GroupId); if found, the submit returns 409 Conflict. The applicant must cancel their pending request before submitting a fresh one.
• Cascade on guest deletion. Deleting a guest participant (e.g. the OWNER removes the guest before anyone has joined) converts every PENDING JOINREQ row whose TargetParticipantId matches into CANCELLED so the OWNER list does not show stale "claim a guest who no longer exists" rows. Implemented in ParticipantsService.removeParticipant: a Query on PendingJoinRequestsIndex (sparse on PendingKey) with a FilterExpression on TargetParticipantId, followed by a per-row TransactWriteItems that flips the row to CANCELLED, REMOVEs the sparse keys, and deletes the per-applicant pending lock. Per-row cancellations swallow ConditionalCheckFailedException so a concurrent OWNER decision on one of the rows doesn't fail the whole pass.
• OWNER promotion is forbidden via join requests. The proposed and final roles are limited to MEMBER or VIEWER. Promoting a participant to OWNER requires an existing OWNER's explicit role-change action via PATCH /groups/{groupId}/participants/{participantId}.
• Disabled invite reads as missing. A GET /groups/{groupId}/invite* against a disabled or absent invite returns 404 Not Found, never 200 with stale data. The handler reads (<groupId>, "INVITE") and rejects when Enabled === false before reading anything else.

Invariants

These rules apply across the schema and must be enforced on the write path. Read paths can rely on them.

• UTC timestamps only. Every createdAt / updatedAt value, including the segments embedded in SK and ExpenseSortKey, must be ISO 8601 with a literal Z suffix. Mixing offsets breaks the lex-sort guarantees the schema relies on for chronological reads.
• Optimistic concurrency on expenses. Expense rows carry a Version (number, starts at 1). Every UpdateItem increments it and conditions on the previously-read value via ConditionExpression: Version = :expected. Concurrent edits surface as ConditionalCheckFailedException, which the API tier maps to 409 Conflict.
• ExpenseId is set only on expense rows. This keeps ExpenseIdIndex sparse to expense heads. Comment rows reference their parent through the SK prefix instead.
• Row-kind segments in SK use 0/1. 0#META for the expense head and 1#COMMENT#… for comments, so a single Query returns the head before any comments without server-side filtering. New row kinds added later must pick 2, 3, … and never reuse 0 or 1.
• Key-encoded attributes are immutable after create. SK, ExpenseSortKey, IdentityKey, and the timestamp segments inside them must never be mutated by an UpdateItem. Renaming or re-timestamping requires a delete-and-recreate. The write path enforces this by never including these attributes in UpdateExpression.
• Conditional creates. All entity creates use a ConditionExpression to make retries safe and to enforce uniqueness:
  • Users.PutItem uses attribute_not_exists(UserId).
  • Identities.PutItem uses attribute_not_exists(IdentityKey) (also enforces global identity uniqueness).
  • New GroupData rows (group head, invite, JOINREQ, participant, expense head, comment, recurring) use attribute_not_exists(SK). The per-applicant join-request lock also requires absence, except a valid invite may atomically replace a JOINREQ_DENIED_LOCK. A denial replaces its pending lock in the same transaction as the conditional request update, preventing tokenless resubmission races. A retry from a Lambda timeout therefore either succeeds idempotently or surfaces a ConditionalCheckFailedException that the API tier maps to 409 Conflict.
• Group + owner + invite are written transactionally. Creating a group writes four rows into the GroupData partition in a single TransactWriteItems call: the META group head, the INVITE row, the creator's OWNER participant row, and an initialized BALANCE#v1 row. If any one fails, the whole operation rolls back, so a group never exists without an owner or without its invite settings.
• Group always has at least one OWNER. Removing the last OWNER from a group is rejected by the API tier (returns 409 Conflict). Demoting the sole owner is also rejected. Enforced atomically via the head row's OwnerCount (see Participants → Roles): the participant write and the guarded OwnerCount mutation (OwnerCount > 1 on a decrement) ride in one TransactWriteItems, so two concurrent demotions/removals can't both succeed and zero out the owners. OwnerCount is seeded to 1 in createGroupTransact; groups predating the attribute are self-healed from a countOwners read seeded via if_not_exists on the first owner mutation.
• One USER participant per group. A Type = USER participant write also writes a USERMEMBER#<userId> uniqueness row (attribute_not_exists(SK)) in the same transaction; a repeat join cancels the transaction → 409 Conflict. Removal deletes the uniqueness row in the same transaction. See Participants → USER uniqueness row.
• Expense participants must exist. Every ParticipantId referenced in an expense's Payments or item splits must correspond to a row in Participants for that group at write time. The API tier validates this by reading the relevant participant rows before the write. Deleting a participant who is referenced by an expense is rejected (409 Conflict); the OWNER must reassign or remove the references first.
• Cascade on expense delete. Deleting an expense first queries begins_with(SK, "EXPENSE#<eid>#") and then deletes all returned rows (head plus comments). When the row count fits, this runs as a single TransactWriteItems (atomic). Otherwise it falls back to BatchWriteItem, which is not atomic: a partial failure can leave orphan comment rows. The implementation must (a) use idempotent deletes (no condition expression so a re-run is a no-op on already-deleted rows), and (b) retry on partial-batch responses (UnprocessedItems) until empty. A background sweeper that re-runs the cascade for any expense head missing its META row but with surviving comment rows is recommended for defence-in-depth.
• Identity uniqueness. Every (provider, subject) pair maps to at most one UserId. Enforced by the conditional create above. Linking an identity already attached to another user surfaces as ConditionalCheckFailedException, which the API tier maps to 409 Conflict.
• Atomic signup. The provisioning Lambda writes the new profile row and its identity rows (the upstream google#…/apple#… anchor plus the cognito#<sub> mapping) in a single TransactWriteItems call. A user always has at least one identity, and an identity always points at a real user.

Recurring expense rows

Recurring-expense templates are another row kind in the GroupData table — keyed (GroupId, "RECURRING#<id>") — alongside the group head, invite, join-request, participant, expense, and comment rows. Single-table design keeps a template colocated with the materialised expenses it produces, in one partition.

Sort key

Attributes (recurring rows)

GSI: DueRecurringExpensesIndex (sparse)

A sparse GSI on the GroupData table, alongside the participant, expense, and join-request indexes.

Projection: ALL. The hourly tick reads due rows and materialises expenses without a follow-up base-table read. Sparse on DueBucket so paused rows are skipped automatically.

Access Patterns (recurring rows)

The hourly tick reads the entire due set across all GSI pages before mutating any row, then processes the snapshot serially. Because the slot-claim advances NextRunAt — the due GSI's sort key — paging the index while mutating it could otherwise skip rows (moved past the cursor) or revisit them (moved into a later page). Slot-claim conditions on NextRunAt = :prev so a duplicate tick (or parallel reader of the same row) finds the slot already advanced and receives ConditionalCheckFailedException — the slot itself is the concurrency token; no separate Version attribute is needed. Domain failures during materialisation (group deleted, participant removed) leave the slot claimed and surface as a failed count so the template doesn't re-attempt every hour; transient failures (throttling, Lambda kill) roll NextRunAt back to the claimed slot (conditioned on the advanced value still holding) and rethrow, so the occurrence is retried next tick instead of being silently dropped. Single-bucket layout is fine at the expected scale; if a future workload outgrows one partition, the GSI key can be sharded by hashing the id without changing the public surface.

Settlements

Settlements are a row kind in the GroupData table — keyed (GroupId, "SETTLEMENT#<createdAt>#<settlementId>") — recording a direct payment from one participant to another ("A paid B 20.00"). Unlike an expense a settlement has no items or splits: it is a single transfer of Amount (group-currency minor units) from PayerId to PayeeId.

Sort key

Prefixing the SK with createdAt (ahead of the id) makes a base-table begins_with(SK, "SETTLEMENT#") query with ScanIndexForward: false return a group's settlements newest-first, so the transaction-history list needs no dedicated GSI. A settlement has no child rows, so the id can trail the timestamp without breaking any head-before-children ordering (contrast the expense head's #0#META / #1#COMMENT scheme).

Attributes (settlement rows)

Effect on balances

A settlement shifts the running net (net = paid − owed, positive when owed): the payer's net moves up by Amount and the payee's net moves down by the same amount, so the group's balances still sum to zero. The balance projection (GET /groups/{groupId}/balances) reads all expenses + all settlements + the participant roster, computes each participant's net, and runs a greedy debt-simplification to produce the minimal "who owes who" transfer plan. The per-caller dashboard balance (GET /users/me/groups) folds settlements into the same net so the two never diverge.

Access Patterns (settlement rows)

Locating a settlement by id requires a filtered query (the createdAt in the SK is unknown to the caller), mirroring how expense comments are resolved by CommentId. Settlement rows are swept by the group-delete cascade automatically — it deletes every row in the GroupId partition.

Operational notes

• All tables use RemovalPolicy.RETAIN in CDK so accidental stack deletion does not drop data.
• tailspend-{stage}- prefixes isolate environments inside the same AWS account.
• The GroupData table mixes every per-group entity (head, invite, join requests, participants, expenses, comments, recurring templates) in a single GroupId partition, distinguished by sort-key prefix and EntityType. Loading "the group" is one Query; loading "an expense and its thread" uses begins_with(SK, "EXPENSE#<eid>#"); listing pending join requests uses the sparse PendingJoinRequestsIndex.
• DynamoDB's 400 KB per-item ceiling now only applies to a single expense head or a single comment, both of which are small.
• Group + initial owner + invite are written transactionally in a single TransactWriteItems call across the same partition; participant add/remove and role change are single-row writes.
• Approving a join request is one TransactWriteItems across the JOINREQ row, the participant row, and the per-applicant lock row — all in GroupData. For TAKEOVER, the participant's ParticipantId is stable, so historical expenses are not rewritten. For NEW, a fresh ParticipantId is minted.
• Decided join-request rows (APPROVED / DENIED / CANCELLED) are kept on the base table for audit but drop out of both GSIs because the PendingKey and UserPendingKey attributes are removed on decision. A future cleanup sweep (or TTL pass) can prune them.
• Deleting an expense uses a Query (begins_with(SK, "EXPENSE#<eid>#")) followed by a batched delete of all returned rows. Deleting a group tombstones the head row first (UpdateItem SET Status = "DELETING" under attribute_exists(SK)) so the group is immediately unreadable — every read path (getGroupById, batchGetGroupsById) treats a DELETING head as not-found — then sweeps the rest of the partition with idempotent (unconditional) batched deletes and removes the head row last. The sweep is resumable: a mid-sweep crash leaves a group that still reads as gone, and the delete can be re-run to finish. This closes the previous non-atomic window where a partial sweep left a half-deleted group reading as live with orphaned participants still pointing at it on the GSI.
• Hot-partition considerations. All rows for a single group share one partition: head + invite + participants (bounded) + expenses + comments + join requests + recurring templates. DynamoDB on-demand provides up to 1000 WCU / 3000 RCU per partition with adaptive capacity, which is comfortable for expected activity. If a group ever approaches those limits in practice, the partition can be split by sharding GroupId with a small fan-out suffix; not needed today.
• GSI projections. GroupExpensesIndex and UserIdentitiesIndex project ALL so list reads are one round-trip; UserParticipantsIndex projects INCLUDE (Role, ParticipantId) so the home-screen list renders without a second read; PendingJoinRequestsIndex and UserPendingJoinRequestsIndex project ALL so the OWNER review UI and the applicant's "my pending requests" view both render without follow-up reads; ExpenseIdIndex projects KEYS_ONLY to keep storage and write amplification minimal.

Materialized group net balances

(GroupId, SK = BALANCE#v1) stores BalanceRevision (number), Initialized (boolean), ExpenseCount, SettlementCount, and numeric Net_<participantId> attributes. Positive net means owed; negative means owing. Each source mutation adds old-to-new net deltas, activity counts and one revision atomically. Uninitialized legacy rows accumulate revisions until backfilled; their partial nets are never served. See migration and rollout.

Pending direct invitations may include ParticipantId pointing to a reserved GUEST participant with InvitedUserId and no UserId or USERMEMBER row. Reservation and invitation are atomic. Accept consumes the invitation, conditionally claims that guest and creates the uniqueness row in one transaction. Its stable ID preserves already assigned expenses. Missing/changed slots return a conflict; acceptance never silently creates a different financial identity.

InviteExpiresAt records the active reservation deadline on a guest; InvitedUserId is a durable expense-identity link, never a membership/GSI key. Decline/revoke atomically deletes the invitation and removes the matching InviteExpiresAt, retaining the guest, history and link for reinvite. Expiry leaves history intact. Owner invitation creation may link an existing unreserved guest through a conditional update. Legacy invitations missing ParticipantId are repaired by a conditional cross-table transaction that preserves the original approval/expiry. Acceptance conditions on the invitation's creation, deadline and participant ID, and on the guest's linked account and active deadline. Generic TAKEOVER approvals reject a live reservation and clear stale reservation attributes when claiming. Account deletion clears outstanding guest reservations before deleting invitations.

TransferTarget is present only while a participant transfer is in progress and stores its fixed destination participant ID. A conditional transaction locks the source and checks that the target exists and is not transferring. Expense, settlement and recurring-template writes condition-check every newly supplied participant reference in the same transaction; a locked/missing participant returns 409. Transfers use optimistic expense/settlement versions and recurring UpdatedAt checks, and retain the source lock after partial failure so the same operation can resume safely. A successful transfer unlocks a retained source or removes it after strongly consistent financial-reference checks, including recurring templates. These checks query the base table with an EntityType filter for EXPENSE and RECURRING_EXPENSE, paging past empty filtered pages; sort-key predicates belong in key conditions, never in query filters. No new table or index is introduced.

Expense chat records (UserData)

Expense chat reuses the user table and existing balance/ledger:

Admission writes session, balance, ledger, counters and work pointer in one conditional transaction, also checking PROFILE existence. Worker claims/results use optimistic version guards; finish removes the work pointer atomically. Outbox reconciliation queries the service partition (no Scan). Chat-only DynamoDB clients request consumed capacity and log aggregate read/write units for serving-cost validation, never keys or content. A saved marker commits in the same transaction as the expense/balances/notification, so even deletion of the created expense cannot allow confirmation replay to recreate it. Account deletion sweeps the complete user partition; orphaned global work pointers are removed when the backstop sees a missing session.

OpenAI MCP connections (#762)

MCP reuses the Users table and its existing ExpiresAt TTL configuration. No new table, index or database engine is required. Grants/session bindings do not set scan-recovery index attributes and do not match PROFILE/BALANCE/ledger keys. All authorization/grant reads are strongly consistent; TTL is not authorization.

Approval conditionally consumes one unexpired request and creates a pending grant. Callback consumes the approval and creates a code-hash claim. Token exchange/ refresh atomically updates the still-approved grant, binds the token family and records a new refresh hash and cleanup pointer. The prior refresh hash is claimed once before the upstream exchange. No code, access token, ID token, plaintext refresh token or token-response cache is persisted.

Disconnect changes the grant to REVOKED, checked on every request and refresh. A new connection has a different grant ID; old token families cannot be rebound to it. Session bindings are removed by account deletion. Before the normal user partition sweep, account deletion follows every MCP cleanup pointer and removes its off-partition locators; each batch deletes its targets before its pointers, preserving progress when account deletion is retried. Unapproved anonymous authorization requests expire after ten minutes.

MCP usage is recorded in the existing developer daily aggregate namespace, with tokenId equal to the stable grant UUID. Its name is displayed as OpenAI. PAT and MCP activity share DEV#USER#<owner> budgets and developer create allowances; refresh and additional grants do not reset the per-user counters.