PostgreSQL Persistence, Migrations and Vector Search

• internal/store/postgres.go
• internal/store/postgres_session.go
• internal/store/postgres_incident.go
• internal/store/postgres_report.go
• internal/store/migrate.go
• internal/store/vector.go
• internal/store/tracer.go
• internal/store/embedding_dims.go
• PostgresStore
• Migrate
• QueryTracer
• ValidateEmbeddingDimensions
• DocChunkEmbeddingDimensions

PostgreSQL is the source of truth for every piece of state Korvus keeps: incidents, alerts, sessions, turns, reports, artifacts and the embedded documentation index. The internal/store package holds both the domain types and the contract that the rest of the system programs against, and behind that contract sits one implementation — PostgresStore — written against pgx/v5 and a pgxpool.Pool.

This page describes that implementation layer: how the pool is wired, how the schema is migrated safely when several replicas start at once, how driver errors are translated into domain sentinels, how embeddings are stored and searched with pgvector, and how database calls are traced. The domain types and the Store interface itself are described in Storage Domain Model and Store Interfaces; the process-level wiring that builds the pool and calls the migrator is in korvus CLI and Process Wiring.

The store and how it is built

PostgresStore is a thin struct holding nothing but the pool it was handed (PostgresStore). The constructor is equally thin: it wraps an already-configured pool and deliberately does not dial, migrate, or probe connectivity (NewPostgresStore). That separation matters because a wrong DSN should be reported against the startup sequence, not against the first query that happens to run — so the pool is built and pinged in the CLI, then migrations are applied, and only then is the store constructed (see korvus CLI and Process Wiring).

The full contract is asserted at compile time so that a missing method surfaces where the code has to change rather than where the store happens to be wired:

var _ Store = (*PostgresStore)(nil)

Every method borrows a connection from the pool for the duration of one statement, except where a transaction is explicitly required. The transaction helper is inTx, which rolls back on both error and panic, and rolls back with context.WithoutCancel — a rollback issued on an already-cancelled context would itself be cancelled and leave the transaction to be reaped by the server, still holding its locks.

Around that core sit a handful of boundary normalisers. Timestamps are converted to UTC at the boundary so a caller who built a time.Time in local time cannot write a row that reads back with a different wall clock (utc, utcPtr). A nil label or annotation map becomes an empty map, because a nil Go map encodes to JSON null rather than {}, which the NOT NULL DEFAULT '{}' column rejects (jsonMap). A caller-supplied page size is clamped into a sane band so a paginated read cannot be turned into a full table scan by an empty or hostile filter (clampLimit).

Translating driver errors into domain errors

Callers of the store must not import pgx to interpret a miss or a constraint violation, so all driver errors pass through one function: wrapErr. pgx.ErrNoRows becomes ErrNotFound; a unique violation (23505) becomes ErrConflict; a foreign-key violation (23503) becomes ErrInvalidReference. The constraint name is attached to the message, because on this schema a foreign-key violation usually means the composite (session_id, incident_id) key rejected a row whose session belonged to a different incident — the cross-incident isolation guarantee doing its job, not a transient failure (ErrInvalidReference).

The distinction between "conflict" and "invalid reference" is what lets the transport layer map a duplicate write and a cross-incident write onto different error codes instead of collapsing both into a generic failure (Connect API Server and Service Handlers).

Incident, session and report writes

The three domain areas are split across separate files that share the same store.

Incidents live in postgres_incident.go. Every read shares one projection, incidentColumns, so scanning lives in exactly one place and adding a column means touching that list and scanIncident together (scanIncident). Listing is keyset-paginated rather than OFFSET-based: the cursor carries the last row's (created_at, id) and the query asks for rows strictly before it, so an incident created mid-scroll can neither be skipped nor repeated (ListIncidents). The cursor itself is an opaque base64 token, and the function that produces the next one lives beside the encoder rather than on the filter type, because its shape is a storage detail (NextIncidentCursor).

