Janady13/missionmatch / internal/integration/report_integration_test.go

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / internal/integration/report_integration_test.go
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	internal/integration/report_integration_test.go
Original SHA-256	5e669ac85f3b7191b4b5075d13f0017b806850f420a906d65b3999223274f95e
Git blob SHA	830c4b20baa91dfb420086f7d911e61a468ed5b0
Reading-copy SHA-256	5e669ac85f3b7191b4b5075d13f0017b806850f420a906d65b3999223274f95e

Original source ↗

Every recorded occurrence

These 2 records contain identical original source bytes. Their paths and revisions remain part of the archive.

Original record	Branch or archive	Revision / identifier	Original location
Janady13/missionmatch / internal/integration/report_integration_test.go	feature/opportunity-to-outcome	720d47fae476b0d38477ae4401697d372b1ea2bc	Open original
Janady13/missionmatch / internal/integration/report_integration_test.go	feature/aioe-upgrade	2178e7298d0e5d9af83afa12402fd587f0ba6ed8	Open original

Recovered source transcript

Download complete source text ↓257 lines · 9.1 KB

//go:build integration

package integration

import (
	"context"
	"strings"
	"testing"
	"time"

	"github.com/google/uuid"

	"github.com/missionworkforce/missionmatch/internal/modules/recruiting"
)

// WARNING. Point MM_APP_DSN and MM_MIGRATOR_DSN at a scratch or staging database, never at production.

// TestExportNeutralisesFormulaInjectionEndToEnd. A candidate name comes from a parsed resume, so it is
// attacker controlled, and the exported file is emailed to a client who opens it in a spreadsheet.
func TestExportNeutralisesFormulaInjectionEndToEnd(t *testing.T) {
	org := seedOrg(t)
	_, ctx := seedRecruiter(t, org, "Export Recruiter")
	svc := reqSvc()

	// A candidate whose name is a payload, exactly as a malicious resume would supply it.
	var candID uuid.UUID
	if err := migrator.QueryRow(context.Background(),
		`INSERT INTO candidates (organization_id, full_name, clearance_level, clearance_poly, clearance_status)
		 VALUES ($1, $2, 'none','none','unknown') RETURNING id`,
		org, `=cmd|'/c calc.exe'!A1`).Scan(&candID); err != nil {
		t.Fatalf("seed candidate: %v", err)
	}
	r, err := svc.CreateRequisition(ctx, recruiting.CreateRequisitionInput{Title: "Export role", Openings: 1})
	if err != nil {
		t.Fatalf("requisition: %v", err)
	}
	if _, err := svc.Submit(ctx, uuid.MustParse(r.ID), recruiting.CreateApplicationInput{
		CandidateID: candID.String(), SourceTouch: "referral"}); err != nil {
		t.Fatalf("submit: %v", err)
	}

	body, filename, err := svc.Export(ctx, recruiting.ExportPipeline, recruiting.PeriodMonth, "")
	if err != nil {
		t.Fatalf("export: %v", err)
	}
	if !strings.Contains(body, `'=cmd`) {
		t.Errorf("the payload was not neutralised. Export was:\n%s", body)
	}
	// The dangerous form must not appear at the start of any cell.
	for _, line := range strings.Split(body, "\n") {
		for _, cell := range strings.Split(line, ",") {
			c := strings.TrimPrefix(cell, `"`)
			if strings.HasPrefix(c, "=") || strings.HasPrefix(c, "@") {
				t.Errorf("a live formula reached the file: %q", cell)
			}
		}
	}
	if !strings.HasSuffix(filename, ".csv") {
		t.Errorf("filename = %q", filename)
	}
}

