Add fingerprints for events (!31021) · Merge requests · GitLab.org / GitLab

What does this MR do?

This MR adds a new Event#fingerprint column which allows us to detect if an event has already been created, allowing idempotent event creation.

Migrations

== 20200504191813 AddFingerprintToEvents: migrating ===========================
-- column_exists?(:events, :fingerprint)
   -> 0.0024s
-- add_column(:events, :fingerprint, :binary)
   -> 0.0023s
-- transaction_open?()
   -> 0.0000s
-- execute("ALTER TABLE events\nADD CONSTRAINT check_97e06e05ad\nCHECK ( octet_length(fingerprint) <= 128 )\nNOT VALID;\n")
   -> 0.0025s
-- execute("ALTER TABLE events VALIDATE CONSTRAINT check_97e06e05ad;")
   -> 0.0035s
== 20200504191813 AddFingerprintToEvents: migrated (0.0422s) ==================

== 20200504200709 AddIndexOnFingerprintAndTargetTypeToEvents: migrating =======
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:events, [:target_type, :target_id, :fingerprint], {:using=>:btree, :unique=>true, :algorithm=>:concurrently})
   -> 0.0087s
-- add_index(:events, [:target_type, :target_id, :fingerprint], {:using=>:btree, :unique=>true, :algorithm=>:concurrently})
   -> 0.0132s
== 20200504200709 AddIndexOnFingerprintAndTargetTypeToEvents: migrated (0.0231s)

Current schema:

                                       Table "public.events"
   Column    |           Type           | Collation | Nullable |              Default
-------------+--------------------------+-----------+----------+------------------------------------
 id          | integer                  |           | not null | nextval('events_id_seq'::regclass)
 project_id  | integer                  |           |          |
 author_id   | integer                  |           | not null |
 target_id   | integer                  |           |          |
 created_at  | timestamp with time zone |           | not null |
 updated_at  | timestamp with time zone |           | not null |
 action      | smallint                 |           | not null |
 target_type | character varying        |           |          |
 group_id    | bigint                   |           |          |
 fingerprint | bytea                    |           |          |
Indexes:
    "events_pkey" PRIMARY KEY, btree (id)
    "index_events_on_target_type_and_target_id_and_fingerprint" UNIQUE, btree (target_type, target_id, fingerprint)
    "analytics_index_events_on_created_at_and_author_id" btree (created_at, author_id)
    "index_events_on_action" btree (action)
    "index_events_on_author_id_and_created_at" btree (author_id, created_at)
    "index_events_on_author_id_and_created_at_merge_requests" btree (author_id, created_at) WHERE target_type::text = 'MergeRequest'::text
    "index_events_on_author_id_and_project_id" btree (author_id, project_id)
    "index_events_on_group_id_partial" btree (group_id) WHERE group_id IS NOT NULL
    "index_events_on_project_id_and_created_at" btree (project_id, created_at)
    "index_events_on_project_id_and_id" btree (project_id, id)
    "index_events_on_target_type_and_target_id" btree (target_type, target_id)
Check constraints:
    "check_97e06e05ad" CHECK (octet_length(fingerprint) <= 128)
Foreign-key constraints:
    "fk_61fbf6ca48" FOREIGN KEY (group_id) REFERENCES namespaces(id) ON DELETE CASCADE
    "fk_edfd187b6f" FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
    "fk_rails_0434b48643" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
Referenced by:
    TABLE "push_event_payloads" CONSTRAINT "fk_36c74129da" FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE

Significant queries

Event.for_wiki_meta(meta).for_fingerprint('1234')

which generates the following SQL (illustrative example values):

SELECT
    events.*
FROM
    events
WHERE
    events.target_type = 'WikiPage::Meta'
    AND events.target_id = 1
    AND events.fingerprint = '\x1234'

With the following query plan, indicating use of the new index:

-------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_events_on_target_type_and_target_id_and_fingerprint on events  (cost=0.27..2.23 rows=1 width=80)
   Index Cond: (((target_type)::text = 'WikiPage::Meta'::text) AND (target_id = 1) AND (fingerprint = '\x1234'::bytea))
(2 rows)

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Tested in all supported browsers
Informed Infrastructure department of a default or new setting change, if applicable per definition of done

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

Add fingerprints for events