Column Properties Panel — FE Integration Guide

Complete guide for building the column properties panel, including widget selection, advanced settings, and migration prediction.

Concepts

A column has three layers of configuration:

┌─────────────────────────────────────────────────┐
│  1. Widget Type                                 │  STRING, IMAGE, DATETIME, etc.
│     What UI component renders the cell          │  Metadata-only (ViewColumn)
├─────────────────────────────────────────────────┤
│  2. Widget Options                              │  imageMode, autoSet, etc.
│     Per-widget behavioral settings              │  Metadata-only* (ViewColumn.widgetOptionsJson)
├─────────────────────────────────────────────────┤
│  3. Advanced / DB Settings                      │  type, length, nullable, unique, etc.
│     Actual database column properties           │  May require migration (ColumnMetadata)
└─────────────────────────────────────────────────┘

* Exception: DATETIME's `autoSet` widget option DOES trigger a migration (creates DB triggers/defaults)

Key principle: Widget type and widget options describe how data is displayed and interpreted. Advanced settings describe how data is stored in the database. Only the storage layer triggers migrations.

Layer 1: Widget Type

What the backend provides (per column, dynamic)

{
  "allowedMutations": {
    "allowedWidgetTypes": ["STRING", "TEXT", "EMAIL", "URL", "PHONE", "SELECT", "UUID", "FILE", "IMAGE"],
    "currentDbType": "VARCHAR",
    "currentLength": 255,
    "currentPrecision": null,
    "currentScale": null
  }
}

• allowedWidgetTypes — which widgets are compatible with the column's current DB type. Only show these in the widget selector.
• currentDbType / currentLength / currentPrecision / currentScale — the column's current DB state, used for migration prediction.

What the FE hardcodes (static, same for all columns)

Widget Defaults

Each widget type has a default DB type and a list of DB types it can work with:

interface WidgetDefault {
  dbType: string;           // Default DB type for this widget
  allowedDbTypes: string[]; // DB types the user can choose in advanced settings
  length?: number;          // Default length (VARCHAR-family)
  precision?: number;       // Default precision (NUMERIC-family)
  scale?: number;           // Default scale (NUMERIC-family)
}

const WIDGET_DEFAULTS: Record<string, WidgetDefault> = {
  STRING:       { dbType: "VARCHAR",   allowedDbTypes: ["VARCHAR", "TEXT"],                     length: 255  },
  TEXT:         { dbType: "TEXT",       allowedDbTypes: ["VARCHAR", "TEXT"]                                   },
  EMAIL:        { dbType: "VARCHAR",   allowedDbTypes: ["VARCHAR", "TEXT"],                     length: 255  },
  URL:          { dbType: "VARCHAR",   allowedDbTypes: ["VARCHAR", "TEXT"],                     length: 2048 },
  PHONE:        { dbType: "VARCHAR",   allowedDbTypes: ["VARCHAR", "TEXT"],                     length: 50   },
  NUMBER:       { dbType: "NUMERIC",   allowedDbTypes: ["NUMERIC", "DECIMAL"],   precision: 19, scale: 2    },
  INTEGER:      { dbType: "INTEGER",   allowedDbTypes: ["INTEGER", "BIGINT", "SMALLINT"], precision: 10     },
  DECIMAL:      { dbType: "NUMERIC",   allowedDbTypes: ["NUMERIC", "DECIMAL"],   precision: 19, scale: 2    },
  BOOLEAN:      { dbType: "BOOLEAN",   allowedDbTypes: ["BOOLEAN"]                                          },
  DATE:         { dbType: "DATE",      allowedDbTypes: ["DATE"]                                              },
  DATETIME:     { dbType: "TIMESTAMP", allowedDbTypes: ["TIMESTAMP"]                                         },
  SELECT:       { dbType: "VARCHAR",   allowedDbTypes: ["VARCHAR", "TEXT"],                     length: 255  },
  MULTI_SELECT: { dbType: "TEXT",      allowedDbTypes: ["VARCHAR", "TEXT"]                                    },
  FILE:         { dbType: "TEXT",      allowedDbTypes: ["VARCHAR", "TEXT"]                                    },
  IMAGE:        { dbType: "TEXT",      allowedDbTypes: ["VARCHAR", "TEXT"]                                    },
  UUID:         { dbType: "VARCHAR",   allowedDbTypes: ["VARCHAR", "UUID"],                     length: 36   },
};

