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 and maintenance procedures
• airlock.user.* – end‑user 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.

---

#Guidance for AI agents and automation

This block is for agents and integrators calling procedures from SQL (Snowsight worksheets, SnowSQL, drivers, CI, 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.user.documentation();

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

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

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

-- Chunk fallback for clients that truncate very large FULL result rows.
CALL airlock.user.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.user.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: list_*, describe_*, and get_* procedures are read-only. 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. 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. If a caller wants the file to arrive in a later workflow state such as Submitted, load first 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.user.* 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. Delegated "submit" flows are still audited as load plus workflow advance, and require both load_data and edit_file_workflow permission when they move past Draft.
• Expectations: description should explain the business reason in human terms. clauses encode the cadence, sequence, or interval; target_milestone names the workflow state being expected; is_strict says whether an unmet expectation can block workflow or load. Non-strict expectations surface as warnings/work items only. For specs without file workflow, the current file slot is Active and the implicit upload milestone is became_active. effective_at / expires_at bound the lifecycle. Discovery should show both the configuration (list_expectations / describe_expectation) and operational work (list_my_expectation_work, including DUE_AT and DAYS_UNTIL_DUE) when explaining what a person needs to do.
• Returns: mutation procedures should expose structured outcomes such as STATUS, IS_CREATED / IS_UPDATED / IS_LOADED, VALIDATION, CODE, MESSAGE, and ISSUES. Agents should branch on stable fields and reason codes, then summarize prose for humans.

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

Snowflake returns a result set (rows/columns), not a workspace file or attachment. user.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.user.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, and ISSUES 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.user.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:

snowsql -c myconn -o output_format=tsv -o header=false -q "CALL airlock.user.documentation(CONTENT_MODE => 'FULL');" > airlock_api_v1.md

Other snowsql options (see Snowflake Configuring SnowSQL) include output_format=plain, csv, or friendly=false. Prefer a Snowflake driver or Snowflake CLI JSON_EXT when you want structured PAYLOAD in memory without CLI table art. Start with default CALL airlock.user.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_user 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.user.create_spec_from_template instead of inventing specs ad hoc. Airlock also installs a public default_spec_draft_template so licensed app_user 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_user 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 user 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.user.* (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_user 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_user 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/user_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 procedures (Snowflake application role app_admin).
• airlock.user – user‑facing procedures (application roles app_user and app_admin).

#1.1 Security & access model

• Airlock application roles
• app_admin can call all airlock.admin.* and airlock.user.* procedures.
• app_user can call only airlock.user.* 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.user.* 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.user.* procedures and Streamlit usage require a named license seat (including callers with app_admin).
• 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.
• User-facing procedures (airlock.user.*) use these to enforce who can see which specs/files/history; internal checks here are about Airlock roles, not Snowflake application 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 user 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/user_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, list_events) 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 and file procedures push a row into core.events after each successful mutation, using the same event types and column semantics as the UI, so that procedure-driven changes are auditable in the same way as UI-driven changes. Events are surfaced through:
• airlock.admin.list_events (admin‑only) for querying events.
• Higher‑level analytical helpers in lib/utils/events.py for the Streamlit UI.
• 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 trim of old core.events rows), WORKFLOW_CHANGE (workflow step transitions from edit_file_workflow). Event recording from procedures is best-effort (non-blocking); a failed event insert does not fail the primary operation.
• 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
• Retention policies (core.retention_policies) and manifest procedures (list_outdated_files, 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 user)

The platform has two procedure categories with different trust boundaries:

• airlock.admin.* for control-plane and privileged maintenance operations.
• airlock.user.* for assignment-scoped data operations and user workflows.

The matrix below is a role/action inventory to verify CRUD coverage and identify intentional boundaries.

Completeness check summary

