How the DB codegen plugin works

Nobody hand-writes a data service. You annotate a proto RPC with the SQL it should run, and a custom toolchain in soundverse-proto emits the entire Go Connect handler — pool selection, argument binding, row scanning, Redis cache-aside, error mapping, tracing, and structured logs. This page explains the two programs that do it and the guarantees they bake in.

Two programs cooperate. The first is a real protoc plugin; the second is a plain Go program that stitches the results together:

protoc-gen-soundverse-db

A protoc plugin at cmd/protoc-gen-soundverse-db/main.go. It reads the DB annotations off each method/field and writes one *.soundverse_db.go Connect handler per data service. It fails the build on a dangerous or unmappable annotation.

gen-db-registry

A plain Go program at cmd/gen-db-registry/main.go. It walks the generated files with go/ast and emits one aggregate RegisterAllDBServices(...) that a consuming Go service calls once to mount every handler.

They are steps 4 and 5 of the five-step codegen pipeline; the plugin must be built into ./bin before the DB template runs. The exact ordered commands live in Run proto codegen locally. Everything they emit lands under gen/ and is .gitignored — it is published to soundverse-proto-go, never committed.

The build-and-mount pipeline

flowchart TD
  PROTO["Annotated .proto<br/>sql_query + db_column + schema.sql"]:::data
  PLUGIN["protoc-gen-soundverse-db<br/>(protoc plugin, ./bin)"]
  GEN["gen/.../GenerationDataService.soundverse_db.go<br/>type GenerationDataServiceDBImpl"]:::data
  REG["gen-db-registry<br/>(go/ast walk of *.soundverse_db.go)"]
  REGFILE["registry/db_registry.go<br/>RegisterAllDBServices(...)"]:::data
  CDB["core-database<br/>cmd/server/main.go"]:::data
  PG[("Postgres<br/>generation.tasks")]:::external
  RD[("Redis<br/>cache-aside")]:::external

  PROTO -->|buf generate --template buf.gen.soundverse-db.yaml| PLUGIN
  PLUGIN --> GEN
  GEN -->|go run cmd/gen-db-registry| REG
  REG --> REGFILE
  REGFILE -->|imported as soundverse-proto-go| CDB
  CDB -->|RegisterAllDBServices mounts every handler| CDB
  CDB --> PG
  CDB --> RD

  classDef data     fill:#8b5cf6,color:#fff,stroke:#6d28d9
  classDef external fill:#f59e0b,color:#111,stroke:#b45309

The DB annotations

All of codegen is driven by a handful of custom proto extensions in proto/soundverse_proto/db/v1/db_options.proto. Method options describe the RPC; the field options map a message field to a SQL column. The full table (with extension numbers and the error-code map) lives in the proto contract reference; the ones that matter here:

Annotation	Applies to	What it does
target_db	method	Pool key (e.g. "prod"). Selects which pgxpool.Pool from the DBPools map runs the query.
sql_query	method	The literal SQL the handler executes. Columns must be explicit (no SELECT *); mutations need a WHERE.
require_internal_auth	method	bool. When true the handler rejects any request whose bearer token differs from InternalSecret.
cache_ttl_seconds	method	int32. > 0 turns on read-through Redis caching of the proto-marshalled response.
cache_key	method	Template like tools:active:{environment}; {name} placeholders bind to request fields.
invalidates_keys	method	Repeated key templates; the handler DELs them after a successful write.
db_column	field	Maps a message field to a SQL result column for scanning, e.g. [(db_column) = "created_at"].
db_enum_text	field	Marks an enum request field whose column is a Postgres ENUM: send the lowercase label instead of the int (UNSPECIFIED → "").

A worked RPC: GetTask

Here is the simplest read RPC from generation/task/v1/task.proto. The row message GenerationTask tags every field with a db_column; the RPC carries target_db, sql_query and require_internal_auth; and the request’s first field is a RequestContext.

generation/task/v1/task.proto (trimmed)

message GenerationTask {
  string id           = 1  [(soundverse_proto.db.v1.db_column) = "id"];
  string tool_id      = 3  [(soundverse_proto.db.v1.db_column) = "tool_id"];
  TaskStatus status   = 7  [(soundverse_proto.db.v1.db_column) = "status"];
  string payload_json = 10 [(soundverse_proto.db.v1.db_column) = "payload"];
  google.protobuf.Timestamp created_at = 15 [(soundverse_proto.db.v1.db_column) = "created_at"];
  // …22 more db_column-tagged fields…
}