allowedDbTypes is the clean list shown in the advanced settings dropdown — what the user can choose. It does not need to list every MySQL/PostgreSQL variant because the compatibility groups (below) handle that.

DB Type Aliases (true aliases only)

These are the same type with different names — no migration needed. Any actual type change (even VARCHAR → TEXT) requires a migration.

const DB_TYPE_ALIASES: string[][] = [
  // VARCHAR aliases
  ["VARCHAR", "CHARACTER VARYING"],
  // CHAR aliases
  ["CHAR", "CHARACTER"],
  // INTEGER aliases (same storage size)
  ["INTEGER", "INT", "INT4"],
  // BIGINT aliases
  ["BIGINT", "INT8"],
  // SMALLINT aliases
  ["SMALLINT", "INT2"],
  // NUMERIC aliases
  ["NUMERIC", "DECIMAL"],
  // DOUBLE PRECISION aliases
  ["DOUBLE PRECISION", "FLOAT8", "DOUBLE"],
  // REAL aliases
  ["REAL", "FLOAT4"],
  // TIMESTAMP aliases
  ["TIMESTAMP", "TIMESTAMP WITHOUT TIME ZONE"],
  // TIMESTAMPTZ aliases
  ["TIMESTAMP WITH TIME ZONE", "TIMESTAMPTZ"],
  // TIME aliases
  ["TIME", "TIME WITHOUT TIME ZONE"],
  // TIMETZ aliases
  ["TIME WITH TIME ZONE", "TIMETZ"],
  // BOOLEAN aliases
  ["BOOLEAN", "BOOL"],
  // UUID aliases
  ["UUID", "UNIQUEIDENTIFIER"],
];

Important: VARCHAR and TEXT are NOT aliases. They are different types with different behavior (especially on MySQL). Changing VARCHAR → TEXT or vice versa always requires a migration.

Compatible DB Type Families (for widget matching)

Broader than aliases — these group types that a given widget can work with, even though changing between them requires a migration. Used by isDbTypeCompatibleWithWidget() below.

const COMPATIBLE_DB_TYPES: string[][] = [
  // Text family — all store text, but have different size limits
  ["VARCHAR", "CHARACTER VARYING", "CHAR", "CHARACTER",
   "TEXT", "TINYTEXT", "MEDIUMTEXT", "LONGTEXT"],
  // Integer family
  ["INTEGER", "INT", "INT2", "INT4", "INT8",
   "BIGINT", "SMALLINT", "TINYINT", "MEDIUMINT",
   "SERIAL", "BIGSERIAL", "SMALLSERIAL"],
  // Decimal family
  ["NUMERIC", "DECIMAL", "REAL", "DOUBLE PRECISION",
   "FLOAT", "DOUBLE", "FLOAT4", "FLOAT8"],
  // Boolean
  ["BOOLEAN", "BOOL"],
  // Date
  ["DATE"],
  // Timestamp family
  ["TIMESTAMP", "TIMESTAMP WITHOUT TIME ZONE",
   "TIMESTAMP WITH TIME ZONE", "TIMESTAMPTZ", "DATETIME"],
  // Time family
  ["TIME", "TIME WITHOUT TIME ZONE",
   "TIME WITH TIME ZONE", "TIMETZ"],
  // UUID
  ["UUID", "UNIQUEIDENTIFIER"],
  // JSON family
  ["JSON", "JSONB"],
  // Binary family
  ["BYTEA", "BLOB", "TINYBLOB", "MEDIUMBLOB",
   "LONGBLOB", "BINARY", "VARBINARY", "BIT"],
  // MySQL ENUM/SET
  ["ENUM", "SET"],
];

Two different lookups:

