# Insights jobs and status reads: verification evidence

Date: 2026-09-05. Owner: speed, jobs, and instrumentation agent.
Baseline source: `6ce7648afa0b69d17b765c1bb20a6fe7c3fa061f`.

## Finding status

| Finding | Current evidence | Change | Verification | Limit |
|---|---|---|---|---|
| F06 | Confirmed. Original repository permits 20 identical keys to create 20 jobs. At 181 seconds, its response says failed, its row says running, and late finish changes it to done. | Atomic scoped reservation. Payload fingerprint. Initial thread and job share one transaction. Empty creation also has atomic keys. Explicit parent. Bounded admission. Durable timeout, cancellation, and process ownership. | Concurrent route tests, initial lost-ack replay, changed payload, busy/admission, captured parent, late append/finish, watchdog, restart, and migration tests pass. | Workers remain in-process. See deadline and restart limits below. |
| F07 | Confirmed dependency. Optional narration was inside the conversion required before storing a turn. | Store checked rows first. Publish ready. Bound concurrent optional providers to 25 seconds and remaining job time. Patch only presentation fields on that same turn. | A real injected 20-second narrator delay exposes checked rows in 119.394 ms. Full completion takes 20,193.104 ms. Optional failure preserves the rows. | This measures the worker/converter/persistence seam. Required query execution is synthetic. It does not measure production answer latency. |
| F08 | Confirmed. Status authorization hydrated every historical result. | Ownership metadata reads. Opt-in conversation summaries. Single-turn reads and history deltas, integrated by the lead. | N=30 metadata and real loopback HTTP observations. SQL trace proves status reads never select result JSON. Status body remains 385 bytes on both fixtures. | Full detail remains available. Worker follow-up context still hydrates historical turns through its captured parent. |
| F12 | Connection/pooling benefits remain hypotheses. | This agent adds bounded turn admission, worker/provider deadlines, and job phase timing in the existing query trace snapshot. Query agent owns shared source admission, database timings, SQL caps, and driver cancellation. | Queue/required/optional/first-useful/full-completion timings are persisted. Generic provider tests pass. Query-agent evidence covers database work. | No connection pool was added. No production end-to-end speed claim is made. |

## Implementation references

- `migrations/bic_035_durable_turn_jobs.sql`: append-only schema migration. Keeps every prior job, including duplicate legacy keys. Adds the unique scope for new fingerprinted submissions.
- `migrations/rollback/bic_035_durable_turn_jobs.sql`: old-code compatibility rollback. Keeps answers and metadata. Maps active/new terminal states into the old status domain.
- `repositories/bi_turn_job.py`: atomic `reserve`, bounded global admission of 32 active jobs, one pending question per conversation, immutable terminal transitions, process-aware orphan selection.
- `repositories/bi_conversation.py`: atomic empty creation; metadata getters; summary projection; turn deltas; guarded turn insertion. A stopped job cannot insert an answer.
- `services/bi_job_control.py`: propagated deadline and cancellation context. Owner identity includes hostname, PID, and Linux process birth time.
- `api/routers/bi_query.py`: create/append/status/resume/cancel routes; captured-parent worker; ready publication; optional presentation patch; existing trace snapshot gains a `job` section.
- `api/app.py`: orphan sweep skips live sibling processes. An interrupted presentation stage keeps the already published answer.
- `services/llm_executor.py` and `services/bi_sql_llm_generator.py`: actual provider timeouts use the remaining deadline. Worker scopes disable SDK retries.

## Contract

Submission keys are scoped by user, session vault, and operation. Append keys also include the conversation. The fingerprint includes all submitted fields except the key. A changed payload returns HTTP 409. A replay returns the original job and captured parent. Creating an initial question returns HTTP 202 for a new job and HTTP 200 for replay. An empty creation returns HTTP 201 or 200.

States are `queued`, `running`, `ready`, `done`, `failed`, `timed_out`, and `cancelled`. The first three are active. The deadline is 180 seconds from submission, including queue time. The worker watchdog persists timeout without a status poll. A later read also persists an overdue outcome. Terminal transitions are conditional. A late worker cannot reverse cancellation or timeout.

The pending record carries the exact question, request key, parent, deadline, status, and result turn id. A ready result is readable before optional work ends. `follow_up_ready` stays false if required aggregate/listing cohort preparation failed or its persisted cohort is incomplete. A new scope can proceed with explicit `parent_turn_id: null`.

Status authorization proves owner and session vault before reading the job. The cancel route uses the same boundary. Append validates rerun ownership before admission. Denied requests never dispatch workers.

## Matched measurements

