Transactions

Storage

Table: prod-txn-transactions

Location: us-east-2

Keys

  • PK - TXN#<txn_id>

  • SK - TXN#<txn_id>

  • GSI1PK - ACCOUNTS#<user_id>

  • GSI1SK - DATE<year-month-day>#TXN#<txn_id>

  • GSI2PK - ACCOUNTS#<user_id>#CATEGORY#<category_id>

  • GSI2SK - [REMOVED]DATE#<year-month-day>#TXN#<txn_id>

  • GSI3PK - ACCOUNTS#<user_id>#ACCOUNT#<acct_id>

  • GSI3SK - [REMOVED]DATE#<year-month-day>#TXN#<txn_id>

  • GSI4PK - ACCOUNTS#<user_id>#ACCOUNT#<acct_id>#CATEGORY#<category_id>

  • GSI4SK - [REMOVED]DATE#<year-month-day>#TXN#<txn_id>

Attributes

  • type - TRANSACTION

  • user_id

  • acct_id

  • txn_id

  • txn_account_owner_id

  • txn_pendint_transaction_id

  • txn_pending

  • txn_removed

  • txn_payment_channel

  • txn_payment_meta

  • txn_name

  • txn_original_description

  • txn_merchant_name

  • txn_payment_location

  • txn_authorized_date

  • txn_date

  • txn_category_id

  • txn_category

  • txn_iso_cc - Currency code

  • txn_unofficial_cc

  • txn_amount - In cents

  • txn_amount_string

  • txn_transaction_code

  • txn_personal_finance_category

  • merchant_logo_url

  • merchant_website

Query Patterns

Query Index Conditions

Get transaction by ID

Primary

PK = TXN#txn_id, SK = TXN#txn_id

List transactions by userID

GSI1

GSI1PK = ACCOUNTS#user_id, GSI1SK begins with DATE#

List transactions by categoryID

GSI2

GSI2PK = ACCOUNTS#user_id#CATEGORY#category_id, GSI2SK begins with DATE#

List transactions by accountID

GSI3

GSI3PK = ACCOUNTS#user_id#ACCOUNT#account_id, GSI3SK begins with DATE#

List transactions by accountID and categoryID

GSI4

GSI4PK = ACCOUNTS#user_id#ACCOUNT#account_id#CATEGORY#category_id, GSI4SK begins with DATE#

Removal

Transactions are removed by prepending REMOVED to all GSI SK’s, which excludes it from the list functions where the SK begins with DATE#