Device-Type Flow V2 Migration and Reset

Migration 20260806000100000_device_type_field_contract_flow_v2.sql is a forward migration with an intentional development-data reset. Its down section restores only the prior schema; deleted rows require the pre-migration backup.

Preserved and deleted data

Preserved: Raw rows and idempotency keys, Devices, Device Types, organization assignments, audit logs, custom scripts and versions, E-mail templates and recipient bindings, and Flow-template metadata. Existing attribute definitions are converted to current-only Device fields. Non-Flow current attribute values are converted to device_current_values.

Deleted: Flow assignments, Flows and versions, parameter values, test fixtures, executions and errors, Actions and Dead Letters, rate-limit runtime rows, Flow-generated Measurements and revision heads, Flow-generated attribute values, and Flow-related outbox events. Released V1 template versions are marked deprecated and remain readable.

Local and test rollout

1. Create and verify a recoverable database backup or snapshot.
2. Stop MQTT, Raw, Flow, and Action workers so no derived messages race the reset.
3. Set the existing one-run gate SENS_VISUAL_FLOW_BACKUP_CONFIRMED=true only after verifying the backup, then run pnpm db:migrate with the normal database connection contract.
4. Purge and bootstrap these derived streams: SENS_RAW_STORED_V1, SENS_FLOW_ACTIONS_V1, SENS_FLOW_REPLAYS_V1, and SENS_FLOW_DEAD_LETTERS_V1. Preserve SENS_RAW_INGESTION_V1.
5. Deploy Platform API, Flow worker, Action worker, and Web Console with V2 support as one compatibility unit.
6. Restart services and verify readiness, consumer lag, outbox failures, and database errors.
7. Create the EM320-TH fields temperature, humidity, and battery, create its Flow, release and activate it, and execute the acceptance payload.

Use a non-production test database first:

SENS_TEST_DATABASE_URL=postgresql://... pnpm db:test:migrate

Then run database and HTTP integration tests with SENS_RUN_DATABASE_INTEGRATION_TESTS=true.

Acceptance and recovery

For Netmore timestamp 2026-08-06T13:28:01.578Z, the EM320-TH proof must atomically produce three Measurements and three Current Values with exactly that timestamp. A later processed older Raw row adds Measurements only. Injecting a failure into either storage target must leave both targets unchanged.

After V2 activation, old Flow binaries are incompatible. Prefer a forward-fix. Restore the backup only when the accepted restore point and resulting data-loss window are understood. Deleted development Flows cannot be reconstructed by the SQL down migration.