556
Bn
bees-nest / files
bees-nest / drizzle / 0000_absurd_hairball.sql
2.3 KB
1CREATE TYPE "public"."url_status" AS ENUM('pending', 'processing', 'completed', 'failed');--> statement-breakpoint 2CREATE TABLE "domains" ( 3 "id" serial PRIMARY KEY NOT NULL, 4 "domain" text NOT NULL, 5 "robots_txt" text, 6 "last_crawled_at" timestamp, 7 "created_at" timestamp DEFAULT now(), 8 CONSTRAINT "domains_domain_unique" UNIQUE("domain") 9); 10--> statement-breakpoint 11CREATE TABLE "links" ( 12 "id" serial PRIMARY KEY NOT NULL, 13 "source_url_id" integer NOT NULL, 14 "target_url_id" integer NOT NULL, 15 "created_at" timestamp DEFAULT now() 16); 17--> statement-breakpoint 18CREATE TABLE "pages" ( 19 "id" serial PRIMARY KEY NOT NULL, 20 "url_id" integer NOT NULL, 21 "title" text, 22 "description" text, 23 "site_name" text, 24 "image" text, 25 "content" text, 26 "created_at" timestamp DEFAULT now() 27); 28--> statement-breakpoint 29CREATE TABLE "urls" ( 30 "id" serial PRIMARY KEY NOT NULL, 31 "url" text NOT NULL, 32 "domain_id" integer, 33 "status" "url_status" DEFAULT 'pending' NOT NULL, 34 "priority" integer DEFAULT 10 NOT NULL, 35 "depth" integer DEFAULT 0 NOT NULL, 36 "error_message" text, 37 "next_retry_at" timestamp, 38 "created_at" timestamp DEFAULT now(), 39 "updated_at" timestamp DEFAULT now(), 40 CONSTRAINT "urls_url_unique" UNIQUE("url") 41); 42--> statement-breakpoint 43ALTER TABLE "links" ADD CONSTRAINT "links_source_url_id_urls_id_fk" FOREIGN KEY ("source_url_id") REFERENCES "public"."urls"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint 44ALTER TABLE "links" ADD CONSTRAINT "links_target_url_id_urls_id_fk" FOREIGN KEY ("target_url_id") REFERENCES "public"."urls"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint 45ALTER TABLE "pages" ADD CONSTRAINT "pages_url_id_urls_id_fk" FOREIGN KEY ("url_id") REFERENCES "public"."urls"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint 46ALTER TABLE "urls" ADD CONSTRAINT "urls_domain_id_domains_id_fk" FOREIGN KEY ("domain_id") REFERENCES "public"."domains"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint 47CREATE UNIQUE INDEX "source_target_idx" ON "links" USING btree ("source_url_id","target_url_id");--> statement-breakpoint 48CREATE INDEX "url_id_idx" ON "pages" USING btree ("url_id");--> statement-breakpoint 49CREATE INDEX "status_idx" ON "urls" USING btree ("status");--> statement-breakpoint 50CREATE INDEX "priority_idx" ON "urls" USING btree ("priority");