mirror your GitHub repos to tangled.org automatically
1CREATE TABLE "atproto_session" (
2 "sub" text PRIMARY KEY NOT NULL,
3 "value_ciphertext" "bytea" NOT NULL,
4 "value_nonce" "bytea" NOT NULL,
5 "created_at" timestamp with time zone DEFAULT now() NOT NULL,
6 "updated_at" timestamp with time zone
7);
8--> statement-breakpoint
9CREATE TABLE "atproto_state" (
10 "key" text PRIMARY KEY NOT NULL,
11 "value_ciphertext" "bytea" NOT NULL,
12 "value_nonce" "bytea" NOT NULL,
13 "created_at" timestamp with time zone DEFAULT now() NOT NULL
14);
15--> statement-breakpoint
16CREATE TABLE "installation" (
17 "id" bigint PRIMARY KEY NOT NULL,
18 "account_login" text NOT NULL,
19 "account_id" bigint NOT NULL,
20 "account_type" text NOT NULL,
21 "suspended_at" timestamp with time zone,
22 "created_at" timestamp with time zone DEFAULT now() NOT NULL,
23 CONSTRAINT "installation_account_type_chk" CHECK ("installation"."account_type" in ('User','Organization'))
24);
25--> statement-breakpoint
26CREATE TABLE "job" (
27 "id" bigserial PRIMARY KEY NOT NULL,
28 "kind" text NOT NULL,
29 "payload" jsonb NOT NULL,
30 "attempts" integer DEFAULT 0 NOT NULL,
31 "run_after" timestamp with time zone DEFAULT now() NOT NULL,
32 "locked_by" text,
33 "locked_until" timestamp with time zone,
34 "status" text DEFAULT 'queued' NOT NULL,
35 "last_error" text,
36 "created_at" timestamp with time zone DEFAULT now() NOT NULL,
37 "updated_at" timestamp with time zone,
38 CONSTRAINT "job_status_chk" CHECK ("job"."status" in ('queued','running','done','failed'))
39);
40--> statement-breakpoint
41CREATE TABLE "repo_mapping" (
42 "id" bigserial PRIMARY KEY NOT NULL,
43 "installation_id" bigint NOT NULL,
44 "github_repo_id" bigint NOT NULL,
45 "github_full_name" text NOT NULL,
46 "tangled_repo_did" text,
47 "tangled_full_name" text,
48 "knot" text,
49 "primary_side" text DEFAULT 'github' NOT NULL,
50 "last_synced_refs" jsonb DEFAULT '{}'::jsonb NOT NULL,
51 "status" text DEFAULT 'pending' NOT NULL,
52 "last_error" text,
53 "disabled_at" timestamp with time zone,
54 "created_at" timestamp with time zone DEFAULT now() NOT NULL,
55 "updated_at" timestamp with time zone,
56 CONSTRAINT "repo_mapping_installation_repo_unq" UNIQUE("installation_id","github_repo_id"),
57 CONSTRAINT "repo_mapping_primary_side_chk" CHECK ("repo_mapping"."primary_side" in ('github','tangled')),
58 CONSTRAINT "repo_mapping_status_chk" CHECK ("repo_mapping"."status" in ('pending','enrolling','active','error'))
59);
60--> statement-breakpoint
61CREATE TABLE "ssh_key" (
62 "id" bigserial PRIMARY KEY NOT NULL,
63 "installation_id" bigint NOT NULL,
64 "did" text NOT NULL,
65 "public_key" text NOT NULL,
66 "private_key_ciphertext" "bytea" NOT NULL,
67 "private_key_nonce" "bytea" NOT NULL,
68 "tangled_key_rkey" text,
69 "created_at" timestamp with time zone DEFAULT now() NOT NULL,
70 "rotated_at" timestamp with time zone,
71 CONSTRAINT "ssh_key_installation_did_unq" UNIQUE("installation_id","did")
72);
73--> statement-breakpoint
74CREATE TABLE "user_identity" (
75 "did" text PRIMARY KEY NOT NULL,
76 "handle" text,
77 "installation_id" bigint,
78 "created_at" timestamp with time zone DEFAULT now() NOT NULL,
79 "updated_at" timestamp with time zone
80);
81--> statement-breakpoint
82CREATE TABLE "webhook_event" (
83 "delivery_id" uuid PRIMARY KEY NOT NULL,
84 "source" text NOT NULL,
85 "event" text NOT NULL,
86 "received_at" timestamp with time zone DEFAULT now() NOT NULL,
87 "processed_at" timestamp with time zone,
88 CONSTRAINT "webhook_event_source_chk" CHECK ("webhook_event"."source" in ('github','tangled'))
89);
90--> statement-breakpoint
91ALTER TABLE "repo_mapping" ADD CONSTRAINT "repo_mapping_installation_id_installation_id_fk" FOREIGN KEY ("installation_id") REFERENCES "public"."installation"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
92ALTER TABLE "ssh_key" ADD CONSTRAINT "ssh_key_installation_id_installation_id_fk" FOREIGN KEY ("installation_id") REFERENCES "public"."installation"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
93ALTER TABLE "user_identity" ADD CONSTRAINT "user_identity_installation_id_installation_id_fk" FOREIGN KEY ("installation_id") REFERENCES "public"."installation"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
94CREATE INDEX "job_status_run_after_idx" ON "job" USING btree ("status","run_after");--> statement-breakpoint
95CREATE INDEX "repo_mapping_installation_idx" ON "repo_mapping" USING btree ("installation_id");--> statement-breakpoint
96CREATE INDEX "ssh_key_installation_idx" ON "ssh_key" USING btree ("installation_id");--> statement-breakpoint
97CREATE INDEX "user_identity_installation_idx" ON "user_identity" USING btree ("installation_id");