Factory to Cloud Behind NAT: Push Snapshots and a Command Queue

I had a client project where the machine holding the actual data sat in the factory LAN, running a PHP website and MariaDB. It fetched data from endpoint devices through cron at several scheduled times each day. The owner wanted to view summaries on a phone from outside, then later added, "Can I edit the data from the website too?" The problem was that the machine had no public IP, sat behind the ISP's NAT, and had a clear policy: no port forwarding and no VPN, because there was no dedicated administrator. So I had to redesign the entire path.

In this article, I have replaced all system names, route names, and env names with the generic name attsync, used example.com for the domain, and assumed 192.168.1.0/24 for the example LAN. Every line of the structure and logic comes from the real system.

Open ports or reverse direction — choose correctly from the start

For a small project without a full-time administrator, I think the risk of opening an inbound path into the client's LAN costs more than the work is worth. If the requirement is "read data + request edits to a few fields," rather than "full remote access," reversing the direction is much more worthwhile.

Overall structure: three paths, all initiated from inside

The cloud has two tables: a read-only mirror and a command queue. When a user taps the edit button on a phone, nothing is written directly to the factory machine. The action just drops a command into the queue and waits for the internal machine to pick it up.

Two tables in the cloud

These tables live in a separate database from the main website, so I create them directly from code instead of using migrations. Here is the actual DDL.

CREATE TABLE IF NOT EXISTS att_day (
    d            DATE NOT NULL PRIMARY KEY,
    active_total SMALLINT UNSIGNED NULL,
    cnt_full     SMALLINT UNSIGNED NULL,
    cnt_partial  SMALLINT UNSIGNED NULL,
    cnt_absent   SMALLINT UNSIGNED NULL,
    gen_ts       INT UNSIGNED NULL,
    payload      MEDIUMTEXT NOT NULL,
    updated_at   DATETIME NOT NULL
) DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS att_edit (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    d           DATE NOT NULL,
    period      TINYINT UNSIGNED NOT NULL,
    employee_no VARCHAR(12) NOT NULL,
    full_name   VARCHAR(120) NULL,
    status      ENUM('present','late','absent') NOT NULL,
    created_at  DATETIME NOT NULL,
    applied_at  DATETIME NULL,
    UNIQUE KEY uq_edit (d, period, employee_no),
    KEY k_pending (applied_at, d)
) DEFAULT CHARSET=utf8mb4;

The PHP side uses a separate connection and creates the tables itself if they do not exist:

private function conn()
{
    return DB::connection('legacy');   // DB เดิมของอีกระบบ ไม่ปน DB หลักของเว็บ
}

private function ensureTable(): void
{
    $this->conn()->statement('CREATE TABLE IF NOT EXISTS att_day ( ... )');
    $this->conn()->statement("CREATE TABLE IF NOT EXISTS att_edit ( ... )");
}

att_day stores one row per day, uses the date as its primary key, and puts the entire day's data into payload as JSON, with three count columns for a fast summary page. att_edit is the queue; its core is UNIQUE KEY uq_edit (d, period, employee_no), and applied_at NULL means the work has not been done yet.

Why the queue uses UNIQUE instead of being append-only There can only be one final user intention for one person in one period on one day. If the user taps "Present" and then changes their mind and taps "Late," the result must be "Late," rather than running two commands in sequence and hoping they arrive in the right order. An append-only queue suits events where each entry has its own meaning, but this job deals with state, not events.

Routes and CSRF exemptions

// routes/web.php — ซ่อน ไม่อยู่ใน nav เข้าด้วยรหัสอย่างเดียว
Route::post('/att-sync/ingest', [AttSyncController::class, 'ingest']);              // push จาก on-prem (X-API-KEY)
Route::get('/att-sync',         [AttSyncController::class, 'index'])->name('attsync');
Route::post('/att-sync/login',  [AttSyncController::class, 'login'])->middleware('throttle:10,1');
Route::post('/att-sync/logout', [AttSyncController::class, 'logout']);
Route::get('/att-sync/api',     [AttSyncController::class, 'api']);
Route::post('/att-sync/mark',   [AttSyncController::class, 'mark'])->middleware('throttle:120,1');   // ผู้ใช้กดแก้ -> เข้าคิว
Route::get('/att-sync/pending', [AttSyncController::class, 'pending']);                              // on-prem ดึงคิว (X-API-KEY)
Route::post('/att-sync/applied',[AttSyncController::class, 'applied']);                              // on-prem ยืนยันทำแล้ว (X-API-KEY)

