Data model

Eight attendance tables plus the standard Laravel auth tables, all in the attendance_dev Postgres database. Audit events live in a separate sds_audit database in the same cluster.

ER diagram

erDiagram attendance_employee_register ||--o{ attendance_devices : "owns" attendance_employee_register ||--o{ attendance_punches : "made by" attendance_employee_register ||--o{ attendance_leave_applications : "filed by" attendance_employee_register ||--o{ attendance_leave_balances : "for year" attendance_employee_register ||--o{ attendance_tours : "filed by" attendance_devices ||--o{ attendance_punches : "used in" attendance_geo_fences ||--o{ attendance_punches : "matched" users }o--|| attendance_employee_register : "linked via employee_id" attendance_employee_register { bigint id PK bigint sanchalan_employee_id UK "FK to legacy sanchalan_master.employees" varchar(200) display_name varchar(200) email varchar(16) employee_type "staff | contract | vendor | member" varchar(64) parichay_id bool active jsonb reporting_chain "appointing authority + reporting officer" } attendance_devices { bigint id PK bigint employee_id FK varchar(64) device_uuid "client-stable UUIDv4" varchar(128) device_imei_hash "sha256(installation_id + salt)" varchar(16) platform "android | ios" text public_key_pem "P-256 SubjectPublicKeyInfo PEM" jsonb anti_spoof_flags timestamp last_used_at varchar(16) status "active | suspended | deactivated" } attendance_punches { bigint id PK bigint employee_id FK bigint device_id FK varchar(8) punch_type "IN | OUT" timestamptz punched_at timestamptz received_at float lat float lng int accuracy_m varchar(64) ssid_seen bigint geo_fence_id FK varchar(64) nonce text signature_b64 bool signature_verified varchar(32) verdict "accepted | rejected_geofence | rejected_signature | rejected_spoof | duplicate | offline_replay" jsonb verdict_reason jsonb device_anti_spoof_snapshot } attendance_geo_fences { bigint id PK varchar(80) name varchar(32) location_type "main_building | committee_room | residence" jsonb polygon_geojson jsonb allowed_ssids jsonb applies_to_employee_types bool active } attendance_holidays { bigint id PK date holiday_date varchar(100) name varchar(16) type "gazetted | restricted | weekend" jsonb applies_to_employee_types } attendance_leave_policies { bigint id PK varchar(16) leave_type varchar(16) employee_type decimal(6_2) credit_per_year decimal(6_2) max_balance bool supports_half_day bool consumes_holidays jsonb rules } attendance_leave_balances { bigint id PK bigint employee_id FK varchar(16) leave_type smallint year decimal(6_2) opening_days decimal(6_2) accrued_days decimal(6_2) used_days decimal(6_2) balance_days "GENERATED: opening + accrued - used" } attendance_leave_applications { bigint id PK bigint employee_id FK varchar(16) leave_type date start_date date end_date bool is_half_day decimal(6_2) days_consumed text reason varchar(16) status "pending | approved | rejected | cancelled" bigint reporting_officer_id timestamp reporting_officer_decided_at jsonb decision_history "audit log per row" jsonb attachments } attendance_tours { bigint id PK bigint employee_id FK varchar(200) purpose varchar(200) location date from_date date to_date varchar(16) status "pending | approved | rejected | cancelled" bigint reporting_officer_id jsonb decision_history text reason } users { bigint id PK varchar(255) email varchar(255) name varchar(255) password varchar(64) parichay_id bigint employee_id FK varchar(32) role "from sb-iam claim" bool active timestamp last_login_at }

Indexes + constraints worth noting

attendance_punches: partial unique index (employee_id, nonce) WHERE verdict = 'accepted' — replay protection without bloating the index with rejected rows.
attendance_punches: composite indexes (employee_id, punched_at), (verdict, punched_at).
attendance_leave_balances.balance_days: stored generated column — recomputed automatically when used_days increments.
attendance_devices: unique (employee_id, device_uuid) — same UUID can't bind to two employees.
CHECK constraints on every status/type column (verdict, punch_type, employee_type, leave_type, fence type, tour status, etc.) — Postgres-level guard against application bugs.

Retention model

Table	Retention	Reason
`attendance_punches`	7 years (per RTI Act-aligned default)	Each punch ties to compliance / payroll evidence.
`attendance_leave_applications`	7 years	Decision history is service-record material.
`attendance_leave_balances`	10 years	EL accumulation up to 300 days needs long historical view.
`attendance_tours`	5 years	TA/DA reconciliation window.
`attendance_devices`	retain even after deactivation	Future audit must be able to replay-verify historical signatures.
`sds_audit.audit_events` (attendance rows)	indefinite	Hash-chained record. Anchored daily.

Migration history

2026_04_27_100000  create_attendance_employee_register
2026_04_27_100100  create_attendance_devices
2026_04_27_100200  create_attendance_geo_fences
2026_04_27_100300  create_attendance_punches
2026_04_27_100400  create_attendance_holidays
2026_04_27_100500  create_attendance_leave_policies
2026_04_27_100600  create_attendance_leave_balances
2026_04_27_100700  create_attendance_leave_applications
2026_04_27_120000  extend_users_for_sso
2026_04_27_134908  create_personal_access_tokens_table   (Sanctum)
2026_04_27_140000  add_employee_id_to_users
2026_04_27_141000  add_nonce_replay_guard                (partial unique index)
2026_04_27_142000  widen_verdict_column                  (16 → 32)
2026_04_27_150000  create_attendance_tours