roost.moe
The recipes.blue monorepo
recipes.blue
recipes
appview
atproto
hayden.moe
1CREATE TABLE IF NOT EXISTS "profiles" (
2 "uri" text GENERATED ALWAYS AS ('at://' || "profiles"."did" || '/blue.recipes.actor.profile/self') STORED,
3 "cid" text NOT NULL,
4 "did" text PRIMARY KEY NOT NULL,
5 "ingested_at" text DEFAULT CURRENT_TIMESTAMP NOT NULL,
6 "display_name" varchar(640) NOT NULL,
7 "description" varchar(2500),
8 "pronouns" varchar(200),
9 "website" text,
10 "avatar" text,
11 "banner" text,
12 "created_at" text NOT NULL
13);
14--> statement-breakpoint
15CREATE TABLE IF NOT EXISTS "recipes" (
16 "uri" text GENERATED ALWAYS AS ('at://' || "recipes"."author_did" || '/blue.recipes.feed.recipe/' || "recipes"."rkey") STORED,
17 "cid" text NOT NULL,
18 "author_did" text NOT NULL,
19 "rkey" text NOT NULL,
20 "image" text,
21 "title" text NOT NULL,
22 "time" integer DEFAULT 0 NOT NULL,
23 "serves" integer,
24 "description" text,
25 "ingredients" jsonb NOT NULL,
26 "ingredients_count" integer GENERATED ALWAYS AS (jsonb_array_length("recipes"."ingredients")) STORED,
27 "steps" jsonb NOT NULL,
28 "steps_count" integer GENERATED ALWAYS AS (jsonb_array_length("recipes"."steps")) STORED,
29 "created_at" text NOT NULL,
30 "ingested_at" text DEFAULT CURRENT_TIMESTAMP NOT NULL,
31 CONSTRAINT "recipes_author_did_rkey_pk" PRIMARY KEY("author_did","rkey")
32);
33--> statement-breakpoint
34DO $$ BEGIN
35 ALTER TABLE "recipes" ADD CONSTRAINT "recipes_author_did_profiles_did_fk" FOREIGN KEY ("author_did") REFERENCES "public"."profiles"("did") ON DELETE cascade ON UPDATE no action;
36EXCEPTION
37 WHEN duplicate_object THEN null;
38END $$;
39--> statement-breakpoint
40CREATE INDEX IF NOT EXISTS "profiles_cid_idx" ON "profiles" USING btree ("cid");--> statement-breakpoint
41CREATE INDEX IF NOT EXISTS "profiles_cat_idx" ON "profiles" USING btree ("created_at");--> statement-breakpoint
42CREATE INDEX IF NOT EXISTS "profiles_iat_idx" ON "profiles" USING btree ("ingested_at");--> statement-breakpoint
43CREATE INDEX IF NOT EXISTS "recipes_title_idx" ON "recipes" USING btree ("title");--> statement-breakpoint
44CREATE INDEX IF NOT EXISTS "recipes_cid_idx" ON "recipes" USING btree ("cid");--> statement-breakpoint
45CREATE INDEX IF NOT EXISTS "recipes_cat_idx" ON "recipes" USING btree ("created_at");--> statement-breakpoint
46CREATE INDEX IF NOT EXISTS "recipes_iat_idx" ON "recipes" USING btree ("ingested_at");