[Bug]: A filter comparing created_at with a non-date() value returns HTTP 500 on PostgreSQL; /memories/list returns 500 for every invalid filter

[edwinyyyu]

Describe the bug

A filter that compares created_at with anything other than a date() literal reaches the database and fails there, and the failure surfaces as HTTP 500:

created_at > '2026-01-01T00:00:00Z'
created_at = '2026-01-01T00:00:00Z'
created_at > 5

The working form is created_at > date('2026-01-01T00:00:00Z').

Affected routes on PostgreSQL: POST /api/v2/memories/list (type: episodic and type: semantic) and POST /api/v2/memories/search with types: ["semantic"]. Episodic search on the event backend maps created_at to a JSON property and returns 200 with no match instead.

Cause: the parser types a value by its literal ('...' is a str, date('...') is a datetime), and compile_sql_filter binds a column-encoded leaf as column > value. SQLAlchemy types the bind by the Python value, not by the column, so PostgreSQL receives timestamp with time zone > character varying and rejects the statement:

asyncpg.exceptions.UndefinedFunctionError: operator does not exist: timestamp with time zone > character varying
sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) ...

Nothing maps that error to a status. On SQLite the same statement runs, as a text comparison of the ISO string against the stored YYYY-MM-DD HH:MM:SS.ffffff form, so it returns rows ordered by characters rather than by instant.

A second defect makes /memories/list worse than /memories/search for every invalid filter, not only this one: search_memories maps ValueError (which FilterParseError and the resolvers' "Unknown filter field" are) to 422, while list_memories catches nothing. filter: "created_at >" and filter: "bogus = 1" are 422 on search and 500 on list.

Reproduced at speedkick acb4f9a and main da7de4c; the filter parser and compiler are the same on both.

The report that prompted this also said each failed request leaves a database connection open, and that nine of them disabled half the workers while /health still returned 200. Not reproduced here: with pool_size: 2, max_overflow: 0, nine failing list requests on a single worker, pg_stat_activity stayed at 3 client backends throughout, the next valid request succeeded in 4 ms, and the pool reported zero checked-out connections after each failure at the store level (async with self._create_session() closes the session on the exception). A 500 does close the HTTP keep-alive socket, so a client reusing the connection sees a read error on its next request; that is the client-visible collateral I could find. If you can reproduce the connection leak, please post the SQLAlchemy and asyncpg versions and the database config.

Steps to reproduce

Server on PostgreSQL (resources.databases.<name>.provider: postgres), one project with one memory, then:

POST /api/v2/memories/list
{"org_id": "o1", "project_id": "p1", "type": "episodic", "filter": "created_at > '2026-01-01T00:00:00Z'"}
-> 500 Internal Server Error

POST /api/v2/memories/list
{"org_id": "o1", "project_id": "p1", "type": "episodic", "filter": "created_at > date('2026-01-01T00:00:00Z')"}
-> 200

Store-level, on any SqlAlchemyEpisodeStore bound to an asyncpg engine:

expr = parse_filter("created_at > '2026-01-01T00:00:00Z'")
await store.get_episode_messages(filter_expr=expr)
# sqlalchemy.exc.ProgrammingError: operator does not exist: timestamp with time zone > character varying

Expected behavior

The value's type is checked against the column before a statement is built, on every SQL-backed store, and a mismatch is an invalid-argument error: 422 with a message that names the field and points at date('...'). /memories/list answers invalid filters with 422 the way /memories/search does.

Environment

• OS: macOS 15 (server), pgvector/pgvector:pg16 in Docker
• MemMachine: speedkick acb4f9a (0.3.9.post2.dev20) and main da7de4c (0.3.10.dev22)
• Python 3.14.3, SQLAlchemy asyncpg dialect

Additional context

• [Bug]: Internal server error when filtering with created_at #959 was the same symptom on main in January; [GH-959] Add datetime filter expr support #960 closed it by adding the date() literal rather than by rejecting or coercing the string form, so the string form kept failing the same way.
• [vector store 14/14] Close the filter union, and make negation the complement on every backend (speedkick) #1616 (open, stacked on [vector store 1/12] Remove per-project filterable properties (speedkick) #1606) makes the parser reject a string in an ordering comparison (>, <, >=, <=), which turns that case into a FilterParseError. It leaves created_at = '...' and created_at > 5 reaching the database, and does not touch the list route, so list would still answer 500.