Permit human-friendly updates to source + signal naming, description, and grouping tables

[krivard]

#539 and #546 describe a method for explicitly encoding in the database useful information about covidcast sources and signals, including:

• Display names
• Descriptions
• Clusters of related signals
• Representative signals from each source (or ideally each cluster) used for indicator status statistics
• Most of the information currently maintained in the Signals Description doc, including timeseries axis names and units, license information, sentiment, and a suite of other flags (smoothed/weighted/cumulative/sampling details/pyramid location)

Storing this information in database tables is vastly superior to trying to make our websites retrieve it from its current locations spread across google docs and markdown files. However, once we've shifted to database storage, we will need a way to maintain and update this information that is less error-prone than manually typing SQL into a MariaDB prompt.

Some possibilities include:

• Suck the information out of the existing google docs and markdown files as part of a release workflow or process
• Some new file format that can be committed to the repository for easy review and undo (which may make one or more of the existing google docs and markdown locations obsolete)
• A tiny restricted-access web app

[sgratzl]

do we have a plan regarding versioning?

• keeping old version of the metadata
• adding/changing data in the staging/dev version but not in the production version?

Suck the information out of the existing google docs and markdown files as part of a release workflow or process

should the meta data only be updated when a new release of delphi-epidata is performed? or also out of order updates?

general question
since this is a write operation to the database, how do we make sure that it is properly distributed within the replicates? or should we try to ensure that any operation (including webapp) is only working on the internal production servers but not its replicates.

[krivard]

Versioning in the tables would be neat if we can do it, but not necessary if we use external inputs and those are versionable.

Our staging/dev database is currently too freeform to effectively support a reliable stage->release pipeline for this kind of metadata, since we don't keep it updated with other epidata tables and often use it as a sandbox for testing out dramatic changes to the schema or upload & analytics procedures. I do see the value of being able to stage metadata changes for preview though, so it might be worth it to set up a more rigorous staging/dev data environment. How big a job would that be?

should the meta data only be updated when a new release of delphi-epidata is performed?

Yep, we'd use patch releases for typos and other small language changes, and minor releases for new signals, with updated metadata coming along with the new API docs.

since this is a write operation to the database, how do we make sure that it is properly distributed within the replicates?

Replication happens automatically, and is both quick and simultaneous when the system is healthy*. Edits made by a webapp would have to be directed only to the primary server, but since we expect the number of write-users to be in the single digits we don't need replication for responsiveness anyhow.

• when the system is not healthy, new data can take a while to get to one or more replicas. This happens infrequently now, maybe once a month or less. Any read-only systems should be robust to rare transient unavailability of new data.

[sgratzl]

I'm currently wondering whether we wanna store it is a database table or whether we store it s a file that the server loads during startup. Assuming we are only changing things through a new release there wouldn't be much difference.

pro database: we could use it for joining. However, we could to the aliasing also before manually
pro file: no database migrations needed and we can have different files in main/dev easily

For the file approach we could use the same strategy that we are using for www-covidcast and its meta data descriptions. Have a google sheet for the data and a workflow that triggers a synchronization.

[krivard]

pro database: database backup files maintain connection between metadata snapshot and data snapshot

that may not be terribly important though; the only time i can think where it would matter is if something went horribly wrong at the same time as a major change to the metadata file, but we didn't find out about it until a large amount of time had passed. that would make it more difficult to figure out which git version of metadata was active when the last good backup was taken, but that's a low likelihood, moderate impact, not unrecoverable scenario.

the data warehousing reorg we're working on will probably eventually implement tables for signal clusters and name aliasing, but it's not going to be done any time soon. we can revisit this item at that time and revise our approach if needed.

go ahead with the file idea -- that gives us the flexibility and confidence we need right now, and will be way quicker to implement.

[sgratzl]

start in https://docs.google.com/spreadsheets/d/1zb7ItJzY5oq1n-2xtvnPBiJu2L3AqmCKubrLkKJZVHs/edit#gid=0

[sgratzl]

a version implemented in #564