# Registry publishing: DuckDB Community Extensions + PGXN

Research report (verified 2025‑12, against the actual upstream repos). Goal:
automate, on a `v*` tag push, publishing `mentat_duckdb` to the DuckDB Community
Extensions registry and `pg_mentat` to PGXN — on top of the existing
`.github/workflows/release.yml` (which builds per‑PG tarballs + CLI and makes a
GitHub Release).

Repo: `codeberg.org/gregburd/mentat` (push‑mirrors to `github.com/gburd/mentat`).
CI runs on the GitHub side.

Two truths up front:

- **DuckDB registry:** publishing = **a PR to `duckdb/community-extensions`** that
  edits one `description.yml`. Their CI builds+signs centrally for all platforms.
  First submission is a human‑reviewed PR; version bumps can be a CI‑opened PR,
  but every publish still passes through their PR merge (a human gate).
- **PGXN:** publishing = `pgxn-bundle` + `pgxn-release` inside the
  `pgxn/pgxn-tools` container with `PGXN_USERNAME`/`PGXN_PASSWORD`. Fully
  automatable, **but** our `META.json` is currently wrong (bad `file` path,
  stale version) and the pgrx layout is non‑standard for classic PGXN install.
  Bundle will *upload* but the distribution is not source‑installable the
  classic way. Details below.

---

## 1. DuckDB Community Extensions — `mentat_duckdb`

Upstream verified:
- Repo: `https://github.com/duckdb/community-extensions`
- Submission model doc: `UPDATING.md` in that repo (Community Extension Update Guide).
- Rust extension template: `https://github.com/duckdb/extension-template-rs`
- CI‑tools (reusable build workflow + Makefiles): `https://github.com/duckdb/extension-ci-tools`

### 1.1 The submission model — one `description.yml` per extension

Register by adding **`extensions/<name>/description.yml`** (note: `.yml`, not
`.yaml`) to `duckdb/community-extensions` via PR. There is exactly one descriptor
file per extension; it points at *your* repo + a git ref. Their CI reads it,
clones your repo at that ref, builds, signs, and deploys.

Fields, verified against `scripts/build.py` and real descriptors:

```yaml
extension:
  name: mentat                    # INSTALL/LOAD name; must match Makefile EXTENSION_NAME
  description: <one line>          # required
  version: <string>               # your extension version (free-form; e.g. 1.8.0)
  language: Rust                   # display language
  build: cargo                    # build system: cmake | cargo (cargo => Rust template path)
  license: Apache-2.0
  maintainers:
    - gregburd                    # GitHub usernames
  requires_toolchains: "rust;python3"   # extra toolchains their CI installs before build
  # optional:
  # excluded_platforms: "wasm_mvp;wasm_eh;wasm_threads;windows_amd64_mingw;linux_amd64_musl"
  # vcpkg_url / vcpkg_commit      # only for C/C++ vcpkg deps; N/A for us

repo:
  github: gburd/mentat            # owner/repo that CI clones (the GitHub mirror)
  ref: <full 40-char commit SHA>  # the source ref they build (SHA, not tag — see 1.2)
  # ref_next: <sha>               # only used when validating against next DuckDB (see 1.2)

docs:
  hello_world: |                  # optional, shown on the website
    LOAD mentat;
    SELECT * FROM mentat_query('...');
  extended_description: |         # optional
    ...
```

Note what the descriptor **does not** contain: no `duckdb` version and no
`extension-ci-tools` version. Those are **owned by community‑extensions' own CI**,
not by you (see 1.3). You only declare `build: cargo` + `requires_toolchains: rust`
and point at your repo.

**Real pure‑Rust example** (verified live) —
`extensions/quackformers/description.yml`:

```yaml
extension:
  name: quackformers
  description: Bert-based embedding extension.
  version: 0.1.5.5
  language: Rust
  build: cargo
  license: MIT
  excluded_platforms: "wasm_mvp;wasm_eh;wasm_threads;windows_amd64_mingw;linux_amd64_musl"
  requires_toolchains: "rust;python3"
  maintainers:
    - martin-conur
repo:
  github: martin-conur/quackformers
  ref: 4741f1a317837cd110e5801825ad7af1bbc3bd87
docs:
  hello_world: |
    SELECT embed('this is an embeddable sentence');
```

`quackformers` is built from `duckdb/extension-template-rs` — the same base our
`crates/duckdb` Makefile mirrors. **That's our template.** (Most `query-farm`
Rust extensions such as `crypto`, `lindel`, `evalexpr_rhai` use
`build: cmake` + `requires_toolchains: rust` because they wrap Rust inside a C++
shell; the pure duckdb‑rs path is `build: cargo`, which is what we want.)

### 1.2 How new versions publish

From `UPDATING.md` and `scripts/build.py`:

- The descriptor points at **one active source ref** via `repo.ref`.
- **Updating `repo.ref` (and `extension.version`) and merging that PR** makes the
  new ref the source; community CI rebuilds and re‑deploys for every platform +
  DuckDB version. **Every release = a PR to `duckdb/community-extensions`.**
  There is no webhook/API that pulls your new tag automatically.
- `repo.ref` is a **commit SHA**, not a tag, in practice (all examples use SHAs).
  You can resolve `v1.8.0` → its SHA in CI and write the SHA.
- Community extensions are **also** rebuilt automatically whenever DuckDB itself
  releases a new version — you don't PR for that; it just re‑runs against the
  ref you already have. `ref_next` exists only to let you supply a *different*
  commit for validating against the upcoming (not-yet-stable) DuckDB.

So "automate future releases update the registry" concretely means:
**CI opens a PR to `duckdb/community-extensions` that changes exactly
`extensions/mentat/description.yml` — bumping `extension.version` and `repo.ref`
to the new tag's commit.**

### 1.3 Build & signing are central — version‑lock is a lucky match

Their CI does the build + sign for all platforms and all supported DuckDB
versions. You provide only a buildable repo at `repo.ref`. Verified from
`community-extensions/.github/workflows/build.yml`:

```
DUCKDB_LATEST_STABLE: 'v1.5.6'         # was v1.5.5 until 2026-09-30
DUCKDB_VERSION:       v1.5.6           # default
uses: duckdb/extension-ci-tools/.github/workflows/_extension_distribution.yml@v1.5-variegata
  duckdb_version:    v1.5.6
  ci_tools_version:  v1.5-variegata
  extra_toolchains:  <requires_toolchains from descriptor>
```

**Keep this aligned:** community-extensions builds against
`DUCKDB_LATEST_STABLE`, and `mentat_duckdb` must target exactly that version
(`duckdb = "~1.10506.0"`, `TARGET_DUCKDB_VERSION=v1.5.6`, `USE_UNSTABLE_C_API=1`).
The unstable C API makes the binary load only into the DuckDB version it was
built for, and the registry tests it by loading it into its own DuckDB. When
the registry bumped v1.5.5 -> v1.5.6, our v1.5.5 build failed its Windows test
with "built specifically for DuckDB version 'v1.5.5'". Linux looked green only
because extension-ci-tools skips tests on linux_amd64 (and inside the Linux
Docker build), so only the Windows and macOS jobs actually load the binary.