rpc GetTask(GetTaskRequest) returns (GetTaskResponse) {
  option (soundverse_proto.db.v1.target_db) = "prod";
  option (soundverse_proto.db.v1.sql_query) =
    "SELECT id, environment, tool_id, …, payload::text, output::text, …, status, … "
    "FROM generation.tasks WHERE id = NULLIF($1, '')::uuid LIMIT 1";
  option (soundverse_proto.db.v1.require_internal_auth) = true;
}

message GetTaskRequest {
  soundverse_proto.common.v1.RequestContext context = 1;   // SKIPPED — not $1
  string task_id = 2;                                       // this is $1
}
message GetTaskResponse { GenerationTask task = 1; }

From these annotations the plugin emits a GetTask method on GenerationDataServiceDBImpl that runs, in order:

1. Open a span + arm the log. Every generated method starts an OpenTelemetry span (GenerationDataService/GetTask) and defers one structured slog line recording cache status and cache-get / query / cache-set timings — covering every return path, including errors.

2. Check auth (because require_internal_auth = true): read the Authorization header, strip the Bearer prefix, and reject with CodeUnauthenticated anything that isn’t exactly s.InternalSecret. An empty configured secret also rejects.

3. Optionally read cache — skipped here since GetTask has no cache_ttl_seconds. When set, the handler GETs the marshalled response from Redis (key prefixed by s.CachePrefix) and returns on a hit.

4. Pick the pool s.DBPools["prod"]. A missing/nil pool returns CodeInternal “database pool … is not configured” — this is a request-time failure, not a build-time one (see the mounting section).

5. Bind args and query. The request’s scalar fields bind to $N (here task_id → $1), then QueryRow runs and scans each projected column into the response through the db_column map. Because GetTaskResponse is a single nested message (GenerationTask task), the scanner fills that nested message; a repeated field instead triggers a Query + row-loop that appends each row.

6. Map the error faithfully. Every DB error passes through dbErrToConnect, which translates pgx/Postgres errors into the right Connect code instead of a flat Internal.

Scan planning: nested, list, flat, or exec

buildScanPlan decides the shape of the generated body from the response message:

• Single nested message field (GetTaskResponse { GenerationTask task = 1 }) → QueryRow, fill the nested message.
• Repeated message field (ListUserTasksResponse { repeated GenerationTask tasks }) → Query + row loop + append.
• db_column fields directly on the response → scan straight into the response.
• No db_column fields at all → the RPC is a mutation; the plugin emits Exec (used by writes whose SQL has no RETURNING).

Robust enum & timestamp scanning

Enum columns are read through a small svEnumScan adapter and a generated label → number map, so the same generated code works whether status is stored as an int or as a real Postgres ENUM (text label). Timestamps scan into pgtype.Timestamp and convert via timestamppb.New. Nullable scalars scan into pgtype holders (pgtype.Text, pgtype.Int4, …) and only assign when .Valid, so a SQL NULL leaves the proto field at its zero value. payload::text casts the jsonb column so it scans into the string payload_json field.

The $N placeholder rule footgun

This is the single most important trap in the codebase. The plugin binds request fields to $N in renderQueryArgs, with one non-obvious rule:

Message-typed fields consume NO placeholder

The leading common.v1.RequestContext — and any message-typed request field — is skipped entirely. Only flat scalar/enum fields become $N, numbered 1-based after context. So in GetTaskRequest, context is not $1; task_id is $1.

A sql_query that starts at $2 because the author assumed “context is $1” is off-by-one and fails at runtime, not at build time — pgx gets the wrong (or too few) arguments.

This exact off-by-one once silently broke tool registration: RegisterTool, GetActiveTools, and two Upsert* RPCs all started at $2, so a freshly booted worker could never register its tool, claimed no tasks, and chat surfaced a NotFound “no rows in result set”. The fix renumbered everything from $1. Because the guardrails cannot catch this class of bug, the discipline is enforced by convention: never nest a filter or page message in a List* request. Put the cursor and filters as flat scalars (after_created_at, after_id, page_limit) directly on the request — exactly as ListUserTasks does. The db_enum_text args are the one deliberate transform: an enum request field so marked is sent as its lowercase label via a generated int→label map, not the raw int.

Guardrails that fail the build

The plugin is opinionated and refuses to generate dangerous or broken code. These checks — in projectedColumnsFromSQL and buildColumnsForMessage — abort the whole buf generate with a loud, friendly error:

1. No SELECT * / RETURNING * / table.*. A wildcard projection silently breaks the row scanner the moment a column is added in production.

2. No WHERE-less UPDATE / DELETE. That would rewrite or wipe the entire table.

3. Every projected column must have a matching db_column field. If your SELECT asks for a column the response message can’t hold, generation stops and tells you to add the field or drop the column.

4. Only supported scan types. string, bool, int32/64, float, bytes, enum, google.protobuf.Timestamp, and their repeated forms. Anything else (a nested-message column, a map) fails fast.

