Investigate beta 6.0.2 deployment database regression

[dondi]

When #985 was merged into beta, that was pulled into the server but doing so appears to have regressed the database functionality: gene searches no longer produce results. The same branch was verified to have worked locally for @ahmad00m so the issue looks like it is server-specific

[dondi]

Based on @Onariaginosa’s findings, the inconsistencies we have noticed are due to time zone shifts. The general principle for handling this is to store all timestamp values in UTC and to defer all computations to local time until the very end, if at all. In this particular case, since timestamp is not involved in the user interface, our goal is to move everything to UTC

Our long-term fix is to be fully “time-zone-aware” across our stack. This involves:

1. We will consider original data to be in UTC; in the future, any code that adds a timestamp should include time zone with it
2. Our source table must change the time_stamp column to type timestamp with time zone—and further, we will store values in this table in UTC
3. All of our browser code must be “aware” of time zone as a factor and must convert all timestamp parameters to UTC before sending to our API

Based on looking at current behavior, short-term fix may be just #3. Recommendation is to use the date-fns to parse and format dates—this is the current go-to library for reliable date processing. @dondi and @Onariaginosa can work offline to do this, with @ahmad00m and @Sarronnn being welcome to join, time permitting 😁

[dondi]

This is looking fixed; just need a couple of follow-ups to ensure that this isn’t a one-off:

1. As it turns out, Los Angeles drops Daylight Savings Time this weekend, so re-checking functionality after we change time zones will be worthwhile
2. @ahmad00m will verify the “start from scratch” sequence to make sure that these adjustments work well when initializing a database from the beginning
3. Less crucial but potentially useful: have another machine temporarily set to a different time zone connect to the “start from scratch” system to make sure that the time zone fix holds—check your network settings beforehand to determine the IP address of your local machine on the local network, and use that instead of localhost to connect from the different-time-zone computer

[ahmad00m]

With the help of @Onariaginosa I was able to make a new copy of the database with all the new changes in the schema. However, for some unknown (at the moment) reasons the queries are still pending and nothing gets returned. Attached are screenshots of what this exactly means. I also tried changing the time zone, however the new mac update is weird and doesn't change the time zone but I was able to change the time manually, so this might have some effect on the timestamps shown for the network source. Also, altering the current schema on my laptop to match the changes in the schema seemed to work just fine, so at this point the schemas look the same but it works for my laptop but not my desktop.

[Onariaginosa]

So I figured out the issue with that and fixed it (I hope). The expression dal needed to be restructured a bit.

Instructions for altering an existing database to use timestamp with time zone (Just in case!)

1. Drop the foreign key constraint in the network table: Your foreign key should look something like network_time_stamp_source_fkey, but to be certain type in \d network and check for the foreign key that references time_stamp and source

   ALTER TABLE network
   DROP CONSTRAINT network_time_stamp_source_fkey;

2. Alter the network table and the source table to use timestamp with time zone and interpret the existing data as utc

   ALTER TABLE  network
   ALTER COLUMN time_stamp
   TYPE TIMESTAMP WITH TIME ZONE
   USING time_stamp AT TIME ZONE 'UTC';

   ALTER TABLE  source
   ALTER COLUMN time_stamp
   TYPE TIMESTAMP WITH TIME ZONE
   USING time_stamp AT TIME ZONE 'UTC';

3. Add the constraint that we dropped in step 1 back. You don't have to keep the original name, you can directly copy and paste this

   ADD CONSTRAINT network_time_stamp_source_fkey FOREIGN KEY (time_stamp, source) REFERENCES source(time_stamp, source);

[ahmad00m]

I checked the node version on my desktop and it was v19.0.1 whereas my laptop is v16.17.1. Also I was able to find the pg-hstore version on my desktop which is 2.3.4 using the npm list command, however, for some reason I couldn't find it on my laptop using the same command.

[dondi]

Reviewed, merged, and deployed