Skip to content

D1 Seed LFS → R2 Migration Implementation Plan

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development to implement Tasks 1-4. Task 5 (cutover) is developer-run (needs the local ~474 MB seed + R2 creds). Steps use checkbox (- [ ]) syntax.

Goal: Move the ~474 MB d1-seed.sql bytes to a private R2 bucket, keep a small committed d1-seed.manifest.json as the git pointer, add sha256 verification, and remove Git LFS from the repo — transport only, no seed-content change.

Architecture: Local build uploads the seed to content-addressed R2 (d1-seed-<sha256>.sql) and writes a manifest; deploy watches the manifest via paths-filter, downloads + sha256-verifies before the existing D1 apply. Mirrors the picture-card R2 pattern (verify-generated-images-r2.py, bucket phonolex-aac-images).

Tech Stack: Python 3.12 + boto3 (via uv run --with boto3); GitHub Actions; Cloudflare R2 (S3 API) + wrangler d1.

Global Constraints

  • Spec: docs/superpowers/specs/2026-07-19-d1-seed-r2-migration-design.md.
  • R2: private bucket phonolex-d1-seed, endpoint https://1d876879deb11707881d1bf1fc5cd467.r2.cloudflarestorage.com, creds R2_ACCESS_KEY_ID / R2_SECRET_ACCESS_KEY (already in CI + developer env; no new secrets). Bucket already created + token authorized (confirmed by user).
  • Object keys are content-addressed + immutable: d1-seed-<sha256>.sql. Upload is idempotent (skip if key exists).
  • Manifest path (the git pointer): packages/web/workers/scripts/d1-seed.manifest.json.
  • Follow the existing R2-script conventions in verify-generated-images-r2.py: ACCOUNT_ID/BUCKET/ENDPOINT module constants; import boto3 inside main() (so pure helpers import without boto3); REPO_ROOT = Path(__file__).resolve().parents[4]; exit non-zero with a clear message on failure.
  • sha256 verification in the deploy MUST run on the pristine download, before the "Bust D1 import cache" step appends bytes.
  • Do NOT change seed content, emit_d1_sql, chunk-seed-sql.py (local-only), or the wrangler d1 execute apply/retry mechanics.
  • Tests: pure helpers (sha256, manifest build, verify) unit-tested with tmp files + a fake S3 client (monkeypatch), no network, no real boto3 needed. Run: uv run --with boto3 python -m pytest <file>.
  • Gate each code task: the task's pytest + (for YAML) python -c "import yaml,sys; yaml.safe_load(open(f))" parse check on both workflows.

Task 1: upload-seed-to-r2.py + manifest writer

Files: - Create: packages/web/workers/scripts/upload-seed-to-r2.py - Test: packages/web/workers/scripts/test_seed_r2.py

Interfaces: - Produces (pure, importable without boto3): - sha256_of(path: Path) -> str (hex, streamed in 1 MiB chunks) - build_manifest(*, object_key: str, sha256: str, bytes_: int, built_at: str, git_sha: str, row_counts: dict | None, note: str | None) -> dict - object_key_for(sha256: str) -> str → f"d1-seed-{sha256}.sql" - main() does the boto3 I/O: sha256 the local d1-seed.sql, head_object to skip-if-exists, else upload_file, then write d1-seed.manifest.json (pretty JSON, trailing newline).

  • [ ] Step 1: Write failing tests (test_seed_r2.py)
import hashlib, json
from pathlib import Path
import importlib.util

# Load the hyphenated module by path.
_spec = importlib.util.spec_from_file_location(
    "upload_seed_to_r2", Path(__file__).with_name("upload-seed-to-r2.py"))
up = importlib.util.module_from_spec(_spec); _spec.loader.exec_module(up)

def test_sha256_of(tmp_path):
    p = tmp_path / "s.sql"; p.write_bytes(b"hello seed")
    assert up.sha256_of(p) == hashlib.sha256(b"hello seed").hexdigest()

def test_object_key_is_content_addressed():
    assert up.object_key_for("abc123") == "d1-seed-abc123.sql"

def test_build_manifest_shape():
    m = up.build_manifest(object_key="d1-seed-ab.sql", sha256="ab", bytes_=10,
                          built_at="2026-07-20T00:00:00Z", git_sha="deadbee",
                          row_counts={"words": 5}, note="x")
    assert m["object_key"] == "d1-seed-ab.sql" and m["sha256"] == "ab"
    assert m["bytes"] == 10 and m["row_counts"] == {"words": 5}
    json.dumps(m)  # serializable

  • [ ] Step 2: Run — expect FAIL (module missing). Run: cd packages/web/workers/scripts && uv run --with boto3 python -m pytest test_seed_r2.py -q

  • [ ] Step 3: Implement upload-seed-to-r2.py

