Lesson 04 — Workspace.rm range scan

1. What breaks

Workspace.rm(path, { recursive: true }) in @cloudflare/shell walks a directory subtree and removes every row in one shot. The implementation issues DELETE FROM <table> WHERE path LIKE ? ESCAPE ? against the configured SqlBackend.

When the backend is D1, that query can come back with:

D1_ERROR: LIKE or GLOB pattern too complex: SQLITE_ERROR

The reproduction in #1539 is a directory shaped like an Open Agent Skill: a top-level SKILL.md, plus scripts/, references/, and assets/ subdirectories with a few files each. Nothing pathological. The LIKE pattern is short. But D1's complexity check rejects it anyway, leaving the workspace in an inconsistent state: some R2 blobs may already be gone but the SQL rows still exist, or vice versa.

2. The Workspace storage shape

Each entry in a workspace is one SQL row keyed by path (always leading-slash-normalised). Content either lives inline in a column or is offloaded to R2 with a r2_key stored in the row. Directories are zero-byte entries with type = "directory".

flowchart LR
  caller["Workspace.rm('/skill', { recursive: true })"]
  driver["rm()"]
  desc["deleteDescendants('/skill')"]
  sql[(SQL table)]
  r2[(R2 bucket)]
  caller --> driver
  driver -->|"dir? yes"| desc
  desc -->|"SELECT r2_key WHERE path LIKE '/skill/%'"| sql
  desc -->|"r2.delete(keys)"| r2
  desc -->|"DELETE WHERE path LIKE '/skill/%'"| sql
  driver -->|"DELETE WHERE path = '/skill'"| sql
            

Two LIKE queries fire per recursive rm: one to collect R2 keys before deletion, and one to actually remove the rows. The directory row itself is deleted afterwards via an equality predicate (no LIKE involved).

3. The current query

From packages/shell/src/filesystem.ts:1514:

private async deleteDescendants(dirPath: string): Promise<void> {
  const pattern = escapeLike(dirPath) + "/%";
  const T = this.tableName;

  const r2Rows = await this.sql.query<{ r2_key: string }>(
    `SELECT r2_key FROM ${T}
    WHERE path LIKE ? ESCAPE ?
      AND storage_backend = 'r2'
      AND r2_key IS NOT NULL`,
    pattern,
    LIKE_ESCAPE
  );

  if (r2Rows.length > 0) {
    const r2 = this.getR2();
    if (r2) {
      const keys = r2Rows.map((r) => r.r2_key);
      await r2.delete(keys);
    }
  }

  await this.sql.run(
    `DELETE FROM ${T} WHERE path LIKE ? ESCAPE ?`,
    pattern,
    LIKE_ESCAPE
  );
}

escapeLike backslash-escapes %, _, and \ so a literal path containing those characters does not become a wildcard. The escape character is passed as the second parameter to the LIKE ESCAPE clause.

4. Why LIKE trips D1

SQLite (and therefore D1) applies a complexity budget to LIKE patterns at compile time. The default SQLITE_MAX_LIKE_PATTERN_LENGTH is 50. D1 may apply additional limits on top.

The shape of the workaround is well-known in SQLite circles: replace prefix LIKE with a range predicate that uses the path index directly, without invoking the LIKE code path at all.

5. The range-scan rewrite

The canonical SQLite idiom for "every row whose key starts with prefix/" is a half-open range:

path >= '/skill/' AND path < '/skill/' || x'FFFF'

\uFFFF is the largest valid BMP code point; every descendant of /skill/ sorts before it. The range scan walks the existing path index from the lower bound to the upper bound and stops, no per-row LIKE evaluation needed.

The full rewrite of deleteDescendants reads:

private async deleteDescendants(dirPath: string): Promise<void> {
  const lowerBound = `${dirPath}/`;
  const upperBound = `${lowerBound}\uFFFF`;
  const T = this.tableName;

  const r2Rows = await this.sql.query<{ r2_key: string }>(
    `SELECT r2_key FROM ${T}
    WHERE path >= ? AND path < ?
      AND storage_backend = 'r2'
      AND r2_key IS NOT NULL`,
    lowerBound,
    upperBound
  );

  if (r2Rows.length > 0) {
    const r2 = this.getR2();
    if (r2) {
      const keys = r2Rows.map((r) => r.r2_key);
      await r2.delete(keys);
    }
  }

  await this.sql.run(
    `DELETE FROM ${T} WHERE path >= ? AND path < ?`,
    lowerBound,
    upperBound
  );
}

Notice what disappears: escapeLike, LIKE_ESCAPE, and the entire LIKE complexity surface. What stays: the R2-then-SQL ordering, the storage_backend filter, the SqlBackend abstraction.

6. The sibling-prefix nuance

This is the single most common way to get range-scan prefix matching wrong, and worth pinning down.

Concretely, with dirPath = "/skill":

- (0x2D) and . (0x2E) both sort before / (0x2F) in lexicographic order. Using dirPath alone would delete /skill-sibling.txt and /skill.bak along with the actual contents of /skill. Disaster.

With the trailing slash, the lower bound is /skill/ (0x2F at the boundary), which excludes both siblings AND the directory row itself. The dir row gets handled separately by the DELETE WHERE path = ? at the end of rm().

7. Keeping R2 in sync

The two range queries must use the same bounds. Otherwise R2 keys collected from one set of rows could be deleted while a different set of rows is removed from SQL, leaving orphans on either side.

The rewrite preserves this guarantee by computing lowerBound and upperBound once and passing them to both statements. R2 deletion happens before SQL deletion, the same ordering as before, so a partial failure after R2 succeeds but before SQL commits leaves orphan SQL rows pointing at missing R2 keys. (This was already the case with the LIKE version. Not introduced by this change. Worth noting that the failure mode exists, not worth fixing in this ticket.)

8. Pinning behaviour with a test

Two layers of assertion are worth considering:

1. Behavioural: deleting /skill removes /skill/SKILL.md and friends, leaves /skill-sibling.txt alone.
2. Mechanical: no LIKE query reaches the SqlBackend during recursive rm.

The community patch in #1539 includes both, by intercepting at the SqlBackend boundary:

const rejectLike = (sql: string) => {
  if (/\bLIKE\b/i.test(sql)) {
    throw new Error(`unexpected LIKE query: ${sql}`);
  }
};
const sqlBackend: SqlBackend = {
  query(sql, ...params) { rejectLike(sql); /* ... */ },
  run(sql, ...params)   { rejectLike(sql); /* ... */ }
};
const ws = new Workspace({ sql: sqlBackend, namespace: "rmRangeScan" });

await ws.writeFile("/skill/SKILL.md", "skill");
await ws.writeFile("/skill/scripts/setup.mjs", "setup");
await ws.writeFile("/skill/references/readme.md", "refs");
await ws.writeFile("/skill/assets/sample.txt", "asset");
await ws.writeFile("/skill-sibling.txt", "keep");

await ws.rm("/skill", { recursive: true });

// /skill/SKILL.md is gone, /skill-sibling.txt is intact

The mechanical assertion is a blunt instrument: it forbids LIKE anywhere in the recursive rm path. That is more than #1539 strictly requires; the behavioural assertion alone would catch a regression that re-introduces LIKE silently. Both together pin the implementation more tightly than the contract, which is fine for a fix-and-forget patch but worth noting for future test design.