Partial Unique Index Closes a Postgres Phantom Race

Conclusion

A bulk-action button like “duplicate to next year” in a school admin console can’t be made safe against double-clicks or a second tab just by having the app layer check for an existing row before inserting — that “graceful skip” only works when the app layer actually observes the concurrent commit. Under READ COMMITTED, if two transactions run the same existence-check SELECT at nearly the same moment, both see zero rows and both proceed to INSERT. This phantom race is invisible from the app layer, so the final serialization was pushed down to a partial unique index in the database, with a conflicting INSERT detected as 23505 and returned as an ordinary conflict.

Symptom

The target is a classes table, with a bulk action that copies a given grade/class structure forward into the next academic year. The logic is built from two functions.

// hub-core.ts:291-292
export function classDupKey(gradeId: string, name: string): string {
  return `${gradeId} ${name}`;
}

// hub-core.ts:298-308 (nextDuplicationYears)
export function nextDuplicationYears(
  rows: ClassYearRow[],
): { sourceYear: number; targetYear: number } | null {
  if (rows.length === 0) {
    return null;
  }
  const sourceYear = Math.max(...rows.map((r) => r.academicYear));
  return { sourceYear, targetYear: sourceYear + 1 };
}

sourceYear is always “the latest year currently present,” and targetYear is fixed as the year after it. In other words, this action advances one year every time it runs — it is not idempotent — and pressing the same button twice has room to advance it twice. To guard against that, planNextYearDuplication takes existingTargetKeys (the set of classes that already exist in the target year) and excludes anything already there from the duplication plan.

// hub-core.ts:310-331 (planNextYearDuplication, comments kept verbatim)
/**
 * Pure function that computes "which classes should be duplicated into next
 * year" from the current class list. Returns null if there are no classes.
 *
 * @param rows All of this school's classes (every year). Determines
 *   source = latest year / target = source + 1.
 * @param existingTargetKeys The set of classDupKey values already present in
 *   the target year, for idempotency. Excludes duplicate inserts when a
 *   concurrent commit could be observed inside the RLS tx (graceful skip).
 *   The permanent guard against an unobservable phantom race is the
 *   database's partial unique index ux_classes_school_year_grade_name.
 *   Defaults to an empty set (no exclusion — the previous behavior).
 */
export function planNextYearDuplication(
  rows: ClassYearRow[],
  existingTargetKeys: ReadonlySet<string> = new Set(),
): NextYearPlan | null {
  const years = nextDuplicationYears(rows);
  if (!years) {
    return null;
  }
  const { sourceYear, targetYear } = years;
  const toCreate = rows
    .filter((r) => r.academicYear === sourceYear && r.gradeId)
    .filter((r) => !existingTargetKeys.has(classDupKey(r.gradeId as string, r.name)))
    .map((r) => ({
      gradeId: r.gradeId as string,
      name: r.name,
      grade: r.grade,
      academicYear: targetYear,

As the comment itself states, this existingTargetKeys exclusion is a defense for the case “where a concurrent commit could be observed inside the RLS transaction” — it was known at design time that it does not close off concurrent execution that can’t be observed (a phantom race).

Cause

A double-click or a retry from a second tab starts two transactions at nearly the same moment. Under READ COMMITTED, each SELECT only sees rows that were already committed at that instant, so both transactions can observe the same fact: “zero rows exist in the target year yet,” because neither has committed its INSERT yet. As a result, existingTargetKeys comes back empty in both transactions, and planNextYearDuplication returns the identical plan — “duplicate everything” — in both. Neither SELECT lied; both were telling the truth at that moment. The real problem is that the procedure itself — “SELECT to check, then INSERT” — leaves a gap between the check and the INSERT that another transaction can slip into.

The fix

The app-layer pre-check was left in place, and a partial unique index was added to the database as the final line of defense.

-- 20260614024542_chilly_guardsmen.sql:1-27 (comments kept verbatim)
-- Partial UNIQUE index that **closes off, at the DB level**, duplicate
-- next-year classes created by concurrent execution of "duplicate to next
-- year" (duplicateClassesToNextYearAction) or a retry from a second tab
-- (a unique constraint on classes over school x year x grade x class name).
-- Serializes the same school / same year / same grade (grade_id) / same
-- class name down to a single row.
--
-- Why a partial index (WHERE grade_id IS NOT NULL):
--   - Only classes with a grade_id are duplication targets
--     (planNextYearDuplication excludes gradeId=null), and classes with no
--     grade assigned don't appear in the posting hierarchy. There's no
--     point enforcing a uniqueness constraint on rows that are out of
--     scope.
--   - Postgres also treats NULL as distinct, so NULL rows wouldn't collide
--     with each other even if grade_id were included as a key. The partial
--     index excludes NULL rows explicitly to make that intent visible.
--
-- Why school_id is part of the key:
--   classes is multi-tenant (holds every school's rows in one table).
--   RLS limits SELECT/writes to one's own school, but the index itself
--   works physically across the whole table — across every tenant — so
--   school_id is required.
--
-- Relationship to the app layer:
--   duplicateClassesToNextYearAction aims for a graceful skip via
--   planNextYearDuplication plus excluding existing targets, but a
--   READ COMMITTED phantom race (both concurrent tx's SELECT sees zero
--   rows -> both INSERT) cannot be closed off at the app layer. This index
--   is the true backstop for serialization; a conflicting INSERT maps
--   23505 -> a graceful conflict response in finish.
CREATE UNIQUE INDEX "ux_classes_school_year_grade_name" ON "classes" USING btree ("school_id","academic_year","grade_id","name") WHERE "classes"."grade_id" IS NOT NULL;

The key is four columns: (school_id, academic_year, grade_id, name). school_id is included because classes is multi-tenant, holding every school’s rows in one table. RLS policies restrict SELECT and writes to a school’s own rows, but the unique index itself judges uniqueness by physically looking at the whole table, so without school_id explicitly in the key, it would collide against another school’s class with the same name. It’s a partial index with WHERE grade_id IS NOT NULL because duplication targets are themselves limited to classes that have a grade_id (planNextYearDuplication’s .filter((r) => ... && r.gradeId)), and there’s no reason to enforce a uniqueness constraint on classes with no grade assigned. As the comment notes, Postgres treats NULL as distinct so NULL rows wouldn’t collide with each other regardless, but stating it explicitly as the partial index’s condition leaves the intent — “serialize only the duplication targets” — in the schema itself.

With this in place, if two transactions attempt the INSERT at the same time, one of them gets back 23505 (unique_violation). The app layer was redesigned around treating that not as an unexpected error, but as a normal conflict.

Preventing a repeat

This gap existed because, at the point existingTargetKeys was introduced, it wasn’t made explicit that an app-layer “SELECT then write” defense does not close the gap created by the transaction isolation level itself. The comment now draws a clear line between “defense against a concurrent commit that could be observed” and “the permanent guard against a phantom race that can’t be observed.” Whenever “check existence, then insert” logic is added to a bulk-action button, it’s worth separating, at implementation time, exactly how far that check can protect you and which database constraint is responsible for the rest.