Personnel & Attendance¶

Status: Specified — ready to build Build order: 2 of 10 — depends on Projects & Sites

Purpose¶

Personnel tracks every worker on a project — their identity, qualifications, and certifications. Attendance records who is physically on site at any moment, enforces induction requirements before first entry, and triggers alerts when expired licences are detected on site.

The two parts work together. A worker exists in the Personnel register at the project level. Sign-in and headcount happen at the site level. A worker registered on a project can be signed into any site within that project.

Entities¶

Personnel¶

A worker registered on a project. Contains identity and employment details.

Right to work status values:

ni_number and share_code are stored encrypted (AES-256 via SQLAlchemy TypeDecorator) and never included in API responses in plaintext. Both are masked in display (e.g. ••••••••A1B). Neither is synced to offline SQLite.

CREATE INDEX idx_personnel_project_id
    ON personnel(project_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_personnel_status
    ON personnel(status, project_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_personnel_subcontractor
    ON personnel(subcontractor_id) WHERE deleted_at IS NULL AND subcontractor_id IS NOT NULL;
CREATE INDEX idx_personnel_employment_type
    ON personnel(project_id, employment_type) WHERE deleted_at IS NULL;
-- Partial unique index: email unique per project (allows NULL)
CREATE UNIQUE INDEX idx_personnel_email_project
    ON personnel(project_id, email) WHERE deleted_at IS NULL AND email IS NOT NULL;

PersonnelLicence¶

A qualification, certification, or card held by a worker. A single worker can hold multiple licences. Licences are the source of expiry alerts.

CREATE INDEX idx_licences_personnel_id
    ON personnel_licences(personnel_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_licences_project_id
    ON personnel_licences(project_id) WHERE deleted_at IS NULL;
-- Supports the daily expiry scanner
CREATE INDEX idx_licences_expiry_date
    ON personnel_licences(expiry_date) WHERE deleted_at IS NULL AND expiry_date IS NOT NULL;

Suggested licence types (tenant adds their own via custom fields or future label config):

licence_type is stored as VARCHAR — not a FK. New types are added as workers are registered; no central type table in V1.

AttendanceRecord¶

A single sign-in/sign-out event for a worker at a site. One open record at a time per worker per site (sign_out_at IS NULL = currently on site).

CREATE INDEX idx_attendance_site_id
    ON attendance_records(site_id, sign_in_at DESC);
CREATE INDEX idx_attendance_personnel_id
    ON attendance_records(personnel_id, sign_in_at DESC);
CREATE INDEX idx_attendance_project_id
    ON attendance_records(project_id, sign_in_at DESC);
-- Headcount query: currently on site
CREATE INDEX idx_attendance_open
    ON attendance_records(site_id) WHERE sign_out_at IS NULL;
-- Prevent duplicate sign-in per worker per site
CREATE UNIQUE INDEX idx_attendance_open_unique
    ON attendance_records(personnel_id, site_id) WHERE sign_out_at IS NULL;

SiteQRCode¶

Each site has a unique QR code used for self-service sign-in. Regenerating the QR invalidates the old one.

CREATE UNIQUE INDEX idx_site_qr_site ON site_qr_codes(site_id);
CREATE UNIQUE INDEX idx_site_qr_token ON site_qr_codes(token);

A QR code encodes a URL: https://{tenant-slug}.construo.build/sign-in?t={token}

The sign-in page at that URL presents the worker with a simple form (name lookup) and completes the sign-in without requiring the worker to have a platform account.

Status Lifecycle — Personnel¶

Archiving a worker does not delete their attendance history.

Business Rules¶

Personnel¶

1. A worker is registered at the project level and can be signed into any site within that project.

2. employment_type = subcontractor requires subcontractor_id. Validated in service layer before DB write.

3. ni_number is encrypted at rest using AES-256 via a SQLAlchemy TypeDecorator. It is never returned in list responses — only in the full detail response to Project Manager and above. Displayed masked (NI: ****7890) on the frontend.

4. Worker photos are uploaded via the presigned S3 pattern. photo_key stores the S3 object key. The API returns a short-lived presigned GET URL in the response, not the raw key.

5. Soft deleting a worker does not remove their attendance records or licence data. Historical records are preserved.

6. A suspended or archived worker is blocked from signing in. The sign-in flow checks status before processing.

Licences¶

1. expiry_date IS NULL means the licence does not expire. These licences are never included in expiry alert scans.

2. verified = true means someone has physically inspected the card. The verified_by and verified_at fields record who did it. Verification can be revoked (set back to false) — for example if the card was found to be a forgery.

3. For the sign-in licence check, a licence is expired if expiry_date < CURRENT_DATE. Licences expiring today are not yet expired.

4. The licence check at sign-in reviews all non-deleted licences for the worker. If any have expiry_date < CURRENT_DATE, licence_check_passed = false is recorded on the attendance record and the personnel.licence_expired_on_site notification is triggered. The sign-in is not blocked by expired licences — only induction non-completion blocks sign-in.

Attendance¶

1. A worker can only have one open attendance record per site at a time. Enforced by the unique partial index. Attempting to sign in a worker already on site returns 409.

2. The QR code sign-in flow resolves the worker by name lookup against the personnel register for the project. No Cognito account is required for a worker to sign in via QR.

3. End-of-day auto sign-out: a configurable time (tenant default 23:59) triggers a background job to sign out all workers still marked on site. Method recorded as auto_eod. The auto sign-out time is tenant-level config, not per-site.

4. Site managers can manually sign workers in or out from the Attendance page. Manual sign-in records sign_in_method = manual and sign_in_by = current_user.id.

5. Headcount is a derived value — never stored. Computed as COUNT(*) FROM attendance_records WHERE site_id = ? AND sign_out_at IS NULL.

6. A QR code whose expires_at is in the past returns HTTP 403 on the public /sign-in endpoint with error code qr_code_expired. The UI prompts the site manager to regenerate. Regenerating always creates a fresh token with a new expires_at (90 days, or tenant-configured value).

Sign-in Flow¶

The QR code and manual sign-in paths follow the same check sequence:

Worker arrives at site → scans QR code (or site manager triggers manual sign-in)
  1. Resolve token → site_id  (site_qr_codes table)
  2. Look up worker in personnel register by name selection
  3. Check worker status:
       suspended → 403, display reason
       archived  → 403
  4. Check induction completion:
       No complete induction for this site
         → write partial attendance record (induction_check_passed = false)
         → trigger notification: induction.blocked_signin
         → return 422 with induction_required error
  5. Check open attendance record — already signed in → 409
  6. Check licences for expired entries (expiry_date < CURRENT_DATE):
       Found → set licence_check_passed = false on the record
             → trigger notification: personnel.licence_expired_on_site
             → sign-in proceeds (not blocked)
  7. Write attendance_record (sign_in_at = now(), induction_check_passed = true)
  8. Return 201 — sign-in confirmed, licence_warnings in response if any

The induction check in step 4 calls the Inductions module service: has_completed_induction(personnel_id, site_id) → bool. Build with a stub returning True during Personnel development; replace with the real implementation when Inductions is built.

API Endpoints¶

Personnel¶

Licences¶

Attendance¶

QR Codes¶

POST /sign-in is the only unauthenticated endpoint. It accepts a token query parameter and uses a narrow scoped session that can only complete a sign-in — it cannot access any other API operation.

Request / Response Shapes¶

POST /projects/{projectId}/personnel¶

// Request
{
  "first_name": "James",
  "last_name": "Brennan",
  "date_of_birth": "1985-03-14",
  "phone": "07700 900123",
  "email": "j.brennan@acme-civil.co.uk",
  "employment_type": "subcontractor",
  "subcontractor_id": "uuid-subco",
  "emergency_contact_name": "Sarah Brennan",
  "emergency_contact_phone": "07700 900456",
  "custom_fields": {}
}

// Response 201
{
  "id": "uuid-worker",
  "project_id": "uuid-project",
  "first_name": "James",
  "last_name": "Brennan",
  "full_name": "James Brennan",
  "date_of_birth": "1985-03-14",
  "phone": "07700 900123",
  "email": "j.brennan@acme-civil.co.uk",
  "employment_type": "subcontractor",
  "subcontractor_id": "uuid-subco",
  "subcontractor_name": "Acme Civil Ltd",
  "employer_name": null,
  "emergency_contact_name": "Sarah Brennan",
  "emergency_contact_phone": "07700 900456",
  "photo_url": null,
  "status": "active",
  "licence_count": 0,
  "has_expired_licences": false,
  "custom_fields": {},
  "created_at": "2026-05-28T09:00:00Z",
  "updated_at": "2026-05-28T09:00:00Z",
  "created_by": "uuid-user"
}

ni_number is accepted as an input but never returned in responses. Project Managers see it masked (NI: ****7890) in the detail view; Site Managers do not see it at all.

GET /projects/{projectId}/personnel¶

Query params: status, employment_type, subcontractor_id, q (name search), has_expired_licences (boolean), sort (default last_name), limit, cursor

// Response 200
{
  "data": [
    {
      "id": "uuid-worker",
      "full_name": "James Brennan",
      "employment_type": "subcontractor",
      "subcontractor_name": "Acme Civil Ltd",
      "status": "active",
      "licence_count": 3,
      "has_expired_licences": false,
      "next_expiry_date": "2026-11-30",
      "photo_url": "https://...",
      "created_at": "2026-05-28T09:00:00Z"
    }
  ],
  "pagination": {
    "total": 47,
    "cursor": "eyJpZCI6Ii4uLiJ9",
    "has_more": true
  }
}

POST /projects/{projectId}/personnel/{id}/licences¶

// Request
{
  "licence_type": "CSCS — Skilled Worker",
  "licence_number": "CS/1234567",
  "issuing_body": "CITB",
  "issue_date": "2022-06-01",
  "expiry_date": "2027-06-01",
  "verified": false
}

// Response 201
{
  "id": "uuid-licence",
  "personnel_id": "uuid-worker",
  "licence_type": "CSCS — Skilled Worker",
  "licence_number": "CS/1234567",
  "issuing_body": "CITB",
  "issue_date": "2022-06-01",
  "expiry_date": "2027-06-01",
  "verified": false,
  "verified_by": null,
  "verified_at": null,
  "photo_url": null,
  "days_until_expiry": 369,
  "status": "valid",
  "created_at": "2026-05-28T09:00:00Z"
}

status on a licence response is computed:

POST /sites/{siteId}/attendance/sign-in¶

// Request
{
  "personnel_id": "uuid-worker",
  "sign_in_method": "manual",
  "notes": null
}

// Response 201 — signed in, expired licence present
{
  "id": "uuid-attendance",
  "personnel_id": "uuid-worker",
  "full_name": "James Brennan",
  "site_id": "uuid-site",
  "sign_in_at": "2026-05-28T07:34:00Z",
  "sign_out_at": null,
  "sign_in_method": "manual",
  "induction_check_passed": true,
  "licence_check_passed": false,
  "licence_warnings": [
    {
      "licence_type": "CPCS — Excavator 360°",
      "expiry_date": "2026-04-30",
      "days_overdue": 28
    }
  ]
}

// Response 422 — induction not complete
{
  "error": "induction_required",
  "message": "James Brennan has not completed the site induction for this site.",
  "personnel_id": "uuid-worker",
  "site_id": "uuid-site"
}

// Response 409 — already on site
{
  "error": "already_signed_in",
  "message": "James Brennan is already signed in to this site.",
  "attendance_id": "uuid-open-record"
}

GET /sites/{siteId}/attendance/headcount¶

// Response 200
{
  "site_id": "uuid-site",
  "site_name": "Canary Wharf — Site A",
  "headcount": 14,
  "as_at": "2026-05-28T10:15:00Z",
  "workers": [
    {
      "attendance_id": "uuid-attendance",
      "personnel_id": "uuid-worker",
      "full_name": "James Brennan",
      "employment_type": "subcontractor",
      "subcontractor_name": "Acme Civil Ltd",
      "sign_in_at": "2026-05-28T07:34:00Z",
      "licence_check_passed": false
    }
  ]
}

Permission Matrix¶

"Own site" = sites where site_manager_id = current_user.id or explicit project assignment row.

Notifications Triggered¶

The daily scanner queries:

SELECT pl.*, p.project_id
FROM personnel_licences pl
JOIN personnel p ON p.id = pl.personnel_id
WHERE pl.deleted_at IS NULL
  AND pl.expiry_date IS NOT NULL
  AND pl.expiry_date BETWEEN CURRENT_DATE AND CURRENT_DATE + :advance_days
  AND p.status = 'active'
  AND p.deleted_at IS NULL

The personnel.licence_expired_on_site notification fires at sign-in time, once per worker per site per day. Idempotency key: personnel.licence_expired_on_site:{personnel_id}:{site_id}:{date}

The site.qr_expiry notification fires 7 days before site_qr_codes.expires_at. The daily scanner queries:

SELECT sqc.*, s.site_manager_id, s.project_id
FROM site_qr_codes sqc
JOIN sites s ON s.id = sqc.site_id
WHERE sqc.expires_at BETWEEN NOW() AND NOW() + INTERVAL '7 days'
  AND s.deleted_at IS NULL

Recipient: the site's site_manager_id. Idempotency key: site.qr_expiry:{site_id}:{expires_at::date}

Offline Sync Scope¶

Offline sign-in queues the attendance write locally and replays on reconnect. If the server returns 409 on replay (worker signed in by another device in the interim), the queue item is marked failed and shown to the site manager as a conflict to resolve.

ni_number and share_code never sync to any client device under any circumstances. right_to_work_status syncs as a read-only field so site managers can see verification state offline, but right_to_work_verified_at and right_to_work_verified_by do not sync.

Cross-Module Dependencies¶

Upstream (must exist before building)¶

The Subcontractors dependency is soft in V1: subcontractor_id can be nullable and the FK constraint added in a migration once Subcontractors is built.

Downstream (read from Personnel & Attendance)¶

Sign-in dependency on Inductions¶

The sign-in flow reads induction completion status from the Inductions service. The interface: has_completed_induction(personnel_id: UUID, site_id: UUID) -> bool. Build with a stub returning True during Personnel development; replace with the real implementation when Inductions is built.

Tests Required¶

Personnel CRUD¶

• Register worker — valid payload → 201
• Register worker — employment_type = subcontractor without subcontractor_id → 422
• Register worker — duplicate email within project → 409
• Register worker — same email on a different project → 201 (allowed)
• List workers — pagination correct
• List workers — filter by employment_type, status, has_expired_licences
• List workers — name search (q) matches on first name, last name, and full name
• Get worker detail — ni_number present (masked) for Project Manager, absent for Site Manager
• Update worker — PATCH semantics, unspecified fields unchanged
• Transition status — all valid transitions succeed
• Transition status — invalid transition → 409
• Delete worker — soft delete; attendance history and licences preserved

Licences¶

• Add licence — valid payload → 201
• Add licence — expiry_date in the past → accepted (records expired licence)
• days_until_expiry computed correctly — positive (future), negative (overdue)
• Computed status correct for all four values
• Verify licence — sets verified = true, records verified_by and verified_at
• Soft delete licence — excluded from sign-in check and list; attendance history unchanged

Attendance¶

• Sign in — induction complete, no expired licences → 201, both checks true
• Sign in — expired licence → 201, licence_check_passed = false, licence_warnings populated
• Sign in — induction not complete → 422, induction_required error
• Sign in — worker suspended → 403
• Sign in — worker archived → 403
• Sign in — worker already signed in → 409
• Sign out — open record closed, sign_out_at set
• Sign out — worker not currently signed in → 404
• Headcount — count and list reflect only workers with open records
• Headcount — signed-out workers excluded
• Manual sign-in — sign_in_method = manual, sign_in_by set to acting user
• End-of-day auto sign-out — open records closed, method recorded as auto_eod
• Correct attendance — PATCH by Project Manager changes sign_in_at or sign_out_at
• Correct attendance — Site Manager cannot correct → 403

QR Codes¶

• Get QR code — generates token on first request
• Get QR code — returns same token on subsequent requests
• QR sign-in — valid token resolves correct site
• QR sign-in — invalid token → 404
• QR sign-in — expired token (expires_at in past) → 403 qr_code_expired
• Regenerate QR — old token no longer resolves → 404
• Regenerate QR — new token has expires_at 90 days from now
• Daily scanner — fires site.qr_expiry exactly 7 days before expiry, idempotent on re-run

Cross-tenant isolation (mandatory)¶

• GET worker whose project_id belongs to another tenant → 404
• POST licence with personnel_id from another tenant → 404
• POST sign-in with personnel_id from another tenant's project → 404
• QR token belonging to another tenant's site → 404

Right to Work¶

• Create personnel with right_to_work_status = share_code, no share_code value → 422
• Update right_to_work_status to share_code — sets right_to_work_verified_at and right_to_work_verified_by from request context
• share_code absent from all API responses (encrypted, never exposed)
• share_code absent from offline sync payload

Permission boundaries¶

• Site Manager cannot view ni_number — field absent from response
• Site Manager cannot view share_code — field absent from response
• Site Manager cannot register a worker on an unassigned project → 403
• Site Manager cannot correct an attendance record → 403
• Viewer cannot edit personnel → 403
• Viewer headcount response contains count only — workers array absent

Notification triggers¶

• Sign-in with expired licence → personnel.licence_expired_on_site pushed to SQS
• Same worker, same site, same day sign-in again → second push suppressed (idempotency)
• Sign-in blocked by induction → induction.blocked_signin pushed to SQS

Default Tenant Labels¶

Foundation Dependencies¶