feat(scanner): cross-country scan and submission analytics views

[ericsocrat]

Problem Statement

Once the scanner pipeline captures scan_country (#921) and suggested_country (#922), we accumulate valuable cross-country behavioral data. Currently no analytics surface exists to answer questions like:

• What % of scans hit a product from a different country?
• Which EANs are scanned in both PL and DE? (candidates for product_links)
• How many submissions per country per week?
• What's the scan-miss rate by country? (no product found at all)

This data informs product catalog expansion priorities and cross-country linking strategy.

---

Solution

1. Analytics SQL View

CREATE OR REPLACE VIEW v_cross_country_scan_analytics AS
SELECT
  scan_country,
  count(*) AS total_scans,
  count(*) FILTER (WHERE found = true) AS found_scans,
  count(*) FILTER (WHERE found = false) AS missed_scans,
  round(100.0 * count(*) FILTER (WHERE found = false) / NULLIF(count(*), 0), 1) AS miss_rate_pct,
  count(DISTINCT ean) AS unique_eans_scanned,
  count(DISTINCT ean) FILTER (WHERE found = false) AS unique_eans_missed
FROM scan_history
WHERE scan_country IS NOT NULL
GROUP BY scan_country;

2. Cross-Country EAN Report

CREATE OR REPLACE VIEW v_cross_country_ean_candidates AS
SELECT
  ean,
  array_agg(DISTINCT scan_country ORDER BY scan_country) AS scanned_in_countries,
  count(DISTINCT scan_country) AS country_count,
  min(scanned_at) AS first_scanned,
  max(scanned_at) AS last_scanned,
  count(*) AS total_scans
FROM scan_history
WHERE scan_country IS NOT NULL
  AND ean IS NOT NULL
GROUP BY ean
HAVING count(DISTINCT scan_country) > 1
ORDER BY total_scans DESC;

3. Submission Analytics by Country

CREATE OR REPLACE VIEW v_submission_country_analytics AS
SELECT
  suggested_country,
  count(*) AS total_submissions,
  count(*) FILTER (WHERE status = 'pending') AS pending,
  count(*) FILTER (WHERE status = 'approved') AS approved,
  count(*) FILTER (WHERE status = 'rejected') AS rejected,
  count(*) FILTER (WHERE status = 'merged') AS merged,
  round(100.0 * count(*) FILTER (WHERE status = 'approved' OR status = 'merged')
    / NULLIF(count(*), 0), 1) AS acceptance_rate_pct
FROM product_submissions
WHERE suggested_country IS NOT NULL
GROUP BY suggested_country;

---

Scope

In scope:

• 3 analytics views: scan stats by country, cross-country EAN candidates, submission stats by country
• Migration file
• QA checks: views return expected structure, no NULL issues
• Documentation: add views to copilot-instructions.md §4 if significant

Out of scope:

• Admin dashboard UI for these analytics (future issue)
• Automated product_links creation from cross-country EAN data (future)
• Real-time alerting on miss rates (use existing monitoring framework)

---

Acceptance Criteria

• v_cross_country_scan_analytics returns per-country scan metrics
• v_cross_country_ean_candidates identifies EANs scanned in multiple countries
• v_submission_country_analytics returns per-country submission metrics
• All views handle NULL scan_country / suggested_country gracefully (excluded via WHERE)
• Views return 0 rows when no country-annotated data exists (backward safe)
• Migration is idempotent (CREATE OR REPLACE VIEW)
• .\RUN_QA.ps1 — all checks pass

Dependencies

• schema(scanner): add scan_country to scan_history table #921 — scan_country column in scan_history
• schema(scanner): add scan_country + suggested_country to product_submissions #922 — suggested_country column in product_submissions
• Data only populates after feat(scanner): pass user region through api_record_scan and api_submit_product #923 and feat(frontend): pass scan country from frontend scan/submit flow to RPCs #924 are deployed (users start scanning with country context)

Effort & Priority

• Effort: M (3–4h)
• Priority: P3
• Phase: 3 (Data Integrity Hardening)

Part of epic #920

[ericsocrat]

Implemented in PR #945

Migration: 20260321000700_cross_country_analytics_views.sql

3 Analytics Views Created

View	Columns	Purpose
v_cross_country_scan_analytics	7	Per-country scan metrics (total, found, missed, miss rate, unique EANs)
v_cross_country_ean_candidates	6	EANs scanned in >1 country — candidates for product_links
v_submission_country_analytics	7	Per-country submission metrics (by status, acceptance rate)

QA

• View consistency suite updated: 13 → 16 checks — all pass
• All 4 required CI checks pass (Typecheck & Lint ✅, Build ✅, Unit Tests ✅, Playwright Smoke ✅)

Epic #920 Complete

This is the last issue in the Country-Aware Scanner epic (#920). All 12 issues (#921–#932) are now shipped.