Creating a Banking API, Part 1
In this series, I’ll be developing a (lightweight) banking API. Note that if you want to develop your own API, definitely check out this site: https://www.codementor.io/sagaragarwal94/building-a-basic-restful-api-in-python-58k02xsiq. Much of the code that I write for this project will be based on what Sagar wrote, so a big shout out to him for the helpful guide!
Background
As new technology startups and major corporations alike begin to explore solutions in financial services (“FinTech”), Banking-as-a-Service has emerged as a new service paradigm to serve as the liaison between the banking skeleton and the platforms atop that infrastructure. BaaS companies can offer a wide variety of services, from providing APIs to allow developers building apps to connect directly to the backend to processing data (such as payments and billing) to regulatory services (AML/KYC).
Some of the prominent companies in this space include Plaid, whose API allows FinTechs to connect to user’s bank accounts from thousands of different banks nationwide; and Capital One, who offers services like DevExchange and Nessie that allow developers to build applications on top of their banking platform.
Fearing that I might miss out on this BaaS gravy train, I decided to build a (lightweight) banking API. While I don’t have any plans to use it right now, it serves as a helpful tool to use if I ever want to build a product or service on top of it. In this case, I’ll be using my university’s credit union as the bank in this API.
Database Design
The first part of this series will be about data. That is, how data would be stored and tracked.
The fundamental issue (read: first principle) of any API, let alone a banking API, is what data is stored, how is it stored, and how we can make the data as easily accessible as possible. Because an API is primarily an information-getter (GET) or an information-sender (POST), that data has to be manipulated somewhere. We will develop an initial design of the database, with each table to be created and their attributes.
Regarding the USC Credit Union, we would need to take in a wide variety of different data points about the users, such as their personal information and the balances they have in their accounts, as well as the multitude of different financial services offered.
Below, I’ve designed tables for User, Account, and CreditCard:
//PK stands for Primary Key
//FK stands for Foreign Key User
- student_id PK
- first_name
- last_name
- address Account
- account_number PK
- student_id PK FK
- balance
- type_num
- type_nameCreditCard
- card_number PK
- student_id PK FK
- type_num
- current_balance
- security_code
- expiration_date
- spend_limit
The User table lists each of the users, with student_id as the primary key (since this is a university). From the student_id, we can figure out all the other attributes of the user.
The Account table keeps a list of all the open savings and checking accounts in the credit union. For each row, both the account_number and the student_id serve as primary keys, since a student may have multiple accounts.
The CreditCard table keeps a list of all the credit card accounts in the USCCU. The card_number and the student_id serve as primary keys again, since a student could have several credit cards (Need one for the Starbucks and one for the AirPods :)).
So why did I separate the savings and checking accounts from the credit cards? Well, although one’s credit card is technically an account in a bank, it has a few attributes that differentiate it from the others. One, it’s not a place where money is stored — thus the balance will reflect how much one spends rather than how much one saves. Two, it has several features that neither a savings nor a checking account has, such as an expiration date and a security code.
However, our database is not complete yet. USC Credit Union offers a wide variety of different savings and checking accounts, as well as credit cards. We would need to consider the fact that each student can have a combination of different varieties of checking, saving, and credit cards. Moreover, our database currently does not allow developers to track transactions by users on their accounts. This is an essential feature to have because developers should be able to create tools that help with people’s personal transactions (especially for college kids, I see too many of them spending superfluous money on boba and Patagonia and whatever latest fad there is).
Transaction
- transaction_id PK
- student_id
- account_number
- amount
- acquiring_account_number AccountTypes
- type_name PK
- type_num PK
- type CreditCardTypes
- type_num PK
- type
The Transaction table keeps a list of all the transactions through Savings, Checking, and Credit Card accounts. Each transaction has a unique ID, and while it is set to be an integer now, I plan on making it a several character alphanumeric hash in the future. Each transaction has an amount — if it is positive, that means the student received money, and if it is negative, the student spent money. Sometimes, the transaction may have an acquiring_account_number, which is the account number on the other side of the transaction. But without knowing the routing number, it would be difficult to determine the origin of the acquiring account if they are outside of the USC Credit Union.
The AccountTypes table keeps a list of savings and checking accounts and what they are. For example, type_name is either going to be “Savings” or “Checking.” The type_num is the number ID of the account type being referred to. Does that sound confusing? Well, it sort of is. See, for example, there are 4 different Checking accounts that USC offers: Student, Cardinal, Gold, and Trojan. I assigned each of these a numerical ID (type_num) from 1 through 4. So that would mean for a type_name of “Checking” and a type_num of 1, the type would be Student. The same idea goes for savings accounts.
The CreditCardTypes table keeps a list of credit card types. Since we are only dealing with credit cards in this table, it is easier than the AccountTypes. Each type is designated with a type_num. A type_num of 4 would refer to the Student Mastercard, for example.
This database was designed to be dynamic so that it can adapt to new products and services. If at some point in time, the USC Credit Union decides to introduce new financial products, it would not be hard to update the database to add new AccountTypes or CreditCardTypes.
In the next part, I will be showing how I would put this schema in an SQLite database and how to set up the API server on Python Flask! Stay tuned.
