feat(db): full SQLite cache exploitation — enrichment, dedup-aware search, cache CLI, then watch + semantic search #33

Overview

This issue tracks a phased plan to make the local SQLite cache a first-class citizen of the MOSAIC workflow. The four phases build on each other in strict order; phases 3 and 4 are prerequisites for issues #17 and #21.

Phase 3 (enrichment) → Phase 5 (dedup-aware search) → Phase 4 (cache CLI + export tracking + PDF sync) → #17 (watch) / #21 (semantic search)

The numbering 3/5/4 follows the internal roadmap where phases 1 (cache-first get) and 2 (--cached search) are already shipped.

Phase 3 — Smart result enrichment

Problem: Cache.upsert() currently overwrites existing rows unconditionally. A re-search of the same topic silently discards richer data already stored (e.g. a longer abstract from OpenAlex gets replaced by a stub from arXiv).

Goal: merge incoming paper data with the cached version, keeping the best of each field.

Merge strategy

Changes

• db.py — add Cache.enrich(paper: Paper) -> bool that loads the existing row, applies the merge rules above, and upserts only when at least one field improved; returns True if a write occurred
• search.py — call cache.enrich(p) instead of the current unconditional cache.upsert(p) after search_all() returns
• downloader.py — after a successful download, call cache.enrich(Paper(pdf_url=…, …)) so the pdf_url is always up to date

Docs — Phase 3

No new user-facing flags. Update the following to reflect the improved cache behaviour:

• docs/guide/usage.md — add a note under "Offline / cached search" explaining that repeated searches progressively enrich the local library (longer abstracts, higher citation counts are retained)
• docs/guide/cli-reference.md — no flag changes; add a sentence to the search command description noting that results are merged into the cache with best-field-wins semantics

Phase 5 — Dedup-aware / cache-skipping search

Problem: every mosaic search re-fetches all sources even when the local cache already holds fresh, complete records for matching papers.

Goal: allow search_all() to short-circuit sources for papers whose cache entry is already rich enough, without breaking the fan-out model.

Proposed behaviour

# new flag: skip sources for papers already cached with full metadata
mosaic search "attention mechanism" --prefer-cache

# combine with existing flags as usual
mosaic search "diffusion models" --prefer-cache --oa-only --sort citations

--prefer-cache is opt-in (not the default) so existing behaviour is unchanged.

Implementation notes

• db.py — add Cache.is_rich(uid: str) -> bool: returns True when the cached paper has abstract (non-empty), citation_count (non-null), and either pdf_url or is_open_access=False
• search.py — before fanning out to sources, run a cache.search_local(query) pass; collect uids already marked as is_rich; after each source returns results, filter out papers whose uid is in the rich set before merging
• services.py — expose a RichUidSet helper (a frozenset[str]) so filtering is O(1)
• Sources that are purely additive (arXiv, DOAJ, bioRxiv) are always queried regardless; only high-latency / quota-constrained sources (Semantic Scholar, Springer, IEEE…) benefit from skipping

Docs — Phase 5

• docs/guide/usage.md — add a "Prefer cache" subsection under "Offline / cached search":
  • explain --prefer-cache: skips already-rich papers from high-latency sources, instant for the cached subset
  • note which sources are always queried regardless (arXiv, DOAJ, bioRxiv)
  • show a combined example: mosaic search "transformers" --prefer-cache --oa-only --sort citations
• docs/guide/cli-reference.md — add --prefer-cache row to the search options table with tab-complete note (flag, no value)
• README.md — add --prefer-cache to the search examples block alongside --cached

Phase 4 — mosaic cache subcommand + export tracking + PDF sync

Goal: give users visibility into and control over the local SQLite cache via a dedicated CLI, track all artifact exports, and keep PDF download records in sync with the actual filesystem.

PDF sync strategy

The downloads table is the only part of the DB that holds a pointer to the filesystem (local_path). When the user moves or deletes PDFs outside MOSAIC, the DB silently holds stale status="ok" rows — causing mosaic cache list --downloaded to lie and blocking re-downloads.

