Lda Result Database Persistence

---
name: lda-result-database-persistence
description: Use when after running gensim LDA on a corpus of MS2 fragmentation features,
  when you need to store the LDA results (topics, document-topic assignments, term-topic
  distributions) in a PostgreSQL database so they can be queried and visualized by
  a Django web application or other downstream consumers.
license: CC-BY-4.0
metadata:
  edam_operation: http://edamontology.org/operation_3096
  edam_topics:
  - http://edamontology.org/topic_3520
  - http://edamontology.org/topic_0092
  tools:
  - Python
  - Docker
  - PostgreSQL
  - gensim
  - Django
  techniques:
  - LC-MS
  license_tier: open
  provenance_tier: literature
derived_from:
- doi: 10.1073/pnas.1608041113
  title: MS2LDA
evidence_spans:
- pipenv --python 2.7
- docker run --name some-redis
claims: []
provenance:
  collection: https://w3id.org/holobiomicslab/asb-skill/collection/metabolomics/v2
  assembled_by: scripts/collect_metabolomics_collection.py
  sources:
  - build: coll_ms2lda_substructure_discovery_mass2motif_cq
    doi: 10.1073/pnas.1608041113
    title: MS2LDA
  dedup_kept_from: coll_ms2lda_substructure_discovery_mass2motif_cq
schema_version: 0.2.0
attribution:
  generator: AgenticScienceBuilder
  original_doi: 10.1073/pnas.1608041113
  all_source_dois:
  - 10.1073/pnas.1608041113
  zenodo_doi: 10.5281/zenodo.20794027
  curators: []
  promoter: Louis-Félix Nothias
  sponsor: CNRS & Université Côte d'Azur
---

# lda-result-database-persistence

## Summary

Persists Latent Dirichlet Allocation (LDA) topic modeling results from gensim into a PostgreSQL database for downstream visualization and querying. This skill bridges the output of unsupervised topic inference on metabolomics mass spectrometry data with a persistent backend suitable for web application access.

## When to use

After running gensim LDA on a corpus of MS2 fragmentation features, when you need to store the LDA results (topics, document-topic assignments, term-topic distributions) in a PostgreSQL database so they can be queried and visualized by a Django web application or other downstream consumers. Use this skill when the LDA result JSON file has been successfully generated and you are ready to make those results accessible to the ms2ldaviz web interface.

## When NOT to use

- The LDA result JSON file has not yet been generated by gensim (run gensim LDA step first).
- PostgreSQL server is not running or is unreachable on the configured host/port.
- You only need to visualize or analyze LDA results locally without persistence to a shared database.

## Inputs

- LDA result JSON file (e.g., myexp.ldaresult.json) from gensim
- PostgreSQL connection credentials (username, password via environment or .env)
- Experiment identifier (string)
- Optional: pre-tarred gensim LDA dataset directory (e.g., myexp.lda.gensim.tar.gz) for insert_gensim workflow

## Outputs

- PostgreSQL database records containing topic models, document-topic matrix, term-topic distributions
- Experiment metadata record linked to LDA results
- Web-accessible LDA visualization data (via Django ORM)
- Optional: tarballed gensim LDA format files for alternative storage

## How to apply

Execute the `./run_gensim.py insert` command with three required parameters: the path to the gensim LDA result JSON file (e.g., myexp.ldaresult.json), a PostgreSQL username (e.g., stefanv), and an experiment identifier (e.g., myexp). The script parses the JSON file containing topic distributions, document-topic allocations, and vocabulary, then transforms and loads these structures into the PostgreSQL schema. Verify successful insertion by confirming that no error messages are returned and that the experiment record is queryable from the database (e.g., via Django ORM or direct SQL query). If using the faster gensim-format insertion workflow, use `./run_gensim.py insert_gensim` instead with a pre-tarred gensim LDA dataset directory (e.g., myexp.lda.gensim.tar.gz) to avoid reprocessing LDA metadata.

## Related tools

- **PostgreSQL** (Database backend for persisting LDA results and experiment metadata)
- **gensim** (Generates LDA topic models and produces result JSON files consumed by insert command)
- **Python** (Language used to implement run_gensim.py insert and insert_gensim commands)
- **Docker** (Container platform for running PostgreSQL service during development and deployment)
- **Django** (Web framework that accesses persisted LDA results via ORM and serves visualizations) — github.com/sdrogers/ms2ldaviz

## Examples

```
./run_gensim.py insert myexp.ldaresult.json stefanv myexp
```

## Evaluation signals

- No error or exception messages returned from the insert command execution.
- Database query confirms presence of experiment record with matching identifier and linked topic/document-topic records.
- LDA topic data (e.g., top terms per topic, document-topic weights) is retrievable via Django ORM or direct SQL.
- Web interface at `/uploads/` or experiment detail page successfully renders LDA visualization (D3 plots, topic tables) without data fetch errors.
- Row count in LDA-related database tables (e.g., topics, documents, document_topics) matches expected cardinality from the input JSON file.

## Limitations

- The insert command requires the PostgreSQL service to be running and credentials to be correctly configured; initialization errors will halt the process without partial rollback.
- The insert workflow is slower than insert_gensim because it reprocesses LDA metadata from the full JSON file; use insert_gensim with pre-tarred gensim datasets for faster batch loading.
- The LDA result JSON schema must match the expected structure output by gensim; mismatched or corrupted JSON files will cause parsing errors.
- No direct conflict resolution is implemented; inserting a second experiment with the same identifier will likely cause key constraint violations or overwrite prior records, depending on schema design.

## Evidence

- [readme] Insert lda result into db: "3. Insert lda result into db"
- [readme] Insert command consumes LDA result JSON and loads into database: "./run_gensim.py insert myexp.ldaresult.json stefanv myexp"
- [readme] insert_gensim command for faster insertion of gensim format LDA results: "./run_gensim.py insert_gensim myexp.corpus.json myexp.lda.gensim stefanv myexp"
- [readme] Gensim results can also be uploaded via web interface by tarring and submitting form: "The gensim result must be tarballed with for example `tar -zcf myexp.lda.gensim.tar.gz myexp.lda.gensim*` and then uploaded in the form."
- [readme] PostgreSQL database required for persistence: "docker run --name some-pg -d -p 5432:5432 -e POSTGRES_PASSWORD=j7z3rL40w9 -e POSTGRES_USER=django postgres"
- [readme] LDA result JSON file from gensim step: "The insert step consumes the LDA result JSON file (myexp.ldaresult.json) and loads it into the database"