01Write an expression and watch it compile
The widget below is not a simulation. It imports parse, describe and compile from the same package the API calls, so the plain-English reading and the parameterised SQL you see are the ones your instance would produce — down to the character offset of an error.
If the English reading says something you did not mean, the parser and you disagree, and the parser wins.
Runs parse, describe and compile from @mailysend/segments — the same functions the API calls.
((unsubscribed = ?) AND (last_open_at IS NOT NULL AND last_open_at > ?))
Bound parameters: [0,"2026-08-11T20:09:25.736Z"]. Every value you typed is a parameter; the only identifiers in that fragment came from the column registry, which is why there is no expression you can write that becomes SQL.
| What to watch as you type | Why it matters |
|---|---|
| The English reading | It comes from the AST, not from the text you typed. It is the only view of what the parser actually built. |
| The SQL is a fragment | Always parenthesised, always a WHERE clause body, so it can be safely ANDed onto whatever scoping the caller adds for workspace and audience. |
| The parameter list is separate from the SQL | Every value you typed is in it, and none of it is in the SQL. That separation is the whole safety story, and it gets a section of its own below. |
Errors carry two pieces of metadata beyond the message. An offset, which is the character position the segment builder puts a caret under, and a kind. The kind tells you which half of the pipeline objected, and that is the difference between re-reading your syntax and re-reading your types.
- The parser could not build an AST at all
- An unknown field, an unterminated string, a stray character
- Raised before the compiler ever sees the expression
- The expression parsed fine and then asked for something incoherent
- open_count = "ten", created_at contains "2026"
- Raised by the compiler, which is why it arrives with a field label in it: “open count is a number, not text”
02The grammar, in one page
The language is small on purpose. A predicate is a field, an operator and a value; predicates combine with and, or and not; and parentheses group. Everything below fits on this page because there is nothing else.
Field, operator, value — combined with three boolean words and parentheses. There is no fourth production and no escape hatch.
| Production | What it means | Example |
|---|---|---|
| = != > >= < <= | The comparison operators. == normalises to = and <> to !=, so muscle memory from another language does not cost you an error. | open_count >= 3 |
| contains / starts_with / ends_with | The three word operators for text. Each needs a text field on the left and a quoted string on the right. | email ends_with "@example.com" |
| in [a, b, c] | Membership. Takes at least one value — an empty list is a syntax error rather than a predicate that is false for everybody, because an empty list is nearly always a bug in whatever generated it. A null inside the list is refused. | data.plan in ["pro", "team"] |
| is null / is not null | The only way to ask about absence. = null is refused with a message telling you to use is null. | last_click_at is null |
| a bare boolean column | A predicate on its own. Any other column standing alone without an operator is an error, because it is almost always a half-typed comparison rather than an intention. | unsubscribed means unsubscribed = true |
| a duration: <number><unit> | A number glued to a unit — s, m, h, d, w. Anything else glued to a number is rejected by name rather than silently split into a number and an identifier. Compares only against a date field, and reads backwards from now. | last_open_at > 30d — “opened more recently than thirty days ago” |
| a string | Single or double quotes, with backslash escapes. | first_name = 'Ada' |
| data.key / data["key"] | A custom merge field under the fixed data column; the bracket form is for keys with a space in them. A name containing a double quote, a backslash or a control character is refused outright rather than mangled, because those would need JSON-path-level escaping that SQLite’s json1 does not define. | data["seat count"] > 5 |
The duration threshold is resolved at compile time, not at parse time — which is what lets a parsed expression be cached across hours and still mean the right thing when it is recompiled.
| Operator | Binds | Associativity |
|---|---|---|
| not | tightest (3) | prefix — applies to the predicate immediately after it |
| and | next (2) | left |
| or | loosest (1) | left |
If you want a different reading, write the parentheses; the parser will not guess.
not subscribed and bounced # parses as (not subscribed) and bounced # i.e. unsubscribed = true AND bounce_count > 0 not (subscribed and bounced) # parses as the whole conjunction, negated # i.e. NOT (unsubscribed = false AND bounce_count > 0)
03The twelve columns you can name
This table is generated from the registry itself, so it cannot drift from what your instance accepts. Twelve columns, plus the JSON data column for whatever custom fields you import.
The counters and timestamps are denormalised onto the contact row rather than computed from the event table, which is what lets a segment be a single indexed WHERE clause instead of a join whose cost grows with a contact’s history.
| column | type | nullable | reads as |
|---|---|---|---|
| string | no | ||
| first_name | string | yes | first name |
| last_name | string | yes | last name |
| unsubscribed | boolean | no | unsubscribed |
| open_count | number | no | open count |
| click_count | number | no | click count |
| send_count | number | no | send count |
| bounce_count | number | no | bounce count |
| last_open_at | timestamp | yes | last open |
| last_click_at | timestamp | yes | last click |
| last_send_at | timestamp | yes | last send |
| created_at | timestamp | no | creation date |
| Denormalised onto the contact row | Instead of |
|---|---|
| open_count, click_count, send_count, bounce_count | Counting rows in the event table per contact. |
| last_open_at, last_click_at, last_send_at | A MAX() over the event table per contact. |
| no last_bounce_at | The one gap the trade leaves, and the subject of the next section. |
A deliberate trade: a single indexed WHERE clause, at the cost of the one question the columns cannot answer.
| You write | You get |
|---|---|
| open_count = "ten" | open count is a number, not text |
| created_at contains "2026" | ‘contains’ needs a text field, but creation date is a timestamp |
| frist_name = "Ada" | unknown field ‘frist_name’ — did you mean ‘first_name’? |
Types are enforced at compile time and the error names the field in the words the builder shows. A timestamp compares against a duration or an ISO string, and nothing else.
The data column is the one exception to typing. Its type is whatever the contact happened to store, so it accepts any literal and any operator that makes sense for one.
04Sugar, and the one place it is approximate
Four families of shorthand exist so that the common queries read like sentences instead of like column arithmetic. They are not a second language: each one expands to comparisons on the columns you have already seen.
Sugar survives parsing rather than being expanded on the spot — the compiler expands it, the printer does not — which is why the English reading says “opened in the last 30 days” and not “last open is not null and after 2026-08-10T14:00:00Z”.
| You write | It means |
|---|---|
| subscribed | unsubscribed = false |
| opened_last_30d | last_open_at > (now − 30 days) |
| clicked_last_7d | last_click_at > (now − 7 days) |
| sent_last_90d | last_send_at > (now − 90 days) |
| never_opened | open_count = 0 |
| never_clicked | click_count = 0 |
| bounced | bounce_count > 0 |
| bounced_last_30d | bounce_count > 0 — see below |
| The windowed pattern | What it accepts |
|---|---|
| The verb | opened, clicked, sent or bounced |
| The window | Any count and any unit, so opened_last_36h and clicked_last_2w both work without anyone adding a keyword for them |
| A name collision | A real column always wins over sugar with the same name, so unsubscribed reads as the column and gets the bare-boolean treatment rather than acquiring a second meaning |
- You are deciding whether to keep mailing someone
- You want the suppression list, not a segment
- A hard bounce suppresses the address directly, and the send path checks that before it checks anything you wrote
- You are building a cleanup list
- bounced and bounced_last_30d both do exactly what you want
- This is suppression hygiene, not targeting
05Why injection is structurally impossible here
The interesting claim here is not “we escape user input”. It is that there is no code path in which text you typed becomes SQL text at all. That property is enforced by one small file, and it is worth understanding why that is enough.
The set of column names that can appear in a query is fixed at build time. Everything else you typed is a bound parameter — including the JSON path of a custom field.
| Where it comes from | How it reaches SQL |
|---|---|
| A field name you typed | Resolved against the registry at parse time. If it is not there, parsing fails with an offset and it never becomes an AST node — so the compiler can never be handed a field it would have to trust. |
| An operator | A keyword chosen by a switch over a closed union. |
| A physical column name | The sql value read out of that hard-coded table. |
| A literal, an in-list, a LIKE pattern | A bound parameter. There is no sanitising step, because there is nothing to sanitise. |
| A custom field’s JSON path | Also a bound parameter: json_extract(data, ?), never json_extract(data, '$."plan"'). That is precisely why an arbitrary custom field name is harmless — the name is data, in the same sense the value is. |
# subscribed and email ends_with "@example.com" SQL ((unsubscribed = ?) AND (email LIKE ? ESCAPE '\')) params [0, "%@example.com"]
| Detail | Why it is there |
|---|---|
| The ESCAPE clause | LIKE has its own wildcards, so a literal % or _ in a pattern you typed is escaped before binding — otherwise email contains "50%" would quietly match far more people than you asked for. Not a security bug, a correctness bug, and the kind that hides for a year. |
| The explicit __proto__ exclusion | The lookup is Object.hasOwn(COLUMNS, name) && name !== '__proto__'. The own-property check already does most of the work, but prototype-chain surprises are exactly the class of bug that turns a lookup table into a bypass, and a one-token guard is cheaper than being clever about why it is not needed. |
| The 100-parameter budget | D1 caps a statement at 100 bound parameters, and every value in your expression is one. So when the runner chunks a list of contact ids, it chunks against the budget left over after the expression rather than a fixed constant — an expression with forty literals in it gets smaller id chunks, automatically. |
One more property, about cost rather than safety. A full recomputation is a resumable keyset walk over the contacts index, one bounded page at a time, committed per page. No query in this subsystem is allowed to scale with the size of your audience — the same rule that shapes how a broadcast goes out.
06What "not" means when a column is null
SQL’s three-valued logic is where well-meaning segment builders quietly produce the opposite of what the marketer asked for. The fix is in the registry: every column declares whether it is nullable, and the compiler emits a guard when it is.
A comparison against a nullable column is wrapped in a NULL guard, so negating it includes exactly the people a marketer means rather than silently excluding everyone who has no value at all.
- not clicked_last_7d expands to NOT (last_click_at > ?)
- For someone who has never clicked, last_click_at is NULL, so NULL > ? is NULL
- NOT NULL is NULL, and a WHERE clause treats NULL as false
- The people who have never clicked are excluded from “has not clicked in the last seven days” — the exact opposite of the request
- It fails silently: a smaller segment, no error, no reason to suspect anything
- NOT (last_click_at IS NOT NULL AND last_click_at > ?)
- The inner expression is a real boolean for every row — false for a contact with no click, rather than NULL
- Negating it includes exactly the people a marketer means
- Emitted only for nullable targets, so the common queries stay readable
# not clicked_last_7d (NOT (last_click_at IS NOT NULL AND last_click_at > ?)) # not opened_last_30d, but on a NON-nullable column: # open_count is nullable=false, so no guard is emitted (NOT (open_count > ?))
| Target | Guarded? | Why |
|---|---|---|
| open_count, send_count, unsubscribed, email, created_at | No | Declared non-nullable, so their comparisons compile to the clause you would have written by hand. |
| last_click_at and the other nullable columns | Yes | A missing value would turn a negation into silent exclusion. |
| is null / is not null | Never | They are already total, and negating them is exact, so wrapping them would only add noise. |
| data.* | Always | Treated as nullable regardless of what the contacts happen to contain, because a custom field present on most of your list and missing on the rest is the normal case, not the exception. |
The practical habit. When a segment returns a surprising count, negate it and check that the two counts add up to your audience. If they do not, the difference is sitting in a null somewhere, and the English reading in the playground will usually tell you which column it is.
What just happened
You can now read any segment expression and predict both the English reading and the SQL it compiles to: twelve columns plus a JSON data column, a handful of operators, and three boolean words whose precedence you have seen written down. The thing most likely to bite you later is nullability — a comparison against a nullable column carries a NULL guard so that not means what you meant, and the one place the language is deliberately approximate is a windowed bounce, which degrades to “has ever bounced” and says so in the description rather than quietly joining an event table.