Project B — Personal Timetable Generator

Part 1 · The case

Sam runs the program office of a small school. Every semester the timetable coordinator sends him one big spreadsheet — the master timetable — listing every class on offer: the course code, the section number, the day and time it's held, the room, and which lecturer teaches it. One row per class, dozens of lecturers mixed together in a single file.

Then the messages start. "Sam, boleh hantar jadual saya sahaja?" One by one, thirty lecturers ask for their own personal timetable. So Sam opens a blank template — a Mon–Fri grid with time slots down the side — types the lecturer's name at the top, filters the master for their rows, and copy-pastes each class into the right day + time box. Save. Rename. Email. Next lecturer.

Thirty lecturers = one lost afternoon. And every semester it starts again. What he really wants: upload the master once, see a list of every lecturer, click a name, and get their finished personal timetable — in the same clean template shape — instantly.

That's what this project builds.

Part 2 · What you're building

A Personal Timetable Generator. One page. Sam signs in, uploads the master timetable spreadsheet, and the app shows him a list of every lecturer it found inside — with a class count next to each name. Beside each name is a "Generate timetable" button.

Click a name and the app takes the blank personal timetable template (Name and Staff/Matric No at the top, Mon–Fri × 8am–5pm grid underneath), fills in that lecturer's classes in the correct day + time boxes, and gives Sam a downloadable .xlsx file. There's also a "Generate all" button that returns one zip file containing everyone's timetable at once.

Part 3 · Who uses it & how

Part 4 · The Power Prompt to start

A few plain-language notes before reading the prompt:

• Master timetable — the big spreadsheet with every class in it. One row per class per section. Somewhere in each row are: a course code, a day+time+venue (often written together, sometimes with line breaks like THU\n11am-1pm\nMPK3), and the lecturer's name.
• Blank template — a small spreadsheet with a Mon–Fri × time-slot grid and two empty fields at the top: Name and Staff/Matric No. This is the exact shape every lecturer receives.
• Day/time slot — one box in the grid, e.g. "Monday, 10:00–10:50". A class that runs 11am–1pm on Thursday fills the Thursday column across the 11 and 12 rows.
• Grouping by lecturer — the app picks out the lecturer column, and for each unique name collects all the class rows that mention that name. That becomes their personal list.
• AI's role here is small. Most of the work is deterministic — read the sheet, split day + time + venue, drop them in the grid. The AI only steps in when the day/time string is written oddly (e.g. MON 05, Thu 11-1pm, THU\n11am-1pm\nMPK3) and needs to be normalised to a clean day + start + end + venue.

Goal — Build a private tool that takes one master timetable spreadsheet and returns a filled personal timetable (in the same blank template shape) for every lecturer inside it. Only one user signs in.

Input — One .xlsx master timetable file with a row per class per section. Columns include (in some order): course code, section, day/time/venue, and lecturer name. A second uploaded file — the blank personal timetable template — defines the output shape (Name, Staff/Matric No, and a Mon–Fri × time-slot grid).

Layout — One page called Timetables. Top: an upload area for the master file and a slot to (re)upload the blank template. Middle: once uploaded, a list of every lecturer found — name, class count, and a "Generate timetable" button. Bottom: a "Generate all (.zip)" button. A second page called History lists every past upload so Sam can re-download without re-uploading.

Features — The master file is read on the server, not in Sam's browser. Every day/time/venue cell is parsed into day · start · end · venue. When a cell is written oddly, the AI is asked to normalise just that one string in a strict shape the app can check — if the AI answer doesn't fit the shape, that row is flagged for review instead of silently dropped. Every lecturer's file uses the same blank template, so Sam never sees two different layouts. Sign-in with email/password + Google.

Output — A downloadable {lecturer-name}-timetable.xlsx per person, or one timetables-YYYY-MM-DD.zip for the whole batch. Past uploads stay saved so re-exports are one click.

Part 5 · Data & flow

Two small tables

Same idea as Lesson 2: a "table" is just a list of rows the app stores. This project needs two of them.

