Restate CLI mistakenly considers deployments with all 'completed' invocations as still draining

[brainhart]

Restate CLI version: restate-cli 1.5.6 (cb9eab4 aarch64-apple-darwin 2025-12-11)

When trying to remove a deployment which has no active invocations, but has some 'completed' invocations still retained, Restate CLI considers the deployment still actively draining. I suspect this is due to some stale SQL in the CLI now that 'completed' invocations are retained for some period of time?

In the below example, you'll see that the status in the CLI is still 'Draining', despite the restate sql showing that all invocations are in a 'completed' status.

CLI output:

❯ restate deployments remove dp_122hLba7ZVSrXGMXaWWzn1v
 ID:              dp_122hLba7ZVSrXGMXaWWzn1v
 Transport:       HTTP/2.0
 Protocol Style:  Bidirectional Stream
 Endpoint:        <redacted>
 Created at:      2025-12-29T21:52:27.010000000Z
 Protocol:        [5, 6]
 Status:          Draining
 Invocations:     5317
Services:
  <redacted>

❌ Deployment is still Draining. There are 5317 invocations that will break if you proceed
with this operation. Please make sure in-flight invocations are completed (deployment is Drained)
or killed/cancelled before continuing.

Error: If you accept the risk of breaking in-flight invocations, you can use --force to forcefully remove this deployment.

❯ restate sql '
  SELECT
    pinned_deployment_id,
    status,
    COUNT(*) as num
  FROM sys_invocation_status
  GROUP BY pinned_deployment_id, status
  ' --json | jq
8 rows. Query took 9.55410525s
[
  {
    "status": "scheduled",
    "num": 2090
  },
  {
    "pinned_deployment_id": "dp_14nqf5mx4RnMg933MGEmgzT",
    "status": "completed",
    "num": 8308
  },
  {
    "status": "invoked",
    "num": 8
  },
  {
    "pinned_deployment_id": "dp_11SFGHsvDuOvSGXSW5DNjgd",
    "status": "invoked",
    "num": 15
  },
  {
    "pinned_deployment_id": "dp_12DP7CiZnnZL7rYdxbnE6GJ",
    "status": "invoked",
    "num": 10
  },
  {
    "pinned_deployment_id": "dp_11SFGHsvDuOvSGXSW5DNjgd",
    "status": "completed",
    "num": 5201
  },
  {
    "pinned_deployment_id": "dp_122hLba7ZVSrXGMXaWWzn1v",
    "status": "completed",
    "num": 5317
  },
  {
    "status": "inboxed",
    "num": 321
  }
]

[brainhart]

We've been using this SQL as an alternative for the logic of if a deployment is still in use

WITH active_invocations AS (
  SELECT id, pinned_deployment_id, status
  FROM sys_invocation_status
  WHERE status != 'completed'
)
SELECT
  dp.id   AS deployment_id,
  dp.endpoint,
  inv.status as invocation_status,
  COUNT(inv.id) AS invocation_count
FROM sys_deployment dp
LEFT JOIN active_invocations inv
  ON inv.pinned_deployment_id = dp.id
GROUP BY dp.id, dp.endpoint, inv.status
HAVING COUNT(inv.id) > 0
   OR EXISTS (
        SELECT 1
        FROM sys_service ss
        WHERE ss.deployment_id = dp.id
      )
ORDER BY deployment_id, invocation_status;