Although existing Tripal modules can host and display various biological -omics datasets, integrating data from the different modules is not straightforward. The underlying Chado schema is highly flexible and normalized. Unlike the more common Entity-Relation, Chado follows the Entity-Attribute-Value Model database model[87]. The data model relies on biological ontologies, which are loaded as data, to define datatypes and relationships. The different loaders for each module populate database tables, which may be scattered, duplicated and confusing in their utility and terminologies.

An additional complication is that Tripal introduces a layer of abstraction for biological entities on top of the storage tables in the Chado schema. Using ontology terms as type identifiers, a Chado table can store records for different biological entity types. Table 6 lists the default Tripal entities and their corresponding Chado tables and types. The Tripal BioEntity objects and Chado relational table mappings are illustrated in Figure 2. The feature, analysis and stock tables are used in common by multiple Tripal BioEntity types. Data management can be challenging when mapping the relationships between distinct bioentities because they are not equivalent to the entity-relationship defined by the Chado schema. Another challenge is the requirement to manually synchronize the Chado records and the Tripal entities when loaders are not used There are synchronization buttons in Tripal to publish Chado records to Tripal, unpublish deleted records, or delete unpublished bioentities. Using these features requires thorough understanding and experience on the datasets and platform.

Several solutions are implemented to overcome the challenges encountered. The integration process is built on top of existing Tripal modules, specifically for the ‘chado_gene_search’, ‘expression’, and ‘phenotype’ modules. These modules generate materialized views from complex queries used in the visualizations. The -omics integration API queries these materialized views by SQL. Merging from the different modules is implemented using table pivot. All queries return a triplet of property, biomaterial and value. A pivot function then transforms them into a table with the biomaterials as column labels, and properties as row labels. Figure 3 illustrates how the modules are linked for integration. Biomaterial labels can be from ‘dbxref’, ‘biomaterial’ or ‘stock’ tables. Properties are from ‘cvterm’ (sample metadata, traits) or ‘feature’ (genes, transcripts, proteins). The values are collected from any of the other tables following their references. Controlled terms are defined in the ‘cvterm’ table, but their assignment to a specific biomaterial and property are in their datatype-specific tables. The generic -omics query result table should be able to handle any of the dataset types, factors, and variable and values in Table 7.

The datasets are linked by sample IDs defined by their NCBI BioSample whenever available. Publications are required to define SAMN sample IDs when they submit sequences to NCBI, including NGS genotyping and RNA-Seq studies.

We also identified several ambiguous usage of terminology while integrating the Tripal modules. Table 8 summarizes the exact definition of tables mentioned as defined in Chado, or BioEntities as defined in Tripal.

For the ‘stock’ and ‘biomaterial’ tables, these appear to hold similar data. However, from the Chado definition, ‘stock’ represents the physical entity of an organism identified by unique name and stock type that is held within a living or preserved collection. In contrast, ‘biomaterial’ is a biological material (tissue, cells or serum) that may have been processed. Biomaterials should be traceable to source raw biomaterial, recognizing that unless vegetatively cloned, individual plants of a dioecious outcrossing species are likely to have different genetic makeup (haplotype). The Expression module uses the Biomaterial type, while the Phenotype module uses the Germplasm Accession type to represent the samples. Merging expression with phenotype data needs a link between stock and biomaterial which is not explicitly defined in Chado. The workaround implemented in the project is to use the same property and value (NCBI BioSample ID) in the stockprop and biomaterialprop tables. However, BioSample (SAMN) IDs may not be available since only sequences submitted to NCBI or other INDSC repositories have them. In this situation we assigned stocks/biomaterial ID using the simplest unique name deducible from the source publication.

Issues in the ‘organism’ and ‘genome assembly’ entities were also encountered. Although the portal handles only Cannabis sativa L. as the target organism, the ‘organism’ entity is used in Chado and Tripal to define a set of contigs and genes for a given genome. Thus, the different genome assemblies for cs10, PK and FN are stored as distinct records in the organism table. In Tripal, the Genome Assembly entity is defined as a computational analysis for a genome assembly in the analysis table. As implemented in the project, we loaded the different assemblies as separate organisms in the organism table, then modified all Organism labels to Assembly.

