Schema Editor
Customize database fields at runtime — add, rename, or remove columns with automatic FTS and VectorDB rebuild.
The Schema Editor is available only to superadmin users. Schema changes affect the entire system's data structure.
Overview
The Schema Editor allows you to modify the database structure of the Pricing Database and Product Specifications at runtime — without writing SQL or restarting the server. All changes are versioned and auditable.
Add / Rename / Remove Field] --> B{Change Type} B -->|Add Field| C[ALTER TABLE
Add Column] B -->|Rename Field| D[Create New Table
Copy Data
Drop Old Table] B -->|Remove Field| E[Create New Table
Copy Data
Drop Old Table] C --> F[Rebuild FTS5 Index] D --> F E --> F F --> G[Rebuild VectorDB Embeddings] G --> H[Yes Schema Updated
Version Incremented] style A fill:#3B82F6,color:#fff style F fill:#F59E0B,color:#fff style G fill:#8B5CF6,color:#fff style H fill:#22C55E,color:#fff
Accessing the Schema Editor
- Navigate to Pricing Database or Product Specifications.
- Click the "Schema Editor" button at the top of the page (visible only to superadmin).
- A modal opens showing the current schema with all fields listed.
Field Properties
Each field in the schema has the following configurable properties:
| Property | Type | Description |
|---|---|---|
name | Text | Column name in the database (snake_case) |
display_label | Text | Human-readable label shown in the UI |
data_type | Enum | text, real, integer, or boolean |
is_required | Boolean | Whether the field must be filled when adding records |
default_value | Text | Default value for new records |
format_hint | Enum | UI display format: currency, badge, textarea, boolean_badge |
is_fts_indexed | Boolean | Whether this field is included in FTS5 full-text search |
fts_weight | Real | FTS5 ranking weight (higher = more important in search) |
is_vectordb_field | Boolean | Whether this field is included in VectorDB embeddings |
vectordb_template | Text | Template string for how this field appears in VectorDB text |
sort_order | Integer | Display order in the UI (drag to reorder) |
Schema Operations
Add Field
- Click "+ Add Field" in the Schema Editor.
- Enter the field name (snake_case), display label, data type, and other properties.
- Configure FTS settings (indexed? weight?) and VectorDB settings (included? template?).
- Click Save. The column is added via
ALTER TABLE, and FTS/VectorDB rebuild is queued.
Existing records will have NULL (or the default value you specify) for the new field.
You can bulk-update records via import/export if needed.
Edit Field
Click the Edit button next to any field. You can change the display label, required status, default value, format hint, FTS weight, VectorDB template, and other metadata.
You cannot change a field's data type after creation. To change the type, remove the field and re-add it (data in that column will be lost).
Rename Field
- Click "Rename" next to the field.
- Enter the new column name.
- The system creates a new table structure, copies all data, and drops the old table. FTS and VectorDB are rebuilt.
Remove Field
Removing a field permanently deletes the column and all its data from every record. This cannot be undone. Export your data first if you need to preserve the values.
FTS & VectorDB Configuration
FTS Weights
Each text field can be included in FTS5 search with a configurable weight. Higher weights make matches in that field rank higher in search results.
| Weight | Meaning | Example |
|---|---|---|
| 2.0 | Very high priority | product_name |
| 1.5 | High priority | category |
| 1.0 | Normal priority | description, sku |
| 0.5 | Low priority | currency, unit |
VectorDB Templates
The VectorDB template defines how a field's value is represented in the text that gets embedded.
Use {value} as a placeholder.
Example templates:
Product: {value}
Category: {value}
Price is {value} IDRSchema History & Diff
Version History
Every schema change creates a new version. The History tab shows a timeline of all changes with timestamps and descriptions.
Schema Diff
Compare any two versions side-by-side. The diff shows:
- Added fields (highlighted in green)
- Removed fields (highlighted in red)
- Changed properties (highlighted in yellow)
Rebuild Status
After schema changes, FTS and VectorDB indexes need to be rebuilt. This happens automatically but you can also trigger a manual rebuild.
- Auto-rebuild — Queued automatically after add/rename/remove field operations
- Manual rebuild — Click "Rebuild" to trigger FTS and/or VectorDB rebuild manually
- Status polling — A live indicator shows rebuild progress (processing, complete, or failed)