List search, sort and filter (server-side) — design

Status: built, integrated with main’s table export on branch integrate-list-query (2026-10-12), not merged to main; no browser pass. Approved in chat 2026-10-06. Added to docs/ROADMAP.md (Phase 6). The Server section and the rulings below describe what was built.

Why

27 list screens had no way to find a row. A browser-only search (committed 2026-10-06, DataTable searchText) only sees rows already loaded; seven of the lists are keyset-paged, so it had to fetch every page first. Search, sort and filter belong on the server, where paging stays correct at any size. The browser-side search is removed from these screens when each is converted.

Screens and endpoints

Screen(s)	Endpoint	Paged today
Suppliers, Purchase invoices, Supplier payments, Payment batches, WHT remittances	/payables/{suppliers,invoices,payments,batches}, /payables/wht/remittances	invoices, payments, batches, remittances
Customers, Receipts	/receivables/{customers,receipts}	receipts
Journal entries	/accounting/entries	yes, by limit/offset (kept)
Purchase requisitions, Purchase orders, Goods receipts, Purchase returns, Quotations, Sales orders, Deliveries, Sales returns	/procurement/* (requisitions, orders, receipts, returns) and /sales/* (quotations, orders, deliveries, returns); one platform list function, kind	keyset; the screen fetches one page of 200 (unchanged)
Operations reports (open orders, GRNI aging, delivered-not-invoiced)	procurement and sales report endpoints	no
Items, Locations	/inventory/{items,locations}	no
Fixed asset register, Asset classes	/assets, /assets/classes	no
Float register	/pettycash/floats	no
Bank accounts	/banking/accounts	no
e-Invoices	/einvoice/submissions	keyset (the screen showed the newest 50; it now has Load more)
Team members	/org/members (directory)	no
Employees, Vacancies	/hr/employees, /hr/vacancies	no

Endpoints that already took q (customers, employees, suppliers, items) keep it and gain sort and filters; its meaning changed to the contract below (every word, over the shown columns).

Contract (identical on every list endpoint)

• q — string, at most 100 runes (longer is 400 invalid_query). Words split on whitespace; every word must match, case-insensitive, somewhere in the endpoint’s declared search fields (the fields the screen shows, including joined names such as supplier, customer, position). % and _ are literal.
• sort — one allowlisted key, - prefix for descending. Not in the allowlist: 400 invalid_sort naming the allowed keys. Absent: today’s order, unchanged. id always breaks ties; empty values sort last in both directions.
• Filters — named query parameters per endpoint, AND across parameters: pick-list column: repeated parameter, any of (state=posted&state=paid); date or amount column: <col>_from, <col>_to (inclusive); party or unit column: the id. Existing parameters (kind, supplier_id, state, due_before, currency, open) keep their meaning.
• Amounts compare and sort as NUMERIC; never a float, in any layer.
• cursor — opaque; carries the sort key, the sort value (text, or null) and the row id. A cursor issued under a different sort than the request’s is 400 invalid_cursor. Search and filters are applied before the limit; a client that changes them restarts from no cursor.

Server

Package internal/kernel/listquery (imports no platform or module package):

• Like(term) string — escape \, %, _ for ILIKE ... ESCAPE '\'. Replaced the copies in pettycash/spend.go, expenses/filter.go and platform/hr/employee.go.
• SearchClause(exprs []string, q string, firstArg int) (sql string, args []any, err error) — one AND (e1 ILIKE $n OR e2 ILIKE $n …) group per whitespace word, numbered from firstArg. A q over MaxQueryRunes (100) runes is ErrInvalidQuery. exprs are trusted SQL written in the module, never from a request.
• Spec[R]{Columns: []Column[R]{{Key, Expr, Type, Value}}, IDExpr, ID}. Spec.Parse(s) (Sort[R], error) accepts "" (the module’s default order), key or -key; anything else is ErrInvalidSort, whose message names the allowed keys. Sort.Set(), Sort.OrderBy() (expr dir NULLS LAST, id dir), Sort.After(cursor, firstArg) (the keyset predicate (e > v OR (e = v AND id > i) OR e IS NULL) / (e IS NULL AND id > i), mirrored for descending; the value is checked against and cast to the column’s Type: text, numeric, date, timestamptz; a cursor issued under another sort is ErrInvalidCursor) and Sort.NextCursor(last). Expr must be valid in WHERE and ORDER BY and must not equal an output alias of the query (total::text AS total would make ORDER BY total sort text).
• Cursor{Sort, Value, ID} with Encode() / DecodeCursor() (base64 of JSON). Used only when sort is present; with no sort each module keeps its own cursor and order, unchanged. Each module’s cursor and listquery’s cannot be mistaken for each other (tested per module): a listquery cursor sent with no sort, or under another sort, is 400 invalid_cursor; any other malformed cursor keeps the module’s existing 422.
• Problem(err) *apperr.Problem — the 400 for ErrInvalidSort / ErrInvalidCursor / ErrInvalidQuery (codes invalid_sort, invalid_cursor, invalid_query), nil for any other error. Each module’s error mapper starts with if p := listquery.Problem(err); p != nil { return p }.
• Hoisted after the first modules copied them (params.go): NonEmpty (drops empty values of a repeated parameter, so state= is ignored), ParseActive (the active filter: absent or both values = no filter, one value = that value, empty values ignored, anything else 400), CompareText (case-insensitive, then byte order), GoCol[R]{Key, Cmp, Null} and Refine(rows, q, sort, cols, fields, keep) (q and sort on rows already computed in Go; same q rule and 100-rune limit, unknown sort ErrInvalidSort, stable sort, Null rows last both ways).

Handlers parse the parameters by hand (c.Query, listquery.NonEmpty(c.QueryArray(..))), as every handler in the repo does: routes are plain gin handlers and the generated params types are documentation only. Malformed dates and amounts are 422 (the repo’s convention for query values); listquery errors are 400. Amount bounds must match ^-?\d+(\.\d{1,4})?$ and are compared as ::numeric (or money.Amount.Cmp in Go).

The openapi operation is the contract. It declares q (maxLength 100), sort, the filters, and the allowed sort keys in the extension x-sort-keys on the operation (sort is a plain string, not an enum). Go and TypeScript are regenerated, never hand-edited.

Three shapes, chosen per endpoint by where its columns are computed:

• Keyset-paged in SQL (purchase invoices, payments, batches, remittances, receipts, trade documents, e-invoice submissions): the positional query is kept, search and filters are appended after it, ORDER BY becomes Sort.OrderBy() only under a sort; the filter struct carries both the module’s After and SortAfter *listquery.Cursor, each refused under the other; the list function returns the next cursor already encoded. Each has a snapshot test of the no-sort order and cursor, written and run green before the query changed.
• Offset-paged in SQL (journal entries): limit/offset kept; under a sort the order is expr dir NULLS LAST, posted_seq dir, a total order, so offset paging stays stable. No cursor.
• Unpaged: in SQL (suppliers, customers, items, locations, asset classes, employees, float register), or in Go with Refine on the finished rows where the shown values are computed in Go (operations reports, asset register, vacancies, members directory, bank accounts; RefineVacancies, RefineOpenOrders/RefineGRNI/RefineUninvoiced, the module-local bankAccountCols and the members allowlist). Go-side text sorts are case-insensitive byte order, not the database collation (same for ASCII, may differ for accented names).

Permission-hiding rule. A list whose handler hides rows by permission must exclude them in SQL, always, not only under q/sort/filter: next_cursor is built from the last row read and could name a hidden row. Payment batches holding payslips are excluded for a caller without payroll.run.read (non-holders now get full pages). A column the caller may not see is neither searched nor sortable for them: vacancies search and sort the position as the screen labels it (CODE · title with org.structure.read, else … and the id’s last four characters); the members directory offers roles only to org.roles.manage and positions only to org.structure.read (for anyone else those keys are 400 invalid_sort, and role_id is 403); the employee identity number is never searched or sorted; the float register applies the caller’s scope in the same WHERE.

No index in v1: ILIKE '%x%' scans the organization’s rows, as the existing q endpoints did. A trigram index is the upgrade if a table grows large (same note as global search). Not measured.

Rulings (recorded during the build)

• Search covers what the screen shows, with looked-up names joined (INNER JOIN only when the foreign key is NOT NULL and enforced, else LEFT JOIN with NULLS LAST).
• Workflow state and status are searched and sorted by their key, and filtered by a pick list showing the translated label. A ms/zh user typing the translated label finds nothing in free-text search (use the filter); the fix is joining the translation row. Same for the members directory status (active also matches inactive).
• Amounts filter and sort on the document-currency value the column shows (USD 100 and MYR 100 compare equal); users filter by currency. Base amounts are searchable where shown as a second line, not sortable.
• Items and locations: the name is the one in the request’s locale, else English (Accept-Language); a name in another locale is not searched. Items q used to be a code prefix or a name in any locale.
• e-Invoice issued date is the calendar date in Asia/Kuala_Lumpur, the zone the screen formats in, for both search and the issued_from/issued_to range.
• active=false means inactive only (it used to mean “all” on suppliers, customers, items and locations); absent, or both values, means all.
• Computed-per-row columns (outstanding, unallocated) are search-only; sorting them is 400 invalid_sort.

Web

DataTable gains a server mode: the screen owns the state, the table renders it. useListView(tableId) holds {q, sort, filters} in the URL (replace, not push), debounces q 300 ms, and gives the screen the API params. Sortable headers (click: ascending, descending, off, aria-sort), a filter control per filterable header (text contains = the shared q is global; pick-list = multi-select; date and amount = from/to), a “Clear all” and a chip row of active filters. Below 768 px the card list keeps search only; sort and filter live in one “Filter” sheet. The searchText prop and useLoadAllWhileSearching were deleted after the last screen was converted. Filters and sorts do not depend on column visibility: a hidden column’s filter is reached from a “Filter” button, and a sort on a hidden column shows a removable chip. The router reads q, sort and f_* as exact text (web/src/app/search.ts), so 10.50 stays 10.50 through navigation.

Guards

• Per endpoint (Go, real Postgres): presence first (the row is returned unfiltered), then excluded by the search / each filter; sort order verified across a page boundary in both directions with nulls; an unknown sort key is 400; a cursor from another sort is 400; RLS: another org’s row never appears.
• Drift test (internal/app/listsort_test.go): the 30 converted operations, and only they, declare x-sort-keys; each declares q and sort; and through the assembled router, with a member holding every list’s read key, an unknown sort is 400 invalid_sort naming exactly the x-sort-keys (the message is built from the module’s own allowlist, Spec or GoCol). Watched failing once each way.
• Web: one DataTable server-mode test and one per converted screen (params reach the request).

Not in v1

Saved views, shareable named filters, full-text ranking, search across lists (that is /search), multi-column sort.

Since the integration with table export, a converted list that has an export (suppliers, customers, purchase invoices, the eight trade document lists, journal entries) exports what it shows: the export route reads the list’s own filter parser, so it takes the same q, sort and filters (journal entries export oldest first whatever the sort).