Give it a memory.
Event log. Derived shifts.
The events move to SQLite. Three tables: employees, attendance_events (append-only, the source of truth), shifts (a derived cache, one row per shift). The server runs the same derivation as the client and materializes the result. Clock-in becomes idempotent at the API boundary — a second in without an intervening out returns 409. Overnight shifts, break pairing, and the RESTRICT foreign key that makes employee deletion impossible once they've clocked in. The client keeps deriving locally for the live clock, but the server's derivation is the source of truth on load.
Short answer: Three tables. employees(id, name, email UNIQUE, role, hourly_rate, hired_at, archived_at), attendance_events(id, employee_id FK RESTRICT, type, occurred_at, note, created_at) — append-only — and shifts(id, employee_id FK RESTRICT, date, clock_in_at, clock_out_at, break_minutes, minutes_worked, status) — a derived cache. The server walks events per employee, pairs in with out, sums break_start→break_end intervals, and upserts one shifts row. Clock-in checks whether an open shift exists; if so, 409. Clock-out closes it. Break start/end toggle state. On every mutation, the derivation re-runs inside the same transaction. The client calls GET /api/state on load, POST /api/clock-in on clock-in, and re-renders from each response.
✅ The three tables
Facts in events. Derived in shifts. RESTRICT on both.
The schema has two jobs: record what happened (events), and serve what's summarized (shifts). Events are the truth; shifts are the cache. Both reference the employee, and both refuse deletion.
CREATE TABLE employees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
role TEXT NOT NULL DEFAULT 'employee'
CHECK (role IN ('employee', 'manager')),
hourly_rate REAL NOT NULL DEFAULT 0 CHECK (hourly_rate >= 0),
hired_at TEXT NOT NULL,
archived_at TEXT
);
CREATE TABLE attendance_events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
employee_id INTEGER NOT NULL,
type TEXT NOT NULL
CHECK (type IN ('in', 'out', 'break_start', 'break_end',
'manual_in', 'manual_out')),
occurred_at TEXT NOT NULL,
note TEXT,
created_at TEXT NOT NULL,
FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE RESTRICT
);
CREATE TABLE shifts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
employee_id INTEGER NOT NULL,
date TEXT NOT NULL,
clock_in_at TEXT,
clock_out_at TEXT,
break_minutes INTEGER NOT NULL DEFAULT 0,
minutes_worked INTEGER NOT NULL DEFAULT 0,
status TEXT NOT NULL DEFAULT 'open'
CHECK (status IN ('open', 'closed', 'approved')),
created_at TEXT NOT NULL,
UNIQUE (employee_id, date),
FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE RESTRICT
);
Why UNIQUE (employee_id, date) on shifts:
A shift is per employee per day. There cannot
be two shifts rows for the same person on the
same date. The UNIQUE constraint guarantees
it at the schema level.
This means the derivation uses INSERT OR
UPDATE — upsert. If a shift row exists for
(employee, date), it's updated. If not, it's
inserted. The UNIQUE is what makes upsert
safe: the database refuses a duplicate, so
the code path is always "find and update,
or insert if missing."
Without the UNIQUE, two concurrent clock-ins
could create two shift rows for the same day.
With it, the second insert fails and the
transaction rolls back. That's correct.
Why status has three values, not two:
'open' — clocked in, no out yet
'closed' — clocked out, hours computed
'approved' — manager has signed off
Stage 03 uses 'open' and 'closed'. Stage 04
adds 'approved' and the workflow that sets it.
The CHECK constraint lists all three now so
Stage 04 is an additive change, not a schema
migration for the CHECK itself.
The alternative — adding 'approved' to the
CHECK in Stage 04 — requires dropping and
recreating the table in SQLite. Listing it
now avoids that.
Why RESTRICT on attendance_events:
An employee with events cannot be deleted.
The DELETE fails with FOREIGN KEY constraint
failed. Their payroll history survives.
The alternative — CASCADE — would delete all
events when the employee is deleted. That
destroys the record. Wrong for HR.
The alternative — SET NULL — would orphan
the events. Every report that says "Jane
worked 40 hours" becomes "someone worked
40 hours." Useless.
RESTRICT is the only correct action for a
payroll record. Archive the employee instead,
using archived_at.
Why created_at is separate from occurred_at:
occurred_at — when the event happened (the
clock-in time).
created_at — when the row was written (the
API call time).
For a normal clock-in, they're within
milliseconds. For a manual correction (Stage
04), they're different: the manual_out event
has an occurred_at of "yesterday 5 PM" but a
created_at of "today 10 AM" (when the manager
fixed it).
The distinction matters for audit. "When did
this fact become true?" and "When did the
system learn about it?" are different
questions, and payroll auditors ask both.
Why shifts is materialized, not a VIEW:
A VIEW would compute the shift on every
SELECT. That's correct but slow: the timesheet
view needs 40 shifts (5 employees × 8 days),
which means 40 derivations per page load,
each walking that employee's full event list.
A materialized table costs one derivation per
write. Reads are free. For a write-once-read-many
workload (which attendance is), the cache wins.
The invariant: every shifts row can be
recomputed from attendance_events. If you
ever suspect corruption, drop the row and
re-derive. That's the safety net.
✅ The server-side derivation
Same logic as Stage 02. Now it writes rows.
The derive_shift function in Python is the same state machine as the JavaScript version. It walks events in chronological order, tracks working / on-break / finished, and returns a dict with the shift's summary. The difference from Stage 02 is what happens next: the result is written to the shifts table.
def derive_shift(events, employee_id):
"""Walk events in chronological order, produce a shift summary."""
evs = sorted(
[e for e in events if e["employee_id"] == employee_id],
key=lambda e: e["occurred_at"],
)
clock_in = None
clock_out = None
break_minutes = 0
break_start = None
status = "not-started"
for e in evs:
t = e["type"]
if t in ("in", "manual_in"):
if status in ("not-started", "finished"):
clock_in = e["occurred_at"]
clock_out = None
status = "working"
elif t == "break_start":
if status == "working":
break_start = e["occurred_at"]
status = "on-break"
elif t == "break_end":
if status == "on-break" and break_start:
break_minutes += diff_minutes(break_start, e["occurred_at"])
break_start = None
status = "working"
elif t in ("out", "manual_out"):
if status in ("working", "on-break"):
if break_start:
break_minutes += diff_minutes(break_start, e["occurred_at"])
break_start = None
clock_out = e["occurred_at"]
status = "finished"
minutes_worked = 0
if clock_in:
end = clock_out or now_iso()
minutes_worked = diff_minutes(clock_in, end) - break_minutes
if minutes_worked < 0:
minutes_worked = 0
shift_date = evs[0]["occurred_at"][:10] if evs else None
return {
"date": shift_date,
"clock_in_at": clock_in,
"clock_out_at": clock_out,
"break_minutes": break_minutes,
"minutes_worked": minutes_worked,
"status": status,
}
The status values here are internal:
'not-started' → no events yet
'working' → clocked in, not on break
'on-break' → break_start without break_end
'finished' → clocked out
These are DIFFERENT from the shifts table's
status ('open', 'closed', 'approved'). The
derivation's status is the state machine's
internal state. The table's status is the
business state.
Mapping:
derivation 'working' → table 'open'
derivation 'on-break' → table 'open'
derivation 'finished' → table 'closed'
derivation 'not-started' → no row (no shift)
Why the difference? A shift that's currently
on break is still 'open' from the business's
perspective — the employee hasn't clocked out.
The break is an internal state within the open
shift. Collapsing 'working' and 'on-break' to
'open' keeps the table's status meaningful to
a manager ('is anyone still clocked in?').
Why sorting is inside the function:
events come from the DB in insertion order
(which matches chronological order in the
happy path). But a manual correction (Stage
04) has an occurred_at in the past but a
created_at of now — so it's inserted after
events that happened later.
Sorting by occurred_at inside the derivation
guarantees chronological order regardless of
insertion order. The function never trusts
the DB's row order.
Why 'manual_in' is treated as 'in':
A manual correction is a clock-in that a
manager recorded on the employee's behalf.
For derivation purposes, it's identical —
the employee started working at that time.
The distinction matters for audit (who
created it, with what note) but not for
the math. Collapsing them in the derivation
keeps the state machine simple and correct.
Same for 'manual_out' vs 'out'.
Why shift_date is the first event's date:
An overnight shift (22:00 Monday to 06:00
Tuesday) has two dates in its timestamps.
Which one is the shift's date?
The start date. Monday. Because that's when
the work began. The fact that some of it
happened Tuesday doesn't make it a Tuesday
shift — the employee signed in for Monday's
shift and worked through the night.
evs[0] is the chronologically first event,
so evs[0]["occurred_at"][:10] is the start
date. For a shift that starts at 23:59 and
ends at 00:01, this correctly assigns it to
the day it started, not the day it ended.
✅ Idempotent clock-in — at the API
Same request twice → one event.
Stage 02 had three guards in the browser. Stage 03 adds the fourth and final one on the server: an explicit check that the employee doesn't already have an open shift. If they do, the request is a no-op — with a 409 Conflict response that tells the client the current state.
@app.route("/api/clock-in", methods=["POST"])
def clock_in():
data = request.get_json(silent=True) or {}
employee_id = int(data.get("employeeId") or 0)
if not employee_id:
return jsonify({"error": "employeeId is required."}), 400
with get_db() as conn:
emp = conn.execute(
"SELECT id FROM employees WHERE id = ? AND archived_at IS NULL",
(employee_id,),
).fetchone()
if not emp:
return jsonify({"error": "Employee not found."}), 404
# Compute current shift from events
events = conn.execute(
"SELECT * FROM attendance_events WHERE employee_id = ? ORDER BY occurred_at",
(employee_id,),
).fetchall()
current = derive_shift([dict(e) for e in events], employee_id)
# Guard: reject if already working or on break
if current["status"] in ("working", "on-break"):
return jsonify({
"error": "Already clocked in.",
"shift": current,
}), 409
# Insert the event
ts = now_iso()
conn.execute(
"""INSERT INTO attendance_events
(employee_id, type, occurred_at, note, created_at)
VALUES (?, 'in', ?, NULL, ?)""",
(employee_id, ts, ts),
)
# Re-derive and upsert the shifts row
_refresh_shift(conn, employee_id)
conn.commit()
return jsonify(_state_payload(conn)), 201
@app.route("/api/clock-in", methods=["POST"])
def clock_in():
data = request.get_json(silent=True) or {}
employee_id = int(data.get("employeeId") or 0)
if not employee_id:
return jsonify({"error": "employeeId is required."}), 400
with get_db() as conn:
emp = conn.execute(
"SELECT id FROM employees WHERE id = ? AND archived_at IS NULL",
(employee_id,),
).fetchone()
if not emp:
return jsonify({"error": "Employee not found."}), 404
# Compute current shift from events
events = conn.execute(
"SELECT * FROM attendance_events WHERE employee_id = ? ORDER BY occurred_at",
(employee_id,),
).fetchall()
current = derive_shift([dict(e) for e in events], employee_id)
# Guard: reject if already working or on break
if current["status"] in ("working", "on-break"):
return jsonify({
"error": "Already clocked in.",
"shift": current,
}), 409
# Insert the event
ts = now_iso()
conn.execute(
"""INSERT INTO attendance_events
(employee_id, type, occurred_at, note, created_at)
VALUES (?, 'in', ?, NULL, ?)""",
(employee_id, ts, ts),
)
# Re-derive and upsert the shifts row
_refresh_shift(conn, employee_id)
conn.commit()
return jsonify(_state_payload(conn)), 201
Why 409 and not 400: 400 Bad Request means the client sent something malformed — a missing field, a wrong type, an invalid value. The client made a mistake. 409 Conflict means the request is valid but conflicts with the current state of the server. "You asked to clock in, but you're already clocked in." Nothing is wrong with the request; the state just doesn't allow it. The client handles 409 differently from 400: on 409, it doesn't show an error — it silently updates its state to match the server's, so the UI shows the correct current shift. On 400, it shows a real error. The response body on 409 includes the current shift. The client uses it to reconcile without an extra GET. One round-trip, one truth. Why the whole state is returned on 201: After the clock-in INSERT, the client needs to know: the new event, the new shift, and the updated KPIs. Returning the whole state payload (employees, events, shifts) means the client replaces its state wholesale and re-renders. One call, everything in sync. The alternative — returning just the new event — requires the client to update its local state manually: push to events, re-run derivation, recompute KPIs, re-render. That's exactly the logic the server just ran. Why duplicate it? Return the state, replace the state. The client's derivation exists only for the live clock (which needs to tick between API calls). Everything else comes from the server. Why _refresh_shift is a separate function: The clock-in, clock-out, break-start, and break-end endpoints all do the same thing: insert an event, re-derive the shift, upsert the shifts row. That's four lines of logic duplicated across four endpoints. _refresh_shift encapsulates it. Each endpoint just calls _refresh_shift after its INSERT. If the derivation logic changes — say, adding support for split shifts in Stage 04 — the change is in one place. This is the same factoring as renderKPIs in Stage 02: one computation, called from every path that needs it. The transaction: The whole block is inside `with get_db() as conn:`. If the INSERT succeeds but the upsert fails (e.g., FK violation), the INSERT rolls back. The state stays consistent: either both writes landed or neither did. This is the same discipline as POS's atomic checkout. Two writes that must agree cannot be split across two transactions. One transaction, all or nothing.
✅ Overnight shifts
The date is the start date. The math uses timestamps.
An employee clocks in at 22:00 on Monday and out at 06:00 on Tuesday. Which day's shift is it? The one that started. Here's how that falls out of the schema and the derivation:
def _refresh_shift(conn, employee_id):
"""Re-derive this employee's current shift and upsert the shifts row."""
events = conn.execute(
"SELECT * FROM attendance_events WHERE employee_id = ? ORDER BY occurred_at",
(employee_id,),
).fetchall()
shift = derive_shift([dict(e) for e in events], employee_id)
# No events → no shift row to write
if shift["status"] == "not-started" or not shift["date"]:
return
table_status = "open" if shift["status"] in ("working", "on-break") else "closed"
ts = now_iso()
conn.execute(
"""INSERT INTO shifts
(employee_id, date, clock_in_at, clock_out_at,
break_minutes, minutes_worked, status, created_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(employee_id, date) DO UPDATE SET
clock_in_at = excluded.clock_in_at,
clock_out_at = excluded.clock_out_at,
break_minutes = excluded.break_minutes,
minutes_worked = excluded.minutes_worked,
status = excluded.status""",
(
employee_id,
shift["date"],
shift["clock_in_at"],
shift["clock_out_at"],
shift["break_minutes"],
shift["minutes_worked"],
table_status,
ts,
),
)
def _refresh_shift(conn, employee_id):
"""Re-derive this employee's current shift and upsert the shifts row."""
events = conn.execute(
"SELECT * FROM attendance_events WHERE employee_id = ? ORDER BY occurred_at",
(employee_id,),
).fetchall()
shift = derive_shift([dict(e) for e in events], employee_id)
# No events → no shift row to write
if shift["status"] == "not-started" or not shift["date"]:
return
table_status = "open" if shift["status"] in ("working", "on-break") else "closed"
ts = now_iso()
conn.execute(
"""INSERT INTO shifts
(employee_id, date, clock_in_at, clock_out_at,
break_minutes, minutes_worked, status, created_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(employee_id, date) DO UPDATE SET
clock_in_at = excluded.clock_in_at,
clock_out_at = excluded.clock_out_at,
break_minutes = excluded.break_minutes,
minutes_worked = excluded.minutes_worked,
status = excluded.status""",
(
employee_id,
shift["date"],
shift["clock_in_at"],
shift["clock_out_at"],
shift["break_minutes"],
shift["minutes_worked"],
table_status,
ts,
),
)
Why ON CONFLICT (upsert):
SQLite (and Postgres, and MySQL 8+) support
"INSERT ... ON CONFLICT (cols) DO UPDATE".
This is the upsert: try to insert; if the
unique constraint fires, update the existing
row instead.
For shifts, the unique key is
(employee_id, date). If a row exists for
Jane on 2026-11-15, the clock-in's upsert
updates it. If not, it inserts a new one.
The alternative — SELECT then INSERT or
UPDATE — has a race: two concurrent
clock-ins could both SELECT nothing, both
INSERT, and one wins (the other fails with
UNIQUE constraint). The upsert is atomic:
the database serializes the conflict
resolution.
Why excluded.clock_in_at and not shift.date:
In ON CONFLICT ... DO UPDATE, the special
table "excluded" holds the row that was
being inserted. So:
excluded.clock_in_at = the new value
(from the INSERT VALUES)
shifts.clock_in_at = the old value
(already in the table)
We want the NEW value. So we use
excluded.clock_in_at on the right-hand side
of the SET.
This is the SQLite syntax. Postgres uses
the same. MySQL uses VALUES(clock_in_at)
or an alias. The pattern is universal, the
syntax varies.
Why the shift's "date" is from the derivation:
Derivation returns shift["date"] = the
first event's date. For an overnight shift
(in Monday 22:00, out Tuesday 06:00), that's
Monday.
The upsert inserts a row with date=Monday,
clock_in=22:00 Monday, clock_out=06:00
Tuesday, minutes_worked=480, status=closed.
Query "shifts on Monday" finds it. Query
"shifts on Tuesday" does not. The shift
belongs to the day it started.
This is the business rule for payroll:
hours are attributed to the day the work
began. A night shift on Monday is Monday's
labor cost.
If you ever need a different rule — some
jurisdictions attribute hours to the day
the work ended — you'd add a derived
"attribution_date" field and use that in
reports. But the shift row itself is
Monday-based, because that's when the
employee clocked in.
Why not-started returns without writing:
An employee with no events has no shift.
Writing a row with null clock_in would be
noise: every employee would have a zero-
hours shift row for every day. Reports would
have to filter them out.
The early return keeps the shifts table
sparse: one row per actual shift, not per
employee per day. Cleaner queries, less
data, no ambiguity.
✅ The five routes
One read, four writes. All state-shaped.
Every route follows the same shape: parse the request, run the mutation inside a transaction, re-derive and upsert, return the current state. The client never has to assemble state from partial responses.
GET /api/state
Returns {employees, events, shifts, today}. Called on page load. The one read.
POST /api/clock-in
Insert an in event. 409 if already working or on break. Returns the new state.
POST /api/clock-out
Insert an out event. 409 if not working or on break. Returns the new state.
POST /api/break
Insert break_start or break_end depending on current state. Returns the new state.
GET /api/employees
List active employees. Used by the picker in Stage 04 and by the frontend on first load if /api/state is unavailable.
The pattern
Read once. Write with 409-on-conflict. Return full state. The client replaces its state wholesale — no manual merging, no stale fragments.
✅ Client rewired
Same UI. fetch() instead of localStorage.
The Stage 02 frontend is largely unchanged. The differences are:
loadState()callsGET /api/stateinstead of readinglocalStoragesaveState()is gone — the server is the persistence layer nowclockIn()POSTs to/api/clock-ininstead of pushing tostate.eventsdirectly- On 409 from any endpoint, the response's
stateis used to replace the client state — the UI reflects the server's truth, no error toast - Everything else stays: the derivation, the render, the live clock
async function clockIn() {
if (transitioning) return;
transitioning = true;
try {
const res = await fetch(API + '/api/clock-in', {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({ employeeId: CURRENT_EMPLOYEE_ID })
});
const body = await res.json();
if (res.status === 409) {
// Already clocked in. Not an error — sync to the server's state.
if (body.shift) {
// Reconcile silently; the UI already shows the right state
}
await refreshState();
return;
}
if (!res.ok) {
showToast(body.error || 'Clock-in failed', true);
return;
}
applyState(body); // replace state, re-render
} catch (err) {
showToast('Network error: ' + err.message, true);
} finally {
transitioning = false;
}
}
async function clockIn() {
if (transitioning) return;
transitioning = true;
try {
const res = await fetch(API + '/api/clock-in', {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({ employeeId: CURRENT_EMPLOYEE_ID })
});
const body = await res.json();
if (res.status === 409) {
// Already clocked in. Not an error — sync to the server's state.
if (body.shift) {
// Reconcile silently; the UI already shows the right state
}
await refreshState();
return;
}
if (!res.ok) {
showToast(body.error || 'Clock-in failed', true);
return;
}
applyState(body); // replace state, re-render
} catch (err) {
showToast('Network error: ' + err.message, true);
} finally {
transitioning = false;
}
}
Why 409 is not shown as an error: From the user's perspective, "I clicked clock-in and the UI still shows I'm working" is the expected outcome if they were already working. Showing "Already clocked in" as a toast is noise. The 409 is a signal to the client, not to the user. The client responds by syncing its state (calling refreshState, or using body.shift), then re-rendering. The user sees no change because there is no change. On 400 (bad request) or 500 (server error), the client DOES show a toast. Those are genuine failures the user should know about. Why applyState(body) instead of manual merge: The server returns the full state: employees, events, shifts. The client replaces its own state wholesale. No merging, no partial updates, no risk of a stale field surviving. This is the same discipline as Inventory's refresh after a sale: the server is the source of truth, re-fetch it, don't trust local mutation. Why transitioning is set outside try: The guard flag must be set before any await. If it's inside try, a synchronous throw before the first await would skip the finally cleanup. Setting it before try, clearing it in finally, guarantees the flag is always paired. This is the same pattern as POS checkout's checkoutInFlight. It's not new — it's the same discipline applied to a different endpoint. Why refreshState() on 409 but applyState() on 201: 201 (created) returns the full state. Use it. 409 (conflict) returns only the current shift (a fragment). To get the full state, call refreshState() — a GET /api/state. The 409 could include full state, but it usually doesn't need to: the client was just working with the same state, and nothing has changed except the shift confirmation. One extra GET is cheap. Including full state in 409 would be redundant when the client already has it. If this becomes a latency issue, we'd add full state to the 409 response. For now, the split is clear: writes return state, conflicts return fragments, reads return state.
➡️ Next: Stage 04 — Advanced (Final)
The build becomes an HR system.
Stage 03 gives the app a memory. Stage 04 turns it into a tool a manager uses:
- Timesheets — a week grid, employees × days, with hours worked per cell and overtime flagged at 40h/week. The query is a
SUM(minutes_worked) GROUP BY employee_id, dateover the shifts table. - Manual corrections — a manager records a
manual_inormanual_outevent with a note. The original event stays; the correction is a new row. The derivation handles both, no special case. - The roster — a
scheduled_shiftstable (the "should have worked" plan) compared to theshiftstable (the "did work" reality). The gap is what managers care about. - Approval workflow — a manager approves a week.
shifts.status = 'approved'. Corrections on approved shifts require a re-open, which is another event. - The report — attendance summary: who's late, who's absent, who's over overtime. Same aggregate shape as the Z-report from POS.
- Closeout — four stages, three tables, event log, derivation, idempotent clock-in, timesheet, corrections, roster, approval. No Stage 05.
The Stage 04 lesson page follows the same format: six sections, six snippets, all under 30 lines.