# CLAUDE.md — ADMS Local Client App ## Project purpose This is a local Laravel application that reads attendance data directly from the ZKTeco ADMS cloud database (DigitalOcean MySQL). It serves as a local mirror/client for payroll processing, HR reporting, and custom attendance management. It does **not** write to the ADMS database. It is **read-only** against the shared database and has its own local tables for any app-specific data. --- ## Architecture ``` ZKTeco Cloud API ↓ webhook ADMS App (adms.elmntointernet.com) ← cloud server, do not touch ↓ writes to DigitalOcean MySQL (shared DB) ↑ reads from This Local App (Ubuntu + Apache) ← what you are building ↓ writes to Local tables (payroll, reports, etc.) ``` --- ## Stack - **Framework**: Laravel 13, PHP 8.4 - **Admin UI**: Filament 4 - **Database**: Two connections - `adms` — read-only connection to DigitalOcean MySQL (ADMS data) - `mysql` (default) — local MySQL for app-specific data - **Ubuntu + Apache + PHP-FPM 8.4** --- ## Database connections ### `.env` configuration ```env APP_NAME="ADMS Local" APP_ENV=local APP_DEBUG=true APP_URL=http://adms.local # Local database (app-specific tables) DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=adms_local DB_USERNAME=root DB_PASSWORD=your_local_password # ADMS cloud database (read-only) ADMS_DB_HOST=private-db-mysql-sgp1-98316-do-user-26530715-0.d.db.ondigitalocean.com ADMS_DB_PORT=25060 ADMS_DB_DATABASE=adms ADMS_DB_USERNAME=adms_reader ADMS_DB_PASSWORD=your_readonly_password ADMS_DB_SSL_CA=/etc/ssl/certs/ca-certificates.crt ``` ### `config/database.php` — add ADMS connection Add this inside the `connections` array: ```php 'adms' => [ 'driver' => 'mysql', 'host' => env('ADMS_DB_HOST'), 'port' => env('ADMS_DB_PORT', 25060), 'database' => env('ADMS_DB_DATABASE', 'adms'), 'username' => env('ADMS_DB_USERNAME'), 'password' => env('ADMS_DB_PASSWORD'), 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', 'prefix' => '', 'strict' => true, 'engine' => null, 'options' => extension_loaded('pdo_mysql') ? array_filter([ PDO::MYSQL_ATTR_SSL_CA => env('ADMS_DB_SSL_CA'), ]) : [], ], ``` --- ## ADMS database schema (read-only) These tables live in the DigitalOcean `adms` database. Never run migrations against this connection. ### `zkteco_attendance_logs` | Column | Type | Notes | |--------|------|-------| | `id` | bigint | PK | | `device_id` | bigint FK | → `zkteco_devices.id` | | `pin` | varchar | Employee PIN — join key to `zkteco_users.pin` | | `punched_at` | timestamp | Attendance punch datetime | | `status` | tinyint | 0=Check In, 1=Check Out, 2=Break Out, 3=Break In, 4=OT In, 5=OT Out | | `verify_type` | tinyint | 0=Password, 1=Fingerprint, 2=Card, 15=Face | | `work_code` | tinyint, nullable | Optional | | `raw_data` | json, nullable | Full cloud payload | | `cloud_log_id` | varchar, unique | Cloud idempotency key | | `sync_status` | enum | `pending`, `success`, `failed` — **always filter by `success`** | | `retry_count` | int | Processing attempts | | `error_message` | text, nullable | Set when sync failed | | `created_at` | timestamp | | | `updated_at` | timestamp | | ### `zkteco_users` | Column | Type | Notes | |--------|------|-------| | `id` | bigint | PK | | `pin` | varchar, unique | Employee PIN — primary join key | | `name` | varchar, nullable | Employee full name | | `card_number` | varchar, nullable | RFID card | | `is_enabled` | boolean | Active employees only | | `fingerprints` | json, nullable | | | `face_templates` | json, nullable | | ### `zkteco_devices` | Column | Type | Notes | |--------|------|-------| | `id` | bigint | PK | | `serial_number` | varchar, unique | | | `name` | varchar, nullable | Friendly name | | `ip_address` | varchar, nullable | | | `status` | enum | `online`, `offline`, `unknown` | | `last_activity_at` | timestamp, nullable | | | `last_sync_at` | timestamp, nullable | | --- ## Models All ADMS models must set `protected $connection = 'adms'` and must **never** define `$fillable` with write operations. ### `app/Models/Adms/AttendanceLog.php` ```php 'datetime', 'raw_data' => 'array', 'cloud_sync_time' => 'datetime', ]; const STATUSES = [ 0 => 'Check In', 1 => 'Check Out', 2 => 'Break Out', 3 => 'Break In', 4 => 'OT In', 5 => 'OT Out', ]; const VERIFY_TYPES = [ 0 => 'Password', 1 => 'Fingerprint', 2 => 'Card', 15 => 'Face', ]; public function device() { return $this->belongsTo(Device::class, 'device_id'); } public function employee() { return $this->belongsTo(ZktecoUser::class, 'pin', 'pin'); } public function getStatusLabelAttribute(): string { return self::STATUSES[$this->status] ?? 'Unknown'; } public function scopeSuccess($q) { return $q->where('sync_status', 'success'); } public function scopeToday($q) { return $q->whereDate('punched_at', today()); } public function scopeCheckIns($q) { return $q->where('status', 0); } public function scopeCheckOuts($q) { return $q->where('status', 1); } public function scopeForPin($q, string $pin) { return $q->where('pin', $pin); } public function scopeForDevice($q, int $id) { return $q->where('device_id', $id); } public function scopeDateRange($q, $from, $to) { return $q->whereBetween('punched_at', [$from, $to]); } } ``` ### `app/Models/Adms/ZktecoUser.php` ```php hasMany(AttendanceLog::class, 'pin', 'pin'); } public function scopeActive($q) { return $q->where('is_enabled', true); } } ``` ### `app/Models/Adms/Device.php` ```php hasMany(AttendanceLog::class, 'device_id'); } } ``` --- ## Common queries ```php use App\Models\Adms\AttendanceLog; use App\Models\Adms\ZktecoUser; // Today's punches AttendanceLog::with('employee')->success()->today() ->orderBy('punched_at')->get(); // Daily summary — first in, last out per employee AttendanceLog::success() ->selectRaw('pin, DATE(punched_at) as date, MIN(punched_at) as time_in, MAX(punched_at) as time_out') ->groupBy('pin', \DB::raw('DATE(punched_at)')) ->orderBy('date')->get(); // Monthly payroll range AttendanceLog::with('employee')->success() ->dateRange('2026-06-01', '2026-06-30') ->get()->groupBy('pin'); // All active employees with today's attendance ZktecoUser::active()->with(['logs' => fn($q) => $q->success()->today()])->get(); // Who is currently inside (last punch was a check-in) AttendanceLog::success() ->selectRaw('pin, MAX(punched_at) as last_punch, status') ->groupBy('pin') ->havingRaw('status = 0') ->get(); // Late arrivals (after 9am) AttendanceLog::success()->checkIns()->today() ->whereTime('punched_at', '>', '09:00:00') ->with('employee')->get(); ``` --- ## Features to build ### Phase 1 — Core (build first) - [ ] Attendance log list — filterable by date, employee, device, status - [ ] Employee list with today's attendance status (in / out / absent) - [ ] Daily attendance summary table (time_in, time_out, hours worked) - [ ] Device status overview ### Phase 2 — Reporting - [ ] Monthly attendance report per employee (exportable to CSV/Excel) - [ ] Tardiness report (arrivals after configured shift start time) - [ ] Overtime report (punches after configured shift end time) - [ ] Absentee report (employees with no punch on a working day) ### Phase 3 — Payroll integration - [ ] Shift configuration (define work hours per employee or department) - [ ] Working hours calculation (time_out - time_in, minus breaks) - [ ] Payroll summary export (employee, days worked, hours, OT hours) --- ## Filament setup ```bash composer require filament/filament php artisan filament:install --panels ``` Generate resources: ```bash php artisan make:filament-resource AttendanceLog --generate php artisan make:filament-resource ZktecoUser --generate php artisan make:filament-resource Device --generate ``` When creating Filament resources for ADMS models: - Set `protected static ?string $model = \App\Models\Adms\AttendanceLog::class;` - Make all columns read-only (no `CreateAction`, no `EditAction`, no `DeleteAction`) - Add `->searchable()` on `pin` and `->sortable()` on `punched_at` - Use `SelectFilter` for `status`, `sync_status`, `device_id` --- ## Ubuntu + Apache deployment ### Install dependencies ```bash sudo apt update sudo apt install -y apache2 libapache2-mod-fcgid supervisor sudo a2enmod rewrite proxy_fcgi setenvif sudo a2enconf php8.4-fpm sudo systemctl restart apache2 ``` ### Create project ```bash cd /var/www composer create-project laravel/laravel adms-local cd adms-local sudo chown -R www-data:www-data . sudo chmod -R 775 storage bootstrap/cache php artisan key:generate ``` ### Apache virtual host `/etc/apache2/sites-available/adms-local.conf`: ```apache ServerName adms.local DocumentRoot /var/www/adms-local/public AllowOverride All Require all granted Options -Indexes +FollowSymLinks SetHandler "proxy:unix:/run/php/php8.4-fpm.sock|fcgi://localhost" ErrorLog ${APACHE_LOG_DIR}/adms-local_error.log CustomLog ${APACHE_LOG_DIR}/adms-local_access.log combined ``` ```bash sudo a2ensite adms-local.conf sudo systemctl reload apache2 ``` ### Local MySQL database ```sql CREATE DATABASE adms_local CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE USER 'adms_local'@'localhost' IDENTIFIED BY 'strong_password'; GRANT ALL PRIVILEGES ON adms_local.* TO 'adms_local'@'localhost'; FLUSH PRIVILEGES; ``` ```bash php artisan migrate ``` ### Supervisor (if using queues) `/etc/supervisor/conf.d/adms-local-worker.conf`: ```ini [program:adms-local-worker] process_name=%(program_name)s_%(process_num)02d command=php /var/www/adms-local/artisan queue:work --sleep=3 --tries=3 autostart=true autorestart=true user=www-data numprocs=1 redirect_stderr=true stdout_logfile=/var/www/adms-local/storage/logs/worker.log ``` ```bash sudo supervisorctl reread && sudo supervisorctl update sudo supervisorctl start adms-local-worker:* ``` --- ## Rules for working on this project 1. **Never run migrations on the `adms` DB connection** — it is the shared cloud database owned by the ADMS app. Only run migrations on the default `mysql` connection (local tables). 2. **All ADMS models must have `protected $connection = 'adms'`** — without this, queries silently hit the wrong database. 3. **Always filter attendance logs by `sync_status = 'success'`** — `pending` and `failed` records are incomplete and should not appear in reports. 4. **Join employees via `pin`, not `id`** — the join between `zkteco_attendance_logs` and `zkteco_users` is on `pin` (string), not a foreign key integer. 5. **Do not cache ADMS queries for more than 5 minutes** — the cloud app writes new records continuously. --- ## Verification checklist Before reporting a feature as complete: - [ ] ADMS DB connection works: `php artisan tinker` → `App\Models\Adms\AttendanceLog::success()->today()->count()` - [ ] Employee names resolve: `App\Models\Adms\AttendanceLog::with('employee')->first()->employee->name` - [ ] No query touches the `adms` connection with write operations - [ ] Date filters use `punched_at`, not `created_at` - [ ] All reports filter by `sync_status = 'success'`