Device Status Drift Repair

ovahol-status-drift is an operator tool for repairing device statuses that work orders left behind on their snapshotted previous status. Use it after upgrading past v1.2.1.

What the drift is

A work order snapshots the device's status when it is created and writes that snapshot back when the last active work order on the device is completed, cancelled, or deleted:

work_order.previous_device_status_id  ->  restored into  ->  device_record.status_id

Before v1.2.1, an OPEN work order could be deleted without restoring the device, and the leftover status poisoned the next work order's snapshot. From then on the snapshot held a value that was already wrong, and every completion wrote it back, so the drift became permanent.

The visible symptom is a device that is available for maintenance work — ACT — while an unfinished work order still records that it was taken out of service (INA or MAINT). Completing that work order flips a healthy device out of service.

Why this is a tool and not a migration

Migrations run automatically on deploy. This one rewrites real asset state, and the set of affected rows has to be reviewed by a human per facility, so it only runs when you ask it to.

The repair rule

A row is repaired only when all of these hold:

Condition	Value
Work order status	not terminal — OPEN, ASSIGNED, IN_PROGRESS, ON_HOLD, COMPLETED_PENDING_VERIFICATION
Device current status	ACT
Stored snapshot	INA or MAINT

When a row matches, the snapshot is rewritten to the device's current status. This keeps the device where it is and makes the unfinished work order's restore step a no-op, instead of flipping the device out of service when the work order closes.

Rows outside the rule are deliberately left alone:

• A device that is genuinely out of service (INA/MAINT) keeps its snapshot — the operator took it out of service on purpose.
• Terminal work orders (COMPLETED, CANCELLED, REJECTED, …) already restored their snapshot or no longer will. Rewriting them would only falsify history.

Prerequisites

• Backend connectivity to the same database the API uses: OVAHOL_DB_HOST, OVAHOL_DB_PORT, OVAHOL_DB_DATABASE, OVAHOL_DB_USERNAME, OVAHOL_DB_PASSWORD, OVAHOL_DB_SSLMODE.
• The tool reads every facility from core_public.facilities and resolves each to its tenant_facility_* schema. A facility whose schema was never provisioned is skipped.

The flow

Report, review, apply, verify. Never skip the review.

1. Report

make status-drift-report

Facility f5cf01ca-d866-4928-9437-413f0f7dc795 (tenant_facility_f5cf01ca_d866_4928_9437_413f0f7dc795)
  WO-DH-2610-56AF0C      MON-ICU-001        IN_PROGRESS snapshot INA -> ACT
  WO-DH-2610-BFA426      MON-ICU-001        OPEN       snapshot INA -> ACT

2 work order(s) across 1 facility schema(s).
Nothing was changed. Review the list, then re-run with --apply --confirm.

This mode is read-only. It never writes.

For a report you can attach to a change request, emit JSON:

make status-drift-json > drift-$(date +%F).json

2. Review

Check the list before applying:

• Is each device really in service? The report trusts device_record.status_id, so if a device shows ACT but is physically in the workshop, fix the device status first and re-run the report.
• Scan the tag numbers for anything you recognise as intentionally parked (INA because it is awaiting a part, MAINT because a repair is still open). Those rows should not appear here — if they do, the device was flipped to ACT by something else and the real fix is the device status, not the snapshot.

3. Apply

Scope to one facility first:

make status-drift-apply FACILITY=f5cf01ca-d866-4928-9437-413f0f7dc795

Then sweep the rest once the first facility looks right:

make status-drift-apply

Applied rows are marked in the output:

  WO-DH-2610-BFA426      MON-ICU-001        OPEN       snapshot INA -> ACT  [APPLIED]

Each facility is repaired inside a single transaction. A failure part-way rolls that facility back entirely, so no facility is left half-repaired.

4. Verify

make status-drift-verify

Prints No device-status drift found. and exits 0 when clean, exits 1 when drift remains. Suitable for a post-repair check or a CI gate.

Running in production (Coolify)

The tool ships in the backend image at /app/ovahol-status-drift. Nothing invokes it on deploy — not the entrypoint, not a migration, not a scheduled job. Run it by hand.

