Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/migrations/0096_candidate_merge.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | db/migrations/0096_candidate_merge.sql |
| Original SHA-256 | 03089c7782bcf6fee28c29b14825f3ad5eea96c4d1888e9a72a74e1e60ccd2f7 |
| Git blob SHA | c723e9815f4b0752815fa595ac2ec1c4dba71f87 |
| Reading-copy SHA-256 | 03089c7782bcf6fee28c29b14825f3ad5eea96c4d1888e9a72a74e1e60ccd2f7 |
Recovered source transcript
Download complete source text ↓113 lines · 6.8 KB
-- +goose Up
-- CANDIDATE MERGE. Two rows are the same person, and the history has to end up on one of them.
--
-- A bench gets dirty. The same person applies through the careers page, gets imported from an ATS export, and
-- is sourced by a recruiter, and now there are three of them with a third of the history each. Cross source
-- dedup already exists AT IMPORT TIME; nothing existed for the duplicates that arrive afterwards, which is
-- most of them.
--
-- THE LOSER IS A TOMBSTONE, NEVER A DELETE, and that is not squeamishness. This is an Automated Employment
-- Decision Tool: a scoring decision made in March about candidate A has to be reconstructable in June, and
-- deleting A destroys the subject of that decision. So A keeps its row, gains merged_into_id, and is excluded
-- from every working query the way a soft deleted row already is. Anything still pointing at A can follow the
-- pointer and land on the right person.
--
-- THE TABLES TO REPOINT ARE DISCOVERED FROM THE CATALOG, not from a list in the application. Fifteen foreign
-- keys across twelve migrations reference candidates today and the next migration will add another. A hand
-- maintained list is a list that is wrong the first time somebody forgets it, and the failure mode is
-- silent: the merge reports success and a candidate's interviews stay attached to a tombstone.
ALTER TABLE candidates ADD COLUMN merged_into_id uuid;
ALTER TABLE candidates ADD CONSTRAINT fk_candidates_merged_into
FOREIGN KEY (organization_id, merged_into_id) REFERENCES candidates (organization_id, id);
-- A merged record must be soft deleted, and a live record must not claim to be merged. Without this the two
-- states drift and a tombstone reappears in a search.
ALTER TABLE candidates ADD CONSTRAINT ck_candidates_merged_is_deleted
CHECK (merged_into_id IS NULL OR deleted_at IS NOT NULL);
-- A record cannot be merged into itself.
ALTER TABLE candidates ADD CONSTRAINT ck_candidates_merge_not_self
CHECK (merged_into_id IS NULL OR merged_into_id <> id);
CREATE INDEX idx_candidates_merged_into ON candidates (organization_id, merged_into_id)
WHERE merged_into_id IS NOT NULL;
-- ============================================================================
-- candidate_merges, the record of what was done
-- ============================================================================
-- APPEND ONLY, no DELETE grant. A merge is destructive to the shape of the data even though it destroys no
-- rows, and "why does this person have two resumes from different agencies" is a question somebody asks
-- months later. The per table row counts are stored so the answer is arithmetic rather than archaeology.
CREATE TABLE candidate_merges (
organization_id uuid NOT NULL REFERENCES organizations(id),
id uuid NOT NULL DEFAULT gen_random_uuid(),
kept_id uuid NOT NULL,
merged_id uuid NOT NULL,
-- What moved, as {"table": count}. JSONB because the set of tables is discovered at run time and grows
-- with the schema, so typed columns would need a migration every time a table gains a candidate
-- reference.
moved jsonb NOT NULL DEFAULT '{}'::jsonb,
-- Rows that could NOT move because moving them would have violated a unique constraint, meaning both
-- candidates already had that thing. They are dropped from the loser, and the count is recorded, because
-- a merge that silently discarded rows would be indistinguishable from one that moved them.
dropped jsonb NOT NULL DEFAULT '{}'::jsonb,
reason text NOT NULL DEFAULT '',
merged_by uuid,
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (organization_id, id),
CONSTRAINT fk_candidate_merges_kept FOREIGN KEY (organization_id, kept_id)
REFERENCES candidates (organization_id, id),
CONSTRAINT fk_candidate_merges_merged FOREIGN KEY (organization_id, merged_id)
REFERENCES candidates (organization_id, id),
CONSTRAINT fk_candidate_merges_actor FOREIGN KEY (organization_id, merged_by)
REFERENCES users (organization_id, id) ON DELETE SET NULL,
CONSTRAINT ck_candidate_merge_distinct CHECK (kept_id <> merged_id),
CONSTRAINT ck_candidate_merge_reason CHECK (length(reason) <= 500)
);
CREATE INDEX idx_candidate_merges_kept ON candidate_merges (organization_id, kept_id, created_at DESC);
ALTER TABLE candidate_merges ENABLE ROW LEVEL SECURITY;
ALTER TABLE candidate_merges FORCE ROW LEVEL SECURITY;
CREATE POLICY candidate_merges_isolation ON candidate_merges
USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);
GRANT SELECT, INSERT ON candidate_merges TO missionmatch_app;
-- ============================================================================
-- the catalog read the merge depends on
-- ============================================================================
-- +goose StatementBegin
-- mm_candidate_referencing_tables returns every table and column that points at candidates.id, read from
-- pg_constraint at run time. The merge walks THIS, so a table added by a future migration is repointed
-- automatically and nobody has to remember to update a list in Go.
--
-- It excludes candidates itself: the self reference is merged_into_id, which the merge sets deliberately
-- rather than rewriting.
CREATE OR REPLACE FUNCTION mm_candidate_referencing_tables()
RETURNS TABLE (table_name text, column_name text) AS $$
SELECT DISTINCT cl.relname::text, att.attname::text
FROM pg_constraint con
JOIN pg_class cl ON cl.oid = con.conrelid
JOIN pg_namespace ns ON ns.oid = cl.relnamespace
JOIN pg_class ref ON ref.oid = con.confrelid
JOIN LATERAL unnest(con.conkey) WITH ORDINALITY AS k(attnum, ord) ON true
JOIN LATERAL unnest(con.confkey) WITH ORDINALITY AS f(attnum, ord) ON f.ord = k.ord
JOIN pg_attribute att ON att.attrelid = con.conrelid AND att.attnum = k.attnum
JOIN pg_attribute ratt ON ratt.attrelid = con.confrelid AND ratt.attnum = f.attnum
WHERE con.contype = 'f'
AND ns.nspname = 'public'
AND ref.relname = 'candidates'
AND ratt.attname = 'id'
AND cl.relname <> 'candidates';
$$ LANGUAGE sql STABLE;
-- +goose StatementEnd
GRANT EXECUTE ON FUNCTION mm_candidate_referencing_tables() TO missionmatch_app;
SELECT mm_mirror_staging_role_privileges();
-- +goose Down
DROP FUNCTION IF EXISTS mm_candidate_referencing_tables();
DROP TABLE IF EXISTS candidate_merges;
DROP INDEX IF EXISTS idx_candidates_merged_into;
ALTER TABLE candidates DROP CONSTRAINT IF EXISTS ck_candidates_merge_not_self;
ALTER TABLE candidates DROP CONSTRAINT IF EXISTS ck_candidates_merged_is_deleted;
ALTER TABLE candidates DROP CONSTRAINT IF EXISTS fk_candidates_merged_into;
ALTER TABLE candidates DROP COLUMN IF EXISTS merged_into_id;