124 lines
7.1 KiB
SQL
124 lines
7.1 KiB
SQL
CREATE TABLE skills_edu.professional_profiles (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_master_id bigint,
|
|
registration_mode varchar(20) NOT NULL CHECK (registration_mode IN ('individual','institution')),
|
|
full_name varchar(255) NOT NULL,
|
|
identity_hash char(64),
|
|
professional_title varchar(255),
|
|
professional_summary text,
|
|
occupation_id uuid REFERENCES public.occupations(id) ON DELETE SET NULL,
|
|
location_master_id uuid,
|
|
location_level varchar(30) CHECK (location_level IS NULL OR location_level IN ('national','region','corridor','province','district','municipality','influence_area','zee','zfi','industrial_park','indicative')),
|
|
available_from date,
|
|
availability_type varchar(30),
|
|
visibility_level varchar(20) NOT NULL DEFAULT 'private' CHECK (visibility_level IN ('private','validated_investors','institutional','public_summary')),
|
|
status varchar(30) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','submitted','in_validation','validated','rejected','suspended','deactivated')),
|
|
submitted_at timestamptz,
|
|
validated_at timestamptz,
|
|
validated_by bigint,
|
|
created_by bigint,
|
|
updated_by bigint,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
deleted_at timestamptz
|
|
);
|
|
|
|
CREATE UNIQUE INDEX uq_professional_profiles_user_active ON skills_edu.professional_profiles(user_master_id) WHERE user_master_id IS NOT NULL AND deleted_at IS NULL AND status <> 'deactivated';
|
|
CREATE UNIQUE INDEX uq_professional_profiles_identity_active ON skills_edu.professional_profiles(identity_hash) WHERE identity_hash IS NOT NULL AND deleted_at IS NULL AND status <> 'deactivated';
|
|
|
|
CREATE TABLE skills_edu.profile_contacts (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
profile_id uuid NOT NULL REFERENCES skills_edu.professional_profiles(id) ON DELETE CASCADE,
|
|
contact_type varchar(20) NOT NULL CHECK (contact_type IN ('email','phone')),
|
|
contact_value_encrypted text NOT NULL,
|
|
contact_hash char(64) NOT NULL,
|
|
is_primary boolean NOT NULL DEFAULT false,
|
|
is_confirmed boolean NOT NULL DEFAULT false,
|
|
confirmed_at timestamptz,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (profile_id, contact_type, contact_hash)
|
|
);
|
|
|
|
CREATE TABLE skills_edu.profile_skills (
|
|
profile_id uuid NOT NULL REFERENCES skills_edu.professional_profiles(id) ON DELETE CASCADE,
|
|
skill_id uuid NOT NULL REFERENCES public.skills(id) ON DELETE RESTRICT,
|
|
proficiency_level varchar(30) NOT NULL,
|
|
verification_status varchar(20) NOT NULL DEFAULT 'declared' CHECK (verification_status IN ('declared','assessed','validated','rejected','expired')),
|
|
years_experience numeric(5,2) CHECK (years_experience IS NULL OR years_experience >= 0),
|
|
verified_by bigint,
|
|
verified_at timestamptz,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (profile_id, skill_id)
|
|
);
|
|
|
|
CREATE TABLE skills_edu.profile_experiences (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
profile_id uuid NOT NULL REFERENCES skills_edu.professional_profiles(id) ON DELETE CASCADE,
|
|
job_title varchar(255) NOT NULL,
|
|
sector_master_id bigint,
|
|
organisation_name varchar(255),
|
|
started_on date NOT NULL,
|
|
ended_on date,
|
|
description text,
|
|
verification_status varchar(20) NOT NULL DEFAULT 'declared' CHECK (verification_status IN ('declared','in_validation','validated','rejected')),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
CHECK (ended_on IS NULL OR ended_on >= started_on)
|
|
);
|
|
|
|
CREATE TABLE skills_edu.profile_languages (
|
|
profile_id uuid NOT NULL REFERENCES skills_edu.professional_profiles(id) ON DELETE CASCADE,
|
|
language_code varchar(10) NOT NULL,
|
|
proficiency_level varchar(30) NOT NULL,
|
|
verification_status varchar(20) NOT NULL DEFAULT 'declared' CHECK (verification_status IN ('declared','assessed','validated')),
|
|
PRIMARY KEY (profile_id, language_code)
|
|
);
|
|
|
|
CREATE TABLE skills_edu.institutional_candidate_registrations (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
profile_id uuid NOT NULL UNIQUE REFERENCES skills_edu.professional_profiles(id) ON DELETE CASCADE,
|
|
institution_master_id uuid NOT NULL,
|
|
program_master_id uuid,
|
|
internal_candidate_code varchar(100),
|
|
academic_level varchar(100),
|
|
class_name varchar(100),
|
|
study_status varchar(30) CHECK (study_status IS NULL OR study_status IN ('enrolled','finalist','completed','suspended','withdrawn')),
|
|
expected_completion_date date,
|
|
institution_operator_user_id bigint NOT NULL,
|
|
institution_validation_status varchar(20) NOT NULL DEFAULT 'pending' CHECK (institution_validation_status IN ('pending','validated','rejected','suspended')),
|
|
candidate_confirmation_status varchar(20) NOT NULL DEFAULT 'pending' CHECK (candidate_confirmation_status IN ('pending','confirmed','rejected','revoked')),
|
|
candidate_confirmed_at timestamptz,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (institution_master_id, internal_candidate_code)
|
|
);
|
|
|
|
CREATE INDEX idx_profiles_searchable ON skills_edu.professional_profiles(status,visibility_level,occupation_id,location_master_id) WHERE deleted_at IS NULL;
|
|
CREATE INDEX idx_profile_skills_skill ON skills_edu.profile_skills(skill_id,verification_status);
|
|
CREATE INDEX idx_profile_contacts_hash ON skills_edu.profile_contacts(contact_hash);
|
|
CREATE UNIQUE INDEX uq_profile_primary_contact_type ON skills_edu.profile_contacts(profile_id,contact_type) WHERE is_primary = true;
|
|
CREATE TRIGGER trg_profiles_updated_at BEFORE UPDATE ON skills_edu.professional_profiles FOR EACH ROW EXECUTE FUNCTION skills_edu.set_updated_at();
|
|
CREATE TRIGGER trg_profile_skills_updated_at BEFORE UPDATE ON skills_edu.profile_skills FOR EACH ROW EXECUTE FUNCTION skills_edu.set_updated_at();
|
|
|
|
ALTER TABLE skills_edu.professional_profiles ADD FOREIGN KEY (user_master_id) REFERENCES public.users(id) ON DELETE RESTRICT;
|
|
|
|
ALTER TABLE skills_edu.professional_profiles ADD FOREIGN KEY (created_by) REFERENCES public.users(id) ON DELETE RESTRICT;
|
|
|
|
ALTER TABLE skills_edu.professional_profiles ADD FOREIGN KEY (updated_by) REFERENCES public.users(id) ON DELETE RESTRICT;
|
|
|
|
ALTER TABLE skills_edu.professional_profiles ADD FOREIGN KEY (validated_by) REFERENCES public.users(id) ON DELETE RESTRICT;
|
|
|
|
ALTER TABLE skills_edu.professional_profiles ADD FOREIGN KEY (location_master_id) REFERENCES public.geo_locations(id) ON DELETE RESTRICT;
|
|
|
|
ALTER TABLE skills_edu.profile_skills ADD FOREIGN KEY (verified_by) REFERENCES public.users(id) ON DELETE RESTRICT;
|
|
|
|
ALTER TABLE skills_edu.profile_experiences ADD FOREIGN KEY (sector_master_id) REFERENCES public.sectors(id) ON DELETE RESTRICT;
|
|
|
|
ALTER TABLE skills_edu.institutional_candidate_registrations ADD FOREIGN KEY (institution_operator_user_id) REFERENCES public.users(id) ON DELETE RESTRICT;
|
|
|
|
ALTER TABLE skills_edu.institutional_candidate_registrations ADD FOREIGN KEY (institution_master_id) REFERENCES public.training_providers(id) ON DELETE RESTRICT;
|
|
|
|
ALTER TABLE skills_edu.institutional_candidate_registrations ADD FOREIGN KEY (program_master_id) REFERENCES public.courses(id) ON DELETE RESTRICT;
|