-- 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;