# dci — Drupal contrib insights

> A public, daily-refreshed Parquet mirror: static analysis of the entire Drupal contrib ecosystem (~3,300 modules and themes). Change records and contrib adoption verdicts, core symbol catalogue, contrib branch metadata, class-relation edges, subsystem ownership, drupal.org issue tracker state. Released under CC-BY-SA 4.0. HTTP-readable parquet files at https://api.tresbien.tech/data/latest/; no auth, no rate limit, range requests honoured.

## How to query

The DuckDB CLI (and any httpfs-aware client — Python polars, R arrow) can query the parquet files in place without downloading them:

```
SELECT FLOOR(change_to_seq / 1000) AS core_major,
       COUNT(*) AS records
FROM read_parquet('https://api.tresbien.tech/data/latest/change_record.parquet')
WHERE change_to_seq IS NOT NULL
GROUP BY core_major
ORDER BY core_major;
```

Pandas can stream a whole table:

```
import pandas as pd
df = pd.read_parquet('https://api.tresbien.tech/data/latest/change_record.parquet')
```

Every version-keyed column carries an integer `_seq = major*1000 + minor` alongside the string form. Use the integer for ordering and joins; lexical comparison silently breaks (`"10.10" < "10.2"` lexically). See the invariants doc for the four rules that bite every aggregation.

## Documentation

