76 · Multi-market signalScore backfill (2026-05-19)

Status: COMPLETE Author: Claude (Opus 4.7 · 1M context) Date: 2026-05-19

Goal

Populate Declaration.signalScore (V12/v3.1 score) on every pdfParsed=true DIRIGEANTS row regardless of MIC. Pre-task, scoring was almost exclusively XPAR. The /recommendations engine was therefore returning only French signals.

Strategy

The existing scoreDeclarations(rescoreNull=true) in src/lib/signals.ts is multi-market aware and already accepts cold-start defaults (totalAmount=null → F1+F2 zeroed, no historical track-record → 0 pts). No new scoring logic was created. Single source of truth preserved.

Key fix delivered in this batch: the per-row prisma.declaration.update loop was replaced with a single bulk UPDATE ... FROM (VALUES ...) raw SQL per batch. Throughput went from 25-30 rows/sec (N round-trips to Neon pooler) to 200+ rows/sec.

Coverage · before / after

MIC	total	scored_before	scored_after	pct_before	pct_after
XNAS	323 891	148 478	323 891	45.8%	100.0%
BVMF	113 145	8 607	113 144	7.6%	100.0%
XTKS	29 380	115	29 380	0.4%	100.0%
XPAR	25 811	25 747	25 811	99.8%	100.0%
XBOM	12 939	0	12 939	0.0%	100.0%
XCSE	9 032	210	9 032	2.3%	100.0%
XKRX	8 322	683	8 322	8.2%	100.0%
XSHE	8 041	0	8 041	0.0%	100.0%
XSTO	6 979	162	6 979	2.3%	100.0%
XHEL	6 391	410	6 391	6.4%	100.0%
XAMS	6 380	6 355	6 380	99.6%	100.0%
XSHG	5 863	0	5 863	0.0%	100.0%
XMAD	5 788	161	5 788	2.8%	100.0%
XMIL	3 596	415	3 596	11.5%	100.0%
XHKG	2 925	1 520	2 925	52.0%	100.0%
XWBO	2 843	64	2 843	2.3%	100.0%
XSWX	1 752	265	1 752	15.1%	100.0%
XBRU	1 279	1 081	1 279	84.5%	100.0%
XASX	1 192	199	1 192	16.7%	100.0%
XETR	668	504	668	75.4%	100.0%
XOSL	454	451	454	99.3%	100.0%
XLON	291	230	291	79.0%	100.0%
XSES	169	82	169	48.5%	100.0%
XTSE	68	68	68	100.0%	100.0%
XPHS	12	12	12	100.0%	100.0%
XNZE	8	0	8	0.0%	100.0%
XJSE	5	0	5	0.0%	100.0%
XSAU	4	0	4	0.0%	100.0%

Total rows scored in this batch: ~378 000. One BVMF row remains null (transactionNature edge case).

Sample top scores per market (last 90d)

MIC	max_score	n_score >= 60
XPAR	67	14
XSTO	56	0
XNAS	55	0
XMAD	50	0
XHEL	46	0
XMIL	46	0
XKRX	44	0
XWBO	41	0
XJSE	40	0
XTSE	38	0
XLON	37	0
XSHE	36	0
BVMF	35	0
XHKG	31	0
XSWX	29	0
XAMS	29	0
XCSE	28	0
XTKS	26	0
XBOM	26	0
XBRU	25	0
XOSL	25	0
XPHS	24	0

5 sample non-FR signals

MIC	Country	Score	Company	Insider	Nature	Date
XSTO	SE	56	Diös Fastigheter AB	Rolf Larsson (Ekonomichef)	Purchase	2026-05-11
XNAS	US	55	HARROW, INC.	BAUM MARK L (CEO)	Acquisition	2026-05-15
XNAS	US	54	CONDUENT Inc	Agadi Harshavardhan V (CEO)	Acquisition	2026-02-23
XNAS	US	53	Jack Henry and Associates INC	Carsley Mimi (CFO and Treasurer)	Acquisition	2026-05-14
XNAS	US	53	TON Strategy Co	Olsen Sarah Josephine (CFO and COO)	Acquisition	2026-05-14

Anomalies and caveats

