# Watch files

> Every monitor is a plain .sql file you can edit in any editor and keep in git.

Source: https://coisland.app/docs/watch-files/

## What you need

- The watches folder, `~/.coisland/watches`.
- A text editor.

Every CoIsland monitor is a watch file: one plain-text `.sql` file in `~/.coisland/watches`, whichever connector it runs on. Comment lines at the top hold its settings, and the rest is what it asks, in that tool's language: SQL for Snowflake, a GitHub search or filter, JQL for Jira, and so on. The Settings window writes these same files, so you can also edit watch files in any editor, review them in git and share them with your team. That is monitoring as code, with nothing else to install.

## Where watch files live

CoIsland reads one folder, `~/.coisland/watches`. **Settings › General** shows it under **Watches folder**, with **Show in Finder**.

*Screenshot: Settings › General: the Watches folder at the top, ~/.coisland/watches, with Show in Finder.*

- **What counts:** visible `.sql` files at the top of the folder. Subfolders, hidden files and editor swap files are ignored.
- **When edits show up:** CoIsland compares file names, sizes and modification dates every second, so a file you add, edit or delete shows up within a second, without a restart. A new file is checked at once.
- **The file name is the monitor's id.** State and alerts are filed under it. A monitor created in Settings is named after its title: **Big orders** becomes `big-orders.sql`, or `big-orders-2.sql` if taken. Renaming the file makes a new monitor; changing its `-- name:` line only renames it.
- **Deleting** a monitor in Settings moves its file to the Trash.

## The header and the body

The **header** is every line at the top that is blank or starts with `--`. The **body** starts at the first line that is neither.

```sql
-- name: Big orders
-- connector: acme
-- every: 15m
-- alert: new-rows
-- key: ORDER_ID
-- Orders over 10,000 placed in the last day.

SELECT order_id, customer, amount, placed_at
FROM analytics.sales.orders
WHERE amount > 10000
  AND placed_at > DATEADD('hour', -24, CURRENT_TIMESTAMP())
```

- **A setting** is `-- key: value`: a key of letters and underscores, in any case, and the rest of the line as its value. A value never spans two lines.
- **Any other comment** is a note, kept and written back when you save from Settings. A note shaped like a setting, such as `-- todo: narrow this`, reads as an unknown key: the monitor runs and shows `Unknown header key "todo" is ignored.`
- **A key given twice** shows `Header key "every" appears twice; the last one wins.`
- **Order does not matter.** The app writes `name`, `kind`, `connector`, `warehouse`, `database`, `schema`, `every`, `alert`, `key`, `title`, `sound`, `icon`, `role`, `timezone`, your notes, a blank line, then the body.

The monitor editor is a form over this file. **Title** is `name`, **Runs on** is `connector` (left at its default, it writes no line), and under **Advanced**, **Row identity** is `key` and **Row line** is `title`. Lines it has no field for, such as `role` and `timezone`, are kept.

*Screenshot: The Custom SQL editor: Title and Runs on, the SQL box with Test, then Alert when, Sound and the schedule. Saving writes each field as a header line above the SQL.*

## Watch file header keys

| Key | What it sets | Default | Example |
|---|---|---|---|
| `name` | Name in the island, Settings and alerts | The file name, less `.sql` | `-- name: Failed tasks` |
| `kind` | Connector type and body language | `snowflake.custom-sql` | `-- kind: jira.sla` |
| `connector` | The connector, by name; `connection` is its older spelling | That type's default | `-- connector: acme` |
| `every` | A number and `s`, `m` or `h`, from `30s` to `720h` | `15m` | `-- every: 5m` |
| `alert` | `new-rows`, or `count` with `>`, `>=`, `<`, `<=`, `=` or `!=` and a whole number | `new-rows` | `-- alert: count > 0` |
| `key` | Columns that identify a row, comma-separated | Whole row (Snowflake), the kind's id (GitHub, Jira) | `-- key: ORDER_ID` |
| `title` | A row's line, with `{COLUMN}` placeholders | First three columns | `-- title: {NAME} failed` |
| `sound` | A macOS sound, or `none` | `Glass` | `-- sound: Submarine` |
| `icon` | An SF Symbol for the island ring | The kind's symbol | `-- icon: cart` |
| `warehouse`, `role`, `database`, `schema` | Snowflake session overrides | The connection's | `-- role: COISLAND_READER` |
| `timezone` | An IANA zone: Snowflake's session `TIMEZONE`, and how dates read | Account (query), Mac (display) | `-- timezone: America/Toronto` |

