bdougie / bdougie/codebunny

Add Prisma Postgres Storage for Review History

Open
#7 0 comments 0 reactions 0 assignees View on GitHub
enhancement
Dominant language
TypeScript
Stars
7
Forks
0
PR merge metrics
No merged PRs in 30d

Description

## Problem

CodeBunny currently uses file-based storage (`.continue/review-metrics.json`) with a 100-review limit. Following the pattern of issue #1 and PR #2, we need persistent storage for review data to enable:

- πŸ“Š Unlimited review history tracking
- πŸ“ˆ Team analytics and code quality trends
- 🎯 Approval state transition monitoring
- πŸ”„ Cross-PR pattern analysis

---

## Proposed Solution: Prisma Postgres with MCP Integration

Add **optional, opt-in** Prisma serverless Postgres storage that:

βœ… Uses Prisma Platform API key for auto-setup
βœ… Leverages Prisma MCP server for database management
βœ… Provides serverless-optimized connection pooling
βœ… Replaces file storage when enabled (graceful fallback)
βœ… Stores enhanced review data for analytics
βœ… Follows PR #2 artifact storage pattern

---

## Architecture

### Storage Strategy
- **Default OFF**: Existing file-based storage continues working
- **Replace when enabled**: When Prisma enabled, skip `.continue/review-metrics.json` writes
- **Graceful fallback**: DB connection failures fall back to file storage with warning

### Authentication via Prisma Platform
```yaml
inputs:
enable-prisma-storage: 'true'
prisma-api-key: ${{ secrets.PRISMA_API_KEY }}
prisma-database-id: ${{ secrets.PRISMA_DATABASE_ID }} # optional
```

### Data Schema

```prisma
model ReviewSnapshot {
id String @id @default(cuid())
timestamp DateTime
repository String
prNumber Int
prTitle String
prAuthor String
filesChanged Int

// Review state tracking (from removed artifact feature)
reviewState String // MERGE | DONT_MERGE | MERGE_AFTER_CHANGES
reviewText String @db.Text

// Metrics (enhanced from current ReviewMetrics)
processingTime Int
promptLength Int
responseLength Int
issuesHigh Int
issuesMedium Int
issuesLow Int
rulesApplied Int
patternsDetected Int

// Context
projectType String
mainLanguages String[]
hasCustomCommand Boolean

@@index([repository, prNumber])
@@index([repository, timestamp])
}

model ApprovalTransition {
id String @id @default(cuid())
timestamp DateTime
repository String
prNumber Int
fromState String
toState String
reviewId String

@@index([repository, prNumber])
}
```

---

## Implementation Plan

### Phase 1: Storage Abstraction Layer
- [ ] Create `storage/storage-interface.ts` - Abstract storage provider interface
- [ ] Create `storage/file-storage.ts` - Extract existing file-based logic
- [ ] Create `storage/prisma-storage.ts` - New Prisma implementation

### Phase 2: Prisma Setup
- [ ] Create `prisma/schema.prisma` with serverless-optimized schema
- [ ] Add Prisma dependencies: `@prisma/client`, `@prisma/adapter-neon`, `prisma`
- [ ] Configure connection pooling for serverless environments

### Phase 3: MCP Integration
- [ ] Create `prisma-setup.ts` for MCP-based database management
- [ ] Auto-detect or create Prisma Postgres database on first run
- [ ] Use MCP tools: `ListDatabasesTool`, `CreateDatabaseTool`, `CreateConnectionStringTool`
- [ ] Generate pooled connection string automatically

### Phase 4: Core Logic Changes
- [ ] Update `index.ts` with storage provider factory pattern
- [ ] Add approval state tracking (parse TLDR recommendations)
- [ ] Store full review text for analytics
- [ ] Implement team analytics query helpers

### Phase 5: Configuration & Documentation
- [ ] Add new inputs to `action.yml` (enable-prisma-storage, prisma-api-key, prisma-database-id)
- [ ] Update README with Prisma setup guide
- [ ] Document Prisma Platform account creation
- [ ] Provide example analytics queries

---

## Files to Create/Modify

### New Files
```
actions/codebunny/
β”œβ”€β”€ storage/
β”‚ β”œβ”€β”€ storage-interface.ts # Abstract storage interface
β”‚ β”œβ”€β”€ file-storage.ts # Current file-based logic
β”‚ └── prisma-storage.ts # Prisma implementation
β”œβ”€β”€ prisma/
β”‚ └── schema.prisma # Database schema
└── prisma-setup.ts # MCP integration helper
```

### Modified Files
- `action.yml` - New optional inputs
- `actions/codebunny/index.ts` - Storage provider factory, approval tracking
- `actions/codebunny/package.json` - Prisma dependencies
- `actions/codebunny/review-metrics.ts` - Interface extraction
- `README.md` - Setup documentation

---

## Why Prisma Postgres + MCP?

### Serverless-Optimized
- Built-in connection pooling via Prisma Accelerate
- No cold start connection issues
- Optimized for GitHub Actions environments

### MCP Integration Benefits
- Auto-setup: No manual database creation needed
- API-based management: Create, backup, restore via tools
- Secure: API key-based authentication
- Future-proof: Can add schema introspection, migrations via MCP

### Developer Experience
- Type-safe queries with Prisma Client
- No SQL needed for basic operations
- Easy analytics with Prisma's query API
- Familiar to TypeScript developers

---

## Example Analytics Queries

```typescript
// Get approval state transitions for PR
await prisma.approvalTransition.findMany({
where: { repository: 'owner/repo', prNumber: 123 },
orderBy: { timestamp: 'asc' }
})

// Team code quality trend (last 30 days)
await prisma.reviewSnapshot.groupBy({
by: ['projectType'],
where: {
repository: 'owner/repo',
timestamp: { gte: thirtyDaysAgo }
},
_avg: { issuesHigh: true, issuesMedium: true, issuesLow: true }
})

// Most common review state
await prisma.reviewSnapshot.groupBy({
by: ['reviewState'],
where: { repository: 'owner/repo' },
_count: true
})
```

---

## Design Principles

1. **Opt-In by Default**: Feature is OFF unless explicitly enabled
2. **No Breaking Changes**: Existing file storage continues working
3. **Graceful Degradation**: DB failures don't block reviews
4. **Follow PR #2 Pattern**: Same configuration approach as removed artifact feature
5. **No Migration Tool**: Start fresh when enabled (per user preference)
6. **Serverless-First**: Optimized for GitHub Actions ephemeral environments

---

## Open Questions

- Should we provide a separate analytics dashboard/CLI tool?
- Should repository admins be able to query all PRs across their org?
- Maximum retention period for review data?
- Should we add webhooks for real-time analytics?

---

Related to #1, follows pattern of PR #2

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.