[Bug] E2E seed.sql silently fails to insert test_cases/links — Traceability Matrix shows 0% coverage, TC-004 missing

[bg-playground]

Summary

The E2E test environment is starting up with only requirements seeded — the 5 seeded test_cases (TC-001..TC-005) and their 3 requirement_test_case_links are missing from the database by the time the Traceability Matrix tests run. This causes the long-running flake on traceability.spec.ts:37 (seeded test case "TC-004" is visible in the matrix) to fail deterministically, not intermittently.

Reproduces consistently on PR #310 (both initial run and re-run) and the bug is not in #310's diff — #310 only adds the external_case_artifacts table and artifact upload endpoint, neither of which touches test_cases, requirement_test_case_links, seed loading, or the frontend. The same failure would reproduce against main.

Supersedes the pollution-theory hypothesis from #308 — that fix landed but did not address the actual root cause.

Evidence

Screenshot of the Traceability Matrix page from the failing test attachment (error-context.md on the TC-004 retries):

Metric	Observed	Expected (from seed.sql)
Coverage Percentage	0.0%	~60% (3 of 5 reqs linked)
Covered Requirements	0 / 8	3 / 5
Uncovered Requirements	8	2
Orphan Test Cases	2 (only E2E Test Case + E2E Edit TC Target ... (edited))	should also include TC-003, TC-005 (unlinked seeded cases)
Seeded TC-001..TC-005 visible?	No — none of them present	All 5 should appear
Requirements present?	✅ All 5 seeded requirements visible (User Authentication, Dashboard Overview, Export Traceability Matrix, Role-Based Access Control, Performance Under Load) plus 3 from CRUD tests	—

Key observation: requirement seeding works; test-case seeding does not. Both INSERTs live in the same seed.sql file, which strongly suggests the INSERT INTO test_cases block is the failure point — and if it aborts mid-file, the subsequent INSERT INTO requirement_test_case_links block is also skipped, which exactly matches what we see (0 links).

Failing test:

1) [chromium] › tests/e2e/traceability.spec.ts:37:3 › Traceability Matrix › seeded test case "TC-004" is visible in the matrix

   Error: expect(locator).toBeVisible() failed
   Locator: getByText(/TC-004/i)
   Expected: visible
   Timeout: 10000ms
   Error: element(s) not found

Relevant fixture: frontend/tests/e2e/fixtures/seed.sql (specifically the INSERT INTO test_cases block starting around line 63 and the links block around line 91).

Three hypotheses for triage

Hypothesis 1 — Schema mismatch in seed.sql (most likely)

The INSERT INTO test_cases block in seed.sql omits an external_id column, but the actual test_cases schema may require it (NOT NULL, unique, or referenced by the matrix UI as the visible ID). If the insert raises (NOT NULL violation, missing column, etc.), the SQL block aborts mid-file, skipping both the rest of the test_cases inserts and the subsequent requirement_test_case_links block.

This matches every observable: requirements (inserted first) succeed, test_cases (inserted second) silently fail, links (inserted third) never run.

Diagnostic step: Inspect the bgstm-test-db container logs from a failing CI run for any INSERT INTO test_cases ... ERROR. Or run psql -f frontend/tests/e2e/fixtures/seed.sql against a fresh test DB locally and look at exit code + stderr.

Hypothesis 2 — sample_data.py runs after seed.sql and overwrites with a different dataset, then something else clears the resulting rows

backend/app/db/sample_data.py defines its own TC-001..TC-004 with different UUIDs. If it runs as part of container startup after seed.sql, the ON CONFLICT (id) DO NOTHING in seed.sql is irrelevant because the IDs are different. If a later step (test fixture, migration, beforeAll) then truncates test_cases, all rows are wiped while the requirements (which sample_data.py may not insert) survive.

Diagnostic step: Check the docker-compose / backend startup sequence to confirm whether sample_data.py is invoked, and in what order relative to seed.sql.

Hypothesis 3 — A spec file's beforeAll truncates test_cases

A test in another spec (e.g., test_cases CRUD) may truncate the table to assert empty-state behavior, leaving the requirements untouched but the test_cases gone. This would also explain why the orphan list contains only the 2 CRUD-created E2E test cases — they were created after the truncation.

Diagnostic step: git grep -n -E "TRUNCATE|DELETE FROM test_cases" frontend/tests and the backend test fixtures.

Suggested investigation order

1. DB container logs — fastest path to confirming or eliminating Hypothesis 1.
2. Run seed.sql locally against a fresh test DB and check stderr.
3. If 1 and 2 are clean: trace startup order (Hypothesis 2) and grep for truncations (Hypothesis 3).

Acceptance criteria

• Root cause identified and documented in the issue
• Fix applied (likely a seed.sql correction, but defer specifics until diagnosis)
• CI re-run on PR feat: artifact upload endpoint with pluggable storage backend (BGSTM #298) #310 shows TC-004 traceability test passing without retry
• Traceability Matrix in the failing screenshot scenario shows non-zero coverage and includes all 5 seeded TC-00X rows
• If applicable, add a smoke check to the E2E setup that fails fast with a clear error if seed loading is incomplete (e.g., assert row counts post-seed)

Impact

• Blocking: PR feat: artifact upload endpoint with pluggable storage backend (BGSTM #298) #310 (artifact upload) and any future PR that touches code paths exercised by E2E tests
• Masking: Real Traceability Matrix regressions are invisible because the matrix is broken at the data layer before any UI logic runs
• Misleading: [Test] E2E CRUD specs pollute seeded TC-00X rows, cause flaky Traceability Matrix failures #308's pollution fix appeared to work but the real bug was always present — anyone diagnosing similar flakes in the future will be misdirected by the closed issue

Out of scope

• Refactoring the entire seed/fixture strategy (separate epic if needed)
• Migrating to a programmatic seed (Python script) vs. raw SQL — track separately if desired
• Fixing sample_data.py if it's the culprit — handle in a follow-up if Hypothesis 2 is confirmed

References

• Failing run: PR feat: artifact upload endpoint with pluggable storage backend (BGSTM #298) #310 (initial + re-run, both reproduce)
• Prior (incorrect) hypothesis: [Test] E2E CRUD specs pollute seeded TC-00X rows, cause flaky Traceability Matrix failures #308 (closed) — pollution-theory fix
• Related test: frontend/tests/e2e/traceability.spec.ts#L37
• Seed file: frontend/tests/e2e/fixtures/seed.sql
• Possible competing seed: backend/app/db/sample_data.py