The data table's entire backend contract in one struct.

Flop-shaped on purpose, Flop-free on purpose: anything that can produce this struct can drive <.data_table>, and anything that can consume it can execute the query - the in-memory Engine.List for plain lists, or (Petal Pro) a Flop adapter for Ecto.

%State{
  order_by: [{:email, :asc}],
  filters: [%{field: :email, op: :contains, value: "d"}],
  page: 1,
  page_size: 10,
  total: 74            # nil = cursor/unknown mode
}

from_params/2 and to_params/1 round-trip the struct through URL params, so URL-as-state (shareable sorts/filters, working back button) is a one-liner in handle_params rather than a hand-rolled encoding.

Filter operators

The vocabulary describes what the USER expressed, not how a database spells it. Each engine translates intent into its own query language, which is why :contains here means "text contains" even though some query libraries use that name for array membership.

• text - :contains, :not_contains, :eq, :neq, :starts_with
• ordered (numbers, dates) - :gt, :gte, :lt, :lte, :between
• sets - :in, :not_in
• null-ness - :is_empty, :is_not_empty
• dates, read as intent - :before, :on, :after

Semantics pinned deliberately, because "obvious" differs per engine:

• :between is INCLUSIVE of both bounds.
• :is_empty matches nil, "" and [] - a blank-ish " " is not empty. Trim before filtering if you want it to be.
• text comparisons fold CASE, and only case. :eq, :neq, :in, :not_in, :contains, :not_contains, :starts_with, the comparators against text columns, and the quick search all compare downcased on both sides. Accents are NOT folded: "cafe" does not match "café", while "CAFÉ" does. Folding is whatever String.downcase/1 does; a SQL lower() is collation-dependent, so non-ASCII input can diverge between engines (Turkish dotless i is the classic case). ASCII agrees everywhere.
• :eq is deliberately NOT SQL =. A person picking "is" in a filter means "this value", not "these bytes" - and the same dropdown offers :contains and :starts_with, which every engine folds, so a byte-exact :eq would make case behaviour silently flip as the user moves down the operator list. If a byte-exact equality is ever needed it will arrive as a NEW operator (:eq_sensitive), never as a change to this one.
• comparators work on whatever the column holds - numbers, dates and text all compare with the same op.
• :before/:on/:after are date-shaped aliases of :lt/:eq/ :gt; they exist so a date filter reads as a date filter, and an adapter may map them to the same primitives. They are defined only for date-typed cells (Date, DateTime, NaiveDateTime) - a text column holding an ISO string does not match, because no SQL engine would cast every row to find out.
• a nil value on a value-carrying op is unsatisfiable - it matches NO rows. from_params/2 never emits one (a blank input drops the filter instead), so this only concerns hand-built states. Asking for empty cells is what :is_empty is for.
• map-shaped cells are filter-only. Erlang orders maps by size, then keys, then values; jsonb has its own type-ordering rules, and no SQL expression reconciles the two - so a map column has NO defined sort order across engines. Don't mark one sortable.
• an empty order_by, and ties within one, have NO defined row order across engines: this engine preserves input order, SQL without ORDER BY promises nothing. For stable paging, append a unique tiebreaker (the primary key) to every sort. That is the APPLICATION's job, at the point the state is built - an adapter must reproduce order_by exactly as given and never append one silently, or the same state would order differently per engine.

Engines may support a subset. ops/0 returns the recognised set and valueless_op?/1 identifies the ops that carry no value.

Writing an adapter

Text equality in this contract folds case, so lower(col) = lower($1) is the SQL you want and plain = is wrong - for :eq, :neq, :in and :not_in alike, since they share one equality primitive. A half-folded implementation is worse than either extreme: "is not alice" leaving a visible "Alice" on screen is the loudest possible wrong answer.

Plan for the index rather than discovering it: lower(col) will not use a plain btree index - add CREATE INDEX ... ON t (lower(col)), with the emitted expression matching the index expression exactly. Measured at one million rows (PostgreSQL 14), the functional index makes folded equality as fast as exact (~0.02ms either way); without it you get a sequential scan.

One exception: on a citext column, plain = is ALREADY case-insensitive and index-backed - wrapping it in lower() defeats the index (measured ~28x slower). An adapter that knows a column is citext should emit plain = there.

Reproduce order_by exactly - do not append a tiebreaker on the application's behalf (see the tie rule above), and do not reorder or drop entries. If the state's sort is unstable, that is the caller's bug to fix where the state is built.

Text-shaped ops against float columns compare the TEXT FORM of the float, and Elixir and SQL format extremes differently: to_string(1.0e20) is "1.0e20" while Postgres renders 1e+20 - so :contains/:in against exponent-range floats can diverge between engines. Integral floats (10.0) agree. Money belongs in a decimal column, which has no such gap.

Security

from_params/2 never creates atoms from user input: :fields is a required whitelist and anything outside it is dropped, ops outside the known set are dropped, and page/page_size are clamped (:max_page_size, default 100).