This logic has also to take into account that contrary to fps, other lightSource config change(dcb bundles, sunss pointing) is not necessarily associated with a visit.
it gets a visit0 when someone ask for a sps exposure basically.

okay, so that's 90% done, but I'm hitting an opdb issue that cannot be worked around.
basically between iic_sequence and visit_set table.
iic_sequence has a unique visit_set_id and visit_set has pfs_visit_id as primary key which means that a given pfs_visit_id can only be associated with one visit_set_id, which made sense with independent visits.

But since we're sharing pfs_visit_id among fps,sps,agc, and have a visit_set_id, for one of those, you want to insert several sequence for a given visit which break the constraint.

One solution is to

• add a visit0 column to iic_sequence
• change visit_set_id to visit0 in visit_set (without back-populating).

This way, you keep the unicity of pfs_visit_id,visit0 in visit_set but different iic_sequence can share the same visit0

In this case, renaming visit_set_id to sequence_id would probably make more sense, but do we care ? I'm not sure.

okay last proposal after discussions with cloomis and rhl.

• rename iic_sequence.visit_set_id to iic_sequence.sequence_id
• add iic_visit0 table with iic_sequence.sequence_id as primary key, and visit0
• rename visit_set.visit_set_id to visit_set.visit0

I'm pulling opdb table from the summit and preparing a script to do those schema changes which I will hand off to Kiyoto Yabe when I'm sure it's doing the right thing.

That seems to be probably reasonable to me.

`sequence_id` is totally different from `status_sequence_id` right? Can we make it as `iic_sequence_id` to avoid any confusion? 

A script for the schema change would be very helpful, but I prefer to make the actual change through the alembic mechanism after fixing `models.py`.

This change probably affects the `obslog` so I took the liberty of inviting michitaro for further discussion.

in the end, that scheme was again not quite right, we're confusing the official visit0 with the first visit of the visit_set which is sometimes equivalent but sometimes not.

the final proposal is actually much less disruptive than the previous one which is always good.

we're adding a field_set table :

• visit_set_id = Column(Integer, ForeignKey('iic_sequence.visit_set_id'), primary_key=True)
• visit0 = Column(Integer, ForeignKey('pfs_config.visit0'))

I also add to add a unique constraint on pfs_config.visit0 otherwise it's complaining the foreign key is not unique :

ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "pfs_config"

I check a bit more, it's true that currently there is no explicit unique contraint :

  CONSTRAINT pfs_config_pkey PRIMARY KEY (pfs_design_id, visit0),
  CONSTRAINT pfs_config_pfs_design_id_fkey FOREIGN KEY (pfs_design_id)
      REFERENCES public.pfs_design (pfs_design_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION

compared to

  CONSTRAINT pfs_config_pkey PRIMARY KEY (pfs_design_id, visit0),
  CONSTRAINT pfs_config_pfs_design_id_fkey FOREIGN KEY (pfs_design_id)
      REFERENCES public.pfs_design (pfs_design_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT pfs_config_visit0_key UNIQUE (visit0)

I'm a bit confused about using _table_args_ to pass contraints, it's look like it's not completely equivalent to declare a column unique.
Kiyoto Yabe, you have a better expertise than I do about sqlAlchemy, I will ask you to review this ticket.

Changes look fine to me.

Yabe-san has approved the changes.