Definite Data Apps

Build interactive React applications that run inside Definite Docs. Data apps compile to a single HTML file with client-side DuckDB WASM, Perspective.js, and a built-in component library.

How data flows

app.json          Definite platform       Browser DuckDB WASM        App.tsx
(manifest)   -->  (server-side fetch) --> (local tables)        -->  (client-side SQL)

1. app.json declares every data resource the app needs. Each resource has a key, a kind, and a source.
2. The Definite platform reads the manifest and fetches data server-side (from DuckLake, Cube, GCS, etc.). The app never talks to the warehouse directly.
3. The runtime loads fetched data into a browser-side DuckDB WASM instance as local tables.
4. App.tsx queries those local tables via useSqlQuery(dataset, sql, deps). These SQL queries run in the browser, not against the server.

Column names in your useSqlQuery SQL must match the aliases in your app.json SQL. If app.json has SELECT foo AS myColumn, the local table has a column called myColumn.

Quick start

git clone https://github.com/definite-app/definite-data-apps.git
cd definite-data-apps
make setup                                   # npm install
make new-app NAME=my-app                     # blank template (one KPI)
# or:
make new-app NAME=my-app TEMPLATE=refined    # sidebar shell + drill drawer
# Edit examples/my-app/app.json              (declare your data resources)
# Edit examples/my-app/src/App.tsx           (build your UI)
make build NAME=my-app                       # build to dist/index.html

Two bundled templates live under templates/:

| Template | When to use | |---|---| | blank | Single-view dashboards, embedded tiles, anything that doesn't need a navigation rail. Built on AppShell. | | refined | Multi-view analytics apps. Sidebar with nav + date range + 10-filter accordion, KPI cards with sparklines, drill drawer, cache popover, optional AI follow-up chat. Built on ShellLayout + Sidebar. |

To preview bundled examples with mock data:

make preview NAME=revenue-explorer    # blank-style
make preview NAME=loan-portfolio      # refined-style reference app
open examples/<name>/dist/index.html

Directory structure

examples/my-app/
  app.json              # Manifest: declares all data resources
  preview-data.json     # Optional: mock data for local preview
  src/
    main.tsx            # Entry point (boilerplate, don't edit)
    App.tsx             # Your UI code (imports from "@definite/runtime")
  dist/
    index.html          # Built artifact (generated by build.mjs)

Deploy dist/index.html by uploading it to Definite Drive and creating a Doc with an HTML tile:

version: 1
schemaVersion: "2025-01"
kind: dashboard
metadata:
  name: "My App"
datasets: {}
layout:
  columns: 36
  tiles:
    - id: app
      x: 0
      y: 0
      w: 36
      h: 22
      type: html
      fullScreen: true
      driveFile: "apps-v2/my-app/dist/index.html"

Manifest (app.json)

The manifest declares what data the app needs. The platform fetches it server-side and the runtime loads it into the browser.

{
  "version": 2,
  "name": "Revenue Explorer",
  "entry": "src/main.tsx",
  "resources": {
    "transactions": {
      "kind": "dataset",
      "source": {
        "type": "sql",
        "sql": "SELECT id AS transactionId, STRFTIME(created_at, '%Y-%m-%d') AS transactionDate, amount::DOUBLE AS amount FROM LAKE.SCHEMA.transactions LIMIT 200000"
      },
      "public": false
    },
    "branches": {
      "kind": "json",
      "source": {
        "type": "sql",
        "sql": "SELECT branch_id AS branchId, branch_name AS branchName FROM LAKE.SCHEMA.branches ORDER BY 2 LIMIT 3000"
      },
      "snapshot": {
        "format": "json",
        "drivePath": "apps-v2/my-app/snapshots/branches.json"
      },
      "public": true
    }
  }
}

Resource kinds

| Kind | Hook | Use for | |------|------|---------| | dataset | useDataset(key) | Data loaded into browser DuckDB WASM as a local table | | json | useJsonResource(key) | Small lookup lists returned as plain arrays |

Source types

