# UTC Timezone Migration Plan

**Status:** Backend code IMPLEMENTED (2026-06-11) — DB migration NOT yet run; frontend/mobile NOT yet done  
**Risk level:** HIGH — data migration is irreversible without a restore  
**Estimated effort:** 2–3 days (migration + code + frontend + testing)  
**Must be done atomically:** backend deploy, DB migration, and frontend/mobile release in a single maintenance window

> **Implementation note (2026-06-11):** All backend steps below are implemented and pass the test suite.
> The data-shift migration (`database/migrations/2026_06_11_000001_convert_datetimes_to_utc.php`) is
> **guarded to MySQL only** (`DB::getDriverName() !== 'mysql'` returns early) so the SQLite test DB is
> unaffected — it starts fresh in UTC. It also **shifts a table/column only if it exists** on the target
> DB (`Schema::hasTable` / `Schema::hasColumn`), so environments missing optional feature tables (e.g. a
> dev DB without `vilogi_journal_entries`) are handled gracefully instead of erroring mid-run. The whole
> shift runs in one `DB::transaction()`, so a failure rolls back with zero partial shift.
> Two schema facts corrected against the original audit during implementation: the cron table is **`cron`**
> (singular, has `timestamps()`), and **`meter_resets`** has only `created_at` (no `updated_at`, and no
> `reset_date` column exists). `deliver_on` was `NOT NULL`, so the deliver_at migration also makes it
> nullable. The frontend/mobile sections remain TODO.

---

## Background

The app currently runs with `config/app.php timezone = 'Indian/Mauritius'` (UTC+4). Every timestamp written to the database is UTC+4, not UTC. Every `Carbon::now()`, `date()`, `now()` call returns UTC+4.

This is correct for a single-region Mauritius deployment. It becomes wrong the moment a tenant operates in any other timezone — their due dates, bill periods, mail delivery schedules, and reading timestamps all shift.

**The standard:** store UTC everywhere, convert to tenant timezone at the display layer only.

---

## Audit Summary

The full codebase sweep identified:

| Category | Count |
|---|---|
| Datetime/timestamp DB columns (across 14+ tables) | 31 |
| `date()` PHP calls | 20+ |
| `Carbon::` usages | 60+ |
| `strtotime()` calls | 8 |
| `now()` / `today()` / `tomorrow()` calls | 40+ |
| Date-range DB queries (`whereBetween`, `whereDate`) | 17 |
| `->format()` / `->toDateString()` calls | 30+ |

---

## What is and is not affected

### Affected — DATETIME columns with a time component
These are stored as `"2026-06-10 08:00:00"` (UTC+4). After migration they must read `"2026-06-10 04:00:00"` (UTC). These are the high-risk columns.

| Table | Column | Billing impact |
|---|---|---|
| `archived_sessions` | `survey_start_date` | Bill period start — display, queries |
| `archived_sessions` | `survey_end_date` | Bill period end — display, due date base |
| `archived_sessions` | `open_session_date` | Audit trail |
| `archived_sessions` | `updated_at` | Audit |
| `open_sessions` | `survey_start_date` | Active session start |
| `open_sessions` | `survey_end_date` | Active session end |
| `mail_scheduled_histories` | `mail_wants_to_be_delivered` | Mail scheduling |
| `mail_scheduled_histories` | `mail_delievered_on` | Mail delivery record |
| `mail_reports` | `deliver_on` | Cron mail trigger — **see special handling below** |
| `cron` (singular) | `cron_disturb_date` | Cron schedule |
| `cron` (singular) | `cron_run_date` | Cron schedule |
| `accounting_journal_entries` | `last_attempted_at`, `synced_at`, `reversed_at` | Accounting audit |
| `vilogi_journal_entries` | `last_attempted_at`, `synced_at`, `reversed_at` | Vilogi audit |
| `users` | `last_login`, `password_setup_*` | Auth/security |
| `integration_tokens` | `last_used_at`, `expires_at` | Security |
| `integration_request_logs` | `received_at`, `processed_at` | Audit |
| `meter_resets` | `created_at` | Consumption calculation (table has **no** `updated_at`) |
| All tables | `created_at`, `updated_at` (Eloquent auto) | Audit |

### Not affected — DATE-only columns (no time component)
These are stored as `"2026-06-10"` strings. Timezone does not shift a date string; they are safe.

| Table | Column | Note |
|---|---|---|
| `lots` | `last_reading_date` | Written via `date("Y-m-d")` — date only |
| `archived_sessions` | `created_at` | Declared `$table->date('created_at')` — date string only, no time component; used as due-date base in `LotService` — **do NOT apply timezone conversion** |

### Special case — `mail_reports.deliver_on` → replaced by `deliver_at`

`deliver_on` is declared as `dateTime` but written as a date-only string, causing the MailCron query to be fragile and timezone-unaware. Rather than patching it, this migration is the right moment to redesign mail scheduling entirely.

**New design: `deliver_at DATETIME`**

A new column `deliver_at` stores the exact UTC datetime the mail should be sent. The cron runs every hour and queries `WHERE deliver_at <= NOW() AND is_delivered = 0`. No date-boundary logic, no timezone math in the query.

- `deliver_on` is **deprecated** — not written to for new records, not shifted in the DB migration, kept in the schema for historical rows only
- All new records write `deliver_at` instead (see Step 8)
- `MailCron` queries `deliver_at` instead of `deliver_on` (see Step 7)
- A new `delivery_hour` preference (default `22`) makes the send time tenant-configurable

**Do NOT shift `deliver_on` in the DB migration.** It is deprecated and historical values should be left as-is. Flush all pending rows before migration (see Step 0).

### Not affected — third-party tokens
`password_resets.created_at`, `personal_access_tokens.*` — these expire in minutes/hours; the 4-hour shift is inconsequential and they will self-rotate naturally.

---

## Migration steps

### STEP 0 — Prerequisites (before touching anything)

