Design safe worker-update SQL helpers and linked-project CLI commands #712

Status and next step

Design proposal for future /grill-with-docs, not an approved specification or an implementation-ready ticket. Names, storage, command syntax, and final scope remain provisional. Creating this issue does not authorize production queries, deployments, migrations, or automatic resume.

Goal: replace the repeated disable → deprecate → inspect → deploy → restore SQL choreography with a small pgflow-owned worker-update interface and matching CLI commands. Keep lifecycle correctness in PostgreSQL; make the CLI a small caller, not a second implementation of that protocol.

Why this is useful

The deployment guide asks operators to save worker_functions.enabled, disable automatic restarts, deprecate workers, inspect heartbeats and in-flight tasks, deploy code, then restore exact prior settings. Repeating this sequence across guides makes safety errors easy:

• PR #670's deployment review flags an example that always restores enabled = true, despite instructions to preserve an intentionally disabled function.
• The Terminalize tasks after the stalled-task requeue limit #689 final review found the analogous problem in the recovery-cron upgrade recipe: restore activated jobs that were already paused before maintenance.

Helpers should own exact-state preservation and restoration, rather than merely shortening the raw UPDATE statements.

Current implementation seams include pgflow.worker_functions.enabled, pgflow.workers.deprecated_at, worker heartbeat/deprecation checks, and task rows identifying their last worker. Cooperative deprecation exists today; this proposal does not invent immediate runtime eviction. The pgflow CLI currently registers only install in pkgs/cli/src/index.ts.

Proposed database interface

Prefer pgflow.* initially, rather than a new pgflow_deploy schema. Reconsider only if a distinct permissions boundary or other concrete requirement warrants it.

The interface must retain its saved state across SQL sessions and CLI restarts. PostgreSQL owns the authoritative operation state; a local temporary file must not be the only record. Select the smallest storage model that meets the retry and concurrency contract during grilling.

Required design constraints:

• Retrying preparation after a lost response must recover the original operation, not overwrite its saved enabled values with the temporary false values. Decide the caller request-id/token contract explicitly.
• Overlapping updates need a defined rule. An old finish must never undo a newer pause or restore another operation's settings. Do not use missing saved state as a reason to enable anything.
• Validate the selected function set and preserve all previously disabled functions. Serialize with startup registration/acceptance under a reviewed lock order; inspect pending invocations as well as already-registered workers.
• A single-worker deprecation helper may be useful, but deprecation alone permits an enabled function to restart with the old deployed code. Decide whether it earns a separate public operation.

CLI addition: delegate query transport to Supabase

Yes: a linked, authenticated Supabase project can provide the remote query transport, without pgflow owning another database driver or credential store.

Capability checked while filing: npx --yes supabase@latest --version returned 3.0.0. db query --help accepts positional SQL. It defaults to local; --remote targets the linked project, and --remote=<ref|branch|url> selects an explicit remote target. --read-only runs the statement in a read-only transaction. Therefore linking a project alone does not redirect an unqualified query to production.

An existing read-only query illustrates the verified transport syntax:

npx supabase@3.0.0 db query --remote --read-only \
  'select function_name, enabled from pgflow.worker_functions order by function_name;'

Only help and version commands ran during this investigation; no remote database query ran. Pinning/minimum-version policy and exact structured-output handling still need a decision before implementation.

Illustrative pgflow commands, not existing commands or final names:

pgflow workers prepare-update <function...> --remote
pgflow workers update-status <update-token> --remote
pgflow workers finish-update <update-token> --remote

The CLI resolves the target, calls the named SQL functions, and renders their results. Actual code deployment can remain an explicit supabase functions deploy step between preparation/drain and restoration. Do not add a second flow compiler, a generic SQL console, or a deployment orchestrator unless a concrete requirement justifies it.

Transport and production safeguards

