Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/queries/sourcing.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 39df94f92b2a33f852481fe1954dabed841524bb |
| Branch | w0-aedt-compliance |
| Path or archive member | db/queries/sourcing.sql |
| Original SHA-256 | abc8fea182febafbb727c41d0c73e01e01f220069806d6c6f0f7d3c08914a83f |
| Git blob SHA | 593a2aa5f75d859c1ea9aec0e1fd7cc0c77e1b06 |
| Reading-copy SHA-256 | abc8fea182febafbb727c41d0c73e01e01f220069806d6c6f0f7d3c08914a83f |
Recovered source transcript
Download complete source text ↓51 lines · 2.0 KB
-- External candidate sourcing. The import command (cmd/import-candidates) writes as the migrator role
-- with an explicit organization_id; the delete path runs as the app role inside a tenant tx (RLS).
-- name: UpsertExternalCandidate :one
INSERT INTO candidates (
organization_id, full_name, email, headline,
clearance_level, clearance_poly, clearance_status,
years_experience, location, remote, relocatable, available, source, external_id
) VALUES (
$1, $2, $3, $4,
'unknown', 'none', 'unknown',
$5, $6, $7, false, true, 'external', $8
)
ON CONFLICT (organization_id, external_id) WHERE external_id IS NOT NULL DO UPDATE SET
full_name = EXCLUDED.full_name,
email = EXCLUDED.email,
headline = EXCLUDED.headline,
years_experience = EXCLUDED.years_experience,
location = EXCLUDED.location,
remote = EXCLUDED.remote,
updated_at = now()
RETURNING id;
-- name: InsertSourcingLog :exec
INSERT INTO sourcing_log (organization_id, candidate_id, external_id, source, provider, query, legal_basis)
VALUES ($1, $2, $3, $4, $5, $6, $7);
-- name: ListExternalCandidates :many
SELECT id, full_name, email, headline, location, years_experience, available, external_id, created_at
FROM candidates
WHERE source = 'external' AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT $1;
-- name: ListSourcingLog :many
SELECT id, candidate_id, external_id, source, provider, query, legal_basis, collected_at
FROM sourcing_log
ORDER BY collected_at DESC
LIMIT $1;
-- name: DeleteExternalCandidate :execrows
DELETE FROM candidates WHERE id = $1 AND source = 'external';
-- name: DetachSourcingLog :exec
UPDATE sourcing_log SET candidate_id = NULL WHERE candidate_id = $1;
-- name: DeleteSlateMembersForCandidate :exec
-- Erasure must also remove slate placements of the sourced person; slate_members has no ON DELETE rule
-- so the candidate delete would otherwise fail. RLS scopes this to the caller's org.
DELETE FROM slate_members WHERE candidate_id = $1;