# deactivate-obsolete-flows

Phase 0 of the Flow Engine compatibility work. Marks the 19 business-audited
"obsolete" flows inert so the trigger listener stops dispatching to them. No
application code changes — DB-only, reversible.

> The design doc says "~16"; the exact list resolves to 19 once the `BN_*`
> shorthand (3 flows) and the `Account_Related_Leads_Based_on_Website_Domain_Create/Update`
> shorthand (2 flows) are expanded to their DB `DeveloperName` values.

Source of truth for the DROP list is the "Scope After Business Audit (Phase 0)"
section of `amber-main-design-20260416-230159.md`. The list is also hard-coded
in `main.go` (`obsoleteFlows`). Update both together.

## What it does

For each `DeveloperName` in the DROP list:

1. Opens a transaction.
2. `SELECT Id, ActiveVersionId FROM FlowDefinition WHERE DeveloperName = ? FOR UPDATE` — locks the row and captures the pre-change state (printed to stdout as a SQL comment for rollback auditing).
3. Dumps the pre-change `flow.Id → Status` pairs for every version (also as SQL comments).
4. `UPDATE FlowDefinition SET ActiveVersionId = '' WHERE Id = ? AND ActiveVersionId <> ''` — guarded to skip rows already cleared.
5. `UPDATE flow SET Status = 'Obsolete' WHERE DefinitionId = ? AND Status <> 'Obsolete'` — guarded to skip rows already Obsolete.
6. Commits. Any failure rolls back the whole pair.

Before any mutation, a preflight `SELECT` confirms every `DeveloperName` in the list resolves to exactly one `FlowDefinition` row. Missing or duplicate targets abort the run before any write.

Both UPDATEs are idempotent: re-running produces `0 rows affected` once everything is settled.

## Usage

### Dry run (default — no DB writes, no creds needed)

```bash
cd scripts/deactivate-obsolete-flows
go run .
# or, equivalently:
go run . --dry-run
```

Emits the exact SQL that would be executed to stdout, then exits. No DB connection.

### Apply to the test database

Requires all TiDB env vars. **The script refuses to --apply unless `TIDB_USER` starts with the known test-DB prefix.** This is mechanical protection against accidentally pointing at prod or staging credentials.

```bash
export TIDB_HOST=gateway01.us-east-1.prod.aws.tidbcloud.com
export TIDB_PORT=4000
export TIDB_USER=4Qbn3F3nyaU9DLk.root           # test DB — enforced by code
export TIDB_PASSWORD=<secret>
export TIDB_DATABASE=nexuscrm
go run . --apply | tee apply-$(date +%Y%m%d-%H%M%S).log
```

The `tee` capture doubles as the rollback reference: every transaction emits the pre-change `ActiveVersionId` and Flow-version `Status` values as SQL comments before mutating.

Output per flow:
```
-- Account_PoC_Status: FlowDefinition.Id=... pre-change ActiveVersionId="..."
--   flow.Id=... pre-change Status="Active"
--   flow.Id=... pre-change Status="Draft"
Account_PoC_Status: FlowDefinition rows=1, Flow rows=3 (committed)
```

Final line:
```
total rows affected: FlowDefinition=N, Flow=M
```

### Running against a non-test DB (requires explicit override)

Only for operators who have sign-off (e.g. a release engineer running this against staging in a scheduled window). The escape hatch makes the override auditable:

```bash
go run . --apply --i-know-this-is-not-test
```

A `WARNING:` line is printed to stderr. Do not use this without explicit approval.

### Verify post-apply

```sql
SELECT fd.DeveloperName, fd.ActiveVersionId,
       SUM(CASE WHEN f.Status <> 'Obsolete' THEN 1 ELSE 0 END) AS non_obsolete_versions
FROM FlowDefinition fd
LEFT JOIN flow f ON f.DefinitionId = fd.Id
WHERE fd.DeveloperName IN (
  'Account_PoC_Status','Account_Partner_Sales_Owner_Set','Account_status_Flow',
  'Partner_Account_CM_Approval','RevRec_Account','Quote_Account_Opportunity_field',
  'Sync_Account_Status_to_Lead','Account_And_Lead_Sum_Approval_Poc',
  'Account_Auto_Create_Org','Account_Related_Leads_Based_on_Website_Domain_Create',
  'Account_Related_Leads_Based_on_Website_Domain_Update',
  'BN_Account_Lead_Relate_Leads_to_Accounts',
  'BN_Account_Related_Leads_Based_on_Website_Domain',
  'BN_Lead_Relate_to_Account_by_Email_Domain_Flow',
  'Lead_Owner_Is_Change_By_Account_Send_Email','LeadSetOwnerByAccount',
  'Org_Set_Account','Auto_Set_Poc_Account_Contact_Lead_opportunity',
  'update_account_latest_month_end_arr'
)
GROUP BY fd.DeveloperName, fd.ActiveVersionId;
```

Every row should show `ActiveVersionId` empty/NULL and `non_obsolete_versions = 0`.

## Rollback

The `--apply` log written by `tee` above contains the pre-change state as SQL comments, one block per flow. Reversal is row-by-row:

```sql
-- For each block in the log:
UPDATE FlowDefinition
SET ActiveVersionId = '<pre-change ActiveVersionId>'
WHERE Id = '<FlowDefinition.Id from log>';

UPDATE flow
SET Status = '<pre-change Status>'
WHERE Id = '<flow.Id from log>';
```

No data is dropped by this script. Every change is a column update on an existing row.

## Testing

```bash
go test -race ./...
```

Unit tests cover:

- DROP list non-empty, length sanity (~16-20 entries), no duplicates, no empty strings
- `buildStatements` emits two guarded, parameterised UPDATEs per flow
- Dry-run prints SQL and never attempts a DB connection
- Test-DB gate accepts the known test user prefix
- Test-DB gate rejects staging/prod users without the escape hatch
- Test-DB gate honours `--i-know-this-is-not-test`
- `run()` with `apply=true` short-circuits before any DB access when the gate fails

## Safety summary

- Default is `--dry-run`. `--apply` must be explicit.
- `--apply` refuses to run unless `TIDB_USER` starts with the allow-listed test-DB prefix (`4Qbn3F3nyaU9DLk`).
- `--i-know-this-is-not-test` is the only way past the gate. It prints a `WARNING:` to stderr so the override is auditable.
- Preflight aborts if any target `DeveloperName` is missing from the DB or resolves to more than one row.
- Each flow's pair of UPDATEs runs inside a single transaction with `SELECT ... FOR UPDATE`. Concurrent writers cannot resurrect a flow between the two updates.
- Every mutation is preceded by a stdout SQL comment containing the pre-change state, so `tee` capture is sufficient for per-row rollback.
- 5-minute overall timeout. Network stalls fail loudly instead of hanging.
- TLS auto-enabled for any non-localhost host.