Skip to content
Sapporta

Tables, columns, and schema metadata

View as Markdown

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.

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

Section titled “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.

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

Section titled “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:

Terminal window
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.

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.

Section titled “Related reference”