Public and Admin Views

Two routes over the same events data. The public route at /events lists upcoming events without auth. The admin route at /admin/events shows the same data plus a create form and per-row delete actions. Both pages open a view on one shared LiveTable. Mutations are gated inside the LiveTable's insert, update, and delete handlers: only admins succeed; the public page never tries to mutate, but if it did the LiveTable would reject the call server-side.

The recipe demonstrates the auth model where everyone reads through the same live primitive and a small set of users mutates it. The admin gate lives on the LiveTable (defense in depth) and on the admin route (page-render gate). The public route is unauthenticated and unauthorized.

Migration

elements create migration 'add events' -tables=events

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

-- add events

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

create table events (
  id uuid primary key default uuidGenerateV7(),
  createdAt timestamptz not null default now(),
  updatedAt timestamptz not null default now(),
  title text not null,
  description text not null default '',
  eventDate timestamptz not null
);

create index eventsEventDateIdx on events (eventDate asc);

create trigger eventsTouchUpdatedAt
  before update on events
  for each row execute function touchUpdatedAt();

The eventsEventDateIdx backs the where eventDate >= now() filter and the order by eventDate asc clause. description defaults to the empty string so the column stays not-null with no default text noise.

Services

Two pages consume the events LiveTable: the public /events route and the admin /admin/events route. Because more than one page imports it, the file lives in app/shared/services/ rather than colocated with either page. If only the admin page needed it, the file would be app/pages/admin-events/services.ts instead.

app/shared/services/events.ts:

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

export interface Event {
  id: string;
  createdAt: Date;
  updatedAt: Date;
  title: string;
  description: string;
  eventDate: Date;
}

export let events: LiveTable<Event> = new LiveTable<Event>({
  select: () => sql<Event>(
    `select id, createdAt, updatedAt, title, description, eventDate from events
     where eventDate >= now() - interval '1 hour'
     order by eventDate asc`,
  ),
  insert: (item) => {
    isUserAdminOrThrow();
    return events.insert(item);
  },
  update: (item) => {
    isUserAdminOrThrow();
    return events.update(item);
  },
  delete: (item) => {
    isUserAdminOrThrow();
    return events.delete(item);
  },
});

The custom select limits the initial rows to events from the last hour and into the future. The one-hour overlap gives "happening now" events a window before they fall off the list. It ignores its (partition, window) arguments because neither route partitions or windows the view. Both the public and the admin route open a view on this same declaration; the difference is what each template does with it.

Every handler calls isUserAdminOrThrow() first, the guard from elements man recipes admin-roles. It throws when there is no session and when the user is not an admin, so an anonymous browser or a non-admin calling events.insert(...) from a devtools console fails before any SQL runs. The handler then returns the raw auto-SQL on the declaration, events.insert(item) and the rest. The declaration carries an explicit LiveTable<Event> annotation because its initializer refers to it.

Public route

elements create page events

app/pages/events/index.ts:

import events from "./template";
import { events as eventsTable } from "#app/shared/services/events";

export default function route(req, res) {
  return new events({ events: eventsTable.view() });
}

The public route is unauthenticated. Anyone can hit /events and see the upcoming events.

app/pages/events/template.html:

import "./style.css";
import { LiveView } from "@elements/app";
import { Event } from "#app/shared/services/events";

<html class="events" (events: LiveView<Event>)>
  <main>
    <h1>upcoming events</h1>

    <ul class="events">
      <li e:for={e of events.sort((a, b) => +a.eventDate - +b.eventDate)}>
        <h2>{e.title}</h2>
        <time>{e.eventDate.toLocaleString()}</time>
        <p e:if={e.description}>{e.description}</p>
      </li>
      <li e:if={events.length === 0}>no upcoming events.</li>
    </ul>
  </main>
</html>

The public template reads the view. There are no mutation buttons. The broadcast still patches the list when an admin adds, edits, or removes a row, so the public page updates live.

Admin route

elements create page admin-events

app/pages/admin-events/index.ts:

import { isUserAdminOrThrow } from "#app/shared/services/admin";
import adminEvents from "./template";
import { events as eventsTable } from "#app/shared/services/events";

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

  return new adminEvents({ events: eventsTable.view() });
}

