68 lines
2.3 KiB
SQL
68 lines
2.3 KiB
SQL
-- Add CASCADE to all foreign keys referencing candidate to allow candidate deletion
|
|
ALTER TABLE app.candidate_document
|
|
DROP CONSTRAINT candidate_document_candidate_id_fkey,
|
|
ADD CONSTRAINT candidate_document_candidate_id_fkey
|
|
FOREIGN KEY (candidate_id)
|
|
REFERENCES app.candidate(candidate_id)
|
|
ON DELETE CASCADE;
|
|
|
|
ALTER TABLE app.candidate_education
|
|
DROP CONSTRAINT candidate_education_candidate_id_fkey,
|
|
ADD CONSTRAINT candidate_education_candidate_id_fkey
|
|
FOREIGN KEY (candidate_id)
|
|
REFERENCES app.candidate(candidate_id)
|
|
ON DELETE CASCADE;
|
|
|
|
ALTER TABLE app.candidate_transcript
|
|
DROP CONSTRAINT candidate_transcript_candidate_id_fkey,
|
|
ADD CONSTRAINT candidate_transcript_candidate_id_fkey
|
|
FOREIGN KEY (candidate_id)
|
|
REFERENCES app.candidate(candidate_id)
|
|
ON DELETE CASCADE;
|
|
|
|
ALTER TABLE app.candidate_license
|
|
DROP CONSTRAINT candidate_license_candidate_id_fkey,
|
|
ADD CONSTRAINT candidate_license_candidate_id_fkey
|
|
FOREIGN KEY (candidate_id)
|
|
REFERENCES app.candidate(candidate_id)
|
|
ON DELETE CASCADE;
|
|
|
|
ALTER TABLE app.candidate_language
|
|
DROP CONSTRAINT candidate_language_candidate_id_fkey,
|
|
ADD CONSTRAINT candidate_language_candidate_id_fkey
|
|
FOREIGN KEY (candidate_id)
|
|
REFERENCES app.candidate(candidate_id)
|
|
ON DELETE CASCADE;
|
|
|
|
ALTER TABLE app.candidate_experience
|
|
DROP CONSTRAINT candidate_experience_candidate_id_fkey,
|
|
ADD CONSTRAINT candidate_experience_candidate_id_fkey
|
|
FOREIGN KEY (candidate_id)
|
|
REFERENCES app.candidate(candidate_id)
|
|
ON DELETE CASCADE;
|
|
|
|
-- Delete duplicate emails, keeping only the first created occurrence
|
|
DELETE FROM app.candidate
|
|
WHERE candidate_id NOT IN (
|
|
SELECT candidate_id
|
|
FROM (
|
|
SELECT candidate_id,
|
|
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC) as rn
|
|
FROM app.candidate
|
|
WHERE email IS NOT NULL
|
|
) sub
|
|
WHERE rn = 1
|
|
);
|
|
|
|
-- Add email verification token to user table for double opt-in
|
|
ALTER TABLE app.user ADD COLUMN email_verification_token VARCHAR;
|
|
|
|
-- Remove magic_link_expiry since JWT handles expiry internally
|
|
ALTER TABLE app.user_auth DROP COLUMN magic_link_expiry;
|
|
|
|
-- Add unique constraint to candidate email to prevent duplicate registrations
|
|
ALTER TABLE app.candidate ADD CONSTRAINT candidate_email_unique UNIQUE (email);
|
|
|
|
-- Add field to track when incomplete process reminder was sent
|
|
ALTER TABLE app.candidate ADD COLUMN incomplete_reminder_sent_at TIMESTAMP;
|