• uploads — one row per master file Sam uploads. Columns: an id, the filename, a semester label (e.g. "Sem II 2025/2026"), when it was uploaded, and its status (parsed / ready / had-issues).
• class_rows — one row per class per section pulled out of the master, linked back to its uploads entry. Columns: lecturer name, course code, section, day, start time, end time, venue.

The security rules

The database itself makes sure only Sam — the signed-in owner — can read or write his rows. Same row-by-row rule pattern from earlier lessons: every row is tagged with the owner's account id (a unique number the app gives each signed-in user), and the database refuses reads or writes that don't match.

• Reading — only the account that uploaded a master can read its class rows. Lecturers are not accounts here; they just receive files.
• Writing — the piece of code that reads the master (the "server function" from Lesson 1) saves rows tagged with the caller's account id. The database refuses rows tagged with anyone else's id.
• Deleting — same rule. Sam can wipe his own uploads; the database refuses anyone else.

Part 6 · What matters for this project

From the five foundations in Lesson 1, this project leans on:

1. 1Cloud (the star)

   Reading a big spreadsheet, splitting it by lecturer, and writing 30 filled .xlsx files happens on the always-on computer that hosts the app — not inside Sam's browser. Browsers choke on that kind of work; a server barely notices.

2. 2AI (a small helper)

   The bulk of the work is deterministic parsing. The AI is only asked to clean up the odd day/time cell it can't split with a rule — and even then, it must answer in a strict shape the app can check. If it doesn't, that row gets flagged, not guessed at.

3. 3Auth

   One account, one owner. Email/password + Google is more than enough. Lecturers don't need accounts — they just receive their files.

4. 4Database

   Small. It stores each uploaded master and its parsed class rows so Sam can re-download a lecturer's file next month without re-uploading. Nothing here needs to be fancy.

5. 5Security

   Every row is tagged with Sam's account id, and the database refuses reads or writes that don't match. Nothing public to defend.

Part 7 · Strengths ✅

• Turns an afternoon of copy-paste into one upload and a click per lecturer. That's the whole product in one sentence.
• Every lecturer receives the exact same template shape. No 'why is mine different from hers' emails.
• Re-runnable. If the coordinator sends a corrected master mid-semester, Sam re-uploads and regenerates — same clicks, fresh files.
• Deterministic where it can be, AI-assisted only where it must be. Cheap to run, easy to trust.
• Tiny footprint. One user, no public surface, no email sending — nothing much to defend.

Part 8 · Weaknesses & trade-offs ⚠️

• Irregular day/time strings still need a human eye. When the AI can't parse a cell into day + start + end, the row is flagged inside the lecturer's file — Sam has to fix it by hand.
• The blank template is fixed. Classes outside 8am–5pm (evening tutorials, weekend labs) don't fit the grid; the app lists them in a small overflow section rather than silently dropping them.
• The master must actually name a lecturer per row. Rows with an empty lecturer column get collected under 'Unassigned' — useful signal, not silent skip.
• Co-teaching (two names in one cell) is handled by generating the same class in both people's timetables. If the master uses initials or nicknames, names may fail to match — the review step is where that shows up.
• One user only. Sharing this with a second admin means adding real accounts and per-user rows — a bigger change than it looks.

Part 9 · Dummy files to test with

Two files. The first is what Sam uploads — a messy master timetable full of fictional courses and lecturers. The second is the output shape — the blank personal timetable template the app fills in per person. All names, codes, and rooms are made up.

Part 10 · Extend it

• Email each lecturer their file automatically when Sam clicks 'Generate all', with a signed download link that expires in 7 days.
• Detect timetable clashes: two classes for the same lecturer in the same day/time slot — highlight them at the top of that person's file.
• Export to Google Calendar / .ics so lecturers can subscribe to their timetable in their phone calendar app instead of opening a spreadsheet.
• Support co-teaching properly: parse cells like "Dr. A / Dr. B" into both lecturers' files, with a small note that the class is shared.
• Add a semester dropdown so Sam keeps every past master in the same tool — one click to compare who taught what, last year vs this year.