Module constants (ACCOUNT_ID, BUCKET="phonolex-d1-seed", ENDPOINT), REPO_ROOT = Path(__file__).resolve().parents[4], SEED = REPO_ROOT / "packages/web/workers/scripts/d1-seed.sql", MANIFEST = SEED.with_name("d1-seed.manifest.json"). Implement the three pure helpers. main(): require env creds; sha = sha256_of(SEED); key = object_key_for(sha); import boto3; build client (endpoint/creds as in verify script); head_object → if present, print "already uploaded, skipping"; else s3.upload_file(str(SEED), BUCKET, key). Compute git_sha via subprocess.run(["git","rev-parse","HEAD"]) (strip). row_counts: leave None for now (optional per spec; a --note argv is enough). Write MANIFEST with json.dumps(manifest, indent=2) + "\n". Print the object key + bytes.

  • [ ] Step 4: Run — expect PASS.

  • [ ] Step 5: Commit — feat(seed): upload-seed-to-r2 script + manifest writer (PHON-198)

Task 2: fetch-seed-from-r2.py (deploy download + sha256 verify)

Files: - Create: packages/web/workers/scripts/fetch-seed-from-r2.py - Test: extend packages/web/workers/scripts/test_seed_r2.py

Interfaces: - Consumes: the same sha256_of / constants pattern. - Produces (pure): verify_download(path: Path, expected_sha: str) -> None — raises SystemExit(1) with a clear message if sha256_of(path) != expected_sha. - main(): read d1-seed.manifest.json, import boto3, s3.download_file(BUCKET, manifest["object_key"], SEED), then verify_download(SEED, manifest["sha256"]). A missing object (boto3 error) or sha mismatch exits non-zero before any apply.

  • [ ] Step 1: Write failing tests (append)
_spec2 = importlib.util.spec_from_file_location(
    "fetch_seed_from_r2", Path(__file__).with_name("fetch-seed-from-r2.py"))
fetch = importlib.util.module_from_spec(_spec2); _spec2.loader.exec_module(fetch)

def test_verify_download_passes_on_match(tmp_path):
    p = tmp_path / "d.sql"; p.write_bytes(b"data")
    fetch.verify_download(p, hashlib.sha256(b"data").hexdigest())  # no raise

def test_verify_download_fails_on_mismatch(tmp_path):
    p = tmp_path / "d.sql"; p.write_bytes(b"data")
    import pytest
    with pytest.raises(SystemExit) as ei:
        fetch.verify_download(p, "0"*64)
    assert ei.value.code != 0
  • [ ] Step 2: Run — expect FAIL.
  • [ ] Step 3: Implement fetch-seed-from-r2.py per interfaces (reuse the sha256 chunked read; verify_download prints expected vs actual on mismatch then sys.exit(1)).
  • [ ] Step 4: Run — expect PASS (all 5 tests).
  • [ ] Step 5: Commit — feat(seed): fetch-seed-from-r2 with sha256 verify (PHON-198)

Task 3: Deploy workflow edits (deploy.yml + deploy-staging.yml)

Files: - Modify: .github/workflows/deploy.yml, .github/workflows/deploy-staging.yml

Apply the SAME four edits to both (prod seeds phonolex, staging seeds phonolex-staging — otherwise identical):

  • [ ] Step 1: In detect-changes → filters.data, replace the line - 'packages/web/workers/scripts/d1-seed.sql' with - 'packages/web/workers/scripts/d1-seed.manifest.json'. Keep the generated_word_images.tsv line.
  • [ ] Step 2: In the deploy job's actions/checkout@v4, remove with: lfs: true (delete the with:/lfs: lines; the checkout takes no other args there).
  • [ ] Step 3: Add a new step gated on data_changed, placed immediately before "Bust D1 import cache" (and after "Verify generated images present in R2", which already set up uv):

      - name: Fetch + verify seed from R2
        if: needs.detect-changes.outputs.data_changed == 'true'
        run: uv run --no-project --with boto3 python packages/web/workers/scripts/fetch-seed-from-r2.py
        env:
          R2_ACCESS_KEY_ID: ${{ secrets.R2_ACCESS_KEY_ID }}
          R2_SECRET_ACCESS_KEY: ${{ secrets.R2_SECRET_ACCESS_KEY }}
