Evenfire is an intelligence layer platform for running LLM agents that take real actions, on your infrastructure
TypeScript
import type { DbClient } from '../db.js'
// ─── Plugin Workload SDK — schema (migration 0034_plugin_workload_sdk) ──
// Lives apart from pluginWorkloadSdkDb.ts so db.ts can import it without a
// runtime import cycle (this module only has a type-only dependency on db).
export async function applyPluginWorkloadSdkSchema(db: DbClient): Promise<void> {
await db.query(`
CREATE TABLE IF NOT EXISTS plugin_workload_sdk_grants (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
recipe_namespace TEXT NOT NULL,
recipe_name TEXT NOT NULL,
capability_family TEXT NOT NULL CHECK (capability_family IN ('promptBridge','clientNotifications')),
-- Explicit provider bound to a promptBridge grant (R1: credentials resolve
-- per-provider, not per-model). NULLABLE: rows written before migration
-- 0055_plugin_workload_sdk_grant_provider carry NULL and remain legacy/unreviewed until an operator
-- explicitly resaves an ordered target policy. clientNotifications
-- grants leave it NULL.
provider TEXT,
allowed_models JSONB NOT NULL DEFAULT '[]'::jsonb,
allowed_event_types JSONB NOT NULL DEFAULT '[]'::jsonb,
allowed_target_refs JSONB NOT NULL DEFAULT '[]'::jsonb,
allowed_user_refs JSONB NOT NULL DEFAULT '[]'::jsonb,
allowed_callers JSONB NOT NULL DEFAULT '[]'::jsonb,
quota_limits JSONB NOT NULL DEFAULT '{}'::jsonb,
model_policies JSONB NOT NULL DEFAULT '{}'::jsonb,
-- Ordered operator-authorized promptBridge routing policy. This stays
-- separate from legacy provider/allowed_models so old rows can be
-- inventoried without accidentally becoming a routable policy.
prompt_targets JSONB NOT NULL DEFAULT '[]'::jsonb,
default_target_ref TEXT NULL,
policy_revision INTEGER NOT NULL DEFAULT 1,
policy_state TEXT NOT NULL DEFAULT 'active'
CHECK (policy_state IN ('active', 'legacy_unreviewed', 'revoking', 'disabled')),
-- A routable policy must carry durable proof that an operator explicitly
-- reviewed and saved the ordered contract. Shape alone is never
-- provenance: rows without this pair remain fail-closed.
policy_reviewed_at TIMESTAMPTZ NULL,
policy_reviewed_by TEXT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE (recipe_namespace, recipe_name, capability_family)
);
CREATE TABLE IF NOT EXISTS plugin_workload_sdk_invocations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
recipe_namespace TEXT NOT NULL,
recipe_name TEXT NOT NULL,
caller_ref TEXT NOT NULL,
correlation_id TEXT NULL,
method TEXT NOT NULL CHECK (method IN ('promptBridge','clientNotifications')),
detail TEXT NOT NULL,
purpose TEXT NULL,
idempotency_key_hash TEXT NOT NULL,
payload_hash TEXT NOT NULL DEFAULT '',
status TEXT NOT NULL CHECK (status IN ('in_progress','complete','failed','provider_unavailable','accepted','delivered')),
quota_consumed BOOLEAN NOT NULL DEFAULT true,
authorization_decision TEXT NOT NULL,
contract_version INTEGER NOT NULL DEFAULT 2 CHECK (contract_version IN (1, 2)),
-- Persisted, non-secret prompt authorization snapshot used to mint a
-- fresh fallback ticket without trusting caller-supplied target lists.
prompt_authorization JSONB NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-- Tracks the most recent lifecycle transition, including a failed
-- idempotent retry revived by CAS. Stale recovery must not use the
-- original reservation time for an active retry.
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
attempt_generation INTEGER NOT NULL DEFAULT 1 CHECK (attempt_generation >= 1),
lease_expires_at TIMESTAMPTZ NULL,
completed_at TIMESTAMPTZ NULL
);
CREATE TABLE IF NOT EXISTS plugin_workload_sdk_credential_ticket_jtis (
jti UUID PRIMARY KEY,
recipe_namespace TEXT NOT NULL,
recipe_name TEXT NOT NULL,
invocation_id UUID NOT NULL,
target_ref TEXT NOT NULL,
attempt_generation INTEGER NOT NULL DEFAULT 1 CHECK (attempt_generation >= 1),
expires_at TIMESTAMPTZ NOT NULL,
redeemed_at TIMESTAMPTZ NULL
);
CREATE INDEX IF NOT EXISTS plugin_workload_sdk_credential_ticket_jtis_expiry
ON plugin_workload_sdk_credential_ticket_jtis (expires_at);
CREATE TABLE IF NOT EXISTS plugin_workload_sdk_invocation_attempts (
invocation_id UUID NOT NULL,
recipe_namespace TEXT NOT NULL,
recipe_name TEXT NOT NULL,
attempt_generation INTEGER NOT NULL CHECK (attempt_generation >= 1),
method TEXT NOT NULL CHECK (method IN ('promptBridge','clientNotifications')),
target_refs JSONB NOT NULL DEFAULT '[]'::jsonb,
policy_revision INTEGER NULL,
policy_hash TEXT NULL,
status TEXT NOT NULL DEFAULT 'in_progress'
CHECK (status IN ('in_progress','complete','failed','provider_unavailable','accepted','delivered')),
started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
lease_expires_at TIMESTAMPTZ NULL,
completed_at TIMESTAMPTZ NULL,
PRIMARY KEY (invocation_id, attempt_generation)
);
ALTER TABLE plugin_workload_sdk_invocation_attempts
ADD COLUMN IF NOT EXISTS recipe_namespace TEXT,
ADD COLUMN IF NOT EXISTS recipe_name TEXT;
UPDATE plugin_workload_sdk_invocation_attempts attempts
SET recipe_namespace = invocations.recipe_namespace,
recipe_name = invocations.recipe_name
FROM plugin_workload_sdk_invocations invocations
WHERE attempts.invocation_id = invocations.id
AND (attempts.recipe_namespace IS NULL OR attempts.recipe_name IS NULL);
ALTER TABLE plugin_workload_sdk_invocation_attempts
ALTER COLUMN recipe_namespace SET NOT NULL,
ALTER COLUMN recipe_name SET NOT NULL;
CREATE INDEX IF NOT EXISTS plugin_workload_sdk_invocation_attempts_status_idx
ON plugin_workload_sdk_invocation_attempts (status, lease_expires_at);
-- Idempotency uniqueness scoped per recipe + method (OQ-5). Rows older
-- than the 24h TTL are pruned by prunePluginWorkloadSdkExpiredIdempotency
-- so keys become reusable in a new invocation period.
CREATE UNIQUE INDEX IF NOT EXISTS plugin_workload_sdk_invocations_idem
ON plugin_workload_sdk_invocations (recipe_namespace, recipe_name, method, idempotency_key_hash);
CREATE INDEX IF NOT EXISTS plugin_workload_sdk_invocations_recipe_created
ON plugin_workload_sdk_invocations (recipe_namespace, recipe_name, created_at DESC);
-- Historical-only since issue #348: per-run/period enforcement was removed
-- and nothing writes this table anymore (admin read path only). Retained so
-- existing rows stay queryable; hard removal is a separate follow-up. See the
-- note above getQuotaCounters in pluginWorkloadSdkDb.ts.
CREATE TABLE IF NOT EXISTS plugin_workload_sdk_quota_counters (
recipe_namespace TEXT NOT NULL,
recipe_name TEXT NOT NULL,
period_start TIMESTAMPTZ NOT NULL,
prompt_bridge_count INTEGER NOT NULL DEFAULT 0,
notification_count INTEGER NOT NULL DEFAULT 0,
last_updated TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (recipe_namespace, recipe_name, period_start)
);
`)
}
/** Drops the retired super-admin approval column from existing clusters. */
export async function dropPluginWorkloadSdkSuperAdminApprovedColumn(db: DbClient): Promise<void> {
await db.query(`
ALTER TABLE plugin_workload_sdk_grants
DROP COLUMN IF EXISTS super_admin_approved;
`)
}
/**
* Adds the explicit `provider` column to promptBridge grants (R1). Idempotent
* (IF NOT EXISTS) so it is a no-op on fresh clusters that already created the
* column via applyPluginWorkloadSdkSchema. Existing rows keep NULL and remain
* legacy/unreviewed; no provider is inferred from their model list.
*/
export async function addPluginWorkloadSdkProviderColumn(db: DbClient): Promise<void> {
await db.query(`
ALTER TABLE plugin_workload_sdk_grants
ADD COLUMN IF NOT EXISTS provider TEXT;
`)
}
/**
* Adds the explicit ordered promptBridge target policy. JSONB arrays retain
* operator-authored order; the writer updates every policy field together in
* one transaction and increments the revision on every replacement.
*/
export async function addPluginWorkloadSdkPromptTargetPolicyColumns(db: DbClient): Promise<void> {
await db.query(`
ALTER TABLE plugin_workload_sdk_grants
ADD COLUMN IF NOT EXISTS prompt_targets JSONB NOT NULL DEFAULT '[]'::jsonb,
ADD COLUMN IF NOT EXISTS default_target_ref TEXT NULL,
ADD COLUMN IF NOT EXISTS policy_revision INTEGER NOT NULL DEFAULT 1;
`)
}
/**
* Persists the original target suffix for JIT fallback ticket issuance and
* makes every ticket jti redeemable exactly once at credential introspection.
*/
export async function addPluginWorkloadSdkJitCredentialTicketColumns(db: DbClient): Promise<void> {
await db.query(`
ALTER TABLE plugin_workload_sdk_invocations
ADD COLUMN IF NOT EXISTS prompt_authorization JSONB NULL;
CREATE TABLE IF NOT EXISTS plugin_workload_sdk_credential_ticket_jtis (
jti UUID PRIMARY KEY,
recipe_namespace TEXT NOT NULL,
recipe_name TEXT NOT NULL,
invocation_id UUID NOT NULL,
target_ref TEXT NOT NULL,
expires_at TIMESTAMPTZ NOT NULL,
redeemed_at TIMESTAMPTZ NULL
);
CREATE INDEX IF NOT EXISTS plugin_workload_sdk_credential_ticket_jtis_expiry
ON plugin_workload_sdk_credential_ticket_jtis (expires_at);
`)
}
/**
* Monotonic attempt/lease migration. A policy written by the ordered-policy
* writer is already an explicit operator decision and must not be invalidated
* merely because the attempt ledger is being added. Only rows that cannot
* prove an ordered, provider-bound policy are moved to the fail-closed
* legacy state. No policy is reconstructed from the old flat model columns.
*/
export async function addPluginWorkloadSdkAttemptLedgerColumns(db: DbClient): Promise<void> {
await db.query(`
ALTER TABLE plugin_workload_sdk_grants
ADD COLUMN IF NOT EXISTS policy_state TEXT NOT NULL DEFAULT 'active';
DO $$ BEGIN
ALTER TABLE plugin_workload_sdk_grants
ADD CONSTRAINT plugin_workload_sdk_grants_policy_state_check
CHECK (policy_state IN ('active', 'legacy_unreviewed', 'revoking', 'disabled'));
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;
CREATE TABLE IF NOT EXISTS plugin_workload_sdk_invocation_attempts (
invocation_id UUID NOT NULL,
recipe_namespace TEXT NOT NULL,
recipe_name TEXT NOT NULL,
attempt_generation INTEGER NOT NULL CHECK (attempt_generation >= 1),
method TEXT NOT NULL CHECK (method IN ('promptBridge','clientNotifications')),
target_refs JSONB NOT NULL DEFAULT '[]'::jsonb,
policy_revision INTEGER NULL,
policy_hash TEXT NULL,
status TEXT NOT NULL DEFAULT 'in_progress'
CHECK (status IN ('in_progress','complete','failed','provider_unavailable','accepted','delivered')),
started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
lease_expires_at TIMESTAMPTZ NULL,
completed_at TIMESTAMPTZ NULL,
PRIMARY KEY (invocation_id, attempt_generation)
);
ALTER TABLE plugin_workload_sdk_invocation_attempts
ADD COLUMN IF NOT EXISTS recipe_namespace TEXT,
ADD COLUMN IF NOT EXISTS recipe_name TEXT;
UPDATE plugin_workload_sdk_invocation_attempts attempts
SET recipe_namespace = invocations.recipe_namespace,
recipe_name = invocations.recipe_name
FROM plugin_workload_sdk_invocations invocations
WHERE attempts.invocation_id = invocations.id
AND (attempts.recipe_namespace IS NULL OR attempts.recipe_name IS NULL);
ALTER TABLE plugin_workload_sdk_invocation_attempts
ALTER COLUMN recipe_namespace SET NOT NULL,
ALTER COLUMN recipe_name SET NOT NULL;
CREATE INDEX IF NOT EXISTS plugin_workload_sdk_invocation_attempts_status_idx
ON plugin_workload_sdk_invocation_attempts (status, lease_expires_at);
ALTER TABLE plugin_workload_sdk_invocations
ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
ADD COLUMN IF NOT EXISTS attempt_generation INTEGER NOT NULL DEFAULT 1,
ADD COLUMN IF NOT EXISTS lease_expires_at TIMESTAMPTZ NULL;
UPDATE plugin_workload_sdk_invocations
SET updated_at = COALESCE(updated_at, created_at),
attempt_generation = GREATEST(COALESCE(attempt_generation, 1), 1)
WHERE updated_at IS NULL OR attempt_generation IS NULL OR attempt_generation < 1;
DO $$ BEGIN
ALTER TABLE plugin_workload_sdk_invocations
ADD CONSTRAINT plugin_workload_sdk_invocations_attempt_generation_check
CHECK (attempt_generation >= 1);
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;
ALTER TABLE plugin_workload_sdk_credential_ticket_jtis
ADD COLUMN IF NOT EXISTS attempt_generation INTEGER NOT NULL DEFAULT 1;
DO $$ BEGIN
ALTER TABLE plugin_workload_sdk_credential_ticket_jtis
ADD CONSTRAINT plugin_workload_sdk_credential_ticket_jtis_attempt_generation_check
CHECK (attempt_generation >= 1);
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;
-- Rows that predate the attempt ledger still need an immutable receipt.
-- Backfill from the parent snapshot without inferring a new policy: legacy
-- promptBridge grants are blocked below until an operator explicitly
-- reviews them. A malformed/absent policyRevision remains NULL rather
-- than making the migration fail for an otherwise valid audit row.
INSERT INTO plugin_workload_sdk_invocation_attempts
(invocation_id, recipe_namespace, recipe_name, attempt_generation, method,
target_refs, policy_revision, policy_hash, status, started_at,
lease_expires_at, completed_at)
SELECT inv.id,
inv.recipe_namespace,
inv.recipe_name,
GREATEST(COALESCE(inv.attempt_generation, 1), 1),
inv.method,
COALESCE(inv.prompt_authorization->'authorizedTargetRefs', '[]'::jsonb),
CASE
WHEN jsonb_typeof(inv.prompt_authorization->'policyRevision') = 'number'
THEN (inv.prompt_authorization->>'policyRevision')::integer
ELSE NULL
END,
CASE
WHEN jsonb_typeof(inv.prompt_authorization->'policyHash') = 'string'
THEN inv.prompt_authorization->>'policyHash'
ELSE NULL
END,
inv.status,
inv.created_at,
CASE WHEN inv.status = 'in_progress' THEN inv.lease_expires_at ELSE NULL END,
inv.completed_at
FROM plugin_workload_sdk_invocations inv
WHERE NOT EXISTS (
SELECT 1
FROM plugin_workload_sdk_invocation_attempts existing
WHERE existing.invocation_id = inv.id
AND existing.attempt_generation = GREATEST(COALESCE(inv.attempt_generation, 1), 1)
)
ON CONFLICT (invocation_id, attempt_generation) DO NOTHING;
-- Preserve only a policy that proves the complete ordered contract. Do
-- not infer a routable policy from legacy provider/model columns. Rows
-- already revoking/disabled remain fenced; malformed/legacy active rows
-- become explicitly reviewable and fail closed.
UPDATE plugin_workload_sdk_grants
SET policy_state = 'legacy_unreviewed',
policy_revision = 0
WHERE capability_family = 'promptBridge'
AND policy_state = 'active'
AND NOT (
policy_revision >= 1
AND provider IS NOT NULL
AND btrim(provider) <> ''
AND default_target_ref IS NOT NULL
AND btrim(default_target_ref) <> ''
AND CASE WHEN jsonb_typeof(prompt_targets) = 'array'
THEN jsonb_array_length(prompt_targets) > 0 ELSE false END
AND CASE WHEN jsonb_typeof(allowed_callers) = 'array'
THEN jsonb_array_length(allowed_callers) > 0 ELSE false END
AND (prompt_targets -> 0 ->> 'targetRef') = default_target_ref
AND (prompt_targets -> 0 ->> 'provider') = provider
AND NOT EXISTS (
SELECT 1
FROM jsonb_array_elements(
CASE WHEN jsonb_typeof(prompt_targets) = 'array'
THEN prompt_targets ELSE '[]'::jsonb END
) AS target
WHERE jsonb_typeof(target) <> 'object'
OR btrim(COALESCE(target ->> 'targetRef', '')) = ''
OR btrim(COALESCE(target ->> 'provider', '')) = ''
OR btrim(COALESCE(target ->> 'model', '')) = ''
OR btrim(COALESCE(target ->> 'credentialSlot', '')) = ''
)
AND (
SELECT COUNT(DISTINCT target ->> 'targetRef')
FROM jsonb_array_elements(
CASE WHEN jsonb_typeof(prompt_targets) = 'array'
THEN prompt_targets ELSE '[]'::jsonb END
) AS target
) = CASE WHEN jsonb_typeof(prompt_targets) = 'array'
THEN jsonb_array_length(prompt_targets) ELSE 0 END
);
CREATE INDEX IF NOT EXISTS plugin_workload_sdk_invocations_lease_idx
ON plugin_workload_sdk_invocations (status, lease_expires_at)
WHERE status = 'in_progress';
`)
}
/**
* Migration 0086_plugin_workload_sdk_legacy_policy_repair is intentionally a
* no-op for policy authority. A complete
* JSON shape is not provenance of an operator decision, so a legacy row must
* never be reactivated automatically. The forward provenance migration below
* fences rows on clusters that already ran the original unsafe implementation.
*/
export async function repairPluginWorkloadSdkLegacyGrantPolicies(db: DbClient): Promise<void> {
// Keep the migration registered so already-applied version history remains
// monotonic. No UPDATE is intentionally executed here.
void db
}
/**
* Adds explicit operator-review provenance and re-fences every promptBridge
* row that lacks it. This is forward-only and safe for clusters where 0086
* already auto-reactivated a structurally complete legacy row: the row is
* quarantined again and must be explicitly saved through the admin API.
*/
export async function addPluginWorkloadSdkPolicyReviewProvenance(db: DbClient): Promise<void> {
await db.query(`
ALTER TABLE plugin_workload_sdk_grants
ADD COLUMN IF NOT EXISTS policy_reviewed_at TIMESTAMPTZ NULL,
ADD COLUMN IF NOT EXISTS policy_reviewed_by TEXT NULL;
UPDATE plugin_workload_sdk_grants
SET policy_state = 'legacy_unreviewed',
policy_revision = 0,
updated_at = now()
WHERE capability_family = 'promptBridge'
AND policy_state = 'active'
AND policy_reviewed_at IS NULL;
CREATE INDEX IF NOT EXISTS plugin_workload_sdk_grants_review_provenance_idx
ON plugin_workload_sdk_grants (policy_reviewed_at)
WHERE capability_family = 'promptBridge' AND policy_reviewed_at IS NULL;
`)
}
/** Adds the explicit usage source used by SDK-only promptBridge receipts. */
export async function addPluginWorkloadSdkUsageSourceKind(db: DbClient): Promise<void> {
await db.query(`ALTER TYPE usage_source_kind ADD VALUE IF NOT EXISTS 'plugin_workload_sdk';`)
}
/**
* Establishes the dual wire-contract and revocation state without rewriting
* any previously applied migration. Existing invocation rows are explicitly
* classified as legacy v1 and abandoned in-progress rows are terminalized so
* the new lease/sweep contract cannot inherit an unbounded old reservation.
*/
export async function addPluginWorkloadSdkProtocolAndRevocation(db: DbClient): Promise<void> {
await db.query(`
ALTER TABLE plugin_workload_sdk_grants
DROP CONSTRAINT IF EXISTS plugin_workload_sdk_grants_policy_state_check;
ALTER TABLE plugin_workload_sdk_grants
ADD CONSTRAINT plugin_workload_sdk_grants_policy_state_check
CHECK (policy_state IN ('active', 'legacy_unreviewed', 'revoking', 'disabled'));
ALTER TABLE plugin_workload_sdk_invocations
ADD COLUMN IF NOT EXISTS contract_version INTEGER NOT NULL DEFAULT 2;
-- Keep the default at v1 during the rolling-update compatibility window.
-- New writers send contract_version=2 explicitly; an older pod that omits
-- the column is therefore identifiable and can be swept conservatively.
ALTER TABLE plugin_workload_sdk_invocations
ALTER COLUMN contract_version SET DEFAULT 1;
DO $$ BEGIN
ALTER TABLE plugin_workload_sdk_invocations
ADD CONSTRAINT plugin_workload_sdk_invocations_contract_version_check
CHECK (contract_version IN (1, 2));
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;
-- Rows written before this migration used the pre-v2 endpoint. Preserve
-- that fact for status compatibility and operational inventory rather than
-- pretending they negotiated the target-aware/JIT contract.
UPDATE plugin_workload_sdk_invocations
SET contract_version = 1
WHERE contract_version = 2;
-- Old rows had no lease. They cannot be safely resumed after a rollout;
-- close them deterministically and fence any matching attempt receipt.
UPDATE plugin_workload_sdk_invocations
SET status = 'failed',
authorization_decision = CASE
WHEN authorization_decision = 'authorized' THEN 'migration_interrupted'
ELSE authorization_decision
END,
updated_at = now(),
completed_at = COALESCE(completed_at, now()),
lease_expires_at = NULL
WHERE contract_version = 1
AND status = 'in_progress'
AND lease_expires_at IS NULL;
UPDATE plugin_workload_sdk_invocation_attempts attempts
SET status = 'failed',
completed_at = COALESCE(attempts.completed_at, now()),
lease_expires_at = NULL
FROM plugin_workload_sdk_invocations invocations
WHERE attempts.invocation_id = invocations.id
AND attempts.attempt_generation = invocations.attempt_generation
AND invocations.contract_version = 1
AND invocations.status = 'failed';
DO $$ BEGIN
ALTER TABLE plugin_workload_sdk_invocations
ADD CONSTRAINT plugin_workload_sdk_invocations_v2_lease_check
CHECK (
contract_version = 1
OR status <> 'in_progress'
OR lease_expires_at IS NOT NULL
);
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;
CREATE INDEX IF NOT EXISTS plugin_workload_sdk_invocations_contract_idx
ON plugin_workload_sdk_invocations (recipe_namespace, recipe_name, contract_version, created_at DESC);
`)
}
/** Adds one immutable ledger row for every physical provider attempt. */
export async function addPluginWorkloadSdkProviderAttemptLedger(db: DbClient): Promise<void> {
await db.query(`
CREATE TABLE IF NOT EXISTS plugin_workload_sdk_provider_attempts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
invocation_id UUID NOT NULL,
recipe_namespace TEXT NOT NULL,
recipe_name TEXT NOT NULL,
attempt_generation INTEGER NOT NULL CHECK (attempt_generation >= 1),
attempt_index INTEGER NOT NULL CHECK (attempt_index BETWEEN 1 AND 4),
target_ref TEXT NOT NULL,
provider TEXT NOT NULL,
model TEXT NOT NULL,
credential_slot TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'reserved'
CHECK (status IN ('reserved','in_progress','complete','failed','provider_unavailable','skipped')),
credential_jti UUID NULL,
started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
lease_expires_at TIMESTAMPTZ NULL,
completed_at TIMESTAMPTZ NULL,
usage_request_id UUID NULL,
UNIQUE (invocation_id, attempt_generation, attempt_index)
);
ALTER TABLE plugin_workload_sdk_credential_ticket_jtis
ADD COLUMN IF NOT EXISTS provider_attempt_id UUID NULL;
CREATE INDEX IF NOT EXISTS plugin_workload_sdk_provider_attempts_invocation_idx
ON plugin_workload_sdk_provider_attempts (invocation_id, attempt_generation, attempt_index);
CREATE INDEX IF NOT EXISTS plugin_workload_sdk_provider_attempts_status_idx
ON plugin_workload_sdk_provider_attempts (status, lease_expires_at);
-- Existing clusters created the ledger before circuit-breaker skips became
-- auditable. Replace the inline constraint idempotently so those clusters
-- accept the same terminal status as fresh installs.
ALTER TABLE plugin_workload_sdk_provider_attempts
DROP CONSTRAINT IF EXISTS plugin_workload_sdk_provider_attempts_status_check;
ALTER TABLE plugin_workload_sdk_provider_attempts
ADD CONSTRAINT plugin_workload_sdk_provider_attempts_status_check
CHECK (status IN ('reserved','in_progress','complete','failed','provider_unavailable','skipped'));
`)
}
/**
* Adds the recipe-scoped revocation epoch used by the teardown kill-switch.
* A receipt is deliberately separate from policy_revision: policy revisions
* describe operator edits, while revocation_id is a one-shot CAS capability
* that prevents a stale reconciler from disabling a newly-authorized policy.
*/
export async function addPluginWorkloadSdkRevocationEpoch(db: DbClient): Promise<void> {
await db.query(`
ALTER TABLE plugin_workload_sdk_grants
ADD COLUMN IF NOT EXISTS revocation_id UUID NULL;
CREATE INDEX IF NOT EXISTS plugin_workload_sdk_grants_revocation_idx
ON plugin_workload_sdk_grants (recipe_namespace, recipe_name, revocation_id)
WHERE revocation_id IS NOT NULL;
`)
}
/** Applies the runtime ACL for the attempt-ledger tables added after the
* original runtime-role migration. The control API owns their lifecycle and
* retention; no workflow or maintenance runtime should be able to mutate or
* read these provider-attempt receipts. */
export async function addPluginWorkloadSdkRuntimeAccess(db: DbClient): Promise<void> {
await db.query(`
REVOKE ALL ON TABLE
plugin_workload_sdk_invocation_attempts,
plugin_workload_sdk_provider_attempts
FROM PUBLIC, trace_maintenance_runtime, workflow_recipes_runtime;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE
plugin_workload_sdk_invocation_attempts,
plugin_workload_sdk_provider_attempts
TO control_api_runtime;
`)
}
/**
* Reconciles the JIT credential-ticket table created after the original
* runtime-role migration. Existing clusters never received the broad 0062
* table grant for this relation, so rebuild its exact legacy-DML envelope in a
* forward-only migration. Revoking first also removes any accidental table
* privileges beyond the explicit runtime contract; repeated application is a
* no-op at the privilege boundary.
*/
export async function addPluginWorkloadSdkCredentialTicketRuntimeAccess(
db: DbClient
): Promise<void> {
await db.query(`
REVOKE ALL PRIVILEGES ON TABLE plugin_workload_sdk_credential_ticket_jtis
FROM PUBLIC, control_api_runtime, trace_maintenance_runtime, workflow_recipes_runtime;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE plugin_workload_sdk_credential_ticket_jtis
TO control_api_runtime;
`)
}
/**
* Durable spend outcome for a physical promptBridge attempt. The provider
* attempt ledger prevents a second execution, while this table preserves the
* accounting truth when the provider response is known only to have possibly
* executed (for example, a timeout or a lost completion acknowledgement).
* Rows are immutable and keyed by the physical attempt id so retries of the
* finalization request are safe.
*
* Rows are the immutable FLOOR: the least spend provable at write time. For
* oauth-broker attempts the effective outcome is derived at read time from the
* linked `llm_provider_attempts` usage (`loadSpendOutcome`); an `unknown` row
* is never rewritten to `exact`. Any raw SQL that reads this table without
* that join therefore reads a lower bound, not the effective spend — the
* billing truth for Codex already lives in `usage_events`, written by the
* proxy finalize.
*/
export async function addPluginWorkloadSdkSpendOutcomeLedger(db: DbClient): Promise<void> {
await db.query(`
CREATE TABLE IF NOT EXISTS plugin_workload_sdk_spend_outcomes (
provider_attempt_id UUID PRIMARY KEY,
invocation_id UUID NOT NULL,
recipe_namespace TEXT NOT NULL,
recipe_name TEXT NOT NULL,
attempt_generation INTEGER NOT NULL CHECK (attempt_generation >= 1),
attempt_index INTEGER NOT NULL CHECK (attempt_index BETWEEN 1 AND 4),
target_ref TEXT NOT NULL,
-- A normal finalization is bound to the runtime JWT host_ref. A stale
-- lease can be detected only after mcp-host has disappeared, however,
-- so that recovery row is intentionally unattributed rather than
-- fabricating a host identity from recipe names.
host_ref TEXT NULL,
provider TEXT NOT NULL,
model TEXT NOT NULL,
credential_slot TEXT NOT NULL,
outcome TEXT NOT NULL CHECK (outcome IN ('exact', 'unknown', 'not_executed')),
reason TEXT NOT NULL,
input_tokens INTEGER NULL CHECK (input_tokens IS NULL OR input_tokens >= 0),
output_tokens INTEGER NULL CHECK (output_tokens IS NULL OR output_tokens >= 0),
usage_request_id UUID NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT plugin_workload_sdk_spend_outcomes_token_pair_check
CHECK ((outcome = 'exact' AND input_tokens IS NOT NULL AND output_tokens IS NOT NULL)
OR (outcome IN ('unknown', 'not_executed')
AND input_tokens IS NULL AND output_tokens IS NULL))
);
CREATE INDEX IF NOT EXISTS plugin_workload_sdk_spend_outcomes_recipe_idx
ON plugin_workload_sdk_spend_outcomes (recipe_namespace, recipe_name, created_at DESC);
REVOKE ALL ON TABLE plugin_workload_sdk_spend_outcomes
FROM PUBLIC, trace_maintenance_runtime, workflow_recipes_runtime;
GRANT SELECT, INSERT ON TABLE plugin_workload_sdk_spend_outcomes TO control_api_runtime;
`)
}
/**
* Extend the immutable spend ledger for pre-provider failures. Older
* installations already have the table from 0084; this migration changes only
* the two constraints and never rewrites existing exact/unknown receipts.
*/
export async function addPluginWorkloadSdkNotExecutedSpendOutcome(db: DbClient): Promise<void> {
await db.query(`
ALTER TABLE plugin_workload_sdk_spend_outcomes
DROP CONSTRAINT IF EXISTS plugin_workload_sdk_spend_outcomes_token_pair_check;
ALTER TABLE plugin_workload_sdk_spend_outcomes
DROP CONSTRAINT IF EXISTS plugin_workload_sdk_spend_outcomes_outcome_check;
ALTER TABLE plugin_workload_sdk_spend_outcomes
ADD CONSTRAINT plugin_workload_sdk_spend_outcomes_outcome_check
CHECK (outcome IN ('exact', 'unknown', 'not_executed'));
ALTER TABLE plugin_workload_sdk_spend_outcomes
ADD CONSTRAINT plugin_workload_sdk_spend_outcomes_token_pair_check
CHECK ((outcome = 'exact' AND input_tokens IS NOT NULL AND output_tokens IS NOT NULL)
OR (outcome IN ('unknown', 'not_executed')
AND input_tokens IS NULL AND output_tokens IS NULL));
`)
}
/**
* The spend ledger was initially shipped with a required host_ref. Stale
* lease recovery has no bearer token from which to prove that binding, so
* existing clusters must converge to the same nullable shape as fresh ones.
*/
export async function relaxPluginWorkloadSdkSpendOutcomeHostRef(db: DbClient): Promise<void> {
await db.query(`
ALTER TABLE plugin_workload_sdk_spend_outcomes
ALTER COLUMN host_ref DROP NOT NULL;
`)
}