DynamoDB Tables

Storage spans two regions (Infrastructure): dynamo_legacy_region holds the payments table and the other un-prefixed tables; the primary region holds the site-payments single table, site-usio-tokenization-transactions, and site-float-service-log. Clients resolve DYNAMO_LEGACY_REGION for those tables and DYNAMO_REGION + DYNAMO_TABLE for the single table.

payments

One item per payment: every pinless, RTP, and ACH submission that returned a confirmation ID, plus prenotes. Written by the API at submission and updated in place by the syncing mechanisms (Payment Syncing). The table’s DynamoDB stream feeds kinesis-feeder (Events).

Key / Index Type Use

confirmation_id

PK

Usio confirmation ID or JPM endToEndId

user_id-index

GSI (user_id)

GET /{user_id}/payments

payment_status-index

GSI (payment_status)

check-ach-scheduler’s ACHSENT query
Attribute Description

user_id

Owning user

payment_method

ach, pinless, or rtp

payment_type

credit (to the user) or debit (from the user)

payment_provider

jpm or usio

payment_status

See Payment statuses

amount

Dollar amount; 0 for prenotes

loan_id, subscription_id, prorated_subscription_id, installment_loan_id

Exactly one identifies the payment class (float, subscription, prorated subscription, loan); prenotes carry none

source_id

Bank account ID or debit card ID the payment was drawn on

masked_account_number

Official account mask for ACH payments — Account Mask

masked_card_number

Last 4 digits for pinless payments

return_code, return_info

Processor return or decline code and text; set on FAILED payments

available_funds_cents

Card balance in cents reported by Usio on a code-51 partial-auth decline; absent otherwise

event_type

Kinesis event type of the last status change; empty at submission — Events

db_type

PAYMENTS

submit_date, return_date, cleared_date, completion_date

Lifecycle timestamps; zero time (0001-01-01) when unset

early_advance_float_id, early_advance_day

Float taken as an early advance against this ACHSENT payment and the business day it was taken; recorded by Float Service via PATCH /payments/{confirmation_id}, which defaults the day when the request omits it — Early advance

early_advance_eligible_at

First time underwriting made the user eligible for an early advance against this payment; set by underwriting-api via the same PATCH

Source: pkg/dynamo/payments.go

Payment statuses

payment_status Meaning Set by

ACHSENT

ACH accepted by the processor; settlement pending

API on ACH submission and prenotes

COMPLETED

Settled

API on pinless and RTP success; check-ach-worker

FAILED

Declined, rejected, or returned; return_code set

API on pinless and RTP decline; jpm-webhook-processor; usio-syncer (returns)

CHARGED_BACK

Chargeback reported by Usio

usio-syncer

REFUNDED

Refunded through POST /refunds

API

Source: pkg/dynamo/payments.go, pkg/api/refunds.go. Transitions are on Payment Flow.

site-payments (single table)

Keys PK / SK with overloaded indexes GSI1, GSI2, GSI3 (each <name>PK / <name>SK). Item types are distinguished by key prefix and db_type.

Item PK SK Indexes

Debit card

USER#<user_id>

DEBITCARD#<created_on>

GSI1 DEBITCARDEXP#<MMYYYY> / USER#<user_id>#DEBITCARD#<debit_card_id>; GSI2 USIOCARD#<usio_card_id> / same (sparse); GSI3 USER#<user_id> / DEBITCARD#<debit_card_id>

Bank account

BANKINFO#<user_id>

ACCT#<account_id>

GSI1 HASH#<account_routing_hash> / BANKINFO#<user_id>#ACCT#<account_id>

Card attempt

CARDATTEMPT#<card_key>#<class>

<attempted_on>#<confirmation_id>

GSI1 USER#<user_id> / CARDATTEMPT#<attempted_on>#<confirmation_id> (keys only)

Blocklist

BLOCKLIST#<user_id>

CURRENT or DATE#<timestamp>

—

RTP eligibility

RTP#<routing_number>

RTP#<routing_number>

—

Usio ACH risk report

USIOACHRISK#<merchant_account_id>

REPORT_DATE#<YYYY-MM-DD>

—

Marketing lock

USER#<user_id>

MARKETING_FT_DEBIT_LINK_LOCK or MARKETING_FT_FLOAT_ACCEPTED_LOCK

—

Refund attempt

REFUND#<confirmation_id>

REFUND

—

Usio breadcrumb

USIOBREADCRUMB#<natural_id_kind>#<natural_id>

USIOBREADCRUMB

—

Refresh crumb

REFRESHCRUMB

USER#<user_id>#TOKEN#<credit_reference_id>

— (token-refresh state with a TTL — Token Refresh)