Two complementary layers:

Layer A — self-healing on access (always on, zero overhead)
Cache.get_download() is called every time the downloader checks whether a file already exists. Extend it to verify Path(local_path).exists() when status="ok" and auto-reset to status="missing" if the file is gone. This heals individual rows transparently as the user re-encounters those papers — no full sweep, no startup cost.

Layer B — mosaic cache verify (explicit, full sweep)
Walks every status="ok" row in downloads, calls Path.exists() on each, and resets stale paths to status="missing". Prints a summary table of what was found and fixed. Intended to be run manually after a bulk mv or rm, not automatically at startup (would be too slow for large libraries).

Together these mean: normal daily usage self-heals naturally (Layer A), and users who reorganise their PDF folder can repair the whole DB in one command (Layer B).

Known bug: --download-dir override ignored for already-cached papers

There is a pre-existing bug in downloader.py that must be fixed as part of this phase:

rec = cache.get_download(paper.uid)
if rec and rec["status"] == "ok" and Path(rec["local_path"]).exists():
    return rec["local_path"]   # ← returns immediately without checking download_dir

If a paper was previously downloaded to ~/mosaic-papers/foo.pdf (the default dir) and the user later runs:

mosaic get 10.xxxx/yyyy --download-dir ~/project/papers/

the downloader finds status="ok" and Path.exists() == True, and silently returns the old path — the --download-dir override is completely ignored. The user gets a file in the wrong location with no warning.

Fix: before returning the cached path, check whether it lives inside the requested download_dir. If not, proceed with the download to the requested dir and update the downloads row with the new path. The old file is left in place (MOSAIC never deletes files).

rec = cache.get_download(paper.uid)
if rec and rec["status"] == "ok" and Path(rec["local_path"]).exists():
    cached_path = Path(rec["local_path"])
    if cached_path.parent.resolve() == Path(download_dir).resolve():
        return str(cached_path)   # same dir — cache hit is valid
    # different dir — fall through and download to the requested location

This fix also applies to the bulk download path in _bulk_download() in cli.py.

New exports table

Currently the DB tracks only downloaded PDFs (downloads table). Zotero pushes, BibTeX/CSV/JSON/Markdown/Obsidian writes are fire-and-forget — MOSAIC cannot tell whether a paper was already exported to Zotero, is already in refs.bib, or already has an Obsidian note. This causes silent duplicates on re-runs.

New table added in db.py:

CREATE TABLE IF NOT EXISTS exports (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    uid          TEXT NOT NULL,
    format       TEXT NOT NULL,   -- 'zotero', 'bibtex', 'csv', 'json', 'md', 'markdown', 'obsidian', 'ris'
    destination  TEXT,            -- file path, Zotero collection name, Obsidian folder, etc.
    exported_at  TEXT DEFAULT (datetime('now')),
    UNIQUE(uid, format, destination)
);

New Cache methods:

• Cache.record_export(uid, format, destination) — upserts a row (ON CONFLICT DO UPDATE SET exported_at)
• Cache.was_exported(uid, format, destination) -> bool — check before re-exporting
• Cache.list_exports(uid) -> list[dict] — show all export history for a paper

Wire into existing code:

• workflows.py — call cache.record_export() after every successful Zotero push and Obsidian write
• cli.py — call cache.record_export() after every --output file write
• Optionally skip re-export with a --skip-exported flag (opt-in, same pattern as --prefer-cache)

Commands

# overview
mosaic cache stats                      # row counts, DB size, top sources, date range,
                                        # + export summary (N papers exported to Zotero, M to BibTeX…)

# inspection
mosaic cache list                       # paginated Rich table of all cached papers
mosaic cache list --source arxiv        # filter by source
mosaic cache list --oa-only             # filter to OA papers
mosaic cache list --downloaded          # only papers with a local PDF (verified path)
mosaic cache list --exported zotero     # only papers already pushed to Zotero
mosaic cache list --query "CRISPR"      # keyword filter (same as --cached search)

