-- +goose Up -- Phase 10, part 1 of 3. The client and requisition layer of the recruiting operations spine. -- -- These are the two entities recruitMATCH has never had. requirements is a labor line derived from a -- FEDERAL opportunity: it answers "this solicitation needs three cleared systems engineers". A -- requisition is a JOB ORDER placed by a client: it answers "Acme asked us to fill two seats by March". -- They are different objects with different owners and different lifecycles, so a requisition carries an -- OPTIONAL link to a requirement rather than overloading one table. A firm can run pure client work, -- pure federal capture work, or both, and neither model pollutes the other. -- -- staffing_vendors is the opposite direction and already exists: firms that submit candidates TO this -- org. clients are companies this org recruits FOR. -- --------------------------------------------------------------------------- -- clients: the companies this organization recruits for. -- --------------------------------------------------------------------------- CREATE TABLE clients ( organization_id uuid NOT NULL REFERENCES organizations(id), id uuid NOT NULL DEFAULT gen_random_uuid(), name text NOT NULL CHECK (length(btrim(name)) BETWEEN 1 AND 200), -- The government business key when the client is a federal contractor. Nullable via empty string -- with a partial unique index, matching how staffing_vendors handles the same problem. uei text NOT NULL DEFAULT '', industry text NOT NULL DEFAULT '', website text NOT NULL DEFAULT '', status text NOT NULL DEFAULT 'active' CHECK (status IN ('prospect', 'active', 'inactive')), notes 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), FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL ); CREATE UNIQUE INDEX uq_clients_org_uei ON clients (organization_id, uei) WHERE uei <> ''; CREATE UNIQUE INDEX uq_clients_org_name ON clients (organization_id, lower(btrim(name))); CREATE INDEX idx_clients_org ON clients (organization_id, status, name); ALTER TABLE clients ENABLE ROW LEVEL SECURITY; ALTER TABLE clients FORCE ROW LEVEL SECURITY; CREATE POLICY clients_isolation ON clients 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 clients TO missionmatch_app; -- --------------------------------------------------------------------------- -- client_contacts: people at a client. A hiring manager is a contact with is_hiring_manager set, rather -- than a separate table, because the same person is often both the hiring manager and the billing -- contact and duplicating them would let the two copies drift. -- --------------------------------------------------------------------------- CREATE TABLE client_contacts ( organization_id uuid NOT NULL, id uuid NOT NULL DEFAULT gen_random_uuid(), client_id uuid NOT NULL, full_name text NOT NULL CHECK (length(btrim(full_name)) BETWEEN 1 AND 200), title text NOT NULL DEFAULT '', email text NOT NULL DEFAULT '', phone text NOT NULL DEFAULT '', is_hiring_manager boolean NOT NULL DEFAULT false, is_primary boolean NOT NULL DEFAULT false, status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive')), created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), PRIMARY KEY (organization_id, id), FOREIGN KEY (organization_id, client_id) REFERENCES clients (organization_id, id) ON DELETE CASCADE ); -- At most one primary contact per client. A second primary is a data entry error, not a valid state. CREATE UNIQUE INDEX uq_client_contacts_primary ON client_contacts (organization_id, client_id) WHERE is_primary; CREATE INDEX idx_client_contacts_client ON client_contacts (organization_id, client_id, full_name); CREATE INDEX idx_client_contacts_hm ON client_contacts (organization_id, client_id) WHERE is_hiring_manager; ALTER TABLE client_contacts ENABLE ROW LEVEL SECURITY; ALTER TABLE client_contacts FORCE ROW LEVEL SECURITY; CREATE POLICY client_contacts_isolation ON client_contacts 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 client_contacts TO missionmatch_app; -- --------------------------------------------------------------------------- -- requisitions: a job order. -- -- openings is the seat count and is what makes a fill rate meaningful. A requisition with three openings -- is not filled by one placement, so every downstream metric counts placements against openings rather -- than treating a requisition as a single unit. -- -- opened_at is the start of BOTH time to fill clocks and is separate from created_at, because a record -- entered late must still measure from when the order was actually received. -- -- held_days accumulates time the order was on hold. Storing the accumulator rather than a derived value -- keeps an adjusted time to fill computable without replaying every status change, and the raw clock -- stays available because both are legitimate and the industry does not agree on which to report. -- --------------------------------------------------------------------------- CREATE TABLE requisitions ( organization_id uuid NOT NULL REFERENCES organizations(id), id uuid NOT NULL DEFAULT gen_random_uuid(), client_id uuid, hiring_manager_id uuid, owner_user_id uuid, -- Optional bridge to the federal capture side. NULL for pure client work. requirement_id uuid, title text NOT NULL CHECK (length(btrim(title)) BETWEEN 1 AND 300), description text NOT NULL DEFAULT '', labor_category text NOT NULL DEFAULT '', openings int NOT NULL DEFAULT 1 CHECK (openings >= 1), priority text NOT NULL DEFAULT 'normal' CHECK (priority IN ('low', 'normal', 'high', 'critical')), employment_type text NOT NULL DEFAULT 'contract' CHECK (employment_type IN ('contract', 'contract_to_hire', 'direct_hire')), status text NOT NULL DEFAULT 'open' CHECK (status IN ('draft', 'open', 'on_hold', 'filled', 'closed', 'cancelled')), -- Clearance is a self reported indicator throughout this product, never a verified status. required_level text NOT NULL DEFAULT 'none' REFERENCES clearance_levels (code), required_poly text NOT NULL DEFAULT 'none' REFERENCES polygraph_types (code), work_location text NOT NULL DEFAULT '', remote_allowed boolean NOT NULL DEFAULT false, -- Money is exact. Rates are hourly and carry four decimal places because a wrap rate calculation -- multiplies them; salary and fee are whole dollars and cents. bill_rate_min numeric(12,4), bill_rate_max numeric(12,4), pay_rate_min numeric(12,4), pay_rate_max numeric(12,4), salary_min numeric(14,2), salary_max numeric(14,2), opened_at timestamptz NOT NULL DEFAULT now(), target_fill_date date, closed_at timestamptz, held_days int NOT NULL DEFAULT 0 CHECK (held_days >= 0), created_by uuid, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), deleted_at timestamptz, PRIMARY KEY (organization_id, id), FOREIGN KEY (organization_id, client_id) REFERENCES clients (organization_id, id) ON DELETE SET NULL, FOREIGN KEY (organization_id, hiring_manager_id) REFERENCES client_contacts (organization_id, id) ON DELETE SET NULL, FOREIGN KEY (organization_id, owner_user_id) REFERENCES users (organization_id, id) ON DELETE SET NULL, FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id) ON DELETE SET NULL, FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL, -- A range whose floor exceeds its ceiling is not a range. Checked per pair so a partially filled -- range stays legal, which is common while an order is still being scoped. CONSTRAINT requisitions_bill_range_chk CHECK (bill_rate_min IS NULL OR bill_rate_max IS NULL OR bill_rate_min <= bill_rate_max), CONSTRAINT requisitions_pay_range_chk CHECK (pay_rate_min IS NULL OR pay_rate_max IS NULL OR pay_rate_min <= pay_rate_max), CONSTRAINT requisitions_salary_range_chk CHECK (salary_min IS NULL OR salary_max IS NULL OR salary_min <= salary_max), CONSTRAINT requisitions_rates_nonneg_chk CHECK ( COALESCE(bill_rate_min, 0) >= 0 AND COALESCE(bill_rate_max, 0) >= 0 AND COALESCE(pay_rate_min, 0) >= 0 AND COALESCE(pay_rate_max, 0) >= 0 AND COALESCE(salary_min, 0) >= 0 AND COALESCE(salary_max, 0) >= 0), -- A closed or cancelled order must record when. An open one must not pretend it is closed. CONSTRAINT requisitions_closed_at_chk CHECK ( (status IN ('filled', 'closed', 'cancelled') AND closed_at IS NOT NULL) OR (status NOT IN ('filled', 'closed', 'cancelled') AND closed_at IS NULL)), CONSTRAINT requisitions_target_after_open_chk CHECK ( target_fill_date IS NULL OR target_fill_date >= opened_at::date) ); CREATE INDEX idx_requisitions_org_open ON requisitions (organization_id, status, opened_at DESC) WHERE deleted_at IS NULL; CREATE INDEX idx_requisitions_client ON requisitions (organization_id, client_id) WHERE deleted_at IS NULL; CREATE INDEX idx_requisitions_owner ON requisitions (organization_id, owner_user_id) WHERE deleted_at IS NULL; CREATE INDEX idx_requisitions_requirement ON requisitions (organization_id, requirement_id) WHERE requirement_id IS NOT NULL; ALTER TABLE requisitions ENABLE ROW LEVEL SECURITY; ALTER TABLE requisitions FORCE ROW LEVEL SECURITY; CREATE POLICY requisitions_isolation ON requisitions 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 requisitions TO missionmatch_app; -- +goose Down DROP TABLE IF EXISTS requisitions; DROP TABLE IF EXISTS client_contacts; DROP TABLE IF EXISTS clients;