Faceted routing

The escalation table is a pool of work any number of consumers can search and claim by its facets. This page covers the FacetQuery language, the read and claim primitives, the dispatcher pattern, link variables, and how role scope folds into the SQL. Long Tail supplies the surface. What a facet means and how the matching loop runs belong to your app.

Role is the wall, facets are capability

Every row carries a role and a metadata JSONB object. Routing is a funnel over those columns:

Concern	Column	Mechanism
Hard capability	role	A consumer bound to one role never sees another role’s work
Soft capability	metadata	metadata @> facets containment, served by a GIN index
Preference and priority	orderBy	Top-level columns and facets
Capacity	limit	How many rows a claim takes

A multi-unit job (an order with several items) is a set of rows sharing an origin_id, with its unit count in a facet so the group can be claimed only when complete.

The query

interface FacetQuery {
  role?: string;                    // hard isolation (exact)
  roles?: string[];                 // role = ANY(...)
  types?: string[];
  subtypes?: string[];
  facets?: Record<string, any>;     // metadata @> facets (AND), GIN-served
  block?: Record<string, any>[];    // NOT (metadata @> ANY(block))
  range?: { facet: string; op: '<' | '<=' | '>' | '>=' | '='; value: number }[];
  exists?: string[];                // metadata ? key
  status?: string;                  // e.g. 'pending'
  available?: boolean;              // true: unclaimed or claim lapsed. false: held now.
  jeopardy?: boolean;               // true: past the role's priority threshold
  orderBy?: { field: string; direction?: 'asc' | 'desc'; numeric?: boolean }[];
  limit?: number;
  offset?: number;
}

orderBy.field is a top-level column (priority, created_at, updated_at, resolved_at, status, role) or metadata.<key>, read as text or as a number when numeric is set. Every value is a bound parameter. Metadata keys must match ^[a-zA-Z0-9_]+$.

// Pending, available cold-zone picks, soonest deadline first.
const query = {
  role: 'pick',
  status: 'pending',
  available: true,
  facets: { zone: 'cold' },
  orderBy: [{ field: 'metadata.dueBy', numeric: true, direction: 'asc' }],
};

jeopardy keeps only rows past their role’s priority threshold. It is the same expression behind the Pace Board’s priority count, so a jeopardy list’s total always equals the badge that linked to it. Over HTTP it is jeopardy=1.

Reads

The service functions run on the server, inside your workers:

import { EscalationService } from '@hotmeshio/long-tail';

// Rows: filter and sort over columns and facets.
const { escalations, total } = await EscalationService.searchByFacets(query);

// Groups: one entry per origin_id with unit count, availability and completeness.
const groups = await EscalationService.searchGroups(query, { sizeFacet: 'orderSize', limit: 10 });

// Count: capacity and in-flight checks.
const inFlight = await EscalationService.countByFacets({ role: 'pick', available: false });

Atomic claims

Both claim primitives take a FacetQuery, a consumer id and options, and lock with FOR UPDATE SKIP LOCKED, so many consumers run without contention and take disjoint rows.

// Whole groups, all or nothing, in rank order. A group is eligible only when
// its member count equals its declared size.
const orders = await EscalationService.claimGroups(query, 'dispatcher-1', {
  limit: 4,
  sizeFacet: 'orderSize',
});

// Individual rows. With allOrNone the claim commits only when the full limit
// is acquired; otherwise it rolls back and returns [].
const stations = await EscalationService.claimByFacets(
  { role: 'pack-station', facets: { state: 'ready' } },
  'dispatcher-1',
  { limit: 4, allOrNone: true },
);

A claim sets assigned_to and assigned_until (default 30 minutes, durationMinutes to change it). Resolve, release, or let the claim lapse to return the row to the pool.

Availability is a query

A row is held when assigned_to is set and assigned_until > NOW(). There is no separate flag to drift out of sync. An expired claim is available again on the next query.

The dispatcher pattern

The primitives compose into a two-sided market: read how much supply is free, claim that much demand, then lock the supply for it.

