[READ-ONLY] Mirror of https://github.com/thoda-dev/shhh. Self-hostable zero-knowledge pastebin for secrets that expire on their own
docker end-to-end-encryption nuxt nuxtjs pastebin secrets selft-hosted zero-knowledge
Something went wrong. Try again.
SQL
at master
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157CREATE TYPE "public"."role" AS ENUM('user', 'admin', 'super_admin');--> statement-breakpointCREATE TYPE "public"."paste_kind" AS ENUM('text', 'file');--> statement-breakpointCREATE TYPE "public"."email_recipient_status" AS ENUM('sent', 'failed');--> statement-breakpointCREATE TYPE "public"."invitation_status" AS ENUM('pending', 'accepted', 'expired', 'revoked');--> statement-breakpointCREATE TABLE "users" ( "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 with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL, "two_factor_enabled" boolean DEFAULT false NOT NULL, "role" "role" DEFAULT 'user' NOT NULL, CONSTRAINT "users_email_unique" UNIQUE("email"));--> statement-breakpointCREATE TABLE "sessions" ( "id" text PRIMARY KEY NOT NULL, "expires_at" timestamp with time zone NOT NULL, "token" text NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone NOT NULL, "ip_address" text, "user_agent" text, "user_id" text NOT NULL, CONSTRAINT "sessions_token_unique" UNIQUE("token"));--> statement-breakpointCREATE TABLE "accounts" ( "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 with time zone, "refresh_token_expires_at" timestamp with time zone, "scope" text, "password" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone NOT NULL);--> statement-breakpointCREATE TABLE "verifications" ( "id" text PRIMARY KEY NOT NULL, "identifier" text NOT NULL, "value" text NOT NULL, "expires_at" timestamp with time zone NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL);--> statement-breakpointCREATE TABLE "two_factors" ( "id" text PRIMARY KEY NOT NULL, "secret" text NOT NULL, "backup_codes" text NOT NULL, "user_id" text NOT NULL, "verified" boolean DEFAULT true, "failed_verification_count" integer DEFAULT 0, "locked_until" timestamp with time zone);--> statement-breakpointCREATE TABLE "pastes" ( "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, "owner_id" text, "kind" "paste_kind" NOT NULL, "ciphertext" "bytea", "iv" "bytea", "file_blob" "bytea", "file_iv" "bytea", "file_name_enc" "bytea", "file_name_iv" "bytea", "file_mime" text, "file_size" integer, "password_protected" boolean DEFAULT false NOT NULL, "max_reads" integer, "read_count" integer DEFAULT 0 NOT NULL, "expires_at" timestamp with time zone NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "last_read_at" timestamp with time zone, CONSTRAINT "pastes_kind_payload_check" CHECK (("pastes"."kind" = 'text' AND "pastes"."ciphertext" IS NOT NULL AND "pastes"."file_blob" IS NULL) OR ("pastes"."kind" = 'file' AND "pastes"."file_blob" IS NOT NULL AND "pastes"."ciphertext" IS NULL)));--> statement-breakpointCREATE TABLE "paste_email_recipients" ( "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, "paste_id" uuid NOT NULL, "email" text NOT NULL, "status" "email_recipient_status" DEFAULT 'sent' NOT NULL, "sent_at" timestamp with time zone, "created_at" timestamp with time zone DEFAULT now() NOT NULL);--> statement-breakpointCREATE TABLE "user_stats" ( "user_id" text PRIMARY KEY NOT NULL, "active_pastes_count" integer DEFAULT 0 NOT NULL, "active_bytes_stored" bigint DEFAULT 0 NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL);--> statement-breakpointCREATE TABLE "app_stats" ( "id" integer PRIMARY KEY DEFAULT 1 NOT NULL, "active_pastes_count_anonymous" integer DEFAULT 0 NOT NULL, "active_pastes_count_authenticated" integer DEFAULT 0 NOT NULL, "active_bytes_stored" bigint DEFAULT 0 NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL, CONSTRAINT "app_stats_singleton_check" CHECK ("app_stats"."id" = 1));--> statement-breakpointCREATE TABLE "app_settings" ( "key" text PRIMARY KEY NOT NULL, "value" jsonb, "updated_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_by" text);--> statement-breakpointCREATE TABLE "invitations" ( "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, "email" text NOT NULL, "token" text NOT NULL, "invited_by" text, "status" "invitation_status" DEFAULT 'pending' NOT NULL, "expires_at" timestamp with time zone NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "accepted_at" timestamp with time zone, CONSTRAINT "invitations_token_unique" UNIQUE("token"));--> statement-breakpointCREATE TABLE "admin_audit_log" ( "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, "actor_id" text, "action" text NOT NULL, "target_id" text, "details" jsonb, "created_at" timestamp with time zone DEFAULT now() NOT NULL);--> statement-breakpointALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpointALTER TABLE "accounts" ADD CONSTRAINT "accounts_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpointALTER TABLE "two_factors" ADD CONSTRAINT "two_factors_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpointALTER TABLE "pastes" ADD CONSTRAINT "pastes_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpointALTER TABLE "paste_email_recipients" ADD CONSTRAINT "paste_email_recipients_paste_id_pastes_id_fk" FOREIGN KEY ("paste_id") REFERENCES "public"."pastes"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpointALTER TABLE "user_stats" ADD CONSTRAINT "user_stats_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpointALTER TABLE "app_settings" ADD CONSTRAINT "app_settings_updated_by_users_id_fk" FOREIGN KEY ("updated_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpointALTER TABLE "invitations" ADD CONSTRAINT "invitations_invited_by_users_id_fk" FOREIGN KEY ("invited_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpointALTER TABLE "admin_audit_log" ADD CONSTRAINT "admin_audit_log_actor_id_users_id_fk" FOREIGN KEY ("actor_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpointCREATE INDEX "sessions_user_id_idx" ON "sessions" USING btree ("user_id");--> statement-breakpointCREATE INDEX "accounts_user_id_idx" ON "accounts" USING btree ("user_id");--> statement-breakpointCREATE INDEX "verifications_identifier_idx" ON "verifications" USING btree ("identifier");--> statement-breakpointCREATE INDEX "two_factors_secret_idx" ON "two_factors" USING btree ("secret");--> statement-breakpointCREATE INDEX "two_factors_user_id_idx" ON "two_factors" USING btree ("user_id");--> statement-breakpointCREATE INDEX "pastes_owner_id_idx" ON "pastes" USING btree ("owner_id");--> statement-breakpointCREATE INDEX "pastes_expires_at_idx" ON "pastes" USING btree ("expires_at");--> statement-breakpointCREATE INDEX "paste_email_recipients_paste_id_idx" ON "paste_email_recipients" USING btree ("paste_id");--> statement-breakpointCREATE UNIQUE INDEX "invitations_pending_email_idx" ON "invitations" USING btree ("email") WHERE "invitations"."status" = 'pending';