User stories

P0:
- Volunteers can update rental information for a given item
  - Volunteers can rent out an item to a student
  - Volunteers can update the system when an item is returned
- Volunteers can see the rental information for all items
P1:
- Volunteers can add/edit/delete items
- Volunteers can attribute a rental to a club
- Volunteers can see a renter’s active rentals
P2:
- Volunteers can get analytics information to see:
  - What is most popular
  - Average rental duration
- Volunteers can see a log of all transactions to have ever occurred
  - Format: <Account> rents <item> starting at <date> and due at <due date>
- API endpoints exist along the following lines to support external integrations (i.e. Discord bot):
  - /items/list
    - ?filter=<boardgame|calculator|...>
    - ?page=n&limit=m pagination data
    - ?sort=<title | availability | rentals> optional sort info
    - Returns: a paginated list of all rentable items
  - /items/<item_id>
    - Returns:
      - Information about the item:
        
        name
        
        id: unique to each copy of each item
        
        category
        
        last_rented_at (to help with caching)
      - Information about its current rental status: is_rented, due_at, is_overdue
  - /items/board-games/list
    - Query params: see /items/list
    - Returns: a list of board games
  - /items/board-games/<item-id>
    - Returns:
      - item_id
      - bgg_id: a string ID identifying the item within the BoardGamesGeek database.
      - physical_location
      - belongs_toowner_club_id (i.e. mathsoc, watsfic)
    - Does not return:
      - the board_games table record’s ID; omitted to reduce confusion
  - /metrics
    - ?category=<boardgame|calculator|...> optional query param to filter by item type
    - &metric=<rentals|unique_renters|avg_duration> required query param to determine the metric
    - &page=n&limit=m optional pagination data (defaults to first page of limit 20)
    - &id=<item id> optional ID to return the metric for a single item
    - Returns:
      - A paginated list of items, ranked DESC by the given metric, with metric info included
P3:
- Non-volunteer students can see the current state of MathSoc’s rental inventory
- Admins (?) can add, remove, modify who has access to the site
  - Not prioritized because we can hardcode access for volunteer@mathsoc.uwaterloo.ca

Data layer

High-level

Rentals
- Soft deletes only; records marked as “closed” on rental return
Items
- Board game
- Calculator
- [miscellaneous is not given its own table type, as it has no special metadata]
Clubs
- One table to store club info

`rentals`

A rental tracks a single individual renting one physical copy.

It should point to the exact copy being checked out, not just the item/title, so availability, return history, and inventory state stay tied to the correct physical object.

Rental policy rules (due_date is computed from checkout_date using the applicable policy by item type; may be manually overridden for approved exceptions):

calculator rental -> checkout_date + 3 days
board game rental -> checkout_date + 14 days
textbook rental -> checkout_date + 31 days

Column name	Type	Notes	Example
id	UUID	primary key
non-null
copy_id	UUID	foreign key to copies.id
non-null
indexed
renter_email	string	[email]
non-null
indexed	gfeng@uwaterloo.ca
renting_club_idorganization	string	nullable (club / organization if applicable) nullable FK to clubs.id if rented on behalf of a club/organization	CSC
checkout_date	datetime	non-null	March 7, 2026
due_date	datetime	non-null; computed from rental policy by item type, but can be manually overriden for approved exceptions	April 1, 2026
date_returned	datetime	nullable until returned	March 25, 2026
status	enum: ACTIVE, RETURNED, OVERDUE, CANCELLED	non-null (makes filtering rentals easier)	ACTIVE
created_at	datetime	non-null
updated_at	datetime	non-null
deleted_at	datetime	nullable