dhkatzandClaude Opus 5.5
b4f6fce9df
CI / test (push) Successful in 43s
The hub registers catalogs (hidden until a moderator approves them), hands out upload tokens, and records the revisions catalogs register after validating uploads: numbered, never changed, signed by the hub (its own Ed25519 key, published at /.well-known/t5de-keys.json). Creators edit product info (descriptions cleaned by an allowlist) and can retract revisions. People get products into their library, optionally staying on a revision; the proxy asks which revision to load and where. Contract in docs/products.md. Co-Authored-By: Claude Opus 5.5 <[email protected]>
72 lines
3.7 KiB
SQL
72 lines
3.7 KiB
SQL
CREATE TABLE "catalog" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"owner_id" text NOT NULL,
|
|
"name" text NOT NULL,
|
|
"url" text NOT NULL,
|
|
"contact" text NOT NULL,
|
|
"visibility" text DEFAULT 'public' NOT NULL,
|
|
"membership" text DEFAULT 'open' NOT NULL,
|
|
"status" text DEFAULT 'pending' NOT NULL,
|
|
"key_hash" text NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "hosting" (
|
|
"catalog_id" text NOT NULL,
|
|
"product_id" text NOT NULL,
|
|
"number" integer NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "hosting_catalog_id_product_id_number_pk" PRIMARY KEY("catalog_id","product_id","number")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "library" (
|
|
"user_id" text NOT NULL,
|
|
"product_id" text NOT NULL,
|
|
"stay_on" integer,
|
|
"added_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "library_user_id_product_id_pk" PRIMARY KEY("user_id","product_id")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "product" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"creator_id" text NOT NULL,
|
|
"name" text NOT NULL,
|
|
"description" text DEFAULT '' NOT NULL,
|
|
"rating" text NOT NULL,
|
|
"latest" integer DEFAULT 0 NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "product_revision" (
|
|
"product_id" text NOT NULL,
|
|
"number" integer NOT NULL,
|
|
"files" jsonb NOT NULL,
|
|
"rating" text NOT NULL,
|
|
"parent" bigint DEFAULT 0 NOT NULL,
|
|
"notes" text DEFAULT '' NOT NULL,
|
|
"token" text NOT NULL,
|
|
"uploaded_by" text,
|
|
"retracted" boolean DEFAULT false NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "product_revision_product_id_number_pk" PRIMARY KEY("product_id","number")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "signing_key" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"public_key" jsonb NOT NULL,
|
|
"private_key" jsonb NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE "catalog" ADD CONSTRAINT "catalog_owner_id_user_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "hosting" ADD CONSTRAINT "hosting_catalog_id_catalog_id_fk" FOREIGN KEY ("catalog_id") REFERENCES "public"."catalog"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "library" ADD CONSTRAINT "library_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "library" ADD CONSTRAINT "library_product_id_product_id_fk" FOREIGN KEY ("product_id") REFERENCES "public"."product"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "product" ADD CONSTRAINT "product_creator_id_user_id_fk" FOREIGN KEY ("creator_id") REFERENCES "public"."user"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "product_revision" ADD CONSTRAINT "product_revision_product_id_product_id_fk" FOREIGN KEY ("product_id") REFERENCES "public"."product"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "product_revision" ADD CONSTRAINT "product_revision_uploaded_by_user_id_fk" FOREIGN KEY ("uploaded_by") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "catalog_url" ON "catalog" USING btree ("url");--> statement-breakpoint
|
|
CREATE INDEX "hosting_product" ON "hosting" USING btree ("product_id");--> statement-breakpoint
|
|
CREATE INDEX "product_creator" ON "product" USING btree ("creator_id");--> statement-breakpoint
|
|
CREATE INDEX "product_created" ON "product" USING btree ("created_at"); |