So now we're gonna go ahead and ensure that every time this connected bank thing happens we also populate all the transactions, create new accounts and create new categories. So we are going to do that using the Plaid API. We already have the configuration set up here so we can just call this client to obtain transactions, accounts and categories. But we could not have done that until now because only now we have the access token which is required in all of those API routes. So which API routes am I talking about?
Well this one's right here in their API documentation. So we're going to use the transactions sync endpoint for transactions. So there are many ways you can, many things you can explore here. If you want to do it using transactions, get, you can use that. So I'm talking about later, right?
When you, I don't know, want to do this on your own or are looking into extending this functionality. There are many ways you can do this. So with transactions sync, what we're going to receive back is first the list of accounts from where the transactions were fetched. And then we're going to get an object. Let me zoom in on this.
So then you're gonna go, perhaps when I zoom in, you can't see. Yeah. So then we're going to get an array called added. And inside of added, we're going to have all the transactions which were added inside of our, well, inside of our bank account. Then we're gonna have modified in case something was modified, right?
Turned into declined or something like that. And then we're gonna have removed, Right? So that's what the synchronize endpoint is for. So to simplify this, we're just going to focus on the added option. So let's go ahead and see how we can do that.
So first things first, after we connect to our bank account, we're going to go ahead and we're going to write const blade transactions and that's going to be await client dot transactions sync like this and inside of here we are going to pass the access token to the connectedBank.accessToken then we're going to do the same thing but for accounts So await client accounts get and as seen the connected bank access token like this. And lastly we're going to fetch the categories. So the thing with categories is that apparently they are being deprecated, but I'm not entirely sure, right? So if you want to, you can do this. So in the future, if this method doesn't exist, simply skip the categories, right?
But if the method exists, if you don't get an error here, then that's completely fine. Great! So now we have these three and what we are going to do now is we're finally going to realize why we use the played ID option for accounts and for categories here and I think we also have it in transactions or perhaps we don't have it in transactions so let's go ahead and let's add this actually no we don't need to add it in transactions yeah all right let's just go ahead back here first things first let's create new accounts So const new accounts are going to be await database.insertIntoAccounts, so import accounts from database schema here. So into accounts we're going to insert values accounts.data.accounts.map, get the individual account and the return an immediate object with ID, create ID, name account.name, blade ID, account.accountID, user ID, auth.userID. So we're gonna create a bunch of new accounts for this user, right?
And we have the create ID from cuid, great. So that's new accounts. So let's copy this and let's now create new categories. So new categories, insert into categories from database schema. And inside of here, we're gonna use played categories.
We are going to get an individual category. And now we're gonna go ahead and give this a name of category.hierarchy.join like this because the hierarchy looks like an array of strings for example it's gonna be bank finance so like three words in an array. Something like, I don't know, food, travel, expenses, right? So to simplify it, we're going to turn that into a single string. Then we're gonna have to create an external ID.
So category, category ID, and our user ID stays the same here. And now we have to create new transactions here. And we also have to map them to the accounts and to these categories. So let's create const newTransactionsValues playedTransactions.data and I'm only gonna work on the added field. So remember, inside of here, you also have modified and removed, so you can use that how you like it, right?
So for this, I'm going to call reduce, get the accumulator and the transaction. Go ahead and open a method. And let's go ahead and give this a default of an array and let's write as type of transactions. So transactions comes from database schema here. Let me just go ahead and extend this like that so a type of transactions and we're gonna use infer insert and an array so let's go ahead and write const account and we're first going to attempt to find an account so we're going to use new accounts.find account account played ID let me just see so instead of new accounts, oh we also have to add returning to each of these so after we create the accounts make sure you add .returning here and the same thing for the new categories like this, ok so now we can do new accounts.find and inside of here we are attempting to find a matching plate ID with the current transaction dot account ID.
So that's how we know that. So if this new Played transaction has the same account ID as what we have stored in the Played ID, which as you can see here is the same thing account ID but this time coming from a different API endpoint played accounts right so it matches that means that we have successfully created and found that new account so we can assign it to this transaction. And now let's do the same thing so I can copy and paste this, the whole thing, right? So this will now be the matching category. So for that we're going to do new categories.
Find the individual category. So this will be category played ID. And we're going to compare it not to account ID but to category ID. And this is what I'm talking about. So as you can see, I'm gonna zoom out.
So in transaction category ID here, you can see I have a message that category ID is deprecated, right? So in favor of the new personal finance category. But unfortunately I didn't find a way to synchronize. They don't have an endpoint for personal finance category. They only have a CSV file, which just is not something that we can kind of handle at the moment, right?
At least not how I imagined it, but I can still use this. So even though it's deprecated, it's still available, right? So if you have the auto-complete for this and you can hover over it, that probably means you can still do this thing. But in case you can't, don't worry, because remember category is not required. This just means that your Play the Transactions are gonna be uncategorized.
So yeah, not ideal, but you know, you can explore your own personal finance category solution for that. But for now you can go ahead and follow like this. Great and now what I want to do is I want to define the amount in milli units. Because Blade will return the amount in a decimal right and it will accept up to two up to three decimals so let's do convert amount not from a milli units but convert amount to milli units and pass in transaction.amount. There we go.
So inside of here let's see if we can find something. So the settled value of the transaction, denominated in transactions currency, ISO currency code, positive values when money moves out of the account and negative values when money moves in. All right, but I'm trying to see if there's any information here about how many decimals. So I think you can find that here, the amount field. All right, perhaps not, but yes, the amount basically looks like this, 72.1, right?
So either one decimal, two decimal, or at max three decimals. So that's fine for us because we use milli units which means we support up to three decimals here there we go so in order to actually push this to the database we need to have found an account. So what we're gonna do is if we have an account and only if we have an account to the accumulator we are going to push ID create ID amount amount in milli units Payee is going to be either transaction.merchant name or transaction name because this can be optional right So this is kind of a more specific name, but if it doesn't have that, we're just gonna use the transaction name. And now for the notes, we can pass the transaction.name. Date will be new date, transaction.date.
And for the account ID, we're gonna pass in account.id and for the category ID, we're gonna pass in category?id, right? Because category is not required to exist. So even if we don't find the category, that's completely fine. So don't worry if this is not working out for you, right? You can just leave it as null, I believe.
There's still no errors here, right? Great. So we have that, But if we don't, we return the existing accumulator in this reduce method. And now we are going to check if new transactions values dot length is larger than zero. So if we at least have one transaction here, let's go ahead and do the following.
Await database.insertTransactionsValues newTransactionsValues here. There we go. And let's go ahead and do this let's return ok true here because there's no point in returning back the access token. In fact, we kind of need to keep it secure in our database. So I'm gonna go ahead and do the following.
I'm gonna do bun run database studio here, Or do I already have it running? I think I don't. Let's go ahead and open this. And let's remove this bank account here. Right, we don't need it.
And let's try this now again. So now as you can see, I should only have... So I have checkings and savings here. And my transactions... Well, I have a good amount of them, right?
But let's go ahead and see what happens when I go into settings here and use the connect. So now what should happen is a bunch of new transactions and accounts should appear. So I'm gonna go ahead and I'm going to select all of these accounts. I want to create as many changes so it's visible. Let's go ahead and accept the information.
And let's go ahead and click continue and let's see if this will work. So you can see it's working. Now it's taking longer. And there we go. Public token is exchanged.
Let's go inside of accounts and let's go ahead and let's refresh this. And there we go. Play the checking, play the saving, play CD, credit card, money market. Let's go inside of transactions here. And there we go.
You can see we even have some new categories here. Perfect. And you can now filter for example, by play the checking and there we go. Travel, taxi and Uber, McDonald's, Starbucks, everything is here. And take a look at all the new categories which we now have.
660 rows of new categories have been imported and our overview should now also look quite different. There we go. So much more things happening here now. Great! And we even have this new category here.
Perfect! So what I want to do now is show the user that they have their bank connected and give them the ability to disconnect as well as remove all the important stuff. So let's go back inside of our played API endpoint here. So I'm going to keep all of those things here and let's add a get here slash connected dash bank add the clerk middleware and add the asynchronous controller here. So let's go ahead and copy this logic for the unauthorized handling.
And then we're going to get the connected bank using await database select everything from connected banks where and add the equals from drizzle or m so let me just move that here there we go so equals is going to compare the connected bank connected banks dot user id with the current out dot user id So always make sure that you're using the account banks right in this equivalence fields. There we go. So that should be it for our query. So very simple query because we have the user ID right here. No need for any joins or anything like that.
If there is no data, we're gonna go ahead and we're gonna manually return c.json data null. Because by default, this can then be undefined, right? So we have to change this to null because React query doesn't work well when things returned back are undefined. So let's go ahead and return null. Or we can do this.
Connected bank or no, like this. So I don't want to return undefined. I want to return no, that's what I'm doing this. There we go. So now I think we can already try this out.
So if I go ahead inside of my API, played, connected-bank, I should be greeted with my bank right here, access token and the user ID. Perfect. So now let's go ahead and let's create a hook for that. So I'm going to go inside of features, play, API and let's go and copy from the categories use get category for example. Let's paste that here and let's call it use get connected bank.
Let's rename this to use get connected bank as well and it's not gonna accept any id because it works on the user id so no need for enabled and no need for this and the query key will be connected-bank and now we're going to change this to be api played connected bank and there will be no params here like this and this will be failed to fetch connected bank and data can stay the same. There we go. So now that we have this, we can revisit our blade connect, I believe. My apologies, we can revisit our settings card. That's the one.
So let's go inside of settings card. So in here we pretended to have the constant connected bank and now we're actually going to add it. So let's do the following. Const use connected bank, use get connected bank from features played. So let me move this alongside my features and inside of here we're going to getData connectedBank and isLoading isLoadingConnectedBank like this So now we actually have this value right here and there we go.
Bank account connected right here because it can load it right here as we tested in our API before. So just confirm you have the correct if clauses here. Perfect! Obviously we're going to use this but later and now what I want to do is I want to create an alternative button here. So let's do it like this.
If we have the connected bank, we are going to render played disconnect. Otherwise we are going to render our existing played connect. So now we have to create the played disconnect but just before we can do that we have to create our delete API endpoint. So let's revisit the played route. So we just finished the .get and now let's go ahead and let's copy and paste this and let's make this one delete connected bank.
So the authentication works in the exact same way. What's going to change here is that we are not going to be selecting. Instead, we're going to be using delete connected banks, so no from here. And we're also going to add returning here. And I think we can just do id connected banks.id here so let's just return the id of the deleted bank and then I want us to do the next thing.
I want us to do await database dot delete accounts where and from drizzle or m so make sure you've added and and let's also add is not null whoops okay so and equals accounts dot user ID and out user ID so only inside of this users accounts delete everything which is not null when it comes to accounts played ID. So we are going to remove everything we imported using play and because of our schema here If you remember in transactions when we defined account ID we added a rule that if the account is deleted we also delete the transactions assigned to it because transactions cannot exist without an account whereas with category they can so we don't care We just set them to uncategorized if the category gets deleted. So that's it. In here, we take care of deleting the accounts and the transactions. And the last thing we have to do is remove the categories here.
So we're gonna go ahead and write categories user id and categories user played and we can return back the data connected bank or null. Actually, connected bank should always be here. So we can handle this like this. If there is no connected bank, which we apparently tried to remove, we can throw back an error, right? Sorry, not this, but return C.json error, not found with A404.
Remember, it's always important to add the status codes here there we go So we now have the delete endpoint which means that we can now create this plate disconnect and for that we're going to need to create a mutation use delete connected bank. So let's just do that first. So go inside of features, plate API. We can copy for example use create link token and rename it to use delete connected bank. Let's rename the method use delete connected bank change the response type here to be connected bank and it will use the delete method and the response type is the 200 one, right?
Returning the deleted data with just its ID. So inside of here, we're gonna use the connected bank here. The error will be failed to delete connected bank. On success, it will be connected bank deleted and we can copy and paste this. And also this will not be post but delete.
There we go. And now inside of here what I want to do is what I left a comment to do here. Right? So the same thing is needed here. So let's do it immediately.
So let's just copy the use query client from here. And let's add the const query client here in the use deleted connection. Connected bank. There we go. And now inside of here, let's do all of these things.
So query client, invalidate queries, query key. This will be connected bank. Then we have summary. Then we have transactions. Then we have accounts.
And lastly, we have the categories. So we can now remove this to do here, and we can copy all of these things and add them to useExchangePublicToken remember we have an unused query client here so we can go ahead and add that here there we go, so now we won't have to refresh remember when I connected my bank I had to refresh. So that's it for our Use Delete Connected Bank. Great! Now, let's go ahead inside of our settings card back here.
Let's find the Played Connect. Let's copy and paste it here and let's call it plate disconnect. Let's rename the constant plate disconnect here. Go back inside of the settings card and you should now be able to import this and get rid of the error. So I'm just gonna move this here.
There we go. So no errors now, but we still have the exact same connect button here, which is because we copied this. So make sure you are inside of played disconnect. This one, right? First things first, we are not going to need this at all.
We are not going to need anything in the use mount. We are not going to need this at all we are not going to need anything in the use mount we are not going to need played and we are not going to need this and we are not going to need this and we are not going to need this at all so now we're gonna go ahead and remove these two and instead let's import use delete connected bank from features blade API remove all these imports so we're gonna call this disconnect use delete connected bank or let's be more specific so delete connected bank and then inside of delete connected bank we're gonna go ahead and call the mutation here and it's going to be disabled if it is pending. But I don't want it to just work like that and let's also add this connect here. So I want to introduce an old friend of ours called useConfirm. Remember that hook?
So now what we can do is we can pass in are you sure and the description, this will disconnect your bank account and remove all associated data, including, you know, of course, categories, transactions, everything we define. So, dialogue and confirm. Let's go ahead and wrap this entire thing in a fragment. Let's add the dialog. And before we mutate, let's turn this into an asynchronous method.
Const OK, await, confirm. And only if OK, go ahead and mutate. Let's try this out now. So I have the option to disconnect. If I click this, there we go.
If I click cancel, nothing happens, right? So let's go ahead and check. In my accounts, I have all displayed checkings and I have all of this big income here. So let's go inside of settings and let's disconnect right here. Let's click confirm and there we go.
Connected bank deleted. Let's try out our accounts again and we only have two transactions, categories, overview. There we go. The category is now gone for the travel. Perfect.
So you just finished importing the transactions from your bank account. So if you want to explore this even further, Blade offers webhooks, and you can look for synchronized updates available. Right? So you would get in intervals, the exact same data as you would if you would manually fetch transactions sync. So if you want to do that to keep your users data up to date you can explore the webhooks a bit yourself.
If you know a lot of people want this we can create additional chapters on that. And now I want to create a proper loading state for the settings here. So let's go inside of settings. So app folder dashboard settings page.vsx Actually let's go inside of the settings card here. And I'm gonna go ahead and import the skeleton from components UI skeleton here.
And let's just go ahead and copy these card elements here. So just the top ones. And what we're gonna do here is write if is loading connected bank. We're going to return this instead, right? So let's just add an closing tag for the card content and for the card so we don't get any errors.
And now inside, instead of the text settings, we're gonna add a skeleton which is a self-closing tag and a class name height of 6 and a width of 24 like this and inside of here we're going to add a div element with a class name height 350 pixels full width flex and let's do items center justify center and let's add a loader to from lucid react So let me just move it here to the top. And let's style the loader2 by giving it a class name Size6, text slate 300 and animate spin. There we go! So now if you go ahead and refresh this you have a nice little loader here. There we go And let's do this one more time to confirm that this is working.
So we'll sign in. I'm gonna select three of them this time. Let's continue. Let's connect. And until we click continue, nothing is happening, but now you can see the button is disabled, meaning something is happening.
And there we go. Public token exchanged, bank account connected, and I don't have to refresh. They are added here immediately. And also all the new categories, a bunch of new transactions here and the overview has now changed and there we go we have a new category even here in the most used categories. Amazing, amazing job.
What we have to do next is we have to create the, we have to turn this into a software as a service by adding Lemon Squeezy and subscriptions as well as paywalls.