Skip to content

Restore unique index on project_authorizations (project_id, user_id)

Why are we doing this work

We need to restore a previously deleted unique index on project_authorizations.

Note the application code checks the uniqueness of project_authorization on project_id and user_id. This means no instance of project_authorizations table should really contain duplicate records. However, without the actual DB constraint, we cannot assume the uniqueness is there when we write migrations.

Unfortunately, PG does not support a uniqueness constraint that can be partially valid (NOT VALID.)

The following implementation plan details how we can synthetically create a NOT VALID uniqueness constraint using a column and an partial uniqueness index.

Implementation Plan

In release N

Step 1. Create a regular migration and perform the following steps.

  • Add a nullable column named is_unique to the table project_authorizations.

Step 2. Create a partial unique index asynchronously for production.

Step 3. Create the same index concurrently in a post-deployment migration for self-hosted.

In release N+1

Step 1. Update the Rails model so that whenever a new project_authorization is created, mark is_unique=true.

Step 2. Create and enqueue a batched background migration that fixes the potential duplicates ONLY FOR SELF-HOSTED.

Mark each project_authorization record with is_unique=true as the batched background migration confirms it.

For the production DB, we are hoping the records are already unique with respect to user_id and project_id we can easily check this through a replica/clone without running the batched background migration.

In release N+2

Step 1. Finalize the batched background migration on the self-hosted (make sure this is mentioned in a release post. TODO: check with a release/delivery manager/DB team.)

Step 2. Create a post-deployment migration and create the full (normal) unique index on the self-hosted.

CREATE UNIQUE INDEX CONCURRENTLY ON project_authorizations (user_id, project_id);

Step 3. Asynchronously create the same full unique index for SaaS.

In release N+3 and later

  1. Remove the partial index from all environments (SaaS + self-hosted.) Do this concurrently in a post-deployment migration.

  2. Start ignoring the column is_unique and follow the removal procedure.

Relevant links

Edited by euko