Srql Fixtures DB Tests

SRQL Fixtures DB Tests

Use this skill for a focused Mix test against a separately created scratch database on the shared CNPG cluster in the Kubernetes srql-fixtures namespace. The full async/serial serviceradar_core guarded lifecycle is CI-only: it runs in the in-cluster BuildBuddy workflow with typed SERVICERADAR_ENV=ci configuration.

Guardrails

• Do not print database passwords or full URLs containing credentials.
• Use a scratch database named with a unique prefix, for example codex_<topic>_<timestamp>_<pid>.
• Verify the CNPG certificate. For a NodePort IP used by a focused scratch test, set the certificate's DNS name through SRQL_TEST_DATABASE_SERVER_NAME and use sslmode=verify-full with the fixture CA.
• Prefer the existing NodePort/LoadBalancer service over kubectl port-forward for the scratch database; port-forwarding to CNPG is flaky and should be fallback only.
• Keep the Ecto sandbox pool small for an individual scratch-DB test. The guarded async lane runs max_cases=8 with pool_size=12; its four extra checkout slots are BEAM-internal headroom for test-supervised child processes, not workstation or deployment capacity.
• Only the in-cluster BuildBuddy workflow may run guarded lanes. It creates disposable sr_core_test_<run-id>_<lane> clones on srql-fixtures using typed ci configuration. Never point a guarded lifecycle at demo, production, or any non-disposable database.
• Do not invent a NodePort/config override for guarded lanes and do not forward fixture secrets to remote actions.
• Drop the scratch database when finished unless the user asks to keep it for inspection.

Workstation NodePort Discovery For A Scratch Database

From the repo root:

kubectl get pods -n srql-fixtures -l cnpg.io/cluster=srql-fixture -L cnpg.io/instanceRole -o wide
kubectl get svc srql-fixture-rw-ext -n srql-fixtures -o wide
kubectl get nodes -o wide
kubectl get secret srql-test-admin-credentials -n srql-fixtures -o json | jq -r '.data | keys[]'

Use the srql-fixture-rw-ext service for write tests. It is currently exposed as NodePort 30818 and may also advertise an external LoadBalancer IP; verify routeability before choosing the host. Read admin credentials into shell variables without echoing the password:

ADMIN_USER=$(kubectl get secret srql-test-admin-credentials -n srql-fixtures -o jsonpath='{.data.username}' | base64 -d)
ADMIN_PASS=$(kubectl get secret srql-test-admin-credentials -n srql-fixtures -o jsonpath='{.data.password}' | base64 -d)
ADMIN_PASS_ENC=$(printf '%s' "$ADMIN_PASS" | jq -sRr @uri)
TLS_SERVER_NAME=srql-fixture-rw.srql-fixtures.svc.cluster.local
CA_FILE="${TMPDIR:-/tmp}/srql-fixture-ca-$$.crt"
umask 077
kubectl get secret srql-fixture-server-ca -n srql-fixtures -o jsonpath='{.data.ca\.crt}' | \
  base64 -d > "$CA_FILE"

Pick a reachable host/port. From the usual workstation, 192.168.10.31:30818 has been reachable while the advertised LoadBalancer IP may not be:

NODEPORT=$(kubectl get svc srql-fixture-rw-ext -n srql-fixtures -o jsonpath='{.spec.ports[0].nodePort}')

for host in 192.168.10.31 192.168.10.96 $(kubectl get nodes -o jsonpath='{range .items[*]}{.status.addresses[?(@.type=="InternalIP")].address}{" "}{end}'); do
  if PGPASSWORD="$ADMIN_PASS" psql \
    "host=$TLS_SERVER_NAME hostaddr=$host port=$NODEPORT dbname=postgres user=$ADMIN_USER sslmode=verify-full sslrootcert=$CA_FILE connect_timeout=4" \
    -v ON_ERROR_STOP=1 -Atc 'select 1' >/dev/null 2>&1; then
    DB_HOST="$host"
    DB_PORT="$NODEPORT"
    break
  fi
done

test -n "${DB_HOST:-}" || { echo "no reachable srql-fixtures NodePort host"; exit 1; }

Guarded Serviceradar Core Lanes: BuildBuddy Workflow Only

Do not run the full guarded lifecycle from a workstation or substitute the NodePort coordinates above. The guarded Elixir lane and Rust lifecycle resolve their one legitimate endpoint from the typed SERVICERADAR_ENV=ci instance; legacy SRQL_TEST_* endpoint coordinates are deliberately ignored so provisioning and execution cannot diverge.

Use the in-cluster BuildBuddy workflow in buildbuddy.yaml for the complete sequence:

private fixture setup -> sweep_stale_dbs -> cleanup_generations -> prepare_generation
  -> migrate_generation (only on needs_migration) -> prepare_generation (must be ready)
  -> provision_generation -> lane tests -> teardown_db -> release_generation

