🧩 MosAIc — Multi-Source AI ETF Basket Advisor

MosAIc builds ETF basket recommendations by assembling many data pieces into one coherent picture — structured fund data, quantitative analysis, macro regime indicators, prediction market odds, and multi-source news — orchestrated through a two-program architecture that separates data gathering from AI reasoning.

Program 1 scrapes and computes: fetches ETF data from web sources, extracts structured fields, computes correlation matrices, momentum indicators, and currency exposure, then saves everything to a MariaDB database. No AI needed.

Program 2 reasons and reports: reads the database, fetches fresh news, runs multi-model AI analysis, and produces a final report with basket allocation, scenario analysis, risk assessment, and self-critique.

The Web Viewer is a read-only browser interface over the same database. It complements the report rather than replacing it: the report is the strategist's output, the viewer is the analyst's lookup tool — type an ISIN or RIC, see every scraped field, every correlation, and the latest macro snapshot for that ETF. A separate /browse route lets you page through the full universe with filters and sorts.

What It Does

MosAIc splits its work into two CLI programs that share a MariaDB database, plus a web viewer that reads the same database. This separation means data can be refreshed independently of analysis, multiple reports can be generated from the same data snapshot, anyone with browser access can explore the data without running anything, and the database grows incrementally over time.

  ┌──────────────────────────────┐
  │   PROGRAM 1: mosaic-db.js    │
  │   Scraper (no AI needed)     │
  └──────────────┬───────────────┘
                 │
  ┌──────────────▼───────────────┐
  │       DATA GATHERING         │
  │                              │
  │  Per-ETF pages (justETF,     │
  │  Yahoo Finance, Borsa IT)    │
  │  FRED macro indicators       │
  └──────────────┬───────────────┘
                 │
  ┌──────────────▼───────────────┐
  │    STRUCTURED EXTRACTION     │
  │                              │
  │  Performance (1M–5Y, YTD)    │
  │  TER, AUM, distribution      │
  │  Sectors, countries          │
  │  Holdings (top 5)            │
  │  Bond/commodity/gold profiles│
  │  Fund currency, hedging      │
  └──────────────┬───────────────┘
                 │
  ┌──────────────▼───────────────┐
  │     QUANTITATIVE ENGINE      │
  │                              │
  │  Correlation matrix          │
  │  (intra-market only)         │
  │  Holdings overlap            │
  │  Momentum indicators         │
  │  Currency exposure           │
  │  FRED regime signals         │
  └──────────────┬───────────────┘
                 │
  ┌──────────────▼───────────────┐         ┌──────────────────────┐
  │       MariaDB (16 tables)    │ ───────►│   WEB VIEWER (Astro) │
  │  ETF + child tables          │  reads  │   read-only browser  │
  │  Correlation | Fred          │         │   search, browse,    │
  └──────────────┬───────────────┘         │   detail             │
                 │                         └──────────────────────┘
  ┌──────────────▼───────────────┐
  │   PROGRAM 2: mosaic-report.js│
  │   Reporter (AI + fresh news) │
  └──────────────┬───────────────┘
                 │
  ┌──────────────▼───────────────┐
  │     AI REASONING LAYER       │
  │                              │
  │  DB data + fresh news per ETF│
  │  Multi-model consensus       │
  │  Cross-correlation check     │
  │  Deep analysis (top picks)   │
  │  Final report + scenarios    │
  │  Self-critique (red team)    │
  └──────────────┬───────────────┘
                 │
  ┌──────────────▼───────────────┐
  │          OUTPUT              │
  │                              │
  │  Basket composition (%)      │
  │  Buy/Sell/Hold per ETF       │
  │  Risk assessment             │
  │  Scenario analysis           │
  │  Rebalancing actions         │
  │  Signal drift vs last        │
  └──────────────────────────────┘

The Pipeline

Program 1: Data Gathering & Computation

Runs without AI. Designed to be scheduled (daily/weekly cron) or run manually before generating a report.

Step 1 — Read ETF Universe from Database

Reads all ETFs from the ETF table in MariaDB. For Program 1, the database is the source of truth for the ETF universe — watchlist.json is a Program 2 concept and is not consulted here. Each row has at minimum an ISIN, market, and RIC, plus optional description and benchmark area. New ETFs are added by inserting a row directly:

mysql --user=$DB_USER --password=$DB_RAINBOW $DB \
  -e "INSERT INTO ETF (isin, market, ric)
      VALUES ('IE00B4L5Y983', 'BIT', 'SWDA')"

