Angelo Fausti added a comment - 15/Dec/17 4:03 PM - edited Desired behavior is to store timestamps in UTC computed always server side. In MySQL this is accomplished with the TIMESTAMP datatype;   mysql> create table tmp (timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP); Query OK, 0 rows affected (0.03 sec) mysql> describe jobs; +------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+-------------------+----------------+   | timestamp | timestamp | NO | | CURRENT_TIMESTAMP | | +------------+-------------+------+-----+-------------------+----------------+ 6 rows in set (0.00 sec) which in SQLAlchemy translates to:   from sqlalchemy.sql import expression from sqlalchemy.ext.compiler import compiles from sqlalchemy.dialects.mysql import TIMESTAMP   class now(expression.FunctionElement): type = TIMESTAMP   @compiles (now, 'mysql' ) def mysql_now(element, compiler, * * kw): return "CURRENT_TIMESTAMP()"   class TestModel(db.Model): __tablename__ = 'tmp' timestamp = db.Column(db.TIMESTAMP, nullable = False , server_default = now())

Angelo Fausti added a comment - 18/Dec/17 2:08 PM - edited New DB schema for SQuaSH QC-0 database Notable changes compared with the previous implementation: more extensive use of JSON data type for fields that not require query and can have arbitrary content metrics and metric specifications are now in separate tables (following the lsst.verify design guidelines) added package field in the metric table, it allows to group metrics by package data blobs are stored in a separate table support jobs from multiple users support multiple execution environments provenance information in the job and in the package tables are not tight to the CI environment anymore. Support queries in JSON() fields, like the job look up in environment metadata

Angelo Fausti added a comment - 29/Dec/17 5:30 PM - edited See https://jira.lsstcorp.org/browse/DM-12194 for the corresponding pull request and detailed model implementation. See also https://sqr-009.lsst.io/#appendix