Support finished_before and finished_after in Deployments API

What does this MR do and why?

This merge request adds the following two options to filter deployments by in the List Project Deployments endpoint of Deployments API:

• finished_after: to retrieve deployments for which finished_at is after a specified datetime.
• finished_before: to retrieve deployments for which finished_at is before a specified datetime.

They both require specifying order_by=finished_at and status=success as DeploymentsFinder will only retrieve deployments by those options in the presence the order_by and status filters.

Resolves #332652 (closed). Please note, another merge request is submitted to update documentation to match the changes introduced here.

Database Query Plans

Query with finished_after used

Here is the SQL query (for all deployments after 2018 with pagination):

SELECT "deployments".* FROM "deployments" WHERE "deployments"."project_id" = 278964 AND (finished_at >= '20181126T000000+0200') AND "deployments"."status" = 2 ORDER BY "deployments"."finished_at" ASC LIMIT 20 OFFSET 0

And here is the query plan for that:

 Limit  (cost=0.57..28.95 rows=20 width=145) (actual time=0.032..0.048 rows=20 loops=1)
   Buffers: shared hit=11
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using index_deployments_on_project_and_finished on public.deployments  (cost=0.57..759.84 rows=535 width=145) (actual time=0.031..0.044 rows=20 loops=1)
         Index Cond: ((deployments.project_id = 278964) AND (deployments.finished_at >= '2018-11-25 22:00:00+00'::timestamp with time zone))
         Buffers: shared hit=11
         I/O Timings: read=0.000 write=0.000

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13641/commands/47901

Query with finished_before and finished_after used

Here's the SQL query (for all deployments between 2018 and 2022 with pagination):

SELECT "deployments".* FROM "deployments" WHERE "deployments"."project_id" = 278964 AND (finished_at < '20221126T000000+0200') AND (finished_at >= '20181126T000000+0200') AND "deployments"."status" = 2 ORDER BY "deployments"."finished_at" ASC LIMIT 20 OFFSET 0

And here is the query plan:

Limit  (cost=0.57..29.01 rows=20 width=145) (actual time=0.020..0.036 rows=20 loops=1)
   Buffers: shared hit=11
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using index_deployments_on_project_and_finished on public.deployments  (cost=0.57..755.55 rows=531 width=145) (actual time=0.019..0.032 rows=20 loops=1)
         Index Cond: ((deployments.project_id = 278964) AND (deployments.finished_at < '2022-11-25 22:00:00+00'::timestamp with time zone) AND (deployments.finished_at >= '2018-11-25 22:00:00+00'::timestamp with time zone))
         Buffers: shared hit=11
         I/O Timings: read=0.000 write=0.000

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13641/commands/47903

Please note I have used the id of gitlab-org/gitlab project to make sure we get enough hits.

How to set up and validate locally

• In your local GDK setup, create a project if you don't have one already.
• Inside that project, create a number of successful deployments (e.g. via the API), let's say 2 (with a few minutes in between).

For finished_after:

• Send a request to list deployments:
  • Replace PROJECT_ID with the id of your project.
  • Replace USE_YOUR_OWN_TOKEN_HERE with your access token.
  • Replace FINISHED_DATETIME with the datetime you want to check (in ISO-861 format).

curl -H "PRIVATE-TOKEN: USE_YOUR_OWN_TOKEN_HERE" -X GET "http://gdk.test:3000/api/v4/projects/PROJECT_ID/deployments?finished_after=FINISHED_DATETIME&order_by=finished_at&status=success"

• Verify the deployment returned is the one finished running after the datetime you passed.

For finished_before:

• Do the same, replacing finished_after with finished_before and update FINISHED_DATETIME accordingly.

curl -H "PRIVATE-TOKEN: USE_YOUR_OWN_TOKEN_HERE" -X GET "http://gdk.test:3000/api/v4/projects/PROJECT_ID/deployments?finished_before=FINISHED_DATETIME&order_by=finished_at&status=success"

• Verify the deployment returned is the one finished running before the datetime you passed.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.