db

Documentation

Overview

Package db owns Postgres connectivity for Aura — pgxpool open, golang-migrate runner, ping/status/reset helpers, and the redactDSN error-wrap discipline (T-1.05-01 mitigation). Per CONTEXT.md D-07 the Config carries both URL (role aura_app, runtime) and MigrateURL (role aura_migrate, DDL only); each subcommand picks the one it needs.

Migrate runner for Aura — golang-migrate v4 with iofs source pointed at the embedded migrations/*.sql files. Roles `aura_app` and `aura_migrate` are created BY GO (EnsureRoles, parametrized DO blocks via pgxpool) BEFORE m.Up() runs, because golang-migrate iofs does NOT support psql variable substitution (`:'var'`). See RESEARCH §Code Examples note line 788 + T-1.05-06 mitigation.

The literal error string in MissingURLFailsFast is LOAD-BEARING — asserted verbatim by TestMigrate_MissingURLFailsFast per D-07. Do not paraphrase.

Index

• func EnsureRoles(ctx context.Context, bootstrapURL, password string) error
• func Migrate(ctx context.Context, migrateURL string) (int, error)
• func Open(ctx context.Context, cfg *Config) (*pgxpool.Pool, error)
• func Ping(ctx context.Context, pool *pgxpool.Pool) (time.Duration, error)
• func Reset(ctx context.Context, migrateURL string) error
• func WithTx(ctx context.Context, pool *pgxpool.Pool, fn func(*sqlc.Queries) error) (err error)
• type Config
• type MigrationRow
  • func Status(ctx context.Context, pool *pgxpool.Pool) ([]MigrationRow, error)

Functions

func EnsureRoles

func EnsureRoles(ctx context.Context, bootstrapURL, password string) error

EnsureRoles bootstraps the aura_app + aura_migrate Postgres roles AND the aura schema (owner aura_migrate). Run by `aura db migrate` BEFORE Migrate() so the 0001_init grants land on existing roles AND so golang-migrate's schema_migrations tracking table has a home inside `aura.*` (Postgres 17 revokes CREATE on `public` from non-owners; without aura ownership the migrate role can't even create the tracker table).

bootstrapURL must point at a role with CREATE ROLE + CREATE SCHEMA privilege (typically the POSTGRES_USER superuser set by compose). The single `password` is applied to both aura_app and aura_migrate for local-dev ergonomics; production environments use AURA_DB_URL / AURA_DB_MIGRATE_URL overrides with per-role credentials and skip EnsureRoles entirely.

Passwords are inlined as SQL literals (Postgres rejects bound params in CREATE/ALTER ROLE PASSWORD clauses) and immediately scrubbed from every error wrap via redactErrorPassword — T-1.05-06 defense-in-depth, asserted by TestEnsureRoles_NoPlaintextInError.

func Migrate

func Migrate(ctx context.Context, migrateURL string) (int, error)

Migrate applies all pending migrations using the migrate role. Returns the count of newly-applied migrations (0 = "no pending"). Empty migrateURL is a fail-fast error matching the D-07 literal.

golang-migrate's schema_migrations tracker lives in `public`; the aura_migrate role gets CREATE on public from EnsureRoles so the tracker can be bootstrapped on a fresh database (Postgres 17+ default-revokes that grant from non-owners). All application tables live under `aura.*`.

func Open

func Open(ctx context.Context, cfg *Config) (*pgxpool.Pool, error)

Open returns a pgxpool.Pool against cfg.URL (role aura_app for the runtime path; cfg.MigrateURL is the migrate-only path consumed by Migrate). Errors always pass through redactDSN so POSTGRES_PASSWORD never leaks into logs (Pitfall #2 / T-1.05-01).

func Ping

func Ping(ctx context.Context, pool *pgxpool.Pool) (time.Duration, error)

Ping runs `SELECT 1` against the pool and returns the measured latency. Used by `aura db ping` and by integration test TestPing.

func Reset

func Reset(ctx context.Context, migrateURL string) error

Reset rolls every migration Down then re-applies them Up. Dev-only — the `aura db reset` CLI handler guards entry with --yes and AURA_RESET_YES=1. migrateURL must be the aura_migrate DSN (Reset is DDL).

func WithTx

func WithTx(ctx context.Context, pool *pgxpool.Pool, fn func(*sqlc.Queries) error) (err error)

WithTx runs fn inside a single Postgres transaction and is the one DRY seam every multi-statement write reuses (D-A2-03): conversations.Store.AppendTurn (atomic INSERT turn + UPDATE aggregates, SC-2), askuser.Store.MarkResumedBatch, and Runner.Stop auto-resolve. It Begins a tx, hands fn a *sqlc.Queries bound to that tx (pgx.Tx satisfies sqlc.DBTX), and on return:

• panic in fn -> Rollback, then re-panic (the panic is not swallowed)
• fn returns non-nil -> Rollback, return fn's error
• fn returns nil -> Commit (a Commit failure surfaces as the returned err)

The named return `err` is read by the deferred closure, so a Commit error replaces a nil fn result.

Types

type Config

type Config struct {
	URL             string        // role aura_app — runtime
	MigrateURL      string        // role aura_migrate — DDL only (PRD amendment #17, D-07)
	BootstrapURL    string        // role POSTGRES_USER (superuser) — CREATE ROLE bootstrap
	Password        string        // shared password for both application roles
	MaxConns        int32         // default 10
	MinConns        int32         // default 1
	MaxConnIdleTime time.Duration // default 30s
}

Config carries the composed Postgres DSNs (D-07: separate runtime vs DDL role) plus pool tuning fields. Zero-valued tuning fields are filled in by Open with sensible defaults (MaxConns=10, MinConns=1, MaxConnIdleTime=30s).

URL / MigrateURL are composed by internal/config from POSTGRES_* primitives (single password fans out to both roles) OR populated verbatim from the AURA_DB_URL / AURA_DB_MIGRATE_URL overrides for managed-Postgres setups.

BootstrapURL is the superuser DSN used by EnsureRoles for CREATE ROLE DDL — composed from POSTGRES_USER (superuser) by default; override via AURA_DB_BOOTSTRAP_URL. Password is carried separately so cmd/aura/db.go can pass it to EnsureRoles without re-parsing the URL.

type MigrationRow

type MigrationRow struct {
	Version int64
	Dirty   bool
}

MigrationRow is one entry from golang-migrate's own `schema_migrations` tracking table. The table is created and managed by golang-migrate's postgres driver — Aura's `aura.knowledge_migrations` audit table is a separate Slice 0.7 concern.

func Status

func Status(ctx context.Context, pool *pgxpool.Pool) ([]MigrationRow, error)

Status returns the rows of `schema_migrations` (golang-migrate's own tracking table). A genuinely missing table (pre-first-migrate, SQLSTATE 42P01) yields an empty slice and nil error — that is the documented first-boot contract. Any other failure (e.g. 42501 insufficient_privilege when the connecting role lacks SELECT on the tracker) is surfaced as a wrapped error rather than masked.