Indexes & FK Cascade Rules — Frontend Integration

Backend branch: refactor/remove-recover-with-item-antipattern

---

What's New

Feature	Status	FE Work Needed
Database indexes (create/list/delete)	Backend complete	Yes — new UI panel
FK cascade rules (ON DELETE / ON UPDATE)	Backend + Frontend complete	Dropdowns in column properties panel
Schema import (indexes + cascades)	Backend complete	No — auto-detected on import

---

1. Indexes API

Endpoints

Method	Path	Auth	Permission
GET	/api/indexes/view/{viewUuid}	JWT	VIEW_DATA
POST	/api/indexes/view/{viewUuid}	JWT	CONFIGURE_VIEW
DELETE	/api/indexes/{indexUuid}	JWT	CONFIGURE_VIEW

TypeScript Types

interface IndexDTO {
  uuid?: string;          // Read-only — assigned by backend on creation
  name: string;           // Required — index name, e.g. "idx_orders_customer_date"
  columnNames: string[];  // Required — column names in the index
  isUnique?: boolean;     // Optional — default false
}

Create Index

// POST /api/indexes/view/{viewUuid}  →  201 Created
const index = await api.post<IndexDTO>(`/api/indexes/view/${viewUuid}`, {
  name: 'idx_orders_customer_date',
  columnNames: ['customer_id', 'order_date'],
  isUnique: false,
}, {
  headers: { 'X-Client-Id': clientTabId },
});
// index.uuid is now set

Error codes:

• 400 — name or columnNames missing/empty
• 403 — user lacks CONFIGURE_VIEW permission
• 404 — view not found

Async note: The 201 response means the metadata record exists. The actual CREATE INDEX DDL runs asynchronously via the processor. No SSE event is fired specifically for index operations yet — the index is usable immediately from the metadata side, and the DB-side index appears within seconds.

List Indexes

// GET /api/indexes/view/{viewUuid}  →  200 OK
const indexes = await api.get<IndexDTO[]>(`/api/indexes/view/${viewUuid}`);
// Returns [] when no indexes exist

Delete Index

// DELETE /api/indexes/{indexUuid}  →  204 No Content
await api.delete(`/api/indexes/${index.uuid}`, {
  headers: { 'X-Client-Id': clientTabId },
});

UI Suggestion

An "Indexes" tab or section in the View Settings/Configuration panel (alongside entity constraints). Minimal UI:

• Table with columns: Name, Columns (chip list), Unique (badge), Delete (icon button)
• "Add Index" form: name input, multi-select for columns (populated from view's column list), unique checkbox
• Column names in the dropdown should use the database column names (from advancedOptions.name or the column's fieldName), not display names

---

2. FK Cascade Rules (ON DELETE / ON UPDATE)

What It Is

Foreign key columns can specify what happens when the referenced parent row is deleted or updated. This maps directly to PostgreSQL's ON DELETE and ON UPDATE clauses.

Status: Implemented in both backend and frontend. The column properties panel shows "On Delete" and "On Update" dropdowns in the Referential Actions section for FK columns.

Allowed Values

Value	Label for UI	ON DELETE Meaning	ON UPDATE Meaning
CASCADE	Cascade	Delete child rows	Update FK to new key
SET_NULL	Set Null	Set FK to NULL	Set FK to NULL
RESTRICT	Restrict	Block delete if children exist	Block update if referenced
NO_ACTION	No Action	Same as Restrict (DB default)	Same as Restrict (DB default)
SET_DEFAULT	Set Default	Set FK to default value	Set FK to default value
null	(not set)	DB default (No Action)	DB default (No Action)

How to Read It

Both values are returned on column fetch:

// GET /api/columns/view/{viewUuid}
column.advancedOptions?.onDeleteAction  // "CASCADE" | "SET_NULL" | ... | null
column.advancedOptions?.onUpdateAction  // "CASCADE" | "SET_NULL" | ... | null

How to Set It

Use the existing column update endpoint:

// PATCH /api/columns/item/{columnUuid}  →  200 OK (sync) or 202 (async)
await api.patch(`/api/columns/item/${columnUuid}`, {
  advancedOptions: {
    onDeleteAction: 'CASCADE',
    onUpdateAction: 'CASCADE',
  },
}, {
  headers: { 'X-Client-Id': clientTabId },
});

Async migration: Changing cascade actions is a schema change — the backend drops and recreates the FK constraint with the new actions. This follows the same async migration flow as changing nullable/unique/type.

Schema Import

When importing an existing database, both DELETE_RULE and UPDATE_RULE are read from JDBC metadata and stored on the FK column. The referenced_table_name is also stored for DDL generation.

Validation

• SET_NULL requires the FK column to be nullable — the frontend shows a warning if the column is NOT NULL
• Both actions are included in the migration impact analysis and dry-run preview

---

3. Schema Import — No FE Changes

When users import an existing database schema, the backend now automatically:

• Detects and stores ON DELETE rules on FK columns
• Detects and stores database indexes (composite, non-unique)
• Correctly handles composite unique indexes (no longer flags individual columns as unique)

This data is immediately available via the Indexes API and column advancedOptions.onDeleteAction after import. No frontend changes needed for the import flow itself.

---

Not Included / Future

• SSE events for index changes — not implemented yet. If we want real-time index updates across tabs, we'd need a new workspace SSE event type. For now, refreshing the indexes list after create/delete is sufficient.
• Index rename — not supported. Delete and recreate instead.
• Partial index / expression index — not supported yet (only simple column-based indexes).