Backend-owned per-table default sort order + server-side pagination cursor #274

Background

#270 removed the SDK's hardcoded ORDER BY received_timestamp DESC default. The SDK now sends ORDER BY only when the caller uses .orderBy(), so wh.from(table).fetch() is valid on any schema. The trade-off that shipped: cursor pagination's next() is only offered when the query has an explicit .orderBy() (no implicit default, and no client-side default option).

We deliberately did not add a client-side defaultOrderBy option — it's the wrong layer. It would be client-global (one order for every table → wrong granularity, and it re-introduces the invalid-SQL footgun for any queried table lacking that column), and it duplicates per-table sort-key knowledge in every client and language SDK.

Proposal

Move the "default order" concept to the backend, where it's a single source of truth co-located with the table config and benefits every language SDK with zero client config:

1. Per-table default_order_by in backend config. The policy is already keyed by table (policy.tables[name]), so it's a natural home (or a dedicated table-config map). When a /v1/query request arrives with no order_by, the backend applies the table's default_order_by if configured, else emits no ORDER BY (current behavior). The backend builder already only emits ORDER BY when the SDK sends one (internal/query/builder.go), so this is a contained change.
2. Optional auto-derivation. Schema discovery currently reads system.columns but not system.tables.sorting_key (internal/discovery/discovery.go). Capturing the table's actual ClickHouse ORDER BY key would give a sensible zero-config default — a table's sort key is usually exactly what you want to paginate by.

The pagination coupling (the real design work)

The SDK paginates with a client-side keyset cursor: _fetchNext reads the last row's value for the order column and adds WHERE col </> lastValue, so the client must know the order column. Today the /v1/query response is a bare JSON array (no envelope/metadata), so if the backend silently picks the order the client can't build a cursor. Options, roughly in order of cleanliness:

• (a) Server-side opaque cursor token — the backend returns an opaque next_cursor (encoding the order + last key values, ideally a composite key with a unique tiebreaker) and the SDK's next() just replays it. Most robust: it also properly fixes feat: embedded ClickHouse strict validation #175 (the default-cursor tie bug — a single non-unique column skips rows tied on a page boundary), since the server can include a unique tiebreaker. Requires a response envelope ({ data, next_cursor }) — a wire-format change.
• (b) Effective-order metadata — the backend returns the order it applied (response field or header) and the SDK keeps its client-side keyset using that column. Smaller change, but doesn't fix feat: embedded ClickHouse strict validation #175's tie problem on its own.
• (c) Schema-sourced order — expose each table's default/sort order via the schema endpoint; the SDK reads it for both ordering and the cursor. Keeps pagination fully client-side, but adds a schema lookup and still has the tie problem.

(a) is the recommended end-state — it makes the SDK dumb-and-correct about both ordering and cursoring, and subsumes #175.

Scope notes

• Stream dedup in clients/ts/src/stream/live-query.ts / stream/sse.ts uses received_timestamp against the live ingest stream (which always carries it) — unrelated, out of scope.
• Subsumes feat: embedded ClickHouse strict validation #175 (default-cursor tie) if we go with option (a).

Follow-up to #270.