Add Dictionary (Lookup Field)

TL;DR: Create a dictionary type in Settings, reference it from your entity with a FK, use useDictionaryValuesByCode to load options.

When to Use Dictionaries

Dictionaries are for user-configurable lookup values — things your users might want to rename, reorder, or add new options to without touching code:

Statuses — Lead, Active, Inactive (with colors)
Industries — Technology, Finance, Healthcare
Regions — North, South, East, West
Priority levels — Low, Medium, High, Critical

Don't use dictionaries for: fixed enums that never change (like role: "admin" | "member"). Use a plain text column for those.

How It Works

The dictionary system has two tables:

Table	Purpose
`dictionary_types`	Categories of values (e.g. "Client Statuses", "Industries")
`dictionary_values`	The actual options within a type (e.g. "Active", "Lead")

Each value can have:

Color — for status badges and filter dots ("green", "red", "amber", "blue", "gray")
Translations — per-locale labels in a labels JSON column
Sort order — custom display order
Active/inactive — soft-delete without losing data

Users manage dictionaries in Settings → Dictionaries.

The Checklist

#	File	What to do
1	Seed script	Create dictionary type + values
2	Entity schema	Add FK column referencing `dictionary_values`
3	Queries	Join dictionary value in list/detail queries
4	UI	Use `useDictionaryValuesByCode` for selects and filters

Step 1: Define Dictionary Code

Add a constant for your dictionary type code:

// src/features/tasks/types.ts
export const DICTIONARY_CODES = {
  TASK_STATUSES: "task_statuses",
  TASK_PRIORITIES: "task_priorities",
} as const

Step 2: Seed Dictionary Data

Add initial values in the seed script so the dictionary exists when the app starts:

// src/scripts/seed.ts

// Create the dictionary type
const statusTypeId = await ensureDictionaryType(
  "task_statuses",        // code (unique per organization)
  "Task Statuses",        // display name
  "Status values for tasks"  // description
)

// Create values with colors and translations
await ensureDictionaryValue(statusTypeId, "open",        "Open",        "blue",   1, { pl: "Otwarte" })
await ensureDictionaryValue(statusTypeId, "in_progress", "In Progress", "amber",  2, { pl: "W toku" })
await ensureDictionaryValue(statusTypeId, "completed",   "Completed",   "green",  3, { pl: "Zakończone" })
await ensureDictionaryValue(statusTypeId, "cancelled",   "Cancelled",   "gray",   4, { pl: "Anulowane" })

Then run:

npx tsx src/scripts/seed.ts

After seeding, users can edit these values in Settings → Dictionaries without touching code.

Step 3: Add FK to Entity Schema

// src/lib/db/schema/tasks.ts
import { dictionaryValues } from "./dictionaries"

export const tasks = pgTable("tasks", {
  // ... other columns

  // Optional dictionary field — null if value deleted
  priorityId: uuid("priority_id")
    .references(() => dictionaryValues.id, { onDelete: "set null" }),

  // Required dictionary field — prevents deleting used values
  statusId: uuid("status_id")
    .notNull()
    .references(() => dictionaryValues.id, { onDelete: "restrict" }),
})

Choosing onDelete:

Strategy	When	Example
`"set null"`	Field is optional, OK to clear if value deleted	Industry, Region
`"restrict"`	Field is required, prevent deleting values in use	Status

If you have multiple dictionary FKs on one entity, add relationName to disambiguate:

export const tasksRelations = relations(tasks, ({ one }) => ({
  status: one(dictionaryValues, {
    fields: [tasks.statusId],
    references: [dictionaryValues.id],
    relationName: "taskStatus",
  }),
  priority: one(dictionaryValues, {
    fields: [tasks.priorityId],
    references: [dictionaryValues.id],
    relationName: "taskPriority",
  }),
}))

Push schema:

npm run db:push

Step 4: Join in Queries

When you have multiple dictionary FKs, use alias() to join the same table twice:

// src/features/tasks/queries.ts
import { alias } from "drizzle-orm/pg-core"
import { dictionaryValues } from "@/lib/db/schema"

const statusDictionary = alias(dictionaryValues, "status_dict")
const priorityDictionary = alias(dictionaryValues, "priority_dict")