// bootstrap/app.php — server-to-server ไม่มี CSRF token ต้องยกเว้น
$middleware->validateCsrfTokens(except: ['att-sync/ingest', 'att-sync/applied']);

pending is a GET, so it needs no exemption. I throttle mark to 120 per minute because users can tap repeatedly, and login to 10 per minute because the owner requested a single password with no username for this page, so protection against password guessing relies mainly on throttling.

Two auth layers, clearly separated

The first layer is machine-to-machine, using a shared secret in a header with a timing-safe comparison. The second is for people opening the website, using the single password the owner requested and remembering it with a cookie that stores an hmac, not the password itself.

private const SESS = 'attsync_ok';

private function authed(Request $r): bool
{
    if ($r->session()->get(self::SESS)) {
        return true;
    }
    // cookie จำการ login (เข้ารหัสโดย EncryptCookies) — เปลี่ยนรหัสเมื่อไหร่ token เก่าใช้ไม่ได้ทันที
    $c = (string) $r->cookie(self::SESS, '');
    if ($c !== '' && hash_equals($this->token(), $c)) {
        $r->session()->put(self::SESS, true);
        return true;
    }
    return false;
}

private function token(): string
{
    $secret = (string) config('attsync.password_hash') ?: (string) config('attsync.password');
    return hash_hmac('sha256', 'attsync|' . $secret, (string) config('app.key'));
}

private function apiKeyOk(Request $r): bool
{
    $key = (string) config('attsync.key');
    return $key !== '' && hash_equals($key, (string) $r->header('X-API-KEY'));
}

// config/attsync.php
return [
    'key'           => env('ATTSYNC_KEY', ''),            // shared secret ให้ on-prem (header X-API-KEY)
    'password'      => env('ATTSYNC_PASSWORD', ''),
    'password_hash' => env('ATTSYNC_PASSWORD_HASH', ''),  // ถ้าตั้ง hash จะใช้ hash ก่อน
    'remember_days' => (int) env('ATTSYNC_REMEMBER_DAYS', 30),
];

A useful side effect of deriving the token from the password is that changing the password immediately invalidates old cookies on every device. There is no session table to clean up, and using app.key as the salt prevents cookies from being copied across machines or sites for reuse. I use hash_equals() in both places instead of ==, because ordinary string comparison leaks timing information that can be used to guess one byte at a time.

The read path: ingest that can be retried any number of times

public function ingest(Request $r)
{
    if (!$this->apiKeyOk($r)) abort(401, 'unauthorized');

    $body = $r->json()->all();
    $date = (string) ($body['date'] ?? '');
    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date) || empty($body['periods'])) {
        return response()->json(['ok' => false, 'error' => 'bad payload'], 400);
    }

    $this->ensureTable();
    $stat = $body['stat'] ?? [];
    $this->conn()->table('att_day')->updateOrInsert(
        ['d' => $date],
        [
            'active_total' => (int) ($body['active_total'] ?? 0),
            'cnt_full'     => (int) ($stat['full'] ?? 0),
            'cnt_partial'  => (int) ($stat['partial'] ?? 0),
            'cnt_absent'   => (int) ($stat['absent'] ?? 0),
            'gen_ts'       => (int) ($body['gen'] ?? time()),
            'payload'      => json_encode($body, JSON_UNESCAPED_UNICODE),
            'updated_at'   => $this->nowTh(),
        ]
    );

    return response()->json(['ok' => true, 'date' => $date, 'workers' => count($body['workers'] ?? [])]);
}

updateOrInsert on the key d is what allows unlimited retries. My nowTh() is gmdate('Y-m-d H:i:s', time() + 25200), forcing UTC+7 directly instead of relying on the host's timezone, because the cloud machine and the factory machine may have different tz settings, and a seven-hour offset will surface later as a retention calculation bug.

