DynamoDB Tables

Three tables in the deploy region hold all service data, and three shared tables in us-east-1 (prod) carry item history and the listener’s item lookup. The prefixed tables use a single-table design: string PK/SK, a type attribute naming the entity, a ttl attribute, and global secondary indexes GSI1–GSI4 (GSI<n>PK/GSI<n>SK). Key segments are joined with #.

Batch writes and batch reads (BatchWriteItem, BatchGetItem) go through the fmsdk aws/dynamo helpers with WithRetryUnprocessed: items DynamoDB leaves unprocessed are retried with exponential backoff, up to 7 attempts per batch, and any still left return ErrBatchUnprocessed instead of being dropped.

`site-txn-transactions`

Streams: NEW_AND_OLD_IMAGES, consumed by the feeder (filtered — see Event Source Mappings). Indexes: GSI1–GSI4.

type PK SK Indexes Written by Read by

`type`	PK	SK	Indexes	Written by	Read by
`TRANSACTION`	`TXN#<txn_id>`	`TXN#<txn_id>`	GSI1 `ACCOUNTS#<user_id>`; GSI2 `ACCOUNTS#<user_id>#CATEGORY#<category_id>`; GSI3 `ACCOUNTS#<user_id>#ACCOUNT#<acct_id>`; GSI4 `ACCOUNTS#<user_id>#ACCOUNT#<acct_id>#CATEGORY#<category_id>`; all with SK `DATE#<YYYY-MM-DD>#TXN#<txn_id>`	refiner	api
`ACCOUNT`	`ACCOUNTS#<user_id>`	`ACCOUNT#<acct_id>`	—	refiner; api (live-balance refresh)	api; feeder (stream)
`BALANCE`	`BALANCE#<user_id>`	`ACCOUNT#<acct_id>#<YYYY-MM-DD>`	—	refiner; api (QA seed)	api
`WEBHOOK_INFO`	`WEBHOOK_ITEM#<item_id>`	`DATETIME#<ts>#TYPE#<type>#CODE#<code>`	GSI1 `WEBHOOK_USER#<user_id>` / `DATETIME#…ITEM<item_id>`; GSI2 `WEBHOOK_ITEM#<item_id>` / `TYPE#<type>#CODE#<code>#DATETIME#<ts>`; GSI3 `WEBHOOK_USER#<user_id>` / `TYPE#…DATETIME…ITEM…`; GSI4 `WEBHOOK_ITEM#<item_id>` / `SOURCE#<source>#TYPE#…DATETIME…`	miner	listener
`REMOVE`	`REMOVE#<txn_id>`	`REMOVE#<txn_id>`	GSI1 `REMOVE` / `REMOVEDATE#<ts>`	refiner	refiner
`CATEGORY`	`CATEGORIES`	`HIERARCHY#<level1>#<level2>…`	GSI1 `CATEGORY#<category_id>` / `CATEGORY#<category_id>`	None in this repo	api
`SUMMARY`	`SUMMARY#<user_id>`	`DATE#<YYYY-MM>`, optionally `ACCOUNT<acct_id>` and/or `CATEGORY<category_id>`	—	`cmd/summary` (not deployed)	api
`LAYER`	`LAYER#<public_token>`	`TOKEN`	—	api	api; feeder (TTL expiry)

TRANSACTION

TXN#<txn_id>

GSI1 ACCOUNTS#<user_id>; GSI2 ACCOUNTS#<user_id>#CATEGORY#<category_id>; GSI3 ACCOUNTS#<user_id>#ACCOUNT#<acct_id>; GSI4 ACCOUNTS#<user_id>#ACCOUNT#<acct_id>#CATEGORY#<category_id>; all with SK DATE#<YYYY-MM-DD>#TXN#<txn_id>

refiner

api

ACCOUNT

ACCOUNTS#<user_id>

ACCOUNT#<acct_id>

—

refiner; api (live-balance refresh)

api; feeder (stream)

BALANCE

BALANCE#<user_id>

ACCOUNT#<acct_id>#<YYYY-MM-DD>

—

refiner; api (QA seed)

api

WEBHOOK_INFO

WEBHOOK_ITEM#<item_id>

DATETIME#<ts>#TYPE#<type>#CODE#<code>

