# GeneNetwork data types Notes on the core data types GeneNetwork is built around, compiled from `factory/docs/gn-gemtext-threads`, `factory/code/genenetwork2`, `factory/code/genenetwork3`, `factory/code/gn-guile`, and this repo's own `docs/mariadb_db-webqtl.md` schema dump. ## Species The `Species` table (`db_webqtl`; full `CREATE TABLE` in `docs/mariadb_db-webqtl.md`) is the root of GeneNetwork's data hierarchy: every `InbredSet` (a group/population, e.g. BXD), every `Strain`, and every chromosome length record (`Chr_Length`) is scoped to a species via a `SpeciesId` foreign key. ### Fields Field semantics, from `topics/gn-uploader/adding-species.gmi` (written while manually adding C. elegans to the database) and cross-checked against the live schema: | Field | Meaning | |-----------------|-----------------------------------------------------------------------------| | `Id` | Internal species identifier (auto-increment primary key). | | `SpeciesId` | Set equal to `Id` after insert; FKs use this, not `Id` (see Known quirks). | | `SpeciesName` | A layman's name, e.g. "Mouse", "Roundworm", "Barley". | | `Name` | Lowercased short name for URLs/API, e.g. "mouse". | | `MenuName` | UI display name, e.g. "Mouse (Mus musculus, mm10)". | | `FullName` | Binomial (scientific) name, e.g. "Mus musculus". | | `Family` | A loose grouping across species, e.g. "Vertebrates", "Nematodes". | | `FamilyOrderId` | Sort order within `Family`; derivation undocumented (TODO). | | `TaxonomyId` | NCBI Taxonomy ID, e.g. 10090 (mouse), 6239 (C. elegans). | | `OrderId` | Sort order for display; same undocumented TODO as above. | ### Relationships - `InbredSet.SpeciesId` -> `Species` -- every group/population (e.g. BXD) belongs to exactly one species. - `Strain.SpeciesId` -> `Species` -- every strain belongs to exactly one species (`genenetwork2/gn2/base/species.py`'s `TheSpecies`/ `Chromosomes` classes join on this). - `Chr_Length.SpeciesId` -> `Species` -- chromosome names and lengths are per species (used by `gn3.db.species.get_chromosome` and GN2's `Chromosomes.chromosomes`, both selecting from `Chr_Length` joined to either `Species` directly by name, or indirectly via `InbredSet` for cases where only a group name is known). - `GeneIDXRef` cross-references gene IDs between species. GeneNetwork's internal gene identifiers are mouse-centric by convention: GN3's `gn3.db.species.translate_to_mouse_gene_id` translates a rat or human gene ID to its mouse equivalent via this table (returns the ID unchanged if the species is already "mouse"). - `InbredSet.SpeciesId` also scopes authorization: GN3's `gn3/api/case_attributes.py` resolves a species ID from `InbredSet` to build the resource ID used to check a user's access with `gn-auth` (https://github.com/genenetwork/gn-auth), so species is part of GN's permission hierarchy, not just a data-organization concept. - In the RDF/SPARQL metadata graph (`topics/next-gen-databases/gn-classification-scheme.gmi`), species is one of the three levels of GN's resource classification scheme (`gnc:DatasetType`, `gnc:Set`, `gn:Species`), alongside `InbredSet` ("Set") and `DatasetType`. Every member of the `Species` table has a corresponding `gn:Species`-classified RDF resource; `gn-guile`'s `gn/data/species.scm` builds this metadata from SPARQL, separately from the MariaDB `Species` table. ### Reference genomes and coordinate generations Nothing in the schema stores an assembly *identifier*; the only explicit labels are prose in `Species.MenuName` (verified against `db_webqtl` on 2026-11-11): | species | labelled assembly | |---|---| | human | hg19 | | mouse | mm10 | | rat | mRatBN7.2/rn7 | | fly | dm6 | | nematode | ce10 | | arabidopsis | araTha1 | | medaka | Oryl23 | | bat | gsor23 | | turquoise killifish | MPIA_NFZ_2 | | tomato | GCF_001406875.1 | | soybean | US DOE JGI-PGF v2 2021 | | barley, poplar | unlabelled | Every position column exists in **three generations**: `Mb` (current), `Mb_2016`, and a legacy mouse `Mb_mm8` -- the same triplication on `Chr_Length` (`Length`, `Length_2016`, `Length_mm8`), `ProbeSet` (`Chr`/`Mb`, `Chr_2016`/`Mb_2016`) and `BXDSnpPosition`. The labels above do not all agree with the numbers: - **human** is hg19 throughout, in both generations: `Chr_Length` chr1 is 249 250 621 in `Length` and `Length_2016`, and SOX2 sits at chr3 181.429-181.431 Mb (hg19 181 429 788; hg38 would be ~181.71). - **mouse** `Length_2016` chr1 = 197 195 432 (**mm9** exactly), while the current `Length` is 197 426 691, which matches no standard build (mm9 197 195 432, mm10 195 471 971, mm39 195 154 058). The *probes* are mm10-era -- Sox2 at chr3 34.47-35.09 Mb (mm10 ~34.65) -- so the label mm10 is right for `ProbeSet` while `Chr_Length` is not. - **rat** current `Length` chr1 = 282 763 074 (**rn6** exactly), i.e. the column is the older build although `MenuName` claims rn7 (mRatBN7.2, chr1 ~260.5 Mb). Rat probe positions are also internally inconsistent: Sox2's 44 probes span chr2 1.203-117.592 Mb where rn6 Sox2 is ~119 Mb, which reads as unmapped or badly mapped probes rather than one build. - **`Geno`** has a non-null `Mb` for every locus, but `Mb_2016` only for human (all 367 403 rows), mouse (23 609), soybean (4 938) and barley (791); `Mb_mm8`, a mouse-named column, is also populated for barley (791) -- legacy columns reused across species. Practical consequence: a position taken from this database is "GN's current coordinate for that species" and should be quoted with that build, not with a modern assembly. The search index uses the *current* columns (`ProbeSet.Mb`, `Geno.Mb`), so it inherits exactly these builds -- see [search-engines.md](search-engines.md#what-the-index-covers-and-whose-coordinates-it-carries). ### Known quirks - GeneNetwork's genotype tables were originally built mouse-only and still carry that legacy: `topics/gn-uploader/genotypes-assemblies-markers-and-genenetwork.gmi` notes the `Geno` table has assembly-specific, mouse-named columns (`Mb_mm8`, `Chr_mm8`) that don't generalize to other species' genome assemblies. - There is no UI for adding a new species; it's done by hand with `INSERT INTO Species(...)` followed by `UPDATE Species SET SpeciesId=Id WHERE ...` (see `adding-species.gmi` for the full worked example, adding C. elegans). - `FamilyOrderId` and `OrderId` remain undocumented upstream (flagged TODO in `adding-species.gmi`); their exact intended derivation isn't recorded anywhere found in these repos. ### Existing implementations - **GeneNetwork2** (`gn2/wqflask/api/router.py`): `GET /api/v_pre1/species` (all species, columns `Id`/`Name`/`FullName`/ `TaxonomyId`) and `GET /api/v_pre1/species/` (one species, matched against `Name`, `FullName` or `SpeciesName`, same 4-column subset). `gn2/base/species.py`'s `TheSpecies` class also exposes a species' chromosomes via `Chr_Length`. - **GeneNetwork3**: `gn3/db/species.py` has `get_all_species`, `get_chromosome`, `species_name` (species for an `InbredSet` group) and `translate_to_mouse_gene_id`; none of these are exposed as a `Species`-table REST endpoint. `gn3/api/metadata.py`'s `GET /species` and `GET /species/` are SPARQL-based RDF metadata lookups instead, a different data source from the MariaDB `Species` table. - **gn-guile** (`gn/data/species.scm`): SPARQL/RDF metadata only, same category as GN3's `/species` above, not backed by `db_webqtl`. - **GeneNetwork4** (this repo): `GET /species` (all species, mirrors GN3's `get_all_species` query/ordering) and `GET /species/{species}` (one species, every column, same match logic as GN2's `get_species_info` -- see `README.md`). ## InbredSet The `InbredSet` table (full `CREATE TABLE` in `docs/mariadb_db-webqtl.md`) represents a group/population of genetically related individuals within a species, e.g. BXD (a mouse recombinant inbred panel). Every dataset (`PublishFreeze`, `GenoFreeze`, `ProbeFreeze`/`ProbeSetFreeze`) belongs to exactly one `InbredSet`. **A good alias for `InbredSet` is "Group"** -- this is the term used consistently across GeneNetwork's own code and docs, not an invention for this document: - GeneNetwork2's REST API names the endpoints `GET /api/v_pre1/groups` and `GET /api/v_pre1/group/` (`gn2/wqflask/api/router.py`), mirroring `/species`/`/species/` one level down. - GeneNetwork2's `DatasetGroup` class (`gn2/base/data_set/datasetgroup.py`) docstring: "Each group has multiple datasets; each species has multiple groups. For example, Mouse has multiple groups (BXD, BXA, etc)." - The variable name `group_name` (and `self.group`) appears throughout both GeneNetwork2 and GeneNetwork3 wherever an `InbredSet.Name` is passed around. - `topics/documentation/gn-hacking-documentation.gmi` has a "Groups" heading stating plainly: "These are in the *InbredSet* table in the database." "RISet" (Recombinant Inbred Set) shows up as a secondary, more specialist alias -- GeneNetwork2's API docs (`api_readme.md`) call the same thing "Groups/RISets" and use `group/riset` interchangeably in prose, and GeneNetwork3's `gn3.db.species.species_name` docstring reads "given the group (RISet)". "Group" is the more broadly understood term and the one used in user-facing API routes and UI text; "RISet" is closer to the underlying genetics (not every `InbredSet` is strictly a recombinant inbred set -- `GeneticType` also records "intercross" populations -- so "RISet" is a slight misnomer for the general case). ### Fields | Field | Meaning | |-------------------|---------------------------------------------------------------------------| | `Id` | Internal group identifier (auto-increment primary key). | | `InbredSetId` | Set equal to `Id` after insert; FKs (Publish/Geno/ProbeFreeze) use this. | | `InbredSetName` | UI display name -- GN2 calls this `DisplayName` in JSON -- e.g. "BXD". | | `Name` | Short name for URLs/API, e.g. "BXD", "B6D2F2"; usually = `InbredSetName`. | | `SpeciesId` | FK to `Species.SpeciesId`; a group belongs to exactly one species. | | `FullName` | Longer descriptive name, e.g. "BXD RI Family", "BXD Family". | | `public` | Numeric visibility level (0-2 seen); threshold semantics undocumented. | | `MappingMethodId` | Mapping tool offered, if any; see footnote below the table. | | `GeneticType` | Breeding scheme, e.g. "riset" (as in BXD) or "intercross" (F2/AIL). | | `Family` | Loose grouping across groups, parallel to `Species.Family`. | | `FamilyOrder` | Sort order within `Family` (no `Id` suffix, unlike `Species`). | | `MenuOrderId` | Sort order for display in the group menu. | | `InbredSetCode` | Short code, e.g. "BXD", "AXB"; used in precompute/genotype naming. | | `Description` | Free-text description of the group. | `MappingMethodId` is a key into the `MappingMethod` table (`Id`, `Name`): 1 `qtlreaper`, 2 `Rqtl`, 3 `HappyR`, 4 `PLINK`, 5 `FastMap`; null means no mapping tool is offered. This is the authoritative reading, and the one genenetwork4 uses (`mapping_method` is the table's name for the id; `GET /mapping-methods` lists the table). Queried live: 58 groups use 1, 20 use 2, 2 use 3, 22 have none; PLINK and FastMap are defined but unused. GeneNetwork2's `get_mapping_methods` (`gn2/base/data_set/datasetgroup.py`) instead treats the id as a code for a set of tools ("1" = GEMMA + QTLReaper + R/qtl, "2" = GEMMA only, ...); that reading is superseded and is not followed here. ### Relationships - `InbredSet.SpeciesId` -> `Species` -- every group belongs to exactly one species (see Species section above). - `PublishFreeze.InbredSetId`, `GenoFreeze.InbredSetId`, `ProbeFreeze.InbredSetId` -> `InbredSet` -- every dataset belongs to exactly one group; `genenetwork2/gn2/base/data_set/__init__.py` and `datasettype.py` join on these throughout. - `StrainXRef.InbredSetId` -> `InbredSet` -- links which strains/samples are members of a group and whether they're `Used_for_mapping` (`topics/systems/mariadb/precompute-mapping-input-data.gmi`). - `InbredSet.SpeciesId` also scopes authorization the same way as described in the Species section: GN3's `gn3/api/case_attributes.py` resolves a species ID from `InbredSet` to build the `gn-auth` resource ID for a given group. - In the RDF/SPARQL classification scheme (`topics/next-gen-databases/gn-classification-scheme.gmi`), `InbredSet` is the "Set" level (`gnc:Set`), the middle of the three-level scheme between `gnc:DatasetType` and `gn:Species`. ### Known quirks - `public` and the `confidentiality` field seen on dataset-level tables (`ProbeSetFreeze`, etc.) are separate, differently-scaled visibility concepts; neither's exact value semantics were found documented in these repos. - `FamilyOrder` on `InbredSet` has no `Id` suffix, unlike the otherwise parallel `Species.FamilyOrderId` -- a naming inconsistency between the two tables, not a meaningful difference. - Not every `InbredSet` is a true recombinant inbred set in the genetics sense (see "GeneticType" above and the "RISet" alias discussion) -- `InbredSet` is GeneNetwork's catch-all table for any group/population, regardless of its breeding design. ### Existing implementations - **GeneNetwork2** (`gn2/wqflask/api/router.py`): `GET /api/v_pre1/groups[/]` (all groups, optionally scoped to a species) and `GET /api/v_pre1/group/` (one group, matched against `InbredSetName`, `Name` or `FullName`, optionally also scoped to a species). `gn2/base/data_set/datasetgroup.py`'s `DatasetGroup` class additionally resolves a group's available mapping methods from `MappingMethodId`, by its own code table rather than the `MappingMethod` table (see the `MappingMethodId` field above). - **GeneNetwork3**: no `InbredSet`-table REST endpoint or `gn3/db/` module was found for groups specifically (unlike `gn3/db/species.py` for species). - **gn-guile** (`gn/data/group.scm`): `get-group-links` and `get-group-data`, SPARQL/RDF metadata only, same pattern as `gn/data/species.scm`, not backed by `db_webqtl`. - **GeneNetwork4** (this repo): no `/species/{species}/groups`-style endpoint yet. ## PublishXRef The `PublishXRef` table (full `CREATE TABLE` in `docs/mariadb_db-webqtl.md`) is the central record for a published (phenotype) trait within one group: one row per trait per group, tying together that trait's `Phenotype` description, its `Publication` reference, its actual per-strain values (`PublishData`), and -- for traits that have been mapped -- a single best QTL hit (`Locus`/`LRS`/`additive`/`mean`). Explored at length in `topics/systems/mariadb/precompute-publishdata.gmi`, which walks through precomputing GEMMA hits for every `PublishXRef` row and surfacing its `Phenotype`/`Publication` metadata alongside them. ### Fields | Field | Meaning | |-----------------|--------------------------------------------------------------------------------| | `Id` | Trait id within a group; unique only paired with `InbredSetId` (Known quirks). | | `InbredSetId` | FK to `InbredSet`; every trait belongs to exactly one group. | | `PhenotypeId` | FK to `Phenotype`, the trait's description and units. | | `PublicationId` | FK to `Publication`, the article the trait was published in. | | `DataId` | FK to `PublishData.Id`, the trait's actual per-strain values. | | `mean` | Mean value across strains, for the trait's single stored QTL hit. | | `Locus` | Marker/SNP name of that stored hit. | | `LRS` | LOD/LRS score of that hit. | | `additive` | Additive effect size of that hit. | | `Sequence` | Disambiguates rows colliding on `(InbredSetId, PhenotypeId, PublicationId)`. | | `comments` | Free-text comment on the mapping. | | `NSamples` | Number of strains/samples the trait was computed over. | ### Relationships - `PublishXRef.InbredSetId` -> `InbredSet` -- every trait belongs to exactly one group (see InbredSet section above). - `PublishXRef.PhenotypeId` -> `Phenotype` -- the trait's `Pre_publication_description`/`Post_publication_description`/ `Original_description` and `Units`. - `PublishXRef.PublicationId` -> `Publication` -- the article the trait was published in (`Title`, `Authors`, `Journal`, `Volume`, `Pages`, `Month`, `Year`, `PubMed_ID`, `Abstract`). - `PublishXRef.DataId` -> `PublishData.Id` -- the trait's actual per-strain values; `PublishData.StrainId` -> `Strain` names them. `PublishSE` (per-strain standard error) and `NStrain` (per-strain sample count) key off the same `(DataId, StrainId)` pair, both optional (left-joined in GeneNetwork2's own queries). ### Known quirks - `PublishXRef.Id` is not globally unique on its own -- only the pair `(InbredSetId, Id)` is (`UNIQUE KEY InbredSet`) -- so a group must always be known to look up a trait by Id. Observed live: Id `10001` exists under dozens of different groups, each with a different `DataId`. - `Publication` coverage is spotty: per precompute-publishdata.gmi's own count, about 22K of 29K `Publication` rows lack an `Abstract`, and many `PubMed_ID` values look implausible for a real PubMed ID (too few digits to be one). Treat `Publication` metadata as best-effort, not authoritative -- the doc's own conclusion is "It really is a bit of a mess." - Only a single highest-LRS hit is stored per trait (`mean`/`Locus`/`LRS`/`additive`); precompute-publishdata.gmi and precompute-mapping-input-data.gmi are both about extending this to store more hits (e.g. every GEMMA SNP above a threshold, not just the best one) -- not yet reflected in the schema. ### Existing implementations - **GeneNetwork2** (`gn2/wqflask/api/router.py`): `trait_sample_data`'s Publish branch returns per-strain `PublishData` values (joined with `PublishSE`/`NStrain`); `get_trait_info`'s Publish branch returns only `PhenotypeId`/`Locus`/`LRS`/`additive`, without the `Phenotype`/`Publication` join. - **gn-guile** (`gn/data/dataset.scm`): `get-bxd-publish-list`, `get-bxd-publish-values-list`, `get-bxd-publish-name-value-dict` -- hardcoded to `InbredSetId=1` (BXD) throughout, no `Phenotype`/ `Publication` join. - **GeneNetwork4** (this repo): `GET /groups/{group}/traits/{trait_id}` (the `PublishXRef`/`Phenotype`/`Publication` join above, generalized to any group) and `GET /groups/{group}/traits/{trait_id}/values` (per-strain `PublishData` values). ## ProbeSetXRef The `ProbeSetXRef` table (full `CREATE TABLE` in `docs/mariadb_db-webqtl.md`) is the expression (mRNA) counterpart to `PublishXRef`: one row per probe/gene per expression dataset, tying together a probe's actual per-strain values (`ProbeSetData`) with a single best QTL hit (`Locus`/`LRS`/`pValue`/`additive`/`mean`). Unlike `PublishXRef`, it has no single `Id` column of its own -- its primary key is `DataId`, and the natural key identifying one row is the pair `(ProbeSetFreezeId, ProbeSetId)`. ### Fields | Field | Meaning | |--------------------|---------------------------------------------------------------------| | `ProbeSetFreezeId` | FK to `ProbeSetFreeze`; the expression dataset this row belongs to. | | `ProbeSetId` | FK to `ProbeSet`; the probe/gene this row's mapping belongs to. | | `DataId` | Primary key; FK to `ProbeSetData.Id`, the per-strain values. | | `mean` | Mean expression value across strains. | | `se` | Standard error of the mean. | | `Locus` | Marker/SNP name of this trait's single stored QTL hit. | | `LRS` | LOD/LRS score of that hit. | | `pValue` | p-value of that hit. | | `additive` | Additive effect size of that hit. | | `h2` | Heritability estimate. | ### Relationships - `ProbeSetXRef.ProbeSetFreezeId` -> `ProbeSetFreeze` -- the expression dataset (e.g. a tissue/platform/batch combination) this mapping belongs to. `ProbeSetFreeze.ProbeFreezeId` -> `ProbeFreeze.ProbeFreezeId` (the same "set equal to the primary key after insert" pattern as `Species.SpeciesId`/`InbredSet.InbredSetId`) -> `ProbeFreeze.InbredSetId` -> `InbredSet` is how an expression dataset is scoped to a group; `ProbeFreeze.TissueId` -> `Tissue` names the tissue it was measured in. - `ProbeSetXRef.ProbeSetId` -> `ProbeSet` -- the probe/gene annotation (`Name`, `Symbol`, `description`, `Chr`, `Mb`, `alias`, `GeneId`, ...). `ProbeSet.Name` is only unique per `(ChipId, Name)`, not globally, but is unique in practice within one dataset's member probes, which is how GeneNetwork2 and this repo both address a probe (dataset name plus `ProbeSet.Name`) without needing `ProbeSetId` in the URL. - `ProbeSetXRef.DataId` -> `ProbeSetData.Id` -- the trait's actual per-strain values; `ProbeSetData.StrainId` -> `Strain` names them. `ProbeSetSE` (per-strain standard error) keys off the same `(DataId, StrainId)` pair, optional (left-joined in GeneNetwork2's own queries), same pattern as `PublishSE` for `PublishData`. ### Known quirks - No `(ProbeSetFreezeId, ProbeSetId)` `UNIQUE KEY` is declared in the schema (only separate, non-unique `KEY`s on each column) -- a probe is assumed to appear at most once per dataset by convention, not by a database constraint; both GeneNetwork2 and this repo rely on that assumption (`exec_first`/`fetchone()`). - Only a single highest-LRS hit is stored per probe, the same limitation `PublishXRef` has (see its Known quirks above) -- the same precompute docs discuss extending both tables to store more hits. ### Existing implementations - **GeneNetwork2** (`gn2/wqflask/api/router.py`): `get_datasets_for_group` (list, `ProbeSetFreeze` joined to `ProbeFreeze`/`InbredSet`), `get_dataset_info`'s ProbeSet branch (one dataset, joined to `Tissue`), `trait_sample_data`'s ProbeSet branch (per-strain `ProbeSetData` values), `get_trait_info`'s ProbeSet branch (`ProbeSet` annotation plus `ProbeSetXRef` mapping stats, minus `pValue`). - **GeneNetwork4** (this repo): `GET /groups/{group}/datasets` (list), `GET /datasets/{dataset}` (one dataset -- not scoped under `/groups/{group}`, since a dataset name is globally unique on its own), `GET /datasets/{dataset}/probesets/{probeset}` (the `ProbeSetXRef`/`ProbeSet` join above) and `GET /datasets/{dataset}/probesets/{probeset}/values` (per-strain `ProbeSetData` values).