Airlock Stored Procedure API v1

Stable contract for the airlock Snowflake stored procedure API: airlock.admin.* (admin control plane) and airlock.user.* (end-user data access).

CALL airlock.admin.api_info();

CALL airlock.user.documentation();
CALL airlock.user.documentation('markdown', 'api_v1');

1. Versioning, security & compatibility

• API version: 1.0
• 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

Application roles. app_admin may call all airlock.admin.* and airlock.user.* procedures. app_user may call only airlock.user.*. Snowflake GRANTs on procedures are the primary enforcement mechanism. File mutation and validation flows share a policy decision point (PDP) for consistent authorization and reason codes.

Named-license gate. airlock.user.* 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. airlock.admin.* licensing procedures are the break-glass path and do not require the caller to already hold a seat. If the pool is full, use admin licensing procedures to unassign seats or raise the ceiling.

In-app roles. In-app roles (core.in_app_roles) and assignments (core.in_app_assignments) model application-level access. User APIs enforce who can see which specs, files, and history. Admin APIs may accept a lens parameter (e.g. in_app_role) to “see as” a role while relying on Snowflake grants for admin vs user access.

Data access philosophy. Access uses narrow procedures (select_files, select_hist, list_events, etc.) instead of direct table access. Procedures accept simple scalar parameters, enforce row- and object-level access from in-app roles and spec configuration, and avoid returning unnecessary sensitive data.

Events & observability

Activity is recorded in core.events from the Streamlit UI and from stored procedures that perform mutations. Admin and file procedures push rows after successful mutations where applicable. Surface via airlock.admin.list_events (admin-only). Event types include (non-exhaustive): USER_LOGIN, LICENSE_DENIED, LICENSE_ASSIGNED, RECORD_CREATE, RECORD_IMPORT, RECORD_UPDATE, RECORD_DELETE, FILE_UPLOAD, FILE_DELETE, FILE_RETENTION_PURGE, EVENTS_AUTO_PURGE, WORKFLOW_CHANGE. Event inserts from procedures are best-effort and do not fail the primary operation.

Retention & purging

Retention policies (core.retention_policies) and procedures such as list_outdated_files, delete_outdated_files, and mark_files_deleted define lifecycle for spec files and derived data. Successful delete_outdated_files with dry_run => FALSE emits one FILE_RETENTION_PURGE event per removed upload. APIs aim to be idempotent and auditable; destructive actions use soft-delete, explicit force flags, or locked-state checks.

1.3 Procedure coverage matrix (admin vs user)

1.2 Compatibility rules

Non-breaking in v1: new procedures; optional parameters with safe defaults; new trailing result columns (callers must tolerate extra columns).

Breaking (not allowed in v1): renaming/removing procedures; changing parameter names, types, or required/optional semantics; changing meanings such that the same inputs yield incompatible results.

Future breaking changes ship as new suffixed procedures (e.g. select_files_v2) or new versioned schemas (e.g. airlock_v2.admin).

2. Admin schema (airlock.admin.*)

Callable only with the app_admin application role active. Detailed signatures, columns, and behavior follow the bundled documentation() output; below is a concise reference. Help text:

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

2.1 Introspection & help

• airlock.admin.help(procedure_name VARCHAR DEFAULT NULL) → TABLE(LINE VARCHAR)
• airlock.admin.api_info() → API_VERSION, SUPPORTED_VERSIONS, PROCEDURE_NAME, CATEGORY, STATUS, NOTES

2.1.1 Named license operations

• list_licenses(include_inactive BOOLEAN) → holders + policy fields
• get_license_ceiling() → ceiling, auto-assign, seats assigned/free
• get_license_auto_assign() → AUTO_ASSIGN_ENABLED
• set_license_ceiling(new_ceiling NUMBER) → STATUS, MESSAGE
• assign_license(user_name VARCHAR), unassign_license(user_name VARCHAR)
• set_license_auto_assign(enabled BOOLEAN)

2.2 Roles & assignments

• list_roles(username VARCHAR DEFAULT NULL), describe_role(role_name VARCHAR)
• create_roles(role_descriptors VARIANT, validate_only BOOLEAN DEFAULT FALSE) — batch create with dependency order
• alter_role(role_name VARCHAR, role_descriptor VARIANT, validate_only BOOLEAN DEFAULT FALSE)
• drop_role(role_name VARCHAR, force BOOLEAN DEFAULT FALSE)
• list_assignments(username VARCHAR DEFAULT NULL, rolename VARCHAR DEFAULT NULL), describe_assignment(assignment_name VARCHAR)
• create_assignments(assignment_descriptors VARIANT, validate_only BOOLEAN DEFAULT FALSE)
• alter_assignment(assignment_name VARCHAR, assignment_descriptor VARIANT, validate_only BOOLEAN DEFAULT FALSE)
• drop_assignment(assignment_name VARCHAR, force BOOLEAN DEFAULT FALSE)

