feat(datastore): migrate primary keys to UUIDv7 (except blob_storage_* and upload_sessions)

David Fernandezrequested to merge
10io/migrate-pks-to-uuidv7 into main

🧭 Summary

Migrates primary keys from bigint to UUIDv7 across the schema, so API-exposed entities no longer mix identifier types (bigint vs uuid) in responses. UUIDv7 ids are generated application-side (RFC 9562); the converted tables drop their *_id_seq sequence and column default. The database carries no data in any environment yet, so migrations are edited in place rather than added.

📏 Scope

The rule: only the blob-storage tier keeps a bigint sequence-backed id.

  • ✅ Keep bigint id: blob_storage_attachments, blob_storage_blobs, and upload_sessions (internal storage-session state, never API-exposed).
  • 🔀 Flip id → uuid (19 tables): repository_collections, repositories, container_repositories, container_images, container_blobs, container_manifests, container_manifest_relationships, container_tags, npm_repositories, npm_packages, npm_versions, npm_files, npm_tags, npm_metadata_files, maven_repositories, maven_packages, maven_versions, maven_files, and the repository_collection_repositories join (FK columns only). namespaces was already uuid.
  • Every foreign-key column referencing a converted table flips to uuid. Columns referencing the blob-storage tier (for example blob_storage_attachment_id) stay bigint. upload_sessions.repository_id is uuid because it holds a repositories.id, even though the table keeps a bigint primary key.

⚠️ Breaking change

The management API serializes Repository.id as a uuid string instead of an integer. api/openapi/v1.yaml is updated accordingly. This is the intended outcome of #185 (closed) (no mixed identifier types in responses); names and slugs remain the addressable identifiers.

🛠️ Implementation notes

  • Datastore stores generate ids via a shared newID() helper (internal/datastore/ids.go, uuid.Must(uuid.NewV7())) and include id in each INSERT, dropping the now-redundant sequence-backed RETURNING. Idempotent ON CONFLICT DO NOTHING upserts keep reading back the existing row on conflict.
  • structure.sql and the jet models are regenerated from the converted schema.
  • Schema integration tests assert uuid for converted tables and bigint for the blob-storage tier and upload_sessions; docs/dev/database.md documents the new rule.

✅ Validation

  • go build ./..., go vet ./... and go vet -tags=integration ./...: clean.
  • Unit suite and the full integration suite (go test -tags=integration ./...): green.
  • Migrations apply and round-trip; squawk lint clean; structure.sql and jet regeneration show no drift; OpenAPI validates.

📝 Follow-up

ADR 007 and ADR 009 document the previous bigint+sequence rule but are synced from the handbook repo and cannot be edited here. A separate handbook MR will update both to the new rule (noting that application-side UUIDv7 removes the server-side sequence that was the logical-replication desync concern, while UUIDv7's time-ordering keeps B-tree insert locality close to a bigserial).

Related to #185 (closed)

Merge request reports