HR & Attendance Build

Stage 04 · Advanced — Final Stage

Home › Apps › HR & Attendance › Stage 04
Stage 04 · 3–4 hours · FINAL

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.

3–4 hours Intermediate SQL · Flask · aggregation · workflow
HR hub Timesheet Overtime Corrections Roster Approval Report Run it Done — next steps

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.

timesheet.py backend/app.py · lines 240–265
@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.py backend/app.py · lines 275–295
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.

correction.py backend/app.py · lines 315–345
@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.

roster.py backend/app.py · lines 365–395
@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.

approval.py backend/app.py · lines 415–445
@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.

report.py backend/app.py · lines 465–495
@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.

Folder structure:
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)
Commands — note the reseed:
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.

Common mistakes

What to check if Stage 04 doesn't work.

1. Not reseeding after the schema change

The Stage 03 DB has three tables; Stage 04 needs four. Fix: python seed.py (destructive - recreates the DB).

2. Editing events instead of adding corrections

An UPDATE on attendance_events destroys the audit trail. Fix: INSERT a manual_* event with a note.

3. Approving a week with open shifts

Locks incomplete hours into payroll. Fix: check for status='open' first, return 409.

4. Hardcoding the overtime threshold in SQL

Two sources of truth for one rule. Fix: keep the constant in Python, compute overtime there.

5. Using INNER JOIN for the roster comparison

Absent employees vanish from the comparison. Fix: LEFT JOIN from scheduled_shifts.

6. Computing the timesheet per cell

35 round-trips for a 5x7 grid. Fix: one GROUP BY query returning 35 rows.

7. Allowing corrections on approved weeks

Silently changes what payroll already paid. Fix: check status, return 409, require re-open first.

8. Storing "absent" or "late" as columns

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.

The full build
✓ Stage 01 — Interface · two-view shell: manager dashboard, employee clock-in
✓ Stage 02 — Interactions · live clock, idempotent clock-in, derived KPIs, localStorage
✓ Stage 03 — Backend · Flask + SQLite, three tables, server derivation, idempotent API
✓ Stage 04 — Advanced · timesheets, overtime, corrections, roster, approval, reports
Back to the HR hub
All four stages in one place, with the data model and the download links for the full backend.

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.

← Back to HR & Attendance hub
HR Stage 04 · Advanced · Final