Reordering a tree without breaking unique constraints: termnum, parentTermnum and DEFERRABLE

The problem

A configurator is a tree of items: groups contain fields, fields contain options, and so on. Users build and reorganize that tree constantly: dragging an item above another, swapping two options, moving a block under a new parent.

Each item stores two numbers:

• termnum: the item's position number inside the configurator,
• parentTermnum: the position number of its parent (null for top-level items).

And there is a rule enforced by the database:

UNIQUE (termnum, configurator_id)

Two items in the same configurator can never share a position. This rule is important: the tree is rebuilt from these numbers, and a duplicate termnum would make a parent reference ambiguous. Which of the two items is the parent?

Then users tried to swap two items:

Item A: termnum 1 → 5
Item B: termnum 5 → 1

and the database refused:

duplicate key value violates unique constraint "items_termnum_configurator_id_unique"

Nothing was wrong with the result: after the swap, positions are still unique. The problem is the path to get there.

The details

Why a swap breaks a unique constraint

Updates happen one row at a time. To swap A and B:

UPDATE items SET termnum = 5 WHERE id = A;  -- ❌ B already has 5
UPDATE items SET termnum = 1 WHERE id = B;

The first statement fails immediately, because for a moment A and B both have 5. Changing the order of the statements doesn't help: whichever runs first creates a temporary duplicate.

By default, PostgreSQL checks unique constraints immediately, after each statement. The constraint doesn't know you're about to fix the duplicate one line later.

It's not just swaps

Real reorders are bigger than swaps. Dragging one item from position 2 to position 6 shifts everything in between:

2 → 6,  3 → 2,  4 → 3,  5 → 4,  6 → 5

That's a rotation: every intermediate state contains a duplicate, whatever the order. And users can send a batch mixing position changes with other edits (renames, comments, new parents) in one save.

Why parent references make it riskier

Because parentTermnum points to a position, not to an id, a half-applied reorder doesn't just look wrong: children can end up pointing at the wrong parent. A reorder must be all or nothing.

The common workarounds and their costs

• Temporary values: move items to unused positions first (say -1, -2), then to their final positions. It works, but needs careful bookkeeping and two passes.
• Drop the constraint and validate in application code. Fast to write, but now nothing stops a bug from corrupting the tree.
• Delete and re-insert the items. Destroys ids, relations and timestamps.

I wanted to keep the constraint, keep the ids, and still allow any reorder in a single save.

The challenges

Prisma can't declare a deferrable constraint

The schema is managed with Prisma, which creates unique constraints as standard, non-deferrable constraints. There's no schema attribute to change that.

The setup must be repeatable everywhere

Developers reset their databases, CI creates fresh ones, and production gets migrations. Whatever made the constraint deferrable had to apply automatically in all of these, idempotently, and survive Prisma recreating things in different forms (sometimes as a constraint, sometimes as a plain unique index).

Mixed updates in one save

A single request can contain position changes and normal field updates. All of it must succeed or fail together.

Bad input must fail early and clearly

Negative or zero positions, duplicates inside the request itself, or a final state that still collides must be rejected with a clear error, not a cryptic SQL message.

The solution

1. DEFERRABLE INITIALLY IMMEDIATE

PostgreSQL supports deferrable constraints: a constraint can be checked at the end of the transaction instead of after each statement.

ALTER TABLE items
  ADD CONSTRAINT items_termnum_configurator_id_unique
  UNIQUE (termnum, configurator_id)
  DEFERRABLE INITIALLY IMMEDIATE;

The two keywords matter:

• DEFERRABLE: this constraint may be deferred inside a transaction.
• INITIALLY IMMEDIATE: by default it is still checked immediately. Normal inserts and updates behave exactly as before.

Only code that explicitly asks for it gets the deferred behavior:

BEGIN;
SET CONSTRAINTS items_termnum_configurator_id_unique DEFERRED;
UPDATE items SET termnum = 5 WHERE id = A;  -- temporary duplicate: allowed
UPDATE items SET termnum = 1 WHERE id = B;  -- duplicate resolved
COMMIT;                                     -- uniqueness checked here ✅

If the final state still has a duplicate, the commit fails and everything rolls back. The guarantee is intact; only when it's checked changed.

The same treatment was applied to other "unique within a parent" constraints that suffer from the same swap problem: item names per configurator (so two items can swap names in one save) and filter ordering numbers per item.

2. An idempotent setup script that runs after every migration

Since Prisma can't express this, a SQL script converts the constraint, and it's written to be safe to run any number of times:

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_constraint
             WHERE conname = 'items_termnum_configurator_id_unique') THEN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint
                   WHERE conname = 'items_termnum_configurator_id_unique'
                     AND condeferrable) THEN
      ALTER TABLE items DROP CONSTRAINT items_termnum_configurator_id_unique;
      ALTER TABLE items ADD CONSTRAINT items_termnum_configurator_id_unique
        UNIQUE (termnum, configurator_id) DEFERRABLE INITIALLY IMMEDIATE;
    END IF;                                    -- already deferrable: nothing to do
  ELSIF EXISTS (SELECT 1 FROM pg_indexes
                WHERE indexname = 'items_termnum_configurator_id_unique') THEN
    DROP INDEX items_termnum_configurator_id_unique;  -- created as an index
    ALTER TABLE items ADD CONSTRAINT items_termnum_configurator_id_unique
      UNIQUE (termnum, configurator_id) DEFERRABLE INITIALLY IMMEDIATE;
  ELSE
    ALTER TABLE items ADD CONSTRAINT items_termnum_configurator_id_unique
      UNIQUE (termnum, configurator_id) DEFERRABLE INITIALLY IMMEDIATE;
  END IF;
