# Scheduling/payroll migration restart recovery

## Scope and safety boundary

This procedure covers only:

- `2026_09_06_000100_add_scheduling_settings`
- `2026_09_06_000200_create_time_punches_table`
- `2026_09_06_000300_create_payroll_tables`

**Do not use `down()`, rollback, refresh, reset, `migrate:fresh`, table drops, or
migration-ledger deletion to recover a partially applied deployment.** Those
operations can destroy durable clock responses, punches, payroll snapshots, and
settings. The normal `down()` methods remain destructive rollback operations;
their isolated test is not an operator recovery recommendation.

Run a single migration process with application writers, queue workers, and
scheduled jobs paused. Restart guards are not a distributed deployment lock.
Confirm the exact environment and connection, take a consistent backup including
the migration ledger, verify that backup is restorable, and rehearse against a
separate copy before any authorized production work. This change was tested on
isolated SQLite only; it does not establish MySQL/PostgreSQL crash or concurrency
behavior.

## Supported automatic continuation

Settings columns are added only when absent, using the original definitions and
defaults, in this order:

1. `timezone`
2. `work_week_start_day`
3. `work_week_start_time`
4. `payroll_rounding_mode`
5. `payroll_rounding_increment_minutes`
6. `clock_in_early_minutes`
7. `clock_in_late_minutes`
8. `clock_out_late_minutes`

Existing values are never updated. This migration creates **no indexes**. No
settings-index recovery or duplicate cleanup is necessary.

The table migrations can resume any completed-table prefix, including an empty
prefix or every table already present:

| Migration | Creation order |
| --- | --- |
| Clock | `clock_actions`, `time_punches` |
| Payroll | `overtime_rules`, `payroll_exports`, `timesheets` |

An absent table is created from its original definition. Before skipping an
existing table, the migration checks for all declared columns, its primary key,
the named unique/lookup indexes with their ordered columns and uniqueness, and
foreign-key target columns and delete actions. It never rewrites existing rows
or automatically removes duplicates.

**Atomicity assumption:** a database `CREATE TABLE` statement installs its column
definitions together. A Laravel `Schema::create()` callback is *not* assumed to
be one atomic statement: indexes or foreign keys may be emitted separately. A
table present without those later statements is an unsupported automatic state
and fails closed, even if it is empty. A missing column also fails closed. The
error names the table and missing/mismatched structural objects and points here.
These checks are restart checks, not a general schema-drift repair system; they
do not prove existing column types/defaults, collations, check expressions, or
arbitrary manually altered schema are correct. Compare full DDL during preflight.

## Resume a supported prefix

1. Capture `migrate:status`, the failed deployment log, and the schema definitions
   for the affected tables. Inventory the relevant column/table prefix and all
   existing indexes/FKs. Compare to these exact migration files from the deployed
   revision, not to a later release. Record row counts plus primary-key-ordered
   row digests or a full protected export for existing tables; retain the values
   of already-added settings columns.
2. Confirm previous prerequisite migrations are complete. Confirm the interrupted
   migration is still pending in the ledger. Laravel records completion only
   after `up()` returns. If the ledger says complete while schema is incomplete,
   stop and investigate; do not delete ledger rows or mark success by hand.
3. With backups, writer suspension, and connection selection verified, use the
   normal migration runner from `api/`, for example
   `php artisan migrate --database=<verified-connection>`. Review all pending
   migrations before running it; use only the deployment's approved scope. Add
   `--force` only as part of an explicitly authorized production deployment.
4. Read back `migrate:status`, columns, indexes, FKs, and the exact retained rows.
   The original rows and existing settings values must match the preflight
   snapshot; only missing schema/default-backed suffix values should appear.
5. A second normal runner invocation should report no pending work. Direct
   repeated `up()` calls are regression-tested but are not a substitute for
   normal ledger management. Resume writers only after all checks pass.

## Fail-closed incomplete table or conflicting data

Keep writers paused. Do not repeat the runner indefinitely and do not drop or
recreate the table. Preserve the error, full DDL, rows, and ledger in the backup.

1. Compare the failed table with the migration's complete definition. Identify
   every missing column, primary/unique/lookup index, FK, and incorrect delete
   action. Have the DBA prepare a connection-specific, **additive** repair plan
   for review. Do not make up missing historical values (punch timestamps,
   request hashes, payroll snapshot content, etc.). A missing populated column
   requires restoration from authoritative backup/audit data or a separately
   approved recovery decision; this migration intentionally cannot infer it.