These are your first line of defence

A failed buf generate with a friendly error is the cheapest place to catch a schema mistake. What it cannot catch is the $N numbering above — that only surfaces at runtime. Audit new sql_query strings by collecting their $N tokens and checking them against the scalar request fields in order.

dbErrToConnect: faithful error codes

Emitted once per generated file, this helper is why callers see meaningful Connect codes instead of an opaque Internal:

DB error	Connect code
pgx.ErrNoRows	CodeNotFound
SQLSTATE 23505 (unique_violation)	CodeAlreadyExists
23502 / 23503 / 23514 (null / FK / check)	CodeInvalidArgument
40001 / 40P01 (serialization / deadlock)	CodeAborted
class 08* (connection_exception)	CodeUnavailable
context.Canceled / DeadlineExceeded	CodeCanceled / CodeDeadlineExceeded
anything else	CodeInternal

The registry & how services get mounted

After the per-service files exist, gen-db-registry walks gen/go/.../*.soundverse_db.go, finds each generated ...DBImpl type with go/ast, and emits one file: gen/go/soundverse_proto/registry/db_registry.go. It exposes a single mounting function, build-provenance accessors (Version(), SourceCommit(), SourceRef(), fed by the PROTO_GO_* env vars at publish time), and DiscoveredRegistries() — the list of mounted DB-backed service names.

gen/go/soundverse_proto/registry/db_registry.go (generated)

func RegisterAllDBServices(
    mux *http.ServeMux,
    dbPools map[string]*pgxpool.Pool,
    redisClient redis.Cmdable,
    internalSecret string,
    cachePrefix string,   // namespaces every Redis key, e.g. "staging:database:"
) error

It is a 5-arg call — the trailing cachePrefix is load-bearing

Some in-repo docs still show a 4-argument RegisterAllDBServices(mux, dbPools, redisClient, internalSecret). The current generated signature takes a fifth argument, cachePrefix, which is prepended to every cache_key / invalidates_keys entry so deploy environments sharing one Redis instance never collide. Each generated ...DBImpl struct carries the matching fields: DBPools, Redis, InternalSecret, CachePrefix.

A consuming Go service mounts every data service with one call. core-database is the only host today — in cmd/server/main.go:

core-database/cmd/server/main.go

dbPools := map[string]*pgxpool.Pool{
    "prod": dbPool,
}
// …
if err := registry.RegisterAllDBServices(
    mux, dbPools, redisClient, cfg.InternalRPCSecret, cfg.CacheKeyPrefix(),
); err != nil {
    log.Fatalf("register services: %v", err)
}

• cfg.InternalRPCSecret is the value behind the INTERNAL_RPC_SECRET env var — the same secret every require_internal_auth handler checks the bearer token against. See Configuring .env.
• cfg.CacheKeyPrefix() returns {env}:database: (env from the ENVIRONMENT var, default local), mirroring the fleet-wide {env}:{namespace}:... Redis convention.
• After mounting, main.go fails fast: it asserts the "prod" pool exists and Pings every configured pool, so the service crash-loops on bad wiring rather than booting green and 500-ing on first traffic.
• core-database serves these over cleartext HTTP/2 (h2c) on its internal listener, so both Connect-Web (HTTP/1.1) and gRPC (h2c) clients hit the same port without TLS at this layer.

Only the `prod` pool is wired today

Every RPC sets target_db = "prod" and the map contains only a "prod" pool (used as the Postgres database name, not a literal environment). Annotate an RPC with a different target_db value without adding that pool to the map and the handler returns CodeInternal “database pool … is not configured” at request time.

schema.sql: hand-maintained reference DDL

Next to each annotated proto sits a schema.sql holding the real Postgres DDL — schemas, enums, tables, indexes. When you touch a DB-backed RPC, four things must always agree:

1. the sql_query column list,
2. the db_column field annotations on the row message,
3. the schema.sql table definition,
4. the request fields bound to $N.

The ::type casts in your SQL must match the live columns — payload::text works because payload is jsonb; NULLIF($1,'')::uuid works because id is a UUID.

There is no migration runner

schema.sql is not generated and not auto-applied. DDL reaches the live cluster by hand, and these files are reference only. Keeping them in sync is a discipline, not an enforced check — see Known limitations and the data schema catalog.

Related

• The contract: proto as source of truth — why the proto is authoritative and how the cascade fans out
• Add a field or RPC to a data service — the hands-on recipe that uses this plugin
• Run proto codegen locally — the exact ordered commands (build the plugin first)
• Drive the proto → deploy cascade — Python auto-bumps; Go consumers bump go.mod by hand
• core-database — the sole host that mounts these handlers
• Proto contract reference — the full annotation + error-code tables