internal/platform/db

internal/platform/db is the sole sanctioned factory for the shared PostgreSQL connection pool that every bounded context consumes. Direct imports of github.com/jackc/pgx/** from any bounded context under internal/{identity,mesh,policy,access,secrets,state,actions,provisioning,audit,labels,observability,bridge}/** are rejected by the no-direct-persistence-from-contexts depguard rule in .golangci.yml.

This document is the authoritative reference for:

Config fields, types, and defaults
Constructing a pool
Readiness probe
Migration API (core and SpiceDB trees)
sqlc workflow

Config

The exported db.Config struct is the single entry point for tuning the pool. The zero value is intentionally invalid: DSN must be supplied. Every other field has a documented default applied by Config.WithDefaults; db.New applies defaults internally so callers do not need to call it explicitly.

Field	Type	Default	Purpose
`DSN`	`string`	— (REQUIRED)	PostgreSQL connection string (URL or libpq keyword=value). Passwords are redacted from every error via `redactDSN`.
`MinConns`	`int32`	`2` (`DefaultMinConns`)	Minimum idle connections the pool keeps warm.
`MaxConns`	`int32`	`10` (`DefaultMaxConns`)	Upper bound on concurrent Postgres sessions per replica.
`ConnMaxIdleTime`	`time.Duration`	`30s` (`DefaultConnMaxIdleTime`)	Ceiling on idle-connection lifetime before recycle.
`HealthCheckPeriod`	`time.Duration`	`30s` (`DefaultHealthCheckPeriod`)	Interval of pgx's per-connection background health check.
`StatementCacheMode`	`string`	`"describe"` (`DefaultStatementCacheMode`)	Prepared-statement description cache mode.

Config.Validate rejects:

empty DSN — error tagged (REQ-001, PX-0003)
unparseable DSN — redacted before being embedded in the error
negative MinConns, non-positive MaxConns, or MinConns > MaxConns — each tagged (REQ-007, PX-0003)

Validation is pure: it never opens a network connection.

Constructing a pool

db.New(ctx, cfg, logger) returns a *pgxpool.Pool safe for concurrent use. A nil logger is replaced with slog.Default() so wiring callers can pass a bare Config during bootstrap before the structured-logger fan-out lands. Close is idempotent per pgx's contract — callers MUST invoke pool.Close() during shutdown to release Postgres-side resources.

package main

import (
    "context"
    "log/slog"
    "os"
    "time"

    "github.com/plexsphere/plexsphere/internal/platform/db"
    "github.com/plexsphere/plexsphere/internal/platform/health"
)

func main() {
    ctx := context.Background()
    logger := slog.New(slog.NewJSONHandler(os.Stdout, nil))

    cfg := db.Config{
        DSN:               "postgres://plexsphere:REDACTED@postgres:5432/plexsphere?sslmode=require",
        MinConns:          2,
        MaxConns:          10,
        ConnMaxIdleTime:   30 * time.Second,
        HealthCheckPeriod: 30 * time.Second,
    }

    pool, err := db.New(ctx, cfg, logger)
    if err != nil {
        logger.Error("db.New failed", slog.Any("err", err))
        os.Exit(1)
    }
    defer pool.Close()

    registry := health.NewRegistry()
    registry.Register(db.ProbeName, db.ProbeFunc(pool))
}

Readiness probe

db.ProbeFunc(pool) returns a health.ProbeFunc that issues a single Ping per /readyz evaluation and forwards the caller's context so the registry's per-probe timeout applies without modification.

Symbol	Value
`db.ProbeName`	`"db-primary"`
Error suffix	`(REQ-006, PX-0003)`

registry.Register(db.ProbeName, db.ProbeFunc(pool))

The probe is safe to register from init paths because it does not open a new connection — it borrows from the existing pool.

Migration API (goose)

Migrations live in two sibling embed.FS sets so operators can replay the SpiceDB bootstrap against a privileged superuser DSN without mixing privileges with the platform-level migrations:

internal/platform/db/migrations.CoreFS — the plexsphere core schema. Bookkeeping table: plexsphere_migrations.goose_db_version.
internal/platform/db/spicedb.SpiceDBFS — the spicedb logical database bootstrap. Bookkeeping table: spicedb.goose_db_version.

Core tree

import "github.com/plexsphere/plexsphere/internal/platform/db/migrations"

if err := migrations.Up(ctx, pool); err != nil { /* ... */ }
if err := migrations.Down(ctx, pool); err != nil { /* ... */ }

