RFC: Dynamic Tables Schema Documentation

• Authors: - Prathik Shetty(@pshettydev), Shaik Noorullah(@pnow-devsupreme), Abdul Ahad(@ahad-proficientnow )
• Date: 2024-12-20
• Status: Accepted

Overview

The initial "Dynamic Tables" schema in schema.prisma implements a runtime-defined data model using the Entity-Attribute-Value (EAV) pattern. This allows tenants to create their own tables and fields on the fly without requiring actual database schema migrations (DDL) or application restarts.

This system effectively builds a "database within a database," enabling a flexible, Airtable-like experience where users can define custom data structures. At least that's what the plan WAS.

Core Architecture: The EAV Pattern

The architecture is built around four main concepts:

1. TableMeta (Container): Represents the "Table" definition.
2. Field (Attribute): Represents the "Column" definition.
3. Record (Entity): Represents a single "Row" in the table.
4. Data (Value): Represents a single "Cell" value for a specific Record and Field.

Data Flow Diagram

Detailed Model Breakdown

1. TableMeta (table_meta)

Purpose: Acts as the registry for dynamic tables.

• Key Fields:
  • name: Display name of the table.
  • db_table_name: A unique internal identifier for the table (likely used for logical referencing).
  • tenant_id: Ensures multi-tenancy isolation.
• Role: The root object for any dynamic collection.

2. Field (field)

Purpose: Defines the schema/columns of a dynamic table.

• Key Fields:
  • type: High-level type (TEXT, NUMBER, DATE, etc.).
  • cell_value_type: The underlying data type expected (STRING, INTEGER, etc.).
  • db_field_type: The Postgres type mapping (VARCHAR, INT, etc.).
  • options: JSON string storing configuration (e.g., select options, validation rules).
• Role: Validates data and dictates how it should be rendered in the UI.

3. Record (data table alias Record)

Purpose: Represents a single row or entity instance.

• Key Fields:
  • table_id: Links to the definition.
• Role: A container for a collection of values (Data). It has no columns of its own other than metadata (created_at, etc.).

4. Data (record table alias Data)

Purpose: Stores the actual value for a specific cell.

• Key Fields:
  • record_id: The row it belongs to.
  • field_id: The column it belongs to.
  • value: The actual data, stored as a nullable String.
• Role: The atomic unit of storage. Note: All data types (numbers, dates, booleans) are serialized to strings in the value column.

Supporting Models

• TableRelation: Manages links between dynamic tables (One-to-Many, Many-to-Many). It mimics foreign keys at the application level.
• View: Stores UI configurations like saved filters, sorts, and column visibility (Grid, Kanban, Calendar).
• History Models (TableMetaHistory, FieldHistory, RecordHistory): Provide a full audit trail of schema and data changes, essential for enterprise compliance.

Pros & Cons of Current Approach

Pros

1. Flexibility: Users can create/modify tables instantly without deployment or DB admin intervention.
2. Isolation: Schema changes for one tenant do not affect others.
3. Sparse Data Efficiency: If a record has no value for a field, no row exists in the Data table (storage efficient for sparse datasets).
4. Universal History: Implementing audit logs is uniform across all dynamic data.

Cons

1. Performance (The "Join" Problem): Fetching a single "row" with 10 columns requires joining the Data table 10 times or fetching all Data rows and reassembling them in memory. This is O(N) where N is the number of cells, not rows.
2. Type Safety: The value column is a String. The database cannot enforce that a "Number" field actually contains a number. Type checking moves to the application layer.
3. Query Complexity: Simple SQL queries like SELECT * FROM table WHERE age > 20 become complex subqueries or self-joins on the Data table.
4. Aggregation: Database-level aggregations (SUM, AVG) require casting strings to numbers on the fly, which is slow and prevents index usage.

Recommendations & Improvements

1. Move to JSONB (Recommended)

Instead of the Data table (EAV), store all field values in a single jsonb column on the Record model.

Proposed Schema Change:

model Record {
  id        String   @id @default(uuid())
  table_id  String
  // ... other metadata
  data      Json     @db.JsonB  // Stores { "field_id_1": "value", "field_id_2": 123 }
}

Benefits:

• Performance: Fetching a row is a single read. No joins required.
• Indexing: Postgres supports GIN indexes on JSONB, allowing fast querying like WHERE data->>'field_name' = 'value'.
• Simplicity: Removes the Data table entirely, reducing table size by factor of N (columns).

2. Typed Value Columns (If sticking to EAV)

If EAV is preferred for specific reasons (like granular cell-level history), split the value column into typed columns.

Proposed Schema Change:

model Data {
  // ...
  string_value  String?
  int_value     Int?
  float_value   Float?
  date_value    DateTime?
  bool_value    Boolean?
}

Benefits:

• Native Sorting/Filtering: You can index int_value and run fast range queries.
• Type Safety: Database enforces types.