// TestExportIsAudited. An export is the moment tenant data leaves the system, which is exactly the event
// an incident review asks about and the one nothing else records.
func TestExportIsAudited(t *testing.T) {
	org := seedOrg(t)
	_, ctx := seedRecruiter(t, org, "Audited Exporter")
	svc := reqSvc()
	if _, err := svc.CreateRequisition(ctx, recruiting.CreateRequisitionInput{Title: "Audited"}); err != nil {
		t.Fatalf("requisition: %v", err)
	}

	before := countExports(t, org)
	if _, _, err := svc.Export(ctx, recruiting.ExportPeriods, recruiting.PeriodMonth, ""); err != nil {
		t.Fatalf("export: %v", err)
	}
	after := countExports(t, org)
	if after != before+1 {
		t.Fatalf("export audit rows went from %d to %d, want exactly one more", before, after)
	}

	// The row must say what left and how much, not merely that something happened.
	var payload string
	if err := migrator.QueryRow(context.Background(),
		`SELECT new_values::text FROM audit_logs
		  WHERE organization_id = $1 AND action = 'export.run'
		  ORDER BY created_at DESC LIMIT 1`, org).Scan(&payload); err != nil {
		t.Fatalf("read audit row: %v", err)
	}
	for _, want := range []string{"periods", "rows", "bytes", "filename"} {
		if !strings.Contains(payload, want) {
			t.Errorf("the export audit row does not record %q: %s", want, payload)
		}
	}
}

func countExports(t *testing.T, org uuid.UUID) int {
	t.Helper()
	var n int
	if err := migrator.QueryRow(context.Background(),
		`SELECT count(*) FROM audit_logs WHERE organization_id = $1 AND action = 'export.run'`, org).Scan(&n); err != nil {
		t.Fatalf("count exports: %v", err)
	}
	return n
}

// TestReportBucketsInTheOrganizationDay. An evening event is the next day in UTC, so a report bucketed
// without the timezone puts it in the wrong period.
func TestReportBucketsInTheOrganizationDay(t *testing.T) {
	org := seedOrg(t)
	_, ctx := seedRoleUser(t, org, "Report Owner", "owner")
	svc := reqSvc()
	if _, err := svc.SetTimeZone(ctx, "America/Chicago"); err != nil {
		t.Fatalf("set tz: %v", err)
	}
	if _, err := svc.CreateRequisition(ctx, recruiting.CreateRequisitionInput{Title: "Bucketed"}); err != nil {
		t.Fatalf("requisition: %v", err)
	}

	v, err := svc.Report(ctx, recruiting.PeriodDay, time.Now().UTC().AddDate(0, 0, -7).Format(time.RFC3339))
	if err != nil {
		t.Fatalf("report: %v", err)
	}
	if v.TimeZone != "America/Chicago" {
		t.Errorf("report timezone = %q", v.TimeZone)
	}
	if v.Totals.Opened != 1 {
		t.Errorf("totals opened = %d, want 1", v.Totals.Opened)
	}
	// The totals must equal the sum of the rows, or the header contradicts the table under it.
	sum := 0
	for _, r := range v.Rows {
		sum += r.Opened
	}
	if sum != v.Totals.Opened {
		t.Errorf("rows sum to %d but totals say %d", sum, v.Totals.Opened)
	}
}

// TestEveryPeriodGrainWorks, including the federal fiscal quarter, which is the one that matters for a
// government contracting customer and the one a generic tool gets wrong.
func TestEveryPeriodGrainWorks(t *testing.T) {
	org := seedOrg(t)
	_, ctx := seedRecruiter(t, org, "Grain Owner")
	svc := reqSvc()
	for i := 0; i < 3; i++ {
		if _, err := svc.CreateRequisition(ctx, recruiting.CreateRequisitionInput{Title: "Grain"}); err != nil {
			t.Fatalf("requisition: %v", err)
		}
	}
	since := time.Now().UTC().AddDate(-1, 0, 0).Format(time.RFC3339)

	for _, p := range []recruiting.Period{
		recruiting.PeriodDay, recruiting.PeriodWeek, recruiting.PeriodMonth,
		recruiting.PeriodQuarter, recruiting.PeriodFiscalQuarter,
	} {
		v, err := svc.Report(ctx, p, since)
		if err != nil {
			t.Fatalf("report at %s: %v", p, err)
		}
		if v.Totals.Opened != 3 {
			t.Errorf("grain %s totals %d opened, want 3", p, v.Totals.Opened)
		}
		for _, r := range v.Rows {
			if r.Label == "" || r.Start == "" {
				t.Errorf("grain %s produced an unlabelled row: %+v", p, r)
			}
		}
	}
	// The fiscal label must name a fiscal year, which is how it differs from a calendar quarter.
	fq, err := svc.Report(ctx, recruiting.PeriodFiscalQuarter, since)
	if err != nil {
		t.Fatalf("fiscal report: %v", err)
	}
	if len(fq.Rows) == 0 || !strings.HasPrefix(fq.Rows[0].Label, "FY") {
		t.Errorf("fiscal quarter label = %q, want an FY prefix", fq.Rows[0].Label)
	}
	// An unknown grain is refused rather than silently falling back to days.
	if _, err := svc.Report(ctx, recruiting.Period("fortnight"), since); err == nil {
		t.Error("an unknown period grain was accepted")
	}
	if _, _, err := svc.Export(ctx, recruiting.ExportKind("everything"), recruiting.PeriodDay, ""); err == nil {
		t.Error("an unknown export kind was accepted")
	}
}