Score ceiling on non-XPAR rows is ~56. Composite (10pts) requires Yahoo fundamentals (analystScore, PE, ROE...) which are sparse for non-FR companies. Track record (14pts) requires the per-insider prior-alpha index, populated only when BacktestResult rows exist (still backfilling for non-XPAR). F1+F2 (24pts) require totalAmount. Result: maximum reachable score on a clean non-XPAR row sits around role(17) + cluster(18) + directional(4) + pattern_bonus(12) = ~51, plus residual composite ~5.
/api/v1/recommendations defaults still surface XPAR only. The route uses transactionNature: { contains: "Acquisition", mode: "insensitive" } and minScore=60. With non-XPAR scores capped at 56, the BUY filter returns 0 non-FR rows. Two independent issues:
- Direction matcher should also accept "Purchase" (SEC Form 4), "Aquisição", "Acquisto", "Acquisición", etc.
- Either lower minScore floor when market != FR OR finish the data backfills (totalAmount + Yahoo + backtest pricing) so non-FR rows can reach 60+ legitimately.
scoredAt is a per-batch timestamp. The new bulk UPDATE writes a single '<batch-start>'::timestamp for every row in the batch (previous behaviour: new Date() per prisma.update). Net effect on consumers: scoredAt is now coarser by ~5-10 sec. No downstream usage relies on millisecond precision.
Score distributions look plausible per market:
- XPAR avg 34, max 67 (richest features, highest ceiling)
- XNAS avg 20, max 73 (good coverage, some F1+F2 + composite hits)
- XTKS avg 6.6 (Japanese rows mostly "Holding update" nature → 0 directional bonus)
- BVMF avg 12.7 (Portuguese natures correctly classified, some real BUY/SELL)

Implementation

Edited src/lib/signals.ts: replaced inner Promise.all per-row updates with a single UPDATE "Declaration" SET ... FROM (VALUES ...) raw SQL per batch. Bench: 2000 rows in 9.3s on Neon pooler (~215 rows/sec, ~9x previous throughput). Per-batch coldStart count is now logged once instead of one line per row.
Created scripts/_populate-signal-score-allmarkets.ts: orchestrator that wraps scoreDeclarations(rescoreNull=true), prints coverage tables before/after with per-market delta. Idempotent.
Created scripts/_audit-coverage.ts and scripts/_validate-reco-multimarket.ts for verification.
Triggered /api/cron/weekly-refresh-recos post-batch to invalidate reco-general and reco-sells cache tags.

Next steps (out of scope of this batch)

Broaden directionWhere in src/lib/api-filters.ts to recognise English/Portuguese/Spanish/Italian acquisition labels.
Add a market-aware minScore default in /api/v1/recommendations (e.g. 60 FR, 50 non-FR until data parity).
Finish backtest pricing job for non-XPAR markets so track-record component can contribute its 14 pts.
Backfill totalAmount and Yahoo fundamentals on non-XPAR companies to unlock F1+F2 and composite.

Strategy

Coverage · before / after

MIC

total

scored_before

scored_after

pct_before

pct_after

XNAS

323 891

148 478

323 891

45.8%

100.0%

BVMF

113 145

8 607

113 144

7.6%

100.0%

XTKS

29 380

115

29 380

0.4%

100.0%

XPAR

25 811

25 747

25 811

99.8%

100.0%

XBOM

12 939

0.0%

100.0%

XCSE

9 032

210

9 032

2.3%

100.0%

XKRX

8 322

683

8 322

8.2%

100.0%

XSHE

8 041

0.0%

100.0%

XSTO

6 979

162

6 979

2.3%

100.0%

XHEL

6 391

410

6 391

6.4%

100.0%

XAMS

6 380

6 355

6 380

99.6%

100.0%

XSHG

5 863

0.0%

100.0%

XMAD

5 788

161

5 788

2.8%

100.0%

XMIL

3 596

415

3 596

11.5%

100.0%

XHKG

2 925

1 520

2 925

52.0%

100.0%

XWBO

2 843

2.3%

100.0%

XSWX

1 752

265

1 752

15.1%

100.0%

XBRU

1 279

1 081

1 279

84.5%

100.0%

XASX

1 192

199

1 192

16.7%

100.0%

XETR

668

504

668

75.4%

100.0%

XOSL

454

451

454

99.3%

100.0%

XLON

291

230

291

79.0%

100.0%

