Add ability to create double value for custom objects

[magrinj]

Context

To elevate our platform experience and adhere to our dedication towards versatility, we've successfully added the integration of custom objects and fields into "Twenty". More on this can be found in Discussion #1142. This initiative now lets users across different workspaces seamlessly access custom objects using a dynamic GraphQL schema.

How it's currently working

Custom objects trace their roots back to metadata tables that dictate the shape, name, and type of the objects. All this information is housed in the metadata schema database, consisting of tables:

• DataSource: Details where data is stored.
• Object: Describes the object and is linked to a DataSource.
• Field: Outlines an Object's fields and connects to the Object.

DataSource

@Entity('data_source_metadata')
export class DataSourceMetadata {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ nullable: true })
  url: string;

  @Column({ nullable: true })
  schema: string;

  @Column({ type: 'enum', enum: ['postgres'], default: 'postgres' })
  type: DataSourceType;

  @Column({ nullable: true, name: 'display_name' })
  displayName: string;

  @Column({ default: false, name: 'is_remote' })
  isRemote: boolean;

  @Column({ nullable: false, name: 'workspace_id' })
  workspaceId: string;

  @CreateDateColumn({ name: 'created_at' })
  createdAt: Date;

  @UpdateDateColumn({ name: 'updated_at' })
  updatedAt: Date;
}```

### Object

```typescript
@Entity('object_metadata')
export class ObjectMetadata {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ nullable: false, name: 'data_source_id' })
  dataSourceId: string;

  @Column({ nullable: false, name: 'display_name' })
  displayName: string;

  @Column({ nullable: false, name: 'target_table_name' })
  targetTableName: string;

  @Column({ default: false, name: 'is_custom' })
  isCustom: boolean;

  @Column({ nullable: false, name: 'workspace_id' })
  workspaceId: string;

  @OneToMany(() => FieldMetadata, (field) => field.object)
  fields: FieldMetadata[];

  @CreateDateColumn({ name: 'created_at' })
  createdAt: Date;

  @UpdateDateColumn({ name: 'updated_at' })
  updatedAt: Date;
}

Field

@Entity('field_metadata')
export class FieldMetadata {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ nullable: false, name: 'object_id' })
  objectId: string;

  @Column({ nullable: false })
  type: string;

  @Column({ nullable: false, name: 'display_name' })
  displayName: string;

  @Column({ nullable: false, name: 'target_column_name' })
  targetColumnName: string;

  @Column('text', { nullable: true, array: true })
  enums: string[];

  @Column({ default: false, name: 'is_custom' })
  isCustom: boolean;

  @Column({ nullable: true, default: true, name: 'is_nullable' })
  isNullable: boolean;

  @Column({ nullable: false, name: 'workspace_id' })
  workspaceId: string;

  @ManyToOne(() => ObjectMetadata, (object) => object.fields)
  @JoinColumn({ name: 'object_id' })
  object: ObjectMetadata;

  @CreateDateColumn({ name: 'created_at' })
  createdAt: Date;

  @UpdateDateColumn({ name: 'updated_at' })
  updatedAt: Date;
}

Introduction to a New Concept

Our vision is to evolve the Field type. We currently restrict to the following types: uuid, text, url, date, boolean, number, and enum. We are now aiming to expand this list with entries such as double-text and amount. These new data types would potentially offer multi key/value storage under a single key.

So for example if I've a type amount on a field, the generated resolver should serve something like this:

query FindManyProduct {
  findManyProduct {
    id
    amount {
      value
      currency
    }
  }
}

Proposals

Use a JSON/JSONB Column

For complex types like double-text or amount, we can use a JSON or JSONB column type.

This approach benefits from flexibility since JSON/JSONB columns can store arbitrary nested data structures. In the GraphQL layer, and we can interpret and expose these as required:

Pros:

• Offers great flexibility.
• Can adapt to varied structures without extra tables.

Cons:

• Searching within JSON fields might hit performance snags.
• Data validation becomes essential.

Field Configuration Column

Add a configuration column (of type JSON/JSONB) in the Field table that defines the shape and attributes of the complex type. This way, we don't need extra tables, and we can still capture the additional attributes of complex types.

Pros:

• Balances flexibility with centralized management.

Cons:

• Might require parsing and validation logic.
• Searching might be a tad challenging.

Composite Fields

In FieldMetadata, we can introduce a notion of "group" or "composite key". This means that fields like value and currency will be associated under a common composite key, such as amount.

Modify FieldMetadata:

@Entity('field_metadata')
export class FieldMetadata {
  // ... other fields ...

  @Column({ nullable: true, name: 'composite_key' })
  compositeKey: string;

  // ... other fields ...
}

For instance:

Field: amount_value, compositeKey: amount, displayName: Amount
Field: amount_currency, compositeKey: amount, displayName: Currency

Pros:

• Optimizes database operations.
• Yields a comprehensible GraphQL Schema.

Cons:

• GraphQL resolver might get complex.
• Introduces challenges in mutation management.

Add a ViewMetadata layer

ViewMetadata approach decouple the storage structure from the presentation structure. It will essentially allow to define how different FieldMetadata entries should be combined and presented in the GraphQL API. Here's a conceptual breakdown of how this can work:

Create ViewMetadata and ViewFieldMetadata entities:

@Entity('view_metadata')
export class ViewMetadata {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @OneToOne(() => ObjectMetadata)
  @JoinColumn({ name: 'object_id' })
  object: ObjectMetadata;

  @Column({ nullable: false, name: 'display_name' })
  displayName: string;

  @Column({ nullable: false, name: 'workspace_id' })
  workspaceId: string;

  @OneToMany(() => ViewFieldMetadata, (viewField) => viewField.view)
  fields: ViewFieldMetadata[];

  @CreateDateColumn({ name: 'created_at' })
  createdAt: Date;

  @UpdateDateColumn({ name: 'updated_at' })
  updatedAt: Date;
}

@Entity('view_field_metadata')
export class ViewFieldMetadata {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ nullable: false, name: 'view_id' })
  viewId: string;

  @ManyToOne(() => ViewMetadata, (view) => view.fields)
  @JoinColumn({ name: 'view_id' })
  view: ViewMetadata;

  @Column({ nullable: false, name: 'field_id' })
  fieldId: string;

  @ManyToOne(() => FieldMetadata)
  @JoinColumn({ name: 'field_id' })
  field: FieldMetadata;

  @CreateDateColumn({ name: 'created_at' })
  createdAt: Date;

  @UpdateDateColumn({ name: 'updated_at' })
  updatedAt: Date;
}

This will act as a bridge between ViewMetadata and FieldMetadata, enabling to define how individual fields (or groups of fields) should appear under a view.

Then we can base our GraphQL resolvers on ViewMetadata:

Fetch the relevant ViewMetadata based on the GraphQL query.
For each ViewFieldMetadata within that view, retrieve the data from the associated FieldMetadata (and its linked table/column).

Pros:

• Boasts high flexibility.
• Facilitates multiple views for the same data set.

Cons:

• Introduces another layer, adding complexity.
• Regular maintenance checks will be necessary.

[Bonapara]

Double-text field type should not be universally available as it's an exception we're using specifically for our People and company standard object.

Also, it's important to note that the goal is not limited to creating double values as mentioned in the issue title. The ability to have multiple key/value pairs is also crucial, such as including the currency exchange rate for the given "amount type" example. (Later, it's just an example)

We should store the amount as an integer and use "cents" to prevent computation errors.