Skip to content

D1 Seed: migrate transport from Git LFS to R2

Date: 2026-07-19 Branch: chore/d1-seed-r2-migration (worktree, off develop) Jira: PHON-198 Status: Design — awaiting review

Problem

packages/web/workers/scripts/d1-seed.sql (~474 MB) is the D1 runtime seed and the repo's only Git LFS artifact. It is a derived build artifact (built locally from parquets, which are built from source TSVs), regenerated whenever the lexicon changes — several times in the last month alone (canonical redesign PHON-193, generated images PHON-185/187).

Storing a large, frequently-regenerated derived binary in Git LFS has two costs:

  1. LFS retains every version. Each ~474 MB regen is kept in LFS storage forever and counts against the LFS quota (free tier: 1 GB storage + 1 GB/month bandwidth; then $5/month per 50 GB pack). Every deploy also git lfs pulls the full file.
  2. It is the wrong home for a build artifact. Object storage is where derived binaries belong. The repo carrying LFS at all forces git lfs install on every clone and every worktree.

We now have R2 in the Cloudflare account, and we already run exactly the right pattern for another large artifact: the generated picture-card WebP pixels live on R2, while a small committed mapping (data/mappings/generated_word_images.tsv) is the git-side pointer that dorny/paths-filter watches to trigger a deploy (see deploy.yml lines 31, 67–83). CI already holds R2 S3 credentials (R2_ACCESS_KEY_ID / R2_SECRET_ACCESS_KEY) and an established standalone-boto3 script pattern (verify-generated-images-r2.py).

Goal

Move the seed bytes to R2 and keep a small committed manifest in git as the pointer — applying the picture-card pattern to the seed. Preserve every property Git LFS currently provides, drop the LFS cost and the large-binary-in-git anti-pattern, and add integrity verification the current LFS flow lacks. Zero new CI secrets (reuse the existing R2 creds).

What Git LFS gives today, and how the manifest preserves each

LFS property Preserved by
Seed version pinned to the commit The committed manifest is versioned in git; a checkout at commit X names exactly the R2 object built for X.
Deploy change-detection (paths-filter on the .sql path) paths-filter watches the manifest path instead.
Seed change visible in the PR diff The manifest (object key + sha256 + row counts) changes in the PR diff.
Rollback via git git revert the manifest → points back at the prior (still-present) R2 object.

Non-Goals

  • No change to seed content, to emit_d1_sql, or to the local build up to the point the .sql exists. This is a transport change only.
  • No change to the D1 apply mechanics (wrangler d1 execute --file --remote, the retry wrapper, or the cache-bust comment appends).
  • Not migrating the picture-card R2 flow (already correct).

Design

1. R2 bucket

A dedicated private bucket phonolex-d1-seed (no public custom-domain binding — the seed is proprietary lexicon data and must never be publicly reachable, unlike the picture-card bucket fronted by aac.phonolex.com). One bucket serves both prod and staging; staging objects are unnecessary to separate because prod and staging apply the same seed today.

Objects are content-addressed and immutable: key d1-seed-<sha256>.sql. Immutable keys give natural rollback (old objects remain), avoid overwrite races between concurrent builds, and make the manifest's sha256 the object's identity. An R2 lifecycle rule may later expire objects older than N days while retaining the most recent few (out of scope for this pass; noted).

2. Committed manifest — packages/web/workers/scripts/d1-seed.manifest.json

Small JSON, committed to git (replaces the LFS .sql as the tracked artifact):

{
  "object_key": "d1-seed-<sha256>.sql",
  "sha256": "<hex>",
  "bytes": 497000000,
  "built_at": "2026-07-19T00:00:00Z",
  "git_sha": "<sha of the build checkout>",
  "row_counts": { "words": 125000, "word_properties": 125000, "pairs": 642000 },
  "note": "optional human note, e.g. 'canonical redesign PHON-193'"
}

row_counts is optional but recommended: it makes the PR diff reviewable ("this reseed changes words from 124K → 125K") the way the raw .sql never was.

3. Local build flow