- [ ] Step 4: Update the "Data Pipeline" comment block (lines ~48-53) to say the seed bytes come from R2 (manifest pointer + sha256 verify), not an LFS checkout. - [ ] Step 5: Verify both files parse and the seed steps still gate on data_changed: python -c "import yaml; [yaml.safe_load(open(f)) for f in ['.github/workflows/deploy.yml','.github/workflows/deploy-staging.yml']]; print('ok')" and grep that lfs: true is gone and the manifest path is present in both. - [ ] Step 6: Commit — ci(seed): deploy fetches seed from R2 via manifest, drops LFS pull (PHON-198)

Task 4: Remove Git LFS wiring + docs

Files: - Modify: .gitattributes, .gitignore, CLAUDE.md - Modify: /Users/jneumann/.claude/projects/-Users-jneumann-Repos-PhonoLex/memory/project_ci_deploy_paradigm.md (if present) + MEMORY.md pointer

NOTE: the git rm --cached of the tracked LFS d1-seed.sql is intentionally deferred to Task 5 (cutover) so untracking the big file and committing the manifest land together. This task only removes the LFS rule and updates docs.

  • [ ] Step 1: Delete the line packages/web/workers/scripts/d1-seed.sql filter=lfs diff=lfs merge=lfs -text from .gitattributes (leave the file empty or with any other entries).
  • [ ] Step 2: In .gitignore, add packages/web/workers/scripts/d1-seed.sql (local build product) and update the three comments (lines ~66, ~80, ~119) that say the seed ships via LFS → now "uploaded to R2; the committed d1-seed.manifest.json is the pointer". Also add packages/web/workers/scripts/d1-chunks/ if not already ignored (it is — verify).
  • [ ] Step 3: CLAUDE.md — update every "only LFS artifact" / "d1-seed.sql to LFS" statement (Architecture diagram, Dev Setup, CI/Deploy Paradigm, Gotchas) to the R2-manifest model; add the upload-seed-to-r2.py step to Dev Setup after chunk-seed-sql.py.
  • [ ] Step 4: Update the project_ci_deploy_paradigm memory: seed transport is R2 + manifest, not LFS.
  • [ ] Step 5: Sanity: git check-attr filter -- packages/web/workers/scripts/d1-seed.sql now reports unspecified (no lfs). grep -rn "lfs\|LFS" CLAUDE.md returns only historical/archival mentions, none claiming the seed is LFS-tracked.
  • [ ] Step 6: Commit — chore(seed): drop Git LFS rule + gitignore seed; docs to R2 manifest (PHON-198)

Task 5: Cutover (developer-run — needs local seed + R2 creds)

Not a subagent task. Requires the real ~474 MB d1-seed.sql (LFS-materialized locally) and R2_ACCESS_KEY_ID/R2_SECRET_ACCESS_KEY in the environment.

  • [ ] Step 1: Confirm the local seed is materialized (not an LFS pointer): git lfs pull if needed; ls -la packages/web/workers/scripts/d1-seed.sql shows ~474 MB.
  • [ ] Step 2: Upload + write manifest: export R2_ACCESS_KEY_ID=… R2_SECRET_ACCESS_KEY=… uv run --with boto3 python packages/web/workers/scripts/upload-seed-to-r2.py --note "LFS→R2 cutover (content unchanged)"
  • [ ] Step 3: Round-trip verify: uv run --with boto3 python packages/web/workers/scripts/fetch-seed-from-r2.py re-downloads + sha256-passes against the just-written manifest.
  • [ ] Step 4: Untrack the big file (atomic with the manifest): git rm --cached packages/web/workers/scripts/d1-seed.sql. Confirm git status shows the .sql now ignored/untracked and d1-seed.manifest.json staged as new.
  • [ ] Step 5: Commit — feat(seed): cut over d1-seed transport to R2 (manifest pointer) (PHON-198) — includes the manifest + the git rm --cached.
  • [ ] Step 6: Push branch, open PR to develop. On merge, paths-filter sees the new manifest → first R2-based reseed runs on staging → verify phonolex-staging intact → then prod via main. (This is the one and only reseed this ticket triggers, and it applies the identical seed content.)

Self-review notes

  • The reseed on merge is expected and intended (manifest is new). Seed content is byte-identical to today's LFS seed, so staging/prod data does not change — only where the bytes came from.
  • Fail-safe preserved: fetch-seed-from-r2.py exits non-zero on missing object / sha mismatch, before the DROP-first apply, exactly like the image-coverage gate.
  • Tasks 1-4 are fully buildable/testable/reviewable without R2 access; only Task 5 touches the bucket.