Database Schema

Database schema #

You can find below the relational database structure.


-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Table Definition
CREATE TABLE "public"."cantons" (
    "term" varchar(255) NOT NULL,
    "text" varchar(255) NOT NULL,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("term")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Table Definition
CREATE TABLE "public"."chromosomes" (
    "name" varchar(255) NOT NULL,
    "length" int4 NOT NULL,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("name")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Table Definition
CREATE TABLE "public"."clinical_genders" (
    "term" varchar(255) NOT NULL,
    "text" varchar(255) NOT NULL,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("term")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Table Definition
CREATE TABLE "public"."clinical_significance_terms" (
    "term" varchar(255) NOT NULL,
    "text" varchar(255) NOT NULL,
    "count" int4 NOT NULL DEFAULT 0,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("term")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Table Definition
CREATE TABLE "public"."clinical_statuses" (
    "term" varchar(255) NOT NULL,
    "text" varchar(255) NOT NULL,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("term")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Table Definition
CREATE TABLE "public"."ethnicities" (
    "term" varchar(255) NOT NULL,
    "text" varchar(255) NOT NULL,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("term")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Table Definition
CREATE TABLE "public"."event_hpo" (
    "event_id" int8 NOT NULL,
    "hpo_id" int8 NOT NULL,
    "created_at" timestamp(0),
    "updated_at" timestamp(0)
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Table Definition
CREATE TABLE "public"."event_types" (
    "term" varchar(255) NOT NULL,
    "text" varchar(255) NOT NULL,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("term")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS events_id_seq;

-- Table Definition
CREATE TABLE "public"."events" (
    "id" int8 NOT NULL DEFAULT nextval('events_id_seq'::regclass),
    "history_id" int8 NOT NULL,
    "date" date NOT NULL,
    "event_type" varchar(255),
    "diagnosis_id" int8,
    "clinical_status" varchar(255),
    "genetic_investigation" varchar(255),
    "is_established" bool,
    "comment" varchar(255),
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("id")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS failed_jobs_id_seq;

-- Table Definition
CREATE TABLE "public"."failed_jobs" (
    "id" int8 NOT NULL DEFAULT nextval('failed_jobs_id_seq'::regclass),
    "uuid" varchar(255) NOT NULL,
    "connection" text NOT NULL,
    "queue" text NOT NULL,
    "payload" text NOT NULL,
    "exception" text NOT NULL,
    "failed_at" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Table Definition
CREATE TABLE "public"."genes" (
    "name" varchar(255) NOT NULL,
    "count" int4 NOT NULL DEFAULT 0,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("name")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Table Definition
CREATE TABLE "public"."genetic_investigations" (
    "term" varchar(255) NOT NULL,
    "text" varchar(255) NOT NULL,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("term")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS histories_id_seq;

-- Table Definition
CREATE TABLE "public"."histories" (
    "id" int8 NOT NULL DEFAULT nextval('histories_id_seq'::regclass),
    "patient_id" int8 NOT NULL,
    "clinical_indication_id" int8 NOT NULL,
    "index_patient" bool NOT NULL,
    "last_diagnosis_id" int8,
    "is_established" bool,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("id")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS hpo_id_seq;

-- Table Definition
CREATE TABLE "public"."hpo" (
    "id" int8 NOT NULL DEFAULT nextval('hpo_id_seq'::regclass),
    "HPO" varchar(255) NOT NULL,
    "name" varchar(255) NOT NULL,
    "category" varchar(255) NOT NULL,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("id")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS institutions_id_seq;

-- Table Definition
CREATE TABLE "public"."institutions" (
    "id" int8 NOT NULL DEFAULT nextval('institutions_id_seq'::regclass),
    "name" varchar(255) NOT NULL,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("id")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Table Definition
CREATE TABLE "public"."job_batches" (
    "id" varchar(255) NOT NULL,
    "name" varchar(255) NOT NULL,
    "total_jobs" int4 NOT NULL,
    "pending_jobs" int4 NOT NULL,
    "failed_jobs" int4 NOT NULL,
    "failed_job_ids" text NOT NULL,
    "options" text,
    "cancelled_at" int4,
    "created_at" int4 NOT NULL,
    "finished_at" int4,
    PRIMARY KEY ("id")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS migrations_id_seq;

-- Table Definition
CREATE TABLE "public"."migrations" (
    "id" int4 NOT NULL DEFAULT nextval('migrations_id_seq'::regclass),
    "migration" varchar(255) NOT NULL,
    "batch" int4 NOT NULL,
    PRIMARY KEY ("id")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS omim_id_seq;

-- Table Definition
CREATE TABLE "public"."omim" (
    "id" int8 NOT NULL DEFAULT nextval('omim_id_seq'::regclass),
    "name" varchar(255) NOT NULL,
    "alias" varchar(255) NOT NULL,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("id")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Table Definition
CREATE TABLE "public"."password_resets" (
    "email" varchar(255) NOT NULL,
    "token" varchar(255) NOT NULL,
    "created_at" timestamp(0)
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Table Definition
CREATE TABLE "public"."patient_user" (
    "patient_id" int8 NOT NULL,
    "user_id" int8 NOT NULL,
    "created_at" timestamp(0),
    "updated_at" timestamp(0)
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Table Definition
CREATE TABLE "public"."patient_variant" (
    "patient_id" int8 NOT NULL,
    "variant_id" int8 NOT NULL,
    "genotype" varchar(255) NOT NULL,
    "is_heterozygous" bool,
    "is_phased" bool,
    "qual" numeric(8,2),
    "filter" varchar(255),
    "info" text,
    "format" text NOT NULL,
    "data" text NOT NULL,
    "is_marked" bool NOT NULL DEFAULT false,
    "created_at" timestamp(0),
    "updated_at" timestamp(0)
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS patients_id_seq;

-- Table Definition
CREATE TABLE "public"."patients" (
    "id" int8 NOT NULL DEFAULT nextval('patients_id_seq'::regclass),
    "identifier" varchar(255) NOT NULL,
    "vcf_id" int8 NOT NULL,
    "institution_id" int8 NOT NULL,
    "ethnicity" varchar(255),
    "canton" varchar(255),
    "clinical_gender" varchar(255),
    "birth_year" int4,
    "chromosomal_sex" varchar(255),
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("id")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS publications_id_seq;

-- Table Definition
CREATE TABLE "public"."publications" (
    "id" int8 NOT NULL DEFAULT nextval('publications_id_seq'::regclass),
    "content" json NOT NULL,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("id")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Table Definition
CREATE TABLE "public"."user_variant" (
    "user_id" int8 NOT NULL,
    "variant_id" int8 NOT NULL,
    "created_at" timestamp(0),
    "updated_at" timestamp(0)
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS users_id_seq;

-- Table Definition
CREATE TABLE "public"."users" (
    "id" int8 NOT NULL DEFAULT nextval('users_id_seq'::regclass),
    "name" varchar(255) NOT NULL,
    "email" varchar(255) NOT NULL,
    "email_verified_at" timestamp(0),
    "password" varchar(255) NOT NULL,
    "remember_token" varchar(100),
    "institution_id" int8 NOT NULL,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("id")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Table Definition
CREATE TABLE "public"."variant_effects" (
    "term" varchar(255) NOT NULL,
    "text" varchar(255) NOT NULL,
    "count" int4 NOT NULL DEFAULT 0,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("term")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Table Definition
CREATE TABLE "public"."variant_types" (
    "term" varchar(255) NOT NULL,
    "text" varchar(255) NOT NULL,
    "count" int4 NOT NULL DEFAULT 0,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("term")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS variants_id_seq;

-- Table Definition
CREATE TABLE "public"."variants" (
    "id" int8 NOT NULL DEFAULT nextval('variants_id_seq'::regclass),
    "chromosome" varchar(255) NOT NULL,
    "pos" int4 NOT NULL,
    "external_id" varchar(255),
    "ref" text NOT NULL,
    "alt" text,
    "vep_annotations" jsonb,
    "vep_annotations_hash" text,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("id")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS vcf_reports_id_seq;

-- Table Definition
CREATE TABLE "public"."vcf_reports" (
    "id" int8 NOT NULL DEFAULT nextval('vcf_reports_id_seq'::regclass),
    "vcf_id" int8 NOT NULL,
    "has_error" bool NOT NULL,
    "errors" text NOT NULL,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("id")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS vcfs_id_seq;

-- Table Definition
CREATE TABLE "public"."vcfs" (
    "id" int8 NOT NULL DEFAULT nextval('vcfs_id_seq'::regclass),
    "institution_id" int8 NOT NULL,
    "import_batch_id" varchar(255),
    "original_name" varchar(255) NOT NULL,
    "file_path" text NOT NULL,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("id")
);

-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS vep_reports_id_seq;

-- Table Definition
CREATE TABLE "public"."vep_reports" (
    "id" int8 NOT NULL DEFAULT nextval('vep_reports_id_seq'::regclass),
    "vcf_id" int8 NOT NULL,
    "file_path" text NOT NULL,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    PRIMARY KEY ("id")
);

ALTER TABLE "public"."event_hpo" ADD FOREIGN KEY ("event_id") REFERENCES "public"."events"("id") ON DELETE CASCADE;
ALTER TABLE "public"."event_hpo" ADD FOREIGN KEY ("hpo_id") REFERENCES "public"."hpo"("id");
ALTER TABLE "public"."events" ADD FOREIGN KEY ("clinical_status") REFERENCES "public"."clinical_statuses"("term");
ALTER TABLE "public"."events" ADD FOREIGN KEY ("event_type") REFERENCES "public"."event_types"("term");
ALTER TABLE "public"."events" ADD FOREIGN KEY ("genetic_investigation") REFERENCES "public"."genetic_investigations"("term");
ALTER TABLE "public"."events" ADD FOREIGN KEY ("diagnosis_id") REFERENCES "public"."omim"("id");
ALTER TABLE "public"."events" ADD FOREIGN KEY ("history_id") REFERENCES "public"."histories"("id") ON DELETE CASCADE;
ALTER TABLE "public"."histories" ADD FOREIGN KEY ("clinical_indication_id") REFERENCES "public"."hpo"("id");
ALTER TABLE "public"."histories" ADD FOREIGN KEY ("last_diagnosis_id") REFERENCES "public"."omim"("id");
ALTER TABLE "public"."histories" ADD FOREIGN KEY ("patient_id") REFERENCES "public"."patients"("id");
ALTER TABLE "public"."patient_user" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users"("id");
ALTER TABLE "public"."patient_user" ADD FOREIGN KEY ("patient_id") REFERENCES "public"."patients"("id");
ALTER TABLE "public"."patient_variant" ADD FOREIGN KEY ("patient_id") REFERENCES "public"."patients"("id");
ALTER TABLE "public"."patient_variant" ADD FOREIGN KEY ("variant_id") REFERENCES "public"."variants"("id");
ALTER TABLE "public"."patients" ADD FOREIGN KEY ("ethnicity") REFERENCES "public"."ethnicities"("term");
ALTER TABLE "public"."patients" ADD FOREIGN KEY ("canton") REFERENCES "public"."cantons"("term");
ALTER TABLE "public"."patients" ADD FOREIGN KEY ("clinical_gender") REFERENCES "public"."clinical_genders"("term");
ALTER TABLE "public"."patients" ADD FOREIGN KEY ("institution_id") REFERENCES "public"."institutions"("id");
ALTER TABLE "public"."patients" ADD FOREIGN KEY ("vcf_id") REFERENCES "public"."vcfs"("id");
ALTER TABLE "public"."user_variant" ADD FOREIGN KEY ("user_id") REFERENCES "public"."users"("id");
ALTER TABLE "public"."user_variant" ADD FOREIGN KEY ("variant_id") REFERENCES "public"."variants"("id");
ALTER TABLE "public"."users" ADD FOREIGN KEY ("institution_id") REFERENCES "public"."institutions"("id");
ALTER TABLE "public"."variants" ADD FOREIGN KEY ("chromosome") REFERENCES "public"."chromosomes"("name");
ALTER TABLE "public"."vcf_reports" ADD FOREIGN KEY ("vcf_id") REFERENCES "public"."vcfs"("id");
ALTER TABLE "public"."vcfs" ADD FOREIGN KEY ("import_batch_id") REFERENCES "public"."job_batches"("id");
ALTER TABLE "public"."vcfs" ADD FOREIGN KEY ("institution_id") REFERENCES "public"."institutions"("id");
ALTER TABLE "public"."vep_reports" ADD FOREIGN KEY ("vcf_id") REFERENCES "public"."vcfs"("id");
Calendar August 31, 2022
Edit Edit this page