Skip to content
Sapporta

Relationships and lookup behavior

View as Markdown

A relationship has three distinct meanings: the foreign key preserves stored integrity, rowLabelColumns names the referenced row, and children exposes the reverse path on a parent record.

Define the forward relationship

Section titled “Define the forward relationship”

The database relationship starts with a Drizzle foreign key. Import the target’s raw table and reference its primary key:

import { integer, sqliteTable } from "drizzle-orm/sqlite-core";
import { projectsTable } from "./projects.js";


export const tasksTable = sqliteTable("tasks", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  project_id: integer("project_id")
    .notNull()
    .references(() => projectsTable.id, { onDelete: "cascade" }),
  // Other task columns...
});

The foreign key lets generated forms treat project_id as a lookup. The target table still needs a human label:

export const projects = sapportaTable({
  drizzle: projectsTable,
  meta: {
    label: "Projects",
    rowScope: "workspaceGlobal",
    rowLabelColumns: ["name"],
    children: [
      {
        table: "tasks",
        foreignKey: "project_id",
        label: "Tasks",
        columns: ["title", "status", "due_date"],
        defaultSort: "due_date",
      },
    ],
    search: {
      children: {
        tasks: "allColumns",
      },
    },
  },
});

rowLabelColumns: ["name"] makes lookup results display the project name while preserving the project primary key as the value. Keep that key type through pickers, caches, filters, and API calls: numeric keys remain numbers and text keys remain strings. The lookup route returns entries, not an ID-to-label map:

{
  "entries": [
    {
      "value": 1,
      "label": "Website Relaunch",
      "meta": { "id": 1, "name": "Website Relaunch" }
    }
  ]
}

meta contains ordinary visible fields from the source row. Consumers may use those fields for presentation, but they should not assume the object is empty or that it has one universal shape.

children describes the reverse path. Expanding a project row in /tables/projects filters tasks by project_id, shows the chosen columns, and applies the stable due-date sort. This is a table-row expansion and child collection, not a generated /tables/projects/:id detail route. Declare child collections only when the reverse path is part of the record workflow. A join table may appear under both parents. A self-reference usually needs a purpose-built hierarchy.

The search.children.tasks entry lets a visible task make its project appear in the parent result. Search configuration is separate from the child grid’s display columns, and it can continue through further declared children when the domain needs it. Child matching uses the Tasks read ability and row scope; it cannot make an inaccessible task reveal its project.

Expanding the matching project runs the child grid’s own query and shows all visible tasks. The parent term is not inherited as a hidden child filter. Use an explicit task-table link or an application result when the workflow needs to show only the matching children.

Exercise both directions

Section titled “Exercise both directions”

Create the parent before the child. Run the app and create a project named Website Relaunch at /tables/projects/new. If you use the API or CLI, capture the returned project key instead of assuming a fresh database assigned 1. Create a task at /tables/tasks/new and choose that project from the lookup. Return to /tables/projects and expand the project row to find the task under Tasks.

Lookup options, child rows, and child-assisted search all use the active read ability and row scope. Relationship metadata never widens access.

Section titled “Related reference”