Every DuckDB patch release therefore needs a mentat release: bump the three
duckdb-rs crates (`1.10X0Y.0` = DuckDB v1.X.Y), `TARGET_DUCKDB_VERSION`, and
`DUCKDB_VERSION` in ci.yml, then PR the new `repo.ref`. The official Rust
template (`extension-template-rs`) works the same way, so this is the normal
maintenance treadmill for duckdb-rs extensions (`UPDATING.md` "Upgrading to a
new DuckDB version").

What our repo must expose for their CI (all already present in `crates/duckdb`):
- `Makefile` including
  `extension-ci-tools/makefiles/c_api_extensions/{base,rust}.Makefile`,
  with `EXTENSION_NAME=mentat`, `USE_UNSTABLE_C_API=1`,
  `TARGET_DUCKDB_VERSION=v1.5.6`, and the `configure`/`debug`/`release`/`test`
  targets. ✅ present.
- `EXTENSION_LIB_FILENAME`/`RUST_LIBNAME` override (our cdylib is
  `libmentat_duckdb.so`). ✅ present.
- The `extension-ci-tools` submodule. ✅ present (pinned to `main`;
  see gotcha below).
- Cross‑platform buildability: the extension is pure duckdb‑rs + rusqlite
  `bundled`, so it should build on linux/macOS/windows. `linux_amd64_musl` is a
  likely `excluded_platforms` (rusqlite/openssl-ish deps); mirror
  `quackformers`' exclusions to start, then relax.

**Gotcha (local, not registry):** our submodule is pinned to `main`
(`20bad04c`), while the community CI uses the tag `v1.5-variegata`. This only
affects *local* `make` builds, not the central build. If a local build breaks
after an upstream `main` change, pin the submodule to `v1.5-variegata` to match
what the registry uses.

### 1.4 Automation recipe (DuckDB)

First submission is **manual** (open the PR to `duckdb/community-extensions`,
their team reviews it). Automation is for the *subsequent* version bumps, and
even then the PR still needs a human merge upstream — you cannot fully
auto‑publish; upstream PR review is a hard gate.

Recommended: maintainer keeps a fork `gburd/community-extensions`. On a `v*`
tag, a job resolves the tag's SHA, edits `extensions/mentat/description.yml` in
the fork, and opens a PR to `duckdb/community-extensions` via
`peter-evans/create-pull-request`.

```yaml
  # New job in release.yml, gated the same way as the others (needs: gate).
  duckdb-registry-pr:
    name: PR to duckdb/community-extensions
    needs: gate
    runs-on: ubuntu-latest
    steps:
      - name: Resolve tag commit SHA
        id: sha
        run: echo "sha=${GITHUB_SHA}" >> "$GITHUB_OUTPUT"

      - name: Check out our fork of community-extensions
        uses: actions/checkout@v4
        with:
          repository: gburd/community-extensions      # maintainer's fork
          token: ${{ secrets.COMMUNITY_EXT_PAT }}     # PAT with repo scope on the fork
          path: community-extensions

      - name: Update descriptor
        working-directory: community-extensions
        run: |
          VER="${GITHUB_REF_NAME#v}"                  # 1.8.0
          SHA="${{ steps.sha.outputs.sha }}"
          mkdir -p extensions/mentat
          cat > extensions/mentat/description.yml <<EOF
          extension:
            name: mentat
            description: Datomic-compatible Datalog engine as a DuckDB extension
            version: ${VER}
            language: Rust
            build: cargo
            license: Apache-2.0
            maintainers:
              - gburd
            requires_toolchains: "rust;python3"
            excluded_platforms: "wasm_mvp;wasm_eh;wasm_threads;windows_amd64_mingw;linux_amd64_musl"
          repo:
            github: gburd/mentat
            ref: ${SHA}
          docs:
            hello_world: |
              LOAD mentat;
              SELECT * FROM mentat_query('/tmp/db.mentat', '[:find ?e :where [?e :db/ident ?a]]');
          EOF

      - name: Open PR upstream
        uses: peter-evans/create-pull-request@v6
        with:
          token: ${{ secrets.COMMUNITY_EXT_PAT }}
          path: community-extensions
          push-to-fork: gburd/community-extensions
          branch: mentat-${{ github.ref_name }}
          commit-message: "mentat ${{ github.ref_name }}"
          title: "Update mentat to ${{ github.ref_name }}"
          body: "Automated descriptor bump for mentat ${{ github.ref_name }} (SHA ${{ github.sha }})."
          # peter-evans opens the PR from the fork branch to the upstream base.
```

Secrets/tokens:
- `COMMUNITY_EXT_PAT` — a fine‑grained or classic PAT owned by the maintainer
  with **`repo`** (contents + pull‑requests) scope on the fork
  `gburd/community-extensions`, allowed to open PRs against the upstream. The
  default `GITHUB_TOKEN` cannot push to another repo, so a PAT (or a GitHub App
  token) is required.

Honest limits:
- **First submission is manual** (human PR + upstream review).
- **Every subsequent publish is a PR upstream** — CI can *open* it, but a DuckDB
  maintainer must **merge** it. There is no auto‑publish past that gate.
- `repo.github` must be the **GitHub** mirror (`gburd/mentat`), since their CI
  clones from GitHub — not the Codeberg canonical.

---

## 2. PGXN — `pg_mentat`

Upstream verified:
- Manager: `https://manager.pgxn.org/` (upload endpoint `POST /upload`).
- Tooling image source: `https://github.com/pgxn/docker-pgxn-tools`
  (Docker Hub image **`pgxn/pgxn-tools`**). This is the canonical current tool —
  ships `pgxn`, `pgxn-bundle`, `pgxn-release`, `pgrx-build-test`, etc.
- Bundle/release scripts read verbatim from that repo's `bin/pgxn-bundle` and
  `bin/pgxn-release`.

### 2.1 Is pg_mentat on PGXN? What publishing requires

Not currently on PGXN (no evidence of a prior release; `META.json` is present but
never bundled/uploaded). To publish you need:

1. A **PGXN account** at `https://manager.pgxn.org/`, approved by the PGXN admins
   (manual, can take a little while — do this first).
2. Then, per release: **`pgxn-bundle`** (validate META + `git archive` a zip) and
   **`pgxn-release`** (HTTP POST the zip to `manager.pgxn.org/upload` with basic
   auth). Both live in the **`pgxn/pgxn-tools`** container.

Confirmed commands & auth from `bin/pgxn-release`:
```
curl --user "$PGXN_USERNAME:$PGXN_PASSWORD" \
     -F 'submit=Release It!' -F "archive=@<dist>-<ver>.zip" \
     -H 'X-Requested-With: XMLHttpRequest' https://manager.pgxn.org/upload
```
Secrets: **`PGXN_USERNAME`** and **`PGXN_PASSWORD`** (your PGXN Manager creds).

`pgxnclient` (the old Python `pgxn` CLI) still exists but the canonical CI path is
the `pgxn/pgxn-tools` image; use it.

### 2.2 What the release ZIP must contain — and our two problems

`pgxn-bundle` does two things (verified from the script):
1. `pgxn validate-meta META.json` — **spec validation only** (structure per
   `meta-spec`), *not* a filesystem check that `provides.*.file` exists.
2. `git archive --prefix "<dist>-<ver>/" ... HEAD` — zips the **entire HEAD tree**
   with the dist prefix.

So the bundle step will *succeed* even with a broken `file` path. But the
resulting distribution is only correct if META matches the tree. Two real issues
in the current `META.json` (`~/ws/mentat/META.json`):

- **Wrong `provides.pg_mentat.file`.** It says `"pg_mentat/pg_mentat.control"`,
  but there is **no `pg_mentat/` dir at the repo root** — the control file lives
  at `crates/pg/pg_mentat/pg_mentat.control`. In the bundled zip the declared
  path won't resolve. Fix the `file` to the real path, e.g.
  `"crates/pg/pg_mentat/pg_mentat.control"` (and `docfile` — `README.md` at root
  is fine).
- **Stale `version`.** META is `1.7.0` (both top‑level and `provides.*.version`);
  releasing v1.8.0 requires bumping both. `Trunk.toml` is likewise `1.7.0`.
  `pgxn validate-meta` won't catch a version *mismatch with the tag* — you must
  keep them in sync yourself (or derive META version from the tag in CI).

Present & valid in META: `name`, `abstract`, `description`, `maintainer`,
`license` (`apache_2_0`), `meta-spec` (1.0.0 + url), `provides`, `resources`,
`tags`. Structurally `pgxn validate-meta` should pass once `version` is bumped;
the `file` path is a *correctness* bug, not a validation failure.

- **pgrx‑vs‑PGXN layout (the honest caveat).** Classic PGXN distributions are
  PGXS source trees: a consumer downloads the zip and runs `make && make install`
  driven by `provides.*.file` (a `.control`) + a PGXS `Makefile`. `pg_mentat` is a
  **pgrx** extension — no PGXS `Makefile`, and the `.control`/SQL are *generated*
  by `cargo pgrx package`, not shipped as static installable files at the paths
  PGXN expects. Consequences:
  - `pgxn-bundle`/`pgxn-release` **will upload** (the Manager only needs a valid
    META + a zip), so the release *appears* on PGXN and is mirrored/searchable.
  - But it is **not `make`‑installable from source** by a classic PGXN user, and
    PGXN's build‑farm won't build a pgrx tree. In practice PGXN becomes a
    *discovery/metadata* channel for `pg_mentat`, while real installation happens
    via the GitHub Release tarballs (already built by `release.yml`) or Trunk‑like
    binary registries. Decide whether a "listed but not source‑installable" PGXN
    entry is worth it. (Several pgrx extensions do exactly this — publish to PGXN
    for discoverability and ship binaries elsewhere.)

### 2.3 Trunk (`pgt.dev` / `Trunk.toml`) — skip it, the registry is defunct

`Trunk.toml` targets the Tembo "Trunk" PG binary registry (`pgt.dev`,
`trunk publish`). Verified 2025‑12:
- `pgt.dev` **no longer resolves in DNS** (dead host).
- The `tembo-io` GitHub org / `tembo-io/trunk` repo returns **Not Found** (gone).
- The `pg-trunk` crate on crates.io hasn't been updated since **April 2025**.

Tembo wound down the hosted Trunk registry. **Do not automate Trunk.** Leave
`Trunk.toml` in place as harmless metadata (or delete it); there's nothing live
to publish to. Revisit only if a successor registry appears.

### 2.4 Automation recipe (PGXN)

Fully automatable (no upstream human gate on publish — the account approval is a
one‑time human step). Add a job to `release.yml` gated the same way (`>= v1.7.0`,
so use the existing `gate` job). Fix META first (2.2) or the uploaded dist is
broken.

```yaml
  pgxn-release:
    name: Publish to PGXN
    needs: gate
    runs-on: ubuntu-latest
    container: pgxn/pgxn-tools      # canonical image; ships pgxn-bundle + pgxn-release
    steps:
      - name: Check out the repo
        uses: actions/checkout@v4
        with:
          submodules: false         # PGXN dist is pg_mentat only; keep the zip lean

      - name: Sync META.json version to the tag (belt-and-suspenders)
        run: |
          VER="${GITHUB_REF_NAME#v}"           # 1.8.0
          # Fail loudly if META wasn't bumped to match the tag.
          MJSON_VER="$(perl -MJSON=decode_json -E 'say decode_json(join "", <>)->{version}' META.json)"
          if [ "$MJSON_VER" != "$VER" ]; then
            echo "ERROR: META.json version ($MJSON_VER) != tag ($VER). Bump META.json." >&2
            exit 1
          fi

      - name: Bundle the release
        id: bundle
        run: pgxn-bundle                       # validates META, writes pg_mentat-<ver>.zip

      - name: Release on PGXN
        env:
          PGXN_USERNAME: ${{ secrets.PGXN_USERNAME }}
          PGXN_PASSWORD: ${{ secrets.PGXN_PASSWORD }}
        run: pgxn-release
```

Secrets: **`PGXN_USERNAME`**, **`PGXN_PASSWORD`** — add to the GitHub repo
settings once the PGXN account is approved.

One‑time maintainer steps:
1. Register at `https://manager.pgxn.org/` and get the account approved.
2. Add `PGXN_USERNAME` / `PGXN_PASSWORD` repo secrets.
3. **Fix `META.json`** (`provides.pg_mentat.file` → real path; bump `version` to
   the release version) — otherwise the uploaded distribution is malformed.
4. Accept the pgrx caveat (2.2): PGXN entry is discovery/metadata, not classic
   source‑install.

---

## 3. Summary table

| | DuckDB Community Extensions | PGXN |
|---|---|---|
| Publish mechanism | PR editing `extensions/mentat/description.yml` in `duckdb/community-extensions` | `pgxn-bundle` + `pgxn-release` (image `pgxn/pgxn-tools`) |
| Build/sign | **Central** (their CI, all platforms, DuckDB v1.5.6, `ci_tools_version v1.5-variegata`) | You bundle a zip; PGXN Manager just stores it |
| Our version‑lock | **Matches** (registry stable = v1.5.6 = our pin) ✅ | n/a |
| First time | Manual PR + upstream review | Create + get PGXN account approved |
| Per release (automatable?) | CI opens PR (needs `COMMUNITY_EXT_PAT`); **merge is a human gate** | Fully automatable (`PGXN_USERNAME`/`PGXN_PASSWORD`) |
| Secrets | `COMMUNITY_EXT_PAT` (fork+PR scope) | `PGXN_USERNAME`, `PGXN_PASSWORD` |
| Non‑standard for us | Unstable‑C‑API version‑lock (normal for duckdb‑rs; breaks on DuckDB bumps) | `META.json` `file` path wrong + version stale; pgrx tree not classic `make`‑installable |
| `repo.github` / source | Must be **GitHub mirror** `gburd/mentat` (their CI clones GitHub) | Bundles whatever is at HEAD of the checked‑out repo |
| Trunk (`pgt.dev`) | — | **Defunct — do not automate** (`pgt.dev` dead, `tembo-io/trunk` gone) |

## 4. Verified sources

- DuckDB community: `github.com/duckdb/community-extensions`
  (`README.md`, `UPDATING.md`, `scripts/build.py`,
  `.github/workflows/build.yml` → `DUCKDB_LATEST_STABLE: v1.5.6`,
  `_extension_distribution.yml@v1.5-variegata`).
- Real descriptors: `extensions/quackformers/description.yml` (pure Rust,
  `build: cargo`), `extensions/crypto/description.yml`,
  `extensions/waddle/description.yml` (in `UPDATING.md`).
- Rust template: `github.com/duckdb/extension-template-rs`
  (`Makefile` → `USE_UNSTABLE_C_API=1`, `TARGET_DUCKDB_VERSION=v1.5.6`;
  `.github/workflows/MainDistributionPipeline.yml`).
- PGXN tooling: `github.com/pgxn/docker-pgxn-tools`
  (`README.md`, `bin/pgxn-bundle`, `bin/pgxn-release`); image `pgxn/pgxn-tools`;
  upload endpoint `https://manager.pgxn.org/upload`.
- Local: `~/ws/mentat/META.json`, `~/ws/mentat/Trunk.toml`,
  `~/ws/mentat/crates/duckdb/Makefile`, `~/ws/mentat/crates/duckdb/Cargo.toml`,
  `~/ws/mentat/.github/workflows/release.yml`,
  `~/ws/mentat/crates/pg/pg_mentat/pg_mentat.control`.
- Trunk defunct: `pgt.dev` no DNS; `github.com/tembo-io/trunk` 404;
  crates.io `pg-trunk` last updated 2025‑04.