My initial, very naive thought is:

Assuming that we can get every status periodically and utilizing `tel_visit_id`, we have three tables:

• tel_condition
  • pfs_visit_id (not null)
  • tel_visit_id  (not null)
  • tel_az
  • tel_el
  • tel_ra
  • tel_dec
  • tel_rot
  • tel_adc
  • tel_focus
  • timestamp
  • ...
• env_condition
  • pfs_visit_id (not null)
  • tel_visit_id (not null)
  • dome_temperature
  • dome_humidity
  • dome_pressure
  • outside_temperature
  • ...
• obs_condition
  • pfs_visit_id (not null)
  • tel_visit_id (not null)
  • seeing
  • transparency
  • cloud_condition
  • ...

, where `tel_visit_id` is just like a counter with a certain cadence starting just after pfs_visit_id is issued. (pfs_visit_id, tel_visit_id) is a unique key, so we probably need to have `tel_visit_id` in mcs_exposure, sps_exposure, agc_exposure, and obslog table, which should be the closest one to each exposure, so that we can easily identify conditions we want to know for each subsystem.

I surely do not know if this works and how to do this, but any comments are welcome.

Yes. I think all the tables should have pfs_visit_id and timestamp columns, but am not sure what the benefit of tel_visit_id is or what the value should be.

There will be multiple AGCC and MCS exposures per visit, and I think we can guarantee that there will be at least one (and probably no more than one) tel/env/obs update per AGCC/MCS exposure. So a variant of (visit == $visit AND timestamp between $agcc_tStart and $agcc_tEnd) query should be good for all reasonable sps/agcc/mcs investigations. I think that would be simpler than tracking some additional "telescope"/agcc/mcs id, since it would work in all cases.

As a side note, I would add some other dome information that we know we can track. For now:

• dome shutter open/closed/unknown
• dome (room) light mask integer.

Thank you for your comments. 

I feel timestamp is essentially all what we need to identify conditions of interest. `tel_visit_id` (or any unique identifiers) is beneficial only if we cannot guarantee the uniquness of the timestamp, but probably not? So, `timestamp` (or a combination with `pfs_visit_id` just in case) would be a primary key (if we want PK in each table). I'm not sure whether this is a standard design method, though.

One stupid question is whether we guarantee these timestamps are syncronized well enough with each subsystem (probably yes because we use the same gen2actor)? The record timing may be what we need to think about seriously.

Additional columns such as dome shutter/light status are surely fine to me.

The actual values dribble in from parts of the telescope systems at various rates and without any exploitable synchronization. The gen2Actor currently queries for new values when it needs to, but it could also passively listen to a 1 Hz update stream from gen2. The sunssActor currently does that.

The obvious times when we want good values are at the start of integration, particularly for the faster exposures (AGCC, MCS, H4 reads), and otherwise every 15s or so while observing.

Given the understandable reluctance to use timestamps in a key, I propose that all tables have a primary key of (pfs_visit_id, status_sequence_id), where the sequence id is maintained per-visit by the gen2Actor, and that all tables also have an indexed timestamp column. The gen2Actor would grow an updateConditions command which would force a update; that could be called by the agcc/mcs actors when they start an integration.

I am worried about delays if the real gen2 pauses as it seems to occasionally pause when getting a new frame/visit id. So will probably use the passive 1Hz stream whenever safe.

Per 2021-08-18 phone con, will be implemented as written.

Kiyoto Yabe : what is the progress on this ticket? Can we close it based on your branch?

I believe so. cloomis?

Since the current schema at summit actually includes this revision, I'll merge the branch and close this ticket. We will file a new ticket if further revisions are necessary.