status, err := migrations.Status(ctx, pool)
// status is []MigrationStatus { Version, Source, Applied }

Up is idempotent: goose skips migrations already recorded in the bookkeeping table, so a redundant Up is a no-op. Down rolls back the most recent migration; it is intended for test teardown and manual operator use — production deployments should not invoke it.

SpiceDB tree

import "github.com/plexsphere/plexsphere/internal/platform/db/spicedb"

// SPICEDB_ROLE_PASSWORD must be provisioned out-of-band before bootstrap
// (see "Security invariants" below).
if err := spicedb.ApplySpiceDBBootstrap(ctx, superuserDSN, logger); err != nil {
    switch {
    case errors.Is(err, spicedb.ErrWrongTarget):
        // DSN pointed at the core database; refuse before any schema change.
    case errors.Is(err, spicedb.ErrSuperuserDSNMissingPassword):
        // DSN has no password component; would rely entirely on
        // pg_hba.conf for auth. Refuse.
    case errors.Is(err, spicedb.ErrRolePasswordMissing):
        // SPICEDB_ROLE_PASSWORD unset. Refuse — a passwordless role
        // is a credential-free back door.
    }
}

ApplySpiceDBBootstrap refuses any DSN whose target database is not the SpiceDB admin database. The sentinel spicedb.ErrWrongTarget is exported so callers can distinguish a wiring mistake from a runtime error via errors.Is. Passwords embedded in superuserDSN are redacted from every wrapped error and log line.

Security invariants

ApplySpiceDBBootstrap enforces two invariants at startup so the SpiceDB role cannot end up as a credential-free LOGIN role whose safety depends on pg_hba.conf:

SPICEDB_ROLE_PASSWORD MUST be set to a non-empty password. The value is injected into the CREATE ROLE spicedb LOGIN PASSWORD '…' (or matching ALTER ROLE) statement at bootstrap time so the role is never persisted without a password. Single quotes in the password are doubled per the SQL standard; no other escaping is performed. Unset or empty → spicedb.ErrRolePasswordMissing.
superuserDSN MUST carry a password component. A DSN of the shape postgres://postgres@host/spicedb typically points at a cluster configured with trust or peer auth, where any client reachable on the Postgres port can assume the superuser role. We refuse that surface. Missing or empty password → spicedb.ErrSuperuserDSNMissingPassword.

Operators must provision SPICEDB_ROLE_PASSWORD out-of-band — e.g. through OpenBao, a Kubernetes Secret, or a CI job secret — and pair the cluster's pg_hba.conf with scram-sha-256 for the spicedb role. Re-running ApplySpiceDBBootstrap is idempotent: the role's password is re-set via ALTER ROLE … WITH LOGIN PASSWORD …, so the same call acts as a rotation primitive.

The bootstrap SQL (0001_spicedb_bootstrap.sql) additionally emits REVOKE ALL ON SCHEMA spicedb FROM PUBLIC; before granting to the spicedb role, so no other Postgres role inherits access through the default PUBLIC grant.

Accepted admin-DB name patterns

ApplySpiceDBBootstrap accepts the following database names as valid SpiceDB admin targets (the requireSpiceDBTarget rule):

Pattern	Example
Exactly `spicedb`	`postgres://…/spicedb`
Suffix `_spicedb`	`postgres://…/platform_spicedb`
Suffix `-spicedb`	`postgres://…/platform-spicedb`

Any other database name yields spicedb.ErrWrongTarget before any schema change is attempted.

Tenancy schema

internal/platform/db/migrations/0002_tenancy.sql adds the six tenancy tables (plexsphere.domains, projects, resources, nodes, project_mesh_ip_reservations, domain_mesh_ip_allocations) plus the transactional plexsphere.outbox_events relay source. The ERD, glossary, invariant-to-test matrix, and allocator rules are owned by docs/contexts/identity/tenancy.md. The migration needs the btree_gist extension (the GIST exclusion that blocks overlapping sub-ranges) and 0001_init.sql's schema; both use IF NOT EXISTS so a repeat migrations.Up is idempotent.

Identity schema

