05-appkit-lakebase-wiring

# Wire Lakebase Backend into AppKit Design a database schema from a PRD, build Express API routes with mock fallback, wire the React frontend to live data, and verify locally. ## When to Use - Wiring a Lakebase PostgreSQL backend into an AppKit app that already has the plugin registered - Designing database tables from a PRD or feature spec - Building CRUD API routes with `server.extend()` - Replacing static mock data with API-backed data fetching - Adding a health endpoint and ConnectionStatus indicator **Not for registering the plugin.** Use `04-appkit-plugin-add` to install and configure the Lakebase plugin first. **Not for deploying.** Use `03-appkit-deploy` after wiring is complete. --- ## Before You Begin **Prerequisites — verify these before proceeding:** 1. The Lakebase plugin is registered in `server/server.ts` (via `04-appkit-plugin-add`) 2. Bundle resources (`postgres_project`/`postgres_branch`/`postgres_endpoint`) are declared in `databricks.yml` 3. `app.yaml` has `LAKEBASE_ENDPOINT` with `valueFrom: postgres` and `DB_SCHEMA` as a static env var 4. `DB_SCHEMA` is set to a user-scoped name derived from `$APP_NAME` (hyphens → underscores, e.g., `jane_d_booking_app`) 5. `npm run build` passes with the Lakebase plugin imported **Upstream docs (always check for latest):** ```bash npx @databricks/appkit docs "lakebase" ``` > **Build system note:** AppKit uses `tsdown` with `unbundle: true` for server compilation. Each `.ts` file in `server/` gets its own `.js` output, and relative imports between them are preserved. You can safely split `server/server.ts` into multiple files (e.g., `server/mock-data.ts`, `server/mappers.ts`) — they will resolve correctly at runtime. The entry point remains `server/server.ts`. ### Working in Genie Code (client routing) All the **code** in this skill — DDL, routes, hooks, mappers — is written into the project the same way on both clients. The DDL is intentionally executed **server-side on first deploy** (the "Deploy-First Pattern" in Step 1d), so it is already client-agnostic. Only the **local build/test gates** differ: | IDE/CLI (as written) | Genie Code substitution | |----------------------|--------------------------| | `npm run build` gates (Steps 2g, 3c2, 4a, 4b) — "**You MUST run `npm run build`**" | **IDE-only** convenience — there is no local Node toolchain on Genie Code. Skip them; the platform runs the same build **server-side** on deploy, and TypeScript/import errors surface in `databricks apps logs <name>`. Wire the code, then deploy and read the logs. | | `npm run dev` | not available on Genie Code (and blocked pre-deploy anyway — `lakebase()` needs platform-injected env) — verify on the deployed app instead | | `npx @databricks/appkit docs "lakebase"` | npx absent (P9) — WebFetch https://databricks.github.io/appkit/docs/plugins/lakebase | | `databricks … --profile $PROFILE` | run via `runDatabricksCli`, **omit `--profile`** (pre-authenticated) | | local `curl … -H "Authorization: Bearer …"` health test | browser `ConnectionStatus` + `apps logs`, **or** the 3-hop OAuth `requests.Session()` test (see Quick Reference note and `03-appkit-deploy`) | Paths are relative to `apps_lakebase/$APP_NAME` — on Genie Code that resolves inside your git-cloned workshop project (`artifact_root`), never the read-only `.assistant/skills` copy and never `/tmp`. The `apps validate` skip and the health-test routing are already flagged inline at Steps 4a2 and Quick Reference. See `skills/genie-code-environment` for the full manifest. --- ## Decision Defaults When multiple approaches are valid, use these defaults. Override only if the PRD demands it. | Decision | Default | Rationale | |----------|---------|-----------| | Where does mock data live? | `server/mock-data.ts` — server owns all fallback data | Client and server are separate builds; cannot import across the boundary | | Single server file or split? | **Split when `server.ts` exceeds 300 lines.** Extract mappers to `server/mappers.ts`. Count lines after writing: `wc -l server/server.ts`. | `tsdown unbundle: true` preserves relative imports between server files (see note above) | | Numeric PK or text PK? | Use `text` PK if the frontend already uses formatted IDs (e.g., `"lst-001"`) everywhere; otherwise `bigint identity` | Avoids a format-conversion layer that touches every route and mapper | | N+1 queries or JOINs? | Application-side joins for <50 rows; SQL JOINs for larger datasets | Simpler code; performance is irrelevant at seed-data scale | | Client-side or server-side validation? (promos, discounts) | Keep client-side for MVP; migrate to server in a follow-up | Reduces scope of this step | | Dynamically generated data (available dates, time slots)? | Generate in the mapper function, not a database table | Avoids seeding hundreds of ephemeral rows | | Mock fallback data format? | camelCase (matching API response after mappers) | Catch block returns data in the same shape as the live path | | Seed data: parameterized inserts or raw SQL? | **Parameterized (`$1, $2`) iterating over MOCK_* arrays** for all tables when mock-data.ts exists. Raw SQL only when no mock array exists and data is purely numeric. | Single source of truth; type-safe; avoids escaping bugs | --- ## Step 1: Design the Database Schema Derive your database schema from the PRD. This step produces DDL and seed data in `server/server.ts`. ### 1a. Identify Entities from the PRD Read the PRD and extract: - **Entities** — each major noun becomes a table (e.g., `bookings`, `listings`, `users`) - **Attributes** — each property becomes a column - **Relationships** — identify 1:N (FK) and M:N (junction table) relationships - **Data types** — map PRD fields to PostgreSQL types using the conventions below ### 1b. PostgreSQL Type Conventions For normalization rules, naming conventions, and additional type guidance, see [references/database-design-guide.md](references/database-design-guide.md). | PRD Concept | PostgreSQL Type | Why | |-------------|----------------|-----| | Unique ID | `bigint generated always as identity primary key` | SQL-standard, future-proof (not `serial`) | | Short text (name, email, status) | `text` | Same performance as `varchar(n)`, no artificial limit | | Constrained text (status enum) | `text` + `CHECK` constraint | `CHECK (status IN ('pending','confirmed','cancelled'))` | | Money / price | `numeric(10,2)` | Exact decimal arithmetic (not `float`) | | Timestamp | `timestamptz default now()` | Always timezone-aware (not `timestamp`) | | Date only | `date` | Calendar dates without time component | | Boolean flag | `boolean default false` | 1 byte (not `varchar`) | | Foreign key | `bigint references other_table(id)` | Always add an index on FK columns | ### 1c. Schema Naming Use `DB_SCHEMA` (from env var) as the PostgreSQL schema for all objects: ```sql CREATE SCHEMA IF NOT EXISTS ${DB_SCHEMA}; CREATE TABLE IF NOT EXISTS ${DB_SCHEMA}.bookings ( ... ); ``` This prevents collisions when multiple apps share a Lakebase database. All DDL, queries, and grants must use `${DB_SCHEMA}` consistently. ### 1d. Write Idempotent DDL All DDL runs on every app startup. It must be safe to execute repeatedly. ```typescript const AppKit = await createApp({ plugins: [server(), lakebase()], }); const DB_SCHEMA = process.env.DB_SCHEMA || "app"; await AppKit.lakebase.query(`CREATE SCHEMA IF NOT EXISTS ${DB_SCHEMA}`); await AppKit.lakebase.query(` CREATE TABLE IF NOT EXISTS ${DB_SCHEMA}.orders ( id bigint generated always as identity primary key, customer_name text not null, amount numeric(10, 2) not null, status text default 'pending' check (status in ('pending', 'confirmed', 'cancelled')), created_at timestamptz default now() ) `); ``` **Index foreign key and frequently filtered columns:** ```typescript await AppKit.lakebase.query(` CREATE INDEX IF NOT EXISTS idx_orders_status ON ${DB_SCHEMA}.orders (status) `); ``` > **Deploy-First Pattern:** DDL is written in `server.ts` so the Service Principal executes it on first deploy and becomes the owner of the schema, tables, and sequences. This is critical — if you run DDL locally first, your personal identity owns the objects and the SP cannot access them after deployment. The workshop sequence (wire code locally with mock fallback, then deploy so SP creates objects) follows this pattern. Reference: [AppKit Lakebase docs - Local development](https://databricks.github.io/appkit/docs/plugins/lakebase#local-development) ### 1e. Seed Data (Count-Check Pattern) Use a count-check pattern for idempotent seeding. Do NOT use `ON CONFLICT DO NOTHING` — it fails to prevent duplicates with `identity`/`serial` PKs that auto-generate new IDs on every insert. ```typescript const seedCheck = await AppKit.lakebase.query( `SELECT count(*) AS cnt FROM ${DB_SCHEMA}.orders` ); if (parseInt(seedCheck.rows[0].cnt) === 0) { await AppKit.lakebase.query(` INSERT INTO ${DB_SCHEMA}.orders (customer_name, amount, status) VALUES ('Alice', 99.99, 'confirmed'), ('Bob', 45.00, 'pending'), ('Carol', 72.50, 'confirmed') `); console.log("[Lakebase] Seed data inserted"); } ``` > **Apostrophe escaping:** If seed data contains apostrophes (e.g., `chef's kitchen`), use double single quotes in raw SQL: `'chef''s kitchen'`. Alternatively, use parameterized inserts (`$1, $2`) which handle escaping automatically — see the Decision Defaults table. For multi-table seed patterns with foreign keys, see [references/multi-table-example.md](references/multi-table-example.md). ### 1f. Mock Data Strategy When the app already has a client-side `mockData.ts` with static arrays, use this migration pattern: 1. **Create `server/mock-data.ts`** with the same data in camelCase format (matching what mappers would produce from DB rows). Export typed arrays. 2. **Import into `server/server.ts`** for use in catch-block fallbacks and as the source for seed SQL generation. 3. **Keep `client/src/data/mockData.ts`** for type definitions, UI constants (filter options, property type lists), and utility functions. Remove the data arrays once all pages use `useLakebaseData`. 4. **Pages switch from** `import { LISTINGS } from '../data/mockData'` **to** `useLakebaseData<Listing>('/api/listings')` — the server returns mock fallback data when Lakebase is unavailable, so the client never needs its own copy of the data. File ownership after wiring: | File | Contains | Does NOT contain | |------|----------|------------------| | `server/mock-data.ts` | All data arrays (listings, bookings, etc.) in camelCase | Type definitions, UI constants | | `client/src/data/mockData.ts` | Type interfaces, filter-option constants, utility functions | Data arrays (deleted after migration) | | `server/server.ts` | DDL, seed logic, routes importing from `mock-data.ts` | Inline mock data objects | > **This table is prescriptive.** `server/mock-data.ts` must NOT define TypeScript interfaces — those belong exclusively in `client/src/data/mockData.ts`. The server file exports data arrays only and imports types from the client when feasible, or uses `Record<string, unknown>` parameter types with explicit return-type annotations. Duplicating interfaces causes type drift (e.g., server `status: string` vs. client `status: "confirmed" | "cancelled"`). --- ## Step 2: Build API Routes AppKit Lakebase is **server-side only** — there are no frontend hooks like `useAnalyticsQuery`. Use `server.extend()` to add Express routes. > **Note:** The upstream [databricks-agent-skills](https://github.com/databricks/databricks-agent-skills) Lakebase reference uses tRPC for server-side CRUD. This skill uses `server.extend()` with Express routes for explicit control over request/response handling, which is simpler for workshop purposes. Both patterns are valid AppKit approaches. If the scaffold generated tRPC boilerplate (from `--features lakebase`), either pattern works — use whichever is already in your codebase. ### 2a. Server Setup Pattern Register custom routes inside the `onPluginsReady` callback. It runs after the plugins initialize but **before** the `server()` plugin starts listening — exactly the window where `appkit.server.extend()` must attach Express routes. Move the Step 1 DDL and the Step 1e seed inside this same callback (before the `extend` call) so the schema exists before the server accepts traffic. Do **NOT** pass `autoStart: false` and do **NOT** call `AppKit.server.start()` yourself: ```typescript import { createApp, server, lakebase } from "@databricks/appkit"; const DB_SCHEMA = process.env.DB_SCHEMA || "app"; await createApp({ plugins: [server(), lakebase()], async onPluginsReady(appkit) { // DDL + seed (from Step 1) — run before the server accepts traffic await appkit.lakebase.query(`CREATE SCHEMA IF NOT EXISTS ${DB_SCHEMA}`); // ... CREATE TABLE / INDEX + count-check seed ... appkit.server.extend((app) => { // Register routes here (Steps 2b-2d) }); }, }); ``` > **`npm run dev` will NOT work until after the first deploy.** The `lakebase()` plugin throws `ConfigurationError` during `createApp()` when `LAKEBASE_ENDPOINT` and `PGHOST` are not set. These env vars are injected by the platform after the Lakebase project is provisioned (first deploy). Use **`npm run build` only** for local validation at this step — it type-checks and bundles without executing the code. Runtime testing with `npm run dev` is available after deployment creates the Lakebase project and you populate `.env` with connection details (see the **Deploy and E2E Test** step, Step 7). > **Gotcha — register routes in `onPluginsReady`, never call `server.start()` manually.** The `server()` plugin owns the HTTP listener. Passing `autoStart: false` and then calling `AppKit.server.start()` yourself is the stale pattern — it double-`listen()`s (the plugin still binds the port) and the app crashes on boot (`EADDRINUSE`). The supported shape is the `onPluginsReady(appkit)` hook on `createApp`: it fires after plugins init but before the listener binds, so `appkit.server.extend(...)` routes are attached in time. The `server()` plugin also auto-adds `/health` — do not register it yourself. Reference: [developer docs — AppKit `createApp` (`onPluginsReady` hook)](https://developers.databricks.com/appkit). > **Gotcha — Do NOT annotate `app` with `: any` or `: Express`.** AppKit's AST-grep linter blocks `any` annotations (`no-as-any` rule), and importing `Express` from the `express` module causes TS2345 (type mismatch with AppKit's internal type). Leave `app` untyped in `server.extend((app) => { ... })` — TypeScript infers the correct type from the callback signature. For route handler parameters, import and use `Request` and `Response` types from `express`. ### 2b. Response Contract Every data endpoint must return this shape: ```typescript