Dynamic Forms Builder using PostgreSQL

dynamic_forms_builder.sql

CREATE TABLE master.forms
  (
    form_id serial NOT NULL,
    code CHARACTER VARYING,
    name CHARACTER VARYING,
    description CHARACTER VARYING,
    properties jsonb,
    audit_trails jsonb,
    stereotype CHARACTER VARYING,
    sort_order INTEGER,
    row_status CHARACTER VARYING DEFAULT 'ACTIVE'::CHARACTER VARYING,
    created_by    INTEGER DEFAULT 0,
    created_on    TIMESTAMP WITH TIME ZONE DEFAULT now(),
    lastupdate_by INTEGER,
    lastupdate_on TIMESTAMP WITH TIME ZONE,
    CONSTRAINT pk_forms PRIMARY KEY (form_id)
  );

CREATE TABLE master.fields
  (
    field_id serial NOT NULL,
    parent_id     INTEGER,
    form_id       INTEGER,
    field_type_id INTEGER,
    code CHARACTER VARYING,
    name CHARACTER VARYING,
    properties jsonb,
    audit_trails jsonb,
    stereotype CHARACTER VARYING,
    sort_order INTEGER,
    row_status CHARACTER VARYING DEFAULT 'ACTIVE'::CHARACTER VARYING,
    created_by    INTEGER DEFAULT 0,
    created_on    TIMESTAMP WITH TIME ZONE DEFAULT now(),
    lastupdate_by INTEGER,
    lastupdate_on TIMESTAMP WITH TIME ZONE,
    CONSTRAINT pk_fields PRIMARY KEY (field_id),
    CONSTRAINT fk_field_has_type FOREIGN KEY (field_type_id) REFERENCES master.field_type (field_type_id) MATCH SIMPLE ON
  UPDATE CASCADE ON
  DELETE RESTRICT,
    CONSTRAINT fk_form_has_fields FOREIGN KEY (form_id) REFERENCES master.forms (form_id) MATCH SIMPLE ON
  UPDATE CASCADE ON
  DELETE CASCADE,
    CONSTRAINT fk_ptsp_form_fields_has_parent FOREIGN KEY (parent_id) REFERENCES master.fields (field_id) MATCH SIMPLE ON
  UPDATE RESTRICT ON
  DELETE CASCADE
  );

CREATE TABLE master.field_type
  (
    field_type_id serial NOT NULL,
    code CHARACTER VARYING,
    name CHARACTER VARYING,
    properties jsonb,
    audit_trails jsonb,
    stereotype CHARACTER VARYING,
    sort_order INTEGER,
    row_status CHARACTER VARYING DEFAULT 'ACTIVE'::CHARACTER VARYING,
    created_by    INTEGER DEFAULT 0,
    created_on    TIMESTAMP WITH TIME ZONE DEFAULT now(),
    lastupdate_by INTEGER,
    lastupdate_on TIMESTAMP WITH TIME ZONE,
    CONSTRAINT pk_field_type PRIMARY KEY (field_type_id)
  );

CREATE TABLE asset.master_data
  (
    asset_id serial NOT NULL,
    form_id INTEGER,
    properties jsonb,
    audit_trails jsonb,
    stereotype CHARACTER VARYING,
    sort_order INTEGER,
    row_status CHARACTER VARYING DEFAULT 'ACTIVE'::CHARACTER VARYING,
    created_by    INTEGER DEFAULT 0,
    created_on    TIMESTAMP WITH TIME ZONE DEFAULT now(),
    lastupdate_by INTEGER,
    lastupdate_on TIMESTAMP WITH TIME ZONE,
    CONSTRAINT pk_master_data2 PRIMARY KEY (asset_id),
    CONSTRAINT fk_data_has_form_id FOREIGN KEY (form_id) REFERENCES master.forms (form_id) MATCH SIMPLE ON
  UPDATE RESTRICT ON
  DELETE SET NULL
  );