API / SQL reference

One governed run moves forward only after each gate succeeds. Policy and budget run inside the Action Service on every REQUEST_ACTION; approvals are out-of-band (Slack/email); execution is asynchronous until the executor callback completes.

Catalog and executor configuration live in the consumer app database (APP.*). Each intent and its evaluations live in the Action Service database (TESSRA.CONTROL.*), exposed back into the app through Native App references after install.

ACTION_INTENT.STATUS is the single source of truth for where the run sits. Shadow mode uses the SIMULATED_* variants.

Allowed transitions (enforce mode)

• PENDING_EVALUATION → DENIED, PENDING_APPROVAL, or APPROVED (first successful evaluation path).
• PENDING_APPROVAL → APPROVED or DENIED when Slack/email approval resolves.
• APPROVED → EXECUTING when the executor run starts → EXECUTED or FAILED when the callback completes.
• Terminal states (DENIED, EXECUTED, FAILED, SIMULATED_*) do not advance except via operator repair flows outside this contract.

Registers or updates a catalog action and toggles org-level enablement. Resolves ORG_ID from APP.DURABLE_ORG using the current account and database.

Signature

CREATE OR REPLACE PROCEDURE APP.UPSERT_ACTION_DEFINITION(
  ACTION_NAME VARCHAR,
  DISPLAY_NAME VARCHAR,
  DESCRIPTION VARCHAR,
  SOURCE_TYPE VARCHAR,
  PARAM_SCHEMA VARIANT,
  EXECUTION_MODE VARCHAR,
  ENABLED BOOLEAN
)
RETURNS VARCHAR

Example

CALL APP.UPSERT_ACTION_DEFINITION(
  'customer.credit_issue',
  'Customer credit',
  'Goodwill or recovery credit',
  'user',
  PARSE_JSON('{"type":"object","properties":{"amount":{"type":"number"},"currency":{"type":"string"}},"required":["amount"]}'),
  NULL,
  TRUE
);

Creates / updates (Snowflake APP schema)

• APP.ACTION_DEFINITION — MERGE on ACTION_KEY.
• APP.ORG_ACTION — MERGE or UPDATE for org + action enablement and source pack id.

Lifecycle impact

No intent rows. Required before UPSERT_ORG_POLICY_RULE, CONFIGURE_EXECUTOR, and catalog-backed REQUEST_ACTION paths that validate against the registered key.

Inserts or replaces one ordered policy band for an action and environment. Rules are evaluated in RULE_ORDER ascending; the first matching band determines ALLOW, REQUIRE_APPROVAL, or DENY.

Signature

CREATE OR REPLACE PROCEDURE APP.UPSERT_ORG_POLICY_RULE(
  ACTION_NAME VARCHAR,
  ENVIRONMENT VARCHAR,
  RULE_ORDER NUMBER,
  OPERATOR VARCHAR,
  THRESHOLD_VALUE FLOAT,
  OUTCOME VARCHAR,
  ENABLED BOOLEAN
)
RETURNS VARCHAR

Example

CALL APP.UPSERT_ORG_POLICY_RULE(
  'customer.credit_issue',
  'prod',
  1,
  '<=',
  100,
  'ALLOW',
  TRUE
);
CALL APP.UPSERT_ORG_POLICY_RULE(
  'customer.credit_issue',
  'prod',
  2,
  '<=',
  500,
  'REQUIRE_APPROVAL',
  TRUE
);

Creates / updates / deletes

• APP.ORG_POLICY — MERGE when enabled; DELETE when disabled.

Lifecycle impact

Changes apply to the next REQUEST_ACTION evaluation only; existing intents keep their persisted policy eval row.

Stores the downstream webhook for an org and action. Catalog-backed requests require an active http_webhook executor before the Action Service accepts the call.

Signature

