Database Design

ER Diagram

Schema file

-- Create user table without foreign keys initially
CREATE TABLE "user" (
                        "id" varchar PRIMARY KEY,
                        "email" varchar,
                        "name" varchar,
                        "oid" varchar,
                        "profile_picture" varchar,
                        "roles" varchar,
                        "created_by" varchar,
                        "updated_by" varchar,
                        "created_at" timestamp,
                        "updated_at" timestamp
);

-- Create user_preferences table
CREATE TABLE "user_preferences" (
                                    "id" SERIAL PRIMARY KEY,
                                    "user_id" varchar UNIQUE,
                                    "table_columns" varchar,
                                    "preferences" jsonb,
                                    "created_by" varchar,
                                    "updated_by" varchar,
                                    "created_at" timestamp,
                                    "updated_at" timestamp
);

-- Create country table
CREATE TABLE "country" (
                           "id" SERIAL PRIMARY KEY,
                           "code" varchar(10),
                           "name" varchar,
                           "created_by" varchar,
                           "updated_by" varchar,
                           "created_at" timestamp,
                           "updated_at" timestamp
);

-- Create city table
CREATE TABLE "city" (
                        "id" SERIAL PRIMARY KEY,
                        "country_id" int,
                        "name" varchar,
                        "created_by" varchar,
                        "updated_by" varchar,
                        "created_at" timestamp,
                        "updated_at" timestamp
);

-- Create client table
CREATE TABLE "client" (
                          "id" SERIAL PRIMARY KEY,
                          "name" varchar,
                          "foreign_id" varchar UNIQUE,
                          "created_by" varchar,
                          "updated_by" varchar,
                          "created_at" timestamp,
                          "updated_at" timestamp
);

-- Create parking table
CREATE TABLE "parking" (
                           "city_id" int,
                           "id" SERIAL PRIMARY KEY,
                           "name" varchar,
                           "type" varchar,
                           "actual_news" varchar,
                           "address" varchar,
                           "exemption_and_rules" varchar,
                           "client_id" int,
                           "rates_sheet_url" varchar,
                           "rates_sheet_name" varchar,
                           "phone_number" varchar,
                           "extension_no" varchar,
                           "details" jsonb,
                           "foreign_id" varchar UNIQUE ,
                           "created_by" varchar,
                           "updated_by" varchar,
                           "created_at" timestamp,
                           "updated_at" timestamp
);

-- Create item table
CREATE TABLE "item" (
                        "id" SERIAL PRIMARY KEY,
                        "title" varchar,
                        "description" varchar,
                        "details" jsonb,
                        "foreign_id" varchar UNIQUE ,
                        "created_by" varchar,
                        "updated_by" varchar,
                        "created_at" timestamp,
                        "updated_at" timestamp
);

-- Create contact table
CREATE TABLE "contact" (
                           "id" SERIAL PRIMARY KEY,
                           "name" varchar,
                           "email" varchar,
                           "phone_number" varchar,
                           "description" varchar,
                           "foreign_id" varchar UNIQUE,
                           "created_by" varchar,
                           "updated_by" varchar,
                           "created_at" timestamp,
                           "updated_at" timestamp
);



-- Create parking_pictures table
CREATE TABLE "parking_pictures" (
                                    "id" SERIAL PRIMARY KEY,
                                    "parking_id" int,
                                    "title" varchar,
                                    "url" varchar,
                                    "foreign_id" varchar UNIQUE ,
                                    "created_by" varchar,
                                    "updated_by" varchar,
                                    "created_at" timestamp,
                                    "updated_at" timestamp
);

-- Create parking_floorplan table
CREATE TABLE "parking_floorplan" (
                                     "id" SERIAL PRIMARY KEY,
                                     "parking_id" int,
                                     "title" varchar,
                                     "picture_url" varchar,
                                     "foreign_id" varchar UNIQUE ,
                                     "created_by" varchar,
                                     "updated_by" varchar,
                                     "created_at" timestamp,
                                     "updated_at" timestamp
);

-- Create protocol table
CREATE TABLE "protocol" (
                            "id" SERIAL PRIMARY KEY,
                            "title" varchar,
                            "known" bool,
                            "foreign_id" varchar UNIQUE ,
                            "created_by" varchar,
                            "updated_by" varchar,
                            "created_at" timestamp,
                            "updated_at" timestamp
);