1. **Full database backup** — verified restorable. Test the restore on staging before proceeding.
2. **Put the app in maintenance mode** — `php artisan down`
3. **Confirm all queued jobs are drained** — no pending mail or Vilogi sync jobs
4. **Flush all pending mail reports** — any `mail_reports` row with `is_delivered = 0` must be cleared before the migration runs. Either trigger `GET /cron-daily-report` to send them, or manually advance their `deliver_on` to a safe post-migration date:
   ```sql
   -- Inspect what's pending
   SELECT id, deliver_on, is_delivered FROM mail_reports WHERE is_delivered = 0;
   -- Option A: mark as delivered (skip sending)
   UPDATE mail_reports SET is_delivered = 1 WHERE is_delivered = 0;
   -- Option B: advance to a date after migration so the new UTC logic picks them up
   -- (only if you want them sent post-migration)
   ```
5. **Confirm `mail_reports` is empty of pending rows** — `SELECT COUNT(*) FROM mail_reports WHERE is_delivered = 0;` must return 0.
6. **Pre-flight double-run check** — record a known `survey_start_date` value from `archived_sessions` before running. After migration it must be exactly 4 hours earlier. If it is already UTC (i.e. the value is already 4 hours less than your baseline), **stop immediately** — the migration has already run and re-running will corrupt data.
   ```sql
   SELECT id, survey_start_date FROM archived_sessions WHERE survey_start_date IS NOT NULL LIMIT 1;
   -- note this value; after migration it should be exactly 4h earlier.
   ```
7. **Tag the current git commit** — `git tag pre-utc-migration`
8. **Snapshot staging** — run the entire migration on staging first; validate for 24 hours before production

---

### STEP 1 — Database: shift all DATETIME columns by −4 hours

Create a new migration: `database/migrations/2026_XX_XX_000001_convert_datetimes_to_utc.php`

> **CLAUDE.md rule:** do not modify committed migrations. This is a new migration.

