Airlock Stored Procedure Reference

Generated from the exact upstream stored procedure Markdown contract used by agents and bundled Airlock documentation.

This document defines the v1 contract for the airlock Snowflake stored procedures, covering both:

• airlock.admin.* – admin‑only control plane, maintenance, and billing-emission APIs
• airlock.observe.* – read-only governance and billing observability APIs
• airlock.agent.* – agent data access procedures

The goal is to provide a stable, predictable surface for app integrators, BI users, and tooling (e.g. Renaissance CLI).

Examples assume the installed app object is named airlock. If an account uses a different app name, substitute that name. For example, CALL airlock.agent.describe_spec(...) becomes CALL MY_AIRLOCK_APP.AGENT.DESCRIBE_SPEC(...) when the installed app object is named MY_AIRLOCK_APP; CALL airlock.agent.list_my_roles() becomes CALL MY_AIRLOCK_APP.AGENT.LIST_MY_ROLES().

Business agents should normally run with the app's app_agent application role and call AGENT.* procedures. Reserve app_admin and ADMIN.* procedures for authorized administrators and setup automation. A demo agent such as csmith may have an Airlock role named agent; that does not imply Snowflake app_admin.

If an agent gets Unknown user-defined function AIRLOCK.ADMIN... or AIRLOCK.AGENT..., first resolve the installed application object name; do not guess new procedure names. Use the app name the human provided, inspect the connection's existing worksheets/scripts, or run an account discovery query such as SHOW APPLICATIONS LIKE 'AIRLOCK%'. Once the app object is known, start agent discovery in the agent schema:

CALL <APP_NAME>.AGENT.LIST_MY_ROLES();
CALL <APP_NAME>.AGENT.LIST_MY_SPECS('<airlock_role>', TRUE);
CALL <APP_NAME>.AGENT.DESCRIBE_SPEC('<spec_name>', '<airlock_role>', TRUE);

Use OBSERVE.SPEC_CONFIG or OBSERVE.SPEC when the active Snowflake role has the app's app_observer or app_admin application role and the task is read-only governance observation rather than agent discovery. app_observer opens the read-only procedure family; Airlock assignments and each spec's observer_access rules determine the caller's actual spec scope.

Every mutating human or automated agent should connect as an attributable Snowflake principal. Direct work uses that principal's Airlock assignments; work for another principal uses explicit Airlock delegation. Snowflake user type, workspace roles, channel membership, reactions, and chat approval do not create Airlock authority. A channel may use a dedicated scoped observer principal, but that principal must not be reused for mutation. See Principal And Workspace Interoperability for the authority matrix, polling contract, and portable commitment receipt. See External Agent Provenance for the bounded workspace envelope, query-tag convention, and two-principal Native MCP proof.

---

#Guidance for AI agents and automation

This block is for agents and integrators calling procedures from SQL (Snowflake CLI, Snowsight worksheets, drivers, CI, and MCP tools). The same text is bundled with the Native App and returned in-SQL:

-- Default: TABLE(PAYLOAD VARIANT) — one row per heading (TOC); each PAYLOAD is structured JSON.
CALL airlock.agent.documentation();

-- Full bundled markdown in one row: PAYLOAD.kind = 'full', PAYLOAD.body (UTF-8).
CALL airlock.agent.documentation(CONTENT_MODE => 'FULL');

-- Procedure registry: one structured PAYLOAD row per stored-procedure signature.
CALL airlock.agent.documentation(CONTENT_MODE => 'PROCEDURES');