• User procedure completeness: user-facing CRUD is complete for assignment-scoped file workflows, attachments, and spec discovery/history paths. Privileged setup/administration actions are intentionally excluded from user endpoints.
• Admin procedure completeness: admin coverage is complete for control-plane CRUD (roles, assignments, specs, templates, references, retention), file workflow and attachments, plus event/audit query paths.
• Boundary model: user endpoints perform in-procedure authorization checks; admin endpoints rely on stronger app-role grants plus admin-only maintenance capabilities.

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.user).

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

CALL airlock.admin.api_info();

---

#2. Admin Schema (airlock.admin.*)

All admin procedures are callable only when the app_admin application role is active.

#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();

#2.1.1. Named license operations

• airlock.admin.list_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.admin.list_licenses();
    CALL airlock.admin.list_licenses(TRUE);

• airlock.admin.get_license_ceiling()
• 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.admin.get_license_ceiling();

• 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, MESSAGE)
• 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, MESSAGE)
• 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, MESSAGE)
• Example:

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

• airlock.admin.set_license_auto_assign(enabled BOOLEAN)
• Description: Enables or disables auto-assignment for gated user.* and Streamlit seat checks.
• Returns: TABLE(STATUS, MESSAGE)
• Example:

    CALL airlock.admin.set_license_auto_assign(TRUE);

#2.2. Roles & Assignments

• airlock.admin.list_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.admin.list_roles();
    CALL airlock.admin.list_roles('jane.doe');

• airlock.admin.describe_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.admin.describe_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; omit for top-level roles), is_spec_admin, is_locked.
• 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.
• 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.
• 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, IS_DROPPED BOOLEAN, MESSAGE VARCHAR, ISSUES VARIANT – array of {code, message, severity} (same shape as load_data ISSUES); empty when STATUS is ok / dropped.
• Example:

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

