Four-person database-systems course project
Chore Tracker API
A backend for roommates to coordinate chores and split shared bills.
- FastAPI
- Python
- PostgreSQL
- SQLAlchemy Core
- Docker
- Supabase CLI
- Swagger
1,000,025
rows in the performance dataset
two roommate problems sharing one relational model
The API handles household work and household money: chores can be assigned and moved through statuses, while bills are split into per-roommate shares with individual payment states.
There is intentionally no frontend. FastAPI's Swagger UI and documented curl workflows are the product surface for this four-person database-systems project.
chores, assignments, bills, and payment state
Roommate records
Create, list, and remove people who participate in assignments and bill splits.
Chore lifecycle
Create chores, assign them, update priorities and statuses, and inspect recent history.
Shared bills
Create a bill, split it across current roommates, edit its metadata, and update each person's payment status.
Administrative reset
A protected reset endpoint and synthetic data make repeatable grading and performance experiments possible.
the API and database layers
FastAPI + Pydantic
Defines 16+ documented REST endpoints, request schemas, enums, and a consistent validation-error response.
SQLAlchemy Core
Manages Postgres connections while the routers issue raw parameterized SQL rather than ORM model calls.
PostgreSQL
Stores five related tables: roommate, chore, chore_assignment, bill, and bill_list.
Docker + Supabase CLI
I used the Supabase CLI to provision the Docker-backed local PostgreSQL environment for the million-row benchmark. The project has no custom Dockerfile or Compose file, and the API itself was not containerized for deployment.
Render
Hosts the FastAPI service and its Swagger documentation. Docker was used locally for the database benchmark, not for production deployment.
Swagger + curl
Replaces a frontend with an inspectable API contract and two documented manual user workflows.
Faker + EXPLAIN ANALYZE
Creates realistic scale, exposes sequential scans, and verifies index-driven plan changes.
Five tables, two household problems
Roommates and chores meet through chore assignments; roommates and bills meet through bill-list shares. I drew the ER diagram and helped establish the original schema and SQLAlchemy connection pattern.
Splitting a bill without losing cents
I built the original bill endpoints and even-split flow, including validation when no roommates exist. A teammate later extended that foundation with the exact remainder-cent distribution used in the final code.
Measure, index, measure again
I generated a weighted synthetic dataset of 1,000,025 rows and set up a local Dockerized PostgreSQL environment through the Supabase CLI so network latency would not distort the database benchmark. I timed the API through Swagger, then ran EXPLAIN and EXPLAIN ANALYZE on the three slowest queries.
Four targeted indexes moved the identified plans away from sequential scans. For roommate assignments, raw database execution time fell from 86.427 ms to 5.088 ms. End-to-end API latency remained higher because serialization, application work, and transport also contributed.
Choosing isolation by scenario
I mapped two concurrency scenarios: bill creation racing with roommate deletion, and two roommates claiming the same chore.
The designs use SERIALIZABLE for the dependent multi-write bill flow and READ COMMITTED plus a unique constraint for chore claims. They were design analyses rather than implemented isolation controls.
the database and API areas I worked on
I bootstrapped the connection/schema foundation, originated the main bill endpoints, added early chore-assignment behavior, drew the ER diagram, generated the million-row dataset, and owned the performance investigation.
- Established the SQLAlchemy connection pattern and first five-table schema.
- Built the original bill creation/list/assignment/payment/update endpoints and even-split foundation.
- Added duplicate-assignment validation, status updates, and a correct upper bound to 30-day history.
- Designed four indexes after reading real sequential-scan plans, then re-measured database and endpoint time separately.
- Authored two concurrency scenarios; these are design analyses, not isolation controls implemented in the final API.
design, peer review, manual workflows, then performance
The team worked from user stories to ER/API specs, responded to named peer reviews, ran two structured curl-based user workflows against the live service, and finished with concurrency and performance analyses. Pytest was listed but no automated suite was implemented.
model
Draw cardinalities and define the schema before endpoint work.
spec
Document request/response contracts and exception behavior.
review
Accept, reject, or defer peer feedback with written rationale.
measure
Seed realistic scale, inspect query plans, add targeted indexes, and re-test.
results, with context
rows tested
Synthetic but deliberately shaped across all five tables.
indexes
Added after reading the actual query plans.
endpoints
Chores, assignments, roommates, bills, and payment states.
The project taught me to separate database execution time from total API latency and to let observed plans, rather than intuition, drive indexes. Pagination and implemented transaction controls would be the next engineering steps.