Search, indexes, and display metadata

This page covers three related choices: which values people can search, how generated surfaces present those values, and which database paths need indexes. You will make the task list easier to scan and keep its common project/status/due-date query efficient. The same approach applies to customer directories, ticket queues, and operational worklists.

Tune my tasks table for real use. Add focused search, readable status and priority fields, and indexes for the queries the generated screens run.

Start with the query people make

Search should cover a small set of human-entered text. IDs, booleans, timestamps, measures, and every text field do not become more useful merely because the toolbar can search them. In the task app, title and description match what a person remembers:

import { sqliteTable } from "drizzle-orm/sqlite-core";
import { sapportaTable, select } from "@sapporta/server/table";

const TASK_STATUSES = ["open", "in_progress", "completed"] as const;
const TASK_PRIORITIES = ["low", "medium", "high"] as const;

export const tasksTable = sqliteTable("tasks", {
  // Other columns omitted for focus.
  status: select("status", TASK_STATUSES).notNull().default("open"),
  priority: select("priority", TASK_PRIORITIES).notNull().default("medium"),
});

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" },
    },
  },
});

rowLabelColumns supplies a short, stable label for record links and lookups. The select() factories declare the controlled strings once on their Drizzle columns. The same options feed structural validation and the generated searchable comboboxes used by forms, inline Grid editing, and enum filters. Column metadata changes labels and presentation; it does not change row authorization.

The database index should follow a demonstrated query path. The task list and project child surface commonly filter by project and status, then sort by due date:

export const tasksTable = sqliteTable(
  "tasks",
  {
    // Columns omitted for focus.
  },
  (table) => [
    index("tasks_project_status_due_date_idx").on(
      table.project_id,
      table.status,
      table.due_date,
    ),
  ],
);

That composite index is not a generic speed switch. Its column order serves this specific filter-and-sort shape. Add or revise indexes after inspecting actual application queries and query plans.

See the metadata in the generated list

Run the app, open /tables/tasks, and create several rows across two projects. Give them different statuses, priorities, descriptions, and due dates. Search for a word that appears only in a title or description, then filter to one project and status.

The task surface is now searchable by meaningful text, readable without raw identifiers, and indexed for its common relationship query. Search and display metadata shape the generated experience; Drizzle indexes shape database execution; neither replaces server-side row scope. Next, carry these schema changes into a reviewed migration.

Related reference

• Table and column metadata
• Query syntax