# 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

### Query Naming

- Use `ByX` when `X` is the lookup or filter column.
- Use `PerX` or `GroupedByX` when `X` is the aggregation or grouping
  dimension.
- Avoid `ByX` names for grouped queries.

### Enum Changes Run in a Single Transaction

All migrations run inside one transaction (`pgTxnDriver`). Postgres forbids
*using* an enum value added by `ALTER TYPE ... ADD VALUE` within the same
transaction that added it, so it fails with `unsafe use of new value`.

Adding the value is fine; using it in the same batch is not. "Using it"
includes a later migration that casts to it (`col::my_enum`), inserts or
updates a row with it, or sets it as a column default. This only fails when a
row actually materializes the new value, so fresh databases and CI pass while
deployments with existing data break.

**MUST DO**: If any migration uses a newly added enum value, recreate the type
instead of using `ADD VALUE`. A freshly created enum's values are usable
immediately in the same transaction. Precedent: `000144_user_status_dormant`.

```sql
CREATE TYPE new_my_enum AS ENUM ('existing', 'value', 'new_value');

ALTER TABLE my_table
    ALTER COLUMN col TYPE new_my_enum USING (col::text::new_my_enum);

DROP TYPE my_enum;

ALTER TYPE new_my_enum RENAME TO my_enum;
```

Recreating produces an identical schema, so `make gen` yields no `dump.sql`
diff and databases that already applied the migration see no drift.

**Testing**: `migrations.Stepper` commits each migration separately, so tests
built on it cannot surface this. To catch it, seed a row using the new value,
then apply the affected migrations in a single transaction (see
`TestMigration000504AIProvidersBackfillEnumInSingleTxn`).

## 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.

## Database-to-SDK Conversions

- Extract explicit db-to-SDK conversion helpers instead of inlining large
  conversion blocks inside handlers.
- Keep nullable-field handling, type coercion, and response shaping in the
  converter so handlers stay focused on request flow and authorization.

## 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

### Transaction Safety with `InTx`

- Inside `db.InTx(...)` closures, do not use the outer store
  (`api.Database`, `p.db`, etc.) directly or indirectly. Use the `tx`
  handle for DB work inside the closure, or fetch read-only inputs before
  opening the transaction.
- Watch for helper methods on a receiver that hide outer-store access. A
  call like `p.someHelper(ctx)` is still unsafe inside `InTx` if that
  helper uses `p.db` internally.
- Using the outer store while a transaction is open can hold one
  connection and then block on another pool checkout, which can cause
  pool starvation and `idle in transaction` incidents under load.

### 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
# Run tests (starts Postgres automatically if needed)
make test

# 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
- [ ] Nullable fields use `sql.Null*` types
- [ ] Authorization context appropriate for endpoint type