Why idempotency matters — a requirement for connections that can drop

Idempotent literally means "repeating the operation gives the same result." That sounds theoretical, but the reason I need it is entirely physical. The connection between the factory machine and the cloud is residential internet. It can drop at any time, and there are three points where a drop has completely different consequences.

The last two are the problem, because the internal machine has actually done the work, but the cloud still sees a pending queue entry. On the next run, the internal machine fetches the same work again. If the code doing the work uses a plain INSERT, it immediately creates duplicate data. But if the operation is "set the value to X," the result stays the same however many times it runs.

So I enforce three rules throughout the system. First, every write must use updateOrInsert or set a target value, rather than insert or increment. Second, there must be a real, unique natural key, which here is (day, period, person ID). Third, an ack must conditionally mark a timestamp instead of deleting the entry. Together, these three rules mean that if an ack is lost in transit, the next run simply repeats the work with exactly the same result, and nobody has to repair anything manually.

Compare-and-set acks — keeping old acks from losing new commands

Fetching the queue is straightforward: read rows where applied_at is still NULL:

/** GET /att-sync/pending — คิวที่ยังไม่ได้ทำ (เรียงเก่า->ใหม่) */
public function pending(Request $r)
{
    if (!$this->apiKeyOk($r)) abort(401, 'unauthorized');
    $this->ensureTable();

    $rows = $this->conn()->table('att_edit')
        ->select('id', 'd', 'period', 'employee_no', 'full_name', 'status', 'created_at')
        ->whereNull('applied_at')->orderBy('id')->limit(200)->get();

    return response()->json(['ok' => true, 'edits' => $rows]);
}

The ack path needs more thought:

/**
 * POST /att-sync/applied — on-prem ยืนยันว่าเขียนตารางจริงแล้ว
 * body: {"acks":[{"id":1,"status":"late"}, ...]}
 * มาร์คเฉพาะแถวที่ status ยังตรงกับตอนที่หยิบไป — ถ้าผู้ใช้กดแก้ซ้ำระหว่างทาง แถวนั้นค้างคิวไว้ทำรอบหน้า
 */
public function applied(Request $r)
{
    if (!$this->apiKeyOk($r)) abort(401, 'unauthorized');
    $this->ensureTable();

    $acks = $r->json()->all()['acks'] ?? [];
    $n = 0;
    foreach ((array) $acks as $a) {
        $id = (int) ($a['id'] ?? 0);
        $st = (string) ($a['status'] ?? '');
        if ($id <= 0 || !in_array($st, ['present', 'late', 'absent'], true)) continue;
        $n += $this->conn()->table('att_edit')
            ->where('id', $id)->where('status', $st)->whereNull('applied_at')
            ->update(['applied_at' => $this->nowTh()]);
    }
    // เก็บกวาดคิวเก่าที่ทำไปแล้วเกิน 30 วัน
    $this->conn()->table('att_edit')->whereNotNull('applied_at')
        ->where('applied_at', '<', gmdate('Y-m-d H:i:s', time() + 25200 - 30 * 86400))->delete();

    return response()->json(['ok' => true, 'acked' => $n]);
}

The line to look at is ->where('id', $id)->where('status', $st)->whereNull('applied_at'). Those three conditions are the entire compare-and-set operation. If an ack arrives and the row's status no longer matches what the internal machine picked up, the update affects zero rows. That row stays queued for the next run.

Walk through this case. The user taps "Present," and a queue entry is created as present. The internal machine fetches it at 10:00:00 and finishes writing to the internal database at 10:00:03. In the meantime, at 10:00:02, the user changes their mind and taps "Late," overwriting the same row with late and resetting applied_at to NULL. When the ack for present arrives at 10:00:04, where('status','present') no longer matches, so the ack quietly does nothing. The row remains queued as late, and the internal machine picks up late on the next run. The final result matches the user's intention exactly.

The write path: mark with server-side guards