END $$;

It handles all three states it can find: an existing non-deferrable constraint, a unique index with the same name, or nothing at all.

These scripts live in a functions/ folder with numeric prefixes (00_…, 01_…) so they run in a predictable order. A small post-migration runner executes them after every migrate command, in development, CI and production. The runner splits SQL files into statements carefully, respecting PostgreSQL's $$ … $$ dollar-quoted function bodies, so a semicolon inside a function doesn't cut it in half.

3. A batch function for position updates

Position changes go through one PL/pgSQL function that takes all updates as JSON:

CREATE OR REPLACE FUNCTION update_items_positions_batch(updates JSONB)
RETURNS INTEGER LANGUAGE plpgsql AS $$
DECLARE
  u JSONB;
  updated INTEGER := 0;
BEGIN
  SET CONSTRAINTS items_termnum_configurator_id_unique DEFERRED;

  FOR u IN SELECT * FROM jsonb_array_elements(updates) LOOP
    UPDATE items
       SET termnum = (u->>'termnum')::INTEGER,
           updated_at = NOW()
     WHERE id = (u->>'id')::INTEGER;
    IF FOUND THEN updated := updated + 1; END IF;
  END LOOP;

  RETURN updated;
END $$;

Called from the application like this:

await tx.$executeRawUnsafe(
  'SELECT update_items_positions_batch($1::jsonb)',
  JSON.stringify([{ id: 1, termnum: 5 }, { id: 2, termnum: 1 }]),
);

The JSON is passed as a bound parameter ($1), never concatenated into the SQL string, so there's no injection risk even though the call is "raw".

The function only touches termnum. Everything else (name, comment, parentTermnum, type) goes through the normal, typed Prisma updates. That keeps the raw SQL tiny and the rest of the logic in one place.

4. One transaction for the whole save

The batch update service ties it together:

return this.items.transaction(async (tx) => {
  // 1. Validate: no negative/zero positions, no negative parents
  for (const u of updates) {
    if ((u.data.termnum !== undefined && u.data.termnum <= 0) ||
        (u.data.parentTermnum != null && u.data.parentTermnum < 0)) {
      throw new BadRequestException('termnum and parentTermnum cannot be negative.');
    }
  }

  // 2. Allow temporary duplicate names during this save
  await this.items.deferNameUniqueness(tx);

  // 3. Positions: one call, deferred uniqueness
  const positions = updates
    .filter((u) => u.data.termnum !== undefined)
    .map((u) => ({ id: u.id, termnum: u.data.termnum }));
  if (positions.length) await this.items.updatePositionsBatch(positions, tx);

  // 4. Everything else (name, comment, parentTermnum…) with normal updates
  for (const u of updates) await this.items.updateOtherFields(u, tx);
});

Either the whole reorder lands, or none of it does. Unique violations that survive to the commit are translated into a clear 409 Conflict ("duplicate position after reorder"), and invalid input into a 400, instead of raw database errors.

What a swap looks like now

PATCH /items/batch
[
  { "id": 1, "data": { "termnum": 5 } },
  { "id": 2, "data": { "termnum": 1 } }
]

One request, one transaction, one deferred check, and a tree that is valid before and after, with no temporary hacks in the data.

Lessons learned

• Validate the result, not every step. Some operations are only invalid halfway through. Deferrable constraints let the database check what actually matters: the final state.
• DEFERRABLE INITIALLY IMMEDIATE is the safe default. Normal writes keep immediate checks; only code that asks gets deferral.
• Keep the constraint. Moving integrity into application code is tempting and fragile. The database is the last line of defense against bugs.
• Your ORM is not your database. When the ORM can't express a feature, add an idempotent SQL step to your migration pipeline instead of giving the feature up.
• Make setup scripts idempotent and order them. They will run on fresh, old and half-migrated databases.
• Keep raw SQL small and parameterized. Let the database function do the one thing it's needed for; keep the rest in typed code.
• Reorders are all-or-nothing. When parents are referenced by position, a half-applied reorder corrupts the tree, so use one transaction.

FAQ

What is a deferrable constraint in PostgreSQL?

A constraint declared DEFERRABLE can be checked at the end of a transaction instead of after each statement, when the transaction runs SET CONSTRAINTS … DEFERRED. It's useful for operations like swaps that temporarily violate the rule.

How do I swap two values in a column with a UNIQUE constraint?

Either make the constraint deferrable and swap inside a transaction with the constraint deferred, or move one row to a temporary unused value first, then perform the swap in two more steps.

Can Prisma create deferrable constraints?

Not from the schema file. You can add them with a custom SQL migration or a post-migration SQL script that alters the constraint, ideally written to be idempotent.

What is the difference between INITIALLY IMMEDIATE and INITIALLY DEFERRED?

INITIALLY IMMEDIATE checks the constraint after each statement unless a transaction explicitly defers it. INITIALLY DEFERRED always checks at commit time unless told otherwise. Immediate is safer as a default.