-- Create problem table
CREATE TABLE "problem" (
                           "id" SERIAL PRIMARY KEY,
                           "title" varchar,
                           "known" bool,
                           "foreign_id" varchar UNIQUE ,
                           "action_id" int,
                           "created_by" varchar,
                           "updated_by" varchar,
                           "created_at" timestamp,
                           "updated_at" timestamp
);

-- Create action table
CREATE TABLE "action" (
                          "id" SERIAL PRIMARY KEY,
                          "title" varchar,
                          "description" varchar,
                          "link" varchar,
                          "attachment" varchar,
                          "question" varchar,
                          "foreign_id" varchar UNIQUE ,
                          "created_by" varchar,
                          "updated_by" varchar,
                          "created_at" timestamp,
                          "updated_at" timestamp
);

-- Create answer table
CREATE TABLE "answer" (
                          "id" SERIAL PRIMARY KEY,
                          "title" varchar,
                          "description" varchar,
                          "known" bool,
                          "contact_id" int,
                          "foreign_id" varchar UNIQUE ,
                          "created_by" varchar,
                          "updated_by" varchar,
                          "created_at" timestamp,
                          "updated_at" timestamp
);

-- Create action_response table
CREATE TABLE "action_response" (
                                   "id" SERIAL PRIMARY KEY,
                                   "title" varchar,
                                   "answer_id" int,
                                   "next_action" int,
                                   "foreign_id" varchar UNIQUE ,
                                   "created_by" varchar,
                                   "updated_by" varchar,
                                   "created_at" timestamp,
                                   "updated_at" timestamp
);

-- Create case_attachment table
CREATE TABLE "case_attachment" (
                                   "id" SERIAL PRIMARY KEY,
                                   "doc_type" varchar(50),
                                   "name" varchar,
                                   "description" varchar,
                                   "language" varchar,
                                   "confirmed" bool,
                                   "url" varchar,
                                   "key" varchar,
                                   "case_id" int,
                                   "created_by" varchar,
                                   "updated_by" varchar,
                                   "created_at" timestamp,
                                   "updated_at" timestamp
);

-- Create follow_up table
CREATE TABLE "follow_up" (
                            "contact_id" int,
                            "id" SERIAL PRIMARY KEY,
                            "timestamp" timestamp,
                            "entry_type" varchar,
                            "status" varchar,
                            "note" varchar,
                            "urgency" varchar,
                            "language" varchar(24),
                            "created_by" varchar,
                            "updated_by" varchar,
                            "created_at" timestamp,
                            "updated_at" timestamp
);


-- Create case table
CREATE TABLE "case" (
                        "follow_up_id" int,
                        "id" SERIAL PRIMARY KEY,
                        "status" varchar,
                        "case_no" varchar,
                        "answer_id" int,
                        "problem_id" int,
                        "protocol_id" int,
                        "parent" int,
                        "created_by" varchar,
                        "updated_by" varchar,
                        "created_at" timestamp,
                        "updated_at" timestamp
);

-- Create audit_log table
CREATE TABLE "audit_log" (
                             "contact_id" int,
                             "id" SERIAL PRIMARY KEY,
                             "case_id" int,
                             "timestamp" timestamp,
                             "status" varchar,
                             "protocol" varchar,
                             "communication" varchar,
                             "message" varchar,
                             "follow_up_time" timestamp,
                             "created_by" varchar,
                             "updated_by" varchar,
                             "created_at" timestamp,
                             "updated_at" timestamp
);


CREATE TABLE "parking_contact" (
                                   "parking_id" int,
                                    "contact_id" int,
                                    PRIMARY KEY ("parking_id" ,"contact_id"),
                                    "created_by" varchar,
                                   "updated_by" varchar,
                                   "created_at" timestamp,
                                   "updated_at" timestamp
                               );

-- Create parking_item table
CREATE TABLE "parking_item" (
                                "parking_id" int,
                                "item_id" int,
                                PRIMARY KEY ("parking_id", "item_id"),
                                "created_by" varchar,
                                "updated_by" varchar,
                                "created_at" timestamp,
                                "updated_at" timestamp
);

-- Create item_protocol table
CREATE TABLE "item_protocol" (
                                 "item_id" int,
                                 "protocol_id" int,
                                 PRIMARY KEY ("item_id", "protocol_id"),
                                 "created_by" varchar,
                                 "updated_by" varchar,
                                 "created_at" timestamp,
                                 "updated_at" timestamp
);

