Orders Dashboard

Use case: A sales team app where reps manage their own orders, managers oversee their team, and admins have full access.

Backend

engine.ts

import { createEngine } from '@superapp/backend'
import { betterAuthProvider } from '@superapp/backend/auth/better-auth'

const auth = betterAuthProvider({
  secret: process.env.AUTH_SECRET!,
  userTable: {
    table: 'main.users',
    matchOn: { column: 'id', jwtField: 'id' },
  },
})

const engine = createEngine({
  connections: {
    main: process.env.PG_URL!,
  },
  auth,

  permissions: {
    // ─── Sales Rep ──────────────────────────────────────────
    //
    // Can CRUD their own orders. Cannot see other reps' data.
    // New orders must start as 'draft' with a positive amount.
    // Can only delete drafts.

    rep_orders: {
      table: 'main.orders',
      roles: ['sales_rep'],
      select: {
        columns: ['id', 'amount', 'status', 'created_by', 'created_at'],
        where: { created_by: { $eq: '$user.id' } },
      },
      insert: {
        columns: ['amount', 'status'],
        validate: {
          amount: { $gt: 0 },
          status: { $eq: 'draft' },
        },
        overwrite: {
          created_by: '$user.id',
          created_at: '$now',
        },
      },
      update: {
        columns: ['amount', 'status'],
        where: { created_by: { $eq: '$user.id' } },
        validate: {
          amount: { $gt: 0 },
          status: { $in: ['draft', 'active', 'cancelled'] },
        },
        overwrite: { updated_at: '$now' },
      },
      delete: {
        where: {
          created_by: { $eq: '$user.id' },
          status: { $eq: 'draft' },
        },
      },
    },

    // ─── Manager ────────────────────────────────────────────
    //
    // Can view all orders and update status on any order.
    // Cannot create or delete orders.

    manager_orders: {
      table: 'main.orders',
      roles: ['manager'],
      select: {
        columns: ['id', 'amount', 'status', 'created_by', 'created_at', 'updated_at'],
      },
      update: {
        columns: ['status'],
        validate: {
          status: { $in: ['active', 'completed', 'cancelled'] },
        },
        overwrite: { updated_at: '$now' },
      },
    },

    // ─── Admin ──────────────────────────────────────────────
    //
    // Full access to all orders. Can delete cancelled orders.

    admin_orders: {
      table: 'main.orders',
      roles: ['admin'],
      select: {
        columns: ['id', 'amount', 'status', 'created_by', 'created_at', 'updated_at', 'notes'],
      },
      insert: {
        columns: ['amount', 'status', 'created_by', 'notes'],
        validate: { amount: { $gt: 0 } },
        overwrite: { created_at: '$now' },
      },
      update: {
        columns: ['amount', 'status', 'notes'],
        validate: { amount: { $gt: 0 } },
        overwrite: { updated_at: '$now' },
      },
      delete: {
        where: { status: { $in: ['cancelled', 'draft'] } },
      },
    },
  },
})

What each role can do:

	View	Create	Update	Delete
sales_rep	Own orders only	Yes — draft only, amount > 0	Own orders — amount > 0, restricted statuses	Own drafts only
manager	All orders + `updated_at`	No	Status only — active/completed/cancelled	No
admin	All orders + `notes`	Yes — any status, can set `created_by`	All fields	Cancelled and draft orders

How it works:

roles — declared at the permission level. The entire permission block (all its operations) applies to those roles.
where — injects a WHERE clause. A rep querying orders automatically gets WHERE created_by = ? — they never see other reps' orders. Managers and admins have no where, so they see everything.
validate — validates incoming data. Sending amount: -5 or status: 'shipped' is rejected with 403 before any SQL runs.
overwrite — always applied server-side. The client cannot override these — created_by and timestamps are enforced automatically.

Client

app/orders/page.tsx

'use client'

import { useEffect, useState } from 'react'
import { useSession } from '@superapp/auth'
import { useDb } from '@/hooks/use-db'
import { desc } from 'drizzle-orm'
import * as schema from '@/generated/schema'

type Order = Awaited<
  ReturnType<ReturnType<typeof useDb>['query']['orders']['findMany']>
>[number]

export default function OrdersDashboard() {
  const { data: session, isPending } = useSession()
  const db = useDb()
  const [orders, setOrders] = useState<Order[]>([])
  const [loading, setLoading] = useState(true)

  // Fetch orders — backend scopes to current user automatically
  const loadOrders = () => {
    if (!db) return
    setLoading(true)
    db.query.orders
      .findMany({
        orderBy: desc(schema.orders.createdAt),
        limit: 50,
      })
      .then(setOrders)
      .finally(() => setLoading(false))
  }

  useEffect(loadOrders, [db])

  // Create — backend enforces created_by and validates amount > 0
  const createOrder = async (amount: number) => {
    await db!.insert(schema.orders).values({ amount, status: 'draft' })
    loadOrders()
  }

  // Update — backend only allows own orders for reps, validates status
  const updateStatus = async (id: number, status: string) => {
    await db!.update(schema.orders).set({ status }).where({ id })
    loadOrders()
  }

  // Delete — backend only allows own draft orders for reps
  const deleteOrder = async (id: number) => {
    await db!.delete(schema.orders).where({ id })
    loadOrders()
  }

  if (isPending) return <p>Loading session...</p>
  if (!session) return <p>Please sign in to view orders.</p>
  if (loading) return <p>Loading orders...</p>

  return (
    <table>
      <thead>
        <tr>
          <th>ID</th>
          <th>Amount</th>
          <th>Status</th>
          <th>Date</th>
          <th>Actions</th>
        </tr>
      </thead>
      <tbody>
        {orders.map((order) => (
          <tr key={order.id}>
            <td>{order.id}</td>
            <td>${order.amount.toFixed(2)}</td>
            <td>{order.status}</td>
            <td>{new Date(order.created_at).toLocaleDateString()}</td>
            <td>
              <button onClick={() => updateStatus(order.id, 'cancelled')}>
                Cancel
              </button>
              <button onClick={() => deleteOrder(order.id)}>Delete</button>
            </td>
          </tr>
        ))}
      </tbody>
    </table>
  )
}

Backend

Client

On this page