Database Structure

Overview

User accounts and transaction data are stored in a SQLite database using the sqlite3 library included with Python 3.x by default. SQLite databases are file-system based, so no additional setup is required. The database name is controlled by the DB_NAME macro found in main.py.

Structure

The database consists of three tables title "bills", "payments", and "users". One bill row will relate to one or more payment row entries. You can manually inspect the contents of the .db file using DB Browser for SQLite.

users:

Field	Description
username	name used for login
password_hash	password hashed and salted using scrypt with 1024 iterations, block size 8, and no parallelism
password_salt	random bytes of salt for password hash

bills:

Field	Description
bill_id	unique integer used to identify this bill
title	user-given title for the bill e.g. 'maccas'
creator_username	the user account which created this bill, not necessarily the payer
timestamp	automatically generated timestamp of bill creation in database
total	the total value of the bill to be paid between all parties
payer	the name of the person who fronted the bill

payments:

Field	Description
bill_id	used to identify the bill which this individual payment to which this payment belongs
payee_name	the name of the person who must pay this particular payment to the payer
amount_owed	the amount of money which this person owes to the payer
is_paid	boolean value, whether this payment has already been completed or not
payment_id	unique integer to identify this payment when interacting with the database

Interacting with the database

Some default testing data can be generated by enabling the CREATE_TEST_DATA macro in main.py.

All i/o to the database from the backend, including when invoked by endpoints, should be handled through the DatabaseManager class. This contains various getters/setters required to achieve basic interaction with the data. A few basic unit tests are located at /server/test_db.py