To join with Findit analysis events, we need to discuss more about another option: whether commit position is better or not.

buildbucket build id are monotonically decreasing and this is extremely unlikely to change. A pair of build ids can be used to represent a range. 

Note that a builder id field is also needed to define a range, because builds in range B1..B2 are not necessarily in the same builder. A builder id in a tuple (project, bucket, name). From bit.ly/chromium-builder-id:

// Identifies a LUCI builder.
// Canonical string representation: “{bucket.project}/{bucket.name}/{name}”
message BuilderID {
  // Identifies a bucket.
  BucketID bucket = 1;
  // Bucket name, e.g. “linux-rel”.
  string name = 2 ;
}

// Identifies a Buildbucket bucket.
message BucketID {
  // ID of the project that defines this bucket, e.g. “chromium”.
  string project = 1;
  // Name of the bucket, e.g. “try”.
  string name = 2;
}

--------

note that "buildbucket" property is a JSON *string* for historical reasons. In LUCI it is a proper JSON *object* with the same object properties. Please support both cases, which means parse it as JSON if it is string.

note that you may want specifically one field of composite type (e.g. BuilderID) in your table, as opposed to 3 fields. It would make JOINs simpler:

Compare:

SELECT *
FROM a JOIN b USING (builder_id)

vs

SELECT *
FROM a JOIN b USING (project, bucket, builder)

it requires that all tables use the same format, such as BuilderID above. I propose that format. Whatever we decide, will also be used in completed_builds table.

Thanks for the clarification, Nodir.

stgao@: Please let me know what your team decides re: commit position vs buildbucket build ID.

wylieb@ could make the decision as he is working on the data reporting and joining: will buildbucket build ID work for joining the two tables? There was a discussion about using commit positions in your codereview as well IIRC.

From my perspective, commit position might be a bit overkill here.

Flake Analyzer does have the idea of commit positions for specific data points, but for the overall analysis we care about more coarse-grained items like:
* The build where the flake was _detected_
* The build where the flake was _introduced_

For failure analysis, it's largely the same.

Regression ranges are helpful internally within Findit, but I don't see them being important for table joins. Looks like SoM alerts are really only concerned with failures on the build level. I think bbucket_id should work just fine.

nodir@: *Which* builders are on buildbucket, again? I'm a little worried that none of the ones SoM cares about are, based on examining some of the recent alerts. e.g. https://ci.chromium.org/buildbot/chromium.mac/Mac10.10%20Tests/28861

as of today, no waterfall builder is on LUCI. Foundation p0 OKR this quarter is moving all tree closers to LUCI.

Can you mark this issue blocked on that issue then?

tandrii, what do you think about switching waterfall builders from Buildbot scheduler to LUCI scheduler? this will provide build ids for waterfall builds without migration to LUCI

I've got this CL ready on the SoM side: https://chromium-review.googlesource.com/c/infra/infra/+/906828

What is the least critical builder that SOM polls? We can try moving it to LUCI Scheduler and see how it goes.

#13: I'm not sure, that's more of a question for our users. 

Chromium GPU FYI might be a good one to approach since it's just FYI (they don't use gatekeeper). kbr@ Owns it, I think.

Re #11: moving to scheduler SGTM.

re comment 4: completed_builds BETA released with a flat version of builder id:
https://chromium.googlesource.com/infra/infra/+/d022d882c59ab8a1781eb6e169440fbf17f56a02/appengine/cr-buildbucket/proto/build.proto#206

  message NameDoesNotMatter {
    // Project ID, e.g. "chromium". Unique within a LUCI deployment.
    string project = 1;
    // Bucket name, e.g. "try". Unique within the project.
    // Together with project, defines an ACL.
    string bucket = 2;
    // Builder name, e.g. "linux-rel". Unique within the bucket.
    string builder = 3;
  }