Experiments DB Dev
50 lines
1.4 KiB
SQL
50 lines
1.4 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "experiments" (
|
|
"id" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "experiments_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "animals" (
|
|
"id" TEXT NOT NULL,
|
|
"experiment_id" TEXT NOT NULL,
|
|
"animal_id_string" TEXT NOT NULL,
|
|
"animal_name" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "animals_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "daily_statuses" (
|
|
"id" TEXT NOT NULL,
|
|
"animal_id" TEXT NOT NULL,
|
|
"date" DATE NOT NULL,
|
|
"experiment_description" TEXT,
|
|
"vitals" TEXT,
|
|
"treatment" TEXT,
|
|
"notes" TEXT,
|
|
|
|
CONSTRAINT "daily_statuses_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "audit_logs" (
|
|
"id" TEXT NOT NULL,
|
|
"table_name" TEXT NOT NULL,
|
|
"record_id" TEXT NOT NULL,
|
|
"action" TEXT NOT NULL,
|
|
"changes" JSONB NOT NULL,
|
|
"timestamp" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "audit_logs_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "animals" ADD CONSTRAINT "animals_experiment_id_fkey" FOREIGN KEY ("experiment_id") REFERENCES "experiments"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "daily_statuses" ADD CONSTRAINT "daily_statuses_animal_id_fkey" FOREIGN KEY ("animal_id") REFERENCES "animals"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|