The admin route checks admin status before opening the view for the template. The page never renders for a non-admin; the LiveTable's mutator checks are the secondary gate.

app/pages/admin-events/template.html:

import "./style.css";
import { LiveView } from "@elements/app";
import { Event } from "#app/shared/services/events";

interface EventInput {
  title: string;
  description: string;
  eventDate: string;
}

function emptyForm(): EventInput {
  return { title: "", description: "", eventDate: "" };
}

function insert(events: LiveView<Event>, form: { value: EventInput }) {
  if (form.value.title.trim().length === 0) {
    return;
  }
  if (form.value.eventDate.length === 0) {
    return;
  }

  events.insert(
    {
      title: form.value.title.trim(),
      description: form.value.description.trim(),
      eventDate: new Date(form.value.eventDate),
    },
    () => form.value = emptyForm(),
  );
}

function onDelete(events: LiveView<Event>, event: Event) {
  if (!confirm(`delete "${event.title}"?`)) {
    return;
  }
  events.delete(event);
}

<html class="admin-events"
      (events: LiveView<Event>,
       private form: { value: EventInput } = { value: emptyForm() })>
  <main>
    <h1>events (admin)</h1>

    <form class="new" onsubmit={() => insert(events, form)}>
      <input type="text" value={form.value.title} placeholder="event title" required>
      <input type="datetime-local" value={form.value.eventDate} required>
      <textarea value={form.value.description} placeholder="description (optional)" rows="3"/>
      <button type="submit">add event</button>
    </form>

    <ul class="events">
      <li e:for={e of events.sort((a, b) => +a.eventDate - +b.eventDate)}>
        <h2>{e.title}</h2>
        <time>{e.eventDate.toLocaleString()}</time>
        <p e:if={e.description}>{e.description}</p>
        <button class="danger" onclick={() => onDelete(events, e)}>delete</button>
      </li>
      <li e:if={events.length === 0}>no upcoming events.</li>
    </ul>
  </main>
</html>

The admin template adds a creation form at the top and a delete button per row. insert calls events.insert(...) with the form's three fields converted to the row shape (the <input type="datetime-local"> value is a string; new Date(...) turns it into the Date the column needs). onDelete uses the browser's confirm() dialog before calling events.delete(event).

Both mutator calls run the LiveTable's handler before any SQL runs. A non-admin who somehow reaches the admin template (a stale session, a bug in the route guard) would still fail at the LiveTable layer, where isUserAdminOrThrow() throws. The two layers carry the same check.

Routes

Register both routes in index.ts:

import events from "#app/pages/events";
import adminEvents from "#app/pages/admin-events";

// ...
app.route("/events", events);
app.route("/admin/events", adminEvents);

Notes

• One LiveTable, two templates. The shared LiveTable is the single source of truth. Both the public and the admin view subscribe to the same broadcast channel, so an admin insert or delete instantly appears on every public browser without any extra plumbing. The data flow is identical regardless of which page the viewer is on.
• Auth at two layers. The admin route's isUserAdminOrThrow blocks page rendering. The same call inside each handler blocks the underlying operation. The route gate is the UX; the handler gate is the security boundary.
• Editing existing events. Add an update button per row that calls events.update({ ...event, title, description, eventDate }) and either a modal form or a separate /admin/events/:id route. The update handler is already gated on admin.
• Past events. The custom select is the initial query, not a filter on what arrives: an admin who inserts an event dated last week broadcasts it and it appears in every open list. Filter in the template (events.filter((e) => +e.eventDate >= Date.now() - 3600000)) if that matters. To let admins manage archived events, add an @rpc listPastEvents() that returns rows from sql, and render it in a separate "archived" section on the admin page. Past events don't need live sync.
• Multiple admin actions. A "feature" toggle, a "highlight" flag, or any other per-event mutation is just another column on the table plus an update call. The update handler covers every field uniformly.
• Public read without LiveTable. If live updates on the public page aren't valuable (the events list barely changes), swap the view for an @rpc that returns rows from sql, or a plain sql call in the public route handler. The admin page keeps the LiveTable for the live add/delete UX. The two pages no longer share infra, but the admin write path is the same.