Activity

Why

While auditing the MCP tools I hit two things that compound each other:

1. security_digest lists the same CVE up to 20× (confirmed live today — CVE-2026-47140 twenty times in the critical block).
2. The reason it shipped: the test suite runs on SQLite, so the 27 Postgres-specific dashboard/MCP query tests (DISTINCT ON, date_trunc, materialized views) all skip in CI. The query layer had zero automated coverage.

This PR fixes both — CI now runs on Postgres (activating the dormant tests), and the dedup bug is fixed with coverage.

1. Postgres in CI

• tests.yml: adds a postgres:16 service + DB_* env. The suite now runs on pgsql.
• Pest.php: refreshes the cross_platform_matches materialized view before each test (created WITH NO DATA; instant + transaction-safe on empty test tables).
• Fixed the tests this exposed (all pre-existing, never run before):
  • GetSecurityDigestTest: firstOrCreate for ghsa_all/krebs (seeded by the add_new_data_sources migration → factory create() hit a unique violation on pg).
  • Dashboard auth tests: home has no auth middleware (it's the public trends landing; /mcp-stats is the auth-gated one). The "guests redirected to login" assertion was stale — updated to assert the public 200. Verified against the route, not assumed.

Result: 274 passed / 0 skipped on Postgres (was 246 passed / 28 skipped on SQLite). SQLite path still green for local runs.

2. security_digest dedup

queryCritical / queryHigh / queryNvdCisa had no dedup — they sorted all the re-fetched duplicate rows by CVSS and took 20. Wrapped each in a DISTINCT ON (url_hash) subquery (external_id for nvd_cisa), keeping the latest fetch per advisory, then sorting by CVSS in the outer query — the pattern querySecurityBlogs already used. medium_count → COUNT(DISTINCT url_hash).

New test seeds one advisory re-fetched 20× and asserts it appears once.

Test plan

• Postgres (local, clean DB): 274 passed / 0 skipped.
• SQLite: 246 passed / 28 skipped (unchanged).
• pint --dirty clean.
• Post-deploy: re-run security_digest via MCP and confirm each CVE appears once.

Follow-ups (separate PRs, now testable on pg CI)

• weekly_intel HN section sorts by url_hash not score.
• opportunity_finder only ever tags [general].
• find_divergences: seed the 17-topic watchlist (never seeded → always empty) + add a discourse-level ceiling so it fires on genuinely quiet topics rather than already-loud ones.