Memo query filtering
Memo query filtering gives the API, web client, and saved memo views one way to select memo data and control its order. A user-facing filter becomes a checked CEL expression, then a database-specific condition that the store combines with access, state, and pagination rules.
This page is a child of Memo lifecycle and collaboration. For content-derived memo properties, see Markdown content processing; for space scope, see Space management and membership; for archive transfer, see Memo archive import and export. The web client and API use this same filter boundary when they browse or save memo views.
Query model
Memo queries have two related languages. The filter field uses CEL names that describe memo properties. The order_by field uses API resource names for sort keys, so time fields use different names in the two fields.
Filter expressions
The filter language accepts boolean CEL expressions. It supports comparisons, &&, ||, !, membership checks, text matching, tag predicates, set operations, sizes, and time expressions. The expression must resolve to a boolean result, and each field must belong to the memo filter schema.
Common expressions include:
| Need | Example |
|---|---|
| Match state and visibility | pinned == true && visibility == "PUBLIC" |
| Match text | content.contains("roadmap") |
| Match a tag | "urgent" in tags or tag in ["urgent"] |
| Match a memo property | has_task_list && has_incomplete_tasks |
| Match a space | space == "spaces/team" or space == null |
| Match a recent time range | created_ts > now - duration("168h") |
| Match one tag predicate | tags.exists(t, t == "urgent") |
The language is deliberately smaller than general CEL. Unknown fields, non-boolean top-level values, legacy numeric logical operands, malformed regular expressions, and expressions that exceed the size limit fail before a query reaches the database. This keeps saved views and API filters subject to the same rules.
References: filter/README.md• L3-20 filter/schema.go• L259-279 filter/engine_test.go• L14-76 filter/engine_test.go• L758-773
Filter factors
The web client also represents filters as small factors such as contentSearch, tagSearch, visibility, displayTime, and memo-property flags. A factor is a user-interface value, not the database language itself. The web query helper maps each factor to a CEL condition before it sends the request.
This split lets the web interface keep simple, removable filter chips while the server keeps one schema-aware query language. Several factors become one && expression, and a selected saved view adds its own expression to the same set of conditions.
References: web/…/MemoFilterContext.tsx• L5-47 web/…/useMemoFilters.ts• L42-112 web/…/MemoFilters.tsx• L20-62
Compilation pipeline
The filter engine turns text into a safe query condition in three stages: CEL checking, condition-tree construction, and SQL rendering. This separation keeps the filter meaning independent from SQLite, MySQL, or PostgreSQL syntax.
CEL validation
The engine creates a CEL environment from the memo schema. CEL checks names and basic types first. The parser then applies filter-specific rules such as allowed operators and literal-only arguments for text and regular-expression matching.
The API validates a supplied filter with the database dialect selected by the running profile. Saved memo views use the same validation path when they are created or changed, so an invalid saved view cannot be stored.
References: filter/README.md• L7-11 filter/schema.go• L259-279 server/…/memo_view_service.go• L124-147 server/…/memo_view_service.go• L224-249
Condition tree
The parser converts the checked CEL expression into an intermediate representation (IR). The IR is a dialect-neutral tree that records the meaning of the query instead of its SQL form.
The main IR shapes are:
- Logical conditions for AND, OR, and NOT.
- Comparisons and membership conditions for scalar and list fields.
- Text and regular-expression conditions for supported strings.
- Boolean field predicates for flags such as
pinnedandhas_code. - List comprehensions for tag
exists,all, andexists_onepredicates. - Value expressions for
size()and timestamp accessors.
The parser also folds constant expressions. For example, now, timestamps, durations, arithmetic, and comparisons between fixed values can become literals in the IR. One compile uses one frozen value for now, which keeps all time checks in that filter consistent.
References: filter/README.md• L12-15 filter/ir.go• L3-15 filter/ir.go• L51-108 filter/ir.go• L110-163 filter/parser.go• L119-175 filter/parser.go• L513-631
SQL statements
The renderer walks the IR and returns a SQL fragment plus bound arguments. The store adds that fragment to its existing WHERE conditions. Values use placeholders, and PostgreSQL placeholder numbers account for arguments that the store added before the filter.
The renderer keeps database differences inside the filter boundary. It maps memo payload fields to JSON access, converts timestamp values to the form used by each database, and uses each database's boolean, text-match, and regular-expression rules. A condition that is always true produces no SQL, while one that is always false produces 1 = 0.
Text values are bound as arguments rather than inserted into SQL. Scalar text matching is case-insensitive and escapes %, _, and \\ so user text stays literal. Tag equality and tag text predicates are evaluated per array element with case-sensitive matching. Tag all() requires a non-empty list, and MySQL uses row counts for tag comprehensions so its query plan keeps the outer memo row attached.
The overall flow is:
flowchart LR
A[Filter text] --> B[CEL schema check]
B --> C[Memo condition IR]
C --> D[Dialect renderer]
D --> E[SQL fragment and bound args]
E --> F[Store memo query]
References: filter/README.md• L16-20 filter/README.md• L47-105 filter/render.go• L24-59 filter/render.go• L495-555 filter/render.go• L571-683 filter/helpers.go• L8-24 filter/engine_test.go• L139-305 filter/engine_test.go• L418-501
Memo schema
The memo schema defines which names a filter can use and how each name relates to memo data. It covers direct memo columns, values in the memo payload, and aliases that make tag membership readable.
Scalar fields
Scalar fields describe values stored in ordinary memo columns or related memo records. Filters can compare content, creator, creator_id, created_ts, updated_ts, pinned, visibility, and space with the operators allowed by their type.
The filter language uses created_ts and updated_ts for timestamp comparisons. These are logical timestamps even when a database stores them in a different physical form. creator represents the user resource name, while creator_id represents the numeric store identity. space represents a space resource name such as spaces/team; space == null selects memos without a space. Space filters support equality only.
References: filter/schema.go• L90-188 filter/schema.go• L259-276 filter/render.go• L312-350 filter/engine_test.go• L78-110
Payload fields
Payload fields expose memo properties without making callers know the payload layout. Boolean names include has_task_list, has_link, has_code, and has_incomplete_tasks. has_location is a presence test for a non-null location value.
These fields support boolean use such as has_link and boolean equality such as has_link == false. Missing JSON data does not count as a location. An explicit JSON null also does not count. An empty location object still counts as present. Missing JSON boolean flags count as false because memo payloads omit false flags.
References: filter/schema.go• L202-257 filter/render.go• L128-155 filter/render.go• L365-417 filter/render.go• L697-755 filter/engine_test.go• L565-756
Tag predicates
Tags are exposed as a string list through tags. The tag name is a virtual alias intended for list-style expressions such as tag in ["work"]; direct tag equality is not the supported membership form.
The engine checks individual tag elements rather than searching serialized JSON text. Equality and text predicates on tags are case-sensitive. The supported list forms are:
| Form | Meaning |
|---|---|
"work" in tags | The tag list contains work. |
tag in ["work", "urgent"] | The memo contains at least one listed tag. |
tags.exists(t, t.startsWith("proj")) | At least one tag matches. |
tags.all(t, t.contains("work")) | Every tag matches, and the list is not empty. |
tags.exists_one(t, t == "work") | Exactly one tag matches. |
sets.contains(tags, ["work", "urgent"]) | The tag set contains all listed values. |
sets.intersects(tags, ["work", "urgent"]) | The tag set contains any listed value. |
sets.equivalent(tags, ["work", "urgent"]) | The tag set has exactly the listed values. |
An empty tag in [] expression is always false. Tag hierarchy depends on the tag set already containing implied ancestor tags. The filter engine does not treat a name prefix as a hierarchy match.
References: filter/schema.go• L189-201 filter/parser.go• L262-309 filter/parser.go• L703-772 filter/render.go• L381-493 filter/render.go• L558-683 filter/engine_test.go• L139-305
Time expressions
Timestamp fields accept comparisons with fixed timestamps, now, and durations. timestamp("...") accepts an RFC3339 value, while timestamp(<epoch-seconds>) accepts an epoch value. Arithmetic combines these values for relative ranges.
Timestamp accessors support calendar parts such as year, month, date, day of week, hour, minute, and second. The engine uses CEL's value bases, including a zero-based month and zero-based day of week. Accessors on now fold at compile time, so a saved expression can compare a memo date with the current calendar date each time it runs.
References: filter/schema.go• L127-151 filter/parser.go• L633-701 filter/render.go• L185-254 filter/README.md• L51-56 filter/README.md• L86-95
Query assembly
The store combines the compiled filter with ordinary memo constraints. This lets one query apply user access rules, memo state, comment exclusion, selected IDs, visibility limits, the user filter, and pagination without asking callers to build SQL.
Store selection
FindMemo is the store-level query model. Its Filters field holds one or more CEL expressions, while separate fields hold IDs, creator and row state, visibility, comment exclusion, limits, offsets, and ordering choices.
Each database driver uses the shared filter engine with its own dialect. The store appends every filter as a grouped condition, and multiple filter strings therefore narrow the result together. The returned memos are then read from the same query that applied the filter.
References: store/memo.go• L57-82 filter/helpers.go• L8-24 store/…/memo.go• L54-63 store/…/memo.go• L51-59 store/…/memo.go• L62-70
API requests
ListMemos validates a request filter before adding it to FindMemo. It also applies request state and visibility rules around that filter. Normal lists exclude comments by default, archived lists are limited to the current creator, and anonymous users only receive public memos.
The service uses a page size and page token with the final ordering. It loads related reactions, attachments, and relations after the filtered memo rows are selected, so those related records enrich the result rather than change which memos match.
References: server/…/memo_service.go• L207-295 server/…/memo_service.go• L305-373 proto/…/memo_service.proto• L298-340
Saved memo views
A memo view is a user-owned saved filter with a title and an expression. The API validates the expression when the view is created or updated, and the web client adds the selected view expression to the current ad hoc filters.
The built-in Tasks view is a fixed expression for memos with task lists and incomplete tasks. Other views use the saved expression, which means the same server filter rules apply to built-in and user-defined views.
References: server/…/memo_view_service.go• L124-156 server/…/memo_view_service.go• L159-200 web/…/useMemoFilters.ts• L75-80 web/…/memo-views.ts• L3-10
Ordering
The API accepts a comma-separated order_by value with optional asc or desc directions. Supported names are pinned, create_time, update_time, and name; create_time and name use the memo creation time, while update_time uses the update time.
Pinned ordering always places pinned memos first. The first time field controls the time sort when several time fields are supplied. If no order is supplied, results use creation time descending. The store adds memo ID descending as a final tie-breaker, which keeps pages stable when timestamps match.
References: server/…/memo_service_query.go• L11-67 server/…/memo_service.go• L239-247 store/…/memo.go• L108-122
Web query state
The web client keeps filter factors in a shared context and mirrors them in the URL's filter query parameter. This makes a filtered memo list linkable and lets navigation restore the same selection.
URL synchronization
The URL format is a comma-separated list of factor:value pairs. Values are URI encoded, and a factor with no value is allowed. The context reads the URL at startup, updates state when the URL changes, and replaces the URL when the user changes state.
The context removes duplicate factors by their factor-and-value key when filters are added. It can also remove one factor, remove all values for a factor, or clear both filters and the selected memo view.
References: web/…/MemoFilterContext.tsx• L21-47 web/…/MemoFilterContext.tsx• L65-101 web/…/MemoFilterContext.tsx• L103-132
Filter translation
useMemoFilters gathers the current factors with optional creator, visibility, pin, and memo-view inputs. It emits one CEL expression for the request. A calendar factor becomes a local-day timestamp range, while text and tag factors become escaped string literals.
The resulting expression is the handoff between the web browsing experience and the API query model. The UI does not need to know how payload JSON or database dialects represent the same memo property.
References: web/…/useMemoFilters.ts• L22-40 web/…/useMemoFilters.ts• L58-112 web/…/MemoFilters.tsx• L64-105
Change checklist
When these files or sections are changed, remember to consider these:
- A new filterable memo field needs one matching schema name, CEL environment entry, value rule, and renderer mapping.
- A payload property change must keep the filter JSON path aligned with payload generation and all three database dialects.
- A space filter must keep its resource-name and null semantics aligned with space assignment and access rules.
- A change to placeholder handling must preserve numbering after store query arguments are added.
- A new web filter factor must map to a valid CEL expression and preserve URL encoding and factor removal behavior.
- A change to
order_bynames must keep the protobuf contract, parser, and driver ordering flags aligned. Do not confuse itscreate_timeandupdate_timenames with filtercreated_tsandupdated_ts. - A saved memo view change must preserve validation before persistence and compatibility with the web view selector.
- A change to tag matching must preserve exact, case-sensitive element checks in SQLite, MySQL, and PostgreSQL. Do not replace them with JSON text matching.
- A change to
has_locationmust preserve the distinction between a missing or null value and a present empty object.
Important failure modes
| Trigger | Consequence | Guard |
|---|---|---|
| Unknown field, non-boolean top-level value, unsupported operator, or oversized expression | The API or saved-view request is rejected before query execution. | Keep the CEL environment, schema fields, parser rules, and expression limit in sync. |
| A space value is rendered without its resource-name or null handling | Memos can match the wrong space or unassigned memos can be omitted. | Preserve the schema's space expression and equality-only rule in every dialect. |
| Filter renders for the wrong database dialect | JSON, boolean, timestamp, or placeholder behavior can return wrong rows or fail at execution. | Route each store driver through the matching dialect and keep dialect logic in the renderer. |
| A filter argument is inserted as SQL text instead of a bound value | User input can change query meaning or break matching. | Use renderer arguments and placeholder offsets for all user values. |
Empty tags input is used with all() | A vacuous true result could make a memo match even though no tag satisfies the predicate. | Preserve the non-empty check in list-comprehension rendering. |
MySQL tag comprehensions use a correlated EXISTS form | MySQL can silently drop the outer-row dependency and return no tag matches. | Keep count-based rendering for MySQL and retain cross-dialect coverage. |
| Tag equality or text matching uses serialized JSON or case-folded comparison | Tags with case or pattern characters can match the wrong memos. | Evaluate individual elements with exact equality or escaped, case-sensitive matching. |
| A date factor contains an invalid local calendar day | The intended day filter is not emitted, which can broaden the query. | Validate the YYYY-MM-DD value and only build a range for a real day. |
| A filter is applied without the service access conditions | A valid expression could expose memos outside the caller's visibility or ownership scope. | Keep access, archived-state, and anonymous visibility constraints in ListMemos. |
| A time sort omits the final ID tie-breaker | Rows with equal timestamps can move between pages. | Preserve the store driver's ID-descending final sort key. |
Key files
| File | Why to read it |
|---|---|
| filter/README.md | Short description of the filter pipeline and its supported semantics. |
| filter/schema.go | Defines memo filter names, types, space and payload mappings, aliases, and supported SQL dialects. |
| filter/engine.go | Shows the public compile and render boundary. |
| filter/parser.go | Shows how CEL expressions become the intermediate condition model. |
| filter/ir.go | Defines the condition, value, tag predicate, and comprehension shapes. |
| filter/render.go | Shows how the shared meaning becomes dialect-specific SQL and bound arguments. |
| filter/helpers.go | Connects compiled filter statements to store WHERE clauses. |
| filter/engine_test.go | Captures the contract for validation, space and payload semantics, exact tag matching, and cross-dialect rendering. |
| store/memo.go | Defines the store-level memo query model and ordering flags. |
| store/…/memo.go | Illustrates filter application, result ordering, pagination, and memo row loading. |
| server/…/memo_service.go | Shows how API filters combine with access, state, and pagination rules. |
| server/…/memo_service_query.go | Defines the API order_by contract and its supported sort keys. |
| server/…/memo_view_service.go | Shows validation and persistence of user-owned saved filters. |
| web/…/MemoFilterContext.tsx | Defines the browser filter state and URL contract. |
| web/…/useMemoFilters.ts | Translates web filter factors and memo views into CEL. |