DynamoDB Tables

This page documents the five primary DynamoDB tables used by the Payments Service: payments, debit cards, bank accounts, account history, and refunds.

Storage is split across two regions:

Primary region (us-east-2 in prod, us-west-2 in test) — the prod-payments single table holds payment records, v2 debit card records (as DEBITCARD# items), and v2 bank account records (as BANKINFO# items). The API Lambda’s bank account store (see cmd/api/main.go wiring bankaccountstore.NewDDBRepository(ddb, config.DynamoTable, …) — ddb is the primary-region client, DYNAMO_TABLE is the single table) reads and writes from this region only.
Legacy region (us-east-1) — the named legacy tables pinless-default-card, bank-accounts, bank-account-history, and usio-debit-refunds live here. They predate the single-table model; the debit-card and bank-account tables act as migration fallbacks, while the refunds table is still the canonical refund store.

The service also uses several additional tables not documented on this page — the fraud log (prod-float-service-log), Usio transaction staging (usio-debit-transactions, usio-credit-transactions, tokenization), jpm-transaction-details, and pinless-changes. See the Terraform data "aws_dynamodb_table" blocks in deploy/main.tf for the full inventory.

`prod-payments`

The primary payment record store. Every payment submitted through the service — pinless debit, ACH credit, ACH debit — produces one record here. ACH payments are written in ACHSENT status and updated in place as they clear or return.

Keys and Indexes

Key / Index Type Description

Key / Index	Type	Description
`confirmation_id`	PK	Unique payment identifier. Generated by the payment processor (JPM or Usio) on submission.
`user_id-index`	GSI (PK: `user_id`)	Retrieves all payments for a given user.
`payment_status-index`	GSI (PK: `payment_status`)	Retrieves payments with a specific status. Used by `check-ach-scheduler` to find `ACHSENT` payments due for polling.

confirmation_id

Unique payment identifier. Generated by the payment processor (JPM or Usio) on submission.

user_id-index

GSI (PK: user_id)

Retrieves all payments for a given user.

payment_status-index

GSI (PK: payment_status)

Retrieves payments with a specific status. Used by check-ach-scheduler to find ACHSENT payments due for polling.

Attributes

Attribute Description

Attribute	Description
`confirmation_id`	Primary key. Unique payment reference from the processor.
`user_id`	Owning user.
`payment_method`	`ach`, `pinless`, or `rtp`.
`payment_type`	`credit` (funds sent to user) or `debit` (funds collected from user).
`payment_provider`	`jpm` or `usio`.
`payment_status`	`COMPLETED`, `ACHSENT`, `CLEARED`, or `FAILED`. Pinless payments are written as `COMPLETED`. ACH payments start as `ACHSENT` and are updated by the syncing system. `CLEARED` is an intermediate ACH status set when JPM reports the payment has cleared internal review but has not yet been forwarded to the ACH network — see Payment Syncing.
`amount`	Payment amount.
`loan_id`	Float ID if the payment was for a float; otherwise null.
`subscription_id`	Subscription ID if the payment was for a subscription; otherwise null.
`installment_loan_id`	Installment loan ID if applicable; otherwise null.
`cbc_card_id`	Internal card reference.
`return_code`	NACHA return code (e.g. `R02`) or JPM return code that maps to a NACHA code (e.g. `AC04`). Set on returned payments.
`return_info`	Human-readable return description for JPM payments; `RETURNED` for Usio.
`masked_account_number`	Last 4 digits of the bank account. Present on ACH payments only.
`masked_card_number`	Last 4 digits of the debit card. Present on pinless payments only.
`event_type`	Payment event classification used by `kinesis-feeder` to set the event type on the Kinesis record.
`db_type`	Record type discriminator.
`submit_date`	When the payment was submitted.
`return_date`	When the payment was returned. `0001-01-01` if not returned.
`cleared_date`	When the payment was cleared by the ACH network. `0001-01-01` if not cleared.
`completion_date`	For pinless: date of successful payment. For ACH: set when three business days pass without a return. `0001-01-01` if not completed.

confirmation_id

Primary key. Unique payment reference from the processor.

user_id

Owning user.

payment_method

ach, pinless, or rtp.

payment_type

credit (funds sent to user) or debit (funds collected from user).

payment_provider

jpm or usio.

payment_status

COMPLETED, ACHSENT, CLEARED, or FAILED. Pinless payments are written as COMPLETED. ACH payments start as ACHSENT and are updated by the syncing system. CLEARED is an intermediate ACH status set when JPM reports the payment has cleared internal review but has not yet been forwarded to the ACH network — see Payment Syncing.

amount

Payment amount.

loan_id

Float ID if the payment was for a float; otherwise null.

subscription_id

Subscription ID if the payment was for a subscription; otherwise null.

installment_loan_id

Installment loan ID if applicable; otherwise null.

cbc_card_id

Internal card reference.

return_code

NACHA return code (e.g. R02) or JPM return code that maps to a NACHA code (e.g. AC04). Set on returned payments.

return_info

Human-readable return description for JPM payments; RETURNED for Usio.

masked_account_number

Last 4 digits of the bank account. Present on ACH payments only.

masked_card_number

Last 4 digits of the debit card. Present on pinless payments only.

event_type

Payment event classification used by kinesis-feeder to set the event type on the Kinesis record.

db_type

Record type discriminator.

submit_date

When the payment was submitted.

return_date

When the payment was returned. 0001-01-01 if not returned.

cleared_date

When the payment was cleared by the ACH network. 0001-01-01 if not cleared.

completion_date

For pinless: date of successful payment. For ACH: set when three business days pass without a return. 0001-01-01 if not completed.

Access Patterns

Operation Pattern

Operation	Pattern
Get payment by confirmation ID	`GetItem(confirmation_id)`
Get all payments for a user	`Query(user_id-index, user_id = ?)`
Get payments by status (e.g. for ACH polling)	`Query(payment_status-index, payment_status = ACHSENT)`

Get payment by confirmation ID

GetItem(confirmation_id)

Get all payments for a user

Query(user_id-index, user_id = ?)

Get payments by status (e.g. for ACH polling)

Query(payment_status-index, payment_status = ACHSENT)

Notes

Pinless payments are written once and never updated.
ACH payments follow the lifecycle: ACHSENT → COMPLETED (cleared) or FAILED (returned). The return_date and return_code fields are only populated on returned payments.
DynamoDB Streams is enabled on this table. The kinesis-feeder Lambda consumes every stream record but only forwards it to the prod-payments Kinesis stream when the record’s event_type field is non-empty. Writes at submission time (with an empty event_type) are not published; only subsequent status-update writes that set an event_type trigger a Kinesis event. See Event Flows.

Debit Cards: Two-Table Architecture

Debit card storage is split across two tables as part of an ongoing migration from a legacy single-card-per-user model to a multi-card model.

New Table: `prod-payments` (single table, `DEBITCARD` records)

The primary debit card store for all new cards. Debit card records share the prod-payments single table with payment records and other entities, distinguished by their key prefix pattern. A user can have multiple debit cards — each card is a separate item. No raw card numbers or CVVs are ever stored.

Keys and Indexes

Key / Index Type Description

Key / Index	Type	Description
PK: `USER#<user_id>`, SK: `DEBITCARD#<created_on>`	Primary key (composite)	One item per card. A user can have multiple `DEBITCARD` items.
`GSI1` (PK: `DEBITCARDEXP#<MMYYYY>`, SK: `USER#<user_id>#DEBITCARD#<debit_card_id>`)	GSI	Used by `usio-refresh-scheduler` to find cards expiring this month and enqueue token refresh jobs.
`GSI3` (PK: `USER#<user_id>`, SK: `DEBITCARD#<debit_card_id>`)	GSI	Used to look up a specific card by its FloatMe `debit_card_id`.

PK: USER#<user_id>, SK: DEBITCARD#<created_on>

Primary key (composite)

One item per card. A user can have multiple DEBITCARD items.

GSI1 (PK: DEBITCARDEXP#<MMYYYY>, SK: USER#<user_id>#DEBITCARD#<debit_card_id>)

GSI

Used by usio-refresh-scheduler to find cards expiring this month and enqueue token refresh jobs.

GSI3 (PK: USER#<user_id>, SK: DEBITCARD#<debit_card_id>)

GSI

Used to look up a specific card by its FloatMe debit_card_id.

Attributes

Attribute Description

Attribute	Description
`user_id`	Owning user.
`debit_card_id`	Unique FloatMe-assigned identifier for this card.
`masked_card_number`	Last 4 digits of the card number. Display only.
`card_hash`	Hash of the last 4 digits and expiration date. Used for fraud deduplication.
`credit_reference_id`	Usio credit token. Used to submit pinless credit (disbursement) payments.
`debit_reference_id`	Usio debit token. Used to submit pinless debit (collection) payments.
`expiration_date`	Card expiration in `MMYYYY` format.
`created_on`	RFC3339 timestamp when the card was added.
`issuer`	Card network: `MASTERCARD`, `VISA`, `AMEX`, `DISCOVER`, `JCB`, `DINERS`, or `UNKNOWN`. Migrated legacy cards are always `UNKNOWN`.
`is_valid`	Whether the card is currently valid for payments. Set to `false` on certain Usio return codes. Users reset this by re-adding the card.
`invalid_date`	When the card was marked invalid.
`status`	`ACTIVE`, `REMOVED`, or `STALE`.
`primary_card_type`	Which payment types this card is primary for: `ALL`, `FLOATS`, `SUBS`, or `NONE`. Currently always `ALL`. A card with `ALL` is also mirrored to the legacy table (see below).
`linked_bank_account_id`	Bank account linked to this card, if any.

user_id

Owning user.

debit_card_id

Unique FloatMe-assigned identifier for this card.

masked_card_number

Last 4 digits of the card number. Display only.

card_hash

Hash of the last 4 digits and expiration date. Used for fraud deduplication.

credit_reference_id

Usio credit token. Used to submit pinless credit (disbursement) payments.

debit_reference_id

Usio debit token. Used to submit pinless debit (collection) payments.

expiration_date

Card expiration in MMYYYY format.

created_on

RFC3339 timestamp when the card was added.

issuer

Card network: MASTERCARD, VISA, AMEX, DISCOVER, JCB, DINERS, or UNKNOWN. Migrated legacy cards are always UNKNOWN.

is_valid

Whether the card is currently valid for payments. Set to false on certain Usio return codes. Users reset this by re-adding the card.

invalid_date

When the card was marked invalid.

status

ACTIVE, REMOVED, or STALE.

primary_card_type

Which payment types this card is primary for: ALL, FLOATS, SUBS, or NONE. Currently always ALL. A card with ALL is also mirrored to the legacy table (see below).

linked_bank_account_id

Bank account linked to this card, if any.

Access Patterns

Operation Pattern

Operation	Pattern
Get all cards for a user (by status)	`Query(PK = USER#<user_id>, SK begins_with DEBITCARD)` with optional `status` filter
Get specific card by debit_card_id	`Query(GSI3, USER#<user_id> / DEBITCARD#<debit_card_id>)`
Find cards expiring this month (for token refresh)	`Query(GSI1, DEBITCARDEXP#<MMYYYY>)`

Get all cards for a user (by status)

Query(PK = USER#<user_id>, SK begins_with DEBITCARD) with optional status filter

Get specific card by debit_card_id

Query(GSI3, USER#<user_id> / DEBITCARD#<debit_card_id>)

Find cards expiring this month (for token refresh)

Query(GSI1, DEBITCARDEXP#<MMYYYY>)

Legacy Table: `pinless-default-card`

The original debit card store. Stores one record per user (user_id is the PK). This table is read-only for migrated users — it serves as a fallback for users whose card has not yet been written to the new table, and as a backup for older cards.

Migration Fallback Behaviour

When a card lookup on the new table returns no results, the repository automatically falls back to the legacy table:

GetByUserIDAndStatus finds no records in the new table.
legacyGetByUserID fetches the single record from pinless-default-card.
The legacy record is converted to a DebitCard struct (issuer = UNKNOWN, a new UUID is assigned as debit_card_id).
The converted card is transparently written back to the new table so subsequent lookups no longer need the legacy table.

When a new card is saved and its primary_card_type = ALL, it is also written to the legacy table (overwriting the existing record) so that any callers still reading from the old table remain consistent.

Keys and Indexes

Key / Index Type Description

Key / Index	Type	Description
`user_id`	PK	One record per user.
`GSI1` (PK: `card_exp`)	GSI	Used to find cards expiring soon for token refresh. Queried alongside the new table’s GSI1 — results are de-duplicated by token reference IDs.
`hashed_card_number-index` (PK: `hashed_card_number`)	GSI	Used by fraud checks to detect when multiple users share the same physical card.

user_id

One record per user.

GSI1 (PK: card_exp)

GSI

Used to find cards expiring soon for token refresh. Queried alongside the new table’s GSI1 — results are de-duplicated by token reference IDs.

hashed_card_number-index (PK: hashed_card_number)

GSI

Used by fraud checks to detect when multiple users share the same physical card.

Attributes

Attribute Description

Attribute	Description
`user_id`	Primary key. Owning user.
`masked_card_number`	Last 4 digits of the card number. Display only.
`hashed_card_number`	Hash of the last 4 digits and expiration date. Used for fraud deduplication.
`transaction_reference_id`	Usio credit token.
`debit_transaction_reference_id`	Usio debit token.
`card_number`	Always `"nil"`. Raw card number is never stored.
`card_cvv`	Always `"nil"`. CVV is never stored.
`card_exp`	Card expiration date in `MMYYYY` format.
`created_on`	When the card was added.
`is_valid`	Whether the card is currently valid for payments.
`invalid_date`	When the card was marked invalid.

user_id

Primary key. Owning user.

masked_card_number

Last 4 digits of the card number. Display only.

hashed_card_number

Hash of the last 4 digits and expiration date. Used for fraud deduplication.

transaction_reference_id

Usio credit token.

debit_transaction_reference_id

Usio debit token.

card_number

Always "nil". Raw card number is never stored.

card_cvv

Always "nil". CVV is never stored.

card_exp

Card expiration date in MMYYYY format.

created_on

When the card was added.

is_valid

Whether the card is currently valid for payments.

invalid_date

When the card was marked invalid.

Notes

Token refresh (usio-refresh-scheduler + usio-refresh-worker) queries both tables for expiring cards and de-duplicates results by credit + debit reference ID pair before enqueuing refresh jobs.
The is_valid flag gates pinless collection attempts. Invalid cards cause the collections engine to fall back to ACH.
The legacy table will be retired once all users have been migrated to the new table.

`bank-accounts` and `bank-account-history`

Stores KMS-encrypted bank account and routing numbers sourced from Plaid. The history table is an append-only log of all changes to a user’s bank accounts.

Keys and Indexes

Key / Index Type Description

Key / Index	Type	Description
`user_id` + `account_id`	PK (composite)	A user can have multiple bank accounts; each is identified by its Plaid `account_id`.
`account_routing_hash-index` (PK: `account_routing_hash`)	GSI	Used by fraud checks to detect when multiple users share the same bank account.

user_id + account_id

PK (composite)

A user can have multiple bank accounts; each is identified by its Plaid account_id.

account_routing_hash-index (PK: account_routing_hash)

GSI

Used by fraud checks to detect when multiple users share the same bank account.

Attributes

Attribute Description

Attribute	Description
`user_id`	Owning user.
`account_id`	Plaid account ID. Used as the sort key to distinguish multiple accounts per user.
`account_number`	KMS-encrypted bank account number. Decrypted at ACH payment submission time.
`routing_number`	KMS-encrypted routing number. Decrypted at ACH payment submission time.
`account_routing_hash`	Hash of the account and routing number combination. Stored in plain text for fraud deduplication queries.
`entry_timestamp`	When the bank account record was created or last updated.

user_id

Owning user.

account_id

Plaid account ID. Used as the sort key to distinguish multiple accounts per user.

account_number

KMS-encrypted bank account number. Decrypted at ACH payment submission time.

routing_number

KMS-encrypted routing number. Decrypted at ACH payment submission time.

account_routing_hash

Hash of the account and routing number combination. Stored in plain text for fraud deduplication queries.

entry_timestamp

When the bank account record was created or last updated.

Access Patterns

Operation Pattern

Operation	Pattern
Get bank account for a user and Plaid account	`GetItem(user_id, account_id)`
Fraud check — find users with same account	`Query(account_routing_hash-index, account_routing_hash = ?)`

Get bank account for a user and Plaid account

GetItem(user_id, account_id)

Fraud check — find users with same account

Query(account_routing_hash-index, account_routing_hash = ?)

Notes

Account and routing numbers are encrypted with an AWS KMS key before storage and decrypted only within the Lambda execution environment at payment submission time.
The account_routing_hash is computed before encryption and stored separately so fraud checks can operate without decryption.
When a user updates their bank account, a blocklist-remove SQS event is published to trigger the blocklist-remover Lambda.

`usio-debit-refunds`

Stores refund records for Usio transactions. Refunds can be tied to a specific payment record or submitted as an arbitrary amount not linked to any payment.

Keys and Indexes

Key / Index Type Description

Key / Index	Type	Description
`refund_confirmation_id`	PK	Usio confirmation ID for the refund transaction.
`user_id-index` (PK: `user_id`)	GSI	Retrieves all refunds for a given user.

refund_confirmation_id

Usio confirmation ID for the refund transaction.

user_id-index (PK: user_id)

GSI

Retrieves all refunds for a given user.

Attributes

Attribute Description

Attribute	Description
`refund_confirmation_id`	Primary key. Usio confirmation ID of the refund transaction.
`user_id`	User receiving the refund.
`amount`	Refund amount.
`account_type`	`debit` (pinless) or `ach`.
`payment_id`	Payment record ID this refund is for, if applicable.
`loan_id`	Float ID, if the original payment was for a float.
`subscription_id`	Subscription ID, if the original payment was for a subscription.
`confirmation_id`	If no payment record exists but a Usio transaction was found, this is its confirmation ID.
`description`	Reason for the refund as entered by the MX team.
`support_name`	Name of the support agent who submitted the refund.
`support_email`	Email of the support agent who submitted the refund.
`support_last_ip`	IP address of the support agent at submission time.
`date_refunded`	When the refund was submitted.

refund_confirmation_id

Primary key. Usio confirmation ID of the refund transaction.

user_id

User receiving the refund.

amount

Refund amount.

account_type

debit (pinless) or ach.

payment_id

Payment record ID this refund is for, if applicable.

loan_id

Float ID, if the original payment was for a float.

subscription_id

Subscription ID, if the original payment was for a subscription.

confirmation_id

If no payment record exists but a Usio transaction was found, this is its confirmation ID.

description

Reason for the refund as entered by the MX team.

support_name

Name of the support agent who submitted the refund.

support_email

Email of the support agent who submitted the refund.

support_last_ip

IP address of the support agent at submission time.

date_refunded

When the refund was submitted.

Access Patterns

Operation Pattern

Operation	Pattern
Get refund by confirmation ID	`GetItem(refund_confirmation_id)`
Get all refunds for a user	`Query(user_id-index, user_id = ?)`

Get refund by confirmation ID

GetItem(refund_confirmation_id)

Get all refunds for a user

Query(user_id-index, user_id = ?)

Notes

Refunds are submitted via backoffice/adminDB by the MX (customer experience) team.
An arbitrary refund not tied to a specific payment record is valid — the payment_id and loan_id fields will be empty.

Architecture — DynamoDB overview in the system context
Payment Flow — How payment records are written and updated during the payment lifecycle
Payment Syncing — How ACH status updates are written to the payments table; token refresh flow for legacy and new debit card tables
Blocklist — How return codes trigger blocklisting via Kinesis events
Fraud Detection — How the hashed_card_number / card_hash and account_routing_hash indexes are used for fraud checks

DynamoDB Tables

prod-payments

Keys and Indexes

Attributes

Access Patterns

Notes

Debit Cards: Two-Table Architecture

New Table: prod-payments (single table, DEBITCARD records)

Keys and Indexes

Attributes

Access Patterns

Legacy Table: pinless-default-card

Migration Fallback Behaviour

Keys and Indexes

Attributes

Notes

bank-accounts and bank-account-history

Keys and Indexes

Attributes

Access Patterns

Notes

usio-debit-refunds

Keys and Indexes

Attributes

Access Patterns

Notes

Related Pages

`prod-payments`

New Table: `prod-payments` (single table, `DEBITCARD` records)

Legacy Table: `pinless-default-card`

`bank-accounts` and `bank-account-history`

`usio-debit-refunds`