Debit card (DEBITCARD#)

One item per card; a user may hold several. A card with primary_card_type = ALL is mirrored to pinless-default-card. Flows: Debit Cards.

Attribute Description

user_id, debit_card_id

Owner; FloatMe card ID

masked_card_number

Last 4 digits

card_hash

Hash of last 4 + expiration; fraud deduplication

credit_reference_id, debit_reference_id

Usio credit and debit tokens

expiration_date

MMYYYY

created_on

RFC3339

issuer

See Card enums

is_valid, invalid_date

Validity; cleared by the return-code engine, restored by re-adding the card

status

See Card enums

primary_card_type

See Card enums

linked_bank_account_id

Bank account linked to the card, if any

usio_card_id

Usio PaymentAccountID for the physical card; fill-only, never overwritten

suspended_on

RFC3339; any record carrying it blocks re-adding the physical card — Suspended cards

Access: cards by user and status (PK + begins_with DEBITCARD, optional status filter); by debit_card_id (GSI3); expiring this month (GSI1 DEBITCARDEXP#<MMYYYY>); every record for a physical card (GSI2). A user with no items here is read from pinless-default-card and the converted card is written back.

Card enums

Field Values

status

ACTIVE, REMOVED, STALE (token refresh failed)

primary_card_type

ALL, FLOATS, SUBS, NONE

issuer

MASTERCARD, VISA, AMEX, DISCOVER, JCB, DINERS, UNKNOWN (cards converted from pinless-default-card)

Source: pkg/dynamo/debit_card.go

Bank account (BANKINFO#)

One item per user and Plaid account_id — Bank Accounts.

Attribute Description

user_id, account_id

Owner; Plaid account ID

account_number, routing_number

KMS-encrypted

account_routing_hash

Salted SHA-256 of account + routing; plain text for the GSI1 hash lookup

mask

Official account mask; absent on records written before the attribute existed until backfilled

entry_timestamp

Created or last updated

Access: by user and account (PK/SK); by hash (GSI1) for fraud checks.

Source: pkg/bankaccount/store/record.go

Card attempt (CARDATTEMPT#)

One item per Usio network response to a pinless debit — the retry-limit ledger. card_key is usio_card_id, else the response’s PaymentAccountID, else LEGACY_<user_id>_<mask>_<expiration>; class is float, sub, or loan.

Attribute Description

user_id, card_key, class

Owner and tracking-key components

amount_cents

Exact attempt amount

status

approved or declined

return_code

Cleaned Usio decline code; absent on approvals

source_id

Float, subscription, prorated-subscription, or loan ID

confirmation_id, attempted_on

Usio confirmation; submission time

ttl

attempted_on + 31 days

Access: rolling window per card and class (PK, SK ≥ window start, ttl filter); every attempt for a user (GSI1, hydrated with BatchGetItem).

Source: pkg/dynamo/card_attempt.go

Blocklist (BLOCKLIST#)

db_type = BLOCKLIST. Attributes user_id, status, event_type, trigger_id, date_updated; semantics on Blocklist. Access: current status (SK CURRENT); history (SK begins_with DATE#).

Source: pkg/dynamo/blocklist/blocklist.go

RTP eligibility (RTP#)

Cache of Usio RTP eligibility per routing number: routing_number, rtp_eligible, ttl (30 days). Read and written by GET /{user_id}/payment-methods — RTP.

Source: pkg/dynamo/rtp.go

Usio ACH risk report (USIOACHRISK#)

db_type = USIO_ACH_RISK; one item per merchant account per Chicago calendar day, written by usio-syncer — ACH risk report. No TTL, no event_type.

Attribute Description

merchant_account_id, report_date, retrieved_at

MID; Chicago date of the fetch; UTC fetch time

total_returns, total_admin_returns, total_unauth_returns

Report header counts (unauth = Usio "bad returns")

return_date_*

returns, admin_returns, unauth_returns, return_rate, admin_return_rate, unauth_return_rate from the "By Return Date" section

submit_date_*

The same six fields from the "By Submit Date" section

Section attributes are absent when the section was not matched; a stored zero is a real zero.

Source: pkg/dynamo/usio_ach_risk.go

Marketing lock

PK = USER#<user_id>, one item per single-fire marketing event, written with a conditional put so the event fires once per user lifecycle:

Event SK

first_debit_card_connected

MARKETING_FT_DEBIT_LINK_LOCK — First-debit event

first_float_accepted

MARKETING_FT_FLOAT_ACCEPTED_LOCK — sent by float-service

PUT /admin/{user_id}/marketing-lock acquires a lock (409 when held) and DELETE releases it (succeeds when not held). The event query parameter picks the lock, defaults to first_debit_card_connected, and an unknown value is a 400. user-service acquires the first-debit lock on prequalify activation and reactivation and releases both on account closure; float-service acquires the first-float lock. Each call records payments.marketing_lock tagged event and outcome (acquired, conflict, released, error).

Source: pkg/dynamo/marketing_lock.go, pkg/api/marketing_lock.go

Refund attempt

db_type = REFUND_ATTEMPT; one item per confirmation ID refunded through POST /refunds, the idempotency record for that refund — Refund idempotency. No TTL.

Attribute Description

confirmation_id, user_id

Payment or staged Usio transaction being refunded; recipient

status

PENDING from the conditional put that reserves the refund until the provider outcome is recorded; COMPLETED once the provider confirmed the refund

refund_confirmation_id

Provider’s refund confirmation, set on COMPLETED

date_created, date_completed

Reservation time; completion time

Source: pkg/dynamo/refund_attempt.go

Usio breadcrumb (USIOBREADCRUMB#)

One item per float or subscription whose last Usio debit submit returned an error; a new failed submit replaces it — Safe retry. Deleted when the search answers landed or not landed. No event_type.

Attribute Description

natural_id_kind, natural_id

float or subscription; the float_id or subscription_id

user_id

Owner

usio_domain

usio.Auth* credential of the failed submit; the search uses it, not the retry’s credential

account_code_3

Random value sent in AccountCode3; the search filters on it

payment_method, source_id, mask, amount

Payment record data if the charge landed. source_id is the debit card ID (pinless) or bank account ID (ACH); mask is the card mask or bank account display mask

submitted_at

Time before the failed submit; the search starts 1 minute earlier

ttl

submitted_at + 7 days (float collections skip weekends, so the next attempt can be 3+ days later)

Source: pkg/dynamo/usio_breadcrumb.go

pinless-default-card

One item per user (user_id PK): the debit card mirror read when a user has no DEBITCARD# items and written whenever a card with primary_card_type = ALL is saved. Indexes: GSI1 on card_exp (expiring cards for token refresh) and hashed_card_number-index (fraud).

Attribute Description

masked_card_number, hashed_card_number

Last 4; hash of last 4 + expiration

transaction_reference_id, debit_transaction_reference_id

Usio credit and debit tokens

card_number, card_cvv

Always "nil"

card_exp

MMYYYY

created_on, is_valid, invalid_date, status, bank_account_link, is_notified

Card state

Source: pkg/dynamo/debit_card.go (LegacyDebitCard)

pinless-changes

Debit card change history, user_id PK with one row per change_date. Each row is a snapshot of the card after the change: credit_reference_id, debit_reference_id, created_on, is_valid, invalid_date, is_override, masked_card_number, and, on rows written since the attributes existed, suspended_on, usio_card_id, debit_card_id, status. When rows are written: Change history.

Source: pkg/dynamo/debit_card_changes.go

usio-debit-refunds

One item per refund (refund_confirmation_id PK). Indexes: user_id-index; confirmation_id-date_refunded-index. Written by POST /refunds and by the usio-syncer’s tokenization auto-refund, which also writes an item when its refund fails, with a generated refund_confirmation_id and no Usio refund behind it.

Attribute Description

user_id, amount, account_type

Recipient; amount; debit (pinless) or ach

payment_id, confirmation_id

Payment record refunded; Usio transaction refunded when no payment record exists

loan_id, subscription_id

Class of the original payment

description, support_name, support_email, support_last_ip

Refund reason and the support agent who submitted it

date_refunded

Submission time

outcome

ISSUED when money was sent, FAILED for the syncer’s failed auto-refund. Absent on items written before the field existed; of those, the failed auto-refunds are the ones with support_name = refunds_syncer and a description starting "System failed to refund"

Source: pkg/dynamo/refunds.go

Usio staging tables

usio-debit-transactions and usio-credit-transactions (dynamo_legacy_region) and site-usio-tokenization-transactions (primary region) hold raw Usio transactions as returned by GetTransactionList, keyed on Confirmation with an EmailAddress-Index GSI, plus MerchantAccountID. Written by usio-syncer (Usio Sync); read by GET /{user_id}/usio/transactions, the AVS check, and refunds.

Source: pkg/dynamo/usio.go

jpm-transaction-details

Raw JPM transaction detail records written daily by jpm-syncer — JPM Transaction Sync. Nothing in this service reads them.

Source: pkg/dynamo/jpm.go

site-float-service-log

Fraud log: PK = <key>#<value>, SK = <RFC3339 timestamp>#<type>, with key in USER, INSTALL_ID, ACCOUNT_HASH, CARD_HASH and type in REQUEST, SUCCESS, FAILED. Queried by PK with SK ≥ now − 24h — Fraud Detection.

Source: pkg/dynamo/fraud_log.go