# dci invariants

The dataset has four rules that affect every aggregation. The `prelude.sql`
shipped with this file encodes the first three as views or macros. The fourth
is a JOIN convention you apply yourself.

## 1. JOIN `contrib_branch_dev`, not raw `contrib_branch`

The raw `contrib_branch` table has two kinds of rows:

- `kind = 'dev_branch'`: the canonical dev tip per project (11.x,
  10.4.x, …). One row per project per major.
- `kind = 'release_tag'`: one row per tagged release. About 93K of them.

It also has one row for Drupal core itself (`project = 'drupal'`).

Dev-branch aggregations must apply both filters every time. The
`contrib_branch_dev` view in the prelude applies them for you.

```sql
-- right
SELECT COUNT(*) FROM contrib_branch_dev;

-- wrong: double-counts release tags + leaks core
SELECT COUNT(*) FROM contrib_branch;
```

Tag-axis queries ("what shipped at 10.4.0?") are the exception. JOIN the raw
table with `kind = 'release_tag'` explicitly.

## 2. Never `SUM(usage)` across rows

`contrib_branch.usage` is the install count for that branch. A site running
100 modules adds one to the `usage` of each of their branches. Summing across
rows inflates the result by 100 to 1000 times.

The ceiling is drupal.org's reported total install base, about 645,221 sites
as of mid-2026. A SUM above it means you double-counted.

Two correct shapes:

```sql
-- distinct project count
SELECT COUNT(DISTINCT project) FROM contrib_branch_dev WHERE …;

-- per-project install base via MAX (the install count is
-- the same on every dev branch of a project)
SELECT SUM(per_project_sites)
FROM (
  SELECT MAX(usage) AS per_project_sites
  FROM contrib_branch_dev WHERE …
  GROUP BY project
);
```

Per-branch `contrib_branch.usage` stays valid at the branch level: the
largest single consumer, a distribution histogram, and so on. The rule is
about cross-row SUM.

## 3. Read `change_record_adoption`, not `change_record_symbol` JOINs

`change_record_symbol` is denormalized 1:N, one row per symbol a change
record mentions. JOINing it to `contrib_branch` to count adoption over-counts
by 10 to 100 times. One contrib branch hitting 5 of a record's 50 symbols
counts as 5 instead of 1.

`change_record_adoption` is the canonical per-record, per-branch table. One
row per matched branch, with `state` in `legacy`, `in_flight`, `migrated`.
Polarity (`paired`, `from_only`, `to_only`) is on the parent
`change_record_track` row. `no_match` branches are implicit: the in-range
universe minus the matched rows.

```sql
-- right
SELECT state, COUNT(*) FROM change_record_adoption
WHERE cr_nid = 1234567 GROUP BY state;

-- wrong: over-counts on multi-symbol CRs
SELECT COUNT(DISTINCT cb.id)
FROM change_record_symbol crs
JOIN contrib_branch_dev cb ON …;
```

The prelude's `adoption_state(cr_nid, branch_id)` macro is the one-call form.

## 4. Version comparison: use `_seq`, not strings

Every version-keyed column in the bundle has an integer sibling, `_seq =
major*1000 + minor`:

- `change_record.change_to_seq`
- `contrib_branch_core_compat.minor_seq`

String comparison silently undercounts: `'10.10' < '10.2'` is true as text.
Use the `seq(major, minor)` macro from the prelude to build a comparison from
raw integers. Join and order on the `_seq` column when one exists.

```sql
-- right
SELECT * FROM change_record WHERE change_to_seq >= seq(10, 4);

-- wrong: lexical compare
SELECT * FROM change_record WHERE change_to >= '10.4';
```

## On FQN form

Every FQN column in the bundle holds the leading-backslash form
(`\Drupal\Core\Field\FieldItemBase`): `core_symbol.fqn`,
`change_record_symbol` through the join, `relation_at_branch.source_fqn` and
`target_fqn`. Match the prefix in WHERE clauses; LIKE on `'\\Drupal%'` works.