fffcb73ea4
Google-SSO PWA for bistro employee clock-in/out, admin employee management, and stats with CSV export. Express + SQLite backend, React + Zustand frontend in the light-mono-tui design language. Multi-stage Dockerfile, compose.yaml for image-based deploys, nginx reverse-proxy template, and an OKF documentation bundle in docs/. Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
69 lines
2.3 KiB
Markdown
69 lines
2.3 KiB
Markdown
---
|
|
type: SQLite Table
|
|
title: attendance_events
|
|
description: Append-only log of clock_in/clock_out/break_start/break_end events, and the state machine built on top of it.
|
|
resource: backend/src/db/index.ts
|
|
tags: [data-model, sqlite]
|
|
timestamp: 2026-08-12T00:00:00Z
|
|
---
|
|
|
|
# attendance_events
|
|
|
|
Append-only event log — there is no "shift" or "session" row. Shifts are
|
|
derived at read time by walking events chronologically
|
|
(`buildSessions` in `backend/src/services/attendance.ts`).
|
|
|
|
# Schema
|
|
|
|
| Column | Type | Description |
|
|
|---|---|---|
|
|
| `id` | INTEGER PK | autoincrement |
|
|
| `employee_id` | INTEGER | references [employees.id](./employees.md) |
|
|
| `type` | TEXT | one of `clock_in`, `clock_out`, `break_start`, `break_end` (CHECK constraint) |
|
|
| `ts` | TEXT | ISO 8601 UTC, set by SQLite default |
|
|
|
|
Indexed on `(employee_id, ts)`.
|
|
|
|
# State machine
|
|
|
|
`getLiveStatus` derives one of three states from an employee's most recent
|
|
event:
|
|
|
|
| Last event | Status |
|
|
|---|---|
|
|
| none / `clock_out` | `clocked_out` |
|
|
| `clock_in` / `break_end` | `working` |
|
|
| `break_start` | `on_break` |
|
|
|
|
`recordEvent` only allows the transition that's valid from the current
|
|
status (`NEXT_ALLOWED` map) — e.g. you can't `break_start` while already
|
|
`clocked_out`. An invalid attempt throws `InvalidTransitionError`, surfaced
|
|
by the API as `409`.
|
|
|
|
```
|
|
clocked_out --clock_in--> working
|
|
working --break_start--> on_break
|
|
on_break --break_end--> working
|
|
working --clock_out--> clocked_out
|
|
```
|
|
|
|
# Deriving shifts
|
|
|
|
`buildSessions(events, now)` walks a chronological event list and pairs
|
|
`clock_in`...`clock_out` into a `Session`, with nested `breaks`. A session
|
|
still missing its `clock_out` is `open: true` and its `workedMs`/`breakMs`
|
|
are computed against `now` (the request time) rather than a real end —
|
|
that's why the frontend re-derives it against a live clock between polls
|
|
(see [Frontend stores](/docs/frontend/stores.md)) instead of trusting a
|
|
stale fetch forever.
|
|
|
|
`summarize(events, now)` reduces sessions into `totalWorkedMs`,
|
|
`totalBreakMs`, `shiftCount` for a date range — the shape returned by both
|
|
the employee's own stats endpoint and the admin per-employee endpoint (see
|
|
[Attendance API](/docs/api/attendance-routes.md), [Admin API](/docs/api/admin-routes.md)).
|
|
|
|
# Related
|
|
|
|
- [employees](./employees.md)
|
|
- [Attendance API](/docs/api/attendance-routes.md)
|