--- name: snowflake-native-app-release-sheriff description: | Preflight Snowflake Native App package releases from trusted, privacy-safe provider evidence without publishing, upgrading, altering, granting, or approving anything. Use when reviewing manifest/setup safety, security-scan gates, version and release-directive validation, App Spec or privilege changes, upgrade-cohort compatibility, and rollback readiness. Trigger with "Native App release", "application package scan", "upgrade cohort", or "App Spec". allowed-tools: Read, Bash(python3:*) argument-hint: "[schema-2-native-app-evidence.json]" version: 3.16.0 author: Jeremy Longshore license: MIT compatibility: Model-agnostic; Python 3.10+; optional Snowflake CLI for live provider-side read-only collection model: inherit effort: high tags: [saas, snowflake, native-app, release, security, upgrade, rollback] --- # Snowflake Native App Release Sheriff ## Purpose Produce a deterministic, as-of provider-side release preflight. A clean result is `READY_FOR_OPERATOR_RELEASE_AS_OF`, never permission or an instruction to publish, set a release directive, upgrade an application, or alter privileges. ## Prerequisites Read both reference files before analysis. The input must use the exact schema-2 contract in [`references/evidence-contract.md`](references/evidence-contract.md) and must include independently retained hashes for the whole bundle, manifest, setup script, normalized cohort rows/denominators, lifecycle evidence, and the complete rollback receipt including its artifact digest. Every array carries an explicit count; zero is evidence only when the owner-approved denominator is exactly zero. Python 3.10+ is required. A Snowflake CLI profile is optional for collection and must already exist; authentication is configured outside this skill. The package manifest and setup script remain local inputs. Never send source SQL, package names, consumer names, account names, free-text scan failures, or App Spec definitions through the evidence packet. Use stable, account-scoped hashes. ## Instructions 1. Collect all three live provider surfaces for one exact package. 2. Build the schema-2 packet with owner-approved denominators and trusted hashes. 3. Run the analyzer at an explicit UTC evaluation time. 4. Stop on invalid, blocked, stale, capped, or incomplete evidence; hand a clean as-of report to the separately authorized release owner. ### Collect current provider evidence Use an existing least-privilege Snowflake CLI profile with visibility to the selected package. The collector uppercases and strictly validates the unquoted one-part package selector, then binds the rendered-query receipt to the Snowflake-produced selected-package hash. An error receipt contains no package fingerprint. ```bash python3 "${CLAUDE_SKILL_DIR}/scripts/collect_snowflake_evidence.py" \ --surface native-app-versions-current --application-package APP_PACKAGE \ --connection native-app-observer --output ./versions.json python3 "${CLAUDE_SKILL_DIR}/scripts/collect_snowflake_evidence.py" \ --surface native-app-release-directives-current --application-package APP_PACKAGE \ --connection native-app-observer --output ./directives.json python3 "${CLAUDE_SKILL_DIR}/scripts/collect_snowflake_evidence.py" \ --surface native-app-upgrade-cohorts-current --application-package APP_PACKAGE \ --connection native-app-observer --output ./cohorts.json ``` `SHOW VERSIONS` requires package visibility. `SHOW RELEASE DIRECTIVES` requires package ownership or the documented package release/version management privilege. `SNOWFLAKE.DATA_SHARING_USAGE.APPLICATION_STATE` is provider-only, can lag up to 10 minutes, and does not retain uninstalled instances. Do not escalate to `ACCOUNTADMIN`; missing or filtered evidence blocks the preflight. ### Analyze Record the five hashes at independent trusted boundaries, then run: ```bash python3 "${CLAUDE_SKILL_DIR}/scripts/analyze_native_app_release.py" \ --input ./native-app-evidence.json --evaluated-at "$EVALUATED_AT_UTC" \ --trusted-input-sha256 sha256:... \ --trusted-manifest-sha256 sha256:... \ --trusted-setup-sha256 sha256:... \ --trusted-cohort-sha256 sha256:... \ --trusted-lifecycle-sha256 sha256:... \ --trusted-rollback-sha256 sha256:... \ > ./native-app-preflight.json ``` Exit `0` means the bounded preflight passed. Exit `1` means valid but blocked; exit `2` means invalid/untrusted evidence. The analyzer writes stdout only. ### Validate the packet Confirm all three receipt ages, exact package/account hashes, source counts, independent digests, target scan row, observed cohort denominator, compatibility edges, lifecycle receipt, and rollback evidence before accepting exit `0`. ## Fail-closed rules - Require a unique READY target version/patch. For an EXTERNAL ALPHA or DEFAULT release require `APPROVED`; QA does not initiate a security scan, so scan status is reported but is not invented as approval. - Setup runs on install and upgrade and can resume after failure. Reject forbidden context/import patterns, non-replay-safe statements, and any grant-destructive replacement not immediately restored at the next statement. - Manifest v2 is required for App Specs. PENDING, DECLINED, absent, stale, or sequence-invalid consumer approval evidence blocks. Provider package metadata does not prove consumer approval. - Bind every automated privilege, reference callback/object/privilege contract, and App Spec delta to an exact denominator. Removed privileges block. For manifest v2 automated grants, changing `manifest_version` is major-upgrade-only and changing the requested privilege list is not a patch operation. - Require compatibility proof for every observed current-version cohort. Block install/upgrade failures, queued work, retries/delay, in-flight states, target mismatch, or previous-version `FINALIZING`. - Require current lifecycle history alongside `APPLICATION_STATE`; the snapshot alone cannot prove completeness or explain an uninstalled instance. - Require a tested, hash-bound rollback artifact and observables for privilege and App Spec reconciliation. A release directive starts upgrades; it is not a validation result. - Reject stale (>15 minutes), capped, tampered, mixed-account, offline, or selector-unbound receipts. Do not convert absence into PASS. ## Safety boundary Never execute `ALTER APPLICATION PACKAGE`, add/drop version or patch, set/unset a release directive, publish a listing, approve an App Spec, run setup, upgrade an application, or grant/revoke a privilege. Produce a review packet for a separate, authorized human change window. ## Output The report contains only finite finding codes, exact denominators, an as-of status, explicit `safe_to_publish: false` and `safe_to_upgrade: false`, non-claims, and a deterministic report hash. It never repeats input rows or raw identifiers. ## Error Handling `INVALID_EVIDENCE` means the strict packet, receipt, type, hash, scope, cap, or freshness contract failed; it intentionally does not echo the rejected value. `BLOCKED` is well-formed evidence with one or more finite remediation codes. Recollect permission-filtered, stale, or capped surfaces with the same approved scope. Never fix missing evidence by escalating privileges or executing a change. ## Examples - `IN_PROGRESS` on an EXTERNAL DEFAULT target yields `SECURITY_SCAN_NOT_APPROVED`. - A missing compatibility edge for one observed cohort yields `INCOMPATIBLE_OR_UNTESTED_COHORT`, even when every other instance is complete. - Fresh, trusted, uncapped evidence with replay-safe setup, approved scan, exact cohorts, lifecycle proof, and tested rollback yields only `READY_FOR_OPERATOR_RELEASE_AS_OF` with both mutation flags false. ## References - [`references/evidence-contract.md`](references/evidence-contract.md) — exact fields, enums, hashes, freshness, and verdict rules. - [`references/source-notes.md`](references/source-notes.md) — primary Snowflake documentation and provider/consumer limitations.