# Admin Table

A searchable, paginated table of orders with inline actions ("ship", "refund")
per row. Admin-only: every route call and every rpc call runs through
`isUserAdminOrThrow` from the shared admin helpers. Search and status filter run
server-side via an `@rpc` that returns a page of results plus the total count.

The recipe composes three patterns already shown elsewhere: admin role gating
(`app/shared/services/admin.ts` from `elements man recipes admin-roles`),
debounced server-driven search (from `elements man recipes search-filter`), and
offset-based pagination with page numbers. The inline actions are thin `@rpc`
calls that mutate one row and return the refreshed page so the table re-renders.

## Migration

```bash
elements create migration 'add orders' -tables=orders
```

`app/migrations/<timestamp>-add-orders.migration.sql`:

```sql
-- add orders

-- Auto-update updatedAt on row changes.
create or replace function touchUpdatedAt()
returns trigger
language plpgsql
as $$
begin
  new.updatedAt = now();
  return new;
end;
$$;

create type orderStatus as enum ('pending', 'shipped', 'refunded');

create table orders (
  id uuid primary key default uuidGenerateV7(),
  createdAt timestamptz not null default now(),
  updatedAt timestamptz not null default now(),
  customerName text not null,
  status orderStatus not null default 'pending',
  amountCents integer not null
);

create index ordersStatusIdx on orders (status);
create index ordersCustomerNameIdx on orders (lower(customerName));
create index ordersCreatedAtIdx on orders (createdAt desc);

create trigger ordersTouchUpdatedAt
  before update on orders
  for each row execute function touchUpdatedAt();
```

`orderStatus` is a Postgres enum that constrains the status column to the three
valid values. The three indexes back the three lookups the admin page does:
filter by status, search by customer name (lowercased), and sort by recency.

## Page setup

```bash
elements create page admin-orders
```

The `Order` interface, the filter shape, the search rpc, and the status-update
rpc are used only by this page, so they live in the page's own `services.ts`.
The admin gate comes from `app/shared/services/admin.ts` (see
`elements man recipes admin-roles` for that file). The route runs the initial
search and passes the first page to the template; the template debounces filter
changes and refreshes via the same rpc.

`app/pages/admin-orders/services.ts`:

```ts
import { sql } from "@elements/app";
import { isUserAdminOrThrow } from "#app/shared/services/admin";

export type OrderStatus = "pending" | "shipped" | "refunded";

export interface Order {
  id: string;
  createdAt: Date;
  customerName: string;
  status: OrderStatus;
  amountCents: number;
}

export interface OrderQuery {
  text: string;
  status: OrderStatus | "all";
  page: number;
}

export interface OrderPage {
  orders: Order[];
  total: number;
  page: number;
  pageCount: number;
}

const PAGE_SIZE = 25;

/** @rpc */
export function searchOrders(query: OrderQuery): OrderPage {
  isUserAdminOrThrow();

  let pattern = `%${query.text.trim()}%`;
  let page = Math.max(1, query.page);
  let offset = (page - 1) * PAGE_SIZE;

  let total: number;
  let rows: Order[];

  if (query.status === "all") {
    total = sql<{ count: number }>(
      `select count(*)::int as count from orders where customerName ilike ${pattern}`,
    ).firstOrThrow().count;

    rows = sql<Order>(
      `select id, createdAt, customerName, status, amountCents from orders
       where customerName ilike ${pattern}
       order by createdAt desc
       limit ${PAGE_SIZE} offset ${offset}`,
    ).all();
  } else {
    total = sql<{ count: number }>(
      `select count(*)::int as count from orders
       where customerName ilike ${pattern} and status = ${query.status}`,
    ).firstOrThrow().count;

    rows = sql<Order>(
      `select id, createdAt, customerName, status, amountCents from orders
       where customerName ilike ${pattern} and status = ${query.status}
       order by createdAt desc
       limit ${PAGE_SIZE} offset ${offset}`,
    ).all();
  }

  let pageCount = Math.max(1, Math.ceil(total / PAGE_SIZE));

  return { orders: rows, total, page, pageCount };
}

/** @rpc */
export function setOrderStatus(orderId: string, status: OrderStatus, query: OrderQuery): OrderPage {
  isUserAdminOrThrow();

  sql(`update orders set status = ${status} where id = ${orderId}`);

  return searchOrders(query);
}
```

`searchOrders` branches on whether the status filter is `"all"` or a specific
value, so the SQL stays explicit and the indexes match the query shape. Both
branches return the page rows plus the total count so the template can render
page numbers.

`setOrderStatus` updates one row and re-runs the search with the current query,
returning the refreshed page. The template binds the returned page to its state,
so the table re-renders with the new status immediately. The admin check at the
top blocks a non-admin who knows the rpc name from calling it directly over the
network; the route guard below blocks page rendering for non-admins.

`app/pages/admin-orders/index.ts`:

```ts
import adminOrders from "./template";
import { isUserAdminOrThrow } from "#app/shared/services/admin";
import { searchOrders, OrderQuery } from "./services";

export default function route(req, res) {
  isUserAdminOrThrow();

  let initialQuery: OrderQuery = { text: "", status: "all", page: 1 };
  let initial = searchOrders(initialQuery);

  return new adminOrders({ initial });
}
```

The route gates on admin status, runs an initial search with the default query,
and passes the first page into the template. The first paint shows orders; no
fetching spinner on mount.

`app/pages/admin-orders/template.html`:

```html
import "./style.css";
import {
  Order,
  OrderQuery,
  OrderStatus,
  OrderPage,
  searchOrders,
  setOrderStatus,
} from "./services";

let debounceTimer: ReturnType<typeof setTimeout> | null = null;

function refresh(query: OrderQuery, results: { value: OrderPage }) {
  results.value = searchOrders({
    text: query.text,
    status: query.status,
    page: query.page,
  });
}

function onChange(query: OrderQuery, results: { value: OrderPage }) {
  if (debounceTimer !== null) {
    clearTimeout(debounceTimer);
  }

  debounceTimer = setTimeout(() => {
    query.page = 1;
    refresh(query, results);
  }, 300);
}

function onGoToPage(target: number, query: OrderQuery, results: { value: OrderPage }) {
  if (target < 1) {
    return;
  }
  if (target > results.value.pageCount) {
    return;
  }

  query.page = target;
  refresh(query, results);
}

function onAction(orderId: string, status: OrderStatus, query: OrderQuery, results: { value: OrderPage }) {
  results.value = setOrderStatus(orderId, status, {
    text: query.text,
    status: query.status,
    page: query.page,
  });
}

<html class="admin-orders"
      (initial: OrderPage,
       private query: OrderQuery = { text: "", status: "all", page: 1 },
       private results: { value: OrderPage } = { value: initial })>
  <h1>orders</h1>

  <form class="filters">
    <input type="text"
           value={query.text}
           placeholder="search by customer"
           oninput={() => onChange(query, results)}>

    <select value={query.status} onchange={() => onChange(query, results)}>
      <option value="all">all statuses</option>
      <option value="pending">pending</option>
      <option value="shipped">shipped</option>
      <option value="refunded">refunded</option>
    </select>
  </form>

  <table class="orders">
    <thead>
      <tr>
        <th>customer</th>
        <th>status</th>
        <th>amount</th>
        <th>placed</th>
        <th>actions</th>
      </tr>
    </thead>
    <tbody>
      <tr e:for={o of results.value.orders}>
        <td>{o.customerName}</td>
        <td class={`status-${o.status}`}>{o.status}</td>
        <td>${(o.amountCents / 100).toFixed(2)}</td>
        <td>{o.createdAt.toLocaleDateString()}</td>
        <td class="actions">
          <button e:if={o.status === "pending"}
                  onclick={() => onAction(o.id, "shipped", query, results)}>
            ship
          </button>
          <button e:if={o.status === "shipped" || o.status === "pending"}
                  onclick={() => onAction(o.id, "refunded", query, results)}>
            refund
          </button>
        </td>
      </tr>
      <tr e:if={results.value.orders.length === 0}>
        <td colspan="5" class="empty">no orders match.</td>
      </tr>
    </tbody>
  </table>

  <nav class="pagination">
    <button onclick={() => onGoToPage(results.value.page - 1, query, results)}
            disabled={results.value.page <= 1}>
      previous
    </button>
    <span>page {results.value.page} of {results.value.pageCount} ({results.value.total} total)</span>
    <button onclick={() => onGoToPage(results.value.page + 1, query, results)}
            disabled={results.value.page >= results.value.pageCount}>
      next
    </button>
  </nav>
</html>
```

The text input and the status `<select>` both call `onChange`, which debounces a
single refresh across them. Every search resets `query.page = 1` because the
previous page number may not exist after filters narrow the result set.

`onGoToPage` clamps to `[1, pageCount]` and re-runs the search. Page numbers
stay in `query.page` so a filter change reads the current page state cleanly.

`onAction` calls the per-row mutation rpc with the current query attached, so
the rpc can return the refreshed page in one round-trip. The template binds the
returned page directly to `results.value`.

## Routes

Register the page in `index.ts`:

```ts
import adminOrders from "#app/pages/admin-orders";

// ...
app.route("/admin/orders", adminOrders);
```

## Notes

- **Offset vs cursor pagination.** Page numbers (offset/limit) match admin UX
  expectations: jump to page N, see total page count, navigate freely. The
  trade-off is that inserts and deletes between pages can shift rows. For an
  admin table where the dataset isn't churning fast that's fine; the
  `order by createdAt desc` keeps the order stable enough. For high-churn
  streams, use cursor pagination as shown in
  `elements man recipes infinite-scroll-feed`.
- **Single rpc returns the refreshed page.** `setOrderStatus` re-runs the search
  and returns the new page. The alternative is two rpc per action (mutate +
  re-search); the single rpc keeps the round-trip count down and avoids a second
  loading state in the UI.
- **Status type: union instead of `const enum`.** The TS side is a union
  (`"pending" | "shipped" | "refunded"`) rather than a `const enum`. The
  Postgres enum is the single source of truth for the value list, so a TS-side
  enum would just duplicate it. The values cross the rpc and json boundary as
  plain strings, so a union matches what's actually transmitted
  (`status: "shipped"` in JSON, `status === "shipped"` in code) without an
  `OrderStatus.Shipped` indirection. Type narrowing on a union is the same as on
  a `const enum` at the call sites; exhaustive `switch` checks both ways. No
  runtime object ships to the browser. Adding `"cancelled"` is the same two-line
  change either way: `alter type orderStatus add value 'cancelled'` plus
  extending the value list. If you'd rather centralize the values for
  autocomplete via dot-access (`OrderStatus.Shipped`), swap to
  `export const enum OrderStatus { Pending = "pending", ... }`; both forms
  compile to the same json on the wire.
- **Sorting.** Add a `sort: "newest" | "oldest" | "highest" | "lowest"` field to
  `OrderQuery` and switch the `order by` clause inside the rpc. The header cells
  become clickable; clicking calls `onChange` with the new sort.
- **Bulk actions.** Add a `selected: string[]` to template state and a
  "selected" column with checkboxes (`group={selected} value={o.id}`). A toolbar
  button calls a bulk rpc that takes the id list and the new status.