Every run clones its lanes from an immutable schema generation, sr_tpl_<first 48 hex of the digest>, where the digest is //build/schema_template:manifest's hash of the migrations, baseline, helpers and construction inputs. A checkout with new migrations gets a new digest and its own generation, which //elixir/serviceradar_core:migrate_generation builds by full replay; a ready generation is never written again, so one branch's unmerged migrations can never become the schema another branch clones. Generation count, concurrent builders, storage, retention and lease length are bounded by build/schema_template/policy.json, and cleanup_generations reclaims only idle, unleased, unconnected generations. See docs/docs/ci-schema-templates.md.

The legacy sr_core_template lifecycle is retired: its targets are deleted and the guard rejects the name. For one lane, provision_generation_<lane> clones only that lane's database. Retirement and rollback prerequisites are owned by the SRQL fixture runbook.

That workflow owns SERVICERADAR_ENV=ci, the typed configuration inputs, the private secret environment, capacity observer, run ID, and caller-owned cleanup. It keeps secret-bearing test actions local to the workflow runner instead of forwarding secrets to remote actions. Do not recreate those inputs from SRQL_FIXTURE_HOST, NodePort values, a direct DSN, or a new override.

One provision_generation invocation clones a disposable srql-fixtures database for every ordinary lane -- integration_tests_async and integration_tests_serial_0 through integration_tests_serial_6 -- and the lanes are then selected by tag. The async target runs at max_cases=8; each serial target runs at max_cases=1; every lane receives its own clone. The large-ingestion gate uses provision_generation_large_ingestion for its dedicated database. Demo and production are never valid targets for this lifecycle.

Create A Workstation Scratch Database

Create an isolated scratch database through the reachable NodePort endpoint. The direct SERVICERADAR_TEST_DATABASE_URL used below is for this focused Mix workflow only; it does not configure a guarded Bazel lane.

DB="codex_${USER:-agent}_$(date +%s)_$$"
PGPASSWORD="$ADMIN_PASS" psql \
  "host=$TLS_SERVER_NAME hostaddr=$DB_HOST port=$DB_PORT dbname=postgres user=$ADMIN_USER sslmode=verify-full sslrootcert=$CA_FILE" \
  -v ON_ERROR_STOP=1 \
  -c "CREATE DATABASE $DB"

Run current branch migrations with mix serviceradar.db.migrate, not mix ecto.migrate (see AGENTS.md): on an empty database it applies the committed baseline instead of replaying every migration, and it records the applied versions in platform.ash_schema_migrations as well, so web-ng's migrations gate accepts the database. It needs a pool of at least 2, because the migration lock holds one connection while the migrator uses another; the queue settings keep it from timing out when the workstation or the fixture is under load:

cd elixir/serviceradar_core
SERVICERADAR_TEST_DATABASE_URL="postgres://${ADMIN_USER}:${ADMIN_PASS_ENC}@${DB_HOST}:${DB_PORT}/${DB}?sslmode=verify-full" \
SRQL_TEST_DATABASE_SERVER_NAME="$TLS_SERVER_NAME" \
SRQL_TEST_DATABASE_CA_CERT_FILE="$CA_FILE" \
SERVICERADAR_TEST_DATABASE_POOL_SIZE=2 \
SERVICERADAR_TEST_DATABASE_QUEUE_TARGET_MS=10000 \
SERVICERADAR_TEST_DATABASE_QUEUE_INTERVAL_MS=10000 \
MIX_ENV=test mix serviceradar.db.migrate

The test configuration (config/test_database_guard.exs) refuses any database whose name does not match codex_[a-z0-9_]+ (lowercase only; sr_core_test_* is reserved for CI lanes), so keep the prefix from the CREATE DATABASE step above.

Run Focused Tests

Use the same database URL and small pool. Add queue settings for slower fixture runs:

cd elixir/serviceradar_core
SERVICERADAR_TEST_DATABASE_URL="postgres://${ADMIN_USER}:${ADMIN_PASS_ENC}@${DB_HOST}:${DB_PORT}/${DB}?sslmode=verify-full" \
SRQL_TEST_DATABASE_SERVER_NAME="$TLS_SERVER_NAME" \
SRQL_TEST_DATABASE_CA_CERT_FILE="$CA_FILE" \
SERVICERADAR_TEST_DATABASE_POOL_SIZE=1 \
SERVICERADAR_TEST_DATABASE_QUEUE_TARGET_MS=10000 \
SERVICERADAR_TEST_DATABASE_QUEUE_INTERVAL_MS=10000 \
SERVICERADAR_TEST_SANDBOX_MODE=shared \
MIX_ENV=test mix test path/to/test_file.exs

For compile-only validation:

cd elixir/serviceradar_core
MIX_ENV=test mix compile --warnings-as-errors