Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/migrations/0101_continuous_vetting_and_break_in_access.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | db/migrations/0101_continuous_vetting_and_break_in_access.sql |
| Original SHA-256 | e8c8f9228e2f9325aaddfc0062aaaa0e4ab90a710413619ade3eb2cdfc525aa2 |
| Git blob SHA | e33d906dcc6d3bd737eee36f070bf8dc1fc98cc5 |
| Reading-copy SHA-256 | e8c8f9228e2f9325aaddfc0062aaaa0e4ab90a710413619ade3eb2cdfc525aa2 |
Recovered source transcript
Download complete source text ↓76 lines · 4.9 KB
-- +goose Up
-- CONTINUOUS VETTING, AND THE BREAK IN ACCESS CLOCK THAT DECIDES WHO IS ACTUALLY PLACEABLE.
--
-- THE MODEL WAS TEN YEARS OUT OF DATE. This product stores clearance_expires_at and reinvestigation_due_at,
-- which describe the world before 1 October 2021: a Top Secret reinvestigated every five years, a Secret every
-- ten. That world is gone. DCSA enrolled DoD clearance holders in CONTINUOUS VETTING under Trusted Workforce
-- 2.0, which REPLACED periodic reinvestigation with ongoing monitoring of financial, criminal and public
-- records. A person in continuous vetting has no reinvestigation date to count down to.
--
-- WHAT ACTUALLY DECIDES PLACEABILITY IS THE BREAK IN ACCESS, and this product could not answer it at all.
-- Separation from a sponsor pulls a person out of that sponsor's continuous vetting enrollment, and the clock
-- that starts at separation is the one that matters:
--
-- under 24 months, and a favourable adjudication closed within the previous 5 years
-- a new employer can usually REINSTATE without a full reinvestigation
-- over 24 months, or an adjudication older than 5 years
-- a new investigation is likely, which is months of lead time and a different staffing proposition
--
-- A TS/SCI candidate three months out of access and one twenty five months out are not the same asset, and
-- missionMATCH showed them identically. For a product whose entire question is "who can support this mission
-- today", that is the single most important fact it was not storing.
--
-- IT CONNECTS TO THE REDEPLOYMENT WORK DIRECTLY. A contractor rolling off is the event that STARTS this
-- clock. The alert that says somebody finishes in thirty days and the clock that says they become hard to
-- place twenty four months later are the same story told at two points in time.
--
-- NOTHING IS INFERRED AND NOTHING IS BACKFILLED. Every column arrives NULL. A candidate whose access history
-- nobody has entered reports "not known" rather than a guess, because a guessed reinstatement posture is a
-- staffing decision made on fiction. The dates come from a person who knows them, or from nowhere.
-- ONE STATEMENT PER CHANGE. sqlc parses these migration files as the schema, and it refuses an ALTER TABLE
-- that mixes ADD COLUMN with a table level CONSTRAINT in the same statement. Splitting them costs nothing and
-- keeps the generated models buildable, which matters because a stale dbgen.Candidate is exactly how the
-- merged_into_id column broke candidate search earlier in this build.
-- Currently inside somebody's continuous vetting enrollment, which in practice means currently sponsored and
-- in access. NULL means nobody has said, which is different from false.
ALTER TABLE candidates ADD COLUMN cv_enrolled boolean;
-- When continuous vetting enrollment began. Useful evidence, never used to compute the window.
ALTER TABLE candidates ADD COLUMN cv_enrolled_on date;
-- THE DATE ACCESS ENDED, which is what the 24 month window is measured from. NULL while somebody is in
-- access, and NULL also when nobody has told us, which is why cv_enrolled is a separate column: the two
-- absences mean different things and one boolean cannot carry both.
ALTER TABLE candidates ADD COLUMN access_ended_on date;
-- The date the favourable adjudication closed. The simple reinstatement path additionally requires this to be
-- within five years, so an in window break with a stale adjudication is still an investigation.
ALTER TABLE candidates ADD COLUMN last_adjudicated_on date;
ALTER TABLE candidates ADD CONSTRAINT ck_candidates_access_dates CHECK (
(last_adjudicated_on IS NULL OR last_adjudicated_on <= CURRENT_DATE + 1)
AND (cv_enrolled_on IS NULL OR cv_enrolled_on <= CURRENT_DATE + 1)
);
-- A person cannot be in continuous vetting enrollment and simultaneously carry a date on which their access
-- ended. Enrollment follows sponsorship, and separation is what ends both.
ALTER TABLE candidates ADD CONSTRAINT ck_candidates_cv_not_separated CHECK (
NOT (cv_enrolled IS TRUE AND access_ended_on IS NOT NULL)
);
-- The index the search filter and the alert arm need: find everybody whose break in access is approaching or
-- past the 24 month line. Partial, because a candidate still in access has no clock running and is the
-- majority of any healthy bench.
CREATE INDEX IF NOT EXISTS idx_candidates_access_ended
ON candidates (organization_id, access_ended_on)
WHERE access_ended_on IS NOT NULL AND deleted_at IS NULL;
-- +goose Down
DROP INDEX IF EXISTS idx_candidates_access_ended;
ALTER TABLE candidates DROP CONSTRAINT IF EXISTS ck_candidates_cv_not_separated;
ALTER TABLE candidates DROP CONSTRAINT IF EXISTS ck_candidates_access_dates;
ALTER TABLE candidates DROP COLUMN IF EXISTS last_adjudicated_on;
ALTER TABLE candidates DROP COLUMN IF EXISTS access_ended_on;
ALTER TABLE candidates DROP COLUMN IF EXISTS cv_enrolled_on;
ALTER TABLE candidates DROP COLUMN IF EXISTS cv_enrolled;