Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/migrations/0105_recruiting_goals.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | db/migrations/0105_recruiting_goals.sql |
| Original SHA-256 | b05c691b9f4dd571e3e1863d404d7479b30259917d6ceea3df7b83eb5a4550b4 |
| Git blob SHA | d561ec7ec63aae9b52e86f8870581a3bc9e37c7f |
| Reading-copy SHA-256 | b05c691b9f4dd571e3e1863d404d7479b30259917d6ceea3df7b83eb5a4550b4 |
Recovered source transcript
Download complete source text ↓88 lines · 4.5 KB
-- +goose Up
-- GOALS, WHICH TURN EVERY NUMBER PHASE 1 REPORTS INTO ATTAINMENT.
--
-- Phase 1 gave a manager four figures and the window before them. That answers "is this better or worse
-- than last month" and cannot answer "is this enough", which is the question the business actually asks.
-- Four placements is a good month or a bad one depending entirely on what was committed to, and the
-- product had nowhere to record what was committed to.
--
-- SCOPE MATCHES THE MANAGER VIEW EXACTLY, organization, team or user, because a goal that cannot be set
-- at the level the figures are read at is a goal nobody can check. The same resolver serves both, so a
-- target set on a desk is compared against that desk's number and never against a different population.
--
-- A PERIOD IS A START AND A LENGTH, not a month name. The manager view already compares a window against
-- the window before it and lets the length be chosen, so a goal has to speak the same way or the two
-- screens disagree about what a period is. Thirty days starting the first is a month; ninety is a
-- quarter; nothing here needs a calendar library to say so.
--
-- TARGETS ARE EXACT DECIMAL. Everything countable in this product is, and a target compared against a
-- decimal actual using a float would produce attainment that drifts at the boundary, which is the one
-- place anybody looks closely.
CREATE TABLE recruiting_goals (
organization_id uuid NOT NULL REFERENCES organizations(id),
id uuid NOT NULL DEFAULT gen_random_uuid(),
-- WHOSE GOAL. subject_id is the team or user; NULL means the whole organization, which is why it
-- cannot simply be NOT NULL and why the CHECK below ties the two columns together.
scope text NOT NULL
CHECK (scope IN ('organization', 'team', 'user')),
subject_id uuid,
-- WHAT IS BEING COMMITTED TO. Deliberately the same four figures the manager view reports, so every
-- goal has a number already on screen to be judged against. A metric with no matching figure would be
-- a target nobody could ever see progress on.
metric text NOT NULL
CHECK (metric IN ('placements', 'submissions', 'fill_rate', 'time_to_fill_days')),
period_start date NOT NULL,
period_days integer NOT NULL DEFAULT 30
CHECK (period_days BETWEEN 1 AND 366),
target numeric(12,2) NOT NULL
CHECK (target >= 0),
note text NOT NULL DEFAULT '',
created_by uuid,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (organization_id, id),
-- An organization goal names nobody and a team or user goal must name somebody. Without this a row
-- claiming to be a team target with no team would be silently compared against the whole company.
CONSTRAINT ck_goal_subject_matches_scope CHECK (
(scope = 'organization' AND subject_id IS NULL)
OR (scope IN ('team', 'user') AND subject_id IS NOT NULL)
)
);
-- ONE TARGET PER SUBJECT PER METRIC PER PERIOD. Two rows for the same thing is not a richer goal, it is a
-- question about which number is real, and a manager should never have to answer that. coalesce lets the
-- organization row take part in the same unique index despite its NULL subject.
CREATE UNIQUE INDEX IF NOT EXISTS uq_recruiting_goal_subject_period
ON recruiting_goals (organization_id, scope, coalesce(subject_id, '00000000-0000-0000-0000-000000000000'::uuid), metric, period_start);
-- The read the manager view makes: this organization's goals covering today.
CREATE INDEX IF NOT EXISTS idx_recruiting_goals_period
ON recruiting_goals (organization_id, period_start DESC);
ALTER TABLE recruiting_goals ENABLE ROW LEVEL SECURITY;
ALTER TABLE recruiting_goals FORCE ROW LEVEL SECURITY;
CREATE POLICY recruiting_goals_tenant ON recruiting_goals
USING (organization_id = current_setting('app.current_org_id', true)::uuid)
WITH CHECK (organization_id = current_setting('app.current_org_id', true)::uuid);
-- Creating a table grants nothing, and forgetting this is the defect 0102 and 0103 each shipped once.
GRANT SELECT, INSERT, UPDATE, DELETE ON recruiting_goals TO missionmatch_app;
SELECT mm_mirror_staging_role_privileges();
-- +goose Down
DROP POLICY IF EXISTS recruiting_goals_tenant ON recruiting_goals;
DROP INDEX IF EXISTS idx_recruiting_goals_period;
DROP INDEX IF EXISTS uq_recruiting_goal_subject_period;
DROP TABLE IF EXISTS recruiting_goals;