Cells

Cells are per-key counters and flags kept up to date as rows change. Only the values are stored, not the rows, so a set over millions of rows takes little memory. A flag turning on or off can run SQL, call a webhook or send mail.

CREATE CELLS picking ON lines KEY orderno (
  lines COUNT,
  done  COUNT WHEN qtypicked >= qty,
  ready BIT ALL qtypicked >= qty
)
  ON RISE ready DO UPDATE salesorders SET orderstatusid = 21 WHERE orderno = KEY TO orders;

Kinds of cell

Cell	Value
`COUNT [WHEN cond]`	number of rows, or of rows matching `cond`
`SUM(expr) [WHEN cond]`	sum
`MIN(expr) [WHEN cond]`, `MAX(expr) [WHEN cond]`	minimum, maximum
`BIT ALL cond`	on when the key has rows and every one matches
`BIT ANY cond`	on when at least one row matches
`BIT NONE cond`	on when the key has rows and none match

The key must be one column. A set can have up to 64 bits.

Actions

ON RISE <bit> runs when the bit turns on, ON FALL <bit> when it turns off. A set can have several.

Action	Form
SQL	`DO <statements> TO <profile>`; `KEY` is filled in. The profile needs `things_write=1`.
Webhook	`NOTIFY 'https://...' [HEADER 'Name: value']... [BODY '<template>']`
Email	`MAIL <mailer> TO '<a>, <b>' [CC '...'] [REPLY_TO '...'] SUBJECT '<template>' BODY '<template>' [HTML '<template>']`

Actions run on their own thread and do not slow the write that triggered them. A failed action is tried again on that key's next change. Bits already on when the set is created count as done; add ACT EXISTING to act on them.

Templates

NOTIFY and MAIL templates can use {{key}}, {{bit}}, {{edge}} (rise or fall), {{set}}, {{id}}, and any cell by name, such as {{lines}}. Values are escaped for where they go: URL-encoded in the URL, JSON-escaped in a NOTIFY body, HTML-escaped in HTML.

A NOTIFY URL can include a stored secret with {{secret:name}}; the URL must still start with https:// or http://. A NOTIFY without BODY sends a JSON object with the set, key, bit, edge and all values. Calls are POST, JSON, with a 5-second timeout.

Secrets and mailers

CREATE SECRET slack_path AS 'T000/B000/XXXX';     -- encrypted with AES-256-GCM
SHOW SECRETS;                                      -- names and dates, never values
DROP SECRET slack_path;

CREATE MAILER alerts OAUTH 'google' ACCOUNT 'alerts@example.com' FROM_NAME 'Stock alerts';
CREATE MAILER office USER 'alerts' PASSWORD SECRET smtp_pw HOST 'smtp.example.com' FROM 'alerts@example.com';
SHOW MAILERS;
DROP MAILER office;

An OAuth mailer signs in once with aios google auth <account>. An SMTP password is always a secret, never written in the statement. PORT and SECURITY 'starttls' | 'tls' | 'none' are optional; a password is never sent without encryption to a host other than 127.0.0.1.

Reading cells

SHOW CELLS;                       -- every set: keys, bits on, memory, actions
SHOW CELLS picking;               -- values for every key
SHOW CELLS picking WHERE ready;   -- keys whose bit is on
SHOW CELLS picking KEY '1001';    -- one key
SHOW CELLS picking ACTS;          -- the last 64 actions and their result
DROP CELLS picking;