# single-paper lookup
mosaic cache show 10.48550/arXiv.1706.03762   # full metadata + download status + export history

# maintenance
mosaic cache verify                     # full sweep: check all downloads.local_path entries,
                                        # reset stale paths to status="missing", print summary
mosaic cache clean                      # remove rows with no abstract AND no pdf_url AND no doi
mosaic cache clean --dry-run            # show what would be removed
mosaic cache clear                      # ⚠ wipe entire cache (prompts for confirmation)

# export (bridges to #23)
mosaic cache export --output dump.bib          # all cached papers to BibTeX
mosaic cache export --query "transformers" -o refs.json

Implementation notes

• New mosaic/cache_cmd.py module with a cache_app = typer.Typer() sub-application
• Registered in cli.py via app.add_typer(cache_app, name="cache")
• stats output is a Rich table (same style as --stats in search): source breakdown, total rows, DB file size, oldest/newest entry date, export summary per format
• list reuses the existing search results table renderer; --downloaded only shows rows where Path(local_path).exists() (i.e. uses Layer A implicitly)
• clean uses Cache.is_rich() (from Phase 5) inverted — removes only truly empty stubs
• export delegates to the existing exporters in mosaic/export.py (or wherever feat: mosaic export --format ris — export results in RIS/BibTeX/CSV formats #23 lands its writers)

Docs — Phase 4

This phase introduces the most user-visible changes and requires the most documentation work.

New page: docs/guide/cache.md

A dedicated guide covering the full mosaic cache subcommand:

• Intro: what the local cache is, where it lives (~/.local/share/mosaic/cache.db), what it stores
• mosaic cache stats — annotated example output
• mosaic cache list — all filter flags with examples
• mosaic cache show <doi> — example showing metadata + download status + export history
• mosaic cache verify — when and why to run it; example output after a bulk mv
• mosaic cache clean / mosaic cache clear — difference between the two; dry-run usage
• mosaic cache export — formats, query filter, combining with --output
• Export tracking — explain the exports table: what gets recorded, --skip-exported flag
• PDF sync — explain Layer A (transparent) and Layer B (verify); describe the --download-dir bug fix

docs/guide/usage.md

• Add --prefer-cache to the "Offline / cached search" section (from Phase 5 docs)
• Add a new "Cache management" section pointing to the dedicated cache.md guide
• Add --skip-exported flag to the export and Zotero/Obsidian sections
• Update "Download by DOI" and "Bulk download" sections to note the --download-dir bug fix

docs/guide/cli-reference.md

• Add full mosaic cache subcommand table (all flags for stats, list, show, verify, clean, clear, export)
• Add --prefer-cache to the search options table
• Add --skip-exported to the search and get options tables
• Note tab-completion where applicable (--exported completes format names)

docs/.vitepress/config.ts (or equivalent)

• Add cache.md to the sidebar under the Guide section, after usage.md

README.md

• Add mosaic cache stats and mosaic cache verify to the quick-start examples block
• Mention export tracking briefly in the "Key features" table (under "Offline-first cache")

Phase 4+ — Prerequisites satisfied for #17 and #21

Once phases 3, 5, and 4 are in place:

Issue #17 — mosaic watch

The watches SQLite table + watch run deduplication logic can be built cleanly on top of Cache.is_rich() and the enrichment pipeline:

• watch run fans out as normal, but compares result uids against the pre-run cache snapshot — only papers not in the cache at run-start are "new"
• watch run --prefer-cache skips re-fetching known-rich papers, making watches cheap to run frequently

Issue #21 — local semantic search

The embedding pipeline needs a stable, well-populated abstracts column — guaranteed by Phase 3's enrichment merge:

• Phase 3 ensures abstracts are never silently downgraded, so embeddings built from them remain valid
• Phase 4's mosaic cache stats can report embedding coverage (N / M papers have embeddings)
• mosaic cache rebuild-index (a natural extension of mosaic cache) can trigger a full re-embedding run

Test suite

Each phase ships with its own test file. All tests follow existing conventions: unittest.mock for network/IO, tmp_cache and tmp_path fixtures, no real network calls.

tests/test_cache_enrich.py — Phase 3

class TestEnrich:
    def test_longer_abstract_wins()
    def test_shorter_abstract_not_stored()
    def test_none_abstract_not_overwritten()
    def test_none_abstract_filled()
    def test_higher_citation_count_wins()
    def test_lower_citation_count_ignored()
    def test_none_citation_count_fills()
    def test_citation_count_not_reset_to_none()
    def test_pdf_url_kept_if_set()
    def test_pdf_url_filled_if_empty()
    def test_is_open_access_true_wins()
    def test_is_open_access_false_not_overwrite_true()
    def test_journal_filled_if_empty()
    def test_journal_not_overwritten()
    def test_volume_issue_pages_same_rule()
    def test_longer_authors_list_wins()
    def test_shorter_authors_list_ignored()
    def test_year_not_overwritten()               # first seen wins
    def test_enrich_returns_true_on_improvement()
    def test_enrich_returns_false_if_no_change()  # no DB write
    def test_enrich_on_unknown_uid_inserts_row()

class TestEnrichWiredIntoSearch:
    def test_search_all_enriches_existing_cache_entry()
    def test_search_all_does_not_downgrade_abstract()
    def test_search_all_does_not_lower_citation_count()

class TestEnrichWiredIntoDownloader:
    def test_successful_download_updates_pdf_url_in_cache()
    def test_pdf_url_not_cleared_on_enrichment_with_none()

tests/test_cache_prefer.py — Phase 5

class TestIsRich:
    def test_rich_paper_has_abstract_citations_and_pdf_url()
    def test_rich_paper_has_abstract_citations_and_is_closed_access()
    def test_not_rich_if_abstract_missing()
    def test_not_rich_if_citation_count_missing()
    def test_not_rich_if_no_pdf_url_and_oa_unknown()
    def test_empty_abstract_string_not_rich()     # "" must not count as rich
    def test_unknown_uid_not_rich()

class TestPreferCache:
    def test_rich_uid_excluded_from_source_fanout()
    def test_non_rich_uid_still_fetched_from_sources()
    def test_additive_sources_always_queried_even_if_rich()  # arXiv, DOAJ, bioRxiv never skipped
    def test_prefer_cache_returns_cached_paper_for_rich_uid()
    def test_prefer_cache_false_always_queries_all_sources()
    def test_rich_set_is_frozenset_of_uids()      # O(1) membership check

class TestRichUidSetHelper:
    def test_empty_cache_returns_empty_set()
    def test_only_rich_papers_included()
    def test_non_rich_papers_excluded()

tests/test_cache_sync.py — Phase 4 (PDF sync)

class TestLayerA:
    def test_returns_path_when_file_exists()
    def test_resets_to_missing_when_file_gone()
    def test_missing_status_then_redownloads()
    def test_non_ok_status_not_affected()
    def test_empty_local_path_not_affected()      # error rows with "" path: no Path.exists() call

class TestLayerB:
    def test_verify_marks_stale_paths_as_missing()
    def test_verify_leaves_valid_paths_untouched()
    def test_verify_returns_count_of_stale_and_valid()
    def test_verify_handles_empty_downloads_table()
    def test_verify_dry_run_does_not_write()

class TestDownloadDirOverrideBug:
    def test_cached_path_in_same_dir_is_cache_hit()
    def test_cached_path_in_different_dir_is_cache_miss()
    def test_re_download_updates_local_path_in_db()
    def test_old_file_left_on_disk_after_dir_change()
    def test_bulk_download_respects_dir_override()
    def test_symlinked_dirs_resolve_correctly()   # Path.resolve() used for comparison

tests/test_cache_exports.py — Phase 4 (export tracking)

class TestExportsTable:
    def test_record_export_inserts_row()
    def test_record_export_is_idempotent()        # ON CONFLICT → updates exported_at, no duplicate
    def test_was_exported_true_after_recording()
    def test_was_exported_false_before_recording()
    def test_was_exported_false_for_different_destination()
    def test_list_exports_returns_all_for_uid()
    def test_list_exports_empty_for_unknown_uid()
    def test_distinct_formats_tracked_independently()

class TestExportTrackingWired:
    def test_zotero_push_records_export()
    def test_obsidian_write_records_export()
    def test_output_file_write_records_export()
    def test_failed_zotero_push_does_not_record()
    def test_export_destination_stored_correctly()

class TestSkipExported:
    def test_skip_exported_skips_already_exported_paper()
    def test_skip_exported_still_exports_new_papers()
    def test_skip_exported_false_re_exports_all()

tests/test_cache_cmd.py — Phase 4 (CLI subcommand)

Uses typer.testing.CliRunner throughout.

class TestCacheStats:
    def test_stats_shows_row_count()
    def test_stats_shows_source_breakdown()
    def test_stats_shows_export_summary()
    def test_stats_empty_db_does_not_crash()

class TestCacheList:
    def test_list_returns_all_papers()
    def test_list_filter_by_source()
    def test_list_filter_oa_only()
    def test_list_filter_downloaded_only_shows_existing_files()
    def test_list_filter_downloaded_excludes_missing_files()  # Layer A fires here
    def test_list_filter_exported_zotero()
    def test_list_query_filter()
    def test_list_empty_db_prints_nothing_gracefully()

class TestCacheShow:
    def test_show_existing_doi_prints_metadata()
    def test_show_includes_download_status()
    def test_show_includes_export_history()
    def test_show_unknown_doi_exits_with_error()

class TestCacheVerify:
    def test_verify_reports_stale_count()
    def test_verify_reports_valid_count()
    def test_verify_dry_run_output_no_db_change()
    def test_verify_empty_table_exits_cleanly()

class TestCacheClean:
    def test_clean_removes_empty_stubs()
    def test_clean_keeps_rich_papers()
    def test_clean_dry_run_shows_candidates_only()
    def test_clean_dry_run_does_not_delete()

class TestCacheClear:
    def test_clear_wipes_all_papers()
    def test_clear_requires_confirmation()        # no "y" → aborted, nothing deleted
    def test_clear_yes_deletes_everything()

class TestCacheExport:
    def test_export_bibtex_writes_file()
    def test_export_json_writes_file()
    def test_export_with_query_filter()
    def test_export_empty_cache_writes_empty_file()

Existing tests to update

Coverage targets

Checklist

• Phase 3 — Cache.enrich() merge strategy + tests
• Phase 3 — wire enrichment into search_all() and downloader
• Phase 3 — docs: usage.md cache enrichment note, cli-reference.md search description
• Phase 5 — Cache.is_rich() predicate + tests
• Phase 5 — --prefer-cache flag on mosaic search
• Phase 5 — source-skipping logic in search_all()
• Phase 5 — docs: usage.md prefer-cache section, cli-reference.md flag row, README.md example
• Phase 4 — fix --download-dir override ignored for cached papers (downloader + bulk)
• Phase 4 — Layer A: self-healing Cache.get_download() (auto-reset missing paths on access)
• Phase 4 — Layer B: mosaic cache verify full sweep command
• Phase 4 — new exports table + Cache.record_export() / was_exported() / list_exports()
• Phase 4 — wire record_export() into workflows, CLI output writers
• Phase 4 — mosaic cache stats (incl. export summary)
• Phase 4 — mosaic cache list with filters (incl. --downloaded, --exported)
• Phase 4 — mosaic cache show <doi> (incl. export history)
• Phase 4 — mosaic cache clean [--dry-run] / mosaic cache clear
• Phase 4 — mosaic cache export
• Phase 4 — docs: new docs/guide/cache.md page, update usage.md, cli-reference.md, README.md, VitePress sidebar
• Update test_db.py and test_downloader.py for changed upsert/cache-hit behaviour
• feat: mosaic watch — saved searches with change detection #17 and feat: local semantic search over cached abstracts #21 can be tackled independently once this issue is closed