-- ============================================================================ -- Krow — employee roles -- -- Phase 4. The supply half of a pair whose demand half already exists. -- -- WHAT THIS TABLE IS FOR -- -- `job_postings` is what the organization NEEDS FILLED: a company, a title, a -- pay range, a set of requirements. This table is what a WORKER SAYS THEY DO: -- the role they present themselves as, what they have done before, what they -- want to be paid, and when they can work. -- -- Those are two different records that happen to share a vocabulary, and -- collapsing them was the obvious wrong turn. A posting without a company is -- not a worker's role, and a worker who is available on weekends is not a -- vacancy. Owliver now has to create both from the same panel, so the -- distinction has to exist somewhere it cannot be blurred — here. -- -- WHY THERE IS NO FOREIGN KEY TO job_postings -- -- Supply and demand meet through `job_applications`, which already exists and -- already carries the funnel. A column here pointing at a posting would be a -- second, weaker version of that relationship — one with no status, no history -- and no interview attached — and the two would disagree the first time -- somebody withdrew. -- -- WHY THE WORKER IS IDENTIFIED TWICE -- -- `worker_profile_id` is the join when a profile exists; `worker_email` is the -- durable identity and is what the talent row-scope predicate reads. Exactly -- the pair `evidence` uses, and for the same reason: `worker_profiles.user_id` -- is itself ON DELETE SET NULL, so a profile is not a stable identifier. -- -- CASCADE on the profile, matching `evidence`. A declared role orphaned to a -- bare email cannot be recovered — nothing else on the row says who the person -- was — so it goes with the profile rather than lingering as a record nobody -- can resolve. -- -- WHAT IS DELIBERATELY ABSENT -- -- a clients table The company a position is staffed for is still -- free text on job_postings, by blueprint decision -- D2. This table does not name a company at all: a -- worker's role is theirs, not a client's. -- UNIQUE on the worker A worker may declare Bartender AND Server, and a -- worker placed as a Bartender who starts seeking -- again needs a second row rather than an -- overwritten one. History is the point. -- a DELETE path Retirement is `status = 'inactive'`. A role that -- was matched against and then vanished is a record -- nobody can explain, which is what §6 exists to -- prevent. -- ============================================================================ SET search_path = public; -- Seeking, placed, inactive. Deliberately NOT job_postings' posting_status: -- 'draft' and 'paused' are authoring states for a vacancy and mean nothing -- about a person, and sharing the type would let one table's new label appear -- in the other's API as a value it has no handling for. CREATE TYPE employee_role_status AS ENUM ('seeking', 'placed', 'inactive'); CREATE TABLE employee_roles ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), legacy_id text UNIQUE, org_id uuid NOT NULL REFERENCES organizations (id) ON DELETE CASCADE, -- The worker. See the note above on why both. worker_profile_id uuid REFERENCES worker_profiles (id) ON DELETE CASCADE, worker_email citext NOT NULL, worker_name text NOT NULL DEFAULT '', -- Matched to role_categories.name BY NAME, exactly as job_postings does. role_category text NOT NULL DEFAULT '', experience_years int NOT NULL DEFAULT 0, english_level english_level NOT NULL DEFAULT 'basic', certifications text[] NOT NULL DEFAULT '{}', -- What the worker is asking for, against job_postings' pay_min/pay_max. -- Zero means unstated rather than free: the check below allows a max of 0 -- with a min set, which is "from $25/hr, no ceiling given". desired_pay_min int NOT NULL DEFAULT 0, desired_pay_max int NOT NULL DEFAULT 0, availability text[] NOT NULL DEFAULT '{}', notes text NOT NULL DEFAULT '', status employee_role_status NOT NULL DEFAULT 'seeking', -- WHO ACTED, always the session user. Never the worker: an operator records -- a role on someone's behalf, and conflating the two would make the audit -- trail say the worker filed it themselves. created_by uuid REFERENCES users (id) ON DELETE SET NULL, created_date timestamptz NOT NULL DEFAULT now(), updated_date timestamptz NOT NULL DEFAULT now(), CONSTRAINT employee_roles_experience_range CHECK (experience_years BETWEEN 0 AND 40), CONSTRAINT employee_roles_pay_nonneg CHECK (desired_pay_min >= 0 AND desired_pay_max >= 0), CONSTRAINT employee_roles_pay_ordered CHECK (desired_pay_max = 0 OR desired_pay_max >= desired_pay_min), -- citext makes '' and ' ' distinct from NULL but equally useless as an -- identity, and the talent scope reads this column. A blank one would scope -- to nothing and read as a bug rather than a denial. CONSTRAINT employee_roles_email_not_blank CHECK (length(btrim(worker_email::text)) > 0) ); -- The list, newest first — the resource's default sort. CREATE INDEX employee_roles_org_created_idx ON employee_roles (org_id, created_date DESC); -- The talent row-scope predicate, which runs on every talent read. CREATE INDEX employee_roles_org_email_idx ON employee_roles (org_id, worker_email); -- "who can work as a Bartender?" — the reason the table exists. CREATE INDEX employee_roles_org_category_idx ON employee_roles (org_id, role_category); -- Partial: the column is nullable and the join is only meaningful when set. CREATE INDEX employee_roles_profile_idx ON employee_roles (worker_profile_id) WHERE worker_profile_id IS NOT NULL; COMMENT ON TABLE employee_roles IS 'What a worker declares they do: role, experience, desired pay and availability. The supply ' 'side of job_postings, which is what the organization needs filled. The two meet through ' 'job_applications, not through a column here.';