[Feature]: Prune site_snapshots no active page version references #651

Problem

Every full-site publish inserts one row into site_snapshots with the whole published SiteDocument (persistSitePublish, server/repositories/publish.ts), and nothing ever deletes one. The table grows by the size of the site on every publish, for as long as the instance runs.

Nothing reads the old rows:

• Every reader (getPublishedPageBySlug, getPublishedPageSnapshotById, getLatestPublishedSiteSnapshot, the publish-status check) joins through data_rows.active_version_id. They only ever reach the snapshot behind a page's active version.
• Row version history (listDataRowVersions / getDataRowVersion, server/repositories/data/versions.ts) reads cells_json and slug from data_row_versions. It doesn't read site_snapshots.
• data_row_versions.site_snapshot_id is on delete set null, so deleting a snapshot leaves the version rows and their history in place.

Measured on two self-hosted 0.0.21 instances (2026-10-15, read-only SQL):

On instance A each snapshot is about 1.5 MB and about 30 are added on a busy day. The agent publishes after every round of edits, so the table grows fastest exactly where the editor is used most.

Proposed solution

Prune snapshots that no active page version references, in the same transaction as the publish, right after the new versions are activated. This mirrors #647, which does the same for published_runtime_assets:

delete from site_snapshots s
where s.id <> $newSnapshotId
  and not exists (
    select 1
    from data_rows r
    join data_row_versions v on v.id = r.active_version_id
    where v.site_snapshot_id = s.id
  )

The query keeps the new snapshot and every snapshot still serving a page. That includes pages whose active version came from an earlier publish, such as pages published individually. The snapshot of a version that only appears in the history list is dropped. Its site_snapshot_id becomes null, which nothing reads today.

Open question for maintainers: should site-level history be kept for a future rollback? No rollback exists today; active_version_id only moves forward on publish. If one is planned, keep the last N snapshots (or the last N days) as well as the referenced ones, and make N configurable. Without such plans, keeping only the referenced snapshots is the simplest rule and bounds the table at about one site document per distinct active publish.

Existing installs: the first publish after the change deletes the backlog in one go. On Postgres the space is only released to the OS by VACUUM FULL site_snapshots (or pg_repack). That is worth a line in the release notes.

Alternatives considered

• Content-addressed snapshots (dedupe on content_hash) don't help much. Almost every publish changes the site, so the hashes rarely repeat.
• A periodic cleanup job instead of pruning on publish works too, but it adds a scheduler for something that only changes when a publish runs.

Related: #647 (runtime-asset pruning), #650 (publish off the main thread).

Area

Publishing