2.3 Events

• list_events(username VARCHAR, event_types VARCHAR, in_app_role VARCHAR, event_description VARCHAR, start_time TIMESTAMP_LTZ, end_time TIMESTAMP_LTZ, limit_rows INTEGER DEFAULT 200) — filters optional; limit_rows capped at 1000

2.4 Specs & files

• Specs: list_specs, describe_spec, get_spec_config, create_spec, import_dbt_spec_definition, describe_spec_import, list_pending_specs, clone_spec_from_template, alter_spec, list_spec_config_versions, get_spec_config_version, promote_spec_config_version, backfill_spec_config_versions, drop_spec
• Templates: list_spec_templates, describe_spec_template, create_spec_template, alter_spec_template, drop_spec_template, list_spec_template_assignments, create_spec_template_assignment, drop_spec_template_assignment
• Files: list_files, edit_file_workflow, select_files, validate_spec, load_spec_data, delete_files, list_file_versions

load_spec_data promotes a validated file into core.FILE_MANIFEST; exactly one of path or file_content. Multi-role specs require scoped path (no inline content). Optional attachment_content_base64 / attachment_filename; when attachment_policy.attachment_required is true, attachment must be supplied or load returns ATTACHMENT_REQUIRED.

validate_spec — exactly one of staged path or inline file_content (CSV). Large files: stage + path.

2.4.1 Attachments

• attach_to_spec — spec-level attachment (stage path or base64 stream)
• add_attachment, replace_attachment, delete_attachment — per-file attachments; workflow/policy enforced; denial code ACCESS_DENIED_WORKFLOW_STATE

2.5 History

• select_hist(spec_name VARCHAR, as_of_timestamp VARCHAR DEFAULT NULL, include_deleted BOOLEAN DEFAULT FALSE, file_version_id VARCHAR DEFAULT NULL) — SCD2 shape plus metadata columns

2.6 Retention & outdated files

• Policy CRUD: list_retention_policies, describe_retention_policy, create_retention_policy, alter_retention_policy, drop_retention_policy
• list_outdated_files(spec_name VARCHAR DEFAULT NULL), delete_outdated_files(spec_name VARCHAR DEFAULT NULL, dry_run BOOLEAN DEFAULT TRUE, limit INTEGER DEFAULT 1000)

2.7 File & history maintenance

• mark_files_deleted, rebuild_file_manifest_from_stage, sync_table(spec_name VARCHAR)

2.8 Spec views & tables

• rebuild_spec_view, rebuild_spec_table, regenerate_spec_views, regenerate_spec_tables

2.9 Setup & validation

• rerun_setup()
• validate_spec_config(spec_config VARIANT, reuse_spec BOOLEAN DEFAULT FALSE, allow_existing_spec BOOLEAN DEFAULT FALSE) — JSON result string with valid and invalid tab details

2.10 Shared schema & FK validation

• Legacy shared views: create_shared_schema, register_shared_view, unregister_shared_view, list_shared_views
• Unified references: register_reference, unregister_reference, list_references, grant_reference_access, revoke_reference_access, list_reference_access

Spec rules may include {"type":"foreign_key","field":"...","ref":{"reference":"...","column":"..."}} (or legacy schema/view/column for shared views).

3. User schema (airlock.user.*)

Available to app_user and app_admin; access-controlled by in-app roles and assignments.

3.1 Introspection & help

• help(procedure_name VARCHAR DEFAULT NULL) → TABLE(LINE VARCHAR)
• documentation(output_format VARCHAR DEFAULT 'markdown', doc_key VARCHAR DEFAULT 'api_v1') — bundled API v1 markdown

3.2 Roles, assignments & license

• list_my_roles()
• list_my_assignments(username VARCHAR DEFAULT NULL, rolename VARCHAR DEFAULT NULL) — comma-separated filters supported
• get_my_license_seat() — read-only snapshot; does not claim a seat

3.3 Specs & files

• list_my_specs(in_app_role VARCHAR DEFAULT NULL)
• create_spec_from_template(template_name VARCHAR, new_spec_name VARCHAR, spec_alias VARCHAR DEFAULT NULL, spec_config_overrides VARIANT DEFAULT NULL)
• list_my_files(spec_name VARCHAR, in_app_role VARCHAR DEFAULT NULL)
• describe_spec(spec_name VARCHAR, in_app_role VARCHAR DEFAULT NULL) — safe descriptor including ACCESSIBLE_PATHS
• validate_spec(..., in_app_role VARCHAR DEFAULT NULL) — same semantics as admin with assignment checks
• load_spec_data(..., in_app_role VARCHAR DEFAULT NULL, path_scope VARCHAR DEFAULT NULL, attachment_content_base64 ..., attachment_filename ...) — use path_scope from ACCESSIBLE_PATHS for guest/shared folders
• select_my_files(spec_name VARCHAR, search_string VARCHAR, regex_pattern VARCHAR, in_app_role VARCHAR)
• edit_file_workflow(..., in_app_role VARCHAR DEFAULT NULL)
• add_attachment, replace_attachment, delete_attachment — with optional in_app_role where applicable

