Proposal for database schema changes

[Nihlus]

This is a proposal for a set of changes to the database schema of rebuilderd in
order to better support the increasing variety of Linux distributions that can
be rebuilt.

The changes unify the handling of core identifying features for individual
source and binary packages, add missing build pipeline metadata, and enable
easier integration of future distribution support.

Problem statement

The current database schema of rebuilderd is mainly built around Arch Linux's
packaging system, reflected in both its structure and field naming. This works
for the basic uses of rebuilderd, but brings challenges for other distributions.

In particular, multiple Debian releases cannot currently coexist within the same
rebuilderd database, requiring several instances of rebuilderd with separate
databases to operate. Multiple architectures within the same database face
similar challenges.

A number of GitHub issues exemplify these hurdles:

• #180: confusing use of suite in ./api/v0/builds/{id}/log
• #178: Save Debian release in DB
• #164: cannot requeue packages

Terminology

Various distributions of Linux use different terminology to refer to the same
things. In order to avoid confusion throughout the rest of the document, and to
serve as a basis for some of the proposed changes, the following definitions are
adopted.

• Distribution: A unique name identifying a Linux distribution such as
  Debian, Arch Linux, Fedora, etc.
• Release: A unique name or version of a single release of a distribution.
  Several releases may be part of a single overall version of the distribution.
• Component: A logical division of packages within a single release.
• Source package: Upstream source code along with a set of metadata
  describing it. Source packages are not installable, and can only be used to
  build binary packages.
• Binary package: A built, installable, and verifiable package.
• Source version: The version a piece of upstream software has been released
  under.
• Binary version: The version a built package was assigned, which may differ
  from the source version.
• Architecture: The architecture a source package has been built for.

To exemplify, here are some equivalents from the aforementioned distributions and which term they'd fall under.

	Distribution	Release	Component
Debian	debian	trixie	main
Arch Linux	archlinux	null	core
Fedora	fedora	rawhide	Everything
Tails	tails	stable	main

Scope and use cases

Firstly, let's identify the requirements for a database schema supporting our
target use cases. To simplify reasoning about such requirements, we will focus
on packaging systems, rather than individual distributions. The distributions
we already have support for will serve as examples and to validate any
assumptions made.

Distribution	Packaging system
Arch Linux	pkgbuild
Debian	deb
Fedora	rpm
Tails	deb

Core to any relational database schema is modeling the data that is to go into
it. In our cases, that is primarily about how to uniquely identify both source
and binary packages for rebuilds. The packaging systems have the following
relevant relational constraints.

	Source to binaries	Source version	Binary version	Source architecture model	Binary architecture model	Release model	Component model
deb	one-to-many	based on upstream	defaults to source	no architecture	architecture-specific	distinct	optional
pkgbuild	one-to-many	based on upstream	same as source	no architecture	architecture-specific	none	distinct
rpm	one-to-many	based on upstream	defaults to source	no architecture	architecture-specific	distinct	distinct

As we can see, there is significant overlap between the packaging systems with
some notable differences. In particular,

• deb does not necessarily divide its releases into components
• pkgbuild has no concept of a release
• rpm may produce binary packages with versions other than their source packages
• deb may have binary versions that do not cleanly map to a source package (binNMU, for example)

Therefore, our database schema must support each of these variants at the same
time while also minimizing data duplication.

Proposed design

The following initial proposal for a reworked database schema takes on the above
requirements with a few design guidelines in mind.

• Any package from any distribution of any architecture should be able to
  coexist in the database at any given time
• Relationship design in the database should generally follow 3NF (third normal
  form), meaning that
  • Data duplication should be minimized
  • Data in a table should be directly relevant to the entity described by the
    table
• Terminology within the database should be
  • distribution-agnostic
  • clearly-defined
  • consistent
• Historical data should be preserved, enabling retrospection of any previous
  state of the database

The current schema is not too far off from ticking most of these boxes and
mostly requires some renaming and the moving of certain data between existing
tables.

An interactive diagram of the proposed new schema can be found here, and a
static image is included for rapid reference.

While not very complex, there are some notable changes from the current model.

1. The "root" entity is a source package. It can be derived from a binary
   package or inserted independently, which is the more common approach.
2. A new table has been added to track build information data separately from source
   packages. This is now the starting point for any rebuild actions and allows
   multiple rebuilds for the same source package to coexist (such as different
   architectures).
