Data model

Long Tail keeps all of its state in Postgres: workflows, escalations, identity, configuration, MCP registrations, credentials. The escalation row is the one to know. It is the queue, the claim lock, the audit record and the time series in one table. This page covers that row in detail, then the rest of the schema and how migrations run.

The escalation row

Escalations live in the shared HotMesh table public.hmsh_escalations, written by both Long Tail and the HotMesh SDK. lt_escalations is a view over it that adds two computed columns, so reads and the public API keep one stable shape.

CREATE VIEW public.lt_escalations AS
  SELECT *,
    (assigned_to IS NULL OR assigned_until IS NULL OR assigned_until <= NOW()) AS available,
    (CASE WHEN status <> 'pending' THEN COALESCE(resolved_at, updated_at) END) AS ended_at
  FROM public.hmsh_escalations;

Columns

Group	Columns	Notes
Identity	id, namespace, app_id	id is a UUID
Resume	signal_key, topic, workflow_id, workflow_type, task_queue	signal_key is set by conditional(signalId, config). Resolving the row signals the waiting workflow in place.
Routing	role, type, subtype, entity, priority, description	role matches lt_roles.role by name; it is not a foreign key
State	status, assigned_to, assigned_until, claimed_at, claim_expires_at, resolved_at, expires_at	status is pending, resolved, cancelled or expired
Facets	metadata	JSONB, GIN-indexed. Routing facets on create, outcome facets merged on resolve.
Payloads	envelope, escalation_payload, resolver_payload	JSONB. The API returns them as JSON strings.
Lineage	task_id, origin_id, parent_id, initiated_by, created_by	origin_id groups a multi-row job
Audit	milestones, trace_id, span_id, created_at, updated_at
View only	available, ended_at	Computed, see below

The API record maps type, subtype and role to '' when absent, so the contract stays non-null.

Scope adds no columns. A row carries a role and an optional assigned_to. Each member’s read and write scope lives on lt_user_roles and is applied in the query.

Claiming is implicit

There is no “claimed” status. A row is held when assigned_to is set and assigned_until is in the future. When the claim expires, the row is available again with no write at all:

WHERE status = 'pending'
  AND (assigned_to IS NULL OR assigned_until <= NOW())

Every state transition guards on status = 'pending', so a resolve, cancel or expiry on a row that already left the live set changes nothing.

Every row is an interval

ended_at is NULL while the row is pending. Once terminal it is resolved_at for a resolve, or updated_at for a cancel or an expiry, each stamped inside its guarded transition. Every escalation is therefore one interval [created_at, ended_at), which is the time series Analytics reads. Wait time is claimed_at - created_at, and work time is resolved_at - claimed_at.

Indexes

The SDK manages the general indexes on hmsh_escalations:

Index	Serves
idx_hmsh_esc_metadata	GIN (metadata jsonb_path_ops): every metadata @> facet query, claim and resolve by metadata
idx_hmsh_esc_signal_key	UNIQUE (namespace, app_id, signal_key) WHERE signal_key IS NOT NULL: resolve by resume key, one row per wait
idx_hmsh_esc_available, idx_hmsh_esc_available_expiry	Available-by-role, priority-ordered
idx_hmsh_esc_assigned	Rows held by a given user
idx_hmsh_esc_stats_pending, _created, _resolved	Backlog and windowed aggregates
idx_hmsh_esc_origin, _task, _workflow, _entity	Lineage lookups

Long Tail adds two at startup with CREATE INDEX CONCURRENTLY:

Index	Serves
idx_hmsh_esc_resolved_cover	(role, resolved_at DESC, claimed_at, created_at) WHERE status = 'resolved': the Pace Board’s wait and work percentiles, read from the index
idx_hmsh_esc_ended_at	((COALESCE(resolved_at, updated_at))) WHERE status <> 'pending': analytics interval scans. Pending inserts are excluded, so each row costs one index write, at its terminal transition.