The --only RIC1,RIC2 flag restricts the run to specific tickers when you only need to refresh part of the universe. It matches all cross-listings of each named RIC — a single RIC may live on multiple exchanges (e.g. SWDA is listed on both Borsa Italiana and XETRA), and all such rows are refreshed together.

Step 2 — Page Fetching

Fetches the justETF profile page for each ETF via MCP. Uses a larger character limit (20,000) to capture performance data deep in the page.

Step 3 — Structured Extraction

Parses fetched pages to extract clean numbers: performance across all time periods, total expense ratio, fund size, distribution policy, replication method, sector/country allocations, top holdings, fund currency, hedging status, and 1-year volatility. For bond ETFs: rating, maturity, yield. For commodity/gold ETFs: categories, backing, domicile.

Step 4 — Quantitative Analysis

Three computations from the structured data, no AI involved:

• Correlation matrix — Pearson correlation on shared performance periods + asset class similarity. Partitioned by market: the algorithm groups ETFs by market then computes pairs only within each group, so cross-market pairs are never produced.
• Holdings overlap — shared positions between equity ETFs by normalized name matching.
• Momentum indicators — short-vs-long direction, acceleration, consistency, score from -2 to +2.

Step 5 — Macro Regime (FRED)

Optionally fetches Treasury yields, VIX, breakeven inflation, EUR/USD, and Euro HY spread from the FRED API. The 6 series are fetched in parallel via Promise.all (with per-series fallback chains preserved), and three regime signals are computed: yield curve shape, VIX regime, and real yield.

Step 5b — Currency Impact

Computes per-ETF currency exposure from country allocations, fund currency, and hedging status. Gold/commodities are automatically HIGH (USD-priced). Hedged funds are forced NONE.

Step 6 — Save to Database

Persists everything to MariaDB via upsert. Each ETF runs in its own transaction; each child table gets a DELETE + batch INSERT. Numeric coercion happens at the boundary: scraped strings like "115,407 m", "4.5 bn", or "€1.2 t" are coerced to BIGINT bytes; bad inputs drop to NULL with a console warning rather than truncating silently. Re-running updates existing rows, new ETFs are added, nothing is deleted unless you explicitly drop the database.

Program 2: AI Analysis & Report

Requires at least one LLM endpoint. Fetches fresh news to complement the database.

Step 1 — Load Basket & Join with Database

Loads watchlist.json (path supplied via the mandatory --watchlist flag), then joins each (isin, market) reference against the ETF table. The basket file is thin — only isin, market, and an optional currentWeight per entry. Everything else (RIC, description, benchmark area, scraped data, computed indicators) comes from the database join.

If any basket entry is not present in the database, Program 2 stops immediately and lists the missing ETFs. Run npm run db first to populate the universe.

Step 2 — Fresh News

Fetches per-ETF pages from justETF, Yahoo Finance, and Borsa Italiana. News is keyed by (isin, market) not just ISIN: cross-listed funds (same ISIN, different markets) get genuinely different content from Yahoo and Borsa Italiana because their URL templates substitute market suffix and exchange code.

Step 3 — Macro Context

Fetches ECB press conferences, monetary decisions, EUR/USD rate, and Polymarket prediction market odds for economy and geopolitics.

Step 4 — Per-ETF AI Analysis

Each ETF gets an AI call with two layers of input: structured data from the database (clean numbers the AI can cite directly) and fresh news text (current narrative context). The AI produces a signal, confidence, drivers, and a summary that must reference specific numbers.

Step 4b — Multi-Model Consensus

When multiple AI providers are configured, all of them independently analyze every ETF. Signals are merged by composite (ric, market) key — the same RIC on different markets is never silently consolidated. A local resolver merges signals: unanimous = HIGH confidence, disagreement = cautious resolution toward HOLD.

Step 5 — Cross-Correlation Check

Reviews all signals together with the computed correlation matrix to find contradictions, reinforcing patterns, and concentration warnings. The prompt mandates RIC (MARKET) notation in the output so cross-listings stay distinguishable in the narrative.

Step 6 — Scoring & Drift

Scores and ranks ETFs by conviction. Compares against the previous run to detect signal changes and demand explanations for any flips. Signal matching uses composite (ric, market) keys; querying for one market never returns another market's data — a critical guard against feeding the AI the wrong rationale for a cross-listed fund.

Confidence levels are HIGH / MEDIUM / LOW / UNKNOWN. UNKNOWN is preserved end-to-end (rather than silently downgraded to LOW) so the display surfaces parsing failures explicitly.