CREATE OR REPLACE PROCEDURE APP.CONFIGURE_EXECUTOR(
  ACTION_NAME VARCHAR,
  EXECUTOR_TYPE VARCHAR,
  EXECUTOR_CONFIG VARIANT,
  ENABLED BOOLEAN DEFAULT TRUE
)
RETURNS VARCHAR

The url is your downstream executor, not the Tessra Action Service host. See examples/tessra-downstream-worker/README.md in this repo for a runnable pattern.

Example

CALL APP.CONFIGURE_EXECUTOR(
  'customer.credit_issue',
  'webhook',
  PARSE_JSON('{
    "type": "webhook",
    "url": "https://YOUR_DOWNSTREAM_HOST/tessra/webhook",
    "headers": {"X-Source": "tessra"}
  }'),
  TRUE
);

Creates / updates

• APP.ORG_EXECUTOR_CONFIG — MERGE on org + action; stores normalized JSON, endpoint URL, optional auth material, and static headers.

Lifecycle impact

Required for catalog-driven execution enqueue. Misconfiguration surfaces as EXECUTOR_NOT_CONFIGURED on request, not as a SQL error.

POSTs /v1/actions/request on the Action Service. Body includes org resolved from APP.DURABLE_ORG, validated params, optional context, and idempotency key. Returns JSON as a VARCHAR string to SQL callers.

Signature

CREATE OR REPLACE PROCEDURE APP.REQUEST_ACTION(
  ACTION_TYPE VARCHAR,
  PARAMS_PAYLOAD VARIANT,
  ENVIRONMENT VARCHAR DEFAULT '',
  TEAM VARCHAR DEFAULT '',
  REQUESTED_BY VARCHAR DEFAULT 'snowflake_sql',
  REASON VARCHAR DEFAULT 'Snowflake Native Action API',
  IDEMPOTENCY_KEY VARCHAR DEFAULT '',
  HARNESS_SECRET VARCHAR DEFAULT '',
  SERVICE_BASE_HOST VARCHAR DEFAULT '',
  SOURCE_SYSTEM VARCHAR DEFAULT 'snowflake_sql',
  CONTEXT_JSON VARIANT DEFAULT NULL
)
RETURNS VARCHAR

Example

CALL APP.REQUEST_ACTION(
  'customer.credit_issue',
  PARSE_JSON('{"customer_id":"c_1","amount":50,"currency":"USD"}'),
  'prod',
  '',
  '[email protected]',
  'Goodwill credit',
  'idem_20250421_001',
  '<HARNESS_SECRET>',
  'https://api.tessra.ai',
  'snowflake_sql',
  NULL
);

Creates / updates (Action Service CONTROL tables)

• ACTION_INTENT — insert; status moves from PENDING_EVALUATION to the evaluated outcome.
• ACTION_CONTEXT — insert snapshot for the intent.
• ACTION_POLICY_EVAL, ACTION_BUDGET_EVAL — insert verdict rows.
• ACTION_APPROVAL — insert when approval is required; updated when Slack/email resolves.
• Execution queue / projection tables — updated as the run progresses and callback lands.

Lifecycle impact

Starts or resumes the state machine for one intent. Duplicate IDEMPOTENCY_KEY for the same org and action returns the existing intent payload without creating a second row.

GET /v1/actions/<intent_id>/status. Returns JSON as VARCHAR merged from intent, ledger, execution surface, and approval delivery metadata.

Signature

CREATE OR REPLACE PROCEDURE APP.GET_ACTION_STATUS(
  INTENT_ID VARCHAR,
  SERVICE_BASE_HOST VARCHAR,
  HARNESS_SECRET VARCHAR DEFAULT ''
)
RETURNS VARCHAR

Example

CALL APP.GET_ACTION_STATUS(
  'a1b2c3d4-e5f6-7890-abcd-ef1234567890',
  'https://api.tessra.ai',
  '<HARNESS_SECRET>'
);

Creates / updates (Snowflake)