• airlock.admin.list_assignments(username VARCHAR DEFAULT NULL, rolename 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.admin.list_assignments();
    CALL airlock.admin.list_assignments('jane.doe');
    CALL airlock.admin.list_assignments(rolename => 'budget_analyst');

• airlock.admin.describe_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.admin.describe_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.
• 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, IS_DROPPED BOOLEAN, MESSAGE VARCHAR, ISSUES VARIANT – array of {code, message, severity} (same shape as load_data ISSUES); empty when STATUS is ok / dropped.
• Example:

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

#2.3. Events

• airlock.admin.list_events(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. Admin-only. Use for auditing, BI, or dashboards.
• 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)
• Example:

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

#2.4. Specs & Files

• airlock.admin.list_specs(in_app_role VARCHAR DEFAULT NULL)
• Description: Lists all specs; supports a “lens” role for access perspective.
• Parameters:
• in_app_role (optional): see specs as if you only had this role; NULL = all specs.
• Returns:
• SPEC_NAME VARCHAR
• SPEC_ALIAS VARCHAR
• DESCRIPTION VARCHAR
• OWNER_ROLE VARCHAR
• ACCESS_TYPE VARCHAR – owner, guest, or admin
• GUEST_ROLE_NAME VARCHAR
• RESTRICTED_PATH VARCHAR
• IS_MULTI_ROLE BOOLEAN
• Example:

    CALL airlock.admin.list_specs();
    CALL airlock.admin.list_specs('budget_analyst');

• airlock.admin.describe_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
• ACCESS_SUMMARY VARIANT – compact explanation of owner, guest, reviewer, watcher, attachment, and reference RLS policy
• Example:

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

• airlock.admin.get_spec(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 (spool to file with SnowSQL -o output_file=config.json still works) 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.admin.get_spec('budget_requests');
    -- With SnowSQL to spool: snowsql -c conn -q "CALL airlock.admin.get_spec('my_spec');" -o output_file=spec.json -o header=false -o friendly=false

• 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 user.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.admin.describe_spec_import(spec_name VARCHAR)
• Description: Returns the same descriptor as describe_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 describe_spec plus IMPORT_METADATA VARIANT (nullable).

• airlock.admin.list_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.admin.list_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.admin.list_spec_templates();

• airlock.admin.describe_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.admin.describe_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_user.
• 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.admin.list_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.admin.list_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. This is the primary ALTER procedure for specs. Also used to complete a pending dbt import: fetch config via describe_spec_import or get_spec, 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 – parsed JSON result from validate_spec (including valid and any invalid tabs/messages, plus any additional alter-specific errors).
• Example:

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

• airlock.admin.list_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.admin.list_spec_versions('budget_requests');

• airlock.admin.get_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.admin.get_spec_version('budget_requests', '20260315123045');

• airlock.admin.backfill_spec_versions(spec_name VARCHAR DEFAULT NULL)
• Description: Ensures versioned spec_config files exist in AIRLOCK_STAGE for specs in core.specs.
• 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.
• 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.admin.list_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
• Example:

    CALL airlock.admin.list_files('budget_requests');
    CALL airlock.admin.list_files('budget_requests', 'budget_analyst');

• airlock.admin.list_work_items(spec_name VARCHAR DEFAULT NULL, in_app_role VARCHAR DEFAULT NULL)
• Description: Lists active workflow work items across specs or one spec. Intended for dashboards and agents that need to know which files can be moved in workflow.
• Returns: SPEC_NAME, PATH, FILENAME, WORKFLOW_STEP, WORKFLOW_STATUS, WORK_ITEM_TYPE, AVAILABLE_ACTIONS, ACCESS_REASON, TIME_IN_STATE_SECONDS, WORKFLOW_STEP_UPDATED_AT.
• Example:

    CALL airlock.admin.list_work_items();
    CALL airlock.admin.list_work_items('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.
• 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, IS_UPDATED, VALIDATION, FROM_STEP, TO_STEP
• 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
• 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 (e.g. PUT via SnowSQL) 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.
• 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. Always validates first. 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).
• 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.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. 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).
• 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.admin.list_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 for a spec 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.
• Example:

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

#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 and 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; limited by Snowflake argument size ~16 MB). When link_file_* are provided, the attachment is linked to that 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, MESSAGE, ATTACHMENT_ID, SIZE_BYTES
• 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. 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.admin.list_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.admin.list_retention_policies();
    CALL airlock.admin.list_retention_policies('my_spec');

• airlock.admin.describe_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.admin.describe_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, IS_DROPPED, MESSAGE.
• Example:

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

Outdated files (retention-driven purge)

• airlock.admin.list_outdated_files(spec_name VARCHAR DEFAULT NULL)
• Description: Lists non‑current uploads that are eligible for purge based on retention policies.
• 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.admin.list_outdated_files();
    CALL airlock.admin.list_outdated_files('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. 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.
• Parameters: force (optional, default FALSE): when TRUE, truncates FILE_MANIFEST before rebuilding.
• Returns: summary string.
• Example:

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

• airlock.admin.sync_table(spec_name VARCHAR)
• Description: Syncs the materialized table for a spec with its view using MERGE. 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.
• 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 get_spec 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 get_spec.
• 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();

• airlock.admin.register_shared_view(schema_name VARCHAR, view_name VARCHAR, ref_columns VARIANT)
• Description: Legacy helper that registers a view in the shared schema for use in spec FK rules. The view must already exist (created by the customer with standard SQL). Registration stores metadata in core.shared_views so validation can resolve FK rules. Does not create or alter the view. New integrations should prefer unified references via airlock.admin.register_reference.
• Parameters:
• schema_name (required): Short name; for MVP only 'SHARED' is supported.
• view_name (required): Name of the view (e.g. 'employees').
• ref_columns (required): Column name(s) that may be used as the FK target — a single VARCHAR (e.g. 'email') or a JSON array (e.g. PARSE_JSON('["email","id"]')).
• Returns: TABLE(SCHEMA_NAME VARCHAR, VIEW_NAME VARCHAR, REF_COLUMNS VARIANT, STATUS VARCHAR, MESSAGE VARCHAR).
• Example:

    CALL airlock.admin.register_shared_view('SHARED', 'employees', PARSE_JSON('["email"]'));

• airlock.admin.unregister_shared_view(schema_name VARCHAR, view_name VARCHAR)
• Description: Removes the registration of a shared view. Does not drop the view itself.
• Parameters: schema_name (required), view_name (required).
• Returns: TABLE(SCHEMA_NAME VARCHAR, VIEW_NAME VARCHAR, STATUS VARCHAR, MESSAGE VARCHAR).
• Example:

    CALL airlock.admin.unregister_shared_view('SHARED', 'employees');

• airlock.admin.list_shared_views(schema_name VARCHAR DEFAULT NULL) (optional, legacy)
• Description: Lists registered shared views. schema_name optional (e.g. 'SHARED'); NULL = all. Superseded by airlock.admin.list_references for the unified reference model.
• Returns: Table of schema name, view name, ref columns, and registration metadata.

• 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, MESSAGE VARCHAR).

• 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, MESSAGE VARCHAR).

