bcgov / bcgov/SIMS

cra_income_verifications index review

Open
#1,579 1 comment 0 reactions 0 assignees View on GitHub
Icebox Technical Debt
Dominant language
TypeScript
Stars
29
Forks
15
PR merge metrics
No merged PRs in 30d

Description

**Additional Context**
Take advantage of the recent Postgres changes mentioned below to review the below index.

`cra_income_verifications_application_id_supporting_user_id UNIQUE (application_Id, supporting_user_id)`

"Previously NULL entries were always treated as distinct values, but this can now be changed by creating constraints and indexes using UNIQUE NULLS NOT DISTINCT."
Source: https://www.postgresql.org/docs/15/release-15.html#id-1.11.6.5.5.3.4

For a particular student application, the `cra_income_verifications` table should contain a unique record for a particular combination of the (application_Id, supporting_user_id) or one and only one possible combination as (application_Id, null) that would represent the student income request. This last one is not currently ensured by the DB, but with the newly mentioned feature of Postgres, we can recreate the index to ensure it.

Acceptance Criteria
- [ ] Recreate the above index to ensure the null value is present only once (null is part of the check for distinct values)
- [ ] Check other tables where supporting users are foreign keys.

Contributor guide

No contributing guide indexed for this repository

Research direction

Start with the cra_income_verifications table and its named UNIQUE (application_id, supporting_user_id) index, then read the PostgreSQL 15 UNIQUE NULLS NOT DISTINCT release-note reference. Inspect other tables where supporting_user_id is a foreign key. Done means the index allows only one (application_id, NULL) record and the related tables have been reviewed.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql
Domain
databases
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.