3.4 History

• select_my_hist(spec_name VARCHAR, as_of_timestamp VARCHAR, include_deleted BOOLEAN, file_version_id VARCHAR, in_app_role VARCHAR)

4. Common workflows

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

CALL airlock.admin.create_roles([PARSE_JSON('{
  "role_name": "budget_analyst",
  "description": "Can view and manage budget specs",
  "is_spec_admin": true
}')]);

CALL airlock.admin.create_assignments([PARSE_JSON('{
  "assignment_name": "jane.budget_analyst",
  "user_id": "JANE",
  "assigned_role": "budget_analyst"
}')]);

CALL airlock.admin.list_roles();
CALL airlock.admin.list_assignments('JANE');

CALL airlock.user.list_my_roles();
CALL airlock.user.list_my_assignments();

4.2 Spec lifecycle (admin)

CALL airlock.admin.validate_spec_config(:spec_config_json);

CALL airlock.admin.create_spec(:spec_config_json);
-- or
CALL airlock.admin.alter_spec('budget_requests', :spec_config_json);

CALL airlock.admin.validate_spec(
  'budget_requests',
  'budget_requests/path/abc123/report_2025.csv.gz'
);

CALL airlock.admin.load_spec_data(
  'budget_requests',
  'budget_requests/path/abc123/report_2025.csv.gz'
);

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

4.2 (cont.) User-side access

CALL airlock.user.list_my_specs();
CALL airlock.user.list_my_files('budget_requests');
CALL airlock.user.select_my_files('budget_requests');

4.3 Retention and purge

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"
}'));

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

CALL airlock.admin.list_events(NULL, 'FILE_RETENTION_PURGE', NULL, NULL, NULL, NULL, 200);

4.4 Auditing and history

CALL airlock.admin.list_events();
CALL airlock.admin.list_events('jane.doe');
CALL airlock.admin.list_events(
  NULL,
  'USER_LOGIN,RECORD_CREATE',
  NULL,
  NULL,
  '2024-01-01'::TIMESTAMP_LTZ,
  CURRENT_TIMESTAMP(),
  500
);

CALL airlock.admin.select_hist('budget_requests');
CALL airlock.admin.select_hist('budget_requests', '2025-01-15T10:30:00');

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

4.5 Shared view & FK validation

CALL airlock.admin.create_shared_schema();

CREATE OR REPLACE VIEW AIRLOCK_DATA.SHARED.employees AS
SELECT id, name, email FROM my_db.my_schema.employee_source;

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

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": {}
}'));

4.6 File workflow and attachments

CALL airlock.admin.edit_file_workflow(
  'budget_requests', 'default', 'report_2025', 'advance', 'Ready for review'
);

CALL airlock.user.edit_file_workflow(
  'budget_requests', 'default', 'report_2025', 'advance', 'Ready for review'
);

CALL airlock.admin.attach_to_spec('my_spec', NULL, 'YWJj', 'note.txt', 'other', 'Sample');
CALL airlock.admin.add_attachment(
  'my_spec', 'default', 'finance', NULL, 'base64...', 'receipt.pdf', 'document', 'Q1 receipt'
);

4.7 User: create file + attachment (guest path)

CALL airlock.user.describe_spec('recipes', 'data_bot');
CALL airlock.user.validate_spec(
  'recipes', NULL,
  'recipe_name,category,attachment_filename\nbasque_cheesecake,dessert,basque_cheesecake.jpg',
  'data_bot'
);
CALL airlock.user.load_spec_data(
  'recipes',
  NULL,
  'recipe_name,category,attachment_filename\nbasque_cheesecake,dessert,basque_cheesecake.jpg',
  'basque_cheesecake',
  'data_bot',
  'public/full_access',
  '<base64_encoded_image>',
  'basque_cheesecake.jpg'
);

4.8 AI and agent integration (summary)

Discovery: list_my_roles, list_my_specs, describe_spec. Submit: validate_spec then load_spec_data with path_scope and optional attachment base64. Read: list_my_files, select_my_files. Pass in_app_role when the caller has multiple roles.

5. Where to go next

• Streamlit implementation under src/airlock/streamlit/lib/** in the product repository
• Tests under tests/airlock/app_setup_scripts/procedures/scripts/
• Attachment architecture: docs/attachments_batch26.md (in product repo)