internal/platform/db/migrations/0003_identity.sql adds the five identity tables (plexsphere.idp_bindings, users, user_sessions, service_identities, api_tokens). Every row relates to exactly one plexsphere.domains parent via a RESTRICT foreign key so a Domain deletion cannot orphan identity state. The operator runbook, claim-mapping, acr/amr examples, and API-token contract are owned by docs/contexts/identity/idp.md.

Schema-level invariants that back aggregate-level contracts:

Partial unique index idp_bindings_active_domain_issuer_uq — only one active IdPBinding per (domain_id, issuer). Deactivated and degraded rows are preserved for audit history but excluded from the uniqueness check.
UNIQUE (domain_id, external_subject) on plexsphere.users — idempotent JIT provisioning.
UNIQUE (session_hash) on plexsphere.user_sessions — the server-side SHA-256 of the session cookie is the authoritative ledger entry; plaintext cookies never touch the database.
CHECK federation_kind IN ('oidc_cc', 'spiffe_svid', 'api_token') on plexsphere.service_identities — pins the credential shape authn accepts.
CHECK api_tokens_exactly_one_owner — XOR on (user_id IS NOT NULL, service_identity_id IS NOT NULL) so every API token binds to exactly one identity.
CHECK hash_algo = 'argon2id' and UNIQUE (token_hash) — Argon2id is the only accepted hash; collisions under RNG failure are rejected before they reach the aggregate.

The identity migration declares no new extensions and reuses the plexsphere schema created by 0001_init.sql; every statement is IF NOT EXISTS so a repeat migrations.Up stays idempotent .

Operator CLI

The Go-built binary tools/migrate wraps the embedded goose runners for operator use — see docs/how-to/platform/run-migrations.md for the end-to-end procedure.

text

DATABASE_URL=postgres://... migrate [--target=core|spicedb] <up|down|status|version>

The tool exits 0 on success, 1 on runtime error, 2 on usage error. make migrate invokes it with up as the default subcommand.

sqlc workflow

Type-safe query code is generated by sqlc from .sql files under internal/platform/db/queries/. The generated Go package lands under internal/platform/db/gen/ and is committed so make generate-check can detect drift between the queries and the produced code.

The sqlc configuration (internal/platform/db/sqlc.yaml) pins:

engine: postgresql
queries: ./queries
schema: ./migrations
gen.go.package: sqlcgen
gen.go.out: ./gen
gen.go.sql_package: pgx/v5
gen.go.emit_pointers_for_null_types: true

Adding a query

Create or edit a file under internal/platform/db/queries/. Each named query follows sqlc's -- name: convention, e.g.
sql
```
-- name: GetPlatformVersion :one
SELECT service, version, applied_at
FROM plexsphere._platform_version;
```
Run make sqlc-generate (or sqlc generate from internal/platform/db/). The generated code lands in internal/platform/db/gen/.
Run make generate-check — it fails if the generated code differs from the committed tree.
Import the generated package only from internal/platform/db or a sanctioned application-layer consumer; never from a bounded context.

Groups schema

The three tables shipped by 0004_groups.sql back the Group, GroupMembership, and GroupParent aggregates in internal/identity/groups. They are the persistent footprint of the bounded context documented in docs/contexts/identity/groups.md. Every row below mirrors the ubiquitous language the aggregates and the sqlc queries speak.

Table	Purpose	PK	Key FKs
`plexsphere.groups`	Group aggregate scoped to exactly one Domain; either operator-curated (`source='manual'`) or mirrored from an upstream IdP (`source='idp'`).	`id` (uuid)	`domain_id` → `plexsphere.domains(id)` ON DELETE RESTRICT; `idp_binding_id` → `plexsphere.idp_bindings(id)` ON DELETE RESTRICT (NULLable, required when `source='idp'`).
`plexsphere.group_memberships`	Edge between a Group and exactly one principal (User, ServiceIdentity, or another Group).	composite via `group_memberships_unique_edge` UNIQUE INDEX	`group_id` → `plexsphere.groups(id)` ON DELETE CASCADE; `principal_user_id`, `principal_service_identity_id`, `principal_group_id` — each nullable, exactly one non-NULL.
`plexsphere.group_parents`	Directed edge from a parent Group to a child Group modelling nested Groups.	`(parent_id, child_id)`	Both `parent_id` and `child_id` → `plexsphere.groups(id)` ON DELETE CASCADE.

Named constraints