```php
public function up(): void
{
    // ONE-TIME migration — subtract 4 hours from every DATETIME column to convert UTC+4 → UTC.
    // DATE-only columns (archived_sessions.created_at, lots.last_reading_date,
    // meter_resets.reset_date) carry no time component and are intentionally excluded.
    //
    // ⚠️  Running this migration twice shifts timestamps by -8 hours with no automatic
    // detection. Perform the pre-flight check in Step 0 before running.
    // ⚠️  deliver_on (mail_reports) is NOT shifted — it is deprecated. See Step 0 and Step 8.

    DB::transaction(function () {

        // archived_sessions — created_at is $table->date() (DATE, not DATETIME): excluded.
        DB::statement("
            UPDATE archived_sessions SET
                survey_start_date  = DATE_SUB(survey_start_date,  INTERVAL 4 HOUR),
                survey_end_date    = DATE_SUB(survey_end_date,    INTERVAL 4 HOUR),
                open_session_date  = DATE_SUB(open_session_date,  INTERVAL 4 HOUR),
                updated_at         = DATE_SUB(updated_at,         INTERVAL 4 HOUR)
            WHERE survey_start_date IS NOT NULL
        ");

        // open_sessions — created_at was missing in earlier plan version; included here.
        DB::statement("
            UPDATE open_sessions SET
                survey_start_date = DATE_SUB(survey_start_date, INTERVAL 4 HOUR),
                survey_end_date   = DATE_SUB(survey_end_date,   INTERVAL 4 HOUR),
                created_at        = DATE_SUB(created_at,        INTERVAL 4 HOUR),
                updated_at        = DATE_SUB(updated_at,        INTERVAL 4 HOUR)
            WHERE survey_start_date IS NOT NULL
        ");

        // mail_scheduled_histories
        DB::statement("
            UPDATE mail_scheduled_histories SET
                mail_wants_to_be_delivered = DATE_SUB(mail_wants_to_be_delivered, INTERVAL 4 HOUR),
                mail_delievered_on         = DATE_SUB(mail_delievered_on,         INTERVAL 4 HOUR),
                created_at                 = DATE_SUB(created_at,                 INTERVAL 4 HOUR),
                updated_at                 = DATE_SUB(updated_at,                 INTERVAL 4 HOUR)
            WHERE mail_wants_to_be_delivered IS NOT NULL
        ");

        DB::statement("
            UPDATE crons SET
                cron_disturb_date = DATE_SUB(cron_disturb_date, INTERVAL 4 HOUR),
                cron_run_date     = DATE_SUB(cron_run_date,     INTERVAL 4 HOUR),
                created_at        = DATE_SUB(created_at,        INTERVAL 4 HOUR),
                updated_at        = DATE_SUB(updated_at,        INTERVAL 4 HOUR)
            WHERE cron_disturb_date IS NOT NULL
        ");

        DB::statement("
            UPDATE accounting_journal_entries SET
                last_attempted_at = DATE_SUB(last_attempted_at, INTERVAL 4 HOUR),
                synced_at         = DATE_SUB(synced_at,         INTERVAL 4 HOUR),
                reversed_at       = DATE_SUB(reversed_at,       INTERVAL 4 HOUR),
                created_at        = DATE_SUB(created_at,        INTERVAL 4 HOUR),
                updated_at        = DATE_SUB(updated_at,        INTERVAL 4 HOUR)
            WHERE last_attempted_at IS NOT NULL
        ");

        DB::statement("
            UPDATE vilogi_journal_entries SET
                last_attempted_at = DATE_SUB(last_attempted_at, INTERVAL 4 HOUR),
                synced_at         = DATE_SUB(synced_at,         INTERVAL 4 HOUR),
                reversed_at       = DATE_SUB(reversed_at,       INTERVAL 4 HOUR),
                created_at        = DATE_SUB(created_at,        INTERVAL 4 HOUR),
                updated_at        = DATE_SUB(updated_at,        INTERVAL 4 HOUR)
            WHERE last_attempted_at IS NOT NULL
        ");

        // users — WHERE on created_at covers all rows; DATE_SUB(NULL,...) returns NULL safely.
        DB::statement("
            UPDATE users SET
                last_login                  = DATE_SUB(last_login,                  INTERVAL 4 HOUR),
                password_setup_sent_at      = DATE_SUB(password_setup_sent_at,      INTERVAL 4 HOUR),
                password_setup_expires_at   = DATE_SUB(password_setup_expires_at,   INTERVAL 4 HOUR),
                password_setup_completed_at = DATE_SUB(password_setup_completed_at, INTERVAL 4 HOUR),
                created_at                  = DATE_SUB(created_at,                  INTERVAL 4 HOUR),
                updated_at                  = DATE_SUB(updated_at,                  INTERVAL 4 HOUR)
            WHERE created_at IS NOT NULL
        ");

        DB::statement("
            UPDATE integration_tokens SET
                last_used_at = DATE_SUB(last_used_at, INTERVAL 4 HOUR),
                expires_at   = DATE_SUB(expires_at,   INTERVAL 4 HOUR),
                created_at   = DATE_SUB(created_at,   INTERVAL 4 HOUR),
                updated_at   = DATE_SUB(updated_at,   INTERVAL 4 HOUR)
            WHERE created_at IS NOT NULL
        ");

        DB::statement("
            UPDATE integration_request_logs SET
                received_at  = DATE_SUB(received_at,  INTERVAL 4 HOUR),
                processed_at = DATE_SUB(processed_at, INTERVAL 4 HOUR),
                created_at   = DATE_SUB(created_at,   INTERVAL 4 HOUR),
                updated_at   = DATE_SUB(updated_at,   INTERVAL 4 HOUR)
            WHERE received_at IS NOT NULL
        ");

        DB::statement("
            UPDATE meter_resets SET
                created_at = DATE_SUB(created_at, INTERVAL 4 HOUR),
                updated_at = DATE_SUB(updated_at, INTERVAL 4 HOUR)
            WHERE created_at IS NOT NULL
        ");

        // Generic created_at / updated_at for remaining tables.
        // mail_reports: created_at/updated_at only — deliver_on is deprecated and NOT shifted.
        foreach ([
            'lots', 'lot_owners', 'sites', 'service_providers', 'service_types',
            'rate_management', 'variable_rates', 'water_services', 'gas_services',
            'electricity_services', 'site_services', 'barcodes', 'mail_reports',
            'service_provider_mail_configs',
        ] as $table) {
            DB::statement("UPDATE `{$table}` SET
                created_at = DATE_SUB(created_at, INTERVAL 4 HOUR),
                updated_at = DATE_SUB(updated_at, INTERVAL 4 HOUR)
            WHERE created_at IS NOT NULL");
        }
    });
}

public function down(): void
{
    // Reversal: add 4 hours back to restore UTC+4 state.
    // IMPORTANT: Only valid against a backup restored to pre-migration state.
    // Do NOT run against a live database that has had new UTC writes since migration.
    DB::transaction(function () {

        DB::statement("
            UPDATE archived_sessions SET
                survey_start_date  = DATE_ADD(survey_start_date,  INTERVAL 4 HOUR),
                survey_end_date    = DATE_ADD(survey_end_date,    INTERVAL 4 HOUR),
                open_session_date  = DATE_ADD(open_session_date,  INTERVAL 4 HOUR),
                updated_at         = DATE_ADD(updated_at,         INTERVAL 4 HOUR)
            WHERE survey_start_date IS NOT NULL
        ");

        DB::statement("
            UPDATE open_sessions SET
                survey_start_date = DATE_ADD(survey_start_date, INTERVAL 4 HOUR),
                survey_end_date   = DATE_ADD(survey_end_date,   INTERVAL 4 HOUR),
                created_at        = DATE_ADD(created_at,        INTERVAL 4 HOUR),
                updated_at        = DATE_ADD(updated_at,        INTERVAL 4 HOUR)
            WHERE survey_start_date IS NOT NULL
        ");

        DB::statement("
            UPDATE mail_scheduled_histories SET
                mail_wants_to_be_delivered = DATE_ADD(mail_wants_to_be_delivered, INTERVAL 4 HOUR),
                mail_delievered_on         = DATE_ADD(mail_delievered_on,         INTERVAL 4 HOUR),
                created_at                 = DATE_ADD(created_at,                 INTERVAL 4 HOUR),
                updated_at                 = DATE_ADD(updated_at,                 INTERVAL 4 HOUR)
            WHERE mail_wants_to_be_delivered IS NOT NULL
        ");

        DB::statement("
            UPDATE crons SET
                cron_disturb_date = DATE_ADD(cron_disturb_date, INTERVAL 4 HOUR),
                cron_run_date     = DATE_ADD(cron_run_date,     INTERVAL 4 HOUR),
                created_at        = DATE_ADD(created_at,        INTERVAL 4 HOUR),
                updated_at        = DATE_ADD(updated_at,        INTERVAL 4 HOUR)
            WHERE cron_disturb_date IS NOT NULL
        ");

        DB::statement("
            UPDATE accounting_journal_entries SET
                last_attempted_at = DATE_ADD(last_attempted_at, INTERVAL 4 HOUR),
                synced_at         = DATE_ADD(synced_at,         INTERVAL 4 HOUR),
                reversed_at       = DATE_ADD(reversed_at,       INTERVAL 4 HOUR),
                created_at        = DATE_ADD(created_at,        INTERVAL 4 HOUR),
                updated_at        = DATE_ADD(updated_at,        INTERVAL 4 HOUR)
            WHERE last_attempted_at IS NOT NULL
        ");

        DB::statement("
            UPDATE vilogi_journal_entries SET
                last_attempted_at = DATE_ADD(last_attempted_at, INTERVAL 4 HOUR),
                synced_at         = DATE_ADD(synced_at,         INTERVAL 4 HOUR),
                reversed_at       = DATE_ADD(reversed_at,       INTERVAL 4 HOUR),
                created_at        = DATE_ADD(created_at,        INTERVAL 4 HOUR),
                updated_at        = DATE_ADD(updated_at,        INTERVAL 4 HOUR)
            WHERE last_attempted_at IS NOT NULL
        ");

        DB::statement("
            UPDATE users SET
                last_login                  = DATE_ADD(last_login,                  INTERVAL 4 HOUR),
                password_setup_sent_at      = DATE_ADD(password_setup_sent_at,      INTERVAL 4 HOUR),
                password_setup_expires_at   = DATE_ADD(password_setup_expires_at,   INTERVAL 4 HOUR),
                password_setup_completed_at = DATE_ADD(password_setup_completed_at, INTERVAL 4 HOUR),
                created_at                  = DATE_ADD(created_at,                  INTERVAL 4 HOUR),
                updated_at                  = DATE_ADD(updated_at,                  INTERVAL 4 HOUR)
            WHERE created_at IS NOT NULL
        ");

        DB::statement("
            UPDATE integration_tokens SET
                last_used_at = DATE_ADD(last_used_at, INTERVAL 4 HOUR),
                expires_at   = DATE_ADD(expires_at,   INTERVAL 4 HOUR),
                created_at   = DATE_ADD(created_at,   INTERVAL 4 HOUR),
                updated_at   = DATE_ADD(updated_at,   INTERVAL 4 HOUR)
            WHERE created_at IS NOT NULL
        ");

        DB::statement("
            UPDATE integration_request_logs SET
                received_at  = DATE_ADD(received_at,  INTERVAL 4 HOUR),
                processed_at = DATE_ADD(processed_at, INTERVAL 4 HOUR),
                created_at   = DATE_ADD(created_at,   INTERVAL 4 HOUR),
                updated_at   = DATE_ADD(updated_at,   INTERVAL 4 HOUR)
            WHERE received_at IS NOT NULL
        ");

        DB::statement("
            UPDATE meter_resets SET
                created_at = DATE_ADD(created_at, INTERVAL 4 HOUR),
                updated_at = DATE_ADD(updated_at, INTERVAL 4 HOUR)
            WHERE created_at IS NOT NULL
        ");

        foreach ([
            'lots', 'lot_owners', 'sites', 'service_providers', 'service_types',
            'rate_management', 'variable_rates', 'water_services', 'gas_services',
            'electricity_services', 'site_services', 'barcodes', 'mail_reports',
            'service_provider_mail_configs',
        ] as $table) {
            DB::statement("UPDATE `{$table}` SET
                created_at = DATE_ADD(created_at, INTERVAL 4 HOUR),
                updated_at = DATE_ADD(updated_at, INTERVAL 4 HOUR)
            WHERE created_at IS NOT NULL");
        }
    });
}
```

