From an api consumer standpoint I have a minor annoyance that generally I deal with job_guid's (creation) but to then fetch my results I need to build a specialized query to job list to convert my job_guid to a job_id. I don't fully understand the "uniqueness" of job_guid it appears that it should be unique but I don't see that enforced if it is unique then we could consider (for get job at least) allowing it to be used interchangeably. Aside from mysql performance concerns I would _love_ to see job_id to go away (again if job_guid is truely unique) from an external api perspective (i.e. never expose it outside of internal code)
I don't remember why it's not set as a unique key, but I think it should. The uniqueness is enforced at the application level which is not great. If you want to get a job by it's guid, you can specify it via a job_guid querystring parameter on the job list endpoint. We could also support the same parameter for the get artifact endpoint. I agree that having both a job guid and a job id generates some confusion. If we want to take the job-guid-only path, we should add a column to the job table to keep the insertion timestamp, so that in the future we can tell the insertion order. Obviously we should also alter all the tables referencing job to use job_guid as a foreign key. I would also ask a DBA to validate it for the perf concerns.
We still have a lot of inconsistent sprinklings of job_guid vs job_id both in schema, ingestion logic, API support etc. I'd love to stick to one or the other as much as possible, and I think that one should be job_id. We'll presumably have to keep the guid for ingestion purposes, but I don't think it makes sense for eg the /jobdetails/ endpoint to support `job_guid` (and `job__guid`) as params.