- There is no `d` unit: a day is `24h`. `alert` also reads `new rows`, and `==` as `=`.
- A placeholder names a column in any case; one that matches no column stays as typed.
- `sound` names a file in `~/Library/Sounds`, `/Library/Sounds` or `/System/Library/Sounds`, such as Basso or Ping. An unknown name plays nothing.
- `icon` takes lower-case words and digits joined by dots, such as `chart.line.uptrend.xyaxis`; anything else is ignored with a warning.

### When a value is wrong

A bad value stops the monitor: it stays in **Settings › Monitors** under its file name, with the message, until you fix the file.

| In the file | CoIsland says |
|---|---|
| `-- every: 5` | `Invalid every "5": use a number followed by s, m or h, e.g. 5m` |
| `-- every: 10s` | `Invalid every "10s": the minimum is 30s` |
| `-- alert: count above 5` | `Invalid alert "count above 5": use new-rows, or count followed by >, >=, <, <=, = or != and a number, e.g. count > 0` |
| `-- timezone: Toronto` | `Invalid timezone "Toronto": not an IANA time zone such as America/Toronto` |
| A header and no body | `The file has no SQL after its header.` |
| A Snowflake body starting with `DELETE` | `Watches only run SELECT, WITH or SHOW; this one starts with DELETE.` |

A `key` column missing from the result fails at the check: `Key column ORDER_ID is not in the result. Columns: ID, AMOUNT.` Errors from the tool itself, such as a SQL compilation error or refused JQL, show in the same place.

*Screenshot: Settings › Monitors: Revenue by region shows Snowflake's SQL compilation error with an Error badge, and the Errors filter counts it, while the other five monitors keep running.*

## Monitor kinds and what the body holds

The `kind` line picks the connector type and the body's language. A file without one is Snowflake SQL, so every other monitor needs its `kind`. Connector names are per type: a Snowflake connection and a Jira connector can both be called `acme`.

The sections below cover Snowflake, GitHub and Jira. Every other connector's kinds, and a watch file for each, are in its setup guide, linked from the [connectors page](https://coisland.app/connectors/).

### Snowflake

`snowflake.custom-sql`, the default, holds one SQL statement:

- It must start with `SELECT`, `WITH` or `SHOW`. CoIsland reads the first word, past comments and parentheses, and refuses anything else before it reaches Snowflake.
- One statement only: CoIsland sends no statement count, so Snowflake's SQL API expects exactly one and rejects a second.
- The whole file runs, header included (comments are valid SQL), with the query tag `coisland:` plus the monitor's name, easy to find in query history. A check has two minutes and CoIsland reads up to 10,000 rows.
- The **Validation** form writes a plain `SELECT` and saves it as Custom SQL, with no `kind` line.

### GitHub

| Kind | The body | Identified by |
|---|---|---|
| `github.issues` | An issue search: `repo:acme/api is:issue is:open label:bug` | `ID` |
| `github.review-requests` | A search, `is:pr` added: `user-review-requested:@me is:open` | `ID` |
| `github.workflow-runs` | `repo:owner/name` (repeatable), optional `branch:`, `event:`, `workflow:`, `actor:`, `status:` (default `failure`). Last 7 days | `ID`, `ATTEMPT` |
| `github.releases` | `repo:owner/name` (repeatable), optional `-is:prerelease`. Last 30 days, never drafts | `ID` |
| `github.security-alerts` | `repo:` or `org:`, optional `tool:dependabot,code-scanning,secret-scanning` and `severity:critical,high`. Open alerts | `SOURCE`, `REPO`, `NUMBER` |

A search is compared only while it matches 100 items or fewer. Filter bodies are `key:value` words; an unknown one is refused with the list of valid ones.

### Jira