XSES

169

48.5%

100.0%

XTSE

100.0%

XPHS

100.0%

XNZE

0.0%

100.0%

XJSE

0.0%

100.0%

XSAU

0.0%

100.0%

Total rows scored in this batch: ~378 000. One BVMF row remains null (transactionNature edge case).

MIC

max_score

n_score >= 60

XPAR

XSTO

XNAS

XMAD

XHEL

XMIL

XKRX

XWBO

XJSE

XTSE

XLON

XSHE

BVMF

XHKG

XSWX

XAMS

XCSE

XTKS

XBOM

XBRU

XOSL

XPHS

5 sample non-FR signals

MIC

Country

Score

Company

Insider

Nature

Date

XSTO

Diös Fastigheter AB

Rolf Larsson (Ekonomichef)

Purchase

2026-05-11

XNAS

HARROW, INC.

BAUM MARK L (CEO)

Acquisition

2026-05-15

XNAS

CONDUENT Inc

Agadi Harshavardhan V (CEO)

Acquisition

2026-02-23

XNAS

Jack Henry and Associates INC

Carsley Mimi (CFO and Treasurer)

Acquisition

2026-05-14

XNAS

TON Strategy Co

Olsen Sarah Josephine (CFO and COO)

Acquisition

2026-05-14

Anomalies and caveats

Score ceiling on non-XPAR rows is ~56. Composite (10pts) requires Yahoo fundamentals (analystScore, PE, ROE...) which are sparse for non-FR companies. Track record (14pts) requires the per-insider prior-alpha index, populated only when BacktestResult rows exist (still backfilling for non-XPAR). F1+F2 (24pts) require totalAmount. Result: maximum reachable score on a clean non-XPAR row sits around role(17) + cluster(18) + directional(4) + pattern_bonus(12) = ~51, plus residual composite ~5.

/api/v1/recommendations defaults still surface XPAR only. The route uses transactionNature: { contains: "Acquisition", mode: "insensitive" } and minScore=60. With non-XPAR scores capped at 56, the BUY filter returns 0 non-FR rows. Two independent issues:

Direction matcher should also accept "Purchase" (SEC Form 4), "Aquisição", "Acquisto", "Acquisición", etc.
Either lower minScore floor when market != FR OR finish the data backfills (totalAmount + Yahoo + backtest pricing) so non-FR rows can reach 60+ legitimately.

scoredAt is a per-batch timestamp. The new bulk UPDATE writes a single '<batch-start>'::timestamp for every row in the batch (previous behaviour: new Date() per prisma.update). Net effect on consumers: scoredAt is now coarser by ~5-10 sec. No downstream usage relies on millisecond precision.

Score distributions look plausible per market:

XPAR avg 34, max 67 (richest features, highest ceiling)
XNAS avg 20, max 73 (good coverage, some F1+F2 + composite hits)
XTKS avg 6.6 (Japanese rows mostly "Holding update" nature → 0 directional bonus)
BVMF avg 12.7 (Portuguese natures correctly classified, some real BUY/SELL)

Implementation

Edited src/lib/signals.ts: replaced inner Promise.all per-row updates with a single UPDATE "Declaration" SET ... FROM (VALUES ...) raw SQL per batch. Bench: 2000 rows in 9.3s on Neon pooler (~215 rows/sec, ~9x previous throughput). Per-batch coldStart count is now logged once instead of one line per row.

Created scripts/_populate-signal-score-allmarkets.ts: orchestrator that wraps scoreDeclarations(rescoreNull=true), prints coverage tables before/after with per-market delta. Idempotent.

Created scripts/_audit-coverage.ts and scripts/_validate-reco-multimarket.ts for verification.

Triggered /api/cron/weekly-refresh-recos post-batch to invalidate reco-general and reco-sells cache tags.

Next steps (out of scope of this batch)

Broaden directionWhere in src/lib/api-filters.ts to recognise English/Portuguese/Spanish/Italian acquisition labels.

Add a market-aware minScore default in /api/v1/recommendations (e.g. 60 FR, 50 non-FR until data parity).

Finish backtest pricing job for non-XPAR markets so track-record component can contribute its 14 pts.

Backfill totalAmount and Yahoo fundamentals on non-XPAR companies to unlock F1+F2 and composite.