| Type | Description | |------|-------------| | sql | SQL query executed server-side against DuckLake. Recommended for most cases. | | duckdbFile | A .duckdb file downloaded from Drive/GCS and attached locally | | cube | Cube semantic model query. Not recommended (column names are Cube titles with spaces). |

Snapshots

For public embeds, add a snapshot block to pre-cache data:

"snapshot": {
  "format": "json",
  "drivePath": "apps-v2/my-app/snapshots/branches.json"
}

Runtime hooks

useDataset(key, opts?)

Loads a kind: "dataset" resource into browser DuckDB WASM. Returns a DatasetHandle with:

• tableRef: the table reference string for use in SQL (e.g., memory.main.transactions)
• db, conn: the DuckDB WASM database and connection
• perspectiveTable: table name for Perspective viewer
• loading, error: loading state
• cache: cache metadata (source, load time, TTL)
• refresh(): hard refresh (bypasses IndexedDB cache)

const data = useDataset("transactions");

if (data.loading) return <LoadingState message="Loading..." />;
if (data.error) return <ErrorState title="Error" message={data.error} />;

useSqlQuery(dataset, sql, deps?)

Runs client-side SQL against a loaded dataset's DuckDB WASM instance.

const result = useSqlQuery(
  data,
  data.tableRef
    ? `SELECT branchName, SUM(amount)::INTEGER AS total FROM ${data.tableRef} GROUP BY 1`
    : "",
  [],
);
// result.data = [{ branchName: "Austin", total: 2685 }, ...]

useJsonResource(key, opts?)

Loads a kind: "json" resource. Returns { data, loading, error, cache, refresh }.

const branches = useJsonResource<{ branchId: string; branchName: string }>("branches");
// branches.data = [{ branchId: "AUS", branchName: "Austin" }, ...]

useTheme()

Returns { theme, toggleTheme } for dark/light mode support. Pass theme and toggleTheme to AppShell.

usePerspective(dataset)

Initializes a Perspective client connected to the dataset's DuckDB instance. Returns { client, perspectiveTable, loading, error } for use with PerspectivePanel.

Component reference

The runtime includes a complete component library. Import from @definite/runtime.

Layout

AppShell

Page wrapper with title, subtitle, theme toggle, and optional meta slot.

| Prop | Type | Description | |------|------|-------------| | title | string | Page title | | subtitle | string? | Subtitle below title | | theme | PerspectiveTheme | Current theme from useTheme() | | onToggleTheme | () => void | Toggle function from useTheme() | | meta | ReactNode? | Content below subtitle (e.g., ResourceCacheBadge) | | children | ReactNode | Page content |

Card

Container with optional title and right-aligned header content.

| Prop | Type | Description | |------|------|-------------| | title | string? | Card header title | | headerRight | ReactNode? | Right-aligned header content | | noPadding | boolean? | Remove inner padding (for tables, charts) | | children | ReactNode | Card body |

TabGroup

Tab bar with accent underline.

| Prop | Type | Description | |------|------|-------------| | tabs | string[] | Tab labels. Must be a plain string array, NOT {key, label} objects. | | activeTab | string | Currently active tab | | onTabChange | (tab: string) => void | Tab change handler | | children | ReactNode? | Content below tabs |

Data display

KpiCard

Metric card with hover lift, accent top-line, and shimmer loading state.

| Prop | Type | Description | |------|------|-------------| | title | string | Metric label | | value | unknown | Metric value | | format | "number" \| "currency" \| "percent" | Required. number: commas, no decimals. currency: USD. percent: appends "%" with 1 decimal. | | loading | boolean? | Show shimmer placeholder | | detail | ReactNode? | Content below value (badges, comparison deltas) |

Pre-formatted strings (e.g., "53.6s") pass through as-is. NaN/Infinity display as a dash.

DataTable

Simple table with row hover.

| Prop | Type | Description | |------|------|-------------| | columns | { key: string; label: string }[] | Column definitions | | rows | Record<string, unknown>[] | Row data | | emptyState | string? | Empty state message |

ReportTable

Rich management report table with grouped column headers, colored bands, section dividers, subtotal/total rows, and per-cell conditional styling.

