Alteration annotation database design

[jjgao]

This is copied from here. Given the multiple recent developments (FACETS, OncoKB, custom-JSON) around enhancement to variant annotation, it would be good to step back and see whether we can implement a generalized solution. The design below is meant for discussion towards a database schema which the community is agreed upon.

It contains two main changes:

• Annotation integration (alteration_annotation & annotation_source)

  • Centralized variant annotation table for mutations, SVs, and CNAs.
  • Metadata of annotation sources are supported by annotation_source, including version
  • One alteration can be annotated by multiple annotation sources (e.g. OncoKB OncoKB v2, Civic)
  • Using JSON makes it flexible to incorporate any types of annotations. For example, with this, it is possible to include the OncoKB levels data to support analysis such as https://github.com/cBioPortal/icebox/issues/82.

• Parent alteration table that includes mutations, CNAs, and SVs

  • No need to query the individual tables. This makes it possible to construct SQL for the study view page without pulling out sample-level data. (Currently, you would have to pull sample-level data from the three tables and combine them together).
  • It will also make the query simpler for the Combined Alteration tab in the Comparison page (and in the future Study View)
  • Connecting to genetic_entity makes it flexible for future extension for other alteration types, e.g. arm-level CNA.

To summarize, a few existing and future projects can be benefit from this design:

• Custom Driver integration in Study View & Comparison page RFC56
• Combined Alterations tab in Comparison page RFC58
• OncoKB levels analysis described here
• We also need a combined alterations table in study view (overall alteration frequencies per gene)
• Integration of PathwayMapper into Study View (overall alteration frequencies per pathway/gene set)
• Costom JSON implementation can be consolidated here

@pvannierop @Sjoerd-van-Hagen @n1zea144 @sheridancbio @inodb @zhx828 @kalletlak

[pvannierop]

I like the design, but I am by no means an expert. One issue of concern is that for passenger/driver filtering by count and enrichment endpoints the JSON field for custom driver annotations (_DRIVERFILTER) in the _alterationannotation table would have to be queried. This could be done with a generated column that maps out and indexes a JSON field as a column. However, this approach does not play well with the dynamic way you envision the ANNOTATION_JSON field to be used in. Perhaps we have to move queried fields out of the JSON and add as fixed fields in the _alterationannotation table?

[jjgao]

@pvannierop Good question. I think we should be able to query inside json but I am not familiar with that. One option is below, in which, we only deal with string/varchar.

[pvannierop]

@jjgao It is not clear to me what solution to the problem you are proposing. You mean that certain fields in the JSON can be extracted into a meta table? I do not see how that is different from the _generatedcolumn mechanism conceptually.

[jjgao]

@pvannierop I mean we don't store any json but limit the annotation to strings.

I think we may have got into too deep into details. It would be good to have a discussion with others before discussing it too deep.

[pvannierop]

@jjgao Ah I get it now. Yes, feedback from others here is essential to work towards a final plan.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.