const rows = await db
  .select({
    task: tasks,
    status: statusDictionary,
    priority: priorityDictionary,
  })
  .from(tasks)
  .leftJoin(statusDictionary, eq(tasks.statusId, statusDictionary.id))
  .leftJoin(priorityDictionary, eq(tasks.priorityId, priorityDictionary.id))
  .where(conditions)

Map to your type:

function mapToTaskListItem(row: { task: ..., status: ..., priority: ... }): TaskListItem {
  return {
    id: row.task.id,
    name: row.task.name,
    status: row.status ? {
      id: row.status.id,
      label: row.status.label,
      labels: row.status.labels,
      color: row.status.color,
    } : null,
    priority: row.priority ? {
      id: row.priority.id,
      label: row.priority.label,
      labels: row.priority.labels,
      color: row.priority.color,
    } : null,
  }
}

Step 5: Use in UI

Select (forms and inline edit)

import { useDictionaryValuesByCode, resolveDictionaryLabel } from "@/features/dictionaries"
import { EditableSelect } from "@/components/inline-edit"
import { useLocale } from "next-intl"

function TaskStatusSelect({ value, onSave }: { value: string | null, onSave: (id: string) => void }) {
  const locale = useLocale()
  const { data: statusValues } = useDictionaryValuesByCode(DICTIONARY_CODES.TASK_STATUSES)

  const options = useMemo(
    () => (statusValues ?? []).map((v) => ({
      value: v.id,
      label: resolveDictionaryLabel(v.label, v.labels, locale),
      color: v.color,
    })),
    [statusValues, locale]
  )

  return (
    <EditableSelect
      value={value}
      options={options}
      onSave={onSave}
    />
  )
}

Faceted filter

const { data: statusValues } = useDictionaryValuesByCode(DICTIONARY_CODES.TASK_STATUSES)

const statusOptions = useMemo(
  () => (statusValues ?? []).map((v) => ({
    value: v.id,
    label: resolveDictionaryLabel(v.label, v.labels, locale),
    color: v.color,  // Shows colored dot in filter dropdown
  })),
  [statusValues, locale]
)

<DataTableFacetedFilter
  title={t("columns.status")}
  options={statusOptions}
  selectedValues={new Set(filters.statusId)}
  onSelectionChange={(values) => onFilterChange("statusId", values)}
/>

Status badge in table column

{
  accessorKey: "status",
  header: ({ column }) => <DataTableColumnHeader column={column} title={t("columns.status")} />,
  cell: ({ row }) => {
    const status = row.original.status
    if (!status) return null
    return (
      <StatusBadge color={status.color}>
        {resolveDictionaryLabel(status.label, status.labels, locale)}
      </StatusBadge>
    )
  },
}

Translations (i18n)

Dictionary values support per-locale labels via the labels JSON column:

{
  "label": "Active",
  "labels": { "pl": "Aktywny", "de": "Aktiv" }
}

Always resolve the label using resolveDictionaryLabel():

import { resolveDictionaryLabel } from "@/features/dictionaries"

// Returns "Aktywny" if locale is "pl", otherwise falls back to "Active"
const displayLabel = resolveDictionaryLabel(value.label, value.labels, locale)

Users can edit translations in Settings → Dictionaries for each value.

Common Mistakes

Don't hardcode dictionary value IDs:

// ❌ Wrong — UUIDs are different per environment
if (task.statusId === "550e8400-e29b-41d4-a716-446655440000") { ... }

// ✅ Correct — use the code field
if (task.status?.code === "completed") { ... }

Don't use raw label — resolve for locale:

// ❌ Wrong — always shows English
<span>{status.label}</span>

// ✅ Correct — shows translation if available
<span>{resolveDictionaryLabel(status.label, status.labels, locale)}</span>

Don't create a new table for simple lookups:

// ❌ Wrong — a whole table just for 5 status options
export const taskStatuses = pgTable("task_statuses", { ... })

// ✅ Correct — use the dictionary system
const { data } = useDictionaryValuesByCode("task_statuses")

Verify

npm run typecheck

Add Field to Entity — Add the FK column
Add Filter — Use dictionary as filter options
Add New Feature — Create the entity first

Add Dictionary (Lookup Field)

On this page