Custom Fields (Developer)

CREATE TABLE columns (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  board_id    UUID NOT NULL REFERENCES boards(id),
  org_id      UUID NOT NULL REFERENCES organizations(id),
  name        TEXT NOT NULL,
  type        TEXT NOT NULL,       -- 'text', 'number', 'status', etc.
  config      JSONB DEFAULT '{}',  -- type-specific configuration
  position    INTEGER NOT NULL,
  width       INTEGER DEFAULT 150,
  required    BOOLEAN DEFAULT FALSE,
  default_val JSONB,               -- default value for new items
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  updated_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE values (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  item_id      UUID NOT NULL REFERENCES items(id),
  column_id    UUID NOT NULL REFERENCES columns(id),
  org_id       UUID NOT NULL REFERENCES organizations(id),
  text_value   TEXT,
  number_value DOUBLE PRECISION,
  date_value   TIMESTAMPTZ,
  json_value   JSONB,
  created_at   TIMESTAMPTZ DEFAULT NOW(),
  updated_at   TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (item_id, column_id)
);

-- Indexes for efficient queries
CREATE INDEX idx_values_item ON values(item_id);
CREATE INDEX idx_values_column ON values(column_id);
CREATE INDEX idx_values_text ON values(column_id, text_value) WHERE text_value IS NOT NULL;
CREATE INDEX idx_values_number ON values(column_id, number_value) WHERE number_value IS NOT NULL;
CREATE INDEX idx_values_date ON values(column_id, date_value) WHERE date_value IS NOT NULL;

// packages/api/src/columns/registry.ts
import { z } from "zod";

export const columnTypes = {
  text: {
    storageField: "text_value",
    validator: z.string().max(10000),
    configSchema: z.object({
      maxLength: z.number().optional(),
      placeholder: z.string().optional(),
    }),
  },

  number: {
    storageField: "number_value",
    validator: z.number(),
    configSchema: z.object({
      decimals: z.number().min(0).max(10).default(0),
      prefix: z.string().optional(),
      suffix: z.string().optional(),
    }),
  },

  status: {
    storageField: "json_value",
    validator: z.object({
      label: z.string(),
      color: z.string(),
    }),
    configSchema: z.object({
      labels: z.array(z.object({
        id: z.string(),
        name: z.string(),
        color: z.string(),
        isDone: z.boolean().default(false),
      })),
      defaultLabelId: z.string().optional(),
    }),
  },

  date: {
    storageField: "date_value",
    validator: z.coerce.date(),
    configSchema: z.object({
      includeTime: z.boolean().default(false),
      format: z.enum(["relative", "absolute"]).default("relative"),
    }),
  },

  people: {
    storageField: "json_value",
    validator: z.array(z.string().uuid()),
    configSchema: z.object({
      multiple: z.boolean().default(true),
      limit: z.number().optional(),
    }),
  },

  formula: {
    storageField: "number_value", // or text_value depending on result type
    validator: z.never(), // formulas are computed, not set directly
    configSchema: z.object({
      expression: z.string(),
      resultType: z.enum(["number", "text", "date"]),
    }),
  },
  // ... 20+ more types
} satisfies Record<string, ColumnTypeDefinition>;

// packages/api/src/routers/values.ts
export const valueRouter = createTRPCRouter({
  set: protectedProcedure
    .input(z.object({
      itemId: z.string().uuid(),
      columnId: z.string().uuid(),
      value: z.unknown(),
    }))
    .mutation(async ({ ctx, input }) => {
      // 1. Load the column definition
      const column = await ctx.db.query.columns.findFirst({
        where: and(
          eq(columns.id, input.columnId),
          eq(columns.orgId, ctx.tenant.id),
        ),
      });

      // 2. Get the type definition
      const typeDef = columnTypes[column.type];

      // 3. Validate the value
      const validated = typeDef.validator.parse(input.value);

      // 4. Write to the correct storage column
      await ctx.db.insert(values).values({
        itemId: input.itemId,
        columnId: input.columnId,
        orgId: ctx.tenant.id,
        [typeDef.storageField]: validated,
      }).onConflictDoUpdate({
        target: [values.itemId, values.columnId],
        set: { [typeDef.storageField]: validated, updatedAt: new Date() },
      });

      // 5. Broadcast real-time update
      await broadcastUpdate(ctx, "value.set", {
        itemId: input.itemId,
        columnId: input.columnId,
        value: validated,
      });
    }),
});

// apps/web/src/components/columns/cell-registry.tsx
export const cellRenderers: Record<string, React.FC<CellProps>> = {
  text: TextCell,
  number: NumberCell,
  status: StatusCell,
  date: DateCell,
  people: PeopleCell,
  rating: RatingCell,
  checkbox: CheckboxCell,
  progress: ProgressCell,
  formula: FormulaCell,
  // ...
};

export const cellEditors: Record<string, React.FC<EditorProps>> = {
  text: TextEditor,
  number: NumberEditor,
  status: StatusDropdown,
  date: DatePicker,
  people: PeoplePicker,
  // ...
};