External-source publications: verify ingest + profile render (code merged #1670)

[paulalbert1]

Goal

Surface manually-added external-source publications (Scopus / OpenAlex / WOS, added by curators in Publication Manager — ReCiter #659/#661) in SPS scholar profiles alongside PubMed pubs, without breaking bibliometrics or publication identity.

How it flows

ExternalArticle (DynamoDB) → reciterdb external_article → STEP 6b unions into analysis_summary_article / analysis_summary_author (ReCiterDB #101, shipped) → SPS reciter ETL ingests into Publication.

Work

• ReCiterDB B02 — /api/edit authorization predicate + 403 telemetry #101 — external-article store + reporting union (STEP 6b), shipped to master (09ef4a6).
• ReCiterDB B19 — Reciter → DynamoDB consistency-window UI placeholder #118 — exposes the stable, source-prefixed article_id in analysis_summary_article. MERGED to master (30147ab). The v1.7 ALTER is not applied by merging — see below.
• SPS External-source publications: SPS ingest + render (#101 downstream) #1670 — key external Publication rows on article_id; render gating via a single pubSource() helper (source label, no dead pubmed.ncbi.nlm.nih.gov/-N link). CI green (build + cdk). Currently a DRAFT and BLOCKED — see the hazard below.

Design outcome

No SPS schema migration. article_id (source-prefixed, ≤32 chars for every current source) fits the existing Publication.pmid VarChar(32) and encodes the source in its prefix, so external-ness derives from the pmid (numeric = PubMed). Stable identity comes from article_id rather than the synthetic pmid, which reciterdb recomputes every nightly.

⛔ Hazard — #1670 is NOT safe to merge yet

SPS #1670 adds article_id to the SELECT ... FROM analysis_summary_article in the reciter ETL. If that column does not exist in the reciterdb the SPS ETL reads, the query fails with MySQL 1054 Unknown column 'article_id' in 'field list' and the nightly reciter ETL breaks. It does not degrade gracefully. Merging #1670 auto-deploys to staging, so it would break the staging nightly.

Note the feature is dormant (0 external rows until cutover) but this schema dependency is not — the ETL reads the column on every run regardless.

Deploy order (must hold)

1. ✅ ReCiterDB B19 — Reciter → DynamoDB consistency-window UI placeholder #118 merged to master (30147ab) — column added to the DDL, populated in STEP 6b.
2. ⬜ Apply alter_add_article_id_analysis_summary_article_v1.7.sql to the reciterdb instance the SPS ETL reads. Merging B19 — Reciter → DynamoDB consistency-window UI placeholder #118 does not apply it — manual operator step.
3. ⬜ Confirm whether that instance is built from reciterdb master or dev. B19 — Reciter → DynamoDB consistency-window UI placeholder #118 targeted master only (the B02 — /api/edit authorization predicate + 403 telemetry #101 union went to both master B17 — VPC endpoints for Secrets Manager / S3 / OpenSearch #116 and dev B18 — OpenSearch alias-swap pattern (scholars_v{N}) #117). If SPS reads a dev-built reciterdb, a dev counterpart of B19 — Reciter → DynamoDB consistency-window UI placeholder #118 is also required.
4. ⬜ Mark SPS External-source publications: SPS ingest + render (#101 downstream) #1670 ready and merge (it is currently a draft to prevent an accidental early merge).
5. ⬜ In-cluster load-verify (ReCiterDB B02 — /api/edit authorization predicate + 403 telemetry #101 remaining) that external rows land in analysis_summary_article with article_id populated.

The feature stays dormant until the upstream ReCiter #626 / PM #719 cutover — 0 external rows until then.

Remaining

• Deploy ordering above (B19 — Reciter → DynamoDB consistency-window UI placeholder #118 before External-source publications: SPS ingest + render (#101 downstream) #1670 takes effect).
• In-cluster load-verify (ReCiterDB B02 — /api/edit authorization predicate + 403 telemetry #101).
• Spot-check an external pub renders correctly on a profile once rows exist (post-cutover): DOI link works, source label shows, no PubMed link, stable URL, no crash on null authors.

Refs

ReCiterDB #101 / #116 / #117 / #118; SPS #1670; ReCiter #659 / #660 / #661 / #662 / #665; PM #771 / #772; upstream cutover ReCiter #626 + PM #719.

[paulalbert1]

Probe of the live reciterdb (2026-07-12) — the #101 union is NOT deployed to the database

Ran a read-only probe in-VPC against the live reciterdb (MariaDB 11.4.9), which both SPS staging and prod read (same host/db — there is only one instance, so there is no master-vs-dev fork; #118 on master is correct and no dev counterpart is needed).

Check	Live DB
external_article table	✅ exists, 2 rows — the projection ETL is running
populateAnalysisSummaryTables_v2 SP	❌ no STEP 6b (has_external=0), last altered 2026-05-06
source_type (v1.6)	❌ absent
doi width (v1.6 widens 128→255)	❌ still varchar(128)
article_id (v1.7)	✅ applied 2026-07-12 (see below)

So the #101 reporting union is inert on the live database. External articles are projected into external_article, but STEP 6b never runs, so they never reach analysis_summary_article / analysis_summary_author, and therefore never reach SPS. #101 was merged in code and the image built — the database side (v1.6 migration + stored-procedure deploy) was never done.

Done

• v1.7 ALTER applied to the live reciterdb: article_id varchar(96) NULL added to analysis_summary_article (309,725 rows, 0 warnings, row count unchanged). Inert — the deployed SP references neither article_id nor source_type. This clears the MySQL 1054 blocker on External-source publications: SPS ingest + render (#101 downstream) #1670.

Remaining (in order)

1. ⬜ Apply v1.6 (source_type + widen doi) — inert today, but required before the SP deploy or STEP 6b fails on the missing column.
2. ⬜ Deploy the updated populateAnalysisSummaryTables_v2 stored procedure (from B17 — VPC endpoints for Secrets Manager / S3 / OpenSearch #116/B19 — Reciter → DynamoDB consistency-window UI placeholder #118). This is the activating step — it makes STEP 6b run and pushes the 2 external pubs into the reporting tables. Owned by the ReCiterDB pipeline; changes production reporting behavior.
3. ⬜ Then the 'B02 — /api/edit authorization predicate + 403 telemetry #101 in-cluster load-verify' becomes meaningful (it would fail today).
4. ⬜ Mark External-source publications: SPS ingest + render (#101 downstream) #1670 ready + merge (now unblocked; behaviorally inert until step 2 lands).

[paulalbert1]

Update 2026-07-12 — schema migrations applied, #1670 merged

Safety check first: probed every stored routine on the live DB for an implicit INSERT ... SELECT * into analysis_summary_article (which would break on a column-count mismatch when columns are added). None found — populateAnalysisSummaryTables_v2 and ...Part3 both use explicit column lists, and analysis_summary_article_new is created via LIKE, so added columns simply take their DEFAULT. Adding columns is safe for the nightly.

Applied to the live reciterdb

Migration	Result
v1.6 — add source_type	✅ varchar(16) DEFAULT 'PUBMED', 309,725 rows, 0 warnings
v1.6 — widen doi 128→255	✅ now varchar(255) (in-place)
v1.7 — add article_id	✅ varchar(96) NULL (applied earlier; re-run was an idempotent no-op)

Row count unchanged (309,725). The reciterdb schema is now consistent with the merged #116/#118 code.

Merged

• SPS External-source publications: SPS ingest + render (#101 downstream) #1670 merged to master (8ca0bd1a) — safe now that article_id exists (no MySQL 1054).

⬜ The one thing still outstanding — and it's the activating step

The updated populateAnalysisSummaryTables_v2 stored procedure is still NOT deployed (live copy last altered 2026-05-06, no STEP 6b). Until the ReCiterDB pipeline deploys it:

• STEP 6b never runs → the 2 rows in external_article never reach analysis_summary_article;
• article_id / source_type stay NULL/default → SPS ingests nothing external and renders exactly as before.

So the whole chain is wired and schema-ready, but inert until that procedure is deployed. That deploy changes production reporting behavior and belongs to the ReCiterDB pipeline — flagging rather than doing it.

[paulalbert1]

2026-07-12 — stored procedure DEPLOYED. The chain is now live end-to-end (pending the next nightly).

First: a defect in #118 that would have silently defeated it

populateAnalysisSummaryTables_v2 is defined in two files — populateAnalysisSummaryTables_v2.sql and createEventsProceduresReciterDb.sql. The #101 union commit updated both; #118 updated only one. Deploying from the other copy would have run STEP 6b but never populated article_id — no error, permanently NULL, and SPS would silently fall back to the churning synthetic pmid. Fixed + merged as ReCiterDB #119 (b2a8e47); the two STEP 6b blocks are now byte-identical.

Pre-deploy safety gates

• Dumped the live procedure and diffed it against 3ee47f7: code-identical → nobody had hand-edited it on the server, so replacing it clobbers nothing.
• Delta 3ee47f7 → master: 52 code lines added, 0 removed — pure addition of STEP 6b, nothing existing touched.
• Verified no routine uses an implicit INSERT ... SELECT * into analysis_summary_article (would break on column-count mismatch).
• Applied only populateAnalysisSummaryTables_v2, leaving the other procedures in that file untouched.
• Saved a rollback script of the prior definition.

Deployed

populateAnalysisSummaryTables_v2 replaced from master (b2a8e47). LAST_ALTERED 2026-05-06 → 2026-07-13. Verified:

• code markers: external_article, temp_external_pmid, syn_pmid, MAX(t.article_id), suppressed = 0 — all present;
• live body vs master, code-only: 1018 == 1018 lines, identical.

(Note: ROUTINE_DEFINITION LIKE '%STEP 6b%' reads 0 because MariaDB strips comments from stored routine bodies — the code is there.)

State of the whole chain

Layer	Status
external_article projection ETL	✅ live (2 rows)
v1.6 + v1.7 schema migrations	✅ applied
STEP 6b union procedure	✅ deployed
SPS ingest + render (#1670)	✅ merged

⬜ Remaining

The next nightly (run_nightly_indexing.sh → CALL populateAnalysisSummaryTables_v2()) will exercise STEP 6b for the first time and push the 2 external pubs into analysis_summary_article/analysis_summary_author with article_id + source_type. After that the SPS reciter ETL ingests them keyed on article_id. That nightly run is the real load-verify — worth watching analysis_job_log for the '6b. Union external-source publications' step.

[paulalbert1]

Load-verify handoff

The remaining step (running STEP 6b for the first time) is written up as an executable handoff rather than fired mid-day, since a manual trigger is a full reporting rebuild with an atomic table swap.

Handoff: ~/Dropbox/Projects/Roadmap/reciterdb-101-external-article-handoff.md
Rollback script (prior 2026-05-06 procedure, dumped from the live DB pre-deploy): ~/Dropbox/Projects/Roadmap/reciterdb-populateAnalysisSummaryTables_v2-ROLLBACK-2026-05-06.sql

It covers:

• Option A — wait for the nightly (zero risk; it now runs 6b automatically).
• Option B — trigger CALL populateAnalysisSummaryTables_v2() manually, with the rebuild/swap risk spelled out and the GET_LOCK guard noted.
• Success criteria as runnable SQL: the 6b. Union external-source publications row in analysis_job_log, the 2 external rows landing in analysis_summary_article with article_id + source_type, their person links (sub9064, avl4004), and a PubMed-rows-untouched sanity check.
• The SPS-side follow-through: external pubs should appear in publication keyed on pmid = 'SCOPUS:...' with pubmed_url NULL, then render with a DOI link + Source: Scopus label and no PubMed link.
• Rollback command.
• Gotchas worth not relearning: 6b is isolated in try/except so a green overall run does NOT mean 6b succeeded — check for a 6b ... ERROR row explicitly; MariaDB strips comments from routine bodies (grep code markers, not STEP 6b); the procedure is defined in two files that must stay in sync.

[paulalbert1]

Narrowing scope. The SPS ingest + render code merged in #1670 (2026-07-13), and the upstream deploy-ordering prerequisite is done. What remains is runtime verification only: confirm external-source rows land in SPS and render correctly on a profile. Retitled to that gap.