Rank

rank orders rows with fractional keys: short base-62 strings that sort in byte order. Moving a card between two others writes one new key to one row, so a drag on a board never renumbers the rest of the list. The same algorithm runs in SQL (rank_between) and in TypeScript (rankBetween), and both are tested against one set of vectors.

better-supabase sql add rank

import { createRank, rankBetween } from "better-supabase/blocks/rank";

Declare a ranked subjectCopy Anchor Link

Give the subject a rank entry in config.subjects. group is optional: it names the column a board groups by, such as a pipeline stage.

subjects: {
  deal: {
    table: "public.deals",
    title: "name",
    permissions: { read: "deals.read", update: "deals.update" },
    rank: { column: "rank", group: "stage" },
  },
},

The rank column is text collate "C", so Postgres compares it byte by byte, the same way rankBetween does:

alter table public.deals add column rank text collate "C" not null default 'V';
create index on public.deals (organization_id, stage, rank);

KeysCopy Anchor Link

rankBetween(null, null) is V, the first key of an empty list. A key is never empty and never ends in 0, so there is always room for another key between two of them.

Move a rowCopy Anchor Link

move runs as the caller. It checks can_subject for the subject's update permission, reads the neighbours' keys and writes the new key. With group, it also sets the group column in the same update, so a state-machine trigger on that column checks the stage change and rejects one the machine does not allow.

const rank = createRank({ transport, service });

const key = await rank
  .move("deal", dealId, { previous: aboveId, next: belowId, group: "won" })
  .orThrow();

RebalanceCopy Anchor Link

Keys grow by about one character each time a row lands between the same two neighbours. When a new key is longer than 24 characters and the jobs module is installed, move enqueues rank.rebalance with the subject type, tenant and group. The handler calls rebalance_ranks, which rewrites the group's keys to short, evenly spaced ones in the same order. It is service only.

export const GET = jobs.drainRoute({
  secret: process.env.CRON_SECRET,
  handlers: { "rank.rebalance": rank.job },
});

rank.rebalance("deal", organizationId, "lead") runs the same rewrite directly and resolves to the number of rows it changed.

Tags and custom fieldsCopy Anchor Link

Tag definitions and custom field definitions keep their order in a rank column of their own and don't need this module. A new definition goes after the last one. To reorder, pass the keys of the definitions it now sits between:

await tags.reorder(tag.id, { previous: above?.rank, next: below?.rank });

reorder and reorderDefinition take the neighbours' keys, compute the key with rankBetween and save it through rank_tag or rank_field_definition.