[Bug]: published_runtime_assets stores a full copy of every JS bundle per page version — 40,534 rows holding 61 distinct files, ~100 MB/month growth

Environment

• Self-hosted, SQLite, Docker (instatic:local built from main around PR #546)
• 45 pages, 19 posts, ~130 media files
• Site has been live since 2026-07-23, 172 publishes

What happens

published_runtime_assets writes a fresh row holding the full bytes of every runtime JS bundle, for every page version, on every publish. Nothing deduplicates by content, and nothing ever removes rows for superseded versions. On our site this table reached 124 MB of blobs across 40,534 rows that hold only 61 distinct files.

Measured on the production DB:

home-v4-hero-E6JSLMDL.js alone is stored 2,689 times. The largest single asset is ~13 kB, so this is purely row count, not asset size.

Growth by month:

2026-07     260 rows    0.3 MB
2026-08   5,876 rows   18.7 MB
2026-09  34,398 rows  104.9 MB

The whole database was 498 MB, of which this table was 148 MB and site_snapshots (one full site_json per publish, ~3.7 MB each) was 307 MB. For a site whose actual content is a few MB.

Why

persistSitePublish loops over every page in the publish and calls savePublishedRuntimeAssets for each one:

server/repositories/publish.ts, in the for (const page of input.pages) loop inside the publish transaction.

That helper (server/repositories/runtimeAsset.ts) inserts one row per file with a fresh nanoid(), with no lookup of existing content:

export async function savePublishedRuntimeAssets(
  db: DbClient,
  dataRowVersionId: string,
  files: BuiltRuntimeAssetFile[],
): Promise<void> {
  for (const file of files) {
    await db`
      insert into published_runtime_assets
        (id, data_row_version_id, asset_path, public_path, content_type, content_bytes)
      values (${nanoid()}, ${dataRowVersionId}, ${file.path}, ${file.publicPath}, ${file.contentType}, ${Buffer.from(file.bytes)})
    `
  }
}

Because publicPath is built from the page's version id, every version gets its own URL for bytes that are usually identical to the previous publish. So the row count is publishes × pages × bundles-per-page, and the bytes scale with it.

Impact

• ~100 MB of database growth per month on a small site, unbounded.
• Backups, VACUUM, and any full-table scan pay for it. Our VACUUM INTO backup takes 522 MB.
• On managed Postgres or a volume-limited host this is a real cost, and there is no documented way to reclaim it. Nothing in the admin UI reports it either; the Overview storage widget reports "Database 489 MB" with no breakdown.

Suggested fix

Store each blob once, keyed by content hash, and reference it:

1. Add a content_sha256 column (or a separate runtime_asset_blobs table keyed by hash) and have savePublishedRuntimeAssets insert the bytes only when the hash is new, otherwise insert just the public_path to hash mapping. For our site that is 61 blobs (~2.5 MB) instead of 40,534 rows (124 MB).
2. Independently, prune assets belonging to superseded data_row_versions beyond a retention window, since only the active version's assets are ever requested. On our site the live pages request exactly one version id's set of 17 files.

Some retention policy for site_snapshots would help for the same reason, whether a count, an age, or compressing site_json.

Workaround

We deleted all but the newest 20 site_snapshots and vacuumed, which took the DB from 498 MB to 262 MB. Note that data_row_versions.site_snapshot_id is on delete set null, and SQLite has PRAGMA foreign_keys off by default, so this has to be run with foreign keys enabled or it leaves dangling references. We have not touched published_runtime_assets yet.