39 lines
1.2 KiB
SQL
39 lines
1.2 KiB
SQL
-- 010_linkedin_url.sql
|
|
-- Persist the public LinkedIn profile URL extracted from a CV at ingest time
|
|
-- on users, manual_upload_candidate, and candidates. Fetch reads this column
|
|
-- instead of re-parsing resume text. Applied at startup by
|
|
-- alembic_setup.run_manual_sql().
|
|
|
|
ALTER TABLE app.users
|
|
ADD COLUMN IF NOT EXISTS linkedin_url TEXT;
|
|
|
|
ALTER TABLE app.manual_upload_candidate
|
|
ADD COLUMN IF NOT EXISTS linkedin_url TEXT;
|
|
|
|
ALTER TABLE app.candidates
|
|
ADD COLUMN IF NOT EXISTS linkedin_url TEXT;
|
|
|
|
-- Backfill from already-extracted /in/<slug> values.
|
|
UPDATE app.manual_upload_candidate
|
|
SET linkedin_url = 'https://www.linkedin.com/in/' || linkedin_slug
|
|
WHERE linkedin_url IS NULL
|
|
AND linkedin_slug IS NOT NULL
|
|
AND linkedin_slug <> '';
|
|
|
|
UPDATE app.users AS u
|
|
SET linkedin_url = m.linkedin_url
|
|
FROM app.manual_upload_candidate AS m
|
|
WHERE u.id = m.user_id
|
|
AND u.linkedin_url IS NULL
|
|
AND m.linkedin_url IS NOT NULL
|
|
AND m.linkedin_url <> '';
|
|
|
|
UPDATE app.users AS u
|
|
SET linkedin_url = 'https://www.linkedin.com/in/' || m.linkedin_slug
|
|
FROM app.inbox AS i
|
|
JOIN app.inbox_messages AS m ON m.id = i.message_id
|
|
WHERE i.user_id = u.id
|
|
AND u.linkedin_url IS NULL
|
|
AND m.linkedin_slug IS NOT NULL
|
|
AND m.linkedin_slug <> '';
|