Preventing duplicates when fetching transactions

Question

I'm trying to fetch transactions using the transactions/get endpoint with Plaid, but I'm running into an issue, at least with Sandbox data, where the transaction_id always comes back differently for the same call. This is less of a code problem and more of a modeling problem.

The flow on my app is you click an "Add sources" button and then you go through the Plaid Link flow. Right now the app just allows you to go through this flow, stores the access token on the server, and then tells another endpoint to "sync" my data with Plaid by pulling all available transactions and putting them in my database. The problem is I am currently just looking at the transaction_id as the primary key, and when I don't see it in my database, I add a new entry--but since that field is different with every link, I end up adding the same data to the database over and over again.

I could theoretically just do a new Plaid API call every time I need to get the account's transaction data, but that would be expensive (both from a performance and monetary cost perspective) and I would not be able to store other metadata with the transaction, such as a user-defined category.

The ideal solution would be 1) some way to tell when a new account has been added for the first time vs is re-linked, then only fetching data from that new account and removing all data in the case of a re-link. (Or maybe there's a way to prevent the user from linking the same institution twice via something provided via the Plaid Link parameters). And 2) Some way to incrementally sync new data and not the old data again.

Answer 1

"just do a new Plaid API call every time I need to get the account's transaction data" is exactly the intended model for using the Plaid Transactions API. With the way you are doing it, not only does the user have to log on to their bank every time they want to update data in your app, but it's also resulting in a new monthly subscription-billed Item being created every time the user links an account, so if the user wants to update their account in your app more than once a month, it's actually costing you more money to do it this way than to use the API as designed. There shouldn't be any reason that calling Transactions in this way would prevent you from storing user-created categories with transactions.

If you want to incrementally fetch new data, you can use the /transactions/sync endpoint. You should do this instead of /transactions/get anyway, since /transactions/sync is the newer endpoint and much easier to work with, but it won't fix the inherent problem in how you're using the Transactions API in the first place.