Skip to content
Merged
Show file tree
Hide file tree
Changes from all commits
Commits
File filter

Filter by extension

Filter by extension

Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
79 changes: 79 additions & 0 deletions docs/tutorials/query-before-mutation-01/README.md
Original file line number Diff line number Diff line change
@@ -0,0 +1,79 @@
# Query Before Mutation

Companion demo for the tutorial **Query before you mutate: how agents should touch your infrastructure**.

The example demonstrates a simple infrastructure control loop using Google Cloud Storage:

1. Query live state
2. Identify resources that differ from policy
3. Apply bounded policy gates
4. Mutate only non-compliant resources
5. Query again to verify convergence
6. Skip mutation when the resource is already compliant

## Scenario

The policy for this demo requires GCS buckets to use a customer-managed Cloud KMS key (CMEK).

Buckets where `encryption` is `NULL` are using Google-managed encryption.

The demo queries the live GCS API, identifies buckets that do not satisfy the policy, checks location and resource-count gates, applies CMEK, and verifies the resulting state.

## Requirements

- Docker
- Google Cloud project
- Service account credentials with permission to read and update Cloud Storage buckets
- Existing Cloud KMS key
- stackql

## Provider

This demo uses:

```text
google.storage.buckets
```

Pull the validated Google provider version:

```sql
REGISTRY PULL google v26.07.00432;
```

## Run the demo

Follow the SQL statements in:

```text
queries.sql
```

Before running them, replace:

- `your-project-id`
- `demo-app-bucket1`
- `your-ring`
- `your-key`

with values from your Google Cloud environment.

The flow is:

```text
live query -> policy check -> bounded gate -> mutation -> verification -> repeat safely
```

The important property is that every execution begins by querying current cloud state.

A previous state snapshot can describe what was managed before. The live API describes what exists now.

## Agent usage

The same pattern can be exposed to an agent through the StackQL MCP server.

An MCP-capable client can use StackQL to query the same live cloud APIs and reason over the results before taking an allowed action.

The SQL walkthrough in this folder keeps the underlying query, gate, mutation, and verification steps explicit.


139 changes: 139 additions & 0 deletions docs/tutorials/query-before-mutation-01/queries.sql
Original file line number Diff line number Diff line change
@@ -0,0 +1,139 @@
-- Tutorial 2: Query Before Mutation
-- Validated end to end against Google Cloud Storage.
--
-- Pattern:
-- query live state
-- identify delta
-- policy gate
-- mutate
-- verify convergence


-- ============================================================
-- SETUP
-- ============================================================

REGISTRY PULL google v26.07.00432;


-- ============================================================
-- STEP 1: QUERY LIVE STATE
-- ============================================================

-- Replace your-project-id with the Google Cloud project used for the demo.

SELECT
name,
location,
encryption
FROM google.storage.buckets
WHERE project = 'your-project-id';


-- Buckets where encryption is NULL use Google-managed encryption.
-- For this tutorial, the desired policy is CMEK.

SELECT
name,
location,
encryption
FROM google.storage.buckets
WHERE project = 'your-project-id'
AND encryption IS NULL;


-- ============================================================
-- STEP 2: POLICY GATE
-- ============================================================

-- Gate 1:
-- Only operate on buckets in the expected location.

SELECT
name,
location,
encryption
FROM google.storage.buckets
WHERE project = 'your-project-id'
AND location = 'US'
AND encryption IS NULL;


-- Gate 2:
-- Count the number of resources that would be mutated.
--
-- The automation should stop if this number is higher than
-- the configured safety threshold.

SELECT COUNT(*)
FROM google.storage.buckets
WHERE project = 'your-project-id'
AND location = 'US'
AND encryption IS NULL;


-- ============================================================
-- STEP 3: MUTATE TO CONVERGE
-- ============================================================

-- Apply a customer-managed Cloud KMS key to one non-compliant bucket.
--
-- Replace:
-- demo-app-bucket1
-- your-project-id
-- your-ring
-- your-key
--
-- with values from your environment.

UPDATE google.storage.buckets
SET data__encryption =
'{"defaultKmsKeyName":"projects/your-project-id/locations/us/keyRings/your-ring/cryptoKeys/your-key"}'
WHERE bucket = 'demo-app-bucket1';


-- Expected result:
-- The operation was despatched successfully


-- ============================================================
-- STEP 4: VERIFY CONVERGENCE
-- ============================================================

SELECT
name,
encryption
FROM google.storage.buckets
WHERE bucket = 'demo-app-bucket1';


-- Expected state:
--
-- encryption should now contain a defaultKmsKeyName similar to:
--
-- {
-- "defaultKmsKeyName":
-- "projects/your-project-id/locations/us/keyRings/your-ring/cryptoKeys/your-key"
-- }


-- ============================================================
-- STEP 5: IDEMPOTENCE CHECK
-- ============================================================

SELECT
name,
location,
encryption
FROM google.storage.buckets
WHERE project = 'your-project-id'
AND location = 'US'
AND encryption IS NULL;


-- Once the target bucket has CMEK configured, it should no longer
-- appear in this result set.
--
-- If no non-compliant target remains, no UPDATE should run.
-- Every execution starts from live state and mutates only when
-- the current state differs from policy.
Loading