gui.do
Sifa professional network API (Fastify, AT Protocol, Jetstream)
sifa.id/
1-- Catch-up migration: migration 0005 partially applied (created certifications
2-- and courses, then failed on pre-existing external_account_verifications).
3-- This creates remaining tables and ensures all FK constraints exist.
4
5CREATE TABLE IF NOT EXISTS "certifications" (
6 "did" text NOT NULL,
7 "rkey" text NOT NULL,
8 "name" text NOT NULL,
9 "authority" text,
10 "credential_id" text,
11 "credential_url" text,
12 "issued_at" text,
13 "expires_at" text,
14 "created_at" timestamp with time zone NOT NULL,
15 "indexed_at" timestamp with time zone DEFAULT now() NOT NULL,
16 CONSTRAINT "certifications_did_rkey_pk" PRIMARY KEY("did","rkey")
17);
18--> statement-breakpoint
19CREATE TABLE IF NOT EXISTS "courses" (
20 "did" text NOT NULL,
21 "rkey" text NOT NULL,
22 "name" text NOT NULL,
23 "number" text,
24 "institution" text,
25 "created_at" timestamp with time zone NOT NULL,
26 "indexed_at" timestamp with time zone DEFAULT now() NOT NULL,
27 CONSTRAINT "courses_did_rkey_pk" PRIMARY KEY("did","rkey")
28);
29--> statement-breakpoint
30CREATE TABLE IF NOT EXISTS "external_account_verifications" (
31 "did" text NOT NULL,
32 "url" text NOT NULL,
33 "verified" boolean DEFAULT false NOT NULL,
34 "verified_via" text,
35 "checked_at" timestamp with time zone DEFAULT now() NOT NULL,
36 CONSTRAINT "external_account_verifications_did_url_pk" PRIMARY KEY("did","url")
37);
38--> statement-breakpoint
39CREATE TABLE IF NOT EXISTS "external_accounts" (
40 "did" text NOT NULL,
41 "rkey" text NOT NULL,
42 "platform" text NOT NULL,
43 "url" text NOT NULL,
44 "label" text,
45 "feed_url" text,
46 "created_at" timestamp with time zone NOT NULL,
47 "indexed_at" timestamp with time zone DEFAULT now() NOT NULL,
48 CONSTRAINT "external_accounts_did_rkey_pk" PRIMARY KEY("did","rkey")
49);
50--> statement-breakpoint
51CREATE TABLE IF NOT EXISTS "honors" (
52 "did" text NOT NULL,
53 "rkey" text NOT NULL,
54 "title" text NOT NULL,
55 "issuer" text,
56 "description" text,
57 "awarded_at" text,
58 "created_at" timestamp with time zone NOT NULL,
59 "indexed_at" timestamp with time zone DEFAULT now() NOT NULL,
60 CONSTRAINT "honors_did_rkey_pk" PRIMARY KEY("did","rkey")
61);
62--> statement-breakpoint
63CREATE TABLE IF NOT EXISTS "languages" (
64 "did" text NOT NULL,
65 "rkey" text NOT NULL,
66 "name" text NOT NULL,
67 "proficiency" text,
68 "created_at" timestamp with time zone NOT NULL,
69 "indexed_at" timestamp with time zone DEFAULT now() NOT NULL,
70 CONSTRAINT "languages_did_rkey_pk" PRIMARY KEY("did","rkey")
71);
72--> statement-breakpoint
73CREATE TABLE IF NOT EXISTS "projects" (
74 "did" text NOT NULL,
75 "rkey" text NOT NULL,
76 "name" text NOT NULL,
77 "description" text,
78 "url" text,
79 "started_at" text,
80 "ended_at" text,
81 "created_at" timestamp with time zone NOT NULL,
82 "indexed_at" timestamp with time zone DEFAULT now() NOT NULL,
83 CONSTRAINT "projects_did_rkey_pk" PRIMARY KEY("did","rkey")
84);
85--> statement-breakpoint
86CREATE TABLE IF NOT EXISTS "publications" (
87 "did" text NOT NULL,
88 "rkey" text NOT NULL,
89 "title" text NOT NULL,
90 "publisher" text,
91 "url" text,
92 "description" text,
93 "published_at" text,
94 "created_at" timestamp with time zone NOT NULL,
95 "indexed_at" timestamp with time zone DEFAULT now() NOT NULL,
96 CONSTRAINT "publications_did_rkey_pk" PRIMARY KEY("did","rkey")
97);
98--> statement-breakpoint
99CREATE TABLE IF NOT EXISTS "volunteering" (
100 "did" text NOT NULL,
101 "rkey" text NOT NULL,
102 "organization" text NOT NULL,
103 "role" text,
104 "cause" text,
105 "description" text,
106 "started_at" text,
107 "ended_at" text,
108 "created_at" timestamp with time zone NOT NULL,
109 "indexed_at" timestamp with time zone DEFAULT now() NOT NULL,
110 CONSTRAINT "volunteering_did_rkey_pk" PRIMARY KEY("did","rkey")
111);
112--> statement-breakpoint
113ALTER TABLE "profiles" ADD COLUMN IF NOT EXISTS "display_name" text;--> statement-breakpoint
114ALTER TABLE "profiles" ADD COLUMN IF NOT EXISTS "avatar_url" text;--> statement-breakpoint
115DO $$ BEGIN
116 ALTER TABLE "certifications" ADD CONSTRAINT "certifications_did_profiles_did_fk" FOREIGN KEY ("did") REFERENCES "public"."profiles"("did") ON DELETE cascade ON UPDATE no action;
117EXCEPTION WHEN duplicate_object THEN NULL;
118END $$;--> statement-breakpoint
119DO $$ BEGIN
120 ALTER TABLE "courses" ADD CONSTRAINT "courses_did_profiles_did_fk" FOREIGN KEY ("did") REFERENCES "public"."profiles"("did") ON DELETE cascade ON UPDATE no action;
121EXCEPTION WHEN duplicate_object THEN NULL;
122END $$;--> statement-breakpoint
123DO $$ BEGIN
124 ALTER TABLE "external_accounts" ADD CONSTRAINT "external_accounts_did_profiles_did_fk" FOREIGN KEY ("did") REFERENCES "public"."profiles"("did") ON DELETE cascade ON UPDATE no action;
125EXCEPTION WHEN duplicate_object THEN NULL;
126END $$;--> statement-breakpoint
127DO $$ BEGIN
128 ALTER TABLE "honors" ADD CONSTRAINT "honors_did_profiles_did_fk" FOREIGN KEY ("did") REFERENCES "public"."profiles"("did") ON DELETE cascade ON UPDATE no action;
129EXCEPTION WHEN duplicate_object THEN NULL;
130END $$;--> statement-breakpoint
131DO $$ BEGIN
132 ALTER TABLE "languages" ADD CONSTRAINT "languages_did_profiles_did_fk" FOREIGN KEY ("did") REFERENCES "public"."profiles"("did") ON DELETE cascade ON UPDATE no action;
133EXCEPTION WHEN duplicate_object THEN NULL;
134END $$;--> statement-breakpoint
135DO $$ BEGIN
136 ALTER TABLE "projects" ADD CONSTRAINT "projects_did_profiles_did_fk" FOREIGN KEY ("did") REFERENCES "public"."profiles"("did") ON DELETE cascade ON UPDATE no action;
137EXCEPTION WHEN duplicate_object THEN NULL;
138END $$;--> statement-breakpoint
139DO $$ BEGIN
140 ALTER TABLE "publications" ADD CONSTRAINT "publications_did_profiles_did_fk" FOREIGN KEY ("did") REFERENCES "public"."profiles"("did") ON DELETE cascade ON UPDATE no action;
141EXCEPTION WHEN duplicate_object THEN NULL;
142END $$;--> statement-breakpoint
143DO $$ BEGIN
144 ALTER TABLE "volunteering" ADD CONSTRAINT "volunteering_did_profiles_did_fk" FOREIGN KEY ("did") REFERENCES "public"."profiles"("did") ON DELETE cascade ON UPDATE no action;
145EXCEPTION WHEN duplicate_object THEN NULL;
146END $$;