| Prop | Type | Description | |------|------|-------------| | headerGroups | { label, colSpan?, color?, subHeaders[] }[] | Grouped headers with optional color bands | | rows | { type?, indent?, cells }[] | Rows with type: data, section, subtotal, or total | | emptyState | string? | Empty state message |

Badge

Status indicator with colored dot.

| Prop | Type | Description | |------|------|-------------| | variant | "default" \| "success" \| "warning" \| "error" \| "info" | Color scheme | | dot | boolean? | Show/hide the dot (default: true) | | children | ReactNode | Badge text |

Charts

EChart

Apache ECharts wrapper. Handles init/dispose lifecycle, theme switching, and resize.

| Prop | Type | Description | |------|------|-------------| | option | Record<string, unknown> | Full ECharts option spec | | height | number? | Chart height in pixels | | theme | PerspectiveTheme? | Theme for auto-switching | | onClick | (params) => void | Click handler for drill-down |

Critical: EChart serializes options through JSON.stringify. Functions are silently stripped. Do not use formatter, valueFormatter, or callbacks. Use ECharts string templates instead (e.g., "{value}%").

PerspectivePanel

Wrapper for Perspective.js viewer. Supports Datagrid, Y Bar, Y Line, Y Area, X Bar, Y Scatter, Heatmap, Treemap, and Sunburst.

| Prop | Type | Description | |------|------|-------------| | client | any | Perspective client from usePerspective() | | table | string | Table name from dataset's perspectiveTable | | theme | PerspectiveTheme | Current theme | | config | Record<string, unknown>? | Perspective viewer config (plugin, columns, group_by, etc.) | | onSelect | (row) => void | Click handler for drill-down (perspective-select event) |

Example:

const perspective = usePerspective(data);

<PerspectivePanel
  client={perspective.client}
  table={data.perspectiveTable}
  theme={theme}
  config={{
    plugin: "Y Bar",
    columns: ["amount"],
    group_by: ["branchName"],
    aggregates: { amount: "sum" },
    sort: [["amount", "desc"]],
  }}
  onSelect={(row) => {
    if (!row) return;
    setFilter(prev => prev === row.branchName ? "" : String(row.branchName));
  }}
/>

Inputs

Select

Single-select dropdown.

| Prop | Type | Description | |------|------|-------------| | options | { value: string; label: string }[] | Options | | value | string | Selected value | | onChange | (value: string) => void | Change handler | | placeholder | string? | Placeholder text | | label | ReactNode? | Label above dropdown |

MultiSelect<T>

Searchable checkbox dropdown.

| Prop | Type | Description | |------|------|-------------| | options | T[] | All options | | selected | T[] | Selected items | | onChange | (selected: T[]) => void | Change handler | | labelKey | string | Key for display label | | valueKey | string | Key for value | | label | ReactNode? | Label above dropdown | | placeholder | string? | Placeholder text |

FilterPills

Single-select horizontal toggle group.

| Prop | Type | Description | |------|------|-------------| | options | { value: string; label: string }[] | Options | | value | string | Selected value | | onChange | (value: string) => void | Change handler | | label | ReactNode? | Label above pills |

TextInput

Text input with focus ring and optional icon.

| Prop | Type | Description | |------|------|-------------| | value | string | Current value | | onChange | (value: string) => void | Change handler | | placeholder | string? | Placeholder | | label | ReactNode? | Label | | icon | ReactNode? | Icon on left |

DateInput

Native date picker with design system styling.

| Prop | Type | Description | |------|------|-------------| | value | string | Date string (YYYY-MM-DD) | | onChange | (value: string) => void | Change handler | | label | ReactNode? | Label | | max | string? | Max date | | min | string? | Min date |

Feedback

LoadingState

Full-page pulsing dots animation.

| Prop | Type | Description | |------|------|-------------| | message | string? | Loading message (default: "Loading...") |

ErrorState

Full-page error with red left bar.

| Prop | Type | Description | |------|------|-------------| | title | string | Error title | | message | string | Error details |

Tooltip

Hover tooltip with arbitrary content.