| Kind | The body | Identified by |
|---|---|---|
| `jira.issues` | Any JQL | `ID` |
| `jira.status` | JQL; alerts each time an issue is in a new status | `ID`, `STATUS` |
| `jira.comments` | JQL for the issues whose new comments alert | `COMMENT_ID` |
| `jira.sla` | Service Management JQL with `breached()` or `remaining("30m")` | `ID` |
| `jira.sprint` | Jira Software JQL, such as `sprint in openSprints()` | `ID` |

Issues are keyed on their id, which survives a move to another project. In GitHub and Jira bodies every line is sent as the query, so keep notes in the header.

### Columns for key and title

A Snowflake or Databricks SQL monitor's columns are its query's. Every other kind returns fixed ones; GitHub's and Jira's are:

- `github.issues`: REPO, NUMBER, TITLE, TYPE, STATE, AUTHOR, LABELS, ASSIGNEES, CREATED, UPDATED, URL, ID.
- `github.review-requests`: REPO, NUMBER, TITLE, AUTHOR, DRAFT, CREATED, UPDATED, URL, ID.
- `github.workflow-runs`: REPO, WORKFLOW, TITLE, BRANCH, EVENT, CONCLUSION, RUN, ATTEMPT, ACTOR, SHA, CREATED, URL, ID.
- `github.releases`: REPO, TAG, NAME, PRERELEASE, AUTHOR, PUBLISHED, URL, ID.
- `github.security-alerts`: REPO, SEVERITY, SUMMARY, SOURCE, NUMBER, STATE, SUBJECT, IDENTIFIER, CREATED, URL.
- Jira issue kinds: KEY, SUMMARY, STATUS, STATUS_CATEGORY, TYPE, PRIORITY, ASSIGNEE, REPORTER, PROJECT, CREATED, UPDATED, DUE, URL, ID, plus FROM_STATUS and CHANGED_AT for `jira.status`, SPRINT for `jira.sprint`.
- `jira.comments`: KEY, AUTHOR, BODY, SUMMARY, CREATED, COMMENT_ID, ID, URL.

## How the key decides what is new

With `alert: new-rows`, each check hashes every row's `key` columns (all columns without a `key`) and keeps the hashes in `state.json`, never the values. A row alerts when its key was not in the previous complete check, and again if it leaves and comes back. The first check only records what matches: the silent baseline.

So a column that changes needs a key. Without one, a row is its values, and a check timestamp, a running total or a "minutes running" column makes every row new at every check. `-- key: ORDER_ID` keeps an order the same row when its amount is corrected.

- **Count rules** have no baseline: `count > 0` fires on the first check if already true, then each time it becomes true again after clearing.
- **A new question starts over.** Changing the body, `key`, `connector`, `role`, `database`, `schema`, `timezone`, `alert` or `kind` checks at once from a new baseline. `name`, `title`, `sound`, `icon`, `every` and `warehouse` keep it.
- **Only complete results count.** Past 10,000 Snowflake rows or 100 GitHub search matches, nothing is compared: `More than the 10000 rows fetched match, so nothing is compared. Narrow the query.`
- **Old Jira items are not news.** A status change or comment dated over an hour before the previous check came back into the JQL, so it does not alert.

## Examples: four complete watch files

Save each in `~/.coisland/watches` with your own connector names, tables and projects.

### Failed Snowflake tasks

```sql
-- name: Failed tasks
-- connector: acme
-- database: ANALYTICS
-- every: 5m
-- alert: new-rows
-- key: QUERY_ID
-- title: {NAME} failed: {ERROR_MESSAGE}
-- sound: Basso
-- icon: exclamationmark.triangle

SELECT query_id, name, schema_name, error_code, error_message, scheduled_time
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
    SCHEDULED_TIME_RANGE_START => DATEADD('hour', -1, CURRENT_TIMESTAMP()),
    RESULT_LIMIT => 1000))
WHERE state IN ('FAILED', 'FAILED_AND_AUTO_SUSPENDED')
```