**Measured.** Python 3.12.13; SQLite 3.53.1. File-backed temporary SQLite. Ten integer columns. Three warmups. N=30 sequential samples. N=32 at concurrency four. p95 uses nearest rank. All status requests returned HTTP 200 with a completed job. Fast errors are excluded.

HTTP measurements use a real local Uvicorn server and TCP loopback. The permission provider is injected with a fixed synthetic user and vault. The before route reproduces the old full-history authorization algorithm. Both variants return the same current status response. These are matched status workloads, not full question-to-answer requests.

| Fixture | Full getter p50 / p95 | Metadata getter p50 / p95 | HTTP before, c1 p50 / p95 | HTTP after, c1 p50 / p95 |
|---|---:|---:|---:|---:|
| 1 turn × 100 rows | 0.356 / 0.400 ms | 0.034 / 0.076 ms | 2.750 / 4.017 ms | 2.343 / 2.962 ms |
| 20 turns × 10,000 rows | 445.723 / 1,221.311 ms | 0.032 / 0.075 ms | 393.617 / 1,117.625 ms | 1.715 / 2.299 ms |

| Fixture | HTTP before, c4 p50 / p95 | HTTP after, c4 p50 / p95 | Status body | Summary body | Full conversation body |
|---|---:|---:|---:|---:|---:|
| 1 × 100 | 13.596 / 18.495 ms | 11.239 / 18.831 ms | 385 bytes | 469 bytes | 7,441 bytes |
| 20 × 10,000 | 2,324.828 / 3,177.752 ms | 9.818 / 14.269 ms | 385 bytes | 470 bytes | 10,258,265 bytes |

The large-fixture metadata p95 is below the 25 ms acceptance threshold. Query count and selected metadata fields are constant. Historical result rows do not enter status work or its payload. The small-fixture c4 p95 did not improve; these observations must not be generalized into a blanket speed claim.

New HTTP clients were also measured, N=30. After-change p50/p95 was 11.747/26.626 ms on the small fixture and 9.007/27.468 ms on the large fixture. This includes client setup and a new TCP connection. It is not a cold filesystem-cache measurement. The first metadata read was measured once: 0.125 and 0.166 ms. OS-cold cache and production-network latency remain unmeasured.

Raw observations and SQL statements: [job-status-benchmark.json](job-status-benchmark.json).
Baseline reproduction: [job-baseline-reproduction.json](job-baseline-reproduction.json).
Optional-stage timings: [optional-readiness.xml](optional-readiness.xml).

## Commands and results

```text
.venv/bin/python -m pytest tests/test_bic015_async_turn_jobs.py tests/test_bic023_turn_dedup.py -q
Baseline: 24 passed in 29.06 seconds.

.venv/bin/python -m pytest tests/test_bic015_async_turn_jobs.py tests/test_bic023_turn_dedup.py tests/test_llm_executor.py -q
Expanded regression gate: 71 passed in 50.43 seconds.

.venv/bin/python -m pytest tests/test_bic015_async_turn_jobs.py -k checked_rows_visible -q -o junit_family=legacy --junitxml=docs/evidence/insights-fixes-2026-09-05/optional-readiness.xml
2 passed, 20 deselected in 42.16 seconds.

.venv/bin/python scripts/reproduce_insights_job_baseline.py
20 same-key insertions -> 20 distinct jobs.
At 181 seconds: response failed; stored running; late completion done.

.venv/bin/python scripts/benchmark_insights_job_status.py --output docs/evidence/insights-fixes-2026-09-05/job-status-benchmark.json
Completed both fixtures and all sequential/concurrent samples.

.venv/bin/python -m mypy --follow-imports=silent --strict src/institutional_kb/services/bi_job_control.py src/institutional_kb/repositories/bi_turn_job.py src/institutional_kb/models/bi_turn_job.py
Success: no issues found in 3 source files.
```

Ruff passes for this agent's repositories, job model/control, provider modules, tests, and benchmark scripts. The lead owns the broader integrated checks. Existing Pydantic UTC deprecation warnings remain in scoped tests.

The core tests assert 20 concurrent initial/append route retries create one execution and one terminal turn. The worker is deliberately invoked twice after admission; the second invocation cannot create another answer. Other cases assert changed-payload conflict, empty-create replay, completed-request replay, explicit parent capture, busy/admission limits, 181-second durable timeout, cancellation before a late write, watchdog without polling, restart preservation, live sibling ownership, and schema rollback.

## Rollout and remaining checks

Stop or drain old application workers before applying the migration. Back up the metadata database. Apply normal migrations, then start the updated application. Legacy rows have no process identity; startup treats those rows as pre-migration orphans. New rows from a live sibling process are preserved. A remote-host owner is kept until its deadline because local PID checks cannot prove its death.

