--- apiVersion: data-spec/v1 id: backfill-flow-source-20260423e title: "Backfill Flow.Source='standard' for pack-seeded rows (runs in bootstrap, NOT as SQL spec)" description: "Audit-side documentation for the one-shot Go migrations that mark pack-seeded Flow rows as Source='standard' on upgraded clusters. The executable paths live in standard_flows.go, gated by _System_BootstrapState rows 'flow_source_backfill_v1' and 'flow_source_backfill_v2'. This file is kept as a paper trail, NOT as an apply target." issue: "P1 finding on PR work/flow-engine-atomic-run-mode" created: 2026-04-23 scope: both status: runs-in-bootstrap tables_modified: - Flow tables: [Flow] idempotent: true risk: low reviewers: ["@codeowners"] depends_on: [] --- # Backfill `Flow.Source` = 'standard' for pack-seeded rows **Status: not applied via data-spec runner. The actual backfill lives in Go bootstrap code at `backend/internal/bootstrap/standard_flows.go`: `migrateFlowSourceBackfill` is gated by `flow_source_backfill_v1`, and `migrateHistoricalFlowSourceBackfill` is gated by `flow_source_backfill_v2`. This file is retained as an audit artifact documenting what those migrations do and why.** ## Why this is not a SQL data-spec The Source column was added in PR `work/flow-engine-atomic-run-mode` to close the upgrade-path gap where a pack fix to a shipped `.flow.json` would land as an inactive Draft because the seeder could not tell "pack-owned, untouched" from "admin-owned active version". On an upgraded cluster, every pre-existing Flow row reads as `Source='custom'` (the DDL column default), because the old code never wrote the column. We need to flip the rows that are actually pack-authored. The obvious SQL approach — ```sql UPDATE Flow SET Source = 'standard' WHERE Id LIKE 'flowver_std_%' AND Source = 'custom'; ``` — is **unsafe**. It only sees the Id prefix and the current column value. That's not enough to distinguish: | State | `Source` today | Correct outcome | |---|---|---| | (a) admin never touched; column default | `'custom'` | flip to `'standard'` | | (b) admin PATCHed in place before the Source column existed | `'custom'` | must stay `'custom'` | Admin-edited rows in case (b) would be silently re-claimed as platform metadata, and the next pack upgrade's auto-advance branch would then obsolete the admin's content. That is data loss in the metadata-ownership sense. The discriminator is the pack bytes embedded in the binary. A pure SQL spec cannot carry those bytes (they are JSON blobs up to tens of KB each); a Go migration can, via `go:embed`. The migration therefore lives in bootstrap. ## What the Go migration does (behavioural contract) See `migrateFlowSourceBackfill` in `backend/internal/bootstrap/standard_flows.go`. Equivalent SQL, run once per seed file, is: ```sql UPDATE Flow SET Source = 'standard', LastModifiedDate = WHERE DefinitionId = AND Id LIKE 'flowver_std__v%' AND Source = 'custom' AND Metadata = .flow.json>; ``` The fourth predicate — `Metadata = ` — is the byte-match that makes the v1 backfill safe. A row with an admin edit has different bytes and is skipped. ### v2 historical-pack repair PR #2032 changes `Std_ConvertLead` and `Std_RelinkLeadChildren`. Upgraded clusters can therefore hold untouched standard-flow rows whose bytes came from the previous shipped pack: v1 cannot match them against today's bytes, so they would remain `Source='custom'`, the new standard version would land as Draft, and Lead Convert would miss the protected standard-action/internal-scope path. The v2 migration repairs only known historical L1 pack bytes by SHA-256. Its effective predicate is: ```sql UPDATE Flow f JOIN FlowDefinition fd ON fd.Id = f.DefinitionId SET f.Source = 'standard', f.LastModifiedDate = WHERE f.DefinitionId = AND f.Id LIKE 'flowver_std__v%' AND f.Source = 'custom' AND (fd.IsCustomized = 0 OR fd.IsCustomized IS NULL) AND SHA2(f.Metadata, 256) IN (); ``` The known hashes are limited to the previous standard pack bytes for `Std_ConvertLead` and `Std_RelinkLeadChildren`. Rows with unknown bytes, customized FlowDefinition ownership, non-standard ids, or tooling-authored versions stay `custom`. This keeps runtime on the single SSoT (`Flow.Source`) without adding a long-lived "old pack" branch. ### Gate stamping order Each gate row (`flow_source_backfill_v1`, `flow_source_backfill_v2`) is stamped **after every seed's UPDATE succeeds**, not per-seed mid-loop. A partial-success restart leaves that gate unstamped, so the next boot re-runs the full pass. Idempotent by construction: the UPDATE's `Source = 'custom'` predicate filters rows already flipped. ## Post-boot audit (operator-runnable SELECT) After the migrations have run (look for both `flow_source_backfill_v1` and `flow_source_backfill_v2` rows in `_System_BootstrapState` with `Status='applied'`), confirm: ```sql SELECT Source, COUNT(*) FROM Flow WHERE Id LIKE 'flowver_std_%' GROUP BY Source; ``` Expected distribution on an org with no admin edits: single row `Source = standard`, count = number of seeded versions. A non-zero `custom` count here flags one of: 1. A pack-seeded row whose stored Metadata diverges from both today's shipped pack bytes and the known historical pack hashes. Either the admin edited it (correct to leave `'custom'`), or the pack content came from an older release not represented in the v2 manifest. 2. A row with a pack-shaped Id that did not originate from the seeder (should not happen under the current Id derivation, but can arise from manual SQL imports). Audit mapping back to pack files: ```sql SELECT fd.DeveloperName, f.VersionNumber, f.Source, f.LastModifiedDate FROM FlowDefinition fd LEFT JOIN Flow f ON f.DefinitionId = fd.Id WHERE fd.Id LIKE 'flowdef_std_%' ORDER BY fd.DeveloperName, f.VersionNumber; ``` ## Rollback (defensive, not required in normal operation) Reversing the backfill by itself is operationally meaningless — the `Source` column is read ONLY by the seeder's auto-advance decision, and clearing it back to `'custom'` just makes the seeder conservative again (today's default). There is no data-integrity concern. To disable the migrations on a specific cluster (e.g. during a staged rollout), mark the gate rows as applied before booting the new binary: ```sql INSERT INTO `_System_BootstrapState` (StateKey, RunID, Phase, Status, LastError, CreatedDate, LastModifiedDate) VALUES ('flow_source_backfill_v1', 'manual-skip', 'manual-skip', 'applied', '', NOW(), NOW()), ('flow_source_backfill_v2', 'manual-skip', 'manual-skip', 'applied', '', NOW(), NOW()) ON DUPLICATE KEY UPDATE Status='applied', LastModifiedDate=NOW(); ``` This tells the bootstrap migrations to short-circuit on the next boot. All matching pack-seeded rows remain `Source='custom'`, so the seeder takes the conservative branch on every pack change — same shipping behaviour as pre-PR. ## Greenfield behaviour Greenfield clusters do not trigger the backfill in a meaningful way. The seeder writes `Source='standard'` on `INSERT`, so the `Source = 'custom'` predicate in the backfill UPDATE finds zero rows. The gate is still stamped (to mark the migration as applied), but `0 rows affected`.