For the entities ‘Project’, ‘Study’, ‘Experiment’ and ‘Analysis’, by Chado definition, “Study” represents an experiment that produced microarray data; “Project” is a flexible property table for projects; “Analysis” is a single unit of computational analysis; “Assay” consists of a physical instance of an array, when combined with conditions create an Array.

The different types of computational analysis are reflected in Tripal by using the specific analysis entities. Currently defined are Genome Annotation, Genome Assembly, BLAST Results, InterPro Results. The phenotype module loads the experiment (Experiment is used in the label) to the Project table and is associated to a genus and the samples to Germplasm Accessions. A genus is defined in the phenotype configuration to consist of a controlled vocabulary of Trait, Method and Unit, or optionally by Crop Ontology. “Germplasm Accessions” is a type of stock in Tripal so phenotype samples are loaded to the stock table. Phenotype studies are added to the Project table. The Expression and Biomaterials loader in the expression module associates the data to an Analysis and Organism, which was used earlier for assemblies.

The interchanging use of terms made it confusing to merge samples between different modules, and our workaround was to use common terms for both the stocksprops and biomaterialprops table.

The Tripal genotype module, which uses the Natural Diversity tables [88], was tested and found to be performing sub optimally especially when loading large genotyping data matrices generated from NGS variant calling. To solve the speed issue, the SNP-Seek software system [65], developed to host large SNPs datasets, was instantiated in a podman container and embedded as a Tripal page. This feature, now named CannSeek database, is described in the accompanying paper. For the API calls to query SNPs, BCFtools (RRID:SCR_005227) [89] was used directly on the VCF files stored separately for each reference and dataset. The genotyped samples metadata are loaded to the ‘biomaterial’ table so they can be autoloaded from NCBI BioSample.

Some issues for the entities ‘gene’, ‘mRNA’, and ‘protein’ were also resolved. The central dogma of molecular biology relations is well-defined in the Tripal model and all are stored in the Chado feature table by different feature types. Their relationships are explicitly related as: mRNA part_of Gene, Exon part_of mRNA, Polypeptide derives_from mRNA. BLAST and InterPro Analysis Results were assigned to the specific molecule type used (i.e. DNA, mRNA or polypeptide). In the ‘chado_gene_search’ module, keyword search was modified to include the annotations assigned to all genes, mRNA and proteins by tracing their relationships. However, it became challenging to cross- reference genes between external databases and applications. The accessions or IDs will be different for the same gene but different molecule types. In addition, a gene locus can be associated with multiple mRNAs and proteins. To avoid the complicated query, a materialized view was created to map the corresponding genes, mRNA and polypeptides.

Replicates are challenging to handle when samples are measured, analyzed, and reported on multiple data types. Replicates may be biological, (i.e., multiple studies using the same plant source/genebank accession, multiple individuals in a study) or technical (i.e., multiple samples from a tissue with the same treatments). In NCBI, a biomaterial in BioSample (SAMN) can have multiple sequence replicates in SRA (SRR). Replicated NGS reads can be variant-called separately or jointly for a given SAMN. The measurement of metabolites for a SAMN can also have technical replicates. Some phenotype datasets loaded from publications report measurements per replicate [53], while others the mean and standard deviation [54]. The Tripal phenotype module documentation suggests loading the individual replicate data [88]. However, their proposed use-case is focused on geolocational and seasonal factors, which may be more applicable for a single project or institute, but not for a community portal that integrates from various data sources. These replicates create a Cartesian join between biomaterials and properties, that is, rows or columns will be duplicated except on one element. For this reason, we use average and standard deviation for technical replicates from published datasets. We pre-calculated these statistics before loading when only replicate values were available. It is also possible to have an array of values for a given biomaterial and property without a traceable replicate number. For publications that report in this format, the system can still store them and report in JSON format.