Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / internal/platform/benchimport/structured_source.go |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | internal/platform/benchimport/structured_source.go |
| Original SHA-256 | 2b1836b8190cf3368ebdae585d671a8d16910bcf2025c7c352b9fe8c3dc8e853 |
| Git blob SHA | 9e28a8f67743f32b06619bc7d84896c6458b80dc |
| Reading-copy SHA-256 | 2b1836b8190cf3368ebdae585d671a8d16910bcf2025c7c352b9fe8c3dc8e853 |
Recovered source transcript
Download complete source text ↓498 lines · 19.0 KB
package benchimport
import (
"bufio"
"context"
"encoding/csv"
"encoding/json"
"errors"
"fmt"
"io"
"os"
"path/filepath"
"strconv"
"strings"
"github.com/xuri/excelize/v2"
)
// structuredSource is the FileSource backed by a structured roster file, decoded with the standard library
// only. It supports two formats picked by extension: CSV with a header row, and JSONL (one JSON object per
// line). Each well formed row becomes a BenchRecord; a malformed row is skipped and counted, never aborts
// the batch. The structured fields here are the same workforce tokens the resume path produces, so the
// downstream ingest is identical regardless of source.
type structuredSource struct{}
// NewStructuredSource builds a structured roster FileSource.
func NewStructuredSource() FileSource { return structuredSource{} }
// Resumes on a structured source delegates to the resume reader is not meaningful; a structured source
// has no resume directory. It returns an empty result so a caller that picked the structured source by
// mistake gets nothing rather than a panic.
func (structuredSource) Resumes(ctx context.Context, dir string, yield func(rec BenchRecord, file string) error) (BatchResult, error) {
_ = ctx
_ = dir
_ = yield
return BatchResult{}, nil
}
// Profiles decodes the roster at path and yields a BenchRecord per well formed row. The format is chosen
// by extension: .csv uses the header mapped CSV decoder, .jsonl or .ndjson or .json uses the line
// delimited JSON decoder. The batch identifier passed to yield is the file base name, recorded against
// bench_import_log.
func (s structuredSource) Profiles(ctx context.Context, path string, yield func(rec BenchRecord, batch string) error) (BatchResult, error) {
f, err := os.Open(path)
if err != nil {
return BatchResult{}, err
}
defer func() { _ = f.Close() }()
batch := filepath.Base(path)
ext := strings.ToLower(filepath.Ext(path))
switch ext {
case ".csv":
return decodeCSV(ctx, f, batch, yield)
case ".jsonl", ".ndjson", ".json":
return decodeJSONL(ctx, f, batch, yield)
case ".xlsx", ".xlsm":
// A RECRUITER SENDS A SPREADSHEET. Asking them to export to CSV first is asking them to do a
// conversion they should never have to think about, and it is the step where a phone number becomes
// scientific notation and a leading zero disappears.
return decodeXLSX(ctx, path, batch, yield)
default:
return BatchResult{}, fmt.Errorf(
"benchimport: cannot read %q. Supported: .xlsx and .xlsm spreadsheets, .csv, and .json or .jsonl", ext)
}
}
// decodeXLSX reads the FIRST sheet of a workbook and hands its rows to the same header mapping the CSV path
// uses, so a spreadsheet and a CSV of the same data import identically. excelize is already a dependency and
// already proven in this repository: internal/modules/ontology/adapters/psc.go parses the PSC Manual with it.
//
// THE FIRST SHEET, DELIBERATELY. Guessing which of several sheets holds the roster would be a silent choice
// about someone's data. If a workbook's first sheet is not the roster, that is worth an error a person can
// read rather than importing the wrong tab and reporting success.
//
// VALUES ARE READ AS THE SHEET DISPLAYS THEM, which is what makes a phone number survive. excelize returns the
// formatted string, so 5551234567 stays digits instead of arriving as 5.55123e+09, and a clearance code typed
// as text is not helpfully converted into something else.
func decodeXLSX(ctx context.Context, path, batch string, yield func(rec BenchRecord, batch string) error) (BatchResult, error) {
var res BatchResult
wb, err := excelize.OpenFile(path)
if err != nil {
return res, fmt.Errorf("benchimport: open spreadsheet: %w", err)
}
defer func() { _ = wb.Close() }()
sheets := wb.GetSheetList()
if len(sheets) == 0 {
return res, errors.New("benchimport: the workbook has no sheets")
}
rows, err := wb.GetRows(sheets[0])
if err != nil {
return res, fmt.Errorf("benchimport: read sheet %q: %w", sheets[0], err)
}
if len(rows) < 2 {
return res, fmt.Errorf("benchimport: sheet %q has a header but no data rows", sheets[0])
}
return decodeTable(ctx, rows[0], rows[1:], batch, yield)
}
// decodeCSV reads a header row, maps the standard bench columns by lowercased header name, and yields one
// BenchRecord per data row. A row with the wrong field count or no usable signal is skipped and counted.
// Columns are matched flexibly (name, location, skills, certs, agencies, years, clearance, email, phone,
// external_id) with a few common aliases.
func decodeCSV(ctx context.Context, r io.Reader, batch string, yield func(rec BenchRecord, batch string) error) (BatchResult, error) {
var res BatchResult
cr := csv.NewReader(r)
cr.FieldsPerRecord = -1 // a short or long row is handled per row, not as a fatal parse error
cr.TrimLeadingSpace = true
header, err := cr.Read()
if err == io.EOF {
return res, nil
}
if err != nil {
return res, fmt.Errorf("benchimport: read csv header: %w", err)
}
// ROW BY ROW, NEVER ReadAll. A single malformed row must be SKIPPED AND COUNTED, never fatal to the batch,
// which is what the FileSource contract promises and what a recruiter's real export needs: one stray quote
// in row 3 of 800 must not throw away the other 799. Refactoring this to ReadAll while extracting the
// shared mapping quietly broke that, and the existing test caught it, which is the whole reason that test
// has a deliberately malformed row in it.
var rows [][]string
for {
row, rerr := cr.Read()
if rerr == io.EOF {
break
}
if rerr != nil {
var pe *csv.ParseError
if errors.As(rerr, &pe) {
res.Read++
res.Skipped++
continue
}
return res, fmt.Errorf("benchimport: read csv rows: %w", rerr)
}
rows = append(rows, row)
}
tab, err := decodeTable(ctx, header, rows, batch, yield)
// Merge, because the parse skips counted above happened before decodeTable ever saw a row.
tab.Read += res.Read
tab.Skipped += res.Skipped
return tab, err
}
// decodeTable is the ONE mapping both a CSV and a spreadsheet go through, so the same data imports identically
// whichever way a recruiter sends it. Splitting them would mean two behaviours to keep in step and one of them
// would drift.
//
// UnmappedHeaders is recorded rather than discarded. A column nobody could match is the single most useful
// thing to tell a person after an import: it turns "only 40 of my 200 people came in" into "your Clearance
// column is called Eligibility Status and we did not read it", which is a sentence they can act on.
func decodeTable(ctx context.Context, header []string, rows [][]string, batch string, yield func(rec BenchRecord, batch string) error) (BatchResult, error) {
var res BatchResult
colIndex := map[string]int{}
known := map[string]bool{}
for i, h := range header {
c := canonHeader(h)
if c == "" {
continue
}
if _, dup := colIndex[c]; !dup {
colIndex[c] = i
}
known[c] = true
}
// AUTO SORT. For any heading the alias table could not place, look at the VALUES in that column and
// propose what it is. This is what lets a recruiter hand over a file with a column called
// "Eligibility Status" and have the clearance land in the right place without anyone mapping anything.
//
// A confident proposal is APPLIED, and every proposal is REPORTED either way, so nothing is inferred
// silently. The preview shows the recruiter what was worked out and on what evidence, which is the
// difference between a helpful import and one that quietly puts the wrong data in the wrong column.
for i, h := range header {
c := canonHeader(h)
if c == "" || recognisedColumns[c] {
continue
}
col := make([]string, 0, 40)
for _, row := range rows {
if i < len(row) {
col = append(col, row[i])
}
if len(col) == 60 {
break
}
}
inf := InferColumn(strings.TrimSpace(h), col)
if inf.Canonical != "" {
res.Inferred = append(res.Inferred, inf)
}
// Only fill a slot nothing already claims. A heading that matched properly always wins over a guess.
if inf.Applied() {
if _, taken := colIndex[inf.Canonical]; !taken {
colIndex[inf.Canonical] = i
continue
}
}
res.UnmappedHeaders = appendUnique(res.UnmappedHeaders, strings.TrimSpace(h))
}
for _, row := range rows {
if len(row) == 0 {
continue
}
res.Read++
get := func(key string) string {
i, ok := colIndex[key]
if !ok || i >= len(row) {
return ""
}
return strings.TrimSpace(row[i])
}
rec := BenchRecord{
FullName: firstNonEmpty(get("name"), get("full_name"), get("fullname")),
Location: firstNonEmpty(get("location"), get("city_state")),
JobTitle: firstNonEmpty(get("job_title"), get("title"), get("role")),
Skills: splitList(firstNonEmpty(get("skills"), get("skill"))),
Certs: splitList(firstNonEmpty(get("certs"), get("certifications"), get("certification"))),
Agencies: splitList(firstNonEmpty(get("agencies"), get("agency"), get("customers"))),
Years: parseYears(firstNonEmpty(get("years"), get("years_experience"), get("experience"))),
ClearanceCode: firstNonEmpty(get("clearance"), get("clearance_level")),
ExternalID: firstNonEmpty(get("external_id"), get("id")),
Email: strings.ToLower(firstNonEmpty(get("email"))),
Phone: firstNonEmpty(get("phone")),
}
if !hasSignal(rec) {
res.Skipped++
continue
}
// Count a record as mapped only after yield accepts it, so a batch cap (yield returns a stop error
// before the record is processed) does not inflate the mapped count over new plus enriched.
if err := yield(rec, batch); err != nil {
return res, err
}
res.Mapped++
}
_ = ctx
return res, nil
}
// recognisedColumns is every heading the mapping above actually reads, AFTER canonicalisation. It exists only
// so an unrecognised column can be reported by name; nothing branches on it.
var recognisedColumns = map[string]bool{
"name": true, "full_name": true, "fullname": true,
"location": true, "city_state": true,
"job_title": true, "title": true, "role": true,
"skills": true, "skill": true,
"certs": true, "certifications": true, "certification": true,
"agencies": true, "agency": true, "customers": true,
"years": true, "years_experience": true, "experience": true,
"clearance": true, "clearance_level": true,
"external_id": true, "id": true,
"email": true, "phone": true,
}
func appendUnique(xs []string, v string) []string {
if v == "" {
return xs
}
for _, x := range xs {
if strings.EqualFold(x, v) {
return xs
}
}
return append(xs, v)
}
// jsonlRow is the decode shape for one JSONL object. The bench columns map to JSON keys with a few common
// aliases handled in fromJSONLRow.
type jsonlRow struct {
Name string `json:"name"`
FullName string `json:"full_name"`
Location string `json:"location"`
JobTitle string `json:"job_title"`
Title string `json:"title"`
Role string `json:"role"`
Skills []string `json:"skills"`
Certs []string `json:"certs"`
Certifications []string `json:"certifications"`
Agencies []string `json:"agencies"`
Years int `json:"years"`
YearsExp int `json:"years_experience"`
Clearance string `json:"clearance"`
ClearanceLevel string `json:"clearance_level"`
ExternalID string `json:"external_id"`
ID string `json:"id"`
Email string `json:"email"`
Phone string `json:"phone"`
}
// decodeJSONL reads one JSON object per line and yields one BenchRecord per well formed line. A blank line
// is ignored; a line that does not parse, or that yields no signal, is skipped and counted.
func decodeJSONL(ctx context.Context, r io.Reader, batch string, yield func(rec BenchRecord, batch string) error) (BatchResult, error) {
var res BatchResult
sc := bufio.NewScanner(r)
sc.Buffer(make([]byte, 0, 64*1024), 4*1024*1024) // allow a fat profile line without splitting it
for sc.Scan() {
line := strings.TrimSpace(sc.Text())
if line == "" {
continue
}
res.Read++
var jr jsonlRow
if err := json.Unmarshal([]byte(line), &jr); err != nil {
res.Skipped++
continue
}
rec := fromJSONLRow(jr)
if !hasSignal(rec) {
res.Skipped++
continue
}
// Count a record as mapped only after yield accepts it, so a batch cap (yield returns a stop error
// before the record is processed) does not inflate the mapped count over new plus enriched.
if err := yield(rec, batch); err != nil {
return res, err
}
res.Mapped++
}
if err := sc.Err(); err != nil {
return res, fmt.Errorf("benchimport: scan jsonl: %w", err)
}
_ = ctx
return res, nil
}
// fromJSONLRow folds the alias keys into one BenchRecord.
func fromJSONLRow(jr jsonlRow) BenchRecord {
return BenchRecord{
FullName: firstNonEmpty(strings.TrimSpace(jr.Name), strings.TrimSpace(jr.FullName)),
Location: strings.TrimSpace(jr.Location),
JobTitle: firstNonEmpty(strings.TrimSpace(jr.JobTitle), strings.TrimSpace(jr.Title), strings.TrimSpace(jr.Role)),
Skills: trimList(firstNonEmptyList(jr.Skills, nil)),
Certs: trimList(firstNonEmptyList(jr.Certs, jr.Certifications)),
Agencies: trimList(jr.Agencies),
Years: firstNonZero(jr.Years, jr.YearsExp),
ClearanceCode: firstNonEmpty(strings.TrimSpace(jr.Clearance), strings.TrimSpace(jr.ClearanceLevel)),
ExternalID: firstNonEmpty(strings.TrimSpace(jr.ExternalID), strings.TrimSpace(jr.ID)),
Email: strings.ToLower(strings.TrimSpace(jr.Email)),
Phone: strings.TrimSpace(jr.Phone),
}
}
// ---------- helpers ----------
// canonHeader normalizes a CSV header to a lowercased, underscore joined key so "Full Name", "full_name",
// and "FULL NAME" all map to the same column.
// canonHeader normalises one column heading so a recruiter's real export matches without anyone editing it.
//
// IT USED TO BE lowercase plus spaces to underscores, AND NOTHING ELSE. That silently required the file to
// already be in our format. A Bullhorn export heading "Candidate Name" became "candidate_name", matched none
// of the aliases, and the row was dropped with no explanation, so a recruiter uploading a perfectly good
// spreadsheet saw zero records imported and no reason why. The parser was correct and the product was unusable,
// which is the worst combination because nothing looks broken.
//
// So it now also strips the punctuation exports carry, collapses repeated separators, and drops a leading
// noise word that ATS systems prefix onto otherwise clear headings. "Candidate Name" and "Candidate: Name"
// and "candidate_name" all become "name", which is a heading the mapping already understands.
func canonHeader(h string) string {
h = strings.ToLower(strings.TrimSpace(h))
h = strings.Map(func(r rune) rune {
switch {
case r >= 'a' && r <= 'z', r >= '0' && r <= '9':
return r
default:
return '_'
}
}, h)
for strings.Contains(h, "__") {
h = strings.ReplaceAll(h, "__", "_")
}
h = strings.Trim(h, "_")
// A leading qualifier that adds nothing. "candidate_name" and "person_email" are the same columns as
// "name" and "email", and every ATS spells them differently.
for _, p := range []string{"candidate_", "person_", "applicant_", "contact_", "employee_", "worker_"} {
if strings.HasPrefix(h, p) && len(h) > len(p) {
h = strings.TrimPrefix(h, p)
break
}
}
if canon, ok := headerAliases[h]; ok {
return canon
}
return h
}
// headerAliases maps what real exports actually say onto the headings the mapping below already reads. Every
// entry here is a column name a recruiter has no reason to change by hand.
//
// IT IS DELIBERATELY A CLOSED TABLE rather than fuzzy matching. A guess that silently maps "manager_name" onto
// the candidate's name would put the wrong human in the bench, and a bench with wrong names is worse than an
// import that refused. When a heading is not recognised the column is simply not read, the row survives on its
// other columns, and the caller can report the unmapped headings so a person decides.
var headerAliases = map[string]string{
// the person
"first_and_last_name": "name", "candidate": "name", "fullname": "full_name",
"display_name": "name", "legal_name": "name",
// contact, used only for dedup and never embedded
"e_mail": "email", "email_address": "email", "primary_email": "email", "work_email": "email",
"mobile": "phone", "cell": "phone", "cell_phone": "phone", "mobile_phone": "phone",
"phone_number": "phone", "primary_phone": "phone", "telephone": "phone",
// where they work
"city_st": "location", "city_state_zip": "location", "work_location": "location",
"geography": "location", "market": "location", "region": "location", "city": "location",
// what they do
"current_title": "job_title", "job": "job_title", "position": "job_title",
"labor_category": "job_title", "lcat": "job_title", "occupation": "job_title",
// what they know
"skill_set": "skills", "technical_skills": "skills", "keywords": "skills", "tags": "skills",
"certs_held": "certs", "credentials": "certs", "licenses": "certs",
// who they served
"customer": "agencies", "customer_agency": "agencies", "clients": "agencies", "agency_experience": "agencies",
// how long
"yrs": "years", "yoe": "years", "years_of_experience": "years", "total_experience": "years",
"experience_years": "years",
// clearance, self reported and triage only, never scored as held
"security_clearance": "clearance", "clearance_status": "clearance", "clearance_held": "clearance",
"active_clearance": "clearance", "eligibility": "clearance",
// identity for dedup
"external": "external_id", "source_id": "external_id", "ats_id": "external_id",
"record_id": "external_id", "candidate_number": "external_id",
}
// splitList splits a delimited cell into trimmed tokens. The standard bench format uses a semicolon or a
// comma between skills, so both are accepted; an empty cell yields no tokens.
func splitList(s string) []string {
if strings.TrimSpace(s) == "" {
return nil
}
sep := ","
if strings.Contains(s, ";") {
sep = ";"
}
return trimList(strings.Split(s, sep))
}
// trimList trims each element and drops the empties.
func trimList(xs []string) []string {
out := make([]string, 0, len(xs))
for _, x := range xs {
t := strings.TrimSpace(x)
if t != "" {
out = append(out, t)
}
}
if len(out) == 0 {
return nil
}
return out
}
func parseYears(s string) int {
s = strings.TrimSpace(s)
if s == "" {
return 0
}
n, err := strconv.Atoi(s)
if err != nil || n < 0 || n > 60 {
return 0
}
return n
}
func firstNonEmpty(xs ...string) string {
for _, x := range xs {
if strings.TrimSpace(x) != "" {
return strings.TrimSpace(x)
}
}
return ""
}
func firstNonEmptyList(a, b []string) []string {
if len(a) > 0 {
return a
}
return b
}
func firstNonZero(xs ...int) int {
for _, x := range xs {
if x != 0 {
return x
}
}
return 0
}
// hasSignal reports whether a row carries enough structured signal to be worth writing. A row with no
// skills, no clearance, no certs, and no years is dropped so an empty or header only line is not imported.
func hasSignal(r BenchRecord) bool {
return len(r.Skills) > 0 || r.ClearanceCode != "" || len(r.Certs) > 0 || r.Years > 0
}