agentleFS
carverautocarverauto/serviceradar/.agents/skills/srql-fixtures-db-tests/SKILL.md

srql-fixtures-db-tests

Skill934 starsChanged 2 days agoApache-2.0View on GitHub

Run focused ServiceRadar Elixir database tests against a scratch database on the Kubernetes CNPG instance in the `srql-fixtures` namespace. The guarded Bazel integration lifecycle runs only in the in-cluster BuildBuddy workflow using typed ci configuration.

  • Deletes or force-pushes

The file

Show its rules and text

We split this skill into the rules it gives an agent

19 rules. About 0 are common, 4 are distinctive, and 15 are very specific to this project.

  • ≈0

    Common · estimate

    Many files like this have a rule of this kind.

  • ≈4

    Distinctive · estimate

    This author's own choices. Worth a look.

  • 15

    Very specific

    Tied to their project's names, files or tools. Probably skip.

  • Common
  • Distinctive
  • Very specific
  1. Very specific:

    Use this skill for a focused Mix test against a separately created scratch database on the shared CNPG cluster in the Kubernetes …

    line 8
  2. Distinctive:

    Do not print database passwords or full URLs containing credentials.

    line 15
  3. Very specific:

    Use a scratch database named with a unique prefix, for example codex_<topic>_<timestamp>_<pid>.

    line 16
  4. Very specific:

    Verify the CNPG certificate.

    line 17
  5. Very specific:

    Prefer the existing NodePort/LoadBalancer service over kubectl port-forward for the scratch database.

    line 20
  6. Very specific:

    Keep the Ecto sandbox pool small for an individual scratch-DB test.

    line 22
  7. Very specific:

    Only the in-cluster BuildBuddy workflow may run guarded lanes.

    line 25
  8. Distinctive:

    Do not invent a NodePort/config override for guarded lanes and do not forward fixture secrets to remote actions.

    line 28
  9. Very specific:

    Use the srql-fixture-rw-ext service for write tests.

    line 43
  10. Very specific:

    Do not run the full guarded lifecycle from a workstation or substitute the NodePort coordinates above.

    line 76
  11. Very specific:

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

    line 81
  12. Very specific:

    Every run clones its lanes from an immutable schema generation, sr_tpl_<first 48 hex of the digest>, where the digest is …

    line 89
  13. Very specific:

    Create an isolated scratch database through the reachable NodePort endpoint.

    line 117
  14. Very specific:

    Run current branch migrations with mix serviceradar.db.migrate, not mix ecto.migrate (see AGENTS.md): on an empty database it applies the …

    line 129
  15. Distinctive:

    Use the same database URL and small pool.

    line 153
  16. Very specific:

    If no NodePort route works, fallback to kubectl port-forward -n srql-fixtures svc/srql-fixture-rw 15436:5432, set DB_HOST=127.0.0.1 …

    line 184
  17. Distinctive:

    After tests finish, drop the scratch database:

    line 188
  18. Very specific:

    Do not install, start, or connect to a workstation Postgres (Homebrew, /tmp:5432, localhost:5432) and do not start the Docker Compose stack …

    line 205
  19. Very specific:

    Use the srql-fixtures-db-tests skill when elixir/serviceradar_core integration tests need the shared CNPG/AGE fixture.

    line 220

≈ “Common” is an estimate: at least 1 in 5 of the 364,980 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.

Keep what fits and drop the rest. Your version, ready for your agents.
Files
SKILL.md

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

Common Failures

  • For a scratch database, pg_hba.conf rejects ... no encryption: require TLS and provide the fixture CA.
  • hostname check failed: connect through hostaddr=$DB_HOST while validating host=$TLS_SERVER_NAME, or set SRQL_TEST_DATABASE_SERVER_NAME for Elixir.
  • No route to host for the LoadBalancer IP: try the NodePort on a routeable node IP such as 192.168.10.31.
  • connection refused on a NodePort: rerun host discovery; the selected node may not be reachable from the workstation.
  • column ... does not exist: the database is stale; create a scratch database and run mix serviceradar.db.migrate with the same pool and queue settings as the migrate step above.
  • Postgrex expected %Postgrex.INET{} for string parameters: cast through text in SQL, for example ($1::text)::cidr or ($2::text)::inet, or pass the project native CIDR type.
  • If no NodePort route works, fallback to kubectl port-forward -n srql-fixtures svc/srql-fixture-rw 15436:5432, set DB_HOST=127.0.0.1 DB_PORT=15436, and reuse the same commands. Expect possible dropped forwards during long migrations.

Cleanup

After tests finish, drop the scratch database:

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 "DROP DATABASE IF EXISTS $DB"

rm -f "$CA_FILE"

Repository-wide fixture and validation rules

SRQL Fixture Integration Tests

Database-backed tests run only against a scratch database on the CNPG in the srql-fixtures namespace (kube context carverauto), never against a local Postgres. Do not install, start, or connect to a workstation Postgres (Homebrew, /tmp:5432, localhost:5432) and do not start the Docker Compose stack to get one, even if a server happens to be running: it lacks the TimescaleDB and AGE extensions and is not the fixture. This applies to every agent, including review and test agents in a validation pipeline.

Validation-pipeline test agents do not compile Elixir apps or build databases. In a no-mistakes (or similar) Test step, do not cold-compile serviceradar_core or web-ng, and do not create, migrate, or run tests against a scratch database: on this workstation that takes most of an hour and duplicates two checks that already exist, the coordinating session's scratch-database run before it submits, and BazelCI's in-cluster integration lanes in the CI step. Limit the Test step to checks that finish in minutes (reading the diff, targeted Go/Rust/Python/JS tests, python3 -m unittest build/contracts/ci_heavy_gate_contract_test.py), and report database-backed scenarios as untested with that reason. The step has a short timeout by design and fails fast.

Use the srql-fixtures-db-tests skill when elixir/serviceradar_core integration tests need the shared CNPG/AGE fixture. The guarded lifecycle runs only in the in-cluster BuildBuddy workflows (BazelCI Core Integration, BazelCI Web-NG DB, LargeIngestionGate, WorldWorkerScale, IntegrationBenchmark*). There is deliberately no orchestration script; the caller invokes each step in order: sweep_stale_dbs -> cleanup_generations -> prepare_generation -> migrate_generation (only on needs_migration) -> prepare_generation (must be ready) -> provision_generation -> tests -> teardown_db -> release_generation.

Schemas come from immutable per-digest generations. //build/schema_template:manifest hashes the migrations, baseline, helpers and construction inputs; prepare_generation reuses or starts building sr_tpl_<first 48 hex of that digest>, and //elixir/serviceradar_core:migrate_generation replays every migration into a new one. A ready generation is never written again, so a branch's unmerged migrations get their own generation and never reach the schema another branch clones. Capacity, retention and lease length live in build/schema_template/policy.json; cleanup_generations reclaims only generations idle past retention with no live lease and no connections. Contract and recovery: docs/docs/ci-schema-templates.md.

For the retired singleton and rollback prerequisites, see the SRQL fixture runbook.

Step order, run-id and credential rules, the BazelCI merge-tree caveat and cleanup checks: docs/agent-runbooks.md.

Download now (ZIP)

Does it work?

Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.

No reviews yet

People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.

  • It worked
  • Partly
  • It didn't
What did you use it for? e.g. Reviewing pull requests in a Rails app

Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.

Your trail

Files you open will collect here, so you can find them again.

Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.

Now do this for your own.

Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.