DynamoDB

payments - us-east-1

Payment

A payment record. Can be to either jpm or usio, credit or debit, pinless or ach. Pinless debit card payments are saved and do not get updated. ACH payments go into an ACHSENT status upon being submitted, and returned/completed later.

Indexes

  • Primary - confirmation_id

  • GSI - user_id-index - used to retrieve payments by user_id

  • GSI - payment_status-index - used to get payments with a specific status, mostly for ACH clearing system

Keys

  • confirmation_id

Attributes

  • payment_method - ach or pinless

  • payment_type - credit or debit

  • payment_provider - jpm or usio

  • loan_id - if the payment was for a loan, this is the loan_id. If not it will be null

  • subscription_id - if the payment was for a subscription, this will be the subscription_id. If not it will be null

  • cbc_card_id

  • return_code - NACHA return code (R01, etc) or jpm return code that maps to a NACHA return code (AM04, etc)

  • return_info - NACHA return code with description if jpm, RETURNED if usio

  • user_id

  • masked_account_number - the last 4 of the bank account that the payment is going to. Should only exist if the payment is ach

  • masked_card_number - the last 4 of the card that the payment is going to. Should only exist if the payment is pinless

  • payment_status - COMPLETED, ACHSENT, FAILED

  • amount

  • event_type

  • db_type

  • submit_date

  • return_date - 0001-01-01 if not returned

  • cleared_date - 0001-01-01 if not cleared

  • completion_date - The date the payment was successful if pinless, or if three business days passed after an ach payment was submitted without it getting returned. 0001-01-01 if not completed

pinless-default-card - us-east-1

Debit Card

A users debit card entry. A user can only have one debit card at a time.

Indexes

  • Primary - user_id

  • GSI - GSI1 - card_exp and GSI1SK - used by the debit card updater

  • GSI - hashed_card_number-index - used for fraud detection

Keys

  • user_id

Attributes

  • masked_card_number - last 4 of card

  • hashed_card_number - hashed last 4 of card and expiration date

  • transaction_reference_id - the usio credit token for making tokenized credits

  • debit_transaction_reference_id - the usio debit token for making tokenized debits

  • card_number - should always be nil

  • card_cvv - should always be nil

  • card_exp - expiration date of card in MMYYYY format

  • created_on

  • is_valid - If the card is considered "valid". Certain usio return codes(not in(51, 57, 82)) changes this to true. User can reset by re-adding card.

  • invalid_date

bank-accounts and bank-account-history - us-east-1

Bank Account

A record containing the users encrypted account and routing numbers.

Indexes

  • Primary - user_id and account_id

  • GSI - account_routing_hash-index - used for fraud checking

Keys

  • user_id

  • account_id - the plaid account_id that the account is for

Attributes

  • account_number - encrypted using kms key

  • routing_number - encrypted using kms key

  • account_routing_hash - hashed account and routing number combination

  • entry_timestamp

usio-debit-refunds - us-east-1

Refund

MX can submit refunds in backoffice/adminDB. Can be for a specific payment or an arbitrary amount that is not tied to a specific payment, i.e for multiple payments.

Indexes

  • Primary - refund_confirmation_id

  • GSI - user_id-index

Keys

  • refund_confirmation_id - the usio confirmation id of the refund transaction

Attributes

  • support_name

  • support_email

  • support_last_ip

  • description - mx described reason for refund

  • amount

  • payment_id - if the refund was for a specific payment record, this will map to it

  • loan_id - if the payment was a loan and had a loan_id this will be that id

  • subscription_id - if the payment was a subscription and had a subscription_id, this will be that id

  • confirmation_id - If no payment record was found, but a usio transaction was found, this will be the confirmation_id of that usio transactions

  • account_type - debit or ach

  • user_id

  • date_refunded