The persistence layer of `gh.gg` is built upon a robust **PostgreSQL database**, with all interactions managed through **Drizzle ORM**. This setup ensures type safety, efficient querying, and seamless
The persistence layer of gh.gg is built upon a robust PostgreSQL database, with all interactions managed through Drizzle ORM. This setup ensures type safety, efficient querying, and seamless schema evolution. As highlighted in the Architecture Overview, the database is central to storing user data, repository insights, and application state.
Drizzle ORM (Object-Relational Mapper) is utilized to define and interact with the PostgreSQL database. Drizzle offers:
src/db/schema.ts, enabling a clear and declarative approach to database structure. Drizzle also facilitates database migrations (drizzle-kit), making it easy to introduce changes to the schema as the application evolves.The src/db/schema.ts file defines the core tables that underpin the gh.gg platform. Here's an overview of the main tables and their purposes:
userid (primary key, GitHub user ID), name, email, image.userSubscriptionsuserId (foreign key to user), stripeCustomerId, stripeSubscriptionId, plan (byok, pro), status (active, canceled, past_due), currentPeriodEnd.tokenUsageuserId (foreign key to user), feature (e.g., diagram, scorecard), inputTokens, outputTokens, totalTokens, isByok (true if using user's own key).developerRankingsuserId (foreign key to user, can be null for non-registered opponents), username, eloRating, wins, losses, tier.arenaBattleschallengerId (foreign key to user), opponentId, challengerUsername, opponentUsername, status, battleType, scores (JSONB for detailed breakdown), aiAnalysis (JSONB for AI review), eloChange (JSONB).repositoryScorecardsuserId (foreign key to user), repoOwner, repoName, ref, version, overallScore, metrics (JSONB), markdown (full AI analysis).repositoryDiagramsuserId (foreign key to user), repoOwner, repoName, ref, diagramType, version, diagramCode, options (JSONB).aiSlopAnalysesuserId (foreign key to user), repoOwner, repoName, ref, version, overallScore, aiGeneratedPercentage, detectedPatterns (JSONB), markdown.repositoryWikiPagesrepoOwner, repoName, slug (URL-friendly identifier), title, content (Markdown), summary, version, isPublic.prAnalysisCacheuserId (foreign key to user), repoOwner, repoName, prNumber, version, overallScore, analysis (JSONB), markdown.issueAnalysisCacheuserId (foreign key to user), repoOwner, repoName, issueNumber, version, overallScore, slopRanking, suggestedPriority, analysis (JSONB), markdown.githubAppInstallationsinstallationId (unique GitHub ID), accountId (GitHub user/org ID), accountLogin, repositorySelection.webhookPreferencesinstallationId (foreign key to githubAppInstallations), prReviewEnabled, autoUpdateEnabled, excludedRepos (JSONB).These tables are interconnected through foreign key relationships, establishing a robust and normalized data model that supports all the complex functionalities of the gh.gg platform.