SQL functions¶

Status: Current | Last reviewed: 2026-06-20

The PL/pgSQL functions that mediate the agent / agent_version split (plus a parallel *_workflow_version set for workflows). Every read of an agent's runtime config goes through one of these. All are defined in 000_full_schema.sql.

Signatures below are simplified for readability. Real arg names are p_-prefixed (p_versioned_id, p_agent_id, p_published_by) and real OUT/columns are version_number / traffic_weight. Since the 2026-06-20 squash these functions return/key on agent_id (renamed from base_agent_id); the workflow analogs use workflow_id. For exact current signatures: \df+ resolve_agent_version in psql. Workflow set: resolve_workflow_version[_weighted], publish_workflow_version, create_workflow_draft, update_workflow_draft, create_draft_from_workflow_version, update_workflow_traffic_weights.

resolve_agent_version¶

resolve_agent_version(
  agent_id_or_ref text,
  allow_draft boolean default false
) returns table (
  agent_id text,
  version int,
  config jsonb
)

Resolves a string reference to a concrete (agent, version, config) tuple.

Reference forms:

1004 — current published version of agent 1004.
1004:v3 — exact version 3.
1004:draft — version 0 (the draft). Requires allow_draft = true.

If allow_draft = false and the reference points at a draft, the function raises.

This is the function ai-orchestrator and async-agent call at the start of every conversation or workflow run. The orchestrator never reads agent_versions directly; always through this.

resolve_agent_version_weighted¶

resolve_agent_version_weighted(
  agent_id text
) returns table (
  agent_id text,
  version int,
  config jsonb
)

Used for A/B traffic split. Returns one of the published versions probabilistically based on agent_versions.weight. If no row has a weight, falls back to is_current_published.

Set non-zero weights only when you mean it; the function does not validate that weights sum to 100. They just need to be non-zero on multiple versions.

publish_agent_version¶

publish_agent_version(
  agent_id text,
  by_user uuid,
  notes text default null,
  weight int default 100
) returns int

Promotes the current draft (version 0) to a new published version. Steps:

Find the next version number (max + 1).
Copy version 0's config into a new row at that number.
Set is_current_published = false on the previous current.
Set is_current_published = true on the new row.
Write an agent_version_snapshots audit row.

The draft row is preserved — version 0 still exists for the next round of edits.

create_new_agent_draft¶

create_new_agent_draft(
  agent_id text,
  config jsonb,
  agent_type text,
  agent_subtype text,
  by_user uuid,
  agent_name text,
  agent_description text
) returns void

Creates a brand new agent + its version 0 draft, atomically. name and description go on the agents row; everything else (including agent_type/agent_subtype for backward-compat) goes into the version 0 config JSONB.

Use this from ai-manager's "New Agent" flow.

update_agent_draft¶

update_agent_draft(
  agent_id text,
  config jsonb,
  agent_type text,
  agent_subtype text
) returns void

Updates the version 0 draft for an existing agent.

Note: this function explicitly strips name and description from config before writing. They live only on agents. If you put them in the JSONB, they are silently dropped.

Why these exist¶

Two principles:

The orchestrator should not know about drafts. It calls resolve_agent_version with allow_draft = false and gets the right published config. The function hides the version logic.
Publishing is atomic. A bad UPDATE agent_versions SET is_current_published = ... could leave two rows with true or zero with true. The function wraps the swap in a transaction and a unique constraint check.

Where they are called¶

Caller	Function	Why
ai-orchestrator `pipeline_config_provider.py`	`resolve_agent_version`	Build pipeline at conversation start
ai-orchestrator (A/B-enabled agents)	`resolve_agent_version_weighted`	Pick traffic-split version
ai-manager `api/agents/[id]/publish/route.ts`	`publish_agent_version`	Publish button
ai-manager `api/agents/route.ts`	`create_new_agent_draft`	New Agent flow
ai-manager (autosave + manual save)	`update_agent_draft`	Every edit while in draft
async-agent	`resolve_agent_version`	Workflow tool node referencing an agent