The incident write paths encode their invariants in SQL rather than in read-then-write logic in Go. UpdateIncident updates only the mutable descriptive fields and is conditional on closed_at IS NULL, which is what makes "a closed incident is never edited" true even when a close lands between a caller's read and its write. CloseIncident is likewise conditional, so two concurrent closes cannot both report success. When a conditional write matches no row, the ambiguity between "no such incident" and "already closed" is resolved by a follow-up read rather than collapsed into ErrNotFound — an operator closing an incident twice needs to be told it was already closed (classifyMissingIncident). The lifecycle rules behind these transitions are described in Incident Manager: Open, List, Update, Close, and the alert-driven path that opens them in Alert Ingestion, Fingerprinting and Debounce.

Sessions live in postgres_session.go. The interesting part is sequence allocation: CreateSession takes a transaction-scoped advisory lock keyed on (seqLockNamespace, hashtext(incident_id)) before computing MAX(seq)+1. The file documents the two cheaper designs and why both were rejected under measurement — reading MAX(seq) in Go loses outright under concurrency, and computing it in an INSERT subquery is merely optimistic, since concurrent transactions read the same snapshot and losers collide and depend on a retry. The lock makes allocation correct by construction, so there is no retry budget to tune. The seqLockNamespace constant lives in the separate two-argument advisory lock space, so it can never collide with the migration lock key (seqLockNamespace). Reopening a finished session is likewise a conditional UPDATE that doubles as the lock, which is what makes exactly one of two concurrent reopens win (ReopenSession); the surrounding session lifecycle is covered in Session Persistence, Reopen and Scratch Seeding.

Reports and artifacts live in postgres_report.go. A report row is created with a caller-stated revision, and a revision below 1 is refused in Go before the database's CHECK refuses it with only a constraint name (CreateReport). The report schema itself is documented in Incident Report Schema.

Migrations