The constraint names below are load-bearing: the repository layer classifies pgerrcode 23505/23514/23503 trips back to domain sentinels (ErrConflict, ErrInvariant, ErrSourceMismatch) by matching on the constraint name, so renaming any of them is a breaking change that must ripple through internal/identity/groups/repo/errors.go in the same commit.

Constraint	Table	Kind	Invariant
`groups_domain_slug_uq`	`plexsphere.groups`	UNIQUE `(domain_id, slug)`	Slug is unique per Domain. A duplicate INSERT trips 23505, mapped to `ErrConflict`.
`groups_source_idp_requires_binding`	`plexsphere.groups`	CHECK	`source='idp'` rows MUST carry a non-NULL `idp_binding_id` and a non-empty `idp_claim_value`; `source='manual'` rows MUST carry neither. Trips 23514 → `ErrInvariant`.
`groups_slug_check` (inline CHECK on `slug`)	`plexsphere.groups`	CHECK	Slug matches the kebab-case regex pinned in `0004_groups.sql` — identical to the pattern the `groups.NewGroup` constructor enforces in Go, so an aggregate-level rejection and a DB-level rejection cover the exact same shape.
`group_memberships_principal_xor`	`plexsphere.group_memberships`	CHECK	Principal XOR: exactly one of `principal_user_id`, `principal_service_identity_id`, `principal_group_id` is non-NULL. Trips 23514 → `ErrInvariant`.
`group_memberships_unique_edge`	`plexsphere.group_memberships`	UNIQUE INDEX (with COALESCE to the zero UUID)	Idempotent adds of the same (group, principal) edge trip 23505; the repository treats this as a no-op upsert.
`group_memberships_source_matches_parent`	`plexsphere.group_memberships`	Trigger → CHECK violation	Membership.source must equal parent Group.source; a `source='manual'` edge into an `idp` Group (or vice versa) raises 23514 → `ErrSourceMismatch`. Enforced via the `group_memberships_source_matches_tg` `BEFORE INSERT OR UPDATE` trigger so mis-wired direct INSERTs cannot bypass the aggregate.
`group_parents_no_self_parent`	`plexsphere.group_parents`	CHECK	`parent_id <> child_id`; the trivial self-cycle is rejected at the schema boundary. Deeper cycles are prevented aggregate-side (see Resolver semantics for the `MaxDepth=32` cap) because a recursive SQL check would have to walk the graph on every INSERT.
PK `(parent_id, child_id)` on `plexsphere.group_parents`	`plexsphere.group_parents`	UNIQUE (primary key)	A second INSERT of the same directed edge trips 23505 → `ErrConflict`.

Schema invariants at a glance

Three invariants are worth calling out separately because they cross more than one table and are the ones most likely to be violated by a hand-rolled INSERT that bypasses the aggregate layer:

Principal XOR (group_memberships_principal_xor). Every membership row names exactly one principal. Zero or two non-NULL principal columns trip 23514; the repository classifier converts this to ErrInvariant.
Source-matches-parent (group_memberships_source_matches_parent via the group_memberships_source_matches_tg trigger). A source='manual' membership into a Group with source='idp' — or vice versa — is rejected by the trigger with SQLSTATE 23514, which the repository classifier converts to ErrSourceMismatch. This is the schema-level companion to the groups.NewMembership constructor guard.
No self-parent (group_parents_no_self_parent). A row where parent_id = child_id is rejected at the CHECK boundary. This does NOT cover deeper cycles; the resolver in internal/identity/groups/resolver.go enforces the MaxDepth=32 cap at the application layer.

sqlc hooks

The named queries for these tables live in internal/platform/db/queries/30_groups.sql and are regenerated into internal/platform/db/gen/ by make sqlc-generate. Every query is transactional — aggregate mutations ride alongside an outbox append in the same runInTx call so the "one aggregate, one outbox row per transaction" invariant survives every write path.

Cross-references

../../contributing/layout.md — the bounded-context map locating this package in the codebase.
../../../internal/platform/db/ — the package source.
../index.md — the Reference quadrant index.

internal/platform/db ​

Config ​

Constructing a pool ​

Readiness probe ​

Migration API (goose) ​

Core tree ​

SpiceDB tree ​

Security invariants ​

Accepted admin-DB name patterns ​

Tenancy schema ​

Identity schema ​

Operator CLI ​

sqlc workflow ​

Adding a query ​

Groups schema ​

Named constraints ​

Schema invariants at a glance ​

sqlc hooks ​

Cross-references ​