Configuring Plaid Transactions in my DB

jackb

We are sourcing Plaid transactions which contain the following logic for negative/positive markers on the 'amount' field (from Plaid docs):

The settled value of the transaction, denominated in the transactions's currency, as stated in iso_currency_code or unofficial_currency_code. Positive values when money moves out of the account; negative values when money moves in. For example, debit card purchases are positive; credit card payments, direct deposits, and refunds are negative.

Part of this logic is giving us trouble, which is the fact that when money moves into a Checking account, it is represented as a negative amount. 

A main use case of our app is to sum up transaction values to provide an aggregated view of revenue/spend in a given month (like a personal finance app). 

We would also potentially ideally show these as "Spent" and "Received" columns on the front-end, as Quickbooks does, rather than just one column (w/ sometimes unintuitive negative and positive markers) in a Transactions table. 

So the questions are: 

• How can I best convert the values Plaid gives us into what we need in the DB? Is there any way to do this that's more efficient than a For loop on each transaction that looks at certain criteria and then makes an update to the DB? E.g., Loop through transactions and where amt = a negative amount AND account type = Checking, update another field for this item with a value that makes more sense.
• Should this happen as soon as transactions are fetched from the Plaid API (we're planning to do this in a batch process), or when we Query All Records? Any way particularly better for performance?

Thanks for any guidance you can provide, understand this is a bit open-ended.

sepirak-nick

The way we've been storing these is an always-positive value, then a column "type" with an enum value "Debit" or "Credit". When we display it we can choose how it gets displayed, so in your case you could display it in one column or the other depending on the type.

I would do that change before inserting into your DB when retrieving data from Plaid.

Alternatively, if you're doing a lot of aggregating or have many transactions, just use positive/negative numbers for credit/debit, since you can then just use the the "aggregate" features to sum that amount column when you're querying it.

Also since it looks like Plaid does the reverse of what's intuitive for credit/debit, you could just reverse it before inserting into your DB.