Tapping the button on the phone reaches this endpoint. The thing to watch is that the client must never be trusted. Hiding a button on the page is not protection. The server must check again.

public function mark(Request $r)
{
    if (!$this->authed($r)) return response()->json(['ok' => false, 'error' => 'auth'], 401);
    $this->ensureTable();

    $date   = (string) $r->input('date', '');
    $period = (int) $r->input('period', 0);
    $emp    = preg_replace('/[^0-9]/', '', (string) $r->input('employee_no', ''));
    $status = (string) $r->input('status', '');

    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date) || $emp === ''
        || !in_array($status, ['present', 'late', 'absent'], true)) {
        return response()->json(['ok' => false, 'error' => 'ข้อมูลไม่ครบ'], 400);
    }

    // ตรวจกับ snapshot ล่าสุด (+คิวที่ยังไม่ applied) — กันแก้คนที่ไม่มีจริง / ลบเวลาสแกนจริง
    $row = $this->conn()->table('att_day')->where('d', $date)->first();
    if (!$row) return response()->json(['ok' => false, 'error' => 'ยังไม่มีข้อมูลของวันนี้'], 404);
    $payload = $this->mergePending(json_decode($row->payload, true) ?: [], $date);

    $codes = array_map(fn ($p) => (int) $p['code'], $payload['periods'] ?? []);
    if (!in_array($period, $codes, true)) {
        return response()->json(['ok' => false, 'error' => 'ช่วงเวลาไม่ถูกต้อง'], 400);
    }
    $worker = null;
    foreach ($payload['workers'] ?? [] as $w) {
        if ((string) $w['no'] === $emp) { $worker = $w; break; }
    }
    if (!$worker) return response()->json(['ok' => false, 'error' => 'ไม่พบคนงานคนนี้'], 404);

    $cur = $worker['p'][(string) $period] ?? null;                 // null | 'HH:MM' | present | late
    $isManual = in_array($cur, ['present', 'late'], true);
    if ($status === 'absent' && !$isManual) {
        return response()->json(['ok' => false, 'error' => 'ยกเลิกได้เฉพาะรายการที่แก้มือไว้ (เวลาสแกนจริงลบไม่ได้)'], 409);
    }
    if ($status !== 'absent' && $cur !== null && !$isManual) {
        return response()->json(['ok' => false, 'error' => 'คนนี้สแกนจริงแล้ว (' . $cur . ') ไม่ต้องแก้'], 409);
    }

    // 1 แถวต่อ วัน/ช่วง/คน — แก้ซ้ำ = ทับ + re-queue (applied_at กลับเป็น NULL)
    $this->conn()->table('att_edit')->updateOrInsert(
        ['d' => $date, 'period' => $period, 'employee_no' => $emp],
        ['status' => $status, 'full_name' => $worker['name'] ?? null,
         'created_at' => $this->nowTh(), 'applied_at' => null]
    );

    return response()->json([
        'ok'      => true,
        'pending' => $this->conn()->table('att_edit')->where('d', $date)->whereNull('applied_at')->count(),
        'data'    => $this->payloadFor($date),
    ]);
}

My rule is that data recorded by the endpoint devices themselves cannot be deleted from the cloud. Only entries manually added by a person can be canceled. Anything beyond that gets a 409 response with a Thai message the user can understand, rather than a bare error code.

An optimistic UI that still tells the truth

Users need to see a result immediately after tapping a button, or they will tap again. But showing success before the change reaches the factory machine would be lying. My approach is to merge the unacknowledged queue into the snapshot when reading it and include a pending flag.

