Support `finished_after` filter in Deployment API
Release notes
With this update, you can now filter the list of deployments in a project by finished_before
or finished_after
. Previously, filtering was allowed only by the updated dates. This allows you to more efficiently search for the relevant deployments in your workflow.
Problem
Deployment API has updated_before
filter, which was introduced in #33101 (closed). The issue describes that:
An Enterprise customer is pulling data from the "list project deployments" API endpoint to populate a custom-built dashboard. The customer only wants to display the latest changes. However, the customer has no way to restrict the API results to the latest changes. Instead, the customer retrieves all records, checks one-by-one and processes only the records updated after the latest
updated_at
value in the last batch retrieved. The customer has asked for the ability to query forupdated_at
values after a specified timestamp.
updated_at
filtering would make sense in Issues or MRs context, however, it does NOT make sense in deployments context. For example, looking at a query that customers are currently executing:
SELECT
"deployments".*
FROM
"deployments"
INNER JOIN "environments" ON "environments"."id" = "deployments"."environment_id"
WHERE
"deployments"."project_id" = $ 1
AND "deployments"."updated_at" <= $ 2
AND "environments"."name" = $ 3
ORDER BY
"deployments"."iid" DESC
LIMIT
$ 4 OFFSET $ 5
This does NOT fetch the latest changes to the environment that the original issue was born for. But this fetches mixed states of deployment records that includes:
- Deployments that actually deployed to the target environment. This is the "changes" in deployment context.
- Deployment jobs that still running to the target environment. (It should be excluded)
- Failed deployment jobs that didn't make into the target environment. (It should be excluded)
- Canceled deployment jobs that didn't make into the target environment. (It should be excluded)
- These mixed-states deployment rows are ordered by
iid
, which is creation order, not update order (See more #29884 (comment 549584527)) (i.e. this sorting is messing upupdated_at
filtering)
We should allow them to filter by finished_at
instead. Here is the correct query/parameter combination to fetch latest changes/deployments to a specific environment.
SELECT
"deployments".*
FROM
"deployments"
WHERE
"deployments"."environment_id" = 1069567
AND "deployments"."status" = 2
AND "deployments"."finished_at" <= '2021-04-14T09:05:29Z'
ORDER BY
"deployments"."finished_at" DESC
LIMIT
100 OFFSET 100
FYI, this query can be executed in 40ms, which is 2000x faster than the query above.
Proposal
Allow finished_before
and finished_after
query parameters to be passed to Deployment LIST API
diff --git a/lib/api/deployments.rb b/lib/api/deployments.rb
index ee0a026d7ac..ec4577cf460 100644
--- a/lib/api/deployments.rb
+++ b/lib/api/deployments.rb
@@ -24,6 +24,8 @@ class Deployments < ::API::Base
optional :sort, type: String, values: DeploymentsFinder::ALLOWED_SORT_DIRECTIONS, default: DeploymentsFinder::DEFAULT_SORT_DIRECTION, desc: 'Sort by asc (ascending) or desc (descending)'
optional :updated_after, type: DateTime, desc: 'Return deployments updated after the specified date'
optional :updated_before, type: DateTime, desc: 'Return deployments updated before the specified date'
+ optional :finished_after, type: DateTime, desc: 'Return deployments finished after the specified date'
+ optional :finished_before, type: DateTime, desc: 'Return deployments finished before the specified date'
optional :environment,
type: String,
desc: 'The name of the environment to filter deployments by'