feat(npm): package and file publish writes (S11 Step 5)

📦 What

Step 5 of the npm hosted plan, sliced into four stacked ~500-LOC MRs. Slice 2/4 — the package & file publish-side write methods, plus the shared integration-test harness the later slices reuse.

Store file	Write methods
npm_packages.go	UpsertNpmPackage — insert, or return the existing row id on conflict
npm_files.go	InsertNpmFile

Both mutate inside the publish transaction via a qrm.DB handle (a *sql.Tx satisfies it); writes live in the flat per-entity store files (ADR 023).

Foreign-key violations on the parent rows (repository / version / attachment) are mapped to leak-safe sentinels (ErrParentNpmRepositoryMissing, ErrParentNpmVersionMissing, ErrParentBlobStorageAttachmentMissing) returned unwrapped, so PgError.Detail can't echo the violated FK's key columns (the parent id and namespace UUID, plus blob_sha256 for the attachment FK; never the package or file name, which is not part of the FK) - matching blob_storage_attachments.go. The tarball digest is carried as a fixed-length checksum.SHA256Sum, so a truncated or malformed digest is rejected at the call site rather than at the DB octet_length CHECK — the same compile-time guarantee blob_storage_attachments.Create relies on.

✅ Testing

internal/datastore/npm_write_integration_test.go (integration, testcontainers PostgreSQL): UpsertNpmPackage insert / conflict idempotency / @scope sigil; InsertNpmFile referencing an attachment, plus failure subtests asserting a bad NpmRepositoryID / NpmVersionID / BlobStorageAttachmentID each surfaces the matching leak-safe sentinel (ErrParentNpmRepositoryMissing / ErrParentNpmVersionMissing / ErrParentBlobStorageAttachmentMissing), and ghost-namespace subtests pinning the FK classification (a nonexistent namespace takes the generic wrap for packages and surfaces the attachment sentinel for files, since the composite parent FKs fire before the namespaces FK). Argument-guard unit tests in npm_packages_test.go / npm_files_test.go (non-integration) cover the nil-ctx / nil-db / zero-namespace sentinels in the fast job. Lands the shared helpers (beginTx, cleanupNpmRowsForNamespace, validPackageJSON) the version / tag / metadata slices reuse; validPackageJSON builds the fixture via json.Marshal, and cleanupNpmRowsForNamespace deletes tags before versions (the npm_tags→npm_versions FK).

Size: ~600 LOC (~50% test). Above the 500-LOC reviewable ceiling, but ~300 LOC is the integration test plus the shared harness the version / tag slices reuse; the production surface is two short write methods with their sentinels and input structs. Kept as one cohesive slice per the stack plan.

🔗 Stack

Step 5 was split into four stacked MRs (each targets the previous; GitLab auto-retargets to main as they merge):

1. !499 (merged) — package_json schema validator — merged
2. !503 (merged) — package & file writes + shared test harness (this MR; now targets main)
3. !504 (merged) — version writes
4. !505 (merged) — dist-tag writes

The metadata-cache writes that originally shared slice 4 were carved out: the Step 4b reorg removed the npm_metadata_files read layer they build on (deferred to a later step), so they follow it there.

Database Review Evidence

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17 container (matching GL_PG_CURR_VERSION from .gitlab-ci-other-versions.yml), with synthesized seed data rolled back per query and the container torn down at the end of the run. Numbers reflect moderate cardinality and do not capture production-scale effects. See Database review evidence for seed sizing, methodology, and the anomalies the skill flags. Expand each row's details for the seed shape, rendered SQL, bound args, and raw plan.

Method	Plan node	Index	Rows (plan / actual)	Cost	Time	Buffers (hit / read)	Partitions
npm_files.InsertNpmFile	Insert	n/a	1 / 1	0.01	0.223ms	37 / 4	1
npm_packages.UpsertNpmPackage	Insert	unique_npm_packages_ns_id_repo_id_name (conflict arbiter)	1 / 1	0.01	0.177ms	49 / 3	1
npm_packages.npmPackageIDByName	Index Scan	unique_npm_packages_ns_id_repo_id_name	1 / 1	8.30	0.009ms	3 / 0	1

npm_files.InsertNpmFile

Summary: Plan matches intent: a plain partition-routed Insert into npm_files_p29 (the namespace_id hash partition), returning the new id. The three FK-validation triggers fire as expected — the composite (blob_storage_attachment_id, namespace_id, blob_sha256) attachment FK plus the scalar namespace and npm_version FKs — accounting for the bulk of the 2.1ms execution time. No anomalies.

Seed shape: namespaces=1, repositories=1, npm_repositories=1, npm_packages=1, npm_versions=1, blob_storage_blobs=1, blob_storage_attachments=1

Rendered SQL:

INSERT INTO public.npm_files (namespace_id, npm_version_id, blob_storage_attachment_id, file_name, blob_sha256)
VALUES ($1::uuid, $2, $3, $4::text, $5::bytea)
RETURNING npm_files.id AS "npm_files.id";