/** รวมคิวที่ยังไม่ applied ลงใน payload (ให้ UI เห็นผลทันทีระหว่างรอ on-prem) */
private function mergePending(array $payload, string $date): array
{
    $edits = $this->conn()->table('att_edit')->where('d', $date)->whereNull('applied_at')->orderBy('id')->get();
    if ($edits->isEmpty()) return $payload;

    foreach ($edits as $e) {
        $emp = (string) $e->employee_no;
        $pc  = (int) $e->period;

        foreach ($payload['periods'] ?? [] as $i => $p) {
            if ((int) $p['code'] !== $pc) continue;
            $scan = $p['scan'] ?? [];
            $miss = $p['miss'] ?? [];
            $name = $e->full_name;
            $g    = null;
            foreach ($miss as $m) if ((string) $m['no'] === $emp) { $name = $m['name']; $g = $m['g'] ?? null; }
            foreach ($scan as $s) if ((string) $s['no'] === $emp) { $name = $s['name']; $g = $s['g'] ?? null; }

            $scan = array_values(array_filter($scan, fn ($s) => (string) $s['no'] !== $emp));
            $miss = array_values(array_filter($miss, fn ($m) => (string) $m['no'] !== $emp));

            if ($e->status === 'absent') {
                $miss[] = ['no' => $emp, 'name' => $name, 'g' => $g, 'pending' => 1];
            } else {
                $scan[] = ['no' => $emp, 'name' => $name, 't' => null, 'g' => $g, 'm' => $e->status, 'x' => 0, 'pending' => 1];
            }

            $payload['periods'][$i]['scan']       = $scan;
            $payload['periods'][$i]['miss']       = $miss;
            $payload['periods'][$i]['count_scan'] = count($scan);
            $payload['periods'][$i]['count_miss'] = count($miss);
        }

        foreach ($payload['workers'] ?? [] as $i => $w) {
            if ((string) $w['no'] !== $emp) continue;
            $payload['workers'][$i]['p'][(string) $pc]  = $e->status === 'absent' ? null : $e->status;
            $payload['workers'][$i]['pd'][(string) $pc] = 1;   // ธง "รอซิงก์"
        }
    }
    $payload['pending'] = $edits->count();
    return $payload;
}

What I like most about this approach is that the snapshot in the DB is never modified. The merge happens only when reading. Once the factory machine pushes the actual data back up and the queue has been acknowledged, mergePending has nothing to merge on the next run. The value the user sees automatically becomes the value from the source. I do not have to write a single line of logic to "clear the optimistic state."

The frontend also polls more frequently only while there are pending queue entries:

// รีเฟรชเอง: มีคิวค้าง = ทุก 20 วิ (รอผลจาก on-prem) · ปกติวันนี้ = ทุก 2 นาที (คงการ์ดที่เปิดไว้)
setInterval(async ()=>{
  if(BUSY || !DATA) return;
  const waiting = DATA.pending>0, t=Date.now();
  if(t-LASTPOLL < (waiting?20000:120000)) return;
  if(!waiting && $('#date').value!==todayISO()) return;
  LASTPOLL=t;
  const o=openCards(), sc=window.scrollY;
  await load($('#date').value);
  o.forEach(c=>{const el=document.querySelector('.pcard[data-c="'+c+'"]'); if(el) el.classList.add('open');});
  window.scrollTo(0,sc);
}, 5000);

The factory side: one script handling both directions

This single script both pushes snapshots up and fetches queued commands to apply locally. It can run from cron every minute and at the end of the scheduled data-fetching cron jobs.

/**
 * push_cloud.php — ส่ง snapshot รายวันขึ้นคลาวด์ + ดึงคิวแก้ไขลงมาทำ
 *
 * ปลายทาง : POST https://example.com/att-sync/ingest  (header X-API-KEY)
 *
 * ขาแก้ไข (เว็บ -> on-prem): คลาวด์เขียนเข้า MariaDB ของ on-prem ตรงๆ ไม่ได้ (อยู่หลัง NAT)
 *   1. GET  /att-sync/pending  -> คิวที่ผู้ใช้กดแก้ไว้
 *   2. เขียนลงตารางจริงผ่าน AttendanceService::setManual()
 *   3. POST /att-sync/applied {acks:[{id,status}]} -> เว็บมาร์คว่าทำแล้ว
 *   4. push snapshot ของวันที่ถูกแก้กลับขึ้นเว็บทันที (--force)
 *  ทุกขั้น idempotent: ack ไม่ถึง/เน็ตหลุด -> รอบหน้าทำซ้ำ ผลเท่าเดิม
 *
 * Usage:
 *   php push_cloud.php                 # วันนี้ (+ ดึงคิวแก้ไขมาทำ)
 *   php push_cloud.php 2026-07-28      # วันที่ระบุ
 *   php push_cloud.php --days=60       # ย้อนหลัง N วัน (backfill)
 *   php push_cloud.php --force         # ส่งแม้ข้อมูลไม่เปลี่ยน (ข้าม hash cache)
 *   php push_cloud.php --no-edits      # ไม่ดึงคิวแก้ไข (push อย่างเดียว)
 */
