Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/seed/demo_recruiting_desk.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | db/seed/demo_recruiting_desk.sql |
| Original SHA-256 | 61e07796d53877e95f428164beec686eca05617f0c5b4558d8efd6b827094c09 |
| Git blob SHA | e65ca44e26383e70ea5bc313808b656775eb2b57 |
| Reading-copy SHA-256 | 61e07796d53877e95f428164beec686eca05617f0c5b4558d8efd6b827094c09 |
Recovered source transcript
Download complete source text ↓291 lines · 18.3 KB
-- THE DEMO RECRUITING DESK.
--
-- WHY THIS EXISTS. The recruiting desk was built out over Tier 1, Tier 2 and the two manager phases, and
-- production held nothing for it to show: zero clients, zero job orders, zero applications, zero
-- placements, zero logged activity and zero teams. Every manager surface was live, correct, and blank.
-- This is the demonstration data that makes the product show what it does.
--
-- IT IS SYNTHETIC AND IT IS SHAPED ON PURPOSE. Nothing here is a real company, a real job order or a real
-- person. The shaping is documented below rather than left for somebody to reverse engineer, in the same
-- spirit as demo_gapfillers.sql, because a demo seed whose distribution nobody can explain is a demo
-- nobody can trust.
--
-- IT IS DELIBERATELY NOT ATTACHED TO THE REAL PEOPLE IN THIS ORGANIZATION. Acme GovCon carries genuine
-- user accounts. Attributing invented placements, invented activity and invented performance figures to a
-- real named colleague would put fabricated numbers next to somebody's name on a screen a business partner
-- is going to read. The four recruiters below are fictional and exist only to own demonstration work.
--
-- RERUNNABLE. Every identifier is derived deterministically from a fixed namespace, so running this twice
-- changes nothing rather than doubling the desk.
\set org '11111111-1111-1111-1111-111111111111'
-- ---------------------------------------------------------------------------------------------------
-- THE FOUR RECRUITERS, ONE PER CAPACITY BAND.
--
-- The manager view bands load at 14 open orders (the published benchmark), 20 (strained) and 30
-- (overloaded). A demo where everybody sits in one band shows a manager a colour they cannot interpret,
-- so the four desks below are sized to land one in each band and make the scale legible:
--
-- Dana Reyes 9 open ok comfortably inside the benchmark
-- Marcus Bell 17 open watch past the benchmark, not yet in trouble
-- Priya Raman 23 open strained the work is starting to change shape
-- Tom Okafor 32 open overloaded past the point where quality goes
--
-- These accounts have NO credentials and cannot be signed into. They own work and appear in lists; they
-- are not a way in. That is deliberate: a demo should not ship four unused logins.
-- ---------------------------------------------------------------------------------------------------
INSERT INTO users (id, organization_id, email, full_name, role, status, title)
VALUES
(md5('mm.demo.recruiter.dana')::uuid, :'org', 'dana.reyes@demo.missionmatch.invalid', 'Dana Reyes', 'recruiter', 'active', 'Technical Recruiter'),
(md5('mm.demo.recruiter.marcus')::uuid, :'org', 'marcus.bell@demo.missionmatch.invalid', 'Marcus Bell', 'recruiter', 'active', 'Technical Recruiter'),
(md5('mm.demo.recruiter.priya')::uuid, :'org', 'priya.raman@demo.missionmatch.invalid', 'Priya Raman', 'recruiter', 'active', 'Senior Recruiter'),
(md5('mm.demo.recruiter.tom')::uuid, :'org', 'tom.okafor@demo.missionmatch.invalid', 'Tom Okafor', 'recruiter', 'active', 'Senior Recruiter'),
(md5('mm.demo.manager.alina')::uuid, :'org', 'alina.whitfield@demo.missionmatch.invalid', 'Alina Whitfield', 'manager', 'active', 'Delivery Manager')
ON CONFLICT (id) DO NOTHING;
-- ---------------------------------------------------------------------------------------------------
-- TWO DESKS, so the manager scope selector has something to choose between and the empty team case is not
-- the only thing a demo can show. Each desk deliberately holds one healthy recruiter and one under
-- pressure, because that is what a real desk looks like and it makes the team level figures meaningful
-- rather than uniformly good or uniformly alarming.
-- ---------------------------------------------------------------------------------------------------
INSERT INTO teams (id, organization_id, name, lead_user_id, status)
VALUES
(md5('mm.demo.team.cleared')::uuid, :'org', 'Cleared Programs Desk', md5('mm.demo.manager.alina')::uuid, 'active'),
(md5('mm.demo.team.intel')::uuid, :'org', 'Intel Programs Desk', md5('mm.demo.manager.alina')::uuid, 'active')
ON CONFLICT (organization_id, id) DO NOTHING;
INSERT INTO team_members (organization_id, team_id, user_id)
VALUES
(:'org', md5('mm.demo.team.cleared')::uuid, md5('mm.demo.recruiter.dana')::uuid),
(:'org', md5('mm.demo.team.cleared')::uuid, md5('mm.demo.recruiter.marcus')::uuid),
(:'org', md5('mm.demo.team.intel')::uuid, md5('mm.demo.recruiter.priya')::uuid),
(:'org', md5('mm.demo.team.intel')::uuid, md5('mm.demo.recruiter.tom')::uuid)
ON CONFLICT DO NOTHING;
-- ---------------------------------------------------------------------------------------------------
-- CLIENTS. Fourteen, named as the kind of prime and agency a cleared staffing firm actually works with,
-- and obviously invented rather than borrowed from real companies.
-- ---------------------------------------------------------------------------------------------------
INSERT INTO clients (id, organization_id, name, industry, status, owner_user_id, owner_since)
SELECT md5('mm.demo.client.' || n)::uuid, :'org',
(ARRAY['Northgate Systems','Cardinal Federal','Blue Harbor Technologies','Sentinel Analytics Group',
'Pinnacle Defense Solutions','Ironwood Integrated','Vantage Mission Services','Granite Peak Federal',
'Redstone Digital','Halcyon Defense','Meridian Cyber Works','Copperline Federal',
'Arclight Intelligence Services','Foxglove Systems Group'])[n],
'Government Services', 'active',
(ARRAY[md5('mm.demo.recruiter.dana')::uuid, md5('mm.demo.recruiter.marcus')::uuid,
md5('mm.demo.recruiter.priya')::uuid, md5('mm.demo.recruiter.tom')::uuid])[1 + (n % 4)],
now() - make_interval(days => 60 + n)
FROM generate_series(1, 14) AS n
ON CONFLICT (organization_id, id) DO NOTHING;
-- ---------------------------------------------------------------------------------------------------
-- CANDIDATES. A hundred and forty synthetic profiles so the pipeline has people in it. Clearance is a
-- SELF REPORTED INDICATOR on a generated profile, exactly as the product says everywhere it shows one.
-- The real check is the hiring client facility security officer through DISS at onboarding, and nothing
-- in this file should ever be read as a verification.
-- ---------------------------------------------------------------------------------------------------
INSERT INTO candidates (id, organization_id, full_name, email, headline, clearance_level, clearance_status,
years_experience, location, remote, available, source)
SELECT md5('mm.demo.candidate.' || n)::uuid, :'org',
(ARRAY['Avery','Jordan','Riley','Casey','Morgan','Quinn','Rowan','Sage','Elliot','Harper',
'Emerson','Reese','Finley','Blake','Skyler','Marlowe','Ellis','Devon','Kendall','Peyton'])[1 + (n % 20)]
|| ' ' ||
(ARRAY['Calloway','Whitmore','Ashford','Brennan','Sutcliffe','Marchetti','Okonkwo','Delacroix',
'Vasquez','Lindqvist','Abernathy','Rosenthal','Nakamura','Ferreira'])[1 + (n % 14)]
|| ' ' || n,
'demo.candidate.' || n || '@demo.missionmatch.invalid',
(ARRAY['Intelligence Analyst','Cyber Security Engineer','Systems Administrator','Data Scientist',
'Software Engineer','Network Engineer','Cloud Architect','SIGINT Analyst',
'Program Manager','Systems Engineer'])[1 + (n % 10)],
(ARRAY['secret','top_secret','ts_sci','secret','top_secret'])[1 + (n % 5)],
'active',
3 + (n % 18),
(ARRAY['Fort Meade, MD','Chantilly, VA','Colorado Springs, CO','Augusta, GA','Tampa, FL',
'Huntsville, AL','San Antonio, TX','Arlington, VA'])[1 + (n % 8)],
(n % 4 = 0), (n % 3 <> 0), 'seed'
FROM generate_series(1, 140) AS n
ON CONFLICT (id) DO NOTHING;
-- ---------------------------------------------------------------------------------------------------
-- OPEN JOB ORDERS, eighty one of them, distributed to land each recruiter in their intended band.
--
-- opened_at is spread backwards across four months so the aging calculation has a real distribution to
-- work with rather than every order looking the same age.
-- ---------------------------------------------------------------------------------------------------
INSERT INTO requisitions (id, organization_id, client_id, owner_user_id, title, labor_category, openings,
priority, employment_type, status, required_level, work_location, remote_allowed,
bill_rate_min, bill_rate_max, pay_rate_min, pay_rate_max, opened_at, created_at)
SELECT md5('mm.demo.req.' || d.owner_key || '.' || s.i)::uuid, :'org',
md5('mm.demo.client.' || (1 + ((s.i * 3) % 14)))::uuid,
md5(d.owner_key)::uuid,
(ARRAY['Intelligence Analyst','Cyber Security Engineer','Systems Administrator','Data Scientist',
'Software Engineer','Network Engineer','Cloud Architect','SIGINT Analyst',
'All Source Analyst','Systems Engineer','DevSecOps Engineer','Targeting Analyst'])[1 + (s.i % 12)]
|| ' ' || (ARRAY['I','II','III','Senior','Lead'])[1 + (s.i % 5)],
(ARRAY['Analysis','Engineering','Operations','Architecture'])[1 + (s.i % 4)],
1,
(ARRAY['normal','normal','high','critical','low'])[1 + (s.i % 5)],
(ARRAY['contract','contract','contract_to_hire','direct_hire'])[1 + (s.i % 4)],
'open',
(ARRAY['secret','top_secret','ts_sci'])[1 + (s.i % 3)],
(ARRAY['Fort Meade, MD','Chantilly, VA','Colorado Springs, CO','Augusta, GA','Tampa, FL',
'Huntsville, AL','San Antonio, TX','Arlington, VA'])[1 + (s.i % 8)],
(s.i % 5 = 0),
95 + (s.i % 40), 135 + (s.i % 55), 62 + (s.i % 25), 88 + (s.i % 32),
now() - make_interval(days => 3 + ((s.i * 7) % 118)),
now() - make_interval(days => 3 + ((s.i * 7) % 118))
FROM (VALUES
('mm.demo.recruiter.dana', 9),
('mm.demo.recruiter.marcus', 17),
('mm.demo.recruiter.priya', 23),
('mm.demo.recruiter.tom', 32)
) AS d(owner_key, n)
CROSS JOIN LATERAL generate_series(1, d.n) AS s(i)
ON CONFLICT (organization_id, id) DO NOTHING;
-- CLOSED AND FILLED ORDERS give the manager comparison something to compare against.
--
-- Without history in BOTH the current window and the one before it, every figure reads "up from zero",
-- which is the least useful thing a comparison can say and makes the whole screen look broken.
INSERT INTO requisitions (id, organization_id, client_id, owner_user_id, title, labor_category, openings,
priority, employment_type, status, required_level, opened_at, created_at, closed_at)
SELECT md5('mm.demo.req.closed.' || n)::uuid, :'org',
md5('mm.demo.client.' || (1 + (n % 14)))::uuid,
(ARRAY[md5('mm.demo.recruiter.dana')::uuid, md5('mm.demo.recruiter.marcus')::uuid,
md5('mm.demo.recruiter.priya')::uuid, md5('mm.demo.recruiter.tom')::uuid])[1 + (n % 4)],
(ARRAY['Intelligence Analyst','Cyber Security Engineer','Data Scientist','Systems Engineer',
'Software Engineer','Network Engineer'])[1 + (n % 6)] || ' ' ||
(ARRAY['II','III','Senior'])[1 + (n % 3)],
'Analysis', 1, 'normal', 'contract', 'filled',
(ARRAY['secret','top_secret','ts_sci'])[1 + (n % 3)],
now() - make_interval(days => 40 + (n % 70)),
now() - make_interval(days => 40 + (n % 70)),
-- Half land inside the last thirty days and half in the thirty before it, so both windows are populated.
now() - make_interval(days => CASE WHEN n % 2 = 0 THEN 4 + (n % 24) ELSE 33 + (n % 26) END)
FROM generate_series(1, 34) AS n
ON CONFLICT (organization_id, id) DO NOTHING;
-- ---------------------------------------------------------------------------------------------------
-- APPLICATIONS. A live pipeline on the open orders, plus the placed history that the filled orders imply.
--
-- The stage CHECK is strict and correctly so: a rejected or withdrawn application MUST carry a disposition
-- code, and anything else must carry neither a code nor a reason. The seed respects that rather than
-- working around it, because a demo that violates the product's own rules teaches the wrong thing.
-- ---------------------------------------------------------------------------------------------------
INSERT INTO applications (id, organization_id, requisition_id, candidate_id, stage, stage_changed_at,
owner_user_id, furthest_stage_ord, created_at)
SELECT md5('mm.demo.app.' || r.id::text || '.' || s.i)::uuid, :'org', r.id,
md5('mm.demo.candidate.' || (1 + ((abs(hashtext(r.id::text)) + s.i * 17) % 140)))::uuid,
stg.stage,
now() - make_interval(days => 1 + (s.i * 3) % 20),
r.owner_user_id,
stg.ord,
now() - make_interval(days => 2 + (s.i * 5) % 40)
FROM requisitions r
CROSS JOIN LATERAL generate_series(1, 1 + (abs(hashtext(r.id::text)) % 4)) AS s(i)
CROSS JOIN LATERAL (
SELECT (ARRAY['sourced','screened','submitted','interviewing','offered'])[1 + ((abs(hashtext(r.id::text)) + s.i) % 5)] AS stage,
1 + ((abs(hashtext(r.id::text)) + s.i) % 5) AS ord
) AS stg
WHERE r.status = 'open' AND r.organization_id = :'org'
ON CONFLICT (organization_id, id) DO NOTHING;
-- The placed applications behind the filled orders. One each, so a filled order has the person in it.
INSERT INTO applications (id, organization_id, requisition_id, candidate_id, stage, stage_changed_at,
owner_user_id, furthest_stage_ord, created_at)
SELECT md5('mm.demo.app.placed.' || r.id::text)::uuid, :'org', r.id,
md5('mm.demo.candidate.' || (1 + (abs(hashtext(r.id::text)) % 140)))::uuid,
'placed', r.closed_at, r.owner_user_id, 6, r.opened_at + interval '5 days'
FROM requisitions r
WHERE r.status = 'filled' AND r.organization_id = :'org'
ON CONFLICT (organization_id, id) DO NOTHING;
-- ---------------------------------------------------------------------------------------------------
-- PLACEMENTS, and the redeployment horizon.
--
-- THIS IS THE SHAPING THAT MATTERS MOST. The redeployment arm looks forty five days ahead and escalates
-- inside fourteen. A demo whose placements all end next year proves the alert compiles and nothing else.
-- So the end dates below are spread deliberately across the horizon:
--
-- inside 14 days urgent, the replacement conversation is already late
-- 15 to 45 days the window the product exists to catch
-- beyond 45 days not yet a concern, present so the alert is seen to be selective rather than noisy
--
-- Rates satisfy the CHECK that bill must be at or above pay, and a burden rate is set on roughly half so
-- the margin model has something real to compute and the unburdened case is also visible.
-- ---------------------------------------------------------------------------------------------------
INSERT INTO placements (id, organization_id, application_id, status, employment_type, start_date, end_date,
bill_rate, pay_rate, burden_pct, created_at)
SELECT md5('mm.demo.placement.' || p.id::text)::uuid, :'org', p.id,
'active', 'contract',
(p.stage_changed_at + interval '10 days')::date,
(CASE p.bucket
WHEN 0 THEN now() + make_interval(days => 4 + (p.seq % 10))
WHEN 1 THEN now() + make_interval(days => 18 + (p.seq % 26))
ELSE now() + make_interval(days => 70 + (p.seq % 120))
END)::date,
128 + (abs(hashtext(p.id::text)) % 45),
82 + (abs(hashtext(p.id::text)) % 26),
CASE WHEN abs(hashtext(p.id::text)) % 2 = 0 THEN 0.285 ELSE NULL END,
p.stage_changed_at
FROM (
SELECT a.id, a.stage_changed_at,
(row_number() OVER (ORDER BY a.id))::int AS seq,
((row_number() OVER (ORDER BY a.id))::int % 5) / 2 AS bucket
FROM applications a
WHERE a.stage = 'placed' AND a.organization_id = :'org'
) AS p
ON CONFLICT (organization_id, id) DO NOTHING;
-- ---------------------------------------------------------------------------------------------------
-- LOGGED ACTIVITY, AND THE GAPS THAT ARE THE WHOLE POINT.
--
-- The exception queue asks two questions of this table: has anybody touched this job order in fourteen
-- days, and has this recruiter logged anything at all in seven. Seeding uniform recent activity would
-- answer both questions cleanly and demonstrate nothing, so the gaps below are deliberate.
--
-- TOM OKAFOR LOGS NOTHING RECENT. His last activity is thirty one days old. He is the overloaded desk,
-- which is exactly the person a manager most needs the product to surface, and the combination of a
-- full desk and no logged work is the realistic signature of somebody drowning or already halfway out
-- the door. He appears in the queue as a silent desk.
--
-- ROUGHLY A QUARTER OF OPEN ORDERS ARE LEFT UNTOUCHED past the fourteen day line, spread across the
-- other three recruiters, so aging orders are a distribution rather than one person's problem.
-- ---------------------------------------------------------------------------------------------------
INSERT INTO recruiter_activities (organization_id, actor_user_id, kind, direction, outcome,
requisition_id, candidate_id, body, occurred_at, actor_name)
SELECT :'org', r.owner_user_id,
(ARRAY['call','email','inmail','screen','submission','note'])[1 + (s.i % 6)],
'outbound',
(ARRAY['connected','left_message','replied','logged','no_answer'])[1 + (s.i % 5)],
r.id,
md5('mm.demo.candidate.' || (1 + ((abs(hashtext(r.id::text)) + s.i) % 140)))::uuid,
'Demo activity, generated for demonstration',
CASE
-- Tom's desk has gone quiet. Every one of his touches is over a month old.
WHEN r.owner_user_id = md5('mm.demo.recruiter.tom')::uuid
THEN now() - make_interval(days => 31 + (s.i % 9))
-- One order in four is left past the aging line for the other three recruiters.
WHEN abs(hashtext(r.id::text)) % 4 = 0
THEN now() - make_interval(days => 16 + (abs(hashtext(r.id::text)) % 22))
ELSE now() - make_interval(days => (abs(hashtext(r.id::text)) % 6))
END,
u.full_name
FROM requisitions r
JOIN users u ON u.id = r.owner_user_id
CROSS JOIN LATERAL generate_series(1, 2 + (abs(hashtext(r.id::text)) % 3)) AS s(i)
WHERE r.status = 'open' AND r.organization_id = :'org'
AND r.owner_user_id IS NOT NULL
ON CONFLICT DO NOTHING;