fos_imported_at / photos_first_cached_at — canonical "first import into FOS" (customer-delivery) timestamp + Uploaded→Delivered lag

If Approach A: are you OK running a Laravel 4.2 migration to add a column on the ~1M-row FOS orders table (online/INSTANT DDL in a low-traffic window) plus a ~3-line edit to the hot import path in Order.php, shipped via staging→cherry-pick→master→Forge and coordinated with Daniel? And is the column name photos_first_cached_at good?

FOS-first (add photos_first_cached_at to FOS + null-guard root-fix + backfill there, DAP copies) vs DAP-only (DAP derives from FOS audit_logs, no FOS change)? A is canonical/permanent but needs a FOS prod deploy; B touches no legacy code but leaves the overwrite bug in place. Recommend A given you liked it and the brief lives in the FOS project.

# fos_imported_at — canonical "first import into FOS" (customer-delivery) timestamp ## Goal Give every order an accurate **"first time its photos were imported/cached into FOS"** timestamp — the real *customer-delivery* moment (FOS finished caching the photos and fired the "Order Ready" alert). Surface it on DAP as `orders.fos_imported_at`, and report the **Uploaded → Delivered** lag on the /admin Lab Dashboard. This is the "Imported/Delivered" milestone from Solo todo **514** (active). It is DISTINCT from `uploaded_at` and must not replace it: - `uploaded_at` = scans finished uploading to *our* cloud storage (Dropbox+Backblaze). NOT delivery. (todo 515) - `fos_imported_at` = FOS imported/cached the photos for the customer + alerted them. The real "delivered" moment. (todo 514) ## The core problem (confirmed from FOS source) FOS `orders.photos_cached_at` holds the **LAST** successful cache, not the first — it is overwritten on every re-import. `~/Documents/code/tdr/thedarkroom/laravel/app/models/Order.php`, in `asyncImportPhotosFromObjectStorage()`: - **Line 1893**: `$this->photos_cached_at = Carbon::now();` — UNCONDITIONAL, inside the `if($thumbs_failed === 0)` block (line 1887). No null-guard → rewritten to "now" on every re-import that caches with zero thumb failures. - **Line 1941**: every image-import run logs `AuditLog::createEntry('order.import-images', $message, …)`. - Prefix `'Reimport attempted.'` when `import_attempts > 0` at entry, else `'Import attempted.'` (line 1931). `import_attempts` is incremented at line **1617** *before* logging, so the FIRST import logs `Import count: 1`. - The success branch (`count($images) > 0`) ALWAYS emits a **`"N photos added this run"`** line (1934). The no-images branch (line 1946–1958) also logs `'order.import-images'` but says `"0 files found"` and has NO "photos added this run" line. - A **`"N photos failed thumbs"`** line appears ONLY when thumbs failed (1935–1936) — i.e. when `photos_cached_at` was NOT set. **Therefore the exact signal for a run that set `photos_cached_at`** = event `order.import-images` AND message `LIKE '%photos added this run%'` AND `NOT LIKE '%failed thumbs%'`. The FIRST such row's `created_at` = the true first import/delivery. ### Audit table facts - Table `audit_logs` (migration `2020_02_25_162721_create_audit_logs_table.php`): `id, event, message, elapsed_time, customer_id, order_id, created_at, updated_at`. Written via `AuditLog::createEntry($event, $message, $elapsed, $order_id, $customer_id)`. **No pruning/retention job** — history is intact. - **Audit epoch = 2020-02-25.** Orders cached before then have `photos_cached_at` but NO import-images audit rows → must fall back to `photos_cached_at`. - Join keys on FOS `orders`: `id` (PK), `woo_id` (unique int), `labworks_id` (nullable, indexed int). - ⚠️ FOS already has an `old_photos_cached_at` column (migration `2018_05_31_...`) — inspect what it holds before naming a new column, and don't collide. ### Never-cached orders resolve correctly Orders whose imports all failed thumbs (e.g. labworks 2755992: 20 reimports, all "1 photos failed thumbs / 0 added", `photos_cached_at` NULL) produce NO qualifying audit row → `fos_imported_at` stays NULL. Correct — they were never delivered. ### Local testability gap DAP's `fos` connection points at `tdr_laravel_staging` locally. Probe (2026-07-01): 1,534 orders, 393 with `photos_cached_at`, but **ZERO `order.import-images` audit rows** (staging didn't run the real import flow; example orders absent). The audit-correction path can only be validated against **production FOS**. Build/unit-test the logic locally against the fallback path; dry-run (read-only) against prod FOS for the audit path. ## Key simplifying insight — "lock once" Going forward, DAP does NOT need audit logs: capture `fos_imported_at = photos_cached_at` at the **first non-null observation** and never update it again. A later re-import can't corrupt an already-locked value. The audit-log archaeology (MIN of first successful cache) is required ONLY for the one-time **historical backfill** of orders already re-imported before we started locking. ## Two approaches ### Approach A — FOS-first (recommended; why this brief lives in tdr-thedarkroom) Make "first cache" canonical in the system that owns the fact. 1. Add `orders.photos_first_cached_at` to FOS (nullable timestamp, indexed). 2. Root-fix the overwrite at `Order.php:1893` with a null-guard: `if ($this->photos_first_cached_at === null) $this->photos_first_cached_at = Carbon::now();` inside the existing success block. ~3 lines; permanently correct for all future orders, no heuristic. 3. One-time FOS-side backfill (local join, no cross-DB): `photos_first_cached_at = MIN(audit_logs.created_at)` over the qualifying import rows per order; fall back to `photos_cached_at` where no audit row exists (pre-2020-02-25). Mirror the `tdr:sync-*` conventions. 4. DAP then just **copies** `FOS.photos_first_cached_at → DAP.orders.fos_imported_at` over the `labworks_id` join — a dumb, cheap, non-fragile sync. No message parsing in DAP. - **Pros:** canonical + reusable (Woo/reports/other consumers), heuristic can't drift (lives with the code that writes the message), DAP job trivial, permanent root fix. - **Cons:** requires a FOS production change — a Laravel 4.2 migration on the ~1M-row `orders` table (consider online/INSTANT DDL + low-traffic window) + editing the hot import path, shipped via the FOS deploy path (staging → cherry-pick to master → Forge; coordinate with Daniel). Hazard: a prior stray PHP-8 reformat of `Order.php` broke parse — keep the diff minimal and 4.2-clean. ### Approach B — DAP-only (fallback, no FOS change) DAP derives everything by reading FOS. Fully in the groove of `tdr:sync-shipped-at`/`sync-received-at`/`sync-uploaded-at` (all DAP-side reads of external DBs; no legacy-system edits). - Going forward: lock-once on first non-null `photos_cached_at` observation (no audit needed). - Historical: `tdr:sync-fos-imported-at` reads FOS `audit_logs` for MIN first-successful-cache (`LIKE '%photos added this run%'` AND NOT `'%failed thumbs%'`), fallback `photos_cached_at`. - **Pros:** zero FOS risk/coordination. **Cons:** the FOS overwrite bug persists for everyone else; message-LIKE heuristic lives in DAP, away from the code that generates it. ## DAP-side work (shared by both approaches) 1. **Migration** — add `orders.fos_imported_at` (nullable timestamp, indexed) to DAP `orders`. Optional small `fos_imported_at_source` (`audit` / `cached_fallback` / `copied`) for dashboard caveats + debugging. (Check whether the neighbor `single_use_only` / lifecycle migrations set a precedent for column grouping.) 2. **Backfill command** `tdr:sync-fos-imported-at` — model EXACTLY on `SyncShippedAt` / `SyncReceivedAt` / `SyncUploadedAt`: - **Direct `DB::table()->where('id',$id)->update([...])`** — NOT `saveQuietly()` (it still bumps `updated_at`; this bug scrambled the order-log sort on the 991k-row shipped_at run — commits `6261`/`1074`). Direct update also creates no revisionable revision (fine for backfill). - Chunk order ids (≤500 per `whereIn`), `--dry-run` / `--limit=N` / `--all`, never overwrite an existing non-null `fos_imported_at` (idempotent). - Approach A: 1 cross-DB query per chunk (copy `photos_first_cached_at`). Approach B: 2 queries per chunk (FOS orders for `photos_cached_at`; audit aggregate for first cache) — no per-order N+1. - Join on `labworks_id` (DAP `woo_id` is still empty — added 2026-06-11, unpopulated); structure so it can switch to `woo_id` later. 3. **Schedule** incrementally (fills `WHERE fos_imported_at IS NULL`), like the other `tdr:sync-*`. Value is immutable once set → cheap. ## Dashboard integration — USE THE NEW ARCHITECTURE ⚠️ The turnaround card was refactored ~2026-07-01 (by codex, possibly still uncommitted / NOT yet on master — this branch is off `692682e`, the 6-17 tip). It moved from live inline `turnaroundMetrics()` + `COALESCE` `*_MOMENT_SQL` constants (now REMOVED) to a **cached, strict-timestamp** service: - `app/Services/DashboardTurnaroundMetrics.php` + `app/Console/Commands/CacheDashboardTurnaroundMetrics.php` (scheduled every 5 min), strict timestamps only, no fallback, no sample cap, across 5 windows; Livewire `Dashboard` reads the cache. Add an **"Uploaded → Delivered"** phase (`uploaded_at → fos_imported_at`) to THAT service — do not resurrect the old COALESCE method. Coordinate with / rebase onto that work before building. - **Gate:** the lag chart needs `uploaded_at` coverage, which is still sparse (~0.6%, forward-filled since 2026-06-17). `SyncUploadedAt` backfill exists but was written uncommitted and not yet run on prod (todo 515). Run it first for historical lag. - Also note the related **DAP/FOS Gaps** card (`fosImportGaps()`, later merged into Flagged Orders) already compares recent DAP uploaded orders vs FOS `photos_cached_at` by `labworks_id` — reuse its join/config patterns. ## Order Progress timeline (todo 518) When the column lands, add an "Imported by FOS / Delivered" milestone to `resources/views/livewire/order-change-history.blade.php` after Uploaded/Shipped. Because the sync writes via direct DB update (no revision, like `shipped_at`), surface it on the Progress timeline, not the revision-based change history. ## Definition precision (open question) "Delivered" candidates, ~seconds apart in the audit trail: (a) first successful photo cache = first `order.import-images` success row / `photos_cached_at` first-set; (b) the `notification.created "Order Ready"` event. Recommend (a) — always present, cleaner, matches `photos_cached_at` semantics. ## Related prior art - Solo todos (project 69): **514** (Imported/Delivered stage + Uploaded→Imported lag — the product spec), **518** (timeline/change-history), **515** (uploaded_at backfill + semantics), **521** (dead-accurate cached turnaround, `received_at`), **513** (AppSettings thresholds), **516** (event-driven Labworks sync). - Commits/files (DAP): `SyncShippedAt.php`, `SyncReceivedAt.php`, `SyncUploadedAt.php`; commit `6261` "Stop tdr:sync-shipped-at from bumping orders.updated_at"; `DashboardTurnaroundMetrics.php` + `CacheDashboardTurnaroundMetrics.php`; `app/Livewire/Dashboard.php`; `fosImportGaps()`. - FOS: `laravel/app/models/Order.php` (`asyncImportPhotosFromObjectStorage` ~1548–1990, photos_cached_at 1893, audit 1941/1958), `app/models/AuditLog.php`, migration `2020_02_25_162721_create_audit_logs_table.php`, existing `old_photos_cached_at` (`2018_05_31_...`). - FOS deploy: repo `The-Darkroom/thedarkroom` (moved from tracysurf; dual remotes), staging→cherry-pick→master→Forge, coordinate w/ Daniel; local `thedarkroom.test` PHP 7.4 (Herd). ## Suggested sequence 1. Decide Approach A vs B (see open questions). 2. DAP migration for `fos_imported_at` (+ source col). 3. Implement the going-forward lock-once (cheap, unblocks new orders immediately). 4. Historical backfill (A: FOS `photos_first_cached_at` + copy; B: DAP audit derivation). Dry-run against prod FOS first. 5. Add "Uploaded → Delivered" to `DashboardTurnaroundMetrics` (after the refactor is merged + `uploaded_at` backfilled). 6. Add the Order Progress timeline milestone (todo 518).

Establish an accurate "first time this order's photos were imported/cached into FOS" timestamp (the real customer-delivery moment), expose it on DAP as orders.fos_imported_at, and report the Uploaded→Delivered lag on the /admin Lab Dashboard. Core problem: FOS orders.photos_cached_at is OVERWRITTEN on every successful re-import, so it holds the LAST cache, not the first. Full spec follows.