Open the backend application in Coolify, start a terminal on the running container, and report first:

/app/ovahol-status-drift

Apply once the list has been reviewed:

/app/ovahol-status-drift --apply --confirm --facility <facility-uuid>

The container already carries the database environment, so no flags or exports are needed.

Equivalent from a host with Docker access:

docker exec -it <backend-container> /app/ovahol-status-drift

Log the JSON report and its sign-off alongside the release notes for the deploy that ran it.

Audit trail

Every applied row writes to core_public.audit_logs with event_type = 'device_status_drift_repaired' and event_category = 'system', in the same transaction as the update:

SELECT
  metadata->>'device_tag_number'   AS device,
  metadata->>'work_order_code'     AS work_order,
  metadata->>'previous_snapshot'   AS was,
  metadata->>'corrected_snapshot'  AS now,
  created_at
FROM core_public.audit_logs
WHERE event_type = 'device_status_drift_repaired'
ORDER BY created_at DESC;

user_id is NULL because no user performed the change. metadata.source is status-drift-cli, and the row also records the facility id and tenant schema.

Safety properties

• Idempotent. After a successful apply the rows no longer match the rule, so a second run reports nothing and writes nothing.
• Read-only unless asked. --apply refuses to run without --confirm.
• Mutually exclusive modes. --apply and --verify cannot be combined.
• Row-locked. Candidate work orders are selected FOR UPDATE inside the transaction, so a concurrent status change cannot slip between the read and the write.
• Schema-scoped. The tenant schema comes from the facility id, is validated against information_schema.schemata, and is identifier-quoted before it reaches search_path.

Flags

Flag	Effect
(none)	Report only.
--json	Emit the report as JSON.
--apply	Rewrite the matching snapshots. Requires --confirm.
--confirm	Acknowledge that real asset state is being rewritten.
--facility <uuid>	Limit the run to one facility.
--verify	Exit 1 if any drift remains.

Exit codes: 0 clean or successful, 1 failure or drift still present under --verify.

Testing locally

On the dev stack the Postgres container is published on host port 5434:

cd backend
set -a; . ../.env; set +a
OVAHOL_DB_HOST=localhost OVAHOL_DB_PORT=5434 OVAHOL_DB_SSLMODE=disable \
  go run ./cmd/status-drift

The seeded dev tenant has no drift, so the report prints No device-status drift found. To exercise the apply path, flip one non-terminal work order's device to ACT so it matches the rule, run the report, apply, verify, then restore it.

SET search_path TO <tenant_schema>, lookup;

-- status ids: ACT = a2c3cfbd-842e-407a-8ef2-7801a7329a11,
--             INA = 52664a89-dfc0-4f64-80f1-7887fa1f3fde

-- seed a drifted row
UPDATE device_record SET status_id = 'a2c3cfbd-842e-407a-8ef2-7801a7329a11'
 WHERE device_tag_number = 'MON-ICU-001';

-- restore afterwards (and delete the audit rows the test wrote)
UPDATE device_record SET status_id = '52664a89-dfc0-4f64-80f1-7887fa1f3fde'
 WHERE device_tag_number = 'MON-ICU-001';
UPDATE work_order SET previous_device_status_id = '52664a89-dfc0-4f64-80f1-7887fa1f3fde'
 WHERE reference_code IN ('WO-DH-2610-56AF0C', 'WO-DH-2610-BFA426');
DELETE FROM core_public.audit_logs
 WHERE event_type = 'device_status_drift_repaired';

Troubleshooting

Symptom	Cause
No device-status drift found.	Nothing matches the rule. Not an error.
facility <uuid> does not exist	--facility was passed an id absent from core_public.facilities.
refusing to rewrite asset state without --confirm	--apply was run without --confirm.
--apply and --verify are mutually exclusive	Both flags were passed in one run.
N facility/facilities could not be processed	At least one schema failed; the rest were still processed. The per-facility error is printed.
package cmd/status-drift is not in std	go run needs the relative form, ./cmd/status-drift.