Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/migrations/0049_recruiting_activity.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | db/migrations/0049_recruiting_activity.sql |
| Original SHA-256 | 21bcdf2dc8272667170201abfe24ef39b66746fd2b8910c5c62c7bd76ba8a108 |
| Git blob SHA | b62b78b4177ff078428487d43f6cb346c71feda7 |
| Reading-copy SHA-256 | 21bcdf2dc8272667170201abfe24ef39b66746fd2b8910c5c62c7bd76ba8a108 |
Every recorded occurrence
These 2 records contain identical original source bytes. Their paths and revisions remain part of the archive.
| Original record | Branch or archive | Revision / identifier | Original location |
|---|---|---|---|
| Janady13/missionmatch / db/migrations/0049_recruiting_activity.sql | feature/opportunity-to-outcome | 720d47fae476b0d38477ae4401697d372b1ea2bc | Open original |
| Janady13/missionmatch / db/migrations/0049_recruiting_activity.sql | feature/aioe-upgrade | 2178e7298d0e5d9af83afa12402fd587f0ba6ed8 | Open original |
Recovered source transcript
Download complete source text ↓131 lines · 8.1 KB
-- +goose Up
-- Phase 10, part 3 of 3. Recruiter effort and the per user work queue.
--
-- Activity is recorded so effort can be shown NEXT TO outcome, never as a score on its own. Call volume
-- is an input, not an achievement, and a leaderboard built on it pushes a recruiter away from the hard
-- placements and toward whatever is easy to count. Every read of this table in phase 17 pairs it with a
-- downstream result for that reason.
-- ---------------------------------------------------------------------------
-- recruiter_activities: one logged touch. The subject is polymorphic across the three things a recruiter
-- actually works on, expressed as three nullable typed columns rather than a type plus id pair, so every
-- reference keeps a real foreign key and a deleted candidate cannot leave a dangling row.
-- ---------------------------------------------------------------------------
CREATE TABLE recruiter_activities (
organization_id uuid NOT NULL REFERENCES organizations(id),
id bigint GENERATED ALWAYS AS IDENTITY,
actor_user_id uuid NOT NULL,
kind text NOT NULL
CHECK (kind IN ('call','email','inmail','text','meeting','note','submission','screen')),
direction text NOT NULL DEFAULT 'outbound'
CHECK (direction IN ('outbound','inbound','internal')),
outcome text NOT NULL DEFAULT 'logged'
CHECK (outcome IN ('logged','connected','no_answer','left_message','replied','bounced','declined')),
candidate_id uuid,
contact_id uuid,
requisition_id uuid,
application_id uuid,
body text NOT NULL DEFAULT '',
-- True when the platform recorded this itself, for example a screen run or a slate decision, rather
-- than a human typing it. Mixing captured and self reported effort without a flag makes any
-- productivity comparison meaningless.
auto_captured boolean NOT NULL DEFAULT false,
occurred_at timestamptz NOT NULL DEFAULT now(),
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (organization_id, id),
FOREIGN KEY (organization_id, actor_user_id) REFERENCES users (organization_id, id) ON DELETE CASCADE,
FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id) ON DELETE CASCADE,
FOREIGN KEY (organization_id, contact_id) REFERENCES client_contacts (organization_id, id) ON DELETE CASCADE,
FOREIGN KEY (organization_id, requisition_id) REFERENCES requisitions (organization_id, id) ON DELETE CASCADE,
FOREIGN KEY (organization_id, application_id) REFERENCES applications (organization_id, id) ON DELETE CASCADE,
-- An activity about nothing is noise. At least one subject is required.
CONSTRAINT recruiter_activities_subject_chk CHECK (
candidate_id IS NOT NULL OR contact_id IS NOT NULL
OR requisition_id IS NOT NULL OR application_id IS NOT NULL)
);
CREATE INDEX idx_activities_actor ON recruiter_activities (organization_id, actor_user_id, occurred_at DESC);
CREATE INDEX idx_activities_candidate ON recruiter_activities (organization_id, candidate_id, occurred_at DESC)
WHERE candidate_id IS NOT NULL;
CREATE INDEX idx_activities_req ON recruiter_activities (organization_id, requisition_id, occurred_at DESC)
WHERE requisition_id IS NOT NULL;
CREATE INDEX idx_activities_window ON recruiter_activities (organization_id, occurred_at DESC);
ALTER TABLE recruiter_activities ENABLE ROW LEVEL SECURITY;
ALTER TABLE recruiter_activities FORCE ROW LEVEL SECURITY;
CREATE POLICY recruiter_activities_isolation ON recruiter_activities
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, UPDATE, DELETE ON recruiter_activities TO missionmatch_app;
-- ---------------------------------------------------------------------------
-- tasks: a per user work item. Distinct from alerts, which the platform generates from state on a timer
-- and which belong to the organization. A task belongs to a person and is created by a person.
-- ---------------------------------------------------------------------------
CREATE TABLE tasks (
organization_id uuid NOT NULL REFERENCES organizations(id),
id uuid NOT NULL DEFAULT gen_random_uuid(),
assigned_to uuid NOT NULL,
title text NOT NULL CHECK (length(btrim(title)) BETWEEN 1 AND 300),
detail text NOT NULL DEFAULT '',
status text NOT NULL DEFAULT 'open'
CHECK (status IN ('open','done','cancelled')),
priority text NOT NULL DEFAULT 'normal'
CHECK (priority IN ('low','normal','high')),
due_at timestamptz,
completed_at timestamptz,
candidate_id uuid,
contact_id uuid,
requisition_id uuid,
application_id uuid,
created_by uuid,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (organization_id, id),
FOREIGN KEY (organization_id, assigned_to) REFERENCES users (organization_id, id) ON DELETE CASCADE,
FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL,
FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id) ON DELETE CASCADE,
FOREIGN KEY (organization_id, contact_id) REFERENCES client_contacts (organization_id, id) ON DELETE CASCADE,
FOREIGN KEY (organization_id, requisition_id) REFERENCES requisitions (organization_id, id) ON DELETE CASCADE,
FOREIGN KEY (organization_id, application_id) REFERENCES applications (organization_id, id) ON DELETE CASCADE,
CONSTRAINT tasks_completed_chk CHECK (
(status = 'done' AND completed_at IS NOT NULL)
OR (status <> 'done' AND completed_at IS NULL))
);
CREATE INDEX idx_tasks_assignee ON tasks (organization_id, assigned_to, due_at) WHERE status = 'open';
CREATE INDEX idx_tasks_due ON tasks (organization_id, due_at) WHERE status = 'open';
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks FORCE ROW LEVEL SECURITY;
CREATE POLICY tasks_isolation ON tasks
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, UPDATE, DELETE ON tasks TO missionmatch_app;
-- ---------------------------------------------------------------------------
-- Candidate source attribution. candidates.source already exists and is CHECK constrained to seed or
-- external, which records how a row entered the SYSTEM. It cannot express which channel produced the
-- person, so job board attribution has been impossible. These two columns add that without touching the
-- existing constraint, so nothing that reads source today changes behaviour.
--
-- Two columns, not one. First touch is how the relationship began and never changes. Last touch is the
-- channel that produced the current engagement. Naive last touch systematically over credits job boards
-- and the careers page while under crediting sourcing and referrals, and a single mutable column can
-- only ever tell one of those two stories.
-- ---------------------------------------------------------------------------
ALTER TABLE candidates
ADD COLUMN source_first_touch text NOT NULL DEFAULT '',
ADD COLUMN source_last_touch text NOT NULL DEFAULT '',
ADD COLUMN source_detail text NOT NULL DEFAULT '';
CREATE INDEX idx_candidates_source_first ON candidates (organization_id, source_first_touch)
WHERE source_first_touch <> '';
GRANT UPDATE (source_last_touch, source_detail) ON candidates TO missionmatch_app;
-- +goose Down
DROP INDEX IF EXISTS idx_candidates_source_first;
ALTER TABLE candidates
DROP COLUMN IF EXISTS source_detail,
DROP COLUMN IF EXISTS source_last_touch,
DROP COLUMN IF EXISTS source_first_touch;
DROP TABLE IF EXISTS tasks;
DROP TABLE IF EXISTS recruiter_activities;