-- Create problem_item table
CREATE TABLE "item_problem" (
                                "problem_id" int,
                                "item_id" int,
                                PRIMARY KEY ("problem_id", "item_id"),
                                "created_by" varchar,
                                "updated_by" varchar,
                                "created_at" timestamp,
                                "updated_at" timestamp
);

-- Create answer_item table
CREATE TABLE "item_answer" (
                               "answer_id" int,
                               "item_id" int,
                               PRIMARY KEY ("answer_id", "item_id"),
                               "created_by" varchar,
                               "updated_by" varchar,
                               "created_at" timestamp,
                               "updated_at" timestamp
);

-- Create protocol_problem table
CREATE TABLE "protocol_problem" (
                                    "protocol_id" int,
                                    "problem_id" int,
                                    PRIMARY KEY ("protocol_id", "problem_id"),
                                    "created_by" varchar,
                                    "updated_by" varchar,
                                    "created_at" timestamp,
                                    "updated_at" timestamp
);


-- Create protocol_problem table
CREATE TABLE "problem_answer" (
                                    "problem_id" int,
                                    "answer_id" int,
                                    PRIMARY KEY ("problem_id", "answer_id"),
                                    "created_by" varchar,
                                    "updated_by" varchar,
                                    "created_at" timestamp,
                                    "updated_at" timestamp
);


-- Create case_action_response table
CREATE TABLE "case_action_response" (
                                        "case_id" int,
                                        "action_response_id" int,
                                        PRIMARY KEY ("case_id", "action_response_id"),
                                        "created_by" varchar,
                                        "updated_by" varchar,
                                        "created_at" timestamp,
                                        "updated_at" timestamp
);


-- Create problem_action_response
CREATE TABLE "item_protocol_problem_action_response" (
                                           "problem_id" int,
                                           "action_id" int,
                                           "response_id" int,
                                           "item_id" int,
                                           "protocol_id" int,
                                           PRIMARY KEY ("problem_id", "action_id", "response_id","item_id", "protocol_id"),
                                           "created_by" varchar REFERENCES "user"("id"),
                                           "updated_by" varchar REFERENCES "user"("id"),
                                           "created_at" timestamp,
                                           "updated_at" timestamp,
                                           FOREIGN KEY ("problem_id") REFERENCES "problem" ("id"),
                                           FOREIGN KEY ("action_id") REFERENCES "action" ("id"),
                                           FOREIGN KEY ("response_id") REFERENCES "action_response" ("id"),
                                           FOREIGN KEY ("item_id") REFERENCES "item" ("id"),
                                           FOREIGN KEY ("protocol_id") REFERENCES "protocol" ("id")
);


-- Create answer_contact
CREATE TABLE "answer_contact"(
    "contact_id" int,
    "answer_id" int,
    PRIMARY KEY ("contact_id", "answer_id"),
    "created_by" varchar REFERENCES "user"("id"),
    "updated_by" varchar REFERENCES "user"("id"),
    "created_at" timestamp,
    "updated_at" timestamp,
    FOREIGN KEY ("answer_id") REFERENCES "answer" ("id"),
    FOREIGN KEY ("contact_id") REFERENCES "contact" ("id")
);

-- Add foreign key constraints to created_by and updated_by columns in user table
ALTER TABLE "user"
    ADD CONSTRAINT fk_user_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_user_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");



-- Add foreign key constraints for user_preferences
ALTER TABLE "user_preferences"
    ADD CONSTRAINT fk_user_preferences_user_id FOREIGN KEY  ("user_id") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_user_preferences_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_user_preferences_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");


-- Add foreign key constraints for country
ALTER TABLE "country"
    ADD CONSTRAINT fk_country_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_country_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");



-- Add foreign key constraints for city
ALTER TABLE "city"
    ADD CONSTRAINT fk_city_country_id FOREIGN KEY ("country_id") REFERENCES "country" ("id"),
    ADD CONSTRAINT fk_city_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_city_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");


-- Add foreign key constraints for client
ALTER TABLE "client"
    ADD CONSTRAINT fk_client_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_client_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");


-- Add foreign key constraints for parking
ALTER TABLE "parking"
    ADD CONSTRAINT fk_parking_city_id FOREIGN KEY ("city_id") REFERENCES "city" ("id"),
    ADD CONSTRAINT fk_parking_client_id FOREIGN KEY ("client_id") REFERENCES "client" ("id"),
    ADD CONSTRAINT fk_parking_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_parking_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");



-- Add foreign key constraints for item
ALTER TABLE "item"
    ADD CONSTRAINT fk_item_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_item_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");


