CodeIssuesDiscussionsWikiPull RequestsProjectsCommitsActionsReleasesContributorsPulse● GatesSecuritySettingsDeploymentsPipelineInsightsAgents✨ Explain✨ Ask AI✨ Workspace✨ Spec✨ Tests▓ Debt Map✨ NL Search🏛 Archaeology
Blame · Line-by-line history
0029_security_advisories.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.
| f60ccde | 1 | -- Gluecron migration 0029: security advisories + dependency alerts. |
| 2 | -- | |
| 3 | -- J2 — Per-package CVE-style advisories mapped against J1's `repo_dependencies`. | |
| 4 | -- Builds the "Dependabot alerts" / "GitHub security advisory" feature. | |
| 5 | -- | |
| 6 | -- `security_advisories` is the master rule list — populated initially from a | |
| 7 | -- seeded set in `src/lib/advisories.ts`, extensible via admin import later. | |
| 8 | -- `repo_advisory_alerts` is the per-repo match state: one row per (repo, | |
| 9 | -- advisory) when we detect a dependency matches the advisory's affected range. | |
| 10 | -- An alert can be dismissed (ignored), auto-closed when the dep goes away, or | |
| 11 | -- marked fixed when the dep's version moves past the fixed_version. | |
| 12 | ||
| 13 | --> statement-breakpoint | |
| 14 | CREATE TABLE IF NOT EXISTS "security_advisories" ( | |
| 15 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(), | |
| 16 | "ghsa_id" text UNIQUE, -- GitHub Security Advisory ID, e.g. GHSA-xxxx | |
| 17 | "cve_id" text, -- CVE-YYYY-NNNN when assigned | |
| 18 | "summary" text NOT NULL, | |
| 19 | "severity" text NOT NULL DEFAULT 'moderate', -- low | moderate | high | critical | |
| 20 | "ecosystem" text NOT NULL, -- npm | pypi | go | rubygems | cargo | composer | |
| 21 | "package_name" text NOT NULL, | |
| 22 | "affected_range" text NOT NULL, -- "<1.2.3" | ">=2.0.0 <2.5.1" | |
| 23 | "fixed_version" text, -- "1.2.3" — suggestion to upgrade to | |
| 24 | "reference_url" text, -- link to upstream advisory / CVE | |
| 25 | "published_at" timestamp NOT NULL DEFAULT now() | |
| 26 | ); | |
| 27 | ||
| 28 | --> statement-breakpoint | |
| 29 | CREATE INDEX IF NOT EXISTS "security_advisories_pkg_idx" | |
| 30 | ON "security_advisories" ("ecosystem", "package_name"); | |
| 31 | ||
| 32 | --> statement-breakpoint | |
| 33 | CREATE TABLE IF NOT EXISTS "repo_advisory_alerts" ( | |
| 34 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(), | |
| 35 | "repository_id" uuid NOT NULL REFERENCES "repositories"("id") ON DELETE CASCADE, | |
| 36 | "advisory_id" uuid NOT NULL REFERENCES "security_advisories"("id") ON DELETE CASCADE, | |
| 37 | "dependency_name" text NOT NULL, | |
| 38 | "dependency_version" text, | |
| 39 | "manifest_path" text NOT NULL, | |
| 40 | "status" text NOT NULL DEFAULT 'open', -- open | dismissed | fixed | |
| 41 | "dismissed_reason" text, | |
| 42 | "created_at" timestamp NOT NULL DEFAULT now(), | |
| 43 | "updated_at" timestamp NOT NULL DEFAULT now() | |
| 44 | ); | |
| 45 | ||
| 46 | --> statement-breakpoint | |
| 47 | CREATE UNIQUE INDEX IF NOT EXISTS "repo_advisory_alerts_unique_idx" | |
| 48 | ON "repo_advisory_alerts" ("repository_id", "advisory_id", "manifest_path"); | |
| 49 | ||
| 50 | --> statement-breakpoint | |
| 51 | CREATE INDEX IF NOT EXISTS "repo_advisory_alerts_status_idx" | |
| 52 | ON "repo_advisory_alerts" ("repository_id", "status"); |