diff options
Diffstat (limited to 'db/migrations/0001_robust_landau.sql')
| -rw-r--r-- | db/migrations/0001_robust_landau.sql | 32 |
1 files changed, 32 insertions, 0 deletions
diff --git a/db/migrations/0001_robust_landau.sql b/db/migrations/0001_robust_landau.sql new file mode 100644 index 00000000..94368814 --- /dev/null +++ b/db/migrations/0001_robust_landau.sql @@ -0,0 +1,32 @@ +CREATE TABLE "permissions" ( + "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "permissions_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), + "permission_key" text NOT NULL, + "description" text, + "created_at" timestamp DEFAULT now() +); +--> statement-breakpoint +CREATE TABLE "role_permissions" ( + "role_id" integer NOT NULL, + "permission_id" integer NOT NULL +); +--> statement-breakpoint +CREATE TABLE "roles" ( + "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "roles_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), + "name" text NOT NULL, + "created_at" timestamp DEFAULT now() +); +--> statement-breakpoint +DROP VIEW "public"."user_view";--> statement-breakpoint +ALTER TABLE "user_roles" DROP CONSTRAINT "user_roles_user_id_users_id_fk"; +--> statement-breakpoint +ALTER TABLE "user_roles" ALTER COLUMN "user_id" SET NOT NULL;--> statement-breakpoint +ALTER TABLE "user_roles" ADD COLUMN "role_id" integer NOT NULL;--> statement-breakpoint +ALTER TABLE "role_permissions" ADD CONSTRAINT "role_permissions_role_id_roles_id_fk" FOREIGN KEY ("role_id") REFERENCES "public"."roles"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "role_permissions" ADD CONSTRAINT "role_permissions_permission_id_permissions_id_fk" FOREIGN KEY ("permission_id") REFERENCES "public"."permissions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_role_id_roles_id_fk" FOREIGN KEY ("role_id") REFERENCES "public"."roles"("id") ON DELETE cascade ON UPDATE no action;--> 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 cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "user_roles" DROP COLUMN "id";--> statement-breakpoint +ALTER TABLE "user_roles" DROP COLUMN "role";--> 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("roles"."name") + 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" left join "roles" on "user_roles"."role_id" = "roles"."id" group by "users"."id", "companies"."id");
\ No newline at end of file |