mirror of
https://github.com/temetro/temetro.git
synced 2026-07-28 12:48:58 +00:00
Claude
9dabe2f5d2
Implements the first temetro backend: an Express 5 API on Postgres via Drizzle ORM, with authentication and multi-tenant clinics powered by Better Auth. - Auth: email/password with required email verification, password reset, rate limiting, CSRF/trusted-origins, secure cookies, session audit hook. - Organizations (clinics) with RBAC (owner/admin/member/viewer) and an extended `patient` permission set; member invitations by email. - Org-scoped patient records mirroring the frontend Patient shape, with CRUD endpoints gated by permission (read/write/delete). - Email helper logs links to the console when SMTP is unset (zero-setup local dev); Dockerfile + docker-compose (db + backend + frontend) with migrations applied on startup and a configurable Postgres host port. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
183 lines
8.5 KiB
SQL
183 lines
8.5 KiB
SQL
CREATE TABLE "account" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"account_id" text NOT NULL,
|
|
"provider_id" text NOT NULL,
|
|
"user_id" text NOT NULL,
|
|
"access_token" text,
|
|
"refresh_token" text,
|
|
"id_token" text,
|
|
"access_token_expires_at" timestamp,
|
|
"refresh_token_expires_at" timestamp,
|
|
"scope" text,
|
|
"password" text,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "invitation" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"organization_id" text NOT NULL,
|
|
"email" text NOT NULL,
|
|
"role" text,
|
|
"status" text DEFAULT 'pending' NOT NULL,
|
|
"expires_at" timestamp NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"inviter_id" text NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "member" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"organization_id" text NOT NULL,
|
|
"user_id" text NOT NULL,
|
|
"role" text DEFAULT 'member' NOT NULL,
|
|
"created_at" timestamp NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "organization" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"name" text NOT NULL,
|
|
"slug" text NOT NULL,
|
|
"logo" text,
|
|
"created_at" timestamp NOT NULL,
|
|
"metadata" text,
|
|
CONSTRAINT "organization_slug_unique" UNIQUE("slug")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "rate_limit" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"key" text NOT NULL,
|
|
"count" integer NOT NULL,
|
|
"last_request" bigint NOT NULL,
|
|
CONSTRAINT "rate_limit_key_unique" UNIQUE("key")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "session" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"expires_at" timestamp NOT NULL,
|
|
"token" text NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp NOT NULL,
|
|
"ip_address" text,
|
|
"user_agent" text,
|
|
"user_id" text NOT NULL,
|
|
"active_organization_id" text,
|
|
CONSTRAINT "session_token_unique" UNIQUE("token")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "user" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"name" text NOT NULL,
|
|
"email" text NOT NULL,
|
|
"email_verified" boolean DEFAULT false NOT NULL,
|
|
"image" text,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL,
|
|
CONSTRAINT "user_email_unique" UNIQUE("email")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "verification" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"identifier" text NOT NULL,
|
|
"value" text NOT NULL,
|
|
"expires_at" timestamp NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "patient_allergies" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"patient_id" uuid NOT NULL,
|
|
"position" integer DEFAULT 0 NOT NULL,
|
|
"substance" text NOT NULL,
|
|
"reaction" text NOT NULL,
|
|
"severity" text NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "patient_encounters" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"patient_id" uuid NOT NULL,
|
|
"position" integer DEFAULT 0 NOT NULL,
|
|
"date" text NOT NULL,
|
|
"type" text NOT NULL,
|
|
"provider" text NOT NULL,
|
|
"summary" text NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "patient_labs" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"patient_id" uuid NOT NULL,
|
|
"position" integer DEFAULT 0 NOT NULL,
|
|
"name" text NOT NULL,
|
|
"value" text NOT NULL,
|
|
"flag" text NOT NULL,
|
|
"taken_at" text NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "patient_medications" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"patient_id" uuid NOT NULL,
|
|
"position" integer DEFAULT 0 NOT NULL,
|
|
"name" text NOT NULL,
|
|
"dose" text NOT NULL,
|
|
"frequency" text NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "patients" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"organization_id" text NOT NULL,
|
|
"file_number" text NOT NULL,
|
|
"name" text NOT NULL,
|
|
"age" integer NOT NULL,
|
|
"sex" text NOT NULL,
|
|
"pcp" text NOT NULL,
|
|
"status" text NOT NULL,
|
|
"initials" text NOT NULL,
|
|
"alerts" jsonb NOT NULL,
|
|
"vitals_bp" text NOT NULL,
|
|
"vitals_hr" text NOT NULL,
|
|
"vitals_temp" text NOT NULL,
|
|
"vitals_spo2" text NOT NULL,
|
|
"vitals_taken_at" text NOT NULL,
|
|
"vitals_trend" jsonb NOT NULL,
|
|
"lab_trend" jsonb NOT NULL,
|
|
"created_by" text,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "patient_problems" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"patient_id" uuid NOT NULL,
|
|
"position" integer DEFAULT 0 NOT NULL,
|
|
"label" text NOT NULL,
|
|
"since" text NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE "account" ADD CONSTRAINT "account_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "invitation" ADD CONSTRAINT "invitation_organization_id_organization_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organization"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "invitation" ADD CONSTRAINT "invitation_inviter_id_user_id_fk" FOREIGN KEY ("inviter_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "member" ADD CONSTRAINT "member_organization_id_organization_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organization"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "member" ADD CONSTRAINT "member_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "session" ADD CONSTRAINT "session_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "patient_allergies" ADD CONSTRAINT "patient_allergies_patient_id_patients_id_fk" FOREIGN KEY ("patient_id") REFERENCES "public"."patients"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "patient_encounters" ADD CONSTRAINT "patient_encounters_patient_id_patients_id_fk" FOREIGN KEY ("patient_id") REFERENCES "public"."patients"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "patient_labs" ADD CONSTRAINT "patient_labs_patient_id_patients_id_fk" FOREIGN KEY ("patient_id") REFERENCES "public"."patients"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "patient_medications" ADD CONSTRAINT "patient_medications_patient_id_patients_id_fk" FOREIGN KEY ("patient_id") REFERENCES "public"."patients"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "patients" ADD CONSTRAINT "patients_organization_id_organization_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organization"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "patients" ADD CONSTRAINT "patients_created_by_user_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "patient_problems" ADD CONSTRAINT "patient_problems_patient_id_patients_id_fk" FOREIGN KEY ("patient_id") REFERENCES "public"."patients"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
CREATE INDEX "account_userId_idx" ON "account" USING btree ("user_id");--> statement-breakpoint
|
|
CREATE INDEX "invitation_organizationId_idx" ON "invitation" USING btree ("organization_id");--> statement-breakpoint
|
|
CREATE INDEX "invitation_email_idx" ON "invitation" USING btree ("email");--> statement-breakpoint
|
|
CREATE INDEX "member_organizationId_idx" ON "member" USING btree ("organization_id");--> statement-breakpoint
|
|
CREATE INDEX "member_userId_idx" ON "member" USING btree ("user_id");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "organization_slug_uidx" ON "organization" USING btree ("slug");--> statement-breakpoint
|
|
CREATE INDEX "session_userId_idx" ON "session" USING btree ("user_id");--> statement-breakpoint
|
|
CREATE INDEX "verification_identifier_idx" ON "verification" USING btree ("identifier");--> statement-breakpoint
|
|
CREATE INDEX "allergies_patient_idx" ON "patient_allergies" USING btree ("patient_id");--> statement-breakpoint
|
|
CREATE INDEX "encounters_patient_idx" ON "patient_encounters" USING btree ("patient_id");--> statement-breakpoint
|
|
CREATE INDEX "labs_patient_idx" ON "patient_labs" USING btree ("patient_id");--> statement-breakpoint
|
|
CREATE INDEX "medications_patient_idx" ON "patient_medications" USING btree ("patient_id");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "patients_org_file_uidx" ON "patients" USING btree ("organization_id","file_number");--> statement-breakpoint
|
|
CREATE INDEX "patients_org_idx" ON "patients" USING btree ("organization_id");--> statement-breakpoint
|
|
CREATE INDEX "problems_patient_idx" ON "patient_problems" USING btree ("patient_id"); |