• Require an explicit remote target and show the resolved project/branch before a mutation. A project link is not write approval. Unknown or missing targets must fail, not fall back to another database.
• Require target-specific confirmation for mutations, with a deliberate noninteractive approval path. Do not silently forward Supabase's --yes; pgflow must obtain its own approval before forwarding it. An inherited confirmation environment variable must not bypass that gate.
• Use an argv-based Node subprocess invocation with shell: false, not shell-command interpolation. Encode SQL values safely and validate identifiers/tokens; avoiding a shell does not itself prevent SQL injection.
• Reuse Supabase authentication/linking, check supported CLI capabilities, preserve useful errors/exit codes, and avoid logging credentials or connection URLs. Status should use read-only execution. Machine-readable output must omit the current CLI startup banner and interactive prompts. Choose a documented installed-CLI/npx version policy.
• Keep mutation retries under the database operation's idempotency contract. On failure, leave the function paused and show the update token and recovery action; never automatically resume an uncertain deployment.

Guarantee limits and exclusions

Deprecation requests a cooperative drain. Workers observe it on subsequent lifecycle checks; a helper return is not an immediate no-new-claims barrier. A stale heartbeat or a task leaving started is an observation, not proof that a handler or external effect stopped.

Keep strict lifetime ownership and claim-time fencing in #697. Keep unknown external effects in #698. Neither issue is an automatic prerequisite to wrapping today's cooperative behavior honestly. Define HTTP-supervised versus process-worker behavior; external supervisors/direct invocations may need an explicit operator step.

Global recovery-cron maintenance is a separate scope. A function-scoped worker update must not silently pause recovery for all flows. If recovery pause/restore helpers become necessary, design their own exact-state contract, including absent jobs and escalation introduced by upgrades.

No hosted-runtime kill guarantee, automatic schema migration, global producer pause, exactly-once external-effect claim, or automatic release/deploy operation is included.

Questions for future grilling

1. What is the smallest useful operation set, and is deployment/update vocabulary accurate? Do we need per-worker rotation now or only coordinated function-set replacement?
2. What identifies an operation across retries, lost responses, crashes, and overlapping operators? What storage and expiry rules preserve an exact, safe restore?
3. Which observations permit an operator to proceed, what counts as an unknown drain outcome, and how do pending startup invocations, legacy binaries, and process workers affect that contract?
4. What should the CLI expose for targets, confirmation, version support, machine-readable output, optional waiting/timeouts, and recovery after an interrupted deploy?
5. Can this remain one bounded delivery, or does a runnable prototype reveal a protocol question that needs its own decision ticket?

Evidence required before closing an eventual implementation

• Transactional tests cover enabled/disabled mixtures, invalid function selections, lost-response retry, overlapping prepares, stale finish, and preservation of old workers' deprecation.
• Status tests cover current/stale heartbeats, in-flight tasks, unexpected workers, pending starts, and an honest distinction between observed drain and guaranteed termination.
• CLI tests cover argv/SQL encoding, explicit target selection, missing linking/auth, unsupported Supabase versions, confirmation, read-only status, interrupted subprocesses, and error propagation. Use a fake subprocess plus an isolated local integration check, not production mutations.
• Documentation uses the same functions/CLI operations, preserves direct SQL as an escape hatch, and states the cooperative-drain limits.

Workflow and related work

Start later with /grill-with-docs against this issue. Record settled terminology and hard-to-reverse decisions in the repository's glossary/ADRs. Do not triage this self-authored proposal as an incoming raw report, and do not treat it as ready for autonomous implementation.

If grilling settles the design within one session, use /implement. If the settled build spans sessions, use /to-spec → /to-tickets, then implement the resulting graph. Use /wayfinder only if the design grows into several interdependent decisions that cannot fit one grilling session; a wayfinder map holds decision tickets, not premature implementation slices.

Related: #689, worker startup #696 / PR #711, deployment procedure #654 / PR #670, strict worker ownership #697, external-effect uncertainty #698, and portability #665. Recheck the merged startup protocol before implementation. This proposal does not add a blocker to the existing per-step queue release epic #653 or the application-adoption plan #710.