## Context `docs/adr/007_database_schema.md` defines artifact-level storage accounting in full — sections `#### Artifact-level storage accounting` and `#### Artifact-level storage accounting reconciliation`. S22 excludes this layer (`Accounting layers`, `Out of Scope`) and hands it to the format slices; #441 defers the per-format work and asks for one issue per format. This is the npm issue. Note that S22's `Accounting layers` says version `size_bytes` is maintained "synchronously, in the same transaction as the underlying file rows". ADR-007 says the opposite — a buffered counter, like `repositories.size_bytes`. ADR-007 is the authority here; correcting S22's sentence is in scope. ## What exists today | Column | ADR-007 | Schema | Writer | | --- | --- | --- | --- | | `npm_versions.size_bytes` | buffered counter, with a `(namespace_id, npm_package_id, size_bytes DESC) WHERE soft_deleted_at IS NULL` index | **does not exist** | — | | `npm_remote_versions.size_bytes` | same | exists (`internal/datastore/migrations/sql/20260722211605_create_npm_remote_versions.sql:20`) | `AddNpmRemoteVersionSizeBytes` (`internal/datastore/npm_remote_versions.go:408`) has **test-only callers**, and the cache-fill upsert (`:307-330`) omits the column, so it stays `0` | | `npm_packages.versions_count` | buffered counter enforcing ADR-004's 25,000-version cap | exists | `internal/datastore/npm_packages.go:525` (+1), `:1144` (-n) | | `npm_packages.tags_count` | buffered counter enforcing ADR-004's 1,000-tag cap | exists | `npm_packages.go:526`, `:1038`, `:1085`, `:1160` | Version size is read-derived today (`SumNpmFileSizesByPackage`, `internal/datastore/npm_files.go:335`). ADR-007 benchmarks that at 29 ms for a top-50-by-size page against 0.08 ms for the indexed column. S11's buffered-column enumeration omits `npm_versions.size_bytes`; S17's follow-ups record that "no artifact table stores them". ## Work 1. Add `npm_versions.size_bytes bigint NOT NULL DEFAULT 0` and the `size_bytes DESC` partial index. Backfill by set-based recompute grouped by version (ADR-007 measures ~284 ms for ~26K versions). 1. Maintain it per ADR-007: increment when a `blob_sha256` first attaches to the version, decrement when GC hard-deletes the last attachment of that `sha256` within the version, deduplicated within the version. Soft-delete and restore are no-ops for the counter. 1. Wire `npm_remote_versions.size_bytes` on the cache-fill path, or remove the column and its index. 1. Fix whole-package unpublish. `internal/datastore/npm_package_unpublish_deleter.go:246` skips `versions_count` on a comment that is now stale ("the publish-side increment ships later" — it shipped, `npm_packages.go:497`). The tombstoned package row keeps non-zero `versions_count` and `tags_count`, and both are read by the caps (`internal/format/npm/publish_precheck.go:199`, `disttags.go:613`), so the drift is enforcement-visible. 1. Decide the write mechanism for the mutable counters. Every npm site goes through `bufferedUpdate` (`internal/format/npm/buffered.go`): a detached goroutine that sheds at 64 in flight and logs at Warn, with no retry. S22's repository counters can afford that because reconciliation recomputes them; ADR-007's artifact-level reconciliation is specified but unimplemented and unowned. 1. Implement ADR-007's artifact-level reconciliation for npm: distinct `blob_sha256` from `npm_files` joined to `blob_storage_blobs_by_namespace` (that shadow table is created by S22's migration), scoped to one `npm_version_id`. 1. Serialize the version size through S17 once the column exists. ## Decisions required - **`versions_count` soft-delete semantics.** ADR-007 counts soft-deleted rows and decrements only at GC hard-delete, with an explicit anti-cap-gaming rationale. The implemented S11 unpublish decrements at soft-delete, and S17 documents the column as counting active versions. S17's follow-ups already record this as an unreconciled ADR-007 amendment. The cap's behavior depends on the answer, so it settles before the counter is trusted. - **Buffered or in-transaction** maintenance for the npm counters (item 5). - **Wire or drop** `npm_remote_versions.size_bytes` (item 3). ## Done when - `npm_versions.size_bytes` and `npm_remote_versions.size_bytes` are non-zero and correct after a publish / cache fill, and move on file attach and GC detach. - Both unpublish paths leave `versions_count` and `tags_count` consistent with the settled semantics. - The reconciliation recompute matches the maintained value for a seeded version. - ADR-007's decrement rule has a GC-side emitter, or the dependency on S28 is recorded here. Refines #441. --- > 🤖 **Triage note (automated):** This issue has been classified as `type::feature` and labelled with `Category:Artifact Registry`, `devops::package`, and `group::package registry`. > > **Reasoning:** The issue requests net-new functionality — introducing the `npm_versions.size_bytes` buffered counter column (which does not yet exist in the schema), its backfill migration, maintenance logic on file attach/GC detach, reconciliation, and related counter fixes. None of this replaces broken existing behaviour; it implements capability specified in ADR-007 that has not been built yet. That squarely fits `type::feature`. > > If this classification doesn't look right to you, please update the label and let the team know — happy to be corrected!