job_etl

Stores extract-transform-load (ETL) related jobs. Specifically this means csvloader and extractor right now.

Primary Key:

• job_etl_id

Checks:

• job_etl_deleted_check - ((deleted_at IS NOT NULL) = (deleted_by IS NOT NULL))
• job_etl_extracted_path_check - CASE WHEN (job_start < '2026-05-31 17:00:00-07'::timestamp with time zone) THEN true WHEN (((job_type = 'extractor'::claims.job_type) AND (status = 'succeeded'::claims.job_status_type)) OR (job_type = 'csvloader'::claims.job_type)) THEN (extracted_path <> ''::text) ELSE (extracted_path = ''::text) END
• job_etl_job_start_check - ((job_start IS NULL) = (status = 'queued'::claims.job_status_type))
• job_etl_job_type_check - (job_type = ANY (ARRAY['csvloader'::claims.job_type, 'extractor'::claims.job_type]))
• job_etl_parent_id_check - ((job_type = 'csvloader'::claims.job_type) = (parent_id IS NOT NULL))
• job_etl_status_check - ((status = ANY (ARRAY['succeeded'::claims.job_status_type, 'failed'::claims.job_status_type, 'cancelled'::claims.job_status_type, 'skipped'::claims.job_status_type])) = (job_end IS NOT NULL))
• job_etl_time_order_check - (((job_start IS NULL) OR (queued_at <= job_start)) AND ((job_end IS NULL) OR (job_start <= job_end)))

Foreign Keys:

• deleted_by => login.login_id
• parent_id => job_etl.job_etl_id
• queued_by => login.login_id
• uploaded_file_id => uploaded_file.id

Indexes:

• job_etl_deleted_by_idx - deleted_by
• job_etl_downloaded_path_idx - downloaded_path
• job_etl_extracted_path_idx - extracted_path
• job_etl_parent_id_idx - parent_id
• job_etl_queued_by_idx - queued_by
• job_etl_uploaded_file_id_idx - uploaded_file_id

Column	Data Type	Nullable	Indexed	Description
job_etl_id	bigint	No	Yes	Snowflake ID for job_etl primary key.
parent_id	bigint	Yes	Yes	Always sets for csvloader as a link to the extractor parent.
job_type	job_type	No	No	The type of job. This is usually the name of a service running the job (e.g. extractor, csvloader, exporter).
name	text	No	No	Name for the job. Comes from the zipName in extractor/csvloader config.
release_start_date	date	Yes	No	Start of the release-date range covered by this input. One of release_*_date or version must be set.
release_end_date	date	Yes	No	End of the release-date range covered by this input. One of release_*_date or version must be set.
version	text	Yes	No	Version of the input filename. One of release_*_date or version must be set.
job_start	timestamp with time zone	Yes	No	Time this attempt started. NULL while the job is queued.
job_end	timestamp with time zone	Yes	No	Time this attempt finished. NULL while the attempt is still in progress.
status	job_status_type	No	No	Status of this attempt (e.g. queued, running, succeeded, failed, cancelled, skipped).
job_metadata	jsonb	No	No	Additional metadata describing what the attempt did (e.g. archive_filename and anything else useful for understanding what was done and how).
log	text	No	No	Logs captured from this attempt.
extracted_path	text	No	Yes	The path of the file that is in extracted. Not empty for job_type = 'extractor' when a file is successfully extracted. Always non-empty for csvloader but may differ from the parent extractor row due to a users moving the files.
downloaded_client_type	text	No	No	The client type that the downloaded file was from.
downloaded_bucket	text	No	No	The bucket that the downloaded file was from.
extracted_client_type	text	No	No	The client type that the extracted file was from. Usually non-empty when extracted_path is but historic data prevents this from being guaranteed.
extracted_bucket	text	No	No	The bucket that the extracted file was from. Usually non-empty when extracted_path is but historic data prevents this from being guaranteed.
downloaded_path	text	No	Yes	The path of the file that was in downloaded. Both csvloader and extractor should always agree when linked by parent_id.
queued_at	timestamp with time zone	No	No	Time this job entered the queue (set at creation). queued_at <= job_start <= job_end.
deleted_at	timestamp with time zone	Yes	No	When this job's loaded data was deleted via the load-delete endpoint. NULL if still live, set along with deleted_by.
deleted_by	bigint	Yes	Yes	The user who deleted this job. NULL if still live, set along with deleted_at.
queued_by	bigint	Yes	Yes	The user who queued this job. NULL if this information is not being tracked, such as if it's loaded through the `downloaded/` folder in a bucket.
uploaded_file_id	bigint	Yes	Yes	Originating uploaded_file for this job, for display. Set by extract-uploads.