Milestone 3


“classcheck”: (short URL)

Instructor point of view

  • Register, log in using email address and password
  • Reset password
  • I have a set of courses: title, abbreviation “CS101”
  • Each course has a schedule that I specify somehow
  • Schedule is a series of meetings: start time, duration, location
  • Probably some sort of “clone” into subsequent weeks
  • Allow edit and delete individual meetings
  • See the activity log and leaderboard for each class
  • Audit check-ins: punish students who are cheating system

“CRUD” operations == Create, Retrieve, Update, Delete

Student point of view

  • Check in to a class, maybe using shortcut URL like

  • First time: “Great, we’ve got you in CS101. But now, who are you?” (type name and email) “Would you like to set a password?”

  • Then they see two types of data:
    1. Their own activity log (check-ins, points, badges)
    2. Leaderboard (compare to fellow students)

Validating check-ins

  • No automated system will be uncheatable, so instructors need to track to some extent
  • IP address check
  • Time interval
  • Lat/Lon check (browser geo api)
  • Unique code ( or QR/bar code

What is the point structure / badges?

  • Simple: one point per check-in, the end.
  • More complex:
    • Start with 1 point for 1st check-in, 2 for 2nd, etc.
    • up to 5 points for 5th check-in. Then it stays at 5.
    • Until you’re absent, then it goes back to 1.
    • “Welcome back” badge – a few extra points for returning after an absence.
    • Gold star for 10 consecutive attendances.


  • User (both instructors and students)
    • Full name
    • Student ID (only shown to instructor?)
    • Email address
    • Password
    • Avatar icon (profile pic) – use
  • Activation (password reset link)
    • User
    • Code (e.g., 9516607201be11f3f0c1386ebffe0d19)
    • Expiration
  • Course
    • Instructor (User)
    • Title
    • Abbreviation
  • Meeting
    • Course
    • Start time
    • Duration
    • Location (Room ID, Lat/Lon, IP mask)
    • Unique code (e.g., 23842)
  • Checkin : Records a student attending a meeting
    • Timestamp
    • Student (User)
    • Meeting
  • Reward (points/badges)
    • Timestamp
    • Checkin (optional)
    • Student (User)
    • Course
    • Points (int)
    • Name (“Welcome back”, “You cheated!”, “Glad you’re here, but you’re late”)
    • Icon (optional)

Create schema.sql

Write create table statements for these six entities.

Make sure it’s loadable into SQLite3 using an initdb command.

For SQL reference, look at SQLite manual and PostgreSQL manual.

“Dev/prod parity” refers to development and production tools being the same.

Create some “fixtures” using SQL or Python

Fixture basically means sample data: several sample users, sample courses, sample meetings, initial set of checkins, some rewards.

Write SQL queries (SELECT statements) for basic operations

  • Leaderboard data
  • Show course schedule