07-appkit-chat-history

# Add Persistent Chat History to an AppKit Application Persist chat conversations in Lakebase so users can revisit past conversations, using AppKit-native primitives only: `AppKit.lakebase.query()` for persistence, `AppKit.serving("agent").asUser(req).stream()` for the agent stream, and `server.extend()` with `autoStart: false` for the proxy route. **No Vercel AI SDK, no `@databricks/ai-sdk-provider`, no `streamText`.** This skill builds on top of the agent wiring already established in [06-appkit-serving-wiring](../06-appkit-serving-wiring/SKILL.md). ## When to Use - Adding persistent conversation storage to an AppKit chat interface built with `useServingStream` - Building a sidebar with conversation history - Supporting ephemeral mode (graceful degradation when Lakebase is unavailable) - Capturing `trace_id` server-side for later feedback logging (see [08-appkit-feedback](../08-appkit-feedback/SKILL.md)) **Not for agent streaming fundamentals.** Use [06-appkit-serving-wiring](../06-appkit-serving-wiring/SKILL.md) to wire `useServingStream` and the base `/api/serving/:alias/stream` route first. **Not for plugin registration.** Use [04-appkit-plugin-add](../04-appkit-plugin-add/SKILL.md) + [05-appkit-lakebase-wiring](../05-appkit-lakebase-wiring/SKILL.md) to register the Lakebase plugin and declare bundle resources first. --- ## Prerequisites Verify all of these before proceeding: 1. Lakebase plugin registered in `server/server.ts` with bundle resources deployed (see [05-appkit-lakebase-wiring](../05-appkit-lakebase-wiring/SKILL.md)) 2. Serving plugin registered in `server/server.ts` with a working agent endpoint (see [06-appkit-serving-wiring](../06-appkit-serving-wiring/SKILL.md)) 3. `useServingStream` renders a basic chat UI that works end-to-end 4. App deployed at least once so the Service Principal exists and can create schemas 5. `AppKit.server` is constructed with `server({ autoStart: false })` and `AppKit.server.start()` is called after `AppKit.server.extend(...)` **Upstream docs (always check for latest):** ```bash npx @databricks/appkit docs "lakebase" npx @databricks/appkit docs "serving" ``` ### Working in Genie Code (client routing) Everything in this skill is **source editing + server-side code** (DDL, proxy route, sidebar, hooks) — written the same way on both clients. The DDL runs server-side on startup (Step 1) and is already client-agnostic. Only local gates and the deployed-app checks differ: | IDE/CLI (as written) | Genie Code substitution | |----------------------|--------------------------| | `npm run build` / `npm test` gates (Steps 1–8) | **IDE-only** convenience — no local Node toolchain. Skip; the platform builds **server-side** on deploy, and the retry tests run in CI or post-clone where npm exists. Errors surface in `databricks apps logs <name>` | | `npm run dev` | not available — verify on the deployed app | | `npx @databricks/appkit docs …` | npx absent (P9) — WebFetch https://databricks.github.io/appkit/docs/plugins/ | | `databricks bundle validate --profile $PROFILE` (Step 9) | run via `runDatabricksCli` (omit `--profile`; `--target dev` if a targetless validate is guardrail-blocked) — this is the canonical gate on both clients | | `databricks apps validate` (Step 9) | hard-blocked **and** unsafe for Lakebase apps (boots locally) — skip on both clients, rely on `bundle validate` + server-side build logs | | local `curl http://localhost:8000/api/…` gates (Steps 3, 7) | no local dev server — exercise the routes on the **deployed** app via browser, or the OAuth-session `requests.Session()` test in `03-appkit-deploy` | | `databricks apps deploy …` | see the `03-appkit-deploy` deploy-routing contract (`runDatabricksCli`, else SDK `w.apps.deploy(... SNAPSHOT)`) | Paths are relative to `apps_lakebase/$APP_NAME` — inside your git-cloned workshop project (`artifact_root`) on Genie Code, never the read-only `.assistant/skills` copy and never `/tmp`. See `skills/genie-code-environment` for the full manifest. --- ## Architecture ``` Browser (useServingStream or direct fetch) │ ▼ AppKit Server — server.extend() proxy route /api/chat │ ├─► AppKit.lakebase.query() (persist chat record, user message) │ ├─► AppKit.serving("agent").asUser(req).stream({ messages }) │ │ │ └─ for await (chunk of stream): │ 1. Forward chunk to client as SSE │ 2. Accumulate assistant text via dual-format extractor │ 3. Scan chunk for trace_id │ └─► AppKit.lakebase.query() (INSERT assistant message + traceId on stream close) ``` This pattern extends the `server.extend()` streaming proxy already documented in [06-appkit-serving-wiring/SKILL.md](../06-appkit-serving-wiring/SKILL.md) Step 6b. The only additions are the Lakebase inserts and the text/traceId accumulators. --- ## Step 1: Create the Chat Schema Add idempotent DDL after `createApp()` in `server/server.ts`. The `chat` schema is deliberately isolated from application data schemas so it doesn't collide with other tables. > **RULE_10 — this DDL is intentionally in-app, not a bundle resource or a `psql` script.** `initChatSchema()` runs **server-side on every app startup** via `AppKit.lakebase.query()`, so the app's Service Principal owns the schema, tables, and indexes (the same Deploy-First Pattern as `05-appkit-lakebase-wiring` Step 1d). This is **client-agnostic** — it executes identically whether the app was deployed from an IDE or Genie Code, and there is **no client-side `psql`/DDL step**. Do not move it into `databricks.yml` or a setup script: idempotent `CREATE … IF NOT EXISTS` on startup is the correct, SP-owning pattern. ```typescript async function initChatSchema() { try { await AppKit.lakebase.query(`CREATE SCHEMA IF NOT EXISTS chat`); await AppKit.lakebase.query(` CREATE TABLE IF NOT EXISTS chat."User" ( id TEXT PRIMARY KEY NOT NULL, email VARCHAR(64) NOT NULL ) `); await AppKit.lakebase.query(` CREATE TABLE IF NOT EXISTS chat."Chat" ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, title TEXT NOT NULL DEFAULT 'New chat', "userId" TEXT NOT NULL, visibility VARCHAR(10) NOT NULL DEFAULT 'private', "lastContext" JSONB ) `); await AppKit.lakebase.query(` CREATE TABLE IF NOT EXISTS chat."Message" ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), "chatId" UUID NOT NULL REFERENCES chat."Chat"(id) ON DELETE CASCADE, role VARCHAR(20) NOT NULL, parts JSON NOT NULL DEFAULT '[]', attachments JSON NOT NULL DEFAULT '[]', "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, "traceId" TEXT ) `); await AppKit.lakebase.query(` CREATE TABLE IF NOT EXISTS chat."Vote" ( "chatId" UUID NOT NULL REFERENCES chat."Chat"(id) ON DELETE CASCADE, "messageId" UUID NOT NULL REFERENCES chat."Message"(id) ON DELETE CASCADE, "isUpvoted" BOOLEAN NOT NULL, PRIMARY KEY ("chatId", "messageId") ) `); await AppKit.lakebase.query(` CREATE INDEX IF NOT EXISTS idx_chat_user ON chat."Chat" ("userId", "createdAt" DESC) `); await AppKit.lakebase.query(` CREATE INDEX IF NOT EXISTS idx_message_chat ON chat."Message" ("chatId", "createdAt" ASC) `); console.log("[Chat] Schema initialized"); } catch (err) { console.warn("[Chat] Schema init failed (may not have DB access):", err); } } await initChatSchema(); ``` See [references/chat-schema.md](references/chat-schema.md) for the full ER diagram, per-column rationale, and optional Drizzle ORM migration path. **Gate:** Schema init runs on startup. A subsequent `npm run build` passes, and deploying the app produces server logs containing `[Chat] Schema initialized`. --- ## Step 2: Add Auth Middleware Add Express middleware to parse the OBO user identity once per request and attach it to `req.session`. This avoids calling a helper function inline in every route handler. > **Header contract.** Databricks Apps sets four canonical user headers on every request: > > - `x-forwarded-email` — end user's email (the value AppKit and downstream agents use as the canonical user attribute) > - `x-forwarded-preferred-username` — preferred username (often equals the email) > - `x-forwarded-user` — stable workspace user id > - `x-forwarded-access-token` — downscoped OBO token (only present when `user_api_scopes` is declared) > > `x-forwarded-user-info` is **not** a canonical Databricks Apps header and must not be used. Earlier internal patterns assumed a JSON-blob header existed; it does not. Read the canonical headers above directly. ```typescript interface AppSession { userId: string; email: string; name?: string; authenticated: boolean; } declare global { namespace Express { interface Request { session?: AppSession; } } } function readCanonicalUserHeaders(req: import("express").Request) { const email = req.headers["x-forwarded-email"]; const preferred = req.headers["x-forwarded-preferred-username"]; const user = req.headers["x-forwarded-user"]; const pick = (h: string | string[] | undefined): string | undefined => typeof h === "string" && h.length > 0 ? h : undefined; return { email: pick(email), preferredUsername: pick(preferred), userId: pick(user), }; } function authMiddleware( req: import("express").Request, _res: import("express").Response, next: import("express").NextFunction, ) { const canonical = readCanonicalUserHeaders(req); if (canonical.email || canonical.userId) { // userId persists in Lakebase as TEXT — prefer the email so it lines up // with MLflow AssessmentSource.source_id. Fall back to x-forwarded-user. const userId = canonical.email ?? canonical.userId!; const email = canonical.email ?? canonical.preferredUsername ?? "unknown@local"; req.session = { userId, email, name: canonical.preferredUsername, authenticated: true, }; } else { req.session = { userId: process.env.DEV_USER_EMAIL ?? "local-dev-user", email: process.env.DEV_USER_EMAIL ?? "dev@local", authenticated: false, }; } next(); } function requireAuth( req: import("express").Request, res: import("express").Response, next: import("express").NextFunction, ) { if (!req.session) { return res.status(401).json({ error: "Authentication required" }); } next(); } function requireChatAccess( req: import("express").Request, res: import("express").Response, next: import("express").NextFunction, ) { const chatId = req.params.chatId; if (!chatId) return next(); AppKit.lakebase .query(`SELECT "userId", visibility FROM chat."Chat" WHERE id = $1`, [chatId]) .then((result) => { if (result.rows.length === 0) return res.status(404).json({ error: "Chat not found" }); const chat = result.rows[0]; if (chat.userId !== req.session?.userId && chat.visibility !== "public") { return res.status(403).json({ error: "Access denied" }); } next(); }) .catch(() => next()); } ``` Apply the middleware to all chat routes: ```typescript AppKit.server.extend((app) => { app.use("/api/chat", authMiddleware, requireAuth); app.use("/api/history", authMiddleware, requireAuth); app.use("/api/messages", authMiddleware, requireAuth); app.use("/api/feedback", authMiddleware, requireAuth); app.use("/api/session", authMiddleware); }); ``` Route handlers then read `req.session.userId` instead of calling a helper. See [references/session-auth.md](references/session-auth.md) for the full middleware reference, OBO header format, and access control rules. **Gate:** `npm run build` passes with the middleware imports. Hitting any protected route without the canonical `x-forwarded-email` / `x-forwarded-user` headers (i.e. running outside the Databricks Apps platform) returns the local-dev fallback identity. --- ## Step 3: Add History API Routes These routes power the sidebar and message loading. All use `AppKit.lakebase.query()` — no ORM. ```typescript AppKit.server.extend((app) => { async function ensureUser(req: import("express").Request) { const userId = req.session!.userId; const email = req.session!.email; await AppKit.lakebase.query( `INSERT INTO chat."User" (id, email) VALUES ($1, $2) ON CONFLICT (id) DO NOTHING`, [userId, email], ); return userId; } app.get("/api/history", async (req, res) => { try { const userId = await ensureUser(req); const limit = Math.min(parseInt(req.query.limit as string) || 20, 100); const startingAfter = req.query.starting_after as string | undefined; const endingBefore = req.query.ending_before as string | undefined; let sql: string; let params: unknown[]; if (startingAfter) { sql = `SELECT id, title, "createdAt", visibility FROM chat."Chat" WHERE "userId" = $1 AND "createdAt" < (SELECT "createdAt" FROM chat."Chat" WHERE id = $2) ORDER BY "createdAt" DESC LIMIT $3`; params = [userId, startingAfter, limit + 1]; } else if (endingBefore) { sql = `SELECT * FROM ( SELECT id, title, "createdAt", visibility FROM chat."Chat" WHERE "userId" = $1 AND "createdAt" > (SELECT "createdAt" FROM chat."Chat" WHERE id = $2)