Schema changes are applied by Migrate, which runs goose over the SQL files embedded into the binary (//go:embed migrations/*.sql, migrationsFS). The public function is a thin wrapper over an internal migrate that takes the apply step as a parameter, so the migration tests can stop a database at an older version and fill it before the next migration runs.

Two things make a concurrent start-up safe. First, the whole run is serialised by a session-scoped PostgreSQL advisory lock: the losing replica blocks until the winner finishes, and then finds nothing left to apply. The lock is taken on a pinned connection, because pg_advisory_lock is scoped to the backend session — locking and unlocking through the pool at large could land on two different connections and leak the lock for the lifetime of the backend (acquireMigrationLock). The lock key is an arbitrary but stable constant that no other Korvus code may reuse (migrationLockKey).

Second, the pool size is checked rather than assumed. A migration run needs two connections at once — one pinned for the lock, one for goose — so a MaxConns of 1 would deadlock with the lock connection held while goose waited for a connection that could never be released, surfacing only as a context deadline with no stated cause. The check returns a named error instead (ErrPoolTooSmall).

The application itself wires this into the ordered start-up sequence: pool first, then migrations, then the store, because the store reads the schema the migration creates.

Vector storage and search

The documentation index stores one embedding per chunk in a pgvector column. Two constants anchor the width of that column — docChunkDim in the store layer and the configuration's embedding dimension — and the store's copy is fixed by migration 001 (docChunkDim).

Korvus encodes pgvector's text format itself rather than depending on pgvector-go, because the dependency set is fixed and the entire surface needed is one array in and one array out. vectorLiteral writes each element with 'f' and precision -1, which round-trips a float32 exactly: the stored vector is the one the embedding model produced, not a rounded copy. parseVector decodes it back.

Writes go through ReplaceDocChunks, which deletes and re-inserts the whole chunk set for one workspace ref inside a single transaction, so a reader never observes a partially rebuilt index and silently answers from incomplete context. Every chunk's embedding width is validated before the write, because pgvector's own rejection names neither the path nor the chunk number.

Search goes through SearchDocChunks, which orders by the cosine distance operator <=> to match the HNSW vector_cosine_ops index migration 001 builds — a different operator would silently degrade to a sequential scan. The query embedding's width is checked before the query is sent, and a non-positive topK defaults to 10. Garbage collection after a workspace sync is PruneDocChunks, which refuses an empty keep-set rather than emptying the index of the revision being served. HasDocChunks reports whether a ref has been indexed at all, which is what lets the indexer skip re-embedding an unchanged ref after a restart. The chunking and indexing pipeline that feeds these calls is in Documentation Chunking, Indexing and Search, and the sync that triggers a prune is in Workspace Sync, Checkout and Materialization.

Catching a width mismatch at startup

A configured embedding width that does not match the database column is a startup failure, not a first-sync failure. DocChunkEmbeddingDimensions reads pg_attribute for the connected database's docs_chunks.embedding column rather than returning the in-binary constant: comparing a configured width against a constant that moves in the same commit would be a tautology, and would pass against a database migrated by a different Korvus version — exactly the deployment where the check has to fire. An unconstrained vector column is refused too, since the HNSW index and every insert path assume a fixed width.

ValidateEmbeddingDimensions compares the two and names both, plus the model, in the failure message — "which of my two numbers is wrong" is the whole question an operator has at that moment. It accepts any reader with a single method, so the wiring test can drive it without a database, and the CLI calls it right after migrations and before the embedder is built (buildEmbedder).

Tracing database calls

QueryTracer emits one span per database call so the writes an incident produces appear inside the same trace as the agent loop that caused them. The type implements exactly two of pgx's five independent tracer interfaces — pgx.QueryTracer for Query/QueryRow/Exec and pgx.BatchTracer for SendBatch — and deliberately not CopyFromTracer or PrepareTracer, because nothing in the tree calls those and a tracer for an absent call path could not be tested. A compile-time assertion pins the claim that the doc comment makes, since pgx checks it with silent type assertions.

The query span records the SQL text but deliberately not the arguments: Korvus's query arguments carry incident narrative and alert payloads, and spans are exported to a backend with a different access model than the database (TraceQueryStart). A pgx.ErrNoRows result is recorded as an attribute rather than as a span error — "this row does not exist" is an ordinary answer that the store maps to ErrNotFound, and marking it red would make every existence check look like a database failure on an error-rate panel (TraceQueryEnd).

A batch is covered by a single db.batch span, with each statement recorded as an event on it rather than as a child span (TraceBatchStart, TraceBatchQuery). pgx gives no per-statement start hook, so a child span would have to invent its duration; an event is a timestamped point, which is exactly what pgx reports. The batch span closes with the same no-rows rule as the query span (TraceBatchEnd).

The tracer must be attached before the pool is opened, because pgx reads ConnConfig.Tracer at pool construction and offers no way to attach one afterwards. It also resolves the global provider lazily, so building it before telemetry is installed is harmless — but building it after an explicit noop provider has been installed is fatal, because delegation is once-only. The wider telemetry story is in OpenTelemetry Setup and GenAI Semantic Attributes.

Relationships

flowchart TD
    cfg["configuration"] --> pool
    pool["pgxpool.Pool"] --> `openPool`
    `openPool` --> `store.NewQueryTracer`
    `openPool` --> `NewPostgresStore`
    pool --> `Migrate`
    `Migrate` --> `acquireMigrationLock`
    `Migrate` --> goose["goose"]
    `NewPostgresStore` --> `PostgresStore`
    `PostgresStore` --> `inTx`
    `PostgresStore` --> `wrapErr`
    `PostgresStore` --> `CreateIncident`
    `PostgresStore` --> `UpdateIncident`
    `PostgresStore` --> `CloseIncident`
    `PostgresStore` --> `CreateSession`
    `PostgresStore` --> `ReopenSession`
    `PostgresStore` --> `CreateReport`
    `PostgresStore` --> `ReplaceDocChunks`
    `PostgresStore` --> `SearchDocChunks`
    `PostgresStore` --> `PruneDocChunks`
    `ReplaceDocChunks` --> `vectorLiteral`
    `SearchDocChunks` --> `vectorLiteral`
    `SearchDocChunks` --> `parseVector`
    `PostgresStore` --> `DocChunkEmbeddingDimensions`
    `DocChunkEmbeddingDimensions` --> `ValidateEmbeddingDimensions`
    `QueryTracer` --> `PostgresStore`

Error handling

Reference

Generated by astrolabe from commit 0c2dca73 on branch main.