- [Invariants](https://api.tresbien.tech/data/docs/invariants): four rules every aggregation must respect (dev-branch isolation, _seq integers, denormalised joins, adoption polarity).
- [Cookbook](https://api.tresbien.tech/data/docs/cookbook): twenty-five worked SQL examples — adoption counts, hook-implementation tallies, subsystem coupling (per-bucket + subsystem-pair rollup), PHPStan signal queries, contrib usage of `@internal` / `@deprecated` core APIs.
- [Prelude](https://api.tresbien.tech/data/docs/prelude.sql): the views + macros encoding the invariants, auto-loaded into the browser shell and copy-pasteable elsewhere.
- [Changelog](https://api.tresbien.tech/data/docs/changelog): schema-shape changes between dumps.
- [Manifest](https://api.tresbien.tech/data/latest/manifest.json): per-table row counts, file sizes, dump date.

## Datasets

### Change-record pipeline — what changed between Drupal core versions

- [change_record](https://api.tresbien.tech/data/latest/change_record.parquet): one row per published change record (nid, title, change_from, change_to, fixed date, body).
- [change_record_track](https://api.tresbien.tech/data/latest/change_record_track.parquet): per-track curated metadata + verbatim cr_tracks.yml payload per track.
- [change_record_symbol](https://api.tresbien.tech/data/latest/change_record_symbol.parquet): symbols (classes, methods, functions) impacted by each CR.
- [change_record_issue](https://api.tresbien.tech/data/latest/change_record_issue.parquet): linked drupal.org issues per CR.
- [change_record_adoption](https://api.tresbien.tech/data/latest/change_record_adoption.parquet): sparse (cr_nid, track_idx, branch_id, state) verdict per contrib branch: legacy / in_flight / migrated.
- [change_record_matches](https://api.tresbien.tech/data/latest/change_record_matches.parquet): pre-aggregated CR↔contrib evidence (derived at dump time).
- [change_record_contrib_impact](https://api.tresbien.tech/data/latest/change_record_contrib_impact.parquet): per-project impact summary view.

The `change_record` parquet also carries a `tags VARCHAR[]` column inlined from change_record_tag at dump time — no separate join needed for tag labels (security, BC break, deprecation).

### Core surface — Drupal core symbols and version lifecycle

- [core_symbol](https://api.tresbien.tech/data/latest/core_symbol.parquet): every callable/class/constant in Drupal core, version-keyed.
- [core_path_subsystem](https://api.tresbien.tech/data/latest/core_path_subsystem.parquet): file path → subsystem ownership map.
- [core_coupling_edge](https://api.tresbien.tech/data/latest/core_coupling_edge.parquet): subsystem-to-subsystem coupling edges (what fails together).
- [core_minor_release](https://api.tresbien.tech/data/latest/core_minor_release.parquet): one row per Drupal minor (8.0 … 11.x) with release dates, EOL.

### Contrib branches and class relations

- [contrib_branch](https://api.tresbien.tech/data/latest/contrib_branch.parquet): every dev branch and release tag across contrib, keyed by project+ref.
- [contrib_branch_core_compat](https://api.tresbien.tech/data/latest/contrib_branch_core_compat.parquet): per-branch declared core_version_requirement, resolved to minor.
- [relation_at_branch](https://api.tresbien.tech/data/latest/relation_at_branch.parquet): class-relation triples (`extends`, `implements`, `uses_trait`) per contrib branch — joins to `contrib_branch` on `(project, version) ↔ (project, branch)`.
- [symbol_usage](https://api.tresbien.tech/data/latest/symbol_usage.parquet): per-(`identity_id`, `branch_id`) aggregate (`occurrences`, `file_count`) for non-class-like core symbols. Class-like usage lives in `relation_at_branch`; non-class kinds (hooks, services, twig/js entries, theme hooks, libraries, class constants on the receiver side) live here.

### Issue tracker — drupal.org core issue queue

- [issue](https://api.tresbien.tech/data/latest/issue.parquet): current state of Drupal core issues (~15-minute refresh).
- [issue_subsystem_touched](https://api.tresbien.tech/data/latest/issue_subsystem_touched.parquet): subsystems implicated by each issue's patches.
- [issue_first_fix_date](https://api.tresbien.tech/data/latest/issue_first_fix_date.parquet): one row per issue ever marked Fixed/Closed (fixed) — `(issue_id, fixed_date_unix)`. Derived replacement for the now-private `comment` join.
- [core_issue_subscriber](https://api.tresbien.tech/data/latest/core_issue_subscriber.parquet): `(username, issue_id)` follower pairs scoped to project_id=3060 (Drupal core). Derived replacement for the now-private `issue_user` join.

Free-text comment bodies and individual-author labels (commenter cohorts, stylometric drift verdicts, agent rule-checks) are intentionally not part of the public bundle — see ADR 0001 (public/private parquet split). Aggregated derivatives above expose the joinable signal without shipping the raw text or per-author labels.

### Project + release metadata

- [project](https://api.tresbien.tech/data/latest/project.parquet): drupal.org project catalogue.
- [release](https://api.tresbien.tech/data/latest/release.parquet): release-node metadata (tag, date, security flag).
- [supportedbranch](https://api.tresbien.tech/data/latest/supportedbranch.parquet): supported-branch declarations per project.
- [project_usage_breakdown](https://api.tresbien.tech/data/latest/project_usage_breakdown.parquet): per-version install counts (extracted from drupal.org `entity` JSON blob).

## Common queries

DuckDB SQL. Each example assumes the catalog is attached: `ATTACH 'https://api.tresbien.tech/data/latest/api.duckdb' AS api; USE api;` (then table names below resolve unqualified). Skip the attach if you prefer `read_parquet('https://…/<table>.parquet')` on the raw files — the joins are identical.

Two evidence sources cover usage. **Class-like kinds** (`class`, `interface`, `trait`, `enum`) are tracked in `relation_at_branch` via `extends` / `implements` / `uses_trait` edges keyed on `target_fqn`. **All other kinds** (hooks, services, theme hooks, twig/js entries, libraries, class constants) are tracked in `symbol_usage` via `identity_id`. The class-like rows were intentionally removed from `symbol_usage` to keep the FK shape clean — joining both sides covers the full surface. PHPStan-derived method/function/constant call evidence (`phpstan_callsite`, `phpstan_attribute_use`) is not part of the public bundle.

Every dev branch lives in `contrib_branch` with `kind = 'dev_branch'`. Filter `cb.project <> 'drupal'` to exclude core itself.

### Which contrib modules use a specific core symbol?

Works for any FQN. The CTE dispatches to the right evidence table based on `core_symbol.kind`. Replace the `s.fqn` literal with your target.

```sql
WITH target AS (
  SELECT identity_id, fqn, kind FROM core_symbol
   WHERE fqn = 'Drupal\Core\Entity\EntityInterface'
),
evidence AS (
  SELECT cb.id AS branch_id
    FROM target t
    JOIN relation_at_branch rab ON rab.target_fqn = t.fqn
    JOIN contrib_branch cb
      ON cb.project = rab.project AND cb.branch = rab.version
   WHERE t.kind IN ('class','interface','trait','enum')
  UNION
  SELECT su.branch_id
    FROM target t
    JOIN symbol_usage su ON su.identity_id = t.identity_id
)
SELECT cb.project AS module
  FROM evidence ev
  JOIN contrib_branch cb ON cb.id = ev.branch_id
 WHERE cb.kind = 'dev_branch' AND cb.project <> 'drupal'
 GROUP BY cb.project
 ORDER BY cb.project;
```

### Which contrib modules touch any `@internal` core API?

One row per (module, internal-tagged symbol). `core_symbol.is_internal` is populated from the `@internal` PHPDoc tag during core scaffold scans.

```sql
WITH candidates AS (
  SELECT identity_id, fqn, kind, subsystem FROM core_symbol WHERE is_internal = TRUE
),
evidence AS (
  SELECT c.identity_id, cb.id AS branch_id
    FROM candidates c
    JOIN relation_at_branch rab ON rab.target_fqn = c.fqn
    JOIN contrib_branch cb
      ON cb.project = rab.project AND cb.branch = rab.version
   WHERE c.kind IN ('class','interface','trait','enum')
  UNION
  SELECT c.identity_id, su.branch_id
    FROM candidates c
    JOIN symbol_usage su ON su.identity_id = c.identity_id
)
SELECT cb.project AS module, c.fqn AS symbol_fqn, c.kind, c.subsystem
  FROM candidates c
  JOIN evidence ev      ON ev.identity_id = c.identity_id
  JOIN contrib_branch cb ON cb.id = ev.branch_id
 WHERE cb.project <> 'drupal' AND cb.kind = 'dev_branch'
 GROUP BY cb.project, c.fqn, c.kind, c.subsystem
 ORDER BY cb.project, c.fqn;
```

### Which contrib modules call a `@deprecated` core API still scheduled for removal?

Filters to symbols with both `deprecated_in` and `removal_in` set, so the result is actionable (something will actually break). Sort by `removal_in` to triage by Drupal version deadline.

```sql
WITH candidates AS (
  SELECT identity_id, fqn, kind, deprecated_in, removal_in, replacement
    FROM core_symbol
   WHERE deprecated_in IS NOT NULL AND removal_in IS NOT NULL
),
evidence AS (
  SELECT c.identity_id, cb.id AS branch_id
    FROM candidates c
    JOIN relation_at_branch rab ON rab.target_fqn = c.fqn
    JOIN contrib_branch cb
      ON cb.project = rab.project AND cb.branch = rab.version
   WHERE c.kind IN ('class','interface','trait','enum')
  UNION
  SELECT c.identity_id, su.branch_id
    FROM candidates c
    JOIN symbol_usage su ON su.identity_id = c.identity_id
)
SELECT cb.project AS module,
       c.fqn AS symbol_fqn,
       c.deprecated_in,
       c.removal_in,
       c.replacement
  FROM candidates c
  JOIN evidence ev      ON ev.identity_id = c.identity_id
  JOIN contrib_branch cb ON cb.id = ev.branch_id
 WHERE cb.project <> 'drupal' AND cb.kind = 'dev_branch'
 GROUP BY cb.project, c.fqn, c.deprecated_in, c.removal_in, c.replacement
 ORDER BY c.removal_in, cb.project;
```

### Which contrib modules extend / implement / use a core class, interface, or trait?

Breaks down the relationship kind, so you can tell "extends BaseClass" apart from "uses_trait BaseClassTrait".

```sql
SELECT cb.project AS module, rab.rel_kind, COUNT(*) AS files
  FROM relation_at_branch rab
  JOIN contrib_branch cb
    ON cb.project = rab.project AND cb.branch = rab.version
 WHERE rab.target_fqn = 'Drupal\Core\Entity\ContentEntityBase'
   AND cb.kind = 'dev_branch'
   AND cb.project <> 'drupal'
 GROUP BY cb.project, rab.rel_kind
 ORDER BY files DESC, cb.project;
```

### Top contrib consumers of a given core subsystem

Distinct-symbol count per module, scoped to a subsystem label from `core_symbol.subsystem` (e.g. `'Form API'`, `'Entity'`, `'Cache'`).

```sql
SELECT cb.project AS module,
       COUNT(DISTINCT s.identity_id) AS distinct_symbols
  FROM core_symbol s
  JOIN symbol_usage su ON su.identity_id = s.identity_id
  JOIN contrib_branch cb ON cb.id = su.branch_id
 WHERE s.subsystem = 'Form API'
   AND cb.kind = 'dev_branch'
   AND cb.project <> 'drupal'
 GROUP BY cb.project
 ORDER BY distinct_symbols DESC
 LIMIT 20;
```

### Adoption status of a specific change record across contrib

Replace `cra.cr_nid` with the CR node id from drupal.org/node/{nid}. Multiple tracks per CR mean a project may be `migrated` for one track and `in_flight` for another — `COUNT(*)` aggregates that without losing the breakdown.

```sql
SELECT cb.project AS module,
       cra.state,
       COUNT(*) AS tracks_in_state
  FROM change_record_adoption cra
  JOIN contrib_branch cb ON cb.id = cra.branch_id
 WHERE cra.cr_nid = 3318035
   AND cb.kind = 'dev_branch'
 GROUP BY cb.project, cra.state
 ORDER BY cb.project, cra.state;
```

### Pre-aggregated alternative: `symbol_facts`

The `api.duckdb` catalog ships a `symbol_facts` view that aggregates per-symbol contrib usage counts in one place: `(identity_id, fqn, kind, subsystem, api_scope, added_in, deprecated_in, removal_in, replacement, frequency, dev_project_count, dev_occurrences, cr_nids)`. Cheaper than re-running the joins above when you only need symbol-level stats and not the project list. Pivots the other way: rows are symbols, not modules.

## API

Read-only JSON/CSV endpoints (no auth) at https://api.tresbien.tech/v1/:

- `/v1/issue` and `/v1/issue?follower={username}`: Drupal core issues, optionally scoped to a user's follow list.
- `/v1/issue/{nid}`: single Drupal core issue with touched subsystems and CI coupling.
- `/v1/issue/{nid}/ai-likely`: AGENTS.md rule-check per text block on a core issue (ai_likely label per body + each comment).
- `/v1/project` and `/v1/project/{machine_name}`: contrib project security coverage and per-version usage.
- `/v1/change-record`: change-record list with linked issues (`?project={machine_name}` switches to per-project impact, `?matches=1` to include matches).
- `/v1/change-record/{nid}`: single change-record detail with impacted projects, symbols, maintainers.
- `/v1/search/code` and `/v1/search/repo`: Zoekt-backed full-text search across all indexed contrib code.

## License

Data files: [CC-BY-SA 4.0](https://creativecommons.org/licenses/by-sa/4.0/). Landing-page and pipeline code: MIT.