xonecas.io
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");