CASESTUDY.md

Granted

Overview

Granted is a scholarship search and matching platform for Alberta students, built to fix a real problem: more than $20 million in scholarships goes unclaimed every year, largely because the process of finding and tracking them is scattered and overwhelming. It launched publicly at SAIT's Capstone Convention and won Best Capstone Project at the SAIT Ignite Awards, judged across multiple programs.

Granted was a two-semester, six-person capstone. I joined in the second semester on the back-end, where I owned the systems that keep students on top of deadlines — a deadline-aware notification engine, profile-aware matching signals, and application tracking, all in PHP and MySQL.

//granted
Granted screenshot

The Challenge

Finding a scholarship is only half the battle — the other half is not letting it slip by. Deadlines are the moment students actually miss out, so saving a scholarship needed to mean more than adding it to a list. It had to quietly set up a plan to bring the student back before each deadline.

On the back-end, that meant turning a static "saved scholarships" list into an active system: schedule the right reminders for the right dates, deliver them where students would actually see them, keep matches relevant to each student's profile, and rebuild all of it cleanly whenever someone changed their mind.

The Process

Deadline-Aware Notification Scheduling

Students choose how far ahead they want reminders — a month, a week, and/or three days before a deadline. When someone saves a scholarship (or updates their preferences), the back-end works backward from that scholarship's deadline and schedules a reminder for each lead time they picked, storing one row per reminder in a notifications table. Only scholarships with a real, future deadline get scheduled.

PHP 
$insert_query = "
    INSERT INTO notifications (
        user_id,
        scholarship_id,
        unread,
        lead_time,
        notification_date
    )
    VALUES (?, ?, ?, ?, ?)
";
$insert_stmt = mysqli_prepare($connection, $insert_query);

foreach ($frequencies as $frequency) {
    $notification_date = null;

    if ($frequency === "3 days") {
        $notification_date = date('Y-m-d', strtotime($deadline . ' -3 days'));
    } elseif ($frequency === "1 week") {
        $notification_date = date('Y-m-d', strtotime($deadline . ' -1 week'));
    } elseif ($frequency === "1 month") {
        $notification_date = date('Y-m-d', strtotime($deadline . ' -1 month'));
    }

    if ($notification_date !== null) {
        $unread = 1;

        mysqli_stmt_bind_param(
            $insert_stmt,
            "iiiss",
            $user_id,
            $scholarship_id,
            $unread,
            $frequency,
            $notification_date
        );

        mysqli_stmt_execute($insert_stmt);
    }
}

Because reminders are derived data, changing preferences can't just add more rows. Updating notification settings first deletes the user's existing reminders (joined through their saved scholarships) and then regenerates the whole schedule from scratch — so the reminders always match the current settings, with no stale duplicates. Unsaving a scholarship removes its reminders, and a cleanup query clears out notifications whose deadlines have already passed.

Native Desktop Notifications

In-app reminders only work if students open the app, so I added real desktop notifications through the browser's Notification API. When a student opts in, the client requests permission at the right moment and handles every permission state — granted, denied, or not-yet-asked — gracefully. Due reminders then fire as native OS notifications, deduplicated with sessionStorage so the same alert never shows twice, and clicking one jumps straight to the scholarship and marks it read.

JS 
dueNotifications.forEach((notification, index) => {
  const storageKey = `desktop-notification-shown-${notification.notification_id}`;

  if (sessionStorage.getItem(storageKey)) return;

  setTimeout(() => {
    const notif = new Notification("Granted", {
      body: `${notification.name || "A scholarship"} closes in ${notification.lead_time}. If you are applying, this is a good time to review the details.`,
      tag: `notification-${notification.notification_id}`,
      icon: `${window.location.origin}/grantedv3/images/favicon.ico`
    });

    sessionStorage.setItem(storageKey, "1");

    notif.onclick = (e) => {
      e.preventDefault();
      notif.close();

      window.focus();
      window.location.href = `scholarships.php?scholarship_id=${notification.scholarship_id}&mark_read=1`;
    };
  }, index * 300);
});

Profile-Aware Matching Signals

Matches are only as good as what the platform knows about a student, so the browse page adapts to how complete their profile is. Rather than tracking completeness as a separate flag that could drift out of sync, I measured it directly from the source of truth with a single query that counts the empty profile fields.

PHP 
$progressquery = "
    SELECT
        (CASE WHEN first_name IS NULL OR first_name = '' THEN 1 ELSE 0 END) +
        (CASE WHEN email IS NULL OR email = '' THEN 1 ELSE 0 END) +
        (CASE WHEN password IS NULL OR password = '' THEN 1 ELSE 0 END) +
        (CASE WHEN date_of_birth IS NULL THEN 1 ELSE 0 END) +
        (CASE WHEN student_status IS NULL OR student_status = '' THEN 1 ELSE 0 END) +
        (CASE WHEN current_institution IS NULL OR current_institution = '' THEN 1 ELSE 0 END) +
        (CASE WHEN level_of_study IS NULL OR level_of_study = '' THEN 1 ELSE 0 END) +
        (CASE WHEN program IS NULL OR program = '' THEN 1 ELSE 0 END) +
        (CASE WHEN year_of_study IS NULL THEN 1 ELSE 0 END) +
        (CASE WHEN enrollment_status IS NULL OR enrollment_status = '' THEN 1 ELSE 0 END)
    AS NullColumnCount
    FROM users
    WHERE user_id = $current_user_id
";

$progressresult = mysqli_query($connection, $progressquery);

if ($progressrow = mysqli_fetch_assoc($progressresult)) {
    $is_profile_complete = ($progressrow['NullColumnCount'] == 0);
}

When the profile is complete, browse promises "the strongest matches based on your profile"; when it isn't, it nudges the student to fill in the gaps for better results — turning a data problem into a gentle prompt.

Application Tracking & Analytics

Two smaller pieces closed the loop. Students can mark a scholarship as applied, which the back-end tracks as a status on their saved record (inserting or updating as needed). And every "Apply" click routes through a counter that increments the scholarship's tally before redirecting out to the external application — giving the team a simple, reliable read on which scholarships actually drive interest.

PHP 
// Increment the counter
$update = $connection->prepare("UPDATE scholarships SET click_counter = click_counter + 1 WHERE scholarships.scholarship_id = ?");
$update->bind_param("i", $id);
$update->execute();

// Redirect to the external site
header("Location: " . $apply_link);
exit;

The Solution

The result is a back-end that makes saving a scholarship an active commitment: reminders scheduled around every deadline, delivered even when the app is closed, kept in sync as students change their minds, and paired with matching that sharpens as a profile fills in. Granted launched at CapCon and took home Best Capstone at the Ignite Awards.