# dci invariants

The dcode dataset has four rules that bite every aggregation. The
`prelude.sql` shipped alongside this file encodes the first three as
views or macros; the fourth is a JOIN convention you carry yourself.

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

The raw `contrib_branch` table carries 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. ~93K of them.

It also carries 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 bakes them in.

```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 contributes a `usage` increment to each of
their branches. Summing across rows inflates by 100×–1000×.

The ceiling is drupal.org's reported total install base (~645,221
sites as of mid-2026). A SUM that exceeds this is a sign 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
(largest single consumer, distribution histogram, etc.); 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
mentioned by a CR. JOINing it to `contrib_branch` to count adoption
over-counts by 10×–100× (a single contrib branch hitting 5 of a
CR's 50 symbols counts as 5 instead of 1).

`change_record_adoption` is the canonical per-CR-per-branch table.
One row per matched branch, with `state ∈ {legacy, in_flight,
migrated}`. Polarity (paired / from_only / to_only) lives 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 `_seq =
major*1000 + minor` sibling:

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

String comparison silently undercounts: `'10.10' < '10.2'` is true
lexically. Use the `seq(major, minor)` macro from the prelude when
constructing comparisons from raw integers, and join/order on the
`_seq` column directly when one is available.

```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 — `core_symbol.fqn`,
`change_record_symbol` (via the join), `relation_at_branch.source_fqn`
and `target_fqn` — carries the leading-backslash form
(`\Drupal\Core\Field\FieldItemBase`). Match the prefix in WHERE
clauses; LIKE on `'\\Drupal%'` works.