Pre-launch — Gluecron is in final validation. Public signups and git hosting for non-owner users open after launch review.
ccantynz/Gluecron.com
CodeIssuesPull RequestsActionsSecurityInsightsSettings
✨ AI
ExplainAsk AIWorkspaceSpec to PRTestsNL SearchDebt MapArchaeology
More
CommitsDiscussionsWikiProjectsReleasesContributorsPulseGatesDeploymentsPipelineAgents
Blame · Line-by-line history

health-score.ts

Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.

Gluecron.com/src/lib/health-score.ts
health-score.tsBlame898 lines · 1 contributor
Normal viewRaw
a7361c0Claude1import {
2 pgTable,
3 uuid,
4 integer,
5 text,
6 jsonb,
7 timestamp,
8} from "drizzle-orm/pg-core";
9import { eq, and, desc, gte, or, sql, inArray } from "drizzle-orm";
10import { db } from "../db/index";
11import {
12 repositories,
13 gateRuns,
14 pullRequests,
15 prComments,
16 repoAdvisoryAlerts,
17 securityAdvisories,
18 repoDependencies,
19 branchProtection,
20 commitVerifications,
21} from "../db/schema";
22import { getTree } from "../git/repository";
23
24// ---------------------------------------------------------------------------
25// Types
26// ---------------------------------------------------------------------------
27
28export interface HealthIssue {
29 category: "security" | "gates" | "ai_review" | "dependencies" | "code_quality";
30 message: string;
31 severity: "critical" | "high" | "medium" | "low";
32}
33
34export interface HealthRecommendation {
35 category: "security" | "gates" | "ai_review" | "dependencies" | "code_quality";
36 message: string;
37 priority: "high" | "medium" | "low";
38}
39
40export interface HealthScoreResult {
41 score: number;
42 grade: "A" | "B" | "C" | "D" | "F";
43 securityScore: number;
44 gatesScore: number;
45 aiReviewScore: number;
46 dependenciesScore: number;
47 codeQualityScore: number;
48 issues: HealthIssue[];
49 recommendations: HealthRecommendation[];
50 breakdown: {
51 security: { label: string; value: string }[];
52 gates: { label: string; value: string }[];
53 aiReview: { label: string; value: string }[];
54 dependencies: { label: string; value: string }[];
55 codeQuality: { label: string; value: string }[];
56 };
57}
58
59export interface StoredHealthScore extends HealthScoreResult {
60 id: string;
61 repositoryId: string;
62 computedAt: Date;
63}
64
65// ---------------------------------------------------------------------------
66// Drizzle table definition (inline — not in schema.ts)
67// ---------------------------------------------------------------------------
68
69export const repoHealthScores = pgTable("repo_health_scores", {
70 id: uuid("id").primaryKey().defaultRandom(),
71 repositoryId: uuid("repository_id").notNull(),
72 score: integer("score").notNull(),
73 grade: text("grade").notNull(),
74 securityScore: integer("security_score").notNull(),
75 gatesScore: integer("gates_score").notNull(),
76 aiReviewScore: integer("ai_review_score").notNull(),
77 dependenciesScore: integer("dependencies_score").notNull(),
78 codeQualityScore: integer("code_quality_score").notNull(),
79 recommendations: jsonb("recommendations")
80 .$type<HealthRecommendation[]>()
81 .notNull()
82 .default([]),
83 issuesFound: jsonb("issues_found")
84 .$type<HealthIssue[]>()
85 .notNull()
86 .default([]),
87 computedAt: timestamp("computed_at", { withTimezone: true })
88 .defaultNow()
89 .notNull(),
90});
91
92// ---------------------------------------------------------------------------
93// Grade helpers
94// ---------------------------------------------------------------------------
95
96function scoreToGrade(score: number): "A" | "B" | "C" | "D" | "F" {
97 if (score >= 90) return "A";
98 if (score >= 75) return "B";
99 if (score >= 60) return "C";
100 if (score >= 45) return "D";
101 return "F";
102}
103
104export function getBadgeColor(grade: string): string {
105 switch (grade) {
106 case "A":
107 return "#2ea44f";
108 case "B":
109 return "#44cc11";
110 case "C":
111 return "#dfb317";
112 case "D":
113 return "#fe7d37";
114 case "F":
115 return "#e05d44";
116 default:
117 return "#9f9f9f";
118 }
119}
120
121// ---------------------------------------------------------------------------
122// Severity / priority sort helpers
123// ---------------------------------------------------------------------------
124
125const SEVERITY_ORDER: Record<string, number> = {
126 critical: 0,
127 high: 1,
128 medium: 2,
129 low: 3,
130};
131
132const PRIORITY_ORDER: Record<string, number> = {
133 high: 0,
134 medium: 1,
135 low: 2,
136};
137
138// ---------------------------------------------------------------------------
139// Category scorers
140// ---------------------------------------------------------------------------
141
142async function computeSecurityScore(
143 repositoryId: string
144): Promise<{
145 score: number;
146 issues: HealthIssue[];
147 recommendations: HealthRecommendation[];
148 breakdown: { label: string; value: string }[];
149}> {
150 const issues: HealthIssue[] = [];
151 const recommendations: HealthRecommendation[] = [];
152 const breakdown: { label: string; value: string }[] = [];
153
154 let score = 30;
155 const thirtyDaysAgo = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000);
156
157 // Query open advisory alerts joined with security_advisories
158 const alerts = await db
159 .select({
160 id: repoAdvisoryAlerts.id,
161 status: repoAdvisoryAlerts.status,
162 severity: securityAdvisories.severity,
163 summary: securityAdvisories.summary,
164 })
165 .from(repoAdvisoryAlerts)
166 .innerJoin(
167 securityAdvisories,
168 eq(repoAdvisoryAlerts.advisoryId, securityAdvisories.id)
169 )
170 .where(
171 and(
172 eq(repoAdvisoryAlerts.repositoryId, repositoryId),
173 eq(repoAdvisoryAlerts.status, "open")
174 )
175 );
176
177 const criticalAlerts = alerts.filter((a) => a.severity === "critical");
178 const highAlerts = alerts.filter((a) => a.severity === "high");
179 const moderateAlerts = alerts.filter((a) => a.severity === "moderate");
180 const lowAlerts = alerts.filter((a) => a.severity === "low");
181
182 // Deduct for critical alerts
183 if (criticalAlerts.length > 0) {
184 score -= 15;
185 for (const a of criticalAlerts) {
186 issues.push({
187 category: "security",
188 message: `Open critical security advisory: ${a.summary}`,
189 severity: "critical",
190 });
191 }
192 } else if (highAlerts.length > 0) {
193 // Only deduct for high if no critical
194 score -= 10;
195 }
196
197 // Deduct for high alerts (issues recorded regardless of critical presence)
198 for (const a of highAlerts) {
199 issues.push({
200 category: "security",
201 message: `Open high-severity advisory: ${a.summary}`,
202 severity: "high",
203 });
204 }
205
206 // Deduct for moderate: -5 per alert, max -10
207 const moderatePenalty = Math.min(moderateAlerts.length * 5, 10);
208 score -= moderatePenalty;
209
210 // Deduct for low: -3 per alert, max -6
211 const lowPenalty = Math.min(lowAlerts.length * 3, 6);
212 score -= lowPenalty;
213
214 // Security gate runs in last 30 days
215 const securityGateRuns = await db
216 .select({
217 id: gateRuns.id,
218 status: gateRuns.status,
219 createdAt: gateRuns.createdAt,
220 })
221 .from(gateRuns)
222 .where(
223 and(
224 eq(gateRuns.repositoryId, repositoryId),
225 gte(gateRuns.createdAt, thirtyDaysAgo),
226 or(
227 sql`lower(${gateRuns.gateName}) like '%security%'`,
228 sql`lower(${gateRuns.gateName}) like '%scan%'`,
229 sql`lower(${gateRuns.gateName}) like '%secret%'`
230 )
231 )
232 )
233 .orderBy(desc(gateRuns.createdAt));
234
235 if (securityGateRuns.length > 0) {
236 const latest = securityGateRuns[0];
237 if (latest.status === "failed") {
238 score -= 8;
239 } else if (latest.status === "repaired") {
240 score -= 2;
241 }
242 breakdown.push({
243 label: "Latest security gate",
244 value: latest.status,
245 });
246 } else {
247 breakdown.push({ label: "Latest security gate", value: "none" });
248 }
249
250 breakdown.push({
251 label: "Open advisories",
252 value: String(criticalAlerts.length + highAlerts.length + moderateAlerts.length + lowAlerts.length),
253 });
254
255 score = Math.max(0, Math.min(30, score));
256
257 if (score < 25) {
258 recommendations.push({
259 category: "security",
260 message:
261 "Run a security scan — go to Gates → Security to trigger one",
262 priority: score < 15 ? "high" : "medium",
263 });
264 }
265
266 return { score, issues, recommendations, breakdown };
267}
268
269async function computeGatesScore(
270 repositoryId: string
271): Promise<{
272 score: number;
273 issues: HealthIssue[];
274 recommendations: HealthRecommendation[];
275 breakdown: { label: string; value: string }[];
276}> {
277 const issues: HealthIssue[] = [];
278 const recommendations: HealthRecommendation[] = [];
279 const breakdown: { label: string; value: string }[] = [];
280
281 const thirtyDaysAgo = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000);
282
283 const runs = await db
284 .select({
285 id: gateRuns.id,
286 status: gateRuns.status,
287 })
288 .from(gateRuns)
289 .where(
290 and(
291 eq(gateRuns.repositoryId, repositoryId),
292 gte(gateRuns.createdAt, thirtyDaysAgo)
293 )
294 )
295 .orderBy(desc(gateRuns.createdAt));
296
297 const total_count = runs.length;
298
299 if (total_count === 0) {
300 breakdown.push({ label: "Green rate (30d)", value: "N/A" });
301 breakdown.push({ label: "Total gate runs", value: "0" });
302 return { score: 20, issues, recommendations, breakdown };
303 }
304
305 const green_count = runs.filter(
306 (r) => r.status === "passed" || r.status === "repaired"
307 ).length;
308
309 const rate = green_count / total_count;
310 const score = Math.round(rate * 25);
311
312 breakdown.push({
313 label: "Green rate (30d)",
314 value: `${Math.round(rate * 100)}%`,
315 });
316 breakdown.push({ label: "Total gate runs", value: String(total_count) });
317
318 if (rate < 0.5) {
319 issues.push({
320 category: "gates",
321 message: `Gate green rate is ${Math.round(rate * 100)}% over the last 30 days`,
322 severity: "high",
323 });
324 }
325
326 if (score < 20) {
327 recommendations.push({
328 category: "gates",
329 message:
330 "Enable gate enforcement on push to catch failures before they merge",
331 priority: "medium",
332 });
333 }
334
335 return { score, issues, recommendations, breakdown };
336}
337
338async function computeAiReviewScore(
339 repositoryId: string
340): Promise<{
341 score: number;
342 issues: HealthIssue[];
343 recommendations: HealthRecommendation[];
344 breakdown: { label: string; value: string }[];
345}> {
346 const issues: HealthIssue[] = [];
347 const recommendations: HealthRecommendation[] = [];
348 const breakdown: { label: string; value: string }[] = [];
349
350 const thirtyDaysAgo = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000);
351
352 // PRs in last 30 days that are not draft
353 const prs = await db
354 .select({ id: pullRequests.id })
355 .from(pullRequests)
356 .where(
357 and(
358 eq(pullRequests.repositoryId, repositoryId),
359 gte(pullRequests.createdAt, thirtyDaysAgo),
360 eq(pullRequests.isDraft, false)
361 )
362 );
363
364 const total_prs = prs.length;
365
366 if (total_prs === 0) {
367 breakdown.push({ label: "PRs reviewed by AI", value: "0/0" });
368 return { score: 18, issues, recommendations, breakdown };
369 }
370
371 const prIds = prs.map((p) => p.id);
372
373 // AI review comments on those PRs
374 const aiComments = await db
375 .select({
376 pullRequestId: prComments.pullRequestId,
377 body: prComments.body,
378 })
379 .from(prComments)
380 .where(
381 and(
382 inArray(prComments.pullRequestId, prIds),
383 eq(prComments.isAiReview, true)
384 )
385 );
386
387 if (aiComments.length === 0) {
388 breakdown.push({ label: "PRs reviewed by AI", value: `0/${total_prs}` });
389 recommendations.push({
390 category: "ai_review",
391 message:
392 "Enable AI code review — it triggers automatically on every new PR",
393 priority: "high",
394 });
395 return { score: 8, issues, recommendations, breakdown };
396 }
397
398 // Group AI comments by PR
399 const prCommentMap = new Map<string, string[]>();
400 for (const c of aiComments) {
401 const existing = prCommentMap.get(c.pullRequestId) ?? [];
402 existing.push(c.body);
403 prCommentMap.set(c.pullRequestId, existing);
404 }
405
406 const reviewed_count = prCommentMap.size;
407 const review_rate = reviewed_count / total_prs;
408
409 // Count approved: AI review comment body contains "✓ Approved" or "approved" (case-insensitive)
410 let approved_count = 0;
411 for (const [, bodies] of prCommentMap) {
412 const hasApproval = bodies.some(
413 (b) =>
414 b.includes("✓ Approved") ||
415 b.toLowerCase().includes("approved")
416 );
417 if (hasApproval) approved_count++;
418 }
419
420 const approval_rate = approved_count / Math.max(reviewed_count, 1);
421 const score = Math.round(review_rate * 10 + approval_rate * 10);
422
423 breakdown.push({
424 label: "PRs reviewed by AI",
425 value: `${reviewed_count}/${total_prs}`,
426 });
427
428 return { score, issues, recommendations, breakdown };
429}
430
431async function computeDependenciesScore(
432 repositoryId: string
433): Promise<{
434 score: number;
435 issues: HealthIssue[];
436 recommendations: HealthRecommendation[];
437 breakdown: { label: string; value: string }[];
438}> {
439 const issues: HealthIssue[] = [];
440 const recommendations: HealthRecommendation[] = [];
441 const breakdown: { label: string; value: string }[] = [];
442
443 // Count dependencies
444 const deps = await db
445 .select({ id: repoDependencies.id })
446 .from(repoDependencies)
447 .where(eq(repoDependencies.repositoryId, repositoryId));
448
449 const dep_count = deps.length;
450
451 if (dep_count === 0) {
452 breakdown.push({ label: "Dependencies tracked", value: "0" });
453 breakdown.push({ label: "Open advisories", value: "0" });
454 return { score: 15, issues, recommendations, breakdown };
455 }
456
457 // All open advisory alerts for this repo
458 const alerts = await db
459 .select({
460 severity: securityAdvisories.severity,
461 status: repoAdvisoryAlerts.status,
462 })
463 .from(repoAdvisoryAlerts)
464 .innerJoin(
465 securityAdvisories,
466 eq(repoAdvisoryAlerts.advisoryId, securityAdvisories.id)
467 )
468 .where(
469 and(
470 eq(repoAdvisoryAlerts.repositoryId, repositoryId),
471 eq(repoAdvisoryAlerts.status, "open")
472 )
473 );
474
475 const open_critical = alerts.filter((a) => a.severity === "critical").length;
476 const open_high = alerts.filter((a) => a.severity === "high").length;
477 const open_moderate = alerts.filter((a) => a.severity === "moderate").length;
478
479 let score = 15 - open_critical * 5 - open_high * 3 - open_moderate * 1;
480 score = Math.max(0, Math.min(15, score));
481
482 breakdown.push({
483 label: "Dependencies tracked",
484 value: String(dep_count),
485 });
486 breakdown.push({
487 label: "Open advisories",
488 value: String(open_critical + open_high + open_moderate),
489 });
490
491 if (open_critical + open_high > 0) {
492 recommendations.push({
493 category: "dependencies",
494 message:
495 "Update vulnerable dependencies — check Security → Advisories for details",
496 priority: "high",
497 });
498 }
499
500 return { score, issues, recommendations, breakdown };
501}
502
503async function computeCodeQualityScore(
504 repositoryId: string,
505 owner: string,
506 repoName: string
507): Promise<{
508 score: number;
509 issues: HealthIssue[];
510 recommendations: HealthRecommendation[];
511 breakdown: { label: string; value: string }[];
512}> {
513 const issues: HealthIssue[] = [];
514 const recommendations: HealthRecommendation[] = [];
515 const breakdown: { label: string; value: string }[] = [];
516
517 const sevenDaysAgo = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000);
518 const thirtyDaysAgo = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000);
519
520 let score = 0;
521
522 // 1. Branch protection for default branch
523 const repo = await db
524 .select({ defaultBranch: repositories.defaultBranch })
525 .from(repositories)
526 .where(eq(repositories.id, repositoryId))
527 .limit(1);
528
529 const defaultBranch = repo[0]?.defaultBranch ?? "main";
530
531 const protection = await db
532 .select({ id: branchProtection.id })
533 .from(branchProtection)
534 .where(
535 and(
536 eq(branchProtection.repositoryId, repositoryId),
537 eq(branchProtection.pattern, defaultBranch)
538 )
539 )
540 .limit(1);
541
542 const hasBranchProtection = protection.length > 0;
543
544 if (hasBranchProtection) {
545 score += 3;
546 breakdown.push({ label: "Branch protection", value: "enabled" });
547 } else {
548 breakdown.push({ label: "Branch protection", value: "none" });
549 recommendations.push({
550 category: "code_quality",
551 message: "Enable branch protection on your default branch",
552 priority: "medium",
553 });
554 }
555
556 // 2. CODEOWNERS — check gate runs first, then git tree
557 let hasCodeowners = false;
558
559 const codeownersGateRun = await db
560 .select({ id: gateRuns.id, status: gateRuns.status })
561 .from(gateRuns)
562 .where(
563 and(
564 eq(gateRuns.repositoryId, repositoryId),
565 gte(gateRuns.createdAt, thirtyDaysAgo),
566 sql`lower(${gateRuns.gateName}) = 'codeowners'`
567 )
568 )
569 .orderBy(desc(gateRuns.createdAt))
570 .limit(1);
571
572 if (codeownersGateRun.length > 0 && codeownersGateRun[0].status === "passed") {
573 hasCodeowners = true;
574 } else {
575 // Check git tree for CODEOWNERS file
576 try {
577 const tree = await getTree(owner, repoName, defaultBranch);
578 hasCodeowners = tree.some(
579 (entry) => entry.name === "CODEOWNERS" && entry.type === "blob"
580 );
581 } catch {
582 hasCodeowners = false;
583 }
584 }
585
586 if (hasCodeowners) {
587 score += 2;
588 breakdown.push({ label: "CODEOWNERS", value: "found" });
589 } else {
590 breakdown.push({ label: "CODEOWNERS", value: "not found" });
591 recommendations.push({
592 category: "code_quality",
593 message:
594 "Set up a CODEOWNERS file to automatically request reviews",
595 priority: "low",
596 });
597 }
598
599 // 3. Commit verifications — last 10 commits
600 const verifications = await db
601 .select({ verified: commitVerifications.verified })
602 .from(commitVerifications)
603 .where(eq(commitVerifications.repositoryId, repositoryId))
604 .orderBy(desc(commitVerifications.verifiedAt))
605 .limit(10);
606
607 const verified_count = verifications.filter((v) => v.verified).length;
608
609 if (verified_count >= 5) {
610 score += 2;
611 breakdown.push({
612 label: "Verified commits (last 10)",
613 value: `${verified_count}/10`,
614 });
615 } else if (verified_count >= 1) {
616 score += 1;
617 breakdown.push({
618 label: "Verified commits (last 10)",
619 value: `${verified_count}/10`,
620 });
621 } else {
622 breakdown.push({
623 label: "Verified commits (last 10)",
624 value: `${verified_count}/10`,
625 });
626 }
627
628 // 4. CI activity — any gate/workflow run in last 7 days
629 const recentActivity = await db
630 .select({ id: gateRuns.id })
631 .from(gateRuns)
632 .where(
633 and(
634 eq(gateRuns.repositoryId, repositoryId),
635 gte(gateRuns.createdAt, sevenDaysAgo)
636 )
637 )
638 .limit(1);
639
640 if (recentActivity.length > 0) {
641 score += 3;
642 breakdown.push({ label: "CI activity (7d)", value: "active" });
643 } else {
644 breakdown.push({ label: "CI activity (7d)", value: "none" });
645 }
646
647 score = Math.max(0, Math.min(10, score));
648
649 return { score, issues, recommendations, breakdown };
650}
651
652// ---------------------------------------------------------------------------
653// Main exported functions
654// ---------------------------------------------------------------------------
655
656export async function computeHealthScore(
657 repositoryId: string,
658 owner: string,
659 repoName: string
660): Promise<HealthScoreResult> {
661 try {
662 const [security, gates, aiReview, dependencies, codeQuality] =
663 await Promise.all([
664 computeSecurityScore(repositoryId),
665 computeGatesScore(repositoryId),
666 computeAiReviewScore(repositoryId),
667 computeDependenciesScore(repositoryId),
668 computeCodeQualityScore(repositoryId, owner, repoName),
669 ]);
670
671 const totalScore =
672 security.score +
673 gates.score +
674 aiReview.score +
675 dependencies.score +
676 codeQuality.score;
677
678 const allIssues: HealthIssue[] = [
679 ...security.issues,
680 ...gates.issues,
681 ...aiReview.issues,
682 ...dependencies.issues,
683 ...codeQuality.issues,
684 ].sort(
685 (a, b) =>
686 (SEVERITY_ORDER[a.severity] ?? 99) -
687 (SEVERITY_ORDER[b.severity] ?? 99)
688 );
689
690 const allRecommendations: HealthRecommendation[] = [
691 ...security.recommendations,
692 ...gates.recommendations,
693 ...aiReview.recommendations,
694 ...dependencies.recommendations,
695 ...codeQuality.recommendations,
696 ].sort(
697 (a, b) =>
698 (PRIORITY_ORDER[a.priority] ?? 99) -
699 (PRIORITY_ORDER[b.priority] ?? 99)
700 );
701
702 return {
703 score: totalScore,
704 grade: scoreToGrade(totalScore),
705 securityScore: security.score,
706 gatesScore: gates.score,
707 aiReviewScore: aiReview.score,
708 dependenciesScore: dependencies.score,
709 codeQualityScore: codeQuality.score,
710 issues: allIssues,
711 recommendations: allRecommendations,
712 breakdown: {
713 security: security.breakdown,
714 gates: gates.breakdown,
715 aiReview: aiReview.breakdown,
716 dependencies: dependencies.breakdown,
717 codeQuality: codeQuality.breakdown,
718 },
719 };
720 } catch (err) {
721 const errorMessage =
722 err instanceof Error ? err.message : "Unknown error during health score computation";
723 return {
724 score: 0,
725 grade: "F",
726 securityScore: 0,
727 gatesScore: 0,
728 aiReviewScore: 0,
729 dependenciesScore: 0,
730 codeQualityScore: 0,
731 issues: [
732 {
733 category: "code_quality",
734 message: `Health score computation failed: ${errorMessage}`,
735 severity: "critical",
736 },
737 ],
738 recommendations: [],
739 breakdown: {
740 security: [],
741 gates: [],
742 aiReview: [],
743 dependencies: [],
744 codeQuality: [],
745 },
746 };
747 }
748}
749
750export async function computeAndStoreHealthScore(
751 repositoryId: string,
752 owner: string,
753 repoName: string
754): Promise<StoredHealthScore> {
755 const result = await computeHealthScore(repositoryId, owner, repoName);
756
757 const todayStart = new Date();
758 todayStart.setUTCHours(0, 0, 0, 0);
759
760 // Check if a record already exists for this repo today
761 const existing = await db
762 .select({ id: repoHealthScores.id })
763 .from(repoHealthScores)
764 .where(
765 and(
766 eq(repoHealthScores.repositoryId, repositoryId),
767 gte(repoHealthScores.computedAt, todayStart)
768 )
769 )
770 .limit(1);
771
772 const now = new Date();
773
774 if (existing.length > 0) {
775 // Update the existing row for today
776 const updated = await db
777 .update(repoHealthScores)
778 .set({
779 score: result.score,
780 grade: result.grade,
781 securityScore: result.securityScore,
782 gatesScore: result.gatesScore,
783 aiReviewScore: result.aiReviewScore,
784 dependenciesScore: result.dependenciesScore,
785 codeQualityScore: result.codeQualityScore,
786 recommendations: result.recommendations,
787 issuesFound: result.issues,
788 computedAt: now,
789 })
790 .where(eq(repoHealthScores.id, existing[0].id))
791 .returning();
792
793 const row = updated[0];
794 return rowToStoredScore(row, result);
795 } else {
796 // Insert a new row
797 const inserted = await db
798 .insert(repoHealthScores)
799 .values({
800 repositoryId,
801 score: result.score,
802 grade: result.grade,
803 securityScore: result.securityScore,
804 gatesScore: result.gatesScore,
805 aiReviewScore: result.aiReviewScore,
806 dependenciesScore: result.dependenciesScore,
807 codeQualityScore: result.codeQualityScore,
808 recommendations: result.recommendations,
809 issuesFound: result.issues,
810 computedAt: now,
811 })
812 .returning();
813
814 const row = inserted[0];
815 return rowToStoredScore(row, result);
816 }
817}
818
819export async function getLatestHealthScore(
820 repositoryId: string
821): Promise<StoredHealthScore | null> {
822 const rows = await db
823 .select()
824 .from(repoHealthScores)
825 .where(eq(repoHealthScores.repositoryId, repositoryId))
826 .orderBy(desc(repoHealthScores.computedAt))
827 .limit(1);
828
829 if (rows.length === 0) return null;
830
831 return dbRowToStoredScore(rows[0]);
832}
833
834export async function getHealthScoreHistory(
835 repositoryId: string,
836 limit = 30
837): Promise<StoredHealthScore[]> {
838 const rows = await db
839 .select()
840 .from(repoHealthScores)
841 .where(eq(repoHealthScores.repositoryId, repositoryId))
842 .orderBy(desc(repoHealthScores.computedAt))
843 .limit(limit);
844
845 return rows.map(dbRowToStoredScore);
846}
847
848// ---------------------------------------------------------------------------
849// Row → StoredHealthScore helpers
850// ---------------------------------------------------------------------------
851
852type DbRow = typeof repoHealthScores.$inferSelect;
853
854function rowToStoredScore(
855 row: DbRow,
856 result: HealthScoreResult
857): StoredHealthScore {
858 return {
859 id: row.id,
860 repositoryId: row.repositoryId,
861 computedAt: row.computedAt,
862 score: row.score,
863 grade: row.grade as "A" | "B" | "C" | "D" | "F",
864 securityScore: row.securityScore,
865 gatesScore: row.gatesScore,
866 aiReviewScore: row.aiReviewScore,
867 dependenciesScore: row.dependenciesScore,
868 codeQualityScore: row.codeQualityScore,
869 issues: (row.issuesFound as HealthIssue[]) ?? [],
870 recommendations: (row.recommendations as HealthRecommendation[]) ?? [],
871 breakdown: result.breakdown,
872 };
873}
874
875function dbRowToStoredScore(row: DbRow): StoredHealthScore {
876 return {
877 id: row.id,
878 repositoryId: row.repositoryId,
879 computedAt: row.computedAt,
880 score: row.score,
881 grade: row.grade as "A" | "B" | "C" | "D" | "F",
882 securityScore: row.securityScore,
883 gatesScore: row.gatesScore,
884 aiReviewScore: row.aiReviewScore,
885 dependenciesScore: row.dependenciesScore,
886 codeQualityScore: row.codeQualityScore,
887 issues: (row.issuesFound as HealthIssue[]) ?? [],
888 recommendations: (row.recommendations as HealthRecommendation[]) ?? [],
889 // Breakdown is not stored — return empty skeleton when reading from DB
890 breakdown: {
891 security: [],
892 gates: [],
893 aiReview: [],
894 dependencies: [],
895 codeQuality: [],
896 },
897 };
898}