# Typed data

Declare a table schema once and get a typed query API in both halves of your extension, with automatic additive migration when the schema grows.

Source: https://docs.vampikez.fun/build/typed-data/

Typed data is the structured store an extension keeps on its engine host. You
declare tables as a plain object, and the host generates the SQL, compiles your
queries, and hands back an API where column names, row shapes, and `where`
clauses are all checked by TypeScript. When you later add a column, the host
adds it to the existing table instead of losing the rows.

After this page you can declare a schema, read and write it from both the main
and renderer halves of an extension, query it with predicates, and know exactly
which schema changes apply themselves and which ones stop you.

## Declare the schema

Put the schema in a file both halves import — that single source of truth is the
whole point, because the renderer's type inference and main's DDL generation
must agree.

```ts
// shared/schema.ts

export const schema = {
  version: 1,
  tables: {
    tasks: {
      id: { type: 'integer', primary: true, autoIncrement: true },
      title: { type: 'text', notNull: true },
      status: { type: 'enum', values: ['todo', 'doing', 'done'] as const, default: 'todo' },
      notes: { type: 'text' },
      dueAt: { type: 'datetime' },
      createdAt: { type: 'datetime', defaultNow: true, notNull: true },
    },
  },
  indexes: [
    { table: 'tasks', columns: ['status'] },
    { table: 'tasks', columns: ['dueAt'] },
  ],
} as const satisfies DataSchema;
```

`as const satisfies DataSchema` is not decoration. Without `as const` the enum
literals widen to `string`, and `where: { status: 'todo' }` stops being
type-checked. Without `satisfies` you lose the error when a column descriptor is
malformed.

### Schema fields

| Field | Type | Notes |
|---|---|---|
| `tables` | `Record<string, TableSchema>` | Required. Table name → column name → column descriptor. |
| `indexes` | `{ table, columns, unique? }[]` | Optional. Created after the tables, so an index may target a column added in the same run. |
| `version` | `number` | Optional, defaults to `1`. Only meaningful alongside `migrations`. |
| `migrations` | `{ version, up }[]` | Optional raw-SQL escape hatch. `up` runs when the persisted version is lower than `version`. |

### Column kinds

Every column is `{ type, …options }`. The shared options are `notNull`,
`primary` (implies `notNull`), and `unique`.

| `type` | JavaScript value | Extra options |
|---|---|---|
| `text` | `string` | `default` |
| `integer` | `number` | `default`, `autoIncrement` |
| `real` | `number` | `default` |
| `boolean` | `boolean` | `default` — stored as `0`/`1`, read back as a boolean |
| `datetime` | `Date` | `defaultNow: true` for the insert timestamp, or a literal `default` |
| `json` | `unknown` | `default` as plain JS — serialized on write, parsed on read |
| `enum` | the literal union | `values` (required), `default` |

A column with no `notNull` and no `primary` is nullable, and its inferred type
includes `null`. Writing a value outside an `enum`'s `values` throws
`Enum violation: 'x' is not in [a, b]` rather than storing it.

## Get a handle

The two halves reach the same store through two calls with the same argument.

```ts
// main/activate.ts — returns a Promise
const data = await ctx.api.data.defineSchema(schema);
```

```ts
// ui/index.tsx — synchronous; the proxy pushes the schema itself
const data = pluginAPI.data.defineSchema(schema);
```

Both are idempotent, and either one alone is enough: an app that is only a page
with no main half is initialized by the renderer call. The renderer surface is a
proxy over the host store, not a second copy of it — same rows, same file.

The store is one SQLite file per extension in the engine host's user-data
directory. On local Desktop that is the local machine; on a remote engine it
is the remote host, and renderer reads cross the engine transport. The API
scopes access by extension id. It is not an end-user directory or device-sync
service.

## Read and write

Each table on the handle carries six operations.

| Operation | Argument | Returns |
|---|---|---|
| `findMany` | `{ where?, orderBy?, limit?, offset? }` | `Row[]` |
| `findUnique` | `{ where }` | `Row \| null` |
| `create` | `{ data }` | the created `Row` |
| `update` | `{ where, data }` | `{ changes: number }` |
| `delete` | `{ where }` | `{ changes: number }` |
| `count` | `{ where? }` | `number` |

Plus `data.raw<R>(sql, params?)` on the handle itself, which returns untyped
rows for the queries the predicate vocabulary cannot express.

```ts
const created = await data.tasks.create({
  data: { title: 'Ship the docs', dueAt: new Date(Date.now() + 86_400_000) },
});

await data.tasks.update({ where: { id: created.id }, data: { status: 'doing' } });

const open = await data.tasks.count({ where: { status: q.ne('done') } });
```