3. The rebuilds table is now a historical database instead of a current-state
   snapshot, containing all current and past attempts to reproduce a source
   package based on its build information.
4. Rebuild information has been separated into two tables - one for source packages, and one for binary packages matching the split for general package metadata.
5. Duplicated information across all tables have been coalesced into their most
   common ancestor (typically source_packages).
6. All distro-specific terminology has been replaced with distro-agnostic
   equivalents.
7. Rarely-used and derivable information has been removed.
8. The JSON field "artifacts" has been removed. Artifacts are now computed based on their relation to their source package.

Challenges

I believe the proposed schema should resolve most, if not all, of the
mentioned challenges raised by the community. Some of the changes are in service
of future proposed work (historical data, for example) and can be pruned back if
desired.

Migrations from the old schema to this schema should be relatively simple though
certainly not trivial.

The new schema would obviously necessitate a number of behavioural changes and
updates to database management throughout the daemon. In particular, several
queries that currently pull data from a single table would need to either join
several tables or insert data into several tables depending on the use case.

[Nihlus]

This was originally shared as a draft (which it honestly still is) in a gist, and I'll import some brief questions and a reply below:

from @jelly

Thanks for the detailed proposal, some things aren't clear for me yet

* What is an example of "component"? Is that like a "suite" in Debian?

* How would the build_info in build_informations get populated? In the initial sync of the repository database? Or later during a build?

I also assume this design is created to make rebuilderd useful to host one rebuilderd instance for multiple architectures? This would require the workers to advertise what architecture they can rebuild. But that's maybe a future thing :)

reply from @Nihlus:

* A component is equivalent to what Arch calls a repository, Fedora calls an edition, and Debian a component. Effectively, it's what the distros use to divide up the packages of a single release into logical sections with specific purposes, terms, or requirements.

* The intent was to make this column the equivalent of input_url on pkgbase, but poking at it again I see that it probably needs a bit of rethinking. I overlooked that it was optional and not used by the rpm or pkgbuild, where these instead base their checks on the first artifact.

[Nihlus]

I reworked the schema slightly to account for the above - build_info is now build_input_url and must contain a URL to a file that is downloaded and passed to the rebuild backend. The details of that file format is left to the backend to interpret (.buildinfo, .rpm, .tar.gz, etc). This also decouples the worker from needing to check if build_input_url is set or, if it is not, assume that the artifact is the input. Instead, we have an explicit specification of what a particular rebuild needs.
Additionally, the backend field was moved from the queue to the build information entity to retain data locality (the backend is tied to the build input format, not necessary an enqueued item).

[h01ger]

I've commented on the gist yesterday already (and most if not all concerns have been addressed) so here I'll just say that I very much applaud this design first, implementation second approach! I also very much applaud these issues being addressed in the first place! 👍 😄 Thank you! <3

[Nihlus]

I've started a prototype implementation of the proposed schema and incorporated some more feedback and discoveries into a new revision.

The main highlights are that rebuilds is now rebuilds and rebuild_artifacts, splitting whole-job information and per-artifact information into two tables. Additionally, I've changed the proposed name of build_informations to build_inputs.

As part of the prototype, I'm also examining the API for the changes we would need to make. I'll update the post with my proposal for that soon.

[jspricke]

Thanks for the work! For Debian the same source and binary package can be part of multiple releases. Currently this is not reflected in the design resulting in the same package being imported and tested multiple times. Also in Debian source packages can build binary packages for multiple components (not sure if that is important here).

[Nihlus]

Thanks for the work! For Debian the same source and binary package can be part of multiple releases. Currently this is not reflected in the design resulting in the same package being imported and tested multiple times. Also in Debian source packages can build binary packages for multiple components (not sure if that is important here).

Yeah, that was a tradeoff I consciously made to keep things more straightforward. The changes mostly focus on co-existing releases and distros in the same database, not necessarily data sharing between them. From the perspective of this design, different releases might as well be entirely separate distros. It does mean we will be re-testing some packages, but on the flip side implementation is more straightforward and data from multiple databases is easier to merge.

[h01ger]

actually it doesnt have to mean that something is tested several times, as the result could just be copied...

[Nihlus]

I've updated the schema to push blobs out into their own tables and edited the proposed logic to copy build results to newly-registered packages for identical inputs on the same distribution. Additionally, incoming results will be registered for all applicable releases of the same build input.