elements
Manual Recipes Admin Table

Admin Table

elements man recipes/admin-table Read as markdown

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

elements create migration 'add orders' -tables=orders

app/migrations/<timestamp>-add-orders.migration.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

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:

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:

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:

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:

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.