-- Add foreign key constraints for contact
ALTER TABLE "contact"
    ADD CONSTRAINT fk_contact_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_contact_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");



-- Add foreign key constraints for parking_pictures
ALTER TABLE "parking_pictures"
    ADD CONSTRAINT fk_parking_pictures_parking_id FOREIGN KEY ("parking_id") REFERENCES "parking" ("id"),
    ADD CONSTRAINT fk_parking_pictures_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_parking_pictures_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");


-- Add foreign key constraints for parking_floorplan
ALTER TABLE "parking_floorplan"
    ADD CONSTRAINT fk_parking_floorplan_parking_id FOREIGN KEY ("parking_id") REFERENCES "parking" ("id"),
    ADD CONSTRAINT fk_parking_floorplan_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_parking_floorplan_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");


-- Add foreign key constraints for protocol
ALTER TABLE "protocol"
    ADD CONSTRAINT fk_protocol_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_protocol_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");


-- Add foreign key constraints for action
ALTER TABLE "action"
    ADD CONSTRAINT fk_action_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_action_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");

-- Add foreign key constraints for problem
ALTER TABLE "problem"
    ADD CONSTRAINT fk_problem_action_id FOREIGN KEY ("action_id") REFERENCES "action" ("id"),
    ADD CONSTRAINT fk_problem_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_problem_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");


-- Add foreign key constraints for action_response
ALTER TABLE "action_response"
    ADD CONSTRAINT fk_action_response_next_action FOREIGN KEY ("next_action") REFERENCES "action" ("id"),
    ADD CONSTRAINT fk_action_response_answer FOREIGN KEY ("answer_id") REFERENCES "answer" ("id"),
    ADD CONSTRAINT fk_action_response_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_action_response_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");


-- Add foreign key constraints for answer
ALTER TABLE "answer"
    ADD CONSTRAINT fk_answer_contact FOREIGN KEY ("contact_id") REFERENCES "contact" ("id"),
    ADD CONSTRAINT fk_answer_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_answer_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");


-- Add foreign key constraints for follow_up
ALTER TABLE "follow_up"
    ADD CONSTRAINT fk_follow_up_contact_id FOREIGN KEY ("contact_id") REFERENCES "contact" ("id"),
    ADD CONSTRAINT fk_follow_up_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_follow_up_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");


-- Add foreign key constraints for case_attachment
ALTER TABLE "case_attachment"
    ADD CONSTRAINT fk_case_attachment_case_id FOREIGN KEY ("case_id") REFERENCES "case" ("id"),
    ADD CONSTRAINT fk_case_attachment_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_case_attachment_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");

-- Add foreign key constraints for parking_item
ALTER TABLE "parking_item"
    ADD CONSTRAINT fk_parking_item_parking_id FOREIGN KEY ("parking_id") REFERENCES "parking" ("id"),
    ADD CONSTRAINT fk_parking_item_item_id FOREIGN KEY ("item_id") REFERENCES "item" ("id"),
    ADD CONSTRAINT fk_parking_item_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_parking_item_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");

-- Add foreign key constraints for parking_contact
ALTER TABLE "parking_contact"
    ADD CONSTRAINT fk_parking_contact_parking_id FOREIGN KEY ("parking_id") REFERENCES "parking" ("id"),
    ADD CONSTRAINT fk_parking_contact_contact_id FOREIGN KEY ("contact_id") REFERENCES "contact" ("id"),
    ADD CONSTRAINT fk_parking_contact_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_parking_contact_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");

ALTER TABLE "case"
    ADD COLUMN "parking_id" int,
    ADD COLUMN "item_id" int;

-- Add foreign key constraints for case
ALTER TABLE "case"
    ADD CONSTRAINT fk_case_follow_up_id FOREIGN KEY ("follow_up_id") REFERENCES "follow_up" ("id"),
    ADD CONSTRAINT fk_case_parking_item FOREIGN KEY ("parking_id", "item_id") REFERENCES "parking_item" ("parking_id", "item_id"),
    ADD CONSTRAINT fk_case_answer_id FOREIGN KEY ("answer_id") REFERENCES "answer" ("id"),
    ADD CONSTRAINT fk_case_problem_id FOREIGN KEY ("problem_id") REFERENCES "problem" ("id"),
    ADD CONSTRAINT fk_case_protocol_id FOREIGN KEY ("protocol_id") REFERENCES "protocol" ("id"),
    ADD CONSTRAINT fk_case_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_case_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_case_parent FOREIGN KEY ("parent") REFERENCES "case" ("id");


