Script Records

The script_records table stores individual data records generated by script runs, designed for columnar storage.

Table Schema

Column Name	Data Type	Description
`id`	`UUID`	Primary Key. Unique identifier for the script record.
`timestamp`	`TIMESTAMP`	Timestamp of the record.
`script_id`	`UUID`	Foreign key referencing the script.
`script_name`	`String(255)`	The name of the script.
`script_run_id`	`UUID`	Foreign key referencing the script run.
`script_data`	`JSONB`	Additional JSONB data for the script.
`experiment_id`	`UUID`	Foreign key referencing the experiment.
`experiment_name`	`String(255)`	The name of the experiment.
`season_id`	`UUID`	Foreign key referencing the season.
`season_name`	`String(255)`	The name of the season.
`site_id`	`UUID`	Foreign key referencing the site.
`site_name`	`String(255)`	The name of the site.
`record_file`	`String(255)`	The file where the record is stored.
`record_info`	`JSONB`	Additional JSONB data for the record.

Unique Constraint: A UniqueConstraint on timestamp, script_id, script_name, script_run_id, experiment_id, experiment_name, season_id, season_name, site_id, and site_name ensures uniqueness for each record.
GIN Index: A GIN index named idx_script_records_record_info is applied to the record_info column to optimize queries on the JSONB data.

filter_records: A class method that allows filtering script records based on various parameters such as start_timestamp, end_timestamp, script_names, script_run_ids, experiment_names, season_names, and site_names. This method leverages a PostgreSQL function gemini.filter_script_records for efficient filtering.