Schema

users_table

column_name	data_type	details
id	integer	not_null, primary_key
name	string	not_null
email	string	not_null, unique, indexed
password_digest	string	not_null
session_token	string	not_null, unique, indexed
created_at	datetime	not_null
updated_at	datetime	not_null

• index on email, unique: true
• index on session_token, unique: true

friends_table

column_name	data_type	details
id	integer	not_null, primary_key
profile_id	integer	not_null, foreign_key
friend_id	integer	not_null, foreign_key, indexed
created_at	datetime	not_null
updated_at	datetime	not_null

• profile_id references id of current_user
• friend_id references users table

groups_table

column_name	data_type	details
id	integer	not_null, primary_key
name	string	not_null
type	string	not_null
created_at	datetime	not_null
updated_at	datetime	not_null

• type, inclusion: {in: ["Apartment", "House", "Trip", "Other"] }

group_friend_table

column_name	data_type	details
id	integer	not_null, primary_key
group_id	integer	not_null, foreign_key, indexed
friend_id	integer	not_null, foreign_key, indexed
created_at	datetime	not_null
updated_at	datetime	not_null

• group_id belongs_to groups
• friend_id belongs to friends_table

expenses_table

column_name	data_type	details
id	integer	not_null, primary_key
profile_id	integer	not_null, foreign_key, indexed
amount	float	not_null
description	string	not_null
category_id	integer	not_null, foreign_key, indexed
payable_id	bigint	not_null, indexed
payable_type	string	not_null, indexed
date	date	not_null
split_type	string	not_null
created_at	datetime	not_null
updated_at	datetime	not_null

• profile_id references current_user, indexed
• index on ["payable_type", "payable_id"]
• payable_id will refer
  • friend_id if 2 people are sharing expense, payable_type: friend
  • group_id if expense is shared between a group, payable_type: group
• category_id references id from category_table, indexed
• split_type, inclusion in ['equally','unequally']
• date is the date of expense (like lunch on 5th Sept)

expense_details_table

column_name	data_type	details
id	integer	not_null, primary_key
expense_id	integer	not_null, foreign_key, indexed
paid_by	integer	not_null, foreign_key, indexed
amount_paid	float
created_at	datetime	not_null
updated_at	datetime	not_null

• expense_id references id from expense table, indexed
• paid_by references friend_id/profile_id from friends_table
  • In case a single person paying all the expense, paid_by refers
    • profile_id if the current_user pays all the expense
    • friend_id if a friend pays all the expense
    • total amount_paid for an expense_id === amount from expense table

This feature is only available for groups:

• In case of multiple people sharing expense, there is possibility of multiple paid_by's for a single expense
  • paid_by refers profile_id if current_user is sharing expense
  • paid_by refers friend_id if friend pays the expense
  • total amount_paid for an expense_id should always be equal to amount from expense table

categories_table

column_name	data_type	details
id	integer	not_null, primary_key
sub_category_id	string	not_null
name	string	not_null
logo_url	string	not_null
created_at	datetime	not_null
updated_at	datetime	not_null

• id belongs_to itself

additional_details_table

column_name	data_type	details
id	integer	not_null, primary_key
expense_id	integer	not_null, foreign_key, indexed
author_id	integer	not_null, foreign_key, indexed
notes	text
asset_url	string
created_at	datetime	not_null
updated_at	datetime	not_null

• asset_url contain url of an image or pdf
• expense_id belongs_to expense
• author_id belongs_to author