Architecture: No sqlc integration for type-safe queries

[kusold]

Description

The framework uses raw SQL with string formatting for schema qualification, losing the type safety and compile-time verification that sqlc provides.

Current Approach

// auth/postgres_store.go
queryUser := fmt.Sprintf(`
    SELECT id, issuer, identifier_subject, COALESCE(tenant_id, '00000000-0000-0000-0000-000000000000'::uuid)
    FROM %s
    WHERE issuer = $1 AND identifier_subject = $2`, s.qualify("users"))

var user UserRef
err := s.pool.QueryRow(ctx, queryUser, identity.Issuer, identity.Subject).Scan(
    &user.UserID,
    &user.Issuer,
    &user.Subject,
    &fallbackTenantID,
)

Problems

1. No compile-time verification - SQL syntax errors caught at runtime only
2. No type safety - Scan arguments must match SELECT order manually
3. String formatting for schema - Loses sqlc benefits, introduces injection risk
4. No query organization - SQL scattered throughout codebase

Proposed Solution

Add sqlc support while maintaining schema flexibility:

Option A: Per-schema generation

# sqlc.yaml
version: "2"
sql:
  - schema: "migrations/auth/"
    queries: "queries/"
    engine: "postgresql"
    gen:
      go:
        package: "db"
        out: "internal/db"
        emit_json_tags: true
        sql_package: "pgx/v5"

Option B: Runtime schema parameter

-- queries/user.sql
-- name: GetUserByIdentifier :one
SELECT id, issuer, identifier_subject, tenant_id
FROM users
WHERE issuer = $1 AND identifier_subject = $2;

At runtime, wrap queries with schema:

func (s *Store) GetUserByIdentifier(ctx context.Context, issuer, subject string) (User, error) {
    // sqlc-generated query, wrapped with schema
    return db.New(s.pool).GetUserByIdentifier(ctx, db.GetUserByIdentifierParams{
        Issuer: sqlc.Schema(issuer, s.schema),
        Subject: sqlc.Schema(subject, s.schema),
    })
}

Benefits

• Compile-time SQL verification
• Type-safe query parameters and results
• Query organization in queries/ directory
• IDE support for SQL

Migration Path

1. Add sqlc.yaml and generate for existing queries
2. Port queries one package at a time
3. Deprecate raw SQL methods

Impact

• Effort: High (significant refactoring)
• Benefit: High (type safety, maintainability)
• Breaking: No (internal implementation detail)