Memo Filter Engine

This package houses the memo-only filter engine that turns standard CEL syntax into SQL fragments for the subset of expressions supported by the memo schema. The engine follows a three phase pipeline inspired by systems such as Calcite or Prisma:

1. Parsing – CEL expressions are parsed with cel-go and validated against the memo-specific environment declared in schema.go. Only fields that exist in the schema can surface in the filter, and non-standard legacy coercions are rejected.
2. Normalization – the raw CEL AST is converted into an intermediate representation (IR) defined in ir.go. The IR is a dialect-agnostic tree of conditions (logical operators, comparisons, list membership, etc.). This step enforces schema rules (e.g. operator compatibility, type checks).
3. Rendering – the renderer in render.go walks the IR and produces a SQL fragment plus placeholder arguments tailored to a target dialect (sqlite, mysql, or postgres). Dialect differences such as JSON access, boolean semantics, placeholders, and LIKE vs ILIKE are encapsulated in renderer helpers.

The entry point is filter.DefaultEngine() from engine.go. It lazily constructs an Engine configured with the memo schema and exposes:

engine, _ := filter.DefaultEngine()
stmt, _ := engine.CompileToStatement(ctx, `has_task_list && visibility == "PUBLIC"`, filter.RenderOptions{
	Dialect: filter.DialectPostgres,
})
// stmt.SQL  -> "((memo.payload->'property'->>'hasTaskList')::boolean IS TRUE AND memo.visibility = $1)"
// stmt.Args -> ["PUBLIC"]

Core Files

SQL Generation Notes

• Placeholders — ? is used for SQLite/MySQL, $n for Postgres. The renderer tracks offsets to compose queries with pre-existing arguments.
• JSON Fields — Memo metadata lives in memo.payload. The renderer handles JSON_EXTRACT/json_extract/->/->> variations and boolean coercion.
• Time Fields — created_ts, updated_ts, and attachment create_time are CEL timestamp values. Express instants with the now variable, duration("…") (e.g. created_ts >= now - duration("24h")), or timestamp("2006-01-02T15:04:05Z") / timestamp(<epoch-seconds>). These fold to epoch seconds at compile time — now is frozen once per compile (injectable for tests via the engine clock) — so the backing columns stay unchanged.
• Tag Operations — tag in [...] and "tag" in tags become JSON array predicates. SQLite uses LIKE patterns, MySQL uses JSON_CONTAINS, and Postgres uses @>.
• Boolean Flags — Fields such as has_task_list render as IS TRUE equality checks, or comparisons against CAST('true' AS JSON) depending on the dialect.
• String Matching — content.contains(x), content.startsWith(x), and content.endsWith(x) render as case-insensitive LIKE/ILIKE with LIKE metacharacters (%, _, \) escaped. Available on scalar string fields whose schema sets SupportsContains (memo content; attachment filename, mime_type).
• Regex — field.matches("pattern") renders to ~ (Postgres) or REGEXP (MySQL/SQLite). SQLite uses a Go-backed regexp function registered in store/db/sqlite/functions.go. Patterns are validated at compile time against Go's RE2 via cel.ValidateRegexLiterals(). Caveat: regex syntax differs per engine (Go RE2 on SQLite, POSIX ERE on Postgres, ICU on MySQL 8.0+), so engine-specific patterns may not be portable.
• Tag all() / exists_one() — tags.all(t, <pred>) matches only non-empty tag sets where every element satisfies the predicate; tags.exists_one(t, <pred>) matches when exactly one element does (COUNT(...) = 1). Both iterate per-element (json_each / jsonb_array_elements_text / JSON_TABLE).
• Timestamp Accessors — created_ts.getFullYear(), getMonth(), getDate(), getDayOfMonth(), getDayOfWeek(), getDayOfYear(), getHours(), getMinutes(), getSeconds() render to date-part extraction (strftime / EXTRACT / YEAR/MONTH/…). Results are normalized to CEL's base (0-based month, 0-based day-of-week with 0 = Sunday). The same accessors on now fold to literal date parts of the frozen evaluation time (UTC), so saved filters like created_ts.getMonth() == now.getMonth() && created_ts.getDate() == now.getDate() ("on this day") re-resolve on every compile. Extraction is UTC on SQLite/Postgres (epoch columns); on MySQL the TIMESTAMP column is read in the session time zone. A timezone argument is not supported.
• Set Operations — ext.Sets(): sets.contains(tags, [...]), sets.intersects(tags, [...]), and sets.equivalent(tags, [...]) desugar to exact-membership checks (AND / OR of "v" in tags); equivalent adds a size(tags) length check (relies on tags being a set).
• size() — size(tags) renders to JSON array length; size(content) (and other string fields) render to LENGTH / CHAR_LENGTH (MySQL) for code-point counts.
• Arithmetic — +, -, *, /, % constant-fold on literal/now/duration operands (division and modulo guard against a zero divisor).

Typical Integration

1. Fetch the engine with filter.DefaultEngine().
2. Call CompileToStatement using the appropriate dialect enum.
3. Append the emitted SQL fragment/args to the existing WHERE clause.
4. Execute the resulting query through the store driver.

The helpers.AppendConditions helper encapsulates steps 2–3 when a driver needs to process an array of filters.