coder
diff --git a/‎.claude/docs/DATABASE.md
Lines changed: 258 additions & 0 deletions b/‎.claude/docs/DATABASE.md
Lines changed: 258 additions & 0 deletions
@@ -0,0 +1,258 @@
+# 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
+
+## In-Memory Database (dbmem) Updates
+
+### Critical Requirements
+
+When adding new fields to database structs:
+
+- **CRITICAL**: Update `coderd/database/dbmem/dbmem.go` in-memory implementations
+- The `Insert*` functions must include ALL new fields, not just basic ones
+- Common issue: Tests pass with real database but fail with in-memory database due to missing field mappings
+- Always verify in-memory database functions match the real database schema after migrations
+
+### Example Pattern
+
+```go
+// In dbmem.go - ensure ALL fields are included
+code := database.OAuth2ProviderAppCode{
+    ID:                  arg.ID,
+    CreatedAt:           arg.CreatedAt,
+    // ... existing fields ...
+    ResourceUri:         arg.ResourceUri,         // New field
+    CodeChallenge:       arg.CodeChallenge,       // New field
+    CodeChallengeMethod: arg.CodeChallengeMethod, // New field
+}
+```
+
+## 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`
+3. **dbmem inconsistencies**: Ensure in-memory implementations match schema
+
+### 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)
+}
+```
+
+### In-Memory Testing
+
+```go
+func TestInMemoryDatabase(t *testing.T) {
+    db := dbmem.New()
+
+    // Test with in-memory 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