Adds scoped internal id to Ml::Candidates

What does this MR do and why?

Adds an internal_id to ml_candidates. Ideally this column would be named iid, but the name is currently being used and we are in the process of renaming it, so 16.0 it should become iid. To add this, it was also necessary to add an association between ml_candidates and project.

All tables on these MR were added as part of feature flag :ml_candidate_experiments, which has not been enabled yet, thus tables are small. No feature behaviour changes, only addition of columns.

Part of #398170 (closed)

Database

Migrations

20230321162810 AddProjectIdToMlCandidates

UP

main: == 20230321162810 AddProjectIdToMlCandidates: migrating =======================
main: -- add_column(:ml_candidates, :project_id, :bigint, {:null=>true})
main:    -> 0.0027s
main: == 20230321162810 AddProjectIdToMlCandidates: migrated (0.0067s) ==============

DOWN

main: == 20230321162810 AddProjectIdToMlCandidates: reverting =======================
main: -- remove_column(:ml_candidates, :project_id, :bigint, {:null=>true})
main:    -> 0.0006s
main: == 20230321162810 AddProjectIdToMlCandidates: reverted (0.0031s) ==============

20230321162902 AddIndexOnProjectIdOnMlCandidates

UP

main: == 20230321162902 AddIndexOnProjectIdOnMlCandidates: migrating ================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0678s
main: -- index_exists?(:ml_candidates, :project_id, {:name=>"index_ml_candidates_on_project_id", :algorithm=>:concurrently})
main:    -> 0.0028s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:ml_candidates, :project_id, {:name=>"index_ml_candidates_on_project_id", :algorithm=>:concurrently})
main:    -> 0.0018s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20230321162902 AddIndexOnProjectIdOnMlCandidates: migrated (0.0859s) =======

DOWN

main: == 20230321162902 AddIndexOnProjectIdOnMlCandidates: reverting ================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0006s
main: -- indexes(:ml_candidates)
main:    -> 0.0023s
main: -- remove_index(:ml_candidates, {:algorithm=>:concurrently, :name=>"index_ml_candidates_on_project_id"})
main:    -> 0.0009s
main: == 20230321162902 AddIndexOnProjectIdOnMlCandidates: reverted (0.0103s) =======

20230321163051 AddProjectIdForeignKeyToMlCandidates

UP

main: == 20230321163051 AddProjectIdForeignKeyToMlCandidates: migrating =============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE ml_candidates ADD CONSTRAINT fk_2a0421d824 FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0022s
main: -- execute("ALTER TABLE ml_candidates VALIDATE CONSTRAINT fk_2a0421d824;")
main:    -> 0.0035s
main: == 20230321163051 AddProjectIdForeignKeyToMlCandidates: migrated (0.0255s) ====

DOWN

main: == 20230321163051 AddProjectIdForeignKeyToMlCandidates: reverting =============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- remove_foreign_key(:ml_candidates, {:column=>:project_id})
main:    -> 0.0031s
main: == 20230321163051 AddProjectIdForeignKeyToMlCandidates: reverted (0.0264s) ====

20230321170734 AddInternalIdToMlCandidates

UP

main: == 20230321170734 AddInternalIdToMlCandidates: migrating ======================
main: -- add_column(:ml_candidates, :internal_id, :bigint, {:null=>true})
main:    -> 0.0007s
main: == 20230321170734 AddInternalIdToMlCandidates: migrated (0.0037s) =============

DOWN

main: == 20230321170734 AddInternalIdToMlCandidates: reverting ======================
main: -- remove_column(:ml_candidates, :internal_id, :bigint, {:null=>true})
main:    -> 0.0013s
main: == 20230321170734 AddInternalIdToMlCandidates: reverted (0.0078s) =============

20230321170803 AddIndexOnProjectIdOnInternalIdToMlCandidates

UP

main: == 20230321170803 AddIndexOnProjectIdOnInternalIdToMlCandidates: migrating ====
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0803s
main: -- index_exists?(:ml_candidates, [:project_id, :internal_id], {:name=>"index_ml_candidates_on_project_id_on_internal_id", :algorithm=>:concurrently})
main:    -> 0.0032s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:ml_candidates, [:project_id, :internal_id], {:name=>"index_ml_candidates_on_project_id_on_internal_id", :algorithm=>:concurrently})
main:    -> 0.0020s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20230321170803 AddIndexOnProjectIdOnInternalIdToMlCandidates: migrated (0.1003s)

DOWN

main: == 20230321170803 AddIndexOnProjectIdOnInternalIdToMlCandidates: reverting ====
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.1799s
main: -- indexes(:ml_candidates)
main:    -> 0.0043s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- remove_index(:ml_candidates, {:algorithm=>:concurrently, :name=>"index_ml_candidates_on_project_id_on_internal_id"})
main:    -> 0.0031s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20230321170803 AddIndexOnProjectIdOnInternalIdToMlCandidates: reverted (0.2230s)

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.

Related to #398170 (closed)