require_once __DIR__ . '/../vendor/autoload.php';

$URL   = getenv('ATTSYNC_URL') ?: 'https://example.com/att-sync/ingest';
$KEY   = getenv('ATTSYNC_KEY') ?: '';
$STATE = '/var/lib/att/push_cloud.state.json';
$BASE  = preg_replace('#/ingest$#', '', $URL);          // .../att-sync

The main loop uses a state hash to avoid unnecessary repeat requests, because cron runs every minute all day. If the data has not changed, there is no need to bother the website.

// ─── ขาแก้ไข: ดึงคิวจากเว็บมาเขียนลงตารางจริง ────
$forceDates = [];
if ($edits && !$dry) {
    $forceDates = syncEdits($BASE, $KEY, $attSvc, $config);
    foreach ($forceDates as $d) if (!in_array($d, $dates, true)) $dates[] = $d;
}

$sent = 0;
foreach ($dates as $date) {
    $payload = buildPayload($date, $db, $attSvc, $config, $syncAt);
    $hash    = md5(json_encode($payload['periods'], JSON_UNESCAPED_UNICODE));

    if (!$force && !in_array($date, $forceDates, true) && ($state[$date] ?? null) === $hash) {
        echo "[" . date('Y-m-d H:i:s') . "] {$date}: ไม่เปลี่ยน ข้าม\n";
        continue;
    }

    [$code, $resp] = post($URL, $KEY, $payload);
    if ($code === 200) {
        $state[$date] = $hash;
        $sent++;
    }
}

@file_put_contents($STATE, json_encode($state, JSON_UNESCAPED_UNICODE));

Here is the full queue-fetching and processing path.

/**
 * ดึงคิวแก้ไขจากเว็บ -> setManual() -> ack กลับ
 * @return string[] วันที่ที่ถูกแก้ (ต้อง push ซ้ำแบบ force)
 */
function syncEdits(string $base, string $key, $attSvc, $config): array
{
    [$code, $body] = httpGet($base . '/pending', $key);
    if ($code !== 200) {
        if ($code !== 0) echo '[' . date('Y-m-d H:i:s') . "] pending: http={$code}\n";
        return [];
    }
    $j = json_decode((string) $body, true);
    $rows = $j['edits'] ?? [];
    if (!$rows) return [];

    $acks = [];
    $days = [];
    foreach ($rows as $e) {
        $d   = (string) ($e['d'] ?? '');
        $pc  = (int) ($e['period'] ?? 0);
        $emp = preg_replace('/[^0-9]/', '', (string) ($e['employee_no'] ?? ''));
        $st  = (string) ($e['status'] ?? '');
        $id  = (int) ($e['id'] ?? 0);
        if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $d) || !isset($config->periods[$pc])
            || $emp === '' || !in_array($st, ['present', 'late', 'absent'], true) || $id <= 0) {
            continue;   // ข้อมูลเพี้ยน — ไม่ ack ปล่อยให้ค้างคิวไว้ตรวจ
        }
        try {
            $attSvc->setManual($d, $pc, $emp, $st);   // present|late = บันทึก · absent = ลบ override
            $acks[] = ['id' => $id, 'status' => $st];
            if (!in_array($d, $days, true)) $days[] = $d;
        } catch (\Throwable $ex) {
            echo '[' . date('Y-m-d H:i:s') . '] แก้ไม่สำเร็จ id=' . $id . ' ' . $ex->getMessage() . "\n";
        }
    }
    if ($acks) {
        [$c2, $r2] = post($base . '/applied', $key, ['acks' => $acks]);
        if ($c2 !== 200) return $days;   // ack ไม่ผ่าน -> รอบหน้าทำซ้ำ (idempotent)
    }
    return $days;
}

