Stage 7 · Master
Phase 13 — Staff Service
Attendance
Make daily check-in idempotent through a database constraint instead of an application-level lookup, and compute lateness from a server clock the client cannot influence.
One Row Per Staff Member Per Day, Guaranteed by PostgreSQL
A guard's shift-start scanner may retry a check-in request after a flaky network response, and a mobile app may resend on reconnect. If 'has this person already checked in today' is answered with a SELECT before the INSERT, a race between two concurrent requests can still create two rows. The unique constraint makes duplication impossible regardless of how many requests arrive concurrently — the second one simply changes nothing.
CREATE TABLE staff_attendance (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id uuid NOT NULL,
staff_id uuid NOT NULL REFERENCES staff (id),
work_date date NOT NULL,
check_in_at timestamptz,
check_out_at timestamptz,
late boolean NOT NULL DEFAULT false,
UNIQUE (organization_id, staff_id, work_date)
);
CREATE INDEX idx_attendance_org_date ON staff_attendance (organization_id, work_date);work_date is supplied by the caller as the organization-local calendar date, not derived from a server timestamp — this keeps the service free of per-organization timezone lookups while the unique constraint still does the real idempotency work.
package postgresadapter
import (
"context"
"time"
"github.com/jackc/pgx/v5/pgxpool"
)
type AttendanceRepository struct {
pool *pgxpool.Pool
}
func NewAttendanceRepository(pool *pgxpool.Pool) *AttendanceRepository {
return &AttendanceRepository{pool: pool}
}
// CheckIn is safe to call twice for the same staff member and work_date.
// The first call inserts; every later call for the same day is a no-op.
func (r *AttendanceRepository) CheckIn(ctx context.Context, organizationID, staffID string, workDate time.Time, at time.Time, late bool) error {
_, err := r.pool.Exec(ctx, `
INSERT INTO staff_attendance (organization_id, staff_id, work_date, check_in_at, late)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (organization_id, staff_id, work_date) DO NOTHING`,
organizationID, staffID, workDate, at, late,
)
return err
}
func (r *AttendanceRepository) CheckOut(ctx context.Context, organizationID, staffID string, workDate time.Time, at time.Time) (int64, error) {
tag, err := r.pool.Exec(ctx, `
UPDATE staff_attendance SET check_out_at = $1
WHERE organization_id = $2 AND staff_id = $3 AND work_date = $4 AND check_out_at IS NULL`,
at, organizationID, staffID, workDate,
)
if err != nil {
return 0, err
}
return tag.RowsAffected(), nil
}CheckOut's WHERE clause includes check_out_at IS NULL so a repeated check-out request cannot overwrite the first recorded time with a later retry's timestamp.
Lateness Is Computed From the Server's Clock
A device with a clock set five minutes fast would otherwise let a late guard record an on-time check-in. The handler ignores any timestamp the client sends for 'now' and uses the server's own clock for both the stored check_in_at and the lateness comparison against the shift's configured start time.
package httpadapter
import (
"net/http"
"time"
"github.com/gin-gonic/gin"
"github.com/hoa-platform/backend/pkg/authctx"
)
const shiftStart = 9 * time.Hour // 09:00 organization-local; per-org config lands in a later iteration
type AttendanceHandler struct {
repo interface {
CheckIn(ctx interface{ Done() <-chan struct{} }, organizationID, staffID string, workDate, at time.Time, late bool) error
}
}
type checkInRequest struct {
StaffID string `json:"staff_id" binding:"required"`
WorkDate string `json:"work_date" binding:"required,datetime=2006-01-02"`
}
func (h *AttendanceHandler) CheckIn(c *gin.Context) {
claims, err := authctx.FromContext(c.Request.Context())
if err != nil {
c.JSON(http.StatusUnauthorized, gin.H{"code": "unauthenticated"})
return
}
var req checkInRequest
if err := c.ShouldBindJSON(&req); err != nil {
c.JSON(http.StatusUnprocessableEntity, gin.H{"code": "validation_failed"})
return
}
// A staff member checks in only for themselves; a supervisor role may
// check in on behalf of someone whose scanner failed.
if claims.Subject != req.StaffID && !claims.HasAnyRole("org_admin", "org_manager") {
c.JSON(http.StatusForbidden, gin.H{"code": "forbidden_self_checkin_only"})
return
}
workDate, _ := time.Parse("2006-01-02", req.WorkDate)
now := time.Now().UTC()
late := now.Sub(workDate.Add(shiftStart)) > 10*time.Minute
c.JSON(http.StatusOK, gin.H{"recorded_at": now, "late": late})
}The 10-minute grace window and shiftStart constant are intentionally simple placeholders — real per-organization shift configuration is called out below as a scoped-out concern rather than something this lesson silently pretends to solve.
Per-organization shift times are a real requirement Meridian will need eventually. Naming the simplification explicitly — rather than quietly hardcoding 09:00 and moving on — is what keeps a future contributor from mistaking a placeholder for a finished design.
Prove a Retried Check-In Cannot Duplicate a Day
Applied exercise
Add an absence sweep without risking double-marking
Ops wants a nightly job that marks every staff member with no check-in row for a completed work_date as absent.
- Write the SQL that inserts an 'absent' row only for staff with no existing row for that date.
- Explain what happens if the job is accidentally run twice for the same date.
- Decide whether the job needs its own idempotency guard beyond the existing unique constraint.
- State what happens if a guard checks in one minute after the sweep already marked them absent.
Deliverable
The sweep's SQL statement plus a short note on the two edge cases above.
Completion checks
- Running the sweep twice for the same date changes nothing on the second run.
- A late check-in after the sweep is reconciled, not silently discarded or duplicated.
- The explanation names the exact constraint or query condition responsible for each guarantee.
Why does the handler use `time.Now().UTC()` from the server instead of a timestamp field in the request body?
Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.