Skip to main content

Relationships

erDiagram
    ORGANIZATIONS ||--o{ ORGANIZATION_UNITS : has
    ORGANIZATIONS ||--o{ HOLIDAYS : observes
    ORGANIZATION_UNITS ||--o{ SHIFTS : defines
    ORGANIZATION_UNITS ||--o{ RED_FLAG_POLICIES : configures
    ORGANIZATION_UNITS ||--o{ EMPLOYEE_DEPLOYMENTS : hosts

    USERS ||--|| EMPLOYEES : account
    EMPLOYEES ||--o{ EMPLOYEE_DEPLOYMENTS : deployed_at
    EMPLOYEES ||--o{ DEVICES : owns
    EMPLOYEES ||--o{ SHIFT_ASSIGNMENTS : scheduled
    EMPLOYEES ||--o{ PUNCH_LOGS : punches
    EMPLOYEES ||--o{ LEAVES : requests
    EMPLOYEES ||--o{ REGULARIZATIONS : requests

    SHIFTS ||--o{ SHIFT_ASSIGNMENTS : instantiates
    SHIFT_ASSIGNMENTS ||--|| ATTENDANCES : produces
    ATTENDANCES ||--o{ ATTENDANCE_FLAGS : has

    ORGANIZATION_UNITS ||--o{ ATTENDANCE_LOCKS : locked
    USERS ||--o{ AUDIT_LOGS : performs

Cardinalities​

  • Organization → Units — 1-to-many.
  • Unit → Shifts — 1-to-many. Each shift belongs to exactly one unit.
  • Employee → Deployments — many over time. Exactly one is_primary = true per active organization at any point.
  • Shift × Employee × Date → Shift Assignment — one row. A locum assignment additionally carries locum_for_id.
  • Shift Assignment → Attendance — 1-to-1. attendances.shift_assignment_id is unique.
  • Attendance → Flags — 0-to-many.
  • Leave / Regularization → Approval chain — modelled as status transitions + audit rows (not a separate table) for V1.

Soft Deletes​

  • organizations, organization_units, shifts, employees, users use soft deletes.
  • punch_logs, attendances, attendance_flags, audit_logs are append-only — never soft-deleted.

Indexes Worth Calling Out​

  • attendances (employee_id, attendance_date) — primary read pattern.
  • punch_logs (employee_id, punched_at) — processor and recalc scans.
  • shift_assignments (organization_unit_id, assigned_for) — roster views.
  • audit_logs (subject_type, subject_id) — "history for this record".