Runtime Dynamic Tables

[mwillbanks]

Runtime Dynamic Tables

Overview

In multi-tenant platforms, CMSs, or app-builders, you often want to allow users to define custom content types (tables) at runtime—while retaining type-safety, relations, indexes, and the full semantics of your ORM/permission system. With ZenStack and its schema language ZModel, we can imagine a mechanism where you declare a generic “dynamic table” pattern and then at runtime generate concrete tables, persist their schema specs, migrate them, and expose them as first-class models in the query layer.

Why it’s needed

• Enables platforms to support user-defined data models without needing to hand-code every table.
• Maintains strong typing, permissions, indexing and migration support rather than “loose/no-schema” models.
• Ensures that generated tables remain integrated with ORM, relations, policies and tooling—rather than being “ad-hoc” tables manually managed.
• Supports multi-tenant use-cases (per-tenant schema/customization) while retaining a consistent backend architecture.

Proposed Solution

type BaseFieldsMixin {
    id        String   @id @default(cuid())
    createdAt DateTime @default(now())
    updatedAt DateTime @updatedAt
}

model Content with BaseFieldsMixin {
    tenant     Tenant     @relation(fields: [tenantId], references: [id])
    tenantId   Int
    name       String
    createdAt  DateTime @default(now())
    owner      User       @relation(fields: [ownerId], references: [id])
    ownerId    Int
    type       String

    @@delegate(type)

    @@allow('create', auth().tenantId == tenantId)
    @@allow('read',   auth().tenantId == tenantId)
    @@deny('all',     auth() == null)
}

@dynamic
model CustomContent<T> extends Content {
  // schema for T defined at runtime
}

Runtime API usage:

// DDL
db.createDynamicModel(modelName: string, baseModel: keyof typeof schema.models, spec: ModelDef);
db.updateDynamicModel(modelName: string, spec: ModelDef);
db.deleteDynamicModel(modelName: string);

// These specs are stored in a special table for ZenStack called: `_zenstack_dynamic_models`
/* structure:
   id:      modelName (string)
   base:    baseModelName (string)
   spec:    JSON
   isDirty: boolean
*/

// Query API example
await db.getDynamicModel(modelName: string).create({ ... });

// Query builder (via Kysely)
await db.$qb
  .insertInto(modelName)
  .values({ ... })
  .returningAll()
  .executeTakeFirstOrThrow();

Proposed workflow outline

1. Developer or tenant defines a new content model via
   createDynamicModel("BlogPost", "CustomContent", { …fields, indexes… }).
2. The system validates that no conflict exists (model name, table name, base model constraints).
3. Spec is saved in _zenstack_dynamic_models. isDirty is set to true.
4. Migration engine picks up dirty dynamic models: generates a table definition (or alter) based on baseModel + spec, adds primary key/indices, etc.
5. After migration, isDirty → false. The ORM client is aware of the new model (may require code-gen or dynamic proxy).
6. Query layer (db.getDynamicModel("BlogPost")) is available to CRUD on the newly created table, subject to policy rules inherited from base model (Content) and/or custom rules.
7. Relations, indexes, permissions apply just like static models.
8. If tenant later updates the spec (adds/removes fields or indexes) via updateDynamicModel, migrations run to alter table accordingly. If deletes via deleteDynamicModel, table drop/cleanup runs.
9. Developer uses query builder or standard ORM functions seamlessly.

Challenges & Considerations

• TypeScript typing: If models are generated at runtime, compile-time types may not exist. Options: generate TS types post-spec-save, provide generic type fallback, or support code-gen watcher.
• Migration complexity: Altering existing tables (adding/removing columns/indexes) introduces complexity (data migration, defaults, nullability) and risk.
• Policy inheritance: Ensuring dynamic tables correctly inherit base model’s access/policy rules (@@allow/@@deny) and possibly permit overrides.
• Performance / indexing: Generated tables must support indexing, constraints, FK relations, etc; spec should support indexing syntax.
• Table naming and collisions: Clear naming conventions needed (tenant prefix, baseModel prefix, etc) so table names don’t collide, and queries remain efficient.
• Multi-dialect support: Some features (e.g., full-text indexes, JSON columns) vary by provider; dynamic table workflow must detect or restrict unsupported features.
• Tooling & code-gen: Supporting developer experience (autocompletion, types, migrations) requires tooling around dynamic models.
• Lifecycle / cleanup: When a dynamic model is deleted, ensure drop table, drop indexes, and potentially clean data, update code-gen artifacts, and remove any dependencies (views, relations).
• Security and sandboxing: Since users define new tables/fields, you must guard against schema injection, unbounded growth, and ensure isolation (especially multi-tenant).
• Polymorphic integration: Your @dynamic model CustomContent<T> extends Content leverages polymorphism; you’ll need to ensure that the dynamic model inherits relations, discriminator logic, and querying shape correctly (see polymorphism docs).