Janady13/missionmatch / cmd/promote-ontology/main.go

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / cmd/promote-ontology/main.go
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	cmd/promote-ontology/main.go
Original SHA-256	72ad64b34abd2c1cafb725707b7f8120d104a0155f6123adad73eb946539639a
Git blob SHA	aa686d84bb398a39e18d92d4af6d7da8a2a55ca3
Reading-copy SHA-256	72ad64b34abd2c1cafb725707b7f8120d104a0155f6123adad73eb946539639a

Original source ↗

Recovered source transcript

Download complete source text ↓117 lines · 4.6 KB

// Command promote-ontology promotes curated, mined, and O*NET technology terms into the global
// skills table. It runs three idempotent INSERT ... ON CONFLICT DO NOTHING passes as the migrator
// role and writes one AEDT audit row recording the counts. It carries NO org context because skills
// is a global reference table outside Row Level Security. Safe to re-run at any time.
//
// Sources promoted:
//   - db/seed/cleared_skills.sql curated cleared workforce terms (source='curated') -- apply that
//     seed separately via psql; this command handles the ontology_terms and mission_spine paths.
//   - ontology_terms rows where term_kind='skill' and source_name='Corpus Mining'.
//   - mission_spine rows for version='v1', promoted as mission_area skills.
//   - ontology_terms rows sourced from O*NET Web Services technology tools filtered to cleared
//     relevant tech categories (development, operating system, network, security, database, etc.).
//
// Usage:
//
//	promote-ontology [-dsn $MM_MIGRATOR_DATABASE_URL]
package main

import (
	"context"
	"encoding/json"
	"flag"
	"log"
	"os"

	"github.com/jackc/pgx/v5/pgxpool"
)

func main() {
	dsn := flag.String("dsn", "", "migrator DSN, defaults to MM_MIGRATOR_DATABASE_URL")
	flag.Parse()
	if *dsn == "" {
		*dsn = os.Getenv("MM_MIGRATOR_DATABASE_URL")
	}

	if *dsn == "" {
		log.Fatal("promote-ontology: -dsn or MM_MIGRATOR_DATABASE_URL is required")
	}

	ctx := context.Background()
	pool, err := pgxpool.New(ctx, *dsn)
	if err != nil {
		log.Fatalf("promote-ontology: db connect: %v", err)
	}
	defer pool.Close()

	var nMined, nSpine, nOnet int64

	// Pass 1: corpus mined skills from ontology_terms.
	tag, err := pool.Exec(ctx, `
INSERT INTO skills (name, category, source)
SELECT DISTINCT ot.term, COALESCE(NULLIF(ot.category,''),'mined'), ot.source_name
FROM ontology_terms ot
WHERE ot.term_kind = 'skill'
  AND ot.source_name = 'Corpus Mining'
  AND NOT EXISTS (SELECT 1 FROM skills s WHERE lower(s.name) = lower(ot.term))
ON CONFLICT (name) DO NOTHING`)
	if err != nil {
		log.Fatalf("promote-ontology: corpus mined insert: %v", err)
	}
	nMined = tag.RowsAffected()
	log.Printf("promote-ontology: corpus mined skills inserted: %d", nMined)

	// Pass 2: mission_spine IC terms for version v1, promoted as mission_area skills.
	tag, err = pool.Exec(ctx, `
INSERT INTO skills (name, category, source)
SELECT ms.term, 'mission_area', 'mission_spine'
FROM mission_spine ms
WHERE ms.version = 'v1'
  AND NOT EXISTS (SELECT 1 FROM skills s WHERE lower(s.name) = lower(ms.term))
ON CONFLICT (name) DO NOTHING`)
	if err != nil {
		log.Fatalf("promote-ontology: mission spine insert: %v", err)
	}
	nSpine = tag.RowsAffected()
	log.Printf("promote-ontology: mission spine terms inserted: %d", nSpine)

	// Pass 3: O*NET technology tools filtered to cleared tech relevant categories.
	// The regex matches category values that indicate development, OS, networking, security,
	// database, programming, configuration, virtualization, cloud, analytical, or data management
	// tool types. The soc_code column on skills stores the O*NET code for later crosswalk use.
	tag, err = pool.Exec(ctx, `
INSERT INTO skills (name, category, source, soc_code)
SELECT DISTINCT ot.term, COALESCE(NULLIF(ot.category,''),'tool'), 'onet', ot.code
FROM ontology_terms ot
WHERE ot.source_name = 'O*NET Web Services'
  AND ot.source_record_id LIKE 'onet-tech:%'
  AND lower(ot.category) ~ 'development environment|web platform development|operating system|object or component|network monitoring|program testing|transaction security|data base management system|network security|virtual private network|cloud-based|configuration management|virtualiz'
  AND NOT EXISTS (SELECT 1 FROM skills s WHERE lower(s.name) = lower(ot.term))
ON CONFLICT (name) DO NOTHING`)
	if err != nil {
		log.Fatalf("promote-ontology: onet tech insert: %v", err)
	}
	nOnet = tag.RowsAffected()
	log.Printf("promote-ontology: onet tech tools inserted: %d", nOnet)

	total := nMined + nSpine + nOnet
	log.Printf("promote-ontology: total skills inserted across all sources: %d", total)

	// Write one AEDT audit row. No PII: skills are global reference data, not candidate or org data.
	payload, _ := json.Marshal(map[string]any{
		"corpus_mined":  nMined,
		"mission_spine": nSpine,
		"onet_tech":     nOnet,
		"total":         total,
	})
	_, err = pool.Exec(ctx,
		`INSERT INTO audit_logs (organization_id, action, entity_type, new_values)
		 VALUES (NULL, 'aedt.ontology.promoted', 'skills', $1)`,
		payload)
	if err != nil {
		log.Printf("promote-ontology: audit write warning (non-fatal): %v", err)
	}

	log.Printf("promote-ontology: DONE (mined=%d spine=%d onet=%d total=%d)", nMined, nSpine, nOnet, total)
}