supabase-js .range() Without .order() Drops Rows

The short version

Page with OFFSET/LIMIT using .range() alone, without .order() pinning the ordering, and rows can be duplicated or dropped at a page boundary. The code that fetched a reference set paged exactly like that, and because the target column has no unique constraint to order by, a single missing reference could make an object read as “not referenced”. The fix is to .order() explicitly on the unique primary key id before calling .range().

The state it could become

The subject is the orphan-object sweep for Supabase Storage. It works like this. Ad creatives are uploaded in two stages — “browser uploads straight to storage with a signed URL, then finalizeCreative registers it in the DB” — and when the upload succeeds but registration never happens (the browser leaves, the connection fails), an object nobody references from creatives.file_path is left in the bucket. The sweep collects those periodically, deleting only unreferenced objects older than DEFAULT_ORPHAN_HOURS = 24 (the default age threshold). The threshold’s floor is MIN_ORPHAN_HOURS = 6, and one run deletes at most MAX_DELETE_PER_RUN = 500.

partitionOrphans, which decides what gets deleted, is a pure function that walks the storage objects one by one, excludes anything where referenced.has(o.path) is true as a legitimate creative, and otherwise compares created_at against the threshold to sort it into eligible (to delete) or tooRecent (keep). That referenced is a Set built by fetchReferencedPaths, which pages through every file_path in the creatives table, 1000 at a time (FILE_PATH_PAGE) with .range().

The pre-fix code had no .order() in that fetch.

export async function fetchReferencedPaths(
  admin: SupabaseClient,
  errors: string[]
): Promise<{ paths: Set<string>; ok: boolean }> {
  const set = new Set<string>();
  let from = 0;
  for (;;) {
    const { data, error } = await admin
      .from("creatives")
      .select("file_path")
      .not("file_path", "is", null)
      .range(from, from + FILE_PATH_PAGE - 1);
    if (error) {
      errors.push(`creatives.file_path: ${error.message}`);
      return { paths: set, ok: false };
    }
    // ...
  }
}

file_path has no unique constraint. Splitting pages with .range() without specifying an order means a boundary row can appear on both pages (duplicated) or on neither (dropped). One gap in referenced and the corresponding storage object is treated as “unreferenced” by partitionOrphans, landing in eligible for deletion if its created_at is older than DEFAULT_ORPHAN_HOURS (24 hours).

Why

.range() corresponds, through PostgREST, to PostgreSQL’s OFFSET / LIMIT. The row order of a SELECT with no ORDER BY is the database’s to choose, and there is no guarantee that the same query returns the same order when issued twice with different OFFSETs. Paging — asking for the same set across several queries — implicitly requires “the same order every time”, and nothing satisfies that unless .order() says so.

This code was fail-closed: one page failing with a DB error returns ok=false, and the sweep itself (sweepCreativeOrphans) skips deleting anything. But what that mechanism can detect is an explicit error. Rows quietly duplicating or dropping because the order is undefined returns no error, and so falls outside that fail-closed guard.

Fixing it

We pin the unique primary key with .order("id", { ascending: true }) before calling .range().

const { data, error } = await admin
  .from("creatives")
  .select("file_path")
  .not("file_path", "is", null)
  // Pin a total order on the unique primary key id before paging. OFFSET/LIMIT with no
  // ORDER BY can duplicate or drop rows at a page boundary (file_path is not unique, so it
  // cannot be the ordering key). One missing reference path means a live creative reads as
  // unreferenced and gets deleted — this is required.
  .order("id", { ascending: true })
  .range(from, from + FILE_PATH_PAGE - 1);

id is the primary key and unique, so as long as the same .order("id") is specified, every page query returns a subset of the same total order. That removes any room for boundary rows to duplicate or drop, and referenced is always a complete set.

Preventing a repeat

The same commit added a no-op, order: () => chain, to the test’s mock chain.

const chain = {
  select: () => chain,
  not: () => chain,
  // Production pins a total order with .order("id") before paging (prevents boundary drops).
  // In the mock it does not affect the slice order, so it is a no-op.
  order: () => chain,
  range: async (a: number, b: number) => {
    const slice = (opts.filePaths ?? []).slice(a, b + 1).map((fp) => ({ file_path: fp }));
    return { data: slice, error: null };
  },
};

This mock does nothing when .order() is called, and range() simply slices the array it was given. So what the test confirms is only that “calling a method named .order() does not break the chain” — it does not reproduce the duplication or the drop that an undefined order causes against a real database. If a later change to the same function removed .order() again, this test would not catch it.

The fix was found in implementation review and landed the same day. The commit message records no deletion actually happening in production, so it was closed before it could. The same function’s docstring already stated that deletion must never run on an incomplete reference set, so the fail-closed intent was right from the start. What was missing was the premise that protects it: .range() alone does not guarantee an order.