Migrate project build payloads from PostgreSQL to object storage

[kof]

Problem

Webstudio project builds currently store pages, instances, styles, resources, and other large namespaces directly in PostgreSQL Build columns. This makes PostgreSQL the payload store as well as the control plane and does not provide a path for moving large build data to object storage.

The asset/content work in #5919 provides portable content compilation and object-backed asset bytes, but it does not abstract authoritative project-build persistence.

Desired outcome

Store project-build payloads in R2-compatible object storage while keeping PostgreSQL as the small authoritative control plane for identity, versioning, deployment state, and payload location.

New projects should use object-backed build payloads after rollout. Existing projects should remain readable without a bulk backfill and migrate safely on their next successful edit or publish.

Constraints

• Preserve current Builder, CLI, MCP, collaboration, import, restore-point, and publishing behavior.
• Do not require a database for independently hosted generated sites.
• Preserve optimistic concurrency and idempotent transaction behavior.
• A failed or interrupted migration must leave the previous PostgreSQL build readable and authoritative.
• Historical production builds must remain redeployable.
• Reuse the existing portable build/state serialization; do not reuse content-source, content-database, or local project-session cache abstractions for authoritative build persistence.
• Avoid a mandatory all-project backfill.

Migration checklist

Storage contract and data model

• Define and version one portable serialized build-payload envelope using the existing build/state serializers.
• Introduce an authoritative build repository boundary covering development and production build reads, creation, compare-and-swap updates, publication snapshots, deployment metadata, and deletion.
• Keep PostgreSQL as the control plane and add the minimum storage metadata needed to locate and verify a payload: backend, object key, object revision/ETag, format version, and checksum.
• Define stable object-key ownership and lifecycle rules for development builds, immutable production builds, retries, replacement, and deletion.

Adapters

• Implement a PostgreSQL adapter that preserves the current behavior for legacy builds.
• Implement an R2-compatible payload adapter with integrity verification and conditional writes.
• Implement a hybrid migration adapter: read R2 when a valid pointer exists; otherwise read the legacy PostgreSQL payload.
• Keep the repository interface independent of PostgREST rows, R2 bindings, and application request context.

Cutover and migration

• Create new project development builds directly in R2 after the rollout is enabled.
• On the next successful edit of a legacy development build, write and verify the complete payload in R2 before atomically switching its PostgreSQL control-plane pointer.
• On publish, migrate the development payload when necessary and create an immutable R2-backed production snapshot.
• Preserve the PostgreSQL payload as the fallback until the pointer transition is committed; retries must be safe and idempotent.
• Define cleanup for abandoned uploads and superseded development payloads without placing cleanup on the publish critical path.
• Support historical production-build reads, domain operations, unpublish, and redeployment throughout the migration.

Operator-run bulk migration

• Provide a non-lazy migration command that can move selected or all legacy build payloads from PostgreSQL to R2 without waiting for an edit or publish.
• Support project, workspace, build-type, age, and bounded-batch selection so operators can migrate incrementally.
• Provide dry-run output with build counts, estimated payload bytes, invalid builds, and already-migrated builds before writing anything.
• Make the command resumable and idempotent with bounded concurrency, checkpoints, retry limits, and a clear per-build result.
• For each build, write and verify the R2 payload before atomically switching the PostgreSQL control-plane pointer; leave PostgreSQL authoritative on any failure.
• Support a verification-only mode that compares stored checksums and reports missing, corrupt, or mismatched R2 objects.
• Emit an audit report suitable for rollout monitoring and targeted retry of failed builds.
• Require an explicit confirmation flag for mutations and document a safe staged rollout and rollback procedure.

Integration

• Move build creation, loading, patching, imports, restore points, publishing, unpublishing, domain lookup, sitemap lookup, and collaboration state behind the repository boundary.
• Identify and retain only genuinely control-plane PostgreSQL queries; remove direct payload reads and writes outside the adapter.
• Preserve current build-version conflict behavior for concurrent Builder, CLI, MCP, and collaboration requests.
• Preserve the ordering and failure guarantees between build patches and asset/folder mutations without requiring a cross-service distributed transaction.
• Keep existing public API, CLI sync, MCP, and project-session transport contracts storage-neutral.

Verification and rollout

• Add shared adapter contract tests so PostgreSQL, R2, and hybrid adapters return equivalent builds and errors.
• Add tests for concurrent edits, stale versions, duplicate retries, partial R2 writes, checksum failures, missing objects, and R2 outages.
• Add migration tests for untouched legacy projects, first edit, first publish, new projects, imports, clones, restore points, and historical production builds.
• Add end-to-end verification for Builder editing, CLI/MCP editing, publishing, unpublishing, custom domains, and redeployment before enabling the new default.
• Add migration state and failure observability sufficient to identify which backend owns a build and safely retry incomplete transitions.
• Roll out gradually, verify hybrid reads and migrations, then make R2 the default for new projects.
• Define when legacy PostgreSQL payload columns may be cleared; do not remove them until rollback and retention requirements are satisfied.

Acceptance criteria

• New projects store authoritative build payloads in R2-compatible object storage.
• Existing PostgreSQL-backed projects open without migration and can move to R2 either on their next successful edit or publish or through the operator-run bulk migration command.
• PostgreSQL contains only build control-plane metadata for migrated builds.
• No client needs to know which storage backend owns a build.
• Concurrent updates, failed migrations, retries, publication, and historical redeployment are covered by automated tests.
• Operators can dry-run, execute, resume, verify, audit, and selectively retry bulk migration without making builds unreadable.
• The rollout can be stopped or rolled back without making existing projects unreadable.

Related: #5919