Notice the continue; where validation fails. I deliberately do not ack rows with invalid data, leaving them queued for someone to inspect instead of marking work as done when it was not. A queue entry that remains pending unusually long is a signal to investigate. If I silently ack it away, that signal disappears too.

The HTTP helper has one line that caused trouble with the WAF.

function post(string $url, string $key, array $payload): array
{
    $ch = curl_init($url);
    curl_setopt_array($ch, [
        CURLOPT_POST           => true,
        CURLOPT_POSTFIELDS     => json_encode($payload, JSON_UNESCAPED_UNICODE),
        CURLOPT_HTTPHEADER     => ['Content-Type: application/json', 'Accept: application/json', "X-API-KEY: {$key}"],
        CURLOPT_USERAGENT      => 'Mozilla/5.0 (att pusher on-prem)',   // WAF บล็อก UA แปลกๆ
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_TIMEOUT        => 30,
        CURLOPT_CONNECTTIMEOUT => 10,
    ]);
    $resp = curl_exec($ch);
    $code = (int) curl_getinfo($ch, CURLINFO_HTTP_CODE);
    if ($resp === false) $resp = 'curl error: ' . curl_error($ch);
    curl_close($ch);
    return [$code, $resp];
}

The trap I most want to warn about: cron silently disappearing when the system overwrites it

The existing system on the factory machine lets users configure operating time slots from the website. Behind the scenes, it regenerates the entire crontab block every time those settings change. The code that removes the old entries looks like this.

private const CRON_BEGIN = '# ATT_PERIODS_AUTO_BEGIN';
private const CRON_END   = '# ATT_PERIODS_AUTO_END';

// อ่าน crontab ปัจจุบัน + ลบ block marker เก่า
$current = (string) shell_exec("{$crontabCmd} -l 2>/dev/null");
$current = preg_replace(
    '/' . preg_quote(self::CRON_BEGIN, '/') . '.*?' . preg_quote(self::CRON_END, '/') . '\n?/s',
    '',
    $current
);
// ลบ cron ที่อยู่นอก marker ด้วย — แต่เฉพาะที่ชื่อ script อยู่ในลิสต์ managed
$managedScripts = ['pull_period_1.php', 'pull_period_2.php', 'pull_period_3.php', 'pull_period_4.php',
                   'line_notify.php', 'line_daily_summary.php', 'hik_sleep_mode.php'];
$kept = [];
foreach (explode("\n", $current) as $line) {
    $skip = false;
    foreach ($managedScripts as $s) {
        if (strpos($line, $s) !== false) { $skip = true; break; }
    }
    if (!$skip) $kept[] = $line;
}
$current = implode("\n", $kept);
$newCrontab = rtrim($current, "\n") . "\n" . self::CRON_BEGIN . "\n" . implode("\n", $generated) . "\n" . self::CRON_END . "\n";

// ถ้าไม่ใช่ root -> ใช้ sudo crontab (Apache รันเป็น www-data, cron จริงอยู่ที่ root)
$isRoot = function_exists('posix_geteuid') && posix_geteuid() === 0;
$crontabCmd = $isRoot ? '/usr/bin/crontab' : 'sudo -n /usr/bin/crontab';

There are two passes that can delete someone else's entries. The first deletes everything between the markers. The second also deletes lines outside the markers if the filename matches the managed list.

So this is where I actually put it: outside the markers, with a filename that does not clash with the list.

# root crontab — 2 บรรทัดนี้อยู่ "นอก" marker ที่ระบบ regenerate
* * * * * /usr/bin/php /var/www/html/att/cron/push_cloud.php >> /var/log/att/push_cloud.log 2>&1
20 0 * * * /usr/bin/php /var/www/html/att/cron/push_cloud.php --days=2 >> /var/log/att/push_cloud.log 2>&1

Another change I liked was calling the push at the end of the scheduled data-fetching job instead of scheduling a push cron at fixed times. It runs as a separate process so a failure will not affect the main job.

