Storage Strategy

Multi-tenant database architecture, runtime migrations, and supported field types.

Multi-Tenant Isolation

Selegic CRM uses a Physical Column strategy for dynamic objects, providing high performance compared to EAV (Entity-Attribute-Value) models.

Tenant Database

Each tenant has their own schema within the tenant database. The TenantPrisma layer ensures all queries are scoped to the requesting tenant:

const tenantPrisma = new PrismaClient({
  datasources: {
    db: {
      url: tenantConnectionString,
    },
  },
});

Runtime Migrations

When a user adds a new field to a dynamic entity:

Metadata Write: Record added to Field metadata table (global DB)
Hook Trigger: before hook in Field CRUD service triggers migration
DDL Execution: ALTER TABLE <entity> ADD COLUMN <field> <type> runs on tenant DB

// Migration logic
await tenantPrisma.$executeRaw`
  ALTER TABLE ${Prisma.sql(entityName)} 
  ADD COLUMN ${Prisma.sql(fieldName)} ${Prisma.sql(dbType)}
`;

Supported Field Types

Type	UI Type	DB Column	Notes
`TEXT`	Short Text	`VARCHAR(255)`	Default 255 chars
`LONG_TEXT`	Long Text	`TEXT`	Unlimited length
`NUMBER`	Number	`DECIMAL(18,2)`	Precision configurable
`CHECKBOX`	Checkbox	`BOOLEAN`
`PICKLIST`	Dropdown	`VARCHAR(100)`	Predefined options
`LOOKUP`	Related Record	`UUID`	Foreign key to another entity
`DATE`	Date	`TIMESTAMP`
`DATETIME`	Date & Time	`TIMESTAMPTZ`
`EMAIL`	Email	`VARCHAR(255)`	With validation
`PHONE`	Phone	`VARCHAR(50)`
`URL`	URL	`VARCHAR(500)`

Data Integrity

Foreign Keys: Enforced at DB level for LOOKUP fields where possible
Cross-Tenant: Strictly forbidden by architecture
Soft Delete: Deletions mark records as deleted, not physically removed

Indexing

The migration engine automatically creates indexes for:

Primary key (id)
Tenant identifier (org_id)
Common filter fields

Custom indexes can be added via metadata configuration.