Data Model Overview

The schema is designed around idempotent derivation: raw punches + roster + rules determine attendance rows. Everything else is either configuration or audit.

Core Tables

Table	Purpose
organizations	Client tenants.
organization_units	Physical deployment sites. Owns timezone, geo-fence, and policy overrides.
users	Authenticated principals (employees + back-office).
employees	Employment metadata; 1-to-1 with users for employee accounts.
employee_deployments	Many-to-many: employee × (org, unit) with date range.
roles / permissions / model_has_roles / role_has_permissions	spatie/laravel-permission tables.
role_scopes	Row-level scope (global, organization, unit) per role assignment.
shifts	Shift definitions per unit.
shift_assignments	Roster rows: employee × shift × date.
punch_logs	Raw punches from any channel. Append-only.
attendances	Derived attendance per (employee, shift_assignment).
attendance_flags	Red flags attached to an attendance row.
leaves	Leave requests, with approval chain metadata.
regularizations	Correction requests.
devices	Registered devices per employee.
employee_telegram_links	Telegram chat ID binding per employee.
attendance_locks	Monthly lock markers per (org, unit, month).
audit_logs	Generic before/after audit trail.
exports	Tracks background export jobs.
red_flag_policies	Per-unit configuration of red-flag actions.
holidays	Org / unit holiday calendar.

Invariants

• attendance_date = shift_assignments.date — even for overnight shifts, we key off the start date.
• A punch is never mutated — corrections go through regularizations, which create a new attendance row and preserve history.
• attendances.locked_at is non-null iff the (org, unit, month) has a row in attendance_locks.
• employee_deployments may overlap across organizations but not within the same organization (one active deployment per org at a time).

Derived Data

Everything in attendances and attendance_flags is derivable from:

1. The relevant punch_logs rows.
2. The shift_assignments for that (employee, date).
3. The policy configuration in red_flag_policies and organization_units.

This invariant is enforced by AttendanceRecalculator — calling it for a date is safe and idempotent.