-- Slice by numbered section ids (matches ### N. and #### N.M in the bundled doc).
CALL airlock.agent.documentation('markdown', 'api_v1', 'SECTION', '4.8');

-- Chunk fallback for clients that truncate very large FULL result rows.
CALL airlock.agent.documentation('markdown', 'api_v1', 'CHUNK', '1-3');

Parameters are (output_format, doc_key, content_mode, section_ids) — all optional with defaults. Default content_mode is TOC: the first PAYLOAD is kind: documentation_manifest with content_hash / etag and agent_instructions, followed by heading rows where PAYLOAD is a small object (e.g. kind: toc_entry, level, section_id, title). Every heading has a section_id: numbered headings use ids such as 4.8, while unnumbered guidance headings use stable slug ids. Use CONTENT_MODE => 'PROCEDURES' for a compact structured procedure registry: first row is kind: procedure_index_manifest, followed by kind: procedure_entry rows with procedure_name, schema_name, short_name, signature, arguments, and section_id. Use CONTENT_MODE => 'FULL' for the entire document as markdown inside PAYLOAD.body. Use CONTENT_MODE => 'SECTION' with section_ids (e.g. '3', '4.8', 'guidance-for-ai-agents-and-automation') for kind: section_bundle and a sections array of {section_id, body} objects. Use CONTENT_MODE => 'CHUNK' to receive smaller kind: "chunk" rows; section_ids may be blank for all chunks, '1', '1-3', or 'offset=0,limit=12000'.

After each app upgrade, pull the contract with CALL airlock.agent.documentation(); and read PAYLOAD from each row (or use FULL / SECTION when you need prose bodies).

#Procedure mental model

Airlock procedures are meant to be guessable by family. Prefer named arguments whenever a procedure has optional parameters; positional calls are acceptable only for short discovery calls. Omit optional arguments you are not using instead of passing placeholder NULL values or typed casts. Named CALL syntax lets agents set only the fields that matter and avoids Snowflake argument-shape ambiguity.

• Discovery: User-scope discovery procedures may still use list_*, describe_*, and get_* names. Admin read-only inventory and detail procedures have moved to observe, which uses noun-style read-only names: plural nouns return collections (observe.specs), singular nouns return detail (observe.spec), and cross-cutting concepts use domain nouns (observe.activity). Optional filters come after the primary object name. User-scope discovery commonly accepts in_app_role and include_managed_roles so agents can choose an Airlock role lens without confusing it with a Snowflake role.
• Declarative configuration: create_* and alter_* admin procedures usually accept one descriptor VARIANT plus validate_only. Use validate_only, not dry_run, when validating a desired configuration without writing it.
• Operational previews: destructive operational procedures use dry_run when the result is a concrete preview of rows/files/events that would be changed. Drop-style configuration procedures use force when the caller is overriding dependency checks.
• Data submission: validate_data and load_data use exactly one source: path for an already-staged file, or file_content for inline CSV. For inline loads, pass filename. Agents and generated apps may author payloads as records JSON in their tool layer, but the installed procedure contract still receives CSV file_content; do not pass ad hoc records_json arguments. Records JSON payloads should contain only business records plus spec_name / filename; keep path_scope, in_app_role, on_behalf_of_user, delegation_id, workflow target, and attachment content as explicit procedure/tool context. Use a tool-side size guard before inline calls, and stage a file when the generated CSV is too large. OKF-style knowledge bundles are not CSV submissions; for specs that declare core_config.payload_adapter = "okf_knowledge_bundle", use admin.load_okf_bundle with base64 bundle bytes and parsed concept metadata. For shared-folder direct writes, pass path_scope. For delegated writes, pass on_behalf_of_user and let Airlock resolve the principal user's path. Do not pass path => NULL; simply omit path. load_data lands in the spec's configured initial workflow step. If that step is Draft and the caller wants Submitted, load first, pin any required evidence with add_file_reference, and then call edit_file_workflow; do not add workflow-state columns to the payload. load_data evaluates active Expectations before writing the manifest row: strict unmet expectations return EXPECTATION_BLOCKED; non-strict unmet expectations return EXPECTATION_WARNING while allowing the load.
• Payload discipline: build submitted files from the spec's column_config, file_rules, and attachment_policy. Do not invent ids or add Airlock lifecycle fields such as workflow_status, workflow_step, approval_status, approved_by, or approved_at unless those fields are explicitly declared by the spec as real source-system facts. Airlock returns file identity, workflow state, validation issues, and attachment status separately.
• Existing-file operations: attachment and workflow procedures operate on a manifest file and therefore use file_path / file_filename or path / filename as file identity, not staged file source.
• Delegation: delegation is user-to-agent authority for airlock.agent.* procedures. Delegated action procedures put on_behalf_of_user and delegation_id at the tail. In the common case, pass only on_behalf_of_user; omit delegation_id unless list_my_delegations shows multiple active grants that could match. Admin procedures are not delegated. A DELEGATED_ACTION event records the authorized attempt and identifies the actor, principal, and delegation; it does not by itself assert that the requested mutation committed. Normal file, attachment, and workflow events provide commitment evidence. load_data creates the file in the spec's configured initial workflow step; edit_file_workflow is required only when the delegate needs to move the file after load.
• Expectations: description should explain the business reason in human terms. clauses encode the cadence, sequence, interval, or action_limit; target_milestone names the workflow state being expected; is_strict says whether an unmet expectation can block workflow or load. Non-strict schedule/sequence/interval expectations surface as warnings/work items. For specs without file workflow, the current file slot is Active and the implicit upload milestone is became_active. action_limit expectations are usage limits, not work-inbox items; they are observable through observe.usage_limits / observe.usage_limit so auditors and agents can see the rule and recent counted interactions. effective_at / expires_at bound the lifecycle. Discovery should show configuration (observe.expectations / observe.expectation), current operational work (observe.work for observers or agent.list_my_work for agents, including DUE_AT and DAYS_UNTIL_DUE), and usage limits (observe.usage_limits) as distinct surfaces.
• Returns: operational mutation procedures should expose branchable outcomes such as STATUS, CODE, MESSAGE, ISSUES, DETAILS, and IS_CREATED / IS_UPDATED / IS_LOADED. Keep VALIDATION for procedures whose result is genuinely a configuration validation report. Agents should branch on stable fields and reason codes, then summarize prose for humans.
• Workspace receipts: after each supported agent.* or intentional admin.* mutation, preserve an airlock.commitment-receipt/v1 with the authenticated Snowflake actor, represented principal and delegation id derived from the actual call arguments, target, stable status/code/message/issues, and timestamp. After a successful direct file mutation, call agent.file_state with the returned target and retain its UPLOAD_ID, FILE_STATE_ID, and workflow state. Do not make that direct actor state read after delegated work; leave committed_state null unless the actor performs a separate independently authorized observation. Denied calls remain useful receipts but have no committed state.

#Admin read migration to Observe

Admin read-only procedures were retired in favor of the observe schema. The versioned app schema does not retain old procedures across app versions, so use these observe equivalents after upgrading:

Use airlock.observe.admin_activity when you specifically need admin mutation and maintenance activity filtered by stable procedure_name and action_family metadata. Use airlock.observe.spec_admin_activity when you need the admin create/update/delete timeline for one spec, with pointers to observe.spec_versions / observe.spec_version snapshots where available. New alter_spec events also include metadata-only CHANGED_SECTIONS and CHANGED_FIELDS labels so observers and agents can see what kind of spec definition changed before loading a full config snapshot.

#Day-one data submission recipe

For a new agent or generated app, follow one small loop before inventing anything:

1. Resolve the installed app object and use that name consistently.
2. Fetch CALL <APP_NAME>.AGENT.DOCUMENTATION(CONTENT_MODE => 'PROCEDURES');.
3. List roles with CALL <APP_NAME>.AGENT.LIST_MY_ROLES();.
4. List and describe specs with the selected Airlock role: LIST_MY_SPECS('<airlock_role>', TRUE) and DESCRIBE_SPEC('<spec_name>', '<airlock_role>', TRUE).
5. Build records only from the described spec. Records payloads contain only spec_name, filename, and records; all role, path, delegation, workflow, and attachment context stays outside the records payload.
6. Convert records JSON to CSV locally only for flat specs or declared variant columns, then call AGENT.VALIDATE_DATA and AGENT.LOAD_DATA with named arguments.
7. Use the returned PATH and FILENAME for follow-up attachments or workflow moves. Do not recompute file identity from a prompt or filename guess.
8. Report structured results first: STATUS, CODE, MESSAGE, ISSUES, path, filename, workflow state, and attachment status.

Direct writes use explicit role/path context such as in_app_role and path_scope. Delegated writes usually pass only on_behalf_of_user; add delegation_id only when Airlock reports multiple matching grants.

#What agents actually receive (no automatic “saved file”)

Snowflake returns a result set (rows/columns), not a workspace file or attachment. agent.documentation does not create a .md on disk for you — anything you retrieve exists only in that query result until you copy it somewhere durable.

Expectations for agents

1. Fetch — run CALL airlock.agent.documentation() (default TOC), CONTENT_MODE => 'PROCEDURES', CONTENT_MODE => 'FULL', or SECTION with section_ids.
2. Persist — if you need a durable file, assemble from PAYLOAD (e.g. FULL.body, or TOC/section JSON) and write to an artifact you control. SQL still does not create a file for you.
3. Re-read — in later turns, open that saved artifact (or call documentation again) instead of assuming rows still fit in context.
4. Avoid scraping — use PROCEDURES for structured procedure discovery; use SECTION, or use CHUNK and assemble rows by chunk_index when FULL is too large.
5. Cache — store content_hash / etag beside the saved artifact and re-fetch when the hash changes after an app upgrade.

Do not assume a single historical CALL remains in memory across sessions, tools, or humans; treat persistence as mandatory for any long-lived automation.

#Snowflake CLI / terminal clients (optional)

With CONTENT_MODE => 'FULL', a single row can still serialize as a very wide display: many clients pad to the longest line inside the embedded markdown body. Default TOC rows carry small VARIANT objects and are easier in a terminal grid.

Practical rule: for agents using Snowflake CLI, prefer --format JSON_EXT --silent. JSON_EXT preserves VARIANT columns such as PAYLOAD, VALIDATION, ISSUES, and DETAILS as nested JSON objects. Plain JSON may serialize those VARIANT values as strings, and TABLE is for humans, not automation.

snow sql -c myconn --format JSON_EXT --silent \
  -q "CALL airlock.agent.documentation(CONTENT_MODE => 'PROCEDURES');"

For full prose, do not rely on the default ASCII table for huge markdown. Redirect with a non-grid format, or use a driver:

snow sql -c myconn --format JSON_EXT --silent \
  -q "CALL airlock.agent.documentation(CONTENT_MODE => 'FULL');"

Agents should prefer a Snowflake driver or Snowflake CLI JSON_EXT when they need structured PAYLOAD in memory without CLI table art. Start with default CALL airlock.agent.documentation() (TOC) before FULL if you only need the outline.

#Explaining next steps to a human app_admin

Admins may be unfamiliar with Airlock. When you hit limits, briefly state what you attempted, the exact procedure name, error text or reason code, and one concrete remediation (below). Separate Snowflake access / role problems from Airlock license, Airlock role, or template problems. Prefer asking the human to run a specific CALL airlock.admin.* or to use Streamlit admin flows (roles, assignments, templates, licenses) when your session is app_agent only.

#Spec templates and delegated ownership (recommended automation path)

Goal: A trusted admin publishes an approved spec template (columns, file rules, workflow, optional guest model, locked fields). Automation accounts use airlock.agent.create_spec_from_template instead of inventing specs ad hoc. Airlock also installs a public default_spec_draft_template so licensed app_agent agents can start unpublished drafts for app_admin review without a custom template assignment.

Delegation: If the template defines template_rules.forced_owner_role (commonly app_admin for centralized ownership), an app_agent caller may still instantiate the spec: assignment to the template is proof the admin delegated that shape. The merged config carries template_config.template_rules so create validates the forced owner. Without forced_owner_role, a non-admin cannot create a spec owned only by a role outside their assignment subtree—design templates explicitly for the delegation you need.

Admin setup checklist (typically once per template/automation principal):

1. Ensure an Airlock role exists for the agent (e.g. ai_agent, automation_user) via airlock.admin.create_roles (or equivalent).
2. Create the template with airlock.admin.create_spec_template (include forced_owner_role when governance requires a fixed owner).
3. Assign the template to that Airlock role with airlock.admin.create_spec_template_assignment, or set is_public: true when every licensed app agent may use the template.
4. Assign the Snowflake user the agent uses to that Airlock role with airlock.admin.create_assignments (user_id is usually the Snowflake login name).
5. Ensure the agent holds a named license seat if it will call airlock.agent.* (get_my_license_seat, claim_license_seat() with no arguments, or admin licensing procedures).

Only app_admin maintains templates and assignments; do not widen automation to all admin.* procedures unless policy requires it.

#Chaining multiple agents (different Airlock roles)

Model multi-step pipelines with different Snowflake users or accounts, each granted APPLICATION ROLE app_agent but holding different Airlock roles (core.in_app_assignments). Each step gets narrow template assignments and spec/guest access—like a business where one party submits data, another validates completeness, a third runs fraud or duplication checks, and a final stage triggers payment or downstream export.

• Airlock roles (core.in_app_roles) are Airlock’s authorization labels; app_agent is only the Snowflake procedure privilege.
• Use file workflow, guest_access with isolated directories, and/or separate specs per stage so each agent sees only permitted paths (see docs/agent_access_control_model.md).
• References and materialized tables can support cross-stage analytics while keeping write scope tight.

When suggesting changes, name the Airlock role, template, and assignment the admin should add or adjust.

#If the agent cannot authenticate to Snowflake

Resolve account / network / credential / role issues before debugging Airlock procedures.

SSH keys are common for git or bastion access to a runner, not a substitute for Snowflake authentication itself—pair Snowflake auth with how your runner reaches the network.

#Permission gaps: options to give the human

---

#1. Versioning, Security & Compatibility

• Reference version: 1.0
• Schemas:
• airlock.admin – admin‑only mutation/control-plane procedures (Snowflake application role app_admin).
• airlock.observe – read-only governance observability procedures (application roles app_admin and app_observer).
• airlock.agent – agent-facing procedures (application roles app_agent and app_admin).

#1.1 Security & access model

• Airlock application roles
• app_admin can call all airlock.admin.*, airlock.observe.*, and airlock.agent.* procedures.
• app_observer can call only airlock.observe.* procedures. It does not receive direct core table/stage access and cannot call admin mutation procedures. Holding app_observer alone does not grant account-wide visibility: Airlock assignments and spec access rules scope each read.
• app_agent can call only airlock.agent.* procedures.
• Snowflake object security (GRANTs on the procedures themselves) is the primary enforcement mechanism for which application role can call which schema. For file mutation/validation flows, both airlock.agent.* and key airlock.admin.* wrappers use a shared policy decision point (PDP) for consistent in‑procedure authorization behavior and reason-code outcomes.

• Named-license gate model
• airlock.agent.* procedures and Streamlit usage require a named license seat (including callers with app_admin).
• Calls limited to airlock.observe.* do not claim or consume a named seat. An observer becomes seat-consuming only when it also uses the agent or Streamlit work surface.
• Auto-assign may grant a seat on first gated call when enabled.
• License administration under airlock.admin.* is the break-glass path and does not require the caller to already hold a seat.
• Operationally, if the seat pool is full, use CALL airlock.admin.* licensing procedures in SQL to unassign seats or raise the ceiling.

• In‑app roles and assignments
• Airlock roles (core.in_app_roles) and assignments (core.in_app_assignments) model application‑level access on top of Snowflake roles.
• Agent-facing APIs (airlock.agent.*) use these to enforce who can see which specs/files/history; internal checks here are about Airlock roles, not Snowflake application roles.
• Observer-facing APIs use the same assignment graph without invoking the seat gate. observer_access.observer_roles grants read-only spec visibility and may narrow it with path_filter and workflow_states.
• app_admin has account-wide observation. Non-admin account-wide auditors must be explicitly assigned the locked Airlock role global_observer; ordinary observer roles see only their assigned specs. global_observer is reserved case-insensitively for read-only observation and cannot be created, altered, dropped, own specs, receive guest/reviewer grants, or manage child roles.
• Admin procedures (airlock.admin.*) may expose a lens parameter (e.g. in_app_role) so admins can “see as Airlock role X” while still relying on Snowflake’s grants for coarse‑grained admin vs agent access.

• Detailed composition guide
• For the exact cascade of owner vs guest lens, role inheritance/assignment, spec state (is_published, is_archived), path targeting, and workflow mutation checks, see docs/agent_access_control_model.md.
• PDP troubleshooting and reason-code runbook: docs/pdp_authorization_runbook.md.

• Data access philosophy
• All data access goes through narrow, purpose‑built procedures (e.g. select_files, observe.activity) instead of direct table access.
• Procedures are written to:
• Accept simple scalar parameters (VARCHAR, BOOLEAN, TIMESTAMP) suitable for BI tools and SQL clients.
• Enforce row‑level and object‑level access based on Airlock roles and spec configuration (e.g. guest access).
• Avoid returning sensitive data (only metadata needed for audit/observability).

• Events & observability
• Application activity is recorded in core.events from two sources: (1) the Streamlit UI via record_event() in lib/utils/events.py, and (2) stored procedures that perform mutations (create/alter/drop roles, assignments, specs, retention policies; load/delete files; workflow transitions). Admin procedure activity is additionally journaled in core.ADMIN_ACTIVITY with stable procedure and action-family metadata. Events are surfaced through:
• airlock.observe.activity (callable by app_admin and app_observer) for querying events.
• Higher‑level analytical helpers in lib/utils/events.py for the Streamlit UI.
• Stored-procedure events add event_context.snowflake_agent_active from SYS_CONTEXT('SNOWFLAKE$CURRENT', 'IS_AGENT_ACTIVATED')::BOOLEAN inside the existing event insert. This is informational provenance, costs no additional Snowflake round trip, and never changes Airlock authority, policy, workflow, expectations, or billing.
• Event types used by procedures (aligned with UI where applicable): USER_LOGIN, LICENSE_DENIED, LICENSE_ASSIGNED (named-license gate / self-serve seat claim), RECORD_CREATE, RECORD_IMPORT, RECORD_UPDATE, RECORD_DELETE, FILE_UPLOAD, FILE_DELETE, FILE_RETENTION_PURGE (each row removed by delete_outdated_files; object_name is core.FILE_MANIFEST.pk, filename and spec_name/path set, upload_id in event_description), EVENTS_AUTO_PURGE (summary after scheduled trimming of core.events and core.ADMIN_ACTIVITY), WORKFLOW_CHANGE (workflow step transitions from edit_file_workflow). Admin procedure events are written to both activity stores under one ID, with exact response-loss reconciliation and a visible legacy-event fallback. Non-admin event recording remains best-effort and non-blocking.
• The events schema is optimized for:
• Who did what (username, in_app_role, object_name),
• When (event_timestamp),
• Why (event_type, event_description),
• With optional file/spec context (spec_name, path, filename, workflow_event).

• Retention & purging
• The events purge policy applies the same configured time window to core.events and core.ADMIN_ACTIVITY.
• Retention policies (core.retention_policies) plus observe.purge_candidates and admin.delete_outdated_files define a predictable lifecycle for spec files and their derived data.
• Successful delete_outdated_files runs (with dry_run => FALSE) emit one FILE_RETENTION_PURGE event per removed upload, in addition to returning one result row per file (including manifest_pk).
• Procedures are designed to be idempotent and auditable – destructive actions (deletes, drops) are either:
• Soft‑delete first (archive/mark), or
• Guarded by explicit force flags and locked‑state checks.

#1.2 Compatibility rules

Within v1, non‑breaking changes (new procedures, optional parameters with safe defaults, new trailing result columns) are allowed; breaking changes (renames, parameter/type/meaning changes) are not. See the rules and versioning guidance following the procedure matrix below.

#1.3 Procedure coverage matrix (admin vs observe vs user)

The platform has three core API categories with different trust boundaries:

• airlock.admin.* for control-plane and privileged maintenance operations.
• airlock.observe.* for read-only governance inventory, activity, and context.
• airlock.agent.* for assignment-scoped data operations and agent workflows.

The matrix below is a role/action inventory to verify command coverage, read coverage, and intentional boundaries. Read-only governance procedures live in the observe row rather than the admin command column.

Completeness check summary

• Agent API completeness: agent-facing CRUD is complete for assignment-scoped file workflows, attachments, and spec discovery/history paths. spec_state, batched spec_states, and file_state provide authorization-scoped change detection without retrieving governed rows. Privileged setup/administration actions are intentionally excluded from agent endpoints.
• Admin procedure completeness: admin coverage is complete for control-plane CRUD (roles, assignments, specs, templates, references, retention), file workflow and attachments, plus maintenance.
• Observe API completeness: observe coverage starts with read-only governance monitoring for roles, assignments, flattened observer scope and billing posture, delegations, specs, templates, files, compact spec/file state, attachment metadata, file timelines, workflow work, expectations, retention, references, licenses, billing posture, current billing line items, Marketplace billing-event history, billing activity, general activity rows, AI-readable activity summaries, an AI-readable governance map, point access explanations, per-agent context packets, per-role context packets, per-assignment context packets, per-delegation context packets, per-spec context packets, per-spec-version provenance packets, per-template context packets, per-attachment context packets, per-expectation context packets, per-reference context packets, and per-retention-policy context packets.
• Boundary model: agent endpoints perform in-procedure authorization checks and claim a named seat; admin endpoints rely on stronger app-role grants plus admin-only maintenance capabilities; observe endpoints are read-only, never return governed payload rows/bytes, do not claim a seat, and enforce either assignment-scoped spec visibility or explicit global_observer/app_admin access for account-wide surfaces.

#1.3 Observe procedure starting set

All observe procedures are read-only and callable at the Snowflake object layer by app_admin plus app_observer. In-procedure authorization then narrows results: spec procedures require an assigned role with access to that spec; path/workflow filters are enforced where file metadata is returned. Account-wide surfaces such as setup, health, governance map, activity summaries/context, admin activity, roles, assignments, observers, delegations, licenses, billing, templates, pending imports, and unscoped retention/reference inventories require app_admin or the locked Airlock role global_observer. Observe calls do not claim a named seat and never grant raw core table, stage, generated table, or governed file payload access.

• airlock.observe.setup(scope VARCHAR DEFAULT 'summary', subject_type VARCHAR DEFAULT NULL, subject_name VARCHAR DEFAULT NULL, max_items FLOAT DEFAULT 200)
• Description: Returns an AI-readable, account-wide setup inventory as TABLE(PAYLOAD VARIANT). Requires app_admin or an assignment to global_observer. Rows include setup_manifest, setup_counts, setup_roles, setup_assignments, setup_specs, setup_access_edges, setup_delegations, setup_expectations, setup_expectation_exceptions, setup_references, setup_retention_policies, setup_file_summary, setup_attachment_summary, setup_licenses, setup_billing, and setup_activity_retention.
• Parameters: scope accepts summary, inventory, or full; current payload shape is the same bounded inventory for all three. subject_type may be role, user, or spec; when set, subject_name is required. max_items caps array items per section (1-500).
• Returns: One PAYLOAD object per section. Parse by PAYLOAD.kind; do not scrape rendered SQL output.
• Examples:

    CALL airlock.observe.setup();
    CALL airlock.observe.setup(subject_type => 'spec', subject_name => 'budget_requests');
    CALL airlock.observe.setup(subject_type => 'role', subject_name => 'budget_analyst', max_items => 50);

• airlock.observe.health(scope VARCHAR DEFAULT 'summary', subject_type VARCHAR DEFAULT NULL, subject_name VARCHAR DEFAULT NULL, max_items FLOAT DEFAULT 200)
• Description: Returns account-wide governance health findings as TABLE(PAYLOAD VARIANT). Requires app_admin or an assignment to global_observer. Rows include health_manifest, health_summary, and health_findings. Findings use stable code, severity, domain, subject_type, subject_name, message, evidence, and recommended_action fields.
• Parameters: Same filter grammar as observe.setup: optional subject_type (role, user, or spec), required subject_name when subject type is set, and bounded max_items (1-500).
• Examples:

    CALL airlock.observe.health();
    CALL airlock.observe.health(subject_type => 'user', subject_name => 'CSMITH');
    CALL airlock.observe.health(subject_type => 'spec', subject_name => 'budget_requests');

• airlock.observe.governance_map(scope VARCHAR DEFAULT 'summary', subject_type VARCHAR DEFAULT NULL, subject_name VARCHAR DEFAULT NULL, include_inactive BOOLEAN DEFAULT FALSE, max_items FLOAT DEFAULT 200)
• Description: Returns an AI-readable governance graph as TABLE(PAYLOAD VARIANT). Rows include governance_map_manifest, governance_map_summary, governance_map_nodes, governance_map_edges, governance_map_evidence, and governance_map_findings. Nodes and edges cover Snowflake application roles, procedure schemas, users, Airlock roles, managed-by relationships, assignments, specs, path scopes, workflow steps and reviewers, reference access, restricted-reference policies, delegation grants, attachment policies, expectations, and action_limit links for restricted-reference reads.
• Parameters: Same filter grammar as observe.setup, plus include_inactive to include expired/revoked delegations, inactive expectations, archived specs, and inactive assignments. max_items caps array items per section (1-500).
• Examples:

    CALL airlock.observe.governance_map();
    CALL airlock.observe.governance_map(subject_type => 'user', subject_name => 'CSMITH');
    CALL airlock.observe.governance_map(subject_type => 'spec', subject_name => 'budget_requests', include_inactive => TRUE);

• airlock.observe.explain_access(user_id VARCHAR, spec_name VARCHAR, action VARCHAR DEFAULT 'read_validate', path_scope VARCHAR DEFAULT NULL, in_app_role VARCHAR DEFAULT NULL, include_managed_roles BOOLEAN DEFAULT TRUE, max_items FLOAT DEFAULT 200, object_key VARCHAR DEFAULT NULL)
• Description: Returns a read-only point explanation for one user, spec, action, optional path scope, optional Airlock role lens, and optional reference object key as TABLE(PAYLOAD VARIANT). Rows include access_explain_manifest, access_explain_summary, access_explain_roles, access_explain_edges, access_explain_path_scopes, access_explain_restricted_reference, and access_explain_findings.
• Parameters: action accepts canonical PDP action names (read_validate, get_reference_record, write_create, write_update, write_delete, workflow_transition, write_attachment_add, write_attachment_replace, write_attachment_delete) and common aliases such as load_data, delete_files, add_attachment, and restricted_reference. path_scope is a logical Airlock path scope, not a raw stage path. For get_reference_record, pass object_key to explain the restricted-reference object path; path_scope is accepted as a compatibility fallback only. in_app_role optionally narrows the Airlock role lens; include_managed_roles controls whether managed child roles are considered.
• Returns: A compiled-access explanation with allowed, reason_code, matched assignments/roles, compiled access edges, candidate path scopes, restricted-reference policy/object-path/action-limit evidence when relevant, and findings. It intentionally reads compiled Airlock access evidence and does not run user-procedure side effects such as creating isolated-directory path keys or querying reference data.
• Examples:

    CALL airlock.observe.explain_access('CSMITH', 'budget_requests', 'load_data', 'asmith');
    CALL airlock.observe.explain_access(user_id => 'CSMITH', spec_name => 'budget_requests', action => 'read_validate');
    CALL airlock.observe.explain_access(user_id => 'CSMITH', spec_name => 'customer_identity', action => 'get_reference_record', object_key => 'customers');

• airlock.observe.agent_context(agent_id VARCHAR, in_app_role VARCHAR DEFAULT NULL, include_managed_roles BOOLEAN DEFAULT TRUE, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only agent context packet as TABLE(PAYLOAD VARIANT). Rows include agent_context_manifest, agent_context_summary, agent_context_roles, agent_context_specs, agent_context_path_scopes, agent_context_delegations, agent_context_expectations, and agent_context_recent_activity.
• Parameters: agent_id is required and maps to the Snowflake user/principal identifier. in_app_role optionally narrows the Airlock role lens, and include_managed_roles controls managed-role expansion for compiled access evidence. max_items caps arrays per section (1-500).
• Returns: Effective role closure, accessible specs, path scopes, active delegation boundaries, expectations on accessible specs, and recent activity. It intentionally reads compiled Airlock access evidence and does not run agent-procedure side effects or expose raw file data.
• Examples:

    CALL airlock.observe.agent_context('CSMITH');
    CALL airlock.observe.agent_context(agent_id => 'CSMITH', in_app_role => 'observer', include_managed_roles => FALSE);

• airlock.observe.role_context(role_name VARCHAR, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only Airlock-role context packet as TABLE(PAYLOAD VARIANT). Rows include role_context_manifest, role_context_summary, role_context_role, role_context_child_roles, role_context_assignments, role_context_effective_users, role_context_spec_edges, role_context_owned_specs, role_context_delegations, role_context_expectations, and role_context_recent_activity.
• Parameters: role_name is required. max_items caps arrays per section (1-500).
• Returns: Role metadata, managed child roles, assignments, effective users through compiled role closure, compiled spec access, owned specs, active delegations, expectations on accessible specs, and recent role-tagged activity. It does not expose raw governed file rows or attachment bytes.
• Examples:

    CALL airlock.observe.role_context('observer');
    CALL airlock.observe.role_context(role_name => 'finadmin', max_items => 50);

• airlock.observe.assignment_context(assignment_name VARCHAR, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only assignment context packet as TABLE(PAYLOAD VARIANT). Rows include assignment_context_manifest, assignment_context_summary, assignment_context_assignment, assignment_context_role_closure, assignment_context_spec_edges, assignment_context_specs, assignment_context_path_scopes, assignment_context_delegations, assignment_context_expectations, and assignment_context_recent_activity.
• Parameters: assignment_name is required. max_items caps arrays per section (1-500).
• Returns: The assignment row, direct and managed-role closure, compiled spec/path access created by that assignment, related delegations, expectations on accessible specs, and recent activity. It does not expose raw governed file rows or attachment bytes.
• Examples:

    CALL airlock.observe.assignment_context('observer.aroberts');
    CALL airlock.observe.assignment_context(assignment_name => 'finance.csmith', max_items => 50);

• airlock.observe.delegation_context(delegation_id VARCHAR, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only delegation context packet as TABLE(PAYLOAD VARIANT). Rows include delegation_context_manifest, delegation_context_summary, delegation_context_delegation, delegation_context_spec, delegation_context_actor_roles, delegation_context_principal_roles, delegation_context_actor_spec_edges, delegation_context_principal_spec_edges, delegation_context_expectations, and delegation_context_recent_activity.
• Parameters: delegation_id is required. max_items caps arrays per section (1-500).
• Returns: The delegation grant, delegated spec, actor/principal role evidence, actor/principal spec access evidence, expectations on the delegated spec, and recent activity involving the actor or principal. It does not expose raw governed file rows or attachment bytes.
• Examples:

    CALL airlock.observe.delegation_context('01c4f1a6-0313-ba32-0000-c16b022e0f1a');
    CALL airlock.observe.delegation_context(delegation_id => '<delegation_id>', max_items => 50);

• airlock.observe.spec_context(spec_name VARCHAR, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only spec context packet as TABLE(PAYLOAD VARIANT). Rows include spec_context_manifest, spec_context_summary, spec_context_spec, spec_context_role_edges, spec_context_users, spec_context_delegations, spec_context_expectations, spec_context_references, spec_context_file_summary, spec_context_spec_admin_activity, and spec_context_recent_activity. Use spec admin activity version ids with observe.spec_version for exact stage-backed config snapshots; alter_spec activity also carries metadata-only changed section/field labels.
• Parameters: spec_name is required. max_items caps arrays per section (1-500).
• Returns: Spec metadata/config, compiled role and principal access evidence, active delegation boundaries, expectations, source/reference links, file and attachment counts, and recent activity. It does not expose raw governed file rows or attachment bytes.
• Examples:

    CALL airlock.observe.spec_context('finance');
    CALL airlock.observe.spec_context(spec_name => 'budget_requests', max_items => 50);

• airlock.observe.spec_version_context(spec_name VARCHAR, version_id VARCHAR, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only spec-version provenance packet as TABLE(PAYLOAD VARIANT). Rows include spec_version_context_manifest, spec_version_context_summary, spec_version_context_selected_version, spec_version_context_current_spec, spec_version_context_neighboring_versions, and spec_version_context_recent_activity.
• Parameters: spec_name and version_id are required. max_items caps arrays per section (1-500).
• Returns: The selected stage-backed spec config, sidecar metadata, current runtime spec config, nearby version metadata, and recent governance activity. It does not expose raw governed file rows or attachment bytes.
• Examples:

    CALL airlock.observe.spec_version_context('budget_requests', '20260315123045');
    CALL airlock.observe.spec_version_context(spec_name => 'finance', version_id => '20260315123045', max_items => 50);

• airlock.observe.expectation_context(expectation_name VARCHAR, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only expectation context packet as TABLE(PAYLOAD VARIANT). Rows include expectation_context_manifest, expectation_context_summary, expectation_context_expectation, expectation_context_spec, expectation_context_exceptions, expectation_context_workflow_counts, expectation_context_file_evidence, and expectation_context_recent_activity.
• Parameters: expectation_name is required. max_items caps arrays per section (1-500).
• Returns: Expectation metadata, owning spec context, active exception evidence, workflow/file evidence, and recent activity. It does not run workflow gates; use observe.work for current due, late, unmet, or excepted expectation work.
• Examples:

    CALL airlock.observe.expectation_context('monthly_finance_upload');
    CALL airlock.observe.expectation_context(expectation_name => 'monthly_finance_upload', max_items => 50);

• airlock.observe.usage_limits(spec_name VARCHAR DEFAULT NULL, username VARCHAR DEFAULT NULL, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only packet for action_limit Expectations and recent REFERENCE_READ events that consumed those limits.
• Parameters: spec_name and username are optional filters. max_items caps arrays per section (1-500).
• Returns: Manifest, summary, action_limit rules, latest emitted usage counters, and bounded recent activity. It does not query raw reference targets or file rows.
• Examples:

    CALL airlock.observe.usage_limits();
    CALL airlock.observe.usage_limits(spec_name => 'customer_identity', username => 'CSMITH', max_items => 50);

• airlock.observe.usage_limit(expectation_name VARCHAR, username VARCHAR DEFAULT NULL, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only packet for one action_limit Expectation.
• Parameters: expectation_name is required. username is an optional activity filter. max_items caps arrays per section (1-500).
• Returns: The configured rule, latest usage status (action_limit_used, action_time_period, used_count, remaining_count when emitted), and bounded recent activity for the matching controlled action.
• Examples:

    CALL airlock.observe.usage_limit('support_customer_identity_daily_limit');
    CALL airlock.observe.usage_limit(expectation_name => 'support_customer_identity_daily_limit', username => 'CSMITH');

• airlock.observe.reference_context(reference_name VARCHAR, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only reference context packet as TABLE(PAYLOAD VARIANT). Rows include reference_context_manifest, reference_context_summary, reference_context_reference, reference_context_allowed_roles, reference_context_effective_users, reference_context_backing_spec, reference_context_restricted_reference, reference_context_source_links, reference_context_file_references, and reference_context_recent_activity.
• Parameters: reference_name is required. max_items caps arrays per section (1-500).
• Returns: Reference registry metadata, granted Airlock roles, effective users through role closure, backing spec context, explicit restricted_reference policy shape when present, bounded restricted-read activity evidence, source/file-reference lineage evidence, and recent activity. It does not query raw reference targets or file rows.
• Examples:

    CALL airlock.observe.reference_context('customer_ref');
    CALL airlock.observe.reference_context(reference_name => 'customer_ref', max_items => 50);

• airlock.observe.retention_context(policy_name VARCHAR, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only retention-policy context packet as TABLE(PAYLOAD VARIANT). Rows include retention_context_manifest, retention_context_summary, retention_context_policy, retention_context_covered_specs, retention_context_purge_candidates, and retention_context_recent_activity.
• Parameters: policy_name is required. max_items caps arrays per section (1-500).
• Returns: Policy metadata, specs currently governed by the policy, bounded purge candidate evidence using the same read-only eligibility logic as observe.purge_candidates, and retention-related activity. For the account default policy, covered specs exclude specs with an active spec-specific policy override. It does not mutate manifests, stages, policies, or files.
• Examples:

    CALL airlock.observe.retention_context('Account Default');
    CALL airlock.observe.retention_context(policy_name => 'Spec X Retention', max_items => 50);

• airlock.observe.spec_template_context(template_name VARCHAR, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only spec-template context packet as TABLE(PAYLOAD VARIANT). Rows include spec_template_context_manifest, spec_template_context_summary, spec_template_context_template, spec_template_context_forced_owner_role, spec_template_context_assignments, spec_template_context_effective_users, and spec_template_context_recent_activity.
• Parameters: template_name is required. max_items caps arrays per section (1-500).
• Returns: Template metadata/rules, forced owner role evidence, template assignment grants, effective users through Airlock role closure, and recent template activity. It does not mutate templates, assignments, specs, or files.
• Examples:

    CALL airlock.observe.spec_template_context('default_spec_draft_template');
    CALL airlock.observe.spec_template_context(template_name => 'finance_spec_template', max_items => 50);

• airlock.observe.procedures(procedure_name VARCHAR DEFAULT NULL, schema_name VARCHAR DEFAULT NULL)
• Description: Returns a read-only installed procedure catalog from Airlock's bundled API documentation as TABLE(PAYLOAD VARIANT). The first row is procedure_catalog_manifest; following rows are procedure_catalog_entry objects with procedure_name, schema_name, short_name, signature, arguments, and documentation section metadata.
• Parameters: procedure_name may be a short name (setup), schema-qualified name (observe.setup), or full name (airlock.observe.setup). schema_name may be admin, observe, or agent.
• Examples:

    CALL airlock.observe.procedures();
    CALL airlock.observe.procedures(schema_name => 'observe');
    CALL airlock.observe.procedures(procedure_name => 'observe.health');

• airlock.observe.mcp_servers()
• Description: Returns the fixed Airlock agent and observer Native App MCP interfaces and whether each Snowflake-managed MCP object is currently enabled. Requires app_admin or the locked global_observer Airlock role.
• Returns: TABLE(INTERFACE_NAME, MCP_SERVER_NAME, APPLICATION_ROLE, ENABLED, STATUS, CODE, MESSAGE, ENDPOINT_PATH, TOOL_NAMES). MCP_STATUS_UNAVAILABLE distinguishes an unsupported or blocked Snowflake capability from an ordinary disabled server.
• Boundary: Returns setup metadata only. It never returns authentication secrets, chat content, governed rows, restricted-reference values, or attachment bytes.
• Example:

    CALL airlock.observe.mcp_servers();

• airlock.observe.activity(username VARCHAR DEFAULT NULL, event_types VARCHAR DEFAULT NULL, in_app_role VARCHAR DEFAULT NULL, event_description VARCHAR DEFAULT NULL, start_time TIMESTAMP_LTZ DEFAULT NULL, end_time TIMESTAMP_LTZ DEFAULT NULL, limit_rows INTEGER DEFAULT 200)
• Description: Read-only governance activity feed over core.events with optional filters. Scoped observers receive only events within their assigned spec/path scope; app_admin and assigned global_observer callers receive account-wide activity.
• Returns: EVENT_TIMESTAMP, EVENT_TYPE, EVENT_DESCRIPTION, USERNAME, IN_APP_ROLE, OBJECT_NAME, SPEC_NAME, EVENT_CONTEXT, plus trailing stable EVENT_ID. Pass EVENT_ID to observe.activity_context for related governance evidence.

• airlock.observe.admin_activity(procedure_name VARCHAR DEFAULT NULL, action_family VARCHAR DEFAULT NULL, username VARCHAR DEFAULT NULL, event_types VARCHAR DEFAULT NULL, start_time TIMESTAMP_LTZ DEFAULT NULL, end_time TIMESTAMP_LTZ DEFAULT NULL, limit_rows INTEGER DEFAULT 200)
• Description: Read-only admin mutation and maintenance activity feed over the append-only core.ADMIN_ACTIVITY journal plus unmatched legacy or fallback rows from core.events. Filters and returns structured procedure/action fields so account-wide observers and administrators can ask what admins did without parsing prose. Requires app_admin or an assignment to global_observer.
• Parameters: procedure_name accepts short names such as load_data or schema-qualified names such as admin.load_data; action_family groups related admin actions such as roles, assignments, specs, files, attachments, workflow, billing, licenses, events, references, retention, expectations, okf, and maintenance.
• Returns: EVENT_TIMESTAMP, EVENT_TYPE, EVENT_DESCRIPTION, USERNAME, IN_APP_ROLE, PROCEDURE_NAME, ACTION_FAMILY, OBJECT_NAME, SPEC_NAME, EVENT_CONTEXT, plus trailing stable EVENT_ID and EVIDENCE_SOURCE (journal for canonical append-only evidence or legacy_event for an unmatched compatibility row).
• Examples:

    CALL airlock.observe.admin_activity();
    CALL airlock.observe.admin_activity(procedure_name => 'admin.load_data');
    CALL airlock.observe.admin_activity(action_family => 'billing', event_types => 'BILLING_EMIT');

• airlock.observe.spec_admin_activity(spec_name VARCHAR, event_types VARCHAR DEFAULT NULL, start_time TIMESTAMP_LTZ DEFAULT NULL, end_time TIMESTAMP_LTZ DEFAULT NULL, limit_rows INTEGER DEFAULT 200)
• Description: Read-only admin create/update/delete timeline for one spec definition. Returns current core.specs created/modified audit fields when the spec still exists, plus spec config version pointers when the event wrote a stage-backed version. alter_spec events include metadata-only CHANGED_SECTIONS and CHANGED_FIELDS arrays.
• Returns: EVENT_TIMESTAMP, EVENT_TYPE, EVENT_DESCRIPTION, USERNAME, PROCEDURE_NAME, SPEC_EVENT, SPEC_NAME, current created/modified columns, VERSION_ID, VERSION_PATH, META_PATH, SPEC_CONFIG_HASH, CHANGED_SECTIONS, CHANGED_FIELDS, EVENT_CONTEXT, plus trailing stable EVENT_ID and EVIDENCE_SOURCE (journal or legacy_event).
• Examples:

    CALL airlock.observe.spec_admin_activity('budget_requests');
    CALL airlock.observe.spec_admin_activity('budget_requests', event_types => 'RECORD_UPDATE');
    CALL airlock.observe.spec_versions('budget_requests');
    CALL airlock.observe.spec_version('budget_requests', '<version_id>');

• airlock.observe.activity_summary(username VARCHAR DEFAULT NULL, event_types VARCHAR DEFAULT NULL, in_app_role VARCHAR DEFAULT NULL, event_description VARCHAR DEFAULT NULL, start_time TIMESTAMP_LTZ DEFAULT NULL, end_time TIMESTAMP_LTZ DEFAULT NULL, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only AI-oriented activity packet as TABLE(PAYLOAD VARIANT). Rows include activity_summary_manifest, activity_summary, activity_summary_by_event_type, activity_summary_by_user, activity_summary_by_spec, and activity_summary_recent_items.
• Parameters: Same filters as observe.activity; event_types accepts a comma-separated list. max_items caps arrays per section (1-500).
• Returns: Event volume, time bounds, grouped activity counts, and bounded recent activity items with event_context. It does not write read-audit events.
• Examples:

    CALL airlock.observe.activity_summary();
    CALL airlock.observe.activity_summary(event_types => 'FILE_UPLOAD,WORKFLOW_CHANGE', max_items => 50);
    CALL airlock.observe.activity_summary(username => 'CSMITH');

• airlock.observe.activity_context(event_id VARCHAR, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only context packet for one governance event as TABLE(PAYLOAD VARIANT). Rows include activity_context_manifest, activity_context_summary, activity_context_event, activity_context_related_spec, activity_context_related_files, activity_context_related_attachments, activity_context_actor_access, and activity_context_nearby_activity.
• Parameters: event_id is required; use EVENT_ID from observe.activity or the event_id returned by observe.activity_summary context rows. max_items caps arrays per section (1-500).
• Returns: The source event row, related spec/file/attachment metadata, current actor access evidence for the event spec, and nearby activity for the same object/spec/path. Raw file and attachment bytes are never returned, and the procedure does not mutate events, access, files, attachments, or stages.
• Examples:

    CALL airlock.observe.activity_context('event-pk-or-uuid');
    CALL airlock.observe.activity_context(event_id => '<event_id>', max_items => 50);

• airlock.observe.attachments(spec_name VARCHAR DEFAULT NULL, path VARCHAR DEFAULT NULL, filename VARCHAR DEFAULT NULL, include_deleted BOOLEAN DEFAULT FALSE, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only attachment metadata inventory as TABLE(PAYLOAD VARIANT). Rows include attachments_manifest, attachments_summary, attachments_items, attachments_by_spec, and attachments_by_tag.
• Parameters: Optional spec_name, path, and filename filters narrow the inventory to one spec or logical file. Deleted attachments are excluded unless include_deleted => TRUE. max_items caps arrays per section (1-500).
• Returns: Attachment IDs, spec/file linkage, stage paths, filenames, tags, types, sizes, descriptions, audit timestamps, and deleted state. Raw attachment bytes are never returned.
• Examples:

    CALL airlock.observe.attachments();
    CALL airlock.observe.attachments(spec_name => 'finance');
    CALL airlock.observe.attachments(spec_name => 'finance', path => 'asmith', filename => 'budget_june', include_deleted => TRUE);

• airlock.observe.attachment_context(attachment_id VARCHAR, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only attachment context packet as TABLE(PAYLOAD VARIANT). Rows include attachment_context_manifest, attachment_context_summary, attachment_context_attachment, attachment_context_spec_policy, attachment_context_linked_file, attachment_context_sibling_attachments, and attachment_context_recent_activity.
• Parameters: attachment_id is required. max_items caps arrays per section (1-500).
• Returns: Attachment metadata, linked logical file metadata when applicable, spec attachment policy, same-file/tag sibling attachment evidence, and recent attachment/file activity. Raw attachment bytes are never returned.
• Examples:

    CALL airlock.observe.attachment_context('9d4f5f7f-...');
    CALL airlock.observe.attachment_context(attachment_id => '<attachment_id>', max_items => 50);

• airlock.observe.file_timeline(spec_name VARCHAR, path VARCHAR, filename VARCHAR, max_items FLOAT DEFAULT 200)
• Description: Returns an AI-readable timeline for one governed logical file as TABLE(PAYLOAD VARIANT). Rows include file_timeline_manifest, file_timeline_summary, file_timeline_items, file_timeline_file_versions, file_timeline_attachments, and file_timeline_file_references.
• Parameters: spec_name, path, and filename identify the logical file in core.FILE_MANIFEST. max_items caps arrays per section (1-500).
• Returns: Bounded timeline and inventory rows from manifest versions, activity events, attachment metadata, and file-reference edges. Raw file and attachment bytes are never returned.
• Examples:

    CALL airlock.observe.file_timeline('finance', 'default', 'budget_june');
    CALL airlock.observe.file_timeline(spec_name => 'finance', path => 'asmith', filename => 'budget_june', max_items => 50);

• airlock.observe.path_context(spec_name VARCHAR, path_scope VARCHAR, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only path-scope context packet for one governed spec path as TABLE(PAYLOAD VARIANT). Rows include path_context_manifest, path_context_summary, path_context_spec, path_context_role_edges, path_context_users, path_context_file_counts, path_context_files, path_context_attachments, path_context_expectations, and path_context_recent_activity.
• Parameters: spec_name and path_scope are required. max_items caps arrays per section (1-500).
• Returns: Exact/broad/dynamic path access evidence, effective users, current file-state counts, bounded active file and attachment metadata, active expectations, and recent path activity. Raw file and attachment bytes are never returned, and the procedure does not mutate access, files, attachments, expectations, or stages.
• Examples:

    CALL airlock.observe.path_context('finance', 'asmith');
    CALL airlock.observe.path_context(spec_name => 'budget_requests', path_scope => 'public/full_access', max_items => 50);

• airlock.observe.workflow_context(spec_name VARCHAR, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only workflow context packet for one governed spec as TABLE(PAYLOAD VARIANT). Rows include workflow_context_manifest, workflow_context_summary, workflow_context_spec, workflow_context_steps, workflow_context_reviewers, workflow_context_file_counts, workflow_context_files, and workflow_context_recent_activity.
• Parameters: spec_name is required. max_items caps arrays per section (1-500).
• Returns: Workflow configuration, reviewer rules, current active file-state counts, bounded active file evidence, and recent workflow/file activity. Raw file and attachment bytes are never returned, and the procedure does not mutate workflow, files, manifests, or stages.
• Examples:

    CALL airlock.observe.workflow_context('finance');
    CALL airlock.observe.workflow_context(spec_name => 'budget_requests', max_items => 50);

• airlock.observe.license_context(user_id VARCHAR, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only license and billing context packet for one named user as TABLE(PAYLOAD VARIANT). Rows include license_context_manifest, license_context_summary, license_context_seat_policy, license_context_license_holder, license_context_license_spells, license_context_seat_assignment_events, license_context_billing_events, license_context_airlock_assignments, license_context_compiled_access, and license_context_license_activity.
• Parameters: user_id is required. max_items caps arrays per section (1-500).
• Returns: Named-license status, seat policy posture, license spell intervals, seat assignment audit rows, billing emit-log rows, Airlock assignments/access evidence, and license-related activity. It does not emit billing events or mutate licenses, billing, assignments, or access.
• Examples:

    CALL airlock.observe.license_context('CSMITH');
    CALL airlock.observe.license_context(user_id => 'AROBERTS', max_items => 50);

• airlock.observe.billing()
• Description: Read-only Marketplace billing posture: last emit status, pending queue depth, and latest emit run metadata. It does not emit billing events or change license policy.

• airlock.observe.billing_line_items(max_rows FLOAT DEFAULT 5000)
• Description: Read-only current UTC month Marketplace billing line-item candidates and emit status. These rows show which assigned seat-months are pending, emitting, or already logged by Airlock; they are not customer bills or invoices.
• Returns: ASSIGNED_USER, BILL_MONTH_UTC, IDEM_KEY, BILLING_UNITS, LINE_STATUS, EMITTED_AT_UTC, SNOWFLAKE_EMIT_STATUS.

• airlock.observe.billing_events(month_start VARCHAR DEFAULT NULL, month_end VARCHAR DEFAULT NULL, max_rows FLOAT DEFAULT 100000)
• Description: Read-only history of Marketplace billing-event emissions/attempts from Airlock's billing emit log. These rows are not customer bills or invoices.
• Returns: IDEM_KEY, BILL_MONTH_UTC, ASSIGNED_USER, EMITTED_AT_UTC, AMOUNT_USD, BILLING_UNITS, STATUS, RESPONSE_PREVIEW, PLAN_ID, DISCOUNT, OFFER_NAME, plus audit columns.

• airlock.observe.billing_event_context(idem_key VARCHAR, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only context packet for one Marketplace billing-event row as TABLE(PAYLOAD VARIANT). Rows include billing_event_context_manifest, billing_event_context_summary, billing_event_context_billing_event, billing_event_context_license_holder, billing_event_context_license_spells, billing_event_context_seat_assignment_events, billing_event_context_emit_runs, and billing_event_context_license_activity.
• Parameters: idem_key is required. max_items caps arrays per section (1-500).
• Returns: The billing emit-log row, matching license holder/spell evidence, seat assignment audit rows for the billed month, related emit-run log rows, and license-related activity. It does not emit billing events or mutate licenses, billing, assignments, or access.
• Examples:

    CALL airlock.observe.billing_event_context('2026-06|CSMITH');
    CALL airlock.observe.billing_event_context(idem_key => '<idem_key>', max_items => 50);

• airlock.observe.billing_run_context(run_id VARCHAR, max_items FLOAT DEFAULT 200)
• Description: Returns a read-only context packet for one Marketplace billing emit run as TABLE(PAYLOAD VARIANT). Rows include billing_run_context_manifest, billing_run_context_summary, billing_run_context_billing_run, billing_run_context_billing_health, billing_run_context_billing_events, billing_run_context_assigned_users, billing_run_context_license_holders, billing_run_context_license_spells, billing_run_context_seat_assignment_events, and billing_run_context_license_activity.
• Parameters: run_id is required. max_items caps arrays per section (1-500).
• Returns: The billing run-log row, global billing health, referenced billing emit-log rows, assigned-user summaries, matching license holder/spell evidence, seat assignment audit rows, and license-related activity. It does not emit billing events or mutate licenses, billing, assignments, or access.
• Examples:

    CALL airlock.observe.billing_run_context('run-uuid');
    CALL airlock.observe.billing_run_context(run_id => '<run_id>', max_items => 50);

• airlock.observe.billing_activity(max_rows FLOAT DEFAULT 400)
• Description: Read-only unified billing activity feed: billing emit run summaries, license policy or seat changes, and per-row seat-month outcomes. Use this for billing audit context without granting admin.emit_billing.
• Returns: ACTIVITY_AT_UTC, ACTIVITY_KIND, ACTOR_USER, SUMMARY, DETAIL_JSON.

Non‑breaking changes allowed in v1:

• Adding new procedures.
• Adding optional parameters with safe defaults that preserve existing behavior.
• Adding new output columns at the end of result sets (callers must be resilient to extra columns).

Breaking changes (must NOT happen in v1):

• Renaming or removing existing procedures.
• Changing parameter names, types, or required/optional semantics.
• Changing the meaning of parameters or return columns such that the same inputs produce incompatible results.

Future breaking changes must be introduced via:

• New procedures with a version suffix (e.g. select_files_v2), or
• New versioned schemas (e.g. airlock_v2.admin, airlock_v2.agent).

The current Airlock compatibility version and supported versions can be queried via:

CALL airlock.admin.api_info();

---

#2. Admin Schema (airlock.admin.*) And Related Observe Reads

Admin command procedures are callable only when the app_admin application role is active. Related read-only governance procedures live in airlock.observe.* and are callable by app_admin and app_observer.

#2.1. Introspection & Help

• airlock.admin.help(procedure_name VARCHAR DEFAULT NULL)
• Description: Returns help text for admin procedures.
• Parameters:
• procedure_name (optional): name of a specific procedure to describe. If NULL, returns an overview.
• Returns: TABLE(LINE VARCHAR) – one line of help text per row.
• Example:

    CALL airlock.admin.help();
    CALL airlock.admin.help('list_roles');

• airlock.admin.api_info()
• Description: Returns compatibility version metadata and procedure status.
• Returns:
• API_VERSION VARCHAR – e.g. 1.0
• SUPPORTED_VERSIONS VARCHAR – comma‑separated list, e.g. 1.0
• PROCEDURE_NAME VARCHAR – logical name within airlock.admin
• CATEGORY VARCHAR – high‑level area: introspection, roles, specs, files, history, events, retention, maintenance, or validation
• STATUS VARCHAR – existing (others reserved for future use)
• NOTES VARCHAR – freeform notes (e.g. admin-only; v1 surface)
• Example:

    CALL airlock.admin.api_info();

• airlock.admin.enable_mcp_server(interface_name VARCHAR, warehouse_name VARCHAR, validate_only BOOLEAN DEFAULT FALSE)
• Description: Creates one fixed, read-only Snowflake-managed MCP server over the Airlock agent or observer allowlist. If it already exists, Airlock verifies the fixed application-role grants without replacing the endpoint. Disable then enable to change the warehouse or refresh the specification. This remains opt-in so consumer feature-policy restrictions cannot block app installation.
• Parameters: interface_name is agent or observer; warehouse_name is required and must be a conventional unquoted Snowflake identifier because owner-rights Native App procedures do not reliably inherit the caller's current warehouse; validate_only checks the fixed plan without DDL.
• Returns: TABLE(STATUS, CODE, MESSAGE, ISSUES). Stable codes include MCP_SERVER_VALID, MCP_SERVER_ENABLED, MCP_SERVER_BLOCKED, MCP_WAREHOUSE_REQUIRED, and MCP_WAREHOUSE_INVALID.
• Examples:

    CALL airlock.admin.enable_mcp_server('agent', 'AGENT_WH', TRUE);
    CALL airlock.admin.enable_mcp_server('agent', 'AGENT_WH');
    CALL airlock.admin.enable_mcp_server('observer', 'OBSERVER_WH');

• airlock.admin.disable_mcp_server(interface_name VARCHAR, validate_only BOOLEAN DEFAULT FALSE)
• Description: Drops the fixed agent or observer MCP object without changing the underlying stored procedures, procedure grants, or governed state.
• Returns: TABLE(STATUS, CODE, MESSAGE, ISSUES) with stable MCP_SERVER_DISABLE_VALID, MCP_SERVER_DISABLED, or MCP_SERVER_DISABLE_FAILED codes.
• Example:

    CALL airlock.admin.disable_mcp_server('agent');

#2.1.1. Named license operations

• airlock.observe.licenses([include_inactive BOOLEAN])
• Description: Lists named license holder rows and policy (same seat_ceiling / auto_assign_enabled on every row). Default: active only (is_active = TRUE). Pass include_inactive TRUE to include deactivated rows (historic usernames, status inactive). FIRST_LICENSED_AT_UTC is immutable after first assignment (proration anchor). LAST_LICENSED_AT_UTC is spell start (activated_at_utc).
• Returns: TABLE(ASSIGNED_USER, STATUS, FIRST_LICENSED_AT_UTC, LAST_LICENSED_AT_UTC, SEAT_CEILING, AUTO_ASSIGN_ENABLED)
• Example:

    CALL airlock.observe.licenses();
    CALL airlock.observe.licenses(TRUE);

• airlock.observe.license_policy()
• Description: Read named-license state aligned with set_license_ceiling: one row with seat_ceiling, auto_assign_enabled, SEATS_ASSIGNED, and SEATS_FREE. Use set_license_auto_assign(BOOLEAN) to change auto-assign. Use list_licenses() for per-user timestamps; use list_licenses(TRUE) to include inactive holders.
• Returns: TABLE(SEAT_CEILING, AUTO_ASSIGN_ENABLED, SEATS_ASSIGNED, SEATS_FREE)
• Example:

    CALL airlock.observe.license_policy();

• airlock.admin.set_license_ceiling(new_ceiling NUMBER)
• Description: Sets the finite license ceiling (1 to 100). Decrease is blocked if active assignments exceed the new ceiling.
• Returns: TABLE(STATUS, CODE, MESSAGE, ISSUES). Branch on stable CODE values such as SEAT_CEILING_SET, SEAT_CEILING_MAX, or ACTIVE_LICENSES_EXCEED_CEILING; ISSUES is empty on success.
• Example:

    CALL airlock.admin.set_license_ceiling(10);

• airlock.admin.assign_license(user_name VARCHAR)
• Description: Activates a named license for the user (license_holders). Fails if capacity is full.
• Returns: TABLE(STATUS, CODE, MESSAGE, ISSUES). Branch on stable CODE values such as LICENSE_ASSIGNED, LICENSE_ALREADY_ASSIGNED, or NO_LICENSE_CAPACITY; ISSUES is empty on success.
• Example:

    CALL airlock.admin.assign_license('jane.doe');

• airlock.admin.unassign_license(user_name VARCHAR)
• Description: Deactivates the named license for that user (is_active = FALSE; row and first_licensed_at retained).
• Returns: TABLE(STATUS, CODE, MESSAGE, ISSUES). Branch on stable CODE values such as LICENSE_UNASSIGNED, USER_NAME_REQUIRED, or LICENSE_NOT_ACTIVE; ISSUES is empty on success.
• Example:

    CALL airlock.admin.unassign_license('CSMITH');

• airlock.admin.set_license_auto_assign(enabled BOOLEAN)
• Description: Enables or disables auto-assignment for gated agent.* and Streamlit seat checks.
• Returns: TABLE(STATUS, CODE, MESSAGE, ISSUES). Branch on stable CODE values such as AUTO_ASSIGN_SET or ENABLED_REQUIRED; ISSUES is empty on success.
• Example:

    CALL airlock.admin.set_license_auto_assign(TRUE);

• airlock.admin.emit_billing(max_batches FLOAT DEFAULT 1, trigger_kind VARCHAR DEFAULT 'manual')
• Description: Sends unbilled named-seat month rows to Snowflake Marketplace billing using SYSTEM$CREATE_BILLING_EVENTS. Candidates come from license spell overlap with UTC calendar months plus seat-assignment events, minus successful or claimed rows in core.billing_emit_log. Read-only billing posture, line items, events, run context, and activity are exposed through airlock.observe.billing*.
• Parameters: max_batches limits emit batches for one call; trigger_kind identifies the source (manual, scheduled_task, or another operator-defined label).
• Returns: TABLE(STATUS, CODE, MESSAGE, EVENTS_EMITTED, ISSUES). Branch on stable CODE values such as BILLING_EMIT_SENT, BILLING_EMIT_NO_PENDING, or BILLING_EMIT_FAILED; ISSUES is empty on success.
• Examples:

    CALL airlock.admin.emit_billing();
    CALL airlock.admin.emit_billing(3);
    CALL airlock.admin.emit_billing(1, 'scheduled_task');
    CALL airlock.observe.billing_activity();

#2.2. Roles & Assignments

• airlock.observe.roles(username VARCHAR DEFAULT NULL)
• Description: Lists all in‑app roles, optionally filtered by user.
• Parameters:
• username (optional): return roles assigned to this user; if NULL, return all roles.
• Returns:
• ROLE_NAME VARCHAR
• DESCRIPTION VARCHAR
• MANAGED_BY_ROLE VARCHAR
• IS_SPEC_ADMIN BOOLEAN
• IS_LOCKED BOOLEAN
• Example:

    CALL airlock.observe.roles();
    CALL airlock.observe.roles('jane.doe');

• airlock.observe.role(role_name VARCHAR)
• Description: Returns a canonical descriptor for a single in‑app role (round‑trippable for create/alter).
• Parameters:
• role_name (required): in‑app role name to describe.
• Returns:
• ROLE_NAME VARCHAR
• DESCRIPTION VARCHAR
• MANAGED_BY_ROLE VARCHAR
• IS_SPEC_ADMIN BOOLEAN
• IS_LOCKED BOOLEAN
• Example:

    CALL airlock.observe.role('budget_analyst');

• airlock.admin.create_roles(role_descriptors VARIANT, validate_only BOOLEAN DEFAULT FALSE)
• Description: Creates one or more in‑app roles from an array of role_descriptor objects. For a single role, pass a one-element array. Handles dependency resolution automatically: roles with managed_by_role dependencies are created in topological order (parents before children) to respect FK constraints.
• Parameters:
• role_descriptors (required): array of role descriptor objects, each with role_name (required); optional: description, managed_by_role (role name or PK; must not be app_admin—every role is managed by app_admin—or global_observer; omit for top-level roles), is_spec_admin, is_locked. The case-insensitive role name global_observer is system-reserved.
• validate_only (optional, default FALSE): when TRUE, validates dependency order and returns creation plan without inserting.
• Returns:
• One row per role: ROLE_NAME VARCHAR, STATUS VARCHAR, IS_CREATED BOOLEAN, VALIDATION VARIANT.
• VALIDATION is an object (not a bare boolean): outcome (created \| noop \| validate_only \| error), message, and issues (array of { code, message, severity }, same general shape as load_data ISSUES). Idempotent re-create: outcome: "noop", IS_CREATED: FALSE, and an informational issue such as ROLE_ALREADY_EXISTS. With validate_only, dependency_level is included; issues is typically empty.
• Behavior:
• Validates that all managed_by_role references are resolvable (exist in database or in the same create request).
• Detects circular dependencies and returns validation errors.
• Creates roles in dependency order using topological sort (Kahn's algorithm).
• Example:

    -- Single role
    CALL airlock.admin.create_roles([PARSE_JSON('{"role_name": "analyst", "description": "Analyst role"}')]);
    -- Multiple roles (with dependency): one PARSE_JSON per element
    CALL airlock.admin.create_roles([
      PARSE_JSON('{"role_name": "finadmin", "description": "Financial admin", "is_spec_admin": true}'),
      PARSE_JSON('{"role_name": "budget_analyst", "description": "Budget analyst", "managed_by_role": "finadmin"}')
    ]);

• airlock.admin.alter_role(role_name VARCHAR, role_descriptor VARIANT, validate_only BOOLEAN DEFAULT FALSE)
• Description: Alters an existing in‑app role. role_descriptor.role_name must match role_name if set. The reserved global_observer system role cannot be altered.
• Parameters:
• role_name (required): role to alter.
• role_descriptor (required): optional fields description, managed_by_role (must not be app_admin; omit for top-level), is_spec_admin, is_locked.
• validate_only (optional, default FALSE).
• Returns:
• ROLE_NAME VARCHAR, STATUS VARCHAR, IS_UPDATED BOOLEAN, VALIDATION VARIANT
• Example:

    CALL airlock.admin.alter_role('analyst', PARSE_JSON('{"description": "Updated"}'));

• airlock.admin.drop_role(role_name VARCHAR, force BOOLEAN DEFAULT FALSE)
• Description: Drops an in‑app role. Role must have no assignments and no child roles. When force is FALSE, does not delete if role is locked. The reserved global_observer system role cannot be dropped, including with force => TRUE.
• Parameters:
• role_name (required).
• force (optional, default FALSE): when TRUE, allows drop when role is locked (dependents must still be zero).
• Returns:
• ROLE_NAME VARCHAR, STATUS VARCHAR, CODE VARCHAR, MESSAGE VARCHAR, IS_DROPPED BOOLEAN, ISSUES VARIANT, DETAILS VARIANT
• Branch on STATUS and stable CODE; ISSUES is an array of {code, message, severity} and is empty when STATUS is ok.
• Example:

    CALL airlock.admin.drop_role('analyst');
    CALL airlock.admin.drop_role('analyst', TRUE);

• airlock.observe.assignments(username VARCHAR DEFAULT NULL, role_name VARCHAR DEFAULT NULL)
• Description: Lists in‑app role assignments.
• Parameters:
• username (optional): filter by user identifier.
• rolename (optional): filter by role name.
• Returns:
• ASSIGNMENT_NAME VARCHAR
• DESCRIPTION VARCHAR
• USER_ID VARCHAR
• ASSIGNED_ROLE VARCHAR
• EFFECTIVE_FROM TIMESTAMP_LTZ
• EFFECTIVE_TO TIMESTAMP_LTZ
• IS_LOCKED BOOLEAN
• Example:

    CALL airlock.observe.assignments();
    CALL airlock.observe.assignments('jane.doe');
    CALL airlock.observe.assignments(role_name => 'budget_analyst');

• airlock.observe.observers(user_id VARCHAR DEFAULT NULL, observer_role VARCHAR DEFAULT NULL, spec_name VARCHAR DEFAULT NULL, include_inactive BOOLEAN DEFAULT FALSE)
• Description: Returns an account-wide, flattened observer inventory. Each scoped row binds a user assignment and effective observer role to a spec plus optional path/workflow restrictions. global_observer assignments appear as explicit global rows with no spec. Requires app_admin or an assignment to global_observer.
• Parameters: Optional user_id, effective observer_role, and spec_name filters. include_inactive => TRUE includes expired/future assignments and observer grants on unpublished or archived specs.
• Returns: USER_ID, ASSIGNMENT_NAME, ASSIGNED_ROLE, OBSERVER_ROLE, SPEC_NAME, PATH_FILTER, WORKFLOW_STATE_FILTER, EFFECTIVE_FROM, EFFECTIVE_TO, IS_CURRENT, IS_GLOBAL, HAS_ACTIVE_LICENSE, HAS_AGENT_ACCESS, and BILLING_POSTURE.
• Billing posture:
• free_observer_only: no active named seat and no current agent access.
• active_seat_observer_only: only observer access is present, but an existing active seat remains billable until explicitly unassigned.
• active_agent_seat: the identity has agent access and an active seat.
• seat_required_for_agent_use: the identity has agent access but has not yet claimed an active seat.
• Examples:

    CALL airlock.observe.observers();
    CALL airlock.observe.observers(observer_role => 'finance_observer');
    CALL airlock.observe.observers(user_id => 'CSMITH', include_inactive => TRUE);

• airlock.observe.assignment(assignment_name VARCHAR)
• Description: Returns a canonical descriptor for a single in‑app assignment (round‑trippable for alter). ASSIGNED_ROLE is the role name.
• Parameters: assignment_name (required).
• Returns: Same columns as list (single row or empty).
• Example:

    CALL airlock.observe.assignment('jane.analyst');

• airlock.admin.create_assignments(assignment_descriptors VARIANT, validate_only BOOLEAN DEFAULT FALSE)
• Description: Creates one or more in‑app role assignments from an array of assignment_descriptor objects. For a single assignment, pass a one-element array. Validates that all assigned_role references exist before creating any assignments. Assignments don't have dependencies on each other, so all are created in parallel after validation. Successful create, alter, and drop operations write assignment identity, effective role, changed fields, and before/after evidence into admin activity context so deleted assignments remain auditable.
• Parameters:
• assignment_descriptors (required): array of assignment descriptor objects, each with assignment_name, user_id, assigned_role (required); optional: description, effective_from, effective_to, is_locked. assigned_role can be role name or PK.
• validate_only (optional, default FALSE): when TRUE, validates all assignments and returns validation results without inserting.
• Returns:
• One row per assignment: ASSIGNMENT_NAME VARCHAR, STATUS VARCHAR, IS_CREATED BOOLEAN, VALIDATION VARIANT
• Behavior:
• Validates that all assigned_role references exist in core.in_app_roles.
• Checks for duplicate assignment_name values (both in input and existing).
• Checks for duplicate (user_id, assigned_role) combinations (both in input and existing).
• Creates all assignments after validation passes.
• Example:

    -- Single assignment: one PARSE_JSON object inside an array literal (easy to read and edit).
    CALL airlock.admin.create_assignments([
      PARSE_JSON('{"assignment_name": "jane.analyst", "user_id": "JANE", "assigned_role": "analyst"}')
    ]);

    -- Multiple assignments: one PARSE_JSON per element.
    CALL airlock.admin.create_assignments([
      PARSE_JSON('{"assignment_name": "alice_finadmin", "user_id": "alice@example.com", "assigned_role": "finadmin"}'),
      PARSE_JSON('{"assignment_name": "bob_analyst", "user_id": "bob@example.com", "assigned_role": "budget_analyst"}')
    ]);

    -- When a field must be a SQL expression (e.g. CURRENT_USER(), timestamps), use ARRAY_CONSTRUCT(OBJECT_CONSTRUCT(...)) instead.

• airlock.admin.alter_assignment(assignment_name VARCHAR, assignment_descriptor VARIANT, validate_only BOOLEAN DEFAULT FALSE)
• Description: Alters an existing assignment (description, effective_from, effective_to, is_locked only).
• Parameters:
• assignment_name (required).
• assignment_descriptor (required): optional fields description, effective_from, effective_to, is_locked.
• validate_only (optional, default FALSE).
• Returns:
• ASSIGNMENT_NAME VARCHAR, STATUS VARCHAR, IS_UPDATED BOOLEAN, VALIDATION VARIANT
• Example:

    CALL airlock.admin.alter_assignment('jane.analyst', PARSE_JSON('{"description": "Updated", "effective_to": "2025-12-31 23:59:59"}'));

• airlock.admin.drop_assignment(assignment_name VARCHAR, force BOOLEAN DEFAULT FALSE)
• Description: Drops an in‑app role assignment. When force is FALSE, does not delete if assignment is locked.
• Parameters:
• assignment_name (required).
• force (optional, default FALSE): when TRUE, allows drop when assignment is locked.
• Returns:
• ASSIGNMENT_NAME VARCHAR, STATUS VARCHAR, CODE VARCHAR, MESSAGE VARCHAR, IS_DROPPED BOOLEAN, ISSUES VARIANT, DETAILS VARIANT
• Branch on STATUS and stable CODE; ISSUES is an array of {code, message, severity} and is empty when STATUS is ok.
• Example:

    CALL airlock.admin.drop_assignment('jane.analyst');
    CALL airlock.admin.drop_assignment('jane.analyst', TRUE);

#2.3. Events

• airlock.observe.activity(username VARCHAR DEFAULT NULL, event_types VARCHAR DEFAULT NULL, in_app_role VARCHAR DEFAULT NULL, event_description VARCHAR DEFAULT NULL, start_time TIMESTAMP_LTZ DEFAULT NULL, end_time TIMESTAMP_LTZ DEFAULT NULL, limit_rows INTEGER DEFAULT 200)
• Description: Fetches application activity events from the internal core.events table with optional filters. app_observer callers receive only events within their assigned spec/path scope; workflow-filtered observer grants use observe.files, observe.file_state, and observe.work for state-safe polling. app_admin and assigned global_observer callers receive account-wide activity.
• Parameters:
• username (optional): filter by username (case-insensitive exact match).
• event_types (optional): comma-separated list of event types (e.g. USER_LOGIN, RECORD_CREATE, FILE_UPLOAD). See event types in app.
• in_app_role (optional): filter by Airlock role name (case-insensitive exact match).
• event_description (optional): partial match on event description (case-insensitive).
• start_time (optional): earliest event_timestamp (inclusive).
• end_time (optional): latest event_timestamp (inclusive).
• limit_rows (optional, default 200): maximum rows to return; capped at 1000.
• Returns:
• EVENT_TIMESTAMP TIMESTAMP_LTZ
• EVENT_TYPE VARCHAR
• EVENT_DESCRIPTION VARCHAR
• USERNAME VARCHAR
• IN_APP_ROLE VARCHAR
• OBJECT_NAME VARCHAR – table or spec name when applicable
• SPEC_NAME VARCHAR – spec name when applicable (e.g. file events)
• EVENT_CONTEXT VARIANT
• EVENT_ID VARCHAR - trailing stable event identity; pass to observe.activity_context
• Example:

    CALL airlock.observe.activity();
    CALL airlock.observe.activity('jane.doe');
    CALL airlock.observe.activity(
      event_types => 'USER_LOGIN,RECORD_CREATE',
      start_time => '2024-01-01'::TIMESTAMP_LTZ,
      end_time => CURRENT_TIMESTAMP(),
      limit_rows => 500
    );
    CALL airlock.observe.activity(in_app_role => 'spec_admin', limit_rows => 100);

• airlock.observe.admin_activity(procedure_name VARCHAR DEFAULT NULL, action_family VARCHAR DEFAULT NULL, username VARCHAR DEFAULT NULL, event_types VARCHAR DEFAULT NULL, start_time TIMESTAMP_LTZ DEFAULT NULL, end_time TIMESTAMP_LTZ DEFAULT NULL, limit_rows INTEGER DEFAULT 200)
• Description: Fetches admin mutation and maintenance activity from the append-only core.ADMIN_ACTIVITY journal, plus pre-journal or fallback admin events from core.events. Use this for audit/compliance questions such as "what did admins do?", "which billing actions ran?", or "which role/spec maintenance procedures changed governance?"
• Parameters:
• procedure_name (optional): short or schema-qualified admin procedure name, e.g. load_data or admin.load_data.
• action_family (optional): stable grouping such as roles, assignments, specs, files, attachments, workflow, billing, licenses, events, references, retention, expectations, okf, or maintenance.
• username (optional): filter by event username.
• event_types (optional): comma-separated list of event types.
• start_time, end_time (optional): inclusive activity timestamp range.
• limit_rows (optional, default 200): maximum rows to return; capped at 1000.
• Returns:
• EVENT_TIMESTAMP TIMESTAMP_LTZ
• EVENT_TYPE VARCHAR
• EVENT_DESCRIPTION VARCHAR
• USERNAME VARCHAR
• IN_APP_ROLE VARCHAR
• PROCEDURE_NAME VARCHAR
• ACTION_FAMILY VARCHAR
• OBJECT_NAME VARCHAR
• SPEC_NAME VARCHAR
• EVENT_CONTEXT VARIANT
• EVENT_ID VARCHAR - trailing stable activity identity
• EVIDENCE_SOURCE VARCHAR - trailing provenance: journal for canonical append-only evidence; legacy_event for unmatched pre-journal or fallback evidence
• Examples:

    CALL airlock.observe.admin_activity();
    CALL airlock.observe.admin_activity(procedure_name => 'admin.create_assignments');
    CALL airlock.observe.admin_activity(action_family => 'billing', event_types => 'BILLING_EMIT');

• airlock.observe.spec_admin_activity(spec_name VARCHAR, event_types VARCHAR DEFAULT NULL, start_time TIMESTAMP_LTZ DEFAULT NULL, end_time TIMESTAMP_LTZ DEFAULT NULL, limit_rows INTEGER DEFAULT 200)
• Description: Fetches admin spec-definition events for one spec_name, limited to structured admin events whose action_family is specs. Use this for questions like "who changed this spec?", "when was the column config updated?", or "which version id should I inspect?" For new alter_spec events, CHANGED_SECTIONS lists changed top-level spec_config sections and CHANGED_FIELDS lists changed core core.specs fields.
• Parameters:
• spec_name (required): logical Airlock spec name.
• event_types (optional): comma-separated event types such as RECORD_CREATE, RECORD_UPDATE, or RECORD_DELETE.
• start_time, end_time (optional): inclusive activity timestamp range.
• limit_rows (optional, default 200): maximum rows to return; capped at 1000.
• Returns:
• EVENT_TIMESTAMP TIMESTAMP_LTZ
• EVENT_TYPE VARCHAR
• EVENT_DESCRIPTION VARCHAR
• USERNAME VARCHAR
• PROCEDURE_NAME VARCHAR
• SPEC_EVENT VARCHAR
• SPEC_NAME VARCHAR
• CURRENT_CREATED_AT TIMESTAMP_LTZ
• CURRENT_CREATED_BY VARCHAR
• CURRENT_MODIFIED_AT TIMESTAMP_LTZ
• CURRENT_MODIFIED_BY VARCHAR
• VERSION_ID VARCHAR
• VERSION_PATH VARCHAR
• META_PATH VARCHAR
• SPEC_CONFIG_HASH VARCHAR
• CHANGED_SECTIONS VARIANT
• CHANGED_FIELDS VARIANT
• EVENT_CONTEXT VARIANT
• EVENT_ID VARCHAR - trailing stable activity identity
• EVIDENCE_SOURCE VARCHAR - trailing provenance: journal or legacy_event
• Examples:

    CALL airlock.observe.spec_admin_activity('budget_requests');
    CALL airlock.observe.spec_admin_activity('budget_requests', event_types => 'RECORD_UPDATE');
    CALL airlock.observe.spec_version('budget_requests', '<VERSION_ID_FROM_ACTIVITY>');

#2.4. Specs & Files

• airlock.observe.specs(in_app_role VARCHAR DEFAULT NULL)
• Description: Lists specs visible through the caller's active Airlock role assignments; app_admin and assigned global_observer callers see the account-wide inventory. Supports an optional Airlock role lens.
• Parameters:
• in_app_role (optional): restrict results to access through this Airlock role; NULL uses all assigned roles, or all specs for account-wide observers.
• Returns:
• SPEC_NAME VARCHAR
• SPEC_ALIAS VARCHAR
• DESCRIPTION VARCHAR
• OWNER_ROLE VARCHAR
• ACCESS_TYPE VARCHAR – owner, guest, observer, or admin
• GUEST_ROLE_NAME VARCHAR
• RESTRICTED_PATH VARCHAR
• IS_MULTI_ROLE BOOLEAN
• Example:

    CALL airlock.observe.specs();
    CALL airlock.observe.specs('budget_analyst');

• airlock.observe.spec(spec_name VARCHAR)
• Description: Returns a canonical descriptor for a single spec, suitable for programmatic use.
• Parameters:
• spec_name (required): logical spec identifier.
• Returns:
• SPEC_NAME VARCHAR
• SPEC_ALIAS VARCHAR
• DESCRIPTION VARCHAR
• OWNER_ROLE VARCHAR – human‑readable role name
• SPEC_CONFIG VARIANT – full configuration object
• IS_LOCKED BOOLEAN
• IS_ARCHIVED BOOLEAN
• IS_MULTI_ROLE BOOLEAN
• IS_PUBLISHED BOOLEAN
• IS_REFERENCE_SPEC BOOLEAN
• ACTIVE_SPEC_VERSION_ID VARCHAR – immutable complete revision currently active.
• ACTIVE_DATA_VERSION_ID VARCHAR – immutable data contract used for new validation.
• ACTIVE_SPEC_CONFIG_HASH VARCHAR – canonical hash of the active complete revision.
• MIGRATION_SOURCE_SPEC_VERSION_ID VARCHAR – optional read-only source revision being drained.
• MIGRATION_SOURCE_DATA_VERSION_ID VARCHAR – optional second operational data version.
• ACTIVE_MIGRATION_ID VARCHAR – migration that owns the temporary two-version window.
• ACCESS_SUMMARY VARIANT – compact explanation of owner, guest, reviewer, observer, attachment, and reference RLS policy
• Example:

    CALL airlock.observe.spec('budget_requests');

• airlock.observe.spec_state(spec_name VARCHAR)
• Description: Returns compact, authorization-scoped change tokens for one active spec without returning governed file rows or attachment bytes. Use this for observer polling and retrieve larger observe payloads only when a token changes.
• Returns: SPEC_NAME, immutable active spec/data version identifiers and config hash, CONTRACT_TOKEN, authorization-scoped ACCESS_SCOPE_TOKEN, DATASET_TOKEN, combined STATE_TOKEN, ACTIVE_FILE_COUNT, and LAST_CHANGED_AT.
• Token semantics: CONTRACT_TOKEN changes when the active spec contract changes. DATASET_TOKEN changes when the visible active file set, a visible file's mutable UUID, or a spec-level attachment changes. STATE_TOKEN combines those signals with spec metadata changes. Account-wide observers also track the spec revision epoch; scoped observers intentionally ignore epoch changes caused only by hidden paths. A dropped spec or revoked observer grant returns no row; use governance activity for durable evidence. Failures after authorization still surface as procedure errors.
• Example:

    CALL airlock.observe.spec_state('budget_requests');

• airlock.observe.spec_states(spec_names ARRAY)
• Description: Returns the same state-token rows as observe.spec_state for up to 100 unique, non-empty names while sharing observer identity, role expansion, and compiled-access work. Use it when one observer polls several known specs.
• Authorization: Scoped observers receive only specs allowed by their assignments and per-spec observer policy. global_observer and app_admin receive account-wide state. Input order is preserved after case-insensitive deduplication; missing and inaccessible specs are omitted without disclosing which condition applied.
• Returns: Zero or one row per accessible requested spec, with the same columns and token semantics as observe.spec_state.
• Example:

    CALL airlock.observe.spec_states(ARRAY_CONSTRUCT('budget_requests', 'finance'));

• airlock.observe.spec_compatibility(spec_name VARCHAR, proposed_spec_config VARIANT)
• Description: Performs a read-only structured comparison of a proposed canonical config with the active spec. It reports data-contract changes, directional compatibility, affected active-file count, and whether admin.alter_spec would require a governed migration.
• Parameters: spec_name and the complete proposed spec_config are required.
• Returns: SPEC_NAME, STATUS, CODE, MESSAGE, SOURCE_SPEC_VERSION_ID, SOURCE_DATA_VERSION_ID, PROPOSED_SPEC_CONFIG_HASH, ACTIVE_FILE_COUNT, CONTRACT_RELATION, DATA_CONTRACT_CHANGED, MIGRATION_REQUIRED, and DETAILS.
• Example:

    CALL airlock.observe.spec_compatibility('budget_requests', :proposed_spec_config);

• airlock.observe.spec_config(spec_name VARCHAR)
• Description: Returns the current spec configuration as VARIANT (native JSON object in the Snowflake UI), aligned with structured columns such as load_data ISSUES. Use for round-trip editing or to pass into alter_spec / validate_spec with allow_existing_spec => TRUE.
• Parameters:
• spec_name (required): logical spec identifier.
• Returns: VARIANT – parsed spec_config object (NULL if the spec does not exist).
• Example:

    CALL airlock.observe.spec_config('budget_requests');
    -- For automation: snow sql -c myconn --format JSON_EXT --silent -q "CALL airlock.observe.spec_config('my_spec');"

• airlock.admin.create_spec(spec_config VARIANT, validate_only BOOLEAN DEFAULT FALSE, reuse_spec BOOLEAN DEFAULT FALSE, from_assigned_spec_template BOOLEAN DEFAULT FALSE)
• Description: Creates a new spec from a canonical spec_config payload. This is the primary CREATE procedure for specs.
• Parameters:
• spec_config (required): canonical spec configuration object (same structure used by airlock.admin.validate_spec).
• validate_only (optional, default FALSE): when TRUE, runs validation and returns the result without inserting into core.specs.
• reuse_spec (optional, default FALSE): when TRUE, allows spec_name to already exist as a directory in AIRLOCK_STAGE (e.g. after dropping the spec from core.specs); validation is called with reuse_spec => TRUE.
• from_assigned_spec_template (optional, default FALSE): set TRUE only by agent.create_spec_from_template; allows delegated owner rules when template_config.template_rules.forced_owner_role matches the resolved owner.
• Returns:
• SPEC_NAME VARCHAR – canonical spec name that would be (or was) created.
• STATUS VARCHAR – ok or error.
• IS_CREATED BOOLEAN – TRUE only when a new row was successfully inserted.
• VALIDATION VARIANT – parsed JSON result from validate_spec (including valid and any invalid tabs/messages).
• Example:

    CALL airlock.admin.create_spec(PARSE_JSON('{"core_config": {"spec_name": "my_spec", "owner_role": "analyst"}}'), TRUE);
    CALL airlock.admin.create_spec(:spec_config_var);

• airlock.admin.import_dbt_spec_definition(dbt_payload VARIANT, overrides VARIANT DEFAULT NULL)
• Description: Imports a dbt spec definition as a pending Airlock spec. Produces a draft spec_config per the dbt→Airlock mapping and writes a row to core.specs with import_metadata (status=pending, pending_fields, pending_notes, source_payload). dbt models are not supported (models are DAG-based; shape comes from SQL + ancestors). Complete the spec via airlock.admin.alter_spec with a full config (and optionally remove import_metadata).
• Parameters:
• dbt_payload (required): Parsed dbt specs YAML/JSON. Accepts: { "name", "description", "columns": [...] }, or { "specs": [ {...} ] } (first spec used).
• overrides (optional): { "owner_role", "spec_alias", "description", "is_published", "is_archived", "is_multi_role", "current_mode" }. If owner_role is omitted, app_admin is used and core_config.owner_role is added to pending_fields.
• Returns:
• SPEC_NAME, STATUS, IS_CREATED, DRAFT_CONFIG VARIANT, PENDING_FIELDS VARIANT, PENDING_NOTES VARIANT, VALIDATION VARIANT.
• Example:

    CALL airlock.admin.import_dbt_spec_definition(
      PARSE_JSON('{"name":"my_spec","columns":[{"name":"id","tests":["not_null"]}]}'),
      PARSE_JSON('{"owner_role":"app_admin"}')
    );

• airlock.observe.spec_import(spec_name VARCHAR)
• Description: Returns the same descriptor as observe.spec plus IMPORT_METADATA VARIANT when the spec was created via import_dbt_spec_definition. Use to inspect pending_fields and pending_notes before completing with alter_spec.
• Parameters: spec_name (required).
• Returns: Same as observe.spec plus IMPORT_METADATA VARIANT (nullable).

• airlock.observe.pending_specs()
• Description: Lists specs in pending import state (import_metadata.status = pending).
• Returns: SPEC_NAME, SPEC_ALIAS, IMPORTED_AT, PENDING_FIELDS, PENDING_NOTES.

• airlock.admin.clone_spec_from_template(template_name VARCHAR, new_spec_name VARCHAR, spec_alias VARCHAR DEFAULT NULL, spec_config_overrides VARIANT DEFAULT NULL)
• Description: Clones a new spec from a saved spec template. This calls airlock.admin.create_spec with a cloned spec_config.
• Parameters:
• template_name (required): spec template to clone.
• new_spec_name (required): new spec name for the clone (canonicalized).
• spec_alias (optional): optional alias override for the new spec.
• spec_config_overrides (optional): optional overrides for editable fields (dot-path keys).
• Returns:
• SPEC_NAME VARCHAR – canonical spec name that would be (or was) created.
• STATUS VARCHAR – ok, not_found, or error.
• IS_CREATED BOOLEAN – TRUE only when a new row was successfully inserted.
• VALIDATION VARIANT – parsed JSON result from validate_spec (including valid and any invalid tabs/messages).
• Example:

    CALL airlock.admin.clone_spec_from_template(
      'finance_spec_template',
      'fin_budget_requests',
      spec_alias => 'FIN_Budget Requests',
      spec_config_overrides => PARSE_JSON('{"core_config.description": "FY26"}')
    );

• airlock.observe.spec_templates()
• Description: Lists all spec templates.
• Returns:
• TEMPLATE_NAME VARCHAR
• DESCRIPTION VARCHAR
• IS_ACTIVE BOOLEAN
• IS_LOCKED BOOLEAN
• IS_PUBLIC BOOLEAN
• Example:

    CALL airlock.observe.spec_templates();

• airlock.observe.spec_template(template_name VARCHAR)
• Description: Returns a single spec template descriptor.
• Parameters:
• template_name (required): template name.
• Returns:
• TEMPLATE_NAME VARCHAR
• DESCRIPTION VARCHAR
• SPEC_CONFIG VARIANT
• TEMPLATE_RULES VARIANT
• IS_ACTIVE BOOLEAN
• IS_LOCKED BOOLEAN
• IS_PUBLIC BOOLEAN
• Example:

    CALL airlock.observe.spec_template('finance_spec_template');

• airlock.admin.create_spec_template(template_descriptor VARIANT, validate_only BOOLEAN DEFAULT FALSE)
• Description: Creates a new spec template.
• Parameters:
• template_descriptor (required): { template_name, description?, spec_config?, template_rules?, is_locked?, is_active?, is_public? }. is_public: true makes the template available to every licensed app_agent.
• validate_only (optional, default FALSE): when TRUE, validates without inserting.
• Returns:
• TEMPLATE_NAME VARCHAR
• STATUS VARCHAR
• IS_CREATED BOOLEAN
• VALIDATION VARIANT
• Example:

    CALL airlock.admin.create_spec_template(PARSE_JSON('{"template_name": "finance_spec_template"}'));

• airlock.admin.alter_spec_template(template_name VARCHAR, template_descriptor VARIANT, validate_only BOOLEAN DEFAULT FALSE)
• Description: Alters an existing spec template.
• Parameters:
• template_name (required): template name.
• template_descriptor (required): { description?, spec_config?, template_rules?, is_locked?, is_active?, is_public? }.
• validate_only (optional, default FALSE): when TRUE, validates without updating.
• Returns:
• TEMPLATE_NAME VARCHAR
• STATUS VARCHAR
• IS_UPDATED BOOLEAN
• VALIDATION VARIANT
• Example:

    CALL airlock.admin.alter_spec_template('finance_spec_template', PARSE_JSON('{"is_active": false}'));

• airlock.admin.drop_spec_template(template_name VARCHAR, force BOOLEAN DEFAULT FALSE)
• Description: Drops a spec template (blocked if locked or assigned).
• Parameters:
• template_name (required): template name.
• force (optional, default FALSE): when TRUE, allows dropping locked templates.
• Returns:
• TEMPLATE_NAME VARCHAR
• STATUS VARCHAR
• IS_DROPPED BOOLEAN
• MESSAGE VARCHAR
• Example:

    CALL airlock.admin.drop_spec_template('finance_spec_template');

• airlock.observe.spec_template_assignments(template_name VARCHAR DEFAULT NULL)
• Description: Lists spec template assignments.
• Parameters:
• template_name (optional): filter by template name.
• Returns:
• ASSIGNMENT_NAME VARCHAR
• TEMPLATE_NAME VARCHAR
• ASSIGNED_ROLE VARCHAR
• ASSIGNED_ROLE_NAME VARCHAR
• DESCRIPTION VARCHAR
• EFFECTIVE_FROM TIMESTAMP_LTZ
• EFFECTIVE_TO TIMESTAMP_LTZ
• IS_LOCKED BOOLEAN
• Example:

    CALL airlock.observe.spec_template_assignments();

• airlock.admin.create_spec_template_assignment(assignment_descriptor VARIANT, validate_only BOOLEAN DEFAULT FALSE)
• Description: Creates a spec template assignment.
• Parameters:
• assignment_descriptor (required): { assignment_name, template_name, assigned_role, description?, effective_from?, effective_to?, is_locked? }.
• validate_only (optional, default FALSE): when TRUE, validates without inserting.
• Returns:
• ASSIGNMENT_NAME VARCHAR
• STATUS VARCHAR
• IS_CREATED BOOLEAN
• VALIDATION VARIANT
• Example:

    CALL airlock.admin.create_spec_template_assignment(
      PARSE_JSON('{"assignment_name": "fin_template_access", "template_name": "finance_spec_template", "assigned_role": "fin_analyst"}')
    );

• airlock.admin.drop_spec_template_assignment(assignment_name VARCHAR, force BOOLEAN DEFAULT FALSE)
• Description: Drops a spec template assignment.
• Parameters:
• assignment_name (required): assignment name.
• force (optional, default FALSE): when TRUE, allows dropping locked assignments.
• Returns:
• ASSIGNMENT_NAME VARCHAR
• STATUS VARCHAR
• IS_DROPPED BOOLEAN
• MESSAGE VARCHAR
• Example:

    CALL airlock.admin.drop_spec_template_assignment('fin_template_access');

• airlock.admin.alter_spec(spec_name VARCHAR, spec_config VARIANT, validate_only BOOLEAN DEFAULT FALSE)
• Description: Alters an existing spec’s configuration using a canonical spec_config payload. Validation-only calls include structured compatibility analysis. Metadata/governance revisions retain the active data version and remain available during a migration. A data-contract change is rejected with SPEC_MIGRATION_REQUIRED while active files still depend on the current data version, or with SPEC_MIGRATION_ACTIVE until the current migration is retired. Activation is conditional on the version that was analyzed; a concurrent spec change or file load returns SPEC_ACTIVATION_CONFLICT without activating the revision. Also used to complete a pending dbt import: fetch config via observe.spec_import or observe.spec_config, fill required fields, optionally remove or set import_metadata.status to complete, then call alter_spec.
• Parameters:
• spec_name (required): logical spec identifier of the spec to update.
• spec_config (required): canonical spec configuration object (same structure used by airlock.admin.validate_spec). The canonical spec_config.core_config.spec_name must match spec_name.
• validate_only (optional, default FALSE): when TRUE, runs validation and returns the result without updating core.specs.
• Returns:
• SPEC_NAME VARCHAR – canonical spec name that was (or would be) updated.
• STATUS VARCHAR – ok, not_found, or error.
• IS_UPDATED BOOLEAN – TRUE only when an existing row was successfully updated.
• VALIDATION VARIANT – validation result plus compatibility, including active-file impact and migration requirements.
• Example:

    CALL airlock.admin.alter_spec('my_spec', :spec_config_var);
    CALL airlock.admin.alter_spec('my_spec', :spec_config_var, TRUE);

#Spec revisions and governed migration

Airlock separates proposal, proof, approval, activation, bounded execution, and retirement. Approval does not change live behavior. Activation makes the target the sole write version and retains one prior read-only source version while current reads continue to expose the target shape.

AIRLOCK_DATA.ACTIVE.V_<SPEC> and AIRLOCK_DATA.HIST.V_<SPEC> read only rows with a valid attestation for the current data version. During an active migration, the history view also exposes source-version rows through the same approved target-shape transform and suppresses source rows that already have a physical successor. After retirement, older source-native artifacts remain inspectable through observe.file_versions, observe.file_validations, and exact governed file access; Airlock does not reinterpret them with a newer schema merely to keep them in a convenience view.

• airlock.admin.create_spec_revision(spec_name VARCHAR, spec_config VARIANT)
• Writes an immutable proposed revision whose metadata pins the active parent spec/data versions and version epoch.
• Assigns server-managed IDs to new columns and returns static compatibility.
• Does not update core.specs or spec_configs/<spec>/latest.json.

• airlock.admin.create_spec_migration(spec_name VARCHAR, source_spec_version_id VARCHAR, target_spec_version_id VARCHAR, transform_config VARIANT, reversibility VARCHAR DEFAULT 'source_preserved')
• Creates one durable draft migration from the active revision to its immutable child proposal.
• Accepts only bounded operations over immutable column_id values: preserve, rename, drop, add_nullable, add_constant, safe_cast, coalesce, and map_values.
• Rejects arbitrary SQL, Python, prompt text, unknown fields, incomplete column mappings, and revision/hash drift.
• source_preserved protects transformed source bytes from ordinary retention purge so the declared rollback evidence remains available. forward_only leaves inactive source bytes under the normal retention policy.
• Example:

    CALL airlock.admin.create_spec_migration(
      'budget_requests',
      '20260701010101',
      '20260702020202',
      PARSE_JSON('{
        "operations": [{
          "op": "safe_cast",
          "source_column_id": "col_222222222222222222222222",
          "target_column_id": "col_222222222222222222222222",
          "on_failure": "reject"
        }]
      }'),
      'source_preserved'
    );

• airlock.admin.validate_spec_migration(migration_id VARCHAR, preview_limit INTEGER DEFAULT 25)
• Pins every active file to an exact source validation attestation and content hash, then runs one set-based, read-only proof over all source rows.
• Returns source counts, a source-set fingerprint, aggregate failures, and up to 100 transformed preview rows.
• Rechecks the source-set fingerprint before recording validated.
• Returns MIGRATION_VALIDATOR_UNSUPPORTED for changed rule families that do not yet have a set-based migration validator instead of claiming safety.
• The current proof engine supports Airlock's tabular CSV storage contract; specs using non-tabular payload adapters such as okf_knowledge_bundle fail closed with MIGRATION_VALIDATOR_UNSUPPORTED.

• airlock.admin.approve_spec_migration(migration_id VARCHAR)
• Approves only a validated migration whose active source revision and exact source-set fingerprint still match the proof.
• Records approval intent only; it does not activate the target revision.

• airlock.admin.activate_spec_migration(migration_id VARCHAR)
• Rechecks immutable source/target revisions, transform identity, exact source set, and current staged content before activation.
• Resolves core_config.owner_role to its durable Airlock role ID before writing the target spec, so custom-owned specs activate through the same owner contract as create_spec and alter_spec.
• Makes the target data version current for new validation and writes, pins the prior version as the one read-only migration source, and rebuilds the active view as a target-shaped union.
• Atomically invalidates the old compiled access version, rebuilds target views and any materialized table, then republishes the compiled access index. Stale views/tables are removed instead of serving old-shape data.
• If a post-commit repair fails, the migration remains durably running and returns SPEC_MIGRATION_ACTIVATED_WITH_REPAIR_REQUIRED with stable issues. Failed access-index repair leaves agent access disabled until admin.rebuild_access_index(TRUE) succeeds; failed view/table repair uses admin.rebuild_spec_view(...) or admin.rebuild_spec_table(...) before rebuilding access.
• Rejects activation when a spec already has two operational versions.

• airlock.admin.run_spec_migration(migration_id VARCHAR, batch_size INTEGER DEFAULT 25)
• Drains 1 to 100 planned or retryable file items per call without invoking a stored procedure once per row.
• Atomically claims each bounded batch before reading or copying staged bytes; concurrent runners cannot execute the same live item, and abandoned claims become retryable after a bounded stale-claim interval.
• Records a target-version attestation against the existing manifest when the approved transform is byte-compatible. A column rename is not zero-copy because the CSV header must change. Other transforms use one set-based COPY INTO per file and atomically activates a target-version successor with exact source manifest, validation, content-hash, workflow, and source-reference lineage.
• Preserves original validation provenance, uses append-only valid attestations to identify the effective source contract across sequential migrations, verifies staged input and output digests, and leaves failed items retryable without partially replacing the active source file.
• Returns ZERO_COPY_COUNT, TRANSFORMED_COUNT, FAILED_COUNT, and REMAINING_COUNT; the migration becomes completed only after no active file still requires the source contract.

• airlock.admin.retire_spec_migration(migration_id VARCHAR)
• Accepts only a completed migration and rechecks that every item is terminal and every active file has a valid target-data-version attestation.
• Clears the read-only source and active migration pointers, rebuilds the current view without the source branch, and releases the spec for a future migration.
• Retains immutable source revisions, original file bytes and manifest rows, validation attestations, migration evidence, and lineage for audit.

• airlock.admin.cancel_spec_migration(migration_id VARCHAR)
• Cancels a nonterminal migration and releases the spec's one-migration slot.
• Does not change active versions or stored bytes.

• airlock.observe.spec_migrations(spec_name VARCHAR DEFAULT NULL, migration_status VARCHAR DEFAULT NULL, max_rows INTEGER DEFAULT 200)
• Lists migration lifecycle state, immutable version pointers, proof counts, zero-copy/transformed/superseded/retry counts, and validation, approval, activation, completion, and retirement actors.

• airlock.observe.spec_migration(migration_id VARCHAR)
• Returns an AI-oriented detail packet with the bounded transform, compatibility relation, validation summary, retry evidence, and exact source-to-target manifest, attestation, and content-hash lineage for up to 200 source items.
• Includes source_items_total, source_items_returned, and source_items_truncated so callers know when to page exact evidence.
• Does not expose governed file contents.

• airlock.observe.spec_migration_items(migration_id VARCHAR, item_status VARCHAR DEFAULT NULL, after_file_manifest_pk VARCHAR DEFAULT NULL, max_rows INTEGER DEFAULT 200)
• Pages exact migration-item lineage in stable file_manifest_pk order, up to 500 rows per call.
• When has_more is true, pass the final returned file_manifest_pk as after_file_manifest_pk on the next call.
• Exposes hashes, validation IDs, target lineage, attempts, and status without governed file contents or worker claim tokens.

An opt-in repository proof exercises the whole public lifecycle without direct table writes:

make demo-spec-migration SNOWFLAKE_CONN=<admin-connection>

• airlock.observe.spec_versions(spec_name VARCHAR)
• Description: Lists versioned spec_config metadata stored in AIRLOCK_STAGE.
• Parameters:
• spec_name (required): logical spec identifier.
• Returns:
• SPEC_NAME VARCHAR
• VERSION_ID VARCHAR
• STATUS VARCHAR
• IS_LATEST BOOLEAN
• SPEC_CONFIG_HASH VARCHAR
• META VARIANT
• Example:

    CALL airlock.observe.spec_versions('budget_requests');

• airlock.observe.spec_version(spec_name VARCHAR, version_id VARCHAR)
• Description: Fetches a specific versioned spec_config and metadata from AIRLOCK_STAGE.
• Parameters:
• spec_name (required): logical spec identifier.
• version_id (required): version identifier (YYYYMMDDHHMMSS or YYYYMMDDHHMMSS-<8 char uuid>).
• Returns:
• SPEC_NAME VARCHAR
• VERSION_ID VARCHAR
• STATUS VARCHAR
• SPEC_CONFIG VARIANT
• META VARIANT
• Example:

    CALL airlock.observe.spec_version('budget_requests', '20260315123045');

• airlock.admin.backfill_spec_versions(spec_name VARCHAR DEFAULT NULL)
• Description: Initializes exact active spec/data version pointers and canonical hashes for legacy specs. It creates a bootstrap revision from the current durable config and deliberately leaves legacy file validation provenance unknown; it never guesses which old stage snapshot validated a file. Ambiguous legacy column names or identities fail closed and must be corrected before provenance can be initialized. Retryable concurrent-change conflicts are reported as BACKFILL_CONFLICT; install/upgrade setup treats them as failures rather than reporting incomplete initialization as successful.
• Parameters:
• spec_name (optional): limit to a single spec; default is all specs.
• Returns:
• SPEC_NAME VARCHAR
• STATUS VARCHAR
• VERSION_ID VARCHAR
• DETAILS VARIANT
• Example:

    CALL airlock.admin.backfill_spec_versions();
    CALL airlock.admin.backfill_spec_versions('budget_requests');

• airlock.admin.drop_spec(spec_name VARCHAR)
• Description: Deletes the spec row from core.specs (actual drop). Archiving a spec is done via alter_spec (set is_archived in spec_config). Related files in stage, manifest rows, and views are not deleted; clean them up separately if needed.
• Cancels and retains any nonterminal migration history before releasing the spec's active migration slot.
• Parameters:
• spec_name (required): logical spec identifier.
• Returns:
• SPEC_NAME VARCHAR
• STATUS VARCHAR – dropped, not_found, or error
• IS_DROPPED BOOLEAN – TRUE when the row was deleted
• MESSAGE VARCHAR – human‑readable description of the outcome
• ISSUES VARIANT – array of {code, message, severity} (same shape as load_data ISSUES); empty when STATUS is dropped
• Example:

    CALL airlock.admin.drop_spec('budget_requests');

• airlock.observe.files(spec_name VARCHAR, in_app_role VARCHAR DEFAULT NULL)
• Description: Lists files in a spec’s stage directory.
• Parameters:
• spec_name (required): spec to list files from.
• in_app_role (optional): restrict to a specific role directory for multi‑role specs.
• Returns: Snowflake LIST output:
• name VARCHAR
• size NUMBER
• md5 VARCHAR
• last_modified TIMESTAMP
• On normal failure: STATUS, CODE, MESSAGE, ISSUES
• Example:

    CALL airlock.observe.files('budget_requests');
    CALL airlock.observe.files('budget_requests', 'budget_analyst');

• airlock.observe.work(spec_name VARCHAR DEFAULT NULL)
• Description: Read-only, assignment-scoped projection of current workflow and expectation work. Workflow rows include actionable and observed files; expectation rows include unmet, upcoming, overdue, and excepted expectations. Met expectations are omitted because they are no longer work. AVAILABLE_ACTIONS describes actions available to an authorized agent and does not grant the observer permission to perform them. app_admin and assigned global_observer callers receive the account-wide projection.
• Returns: WORK_ITEM_ID, SPEC_NAME, WORK_SOURCE, WORK_ITEM_TYPE, WORK_STATUS, SUMMARY, PATH, FILENAME, DUE_AT, DAYS_UNTIL_DUE, AVAILABLE_ACTIONS, ACCESS_REASON, IS_STRICT, UPDATED_AT, DETAILS. Read-only file visibility uses WORK_ITEM_TYPE = 'observe_visible' and ACCESS_REASON = 'observer'.
• Example:

    CALL airlock.observe.work();
    CALL airlock.observe.work('budget_requests');

• airlock.admin.edit_file_workflow(spec_name VARCHAR, path VARCHAR, filename VARCHAR, action VARCHAR, comment VARCHAR DEFAULT NULL, validate_only BOOLEAN DEFAULT FALSE)
• Description: Moves a file’s workflow state by one step (advance/return) or archives/restores the file. Admin-only. Strict Expectations for the target workflow milestone can block the transition unless an active Exception applies. On advance, every active spec source link with min_count > 0 must have enough active references to non-removed source files in any configured required_workflow_status; otherwise the procedure returns SOURCE_REFERENCE_REQUIRED without changing workflow state.
• Parameters:
• spec_name (required): logical spec identifier.
• path (required): scope/path of the file (e.g. default or role name for multi-role specs).
• filename (required): logical filename (no extension).
• action (required): advance (next step), return (previous step), archive (move to archived), restore (from archived to initial step).
• comment (optional): reason or note for the transition (validated for length and control characters).
• validate_only (optional, default FALSE): when TRUE, returns validation result without updating FILE_MANIFEST.
• Returns:
• SPEC_NAME, PATH, FILENAME, STATUS, CODE, MESSAGE, IS_UPDATED, FROM_STEP, TO_STEP, ISSUES, DETAILS
• Branch on STATUS and CODE. ISSUES contains structured warnings/errors; DETAILS contains compact diagnostics such as workflow step names and expectation findings.
• Example:

    CALL airlock.admin.edit_file_workflow('budget_requests', 'default', 'report_2025', 'advance', 'Ready for review');
    CALL airlock.admin.edit_file_workflow(spec_name => 'budget_requests', path => 'finadmin', filename => 'finance', action => 'archive');

• airlock.admin.select_files(spec_name VARCHAR, search_string VARCHAR DEFAULT NULL, regex_pattern VARCHAR DEFAULT NULL, in_app_role VARCHAR DEFAULT NULL)
• Description: Reads spec data from stage, with fuzzy or regex filename filtering.
• Parameters:
• spec_name (required)
• search_string (optional): fuzzy match anywhere in path (preferred for most users).
• regex_pattern (optional): full regex; used only when search_string is NULL/empty.
• in_app_role (optional): restrict to a role directory (multi‑role specs).
• Returns:
• All configured data columns for the spec, plus:
• source_file
• file_last_modified
• On normal failure: STATUS, CODE, MESSAGE, ISSUES
• Example:

    CALL airlock.admin.select_files('budget_requests');
    CALL airlock.admin.select_files('budget_requests', 'report_2025');
    CALL airlock.admin.select_files(spec_name => 'budget_requests', regex_pattern => '.*\\.csv\\.gz$');
    CALL airlock.admin.select_files(spec_name => 'budget_requests', in_app_role => 'budget_analyst');

• airlock.admin.validate_data(spec_name VARCHAR, path VARCHAR DEFAULT NULL, file_content VARCHAR DEFAULT NULL)
• Description: Validates a file for a spec. Exactly one of path or file_content must be provided. With staged_path: checks spec existence, non‑archived, file in app stage, CSV parseability. With file_content: CSV string (header + rows) passed inline; validates against spec column_config (columns, types, not_null, allowed_values) and FK/reference rules, including opt-in caller-scoped reference validation. For small files only. Excel vs CSV in spec file_rules.file_format.file_type is for UI only; we never store Excel. Specs configured for Excel still accept CSV and pass validation.
• Large file guidance: The Streamlit UI enforces an upload size limit for interactive use. For large files, upload directly to the stage (for example with PUT through Snowflake CLI or a Snowflake driver) and call validate_data with path instead of file_content.
• Parameters:
• spec_name (required): logical spec identifier.
• path (optional): path to the file under the app stage (relative or with stage reference). Omit when using file_content.
• file_content (optional): CSV string (header + data rows) to validate inline. Omit when using path.
• Returns:
• SPEC_NAME VARCHAR
• PATH VARCHAR – the path, or the logical file name (e.g. finance) when file_content was used
• STATUS VARCHAR – ok or error
• IS_VALID BOOLEAN
• ISSUE_COUNT NUMBER
• ISSUES VARIANT – array/object of structured issues.
• CODE VARCHAR – stable machine-readable outcome code. VALID on success; otherwise the first error/warning issue code.
• Example:

    CALL airlock.admin.validate_data('budget_requests', 'budget_requests/path/abc123/report.csv.gz');
    CALL airlock.admin.validate_data(
      spec_name => 'fy26_budget_requests',
      file_content => 'fiscal_year,account,request,narrative\n2026,133-5111,100,Note.'
    );

• airlock.admin.load_data(spec_name VARCHAR, path VARCHAR DEFAULT NULL, file_content VARCHAR DEFAULT NULL, filename VARCHAR DEFAULT NULL, path_scope VARCHAR DEFAULT NULL, attachment_content_base64 VARCHAR DEFAULT NULL, attachment_filename VARCHAR DEFAULT NULL, in_app_role VARCHAR DEFAULT NULL)
• Description: Promotes a validated file into core.FILE_MANIFEST (authoritative in-app table; read-only account projection is AIRLOCK_DATA.CORE.FILE_MANIFEST) for a spec. Exactly one of path or file_content must be provided. For staged paths, Airlock captures the exact object digest before validation and verifies the same digest again immediately before the manifest transaction. A missing digest returns STAGED_FILE_HASH_UNAVAILABLE; replacement during validation returns STAGED_FILE_CHANGED_RETRY. Neither outcome writes validation evidence. The procedure validates before any manifest write and atomically records the exact spec/data revision used. When an upgraded active legacy manifest already owns the same staged path with unknown provenance, a successful staged load re-attests that existing manifest in place instead of inserting a duplicate stage path; its workflow, links, and stored bytes remain intact. If the spec changes during validation or manifest commit, no manifest attestation is written and the call returns SPEC_VERSION_CHANGED_RETRY. Before writing the manifest row, Airlock evaluates active Expectations for the load target: workflow specs target the initial workflow step; no-workflow specs target the implicit became_active milestone and display the current file as Active. A strict unmet expectation rejects the load with STATUS = 'error', IS_LOADED = FALSE, and issue code EXPECTATION_BLOCKED. A non-strict unmet expectation allows the load and returns issue code EXPECTATION_WARNING. Returns spec_name, path (scope), and filename to match FILE_MANIFEST. If the spec has materialize_as_table, calls admin.sync_table(spec_name) after the manifest update. Multi-role specs require an explicit non-default scope: use a staged path with a scope, or use inline file_content with path_scope. When admin-seeding a role-isolated folder, pass the matching in_app_role so authorization and reference validation use the intended role lens. Optional attachment: pass attachment_content_base64 (+ optional attachment_filename) to register one attachment for the loaded file in the same call. When the spec has attachment_policy.attachment_required=true, an attachment is required in the same call: if attachment_content_base64 is omitted or empty, the load is rejected (no file is written to the manifest) and the procedure returns status error with issue code ATTACHMENT_REQUIRED.
• Large file guidance: For large files, use a staged path instead of inline file_content to avoid UI upload limits and session memory constraints.
• Parameters:
• spec_name (required): logical spec identifier.
• path (optional): path to the file under the app stage (e.g. spec/scope/upload_id/file.csv). Omit when using file_content. Multi-role specs require scope in the path.
• file_content (optional): CSV string (header + rows) passed inline. For multi-role specs, include a non-default path_scope.
• filename (optional): logical filename when using file_content.
• path_scope (optional): scope for inline uploads (e.g. 'public/full_access').
• attachment_content_base64 (optional): base64-encoded attachment to register for this file in the same call; required when spec has attachment_required.
• attachment_filename (optional): filename for the attachment (e.g. receipt.pdf).
• in_app_role (optional): role lens for admin-seeded role-isolated folders and role-scoped reference validation.
• Returns:
• SPEC_NAME VARCHAR
• PATH VARCHAR – scope (same as FILE_MANIFEST path)
• FILENAME VARCHAR – logical filename (same as FILE_MANIFEST filename)
• STATUS VARCHAR – ok or error
• IS_LOADED BOOLEAN
• ROW_COUNT NUMBER – number of data rows loaded (nullable if unknown)
• ISSUE_COUNT NUMBER
• ISSUES VARIANT – array/object of structured issues (including validation, expectation, and manifest-level problems).
• CODE VARCHAR – stable machine-readable outcome code. LOADED on success; otherwise the first error/warning issue code.
• Example:

    CALL airlock.admin.load_data('budget_requests', 'budget_requests/scope/abc123/report.csv.gz');
    CALL airlock.admin.load_data(
      spec_name => 'fy26_budget_requests',
      file_content => 'fiscal_year,account,request,narrative\n2026,133-5111,100,Note.',
      filename => 'fy26_budget_request',
      path_scope => 'finadmin',
      in_app_role => 'finadmin'
    );

• airlock.admin.load_okf_bundle(spec_name VARCHAR, bundle_content_base64 VARCHAR, bundle_filename VARCHAR, bundle_metadata VARIANT, concept_metadata VARIANT, path_scope VARCHAR DEFAULT NULL, in_app_role VARCHAR DEFAULT NULL, validate_only BOOLEAN DEFAULT FALSE)
• Description: Loads a governed OKF knowledge bundle into Airlock stage storage and core.FILE_MANIFEST, then projects parsed concept metadata into core.OKF_CONCEPT_METADATA. The target spec must declare core_config.payload_adapter = "okf_knowledge_bundle". Airlock evaluates PDP access and active Expectations before writing. Raw bundle bytes remain governed by normal Airlock file workflow, manifest history, and audit rules. Authoritative agent context should come from AIRLOCK_DATA.ACTIVE.V_OKF_CONCEPT_METADATA, which includes only current Accepted or no-workflow Active manifest rows.
• Parameters:
• spec_name (required): spec configured for the OKF bundle adapter.
• bundle_content_base64 (required): base64-encoded bundle bytes, typically a zip archive validated by the local OKF helper.
• bundle_filename (required): bundle filename. The logical manifest filename is normalized from this value.
• bundle_metadata (required): root bundle metadata such as bundle_name, bundle_version, domain, owner, and purpose.
• concept_metadata (required): array of parsed concept rows. Each row requires concept_path, type, and sha256; optional fields include path, title, description, resource, tags, timestamp, and source_links. Each row's metadata object holds OKF v0.2 provenance, verification, lifecycle, computation descriptors, and extensions. A single verified object becomes an array. Malformed metadata returns OKF_METADATA_INVALID. These claims never grant access, advance workflow, or execute computations.
• path_scope (optional, default NULL): scope for the governed bundle. Multi-role specs require a non-default scope.
• in_app_role (optional, default NULL): role lens for admin-seeded role-isolated folders.
• validate_only (optional, default FALSE): validate bundle metadata, PDP access, and Expectations without writing.
• Returns: TABLE(SPEC_NAME, PATH, FILENAME, STATUS, IS_LOADED, CONCEPT_COUNT, ISSUE_COUNT, ISSUES, CODE). Branch on STATUS and CODE. Successful writes return OKF_BUNDLE_LOADED; validation-only success returns VALID.
• Example:

    CALL airlock.admin.load_okf_bundle(
      spec_name => 'okf_knowledge',
      bundle_content_base64 => '<base64 zip bytes>',
      bundle_filename => 'sales_context.zip',
      bundle_metadata => PARSE_JSON('{"bundle_name":"sales_context","bundle_version":"2026.06"}'),
      concept_metadata => PARSE_JSON('[{"concept_path":"tables/orders","type":"BigQuery Table","sha256":"abc123"}]'),
      validate_only => TRUE
    );

• airlock.admin.sync_okf_bundle_metadata(spec_name VARCHAR, path VARCHAR, filename VARCHAR, bundle_metadata VARIANT, concept_metadata VARIANT, validate_only BOOLEAN DEFAULT FALSE)
• Description: Projects parsed OKF bundle concept metadata for an existing active core.FILE_MANIFEST row. Use load_okf_bundle for the normal governed write path; use this helper when the raw bundle file already has an active manifest row and only the parsed projection needs to be refreshed. Authoritative agent context should come from AIRLOCK_DATA.ACTIVE.V_OKF_CONCEPT_METADATA, which includes only current Accepted or no-workflow Active manifest rows.
• Parameters:
• spec_name (required): spec containing the governed bundle file.
• path (required): FILE_MANIFEST path/scope for the bundle file.
• filename (required): FILE_MANIFEST logical filename for the bundle file.
• bundle_metadata (required): root bundle metadata such as bundle_name, bundle_version, domain, owner, and purpose.
• concept_metadata (required): array of parsed concept rows. Each row requires concept_path, type, and sha256; optional fields include path, title, description, resource, tags, timestamp, and source_links. The nested metadata object uses the same OKF v0.2 normalization and validation as load_okf_bundle; it does not change Airlock workflow state. Normalized frontmatter is exposed under the existing view's METADATA:metadata field.
• validate_only (optional, default FALSE): validate shape and manifest linkage without writing metadata.
• Returns: TABLE(STATUS, CODE, MESSAGE, ROW_COUNT, ISSUE_COUNT, ISSUES). Branch on STATUS and CODE. Successful writes return OKF_METADATA_SYNCED; validation-only success returns VALID.
• Example:

    CALL airlock.admin.sync_okf_bundle_metadata(
      'okf_knowledge',
      'default',
      'sales_context',
      PARSE_JSON('{"bundle_name":"sales_context","bundle_version":"2026.06"}'),
      PARSE_JSON('[{"concept_path":"tables/orders","type":"BigQuery Table","sha256":"abc123"}]'),
      TRUE
    );

• airlock.admin.delete_files(spec_name VARCHAR, source_files VARCHAR, source_directory VARCHAR DEFAULT NULL, dry_run BOOLEAN DEFAULT FALSE)
• Description: Canonical procedure to delete files from a spec: REMOVE from stage and set removed_at/removed_by on FILE_MANIFEST. Files cited by active governed file references are not removed and return SOURCE_FILE_REFERENCED. Unmigrated rows pinned as a read-only migration source return MIGRATION_SOURCE_FILE_FROZEN. When dry_run is TRUE, returns what would be deleted without side effects.
• Parameters:
• spec_name (required)
• source_files (required): single filename or JSON array of filenames.
• source_directory (optional): path/scope; if provided, only files in that path are considered.
• dry_run (optional, default FALSE).
• Returns: TABLE(SPEC_NAME, PATH, FILENAME, UPLOAD_ID, STAGE_PATH, STATUS, MESSAGE, CODE, ISSUES). Branch on STATUS and stable CODE values such as REMOVED, DRY_RUN, NO_MATCH, ACCESS_DENIED, SOURCE_FILE_REFERENCED, MIGRATION_SOURCE_FILE_FROZEN, LOOKUP_FAILED, and UPDATE_FAILED; ISSUES is empty on success and unblocked dry-run rows.
• Example:

    CALL airlock.admin.delete_files(spec_name => 'my_spec', source_files => 'report.csv.gz', dry_run => TRUE);
    CALL airlock.admin.delete_files('my_spec', '["a.csv", "b.csv"]', 'finadmin', FALSE);

• airlock.observe.file_versions(spec_name VARCHAR, source_file VARCHAR DEFAULT NULL, uploaded_after TIMESTAMP_LTZ DEFAULT NULL, uploaded_before TIMESTAMP_LTZ DEFAULT NULL)
• Description: Enumerates file versions and their original validation provenance from FILE_MANIFEST.
• Parameters:
• spec_name (required)
• source_file (optional): filter by filename (exact match).
• uploaded_after / uploaded_before (optional): filter on uploaded_at.
• Returns: SPEC_NAME, PATH, FILENAME, UPLOAD_ID, STAGE_PATH, IS_ACTIVE, UPLOADED_AT, UPLOADED_BY, BECAME_INACTIVE_AT, REMOVED_AT, VALIDATED_SPEC_VERSION_ID, VALIDATED_DATA_VERSION_ID, VALIDATED_SPEC_CONFIG_HASH, VALIDATION_PROVENANCE_STATUS (exact or unknown), MIGRATED_BY_MIGRATION_ID, and MIGRATION_SOURCE_MANIFEST_PK.
• Example:

    CALL airlock.observe.file_versions('my_spec');
    CALL airlock.observe.file_versions('my_spec', 'report.csv');
    CALL airlock.observe.file_versions(
      spec_name => 'my_spec',
      uploaded_after => '2024-01-01'::TIMESTAMP_LTZ,
      uploaded_before => '2024-12-31'::TIMESTAMP_LTZ
    );

• airlock.observe.file_state(spec_name VARCHAR, path VARCHAR, filename VARCHAR)
• Description: Returns compact mutable state for one active logical file without reading its governed rows or attachment bytes. FILE_STATE_ID is a UUID that rotates when the file data is replaced or its workflow, attachments, exact source references, or retained version history change. A missing file or revoked observer grant returns no row; failures after authorization still surface as procedure errors.
• Returns: SPEC_NAME, PATH, FILENAME, UPLOAD_ID, FILE_STATE_ID, WORKFLOW_STEP, WORKFLOW_STATUS, ATTACHMENT_COUNT, REFERENCE_COUNT, and LAST_CHANGED_AT.
• Example:

    CALL airlock.observe.file_state('budget_requests', 'finadmin', 'fy27_budget');

• airlock.observe.file_validations(spec_name VARCHAR, path VARCHAR DEFAULT NULL, filename VARCHAR DEFAULT NULL, max_items FLOAT DEFAULT 500)
• Description: Lists append-only validation attestations for governed file versions. Revalidation creates another row rather than replacing the file's original validated-against claim.
• Parameters: spec_name is required; path, filename, and max_items are optional filters.
• Returns: Manifest identity, immutable spec/data version IDs and hash, optional content checksum, validation status/source, validator contract and app version, optional migration ID/summary, and validation actor/time.
• Example:

    CALL airlock.observe.file_validations('budget_requests');
    CALL airlock.observe.file_validations('budget_requests', 'finadmin', 'fy27_budget', 100);

#2.4.1. Attachment policy

Spec-level attachment policies (attachment_policy in spec config) control whether attachments are enabled, max count, active count per attachment tag, allowed types/extensions, and optional image profiles. The default active limit per attachment_tag is 1; use default_attachment_tag_limit or attachment_tag_limits to override it. When attachment_required is true, load_data (admin and user) will not accept a file upload unless an attachment is provided in the same call; the load is rejected and no file is written to the manifest (issue code ATTACHMENT_REQUIRED). Attachment metadata is stored in core.ATTACHMENT_MANIFEST; AIRLOCK_DATA.CORE.ATTACHMENT_MANIFEST is the read-only secure view for account consumers. Additional attachment behavior and Streamlit touch points: docs/attachments_batch26.md.

Preview behavior: all attachment file types are supported for storage and download. Inline preview is available for select formats only. Images and text files are previewed directly; PDFs up to 100 MB and 2,000 pages can show one bounded rendered page at a time when the runtime can render them. The selected PDF page and next two pages render in one bounded window so forward navigation can use the private session cache. PDFs larger than 12 MB require an explicit open action, stream to private session-local storage, and use short-lived Snowflake links for full-file download when available. Successful page reads emit metadata-only ATTACHMENT_PREVIEW activity. Other binary formats are download-only.

• airlock.admin.attach_to_spec(spec_name VARCHAR, attachment_stage_path VARCHAR DEFAULT NULL, attachment_content_base64 VARCHAR DEFAULT NULL, attachment_filename VARCHAR DEFAULT NULL, attachment_type VARCHAR DEFAULT 'other', description VARCHAR DEFAULT NULL, link_spec_name VARCHAR DEFAULT NULL, link_file_path VARCHAR DEFAULT NULL, link_file_filename VARCHAR DEFAULT NULL, attachment_tag VARCHAR DEFAULT NULL)
• Description: Registers a binary file as a spec-level attachment (not tied to a specific data file). Use when creating a spec and its first attachment in one flow. Exactly one input mode: stage (attachment_stage_path under @AIRLOCK_DATA.CORE.AIRLOCK_ATTACHMENTS) or stream (attachment_content_base64 and optional attachment_filename; decoded and uploaded via put_stream). For larger streamed attachments, use server-bound parameters rather than embedding base64 in SQL text; base64 expands raw bytes by roughly one third and remains subject to the account's Snowflake string limit. When link_file_* are provided, the attachment is linked to a logical file.
• Parameters:
• spec_name (required)
• attachment_stage_path (optional): path under AIRLOCK_ATTACHMENTS stage.
• attachment_content_base64 (optional): base64-encoded content (stream mode).
• attachment_filename (optional): filename for stream mode.
• attachment_type (optional, default 'other'): e.g. 'other', 'image', 'document'.
• description (optional)
• link_spec_name, link_file_path, link_file_filename (optional): link attachment to this logical file.
• attachment_tag (optional, default default): logical attachment tag. Adding another active attachment for the same tag retires older rows beyond the tag limit.
• Returns: SPEC_NAME, ATTACHMENT_STAGE_PATH, STATUS, CODE, MESSAGE, ATTACHMENT_ID, SIZE_BYTES, ISSUES. Normal failures return STATUS = 'error' with stable CODE values such as SPEC_NOT_FOUND, ATTACHMENTS_DISABLED, INVALID_BASE64, ACCESS_DENIED_WORKFLOW_STATE, or ATTACHMENT_SAVE_FAILED.
• Example:

    CALL airlock.admin.attach_to_spec(
      spec_name => 'my_spec',
      attachment_content_base64 => 'YWJj',
      attachment_filename => 'note.txt',
      attachment_type => 'other',
      description => 'Sample note'
    );

• airlock.admin.add_attachment(spec_name VARCHAR, file_path VARCHAR, file_filename VARCHAR, attachment_stage_path VARCHAR DEFAULT NULL, attachment_content_base64 VARCHAR DEFAULT NULL, attachment_filename VARCHAR DEFAULT NULL, attachment_type VARCHAR DEFAULT 'other', description VARCHAR DEFAULT NULL, attachment_tag VARCHAR DEFAULT NULL)
• Description: Adds an attachment to an existing file in a spec. Enforces spec attachment_policy and file workflow state (add allowed only when the file is in a step that permits file/attachment management). Exactly one of attachment_stage_path or attachment_content_base64 (with optional attachment_filename) is required.
• Parameters:
• spec_name, file_path, file_filename (required): identify the target file.
• attachment_stage_path or attachment_content_base64 (+ optional attachment_filename): content source.
• attachment_type (optional, default 'other'), description (optional), attachment_tag (optional, default default).
• Returns: SPEC_NAME, FILE_PATH, FILE_FILENAME, ATTACHMENT_ID, ATTACHMENT_STAGE_PATH, STATUS, CODE, MESSAGE, SIZE_BYTES, ISSUES. On denial (e.g. workflow state), STATUS = 'error', CODE = 'ACCESS_DENIED_WORKFLOW_STATE'.

• airlock.admin.replace_attachment(spec_name VARCHAR, file_path VARCHAR, file_filename VARCHAR, attachment_id VARCHAR, attachment_stage_path VARCHAR DEFAULT NULL, attachment_content_base64 VARCHAR DEFAULT NULL, attachment_filename VARCHAR DEFAULT NULL, attachment_type VARCHAR DEFAULT 'other', description VARCHAR DEFAULT NULL, attachment_tag VARCHAR DEFAULT NULL)
• Description: Replaces an existing attachment by attachment_id. Same workflow and policy checks as add; same content options (stage or base64).

• airlock.admin.delete_attachment(spec_name VARCHAR, file_path VARCHAR, file_filename VARCHAR, attachment_id VARCHAR)
• Description: Deletes an attachment by attachment_id for the given file. Subject to same workflow/policy rules (manage allowed only in permitted steps).

#2.5. Retention Policies & Outdated Files

Retention policy CRUD

• airlock.observe.retention_policies(spec_name VARCHAR DEFAULT NULL)
• Description: Lists retention policies; optional filter by spec_name (policies for that spec or account-level).
• Parameters: spec_name (optional).
• Returns: POLICY_NAME, SPEC_NAME, RETENTION_DAYS, RETENTION_MIN_VERSIONS, RETENTION_SCOPE, DESCRIPTION, IS_ACTIVE, IS_LOCKED.
• Example:

    CALL airlock.observe.retention_policies();
    CALL airlock.observe.retention_policies('my_spec');

• airlock.observe.retention_policy(policy_name VARCHAR)
• Description: Returns a canonical descriptor for a single retention policy (by policy_name).
• Parameters: policy_name (required).
• Returns: Same columns as list (single row or empty).
• Example:

    CALL airlock.observe.retention_policy('Account Default');

• airlock.admin.create_retention_policy(policy_descriptor VARIANT, validate_only BOOLEAN DEFAULT FALSE)
• Description: Creates a new retention policy. Descriptor: policy_name (required); optional: spec_name (NULL = account-level), retention_days, retention_min_versions, retention_scope, description, is_active, is_locked. Validates retention_days / retention_min_versions >= 0 and that spec_name exists in core.specs if provided.
• Returns: POLICY_NAME, STATUS, IS_CREATED, VALIDATION.
• Example:

    CALL airlock.admin.create_retention_policy(PARSE_JSON('{"policy_name": "Spec X", "spec_name": "x", "retention_days": 30}'));

• airlock.admin.alter_retention_policy(policy_name VARCHAR, policy_descriptor VARIANT, validate_only BOOLEAN DEFAULT FALSE)
• Description: Alters an existing retention policy. Optional descriptor fields: spec_name, retention_days, retention_min_versions, retention_scope, description, is_active, is_locked.
• Returns: POLICY_NAME, STATUS, IS_UPDATED, VALIDATION.
• Example:

    CALL airlock.admin.alter_retention_policy('Spec X', PARSE_JSON('{"retention_days": 60}'));

• airlock.admin.drop_retention_policy(policy_name VARCHAR, force BOOLEAN DEFAULT FALSE)
• Description: Drops a retention policy. The account default policy cannot be dropped. When force is FALSE, does not delete if the policy is locked.
• Parameters: policy_name (required), force (optional, default FALSE).
• Returns: POLICY_NAME, STATUS, CODE, MESSAGE, IS_DROPPED, ISSUES, DETAILS.
• Branch on STATUS and stable CODE; ISSUES is an array of {code, message, severity} and is empty when STATUS is ok.
• Example:

    CALL airlock.admin.drop_retention_policy('My Policy');
    CALL airlock.admin.drop_retention_policy('My Policy', TRUE);

Outdated files (retention-driven purge)

• airlock.observe.purge_candidates(spec_name VARCHAR DEFAULT NULL)
• Description: Lists non-current uploads that are eligible for purge based on retention policies. Source manifests protected by a source_preserved migration are excluded. Scoped observers receive only candidates allowed by their spec, path, and workflow-state grant; omitting spec_name requires app_admin or global_observer.
• Parameters:
• spec_name (optional): limit to a specific spec.
• Returns (shape may be extended in future v1.x, but these columns are stable):
• spec_name
• path
• filename
• upload_id
• stage_path
• became_inactive_at
• uploaded_at
• Example:

    CALL airlock.observe.purge_candidates();
    CALL airlock.observe.purge_candidates('sample_budget_requests');

• airlock.admin.delete_outdated_files(spec_name VARCHAR DEFAULT NULL, dry_run BOOLEAN DEFAULT TRUE, limit INTEGER DEFAULT 1000)
• Description: Deletes outdated files from stage and updates history, based on retention policies. Source manifests protected by a source_preserved migration are never selected. When dry_run is FALSE, writes a FILE_RETENTION_PURGE row to core.events per removed file (see Events & observability above).
• Parameters:
• spec_name (optional): restrict scope.
• dry_run (optional, default TRUE): when TRUE, report what would be deleted without side effects.
• limit (optional, default 1000): maximum number of files to delete in one call.
• Returns (one row per candidate in this call; shape is stable in v1.x):
• spec_name, path, filename, upload_id, stage_path, manifest_pk (core.FILE_MANIFEST.pk for that upload row)
• Example:

    CALL airlock.admin.delete_outdated_files(dry_run => TRUE);
    CALL airlock.admin.delete_outdated_files(dry_run => FALSE);
    CALL airlock.admin.delete_outdated_files('sample_budget_requests', FALSE, 100);

#2.6. File Maintenance

• airlock.admin.rebuild_file_manifest_from_stage(force BOOLEAN DEFAULT FALSE)
• Description: Rebuilds FILE_MANIFEST from stage listing, optionally truncating first. Refuses to rebuild while append-only validation attestations exist, preserving their manifest identity links.
• Parameters: force (optional, default FALSE): when TRUE, transactionally clears FILE_MANIFEST before rebuilding. Rebuilds serialize with exact-provenance file writers and are refused while validation attestations exist.
• Returns: summary string.
• Example:

    CALL airlock.admin.rebuild_file_manifest_from_stage();
    CALL airlock.admin.rebuild_file_manifest_from_stage(TRUE);

• airlock.admin.rebuild_access_index(bump_version BOOLEAN DEFAULT TRUE)
• Description: Rebuilds compiled Airlock access rows from app-managed roles, assignments, and specs. Snowflake session/application-role activation is intentionally not cached.
• Parameters: bump_version (optional, default TRUE): when TRUE, publishes a new access version after both compiled halves rebuild successfully.
• Returns: TABLE(STATUS, CODE, MESSAGE, USER_ROLE_ROWS, SPEC_ACCESS_ROWS, VERSION_UUID, ISSUES, DETAILS)
• Example:

    CALL airlock.admin.rebuild_access_index();

• airlock.admin.rebuild_user_role_closure(user_id VARCHAR DEFAULT NULL, bump_version BOOLEAN DEFAULT FALSE)
• Description: Rebuilds compiled user-to-role closure rows for one user or all users. Effective assignment windows remain evaluated at read time.
• Parameters: bump_version (optional, default FALSE): when TRUE without user_id, also rebuilds spec access and publishes a new access version; filtered rebuilds keep the current version.
• Returns: TABLE(STATUS, CODE, MESSAGE, USER_ROLE_ROWS, SPEC_ACCESS_ROWS, VERSION_UUID, ISSUES, DETAILS)
• Example:

    CALL airlock.admin.rebuild_user_role_closure();
    CALL airlock.admin.rebuild_user_role_closure('ASMITH');

• airlock.admin.rebuild_spec_role_access(spec_name VARCHAR DEFAULT NULL, bump_version BOOLEAN DEFAULT FALSE)
• Description: Rebuilds compiled spec role-access rows for one spec or all specs. Per-user isolated guest path keys remain lazy and are not created by this rebuild.
• Parameters: bump_version (optional, default FALSE): when TRUE without spec_name, also rebuilds user-role closure and publishes a new access version; filtered rebuilds keep the current version.
• Returns: TABLE(STATUS, CODE, MESSAGE, USER_ROLE_ROWS, SPEC_ACCESS_ROWS, VERSION_UUID, ISSUES, DETAILS)
• Example:

    CALL airlock.admin.rebuild_spec_role_access();
    CALL airlock.admin.rebuild_spec_role_access('finance');

• airlock.admin.sync_table(spec_name VARCHAR)
• Description: Syncs the materialized table for a spec with its view using MERGE. Creates the materialized table on first use if it does not exist. Called automatically by load_data when the spec has materialize_as_table set. Admin-only; use when you need to force a sync without loading a file. Use rebuild_spec_table only when the spec's column shape changed and the existing table schema must be replaced.
• Parameters: spec_name (required).
• Returns: STATUS, ROWS_INSERTED, ROWS_UPDATED, ROWS_DELETED, MESSAGE
• Example:

    CALL airlock.admin.sync_table('budget_requests');

#2.8. Spec Views & Tables

• airlock.admin.rebuild_spec_view(spec_name VARCHAR)
• Description: Rebuilds ACTIVE and HIST views for a single spec.
• Parameters: spec_name (required).
• Returns: summary string.
• Example:

    CALL airlock.admin.rebuild_spec_view('sample_budget_requests');

• airlock.admin.rebuild_spec_table(spec_name VARCHAR)
• Description: Rebuilds the ACTIVE materialized table (T_{spec_name}) for a spec and syncs it.
• Parameters: spec_name (required).
• Returns: summary string.
• Example:

    CALL airlock.admin.rebuild_spec_table('sample_budget_requests');

#2.9. Setup & Validation

• airlock.admin.rerun_setup()
• Description: Idempotent setup for core objects, baseline data, and setup‑time views.
• Returns: summary string.
• Example:

    CALL airlock.admin.rerun_setup();

• airlock.admin.validate_spec(spec_config VARIANT, reuse_spec BOOLEAN DEFAULT FALSE, allow_existing_spec BOOLEAN DEFAULT FALSE)
• Description: Validates a spec_config object using the same rules as the UI:
• Core config (spec_name, spec_alias, owner_role, is_published, is_archived, workflow, etc.)
• File rules (CSV/Excel)
• Column config
• Guest access, including DB checks
• Optional rules array: rules may define foreign_key, reference_map, date_window, or variant_shape validation. foreign_key may use either field or a field_path inside a VARIANT column; see Shared schema & FK validation. When allow_existing_spec is TRUE, spec_name and spec_alias may already exist for this same spec (round-trip from observe.spec_config or alter flow).
• Parameters:
• spec_config (required): VARIANT or JSON string.
• reuse_spec (optional): allow reuse of an existing spec directory in stage.
• allow_existing_spec (optional): allow spec_name and spec_alias to already exist for this same spec; use when validating a config returned by observe.spec_config.
• Returns:
• JSON string: {"valid": true} or {"valid": false, "invalid_tabs": [...], ...}.
• Example:

    CALL airlock.admin.validate_spec(PARSE_JSON('{"core_config": {"spec_name": "x", "owner_role": "analyst"}}'));
    CALL airlock.admin.validate_spec(:config_var, reuse_spec => TRUE);
    CALL airlock.admin.validate_spec(:config_var, FALSE, TRUE);  -- allow_existing_spec for round-trip

#2.10. Shared schema & FK validation

These procedures support customer-defined shared views and foreign-key-like validation of spec data against those views. The schema AIRLOCK_DATA.SHARED exists in the customer's account; the app does not grant USAGE or CREATE VIEW. The customer grants those privileges (e.g. via SECURITYADMIN). See Shared schema grants.

• airlock.admin.create_shared_schema()
• Description: Creates the schema AIRLOCK_DATA.SHARED if it does not exist. Idempotent. Does not grant USAGE or CREATE VIEW; the customer performs grants (see Shared schema grants).
• Parameters: None.
• Returns: VARCHAR — summary message.
• Example:

    CALL airlock.admin.create_shared_schema();

• Shared-view references
• Description: Views in AIRLOCK_DATA.SHARED are registered through the unified reference API below. Use airlock.admin.register_reference with ref_type => 'shared_view'.

• airlock.admin.register_reference(reference_name VARCHAR, ref_type VARCHAR, schema_name VARCHAR, view_name VARCHAR, spec_name VARCHAR, ref_columns VARIANT)
• Description: Registers a logical FK reference backed by a shared view, a materialized ingestion spec (spec), or a read-only reference spec (reference_spec). References are identified by a stable alias (reference_name) that specs use in FK rules (unless the rule points directly at a reference spec name; see FK rules below).
• Parameters:
• reference_name (required): Logical alias used in spec configs (e.g. employees_ref, ref_employees).
• ref_type (required): 'shared_view', 'spec', or 'reference_spec'.
• schema_name / view_name (for shared_view): identify AIRLOCK_DATA.<schema_name>.<view_name> (e.g. SHARED / employees).
• spec_name (for spec): backing ingestion spec name; FK validation reads distinct values from AIRLOCK_DATA.ACTIVE.T_<spec_name>.
• spec_name (for reference_spec): backing reference spec name (core.specs with is_reference_spec=true). view_name stores the default object_key when the backing spec has multiple reference_config.object_paths (optional when exactly one path exists).
• ref_columns (required): Single FK column name (VARIANT array with one element is accepted).
• Returns: TABLE(REFERENCE_NAME VARCHAR, REF_TYPE VARCHAR, REF_COLUMNS VARIANT, STATUS VARCHAR, CODE VARCHAR, MESSAGE VARCHAR, ISSUES VARIANT).
• CODE is stable for agents; examples include ALLOW, REF_TYPE_INVALID, REF_COLUMNS_REQUIRED, MATERIALIZED_TABLE_REQUIRED, REFERENCE_SAVE_FAILED, and REFERENCE_SPEC_VALIDATION_FAILED.

• airlock.admin.revalidate_reference(reference_name VARCHAR)
• Description: Smoke-check that a registered reference is still readable (SELECT 1 … LIMIT 1). For reference_spec, probes every object_path on the backing spec.
• Returns: TABLE(REFERENCE_NAME VARCHAR, STATUS VARCHAR, CODE VARCHAR, MESSAGE VARCHAR, ISSUES VARIANT).
• CODE is stable for agents; examples include ALLOW, REFERENCE_NOT_FOUND, REF_TYPE_INVALID, and REFERENCE_TARGET_NOT_READABLE.

• airlock.admin.unregister_reference(reference_name VARCHAR)
• Description: Removes a logical reference and any role assignments for it. Does not drop the underlying view or spec.
• Returns: TABLE(REFERENCE_NAME VARCHAR, STATUS VARCHAR, CODE VARCHAR, MESSAGE VARCHAR, ISSUES VARIANT).
• CODE is stable for agents; examples include ALLOW, REFERENCE_NAME_REQUIRED, and REFERENCE_DELETE_FAILED.

• airlock.observe.references(ref_type VARCHAR)
• Description: Lists registered references. ref_type optional filter ('shared_view', 'spec', or 'reference_spec'); pass NULL to list all.
• Returns: TABLE(REFERENCE_NAME, REF_TYPE, SCHEMA_NAME, VIEW_NAME, SPEC_NAME, REF_COLUMNS, REGISTERED_AT).

• airlock.admin.grant_reference_access(reference_name VARCHAR, role_name_or_pk VARCHAR)
• Description: Grants an Airlock role access to use a reference in FK rules. app_admin is implicitly allowed for all references; assignments extend access to additional roles.
• Returns: TABLE(REFERENCE_NAME, ROLE_PK, ROLE_NAME, STATUS, CODE, MESSAGE, ISSUES).
• CODE is stable for agents; examples include ALLOW, ALREADY_GRANTED, REFERENCE_NOT_FOUND, ROLE_NOT_FOUND, ROLE_NOT_SPEC_ADMIN, and REFERENCE_ACCESS_UPDATE_FAILED.

• airlock.admin.revoke_reference_access(reference_name VARCHAR, role_name_or_pk VARCHAR)
• Description: Revokes an Airlock role’s access to use a reference in FK rules.
• Returns: TABLE(REFERENCE_NAME, ROLE_PK, ROLE_NAME, STATUS, CODE, MESSAGE, ISSUES).
• CODE is stable for agents; examples include ALLOW, NOOP, REFERENCE_NAME_REQUIRED, ROLE_NOT_FOUND, and REFERENCE_ACCESS_UPDATE_FAILED.

• airlock.observe.reference_access(reference_name VARCHAR)
• Description: Lists roles that have been granted access to a reference (not including the implicit app_admin access).
• Returns: TABLE(REFERENCE_NAME, ROLE_PK, ROLE_NAME).

Spec config FK rule (unified): Spec config may include an optional top-level rules array. A rule with "type": "foreign_key" has either "field" (top-level column name) or "field_path" (a JSON path inside a VARIANT column), plus "ref": { "reference": "<reference_name_or_spec>", "column": "<column_name>", "object_key": "<optional>", "scope": "<optional>" }. The reference value may be:

• A core.references.reference_name (shared_view, spec, or reference_spec), or
• The spec_name of a reference spec (is_reference_spec=true) with no separate register_reference row (implicit binding).

For VARIANT payloads, use field_path:

{
  "type": "foreign_key",
  "field_path": {
    "column": "evidence",
    "json_path": "$.employee.username"
  },
  "ref": {
    "reference": "employee_info",
    "column": "employee_username"
  }
}

field_path.column must be a variant column in column_config. field_path.json_path supports a small scalar path subset such as $.employee.username, $.items[0].id, or $['employee-id']. Validation parses the JSON/VARIANT cell, extracts the scalar value, and checks it against the same reference target used by normal top-level FK rules.

For flexible business payloads, use variant_shape to make a variant column first-class without freezing every future attribute as a top-level column:

{
  "type": "variant_shape",
  "field": "expense_details",
  "allowed_root_keys": ["expense", "receipt", "allocation"],
  "paths": [
    {"json_path": "$.expense.business_purpose", "type": "string", "required": true},
    {"json_path": "$.receipt.total", "type": "number", "required": true},
    {"json_path": "$.allocation.cost_center", "type": "string", "required": false}
  ]
}

variant_shape.field must be a variant column. Payloads must be valid JSON objects by default. paths[].json_path uses the same simple JSON path subset as field_path; supported path types are string, integer, number, boolean, date, datetime, uuid, object, and array. allowed_root_keys is intentionally strict: when present, any undeclared root key is rejected so business-process drift is reviewed by editing the spec rule, not hidden in uploads.

When the target has multiple reference_config.object_paths, set ref.object_key to the logical key (same as for agent.select_reference_data). FK validation defaults to ref.scope = "global", which runs against the full reference domain. For reference_spec targets, set ref.scope = "caller_visible" to apply that reference spec's row_filters_by_role using the caller's effective Airlock roles, ref.scope = "role_lens" to validate against the explicit in_app_role lens, or ref.scope = "username_lens" to validate against rows where reference_config.row_filter_by_username.column equals trusted CURRENT_USER(). For spec targets, scoped FK validation requires the backing spec to be materialized (core_config.materialize_as_table=true) and to define the matching read_policy.row_filters_by_role or read_policy.row_filter_by_username; Airlock applies that policy to AIRLOCK_DATA.ACTIVE.T_<spec_name> during validation.

Reference specs may omit column_config for legacy/API-minimal setups. When column_config is provided, Airlock validates it and requires every reference_config.object_paths[].readable_columns[] entry to be represented there, so the documented shape matches the selectable reference data.

• For ref_type = 'shared_view': AIRLOCK_DATA.SHARED.<view_name>.<column>.
• For ref_type = 'spec': AIRLOCK_DATA.ACTIVE.T_<spec_name>.<column>; optional scoped validation uses the target spec's read_policy.row_filters_by_role or read_policy.row_filter_by_username.
• For reference specs (implicit or ref_type = 'reference_spec'): FQ object from reference_config.object_paths.

Spec row read policy: Ingestion specs may include optional top-level read_policy.row_filters_by_role using the same vetted filter shape as reference specs, and/or read_policy.row_filter_by_username with a trusted username column. This requires core_config.materialize_as_table=true. airlock.agent.select_my_files then reads from the materialized table and appends the caller's role and username predicates instead of reading raw stage files. This is the recommended model for specs such as dept_account_map, where HR should only see HR rows and finance should only see finance rows, or user-scoped maps where each caller sees only their rows.

Legacy rules using { "schema": "SHARED", "view": "<view_name>", "column": "<column_name>" } are rejected at validation time; use ref.reference + register_reference.

---

#3. Agent Schema (airlock.agent.*)

Agent procedures are available to both app_agent and app_admin application roles and are access‑controlled by in‑app roles and assignments.

#3.1. Introspection & Help

• airlock.agent.help(procedure_name VARCHAR DEFAULT NULL)
• Description: Returns help text for agent procedures.
• Parameters:
• procedure_name (optional): specific procedure; NULL = overview.
• Returns: TABLE(LINE VARCHAR).
• Example:

    CALL airlock.agent.help();
    CALL airlock.agent.help('list_my_specs');

• airlock.agent.documentation(output_format VARCHAR DEFAULT 'markdown', doc_key VARCHAR DEFAULT 'api_v1', content_mode VARCHAR DEFAULT 'TOC', section_ids VARCHAR DEFAULT NULL)
• Description: Returns the bundled copy of this stored procedure reference for the installed app version as TABLE(PAYLOAD VARIANT) — structured objects (JSON-compatible), not markdown as the column type. Default content_mode is TOC: first row is kind: documentation_manifest with agent_instructions, content_hash, and etag, then one row per ATX heading (levels 2–6), each heading PAYLOAD including e.g. kind: toc_entry, level, section_id, title, plus schema_version. Every TOC row has a section_id; unnumbered headings use slug ids. PROCEDURES: first row is kind: procedure_index_manifest, followed by kind: procedure_entry rows with procedure name, schema, signature, arguments, and section id. FULL: one row with kind: full, agent_instructions, suggested_filename, content_hash, etag, and body (UTF‑8 markdown). SECTION: one row with kind: section_bundle, agent_instructions, and sections: array of {section_id, body}. CHUNK: multiple smaller kind: "chunk" rows with chunk_index, chunk_count, char_start, char_end, content_hash, and body.
• Parameters:
• output_format (optional): 'markdown', 'md', or 'text' (same bundled source today).
• doc_key (optional): document id; only 'api_v1' is supported.
• content_mode (optional): TOC (default), PROCEDURES, FULL, SECTION, or CHUNK (aliases accepted; case-insensitive).
• section_ids (optional): required for SECTION — comma‑separated TOC section_id values, such as '3', '4.8', '3,4.8', or a slug id like 'guidance-for-ai-agents-and-automation'. Optional for CHUNK — blank/all returns all chunks; '1', '1-3', or 'offset=0,limit=12000' selects a subset.
• Returns: TABLE(PAYLOAD VARIANT) — parse each PAYLOAD in the client; error rows use kind: error with code / message.
• Examples:

    CALL airlock.agent.documentation();
    CALL airlock.agent.documentation(CONTENT_MODE => 'PROCEDURES');
    CALL airlock.agent.documentation(CONTENT_MODE => 'FULL');
    CALL airlock.agent.documentation('markdown', 'api_v1', 'SECTION', '4.8');
    CALL airlock.agent.documentation('markdown', 'api_v1', 'CHUNK', '1-3');

#3.2. Roles & Assignments

• airlock.agent.list_my_roles()
• Description: Lists in‑app roles available to the current user (excluding app_admin).
• Returns:
• ROLE_NAME VARCHAR
• DESCRIPTION VARCHAR
• MANAGED_BY_ROLE VARCHAR
• IS_SPEC_ADMIN BOOLEAN
• IS_LOCKED BOOLEAN
• Example:

    CALL airlock.agent.list_my_roles();

• airlock.agent.list_my_assignments(username VARCHAR DEFAULT NULL, rolename VARCHAR DEFAULT NULL)
• Description: Lists assignments the caller can manage (their roles + child roles).
• Parameters:
• username (optional): filter by user; supports comma‑separated list.
• rolename (optional): filter by role; supports comma‑separated list.
• Returns:
• ASSIGNMENT_NAME VARCHAR
• DESCRIPTION VARCHAR
• USER_ID VARCHAR
• ASSIGNED_ROLE VARCHAR
• EFFECTIVE_FROM TIMESTAMP_LTZ
• EFFECTIVE_TO TIMESTAMP_LTZ
• IS_LOCKED BOOLEAN
• Example:

    CALL airlock.agent.list_my_assignments();
    CALL airlock.agent.list_my_assignments('jane.doe');
    CALL airlock.agent.list_my_assignments(rolename => 'analyst');

• airlock.agent.create_delegation(delegation_descriptor VARIANT, validate_only BOOLEAN DEFAULT FALSE)
• Description: Creates a self-service user-to-agent delegation for CURRENT_USER(). The caller is always the principal/on-behalf-of user. If delegation_descriptor.principal_user is supplied, it must match CURRENT_USER(). The caller must already have write access to the spec, and the spec must enable delegation_policy.
• Guardrail: Delegates cannot create second-order delegations for someone else. For example, if csmith has a grant to act for asmith, csmith cannot call this procedure to grant Charlie access for asmith.
• Admin boundary: Delegation action names are airlock.agent.* procedure actions only. airlock.admin.* procedures are never delegable through on_behalf_of_user; admin procedures remain direct control-plane authority granted by Snowflake application role.
• Runtime model: Delegations are user-level grants. A delegate's own Airlock role assignments do not expand or restrict delegated authority; Airlock re-checks the principal user's current spec access when a delegated action runs. If a delegation has path_scope, the requested staged path or inline path_scope must resolve to that same scope.
• Spec policy validation: admin.validate_spec rejects invalid delegation_policy config before create/edit. Policy action lists must be arrays of known delegable airlock.agent.* actions; admin action names are rejected; workflow-step action lists may only narrow the global policy and must reference configured workflow steps.
• Common descriptor fields:
• actor_user: agent/service account user that may act.
• spec_name: spec where the grant applies.
• allowed_actions: subset allowed by the spec's delegation_policy, such as validate_data, load_data, add_attachment, replace_attachment, or edit_file_workflow. Include validate_data whenever load_data is allowed, because loads validate before writing.
• Optional: delegation_id, path_scope, duration_days, comment, replace_existing.
• Returns: TABLE(DELEGATION_ID, STATUS, IS_CREATED, VALIDATION, CODE, MESSAGE, ISSUES)
• Branch on STATUS and CODE. MESSAGE and ISSUES are top-level for agents; VALIDATION keeps compact diagnostics.
• Stable CODE examples: CREATED, VALIDATE_ONLY, DELEGATION_DISABLED, DELEGATION_ACTION_NOT_ALLOWED, DELEGATION_PRINCIPAL_SPEC_ACCESS_DENIED, DELEGATION_EXISTS.
• Example:

    CALL airlock.agent.create_delegation(
      PARSE_JSON('{"actor_user":"csmith","spec_name":"demo_employee_reimbursements","allowed_actions":["validate_data","load_data","add_attachment"],"duration_days":30}'),
      FALSE
    );

• airlock.agent.list_my_delegations(direction VARCHAR DEFAULT 'both', spec_name VARCHAR DEFAULT NULL, include_inactive BOOLEAN DEFAULT FALSE)
• Description: Lists user-to-agent delegations involving CURRENT_USER(). Use direction => 'received' for delegations where the caller is the actor/delegate, direction => 'granted' for delegations where another actor can act for the caller, or both for both sides.
• Parameters:
• direction (optional, default both): received, granted, or both.
• spec_name (optional): filter to one spec.
• include_inactive (optional, default FALSE): when FALSE, returns only currently active delegations. Set TRUE to include future, expired, or revoked delegations for acknowledgement and audit.
• Returns:
• DIRECTION VARCHAR, DELEGATION_ID VARCHAR, PRINCIPAL_USER VARCHAR, ACTOR_USER VARCHAR
• ACTOR_AIRLOCK_ROLE VARCHAR (legacy metadata; not authorization), SPEC_NAME VARCHAR, ALLOWED_ACTIONS VARIANT
• PATH_SCOPE VARCHAR, EFFECTIVE_FROM TIMESTAMP_LTZ, EFFECTIVE_TO TIMESTAMP_LTZ
• IS_ACTIVE BOOLEAN, COMMENT VARCHAR, ACTION_CONTEXT VARIANT
• Agent guidance: For rows where DIRECTION = 'received' and IS_ACTIVE = TRUE, pass PRINCIPAL_USER as on_behalf_of_user to delegated agent procedures such as validate_data, load_data, add_attachment, replace_attachment, or edit_file_workflow. For inline validate_data / load_data, omit path, in_app_role, and path_scope; Airlock resolves the principal's folder/lens. For follow-up add_attachment or replace_attachment, pass the file_path and file_filename returned by the delegated load plus the same on_behalf_of_user. If a follow-up delegated mutation omits on_behalf_of_user, Airlock treats it as a direct actor call and evaluates the actor's own path access. When load_data includes attachment_content_base64, that call already registers the first attachment; use a distinct attachment_tag for extra evidence added later. replace_attachment and edit_file_workflow are explicit actions: the spec delegation_policy and active grant must both allow them, and workflow movement must be allowed at the file's current workflow step. A delegated submit-to-Submitted experience should call load_data first, inspect the returned/current workflow state, and call edit_file_workflow(action => 'advance') with the same on_behalf_of_user only when the file is still Draft and workflow movement is delegated. It should report policy limits instead of trying to encode workflow state in file data. Pass DELEGATION_ID as delegation_id only if Airlock reports an ambiguous delegation. Use include_inactive => TRUE only to explain future or no-longer-effective delegations; inactive rows do not authorize work.
• Demo note: After make demo, the csmith demo user can run CALL <APP_NAME>.AGENT.LIST_MY_DELEGATIONS('received', 'demo_employee_reimbursements'); and discover demo_delegation_csmith_for_asmith_reimbursements.
• Example:

    CALL airlock.agent.list_my_delegations('received');
    CALL airlock.agent.list_my_delegations('granted', 'demo_employee_reimbursements');
    CALL airlock.agent.list_my_delegations(direction => 'received', include_inactive => TRUE);

• airlock.agent.get_my_license_seat()
• Description: Read-only snapshot for CURRENT_USER: whether they hold a named license, SEAT_CEILING, AUTO_ASSIGN_ENABLED, and a short MESSAGE. Does not claim a seat; use check_my_license_seat / claim_license_seat() to gate or self-assign.
• Returns: TABLE(HAS_SEAT, SEAT_CEILING, AUTO_ASSIGN_ENABLED, MESSAGE)
• Example:

    CALL airlock.agent.get_my_license_seat();

• airlock.agent.get_my_access_context()
• Description: Read-only snapshot for CURRENT_USER: named-license status, compiled access version, assignment-derived role count, and accessible spec count from Airlock's compiled user/spec access index. Does not claim a license, create per-user guest path keys, or use app_admin session activation. DETAILS includes read_only, policy_seeded, access_index_available, billing_status, billing_code, and license_capacity_available so callers can quickly branch on billing/access readiness without running a mutating license procedure.
• Returns: TABLE(USER_ID, HAS_ACTIVE_SEAT, SEAT_CEILING, AUTO_ASSIGN_ENABLED, SEATS_ASSIGNED, SEATS_FREE, ACCESS_VERSION, ROLE_COUNT, ACCESSIBLE_SPEC_COUNT, STATUS, CODE, MESSAGE, DETAILS)
• Example:

    CALL airlock.agent.get_my_access_context();

#3.3. Specs & Files

• airlock.agent.list_my_specs(in_app_role VARCHAR DEFAULT NULL, include_managed_roles BOOLEAN DEFAULT TRUE)
• Description: Lists specs accessible to the current user (owner and published guest specs).
• Parameters:
• in_app_role (optional): session “lens” role; must be a role the caller holds. When set, access is evaluated for that role and roles it manages in managed_by_role (intersected with the caller’s assignment-derived roles), so guest specs invited to a subordinate role still appear under a manager lens. When NULL, all assignment-derived roles (including descendants) are used.
• include_managed_roles (optional, default TRUE): when FALSE, use only the exact in_app_role lens instead of managed descendants.
• Returns:
• SPEC_NAME VARCHAR, SPEC_ALIAS VARCHAR, DESCRIPTION VARCHAR, OWNER_ROLE VARCHAR,
• ACCESS_TYPE VARCHAR, GUEST_ROLE_NAME VARCHAR, RESTRICTED_PATH VARCHAR, IS_MULTI_ROLE BOOLEAN
• IS_REFERENCE_SPEC BOOLEAN
• Example:

    CALL airlock.agent.list_my_specs();
    CALL airlock.agent.list_my_specs('budget_analyst');

• airlock.agent.create_spec_from_template(template_name VARCHAR, new_spec_name VARCHAR, spec_alias VARCHAR DEFAULT NULL, spec_config_overrides VARIANT DEFAULT NULL)
• Description: Creates a new spec from a spec template that is assigned to at least one of the caller’s Airlock roles (core.spec_template_assignments) or marked is_public. Requires a named license seat like other agent.* calls. Overrides honor template_rules.field_access (locked vs editable paths). When the template defines template_rules.forced_owner_role, new specs use that owner (e.g. central app_admin governance) even though the caller could not choose that owner on airlock.admin.create_spec alone—this is intentional admin-delegated behavior. The installed public default_spec_draft_template forces owner role app_admin and unpublished status so agents can draft specs safely for later admin publication. If forced_owner_role is omitted while the template’s embedded owner is outside the caller’s assignment subtree, creation fails with an owner access error—see Guidance for AI agents and automation above.
• Parameters:
• template_name (required): assigned or public spec template name.
• new_spec_name (required): new spec name for the clone (canonicalized).
• spec_alias (optional): optional spec alias.
• spec_config_overrides (optional): overrides for editable fields (dot-path keys).
• Returns:
• SPEC_NAME VARCHAR
• STATUS VARCHAR
• IS_CREATED BOOLEAN
• VALIDATION VARIANT
• CODE VARCHAR
• MESSAGE VARCHAR
• ISSUES VARIANT
• Branch on STATUS and CODE; use VALIDATION only for detailed form feedback. Common CODE values include CREATED, TEMPLATE_ACCESS_DENIED, TEMPLATE_INACTIVE, and SPEC_TEMPLATE_LOOKUP_FAILED.
• Example:

    CALL airlock.agent.create_spec_from_template(
      'finance_spec_template',
      'fin_budget_requests',
      spec_alias => 'FIN Budget Requests',
      spec_config_overrides => PARSE_JSON('{"core_config.description": "FY26"}')
    );

• airlock.agent.list_my_files(spec_name VARCHAR, in_app_role VARCHAR DEFAULT NULL)
• Description: Lists files in a spec the caller can access.
• Parameters:
• spec_name (required)
• in_app_role (optional): restrict to a role directory for multi‑role specs.
• Returns: Snowflake LIST columns (name, size, md5, last_modified).
• Example:

    CALL airlock.agent.list_my_files('budget_requests');
    CALL airlock.agent.list_my_files('budget_requests', 'budget_analyst');

• airlock.agent.spec_state(spec_name VARCHAR, in_app_role VARCHAR DEFAULT NULL, include_managed_roles BOOLEAN DEFAULT TRUE)
• Description: Returns compact change tokens for one accessible spec without retrieving its governed rows. Poll STATE_TOKEN; call describe_spec, list_my_files, or a governed read procedure only after it changes. Poll list_my_work independently because due dates, expectation windows, and exceptions can change with time without rotating a spec token.
• Authorization: Uses the same Airlock role, managed-role, path, and observer workflow filters as file discovery. DATASET_TOKEN, ACTIVE_FILE_COUNT, and STATE_TOKEN are computed only from files visible through that role lens, so hidden paths cannot be inferred from token changes. Returns no row when access is denied or the spec no longer exists.
• Returns: SPEC_NAME, immutable active spec/data version identifiers and config hash, CONTRACT_TOKEN, ACCESS_SCOPE_TOKEN, visible DATASET_TOKEN, combined STATE_TOKEN, ACTIVE_FILE_COUNT, and LAST_CHANGED_AT.
• Example:

    CALL airlock.agent.spec_state('budget_requests');
    CALL airlock.agent.spec_state('budget_requests', 'budget_reviewer', FALSE);

• airlock.agent.spec_states(spec_names ARRAY, in_app_role VARCHAR DEFAULT NULL, include_managed_roles BOOLEAN DEFAULT TRUE)
• Description: Returns the same state-token rows as agent.spec_state for up to 100 unique, non-empty names while sharing license, role expansion, and compiled-access work. Prefer it when one agent polls several known specs.
• Authorization: Applies one Airlock role lens to the batch. Input order is preserved after case-insensitive deduplication. Missing and inaccessible specs are omitted without disclosing which condition applied; failures while calculating state for an authorized spec remain procedure errors.
• Returns: Zero or one row per accessible requested spec, with the same columns and token semantics as agent.spec_state.
• Example:

    CALL airlock.agent.spec_states(
      ARRAY_CONSTRUCT('budget_requests', 'finance'),
      'budget_reviewer',
      FALSE
    );

• airlock.agent.file_state(spec_name VARCHAR, path VARCHAR, filename VARCHAR, in_app_role VARCHAR DEFAULT NULL, include_managed_roles BOOLEAN DEFAULT TRUE)
• Description: Returns the UUID and compact mutable state for one accessible active logical file without retrieving its data or attachment bytes. Compare FILE_STATE_ID; it rotates for data replacement, workflow changes, attachment changes, exact source-reference changes, and retained version-history changes.
• Authorization: Uses the same role/path/observer filters as spec_state and returns no row when access is denied or the file is inactive, removed, or deleted.
• Returns: SPEC_NAME, PATH, FILENAME, UPLOAD_ID, FILE_STATE_ID, WORKFLOW_STEP, WORKFLOW_STATUS, ATTACHMENT_COUNT, REFERENCE_COUNT, and LAST_CHANGED_AT.
• Example:

    CALL airlock.agent.file_state('budget_requests', 'finadmin', 'fy27_budget');

• airlock.agent.list_my_work(spec_name VARCHAR DEFAULT NULL, in_app_role VARCHAR DEFAULT NULL, include_managed_roles BOOLEAN DEFAULT TRUE)
• Description: Lists the caller's unified operational inbox through their Airlock role lens. Includes files to submit or review, read-only observer items, and unmet, upcoming, overdue, or excepted expectation work. AVAILABLE_ACTIONS contains exact workflow actions such as advance or return only when the caller is authorized to perform them. Met expectations are omitted. Delegated file submission does not itself add the principal's work; delegate-only agents should use list_my_delegations plus delegated procedure results for on-behalf submission.
• Returns: WORK_ITEM_ID, SPEC_NAME, WORK_SOURCE, WORK_ITEM_TYPE, WORK_STATUS, SUMMARY, PATH, FILENAME, DUE_AT, DAYS_UNTIL_DUE, AVAILABLE_ACTIONS, ACCESS_REASON, IS_STRICT, UPDATED_AT, DETAILS.
• Example:

    CALL airlock.agent.list_my_work();
    CALL airlock.agent.list_my_work('budget_requests', 'budget_reviewer', FALSE);

• Cross-spec source references (required evidence and immutable lineage) Admins declare allowed downstream→upstream pairs with airlock.admin.create_spec_source_link. link_options.min_count > 0 makes that source spec required before each downstream workflow advance; max_count limits pins and required_workflow_status requires the cited source to remain in that state when advancing. Users load the downstream Draft, discover eligible upstream files with airlock.agent.list_eligible_source_files, pin exact manifest rows with airlock.agent.add_file_reference, and inspect pins with airlock.agent.list_file_references. Missing or no-longer-qualifying evidence returns SOURCE_REFERENCE_REQUIRED; a policy lookup failure returns SOURCE_REFERENCE_CHECK_FAILED. Active pins also protect cited source files from explicit deletion and retention purge. remove_file_reference remains available for repair or optional evidence, but a later advance will fail if removal puts the file below a required minimum.
• create_spec_source_link returns LINK_PK, DOWNSTREAM_SPEC_NAME, SOURCE_SPEC_NAME, STATUS, IS_CREATED, VALIDATION, CODE, MESSAGE, ISSUES.
• disable_spec_source_link returns LINK_PK, DOWNSTREAM_SPEC_NAME, SOURCE_SPEC_NAME, STATUS, IS_UPDATED, VALIDATION, CODE, MESSAGE, ISSUES.
• add_file_reference returns REFERENCE_PK, DOWNSTREAM_SPEC_NAME, SOURCE_SPEC_NAME, STATUS, IS_CREATED, VALIDATION, CODE, MESSAGE, ISSUES.
• remove_file_reference returns REFERENCE_PK, DOWNSTREAM_SPEC_NAME, STATUS, IS_UPDATED, VALIDATION, CODE, MESSAGE, ISSUES.
• Branch on STATUS and stable CODE values such as CREATED, UPDATED, ALLOW, VALIDATE_ONLY, ACCESS_DENIED, SPEC_NOT_FOUND, LINK_EXISTS, MANIFEST_NOT_FOUND, LINK_NOT_FOUND, WORKFLOW_MISMATCH, MAX_REFERENCES, REFERENCE_EXISTS, INSERT_FAILED, NOT_FOUND, NOOP, and UPDATE_FAILED. Workflow advancement can additionally return SOURCE_REFERENCE_REQUIRED or SOURCE_REFERENCE_CHECK_FAILED. MESSAGE and ISSUES are top-level for agents; VALIDATION keeps compact diagnostics.

• airlock.agent.describe_spec(spec_name VARCHAR, in_app_role VARCHAR DEFAULT NULL, include_managed_roles BOOLEAN DEFAULT TRUE)
• Description: Returns a safe descriptor for a spec the caller can access: column_config (field names, types, tests such as not_null), file_rules (file format, delimiters, etc.), attachment_policy (attachments enabled, max count, allowed extensions, required), minimal core_config / file_workflow (step names and reviewer actions), ACCESSIBLE_PATHS (array of {path_scope, access_level}), and ACCESS_SUMMARY (owner, guest, reviewer, observer, attachment, and reference RLS explanation). Only paths this caller may use are included (other guests and their paths are not shown). Use ACCESSIBLE_PATHS to choose the path_scope to pass to load_data (e.g. public/full_access for write, public/read_access for read-only). Observer roles get read-only visibility for matching path/workflow-state filters. Access is enforced; returns no row if the caller has no access.
• Parameters:
• spec_name (required)
• in_app_role (optional): lens role for multi‑role specs.
• include_managed_roles (optional, default TRUE): when FALSE, use only the exact in_app_role lens instead of managed descendants.
• Returns: One row: SPEC_NAME, SPEC_ALIAS, DESCRIPTION, CORE_CONFIG (VARIANT), COLUMN_CONFIG (VARIANT), FILE_RULES (VARIANT), ATTACHMENT_POLICY (VARIANT), FILE_WORKFLOW (VARIANT), ACCESSIBLE_PATHS (VARIANT array of {path_scope, access_level}).
• Example:

    CALL airlock.agent.describe_spec('incoming_line_items');
    CALL airlock.agent.describe_spec('incoming_line_items', 'automation_user');  -- ACCESSIBLE_PATHS shows e.g. [{"path_scope": "public/full_access", "access_level": "full_access"}]

• airlock.agent.validate_data(spec_name VARCHAR, path VARCHAR DEFAULT NULL, file_content VARCHAR DEFAULT NULL, in_app_role VARCHAR DEFAULT NULL, path_scope VARCHAR DEFAULT NULL, include_managed_roles BOOLEAN DEFAULT TRUE, on_behalf_of_user VARCHAR DEFAULT NULL, delegation_id VARCHAR DEFAULT NULL)
• Description: Validates a file for a spec using assignment-based authorization. Same semantics as airlock.admin.validate_data; caller must have write access to the spec/path (owner or guest with write on that path). Exactly one of path (staged file) or file_content (inline CSV string) required. Prefer named arguments. For normal delegated inline validation, pass file_content and on_behalf_of_user; Airlock resolves the principal user's folder/lens and matching delegation. If role-lens FK/reference rules require an explicit role, pass the principal's Airlock role as in_app_role; Airlock checks it against the principal's assignments, not the actor's. Use delegation_id only if more than one active grant matches. Inline CSV validation includes FK/reference rules. Direct calls use the caller's Airlock role context; delegated calls use the principal user's resolved lens.
• Returns: SPEC_NAME, PATH, STATUS, IS_VALID, ISSUE_COUNT, ISSUES (VARIANT), CODE. CODE is VALID on success; otherwise it is the first error/warning issue code. Delegation denials return STATUS = 'error' with CODE and ISSUES[0].code set to a stable delegation code such as DELEGATION_NOT_FOUND, DELEGATION_ACTION_NOT_ALLOWED, or INVALID_DELEGATION_POLICY.
• Example:

    CALL airlock.agent.validate_data(
      spec_name => 'incoming_line_items',
      file_content => 'record_id,category,attachment_filename\nINV-2025-001,operations,invoice_scan.pdf',
      in_app_role => 'automation_user',
      path_scope => 'public/full_access'
    );

• airlock.agent.load_data(spec_name VARCHAR, path VARCHAR DEFAULT NULL, file_content VARCHAR DEFAULT NULL, filename VARCHAR DEFAULT NULL, in_app_role VARCHAR DEFAULT NULL, path_scope VARCHAR DEFAULT NULL, attachment_content_base64 VARCHAR DEFAULT NULL, attachment_filename VARCHAR DEFAULT NULL, include_managed_roles BOOLEAN DEFAULT TRUE, on_behalf_of_user VARCHAR DEFAULT NULL, delegation_id VARCHAR DEFAULT NULL)
• Description: Loads a file for a spec using assignment-based authorization. Same semantics as airlock.admin.load_data; caller must have write access to the spec/path. Exactly one of path (staged file) or file_content (inline CSV) required. For staged paths, Airlock requires an exact object digest before validation and verifies it again before the manifest transaction. A missing digest returns STAGED_FILE_HASH_UNAVAILABLE; replacement during validation returns STAGED_FILE_CHANGED_RETRY. Neither outcome writes validation evidence. Prefer named arguments. For normal delegated inline loads, pass file_content, filename, any attachment fields, and on_behalf_of_user; Airlock resolves the principal user's folder/lens and matching delegation. If validation needs a role-lens FK/reference check, pass the principal's Airlock role as in_app_role. Use delegation_id only if more than one active grant matches. For non-delegated guest-enabled specs with shared folders, pass path_scope (e.g. 'public/full_access', 'public/append_access') when needed to disambiguate the target scope shown by describe_spec. Active Expectations are evaluated before the manifest row is written. Strict unmet expectations reject the load with EXPECTATION_BLOCKED; non-strict unmet expectations return EXPECTATION_WARNING but still load. For no-workflow specs, the load target is became_active and the visible current state is Active. Optional attachment: pass attachment_content_base64 (+ optional attachment_filename) to register one attachment for the loaded file in the same call. If you later call add_attachment for the same file, use the returned PATH / FILENAME and a distinct attachment_tag for extra evidence. When the spec has attachment_policy.attachment_required=true, an attachment is required in the same call: if attachment_content_base64 is omitted or empty, the load is rejected (no file is written to the manifest) and the procedure returns status error with issue code ATTACHMENT_REQUIRED.
• Returns: SPEC_NAME, PATH, FILENAME, STATUS, IS_LOADED, ROW_COUNT, ISSUE_COUNT, ISSUES, CODE. CODE is LOADED on success; otherwise it is the first error/warning issue code. Delegation denials return STATUS = 'error' with CODE and ISSUES[0].code set to a stable delegation code. Expectation findings use EXPECTATION_BLOCKED for strict failures and EXPECTATION_WARNING for non-strict warnings.
• Example (create file and attach in one call):

    CALL airlock.agent.load_data(
      spec_name => 'incoming_line_items',
      file_content => 'record_id,category,attachment_filename\nINV-2025-001,operations,invoice_scan.pdf',
      filename => 'INV-2025-001',
      in_app_role => 'automation_user',
      path_scope => 'public/full_access',
      attachment_content_base64 => '<base64_encoded_image>',
      attachment_filename => 'invoice_scan.pdf'
    );

• airlock.agent.select_my_files(spec_name VARCHAR, search_string VARCHAR DEFAULT NULL, regex_pattern VARCHAR DEFAULT NULL, in_app_role VARCHAR DEFAULT NULL)
• Description: Reads spec data the caller can access, with the same fuzzy/regex semantics as airlock.admin.select_files. For specs with read_policy.row_filters_by_role or read_policy.row_filter_by_username, reads from AIRLOCK_DATA.ACTIVE.T_<spec_name> and applies the caller's effective-role and/or username row filters; raw stage reads are not used for those row-filtered specs.
• Parameters:
• spec_name (required)
• search_string (optional): fuzzy match in path
• regex_pattern (optional): full regex when search_string is NULL/empty
• in_app_role (optional): restrict to a role directory
• Returns: Same as airlock.admin.select_files (configured data columns plus source_file, file_last_modified). On normal failure: STATUS, CODE, MESSAGE, ISSUES.
• Example:

    CALL airlock.agent.select_my_files('budget_requests');
    CALL airlock.agent.select_my_files('budget_requests', 'report_2025');
    CALL airlock.agent.select_my_files(spec_name => 'budget_requests', in_app_role => 'budget_analyst');

• airlock.agent.select_reference_data(spec_name VARCHAR, object_key VARCHAR, row_limit NUMBER DEFAULT 500, in_app_role VARCHAR DEFAULT NULL, include_managed_roles BOOLEAN DEFAULT TRUE, row_offset NUMBER DEFAULT 0, record_reference_read_event BOOLEAN DEFAULT TRUE)
• Description: Governed SELECT against a read-only reference spec (core_config.is_reference_spec). Resolves access like describe_spec, then reads reference_config.object_paths for object_key (FQ database_name.schema_name.object_name). Optional reference_config.row_filters_by_role appends vetted AND predicates for the caller’s effective Airlock roles; optional reference_config.row_filter_by_username appends a trusted CURRENT_USER() predicate. row_limit defaults to 500 (max 5000); row_offset defaults to 0 (max 100000) for LIMIT/OFFSET pagination. When record_reference_read_event is TRUE (default), inserts a best-effort REFERENCE_READ row into core.events. When readable_columns is set on the object path, only those columns are selected; otherwise SELECT *; if the reference spec also provides column_config, those readable columns must be documented there. If the spec declares restricted_reference for the object key, this procedure returns RESTRICTED_REFERENCE_DENIED; use agent.get_reference_record. The account must grant the application SELECT on the underlying object.
• Returns: TABLE() — dynamic columns from the target object on success. On normal failure, returns TABLE(STATUS, CODE, MESSAGE, ISSUES) with STATUS='error' so agents can branch on CODE instead of parsing Snowflake exception text.
• Example:

    CALL airlock.agent.select_reference_data('finance_ref', 'gl_entries');
    CALL airlock.agent.select_reference_data(
      spec_name => 'finance_ref',
      object_key => 'gl_entries',
      row_limit => 100,
      in_app_role => 'budget_analyst'
    );
    CALL airlock.agent.select_reference_data(
      spec_name => 'finance_ref',
      object_key => 'gl_entries',
      row_limit => 100,
      in_app_role => 'budget_analyst',
      row_offset => 100,
      record_reference_read_event => FALSE
    );

• airlock.agent.get_reference_record(spec_name VARCHAR, object_key VARCHAR, lookup_value VARCHAR, purpose VARCHAR DEFAULT NULL, in_app_role VARCHAR DEFAULT NULL, include_managed_roles BOOLEAN DEFAULT TRUE, record_reference_read_event BOOLEAN DEFAULT TRUE)
• Description: Governed one-record lookup for a read-only reference spec that declares restricted_reference. Snowflake/upstream systems own masking, hashing, secure views, and projections; Airlock enforces the interaction shape, applies configured reference row filters, checks active action_limit Expectations before returning a restricted record, and logs safe REFERENCE_READ events with hashed lookup/purpose metadata. Restricted-reference events are always recorded because usage limits are enforced from that event ledger; the final Boolean is retained for signature stability.
• Returns: TABLE(STATUS, CODE, MESSAGE, SPEC_NAME, OBJECT_KEY, LOOKUP_KEY, ROW_COUNT, RECORD, ACCESS_CONTEXT, USAGE_CONTEXT, ISSUES). Common codes: OK, NOT_FOUND, NON_UNIQUE_LOOKUP_KEY, RESTRICTED_REFERENCE_DENIED, PURPOSE_REQUIRED, USAGE_LIMIT_BLOCKED, REFERENCE_READ_EVENT_FAILED. USAGE_CONTEXT includes fields such as action_limit_used (5/25) and action_time_period (hour, day, week, month, ever, or a rolling-hours label) when an action-limit Expectation applies.
• Example:

    CALL airlock.agent.get_reference_record(
      spec_name => 'customer_identity',
      object_key => 'customers',
      lookup_value => 'CUST-123',
      purpose => 'support_case:CASE-456',
      in_app_role => 'support_agent'
    );

• airlock.agent.edit_file_workflow(spec_name VARCHAR, path VARCHAR, filename VARCHAR, action VARCHAR, comment VARCHAR DEFAULT NULL, validate_only BOOLEAN DEFAULT FALSE, in_app_role VARCHAR DEFAULT NULL, include_managed_roles BOOLEAN DEFAULT TRUE, on_behalf_of_user VARCHAR DEFAULT NULL, delegation_id VARCHAR DEFAULT NULL)
• Description: Moves file workflow state for files the caller can access. Same actions as airlock.admin.edit_file_workflow (advance, return, archive, restore). Direct calls use the caller's Airlock role and assignment/workflow permissions (owner or guest with manage permission in the current step). Delegated calls pass on_behalf_of_user; Airlock checks the active delegation, principal access, target file path, workflow step policy, and the principal's workflow PDP. The spec delegation_policy and active grant must both allow edit_file_workflow at the current workflow step. Strict Expectations for the target workflow milestone can block the transition unless an active Exception applies. An advance also enforces active source-link minimums and returns SOURCE_REFERENCE_REQUIRED when exact qualifying evidence has not been pinned.
• Parameters:
• spec_name, path, filename, action (required); comment (optional); validate_only (optional, default FALSE).
• in_app_role (optional): lens role; must be in caller’s effective roles; when NULL, current context role is used.
• Returns: Same as admin edit_file_workflow: SPEC_NAME, PATH, FILENAME, STATUS, CODE, MESSAGE, IS_UPDATED, FROM_STEP, TO_STEP, ISSUES, DETAILS. Delegation denials return STATUS = 'error' with a stable delegation code in CODE and ISSUES[0].code.
• Example:

    CALL airlock.agent.edit_file_workflow('budget_requests', 'default', 'report_2025', 'advance', 'Ready for review');
    CALL airlock.agent.edit_file_workflow(spec_name => 'timesheets', path => 'joe', filename => 'joe_2026_05_17', action => 'advance', comment => 'Submitted by agent', on_behalf_of_user => 'joe');

• airlock.agent.add_attachment(spec_name VARCHAR, file_path VARCHAR, file_filename VARCHAR, attachment_stage_path VARCHAR DEFAULT NULL, attachment_content_base64 VARCHAR DEFAULT NULL, attachment_filename VARCHAR DEFAULT NULL, attachment_type VARCHAR DEFAULT 'other', description VARCHAR DEFAULT NULL, in_app_role VARCHAR DEFAULT NULL, include_managed_roles BOOLEAN DEFAULT TRUE, attachment_tag VARCHAR DEFAULT NULL, on_behalf_of_user VARCHAR DEFAULT NULL, delegation_id VARCHAR DEFAULT NULL)
• Description: Adds an attachment to an existing file. Same semantics as airlock.admin.add_attachment with in-procedure authorization (caller must have access to the spec/file and the file must be in a workflow step that allows attachment management). Exactly one of attachment_stage_path or attachment_content_base64 (+ optional attachment_filename) required. For delegated follow-up attachments, pass on_behalf_of_user; Airlock checks the active delegation, principal access, target file path, and workflow step. If on_behalf_of_user is omitted, the call is direct actor work and can correctly fail when file_path belongs to the principal's isolated folder. Use delegation_id only if more than one active grant matches.
• Guest roles (e.g. automation_user): The procedure is granted to app_agent. A user who only has guest access to a spec (e.g. assigned role automation_user with full_access on the spec’s public/full_access folder) can attach files to existing files in that path by calling this procedure with the same file_path/file_filename. Pass in_app_role => 'automation_user' (or the guest role name) so the PDP evaluates access under that role; the file path must be under a directory where that guest has write/update (e.g. public/full_access). For delegated calls, omit in_app_role unless selecting the principal's role lens; delegation never uses the actor's role to expand the principal's authority.
• Returns: SPEC_NAME, FILE_PATH, FILE_FILENAME, ATTACHMENT_ID, ATTACHMENT_STAGE_PATH, STATUS, CODE, MESSAGE, SIZE_BYTES, ISSUES. On workflow denial, CODE = 'ACCESS_DENIED_WORKFLOW_STATE'; on delegation denial, CODE is the stable delegation code.
• Example (guest role attaching to an existing file):

    -- Guest role with write on public/full_access attaches supporting evidence to an existing row file.
    CALL airlock.agent.add_attachment(
      spec_name => 'incoming_line_items',
      file_path => 'public/full_access',
      file_filename => 'INV-2025-001',
      attachment_content_base64 => '<base64_encoded_image>',
      attachment_filename => 'invoice_scan.pdf',
      attachment_type => 'other',
      description => 'Supporting scan',
      in_app_role => 'automation_user'
    );
    CALL airlock.agent.add_attachment(
      spec_name => 'demo_employee_reimbursements',
      file_path => 'asmith',
      file_filename => 'rb_asmith_001',
      attachment_content_base64 => '<base64_encoded_image>',
      attachment_filename => 'receipt.png',
      on_behalf_of_user => 'asmith'
    );

• airlock.agent.replace_attachment(spec_name VARCHAR, file_path VARCHAR, file_filename VARCHAR, attachment_id VARCHAR, attachment_stage_path VARCHAR DEFAULT NULL, attachment_content_base64 VARCHAR DEFAULT NULL, attachment_filename VARCHAR DEFAULT NULL, attachment_type VARCHAR DEFAULT 'other', description VARCHAR DEFAULT NULL, in_app_role VARCHAR DEFAULT NULL, include_managed_roles BOOLEAN DEFAULT TRUE, attachment_tag VARCHAR DEFAULT NULL, on_behalf_of_user VARCHAR DEFAULT NULL, delegation_id VARCHAR DEFAULT NULL)
• Description: Replaces an attachment by attachment_id; same content options as admin (stage or base64). Optional in_app_role lens. Subject to same workflow and access checks as add. For delegated replacement, pass on_behalf_of_user; Airlock checks the active delegation, principal access, target file path, workflow step, and requires replace_attachment in both the spec delegation_policy and the active grant. Use delegation_id only if more than one active grant matches. Replacement is permanent in Airlock, so grant it only for specs where corrected evidence replacement is part of the business process.
• Returns: SPEC_NAME, FILE_PATH, FILE_FILENAME, ATTACHMENT_ID, ATTACHMENT_STAGE_PATH, STATUS, CODE, MESSAGE, SIZE_BYTES, ISSUES. On delegation denial, CODE is the stable delegation code.

• airlock.agent.delete_attachment(spec_name VARCHAR, file_path VARCHAR, file_filename VARCHAR, attachment_id VARCHAR)
• Description: Deletes an attachment by attachment_id for the given file; subject to same workflow/access rules as add/replace. This is a direct-role destructive action, not an on-behalf-of delegation surface in the current contract.

#4. Common Workflows

#4.1 Manage in‑app roles and assignments (admin + user)

• Goal: Define application roles and assign users to them, then let users see their own roles/assignments.
• Typical admin flow:

  -- Create a new role (one JSON object per array element)
  CALL airlock.admin.create_roles([
    PARSE_JSON('{
      "role_name": "budget_analyst",
      "description": "Can view and manage budget specs",
      "is_spec_admin": true
    }')
  ]);

  -- Assign a user to the role (same style as create_assignments)
  CALL airlock.admin.create_assignments([
    PARSE_JSON('{
      "assignment_name": "jane.budget_analyst",
      "user_id": "JANE",
      "assigned_role": "budget_analyst"
    }')
  ]);

  -- Inspect roles/assignments (admin)
  CALL airlock.observe.roles();
  CALL airlock.observe.assignments('JANE');

• Typical user flow:

  -- See which Airlock roles you have
  CALL airlock.agent.list_my_roles();

  -- See which assignments you can manage
  CALL airlock.agent.list_my_assignments();

#4.2 Spec lifecycle: from config to current data

• Goal: Create or update a spec, validate a staged file, load it, and inspect current versions.
• Admin flow:

  -- Validate a spec config (without changing data)
  CALL airlock.admin.validate_spec(:spec_config_json);

  -- Create a new spec (or alter an existing one)
  CALL airlock.admin.create_spec(:spec_config_json);
  -- or
  CALL airlock.admin.alter_spec('budget_requests', :spec_config_json);

  -- Validate a staged file for the spec
  CALL airlock.admin.validate_data(
    'budget_requests',
    'budget_requests/path/abc123/report_2025.csv.gz'
  );

  -- Load the validated file into FILE_MANIFEST
  CALL airlock.admin.load_data(
    'budget_requests',
    'budget_requests/path/abc123/report_2025.csv.gz'
  );

  -- Inspect active file versions
  CALL airlock.observe.file_versions('budget_requests');

• User‑side access:

  -- List specs you can see
  CALL airlock.agent.list_my_specs();

  -- List files you can see for a spec
  CALL airlock.agent.list_my_files('budget_requests');

  -- Read current data for a spec you can access
  CALL airlock.agent.select_my_files('budget_requests');

#4.3 Retention and purge (outdated files)

• Goal: Configure retention for a spec and safely purge outdated files.
• Admin flow:

  -- Create or update a retention policy for a spec
  CALL airlock.admin.create_retention_policy(PARSE_JSON('{
    "policy_name": "Budget Retention",
    "spec_name": "budget_requests",
    "retention_days": 365,
    "retention_min_versions": 2,
    "retention_scope": "non_current"
  }'));

  -- Preview which files are eligible for purge
  CALL airlock.observe.purge_candidates('budget_requests');

  -- Dry-run delete to see what WOULD be removed
  CALL airlock.admin.delete_outdated_files('budget_requests', dry_run => TRUE, limit => 100);

  -- Commit delete (REMOVE from stage + mark history)
  CALL airlock.admin.delete_outdated_files('budget_requests', dry_run => FALSE, limit => 100);

  -- Audit retention removals (see FILE_RETENTION_PURGE in list_events event_types)
  CALL airlock.observe.activity(event_types => 'FILE_RETENTION_PURGE', limit_rows => 200);

#4.4 Auditing activity and history

• Goal: Inspect application activity and historical changes to spec data.
• Admin activity audit:

  -- Recent events
  CALL airlock.observe.activity();

  -- Events for a specific user
  CALL airlock.observe.activity('jane.doe');

  -- Events for a specific Airlock role in a time window
  CALL airlock.observe.activity(
    event_types => 'FILE_UPLOAD,FILE_DELETE,FILE_RETENTION_PURGE',
    in_app_role => 'budget_analyst',
    start_time => '2025-01-01'::TIMESTAMP_LTZ,
    end_time => CURRENT_TIMESTAMP(),
    limit_rows => 500
  );

#4.5. Shared view and FK validation

• Goal: Define a shared view (e.g. employees), register it, and create a spec whose column values must match that view (e.g. email_address must exist in employees.email).
• Prerequisite: Customer has granted USAGE and CREATE VIEW on AIRLOCK_DATA.SHARED to the role that will create views (see Shared schema grants).
• Admin flow:

  -- 1) Create shared schema if needed (idempotent)
  CALL airlock.admin.create_shared_schema();

  -- 2) Customer creates the view (standard SQL; role must have CREATE VIEW on AIRLOCK_DATA.SHARED)
  CREATE OR REPLACE VIEW AIRLOCK_DATA.SHARED.employees AS
  SELECT id, name, email FROM my_db.my_schema.employee_source;

  -- 3) Register the view so Airlock can use it in FK rules
  CALL airlock.admin.register_reference(
    reference_name => 'employees_ref',
    ref_type       => 'shared_view',
    schema_name    => 'SHARED',
    view_name      => 'employees',
    spec_name      => NULL,
    ref_columns    => PARSE_JSON('["email"]')
  );

  -- 4) Create (or alter) a spec with a FK rule in spec_config.rules
  CALL airlock.admin.create_spec(PARSE_JSON('{
    "core_config": {"spec_name": "contacts_shared_fk", "owner_role": "app_admin", ...},
    "column_config": [
      {"name": "full_name", "type": "string", "tests": ["not_null"], "description": ""},
      {"name": "email_address", "type": "string", "tests": ["not_null", "foreign_key"], "description": ""}
    ],
    "rules": [{"type": "foreign_key", "field": "email_address", "ref": {"schema": "SHARED", "view": "employees", "column": "email"}}],
    "file_rules": { ... },
    "guest_access": { ... },
    "sample_data": { ... }
  }'));

  -- 5) Validate and load files (FK validation runs; invalid rows are reported and blocked)
  CALL airlock.admin.validate_data('contacts_shared_fk', 'path/to/file.csv.gz');
  CALL airlock.admin.load_data('contacts_shared_fk', 'path/to/file.csv.gz');

#4.6. File workflow and attachments

• Goal: Move files through workflow steps (advance/return/archive/restore) and attach files to specs or to specific data files.
• File workflow (specs with file_workflow: true):

  -- Admin: move file to next step
  CALL airlock.admin.edit_file_workflow('budget_requests', 'default', 'report_2025', 'advance', 'Ready for review');
  -- User: same, with Airlock role context
  CALL airlock.agent.edit_file_workflow('budget_requests', 'default', 'report_2025', 'advance', 'Ready for review');

• Attachments (specs with attachment_policy.attachments_enabled): add/replace/delete are allowed only when the file is in a workflow step that permits file/attachment management. attachment_tag identifies the logical attachment tag and defaults to default with one active row. On denial, procedures return CODE = 'ACCESS_DENIED_WORKFLOW_STATE'.

  -- Admin: spec-level attachment (e.g. when creating spec + first attachment)
  CALL airlock.admin.attach_to_spec(
    spec_name => 'my_spec',
    attachment_content_base64 => 'YWJj',
    attachment_filename => 'note.txt',
    attachment_type => 'other',
    description => 'Sample'
  );
  -- Admin or user: add attachment to an existing file (path/scope + filename)
  CALL airlock.admin.add_attachment(
    spec_name => 'my_spec',
    file_path => 'default',
    file_filename => 'finance',
    attachment_content_base64 => 'base64...',
    attachment_filename => 'receipt.pdf',
    attachment_type => 'document',
    description => 'Q1 receipt',
    attachment_tag => 'receipt'
  );
  CALL airlock.agent.add_attachment(
    spec_name => 'my_spec',
    file_path => 'default',
    file_filename => 'finance',
    attachment_content_base64 => 'base64...',
    attachment_filename => 'receipt.pdf',
    attachment_type => 'other',
    description => 'Receipt',
    in_app_role => 'my_role',
    attachment_tag => 'receipt'
  );

#4.7. User: create a file and attach a file

• Goal: As an app agent (or an AI agent acting as that user), create a new data file in a spec and optionally attach a binary (e.g. CSV row + PDF receipt) in the same call or in a follow-up call. This is the typical flow for line-of-business submissions where evidence is attached to a logical record.
• Prerequisites: Caller has access to the spec (owner or guest). For guest access on a spec with a shared folder, the caller must use path_scope (e.g. 'public/full_access') when loading the file. Call describe_spec(spec_name, in_app_role, include_managed_roles) and use the ACCESSIBLE_PATHS array to see which path_scope values you may use and their access level (e.g. public/full_access for write); only your paths are listed, not other guests.
• One-call option (recommended when the spec has attachment_required): Pass attachment_content_base64 and optional attachment_filename to load_data so the file and attachment are registered in one procedure call. When the spec has attachment_policy.attachment_required=true, you must provide the attachment in this call; otherwise the load is rejected and no file is uploaded (procedure returns status error, issue code ATTACHMENT_REQUIRED).

  CALL airlock.agent.describe_spec('incoming_line_items', 'automation_user', TRUE);
  CALL airlock.agent.validate_data(
    spec_name => 'incoming_line_items',
    file_content => 'record_id,category,attachment_filename\nINV-2025-001,operations,invoice_scan.pdf',
    in_app_role => 'automation_user',
    path_scope => 'public/full_access'
  );
  CALL airlock.agent.load_data(
    spec_name => 'incoming_line_items',
    file_content => 'record_id,category,attachment_filename\nINV-2025-001,operations,invoice_scan.pdf',
    filename => 'INV-2025-001',
    in_app_role => 'automation_user',
    path_scope => 'public/full_access',
    attachment_content_base64 => '<base64_encoded_image>',
    attachment_filename => 'invoice_scan.pdf'
  );

• Two-step option (load then attach in a second call):

1. Describe the spec → Validate → Load (without attachment params).
2. Attach with add_attachment(spec_name, file_path, file_filename, ...) using the same PATH and FILENAME returned by load_data.

• Reimbursements (CSV + receipt image/PDF) same pattern: use load_data with attachment_content_base64 (and attachment_filename) in the same call, or load then add_attachment with the same file_path/file_filename.
• Submit-to-workflow option: If the business action means "submit for review", first check the spec workflow. Some specs configure Submitted as the initial workflow step, so load_data already creates the reviewer-facing item and Draft is only used for reviewer pushback. If the spec starts in Draft, first load the clean business payload, then call edit_file_workflow(action => 'advance') to move the returned PATH / FILENAME to Submitted. For delegated submissions, pass the same on_behalf_of_user to both calls and require edit_file_workflow in both the spec delegation policy and active grant. Reviewer pushback should return the file to Draft with a workflow comment; the principal then sees the item in My Work and can resubmit before any expectation due date.

#4.8. AI and agent integration

• Goal: Integrate AI assistants or automation so they can submit data and files into Snowflake through Airlock without direct stage or broad write privileges. See Product intent in docs/product_intent.md for the vision (receipts, budget drafts, etc.).
• Procedure surface for agents (all under airlock.agent.*, granted to app_agent):
• Discovery: list_my_roles(), list_my_specs(in_app_role, include_managed_roles), describe_spec(spec_name, in_app_role, include_managed_roles) to learn schema, required fields, and attachment rules.
• Efficient polling: cache spec_state(...).STATE_TOKEN for one spec, or call spec_states(...) to poll up to 100 known specs under one role lens. Compare tokens before retrieving descriptors, file lists, or governed data. Poll list_my_work on its own cadence because time-based expectation and exception changes do not rotate STATE_TOKEN. After selecting one logical file, cache file_state(...).FILE_STATE_ID to detect data replacement, workflow movement, attachment changes, or source-reference changes without fetching the file. Treat an omitted or missing row as loss of access, retirement, deletion, or a nonexistent object and perform normal discovery rather than guessing which occurred.
• Delegation discovery: list_my_delegations('received') to learn active grants where the agent may act for another user; pass include_inactive => TRUE only when explaining future, expired, or revoked grants.
• Author payloads: build records from describe_spec field names and types. Tooling may convert records JSON to CSV for file_content only for flat specs or specs with declared variant columns for nested values. Keep Airlock lifecycle fields such as workflow state, approvals, validation status, and audit metadata out of the source payload.
• Create file (and optional attachment in one call): validate_data(...) then load_data(..., path_scope, attachment_content_base64, attachment_filename). When the spec has attachment_required, pass the attachment in this call or the load fails. For delegated inline loads, omit path_scope unless deliberately targeting a non-default shared scope; Airlock resolves the principal's folder.
• Submit for review: after a successful load, inspect the returned/current workflow state. If the spec configured Submitted as its initial step, the load already created the reviewer-facing item. If the file is in Draft, call edit_file_workflow(..., action => 'advance') to reach Submitted only when workflow policy allows it. For delegated work, keep passing on_behalf_of_user; if the grant does not include workflow movement, report the loaded Draft file instead.
• Attach to file (if not done in load_data, or for additional evidence): add_attachment(spec_name => ..., file_path => ..., file_filename => ..., attachment_content_base64 => ..., attachment_filename => ..., attachment_tag => ...); file_path/file_filename must match the loaded file. For delegated follow-up evidence, also pass on_behalf_of_user; use a distinct attachment_tag when the original load_data already attached the default evidence.
• Read: spec_state(spec_name), spec_states(spec_names), and file_state(spec_name, path, filename) for cheap change detection; list_my_files(spec_name) and select_my_files(spec_name) for governed file discovery/data; for reference specs, select_reference_data(spec_name, object_key, ...).
• Authorization: Procedures enforce Airlock roles and assignments; guest roles (e.g. automation_user) only see and write to specs/paths they are granted (e.g. public/full_access). Pass in_app_role when the user has multiple roles so the PDP evaluates under the correct lens.
• Native MCP: An administrator may opt in to separate read-only core.airlock_agent_mcp and core.airlock_observer_mcp servers with admin.enable_mcp_server. They expose only identity/access context, work, safe spec descriptions, and compact spec/file state. They do not expose governed or administrative mutation, governed row reads, restricted-reference reads, admin.*, or attachment bytes. Except for the non-claiming access-context tool, agent MCP calls retain ordinary named-license enforcement and may auto-assign a license when consumer policy enables it; observer MCP calls do not require or assign named licenses. Snowflake's managed endpoint targets internal scalar VARIANT adapters because its SQL API accepts JSONV2 rather than Arrow result sets from table-returning Python procedures; those adapters retain the same handler-level policy checks and do not change the public API. Before relying on a new PAT-based client, run scripts/run_batch62_mcp_identity_probe.py with two independently issued, role-restricted tokens and require distinct matching USER_ID values.
• External provenance: Buzz/Grok-style channel, thread, event, bot, or public-key ids are authority-neutral provenance. Keep a bounded airlock.external-provenance/v1 envelope in the workspace commitment receipt and optionally place only source plus an opaque correlation_id in QUERY_TAG. Never derive a Snowflake principal, Airlock role, or delegation from workspace metadata.

---

#5. Where to Go Next

• For examples and usage patterns, see:
• Streamlit code under src/airlock/streamlit/lib/**
• Tests under tests/airlock/app_setup_scripts/procedures/scripts/

This document should be kept in sync with:

• src/airlock/app_setup_scripts/procedures/** (SQL procedure definitions)
• src/airlock/app_setup_scripts/procedures/scripts/** (Python handlers)
• src/airlock/app_setup_scripts/procedures/scripts/admin_help.py (admin help text)
• src/airlock/app_setup_scripts/procedures/scripts/user_help.py (agent help text)
• docs/attachments_batch26.md (attachment policy companion; Streamlit behavior)