Step 7 — Deep Analysis

Top BUY/SELL candidates get additional news fetches and a deeper AI analysis covering fund mechanics, quantified risks with probabilities, specific timing, and basket interactions.

Step 8 — Macro News

Fetches European ETF market news for the final report's macro section.

Step 9 — Final Report

The most comprehensive AI call. Produces basket composition (constrained by risk level), recommendations, macro view, key risks, and two opposing scenarios. The allocation table identifier column is RIC (Market) so two listings of the same RIC remain distinguishable in the output.

Step 10 — Self-Critique

The complete report goes back to the AI as a "red team" review, producing a structured critique with an overall verdict. UNKNOWN-confidence signals are flagged here as parser-failure red flags requiring manual review.

The Database

Program 1 saves all data to MariaDB — 16 normalized InnoDB tables, utf8mb4 collation. The full DDL lives in mosaic-schema.sql at the repo root; one-shot migrations live in migrations/.

The database is incremental — re-running upserts existing data without dropping tables. To rebuild from scratch, drop the database and reload the schema.

Program 2 reads from the database and reconstructs the same data structures that Program 1 computed, so all existing formatting functions work unchanged.

A note on identity

The ETF table has TWO unique keys: (isin, market) and (ric, market). Both are legal — the same ISIN listed on different markets gets separate rows, AND the same RIC listed on multiple markets is also legal (e.g. SWDA on XETR and on BIT are independent rows with independent signals). Every per-ETF map across the codebase is keyed by composite ${ric}|${market} (or ${isin}|${market} when ISIN is the available identifier). RIC-only or ISIN-only keying anywhere would silently collide cross-listings — this has been audited end-to-end and is covered by regression tests.

The Watchlist

watchlist.json is the basket descriptor for a single report. It selects which ETFs from the universe make it into the analysis and, optionally, what their current portfolio weights are. Program 2 takes the path via the mandatory --watchlist flag.

The file is a thin JSON array — every other field comes from the database join:

[
  { "isin": "IE00B4L5Y983", "market": "BIT", "currentWeight": 25 },
  { "isin": "IE00BKM4GZ66", "market": "BIT", "currentWeight": 15 },
  { "isin": "IE00B3VWN518", "market": "BIT" }
]

Extra keys (your own notes, descriptions, broker IDs, anything) are silently ignored, so the basket file can also serve as a lightweight notebook for the position.

Because the basket is decoupled from the universe, multiple basket files can coexist (conservative.json, aggressive.json, …) and produce different reports from the same database snapshot.

If any basket entry references an ETF that is not in the database, the program stops with a clear error — there is no soft fallback.

The Web Viewer

The viewer is an Astro app that opens MariaDB read-only and serves two routes: a search-and-detail page (/) and a browseable universe view (/browse). It exists for the moments when you don't need a report — you just want to know what the database knows about this one ETF, or to scroll through the entire universe with filters.

The search page (/)

Three input fields at the top:

• ISIN — text field with prefix typeahead. Start typing IE0 and a list of matching ISINs drops down.
• RIC — same, for tickers (SWDA, EIMI, …).
• Market — dropdown listing every distinct exchange present in the universe, with an ETF count next to each.

You can search by ISIN, by RIC, or by either combined with a market filter. If the identifier is ambiguous (same RIC on two exchanges, for example), the viewer asks which one you meant and offers them as clickable links.

The result panel below the form shows everything the database has on the chosen ETF, in a flow that mirrors how an analyst reads a fund:

1. Identity and high-level profile (ISIN, market, RIC, asset class, currency, TER, fund size, distribution, replication, latest scrape time). currencyHedged renders as "yes / no / unknown" — the NULL distinction is preserved end-to-end so missing data isn't conflated with explicit "unhedged".
2. Performance across the standard horizons (1M through 5Y, plus YTD), with green/red coloring for direction.
3. Top sectors and countries, side by side.
4. Top holdings, with weights.
5. Asset-class-specific blocks: bond profile (rating, types, maturity), commodity profile (categories, weighting, exclusions), or gold profile (backing, metal, domicile) — only the relevant one is shown.
6. Computed quantitative signals: momentum and currency exposure.
7. Every correlation pair the ETF participates in, sorted by combined score. Each row's "→" navigation carries explicit ISIN+market so clicking a counterpart never accidentally lands on the wrong cross-listing.
8. The latest FRED macro snapshot stored in the database — series and derived signals.

The browse page (/browse)

A paginated, filterable, sortable table over the entire universe. Server-renders the initial page from URL params (refresh-safe, shareable). Client-side hydration handles:

• Debounced text search across RIC/ISIN/description
• Filter dropdowns: market, asset class, benchmark area
• Sortable columns (numeric columns default to descending on first click)
• Prev/Next pager
• Row click → deep-links to the detail page

The URL stays in sync with the active filters and sort, so sharing or bookmarking a specific view works as expected.

Universe vs basket, again

The viewer is bound to the universe — every ETF the database knows about, not just those in the current basket. This matters because the universe is intentionally broader: it can hold candidates you're considering, alternates you've previously held, or comparison ETFs included only for correlation context. The viewer is the only place where the universe is visible without a basket file in the loop.

Read-only by design

The web viewer cannot write to the database. It opens its own MariaDB connection pool (separate from the CLI's pool, with a smaller connection limit) and exposes only query helpers — no upsert function is even imported. This isn't a security claim, it's a reliability one: if the viewer crashes, hangs, or is accessed concurrently with a running scrape, the data on disk stays consistent.

The two CLI programs (which write) and the viewer (which only reads) can all run at the same time; MariaDB's transaction isolation handles the concurrency.

Canonical URL identity

The canonical UI identifier is (isin, market). ISIN is immutable and regulator-issued; RIC is a market-data-vendor convention that can change. The user can still TYPE a RIC into the search field — the API resolves it — but every URL the UI emits is ?isin=…&market=…. URL construction is centralised in src/lib/links.js, so any new route added later automatically inherits the same ISIN+market contract.

Tooltips

Most labels in the result panel — TER, Distribution, Replication, the correlation column headers, momentum signals, the FRED snapshot — carry a small i icon. Hover (or tap on mobile) to see a short explanation: what the field actually means, what units it's in, what range is considered normal. The text lives in data/ui-tips.json, a versioned plain-text file you can edit and commit like any source. No build step, no rebuild required for the dev server to pick up changes.

The icon only renders when a tip exists for that key. Missing keys silently produce no icon, so adding or removing tips is a one-line edit.

What the viewer is not

A few limitations are deliberate, not pending:

• It cannot edit anything. No "update current weight" button, no "rescrape this ETF" trigger. Changes go through the CLI programs.
• It cannot run a report. Producing a report is a multi-minute, multi-LLM pipeline; that lives in Program 2 and is not a web action.
• It is not multi-user. No accounts, no permissions, no audit trail. Designed for single-operator local use.
• It does not chart. Tables only. A correlation heatmap or a performance line chart would be useful, but they're future work.

The viewer exists to answer two questions quickly — "what do we know about this ETF?" and "what's in the universe?" — without becoming a portfolio management application.

Responsive

The page works down to phone-portrait widths. Wide tables (correlations: 8 columns; FRED series: 6; browse: 12) hide their less essential columns on narrow screens rather than scrolling sideways. Sectors and countries collapse into a single column on phones; the form stacks vertically; padding tightens.

Data Sources

Per-ETF (fetched for every ETF)

Macro Context (fetched once)

FRED Economic Data (optional, fetched once)

The 6 series are fetched in parallel via Promise.all, with per-series fallback chains preserved when the daily series has no recent observation (e.g. DGS10 falls back to GS10).

Deep Analysis (fetched for top candidates only)

Macro News (fetched once for final report)

The Report

The final output is a structured Markdown document containing:

1. Signal Summary — count per signal type across the basket
2. Multi-LLM Consensus — how different AI models agree/disagree per ETF
3. Suggested Basket Composition — percentage allocation table for all ETFs; identifier column shows RIC (Market) so cross-listings stay distinguishable
4. Full Ranking — every ETF with signal, confidence, score
5. Final Report — executive summary, buy/sell/hold recommendations, macro view, currency impact, risks, scenarios, rebalancing actions
6. Cross-Correlation Analysis — contradictions, patterns, insights
7. Per-ETF Analysis — individual signal details for every ETF; each section header carries RIC (Market)
8. Deep Analysis — detailed breakdown of top candidates
9. Current Portfolio Weights — existing holdings (if provided)
10. Self-Critique — the AI's own assessment of report quality

Risk Levels

The basket composition adjusts to five risk profiles:

Within each tier, the AI overweights ETFs with BUY signals and underweights those with SELL signals, while respecting correlation constraints (don't stack correlated assets) and concentration limits (2-25% per ETF). High-correlation pairs flagged by the quant engine should not both be near maximum weight; the prompt notes that correlations are intra-market only, so cross-listings of the same RIC on different markets count as independent positions.

Signal Drift

Each run is saved to a history folder. Subsequent runs compare signals against the previous run for the same AI model:

• Upgrades: signal improved (e.g. HOLD → BUY)
• Downgrades: signal worsened (e.g. BUY → SELL)
• Confidence changes: same signal, different conviction
• New/Removed: ETFs added to or removed from the watchlist for this run

Signal matching uses composite (ric, market) keys, so a query for SWDA-XETR will never silently match against SWDA-BIT in the previous run — feeding the AI the wrong rationale for a cross-listed fund would cause it to hallucinate explanations for a "change" that never happened.

The AI is shown its previous signal and rationale for each ETF and must explain any changes. This prevents random flip-flopping and creates accountability across runs.

Multi-Model Consensus

When multiple AI providers are available, MosAIc automatically uses them all:

• The main model (user-selected) runs the full pipeline
• All other models independently analyze each ETF
• A local resolver merges signals with a configurable strategy:
  • Cautious (default): on disagreement, pick the signal closest to HOLD
  • Majority: most common signal wins

Agreement levels:

This catches single-model blind spots and provides a natural confidence calibration. UNKNOWN confidence (when the parser can't extract a value from a model's response) is preserved as its own category, not silently collapsed to LOW — so the operator can spot extraction failures rather than mistakenly trust a fake "low confidence" signal.

Resilience

MosAIc is designed to run unattended:

• Retry with back-off — transient errors (timeouts, rate limits) are retried automatically with progressive delays.
• Server crash recovery — if a local AI server crashes mid-run, MosAIc polls until it comes back (up to 2 minutes), then continues.
• Payload shrinking — if the AI can't handle the full context, MosAIc progressively truncates the news data (90% → 80% → ... → 10%), cutting at clean sentence boundaries.
• Graceful degradation — if all retries fail for one ETF, it gets a default HOLD/LOW signal and the pipeline continues.
• Source resilience — if a web source returns 403 or times out, the agent skips it and works with the remaining sources.
• TDZ-safe HTTP — synchronous failures in the LLM and MCP HTTP clients (e.g. malformed hostname) are caught and rejected immediately rather than hanging until a misleading "timeout" fires.
• Two-program resilience — if the AI is down or expensive, the database still updates. Multiple reports can be generated from the same data snapshot with different AI providers or risk levels.

Quantitative Layer

MosAIc computes these indicators locally, without AI involvement:

Pearson Correlation — pairwise correlation on shared performance periods (1M, 3M, 6M, 1Y, 3Y, 5Y, YTD), partitioned by market. Combined with an asset class similarity matrix: 60% empirical data, 40% structural expectation.

Holdings Overlap — normalized name matching across top-10 holdings to detect hidden concentration. "SWDA and TRET share 2 holdings worth 1.2% combined weight."

Momentum Score — three-dimensional signal per ETF:

• Short vs long: is 1M annualized above or below 1Y?
• Acceleration: is the monthly rate increasing or decreasing?
• Consistency: are all periods positive, all negative, or mixed?

FRED Derived Signals — yield curve spread (recession indicator), VIX regime classification (calm/normal/elevated/crisis), real yield (bond attractiveness vs inflation).

Currency Exposure — per-ETF breakdown of currency sensitivity using country allocation data. EUR-denominated bonds score NONE, gold/commodities score HIGH (USD-priced), equity ETFs are computed from country weights. Combined with FRED EUR/USD rate for directional context: "SWDA has 66% USD exposure; at EUR/USD 1.17, a further 3% EUR appreciation would cost ~2% in EUR-denominated returns."

These computed facts anchor the AI's reasoning. The prompts explicitly tell the AI: "these are computed data — cite them directly, do not contradict them."

Tests

Regression tests live in __tests__/ as standalone Node scripts — no test framework, just plain console.log assertions. Six test files cover the most dangerous classes of bugs that the migration addressed:

• scoring.test.js — UNKNOWN confidence preservation
• db-coerce.test.js — fund-size unit handling at the DB boundary
• quant.test.js — intra-market correlation isolation
• history.test.js — composite-key drift matching, including the "wrong-market guard" that prevents feeding the AI rationale from the wrong cross-listing
• http-tdz.test.js — synchronous transport throws caught cleanly
• fred-parallel.test.js — Promise.all parallelism + per-series fallback chain

A small runner (__tests__/run-all.js) spawns each test in its own Node process to prevent state-mutation tests (HTTP monkey-patching, config mutation) from leaking between runs.

Future Enhancements