• 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, MESSAGE VARCHAR).

• airlock.admin.list_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, MESSAGE).

• 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, MESSAGE).

• airlock.admin.list_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 user.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.user.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. User Schema (airlock.user.*)

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

#3.1. Introspection & Help

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

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

• airlock.user.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.user.documentation();
    CALL airlock.user.documentation(CONTENT_MODE => 'PROCEDURES');
    CALL airlock.user.documentation(CONTENT_MODE => 'FULL');
    CALL airlock.user.documentation('markdown', 'api_v1', 'SECTION', '4.8');
    CALL airlock.user.documentation('markdown', 'api_v1', 'CHUNK', '1-3');

#3.2. Roles & Assignments

• airlock.user.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.user.list_my_roles();

• airlock.user.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.user.list_my_assignments();
    CALL airlock.user.list_my_assignments('jane.doe');
    CALL airlock.user.list_my_assignments(rolename => 'analyst');

• airlock.user.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 Deb has a grant to act for asmith, Deb cannot call this procedure to grant Charlie access for asmith.
• Admin boundary: Delegation action names are airlock.user.* 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.user.* 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)
• Example:

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

• airlock.user.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 user 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, then edit_file_workflow(action => 'advance') with the same on_behalf_of_user; it should report if workflow movement is not delegated 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 Deb demo user can run CALL airlock.user.list_my_delegations('received', 'demo_employee_reimbursements'); and discover demo_delegation_deb_for_asmith_reimbursements.
• Example:

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

• airlock.user.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.user.get_my_license_seat();

#3.3. Specs & Files

• airlock.user.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.user.list_my_specs();
    CALL airlock.user.list_my_specs('budget_analyst');

• airlock.user.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 user.* 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
• Example:

    CALL airlock.user.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.user.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.user.list_my_files('budget_requests');
    CALL airlock.user.list_my_files('budget_requests', 'budget_analyst');

• airlock.user.list_my_work_items(spec_name VARCHAR DEFAULT NULL, in_app_role VARCHAR DEFAULT NULL, include_managed_roles BOOLEAN DEFAULT TRUE)
• Description: Lists workflow work items visible through the caller's direct Airlock roles. Agents can call this to find files to submit, review, or watch for their own role authority. AVAILABLE_ACTIONS contains actions such as advance or return when the caller can move workflow; watcher rows are read-only. Delegated file submission does not by itself add principal files to this list. Delegate-only agents should use list_my_delegations plus delegated procedure results for on-behalf submission, and list_my_work_items only for their own direct-role work.
• Returns: SPEC_NAME, PATH, FILENAME, WORKFLOW_STEP, WORKFLOW_STATUS, WORK_ITEM_TYPE, AVAILABLE_ACTIONS, ACCESS_REASON, TIME_IN_STATE_SECONDS, WORKFLOW_STEP_UPDATED_AT.
• Example:

    CALL airlock.user.list_my_work_items();
    CALL airlock.user.list_my_work_items('budget_requests', 'budget_reviewer', FALSE);

