## Airlock Stored Procedure API v1 This document defines the **v1 contract** for the `airlock` Snowflake stored procedure API, covering both: - `airlock.admin.*` – admin‑only control plane and maintenance APIs - `airlock.user.*` – end‑user data access APIs 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: ```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`. `load_data` lands in the spec's configured initial workflow step. If that step is Draft and the caller wants 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 audited as normal Airlock actions: `load_data` creates the file in the spec's configured initial workflow step; `edit_file_workflow` is required only when the delegate needs to move the file after load. - **Expectations**: `description` should explain the business reason in human terms. `clauses` encode the cadence, sequence, 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. ```bash 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: ```bash 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](https://docs.snowflake.com/en/user-guide/snowsql-config)) 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. | Approach | What to tell the human | |----------|------------------------| | **SnowSQL** | Install [SnowSQL](https://docs.snowflake.com/en/user-guide/snowsql.html); add a **named connection** in `~/.snowsql/config` (or `SNOWSQL_CONFIG_FILE`) with account, user, authenticator (`SNOWFLAKE_JWT` for key pair, `OAUTH` for SSO, or password where allowed), warehouse, database, schema, and a **role** that can use the Native App and activate **`app_user`** (or `app_admin` for setup). Run `snowsql -c -f script.sql`. | | **Snowsight** | Open **Projects → Worksheets**, use a role with access to the app, `USE APPLICATION ROLE app_user` or `app_admin`, then run the same `CALL` statements. | | **Drivers** (Python, JDBC, etc.) | Same parameters as SnowSQL; use connection parameters documented for the Snowflake connector. | | **Key-pair authentication** | Often preferred for service users: admin associates a public key with the Snowflake user; the agent loads the private key (or JWT) with no password in scripts. See Snowflake “Key-pair authentication & key-pair rotation”. | | **Programmatic access tokens (PAT)** | Where enabled by account policy; time-bounded REST access—rotate and scope per security team guidance. | 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 | Symptom | What the admin can do | |---------|------------------------| | `LICENSE_SEAT_REQUIRED` / seat messages | Adjust pool / assignments via `airlock.admin.*` licensing procedures; or use Streamlit license management if deployed. | | Template “not assigned to your roles” | `create_spec_template_assignment` linking the template to the agent’s **Airlock role** (not only Snowflake `app_user`), or mark the template `is_public` when it should be available to every licensed user. | | Owner / descendant_roles errors on `create_spec_from_template` | Template likely missing `forced_owner_role` while spec owner is outside the caller’s subtree; **alter template** or have admin create the spec; see **Spec templates and delegated ownership** above. | | Cannot run `airlock.admin.*` | Expected for dedicated automation with only `app_user`; human `app_admin` runs control-plane calls in Snowsight or SnowSQL. | | Path / guest / workflow denials | `describe_spec` for permitted `path_scope`; consult `docs/pdp_authorization_runbook.md`; adjust **guest_access** or workflow in admin Streamlit or `alter_spec`. | --- ### 1. Versioning, Security & Compatibility - **API 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 APIs (`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 APIs (`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 API-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`). - APIs 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 API 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. | Domain | Conceptual CRUD action | Admin procedures (`airlock.admin.*`) | User procedures (`airlock.user.*`) | |---|---|---|---| | Roles | Create / Read / Update / Delete | `create_roles`, `list_roles`, `describe_role`, `alter_role`, `drop_role` | `list_my_roles` (read own effective roles) | | Assignments | Create / Read / Update / Delete | `create_assignments`, `list_assignments`, `describe_assignment`, `alter_assignment`, `drop_assignment` | `list_my_assignments` (read own assignments) | | Delegations | Create / Read / Revoke | `create_delegation`, `list_delegations`, `revoke_delegation` | `create_delegation` (self-service principal-only), `list_my_delegations` (read delegations involving current user) | | Expectations | Create / Read / Update / Delete | `create_expectation`, `list_expectations`, `describe_expectation`, `alter_expectation`, `drop_expectation`, `create_expectation_exception`, `alter_expectation_exception`, `list_expectation_exceptions`, `revoke_expectation_exception` | `list_my_expectation_work` (read own actionable expectation work) | | Specs | Create / Read / Update / Delete | `create_spec`, `list_specs`, `describe_spec`, `get_spec`, `alter_spec`, `drop_spec` | `list_my_specs` (read allowed specs), `describe_spec` (get config/fields for a spec), `create_spec_from_template` (scoped create path) | | Files | Create / Read / Update / Delete | `load_data`, `list_files`, `select_files`, `validate_data`, `edit_file_workflow`, `delete_files`, `list_file_versions` | `load_data`, `list_my_files`, `select_my_files`, `select_reference_data`, `validate_data`, `edit_file_workflow`, `delete_files` | | Attachments | Create / Read / Update / Delete | `attach_to_spec`, `add_attachment`, `replace_attachment`, `delete_attachment` | `add_attachment`, `replace_attachment`, `delete_attachment` | | Spec templates | Create / Read / Update / Delete | `create_spec_template`, `list_spec_templates`, `describe_spec_template`, `alter_spec_template`, `drop_spec_template`, `create_spec_template_assignment`, `list_spec_template_assignments`, `drop_spec_template_assignment` | Used indirectly via `create_spec_from_template` | | References | Create / Read / Update / Delete | `register_reference`, `revalidate_reference`, `list_references`, `list_reference_access`, `grant_reference_access`, `revoke_reference_access`, `unregister_reference` | No direct reference-admin APIs (intentional) | | Retention | Create / Read / Update / Delete | `create_retention_policy`, `list_retention_policies`, `describe_retention_policy`, `alter_retention_policy`, `drop_retention_policy`, `list_outdated_files`, `delete_outdated_files` | No direct retention-admin APIs (intentional) | | Events/observability | Read | `list_events` | No direct `list_events` endpoint (intentional) | | Help / API metadata | Read | `api_info`, `admin_help` | `user_help` | **Completeness check summary** - **User API 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 API 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 API version and supported versions can be queried via: ```sql 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**: ```sql CALL airlock.admin.help(); CALL airlock.admin.help('list_roles'); ``` - **`airlock.admin.api_info()`** - **Description**: Returns API 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql -- 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql -- 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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 API 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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 API 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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 API 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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](#210-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**: ```sql 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](shared_schema_grants.md). - **`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](shared_schema_grants.md)). - **Parameters**: None. - **Returns**: `VARCHAR` — summary message. - **Example**: ```sql 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**: ```sql 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**: ```sql 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..` (e.g. `SHARED` / `employees`). - `spec_name` (for `spec`): backing ingestion spec name; FK validation reads distinct values from `AIRLOCK_DATA.ACTIVE.T_`. - `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": "", "column": "", "object_key": "", "scope": "" }`. 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`**: ```json { "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: ```json { "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_` 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..`. - For `ref_type = 'spec'`: `AIRLOCK_DATA.ACTIVE.T_.`; 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": "", "column": "" }` 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**: ```sql 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 API v1 document 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**: ```sql 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**: ```sql 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**: ```sql 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 APIs 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**: ```sql 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, inspect the returned/current workflow state, and call `edit_file_workflow(action => 'advance')` with the same `on_behalf_of_user` only when the file is still Draft and workflow movement is delegated. It should report policy limits instead of trying to encode workflow state in file data. Pass `DELEGATION_ID` as `delegation_id` only if Airlock reports an ambiguous delegation. Use `include_inactive => TRUE` only to explain future or no-longer-effective delegations; inactive rows do not authorize work. - **Demo note**: After `make demo`, the Deb demo user can run `CALL airlock.user.list_my_delegations('received', 'demo_employee_reimbursements');` and discover `demo_delegation_deb_for_asmith_reimbursements`. - **Example**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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.summary` gives agents a compact explanation, while `DETAILS.strict_label`, `DETAILS.active_exception_count`, `DETAILS.active_exception_label`, and `DETAILS.clause_results` carry 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**: ```sql 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**: ```sql 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**: ```sql 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): ```sql 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 => '', 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_` 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**: ```sql 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**: ```sql 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**: ```sql 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)**: ```sql -- 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 => '', 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 => '', 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**: ```sql -- 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**: ```sql -- 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**: ```sql -- 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**: ```sql -- 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**: ```sql -- 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**: ```sql -- 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](shared_schema_grants.md)). - **Admin flow**: ```sql -- 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`): ```sql -- 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'`. ```sql -- 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`). ```sql 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 => '', attachment_filename => 'invoice_scan.pdf' ); ``` - **Two-step option** (load then attach in a second call): 1. **Describe the spec** → **Validate** → **Load** (without attachment params). 2. **Attach** with `add_attachment(spec_name, file_path, file_filename, ...)` using the same `PATH` and `FILENAME` returned by `load_data`. - **Reimbursements (CSV + receipt image/PDF)** same pattern: use `load_data` with `attachment_content_base64` (and `attachment_filename`) in the same call, or load then `add_attachment` with the same `file_path`/`file_filename`. - **Submit-to-workflow option**: If the business action means "submit for review", first check the spec workflow. Some specs configure `Submitted` as the initial workflow step, so `load_data` already creates the reviewer-facing item and `Draft` is only used for reviewer pushback. If the spec starts in `Draft`, first load the clean business payload, then call `edit_file_workflow(action => 'advance')` to move the returned `PATH` / `FILENAME` to Submitted. For delegated submissions, pass the same `on_behalf_of_user` to both calls and require `edit_file_workflow` in both the spec delegation policy and active grant. Reviewer pushback should return the file to Draft with a workflow comment; the principal then sees the item in My Work and can resubmit before any expectation due date. #### 4.8. AI and agent integration - **Goal**: Integrate AI assistants or automation so they can submit data and files into Snowflake through Airlock without direct stage or broad write privileges. See **Product intent** in `docs/product_intent.md` for the vision (receipts, budget drafts, etc.). - **API 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, inspect the returned/current workflow state. If the spec configured `Submitted` as its initial step, the load already created the reviewer-facing item. If the file is in Draft, call `edit_file_workflow(..., action => 'advance')` to reach Submitted only when workflow policy allows it. For delegated work, keep passing `on_behalf_of_user`; if the grant does not include workflow movement, report the loaded Draft file instead. - **Attach to file** (if not done in `load_data`, or for additional evidence): `add_attachment(spec_name => ..., file_path => ..., file_filename => ..., attachment_content_base64 => ..., attachment_filename => ..., attachment_tag => ...)`; `file_path`/`file_filename` must match the loaded file. For delegated follow-up evidence, also pass `on_behalf_of_user`; use a distinct `attachment_tag` when the original `load_data` already attached the default evidence. - **Read**: `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)