Create DB tables for storing mentioned users using postgres array type

Alexandru Croitorrequested to merge
21800-mentioned-users-tables-with-array-type into master

What does this MR do?

Creates DB tables for storing mentioned users, groups, projects referenced in a note(for issue, epic, merge requests, commit or snippet) or issue, epic, merge request description issuable description

All 5 tables are very similar so I will take issue_user_mentions as an example. We would store all mentions for a given issue in this table by parsing issue description and issue notes.

  • issue_id:integer - is always NOT NULL.
  • note_id:integer - is only null if users, projects or groups were mentioned in issue description. We need note_id field mainly to be able to update mentions when note is being updated or deleted.
  • mentioned_users_ids:integer[] - An array of mentioned user IDs.
  • mentioned_projects_ids:integer[] - An array of mentioned project IDs. It will get expanded to all project members when a new notification for given issue needs to be sent out.
  • mentioned_groups_ids:intger[] - An array of mentioned group IDs. It gets expanded to all group members when a new notification for the given issue needs to be sent out.

So for any given issue we can have something like:

issue_id note_id mentioned_users_ids mentioned_projects_ids mentioned_groups_ids ----
1 null {15, 1234, 432} {23, 66} {11, 19, 129}
1 12 {3} {} {4}
1 17 {14, 111, 786} {11, 121, 211} {3, 88, 122}

Having this structure helps us easily get the list of directly mentioned users, list of mentioned projects which can then be joined with project members to get the list of users, as well as list of mentioned groups for one or multiple issues, without having to parse all issue notes every time we need to get the list of participants.

Useful References

Some query plans for issue_user_mentions when using array fields

Get all mentioned users for an issue, plan: https://explain.depesz.com/s/dTH5

EXPLAIN ANALYZE SELECT "users".*
FROM "users"
WHERE "users"."id" IN (
		SELECT DISTINCT unnest(mentioned_users_ids)
		FROM "issue_user_mentions"
		WHERE "issue_user_mentions"."issue_id" = 3937
		);

Get users for all mentioned groups in an issue, plan: https://explain.depesz.com/s/bNwb

# EXPLAIN ANALYZE SELECT DISTINCT "users".*
FROM "users"
INNER JOIN "members" ON "members"."user_id" = "users"."id"
	AND "members"."type" IN ('GroupMember')
	AND "members"."source_type" = 'Namespace'
	AND "members"."requested_at" IS NULL
WHERE "members"."source_id" IN (
		SELECT "namespaces"."id"
		FROM "namespaces"
		WHERE "namespaces"."type" IN ('Group')
			AND "namespaces"."id" IN (
				SELECT DISTINCT unnest(mentioned_groups_ids)
				FROM "issue_user_mentions"
				WHERE "issue_user_mentions"."issue_id" = 3937
				)
		);

Get users for all mentioned projects in an issue, plan: https://explain.depesz.com/s/ptNo

EXPLAIN ANALYZE SELECT DISTINCT "users".*
FROM "users"
INNER JOIN "members" ON "members"."user_id" = "users"."id"
	AND "members"."type" IN ('ProjectMember')
	AND "members"."source_type" = 'Project'
	AND "members"."requested_at" IS NULL
WHERE "members"."source_id" IN (
		SELECT "projects"."id"
		FROM "projects"
		WHERE "projects"."id" IN (
				SELECT DISTINCT unnest(mentioned_projects_ids)
				FROM "issue_user_mentions"
				WHERE "issue_user_mentions"."issue_id" = 3937
				)
		);

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • Label as security and @ mention @gitlab-com/gl-security/appsec
  • The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • Security reports checked/validated by a reviewer from the AppSec team

Closes #21800 (closed)

Edited by Coung Ngo

Merge request reports