CodeIssuesDiscussionsWikiPull RequestsProjectsCommitsActionsReleasesContributorsPulse● GatesSecuritySettingsDeploymentsPipelineInsightsAgents✨ Explain✨ Ask AI✨ Workspace✨ Spec✨ Tests▓ Debt Map✨ NL Search🏛 Archaeology
Blame · Line-by-line history
0021_marketplace_and_apps.sql
Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.
| 06139e6 | 1 | -- Gluecron migration 0021: Block H — Marketplace + GitHub Apps equivalent. |
| 2 | -- | |
| 3 | -- H1 — App marketplace: creators register apps, users install them against | |
| 4 | -- their personal account / org / individual repo. Each install grants a | |
| 5 | -- concrete set of scopes (pull-read, issues-write, checks-write, etc.). | |
| 6 | -- | |
| 7 | -- H2 — Bot identities: every marketplace app gets an "app user" that can | |
| 8 | -- comment, open PRs, attach checks, etc. Bots authenticate with installation | |
| 9 | -- tokens tied to a single installation and a time-window. | |
| 10 | -- | |
| 11 | -- Tables: | |
| 12 | -- apps — app definitions (slug, description, webhook, permissions) | |
| 13 | -- app_installations — app X (repo | org | user) with granted permissions | |
| 14 | -- app_bots — one bot account per app (username ends with `[bot]`) | |
| 15 | -- app_install_tokens — short-lived bearer tokens scoped to a single install | |
| 16 | -- app_events — audit trail of installs, uninstalls, events delivered | |
| 17 | ||
| 18 | --> statement-breakpoint | |
| 19 | CREATE TABLE IF NOT EXISTS "apps" ( | |
| 20 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, | |
| 21 | "slug" text NOT NULL UNIQUE, -- url-safe, globally unique | |
| 22 | "name" text NOT NULL, | |
| 23 | "description" text NOT NULL DEFAULT '', | |
| 24 | "icon_url" text, | |
| 25 | "homepage_url" text, | |
| 26 | "webhook_url" text, -- where events are delivered | |
| 27 | "webhook_secret" text, -- HMAC secret; shown once at create | |
| 28 | "creator_id" uuid NOT NULL, | |
| 29 | "permissions" text NOT NULL DEFAULT '[]', -- JSON array of permission names | |
| 30 | "default_events" text NOT NULL DEFAULT '[]', -- JSON array: push, issues, pulls… | |
| 31 | "is_public" boolean NOT NULL DEFAULT true, -- listed in /marketplace? | |
| 32 | "created_at" timestamp DEFAULT now() NOT NULL, | |
| 33 | "updated_at" timestamp DEFAULT now() NOT NULL, | |
| 34 | CONSTRAINT "apps_creator_fk" FOREIGN KEY ("creator_id") REFERENCES "users"("id") ON DELETE cascade | |
| 35 | ); | |
| 36 | ||
| 37 | --> statement-breakpoint | |
| 38 | CREATE INDEX IF NOT EXISTS "apps_public_slug" ON "apps" ("is_public", "slug"); | |
| 39 | ||
| 40 | --> statement-breakpoint | |
| 41 | CREATE TABLE IF NOT EXISTS "app_installations" ( | |
| 42 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, | |
| 43 | "app_id" uuid NOT NULL, | |
| 44 | "installed_by" uuid NOT NULL, -- user who clicked install | |
| 45 | "target_type" text NOT NULL, -- user | org | repository | |
| 46 | "target_id" uuid NOT NULL, | |
| 47 | "granted_permissions" text NOT NULL DEFAULT '[]', -- JSON subset of app.permissions | |
| 48 | "suspended_at" timestamp, | |
| 49 | "created_at" timestamp DEFAULT now() NOT NULL, | |
| 50 | "uninstalled_at" timestamp, | |
| 51 | CONSTRAINT "app_installations_app_fk" FOREIGN KEY ("app_id") REFERENCES "apps"("id") ON DELETE cascade, | |
| 52 | CONSTRAINT "app_installations_user_fk" FOREIGN KEY ("installed_by") REFERENCES "users"("id") ON DELETE cascade | |
| 53 | ); | |
| 54 | ||
| 55 | --> statement-breakpoint | |
| 56 | CREATE INDEX IF NOT EXISTS "app_installations_app" ON "app_installations" ("app_id"); | |
| 57 | --> statement-breakpoint | |
| 58 | CREATE INDEX IF NOT EXISTS "app_installations_target" ON "app_installations" ("target_type", "target_id"); | |
| 59 | --> statement-breakpoint | |
| 60 | CREATE UNIQUE INDEX IF NOT EXISTS "app_installations_unique" ON "app_installations" ("app_id", "target_type", "target_id") WHERE "uninstalled_at" IS NULL; | |
| 61 | ||
| 62 | --> statement-breakpoint | |
| 63 | CREATE TABLE IF NOT EXISTS "app_bots" ( | |
| 64 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, | |
| 65 | "app_id" uuid NOT NULL UNIQUE, | |
| 66 | "username" text NOT NULL UNIQUE, -- `${app.slug}[bot]` | |
| 67 | "display_name" text NOT NULL, | |
| 68 | "avatar_url" text, | |
| 69 | "created_at" timestamp DEFAULT now() NOT NULL, | |
| 70 | CONSTRAINT "app_bots_app_fk" FOREIGN KEY ("app_id") REFERENCES "apps"("id") ON DELETE cascade | |
| 71 | ); | |
| 72 | ||
| 73 | --> statement-breakpoint | |
| 74 | CREATE TABLE IF NOT EXISTS "app_install_tokens" ( | |
| 75 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, | |
| 76 | "installation_id" uuid NOT NULL, | |
| 77 | "token_hash" text NOT NULL UNIQUE, -- sha256 of bearer | |
| 78 | "expires_at" timestamp NOT NULL, | |
| 79 | "created_at" timestamp DEFAULT now() NOT NULL, | |
| 80 | "revoked_at" timestamp, | |
| 81 | CONSTRAINT "app_install_tokens_inst_fk" FOREIGN KEY ("installation_id") REFERENCES "app_installations"("id") ON DELETE cascade | |
| 82 | ); | |
| 83 | ||
| 84 | --> statement-breakpoint | |
| 85 | CREATE INDEX IF NOT EXISTS "app_install_tokens_hash" ON "app_install_tokens" ("token_hash"); | |
| 86 | ||
| 87 | --> statement-breakpoint | |
| 88 | CREATE TABLE IF NOT EXISTS "app_events" ( | |
| 89 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, | |
| 90 | "app_id" uuid NOT NULL, | |
| 91 | "installation_id" uuid, | |
| 92 | "kind" text NOT NULL, -- installed | uninstalled | delivery_ok | delivery_fail | |
| 93 | "payload" text, -- JSON, first 2048 chars | |
| 94 | "response_status" integer, | |
| 95 | "created_at" timestamp DEFAULT now() NOT NULL, | |
| 96 | CONSTRAINT "app_events_app_fk" FOREIGN KEY ("app_id") REFERENCES "apps"("id") ON DELETE cascade | |
| 97 | ); | |
| 98 | ||
| 99 | --> statement-breakpoint | |
| 100 | CREATE INDEX IF NOT EXISTS "app_events_app_time" ON "app_events" ("app_id", "created_at"); |