-- Add foreign key constraints for audit_log
ALTER TABLE "audit_log"
    ADD CONSTRAINT fk_audit_log_contact_id FOREIGN KEY ("contact_id") REFERENCES "contact" ("id"),
    ADD CONSTRAINT fk_audit_log_case_id FOREIGN KEY ("case_id") REFERENCES "case" ("id"),
    ADD CONSTRAINT fk_audit_log_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_audit_log_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");



-- Add foreign key constraints for item_protocol
ALTER TABLE "item_protocol"
    ADD CONSTRAINT fk_item_protocol_item_id FOREIGN KEY ("item_id") REFERENCES "item" ("id"),
    ADD CONSTRAINT fk_item_protocol_protocol_id FOREIGN KEY ("protocol_id") REFERENCES "protocol" ("id"),
    ADD CONSTRAINT fk_item_protocol_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_item_protocol_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");


-- Add foreign key constraints for problem_item
ALTER TABLE "item_problem"
    ADD CONSTRAINT fk_problem_item_problem_id FOREIGN KEY ("problem_id") REFERENCES "problem" ("id"),
    ADD CONSTRAINT fk_problem_item_item_id FOREIGN KEY ("item_id") REFERENCES "item" ("id"),
    ADD CONSTRAINT fk_problem_item_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_problem_item_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");


-- Add foreign key constraints for answer_item
ALTER TABLE "item_answer"
    ADD CONSTRAINT fk_answer_item_answer_id FOREIGN KEY ("answer_id") REFERENCES "answer" ("id"),
    ADD CONSTRAINT fk_answer_item_item_id FOREIGN KEY ("item_id") REFERENCES "item" ("id"),
    ADD CONSTRAINT fk_answer_item_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_answer_item_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");


-- Add foreign key constraints for protocol_problem
ALTER TABLE "protocol_problem"
    ADD CONSTRAINT fk_protocol_problem_protocol_id FOREIGN KEY ("protocol_id") REFERENCES "protocol" ("id"),
    ADD CONSTRAINT fk_protocol_problem_problem_id FOREIGN KEY ("problem_id") REFERENCES "problem" ("id"),
    ADD CONSTRAINT fk_protocol_problem_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_protocol_problem_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");


-- Add foreign key constraints for problem_answer
ALTER TABLE "problem_answer"
    ADD CONSTRAINT fk_problem_answer_problem_id FOREIGN KEY ("problem_id") REFERENCES "problem" ("id"),
    ADD CONSTRAINT fk_problem_answer_answer_id FOREIGN KEY ("answer_id") REFERENCES "answer" ("id"),
    ADD CONSTRAINT fk_problem_answer_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_problem_answer_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");

-- Add foreign key constraints for case_action_response
ALTER TABLE "case_action_response"
    ADD CONSTRAINT fk_case_action_response_case_id FOREIGN KEY ("case_id") REFERENCES "case" ("id"),
    ADD CONSTRAINT fk_case_action_response_action_response_id FOREIGN KEY ("action_response_id") REFERENCES "action_response" ("id"),
    ADD CONSTRAINT fk_case_action_response_created_by FOREIGN KEY ("created_by") REFERENCES "user" ("id"),
    ADD CONSTRAINT fk_case_action_response_updated_by FOREIGN KEY ("updated_by") REFERENCES "user" ("id");

DBML to recreate the schema diagram ( Without the auditable fields )

Table user {
 id varchar [pk]
 email varchar
 name varchar
 oid varchar
 profile_picture varchar
 roles varchar
}

Table user_preferences{
  id int [pk]
  user_id varchar
  table_columns varchar
  preferences jsonb
}

Table country {
  id int [pk]
  code varchar
  name varchar
}

Table city {
  id int [pk]
  country_id int
  name varchar
}

Table client{
  id  int [pk]
  name varchar
  foreign_id varchar [unique]
}

Table parking {
  city_id int
  id int [pk]
  name varchar
  type varchar
  rates_sheet_url varchar
  rates_sheet_name varchar
  phone_no varchar
  extension_no varchar
  actual_news varchar
  exemption_and_rules varchar
  client_id int
  details jsonb
  foreign_id int
}

Table item {
  id int [pk]
  title varchar
  description varchar
  details jsonb
    foreign_id int

}

Table contact {
  id int [pk]
  name varchar
  email varchar
  phone_number number
  description varchar
  foreign_id int
}

