Lesson 10.6 · 50 minAn internal tool from a spreadsheet

What you will have

A spreadsheet the business kept by hand is now a private page in the admin area. For Brightside Cleaning: /admin/rota, where the owner adds and edits cleaners' shifts for a week and sees one summary, hours per cleaner, with the old rows imported and the totals proven to match.

Video

The video for this lesson is not recorded yet.

Prompts used in this lesson

Part 3 — Let Claude read the sheet first

Purpose: I want to replace a spreadsheet my business keeps by hand with a
small private page in my admin area. First I need to understand the
spreadsheet. Do not build anything yet.

Context: docs/internal-tools/data/brightside-rota.csv is an export of our
cleaners' weekly rota. First add docs/internal-tools/data/ to .gitignore and
confirm with git status that the file is not tracked.

Tell me, from the file itself:
1. Each column: what it seems to mean, the kinds of values, how many empty.
2. Every inconsistency, with row numbers: the same person or place spelled
   differently, mixed time formats, rows entered twice, impossible values,
   notes containing information that belongs in a column.
3. What the sheet is probably used to decide each week, and why you think so.
4. The questions I must answer before you design anything.

Constraints: count with a script and show the totals; do not estimate.
Change nothing in the file. Do not guess what a strange value means: ask.

Part 5 — The one summary, and the build

Purpose: replace our rota spreadsheet with a private page where I plan the
cleaners' week and see each cleaner's hours without adding them up by hand.

Context: your analysis of docs/internal-tools/data/brightside-rota.csv and my
answers. Read docs/crm-spec.md, the admin layout and login from 4.6 and the
CSV import from 5.7; reuse them.

Plan first: tables, routes, and what the import does with each inconsistency
you found. Wait for my approval.

Build:
1. Tables (migrations, no anonymous access): cleaners (id, name, active) and
   rota_shifts (id, created_at, cleaner_id, shift_date, start_time, end_time,
   area, note). Dates and times are plain local values in the business time
   zone.
2. /admin/rota: one week, Monday to Sunday, with previous and next. Each day
   lists its shifts in time order. One form adds and edits a shift: cleaner
   from the active cleaners, date, start, end, area, note. Delete asks for
   confirmation. "Rota" in the admin navigation.
3. Checks with a plain message: end after start; no overlapping shifts for
   one cleaner; cleaner, date, start and end required.
4. Summary on the same page: hours per cleaner for the week, number of
   shifts each, and a total.
5. /admin/rota/cleaners: add, rename, mark inactive. An inactive cleaner
   keeps her past shifts but cannot be chosen for new ones.
6. A one-time import script: apply my answers (merge the spellings, read
   bare numbers as hours, skip the exact duplicate, use my correction), and
   list for me every row you cannot place instead of guessing. Safe to run
   twice.

Not in this version: access for cleaners, notifications, pay rates, links
to leads or bookings.

Verify before reporting: run the build and tests; run the import locally and
show rows in the file, rows imported, rows skipped with reasons, and hours
per cleaner for each week from the page's own summary function; through the
browser connection, with me signing in, add a shift, edit it, try an
overlapping one, reload; confirm /admin/rota redirects to the login when
signed out. Say what you could not verify.

Do along

Work on your own project. Pause the video where a step says so.

1. Pause after Part 2. Choose your spreadsheet with the three ticks, or take the sample rota. Leave sensitive columns out of the export.
2. Pause after Part 3. Export it as CSV into docs/internal-tools/data/. Run the reading prompt with your file name and a one-line description of the sheet. Answer Claude's questions.
3. Pause after Part 4. On paper: what is the "thing" in your sheet? What is chosen from a list? What is left behind? Which rules did the sheet never enforce? What is the one question you open it to answer?
4. Pause after Part 5. Adapt the build prompt: your tables, your route under /admin/ (for a supplies list, /admin/supplies), your checks, your summary. Keep the plan-first step, the import rules and the verification list.
5. Pause after Part 6. Count one week, or one category, by hand from the spreadsheet and compare with the page. Explain every difference.
6. Commit, push, apply the migrations to the live database, run the import there once.
7. Do the "Check your work" steps, then tag with the commands under "Recap and next".

Check your work

1. Signed out, open your new admin page on the live domain. Expected: the login page.
2. Signed in, add a row, edit it, reload. Expected: the change is saved.
3. Break a rule on purpose (an end before the start, an overlapping shift). Expected: a plain message, nothing saved.
4. Compare counts. Expected: rows in the file = rows imported + rows skipped, with a reason for each skipped row.
5. Compare the summary with your hand count for one period. Expected: equal, or every difference explained by a specific row.
6. Run git status. Expected: nothing from docs/internal-tools/data/ is listed.

Common problems

• The summary does not match your hand count and you cannot see why → rows skipped silently, or times read in the wrong format → "List every row of week 2 for this cleaner from the file and from the database side by side, with the hours for each."
• Claude 'fixed' strange values without asking → the import rules were shortened in your adapted prompt → "Do not guess. Put every row you cannot place with certainty into a list for me."
• The CSV appears in git status → the data folder is not ignored → add docs/internal-tools/data/ to .gitignore; if the file was already committed, ask Claude how to remove it and whether it was pushed.

Homework

About 25 minutes, spread over one week, on your own. No other lesson depends on it.

1. One real week. Use the page instead of the spreadsheet for one working week and note each time you wanted to open the spreadsheet, and why. Deliverable: docs/internal-tools/rota-notes.md (use your tool's name). Done when: the note says "nothing missing" or lists what to add, most frequent first.
2. One rule from a past mistake. Think of a mistake the spreadsheet once caused and ask Claude to add one check that would have prevented it, with a test. Done when: you tried to repeat the mistake in the page and it was refused with a clear message. Commit without a tag.
3. Retire the spreadsheet. Decide what happens to the old file: read-only, archived, or deleted, and who needs to be told. Done when: nobody, including you, can add a row to the old sheet by habit.

Save your work

git add -A
git commit -m "Lesson 10.6: rota tool in the admin area, imported from the spreadsheet"
git tag lesson-10.6
git push
git push --tags