diff options
Diffstat (limited to 'db/migrations_backup/0000_dusty_skaar.sql')
| -rw-r--r-- | db/migrations_backup/0000_dusty_skaar.sql | 51 |
1 files changed, 51 insertions, 0 deletions
diff --git a/db/migrations_backup/0000_dusty_skaar.sql b/db/migrations_backup/0000_dusty_skaar.sql new file mode 100644 index 00000000..1f56428d --- /dev/null +++ b/db/migrations_backup/0000_dusty_skaar.sql @@ -0,0 +1,51 @@ +CREATE TYPE "public"."user_domain" AS ENUM('evcp', 'partners');--> statement-breakpoint +CREATE TABLE "companies" ( + "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "companies_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), + "name" varchar(255) NOT NULL, + "taxID" integer NOT NULL, + "created_at" timestamp with time zone DEFAULT now() NOT NULL +); +--> statement-breakpoint +CREATE TABLE "tasks" ( + "id" varchar(30) PRIMARY KEY NOT NULL, + "code" varchar(128) DEFAULT concat('TASK-', to_char(nextval('tasks_code_seq'), 'FM0000')) NOT NULL, + "title" varchar(128), + "status" varchar(30) DEFAULT 'todo' NOT NULL, + "label" varchar(30) DEFAULT 'bug' NOT NULL, + "priority" varchar(30) DEFAULT 'low' NOT NULL, + "archived" boolean DEFAULT false NOT NULL, + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT current_timestamp, + CONSTRAINT "tasks_code_unique" UNIQUE("code") +); +--> statement-breakpoint +CREATE TABLE "otps" ( + "email" varchar(256) PRIMARY KEY NOT NULL, + "code" varchar(6) NOT NULL, + "created_at" timestamp DEFAULT now() NOT NULL, + "otpToken" varchar(512) NOT NULL, + "otp_expires" timestamp NOT NULL +); +--> statement-breakpoint +CREATE TABLE "user_roles" ( + "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "user_roles_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), + "user_id" integer, + "role" varchar(30) DEFAULT 'normal' NOT NULL +); +--> statement-breakpoint +CREATE TABLE "users" ( + "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "users_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), + "name" varchar(255) NOT NULL, + "email" varchar(255) NOT NULL, + "company_id" integer, + "domain" "user_domain" DEFAULT 'partners' NOT NULL, + "created_at" timestamp with time zone DEFAULT now() NOT NULL, + "image_url" varchar(1024), + CONSTRAINT "users_email_unique" UNIQUE("email") +); +--> statement-breakpoint +ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "users" ADD CONSTRAINT "users_company_id_companies_id_fk" FOREIGN KEY ("company_id") REFERENCES "public"."companies"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +CREATE VIEW "public"."user_view" AS (select "users"."id" as "user_id", "users"."name" as "user_name", "users"."email" as "user_email", "users"."image_url" as "user_image", "companies"."id" as "company_id", "companies"."name" as "company_name", + array_agg("user_roles"."role") + as "roles", "users"."created_at" as "created_at" from "users" left join "companies" on "users"."company_id" = "companies"."id" left join "user_roles" on "users"."id" = "user_roles"."user_id" group by "users"."id", "companies"."id");
\ No newline at end of file |