• DB_TYPE_ALIASES → "Does changing between these types need a migration?" (No, they're the same type)
• COMPATIBLE_DB_TYPES → "Can this column type use this widget?" (Yes, even if changing type later needs a migration)

How to check compatibility

The allowedDbTypes list on a widget is clean/short (e.g., ["VARCHAR", "TEXT"]). The user's actual column might be MEDIUMTEXT (MySQL) which isn't in that list, but IS in the same compatibility group as TEXT. So the check is:

function isDbTypeCompatibleWithWidget(currentDbType: string, widget: WidgetDefault): boolean {
  // Direct match
  if (widget.allowedDbTypes.includes(currentDbType)) return true;

  // Check via compatibility groups: is currentDbType in the same group
  // as any entry in widget.allowedDbTypes?
  for (const group of COMPATIBLE_DB_TYPES) {
    if (group.includes(currentDbType)) {
      for (const allowed of widget.allowedDbTypes) {
        if (group.includes(allowed)) return true;
      }
    }
  }
  return false;
}

Examples:

Current DB Type	Widget	allowedDbTypes	Match via	Compatible?
VARCHAR	IMAGE	["VARCHAR", "TEXT"]	Direct	Yes
MEDIUMTEXT	IMAGE	["VARCHAR", "TEXT"]	Text group	Yes
LONGTEXT	STRING	["VARCHAR", "TEXT"]	Text group	Yes
TINYINT	NUMBER	["NUMERIC", "DECIMAL"]	—	No
TINYINT	INTEGER	["INTEGER", "BIGINT"…]	Integer group	Yes
DOUBLE PRECISION	DECIMAL	["NUMERIC", "DECIMAL"]	Decimal group	Yes
TIMESTAMPTZ	DATETIME	["TIMESTAMP"]	Timestamp group	Yes
INTEGER	STRING	["VARCHAR", "TEXT"]	—	No

Layer 2: Widget Options

Widget options are per-widget behavioral settings stored as JSON metadata. They do not affect the database column type (with one exception).

IMAGE widget options

{ "imageMode": "managed" }   // default — cell stores FileReference JSON, thumbnails pre-generated
{ "imageMode": "url" }       // cell stores a plain URL, thumbnails generated lazily on first view

Both modes use the same DB type (TEXT or VARCHAR). The imageMode only changes how the backend interprets the cell value when building the response. No migration, no DB type change.

DATETIME widget options

{ "autoSet": "none" }     // default — no auto-generation
{ "autoSet": "insert" }   // set CURRENT_TIMESTAMP on insert
{ "autoSet": "update" }   // set CURRENT_TIMESTAMP on update (via trigger)
{ "autoSet": "both" }     // both insert default + update trigger

Exception: autoSet is the only widget option that triggers a migration, because it creates/drops database triggers and defaults.

Other widget options

All other widget options (display formatting, placeholder text, etc.) are metadata-only and never trigger migrations.

Layer 3: Advanced / DB Settings

These map to advancedOptions in the PATCH request and directly affect the database column.

Available settings per DB type family

Setting	Applies to	Example
type	All	Override DB type
length	Text family only	VARCHAR(2048)
precision	Decimal family only	NUMERIC(19, ...)
scale	Decimal family only	NUMERIC(..., 2)
nullable	All	NOT NULL constraint
unique	All	UNIQUE index
defaultValue	All	DEFAULT 'hello'

DB type override

The user can override the DB type. The allowedDbTypes in WIDGET_DEFAULTS are suggested options for the dropdown, not a hard constraint. The backend accepts any valid database type.

The FE should:

1. Show a dropdown populated from WIDGET_DEFAULTS[widgetType].allowedDbTypes as common options
2. Allow freeform/custom type entry (e.g., INTERVAL, INET, CIDR, POINT) — the backend accepts any type and will generate the appropriate migration
3. When the user picks a type, pre-fill the default length/precision/scale from WIDGET_DEFAULTS (if available)
4. Compare against current column state for migration prediction

This is important for users who import databases with exotic types (PostgreSQL INTERVAL, INET, etc.) — they should be able to keep those types while using STRING or TEXT as the display widget.

What the FE sends (PATCH body examples)

// Widget type change only — may or may not need migration
{ "widgetType": "IMAGE" }

// Widget type + widget options (imageMode is metadata-only, no migration)
{ "widgetType": "IMAGE", "widgetOptions": { "imageMode": "url" } }

// Widget type + DB type override — likely needs migration
{ "widgetType": "IMAGE", "advancedOptions": { "type": "VARCHAR", "length": 2048 } }

// Advanced settings only (nullable change) — needs migration
{ "advancedOptions": { "nullable": false } }

// DATETIME autoSet change — needs migration (exception: this IS a widget option that triggers migration)
{ "widgetOptions": { "autoSet": "insert" } }

Migration Prediction

Individual rules

Check each rule. If any is true → migration warning.

Rule 1: DB Type Change

Compare the target DB type against currentDbType using the compatibility groups:

function needsTypeMigration(currentDbType: string, targetDbType: string): boolean {
  if (currentDbType === targetDbType) return false;
  for (const group of COMPATIBLE_DB_TYPES) {
    if (group.includes(currentDbType) && group.includes(targetDbType)) return false;
  }
  return true;
}

The targetDbType is either:

• The user's explicit override from advanced settings (advancedOptions.type), OR
• The widget's default WIDGET_DEFAULTS[widgetType].dbType if no override

Rule 2: Length Change

const VARCHAR_TYPES = ["VARCHAR", "CHARACTER VARYING", "CHAR", "CHARACTER"];
const DECIMAL_FAMILY = ["NUMERIC", "DECIMAL", "REAL", "DOUBLE PRECISION", "FLOAT", "DOUBLE", "FLOAT4", "FLOAT8"];

function needsLengthMigration(currentDbType: string, currentLength: number | null, newLength: number | null): boolean {
  // Only VARCHAR-family types enforce length; TEXT/MEDIUMTEXT/etc. ignore it
  if (!VARCHAR_TYPES.includes(currentDbType.toUpperCase())) return false;
  return currentLength !== newLength;
}

Rule 3: Precision / Scale Change

function needsPrecisionMigration(
  currentDbType: string,
  current: { precision: number | null, scale: number | null },
  target:  { precision: number | null, scale: number | null }
): boolean {
  if (!DECIMAL_FAMILY.includes(currentDbType.toUpperCase())) return false;
  return current.precision !== target.precision || current.scale !== target.scale;
}

Rule 4: Nullable Change

currentNullable !== newNullable

Rule 5: Unique Constraint Change

currentUnique !== newUnique

Rule 6: Default Value Change

currentDefault !== newDefault  // including null → value or value → null

Rule 7: AutoSet Change (DATETIME only)

currentAutoSet !== newAutoSet

Combined prediction

function willRequireMigration(current: ColumnState, changes: ColumnChanges): boolean {
  const targetWidget = changes.widgetType ?? current.widgetType;
  const widgetDef = WIDGET_DEFAULTS[targetWidget];
  const targetDbType = changes.advancedOptions?.type ?? widgetDef.dbType;
  const targetLength = changes.advancedOptions?.length ?? widgetDef.length ?? current.length;

  return (
    needsTypeMigration(current.dbType, targetDbType) ||
    needsLengthMigration(current.dbType, current.length, targetLength) ||
    needsPrecisionMigration(current.dbType,
      { precision: current.precision, scale: current.scale },
      { precision: changes.advancedOptions?.precision ?? current.precision,
        scale: changes.advancedOptions?.scale ?? current.scale }) ||
    (changes.advancedOptions?.nullable != null && changes.advancedOptions.nullable !== current.nullable) ||
    (changes.advancedOptions?.unique != null && changes.advancedOptions.unique !== current.unique) ||
    (changes.advancedOptions?.defaultValue !== undefined && changes.advancedOptions.defaultValue !== current.defaultValue) ||
    (changes.widgetOptions?.autoSet != null && changes.widgetOptions.autoSet !== current.autoSet)
  );
}

No Migration (metadata-only changes)

These never trigger a migration:

Change	Why
Display name	ViewColumn metadata
Description	ViewColumn metadata
Hidden / Visible / Readonly	ViewColumn metadata
Position / column reorder	ViewColumn metadata
Widget type (if DB types are compatible)	No DB change needed
imageMode (managed ↔ url)	Runtime interpretation only
Other widget options (formatting, etc.)	ViewColumn metadata

Backend Response Codes

Scenario	HTTP Status	What happens
Metadata-only change	200 OK	Applied immediately, sync
Migration required	202 Accepted	Queued for async processing
Blocked mutation	400 Bad Request	e.g., renaming a primary key

After a 202, listen for the SSE schema.updated event to know when the migration completes.

Auto-Detected Widget Types (Schema Import)

When a database is first synced, the backend auto-assigns widget types based on the column's DB type and name:

DB Type (any variant)	Widget	Name-based refinement
VARCHAR, CHAR	STRING	email → EMAIL, url/link → URL, phone/tel → PHONE
TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT	TEXT
ENUM, SET	SELECT
INTEGER, INT, BIGINT, SMALLINT, TINYINT, MEDIUMINT, SERIAL…	NUMBER
NUMERIC, DECIMAL, FLOAT, DOUBLE, REAL…	DECIMAL
BOOLEAN, BOOL	BOOLEAN
DATE	DATE
TIMESTAMP, TIMESTAMPTZ, DATETIME	DATETIME
TIME, TIMETZ	STRING	No TIME widget yet
UUID, UNIQUEIDENTIFIER	UUID
JSON, JSONB	TEXT
BYTEA, BLOB, BINARY…	STRING	Binary data displayed as string
INTERVAL, INET, POINT…	STRING	PostgreSQL special types
Unknown	STRING	Fallback — always safe

The user can then change the widget type to anything in allowedWidgetTypes.

Example Scenarios

Scenario 1: Change STRING → IMAGE (URL mode)

Current: widgetType=STRING, dbType=VARCHAR, length=255
Change:  widgetType=IMAGE, widgetOptions={ imageMode: "url" }

1. IMAGE allowedDbTypes = ["VARCHAR", "TEXT"] — VARCHAR compatible via direct match ✓
2. No advancedOptions.type override → keep current VARCHAR
3. imageMode is metadata-only
4. Result: No migration (200 OK)

Scenario 2: Imported MySQL MEDIUMTEXT → change to IMAGE

Current: widgetType=TEXT, dbType=MEDIUMTEXT
Change:  widgetType=IMAGE

1. IMAGE allowedDbTypes = ["VARCHAR", "TEXT"] — MEDIUMTEXT not in list, BUT in same compatibility group as TEXT ✓
2. Result: No migration (200 OK)

Scenario 3: Change STRING → INTEGER

Current: widgetType=STRING, dbType=VARCHAR, length=255
Change:  widgetType=INTEGER

1. INTEGER allowedDbTypes = ["INTEGER", "BIGINT", "SMALLINT"] — VARCHAR not compatible ✗
2. Result: Migration required (202 Accepted) — ALTER COLUMN TYPE INTEGER

Scenario 4: Change VARCHAR(255) → VARCHAR(2048)

Current: dbType=VARCHAR, length=255
Change:  advancedOptions={ length: 2048 }

1. Same DB type, no type migration
2. Length changed: 255 → 2048 (VARCHAR enforces length)
3. Result: Migration required (202 Accepted) — ALTER COLUMN TYPE VARCHAR(2048)

Scenario 5: Toggle nullable

Current: nullable=true
Change:  advancedOptions={ nullable: false }

1. Nullable changed: true → false
2. Result: Migration required (202 Accepted) — ALTER COLUMN SET NOT NULL

Scenario 6: Change DATETIME autoSet

Current: widgetType=DATETIME, autoSet=none
Change:  widgetOptions={ autoSet: "insert" }

1. autoSet changed: none → insert
2. Result: Migration required (202 Accepted) — SET DEFAULT CURRENT_TIMESTAMP

Scenario 7: Imported PostgreSQL TIMESTAMPTZ → change to DATE

Current: widgetType=DATETIME, dbType=TIMESTAMPTZ
Change:  widgetType=DATE

1. DATE allowedDbTypes = ["DATE"] — TIMESTAMPTZ not in list, and not in same compatibility group as DATE ✗ (Timestamp family ≠ Date — DATE is standalone)
2. Result: Migration required (202 Accepted) — ALTER COLUMN TYPE DATE