> **Warning:** `DATE_SUB` on MySQL/MariaDB is safe for this. If running SQLite in production (unlikely), use a different approach — SQLite has no native `DATE_SUB`. The test suite uses SQLite in-memory; production uses MySQL. Confirm before running.

---

### STEP 2 — Backend: flip config to UTC

```php
// config/app.php
'timezone' => 'UTC',  // was 'Indian/Mauritius'
```

After this change, all `Carbon::now()`, `date()`, `now()` calls produce UTC. The DB migration in Step 1 ensures existing data is already UTC. Everything is now consistent.

---

### STEP 3 — Backend: add `timezone` preference

**`app/Models/ServiceProvider.php` — `defaultPreferences()`**

Add the key:
```php
'timezone' => 'Indian/Mauritius',
```

Add a migration to hydrate existing records (same pattern as the preferences migration):
```php
DB::table('service_providers')
    ->whereNotNull('preferences')
    ->each(function ($sp) {
        $prefs = json_decode($sp->preferences, true) ?? [];
        if (!isset($prefs['timezone'])) {
            $prefs['timezone'] = 'Indian/Mauritius';
            DB::table('service_providers')
                ->where('id', $sp->id)
                ->update(['preferences' => json_encode($prefs)]);
        }
    });
```

---

### STEP 4 — Backend: make `TenantFormat::date()` timezone-aware

**`app/Helpers/TenantFormat.php`**

```php
// Before
public static function date(string $dateString, array $preferences): string
{
    return date($preferences['date_format'] ?? 'd M Y', strtotime($dateString));
}

// After
public static function date(string $dateString, array $preferences): string
{
    return \Carbon\Carbon::parse($dateString, 'UTC')
        ->setTimezone($preferences['timezone'] ?? 'Indian/Mauritius')
        ->format($preferences['date_format'] ?? 'd M Y');
}
```

This is the single most important display-layer change. Every blade template that calls `tenantFormatDate()` or `TenantFormat::date()` immediately becomes timezone-correct.

---

### STEP 5 — Backend: fix `strtotime()` calls

`strtotime()` always interprets relative to the PHP process timezone. After Step 2 the PHP timezone is UTC, so `strtotime("2026-06-10 08:00:00")` returns the Unix timestamp for `2026-06-10 08:00:00 UTC` — which is correct if the DB value is UTC. These calls become safe. No changes needed for strtotime calls that feed into `TenantFormat::date()` since Step 4 covers those.

The one call to watch: `MailingRepo.php:199–200` — `date($dateFormat, strtotime(...))`. Replace with `TenantFormat::date()` calls to consolidate.

---

### STEP 6 — Backend: fix due date calculation

**`app/Http/Controllers/V1/MyCloudLibrary.php` lines 565–567**  
**`app/Http/Controllers/V1/Export/LotService.php` lines 99–102, 234–237**

