---
title: "Tables, columns, and schema metadata"
description: "Define stored rows and the product behavior Sapporta derives from them."
canonical: "https://sapporta.com/docs/guides/model-data/tables-columns-and-schema-metadata/"
---

> Documentation index: https://sapporta.com/llms.txt

This page explains the two parts of a Sapporta table: the Drizzle table that
owns storage and constraints, and the metadata that shapes generated APIs,
forms, and grids. You will define a task table, choose semantic column types,
and learn where validation and value conversion belong. The same pattern works
for inventories, case trackers, approval queues, and other record-based
applications.

```text
Use the Sapporta skill to add a workspace-scoped tasks table. Give it useful labels, search, status and priority selects, and generated CRUD. Show me the migration before applying it.
```

## One definition, two responsibilities

The raw Drizzle table is the database contract. It defines SQL names,
nullability, defaults, keys, references, indexes, and enum values.
`sapportaTable()` joins that storage contract with labels, row scope, search
fields, column presentation, children, API write policy, and application
validation. Generated APIs, auth, runtime validation, metadata, forms, and grids
all start from the resulting `TableDef`.

Keep both exports in the same schema module. Other schema files import the raw
table when they need a foreign key. Sapporta registers the wrapped table.

```ts
import { index, integer, sqliteTable } from "drizzle-orm/sqlite-core";
import {
  date,
  sapportaTable,
  select,
  text,
  timestamp,
} from "@sapporta/server/table";
import { Temporal } from "@sapporta/shared/temporal";
import { projectsTable } from "./projects.js";

export const tasksTable = sqliteTable(
  "tasks",
  {
    id: integer("id").primaryKey({ autoIncrement: true }),
    workspace_id: text("workspace_id").notNull(),
    project_id: integer("project_id")
      .notNull()
      .references(() => projectsTable.id, { onDelete: "cascade" }),
    title: text("title").notNull(),
    description: text("description"),
    status: select("status", ["open", "in_progress", "completed"] as const)
      .notNull()
      .default("open"),
    priority: select("priority", ["low", "medium", "high"] as const)
      .notNull()
      .default("medium"),
    due_date: date("due_date"),
    created_at: timestamp("created_at")
      .$defaultFn(() => Temporal.Now.instant())
      .notNull(),
    updated_at: timestamp("updated_at")
      .$defaultFn(() => Temporal.Now.instant())
      .notNull(),
  },
  (table) => [
    index("tasks_project_status_due_date_idx").on(
      table.project_id,
      table.status,
      table.due_date,
    ),
  ],
);

export const tasks = sapportaTable({
  drizzle: tasksTable,
  meta: {
    label: "Tasks",
    rowScope: "workspaceGlobal",
    rowLabelColumns: ["title"],
    search: { columns: ["title", "description"] },
    columns: {
      project_id: { label: "Project" },
      description: { textDisplay: "multiLine" },
    },
  },
});

export type Task = typeof tasksTable.$inferSelect;
export type NewTask = typeof tasksTable.$inferInsert;

export default tasks;
```

Sapporta semantic factories attach storage and value semantics in one column
declaration. `select()` also stores its option tuple on the Drizzle text column.
That tuple drives TypeScript inference, structural Zod validation, OpenAPI,
metadata, searchable choice controls, and enum filters. Primary and foreign keys
remain raw Drizzle integers in this example. Schema extraction derives a
semantic kind for those raw columns, so every browser `ColumnSchema` still has a
required `kind`. Derive TypeScript row types with `$inferSelect` and
`$inferInsert`; a separate handwritten interface can drift from the database
schema.

`workspace_id` is required by `workspaceGlobal`, but it is a server-managed
value. Generated clients and forms must not submit it. A `workspaceUserScoped`
table also needs `scoped_to_user_id`; a `systemGlobal` table needs neither
workspace column.

## Values and domain validation

Generated forms, table routes, Drizzle columns, and Grid editors derive their
value rules from the same `TableDef`. Select-backed text remains a string.
Numbers and booleans remain JSON primitives. Dates and timestamps use canonical
strings at generated write boundaries. Direct Drizzle application code uses
Temporal values, and database reads return Temporal values.

Semantic kinds provide structural constraints. Add the top-level `validate()`
callback when generated CRUD needs a cross-field or domain rule. The callback
receives the already-parsed prepared insert or submitted patch and adds issues;
it cannot replace structural validation or transform the value written to
Drizzle. Public payloads, validation fields, metadata, and row objects use SQL
column names even when the Drizzle property name differs.

Start the app, then inspect the registered definition:

```bash
pnpm dev
pnpm exec sapporta tables show tasks
```

The output should identify `tasks`, `workspaceGlobal`, the `title` row label,
both search columns, and the select options derived from the status and priority
columns.

<!--
Screenshot brief
Suggested asset: tables-columns-task-metadata-terminal.png
Setup: Run the task app with the projects and tasks schemas migrated, then run `pnpm exec sapporta tables show tasks` in a wide terminal.
Frame: Capture the command and the complete task table summary; crop unrelated shell history.
Visible proof: The table name, workspaceGlobal row scope, title row label, title/description search fields, and status/priority select metadata are readable.
Alt text: Terminal output describing the registered tasks table and its generated metadata.
-->

The task definition now owns storage, generated behavior, and shared value
semantics in one reviewable module. Metadata changes can alter the generated
experience without duplicating the database model. From here, add relationships
and child collections, tune search and indexes, then generate a reviewed
migration for every storage change.

## Related reference

- [Table definitions](/docs/reference/schema/table-definitions.md)
- [Table and column metadata](/docs/reference/schema/table-and-column-metadata.md)
- [Table validation](/docs/reference/schema/table-validation.md)
- [Semantic value boundaries](/docs/reference/schema/semantic-value-boundaries.md)