The migration test starts with duplicate legacy request keys. It proves all rows survive. An injected DDL failure rolls back the whole transaction. The compatibility rollback preserves completed answer ids and maps statuses into the prior application's domain. Stop all workers before that rollback. Retained metadata supports a forward rollout; reverting the application restores its prior behavioral limitations.

The 180-second job outcome is durable. Provider socket timeouts and database cancellation bound the work they control. Python threads cannot safely preempt arbitrary CPU code or a stuck native extension. A terminal job still rejects later persistence in that case. Real external-provider cancellation, production infrastructure restarts, and production end-to-end answer latency were not tested. No provider credentials or production data were used.

Optional work still uses the turn worker while it waits for its bounded presentation batch. It does not hold back readable rows. Worker-context history hydration remains a later optimization. Pooling, cached answers, and full answer speedups remain hypotheses.

Additional gates:

```text
.venv/bin/python -m pytest tests/test_bic015_async_turn_jobs.py -k local_provider_socket -q
1 passed, 22 deselected in 22.31 seconds.

.venv/bin/python -m pytest tests/test_bic015_async_turn_jobs.py -k 'cancelled_job or worker_watchdog or restart_preserves' -q
3 passed, 20 deselected in 25.34 seconds.
```

The socket test uses a real local HTTP server. A response stalls for 500 ms. The local provider has a 100 ms remaining deadline and returns an error before 400 ms. It uses a synthetic API key. This verifies transport behavior, not only constructor arguments.

The watchdog also checks its small durable job row every 250 ms. This propagates a cancellation handled by another API process into the running worker's cancellation Event. The cancellation test changes only the database row and proves the worker receives that Event. Cohort readiness also persists in `confidence_factors.answer_record.follow_up_ready`; the top-level answer record exposes it after reload.

```text
.venv/bin/python -m pytest tests/test_bic015_async_turn_jobs.py -k incomplete_cohort_readiness -q
1 passed, 23 deselected in 21.34 seconds.

.venv/bin/python -m pytest tests/test_bic015_async_turn_jobs.py tests/test_bic023_turn_dedup.py -k 'completed_trace or source_slot_releases or federated_admission or migrated_request_key' -q
4 passed, 43 deselected in 21.66 seconds.
```

The integrated review found and fixed three further defects. Completed trace spans could raise before source-slot cleanup. A migrated request key could bypass the new fingerprint lookup. Federation admission wait could be lost before trace creation. The four focused regressions cover these failures. See [integrated-review-jobs.md](integrated-review-jobs.md).

Legacy request keys now return 409 within their original user/vault/conversation scope. The original payload is incomplete, so replay cannot safely guess equivalence. The existing answer remains available. A deliberate new key can start fresh work. This migration behavior is now tested.

## Final integration: auxiliary source authorization and column policy

The integrated review found that auxiliary identity, cohort, and freshness SQL used the supplied user/vault as audit arguments without authorizing the actual target connection. A GOLD-only principal could dispatch a CT lookup. The same path could refill an email field using raw values even when the target's configured policy required masking or restriction.

`BiQueryExecutionService._execute_sql` now rejects missing, zero, or malformed principals, resolves the nondeleted target connection's actual workspace, and requires that workspace's Data-vault query permission before decrypting credentials or dispatching a driver. The driver receives the resolved Data vault. Auxiliary builders also pass current table/column validation, masking, and final validation. The three already-validated pipeline call sites explicitly avoid applying masking twice; authorization always runs. Existing display caps and `as_of_date` arguments remain intact.

The new tests use real isolated SQLite source metadata and vault membership. The policy tests execute the resulting simple SELECT/CASE projection over synthetic contact rows, proving masked output, zero restricted dispatch, and preservation of allowed contact access without a column restriction. The original SSH and BI008 transport fixtures use explicit synthetic principals and scoped permission stubs; their transport and safety assertions remain in place. The BI008 cost fixture now avoids any incidental localhost PostgreSQL connection.

```text
.venv/bin/python -m pytest tests/test_insights_auxiliary_authorization.py -q
Before source authorization: 13 failed in 12.56 seconds.

.venv/bin/python -m pytest tests/test_insights_auxiliary_authorization.py -k 'contact_lookup or explicit_allowed' -q
Before auxiliary policy enforcement: 3 failed, 1 passed, 13 deselected in 12.86 seconds.

.venv/bin/python -m pytest tests/test_insights_auxiliary_authorization.py tests/test_bi_query_execution_service_ssh.py tests/test_bi008_query_execution_safety.py -q
After both fixes and transport fixture updates: 67 passed in 13.53 seconds.

.venv/bin/ruff check src/institutional_kb/services/bi_query_execution_service.py tests/test_insights_auxiliary_authorization.py tests/test_bi_query_execution_service_ssh.py tests/test_bi008_query_execution_safety.py
All checks passed.
```