Table answer_contact {
  contact_id int
  answer_id int
  indexes {
    (contact_id, answer_id) [pk]
  }
}

Table parking_pictures {
  id int [pk]
  parking_id int
  title varchar
  url varchar
    foreign_id int

}

Table parking_floorplan {
  id int [pk]
  parking_id int
  title varchar
  picture_url varchar
    foreign_id int

}

Table protocol {
  id int [pk]
  title varchar
  known bool
  foreign_id int
}

Table problem {
  id int [pk]
  title varchar
  known bool
  foreign_id int
  action_id int
}

Table action {
  id int [pk]
  title varchar
  description varchar
  link varchar
  attachment varchar
  question varchar
  foreign_id int

}

Table action_response {
  id int [pk]
  title varchar
  answer_id int
  next_action int
  foreign_id int

}

Table answer {
  id int [pk]
  title varchar
  description varchar
  known bool
  foreign_id int
}

Table follow_up {
  contact_id int
  id int [pk]
  timestamp timestamp
  entry_type entry_type
  "note" varchar
  status follow_up_status
  urgency urgency

  indexes {
    (id, contact_id) [unique]
  }
}

Enum entry_type {
  follow_up
  none
}

Enum follow_up_status {
  malfunction
  open
}

Enum urgency {
  urgent
  normal
}

Table case_attachment {
  id int [pk]
  doc_type varchar
  name varchar
  url varchar
  "key" varchar
  description varchar
  language varchar
  confirmed bool
  case_id int
}

Table case {
  follow_up_id int
  parking_item_id int
  id int [pk]
  status case_status
  case_no varchar [unique]
  answer_id int
  problem_id int
  protocol_id int
  parking_id int
  item_id int

  indexes {
    (id, follow_up_id) [unique]
    (id, answer_id) [unique]
    (id, problem_id) [unique]
    (id, protocol_id) [unique]
  }
}

Enum case_status {
  created
  malfunction
  open
  follow_up
  closed
}

Table audit_log {
  contact_id int
  id int [pk]
  case_id int
  timestamp timestamp
  status case_status
  protocol varchar
  communication varchar
  message varchar
  follow_up_time timestamp
}

Table parking_item {
  parking_id int
  item_id int
  indexes {
    (item_id, parking_id) [pk]
  }

}

Table item_protocol_problem_action_response {
  item_id int
  protocol_id int
  response_id int
  problem_id int
  action_id int

  indexes {
    (action_id,problem_id, response_id) [pk]
  }
}

Ref: case.(item_id, parking_id) > parking_item.(item_id,parking_id)

Ref: item_protocol_problem_action_response.action_id < action.id

Ref: item_protocol_problem_action_response.problem_id < problem.id

Ref: item_protocol_problem_action_response.response_id < action_response.id
Ref: item_protocol_problem_action_response.item_id < item.id
Ref: item_protocol_problem_action_response.protocol_id < protocol.id

Ref item_protocol: item.id <> protocol.id

Ref: "problem"."id" <> "item"."id"

Ref: "answer"."id" <> "item"."id"

Ref "protocol_problem": "protocol"."id" <> "problem"."id"

Ref "case_action_response": "case"."id" <> "action_response"."id"

Ref "problem_answer": "answer"."id" <> "problem"."id"

Ref: "parking"."city_id" < "city"."id"

Ref: "parking_pictures"."parking_id" < "parking"."id"

Ref: "parking_floorplan"."parking_id" < "parking"."id"

Ref: "audit_log"."case_id" < "case"."id"

Ref: "case_attachment"."case_id" < "case"."id"

Ref: "case"."follow_up_id" < "follow_up"."id"

Ref: "case"."protocol_id" < "protocol"."id"

Ref: "case"."problem_id" < "problem"."id"

Ref: "case"."answer_id" < "answer"."id"

Ref: "audit_log"."contact_id" < "contact"."id"

Ref: "follow_up"."contact_id" < "contact"."id"

Ref: "parking"."client_id" < "client"."id"

Ref: "user_preferences"."user_id" < "user"."id"

Ref: "city"."country_id" < "country"."id"

Ref: "problem"."action_id" < "action"."id"


Ref "parking_contact": "contact"."id" <> "parking"."id"

Ref: "answer_contact"."answer_id" < "answer"."id"

Ref: "answer_contact"."contact_id" < "contact"."id"

Ref: "parking_item"."parking_id" < "parking"."id"

Ref: "parking_item"."item_id" < "item"."id"