74 lines
3.2 KiB
SQL
74 lines
3.2 KiB
SQL
-- 042_verification_check.sql
|
|
-- Annexure E - Employment Verification Check Form as its own table
|
|
-- (backend/verification_check/models.py, VerificationCheck). One row per referee
|
|
-- call, so a candidate can have several. recruiter_id and candidate_id both
|
|
-- point at users.id (recruiters and candidates are users in this system);
|
|
-- created_by is the logged-in user who filled the form.
|
|
--
|
|
-- The table name is mixed case at the user's request, so it must stay quoted
|
|
-- ("Verification_Check") in any raw SQL. Index / FK names follow the db_setup
|
|
-- NAMING_CONVENTION so a dev DB that autogenerated first is a no-op.
|
|
--
|
|
-- Idempotent, applied at startup by alembic_setup.run_manual_sql(). Needed
|
|
-- because prod boots with DB_AUTOGENERATE=false and never autogenerates tables.
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_type t
|
|
JOIN pg_namespace n ON n.oid = t.typnamespace
|
|
WHERE t.typname = 'rehireeligibility' AND n.nspname = 'app'
|
|
) THEN
|
|
CREATE TYPE app.rehireeligibility AS ENUM ('yes', 'no', 'conditional');
|
|
END IF;
|
|
END $$;
|
|
|
|
CREATE TABLE IF NOT EXISTS app."Verification_Check" (
|
|
id uuid PRIMARY KEY,
|
|
recruiter_id uuid,
|
|
candidate_id uuid,
|
|
position_applied varchar,
|
|
department_id uuid,
|
|
department varchar,
|
|
date_time timestamptz,
|
|
confirmed_job_title varchar,
|
|
confirmed_from date,
|
|
confirmed_to date,
|
|
reason_for_leaving varchar,
|
|
technical_knowledge_rating integer,
|
|
technical_knowledge_notes varchar,
|
|
reliability_rating integer,
|
|
reliability_notes varchar,
|
|
communication_rating integer,
|
|
communication_notes varchar,
|
|
problem_solving_rating integer,
|
|
problem_solving_notes varchar,
|
|
strengths varchar,
|
|
areas_for_growth varchar,
|
|
rehire_eligibility app.rehireeligibility,
|
|
rehire_comments varchar,
|
|
referee_full_name varchar,
|
|
referee_current_title varchar,
|
|
referee_company varchar,
|
|
referee_phone varchar,
|
|
referee_email varchar,
|
|
created_by uuid,
|
|
created_at timestamptz NOT NULL DEFAULT NOW(),
|
|
updated_at timestamptz NOT NULL DEFAULT NOW(),
|
|
is_deleted boolean NOT NULL DEFAULT false,
|
|
CONSTRAINT "fk_Verification_Check_recruiter_id_users"
|
|
FOREIGN KEY (recruiter_id) REFERENCES app.users (id),
|
|
CONSTRAINT "fk_Verification_Check_candidate_id_users"
|
|
FOREIGN KEY (candidate_id) REFERENCES app.users (id),
|
|
CONSTRAINT "fk_Verification_Check_department_id_departments"
|
|
FOREIGN KEY (department_id) REFERENCES app.departments (id),
|
|
CONSTRAINT "fk_Verification_Check_created_by_users"
|
|
FOREIGN KEY (created_by) REFERENCES app.users (id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS "ix_Verification_Check_recruiter_id"
|
|
ON app."Verification_Check" (recruiter_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS "ix_Verification_Check_candidate_id"
|
|
ON app."Verification_Check" (candidate_id);
|