These fixtures exercise real authorization and configured policies, with the external driver isolated. They do not claim production source access or a complete live answer-latency measurement. An auxiliary table outside its connection's approved inventory now fails closed; optional freshness presentation can report unavailable evidence when that happens.

## Full-suite fixture compatibility follow-up

The lead's integrated suite exposed nine failures in five files assigned back to this agent. All nine were stale test contracts: the intake dispatcher double returned the previous UUID shape instead of `(BiTurnJob, created)`; four SSH response tests and three resolver/SQL-generator runtime tests mocked only the prior router permission helper; the in-flight SSH revocation test supplied `MagicMock` where final SQL validation now requires `SQLValidationContext`.

Updated the intake double, added explicit shared-entrypoint authorization stubs that assert the fixture's principal/workspace/vault/source in isolated runtime-wiring tests, and supplied a real validation context in the revocation pipeline fixture. The revocation test retains the real migrated SQLite Data-vault membership, source authorization, in-process SSH server, and owner-revocation assertions. No production code changed for these failures.

```text
.venv/bin/python -m pytest tests/test_bi_intake_endpoints.py tests/test_bi_query_ssh_secret_handling.py tests/test_bi_ssh_revocation_e2e.py tests/test_rt010_bi_query_definition_resolver_runtime.py tests/test_rt011_bi_sql_llm_runtime.py -q
61 passed, 8 existing Pydantic UTC warnings in 53.00 seconds.

.venv/bin/ruff check tests/test_bi_intake_endpoints.py tests/test_bi_query_ssh_secret_handling.py tests/test_bi_ssh_revocation_e2e.py tests/test_rt010_bi_query_definition_resolver_runtime.py tests/test_rt011_bi_sql_llm_runtime.py
All checks passed.
```

## Final acceptance: separate submission and persistence spans

Added two measurements to the existing durable job timings and the same turn's `trace_snapshot.job.timing`, through `JobControl` and the job repository:

| Measurement | Boundary |
| --- | --- |
| `submission_ack_ms` | Create/append handler entry through authorization, atomic reservation, and executor submission. Excludes request decoding, response serialization, timing-storage overhead, and network acknowledgement. This is server processing time; the browser's request duration remains the end-to-end acknowledgement measurement. |
| `base_persistence_ms` | Base turn append entry through durable commit, including JSON serialization, transaction admission, audit writing, and database writes. Excludes the subsequent turn read-back and optional presentation writes. Applies to success, error, clarification, and fallback error turns. |

The existing `queue_ms`, `optional_ms`, `required_to_ready_ms`, and `total_ms` remain available. `required_to_ready_ms` is an enclosing interval, so it overlaps query work and base persistence; it must not be summed with those child spans. Separate required-check and merge spans are recorded in the existing query trace by the query-correctness work. `first_useful_ms` is now recorded only for success/warn outcomes, preventing fast error or clarification responses from being counted as successful answer latency.

Timing writes atomically merge JSON keys and mirror only the job portion of the trace snapshot. This preserves query trace evidence and handles either order of worker completion and submission timing. An idempotent replay preserves the original submission sample.

```text
.venv/bin/python -m pytest tests/test_bic015_async_turn_jobs.py -k 'submission_and_persistence or worker_persists_error or checked_rows_visible' -q
10 passed, 23 deselected, 8 existing Pydantic UTC warnings in 43.80 seconds.

.venv/bin/ruff check src/institutional_kb/services/bi_job_control.py src/institutional_kb/repositories/bi_conversation.py src/institutional_kb/repositories/bi_turn_job.py src/institutional_kb/api/routers/bi_query.py tests/test_bic015_async_turn_jobs.py
All checks passed.
```

The six new create/append × success/error/clarify cases inject 20 ms into authorization, 20 ms into enqueueing, and 25 ms into audit persistence. They assert separate measured spans, intact query evidence, status-response parity, late acknowledgement preservation after worker completion, and unchanged original timings on replay. These are instrumentation regressions, not a new production performance claim. The earlier full-suite run preceded these final additive tracing changes; the lead owns the final focused union and strict type gate.

```text
.venv/bin/python -m mypy --follow-imports=silent --strict src/institutional_kb/services/bi_job_control.py src/institutional_kb/repositories/bi_conversation.py src/institutional_kb/repositories/bi_turn_job.py src/institutional_kb/api/routers/bi_query.py
Success: no issues found in 4 source files.
```