| Prop | Type | Description | |------|------|-------------| | content | ReactNode | Tooltip content | | children | ReactNode | Trigger element | | position | "top" \| "bottom" | Position (default: top) | | maxWidth | number? | Max width in pixels (default: 240) |

ResourceCacheBadge

Cache metadata popover showing row count, source, load time, TTL, and "Clear cache & reload" button.

| Prop | Type | Description | |------|------|-------------| | rows | number? | Row count to display | | cache | ResourceCacheDetails | Cache metadata from useDataset() | | onClearAndReload | () => Promise<void> | Clear + reload handler (use dataset's refresh()) |

Refined SaaS shell primitives

A second track of components built for multi-view analytics apps. They're driven by a palette object (not CSS vars), so apps can pass a brand accent at the root and every surface (KPI top-line, active nav, filter chip, loading dot) picks it up. Pair with the refined template.

import {
  buildPalette, PaletteProvider, usePalette,
  ShellLayout, Sidebar, SaasKpiCard,
  DrillProvider, useDrill, CachePopover,
  callFiFast, buildDrillPrompt,
} from "@definite/runtime";

const palette = buildPalette(theme, { accent: "#FF006E" });

<PaletteProvider value={palette}>
  <DrillProvider
    aiChat={{
      onAsk: (q, entity) => callFiFast({ prompt: buildDrillPrompt(q, entity) }),
    }}
  >
    <ShellLayout
      palette={palette}
      sidebar={<Sidebar logo={...} navItems={...} activeView={...} ... />}
      title="Overview"
      headerRight={<CachePopover cache={data.cache} onRefresh={data.refresh} ... />}
    >
      <SaasKpiCard title="Total rows" value={count} onClick={() => drill.open({...})} />
    </ShellLayout>
  </DrillProvider>
</PaletteProvider>

Palette

| Export | Purpose | |--------|---------| | buildPalette(theme, { accent? }) | Returns a SaasPalette of semantic colors + fonts. accent override derives accentSoft automatically. | | PaletteProvider / usePalette() | Context for descendant primitives. |

Shell

| Export | Purpose | |--------|---------| | ShellLayout | Outer flex container; renders the sidebar slot, breadcrumb, title, and a headerRight slot (typically CachePopover + an Export button). Wraps children in PaletteProvider. | | Sidebar | Logo, nav, dateRangeSlot, FilterAccordion (if filterGroups provided), theme toggle, footer slot. | | FilterAccordion | Collapsible filter groups with per-group counts, global + per-group search, selected chips. | | Breadcrumb | Simple /-separated trail. |

Data display

| Export | Purpose | |--------|---------| | SaasKpiCard | Accent top-line + sparkline + delta pill + loading shimmer. Props: title, value, delta?, up?, sub?, spark?, accent?, loading?, onClick?. | | Sparkline | 32-px SVG polyline + last-point dot. Props: values, color?, width?, height?. | | SkeletonShimmer | Palette-driven loading shimmer. Props: width?, height?, radius?. | | CachePopover | Click-to-inspect cache pill with "Clear cache & reload". Same cache object as useDataset().cache. |

Drill drawer

DrillProvider mounts a slide-over drawer. Any descendant calls useDrill().open(entity) to show it. The drawer renders computed stats, breakdown bars, SQL, and an optional AI follow-up chat.

const drill = useDrill();
drill.open({
  kind: "kpi",                 // "kpi" | "row" | "chart"
  id: "total_outstanding",
  title: "Total outstanding",
  value: "$50.8M",
  breadcrumb: "Overview",
  stats: [["Active", "2,511"], ["Avg", "$33K"]],
  breakdown: [{ label: "2026-04", value: 1_780_000 }, ...],
  sql: "SELECT SUM(balance) FROM loans WHERE ...",
  narrative: "Total principal balance across active contracts.",
});

Wire AI chat by passing aiChat to DrillProvider:

<DrillProvider
  aiChat={{
    onAsk: (userMessage, entity) => callFiFast({
      prompt: buildDrillPrompt(userMessage, entity),
    }),
    placeholder: "Ask a follow-up…",
  }}
>

AI integration

| Export | Purpose | |--------|---------| | callFiFast({ prompt, system?, authToken?, ... }) | One-shot POST to /v4/fi-fast. Extracts response.content.parts[0].text. Same-origin cookies by default; pass authToken for Bearer auth. | | buildDrillPrompt(userMessage, entity) | Default prompt builder that grounds the model in the drill entity's stats + breakdown. Use as-is or wrap. |

When to use ShellLayout vs AppShell

| Use case | Component | |----------|-----------| | Multi-view standalone analytics app with nav rail | ShellLayout + Sidebar (refined template) | | Embedded Doc tile, single-view dashboard, email-receipt-style view | AppShell (blank template) |

Both remain supported and will coexist.

Best practices

Always use SQL resources

Use type: "sql" for dataset resources. SQL gives you full control over column names via AS aliases. Avoid type: "cube" because Cube responses use long title-based column names (e.g., "Credit Projects FICO Band") that require double-quoting everywhere.

Use camelCase aliases

Alias all columns to camelCase in your app.json SQL:

SELECT
  installer_account_name AS installerAccountName,
  fico::INTEGER AS fico,
  STRFTIME(application_date, '%Y-%m-%d') AS applicationDate,
  approval_cnt::INTEGER AS approvalCnt
FROM LAKE.credit.projects
LIMIT 500000

Keep client-side SQL simple

| Server-side (app.json SQL) | Client-side (useSqlQuery) | |---|---| | Complex joins, subqueries, CTEs | Simple GROUP BY + aggregation | | CASE WHEN with compound booleans | SUM/COUNT of pre-computed columns | | NOT IN, LIKE, regex filters | Simple WHERE on date/string equality | | Type casts (BIGINT, DOUBLE) | ::INTEGER on SUM results | | Flag computation | Date range filters |

Always cast SUM to INTEGER

DuckDB WASM may return HUGEINT from SUM, which JavaScript cannot handle cleanly:

// Good
`SELECT SUM(amount)::INTEGER AS total FROM ${data.tableRef}`

// Bad: may return BigInt that breaks rendering
`SELECT SUM(amount) AS total FROM ${data.tableRef}`

Pre-compute flags server-side

When you need conditional counts, compute the flags in app.json SQL, then aggregate client-side:

"sql": "SELECT ..., CASE WHEN agent_time > 0 AND skill NOT IN ('123','456') THEN 1 ELSE 0 END AS isHandled FROM ..."

const kpis = useSqlQuery(data, data.tableRef ? `
  SELECT SUM(isHandled)::INTEGER AS handled FROM ${data.tableRef}
` : "", []);

Convert dates with STRFTIME

Always convert DuckDB dates/timestamps to VARCHAR in app.json SQL:

STRFTIME(created_at, '%Y-%m-%d') AS createdDate

DuckDB WASM limitations

DuckDB WASM 1.29.0 has known issues with complex expressions. Compound CASE WHEN with AND/OR chains, NOT IN with many values, or LIKE patterns may silently return 0 for all rows in the browser, even though the same query works correctly server-side.

Rule: if you're writing a CASE WHEN with more than one condition in useSqlQuery, move it to app.json instead.

Caching

The runtime caches successful data loads in IndexedDB with a 24-hour TTL. Cache keys include the drive file path, resource key, mode, and manifest definition, so rebuilt apps invalidate naturally.

Use ResourceCacheBadge in your AppShell meta slot to show cache status:

<AppShell
  title="My App"
  meta={
    <ResourceCacheBadge
      rows={result.data?.length}
      cache={data.cache}
      onClearAndReload={async () => { await data.refresh(); }}
    />
  }
>

Call refresh() on useDataset() or useJsonResource() for a hard refresh that bypasses IndexedDB.

Version pins

These versions are pinned because the Arrow ingestion path is version-sensitive:

| Library | Version | |---------|---------| | DuckDB WASM | 1.29.0 | | Apache Arrow | 17.0.0 | | Perspective | 4.3.0 |

License

MIT