Support custom types

[Datron]

Allow users to store custom types for forms where type is a dropdown. You can reuse the jsonschema validations, or customize some of the default types to fit the tenant's requirements. For eg, if teams want nullable default types, they can edit the type and allow null values (which is not allowed in Superposition today)

[Datron]

Database Design

The database structure of the table would be:

CREATE TABLE jsonschema_types (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    type_name TEXT NOT NULL,
    display_name TEXT NOT NULL,
    type_schema JSON NOT NULL,
    created_by TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);
ALTER TABLE jsonschema_types
ADD CONSTRAINT unique_display_name UNIQUE (display_name);
CREATE INDEX IF NOT EXISTS json_schema_types_index ON ONLY public.jsonschema_types(type_name, created_at);

The setup of this table for every tenant looks like the query below. This query adds the default types we support out of the box today

INSERT INTO jsonschema_types(type_name, display_name, type_schema, created_by, created_at)
VALUES (
        'number',
        'Number',
        '{"type": "number"}',
        'user@superposition.io',
        NOW()
    ),
    (
        'number',
        'Decimal',
        '{"type": "number"}',
        'user@superposition.io',
        NOW()
    ),
    (
        'boolean',
        'Boolean',
        '{"type": "boolean"}',
        'user@superposition.io',
        NOW()
    ),
    (
        'enum',
        'String (Enum)',
        '{"type": "string", "enum": "$replacement"}',
        'user@superposition.io',
        NOW()
    ),
    (
        'pattern',
        'String (Regex)',
        '{"type": "string", "pattern": "$replacement"}',
        'user@superposition.io',
        NOW()
    );

API

Name	Method	URL	request	response
List Type	GET	/types	None	[{"created_at":"2024-05-13T12:02:02.030601","created_by":"user@superposition.io","display_name":"Number","id":"ed8c06e7-655b-4805-bd98-cd47f3a90cfc","last_modified":"2024-05-13T12:02:02.030601","type_name":"number","type_schema":{"type":"number"}},{"created_at":"2024-05-13T12:02:02.030601","created_by":"user@superposition.io","display_name":"Decimal","id":"c7b88aa0-dd0b-4b8d-9492-f2bb8701f8b9","last_modified":"2024-05-13T12:02:02.030601","type_name":"number","type_schema":{"type":"number"}},{"created_at":"2024-05-13T12:02:02.030601","created_by":"user@superposition.io","display_name":"Boolean","id":"16abae89-9fbd-488f-ac8b-d9ebdc8fae23","last_modified":"2024-05-13T12:02:02.030601","type_name":"boolean","type_schema":{"type":"boolean"}},{"created_at":"2024-05-13T12:02:02.030601","created_by":"user@superposition.io","display_name":"String (Enum)","id":"eed97c33-4a0d-4ab7-a7fe-8e1408f48e4f","last_modified":"2024-05-13T12:02:02.030601","type_name":"enum","type_schema":{"enum":"$replacement","type":"string"}},{"created_at":"2024-05-13T12:02:02.030601","created_by":"user@superposition.io","display_name":"String (Regex)","id":"2e5cd6a1-8b28-440a-83b1-3d0fce590340","last_modified":"2024-05-13T12:02:02.030601","type_name":"pattern","type_schema":{"pattern":"$replacement","type":"string"}}]
Create Type	PUT	/types	{ "type_schema": {"type": "integer"}, "display_name": "Integer", "type_name": "i16"}	{"message": "custom type created successfully"}
Update Type	PUT	/types	{ "type_schema": {"type": "integer"}, "display_name": "Integer", "type_name": "i32"}	{"message": "custom type created successfully"}
Delete Type	DELETE	/types/{id}	None	{"message": "deleted custom type"}

[knutties]

    type_name TEXT NOT NULL,

I feel we should the same semantics as functions here. The name of the type itself should be the id - better to have types that have unique names than allowing for name clashes.

Few more questions to address:

1. Is a ref to the type stored in the default config or is the type copied at the time of config create?
2. If it is the former, what are the semantics of updation of type - what happens to earlier configs with this type?

[Datron]

I feel we should the same semantics as functions here. The name of the type itself should be the id - better to have types that have unique names than allowing for name clashes.

Sure @knutties , will do this.

Is a ref to the type stored in the default config or is the type copied at the time of config create?

Both tables have a schema column, we should copy the schema - if users want to update the type they can manually

[mahatoankitkumar]

Difference between type_name and display_name?

[Datron]

Difference between type_name and display_name?

Display name is shown in UI, you may want it to be different from the type name. For example, the type_name and display_name for String (Regex) is different

[mahatoankitkumar]

Is it really necessary them separate?

[Datron]

I'm seeing some cases where it is required

[sauraww]

@Datron Why have we created index for created_at ?
Do we have a use case where we are going to search based in created_at ?

[Datron]

@sauraww yes

[ShubhranshuSanjeev]

Can you give an example where the index over created_at will be required?

[Datron]

Consider this query:

select * from jsonschema_types where created_at > yesterday and created_at < tomorrow;

In a filter or list API, this is a very frequent operation to change the date range on the items shown. It is not present in the current API implementation, but adding created_at in index makes sense. In fact, maybe last_modified should also be indexed.

[ShubhranshuSanjeev]

For this kind of operation, won't it be better to have a separate index on just created_at column?

[Datron]

https://www.postgresql.org/docs/current/indexes-multicolumn.html
Hmm it maybe an over optimization on my part, a separate index makes sense. Will add this.

[knutties]

Let us also not depend on database primitives for ids.

Let us send it from the app itself. In this case we can use the name itself as the id and restrict it to alphanumeric string.

[Datron]

Folks, I also want to make the following change in the UI where types are concerned:

When ever a type is selected, it's json schema is added to the textarea and the user can edit it as is. What do you guys think? This is a double edged sword - simplifies our UI but makes it scary to novice users. Of course, novice users are novices for a limited time.