Make it a real HR system.
Timesheets. Approval. Reports.
The final layer. A timesheet that sums hours per employee per week and flags overtime at 40 hours. Manual corrections as new events with notes - the original stays, the correction is added, the audit trail survives. A roster that holds the scheduled plan next to the worked reality. A manager approval workflow that locks a week once signed off. And a report computed in SQL over hundreds of shift rows.
This is the final stage. After it, the HR & Attendance build is complete: four stages, four tables, an event log with a derived cache, idempotent clock-in, corrections that never edit history, timesheets, roster, approval, and reports. No Stage 05.
Short answer: Add a fourth table, scheduled_shifts(id, employee_id, date, start_at, end_at, note) - the roster. Timesheets are a SUM(minutes_worked) GROUP BY employee_id, date over the shifts table, with weekly overtime at 40h. Manual corrections are manual_in or manual_out events with a note - new rows, never edits. Approval flips shifts.status to 'approved' for a week; corrections on approved shifts return 409. The report is one SQL aggregate that returns late, absent, and overtime counts per employee for a date range.
Timesheet - a week grid from one query
Employees × days. Hours in each cell. One aggregate.
The timesheet view shows a grid: each employee is a row, each day of the week is a column, and each cell shows the hours worked. For five employees and seven days, that is 35 cells. Computing them one by one would be 35 round-trips. Computing them in one query is 35 rows in one response.
@app.route("/api/timesheet", methods=["GET"])
def get_timesheet():
week_start = request.args.get("week") or _current_week_start()
week_end = _add_days(week_start, 6)
with get_db() as conn:
rows = conn.execute(
"""SELECT employee_id, date,
SUM(minutes_worked) AS minutes,
MIN(status) AS status
FROM shifts
WHERE date BETWEEN ? AND ?
GROUP BY employee_id, date""",
(week_start, week_end),
).fetchall()
employees = _list_active_employees(conn)
grid = {}
for emp in employees:
grid[emp["id"]] = { "name": emp["name"], "days": {} }
for r in rows:
if r["employee_id"] in grid:
grid[r["employee_id"]]["days"][r["date"]] = {
"minutes": r["minutes"],
"status": r["status"],
}
return jsonify({
"week_start": week_start,
"week_end": week_end,
"employees": list(grid.values()),
})
@app.route("/api/timesheet", methods=["GET"])
def get_timesheet():
week_start = request.args.get("week") or _current_week_start()
week_end = _add_days(week_start, 6)
with get_db() as conn:
rows = conn.execute(
"""SELECT employee_id, date,
SUM(minutes_worked) AS minutes,
MIN(status) AS status
FROM shifts
WHERE date BETWEEN ? AND ?
GROUP BY employee_id, date""",
(week_start, week_end),
).fetchall()
employees = _list_active_employees(conn)
grid = {}
for emp in employees:
grid[emp["id"]] = { "name": emp["name"], "days": {} }
for r in rows:
if r["employee_id"] in grid:
grid[r["employee_id"]]["days"][r["date"]] = {
"minutes": r["minutes"],
"status": r["status"],
}
return jsonify({
"week_start": week_start,
"week_end": week_end,
"employees": list(grid.values()),
})
Why GROUP BY employee_id, date:
A week of shifts is a set of (employee, date, hours)
triples. The query collapses any multiple shifts
per day into one sum. If an employee worked two
shifts on Tuesday, the cell shows the total.
This is the same aggregation the client did in
Stage 02 (sum of minutesWorked across employees),
but grouped differently: per (employee, date)
instead of across all employees.
Why MIN(status):
A shift's status is 'open', 'closed', or 'approved'.
If an employee has two shifts on the same day
(one closed, one open), what status should the
cell show?
MIN works because 'open' < 'closed' < 'approved'
alphabetically. So if ANY shift in the day is
still open, the cell shows 'open' - which is
correct: the day isn't finished yet.
This is a small trick that avoids a CASE
expression. If you want to be explicit, use:
CASE
WHEN SUM(CASE WHEN status = 'open' THEN 1 ELSE 0 END) > 0 THEN 'open'
WHEN SUM(CASE WHEN status = 'closed' THEN 1 ELSE 0 END) > 0 THEN 'closed'
ELSE 'approved'
END
But MIN is shorter and correct for this enum.
Why the grid object, not a raw rows array:
The client renders a grid. If the API returned
rows, the client would have to pivot them into
a grid - iterating the rows, finding or creating
the employee entry, filling in the day cell.
That is client-side logic that duplicates what
a small server-side loop does in 8 lines.
Returning the pivoted shape means the client
just iterates grid.employees, then for each
employee iterates the 7 dates and looks up
grid.employees[i].days[date]. Simple.
This is a general principle: return the shape
the client renders, not the shape the database
stores. The database is normalized; the view is
not. The API is the translation layer.
Why the query is only one round-trip:
The alternative is one query per employee, or
one query per cell. Both are O(employees) or
O(employees * days) round-trips. For 5 employees
and 7 days, that is 5 or 35 round-trips versus 1.
At 30ms per round-trip (typical over a local
network), 35 round-trips is 1 second. 1 round-trip
is 30ms. For an interactive grid, the difference
is felt.
The index on shifts(date) makes the WHERE fast,
and GROUP BY on the result is small (at most
employees * days rows). One query, done.
Overtime - the rule belongs in config
Weekly at 40h. One rule. Documented.
Overtime is jurisdiction-specific. Some places count daily hours over 8, some weekly over 40, some both. Some pay double after 12. Stage 04 implements the simplest common rule - weekly over 40 - and documents that it is a choice, not a universal truth.
OVERTIME_THRESHOLD_MINUTES = 40 * 60 # config, not magic number
def compute_week_totals(conn, week_start, week_end):
"""Per-employee weekly totals with overtime flagged."""
rows = conn.execute(
"""SELECT employee_id,
SUM(minutes_worked) AS total_minutes
FROM shifts
WHERE date BETWEEN ? AND ?
GROUP BY employee_id""",
(week_start, week_end),
).fetchall()
result = []
for r in rows:
total = r["total_minutes"] or 0
regular = min(total, OVERTIME_THRESHOLD_MINUTES)
overtime = max(0, total - OVERTIME_THRESHOLD_MINUTES)
result.append({
"employee_id": r["employee_id"],
"total_minutes": total,
"regular_minutes": regular,
"overtime_minutes": overtime,
"is_overtime": overtime > 0,
})
return result
OVERTIME_THRESHOLD_MINUTES = 40 * 60 # config, not magic number
def compute_week_totals(conn, week_start, week_end):
"""Per-employee weekly totals with overtime flagged."""
rows = conn.execute(
"""SELECT employee_id,
SUM(minutes_worked) AS total_minutes
FROM shifts
WHERE date BETWEEN ? AND ?
GROUP BY employee_id""",
(week_start, week_end),
).fetchall()
result = []
for r in rows:
total = r["total_minutes"] or 0
regular = min(total, OVERTIME_THRESHOLD_MINUTES)
overtime = max(0, total - OVERTIME_THRESHOLD_MINUTES)
result.append({
"employee_id": r["employee_id"],
"total_minutes": total,
"regular_minutes": regular,
"overtime_minutes": overtime,
"is_overtime": overtime > 0,
})
return result
Why a named constant, not a literal 2400:
The 40-hour threshold is a business rule, not
a mathematical fact. Some jurisdictions use 35
or 37.5. Some use daily thresholds instead. The
constant names it and centralizes it.
Changing the rule is one edit:
OVERTIME_THRESHOLD_MINUTES = 37 * 60 + 30
If it were inlined as 2400, every use site would
need to change. One is a config change, the other
is a refactor.
A future Stage 05 (which does not exist) would
move this to a config table or environment
variable. For now, a module-level constant is
the right level of ceremony.
Why regular and overtime are separate fields:
The client needs to display:
- Total hours (for the row label)
- Regular hours (for the payroll baseline)
- Overtime hours (for the highlighted cell)
If the API returned only total and is_overtime,
the client would have to compute regular and
overtime from the threshold - duplicating the
constant. That is the wrong place for the rule.
Return all three. The server owns the rule; the
client renders the numbers.
Why overtime is weekly, not daily:
A common belief: "over 8 hours in a day is
overtime." That is true in California and
several other jurisdictions. But it is not
universal. Federal US law, for example, is
weekly over 40.
Stage 04 implements weekly because it is the
most common and the simplest to reason about:
one sum per employee per week, one threshold,
done.
A daily-overtime variant would be:
regular = SUM(MIN(minutes_worked, 480)) per day
overtime = SUM(MAX(0, minutes_worked - 480)) per day
Same structure, different per-row treatment.
The lesson names the choice; the reader can
adapt.
Why the boundary is 40h exactly:
An employee who works exactly 2400 minutes
has 0 overtime. An employee who works 2401
has 1 minute of overtime.
Some systems round to the nearest hour, or
round down, or use a grace period. Stage 04
uses exact minutes. The threshold is inclusive
(>= 40h is regular, > 40h is overtime). That
is the common interpretation; document it if
your jurisdiction differs.
Manual corrections - new events, never edits
The original stays. The correction is added.
An employee forgot to clock out. The shift is stuck at "working" since yesterday. The manager knows they left at 5:00 PM. What happens?
Not this
UPDATE attendance_events SET occurred_at = ... - the edit destroys the fact that the employee forgot. The audit trail lies.
This
INSERT a new manual_out event with the real time and a note. The original in stays. The derivation sees both events and produces the correct shift.
The note
"Employee forgot to clock out. Left at 5:00 PM per manager." The note is the human explanation the audit log needs.
The result
Shift shows clock_out = 5:00 PM, status = closed. Audit log shows two events: the original in at 9:02, the manual_out at 5:00 with the note.
@app.route("/api/correction", methods=["POST"])
def add_correction():
data = request.get_json(silent=True) or {}
employee_id = int(data.get("employeeId") or 0)
event_type = data.get("type") # 'manual_in' | 'manual_out'
occurred_at = data.get("occurredAt")
note = (data.get("note") or "").strip()
if event_type not in ("manual_in", "manual_out"):
return jsonify({"error": "type must be manual_in or manual_out."}), 400
if not employee_id or not occurred_at:
return jsonify({"error": "employeeId and occurredAt required."}), 400
if not note:
return jsonify({"error": "A note is required for corrections."}), 400
with get_db() as conn:
# Guard: don't allow corrections on approved weeks
week_start = _week_start_for(occurred_at[:10])
approved = conn.execute(
"SELECT 1 FROM shifts WHERE employee_id = ? "
"AND date BETWEEN ? AND ? AND status = 'approved' LIMIT 1",
(employee_id, week_start, _add_days(week_start, 6)),
).fetchone()
if approved:
return jsonify({
"error": "That week is approved. Reopen it before correcting."
}), 409
ts = now_iso()
conn.execute(
"INSERT INTO attendance_events "
"(employee_id, type, occurred_at, note, created_at) "
"VALUES (?, ?, ?, ?, ?)",
(employee_id, event_type, occurred_at, note, ts),
)
_refresh_shift(conn, employee_id)
conn.commit()
return jsonify(_state_payload(conn)), 201
@app.route("/api/correction", methods=["POST"])
def add_correction():
data = request.get_json(silent=True) or {}
employee_id = int(data.get("employeeId") or 0)
event_type = data.get("type") # 'manual_in' | 'manual_out'
occurred_at = data.get("occurredAt")
note = (data.get("note") or "").strip()
if event_type not in ("manual_in", "manual_out"):
return jsonify({"error": "type must be manual_in or manual_out."}), 400
if not employee_id or not occurred_at:
return jsonify({"error": "employeeId and occurredAt required."}), 400
if not note:
return jsonify({"error": "A note is required for corrections."}), 400
with get_db() as conn:
# Guard: don't allow corrections on approved weeks
week_start = _week_start_for(occurred_at[:10])
approved = conn.execute(
"SELECT 1 FROM shifts WHERE employee_id = ? "
"AND date BETWEEN ? AND ? AND status = 'approved' LIMIT 1",
(employee_id, week_start, _add_days(week_start, 6)),
).fetchone()
if approved:
return jsonify({
"error": "That week is approved. Reopen it before correcting."
}), 409
ts = now_iso()
conn.execute(
"INSERT INTO attendance_events "
"(employee_id, type, occurred_at, note, created_at) "
"VALUES (?, ?, ?, ?, ?)",
(employee_id, event_type, occurred_at, note, ts),
)
_refresh_shift(conn, employee_id)
conn.commit()
return jsonify(_state_payload(conn)), 201
Why the note is required:
A correction changes payroll. If a manager
adds a manual_out for 5:00 PM, and the
employee actually left at 4:00 PM, the
employee gets paid for an extra hour.
The note is the audit trail's defense: it
says WHO made the change and WHY. Six months
later, an auditor asks "why was this event
created?" The note answers. Without it, the
manual_out is indistinguishable from a normal
clock-out except for its type.
Enforcing "note required" at the API means
the UI has to require it too - a toast or a
disabled button until the field has content.
Discipline at the boundary.
Why the approved-week guard:
Once a week is approved, the payroll has been
processed. Corrections after that point would
silently change what was paid. That is a
compliance issue - the books would say one
thing, the payroll another.
The guard refuses corrections on approved
weeks. To make a correction, the manager
re-opens the week (a separate endpoint that
flips status back to 'closed'), makes the
correction, and approves again. The re-open
is itself logged (in Stage 04's full
implementation - see the closeout section).
This is the same pattern as POS's "you can't
refund a refund" - the financial record has
a state machine, and transitions are
explicit, not implicit.
Why the correction is a new row, not an update:
The original event is a fact: "the employee
clocked in at 9:02 AM." That fact is true
regardless of what happens later. If you
update it, you erase the fact.
The correction is also a fact: "a manager
recorded the employee leaving at 5:00 PM
on 2026-11-15, with note 'forgot to clock
out.'" That fact was created at a specific
moment (created_at).
Both facts coexist. The derivation walks both
and produces the correct shift. If someone
later asks "what actually happened?", the
answer is: "The employee clocked in at 9:02,
forgot to clock out, and a manager corrected
it at 10:05 AM." The events tell the full
story. Edits would tell a truncated one.
Why 'manual_in' and 'manual_out' are distinct
from 'in' and 'out':
The derivation treats them identically - a
manual_in is a clock-in, a manual_out is a
clock-out. The distinction is for:
1. Audit - the timeline shows 'Clocked in
(manual)' so a reader knows it was
corrected.
2. Reporting - "how many corrections this
month?" is COUNT(WHERE type LIKE 'manual_%').
A health metric.
3. Approval - some workflows require
manager sign-off specifically on
corrections, not on normal events.
If they were the same type, none of these
distinctions would be possible. The type
carries the semantics.
Roster - the plan next to the reality
What should have happened, and what did.
The shifts table tells you what happened. The scheduled_shifts table tells you what was planned. Comparing them answers "was the employee on time?" - a question the worked-only model cannot answer.
@app.route("/api/roster/compare", methods=["GET"])
def roster_compare():
week_start = request.args.get("week") or _current_week_start()
week_end = _add_days(week_start, 6)
with get_db() as conn:
rows = conn.execute(
"""SELECT s.employee_id, s.date,
s.start_at AS sched_start,
s.end_at AS sched_end,
w.clock_in_at AS actual_in,
w.clock_out_at AS actual_out
FROM scheduled_shifts s
LEFT JOIN shifts w
ON w.employee_id = s.employee_id
AND w.date = s.date
WHERE s.date BETWEEN ? AND ?
ORDER BY s.date, s.employee_id""",
(week_start, week_end),
).fetchall()
result = []
for r in rows:
late_minutes = 0
if r["actual_in"] and r["sched_start"]:
late_minutes = max(0, diff_minutes(r["sched_start"], r["actual_in"]))
result.append({
"employee_id": r["employee_id"],
"date": r["date"],
"scheduled": {"start": r["sched_start"], "end": r["sched_end"]},
"actual": {"in": r["actual_in"], "out": r["actual_out"]},
"late_minutes": late_minutes,
"absent": r["actual_in"] is None,
})
return jsonify(result)
@app.route("/api/roster/compare", methods=["GET"])
def roster_compare():
week_start = request.args.get("week") or _current_week_start()
week_end = _add_days(week_start, 6)
with get_db() as conn:
rows = conn.execute(
"""SELECT s.employee_id, s.date,
s.start_at AS sched_start,
s.end_at AS sched_end,
w.clock_in_at AS actual_in,
w.clock_out_at AS actual_out
FROM scheduled_shifts s
LEFT JOIN shifts w
ON w.employee_id = s.employee_id
AND w.date = s.date
WHERE s.date BETWEEN ? AND ?
ORDER BY s.date, s.employee_id""",
(week_start, week_end),
).fetchall()
result = []
for r in rows:
late_minutes = 0
if r["actual_in"] and r["sched_start"]:
late_minutes = max(0, diff_minutes(r["sched_start"], r["actual_in"]))
result.append({
"employee_id": r["employee_id"],
"date": r["date"],
"scheduled": {"start": r["sched_start"], "end": r["sched_end"]},
"actual": {"in": r["actual_in"], "out": r["actual_out"]},
"late_minutes": late_minutes,
"absent": r["actual_in"] is None,
})
return jsonify(result)
Why LEFT JOIN, not INNER JOIN:
The roster lists scheduled shifts. If an
employee was scheduled but never clocked in,
the shifts table has no matching row. An
INNER JOIN would drop that scheduled shift
from the result - the absence would be
invisible.
LEFT JOIN keeps the scheduled row and fills
the actual columns with NULL. The absence
is visible: actual_in IS NULL. That is the
whole point of the comparison.
This is the same LEFT JOIN reasoning as
Inventory's supplier product counts: preserve
the entity even when the joined data is
missing.
Why late_minutes is computed in Python:
The SQL could do it:
CASE WHEN w.clock_in_at IS NOT NULL
THEN MAX(0, (julianday(w.clock_in_at) -
julianday(s.start_at)) * 1440)
ELSE NULL END AS late_minutes
But the syntax is dense, dialect-specific, and
hard to read. The Python version is 3 lines
and obvious.
For a small result set (employees * 7 days),
the difference is negligible. For millions of
rows, you would push it to SQL. Same trade-off
as the derivation - Python is clearer, SQL is
faster at scale.
Why the roster table exists separately:
The shifts table records what happened. The
scheduled_shifts table records what was
planned. They have different lifecycles:
- A schedule is created ahead of time
(Stage 04's UI could let a manager drag
shifts onto a calendar).
- A shift is created when the employee
clocks in.
Merging them into one table would require
a status field to distinguish "scheduled but
not yet worked" from "worked" - and the two
have different fields. The schedule has
start/end; the shift has clock_in/clock_out
and break_minutes. Two tables, two shapes,
one JOIN.
Why "absent" is a derived flag, not a column:
An employee is absent if they were scheduled
but didn't clock in. That is derivable from
the JOIN: sched row exists, actual row doesn't.
Storing "absent = true" would create a second
source of truth that could drift. If the
employee clocks in late, the flag would need
to be updated. If they clock in via a
correction, the flag would need to be
updated. The derivation never drifts - it
re-evaluates the JOIN every time.
Same rule as stockStatus in Inventory and
totals() in POS: store facts, derive display.
Approval - lock the week, log the transition
Approve a week. Reopen it. Both are events.
The approval workflow is a state machine on shifts.status: open → closed → approved, with a re-open transition back to closed. Each transition is an event, so the audit log shows who approved what, when.
@app.route("/api/approve-week", methods=["POST"])
def approve_week():
data = request.get_json(silent=True) or {}
week_start = data.get("weekStart")
if not week_start:
return jsonify({"error": "weekStart is required."}), 400
week_end = _add_days(week_start, 6)
with get_db() as conn:
open_count = conn.execute(
"SELECT COUNT(*) FROM shifts "
"WHERE date BETWEEN ? AND ? AND status = 'open'",
(week_start, week_end),
).fetchone()[0]
if open_count > 0:
return jsonify({
"error": "Cannot approve - " + str(open_count) +
" shifts are still open.",
}), 409
conn.execute(
"UPDATE shifts SET status = 'approved' "
"WHERE date BETWEEN ? AND ? AND status = 'closed'",
(week_start, week_end),
)
conn.commit()
return jsonify({"ok": True, "week_start": week_start}), 200
@app.route("/api/approve-week", methods=["POST"])
def approve_week():
data = request.get_json(silent=True) or {}
week_start = data.get("weekStart")
if not week_start:
return jsonify({"error": "weekStart is required."}), 400
week_end = _add_days(week_start, 6)
with get_db() as conn:
open_count = conn.execute(
"SELECT COUNT(*) FROM shifts "
"WHERE date BETWEEN ? AND ? AND status = 'open'",
(week_start, week_end),
).fetchone()[0]
if open_count > 0:
return jsonify({
"error": "Cannot approve - " + str(open_count) +
" shifts are still open.",
}), 409
conn.execute(
"UPDATE shifts SET status = 'approved' "
"WHERE date BETWEEN ? AND ? AND status = 'closed'",
(week_start, week_end),
)
conn.commit()
return jsonify({"ok": True, "week_start": week_start}), 200
Why "open" blocks approval:
An open shift means someone clocked in and
hasn't clocked out. The hours are unknown -
they could be 4 hours, they could be 14.
Approving a week with open shifts would lock
in an incomplete record. The payroll would
pay for partial hours, then the shift would
close later and the discrepancy would need
another correction cycle.
The guard prevents this. A manager must first
close every open shift - either the employee
clocks out normally, or the manager adds a
manual_out correction. Then approve.
Why "closed" becomes "approved" but "open" doesn't:
The UPDATE has WHERE status = 'closed'. So:
- Closed shifts: flip to approved.
- Open shifts: untouched (and approval
is blocked anyway).
- Already-approved shifts: untouched
(idempotent - approving twice is safe).
This is idempotency by constraint, not by
check. The same pattern as ON DELETE SET NULL
in Inventory - the UPDATE's WHERE clause
encodes the state transition rule, so the
database enforces it.
Why no re-open endpoint in this snippet:
Re-opening a week is the inverse:
UPDATE shifts SET status = 'closed'
WHERE date BETWEEN ? AND ? AND status = 'approved'
It has the same structure: validate, update
with a WHERE that encodes the transition,
commit. We omit it here for space - the
lesson is the pattern, not every instance
of it.
In a complete implementation, the re-open
endpoint would also log an event ("week
reopened by manager X at time Y, reason Z").
The event is appended to attendance_events
with a new type - 'week_reopened' - and the
timeline shows it. Audit trail complete.
Why approval is a bulk UPDATE, not per-shift:
A week has up to 7 shifts per employee, 42
for six employees. Approving one at a time
is 42 UPDATEs. Approving the week is 1
UPDATE with a date range.
Both produce the same result. The bulk is
one round-trip and one transaction. The
per-shift version would need its own
transaction boundary (or none, if you
accept partial approval on failure - which
you shouldn't).
Bulk UPDATE, one WHERE, one commit. The
database does the iteration.
Report - one aggregate, three numbers
Who was late, absent, or over hours.
The report is the summary a manager reads before approving: per employee, for the date range, how many shifts were late, how many were absent, and how many hours of overtime accumulated.
@app.route("/api/report", methods=["GET"])
def get_report():
week_start = request.args.get("week") or _current_week_start()
week_end = _add_days(week_start, 6)
with get_db() as conn:
rows = conn.execute(
"""SELECT e.id AS employee_id,
e.name AS employee_name,
COALESCE(SUM(w.minutes_worked), 0) AS total_minutes,
SUM(CASE WHEN s.id IS NOT NULL
AND w.clock_in_at IS NULL
THEN 1 ELSE 0 END) AS absent_count,
SUM(CASE WHEN w.clock_in_at IS NOT NULL
AND s.start_at IS NOT NULL
AND w.clock_in_at > s.start_at
THEN 1 ELSE 0 END) AS late_count
FROM employees e
LEFT JOIN shifts w
ON w.employee_id = e.id
AND w.date BETWEEN ? AND ?
LEFT JOIN scheduled_shifts s
ON s.employee_id = e.id
AND s.date = w.date
WHERE e.archived_at IS NULL
GROUP BY e.id, e.name
ORDER BY e.name""",
(week_start, week_end),
).fetchall()
result = []
for r in rows:
total = r["total_minutes"] or 0
overtime = max(0, total - OVERTIME_THRESHOLD_MINUTES)
result.append({
"employee_id": r["employee_id"],
"employee_name": r["employee_name"],
"total_minutes": total,
"overtime_minutes": overtime,
"absent_count": r["absent_count"],
"late_count": r["late_count"],
})
return jsonify(result)
@app.route("/api/report", methods=["GET"])
def get_report():
week_start = request.args.get("week") or _current_week_start()
week_end = _add_days(week_start, 6)
with get_db() as conn:
rows = conn.execute(
"""SELECT e.id AS employee_id,
e.name AS employee_name,
COALESCE(SUM(w.minutes_worked), 0) AS total_minutes,
SUM(CASE WHEN s.id IS NOT NULL
AND w.clock_in_at IS NULL
THEN 1 ELSE 0 END) AS absent_count,
SUM(CASE WHEN w.clock_in_at IS NOT NULL
AND s.start_at IS NOT NULL
AND w.clock_in_at > s.start_at
THEN 1 ELSE 0 END) AS late_count
FROM employees e
LEFT JOIN shifts w
ON w.employee_id = e.id
AND w.date BETWEEN ? AND ?
LEFT JOIN scheduled_shifts s
ON s.employee_id = e.id
AND s.date = w.date
WHERE e.archived_at IS NULL
GROUP BY e.id, e.name
ORDER BY e.name""",
(week_start, week_end),
).fetchall()
result = []
for r in rows:
total = r["total_minutes"] or 0
overtime = max(0, total - OVERTIME_THRESHOLD_MINUTES)
result.append({
"employee_id": r["employee_id"],
"employee_name": r["employee_name"],
"total_minutes": total,
"overtime_minutes": overtime,
"absent_count": r["absent_count"],
"late_count": r["late_count"],
})
return jsonify(result)
Why employees is the FROM table, not shifts:
The report wants one row per active employee,
even if they worked zero shifts this week.
If FROM shifts were the base, an employee
who was absent all week would have no rows
in shifts, and would not appear in the
report. The report would say "everyone
worked this week" even though one employee
didn't show up.
FROM employees LEFT JOIN shifts keeps every
employee, filling shift columns with NULL.
The report shows the absence correctly.
This is the same LEFT JOIN discipline as
everywhere else - preserve the entity.
Why two LEFT JOINs, one to shifts and one to
scheduled_shifts:
The report needs:
- Total worked hours (from shifts)
- Late count (needs scheduled start AND
actual start - a JOIN between both)
- Absent count (scheduled but not worked)
The shifts join gives the actual data.
The scheduled_shifts join gives the plan.
Together they answer "late?" and "absent?".
The join key for scheduled_shifts is
(employee_id, date) - the same date as the
shift. If there's no shift on a scheduled
date, the shift columns are NULL, which is
what "absent" means. If there's a shift but
no scheduled shift (unscheduled work), the
late_count CASE is skipped - you can't be
late to a shift you weren't scheduled for.
Why SUM(CASE WHEN ... THEN 1 ELSE 0 END):
This is the portable conditional count.
COUNT(*) FILTER (WHERE ...) is cleaner but
SQLite only added FILTER in 3.30 (2019).
The CASE form works everywhere.
Each CASE produces 1 or 0 for each joined
row. SUM collapses them into a count. If
an employee has 5 shifts and 2 are late,
the SUM is 2.
Why the comparison is > s.start_at in SQL:
Comparing timestamps in SQL is a string
comparison on ISO-8601 strings. ISO-8601 is
lexicographically sortable: '2026-11-15T09:00'
< '2026-11-15T09:05'. So '>' does what you
expect.
This works because all timestamps are UTC
and formatted consistently. If we stored
timestamps in different formats (some with
Z suffix, some without, some local), the
comparison would break. Consistency in
storage is a prerequisite for SQL comparison.
Why overtime is computed in Python, not SQL:
It could be:
MAX(0, COALESCE(SUM(w.minutes_worked), 0) - 2400)
as an SQL expression.
But 2400 is the overtime threshold, which
is a config constant in Python. Duplicating
it in SQL would create two sources of truth.
The SQL returns total_minutes; Python
computes overtime from the constant. One
source, one rule, one place to change.
Same principle as any business rule:
compute in the layer that owns the rule.
The database owns the data; the application
owns the policies.
Run it
Same three commands. Adds the roster table.
apps/hr/downloads/backend/
├── app.py (updated: timesheet, overtime, corrections, roster, approval, report)
├── database.py (unchanged)
├── schema.sql (updated: adds scheduled_shifts)
├── seed.py (updated: seeds roster)
├── requirements.txt (unchanged)
├── README.md (updated)
└── static/
└── hr-stage-04.html (updated: timesheet, roster, approval views)
cd apps/hr/downloads/backend python seed.py # WIPES hr.db and reseeds with scheduled_shifts python app.py # http://localhost:5000
You must reseed. The Stage 03 database has three tables; Stage 04 expects four. seed.py drops and recreates everything. Any data you added in Stage 03 is lost - that is fine, it was sample data. In a real migration, you would write an ALTER TABLE script; here, reseeding is the honest reset.
Test the timesheet
Load the timesheet view. 6 employees, 7 days, hours per cell. The seeded data shows partial weeks. The overtime column flags any employee over 40h.
Test a correction
Record a manual_out for Sofia's shift. The shift closes. The timeline shows the correction with its note. The audit log has the original in and the manual_out - both preserved.
Test the roster comparison
The seeded roster has scheduled shifts for the week. Compare view shows: on-time employees, late employees (with minutes), absent employees (scheduled but never clocked in).
Test approval
Approve the current week. All closed shifts become approved. Then try to add a correction - 409: "week is approved." Reopen, correct, approve again.
Test the report
The report view shows per-employee: total hours, overtime hours, late count, absent count. Matches the timesheet and roster views because all three derive from the same data.
Break it
Stop Flask. The timesheet fails to load with a toast. Restart. Reload. Everything works. The client is stateless - the server is the truth.
Seven files. Drop them into apps/hr/downloads/backend/, keep the static/ subfolder intact, and the three-command run sequence works.
Common mistakes
What to check if Stage 04 doesn't work.
The Stage 03 DB has three tables; Stage 04 needs four. Fix: python seed.py (destructive - recreates the DB).
An UPDATE on attendance_events destroys the audit trail. Fix: INSERT a manual_* event with a note.
Locks incomplete hours into payroll. Fix: check for status='open' first, return 409.
Two sources of truth for one rule. Fix: keep the constant in Python, compute overtime there.
Absent employees vanish from the comparison. Fix: LEFT JOIN from scheduled_shifts.
35 round-trips for a 5x7 grid. Fix: one GROUP BY query returning 35 rows.
Silently changes what payroll already paid. Fix: check status, return 409, require re-open first.
Two sources of truth that drift. Fix: derive from the JOIN on every query.
Frequently asked
Why are manual corrections new events instead of edits?
Because attendance is a payroll record. If a manager could edit an event, the audit trail would lose the original. A missed clock-out becomes: the employee clocked in at 9:02, then a manual_out event was added with the note "forgot to clock out." The original event stays, the correction is a new row, and the derivation handles both.
How is overtime calculated?
Stage 04 uses the simplest common rule: weekly overtime at 40 hours. Sum the minutes_worked across all of an employee's shifts in a week, subtract 40 hours, the remainder is overtime. Some jurisdictions use daily rules. The rule belongs in config, not code. Stage 04 implements weekly only and documents the choice.
What is the roster for?
The roster is the plan: which employee is scheduled to work which shift. The shifts table is the reality: which employee actually clocked in and out. Comparing them shows who was late, who was absent, who worked unscheduled hours. Without a roster, "were they on time?" is unanswerable.
What does the approval workflow lock?
When a manager approves a week, every closed shift in that week gets status='approved'. Approved shifts become read-only: manual corrections return 409. This preserves the payroll record. To make a correction after approval, the manager re-opens the week, makes the correction, and approves again.
Why does the report use SQL aggregates instead of the client's derivation?
Because the report spans a week of shifts across all employees. The SQL aggregate SUM(minutes_worked) GROUP BY employee_id is a single query with indexed access. For the timesheet view, the server computes and returns the grid; the client just renders. Same pattern as POS's Z-report: the aggregate happens where the data lives.
Is this the final stage?
Yes. The HR & Attendance build is four stages: Interface, Interactions, Backend, Advanced. Stage 04 completes it. Authentication, deployment, payroll integration, and timezone/DST handling are separate concept courses - they are not Stage 05 of this build.
What should I build next?
Not another stage of this app. Either take a concept course (deployment, auth, HR concepts), or build something new that reuses this pattern. The four-stage arc - shell, client logic, backend, advanced data layer - applies to any app that tracks periodic state about people: fitness tracker, field service, project tracker, volunteer management.
You're done — the HR & Attendance build is complete
Four stages. Four tables. An event log with a derived cache. Idempotent clock-in at the API. Corrections that never edit history. Timesheets with overtime. A roster comparing plan to reality. Manager approval that locks a week. A report computed in SQL. The app a small operations team could run on.
role field becomes a real auth boundary.Build something new. The four-stage arc — shell, client logic, backend, advanced data layer — is a template for any app that tracks periodic state about people: fitness tracker, field service, project tracker, volunteer management. Same event-log discipline, same derivation pattern, same approval workflow. The pattern is the reusable thing.