`INFORMATION_SCHEMA` belongs to the current database, hence the `database` line. The role must own the tasks, hold MONITOR or OPERATE on them, or hold MONITOR EXECUTION. Each run has its own `QUERY_ID`, so a task that fails again alerts again. The [Snowflake alerts guide](https://coisland.app/guides/snowflake-alerts-on-mac/) builds it step by step.

### Big orders, with a title template

```sql
-- name: Big orders
-- connector: acme
-- warehouse: COISLAND_WH
-- every: 15m
-- alert: new-rows
-- key: ORDER_ID
-- title: #{ORDER_ID} {CUSTOMER} ${AMOUNT} ({REGION})
-- sound: Glass
-- icon: cart
-- timezone: America/Toronto

SELECT order_id, customer, amount, region, placed_at
FROM analytics.sales.orders
WHERE amount > 10000
  AND placed_at > DATEADD('hour', -24, CURRENT_TIMESTAMP())
```

Placeholders take the row's values and the rest stays as written, so a row reads like `#48213 Northwind $12500.00 (EMEA)`. For volume instead, use `-- alert: count > 50` without the key.

### Failed GitHub Actions runs

```sql
-- name: Failed deploys
-- kind: github.workflow-runs
-- connector: acme_github
-- every: 5m
-- alert: new-rows
-- title: {WORKFLOW} failed on {BRANCH}: {TITLE}
-- sound: Basso
-- icon: xmark.octagon

repo:acme/api branch:main workflow:deploy.yml
```

Failed runs of `deploy.yml` on `main` from the last 7 days. A run is its `ID` and `ATTEMPT`, so a re-run that fails again alerts again. The token needs read access to Actions ([Connect GitHub](https://coisland.app/docs/connect-github/)).

### Jira requests about to breach their SLA

```sql
-- name: SLA at risk
-- kind: jira.sla
-- connector: acme
-- every: 5m
-- alert: new-rows
-- title: {KEY} {SUMMARY} ({PRIORITY})
-- sound: Sosumi
-- icon: timer

project = HELP AND "Time to resolution" < remaining("30m") AND "Time to resolution" != breached()
```

It matches requests with under 30 minutes left, and `!= breached()` leaves out those already past, as Atlassian's SLA JQL documentation suggests. Use your service project's SLA name ([Connect Jira](https://coisland.app/docs/connect-jira/)).

## Monitoring as code: watch files in git

The folder is plain text, so git works on it directly:

```sh
cd ~/.coisland/watches
git init
git add *.sql
git commit -m "Our CoIsland monitors"
```

CoIsland ignores the hidden `.git` folder, a `README.md` and any subfolder. A teammate clones the repository into a missing or empty watches folder:

```sh
git clone git@github.com:acme/coisland-watches.git ~/.coisland/watches
```

- **No secrets inside.** A file names its connector, never a credential. Every connector's token, Snowflake's included, lives in the login Keychain.
- **Agree on connector names**, or leave `connector` out so each Mac's default runs the file.
- **Each Mac keeps its own state.** Baselines, alerts and the check log live in `~/Library/Application Support/CoIsland/`, so each teammate starts from their own baseline, with their own token and role.
- **A pull is an edit**, picked up within a second. If that monitor is open in Settings, saving it is refused: `This monitor's file changed on disk since you opened it. Go back and open it again to see the change.`

What monitors watch across connectors is on [Monitors](https://coisland.app/monitors/); how data teams share checks, on [CoIsland for data teams](https://coisland.app/for/data-teams/).

## Watch files: common questions

### Can I create a monitor without the Settings window?

Yes. Save a `.sql` file with a body in `~/.coisland/watches`: CoIsland checks it within a second, and that first check is its silent baseline. [Getting started](https://coisland.app/docs/getting-started/) does the same from Settings.

### Why does my monitor alert on the same rows at every check?

A column in those rows changes between checks and the file has no `key`. Add `-- key:` with the columns that identify a row.

### Can a watch file change data in Snowflake?

CoIsland refuses any body that does not start with `SELECT`, `WITH` or `SHOW` before it reaches Snowflake, and sends one statement per check. The connector's role decides what it can read: see [Connect Snowflake](https://coisland.app/docs/connect-snowflake/) for a read-only role.

### Is it safe to commit watch files to git?

Yes: they hold connector names, never tokens. Check your SQL for literal values you would not share.