Troubleshooting

Every entry below is a symptom you can observe from outside: a status code, a message, a response that is empty when it should not be. Each one states what it means, how to confirm it, and what to change.

Start by reading the failure correctly. Three things about data.svc errors make a misdiagnosis easy.

Read the failure first

Three incompatible error envelopes ship on one process. A single client-side parser does not cover them.

Surface	Body shape
/rest, /anon/rest, /graphql, /bulk, actions, query hashes, scripted endpoints, schema	{"error": "<message>", "code": "v2.<code>", "details": {…}} — code and details omitted when empty
/worksheets/*	{"error": {"code": …, "message": …, "details": {…}, "request_id": …}} — error is an object here
/admin/data/*, /superadmin/*	{"error": "<message>"} — no code field at all
Any authentication rejection	The plain-text body Unauthorized. Not JSON, no envelope

Rate limiting uses two different codes for the same condition: v2.rate_limited on the main surface (with Retry-After and the X-RateLimit-Limit / X-RateLimit-Remaining / X-RateLimit-Reset headers), and rate_limit_exceeded on the worksheet surface (fixed Retry-After: 60, no details). Retry logic keyed on one misses the other.

Caution

An engine failure is classified by error type first, and only falls back to substring-matching the Go error message when the type is unrecognised. The typed pass is authoritative: a runtime access denial is 403, a hook validation failure is 422, a missing row is 404. Everything untyped — including every error the query compiler raises — takes the fallback, which lower-cases the message and matches in order:

First substring found	Status
access denied, not authorised, not authorized, forbidden	403
not found	404
validation, invalid, is required	422
anything else	500

The code field stays the caller’s generic per-operation code — v2.query_failed, v2.create_failed, v2.update_failed, v2.delete_failed, v2.restore_failed, v2.search_query_failed — so one code legitimately arrives with 403, 404, 422 or 500. Branch on the code plus the message, never on the status alone. The sharpest consequence has its own entry below: compile-time access denials arrive as 500.

The process will not start

Message	Cause	Fix
invalid/empty port provided	No top-level port: and no -p	Set one. There is no default
server start failed: failed to load TLSConfig	zerotrust is on without mutual-TLS material	zerotrust: false for a local run; supply the certificate material otherwise
config not ready: <reason> on a one-shot data … verb	The command waited 15s for the first config load and gave up	Point -f at the same boot config the server uses; check the config service is reachable
init environment: …	Boot flags or the boot document failed to bind	Read the reason; the CLI verbs boot the same way the server does, minus the listener

A one-shot CLI verb takes --tenant as the full four-part key customer:product:env:tenant. A bare name only resolves where the config source registers it at a matching path — a path: "" hive serves any key, which is why local examples get away with it.

Every request for a tenant fails

500 v2.engine_unavailable

The per-tenant engine bundle could not be built. This is the most common failure after a configuration change, and the message on the wire is generic — the reason is in the log.

Cause	Confirm
Tenant config not loaded yet	GET /checktenant with the tenant’s four headers
Datastore name does not resolve	no DStore config for "<tenant>":"<ds>" (pass ?datastore=<name>)
Datastore is not in the loaded schema	schema has no datastore "<n>" (default=…, available=…)
dbpool: names no declared pool	dbpool-not-found
Pool type has no factory	no v2 pool factory for DBPool type "<t>" — only postgres is registered
isolation: shared with no schema:	Fails the build deliberately; shared pooling without a per-tenant namespace is no isolation
Database unreachable or credentials wrong	On a dedicated pool this fails fast at pool build. On a shared pool the probe ping is skipped, so it surfaces at the first query instead
Definitions failed to compile	The compile error is logged with an entity and field

The full message table is in Datastores and database pools.

403 v2.no_tenant

No tenant key reached the request context. It reads like an authorization failure and almost never is one. The full routing model is in Tenancy and isolation.

Header	Carries
X-Customer	Customer segment
X-Product	Product segment
X-Env	Environment segment
X-Tenant	The tenant segment only — never the composed c:p:e:t key

Sending the full four-part key in X-Tenant produces exactly this 403. So does a tenant that is not configured, or one whose hive has active: false.

Requests die at exactly one second

The body is context-middleware-timed-out. The chassis per-request timeout defaults to 1s, and a cold request does more work than that: it resolves the tenant config, reads and parses the definitions, folds the layers, opens the pool and runs any init scripts before it executes anything.

timeouts:
  defaultcontexttimeout: 30s

Caution

globaltimeout: appears in shipped example configurations and in older internal notes. Nothing reads it. Setting it leaves the 1s default in place while looking like it was raised. The key the chassis actually reads is timeouts.defaultcontexttimeout.

The same applies to two other keys carried by shipped examples: enablecache: has no reader (the cache is configured under cache.active, cache.query.active and cache.data.active — see Caching), and the telemetry: block is inert on this service because telemetry initialisation is never invoked from its boot path.

/ready is green and nothing works

Health and readiness both flip true as soon as the process has wired its routes — before the listener even binds, and long before any tenant schema, pool or engine exists. A green probe says the process is up, not that any tenant is servable. Use GET /checktenant with the tenant’s headers for that.

401 with a body that will not parse as JSON

Authentication is rejected in middleware, before any handler runs, and the body is the literal text Unauthorized. Clients that unconditionally JSON-decode error bodies throw here. Also check that the token’s issuer is declared under jwks.issuers.<issuer> — an undeclared issuer is rejected outright rather than fetched, so a missing declaration shows up as blanket 401s.

An anonymous route returns an empty 200 and the handler never runs

An X-KisaiAuth-Level: trackedanonymous header without an accompanying X-KisaiAnonymousTrackerID drops the request in the anonymous middleware: neither branch calls the next handler. Send the tracker id, or drop the auth-level header and let the middleware classify the request itself. The only three values the header accepts are anonymous, trackedanonymous and authenticated; anything else is ignored and the request is treated as anonymous.

A definition change does not take effect

Nothing watches a schema source, and nothing polls one. A change needs two separate actions, and skipping either produces a different symptom.

Skipped	Symptom
The reload	The old shape keeps serving; new fields are absent, removed fields still validate
The migration	Queries against the new shape fail with relation … does not exist or column … does not exist

Reload by draining the tenant’s scope — DELETE /cache as the tenant, the superadmin variant DELETE /superadmin/tenant/<name>/cache, or a tenant configuration change. A drain is all-or-nothing: engines, cached schemas, refresh runners, script runtimes and pools go for every datastore that tenant holds. Then apply the DDL with data bootstrap, or data plan generate + data plan apply <plan-id>; see Migrations.

The superadmin :tenant path segment is a bare tenant name, not a four-part key — customer, product and environment come from the caller’s own headers. A full key in the path is rejected.

An entity 404s with v2.entity_not_found

The name is not in the tenant’s composed schema. Confirm what actually composed by reading the schema route (see Schema export and discovery) rather than by inspecting the YAML you think is live.

Cause	Tell
Wrong datastore addressed	The entity exists under another ?datastore=
The schemasource: block was rejected	It fell back to the metadata service with only a warning. Grep the boot log for schemasource override active
A local directory was addressed with a typo’d datastore name	The datastore name selects a subdirectory; when it does not exist the walk falls back to the whole root rather than failing
Duplicate entity name across files	entities: and traits: are keep-first — the later declaration is ignored and logged at error level
The entity lives only in a tenant overlay row	Rows whose content_type is not a YAML type are skipped, as are soft-deleted rows
The entity is not materialization: table	Views, remote and virtual entities exist in the schema but are never migrated

A field is silently missing after a load

Unknown keys are not rejected — there is no strict-field decoding anywhere in the loader — so a wrong spelling parses cleanly and contributes nothing.

Cause	What to write instead
The other YAML front end’s spelling: default:, enumref:, references: "entity.field", terse type strings	defaultvalue:, enum:, references: {entity, field}, explicit type: — see Fields
Keys that parse and are then discarded: hidden:, serial:, subfields:, annotations:, stateflowfield:	Nothing — they have no reader
An augments: block	Augments parse and are never applied on the deployable path. Re-declare the entity in the higher layer and list the new fields
Audit columns written into a tenant overlay	createdby, createdon, updatedby, updatedon, deletedby, deletedon are stripped from every overlay entity before the fold, with no diagnostic
A top-level endpoints: block in a tenant overlay	Removed from the raw YAML before parsing. Scripted endpoints are product-only
A computed field with no default	A purely virtual computed field is excluded from the column diff — it has no column by design

Caution

When a tenant has any overlay row, the fold drops schema-level actions:, endpoints:, migrations: and archives: from both layers — the base’s as well as the overlay’s. A tenant with no overlay rows short-circuits before the fold and is unaffected. Until that is fixed, do not combine tenant overlays with a product that declares schema-level actions or scripted endpoints.

Access blocks have their own drop rules: every tier is replaced wholesale by a product layer, and a tenant layer’s access: is dropped outright. See Access rules.

The fold records a diagnostic for every ignored contribution — ignored-duplicate, ignored-tenant-access, ignored-locked-tier, ignored-final-entity, ignored-scope-change, ignored-shadow — logged at error level with layer, entity, field and tier. They are the only signal. Grep for them after changing any layered definition; Where definitions live has the full list.

The load aborts

Most conflicts are dropped and logged. Four things actually refuse the build.

Failure	Message shape
Malformed YAML in a tenant overlay row	Refuses the engine build — serving a tenant without the extensions it declared would silently change behaviour
A definition file that fails to decode	loader.parse <file>: …
An action name colliding across actions:, queries: and queryhashes:	Action names are globally unique per schema
Schema validation at compile	CODE: message (entity: <e>.<f>) — <suggestion>

The compile codes are ENTITY_NOT_FOUND, FIELD_NOT_FOUND, TYPE_MISMATCH, INVALID_ENUM_VALUE, RELATION_NOT_FOUND, FIELD_NOT_QUERYABLE, UNSUPPORTED_OPERATION, DUPLICATE_ENTITY, DUPLICATE_FIELD, SHADOW_PRODUCT_FIELD and INVALID_TRANSITION. They are boot-time only — a schema that fails them never serves traffic.

Caution

Definitions sourced from the metadata service are handed to the loader under synthetic names — file-000.yaml, file-001.yaml, and so on. A parse error therefore names a file that does not exist in the product repository, and the number is a position in the response, not a filename. Local-directory and embedded sources keep the real relative path.

A datastore cannot be resolved

Message	Meaning
no DStore config for "<tenant>":"<ds>" (pass ?datastore=<name>)	No datastore was selected and none is resolvable for the tenant
schema has no datastore "<n>" (default=…, available=…)	The tenant config and the loaded schema disagree on the name
datastore-not-found	The requested name is not in the tenant’s datastores map
dbpool-not-found	A datastore’s dbpool: names no key under dbpools:
unknown db pool type "<t>" for pool "<n>"	Only postgres, postgresql and clickhouse pass validation
no v2 pool factory for DBPool type "<t>"	Passed validation, but only postgres has a factory registered

Three silent behaviours produce a datastore that resolves to the wrong thing rather than failing:

• A pool with no type: resolves to PostgreSQL. A connection string for another engine is then handed to the PostgreSQL factory.
• A pool with only dsn: skips validation and cannot share an endpoint. Endpoint-key derivation needs discrete dbserver / database fields, so isolation: shared silently downgrades to dedicated.
• An omitted schema: defaults to the tenant’s own name. Convenient per tenant; dangerous when dbpools: is declared once at a shared configuration prefix and inherited, because every tenant then inherits the same explicit schema: and reads and writes one set of tables with no error anywhere. Restate schema: at each tenant leaf.

Malformed durations are dropped without a warning: an unparseable connection_life_time leaves connections with unlimited lifetime, and an unparseable connect_timeout falls back to 5s.

A migration refuses or does not land

Symptom	Meaning
403 v2.requires_superadmin on plan apply, retention or lock release	This host gates the irreversible classes above tenant admin, because it runs shared endpoint pools where one tenant’s long-locking ALTER starves the others. Admins still read plans, generate diffs and run seeds
POST /admin/data/plans returns 200 and a plan_id, but GET /admin/data/plans/<id> 404s	The plan carried refusals, and refusals suppress persistence. Branch on the refusals array being non-empty, not on the status
The request body was accepted but the flags did nothing	The wire contract is snake_case: allow_drops, confirm_max_risk, dry_run. camelCase spellings decode to the safe defaults
lock tenant:<key>:datastore:<ds> held by <holder> until <time>	One coarse lock per (tenant, datastore) serialises migration, init scripts, seeds and retention. Wait, or inspect GET /admin/data/locks
The plan re-emits the same CREATE TABLE on every generate	The tenant namespace was applied to some but not all of dialect, discovery inputs and ledger. See the namespace must be set in three places
A plan stopped part-way	There is no rollback. Steps run without an outer transaction by design; recovery is fix and re-run, since generated DDL is IF NOT EXISTS
type "<name>" does not exist	Declared enum types are never created by any migration path. Create the type in an init script
A foreign key you declared is absent	The diff has no foreign-key operation. Databases provisioned by bootstrap or plan apply carry no declared FK constraints
An unknown live table or index	Reported as a warning and never dropped. Only drop_column exists, and only behind allow_drops

Force-releasing a lock deletes the row regardless of holder or expiry — the previous holder keeps running until its next heartbeat fails. Use it only when a holder has genuinely crashed.

data bootstrap creates the tenant’s schema namespace but never the physical database; that step is external.

A caller is denied

403 with access denied (tier N)

Deny-by-default. An external request against an operation with no rule governing it is denied — there is no implicit allow. Opt each operation in under access.actions. Full model in Access rules.

Every external operation on an entity 403s despite an access: block

The block is almost certainly written in the other front end’s grammar. The loader accepts only access: {services, actions, rls, fields}. The direct-action roles shorthand — access: {create: {roles: [admin]}} — parses without error, contributes nothing, and leaves the entity with no actions at all, which under deny-by-default is a 403 on everything. There is no roles-list rule form: express roles as an expression, '"admin" in user.roles'.

A denial arrives as 500, not 403

Runtime hook denials are a typed access-denied error and map to 403 whatever their text says. Denials raised by the query compiler are plain errors — their messages begin accessgate: or safetyguard: and carry none of the substrings the fallback classifier recognises, so they land on the default, 500. Same logical failure, two statuses depending on which layer caught it. Service-allowlist denials, compile-time action denials, field-read denials and aggregation refusals are all in the 500 group. The tier-by-tier message table is in Access rules.

A tenant’s access rules have no effect

A tenant layer can never change access on an entity that already exists in the base — the block is dropped and recorded as ignored-tenant-access. Note the asymmetry: an entity the tenant layer introduces carries its own access through untouched.

A read returns 200 with zero rows

Row-level security fails closed on this service: a rule the translator cannot compile becomes a predicate that matches nothing. The caller gets a successful, empty response and no error is logged anywhere, so a broken rule is indistinguishable from an empty table.

Cause	Fix
Legacy template syntax, {{user.id}} == row.owner_id	Rewrite without the braces: user.id == row.owner_id
A binding or operator outside the translator’s grammar	See the grammar
user.tenant_id compared against a slug	It is the composed four-part key, e.g. acme:shop:dev:main — not a tenant name
An anonymous caller against an ownership rule	user.id is the empty string on /anon/rest, so the predicate matches nothing. Intended

There is no boot-time validation pass for RLS rules; the first signal is the empty result. Confirm by counting the same rows directly in the database, then re-running the read as a principal the rule allows.

A rule allows what it should deny

Cause	Detail
The rule returns a non-empty string	Any non-empty string coerces to true. Only nil, false, 0 and "" deny; a slice or map errors with a 500
access.fields.write	Parsed, stored, and never enforced. Only fields.read has an enforcement path
fields.read written as an "all"-or-list expression	It is evaluated once per projected field with field bound, and the result is read as a boolean. Write it as a boolean over field
script-file: with function:	File-referenced scripts do not execute. Only inline expression: and script: bodies run; on an action guard the failure surfaces as a 500
rls.related-data:	Parses, no runtime effect on this service
rowlevelsecurity: declared alongside access.rls on one entity	The entity-level key overwrites access.rls wholesale, with no diagnostic

Tier-1 guards evaluate twice per request — once in the runtime hook and once at compile — from bindings built by different paths. A side-effecting rule body runs twice, and a discrepancy between the two evaluations changes the decision.

Sensitive data comes back in the clear

The deployable service registers the defaults, validate and audit hooks, the request-options bridge, the optional file-field hooks and the script-runtime hooks. It registers no transform hook, no compliance hook, no enum-enrichment hook and no encryption hooks.

Declared	What actually happens
compliances: mask / redact / hidden directives	No read-time masking. The classification is carried in the schema and exported, and nothing rewrites a value
transforms:	Values are stored exactly as sent
Field-level encrypt:	Values are stored in plaintext
Enum label/colour enrichment	Reads return the stored value only

Field protection states which halves are live. Two spellings additionally do nothing even where the hook exists: the directive is mask (not masked), and the production loader has no pattern: key — the pattern goes in value:.

Reading a rejected write

Hook-level validation arrives as 422 — the failure is a typed validation error, so it never takes the substring fallback — but the top-level code is the generic v2.create_failed / v2.update_failed. The per-field code is carried inside the message, not in the envelope: REQUIRED, READONLY, FINAL, WRITE_ONCE, MIN, MAX, MIN_LENGTH, MAX_LENGTH, PATTERN, VALIDATION_UNKNOWN, plus whatever code: an entity-level rule declares (ENTITY_VALIDATE / ENTITY_VALIDATE_ERR when it declares none). Only the first failing field reaches the wire — the message is that one error, prefixed with the hook and phase that raised it (hook validate (BeforeValidate): …), and details is empty. Fix one, resubmit, see the next.

A write is rejected or lands wrong

Symptom	Cause
Every write that carries the field fails with VALIDATION_UNKNOWN	validations: [{type: exists}] has no implementation and falls to the reject branch. Validations only run when the field carries a value, so an omitted field slips past. when: conditionals parse and are then dropped — no validator reads them, so the rule runs unconditionally
value too long for type character varying(255)	maxlength: produces a runtime check only; it does not size the column. Declare a custom type with a length: and reference it
A NOT NULL violation on a field you consider optional	Columns are NOT NULL unless nullable: true. required: is an application-layer presence check on create with no DDL effect
Duplicate values accepted	validations: [{type: unique}] is a no-op. Uniqueness comes from unique: true on the column — and that is skipped on versioned entities
A readonly field was set by the caller	readonly blocks updates, not creates. So does writeonce. No modifier expresses “system writes, caller never does”
A field you sent was overwritten	The audit hook rewrites createdon/createdby on create and updatedon/updatedby on create and update, whenever the entity declares them. version is the exception: it is set to 1 on create only when you send none, and incremented by the compiler on update
A bad enum value was stored	Membership is enforced only where the database has a native enum type. Add an explicit validations: [{type: enum, values: […]}]
A declared transform did nothing	No transform hook is registered in this binary, so no transform runs — known or unknown. Values are stored exactly as sent
422 v2.bulk_failed	The whole envelope rolled back. details.operations carries the per-operation results; the failing entry has its own error string

A deleted row is still in the table

Delete is soft by default. DELETE returns 200, the row remains with deletedon stamped, list queries exclude it, and a subsequent get-by-id returns 404 v2.row_not_found. Restore with POST /rest/:entity/id/:id/restore; on an entity with no soft-delete columns that fails with restore: entity "<name>" has no soft-delete columns.

Something returns 501

501 is a deployment or feature state, not a malformed request.

Code	Meaning
v2.search_text_not_supported	Free-text search. Send a structured filter object instead of query
v2.search_dsl_not_supported	filter sent as a string. Only the object form is interpreted
v2.file_storage_unconfigured	Multipart create on a deployment with no file-storage backend wired
v2.file_downloader_unconfigured	The same on the download path

A scripted endpoint 404s

Dispatch maps both “no such endpoint” and “endpoint disabled” to 404 v2.endpoint_not_found, deliberately, so that a disabled endpoint’s existence is not disclosed. A 404 on an endpoint you know is deployed means it is disabled, not missing. See Scripted endpoints.

Things that fail quietly and never surface on a request

Subsystem	Failure behaviour
Datastore init scripts	Failure is logged at error level and the engine still boots and serves. Nothing in /health, /ready or /checktenant reflects it. Check data init list; data init run forces a re-pass
Materialized-refresh install	Logged and non-fatal. Source mutations still succeed, refresh tasks are never enqueued, and cross-entity computed fields go permanently stale. Check GET /admin/data/materialize/pending and GET /admin/data/materialize/stats
Rate limiting	Fails open. A route naming an undeclared profile is allowed with a log line. A present ratelimit: block replaces the built-in profiles in full — declare every profile you want live
Response cache	Fails open — every request becomes a miss
Alias-mounted authorization	Fails open. When a route declares a rule but no authorizer is registered, the check is skipped entirely
Shutdown	Tearing down the refresh runner waits for its final drain with no timeout. A hung shutdown is this, first suspect

Running the functional harness locally

The block ships a harness that boots a real service against a real PostgreSQL instance, seeds a sample product, and drives it with the HTTP-plus-SQL test runner. It is the check to run against a change, because it asserts both sides of every operation: the API response and the row in the database.

Prerequisites — a reachable PostgreSQL instance, the kis CLI, and a Go toolchain to build the binary.

kis flow -f tests/_harness/run.flow.yaml

The test task’s exit status is the flow’s result. The server is left running afterwards; the next boot resets the schema.

What the boot flow does

Six stages, in order: stop-existing, pg-ready, reset-schema, build, migrate, serve.

tasks:
  - name: pg-ready
    shell: |
      nc -z localhost 5432 2>/dev/null || container start pg18 >/dev/null 2>&1 || true
      for i in $(seq 1 30); do nc -z localhost 5432 2>/dev/null && exit 0; sleep 1; done
      echo "database not reachable on :5432"; exit 1
    next: { go: reset-schema }

  - name: reset-schema
    shell: |
      "{{psql}}" "{{dsn}}" -v ON_ERROR_STOP=1 \
        -c "DROP SCHEMA IF EXISTS erptest CASCADE; CREATE SCHEMA erptest;"
    next: { go: build }

  - name: migrate
    shell: |
      cd {{harness}} && ./.bin/data.svc data bootstrap --tenant {{tenant}} -f bootstrap.yaml
    next: { go: serve }

Kill anything left listening on the port and clear the recorded PID, probe the database, drop and recreate the dedicated schema, build the binary, run data bootstrap to materialise the tables, then serve and poll /anon/rest/<entity> until it answers. Every run therefore starts from an empty schema. --tenant here is the full four-part key.

Driving it in pieces

kis flow -f tests/_harness/up.flow.yaml

kis test v2 -t tests/erp -e tests/erp/env.yaml

kis flow -f tests/_harness/down.flow.yaml

down kills the recorded PID. kis test v2 discovers an env file next to --tests when --env is omitted — env.local.* first, then env.<name>.* when --name is set, then env.*, and within each group .yaml, .yml, .json in that order. --vars/-v overrides win over the file, and --junit, --tags, --exclude-tags and --no-color cover CI use.

The boot config the harness uses

There is no configuration service in the loop — the file’s own hives: block is the config source, and path: "" serves one tenant for whatever routing key the process sees.

host: 127.0.0.1
port: 8099
zerotrust: false
source:
  type: fsstore
log:
  level: info
timeouts:
  defaultcontexttimeout: 30s
schemasource:
  type: localdir
  path: ./erp-product
jwks:
  disabled: true
hives:
  cluster:
    - path: ""
      api:
        key: dev-apikey
  tenant:
    - path: ""
      name: dev
      active: true
      domain: [localhost, 127.0.0.1]
      datastores:
        main:
          dbpool: main
      dbpools:
        main:
          type: postgres
          dbserver: localhost:5432
          dbuser: erpadmin
          database: erpdb
          password: erpadmin
          schema: erptest
          maxconnections: 10

schemasource.path is relative to the process working directory, and a malformed schemasource: block reverts to the metadata service with a warning rather than failing — see Where definitions live.

Caution

jwks.disabled: true accepts every bearer token without checking its signature, so the suite can mint principals with arbitrary user ids and roles instead of standing up an identity provider. The claims still matter — tenant must equal the request’s CPET, and userid / roles become user.id / user.roles — so access rules and RLS are exercised for real. Never set it outside a test harness.

A test case that asserts on both sides

- name: create-currency
  http:
    url: "{{anon}}/currency"
    method: POST
    headers:
      Content-Type: application/json
      X-Customer: "{{h_customer}}"
      X-Product: "{{h_product}}"
      X-Env: "{{h_env}}"
      X-Tenant: "{{h_tenant}}"
    body:
      type: raw
      payload: '{"code":"EUR","name":"Euro","symbol":"€","fraction_units":100}'
    response:
      variables:
        cur_id: "jq: .response.data[0].id"
  assertions:
    - status_code == 201
    - response.count == 1
    - response.data[0].code == "EUR"

- name: verify-create-in-db
  sql:
    driver: postgres
    connection: "{{db_url}}"
    query: "SELECT code, name, symbol, fraction_units FROM erptest.currency WHERE id = $1"
    params: ["{{cur_id}}"]
  assertions:
    - row_count == 1
    - rows[0].name == "Euro"

HTTP steps expose status_code and response.*; SQL steps expose row_count and rows[N].<column>. Variables captured in one step are available to every later step, which is what makes the dual assertion possible — and what catches the class of bug where the API returns 2xx and the row did not land.

The same pattern proves soft delete is soft:

- name: verify-soft-delete-in-db
  sql:
    driver: postgres
    connection: "{{db_url}}"
    query: "SELECT count(*) AS active FROM erptest.currency WHERE id = $1 AND deletedon IS NULL"
    params: ["{{cur_id}}"]
  assertions:
    - rows[0].active == 0

Caution

Extracting an array element into a variable requires the jq: prefix. A plain dotted path does not index arrays; it yields an empty variable, and the failure surfaces several steps later as a mismatched assertion rather than as an extraction error.

h_tenant in the env file is the tenant segment, not the composed four-part key — the same trap as v2.no_tenant on a live deployment.