```php
// Before — parses naive UTC+4 string, adds days, outputs UTC+4 date
$invoiceDate = Carbon::parse($data[$key]['survey_end_date']);
$dueDate     = $invoiceDate->addDays($service->payment_terms);
$data[$key]['due_date'] = $dueDate->toDateString();

// After — parse as UTC, convert to tenant timezone before extracting date
$invoiceDate = Carbon::parse($data[$key]['survey_end_date'], 'UTC')
    ->setTimezone($preferences['timezone'] ?? 'Indian/Mauritius');
$dueDate = $invoiceDate->copy()->addDays($service->payment_terms);
$data[$key]['due_date'] = $dueDate->toDateString();
```

The `$preferences` array is already loaded in both locations.

**`LotService.php` — `archived_sessions.created_at` is DATE-only, not a datetime**

Lines [99](app/Http/Controllers/V1/Export/LotService.php#L99) and [234](app/Http/Controllers/V1/Export/LotService.php#L234) base the due date on `$data[$key]['created_at']`, not `survey_end_date`. This field is a pure date string (`"2026-06-10"`, no time part). Applying `setTimezone()` to a bare date parsed as UTC midnight would shift the date backward for any tenant west of UTC.

**Implemented: no change needed in LotService.** The existing code already treats it as a naive local date with no timezone conversion, which is exactly correct for a DATE column:

```php
// created_at from archived_sessions is a DATE string — no UTC parse, no tz conversion (unchanged).
$dueDate = Carbon::parse($data[$key]['created_at'])->addDays($service->payment_terms);
$data[$key]['due_date'] = $dueDate->toDateString();
```

Only `MyCloudLibrary::getLotPdfData()` (which bases the due date on the `survey_end_date` **datetime**) was changed — it now resolves the tenant timezone once after loading the rows and parses `survey_end_date` as UTC before converting.

**`LotService.php:197` — `whereBetween` receives frontend date inputs**

```php
->whereBetween('survey_end_date', [$from, $to])
```

After migration, `survey_end_date` is UTC. The `$from`/`$to` values are user-supplied date range inputs from the frontend (lines 186–187). The frontend **must** convert these picker values to UTC before sending (using `toUtcDateString()` in Vue or `toApiDate()` in Dart). This is covered by the frontend changes in this plan — call it out explicitly for the team member wiring this picker.

---

### STEP 7 — Backend: redesign `MailCron` to use `deliver_at`

This step replaces the fragile date-match query with an exact UTC datetime comparison, and changes the cron from daily to hourly.

**New migration** — add `deliver_at` to `mail_reports`:

```php
// database/migrations/2026_XX_XX_000002_add_deliver_at_to_mail_reports.php
Schema::table('mail_reports', function (Blueprint $table) {
    // Exact UTC datetime to send — replaces the date-only deliver_on field
    $table->dateTime('deliver_at')->nullable()->after('deliver_on');
    $table->index('deliver_at');
});
```

**`app/Http/Controllers/V1/Cron/MailCron.php` — `sendMailReports()`**

```php
// Before — date match against deprecated deliver_on, runs once daily
$mails = MailReport::where('is_delivered', 0)
    ->where('deliver_on', Carbon::today()->format('Y-m-d'))
    ->get();

// After — UTC datetime comparison against deliver_at, runs every hour
// Any missed hour is automatically caught on the next run (<= not =)
$mails = MailReport::where('is_delivered', 0)
    ->where('deliver_at', '<=', Carbon::now('UTC'))
    ->get();
```

No timezone logic in the query. UTC now vs UTC stored — unambiguous regardless of tenant timezone or server location.

**External cron schedule change:**

Change from once-daily to every hour. Example crontab:
```
# Before — once daily at 10pm server time
0 22 * * * curl -s https://yourapp.com/api/cron-daily-report

# After — every hour
0 * * * * curl -s https://yourapp.com/api/cron-daily-report
```

Missed runs are safe — the `<=` operator catches all overdue records on the next hourly tick.

---

### STEP 8 — Backend: write `deliver_at` instead of `deliver_on`

**`app/Repository/MailingRepo.php` line 441**  
**`app/Repository/SessionRepo.php` line 1344**

Also add `delivery_hour` to `ServiceProvider::defaultPreferences()` (default `22`) so the send time is tenant-configurable.

```php
// app/Models/ServiceProvider.php — defaultPreferences()
'delivery_hour' => 22,   // 10 pm local time
```

```php
// Before — UTC+4 date string written to deprecated deliver_on
'deliver_on' => Carbon::tomorrow()->format('Y-m-d'),

// After — exact UTC datetime written to deliver_at
// Target: 22:00 tonight in tenant local time.
// If already past 22:00 locally, advance to tomorrow to avoid scheduling in the past.
// Requires $preferences to be in scope (already loaded in both locations)
$tz           = $preferences['timezone']      ?? 'Indian/Mauritius';
$deliveryHour = (int) ($preferences['delivery_hour'] ?? 22);

$tenantNow = Carbon::now('UTC')->setTimezone($tz);
$delivery  = $tenantNow->copy()->startOfDay()->addHours($deliveryHour);

if ($delivery->lte($tenantNow)) {
    // Already past tonight's delivery window — schedule for tomorrow
    $delivery->addDay();
}

// Write deliver_at in UTC; leave deliver_on null for new records
'deliver_at' => $delivery->setTimezone('UTC')->toDateTimeString(),
```

**Examples — session completed at 14:00 MU (10:00 UTC), `delivery_hour = 22`:**

| Tenant timezone | UTC offset | `deliver_at` stored as UTC |
|---|---|---|
| `Indian/Mauritius` | +4 | `"2026-06-10 18:00:00"` — tonight at 22:00 MU |
| `Africa/Nairobi` | +3 | `"2026-06-10 19:00:00"` — tonight at 22:00 EAT |
| `Europe/London` (BST) | +1 | `"2026-06-10 21:00:00"` — tonight at 22:00 BST |
| `America/New_York` (EDT) | −4 | `"2026-06-11 02:00:00"` — tonight at 22:00 EDT |

**Session completed at 23:00 MU (19:00 UTC) — already past 22:00 locally:**

| Tenant timezone | `deliver_at` stored as UTC |
|---|---|
| `Indian/Mauritius` | `"2026-06-11 18:00:00"` — tomorrow night at 22:00 MU |

The cron fires every hour and picks up any row where `deliver_at <= now()`. If the 18:00 UTC run is missed, the 19:00 run catches it.

---

### STEP 9 — Backend: API date output

All date strings sent to the frontend are currently naive UTC+4 strings. After Step 2 they become UTC strings. The frontend must be updated to handle this (see Frontend section).

**Transformers to update** — convert at the transformer layer. Note: `survey_start_date` and `survey_end_date` are plain string columns (no Eloquent datetime cast), so `$session->survey_start_date->toISOString()` would fatal. Always wrap in `Carbon::parse()`:

```php
// Before — naive UTC+4 string
(string) $session->survey_start_date  // "2026-06-10 14:00:00"

// After — explicit UTC ISO 8601, safe for string columns
\Carbon\Carbon::parse($session->survey_start_date, 'UTC')->toISOString()  // "2026-06-10T10:00:00.000000Z"
```

| File | Fields | Action |
|---|---|---|
| `app/Transformers/ArchivedSessionTrans.php` | `survey_start_date`, `survey_end_date` | `Carbon::parse($session->survey_start_date, 'UTC')->toISOString()` |
| `app/Transformers/ArchivedSessionLotsTrans.php` | `survey_start_date` | Same |
| `app/Transformers/ArchivedSessionLotsTrans.php` | `created_at` | **DATE field** — leave as plain string `(string) $session->created_at`; no UTC conversion |
| `app/Transformers/AdhocListTrans.php` | `created_at` | Confirm whether this is `archived_sessions.created_at` (DATE → plain string) or another table's datetime (→ `Carbon::parse(..., 'UTC')->toISOString()`) |
| `app/Transformers/MailSectionTrans.php` | `survey_start_date`, `survey_end_date` | `Carbon::parse($session->survey_start_date, 'UTC')->toISOString()` |

Outputting ISO 8601 with a `Z` suffix (`2026-06-10T04:00:00Z`) signals to the client that the timestamp is UTC and must be converted.

---

### STEP 10 — Backend: Vilogi / accounting integration dates

**`app/Services/Accounting/VilogiAccountingService.php` line 42**  
**`app/Services/JournalEntryService.php` lines 77–78**  
**`app/Console/Commands/VilogiTestJournalEntry.php` lines 37–38, 48**

These format `survey_end_date` and `lastReadingDate()` for the Vilogi API payload. After Step 2, the parsed dates will be UTC. They must be converted to tenant timezone before formatting for the external API:

```php
// Before
$from = Carbon::parse($archivedSession->lastReadingDate(1))->format('d-m-Y');
$to   = Carbon::parse($archivedSession->survey_end_date)->format('d-m-Y');

// After — get tenant timezone from the session's service provider
$tz   = $archivedSession->serviceProvider?->getPreferences()['timezone'] ?? 'Indian/Mauritius';
$from = Carbon::parse($archivedSession->lastReadingDate(1), 'UTC')->setTimezone($tz)->format('d-m-Y');
$to   = Carbon::parse($archivedSession->survey_end_date, 'UTC')->setTimezone($tz)->format('d-m-Y');
```

---

### STEP 11 — Backend: `ArchivedSession::toText()` method

**`app/Models/ArchivedSession.php` lines 178–186**

```php
// Before
$surveyStartDate = Carbon::parse($this->survey_start_date);
$surveyEndDate   = Carbon::parse($this->survey_end_date);

// After
$tz              = $this->serviceProvider?->getPreferences()['timezone'] ?? 'Indian/Mauritius';
$surveyStartDate = Carbon::parse($this->survey_start_date, 'UTC')->setTimezone($tz);
$surveyEndDate   = Carbon::parse($this->survey_end_date,   'UTC')->setTimezone($tz);
```

---

### STEP 12 — Backend: `lotSummaryPdf.blade.php`

**`resources/views/export/lotSummaryPdf.blade.php` line 80**

```php
// Before — hardcoded format, no timezone
\Carbon\Carbon::parse($record['survey_end_date'])->format('d/m/Y')

// After — use TenantFormat
\App\Helpers\TenantFormat::date($record['survey_end_date'], $preferences)
```

Ensure `$preferences` is passed to this blade template from its controller.

---

## Frontend / Mobile changes

### The contract change

Before migration, the API returns naive UTC+4 strings: `"2026-06-10 08:30:00"`  
After migration, the API returns UTC ISO 8601 strings: `"2026-06-10T04:30:00Z"` (or `"2026-06-10 04:30:00"`)

The frontend must:
1. Always parse date strings as UTC
2. Convert to `prefs.timezone` for display
3. Send date inputs as UTC to the API

### New preference key

```json
"preferences": {
  "timezone": "Indian/Mauritius"
}
```

All existing tenants will have `"Indian/Mauritius"` as the default. Add this to your local preference store on login.

---

### Flutter / Dart changes

**Rule: never use `DateTime.parse()` on API date strings — use UTC parsing.**

```dart
// Add to pubspec.yaml
// timezone: ^0.9.0
// intl: ^0.19.0

import 'package:timezone/timezone.dart' as tz;
import 'package:timezone/data/latest.dart' as tz;

// On app start
void initTimezones() {
  tz.initializeTimeZones();
}

// Core helper — replaces all raw date formatting
String formatApiDate(String? dateString, Map<String, dynamic> prefs) {
  if (dateString == null || dateString.isEmpty) return '';
  final phpFormat  = prefs['date_format'] as String? ?? 'd M Y';
  final tzName     = prefs['timezone']   as String? ?? 'Indian/Mauritius';
  final location   = tz.getLocation(tzName);
  // Parse as UTC; API always returns UTC after migration
  final utc        = DateTime.parse(dateString.endsWith('Z') ? dateString : '${dateString}Z');
  final local      = tz.TZDateTime.from(utc, location);
  return DateFormat(phpFormatToDart(phpFormat)).format(local);
}

String phpFormatToDart(String phpFormat) {
  return phpFormat
      .replaceAll('d', 'dd')
      .replaceAll('M', 'MMM')
      .replaceAll('Y', 'yyyy')
      .replaceAll('m', 'MM')
      .replaceAll('j', 'd');
}

// Sending dates to the API — always send UTC
String toApiDate(DateTime localDate, Map<String, dynamic> prefs) {
  final tzName   = prefs['timezone'] as String? ?? 'Indian/Mauritius';
  final location = tz.getLocation(tzName);
  final local    = tz.TZDateTime(location, localDate.year, localDate.month, localDate.day);
  return local.toUtc().toIso8601String();
}
```

**Impacted screens — every screen that displays a date field must switch to `formatApiDate()`:**
- Bill history list / detail
- Lot card (last reading date)
- Session list (survey start/end)
- Due date display
- Mail delivery date

**Date range pickers:**
```dart
// Before — sends local date string as-is
final from = pickedDate.toIso8601String();

// After — interpret picker value as tenant-local, send as UTC
final from = toApiDate(pickedDate, prefs);
```

---

### Vue.js / JavaScript changes

```bash
npm install dayjs dayjs/plugin/utc dayjs/plugin/timezone
```

```js
// src/utils/dateFormat.js
import dayjs from 'dayjs';
import utc from 'dayjs/plugin/utc';
import timezone from 'dayjs/plugin/timezone';

dayjs.extend(utc);
dayjs.extend(timezone);

// Map PHP date tokens to dayjs tokens
function phpToDayjs(phpFormat) {
  return phpFormat
    .replace(/\bd\b/g, 'DD')
    .replace(/\bM\b/g, 'MMM')
    .replace(/\bY\b/g, 'YYYY')
    .replace(/\bm\b/g, 'MM')
    .replace(/\bj\b/g, 'D');
}

// Format an API date string in the tenant's timezone
export function formatDate(dateString, prefs) {
  if (!dateString) return '';
  const tz  = prefs?.timezone  ?? 'Indian/Mauritius';
  const fmt = phpToDayjs(prefs?.date_format ?? 'd M Y');
  return dayjs.utc(dateString).tz(tz).format(fmt);
}

// Format money (unchanged)
export function formatMoney(value, prefs) {
  const currency = prefs?.currency          ?? 'Rs.';
  const position = prefs?.currency_position ?? 'before';
  const formatted = Number(value).toLocaleString('en-US', {
    minimumFractionDigits: 2,
    maximumFractionDigits: 2,
  });
  return position === 'after' ? `${formatted} ${currency}` : `${currency} ${formatted}`;
}

// Convert a local tenant date to UTC for API calls
export function toUtcDateString(localDateString, prefs) {
  const tz = prefs?.timezone ?? 'Indian/Mauritius';
  return dayjs.tz(localDateString, tz).utc().toISOString();
}
```

**Usage in Vue components:**

```vue
<template>
  <span>{{ formatDate(bill.survey_end_date, prefs) }}</span>
  <span>{{ formatDate(bill.due_date, prefs) }}</span>
</template>

<script setup>
import { formatDate } from '@/utils/dateFormat';
const prefs = inject('prefs'); // or from store
</script>
```

**Date range picker — convert before sending to API:**

```js
// Before
const payload = { from: startDate, to: endDate };

// After
import { toUtcDateString } from '@/utils/dateFormat';
const payload = {
  from: toUtcDateString(startDate, prefs),
  to:   toUtcDateString(endDate,   prefs),
};
```

**"Due today" / overdue logic:**

```js
// Before — compared raw strings, assumed same timezone
const isOverdue = bill.due_date < today;

// After — compare in tenant timezone
import dayjs from 'dayjs';
import utc from 'dayjs/plugin/utc';
import timezone from 'dayjs/plugin/timezone';

function isOverdue(dueDateString, prefs) {
  const tz      = prefs?.timezone ?? 'Indian/Mauritius';
  const dueDate = dayjs.utc(dueDateString).tz(tz).startOf('day');
  const today   = dayjs().tz(tz).startOf('day');
  return dueDate.isBefore(today);
}
```

---

## Files that need code changes — complete list

### Backend

| File | Change required |
|---|---|
| `config/app.php` | `timezone` → `'UTC'` |
| `app/Models/ServiceProvider.php` | Add `'timezone' => 'Indian/Mauritius'` to `defaultPreferences()` |
| `app/Helpers/TenantFormat.php` | `date()` → `Carbon::parse(..., 'UTC')->setTimezone(...)` |
| `app/Http/Controllers/V1/MyCloudLibrary.php` | Due date calc lines 565–567: parse as UTC, convert to tenant tz |
| `app/Http/Controllers/V1/Export/LotService.php` | Due date calc lines 99–102, 234–237; same pattern |
| `app/Repository/MailingRepo.php` | Write `deliver_at` (tenant 22:00 in UTC) instead of `deliver_on` (line 441) |
| `app/Repository/SessionRepo.php` | Same (line 1344) |
| `app/Http/Controllers/V1/Cron/MailCron.php` | Query `deliver_at <= now()` instead of `deliver_on = today`; runs hourly |
| `app/Models/ServiceProvider.php` | Add `delivery_hour => 22` to `defaultPreferences()` |
| `app/Services/JournalEntryService.php` | Parse `lastReadingDate`/`survey_end_date` as UTC→tenant tz; also pass the **already-localized** date into `AccountingPayload->date` |
| `app/Services/Accounting/VilogiAccountingService.php` | **No change** — receives an already-localized date string from `JournalEntryService`, so its `Carbon::parse($payload->date)->format('d/m/Y')` stays correct |
| `app/Console/Commands/VilogiTestJournalEntry.php` | Lines 37–38, 48: parse as UTC, setTimezone to tenant |
| `app/Models/ArchivedSession.php` | `toText()` lines 178–179: parse as UTC, setTimezone |
| `app/Transformers/ArchivedSessionTrans.php` | Output `toISOString()` on date fields |
| `app/Transformers/ArchivedSessionLotsTrans.php` | Same |
| `app/Transformers/AdhocListTrans.php` | Same |
| `app/Transformers/MailSectionTrans.php` | Same |
| `resources/views/export/lotSummaryPdf.blade.php` | Line 80: parse `survey_end_date` as UTC→tenant tz (keeps `d/m/Y`); `multiPdf()` now passes `$preferences` to this view |
| `resources/views/email/welcome.blade.php` | **No change** — the expiry is converted to tenant tz at the source in `ServiceProviderRepo::reInviteAdmin` (`$user->password_setup_expires_at?->copy()->setTimezone($tz)`) before passing to the view |
| `app/Models/MailReport.php` | Add `deliver_at` to `$fillable` and cast `deliver_at => datetime` |
| `database/migrations/2026_06_11_000001_convert_datetimes_to_utc.php` | **NEW** — the −4h data shift (MySQL-guarded, transactional, with reversible `down()`) |
| `database/migrations/2026_06_11_000002_add_deliver_at_to_mail_reports.php` | **NEW** — adds `deliver_at` + index; makes `deliver_on` nullable |
| `database/migrations/2026_06_11_000003_add_timezone_to_service_provider_preferences.php` | **NEW** — hydrates existing tenants' preferences with `timezone` + `delivery_hour` |
| `tests/Unit/TenantFormatTest.php` | Added timezone/iso/nextDeliveryAt regression tests |

### Frontend (Vue)

| File / area | Change |
|---|---|
| `src/utils/dateFormat.js` (new) | Add `formatDate()`, `formatMoney()`, `toUtcDateString()` helpers |
| All bill history components | Switch to `formatDate()` |
| All date range pickers | Wrap with `toUtcDateString()` before API call |
| Overdue / due-today logic | Rewrite using dayjs tz comparison |
| Login / store | Persist `prefs.timezone` alongside other preferences |

### Mobile (Flutter / Dart)

| File / area | Change |
|---|---|
| `pubspec.yaml` | Add `timezone` package |
| App startup | `tz.initializeTimeZones()` |
| Date formatting helpers | Replace `DateTime.parse()` with UTC-aware parsing |
| All date display widgets | Switch to `formatApiDate()` |
| Date input / pickers | Wrap with `toApiDate()` before POST/PUT |
| Overdue logic | Compare in tenant timezone |

---

## Testing plan

### Staging rehearsal (mandatory before production)

1. Restore production backup to staging
2. Run `php artisan migrate` — confirm row counts before/after are identical
3. Spot-check 10 `archived_sessions` rows: `survey_end_date` should be exactly 4 hours earlier than the backup value
4. Generate a PDF invoice — dates should be correct for `Indian/Mauritius` tenant
5. Trigger a test bill email — from/to dates should match
6. Verify the mail cron: set a `deliver_at` to a UTC time in the past, confirm the hourly run picks it up
7. Create a new archived session — confirm `survey_end_date` is stored as UTC
8. Check Vilogi journal entry dates — confirm `saisie` field is still in `d/m/Y` format and represents the correct tenant-local date

### Regression tests to add before migration

- `TenantFormat::date()` with UTC input string and `Indian/Mauritius` timezone → correct date
- `TenantFormat::date()` with UTC input string and `Africa/Nairobi` (UTC+3) timezone → 1 hour less
- Due date calculation: session ending at `2026-06-10 22:00:00 UTC` with 30-day terms in `Indian/Mauritius` (UTC+4) → due date is `2026-07-11` (not `2026-07-10`)
- Mail scheduling: `deliver_at` for `Indian/Mauritius` tenant with `delivery_hour = 22` → stored as `"2026-06-10 18:00:00"` UTC; cron run at 18:01 UTC picks it up

---

## Rollback plan

If anything fails after deployment:

1. `php artisan down` immediately
2. Restore the pre-migration database backup
3. Redeploy the pre-migration git tag: `git checkout pre-utc-migration`
4. `php artisan up`

The only unrecoverable scenario is if the backup is not restorable. Test this on staging before production.

---

## Do NOT do this

- Do not run the data migration while the app is live — writes during migration will be stored in UTC+4 and then not converted
- Do not run `php artisan config:cache` before `php artisan migrate` — `config:cache` is what makes the UTC timezone active for new writes; it must come after the migration, not before
- Do not run this migration twice — there is no automatic guard; a second run shifts all timestamps by −8 hours silently. Use the pre-flight check in Step 0 every time
- Do not deploy frontend/mobile changes before the backend deploy — clients will try to parse UTC+4 strings as UTC
- Do not do this across two separate deploys — everything must land atomically in one maintenance window

---

## Sequence diagram for maintenance window

```
1. php artisan down                        ← maintenance mode ON; no new writes from here
2. git pull                                ← new backend code deployed
                                             NOTE: if config/app.php is in this commit,
                                             the file now says 'UTC' — that is fine because
                                             the app is down and artisan migrate runs BEFORE
                                             config:cache, so the old cached config is still
                                             active during the migration.
3. php artisan migrate                     ← DB conversion runs (Step 1); uses cached UTC+4
                                             config — no new writes occur during migration.
4. php artisan config:cache                ← timezone NOW becomes 'UTC' in the running app
5. php artisan queue:restart
6. Deploy frontend build (Vue + Flutter already updated)
7. Smoke test: load one bill, check date, send test email
8. php artisan up
```

> **Why the order is safe:** `php artisan down` prevents all writes before step 2. The `DATE_SUB` SQL in the migration is pure arithmetic — it does not depend on the PHP timezone. `config:cache` is the moment UTC takes effect for new writes; it runs after the migration, never before.