Refuse a filter value of the wrong type for a datetime column, and answer an invalid list filter with 422 (port of #1620) - #1786

Fixes #1619.

Port

Copy of #1620, merged into speedkick as 8d7b832a1, onto main, which has the same parser, compiler, and route, so the same defect. The one conflict is in list_memories: main lists every memory type when a request names none, and the port keeps that default inside #1620's 422 mapping.

Purpose of the change

A filter such as created_at > '2026-01-01T00:00:00Z' reached PostgreSQL as timestamp with time zone > character varying, failed there, and came back as HTTP 500 from /memories/list (episodic and semantic) and from /memories/search with types: ["semantic"]. The same filter ran on SQLite as a text comparison. SQLAlchemy types a bind by its Python value, so the mismatch was invisible until the database rejected it.

Three changes, one per concern, in one commit here as #1620 merged squashed:

1. compile_sql_filter refuses the pairing before a statement is built. A column-encoded leaf pairs a DateTime column with a datetime value and nothing else; a str, an int, or an IN list against created_at (or timestamp on the segment store), and a date() literal against a non-datetime column, raise ValueError with a message that names the field and points at date('...'). This holds on every SQL-backed store that resolves a datetime column: the episode store, the segment store, and both semantic feature stores.
2. list_memories maps ValueError to 422, as search_memories already did. Before, every invalid filter on list (a syntax error, an unknown field, this mismatch) was a 500.
3. The filter field's description (SpecDoc.FILTER_MEM, and the two copies in docs/openapi.json) names date('...') as the form a datetime field compares with. Only those two descriptions change in the spec; a full local regeneration also drifts on ValidationError and info.version, which the main workflow owns.

The value is rejected rather than coerced: the filter language types a value by its literal, date() is the datetime literal, and a properties_json leaf already matches only a value of the declared type. Coercing a string here would make the column encoding disagree with the others.

Relation to #1616: that PR makes the parser reject a string in an ordering comparison, which covers >/</>=/<= with a str. This change is at the column boundary, so it also covers =, !=, an integer, and an IN list, and it does not depend on the vector store stack. The two compose; the rebase of either over the other is a few lines in _compile_column_leaf.

Stack

21 open PRs: three independent PRs, and the vector store tree of short parallel branches. Every PR in the tree has feat/horizontal-scaling as its GitHub base, and the independent PRs have main. The branches are in a fork, and a pull request can target only this repository's branches, so the on column gives the order the PRs build on each other. A stacked PR's diff on GitHub includes the PRs under it until they merge.

Independent of the vector store tree, directly on main:

The vector store tree. Each PR builds on the one in its on column; PRs on the same parent are parallel branches and do not depend on each other. #1631 is closed, superseded by #1733–#1736, which hold its changes split in four, with review changes since. #1702 and #1670 sit beneath #1627, whose code depends on them. Until the PRs under it merge, their changes show in a stacked PR's diff.

This PR is its one commit, 899719556, directly on main, independent of the vector store tree: review and merge it in any order.

Verification

As measured on speedkick for #1620:

• test_sql_filter_util.py: the fixture model gains a DateTime column; four mismatched filters raise with "holds a datetime", name > date(...) raises with "does not hold a datetime", and created_at > date(...) / >= date(...) compile and select the expected rows. test_bounds_are_normalized_to_utc's fixture column becomes a DateTime, which is what it models.
• test_episode_storage.py: created_at > '...', created_at = '...' and created_at > 5 raise ValueError from both get_episode_messages and get_episode_messages_count, on SQLite and (under -m integration) PostgreSQL.
• test_router.py: /memories/list answers a ValueError from list_search with 422.

uv run pytest packages/server/server_tests: 1937 passed, 3 skipped. -m integration on test_episode_storage.py (PostgreSQL via testcontainers): 24 passed. ruff check, ruff format --check, and ty check --project packages/server clean.

End to end on a server backed by pgvector/pgvector:pg16 (pool_size: 2, max_overflow: 0): the text form on list (episodic, semantic) and on semantic search answers 422 with Field 'created_at' holds a datetime; compare it with date('...'), not str; nine of them in a row leave pg_stat_activity unchanged and the next valid request answers 200 in 4 ms. The same run on the unpatched tree answers 500 at each of those points. The connection leak in the original report did not reproduce on either tree; details in #1619.

The port, on main at ad8ff24b0: ruff check, ruff format --check, and ty check are clean, and uv lock --check passes; the full server suite without integration tests passes (2114 passed, 3 skipped); in test containers, the episode store's, the filter compiler's, and the server routes' integration tests pass against PostgreSQL (92 passed). It merges with feat/horizontal-scaling without conflicts.

🤖 Generated with Claude Code