feat(storage): real BlobStore chunked upload + deferred persistence (S06 Step 8)

What

Extends the real PostgreSQL BlobStore/Session with chunked upload and deferred persistence, per S06 Step 8 of docs/plans/2026-05-15-storage-layer.md. Second real-implementation MR in Stage 2 Track A; builds on Step 7 (!510 (merged)) and re-runs the conformance suite extended by the in-memory stub's chunked-upload MR (Step 3).

• PgBlobStore.NewSession — creates an upload_sessions row (size_bytes=0, hash_state=NULL, UUIDv4 upload_id) and returns a real Session.
• Session.Write / Session.ReadFrom — stream bytes to the staging object via the driver and feed the SHA-256 hasher in lockstep (ReadFrom via io.TeeReader, so the hasher sees exactly the bytes the FileWriter consumes, including the partial prefix before a reader error). Zero DB round-trips.
• Session.Close — flushes size_bytes + serialized hash_state to upload_sessions in one UPDATE, advancing updated_at, via a size_bytes compare-and-swap that returns ErrSessionConflict on a zero-rowcount update. The flush precedes the UPDATE so the DB never records an offset the backend has not durably received (cross-cutting invariant #1); once the staging flush has succeeded the FileWriter is terminal, so a flush failure (G1a), a CAS conflict, or a transient UPDATE error all terminate the session rather than leaving it half-usable.
• Commit / Cancel / ResumeSession / GetSessionStatus remain ErrUnsupportedMethod placeholders — Step 9. The dirty poison-pill on the CAS-loser path is likewise Step 9; Step 8 surfaces the conflict only.

Resolved spec ambiguities

• Deferred Persistence AC-1 ("Per-Write DB hits = 0; per-Close DB hits = 1") is asserted via observable upload_sessions row-state (unchanged across Write, advanced exactly once on Close), not a literal query counter — the labkit client exposes no injectable pgx QueryTracer and database_queries_total is deferred (S03 follow-up). An UPDATE necessarily mutates the row, so row-state is a faithful and mechanically feasible proxy.
• hash_state round-trip "through Close/ResumeSession" — ResumeSession is Step 9, so the round-trip is asserted at the marshalling boundary Step 8 owns: Close serializes the hasher into hash_state; the persisted bytes deserialize into a fresh SHA-256 hasher whose finalized digest equals the digest over the exact bytes written. No dependency on the Step 9 method.

Deferred to later steps (out of Step 8 scope)

#	Criterion	Lands in
Observability AC (Close)	upload_session_hash_state_size_bytes observation per Close	Metrics framework (S03 follow-up)
Deferred Persistence AC-2	Crash between Write and Close; ResumeSession restore + offset-divergence	Step 9
Session Lifecycle AC-4+	ResumeSession / GetSessionStatus / Commit / Cancel	Step 9

Database Review Evidence

Note

The two statements this MR introduces live in internal/storage/pg_session.go (the S06 "Upper-Level Blob Storage Service" owns upload_sessions), which is outside /db-review-prep's internal/datastore/ scope — so this evidence was collected manually following the same methodology. Tracked for a skill follow-up to cover internal/storage/.

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17 container (matching GL_PG_CURR_VERSION in .gitlab-ci-other-versions.yml), with the S06 migrations applied and seed data rolled back per run. upload_sessions was seeded with 20,001 in-flight rows in a single namespace (so they hash to one partition) to make the index path the planner's clear choice. Numbers reflect moderate cardinality, not production scale.

Method	Plan node	Index	Rows (plan / actual)	Cost	Exec time	Buffers (hit / read)	Partitions
storage.pgSession.Close (size_bytes CAS UPDATE)	Index Scan (under Update)	upload_sessions_pNN_namespace_id_upload_id_idx	1 / 1	8.31	0.303 ms	3 / 0 (scan); 23 / 0 (total)	1 of 64 (pruned)
storage.PgBlobStore.NewSession (INSERT)	Insert → Result	n/a (single-row insert)	1 / 1	0.02	0.036 ms	1 / 0 (10 / 0 total)	1 (tuple-routed)

storage.pgSession.Close — size_bytes compare-and-swap UPDATE

Summary: Plan matches intent. The namespace_id literal prunes to a single hash partition (upload_sessions_p59 of 64), and the (namespace_id, upload_id) unique index drives a single-row Index Scan; the size_bytes CAS guard applies as a residual Filter on that one row. Planner estimate matches reality (1 / 1) and execution is 0.3 ms at 20k same-partition rows — the index lookup touches 3 buffer pages, not a scan of the partition. No anomalies.

Seed shape: upload_sessions=20001 (all in the target namespace / partition)

Rendered SQL:

UPDATE public.upload_sessions
SET (size_bytes, hash_state, updated_at) = ($1, $2::bytea, NOW())
WHERE ((upload_sessions.namespace_id = $3::uuid) AND (upload_sessions.upload_id = $4::uuid)) AND (upload_sessions.size_bytes = $5);

Bound args: [4096, <108-byte hash_state>, <namespace_id uuid>, <upload_id uuid>, 0]

Plan (EXPLAIN (ANALYZE, BUFFERS)):

Update on upload_sessions  (cost=0.29..8.31 rows=0 width=0) (actual time=0.104..0.104 rows=0 loops=1)
  Update on upload_sessions_p59 upload_sessions_1
  Buffers: shared hit=23
  ->  Index Scan using upload_sessions_p59_namespace_id_upload_id_idx on upload_sessions_p59 upload_sessions_1  (cost=0.29..8.31 rows=1 width=58) (actual time=0.014..0.014 rows=1 loops=1)
        Index Cond: ((namespace_id = '6d0f522b-de2c-4c7e-9d18-b97d43dc84fc'::uuid) AND (upload_id = '046f180c-93b3-4c60-83e8-9307bec891ea'::uuid))
        Filter: (size_bytes = '0'::bigint)
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=351
Planning Time: 1.382 ms
Execution Time: 0.303 ms

Timings: planning 1.382 ms, execution 0.303 ms, total 1.685 ms.

storage.PgBlobStore.NewSession — upload_sessions INSERT

Summary: Single-row insert into the hash-partitioned parent, tuple-routed to one partition; size_bytes, created_at, updated_at, dirty take schema defaults and hash_state stays NULL. No scan, 0.036 ms. No anomalies.

Seed shape: upload_sessions=20001

Rendered SQL:

INSERT INTO public.upload_sessions (namespace_id, repository_id, upload_id, expires_at)
VALUES ($1::uuid, $2, $3::uuid, $4::timestamp with time zone);

Bound args: [<namespace_id uuid>, 7, <upload_id uuid>, <expires_at = now()+24h>]

Plan (EXPLAIN (ANALYZE, BUFFERS)):

Insert on upload_sessions  (cost=0.00..0.02 rows=0 width=0) (actual time=0.027..0.027 rows=0 loops=1)
  Buffers: shared hit=10
  ->  Result  (cost=0.00..0.02 rows=1 width=113) (actual time=0.004..0.004 rows=1 loops=1)
        Buffers: shared hit=1
Planning Time: 0.011 ms
Execution Time: 0.036 ms

Timings: planning 0.011 ms, execution 0.036 ms, total 0.047 ms.

Query notes:

• No anomalies. The Close UPDATE prunes to one partition and uses the (namespace_id, upload_id) unique index (single-row lookup, CAS guard as a residual filter); the NewSession INSERT is a trivial single-row tuple-routed insert.
• Benign observation: the UPDATE's planning time (1.38 ms) exceeds its execution time (0.30 ms), with 351 planning buffer hits — the expected catalog cost of planning against a 64-partition table. Partition pruning keeps execution to one partition, and in production the prepared-statement plan cache amortizes planning across a session's chunk-append requests.

Checklist

• go build ./... clean
• go test -tags=integration ./internal/storage/... green (conformance + mechanism; verified under -race)
• golangci-lint 0 issues on changed packages
• full pre-commit chain green on the implementation and simplification commits
• /validate-step → /review-branch → /validate-step AI pre-review (pending — kept in Draft)
• Database review evidence for the Close UPDATE / NewSession INSERT (collected manually — see Database Review Evidence below)

Related to #157 (closed)