# Agent-Consumable Views Playbook

How source teams publish Snowflake views for AI agent applications, and how consumer applications onboard.

## Why this matters

ows-coda queries Snowflake to answer analytical questions that go beyond what the individual backend services expose. Source teams across the organization own their own data in Snowflake. This playbook defines the pattern for making that data available to AI agents in a secure, discoverable, decentralized way.

## Principles

- **Snowflake RBAC is the access boundary.** Grants to Snowflake roles are the enforcement layer. Session variables, row access policies, and tags are supplementary.
- **Source teams own their data.** Each team decides which views to expose, who gets access, and whether to add row-level filtering.
- **One service user per consumer app.** Each AI agent application gets its own Snowflake service user and role via Terraform. No shared credentials.
- **Discovery via tags, not a central registry.** Views are discoverable account-wide via `SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES`.

## Shared infrastructure

There is no shared database or central infrastructure. The only shared element is a **naming convention**: source teams create a tag called `AGENT_CONSUMABLE` in their own database. Because `SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES` searches by tag name across the entire account, consumers can discover all tagged views regardless of which database defines the tag.

```sql
-- Each source team creates the tag in their own database
CREATE OR REPLACE TAG MY_DB.PROD.AGENT_CONSUMABLE
  ALLOWED_VALUES 'true'
  COMMENT = 'Marks views as discoverable by AI agent applications.';

-- Discovery query: find all agent-consumable views across the account
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES
WHERE TAG_NAME = 'AGENT_CONSUMABLE';
```

---

## For source teams: publishing views

```mermaid
flowchart TD
    create["Step 1: Create secure view"] --> tag["Step 2: Tag with AGENT_CONSUMABLE"]
    tag --> rbac["Step 3: Grant RBAC access"]
    rbac --> rlp{"Need row-level filtering?"}
    rlp -- Yes --> policy["Step 4: Add row access policy"]
    rlp -- No --> done["Done — view is discoverable"]
    policy --> done
```

### Step 1 — Create the view

Create a secure view in your database. It can live in an existing schema or a dedicated `AGENT` schema.

```sql
CREATE OR REPLACE SECURE VIEW MY_DB.PROD.MY_AGENT_VIEW AS
SELECT col1, col2, col3
FROM MY_DB.PROD.MY_TABLE;
```

### Step 2 — Tag the view