2. Before restoring any unique index, inspect duplicate keys using the exact
   groups below. Include soft-deleted punches; the punch uniqueness rule does
   not exclude them. Also inspect duplicate/null primary keys where the primary
   index is absent.

   ```sql
   SELECT store_membership_id, idempotency_key, COUNT(*) AS duplicate_count
   FROM clock_actions GROUP BY store_membership_id, idempotency_key HAVING COUNT(*) > 1;
   SELECT store_membership_id, punch_type, punched_at, COUNT(*) AS duplicate_count
   FROM time_punches GROUP BY store_membership_id, punch_type, punched_at HAVING COUNT(*) > 1;
   SELECT store_id, effective_start_date, COUNT(*) AS duplicate_count
   FROM overtime_rules GROUP BY store_id, effective_start_date HAVING COUNT(*) > 1;
   SELECT store_id, idempotency_key, COUNT(*) AS duplicate_count
   FROM payroll_exports GROUP BY store_id, idempotency_key HAVING COUNT(*) > 1;
   SELECT store_id, store_membership_id, week_start_date, COUNT(*) AS duplicate_count
   FROM timesheets GROUP BY store_id, store_membership_id, week_start_date HAVING COUNT(*) > 1;
   ```

   **If duplicates exist, stop automatic repair.** Preserve every row and require
   the payroll/data owner to approve a separate reconciliation plan with durable
   original-row archives and explicit identity mappings. Do not choose an
   arbitrary winner, delete rows, rewrite idempotency keys to hide conflicts, or
   recompute approved/exported history. There is no universally safe automatic
   dedupe rule for these records; unresolved duplicates remain a release blocker.
3. Before adding a missing FK, check all non-null child IDs for absent referenced
   rows with a left join to its declared parent. Resolve any orphan using
   authoritative data under the same review process; do not cascade-delete it.
   Restore the exact delete action from the source: `restrict` for all these FKs
   except `timesheets.approved_by`, which is `set null`. Restore named unique
   indexes exactly and retain `punch_member_type_second_unique` as the punch
   dedupe guard.
4. If the only defect is a missing index and duplicate checks are empty, restore
   **only that index** under the writer lock, with an existence check before DDL.
   For example, the clock index definition is:

   ```php
   if (! Schema::hasIndex('clock_actions', 'clock_member_key_unique')) {
       Schema::table('clock_actions', function (Blueprint $table): void {
           $table->unique(['store_membership_id', 'idempotency_key'], 'clock_member_key_unique');
       });
   }
   ```

   Execute such repair only through a reviewed, connection-bound operator script
   or repair migration, not a blind pasted command. If an index exists with the
   wrong definition, stop for a reviewed alteration plan rather than treating
   the name as proof of correctness. Schema work must preserve the original rows.
5. Read back the repaired objects and the original row snapshot. If the operator
   process was interrupted, re-inventory before executing any DDL again. Once the
   table matches, rerun the pending migration normally. If interruption occurred
   after repair but before the migration runner, the same restart guards accept
   the now-complete table and finish the missing suffix. Never modify the ledger
   to bypass the fail-closed check.

## Executed regression coverage

`api/tests/Feature/Scheduling/SchedulingMigrationRestartTest.php` creates a named,
new SQLite `:memory:` connection for each test, explicitly independent of the
application database and `RefreshDatabase`'s shared PDO. It exercises the actual
migration `up()` methods against every settings prefix (0–8), every clock-table
prefix (0–2), and every payroll-table prefix (0–3). Fixtures are written before
restart and between the two `up()` executions. Exact rows and schema metadata
(columns, indexes, and FKs) are compared after convergence.

Additional cases cover every table missing a column, unique index, FK, or correct
delete action; the missing punch lookup index; retained duplicate writes while
unique indexes are absent; a missing-index operator repair interrupted before
restart; and a normal reverse-`down()`/forward-`up()` cycle on an empty isolated
fixture. The suite never runs those destructive setup operations on an operator
or production database.

From `api/`:

```sh
php vendor/bin/phpunit --do-not-cache-result tests/Feature/Scheduling/SchedulingMigrationRestartTest.php
```

This is SQLite restart-state evidence, not a production-engine certification.