Janady13/missionmatch / db/migrations/0047_recruiting_clients_requisitions.sql

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / db/migrations/0047_recruiting_clients_requisitions.sql
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	db/migrations/0047_recruiting_clients_requisitions.sql
Original SHA-256	27e7dc5794c643ac28bf2a3c8786c16ca944455d2f1f3c9dcb1d23200a948053
Git blob SHA	4a3b4277168a3fc3471b3f7248a3e7f4a45b5789
Reading-copy SHA-256	27e7dc5794c643ac28bf2a3c8786c16ca944455d2f1f3c9dcb1d23200a948053

Original source ↗

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/0047_recruiting_clients_requisitions.sql	feature/opportunity-to-outcome	720d47fae476b0d38477ae4401697d372b1ea2bc	Open original
Janady13/missionmatch / db/migrations/0047_recruiting_clients_requisitions.sql	feature/aioe-upgrade	2178e7298d0e5d9af83afa12402fd587f0ba6ed8	Open original

Recovered source transcript

Download complete source text ↓172 lines · 10.8 KB

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