Create the `AGENT_CONSUMABLE` tag in your database (if it doesn't exist) and apply it.

```sql
CREATE TAG IF NOT EXISTS MY_DB.PROD.AGENT_CONSUMABLE
  ALLOWED_VALUES 'true'
  COMMENT = 'Marks views as discoverable by AI agent applications.';

ALTER VIEW MY_DB.PROD.MY_AGENT_VIEW
  SET TAG MY_DB.PROD.AGENT_CONSUMABLE = 'true';
```

### Step 3 — Grant access via RBAC

Grant SELECT to a database role, then grant that database role to the consumer's account role.

```sql
-- Option A: grant per view
GRANT SELECT ON VIEW MY_DB.PROD.MY_AGENT_VIEW
  TO DATABASE ROLE MY_DB.AGENT_READ;

-- Option B: grant all views in a schema (for dedicated AGENT schemas)
GRANT SELECT ON ALL VIEWS IN SCHEMA MY_DB.AGENT
  TO DATABASE ROLE MY_DB.AGENT_READ;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA MY_DB.AGENT
  TO DATABASE ROLE MY_DB.AGENT_READ;

-- Grant the database role to the consumer's account role
GRANT DATABASE ROLE MY_DB.AGENT_READ
  TO ROLE AGENT_ANALYTICS_SVC;
```

The last grant can also be done in Terraform by adding the database role to the consumer role's `granted_database_roles` list in `prod/snowflake/orchard/roles/variables.tf`.

### Step 4 (optional) — Add row-level filtering

If your team needs finer-grained access control, create your own row access policy. Consumer applications set session variables on each connection that your policy can reference via `GETVARIABLE()`.

**Trust model:** Session variables are self-asserted by the consumer application. Snowflake cannot verify them. Your policy is trusting the application to set these values correctly. The Snowflake role (RBAC) remains the hard access boundary — session variables add defense-in-depth, not primary enforcement.

```sql
CREATE OR REPLACE ROW ACCESS POLICY MY_DB.PROD.MY_POLICY
  AS (account_id VARCHAR) RETURNS BOOLEAN ->
    GETVARIABLE('APP_PROFILE_TYPE') = 'AbacusProfile'
    AND account_id IN (
      SELECT account_id FROM MY_DB.PROD.USER_ACCOUNTS
      WHERE profile_id = GETVARIABLE('APP_PROFILE_ID')
    );

ALTER VIEW MY_DB.PROD.MY_AGENT_VIEW
  ADD ROW ACCESS POLICY MY_DB.PROD.MY_POLICY ON (ACCOUNT_ID);
```

Session variables set by ows-coda:

| Variable            | Description                                         |
| ------------------- | --------------------------------------------------- |
| `APP_ID`            | Application identifier                              |
| `APP_ORG_ID`        | Organization ID                                     |
| `APP_IDENTITY_ID`   | Identity ID                                         |
| `APP_IDENTITY_UUID` | Identity UUID                                       |
| `APP_PROFILE_ID`    | Profile ID                                          |
| `APP_PROFILE_UUID`  | Profile UUID                                        |
| `APP_PROFILE_TYPE`  | Profile type (e.g. `AbacusProfile`, `LabelProfile`) |
| `APP_ROLES`         | Comma-separated roles (e.g. `administrator,viewer`) |

Other consumer apps may set different variables — coordinate with them directly.

---

## For consumer apps: onboarding

### Step 1 — Create a service user and role (Terraform)

Each consumer app gets its own identity in Snowflake.

**Role** (`prod/snowflake/orchard/roles/variables.tf`):

```hcl
{
  name    = "MY_APP_SVC"
  comment = "Consumer role for my-app. Source teams grant database roles here."
  granted_database_roles = [
    # Source teams add their database roles as they onboard:
    # "ORCHARD_APP_REPORTING_V2.DB_AGENT_SCHEMA_READ",
  ]
}
```

**Service user** (`prod/snowflake/orchard/service_users/variables.tf`):

```hcl
"MY_APP_SVC_USER" = {
  default_role      = "MY_APP_SVC"
  default_wh        = "<team warehouse>"
  default_namespace = ""
  roles             = ["MY_APP_SVC"]
  comment           = "Service account for my-app. Key-pair auth only."
  team              = "my-team"
}
```

### Step 2 — Store credentials in Secrets Manager

Store the Snowflake private key in AWS Secrets Manager. Grant the Fargate task role permission to read it. See `terraform-infra/accounting/qa/ows-coda/snowflake.tf` for an example.

### Step 3 — Connect and query

Use key-pair authentication. Set session variables on checkout if you want source teams to be able to write row access policies against your user context.

See `apps/server/src/db/snowflake/pool.ts` for a reference implementation of a secure connection pool with session variable injection.

### Step 4 — Request access from source teams

Ask each source team to grant their database role to your consumer role (Step 3 in the source team section). This is the only coordination point — no central approval needed.

---

## ows-coda's Snowflake identity

| Resource            | Value                                                 |
| ------------------- | ----------------------------------------------------- |
| Service user        | `AGENT_ANALYTICS_SVC_USER`                            |
| Service role        | `AGENT_ANALYTICS_SVC`                                 |
| Auth                | Key-pair (private key in Secrets Manager)             |
| Pool implementation | `apps/server/src/db/snowflake/pool.ts`                |
| Terraform           | `terraform-infra/accounting/qa/ows-coda/snowflake.tf` |

## FAQ

**Q: Do I need a central database for agent views?**
No. Keep your views in your own database. The tag convention is all that's shared.

**Q: What if I don't need row-level filtering?**
Skip Step 4 of the source team section. Tag + grant is the minimum. The consumer will see all rows in your view.

**Q: Can two consumer apps see different rows in the same view?**
Yes, if the source team's row access policy checks session variables that differ between apps. But the simpler approach is to create separate views per consumer if the access patterns differ significantly.

**Q: Who approves new consumers?**
There is no central approval. Each source team decides independently whether to grant their database role to a new consumer role.