GSI1 WEBHOOK_USER#<user_id> / DATETIME#…ITEM<item_id>; GSI2 WEBHOOK_ITEM#<item_id> / TYPE#<type>#CODE#<code>#DATETIME#<ts>; GSI3 WEBHOOK_USER#<user_id> / TYPE#…DATETIME…ITEM…; GSI4 WEBHOOK_ITEM#<item_id> / SOURCE#<source>#TYPE#…DATETIME…

miner

listener

REMOVE

REMOVE#<txn_id>

GSI1 REMOVE / REMOVEDATE#<ts>

refiner

CATEGORY

CATEGORIES

HIERARCHY#<level1>#<level2>…

GSI1 CATEGORY#<category_id> / CATEGORY#<category_id>

None in this repo

api

SUMMARY

SUMMARY#<user_id>

DATE#<YYYY-MM>, optionally ACCOUNT<acct_id> and/or CATEGORY<category_id>

—

cmd/summary (not deployed)

api

LAYER

LAYER#<public_token>

TOKEN

—

api

api; feeder (TTL expiry)

Source: pkg/dynamo/transaction.go, account.go, account_balance.go, webhook_info.go, remove.go, category.go, summary.go, layer_stub.go

Transaction

txn_amount is in cents; positive is money leaving the account.
Attributes: user_id, acct_id, txn_id, txn_amount, txn_date, txn_authorized_date, txn_authorized_datetime, txn_name, txn_merchant_name, txn_original_description, txn_pending, txn_pending_transaction_id, txn_removed, txn_category_id, txn_category, txn_personal_finance_category, txn_iso_cc, merchant_logo_url, merchant_website. Optional attributes are omitted when empty. Rows written before this set may also carry txn_amount_string, which the API reads to correct negative amounts stored before 07/24.
ttl is the transaction date plus 120 days, never sooner than 14 days after the write. Source: pkg/dynamo/utils.go.
Soft delete prefixes every GSI sort key with REMOVED# and sets txn_removed. List queries use begins_with DATE#, so deleted rows drop out of lists while point reads by txn_id still return them.

Account

acct_balances holds bal_available, bal_current, calc_bal_available, and bal_limit in cents, each with a _string copy. Plaid may return null for available or current; the amount is then stored as 0 and bal_available_reported / bal_current_reported is false, so a stored 0 with the flag true is a genuine $0 reading. The API exposes the flags as available_reported and current_reported; rows written before the flags existed have neither attribute.

Balance

One row per account per calendar day, keyed on the balance’s updated_date, copying the amounts and available_reported / current_reported from the account row. QA-seeded rows set both flags true. ttl is ACCOUNT_BALANCE_TTL_DAYS (100) from the write; QA-seeded rows use 30 days.

Webhook Info

One row per webhook the miner handles, carrying the raw Plaid payload and, for transaction mines, update_info (total transactions, accounts, institution). source is PLAID for Plaid-originated webhooks or the synthetic origin: FROM_LISTENER, FROM_ADD_ITEM, FROM_LAYER_STUB, API_REMINE. The listener reads GSI2 to find recent mines and HISTORICAL_UPDATE rows — Listener. Every row carries a ttl 30 days after the write.

Remove

A tombstone per retracted txn_id. The refiner checks incoming transactions against tombstones so an out-of-order insert after a removal is re-tombstoned — Refiner. ttl is 30 days after the write.

Summary

Monthly spend totals with added/removed transaction-id sets, at four granularities (month, month+account, month+account+category, month+category).

GET /{user_id}/transactions/summary reads these rows, but the only writer is the undeployed cmd/summary binary. The endpoint returns whatever was last written.

Layer Stub

Written when a Plaid Layer session resolves, holding items (item_id, access_token pairs) with a 7-day ttl. Consumed when the user’s items are created from the stub; on TTL expiry the feeder disconnects any stub item that never became an ITEM — Plaid Layer.

`site-txn-plaid`

Streams: NEW_AND_OLD_IMAGES, consumed by the feeder for ITEM TTL expirations only. Indexes: GSI1, GSI2.

type PK SK Indexes Written by Read by