• airlock.user.list_my_expectation_work(spec_name VARCHAR DEFAULT NULL, in_app_role VARCHAR DEFAULT NULL, include_managed_roles BOOLEAN DEFAULT TRUE)
• Description: Lists expectation work visible to the current user. DESCRIPTION is the human-friendly task text admins maintain so expectation names can stay stable and compact; DETAILS carries the underlying schedule/sequence/interval evaluation.
• Returns: EXPECTATION_NAME, SPEC_NAME, DESCRIPTION, TARGET_MILESTONE, IS_STRICT, EXPECTATION_STATUS, WORK_ITEM_TYPE, DUE_AT, DAYS_UNTIL_DUE, MATCHING_FILE_COUNT, ACTIVE_EXCEPTION_COUNT, DETAILS.
• Example:

    CALL airlock.user.list_my_expectation_work();
    CALL airlock.user.list_my_expectation_work('budget_requests');

• Cross-spec source references (immutable lineage)

Admins declare allowed downstream→upstream pairs with airlock.admin.create_spec_source_link (optional link_options: required_workflow_status, max_count, upload flags) and can revoke policy with airlock.admin.disable_spec_source_link. Users list eligible upstream files with airlock.user.list_eligible_source_files, pin rows with airlock.user.add_file_reference, remove pins with airlock.user.remove_file_reference, and inspect pins with airlock.user.list_file_references (pass a file_manifest_pk that appears on either side of the reference).

• airlock.user.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, watcher, 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). Watcher 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.user.describe_spec('incoming_line_items');
    CALL airlock.user.describe_spec('incoming_line_items', 'automation_user');  -- ACCESSIBLE_PATHS shows e.g. [{"path_scope": "public/full_access", "access_level": "full_access"}]

• airlock.user.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). Delegation denials return STATUS = 'error' with one ISSUES entry whose code is a stable delegation code such as DELEGATION_NOT_FOUND, DELEGATION_ACTION_NOT_ALLOWED, or INVALID_DELEGATION_POLICY.
• Example:

    CALL airlock.user.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.user.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. 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. Delegation denials return STATUS = 'error' with one ISSUES entry whose code is 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.user.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.user.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).
• Example:

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

• airlock.user.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. The account must grant the application SELECT on the underlying object.
• Returns: TABLE() — dynamic columns from the target object.
• Example:

    CALL airlock.user.select_reference_data('finance_ref', 'gl_entries');
    CALL airlock.user.select_reference_data(
      spec_name => 'finance_ref',
      object_key => 'gl_entries',
      row_limit => 100,
      in_app_role => 'budget_analyst'
    );
    CALL airlock.user.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.user.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.
• 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, IS_UPDATED, VALIDATION, FROM_STEP, TO_STEP. Delegation denials return STATUS = 'error' with a stable delegation code in VALIDATION.issues[0].code.
• Example:

    CALL airlock.user.edit_file_workflow('budget_requests', 'default', 'report_2025', 'advance', 'Ready for review');
    CALL airlock.user.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.user.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_user. 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. 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.user.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.user.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.user.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. On delegation denial, CODE is the stable delegation code.

• airlock.user.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.admin.list_roles();
  CALL airlock.admin.list_assignments('JANE');

• Typical user flow:

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

  -- See which assignments you can manage
  CALL airlock.user.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.admin.list_file_versions('budget_requests');

• User‑side access:

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

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

  -- Read current data for a spec you can access
  CALL airlock.user.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.admin.list_outdated_files('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.admin.list_events(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.admin.list_events();

  -- Events for a specific user
  CALL airlock.admin.list_events('jane.doe');

  -- Events for a specific Airlock role in a time window
  CALL airlock.admin.list_events(
    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_shared_view('SHARED', 'employees', 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.user.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.user.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 user (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.user.describe_spec('incoming_line_items', 'automation_user', TRUE);
  CALL airlock.user.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.user.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 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.user.*, granted to app_user):
• 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.
• 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.
• 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, 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: list_my_files(spec_name), select_my_files(spec_name); 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.

---

#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 (user help text)
• docs/attachments_batch26.md (attachment policy companion; Streamlit behavior)