1. Anticipate supply.

   const free = await EscalationService.searchByFacets({
     role: 'pack-station', available: true, facets: { state: 'ready' },
   });

2. Claim that much demand, by priority.

   const orders = await EscalationService.claimGroups(demandQuery, consumer, {
     limit: free.escalations.length,
   });

3. Lock supply for the claimed demand, all or none.

   const stations = await EscalationService.claimByFacets(supplyQuery, consumer, {
     limit: orders.length, allOrNone: true,
   });

4. Pair, do the work, resolve both.

Group claims re-check completeness inside the lock, so concurrent dispatchers never split a group or double-claim a row. A dispatcher workflow that holds a claim it cannot place yet can carry it across continueAsNew and converge later.

Record the outcome on resolve

A row is created with intent: the facets that route and claim it. Resolving it can record the outcome on the same row. The metadata patch merges into the indexed metadata in the same atomic update, on the winning resolve only:

await EscalationService.resolveEscalation(
  rowId,
  { result: 'success' },               // resolverPayload: returned to the waiting workflow
  { outcome: 'success', unitsPacked: 6 }, // metadata: merged and queryable
);

created_at to resolved_at is the elapsed time. The row answers what was asked, what happened and how long it took:

await EscalationService.searchByFacets({ role: 'pack-station', facets: { outcome: 'success' } });

The operations query

People query across their roles with the same language through the scoped list API. The filter and the count both run in SQL:

SDK

import { createClient } from '@hotmeshio/long-tail';

const lt = createClient({ auth });
const res = await lt.escalations.list({
  status: 'pending',
  facets: { flags: 'too_short' },
  range: [{ facet: 'confidence', op: '<=', value: 0.7 }],
  exists: ['needsReview'],
  block: [{ outcome: 'success' }],
  orderBy: [{ field: 'metadata.confidence', numeric: true, direction: 'asc' }],
  limit: 50,
});
// res.data: { escalations, total }

HTTP

Each faceted element is a JSON-encoded query parameter, so the whole query fits in a URL:

GET /api/escalations?status=pending&facets={"flags":"too_short"}&range=[{"facet":"confidence","op":"<=","value":0.7}]

The dashboard’s copy-URL and copy-curl reproduce the exact request.

MCP

search_by_facets, claim_by_facets and claim_groups take the same query. Reading needs mcp:read; claiming needs mcp:full and write access to the role.

list and listAvailable accept facets, block, range, exists, roles, available and orderBy on top of the plain filters.

Scope is enforced in SQL

The read-scope predicate is ANDed with the facets:

Caller	Sees
Superadmin, global admin	Every role’s queue
read_all on a role	The whole queue for that role
read_self on a role	Only rows where assigned_to is the caller

A caller can narrow within their scope by passing role or roles, and never widen it: a role they do not hold matches nothing. The page total comes from the same WHERE, so pagination stays correct.

Link variables

A role can declare link variables: facet names its members bind per device.

{ "link_variables": [ { "name": "facility", "label": "Facility", "default": "main" } ] }

A pinned URL uses one as the entire value of a facet:

/escalations/available?role=packer&facets={"facility":"{lt:facility}"}&status=available

The dashboard substitutes at render time. Each variable resolves to the device’s bound value, then the declared default, then drops the facet (unbound means no filter). Members bind values from the user menu, and the binding lives in the device’s local storage, so a shared workstation keeps its scope across reloads and account switches. Values are strings. RBAC read scope still governs what any query returns.

{lt:me} is built in: the badged person while a badge grant is live, otherwise the signed-in user. Every completing resolve records metadata.resolved_by, so one pin gives “my recent work”:

/escalations/available?status=resolved&facets={"resolved_by":"{lt:me}"}&orderBy=[{"field":"resolved_at","direction":"desc"}]

A bound link variable also scopes the Pace Board’s counts and timelines.

Related

AnalyticsAggregate and timeline the same facets.

Data modelThe escalation row and its indexes.

Two-sided dispatcher

Availability is a query