`type`	PK	SK	Indexes	Written by	Read by
`ITEM`	`USERS#<user_id>`	`ITEMS#<item_id>`	GSI1 `ITEMS#<item_id>` / `USERS#<user_id>`	api, miner, feeder	api, miner, throttle-miner, feeder
`LIABILITY` (current)	`LIABILITIES#<user_id>`	`ITEM#<item_id>#ACCOUNT#<acct_id>`	GSI1 `LIABILITIES#<acct_id>` / `USERS#<user_id>`	miner	api
`LIABILITY` (history)	`LIABILITY_HISTORY#<user_id>`	`ITEM#<item_id>#ACCOUNT#<acct_id>#TIMESTAMP#<ts>`	GSI1 `LIABILITY_HISTORY#<acct_id>` / `TIMESTAMP#<ts>`; GSI2 `LIABILITY_HISTORY#<user_id>` / `TIMESTAMP#<ts>`	miner	api
`IDENTITY_VERIFICATION`	`USERS#<user_id>`	`IDENTITY_VERIFICATION#<idv_id>`	GSI1 `USERS#<user_id>` / `IDENTITY_VERIFICATION#<created_at>#<idv_id>`; GSI2 `USERS#<user_id>` / `IDENTITY_VERIFICATION#<watchlist_id>`	api, miner	api, miner

ITEM

USERS#<user_id>

ITEMS#<item_id>

GSI1 ITEMS#<item_id> / USERS#<user_id>

api, miner, feeder

api, miner, throttle-miner, feeder

LIABILITY (current)

LIABILITIES#<user_id>

ITEM#<item_id>#ACCOUNT#<acct_id>

GSI1 LIABILITIES#<acct_id> / USERS#<user_id>

miner

api

LIABILITY (history)

LIABILITY_HISTORY#<user_id>

ITEM#<item_id>#ACCOUNT#<acct_id>#TIMESTAMP#<ts>

GSI1 LIABILITY_HISTORY#<acct_id> / TIMESTAMP#<ts>; GSI2 LIABILITY_HISTORY#<user_id> / TIMESTAMP#<ts>

miner

api

IDENTITY_VERIFICATION

USERS#<user_id>

IDENTITY_VERIFICATION#<idv_id>

GSI1 USERS#<user_id> / IDENTITY_VERIFICATION#<created_at>#<idv_id>; GSI2 USERS#<user_id> / IDENTITY_VERIFICATION#<watchlist_id>

api, miner

Source: pkg/dynamo/item.go, liability.go, identity_verification.go

Item

item_status values and who sets them: Item Statuses.
item_source is PREQUAL on prequal items and absent otherwise.
ttl is set on reactivation and prequal items and cleared by the persist endpoint — TTL Expiry.
item_removed_reason is MX for a forced removal, item expired, user did not complete prequal, or item expired, user did not complete reactivation.
item_processor_tokens is a list of {processor, processor_token, account_id}.
Lookups by item id alone use GSI1; more than one row for an item id logs a warning and returns the first.

Liability

type is CREDIT, MORTGAGE, or STUDENT (source: pkg/dynamo/liability.go). Every store overwrites the current row and appends a history row; history is never deduplicated.

Identity Verification

idv_kyc_status and idv_watchlist_status values: KYC Statuses. GSI1 returns the latest session for a user (sorted by created_at); GSI2 finds the session for a SCREENING webhook by watchlist id. idv_steps holds the per-step status strings Plaid reports.

`site-txn-plaid-institutions`

Key-value table keyed on institution_id; no sort key, no streams. Attributes: name, products, country_codes, url, primary_color, logo (base64 PNG), routing_numbers, dtc_numbers, oauth. The institution Lambda overwrites every row weekly with a full PutItem; the API reads it for institution names and logos.

Source: pkg/dynamo/institution.go

Shared Tables (us-east-1)

One table without an environment prefix, in us-east-1 in staging and prod and the deploy region in test. None is defined in this repo’s Terraform.

Table Queried by Use

Table	Queried by	Use
`plaid-account-changes`	`user_id`, newest first	One row per item removal or main-account change (`user_id`, `item_id`, `change_date`, `account_id` = previous main account, `is_override`). Read by Change Check and the item-history endpoint.

plaid-account-changes

user_id, newest first

One row per item removal or main-account change (user_id, item_id, change_date, account_id = previous main account, is_override). Read by Change Check and the item-history endpoint.

Source: pkg/dynamo/item.go