annihilatorrrr · pull · Jun 20, 2025 · Jun 20, 2025 · Jun 20, 2025 · Jun 20, 2025
diff --git a/.claude/docs/DATABASE.md b/.claude/docs/DATABASE.md
@@ -0,0 +1,219 @@
+# Database Development Patterns
+
+## Database Work Overview
+
+### Database Generation Process
+
+1. Modify SQL files in `coderd/database/queries/`
+2. Run `make gen`
+3. If errors about audit table, update `enterprise/audit/table.go`
+4. Run `make gen` again
+5. Run `make lint` to catch any remaining issues
+
+## Migration Guidelines
+
+### Creating Migration Files
+
+**Location**: `coderd/database/migrations/`
+**Format**: `{number}_{description}.{up|down}.sql`
+
+- Number must be unique and sequential
+- Always include both up and down migrations
+
+### Helper Scripts
+
+| Script | Purpose |
+|--------|---------|
+| `./coderd/database/migrations/create_migration.sh "migration name"` | Creates new migration files |
+| `./coderd/database/migrations/fix_migration_numbers.sh` | Renumbers migrations to avoid conflicts |
+| `./coderd/database/migrations/create_fixture.sh "fixture name"` | Creates test fixtures for migrations |
+
+### Database Query Organization
+
+- **MUST DO**: Any changes to database - adding queries, modifying queries should be done in the `coderd/database/queries/*.sql` files
+- **MUST DO**: Queries are grouped in files relating to context - e.g. `prebuilds.sql`, `users.sql`, `oauth2.sql`
+- After making changes to any `coderd/database/queries/*.sql` files you must run `make gen` to generate respective ORM changes
+
+## Handling Nullable Fields
+
+Use `sql.NullString`, `sql.NullBool`, etc. for optional database fields:
+
+```go
+CodeChallenge: sql.NullString{
+    String: params.codeChallenge,
+    Valid:  params.codeChallenge != "",
+}
+```
+
+Set `.Valid = true` when providing values.
+
+## Audit Table Updates
+
+If adding fields to auditable types:
+
+1. Update `enterprise/audit/table.go`
+2. Add each new field with appropriate action:
+   - `ActionTrack`: Field should be tracked in audit logs
+   - `ActionIgnore`: Field should be ignored in audit logs
+   - `ActionSecret`: Field contains sensitive data
+3. Run `make gen` to verify no audit errors
+
+## Database Architecture
+
+### Core Components
+
+- **PostgreSQL 13+** recommended for production
+- **Migrations** managed with `migrate`
+- **Database authorization** through `dbauthz` package
+
+### Authorization Patterns
+
+```go
+// Public endpoints needing system access (OAuth2 registration)
+app, err := api.Database.GetOAuth2ProviderAppByClientID(dbauthz.AsSystemRestricted(ctx), clientID)
+
+// Authenticated endpoints with user context
+app, err := api.Database.GetOAuth2ProviderAppByClientID(ctx, clientID)
+
+// System operations in middleware
+roles, err := db.GetAuthorizationUserRoles(dbauthz.AsSystemRestricted(ctx), userID)
+```
+
+## Common Database Issues
+
+### Migration Issues
+
+1. **Migration conflicts**: Use `fix_migration_numbers.sh` to renumber
+2. **Missing down migration**: Always create both up and down files
+3. **Schema inconsistencies**: Verify against existing schema
+
+### Field Handling Issues
+
+1. **Nullable field errors**: Use `sql.Null*` types consistently
+2. **Missing audit entries**: Update `enterprise/audit/table.go`
+
+### Query Issues
+
+1. **Query organization**: Group related queries in appropriate files
+2. **Generated code errors**: Run `make gen` after query changes
+3. **Performance issues**: Add appropriate indexes in migrations
+
+## Database Testing
+
+### Test Database Setup
+
+```go
+func TestDatabaseFunction(t *testing.T) {
+    db := dbtestutil.NewDB(t)
+
+    // Test with real database
+    result, err := db.GetSomething(ctx, param)
+    require.NoError(t, err)
+    require.Equal(t, expected, result)
+}
+```
+
+## Best Practices
+
+### Schema Design
+
+1. **Use appropriate data types**: VARCHAR for strings, TIMESTAMP for times
+2. **Add constraints**: NOT NULL, UNIQUE, FOREIGN KEY as appropriate
+3. **Create indexes**: For frequently queried columns
+4. **Consider performance**: Normalize appropriately but avoid over-normalization
+
+### Query Writing
+
+1. **Use parameterized queries**: Prevent SQL injection
+2. **Handle errors appropriately**: Check for specific error types
+3. **Use transactions**: For related operations that must succeed together
+4. **Optimize queries**: Use EXPLAIN to understand query performance
+
+### Migration Writing
+
+1. **Make migrations reversible**: Always include down migration
+2. **Test migrations**: On copy of production data if possible
+3. **Keep migrations small**: One logical change per migration
+4. **Document complex changes**: Add comments explaining rationale
+
+## Advanced Patterns
+
+### Complex Queries
+
+```sql
+-- Example: Complex join with aggregation
+SELECT
+    u.id,
+    u.username,
+    COUNT(w.id) as workspace_count
+FROM users u
+LEFT JOIN workspaces w ON u.id = w.owner_id
+WHERE u.created_at > $1
+GROUP BY u.id, u.username
+ORDER BY workspace_count DESC;
+```
+
+### Conditional Queries
+
+```sql
+-- Example: Dynamic filtering
+SELECT * FROM oauth2_provider_apps
+WHERE
+    ($1::text IS NULL OR name ILIKE '%' || $1 || '%')
+    AND ($2::uuid IS NULL OR organization_id = $2)
+ORDER BY created_at DESC;
+```
+
+### Audit Patterns
+
+```go
+// Example: Auditable database operation
+func (q *sqlQuerier) UpdateUser(ctx context.Context, arg UpdateUserParams) (User, error) {
+    // Implementation here
+
+    // Audit the change
+    if auditor := audit.FromContext(ctx); auditor != nil {
+        auditor.Record(audit.UserUpdate{
+            UserID: arg.ID,
+            Old:    oldUser,
+            New:    newUser,
+        })
+    }
+
+    return newUser, nil
+}
+```
+
+## Debugging Database Issues
+
+### Common Debug Commands
+
+```bash
+# Check database connection
+make test-postgres
+
+# Run specific database tests
+go test ./coderd/database/... -run TestSpecificFunction
+
+# Check query generation
+make gen
+
+# Verify audit table
+make lint
+```
+
+### Debug Techniques
+
+1. **Enable query logging**: Set appropriate log levels
+2. **Use database tools**: pgAdmin, psql for direct inspection
+3. **Check constraints**: UNIQUE, FOREIGN KEY violations
+4. **Analyze performance**: Use EXPLAIN ANALYZE for slow queries
+
+### Troubleshooting Checklist
+
+- [ ] Migration files exist (both up and down)
+- [ ] `make gen` run after query changes
+- [ ] Audit table updated for new fields
+- [ ] In-memory database implementations updated
+- [ ] Nullable fields use `sql.Null*` types
+- [ ] Authorization context appropriate for endpoint type
diff --git a/.claude/docs/OAUTH2.md b/.claude/docs/OAUTH2.md
@@ -0,0 +1,158 @@
+# OAuth2 Development Guide
+
+## RFC Compliance Development
+
+### Implementing Standard Protocols
+
+When implementing standard protocols (OAuth2, OpenID Connect, etc.):
+
+1. **Fetch and Analyze Official RFCs**:
+   - Always read the actual RFC specifications before implementation
+   - Use WebFetch tool to get current RFC content for compliance verification
+   - Document RFC requirements in code comments
+
+2. **Default Values Matter**:
+   - Pay close attention to RFC-specified default values
+   - Example: RFC 7591 specifies `client_secret_basic` as default, not `client_secret_post`
+   - Ensure consistency between database migrations and application code
+
+3. **Security Requirements**:
+   - Follow RFC security considerations precisely
+   - Example: RFC 7592 prohibits returning registration access tokens in GET responses
+   - Implement proper error responses per protocol specifications
+
+4. **Validation Compliance**:
+   - Implement comprehensive validation per RFC requirements
+   - Support protocol-specific features (e.g., custom schemes for native OAuth2 apps)
+   - Test edge cases defined in specifications
+
+## OAuth2 Provider Implementation
+
+### OAuth2 Spec Compliance
+
+1. **Follow RFC 6749 for token responses**
+   - Use `expires_in` (seconds) not `expiry` (timestamp) in token responses
+   - Return proper OAuth2 error format: `{"error": "code", "error_description": "details"}`
+
+2. **Error Response Format**
+   - Create OAuth2-compliant error responses for token endpoint
+   - Use standard error codes: `invalid_client`, `invalid_grant`, `invalid_request`
+   - Avoid generic error responses for OAuth2 endpoints
+
+### PKCE Implementation
+
+- Support both with and without PKCE for backward compatibility
+- Use S256 method for code challenge
+- Properly validate code_verifier against stored code_challenge
+
+### UI Authorization Flow
+
+- Use POST requests for consent, not GET with links
+- Avoid dependency on referer headers for security decisions
+- Support proper state parameter validation
+
+### RFC 8707 Resource Indicators
+
+- Store resource parameters in database for server-side validation (opaque tokens)
+- Validate resource consistency between authorization and token requests
+- Support audience validation in refresh token flows
+- Resource parameter is optional but must be consistent when provided
+
+## OAuth2 Error Handling Pattern
+
+```go
+// Define specific OAuth2 errors
+var (
+    errInvalidPKCE = xerrors.New("invalid code_verifier")
+)
+
+// Use OAuth2-compliant error responses
+type OAuth2Error struct {
+    Error            string `json:"error"`
+    ErrorDescription string `json:"error_description,omitempty"`
+}
+
+// Return proper OAuth2 errors
+if errors.Is(err, errInvalidPKCE) {
+    writeOAuth2Error(ctx, rw, http.StatusBadRequest, "invalid_grant", "The PKCE code verifier is invalid")
+    return
+}
+```
+
+## Testing OAuth2 Features
+
+### Test Scripts
+
+Located in `./scripts/oauth2/`:
+
+- `test-mcp-oauth2.sh` - Full automated test suite
+- `setup-test-app.sh` - Create test OAuth2 app
+- `cleanup-test-app.sh` - Remove test app
+- `generate-pkce.sh` - Generate PKCE parameters
+- `test-manual-flow.sh` - Manual browser testing
+
+Always run the full test suite after OAuth2 changes:
+
+```bash
+./scripts/oauth2/test-mcp-oauth2.sh
+```
+
+### RFC Protocol Testing
+
+1. **Compliance Test Coverage**:
+   - Test all RFC-defined error codes and responses
+   - Validate proper HTTP status codes for different scenarios
+   - Test protocol-specific edge cases (URI formats, token formats, etc.)
+
+2. **Security Boundary Testing**:
+   - Test client isolation and privilege separation
+   - Verify information disclosure protections
+   - Test token security and proper invalidation
+
+## Common OAuth2 Issues
+
+1. **OAuth2 endpoints returning wrong error format** - Ensure OAuth2 endpoints return RFC 6749 compliant errors
+2. **Resource indicator validation failing** - Ensure database stores and retrieves resource parameters correctly
+3. **PKCE tests failing** - Verify both authorization code storage and token exchange handle PKCE fields
+4. **RFC compliance failures** - Verify against actual RFC specifications, not assumptions
+5. **Authorization context errors in public endpoints** - Use `dbauthz.AsSystemRestricted(ctx)` pattern
+6. **Default value mismatches** - Ensure database migrations match application code defaults
+7. **Bearer token authentication issues** - Check token extraction precedence and format validation
+8. **URI validation failures** - Support both standard schemes and custom schemes per protocol requirements
+
+## Authorization Context Patterns
+
+```go
+// Public endpoints needing system access (OAuth2 registration)
+app, err := api.Database.GetOAuth2ProviderAppByClientID(dbauthz.AsSystemRestricted(ctx), clientID)
+
+// Authenticated endpoints with user context
+app, err := api.Database.GetOAuth2ProviderAppByClientID(ctx, clientID)
+
+// System operations in middleware
+roles, err := db.GetAuthorizationUserRoles(dbauthz.AsSystemRestricted(ctx), userID)
+```
+
+## OAuth2/Authentication Work Patterns
+
+- Types go in `codersdk/oauth2.go` or similar
+- Handlers go in `coderd/oauth2.go` or `coderd/identityprovider/`
+- Database fields need migration + audit table updates
+- Always support backward compatibility
+
+## Protocol Implementation Checklist
+
+Before completing OAuth2 or authentication feature work:
+
+- [ ] Verify RFC compliance by reading actual specifications
+- [ ] Implement proper error response formats per protocol
+- [ ] Add comprehensive validation for all protocol fields
+- [ ] Test security boundaries and token handling
+- [ ] Update RBAC permissions for new resources
+- [ ] Add audit logging support if applicable
+- [ ] Create database migrations with proper defaults
+- [ ] Update in-memory database implementations
+- [ ] Add comprehensive test coverage including edge cases
+- [ ] Verify linting compliance
+- [ ] Test both positive and negative scenarios
+- [ ] Document protocol-specific patterns and requirements