Bound args: [<namespace_id uuid>, <npm_versions.id>, <blob_storage_attachments.id>, 'review-prep-pkg-1.0.0.tgz', decode(repeat('ab',32),'hex')]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Insert on npm_files  (cost=0.00..0.01 rows=1 width=120) (actual time=0.223..0.223 rows=1 loops=1)
   Buffers: shared hit=37 read=4 dirtied=9 written=5
   ->  Result  (cost=0.00..0.01 rows=1 width=120) (actual time=0.026..0.026 rows=1 loops=1)
         Buffers: shared hit=12
 Planning Time: 0.042 ms
 Trigger for constraint fk_npm_files_blob_storage_attachment_id_bsa on npm_files_p29: time=0.818 calls=1
 Trigger for constraint fk_npm_files_namespace_id_namespaces on npm_files_p29: time=0.148 calls=1
 Trigger for constraint fk_npm_files_npm_version_id_npm_versions on npm_files_p29: time=0.657 calls=1
 Execution Time: 2.142 ms

Timings: planning 0.042ms, execution 2.142ms, total 2.184ms.

npm_packages.UpsertNpmPackage

Summary: Plan matches the idempotent-upsert intent: an Insert with Conflict Resolution: NOTHING whose arbiter is unique_npm_packages_ns_id_repo_id_name — the partial unique index carrying the same WHERE soft_deleted_at IS NULL predicate the method's ON_CONFLICT ... WHERE builds, confirming the arbiter resolves. Routed to a single namespace_id partition (npm_packages_p45); both parent FK triggers fire. No anomalies.

Seed shape: namespaces=1, repositories=1, npm_repositories=1

Rendered SQL:

INSERT INTO public.npm_packages (namespace_id, npm_repository_id, name, scope)
VALUES ($1::uuid, $2, $3::text, NULL)
ON CONFLICT (namespace_id, npm_repository_id, name) WHERE soft_deleted_at IS NULL DO NOTHING
RETURNING npm_packages.id AS "npm_packages.id";

Bound args: [<namespace_id uuid>, <npm_repositories.id>, 'review-prep-pkg'] (the scope column renders as a literal NULL, not a placeholder, for an unscoped package)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Insert on npm_packages  (cost=0.00..0.01 rows=1 width=120) (actual time=0.177..0.177 rows=1 loops=1)
   Conflict Resolution: NOTHING
   Conflict Arbiter Indexes: unique_npm_packages_ns_id_repo_id_name
   Tuples Inserted: 1
   Conflicting Tuples: 0
   Buffers: shared hit=49 read=3 dirtied=7 written=4
   ->  Result  (cost=0.00..0.01 rows=1 width=120) (actual time=0.022..0.023 rows=1 loops=1)
         Buffers: shared hit=12
 Planning:
   Buffers: shared hit=56
 Planning Time: 0.129 ms
 Trigger for constraint fk_npm_packages_namespace_id_namespaces on npm_packages_p45: time=0.080 calls=1
 Trigger for constraint fk_npm_packages_npm_repository_id_npm_repositories on npm_packages_p45: time=0.178 calls=1
 Execution Time: 0.503 ms

Timings: planning 0.129ms, execution 0.503ms, total 0.632ms.

npm_packages.npmPackageIDByName

Summary: Plan matches the conflict read-back intent: an Index Scan over unique_npm_packages_ns_id_repo_id_name (shown as its per-partition child on npm_packages_p00), with the namespace_id literal pruning to one of 64 partitions and the index covering the full (namespace_id, npm_repository_id, name) predicate plus the soft_deleted_at IS NULL partial clause. Plan and actual rows match (1 / 1) and execution stays under 20us against 5000 seeded packages in the partition. No anomalies.

Seed shape: namespaces=1, repositories=1, npm_repositories=1, npm_packages=5000

Rendered SQL:

SELECT npm_packages.id AS "npm_packages.id"
FROM public.npm_packages
WHERE (((npm_packages.namespace_id = $1::uuid) AND (npm_packages.npm_repository_id = $2)) AND (npm_packages.name = $3::text)) AND (npm_packages.soft_deleted_at IS NULL)
LIMIT $4;

Bound args: [<namespace_id uuid>, <npm_repositories.id>, 'review-prep-pkg-002500', 1] ($4 is the jet-rendered placeholder for the constant LIMIT(1))

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..8.30 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using npm_packages_p00_namespace_id_npm_repository_id_name_idx on npm_packages_p00 npm_packages  (cost=0.28..8.30 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1)
         Index Cond: ((namespace_id = '1e41b29c-dcc8-454f-a22b-f8ed39adb09d'::uuid) AND (npm_repository_id = '3'::bigint) AND (name = 'review-prep-pkg-002500'::text))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=139
 Planning Time: 0.440 ms
 Execution Time: 0.017 ms

Timings: planning 0.440ms, execution 0.017ms, total 0.457ms.

Query notes:

• No anomalies. All three statements are single-partition writes/reads against the namespace_id hash partitioning (ADR 022). npmPackageIDByName uses the partial unique index unique_npm_packages_ns_id_repo_id_name with plan rows matching actual (1 / 1) at 5000 seeded rows; UpsertNpmPackage resolves its ON CONFLICT arbiter to that same index; InsertNpmFile is a plain partition-routed insert whose FK-validation triggers (composite attachment FK + namespace + version) dominate its time, as expected for a write that validates three parents.

Related to #122 (closed)