sqlc in Hive

Hive uses sqlc to generate type-safe Go from SQL queries. The generated files are committed to the repo — never edit them manually.

File Layout

internal/store/db/
├── queries/
│   ├── queries.sql          # Core session/message queries
│   └── queries_hc.sql       # Honeycomb queries (separate file)
├── migrations/
│   └── NNNN_name.up.sql     # Schema migrations (source of truth for sqlc)
├── queries.sql.go           # Generated — do not edit
├── queries_hc.sql.go        # Generated — do not edit
└── models.go                # Generated — do not edit

sqlc.yaml at the repo root lists both query files under sql[0].queries.

Running Code Generation

After any change to .sql query files or sqlc.yaml:

mise run generate    # runs sqlc generate + go-enum generation
# or directly:
sqlc generate

Always commit the generated *.sql.go and models.go alongside the SQL changes.

Adding a Query

1. Write the annotated query in the appropriate .sql file:

-- name: GetHCItem :one
SELECT id, repo_key, epic_id, parent_id, session_id, title, "desc",
       type, status, depth, created_at, updated_at
FROM hc_items WHERE id = ?;

-- name: ListHCItems :many
SELECT ... FROM hc_items WHERE repo_key = ? ORDER BY created_at DESC;

-- name: InsertHCItem :exec
INSERT INTO hc_items (...) VALUES (...);

Annotations: :one (returns single row), :many (returns slice), :exec (no rows returned).

2. Run mise run generate.
3. The generated function appears in queries_hc.sql.go (or queries.sql.go) under the db package.
4. Call it via s.db.Queries().GetHCItem(ctx, id).

Type Overrides (Domain Types in Generated Code)

When a column stores a domain enum, add an override in sqlc.yaml so the generated params/returns use the Go type directly instead of string:

overrides:
  - column: "hc_items.status"
    go_type:
      import: "github.com/colonyops/hive/internal/domain/hc"
      type: "Status"
  - column: "hc_items.type"
    go_type:
      import: "github.com/colonyops/hive/internal/domain/hc"
      type: "ItemType"

The domain type must implement driver.Valuer and sql.Scanner (or use text marshaling). go-enum generated types satisfy this via MarshalText/UnmarshalText, which SQLite handles as text.

Schema Source of Truth

sqlc derives the schema from internal/store/db/migrations/*.up.sql. When you add a migration, run mise run generate to regenerate models. The generated models.go is always overwritten — do not add hand-written code there.

Separate Query Files

HC queries live in queries_hc.sql to keep the diff surface small. Add new feature query files by listing them under sql[0].queries in sqlc.yaml. All query files share the same gen.go output directory and package.

Common Patterns

Nullable / optional columns: Use sql.NullString, sql.NullInt64, etc. Override with nullable: true in sqlc.yaml if needed.

Timestamps: Stored as INTEGER NOT NULL (Unix seconds). The generated code uses int64. Conversion to/from time.Time happens in the store layer, not in generated code.

Transactions: Store methods call queries through s.db.Ctx(ctx), which joins an ambient transaction when the context carries one. Open a unit of work with s.db.WithinTx(ctx, func(ctx context.Context, q *db.DB) error { ... }); a nested call joins the outer transaction and only the outermost call commits or rolls back.