102 lines
5.8 KiB
PL/PgSQL
102 lines
5.8 KiB
PL/PgSQL
CREATE TABLE skills_edu.profile_qualifications (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
profile_id uuid NOT NULL REFERENCES skills_edu.professional_profiles(id) ON DELETE CASCADE,
|
|
qualification_type varchar(30) NOT NULL CHECK (qualification_type IN ('degree','diploma','certificate','licence','completion_statement','other')),
|
|
name varchar(255) NOT NULL,
|
|
field_of_study varchar(255),
|
|
qualification_level varchar(100),
|
|
issuing_institution_name varchar(255) NOT NULL,
|
|
institution_master_id uuid,
|
|
issued_on date NOT NULL,
|
|
expires_on date,
|
|
holder_name varchar(255) NOT NULL,
|
|
verification_status varchar(20) NOT NULL DEFAULT 'declared' CHECK (verification_status IN ('declared','in_validation','validated','rejected','expired')),
|
|
verified_by bigint,
|
|
verified_at timestamptz,
|
|
verification_reason text,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
deleted_at timestamptz,
|
|
CHECK (expires_on IS NULL OR expires_on >= issued_on)
|
|
);
|
|
|
|
CREATE TABLE skills_edu.qualification_document_details (
|
|
id uuid PRIMARY KEY REFERENCES public.documents(id) ON DELETE RESTRICT,
|
|
qualification_id uuid REFERENCES skills_edu.profile_qualifications(id) ON DELETE RESTRICT,
|
|
experience_id uuid REFERENCES skills_edu.profile_experiences(id) ON DELETE RESTRICT,
|
|
document_type varchar(30) NOT NULL CHECK (document_type IN ('diploma','certificate','licence','completion_statement','academic_record','experience_proof','other')),
|
|
file_size_bytes bigint NOT NULL CHECK (file_size_bytes > 0),
|
|
document_group_id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
version_no integer NOT NULL DEFAULT 1 CHECK (version_no > 0),
|
|
replaces_document_id uuid REFERENCES skills_edu.qualification_document_details(id) ON DELETE RESTRICT,
|
|
malware_scan_status varchar(20) NOT NULL DEFAULT 'pending' CHECK (malware_scan_status IN ('pending','clean','infected','error')),
|
|
malware_scanned_at timestamptz,
|
|
quarantine_status varchar(20) NOT NULL DEFAULT 'quarantined' CHECK (quarantine_status IN ('quarantined','released','blocked','deleted')),
|
|
validation_status varchar(20) NOT NULL DEFAULT 'pending' CHECK (validation_status IN ('pending','in_validation','validated','rejected','expired','replaced')),
|
|
validation_reason text,
|
|
validated_by bigint,
|
|
validated_at timestamptz,
|
|
uploaded_by bigint NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
deleted_at timestamptz,
|
|
CHECK (((qualification_id IS NOT NULL)::integer + (experience_id IS NOT NULL)::integer) = 1),
|
|
CHECK (replaces_document_id IS NULL OR replaces_document_id <> id),
|
|
UNIQUE (document_group_id, version_no)
|
|
);
|
|
|
|
CREATE TABLE skills_edu.consent_details (
|
|
id uuid PRIMARY KEY REFERENCES public.consents(id) ON DELETE RESTRICT,
|
|
profile_id uuid NOT NULL REFERENCES skills_edu.professional_profiles(id) ON DELETE CASCADE,
|
|
legal_basis varchar(100),
|
|
policy_version varchar(50) NOT NULL,
|
|
channel varchar(30) NOT NULL,
|
|
expires_at timestamptz,
|
|
evidence jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE skills_edu.institutional_authorisations (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
profile_id uuid NOT NULL REFERENCES skills_edu.professional_profiles(id) ON DELETE CASCADE,
|
|
institution_master_id uuid NOT NULL,
|
|
purpose_code varchar(100) NOT NULL,
|
|
policy_version varchar(50) NOT NULL,
|
|
status varchar(20) NOT NULL CHECK (status IN ('granted','expired','revoked')),
|
|
granted_at timestamptz NOT NULL,
|
|
expires_at timestamptz,
|
|
revoked_at timestamptz,
|
|
evidence jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
CHECK (expires_at IS NULL OR expires_at > granted_at),
|
|
CHECK (revoked_at IS NULL OR revoked_at >= granted_at)
|
|
);
|
|
|
|
CREATE INDEX idx_qualifications_profile_status ON skills_edu.profile_qualifications(profile_id,verification_status) WHERE deleted_at IS NULL;
|
|
CREATE INDEX idx_documents_qualification_status ON skills_edu.qualification_document_details(qualification_id,validation_status) WHERE deleted_at IS NULL;
|
|
CREATE INDEX idx_documents_scan_status ON skills_edu.qualification_document_details(malware_scan_status,quarantine_status);
|
|
CREATE UNIQUE INDEX uq_documents_current_version ON skills_edu.qualification_document_details(document_group_id) WHERE deleted_at IS NULL AND validation_status <> 'replaced';
|
|
CREATE INDEX idx_authorisations_profile_institution ON skills_edu.institutional_authorisations(profile_id,institution_master_id,status);
|
|
|
|
ALTER TABLE skills_edu.profile_qualifications ADD FOREIGN KEY (verified_by) REFERENCES public.users(id) ON DELETE RESTRICT;
|
|
|
|
ALTER TABLE skills_edu.profile_qualifications ADD FOREIGN KEY (institution_master_id) REFERENCES public.training_providers(id) ON DELETE RESTRICT;
|
|
|
|
ALTER TABLE skills_edu.qualification_document_details ADD FOREIGN KEY (validated_by) REFERENCES public.users(id) ON DELETE RESTRICT;
|
|
|
|
ALTER TABLE skills_edu.qualification_document_details ADD FOREIGN KEY (uploaded_by) REFERENCES public.users(id) ON DELETE RESTRICT;
|
|
|
|
ALTER TABLE skills_edu.institutional_authorisations ADD FOREIGN KEY (institution_master_id) REFERENCES public.training_providers(id) ON DELETE RESTRICT;
|
|
|
|
CREATE FUNCTION skills_edu.validate_consent_dates() RETURNS trigger LANGUAGE plpgsql SET search_path=pg_catalog AS $$
|
|
DECLARE granted timestamptz; subject text;
|
|
BEGIN
|
|
SELECT granted_at,subject_ref INTO granted,subject FROM public.consents WHERE id=NEW.id;
|
|
IF subject IS DISTINCT FROM NEW.profile_id::text OR granted IS NULL OR (NEW.expires_at IS NOT NULL AND NEW.expires_at<=granted) THEN
|
|
RAISE EXCEPTION 'Consent master profile or validity mismatch' USING ERRCODE='23514';
|
|
END IF;
|
|
RETURN NEW;
|
|
END $$;
|
|
CREATE TRIGGER validate_consent_master BEFORE INSERT OR UPDATE ON skills_edu.consent_details
|
|
FOR EACH ROW EXECUTE FUNCTION skills_edu.validate_consent_dates();
|