// TestEveryExportKindProducesAFile.
func TestEveryExportKindProducesAFile(t *testing.T) {
	org := seedOrg(t)
	_, ctx := seedRecruiter(t, org, "All Exports")
	svc := reqSvc()
	r, _ := svc.CreateRequisition(ctx, recruiting.CreateRequisitionInput{Title: "Exportable", Openings: 1})
	cand := seedPipelineCandidate(t, org, "Export Cand")
	a, err := svc.Submit(ctx, uuid.MustParse(r.ID), recruiting.CreateApplicationInput{CandidateID: cand.String()})
	if err != nil {
		t.Fatalf("submit: %v", err)
	}
	if _, err := svc.CreatePlacement(ctx, uuid.MustParse(a.ID), recruiting.CreatePlacementInput{
		EmploymentType: "contract", StartDate: time.Now().UTC().Format("2006-01-02"),
		BillRate: "150", PayRate: "100"}); err != nil {
		t.Fatalf("place: %v", err)
	}
	if _, err := svc.LogActivity(ctx, recruiting.LogActivityInput{
		Kind: "call", Outcome: "connected", CandidateID: cand.String(), Body: "Spoke"}); err != nil {
		t.Fatalf("log: %v", err)
	}

	for _, k := range []recruiting.ExportKind{
		recruiting.ExportPeriods, recruiting.ExportPlacements,
		recruiting.ExportPipeline, recruiting.ExportActivity,
	} {
		body, name, err := svc.Export(ctx, k, recruiting.PeriodMonth, "")
		if err != nil {
			t.Fatalf("export %s: %v", k, err)
		}
		if !strings.Contains(body, "\n") || len(body) < 20 {
			t.Errorf("export %s produced %d bytes: %q", k, len(body), body)
		}
		if !strings.HasSuffix(name, ".csv") {
			t.Errorf("export %s filename = %q", k, name)
		}
		// A negative margin or rate must survive the safety guard untouched.
		if strings.Contains(body, "'-") {
			t.Errorf("export %s escaped a negative number: %s", k, body)
		}
	}
}

// TestExportTenantIsolation, with a control.
func TestExportTenantIsolation(t *testing.T) {
	orgA := seedOrg(t)
	_, ctxA := seedRecruiter(t, orgA, "Tenant A")
	svc := reqSvc()
	if _, err := svc.CreateRequisition(ctxA, recruiting.CreateRequisitionInput{
		Title: "Tenant A confidential role"}); err != nil {
		t.Fatalf("requisition in A: %v", err)
	}
	orgB := seedOrg(t)
	_, ctxB := seedRecruiter(t, orgB, "Tenant B")

	bodyB, _, err := svc.Export(ctxB, recruiting.ExportPipeline, recruiting.PeriodMonth, "")
	if err != nil {
		t.Fatalf("export in B: %v", err)
	}
	if strings.Contains(bodyB, "Tenant A confidential role") {
		t.Fatal("tenant B exported tenant A's data")
	}
	// Control: A's own export reaches the row count it should.
	pv, err := svc.Report(ctxA, recruiting.PeriodMonth, time.Now().UTC().AddDate(0, 0, -30).Format(time.RFC3339))
	if err != nil {
		t.Fatalf("report in A: %v", err)
	}
	if pv.Totals.Opened != 1 {
		t.Errorf("tenant A's own report shows %d opened, want 1", pv.Totals.Opened)
	}
}