Janady13/missionmatch / docs/plans/2026-06-30-ws7-federal-signal.md

1# WS7 Federal Signal Extraction Implementation Plan 2 3> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking. 4 5**Goal:** Tag federal opportunities, awards, requirements, and candidates with structured mission areas and incumbent staffing mix using a pure deterministic classifier, so the platform can list open requirements by mission and surface incumbent role mix on a recompete, each inference traceable to its source text. 6 7**Architecture:** A new `mission_areas` code table becomes the single FK source of truth for the 10 IC mission terms. A pure deterministic classifier (keyword gazetteer plus the WS2 alias layer plus a PSC and NAICS crosswalk, no embeddings, no plane call) tags records into multi valued tag tables with mechanism and evidence provenance. A new pure document text extractor plus a server side SAM fetch client mine solicitation attachments for incumbent labor mix. A sync command runs the classifier and writes tags and incumbent roles, audited. The AI retrieval mission filter is upgraded from WS6 text match to a structured predicate, and the Command Center gains open by mission and incumbent mix panels. The frozen deterministic engine is never touched. 8 9**Tech Stack:** Go 1.26, chi, pgx/v5, sqlc v1.31.1, goose v3.24.1, shopspring/decimal, pgvector, FIPS 140-3 (`GOFIPS140=v1.0.0`, `CGO_ENABLED=0`), pure Go PDF and DOCX and HTML text extraction (no Python). 10 11**Branch:** `ws7-federal-signal` off `ws6-retrieval`. Migrations from `0025` (head is `0024`; `0020` reserved by the unmerged per tenant branch). Validate on M4 `brain_staging` only (`joseph@100.94.8.94`), never production. DSNs come from Bubbles `/etc/missionmatch/api.env` with the database name rewritten `missionmatch` to `brain_staging`. 12 13**Hard invariants for every task:** No Python. No dashes in any written output (comments, logs, docs, commit messages) except inside URLs, CLI flags, and code identifiers. Every API key stays server only (load from `/etc/missionmatch/sam-sync.env`). Mission tags are NEVER a scoring input and never reference the eeo schema. Global reference tables are migrator owned, app SELECT only, outside RLS. Tenant tables lead their composite index with `organization_id`, FORCE RLS, and use the `NULLIF(current_setting('app.current_org_id', true),'')::uuid` policy. Every ORDER BY carries a unique id tiebreaker for byte stable output. The frozen engine under `internal/modules/matching/engine` is not modified, and the golden contract (100.00, 87.50, 26.00) stays green. 14 15--- 16 17## File Structure 18 19- Create `db/migrations/0025_mission_areas.sql`, global `mission_areas` code table plus seed. 20- Create `db/migrations/0026_mission_tags.sql`, global `federal_mission_tags` and `award_incumbent_roles`. 21- Create `db/migrations/0027_tenant_mission_tags.sql`, tenant `requirement_mission_tags` and `candidate_mission_tags` with RLS. 22- Create `internal/platform/missionclass/missionclass.go`, the pure deterministic mission and labor classifier. 23- Create `internal/platform/missionclass/missionclass_test.go`, table driven classifier tests, no DB. 24- Create `internal/platform/docextract/docextract.go`, pure Go text extraction for PDF, DOCX, HTML, plain. 25- Create `internal/platform/docextract/docextract_test.go`, fixture based extraction tests. 26- Create `internal/modules/federalsignal/` (`service.go`, `repo.go`, `fetch.go`), the classification pass and SAM attachment fetch. 27- Create `cmd/missionclass-sync/main.go`, the FIPS stamped sync binary. 28- Modify `db/queries/opportunity_records.sql`, optional missions filter. 29- Modify `db/queries/candidates.sql`, `SearchCandidatesHybridMissionTagged`. 30- Modify `internal/modules/ai/retrieval.go`, tagged mission branch in `HybridSearch`. 31- Modify `internal/modules/matching/intel.go`, call the shared labor classifier. 32- Modify `internal/modules/commandcenter/`, open by mission and incumbent mix panels. 33- Modify `docs/STACK_DECISIONS.md`, record the PDF and DOCX dependency and its FIPS posture. 34 35--- 36 37## Phase 0: Vocabulary foundation 38 39### Task 1: mission_areas code table 40 41**Files:** 42- Create: `db/migrations/0025_mission_areas.sql` 43- Test: validate up and down on `brain_staging` 44 45- [ ] **Step 1: Write the migration** 46 47```sql 48-- +goose Up 49-- mission_areas is the controlled vocabulary of IC mission areas and the single FK source of truth for 50-- every mission tag. It is curated from the mission_spine seed (migration 0021) but adds a stable short 51-- code that the tag tables and the in code vocabulary key on. Global reference data: migrator owned, app 52-- SELECT only, outside RLS, no PII and no eeo reach. 53CREATE TABLE mission_areas ( 54 code text PRIMARY KEY, 55 name text NOT NULL, 56 description text NOT NULL DEFAULT '', 57 spine_term text, -- soft link to mission_spine.term for provenance, not an FK 58 sort_order int NOT NULL DEFAULT 0, -- deterministic iteration order, mirrors ai/mission.go ordering 59 created_at timestamptz NOT NULL DEFAULT now() 60); 61 62INSERT INTO mission_areas (code, name, description, spine_term, sort_order) VALUES 63 ('SIGINT','Signals Intelligence','Intelligence derived from signals, communications, electronic, and foreign instrumentation.','SIGINT',1), 64 ('GEOINT','Geospatial Intelligence','Intelligence from the exploitation and analysis of imagery and geospatial information.','GEOINT',2), 65 ('HUMINT','Human Intelligence','Intelligence collected from human sources.','HUMINT',3), 66 ('MASINT','Measurement and Signature Intelligence','Measurement and signature intelligence.','MASINT',4), 67 ('OSINT','Open Source Intelligence','Intelligence produced from publicly available information.','OSINT',5), 68 ('SAT_C2','Satellite Command and Control','Command and control of satellite systems.','Satellite C2',6), 69 ('CDS','Cross Domain Solution','A controlled interface providing access between security domains.','Cross Domain Solution',7), 70 ('COMSEC','Communications Security','Measures to deny unauthorized access to telecommunications.','COMSEC',8), 71 ('TEMPEST','TEMPEST','Protection against compromising emanations.','TEMPEST',9), 72 ('EW','Electronic Warfare','Military action using electromagnetic and directed energy.','Electronic Warfare',10); 73 74GRANT SELECT ON mission_areas TO missionmatch_app; 75 76-- +goose Down 77DROP TABLE IF EXISTS mission_areas; 78``` 79 80- [ ] **Step 2: Apply up and down on brain_staging from M2** 81 82Run (the goose dance the prior workstreams used, migrator DSN rewritten to brain_staging): 83```bash 84cd ~/missionmatch 85eval "$(ssh user@bubbles 'echo user | sudo -S cat /etc/missionmatch/api.env 2>/dev/null' 2>/dev/null | grep '^MM_MIGRATOR_DATABASE_URL=')" 86MIG=$(printf '%s' "$MM_MIGRATOR_DATABASE_URL" | sed 's#/missionmatch?#/brain_staging?#') 87go run github.com/pressly/goose/v3/cmd/goose@v3.24.1 -dir db/migrations postgres "$MIG" up 88go run github.com/pressly/goose/v3/cmd/goose@v3.24.1 -dir db/migrations postgres "$MIG" down 89go run github.com/pressly/goose/v3/cmd/goose@v3.24.1 -dir db/migrations postgres "$MIG" up 90``` 91Expected: up creates the table and seeds 10 rows, down drops it cleanly, up re applies. 92 93- [ ] **Step 3: Regenerate sqlc and commit** 94 95```bash 96/Users/josephanady/go/bin/sqlc generate 97git add db/migrations/0025_mission_areas.sql internal/platform/database/dbgen/ 98git commit -m "feat(ws7): mission_areas code table, the single FK source of truth" 99``` 100 101### Task 2: vocabulary consistency test 102 103**Files:** 104- Create: `internal/platform/missionclass/vocab.go` (the in code mission code list) 105- Test: `internal/platform/missionclass/vocab_test.go` 106 107- [ ] **Step 1: Write the in code vocabulary as the shared source** 108 109```go 110package missionclass 111 112// MissionCode is a stable mission area code. The list mirrors the mission_areas table (migration 0025) 113// in BOTH membership and order. A consistency integration test pins this to the table so the two cannot 114// drift. ai/mission.go MissionFromQuestion keys on the same codes. 115type MissionCode = string 116 117// Codes is the ordered mission vocabulary. Order matches mission_areas.sort_order, so when a record names 118// more than one mission the earliest code wins deterministically. 119var Codes = []MissionCode{"SIGINT", "GEOINT", "HUMINT", "MASINT", "OSINT", "SAT_C2", "CDS", "COMSEC", "TEMPEST", "EW"} 120``` 121 122- [ ] **Step 2: Write the unit test (offline)** 123 124```go 125package missionclass 126 127import "testing" 128 129func TestCodesAreUniqueAndOrdered(t *testing.T) { 130 seen := map[string]bool{} 131 for _, c := range Codes { 132 if seen[c] { 133 t.Fatalf("duplicate mission code %q", c) 134 } 135 seen[c] = true 136 } 137 if len(Codes) != 10 { 138 t.Fatalf("expected 10 mission codes, got %d", len(Codes)) 139 } 140} 141``` 142 143- [ ] **Step 3: Add the integration consistency test** 144 145Add to `internal/integration/` a test (build tag `integration`) that selects `code` from `mission_areas` ordered by `sort_order` and asserts the slice equals `missionclass.Codes`. This is the drift guard. 146 147- [ ] **Step 4: Run and commit** 148 149Run: `go test ./internal/platform/missionclass/`. Expected PASS. Commit. 150 151--- 152 153## Phase 1: The deterministic classifier (pure core) 154 155### Task 3: keyword mission classifier 156 157**Files:** 158- Create: `internal/platform/missionclass/missionclass.go` 159- Test: `internal/platform/missionclass/missionclass_test.go` 160 161- [ ] **Step 1: Write the failing test** 162 163```go 164package missionclass 165 166import "testing" 167 168func TestClassifyKeyword(t *testing.T) { 169 cases := []struct { 170 text string 171 want []string // mission codes, in Codes order 172 }{ 173 {"Provide SIGINT collection and COMSEC support", []string{"SIGINT", "COMSEC"}}, 174 {"signals intelligence analysis for the customer", []string{"SIGINT"}}, 175 {"satellite command and control operations", []string{"SAT_C2"}}, 176 {"a cross domain solution accreditation effort", []string{"CDS"}}, 177 {"general administrative support services", nil}, 178 {"", nil}, 179 // bare acronym must not match inside a longer word 180 {"the cdsa framework", nil}, 181 } 182 for _, tc := range cases { 183 got := codesOf(ClassifyKeyword(tc.text)) 184 if !equal(got, tc.want) { 185 t.Fatalf("ClassifyKeyword(%q) = %v, want %v", tc.text, got, tc.want) 186 } 187 } 188} 189``` 190(Provide small `codesOf` and `equal` helpers in the test file.) 191 192- [ ] **Step 2: Run to verify it fails** 193 194Run: `go test ./internal/platform/missionclass/ -run TestClassifyKeyword`. Expected FAIL (undefined `ClassifyKeyword`). 195 196- [ ] **Step 3: Implement the keyword classifier** 197 198`Hit` carries the provenance the acceptance criterion requires. `ClassifyKeyword` runs a word boundary matcher over the lowercased text for each mission code's forms, returns one `Hit` per matched code in `Codes` order, deduped. Forms mirror `ai/mission.go` (acronym plus long phrase, whitespace tolerant). Mechanism is `keyword`, evidence is the matched phrase. 199 200```go 201package missionclass 202 203import ( 204 "regexp" 205 "sort" 206 "strings" 207) 208 209// Mechanism records HOW a mission was inferred, so every tag is traceable (the WS7 acceptance criterion). 210const ( 211 MechKeyword = "keyword" 212 MechAlias = "alias" 213 MechPSC = "psc" 214 MechNAICS = "naics" 215) 216 217// Hit is one mission inference with its provenance. 218type Hit struct { 219 Code string 220 Mechanism string 221 Evidence string // the matched phrase or code 222} 223 224type form struct { 225 code string 226 re *regexp.Regexp 227 text string // canonical evidence phrase for this form 228} 229 230func wbForm(code, phrase string) form { 231 return form{code: code, text: phrase, 232 re: regexp.MustCompile(`(?i)\b` + strings.ReplaceAll(regexp.QuoteMeta(phrase), " ", `\s+`) + `\b`)} 233} 234 235// missionForms is iterated in Codes order. Keep in sync with ai/mission.go missionVocabulary. 236var missionForms = []form{ 237 wbForm("SIGINT", "sigint"), wbForm("SIGINT", "signals intelligence"), 238 wbForm("GEOINT", "geoint"), wbForm("GEOINT", "geospatial intelligence"), 239 wbForm("HUMINT", "humint"), wbForm("HUMINT", "human intelligence"), 240 wbForm("MASINT", "masint"), wbForm("MASINT", "measurement and signature intelligence"), 241 wbForm("OSINT", "osint"), wbForm("OSINT", "open source intelligence"), 242 wbForm("SAT_C2", "satellite c2"), wbForm("SAT_C2", "satellite command and control"), 243 wbForm("CDS", "cross domain solution"), wbForm("CDS", "cross domain solutions"), 244 wbForm("COMSEC", "comsec"), wbForm("COMSEC", "communications security"), 245 wbForm("TEMPEST", "tempest"), 246 wbForm("EW", "electronic warfare"), 247} 248 249// ClassifyKeyword returns one Hit per mission code whose keyword form appears in text, in Codes order. 250func ClassifyKeyword(text string) []Hit { 251 if strings.TrimSpace(text) == "" { 252 return nil 253 } 254 found := map[string]Hit{} 255 for _, f := range missionForms { 256 if _, ok := found[f.code]; ok { 257 continue 258 } 259 if f.re.MatchString(text) { 260 found[f.code] = Hit{Code: f.code, Mechanism: MechKeyword, Evidence: f.text} 261 } 262 } 263 return ordered(found) 264} 265 266// ordered returns the hits sorted by the canonical Codes order, for byte stable output. 267func ordered(m map[string]Hit) []Hit { 268 rank := map[string]int{} 269 for i, c := range Codes { 270 rank[c] = i 271 } 272 out := make([]Hit, 0, len(m)) 273 for _, h := range m { 274 out = append(out, h) 275 } 276 sort.Slice(out, func(i, j int) bool { return rank[out[i].Code] < rank[out[j].Code] }) 277 return out 278} 279``` 280 281- [ ] **Step 4: Run to verify it passes** 282 283Run: `go test ./internal/platform/missionclass/ -run TestClassifyKeyword`. Expected PASS. 284 285- [ ] **Step 5: Commit** 286 287```bash 288git add internal/platform/missionclass/ 289git commit -m "feat(ws7): deterministic keyword mission classifier with provenance" 290``` 291 292### Task 4: alias driven mission inference 293 294**Files:** 295- Modify: `internal/platform/missionclass/missionclass.go` 296- Test: `internal/platform/missionclass/missionclass_test.go` 297 298- [ ] **Step 1: Write the failing test** 299 300The alias path resolves a mission bearing skill or alias mention to a mission code via an injected map (no DB), mirroring the WS2 resolver test pattern `newResolverFromMaps`. Example: an `AliasIndex` mapping `"signals intelligence"` and `"sigint analyst"` to code `SIGINT`. `ClassifyAlias("hiring a SIGINT Analyst", idx)` returns a `SIGINT` hit with mechanism `alias` and evidence the matched alias. 301 302```go 303func TestClassifyAlias(t *testing.T) { 304 idx := NewAliasIndex(map[string]string{ 305 "sigint analyst": "SIGINT", 306 "signals intelligence": "SIGINT", 307 "comsec custodian": "COMSEC", 308 }) 309 got := codesOf(ClassifyAlias("seeking a SIGINT Analyst and a COMSEC Custodian", idx)) 310 want := []string{"SIGINT", "COMSEC"} 311 if !equal(got, want) { 312 t.Fatalf("ClassifyAlias = %v, want %v", got, want) 313 } 314} 315``` 316 317- [ ] **Step 2: Run to verify it fails.** Expected FAIL (undefined `NewAliasIndex`, `ClassifyAlias`). 318 319- [ ] **Step 3: Implement the alias index and classifier** 320 321```go 322// AliasIndex maps a lowercased mission bearing phrase to a mission code. It is built from the curated WS2 323// alias rows and the category='mission_area' skills, so the alias layer (not a cosine threshold) carries 324// acronym and synonym knowledge. Phrases are matched on a word boundary, whitespace tolerant. 325type AliasIndex struct { 326 forms []form 327} 328 329func NewAliasIndex(phraseToCode map[string]string) AliasIndex { 330 keys := make([]string, 0, len(phraseToCode)) 331 for k := range phraseToCode { 332 keys = append(keys, k) 333 } 334 sort.Strings(keys) // deterministic build order 335 ai := AliasIndex{} 336 for _, k := range keys { 337 ai.forms = append(ai.forms, wbForm(phraseToCode[k], k)) 338 } 339 return ai 340} 341 342// ClassifyAlias returns one Hit per mission code whose alias phrase appears in text, in Codes order. 343func ClassifyAlias(text string, idx AliasIndex) []Hit { 344 if strings.TrimSpace(text) == "" { 345 return nil 346 } 347 found := map[string]Hit{} 348 for _, f := range idx.forms { 349 if _, ok := found[f.code]; ok { 350 continue 351 } 352 if f.re.MatchString(text) { 353 found[f.code] = Hit{Code: f.code, Mechanism: MechAlias, Evidence: f.text} 354 } 355 } 356 return ordered(found) 357} 358``` 359 360- [ ] **Step 4: Run to verify it passes.** Commit `feat(ws7): alias driven mission inference reusing the WS2 alias layer`. 361 362### Task 5: PSC and NAICS crosswalk 363 364**Files:** 365- Modify: `internal/platform/missionclass/missionclass.go` 366- Create: `internal/platform/missionclass/crosswalk.go` (the curated code map) 367- Test: `internal/platform/missionclass/missionclass_test.go` 368 369- [ ] **Step 1: Write the failing test** 370 371```go 372func TestClassifyCodes(t *testing.T) { 373 // AC* is national defense R&D, mapped to defense missions; D3 is IT; unknown maps to nothing. 374 if got := codesOf(ClassifyCodes("AC12", "")); !contains(got, "SIGINT") && !contains(got, "EW") { 375 // the exact mapping is curated; assert the curated expectation for the seeded code 376 } 377 if got := ClassifyCodes("ZZZZ", "999999"); len(got) != 0 { 378 t.Fatalf("unknown PSC and NAICS should yield no mission, got %v", got) 379 } 380} 381``` 382(Author the positive assertions against the curated map you seed in Step 3; keep them exact.) 383 384- [ ] **Step 2: Run to verify it fails.** 385 386- [ ] **Step 3: Implement the curated crosswalk** 387 388A small explicit map from PSC prefix and NAICS code to mission codes, conservative (only codes with a defensible mission signal). Mechanism `psc` or `naics`, evidence the code. Document that this is curated and deliberately narrow to avoid false positives, and that broad codes map to nothing. 389 390```go 391// pscMission maps a PSC code prefix to mission codes. Deliberately narrow: only codes with a defensible 392// mission signal are mapped, so a generic code produces no tag rather than a wrong one. 393var pscMission = map[string][]string{ 394 "AC": {"SIGINT", "EW"}, // R&D, National Defense (curated, conservative) 395 // extend with vetted entries only 396} 397 398// naicsMission maps a full NAICS code to mission codes (curated, conservative). 399var naicsMission = map[string][]string{ 400 "541330": {}, // Engineering Services: too broad to assign a mission, intentionally empty 401} 402 403func ClassifyCodes(psc, naics string) []Hit { 404 found := map[string]Hit{} 405 psc = strings.ToUpper(strings.TrimSpace(psc)) 406 for prefix, codes := range pscMission { 407 if strings.HasPrefix(psc, prefix) { 408 for _, c := range codes { 409 found[c] = Hit{Code: c, Mechanism: MechPSC, Evidence: psc} 410 } 411 } 412 } 413 if codes, ok := naicsMission[strings.TrimSpace(naics)]; ok { 414 for _, c := range codes { 415 found[c] = Hit{Code: c, Mechanism: MechNAICS, Evidence: naics} 416 } 417 } 418 return ordered(found) 419} 420 421// Classify unions the keyword, alias, and code mechanisms and dedupes by code, keeping the FIRST mechanism 422// seen in priority order keyword, alias, code (so the most direct evidence is recorded). Result is in 423// Codes order, byte stable. 424func Classify(text, psc, naics string, idx AliasIndex) []Hit { 425 merged := map[string]Hit{} 426 for _, h := range ClassifyKeyword(text) { 427 if _, ok := merged[h.Code]; !ok { 428 merged[h.Code] = h 429 } 430 } 431 for _, h := range ClassifyAlias(text, idx) { 432 if _, ok := merged[h.Code]; !ok { 433 merged[h.Code] = h 434 } 435 } 436 for _, h := range ClassifyCodes(psc, naics) { 437 if _, ok := merged[h.Code]; !ok { 438 merged[h.Code] = h 439 } 440 } 441 return ordered(merged) 442} 443``` 444 445- [ ] **Step 4: Test the union `Classify` too** (a record with both a keyword and a code hit dedupes to one tag, keyword mechanism wins). Run, then commit `feat(ws7): PSC and NAICS mission crosswalk and unified Classify`. 446 447### Task 6: shared labor classifier 448 449**Files:** 450- Modify: `internal/platform/missionclass/missionclass.go` (add `ClassifyLabor`) 451- Modify: `internal/modules/matching/intel.go` (call the shared classifier) 452- Test: `internal/platform/missionclass/missionclass_test.go`, `internal/modules/matching/intel_test.go` 453 454- [ ] **Step 1: Move the 8 entry `laborKeywords` (intel.go:96 to 108) into missionclass as data and write `ClassifyLabor(text string) []string`** returning the matched LCAT names in a stable order, word boundary matched. Keep the exact same words so candidate output is unchanged. 455 456- [ ] **Step 2: Write a golden test** that `ClassifyLabor` over a sample headline returns the SAME LCATs the old inline logic returned (lift representative cases from `intel_test.go`). 457 458- [ ] **Step 3: Rewire `CandidateIntelligence`** (intel.go) to call `missionclass.ClassifyLabor` instead of the inline list. Delete the inline `laborKeywords`. 459 460- [ ] **Step 4: Run the matching suite** `go test ./internal/modules/matching/...` and confirm candidate intelligence output is byte identical (the golden/regression tests pass). The FROZEN engine is untouched; only the intel helper changed. Commit `refactor(ws7): shared labor classifier used by candidates and opportunities`. 461 462--- 463 464## Phase 2: Tagging tables and queries 465 466### Task 7: global mission tag and incumbent role tables 467 468**Files:** 469- Create: `db/migrations/0026_mission_tags.sql` 470 471- [ ] **Step 1: Write the migration** 472 473```sql 474-- +goose Up 475-- federal_mission_tags tags global federal records (opportunity_records, usaspending_awards) with one or 476-- more mission areas, each carrying the mechanism and the evidence phrase so the inference is traceable. 477-- Global reference data: migrator owned writes, app SELECT only, outside RLS, no PII and no eeo reach. 478CREATE TABLE federal_mission_tags ( 479 record_table text NOT NULL CHECK (record_table IN ('opportunity_records','usaspending_awards')), 480 record_id text NOT NULL, 481 mission_code text NOT NULL REFERENCES mission_areas(code), 482 mechanism text NOT NULL, 483 evidence text NOT NULL DEFAULT '', 484 confidence numeric(3,2) NOT NULL DEFAULT 1.0, 485 classified_at timestamptz NOT NULL DEFAULT now(), 486 PRIMARY KEY (record_table, record_id, mission_code) 487); 488CREATE INDEX idx_federal_mission_tags_code ON federal_mission_tags (mission_code, record_table); 489GRANT SELECT ON federal_mission_tags TO missionmatch_app; 490 491-- award_incumbent_roles holds the incumbent labor mix mined from award and solicitation text, with the 492-- evidence phrase. Counts come only from genuine text matches, never fabricated. 493CREATE TABLE award_incumbent_roles ( 494 record_table text NOT NULL CHECK (record_table IN ('opportunity_records','usaspending_awards','sam_opportunities')), 495 record_id text NOT NULL, 496 labor_category text NOT NULL, 497 role_count int NOT NULL DEFAULT 0, 498 evidence text NOT NULL DEFAULT '', 499 classified_at timestamptz NOT NULL DEFAULT now(), 500 PRIMARY KEY (record_table, record_id, labor_category) 501); 502GRANT SELECT ON award_incumbent_roles TO missionmatch_app; 503 504-- +goose Down 505DROP TABLE IF EXISTS award_incumbent_roles; 506DROP TABLE IF EXISTS federal_mission_tags; 507``` 508 509- [ ] **Step 2: Apply up and down on brain_staging** (same goose dance as Task 1). Regenerate sqlc. Commit. 510 511### Task 8: tenant mission tag tables with RLS 512 513**Files:** 514- Create: `db/migrations/0027_tenant_mission_tags.sql` 515 516- [ ] **Step 1: Write the migration** (copy the `candidate_skills` RLS pattern from `0002_matching.sql:100 to 112` verbatim for both tables). 517 518```sql 519-- +goose Up 520CREATE TABLE requirement_mission_tags ( 521 organization_id uuid NOT NULL, 522 requirement_id uuid NOT NULL, 523 mission_code text NOT NULL REFERENCES mission_areas(code), 524 mechanism text NOT NULL, 525 evidence text NOT NULL DEFAULT '', 526 PRIMARY KEY (requirement_id, mission_code), 527 FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id) ON DELETE CASCADE 528); 529CREATE INDEX idx_requirement_mission_tags_org ON requirement_mission_tags (organization_id, requirement_id); 530ALTER TABLE requirement_mission_tags ENABLE ROW LEVEL SECURITY; 531ALTER TABLE requirement_mission_tags FORCE ROW LEVEL SECURITY; 532CREATE POLICY requirement_mission_tags_isolation ON requirement_mission_tags 533 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 534 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 535GRANT SELECT, INSERT, UPDATE, DELETE ON requirement_mission_tags TO missionmatch_app; 536 537CREATE TABLE candidate_mission_tags ( 538 organization_id uuid NOT NULL, 539 candidate_id uuid NOT NULL, 540 mission_code text NOT NULL REFERENCES mission_areas(code), 541 mechanism text NOT NULL, 542 evidence text NOT NULL DEFAULT '', 543 PRIMARY KEY (candidate_id, mission_code), 544 FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id) ON DELETE CASCADE 545); 546CREATE INDEX idx_candidate_mission_tags_org ON candidate_mission_tags (organization_id, candidate_id); 547ALTER TABLE candidate_mission_tags ENABLE ROW LEVEL SECURITY; 548ALTER TABLE candidate_mission_tags FORCE ROW LEVEL SECURITY; 549CREATE POLICY candidate_mission_tags_isolation ON candidate_mission_tags 550 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 551 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 552GRANT SELECT, INSERT, UPDATE, DELETE ON candidate_mission_tags TO missionmatch_app; 553 554-- +goose Down 555DROP TABLE IF EXISTS candidate_mission_tags; 556DROP TABLE IF EXISTS requirement_mission_tags; 557``` 558 559- [ ] **Step 2: Apply up and down on brain_staging.** Regenerate sqlc. Commit. 560 561### Task 9: sqlc queries for tags 562 563**Files:** 564- Create: `db/queries/mission_tags.sql` 565- Modify: generated `dbgen` 566 567- [ ] **Step 1: Author the queries** (named args): `UpsertFederalMissionTag`, `UpsertAwardIncumbentRole`, `UpsertCandidateMissionTag`, `UpsertRequirementMissionTag`, `ListMissionTagsForRecord`, `ListCandidatesByMission` (RLS scoped). Every list query ends `ORDER BY ... , id` for byte stability. Use `ON CONFLICT ... DO UPDATE` so the sync pass is idempotent. 568 569- [ ] **Step 2: Regenerate sqlc, build, and write a small integration test** proving a tenant tag round trips and cannot be read cross tenant (copy the existing RLS isolation test shape). Run on brain_staging. Commit. 570 571--- 572 573## Phase 3: Document extraction and SAM fetch (attachment mining) 574 575### Task 10: pure Go document text extractor 576 577**Files:** 578- Create: `internal/platform/docextract/docextract.go` 579- Test: `internal/platform/docextract/docextract_test.go` 580- Modify: `docs/STACK_DECISIONS.md` (record the dependency and FIPS posture) 581 582- [ ] **Step 1: Decide the PDF dependency against STACK_DECISIONS.** Use a pure Go PDF text extractor that builds under `CGO_ENABLED=0` (candidates: `github.com/ledongthuc/pdf`, `rsc.io/pdf`). Confirm it is CGO free (`go list -deps` shows no `C` package) and record it in `docs/STACK_DECISIONS.md` the way excelize is recorded (a dead path note if it drags any md4 or ripemd160; it must stay out of `cmd/api`). DOCX and HTML use the standard library only. 583 584- [ ] **Step 2: Write the failing test** with three tiny fixture files committed under `testdata/`: a one page PDF containing the text `SIGINT Analyst x3`, a DOCX containing `ISSO x2`, and an HTML file containing `<p>COMSEC Custodian</p>`. Assert `ExtractText` returns text containing those phrases. 585 586```go 587func TestExtractText(t *testing.T) { 588 for _, tc := range []struct{ file, want string }{ 589 {"testdata/sample.pdf", "SIGINT Analyst"}, 590 {"testdata/sample.docx", "ISSO"}, 591 {"testdata/sample.html", "COMSEC Custodian"}, 592 {"testdata/sample.txt", "Cyber Engineer"}, 593 } { 594 b, _ := os.ReadFile(tc.file) 595 got, err := ExtractText(context.Background(), tc.file, b) 596 if err != nil { 597 t.Fatalf("ExtractText(%s): %v", tc.file, err) 598 } 599 if !strings.Contains(got, tc.want) { 600 t.Fatalf("ExtractText(%s) missing %q in %q", tc.file, tc.want, got) 601 } 602 } 603} 604``` 605 606- [ ] **Step 3: Implement `ExtractText(ctx, filename string, data []byte) (string, error)`** dispatching on the lowercased extension: `.pdf` via the pure Go PDF reader, `.docx` via `archive/zip` plus `encoding/xml` over `word/document.xml`, `.htm`/`.html` via tag stripping (`golang.org/x/net/html` tokenizer or a conservative stripper), `.txt` and unknown via raw bytes. Cap the output length (for example 1 MB of text) and return a typed error on an unsupported or corrupt file. No network, no Python, pure Go. 607 608- [ ] **Step 4: Run** `CGO_ENABLED=0 GOFIPS140=v1.0.0 go test ./internal/platform/docextract/`. Expected PASS. Commit `feat(ws7): pure Go PDF DOCX HTML text extractor`. 609 610### Task 11: SAM attachment fetch client 611 612**Files:** 613- Create: `internal/modules/federalsignal/fetch.go` 614- Test: `internal/modules/federalsignal/fetch_test.go` 615 616- [ ] **Step 1: Write the failing test** against an `httptest` server: the client fetches a `description_url` (append the api_key) and a `resource_links` entry, runs the bytes through `docextract.ExtractText`, and returns the concatenated text. The api_key is read from config, never logged, never in the URL it returns. Use a fake key in the test. 617 618- [ ] **Step 2: Implement the fetch client** reusing the sam-sync HTTP client style (timeout, bounded retries). Signature: `FetchSolicitationText(ctx, descURL string, resourceLinks []string) (string, error)`. It appends `&api_key=...` to the SAM URLs (the key from `config`), downloads each, caps total bytes, extracts text via `docextract`, and concatenates with a separator. On a per attachment error it logs a metric and continues (best effort), never failing the whole record. It optionally stores the raw bytes via `storage.Storer.Put` under a deterministic key for retention. 619 620- [ ] **Step 3: Run, then commit** `feat(ws7): server side SAM attachment fetch and text extraction`. 621 622--- 623 624## Phase 4: The classification sync pass 625 626### Task 12: missionclass-sync command 627 628**Files:** 629- Create: `internal/modules/federalsignal/service.go`, `internal/modules/federalsignal/repo.go` 630- Create: `cmd/missionclass-sync/main.go` 631- Test: `internal/modules/federalsignal/service_test.go` (offline, fixture rows) 632 633- [ ] **Step 1: Write the failing service test (offline)** with in memory fixture records: given a few `opportunity_records` and `usaspending_awards` rows (title plus description text, psc, naics) and a stub fetch client returning fixed attachment text, the service produces the expected `federal_mission_tags` and `award_incumbent_roles` with evidence. No DB, no network: inject the classifier, an `AliasIndex` built from a fixture map, and a fake fetch client and repo. 634 635- [ ] **Step 2: Implement the service `ClassifyAll(ctx)`** that builds the `AliasIndex` once from the curated alias and mission_area skill rows (a repo read), iterates the global records, runs `missionclass.Classify` over the record text plus, for SAM backed solicitations, the fetched attachment text, runs the labor classifier for incumbent roles, and upserts tags and roles idempotently. It writes ONE append only audit event per run (action `aedt.federalsignal.classified`, counts only, no PII), mirroring the oppintel sync audit shape. 636 637- [ ] **Step 3: Implement `cmd/missionclass-sync/main.go`** as a FIPS stamped binary (the in build stamp gate like the other sync commands), wiring config, db (migrator role), the inference free classifier, the SAM fetch client, and the storage port. It is a cron candidate like `mm-opp-sync`. 638 639- [ ] **Step 4: Build with the FIPS flags, run the offline service test.** Commit `feat(ws7): missionclass-sync classification and incumbent mix pass`. 640 641- [ ] **Step 5 (needs federal data, staging gate):** Seed `usaspending_awards` and `sam_opportunities` into `brain_staging` by running `cmd/awards-sync` and `cmd/sam-sync` against the staging DSN (the staging award feed is currently empty). Then run `missionclass-sync` against staging and confirm tags and incumbent roles are written with evidence. Capture counts. 642 643--- 644 645## Phase 5: Retrieval upgrade (additive, default unchanged) 646 647### Task 13: structured mission query 648 649**Files:** 650- Modify: `db/queries/candidates.sql` 651 652- [ ] **Step 1: Author `SearchCandidatesHybridMissionTagged`** identical to `SearchCandidatesHybridMission` but the mission constraint on BOTH arms is `EXISTS (SELECT 1 FROM candidate_mission_tags t WHERE t.candidate_id = candidates.id AND t.mission_code = sqlc.arg(mission_code))` instead of the `search_tsv` text match. Keep every ORDER BY id tiebreaker. Regenerate sqlc. 653 654- [ ] **Step 2: Write a live integration test** seeding tagged and untagged candidates and asserting only the tagged candidates return, RLS scoped, byte stable. Run on brain_staging. Commit. 655 656### Task 14: HybridSearch tagged branch 657 658**Files:** 659- Modify: `internal/modules/ai/retrieval.go` 660- Test: `internal/modules/ai/` and integration 661 662- [ ] **Step 1: Add a tagged branch to `HybridSearch`** preferred when the candidate has structured tags available: when `mission != ""`, call `SearchCandidatesHybridMissionTagged` with the mission CODE (map the canonical keyword from `MissionFromQuestion` to the code). Keep the WS6 text query as a documented fallback only if no tags exist yet (a config or a simple has tags probe), so the default unscoped path stays byte identical and EEO and RLS guards remain on the path. Update `docs/AI_RESEARCH.md` to note the WS6 to WS7 upgrade (text filter to structured predicate). 663 664- [ ] **Step 2: Test** the tagged branch returns only tagged candidates, the unnamed mission path is unchanged, and `ForbidEEO` still fires. Run unit plus integration on brain_staging. Commit. 665 666--- 667 668## Phase 6: Command Center surfacing 669 670### Task 15: list opportunities by mission 671 672**Files:** 673- Modify: `db/queries/opportunity_records.sql` 674 675- [ ] **Step 1: Add an optional `missions::text[]` narg** to `ListOpportunityRecords` (the query already takes optional array nargs with a NULL means no filter default, `opportunity_records.sql:56 to 66`): `AND (sqlc.narg('missions')::text[] IS NULL OR EXISTS (SELECT 1 FROM federal_mission_tags t WHERE t.record_table = 'opportunity_records' AND t.record_id = opportunity_records.id::text AND t.mission_code = ANY(sqlc.narg('missions'))))`. Default NULL keeps current behavior. Regenerate sqlc. 676 677- [ ] **Step 2: Test** the default path is unchanged and the mission filtered path returns only tagged records. Run on brain_staging. Commit. 678 679### Task 16: Command Center mission and incumbent panels 680 681**Files:** 682- Modify: `internal/modules/commandcenter/service.go`, `dto.go`, `handler.go` 683 684- [ ] **Step 1: Add an open requirements by mission view and an incumbent staffing mix view.** Open by mission lists active solicitations filtered by mission code. Incumbent mix joins `award_incumbent_roles` to the recompete signal (`usaspending_awards.pop_end_date`) and shows the role counts with their evidence phrase (the acceptance criterion: each inference traceable to its source text). These are additive panels; the existing readiness math is unchanged. 685 686- [ ] **Step 2: Wire the DTO and handler**, keep it read only and RLS scoped, label incumbent data with its provenance. AEDT and EEO untouched (this reads public federal data, never the eeo schema, never a scoring input). 687 688- [ ] **Step 3 (needs federal data):** Integration test on brain_staging after the Task 12 staging seed, proving the panels populate and each row carries evidence. Commit. 689 690--- 691 692## Phase 7: Close out 693 694### Task 17: review, validate, and prepare the merge 695 696- [ ] **Step 1: Dispatch the final adversarial review** over the whole WS7 diff (AEDT determinism, EEO isolation, RLS, no Python, no dashes, both cloud, the polymorphic FK tradeoff, the document extractor FIPS posture). Fix findings. 697- [ ] **Step 2: Confirm the frozen engine is untouched** and the golden and frozen tests are green; stamp verify the new binaries. 698- [ ] **Step 3: Run the full integration suite on brain_staging** (after the award and SAM seed) and capture the result. The pre existing `usaspending_awards=0` gap is closed by the Task 12 seed. 699- [ ] **Step 4: Update the memory file** and `docs/AI_GROUNDING_PROGRAM.md` WS7 status. Do not merge to master or deploy to production without explicit instruction; the whole grounding stack merges together. 700 701--- 702 703## Self review notes 704 705- Spec coverage: every WS7 deliverable D1 through D7 maps to a task (D1 Task 1, D2 Tasks 7 and 8, D3 Tasks 3 to 5, D4 Tasks 10 to 12, D5 Task 6, D6 Tasks 15 and 16, D7 Tasks 13 and 14). 706- The frozen engine is never in a modify list. The only `internal/modules/matching` change is the intel labor helper (Task 6), guarded by a byte identical regression test. 707- Two tasks need live federal data (Task 12 step 5, Task 16 step 3); both are gated on seeding `brain_staging`, which the plan calls out explicitly. Everything else is offline unit testable. 708- Migration order: 0025 code table before 0026 and 0027 tag tables (FK dependency), tenant tables after the global ones. 709