None — read-only HTTP from Snowflake.

Lifecycle impact

Observes status only. Typical JSON fields include status, policy_verdict, budget_verdict, approval_decision, execution_status, execution_id, external_tx_id.

Related: APP.GET_ACTION_RECEIPT

Same host and secret pattern; GET /v1/receipts/<intent_id> for a receipt-shaped document. Use status for polling state machines; use receipt when a signed summary is required downstream.

Created by CALL APP.CREATE_OR_REFRESH_UI_VIEWS() after Native App references bind. Column sets below match the shipped installer DDL.

APP.ACTION_INTENTS_VIEW

Thin read on ACTION_INTENT for operators and Streamlit.

SELECT intent_id, status, action_type, amount, created_at
FROM APP.ACTION_INTENTS_VIEW
ORDER BY created_at DESC
LIMIT 25;

APP.APPROVALS_VIEW

One row per approval record with channel metadata and decision timestamps.

SELECT intent_id, approval_channel, decision, decided_at
FROM APP.APPROVALS_VIEW
WHERE intent_id = '<INTENT_ID>';

APP.RECEIPTS_VIEW

One audit-ready row per intent: policy summary, approval flags, execution phase, derived next_step, and terminal boolean for UI and Cortex helpers.

SELECT intent_id, status, policy_decision, approval_required,
       execution_phase, next_step, terminal, result_json
FROM APP.RECEIPTS_VIEW
WHERE org_id = '<ORG_ID>'
ORDER BY created_at DESC
LIMIT 50;

• There is no SQL APPROVE_INTENT API. Humans act in Slack or email using links and tokens issued by the Action Service.
• Slack and email channels are selected from deploy-time configuration (APP.DURABLE_ORG and related approval config consumed by the service), not from extra procedure parameters on REQUEST_ACTION.
• Additional enterprise systems (for example ServiceNow) can be added as new approval delivery adapters behind the same pending row; the Native App contract stays stable.

Setup walkthrough: Configure approvals.

Webhook executors complete by POSTing results to the Action Service /v1/executions/callback (exact path and auth headers depend on your deployment manifest). Until that callback succeeds, the intent remains non-terminal even if the downstream system already applied side effects—design callbacks to be idempotent.

Thin wrappers that resolve ACTION_SERVICE_BASE_URL and harness secret from APP.DURABLE_ORG, then call the same HTTP contract as SQL operators.

-- Returns VARIANT (structured), not VARCHAR JSON
CALL APP.REQUEST_ACTION_FROM_CORTEX(
  '<action_key>',
  OBJECT_CONSTRUCT('amount', 50, 'currency', 'USD'),
  'Reason text'
);

CALL APP.GET_ACTION_STATUS_FOR_CORTEX('<intent_id>');

Cortex path reads APP.DURABLE_ORG automatically. For REQUEST_ACTION, pass the base URL explicitly or centralize it in your SQL harness.

UPDATE APP.DURABLE_ORG
SET ACTION_SERVICE_BASE_URL = 'https://api.tessra.ai',
    ACTION_SERVICE_HARNESS_SECRET = '<secret>'
WHERE WORKSPACE_KEY = LOWER(TRIM(CURRENT_ACCOUNT()))
  || ':' || LOWER(TRIM(CURRENT_DATABASE()));

• Missing idempotency key — an empty IDEMPOTENCY_KEY generates a fresh UUID per call, so retries create duplicate intents. Always pass a deterministic key per logical operation.
• Misconfigured executor — catalog actions without an active http_webhook row in APP.ORG_EXECUTOR_CONFIG fail fast with EXECUTOR_NOT_CONFIGURED even when policy would allow.
• Policy band gaps or order — RULE_ORDER must cover the amounts you send; unexpected DENY often means no band matched or the first match is DENY.
• Mixing service host and Native App DB — GET_ACTION_STATUS must target the same Action Service instance that received REQUEST_ACTION.