// ท้าย cron/pull_period.php : push snapshot แยก process ไม่ให้ error กระทบงานหลัก
$pushCmd = sprintf('/usr/bin/php %s %s >> %s 2>&1 &',
    escapeshellarg(__DIR__ . '/push_cloud.php'),
    escapeshellarg($date),
    escapeshellarg("{$logDir}/push_cloud.log")
);
@exec($pushCmd);

As a result, cloud uploads always follow the time slots configured by the user. When the user changes the schedule, I do not have to go back and edit the push cron.

Another bug with no error, but wrong numbers

Initially, the cloud website counted "present all day" by checking each period and requiring attendance in every one. The result showed only one person present all day, even though nearly everyone had attended. The reason was that almost nobody checked in during the first period; most arrived in time for the second. The factory system already grouped the morning as "period 1 or 2," but the website recalculated the count without that grouping, so the numbers on the two sides did not match.

// จับกลุ่มช่วงแบบเดียวกับที่ on-prem สรุป: เช้า = ช่วง 1 หรือ 2 · ช่วง 3 · ช่วง 4
const GRP=[['1','2'],['3'],['4']].map(g=>g.filter(c=>PST[c]!==undefined)).filter(g=>g.length);
const endedG=GRP.filter(g=>g.every(c=>PST[c]==='end'));
const st={full:0,partial:0,absent:0};
(d.workers||[]).forEach(w=>{
  if(!endedG.length){ w.st2='wait'; return; }
  const came=endedG.filter(g=>g.some(c=>w.p&&w.p[c])).length;
  w.st2 = came===endedG.length?'full':(came===0?'absent':'partial');
  st[w.st2]++;
});

I also changed the label while the day was still in progress. Otherwise, at nine in the morning, the website would say that almost everyone had been absent all day.

const openDay = isToday && (d.periods||[]).some(p=>pstate(p)!=='end'); OPEN=openDay;
// tile: 'มาเต็มวัน' -> 'มาครบทุกช่วงที่ผ่านมา' , 'ขาดทั้งวัน' -> 'ยังไม่มาเลย'
if(openDay) h+='<div class="hint">วันนี้ยังไม่จบ — นับเฉพาะช่วงที่ผ่านไปแล้ว</div>';

The lesson is that if numbers are summarized in two places, the rules must match exactly. Otherwise, the result is a bug with no error, no exception, and no log, but the numbers on the screen are wrong, and several days may pass before anyone points it out.

Actual measurements after deployment

When testing the actual write path, I tapped the button on my phone to change the status of one entry, then counted the rows in the table on the factory machine. The count increased by one within a minute. When I canceled it from the phone, the count returned to its original value, with no leftover data. Repeated tests gave the same result every time, which is what should happen if idempotency is implemented correctly.

Applying this structure to other projects

1. Create the cloud mirror table first: one row per unit of time, store the payload as JSON, and use updateOrInsert on a single key so requests can be repeated without limit.
2. Create a command queue table with UNIQUE on the natural key of the "intention," rather than only an auto increment key, and use applied_at to indicate status.
3. Expose three endpoints for the internal machine: ingest, pending, and applied. Protect them with a shared secret in a header, compare with hash_equals, and remember to exempt the two POST endpoints from CSRF.
4. Write one script on the internal machine to handle both directions, with a state hash to avoid repeat requests and force and backfill modes for installation and troubleshooting.
5. Place cron entries beyond the reach of other systems that might overwrite them, then actually check with crontab -l after a user has changed settings in the existing system once.
6. Build a UI that indicates changes are not yet confirmed by merging the queue when reading and adding a pending flag. Never overwrite the snapshot in the database.

I think this pattern applies much more broadly than the project I worked on. Storefront POS systems, machines that keep logs locally, warehouse inventory systems, or anything where the actual data lives in a LAN and needs to be viewed from outside can all use the same structure. The only things that need changing are the shape of the payload and the guard rules for incoming commands. For anyone with a full-time administrator who really needs full remote access, a VPN is still the more appropriate answer. Choose according to the situation. If I have left anything out or made a typo, I will come back and correct it later.