`create` lets you omit any column that the runtime can fill in: nullable
columns, an `autoIncrement` primary key, anything with a literal `default`, and
`datetime` columns with `defaultNow: true`. Everything else is required, and
TypeScript says so.

## Query

A `where` clause maps column names to either a bare value (which means equality)
or a predicate built with `q`. Predicates from separate columns are `AND`-ed.

```ts

const urgent = await data.tasks.findMany({
  where: {
    status: q.in(['todo', 'doing']),
    dueAt: q.lt(q.now()),
  },
  orderBy: { dueAt: 'asc' },
  limit: 20,
});
```

| Predicate | SQL |
|---|---|
| `q.eq(v)` / a bare value | `= ?` |
| `q.ne(v)` | `<> ?` |
| `q.gt(v)`, `q.gte(v)`, `q.lt(v)`, `q.lte(v)` | `>`, `>=`, `<`, `<=` |
| `q.in(vs)` | `IN (…)`; an empty array matches no rows |
| `q.notIn(vs)` | `NOT IN (…)`; an empty array matches every row |
| `q.like(pattern)` | `LIKE ?` — you supply the `%` and `_` |
| `q.isNull()` / `q.isNotNull()` | `IS NULL` / `IS NOT NULL` |

`q.now()` returns a `Date` for use as a value, not a predicate — write
`dueAt: q.lt(q.now())`.

For alternatives, add an `OR` key holding a list of full `where` clauses. The
group is `AND`-ed with the column predicates beside it.

```ts
const mine = await data.tasks.findMany({
  where: {
    status: q.ne('done'),
    OR: [{ title: q.like('%docs%') }, { notes: q.like('%docs%') }],
  },
});
```

`orderBy` takes column names mapped to `'asc'` or `'desc'`, and several columns
sort in the order you wrote them. `limit` and `offset` are integers — pass
`offset` only together with `limit`, since an offset alone produces invalid SQL.

:::caution[Predicates must come from `q`, not from a bare object]
A `where` value is either a plain value or a `{ op, value }` shape built by `q`.
An object that looks Prisma-shaped — `{ createdAt: { gte: dayAgo } }` — has no
`op` key, so it is treated as a *value* and compiled to
`"createdAt" = '[object Object]'`. You get zero rows and no error. Write
`{ createdAt: q.gte(dayAgo) }`.
:::

Mistakes that do throw, immediately and by name: an unknown column in `where`
(`Unknown column 'x' in where clause`), an unknown column in a `create` or
`update` payload, an empty or missing `where` on `update` or `delete` — so
there is no way to accidentally rewrite the whole table — and an empty `data`
payload.

## Changing the schema

Reconciliation runs on every `defineSchema` call. Tables that do not exist are
created. Tables that do exist are compared against the declaration column by
column, and the outcome is one of three things.

**Applied automatically.** A newly declared column that SQLite can backfill onto
existing rows is added with `ALTER TABLE … ADD COLUMN`. Existing rows keep their
data and get the column's default, or `NULL` if it has none. This is the case
you want, and it is why adding a field to a shipped app does not lose the user's
records. New tables and new indexes are created the same way. (One wrinkle: a
column added to an existing table gets its type, default, and enum check, but
not a `notNull` constraint — SQLite cannot add one after the fact, so the
persisted table is slightly more permissive than a freshly created one.)

**Tolerated.** A column, table, or index that is still in the database but no
longer in your schema is left alone. Reads ignore it. Nothing is dropped, ever —
losing data is worse than carrying a dead column.

**Refused.** Three changes cannot be applied to a populated table, and
`defineSchema` throws with the table and column named:

- adding a column marked `primary` or `unique` — `ALTER ADD` cannot introduce
  either constraint;
- adding a `notNull` column with no default — existing rows would have no value
  to backfill;
- changing the `type` of an existing column — retyping in place would
  reinterpret the stored bytes.

:::caution[A refused change is a hard failure, not a warning]
The error is thrown before anything touches the database, and it stops the whole
`defineSchema` call — so the handle you were about to use never materializes and
the extension surfaces the fault instead of running against a half-migrated
table. The message names the table and every offending column. Fix the
declaration, or express the change as raw SQL in `migrations`.
:::

To make a refused change anyway, bump `version` and add the SQL yourself:

```ts
export const schema = {
  version: 2,
  tables: { /* … */ },
  migrations: [
    { version: 2, up: 'CREATE UNIQUE INDEX tasks_title_unique ON tasks(title)' },
  ],
} as const satisfies DataSchema;
```

Migrations are up-only; there is no down direction.

## Values across the process boundary

Queries from a page cross a JSON boundary, so the runtime converts values in
both directions using your schema as the shape oracle. You do not do this
yourself, but knowing it explains what you get back:

- `datetime` columns take a `Date` (or an ISO string) on write and come back as
  a `Date`.
- `boolean` columns are stored as `0`/`1` and come back as booleans.
- `json` columns are serialized on write and parsed on read; a value that fails
  to parse comes back as the raw string rather than throwing.

## Per user, per device, or shared

Typed data is scoped to the extension in one engine user-data directory. A
separate host profile has separate rows; multiple windows using the same
profile see the same rows. It does not leave the machine. Use your own backend
when records must be shared across devices or people.

## Permission

There is no permission for typed data, and none is checked. The API scopes the
store to your extension id, like the ungated key/value `storage` primitive.
Remote renderer reads still travel across the engine connection.

## A complete worked example

An app whose page lists tasks and whose main half checks for overdue ones at
startup. Three files plus the schema above.

```json check title="extension.json"
{
  "name": "Task Tracker",
  "version": "1.0.0",
  "description": "Typed task storage with a startup overdue check.",
  "main": "dist/main.js",
  "icon": "ListChecks",
  "permissions": ["notifications"],
  "activationEvents": ["onStartupFinished"],
  "contributes": {
    "pages": [
      { "id": "task-tracker", "title": "Tasks", "icon": "ListChecks", "presentation": "app" }
    ]
  }
}
```

```ts
// main/activate.ts

export async function activate(ctx: PluginContext): Promise<void> {
  const data = await ctx.api.data.defineSchema(schema);

  const { notifications } = ctx.api;
  if (!notifications) throw new Error('Task Tracker requires notifications');
  const overdue = await data.tasks.findMany({
    where: { status: q.ne('done'), dueAt: q.lt(new Date()) },
  });
  if (overdue.length > 0) {
    notifications.show({
      title: `${overdue.length} overdue task${overdue.length === 1 ? '' : 's'}`,
      body: overdue.slice(0, 3).map((t) => `• ${t.title}`).join('\n'),
    });
  }
}
```

```tsx
// ui/index.tsx

type Task = RowFor<typeof schema.tables.tasks>;

const data = pluginAPI.data.defineSchema(schema);

function TaskTrackerPage() {
  const tasks = useDataQuery(
    'tasks.list',
    () => data.tasks.findMany({ orderBy: { createdAt: 'desc' } }),
    [],
  );

  const add = async () => {
    await data.tasks.create({ data: { title: 'Untitled task' } });
    await tasks.refetch();
  };

  const cycle = async (task: Task) => {
    const next = task.status === 'todo' ? 'doing' : task.status === 'doing' ? 'done' : 'todo';
    await data.tasks.update({ where: { id: task.id }, data: { status: next } });
    await tasks.refetch();
  };

  if (tasks.loading) return <div className="p-4 text-sm text-muted-foreground">Loading…</div>;
  if (tasks.error) return <div className="p-4 text-sm text-destructive">{String(tasks.error)}</div>;

  return (
    <div className="h-full flex flex-col bg-background text-foreground">
      <header className="flex items-center justify-between border-b border-border p-3">
        <h1 className="text-sm font-semibold">Tasks</h1>
        <Button size="sm" onClick={add}>New task</Button>
      </header>
      {tasks.data?.length ? (
        <ul className="flex-1 divide-y divide-border overflow-y-auto">
          {tasks.data.map((task) => (
            <li key={task.id} className="flex items-center gap-3 px-4 py-3">
              <button onClick={() => cycle(task)} aria-label={`Status: ${task.status}`}>
                <Badge variant="outline" className="capitalize">{task.status}</Badge>
              </button>
              <span className="truncate">{task.title}</span>
            </li>
          ))}
        </ul>
      ) : (

      )}
    </div>
  );
}

export const views = { 'task-tracker': TaskTrackerPage };
export default TaskTrackerPage;
```

`RowFor<typeof schema.tables.tasks>` is how you name a row type in your own
code; with the schema declared `as const`, `task.status` is
`'todo' | 'doing' | 'done'` and not `string`.

`useDataQuery(queryId, thunk, deps)` from `@wamp/ui` runs the query, coalesces
identical in-flight calls, and gives you `{ data, loading, error, refetch }`.
Choose a stable `queryId` that uniquely names the returned data within your
extension, such as `tasks.list` or `tasks.by-id`; `deps` contain every value that
parameterizes that query. It re-runs when `deps` change, and it invalidates
itself when a write lands from another window. Call `refetch()` after your own
mutations; never poll on an interval.

## Next

- [Scheduled work](/build/scheduled-work/) — recurring work through an agent definition.
- [Contributing tools](/build/tools/) — let the AI read and write these rows.
- [Interface kit](/build/ui/) — the components and tokens the page uses.