The other tables

Area	Tables	Holds
Workflows	lt_tasks	One row per workflow run: status, envelope, milestones, returned data, initiated_by, principal_type
Roles	lt_roles, lt_role_schemas, lt_role_list_schemas, lt_role_upstreams	The role registry and its dials, immutable form and list schema versions, cross-sequence inputs
Identity	lt_users, lt_user_roles, lt_bot_api_keys, lt_personas, lt_persona_roles, lt_user_personas	People and bots, memberships with type, read_scope and write_scope, hashed API keys, persona bundles
Workflow config	lt_config_workflows, lt_config_roles, lt_config_invocation_roles, lt_config_role_escalations	Registration profiles, who may invoke, escalation routing edges
Scan codes	lt_config_scan_schemes, lt_config_scan_actions	Schemes and their rules
MCP	lt_mcp_servers	Registered servers: transport_type is stdio, sse or streamable-http; status is registered, connected, error or disconnected; cached tool_manifest, GIN-indexed tags
Graph workflows	lt_yaml_workflows, lt_yaml_workflow_versions, lt_workflow_sets	Compiled YAML workflows (draft, deployed, active, archived) and their versions
Events and agents	lt_topic_catalog, lt_agents, lt_agent_subscriptions	The topic catalog, agents and their subscriptions
Knowledge	lt_knowledge, lt_knowledge_versions, lt_domain	Versioned reference data, the domain dictionary
Credentials	lt_oauth_tokens, lt_service_tokens, lt_ephemeral_credentials	Encrypted provider tokens, hashed service tokens, short-lived encrypted secrets
OAuth server	lt_oauth_clients, lt_oauth_codes, lt_oauth_grants, lt_oauth_refresh_tokens	MCP clients that sign in to /mcp
Platform	lt_namespaces, lt_announcements, lt_nats_ws_tickets, lt_migrations	Namespace registry, dashboard announcements, NATS WebSocket tickets, applied migrations

Memberships

lt_user_roles has primary key (user_id, role). CHECK constraints enforce type IN ('superadmin', 'admin', 'member'), read_scope IN ('self', 'all'), write_scope IN ('none', 'self', 'all'), and write within read: write_scope = 'all' requires read_scope = 'all'. Both scopes default to all. See Identity.

Role schema versions

Every change to a role’s form_schema or metadata_schema appends the next (role, version) row to lt_role_schemas in the same statement that updates lt_roles. An escalation pins its version in metadata.schema_version (the schemaVersion option on conditional), so its form renders exactly as authored. lt_role_list_schemas versions list views on their own timeline.

Migrations

Long Tail migrates itself at startup. The runner reads the numbered .sql files that ship with the package, in name order, and records each in lt_migrations:

CREATE TABLE IF NOT EXISTS lt_migrations (
  id         SERIAL PRIMARY KEY,
  name       TEXT NOT NULL UNIQUE,
  applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

1. The runner pins search_path to public and takes a Postgres advisory lock on a dedicated connection.

2. It applies each file not yet recorded, then records it. Each file runs once.

3. It releases the lock. Containers that were waiting find nothing left to apply.

Every container can call migrate() at boot: API and worker containers alike. The lt_escalations view install and the code-owned configuration pass each take their own advisory lock, so concurrent boots serialize on each step.

Caution

The runner refuses to boot when it finds an lt_migrations table in a schema other than public. That means an earlier boot ran with a search path that shadowed public, and the tables have forked. Point the database role’s search path at public and reconcile before restarting.

All tables with updated_at (lt_tasks, lt_users, lt_config_workflows, lt_mcp_servers, lt_yaml_workflows, lt_namespaces) share one BEFORE UPDATE trigger. On hmsh_escalations, the SDK maintains updated_at.

Related

PostgresSizing, pooling and tuning.

Faceted routingQuerying and claiming by metadata.

Availability is a query

Park every state