d1-seed.sql becomes a gitignored local build product (like the parquets). After the existing export-to-d1.py (+ chunk-seed-sql.py) produces it, a new script uploads it and writes the manifest:

  • packages/web/workers/scripts/upload-seed-to-r2.py (boto3, mirrors verify-generated-images-r2.py): computes sha256, puts the object at d1-seed-<sha256>.sql (skip if the key already exists — idempotent), writes d1-seed.manifest.json. Uses the developer's R2 credentials from env.

The developer commits only the manifest (a few hundred bytes). CLAUDE.md's Dev Setup section gains the upload step.

4. Deploy flow — deploy.yml and deploy-staging.yml (symmetric)

  1. detect-changes filter: replace packages/web/workers/scripts/d1-seed.sql with packages/web/workers/scripts/d1-seed.manifest.json. (The data/mappings/generated_word_images.tsv path stays.)
  2. Remove lfs: true from the actions/checkout.
  3. New step, gated on data_changed, before the cache-bust step: run packages/web/workers/scripts/fetch-seed-from-r2.py (boto3, reuses R2_ACCESS_KEY_ID/R2_SECRET_ACCESS_KEY). It reads the manifest, downloads object_key to packages/web/workers/scripts/d1-seed.sql, and verifies the sha256 against the manifest. A mismatch or missing object fails the run before the DROP-first seed touches D1 — same fail-safe ordering as the existing image-coverage gate.
  4. The existing cache-bust append + nick-fields/retry apply run unchanged — they operate on the now-downloaded d1-seed.sql at the same path.

Ordering note: sha256 verification must run on the pristine download, before the cache-bust step appends its unique SQL comment (which changes the bytes). The new fetch step is placed immediately before "Bust D1 import cache".

5. Remove Git LFS from the repo

Since the seed is the only LFS artifact:

  • Delete the packages/web/workers/scripts/d1-seed.sql line from .gitattributes.
  • Add d1-seed.sql to .gitignore (local build product).
  • Existing LFS objects remain referenced by old commits (harmless; history is intact). No new LFS objects are created. New clones no longer require git lfs install.

6. Docs + memory

  • CLAUDE.md: the "only LFS artifact" statements (Architecture, Gotchas), Dev Setup (add upload step), CI/Deploy Paradigm (manifest + R2 fetch, not LFS pull).
  • Update the project_ci_deploy_paradigm memory.

Cutover

Single migration PR:

  1. Developer uploads the current seed to R2 and commits the first d1-seed.manifest.json in the same PR (content unchanged — only transport).
  2. PR also lands the two scripts, both workflow edits, the .gitattributes/.gitignore changes, and the doc updates.
  3. On merge, paths-filter sees the new manifest → first R2-based reseed runs on staging. Verify staging D1 is intact, then it rides the normal path to prod.

Because the migration changes transport but not seed content, and the R2 fetch fails the run before the DROP-first seed if anything is wrong, a botched migration cannot half-seed live D1.

Testing / Verification

  • upload-seed-to-r2.py: round-trip locally — upload, confirm object exists, manifest sha256 matches sha256sum of the file, re-run is idempotent (no duplicate upload).
  • fetch-seed-from-r2.py: downloads the manifest's object, sha256 matches; a deliberately wrong sha256 in the manifest fails the step non-zero.
  • Dry-run the staging deploy on a branch: confirm paths-filter triggers on the manifest, the fetch step lands d1-seed.sql, and the existing apply seeds phonolex-staging unchanged.
  • Confirm the R2 token scope covers read on phonolex-d1-seed in CI and the picture-card bucket access is unaffected (same creds, both buckets).

Risks

  • Token scope. The existing R2_ACCESS_KEY_ID/R2_SECRET_ACCESS_KEY must be authorized for the new bucket. If the token is bucket-scoped, add phonolex-d1-seed to its scope (an account action, not a code change) — the one manual prerequisite.
  • CI egress/time. A ~474 MB R2→Actions download per data-deploy. R2 egress is free; download is tens of seconds — comparable to the git lfs pull it replaces.
  • Bucket privacy. phonolex-d1-seed must have no public binding. Verify at creation.

Follow-ups (not this pass)

  • R2 lifecycle rule to age out old seed objects (keep last N).
  • Optionally fold row_counts generation into emit_d1_sql so the manifest is fully build-generated.