Exericse: Build Farm for Continuous Integration

2021-06-25

Continuous Integration software like Jenkins, Bamboo or Concourse CI are following more or less the same data structure. For each Continuous Integration project there can be multiple versions. Each version can have multiple artifacts. Those artifacts can be executable files for different target platforms, e.g. x64 or arm64. Artifacts are produced by build agents which are installed on a host, e.g. a physical or virtual machine. There can be multiple build agents installed on the same host. Each build agent can be assigned to a number of target platforms, e.g. for cross-compile builds.

How could a database model look alike? For this exercise, it is sufficient to model just the entities/tables.

Solution

Schema

Diagram

Table project {
  id integer [primary key, not null, unique]
  name char(128) [not null]
}

// a project can have multiple versions
Table version {
  id integer [primary key, not null, unique]
  name char(128) [not null]
  project_id int [not null, ref: > project.id]
}

// out target platform
Table target {
  id integer [primary key, not null, unique]
  // e.g. Windows x64, ARMx64
  name char(128) [not null]
}

// build agent
Table agent {
  id integer [primary key, not null, unique]
  // a build agent is running on one host; there can be multiple build agents per host
  host_id int [not null, ref: > host.id]
}

// a physical or virtual host
Table host {
  id integer [primary key, not null, unique]
}

// one project can be assigned to multiple target platforms; vice versa a target platform can have multiple projects
Table project_in_target {
  id integer [primary key, not null, unique]
  project_id int [not null, ref: > project.id]
  target_id int [not null, ref: > target.id]
}

// agents can be also assigned to multiple targets
Table agent_in_target {
  id integer [primary key, not null, unique]
  agent_id int [not null, ref: > agent.id]
  target_id int [not null, ref: > target.id]
}

// an artifact is produced by a build agent, belongs to a version and has an assigned taret platform
Table artifact {
  id integer [primary key, not null, unique]
  // artifact has been built by agent
  agent_id int [not null, ref: > agent.id]
  // artifact belongs to a version
  version_id int [not null, ref: > version.id]
  // artifacts is assigned to a target platform
  target_id int [not null, ref: > target.id]
}