# noCV engineering task library Content version 5 Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Tests, patches, and runbooks are requested deliverables. They become Outcome Evidence only through a qualified Mission and immutable Evidence IDs. Independent adaptation must be observed under a declared verification policy and cite immutable Evidence IDs. Completing a planning ticket establishes no Ownership Evidence. ## PQUERY — Keep the operations inbox fast as history grows A fictional maintenance service lists open work orders beside years of closed history. A query that was cheap in a small demo now scans far more rows than it returns. Recreate the schema and synthetic workload locally; no customer database or supplied fixture is assumed. **Field:** Performance engineering. **Suggested stack:** PostgreSQL, SQL, TypeScript, pg_stat_statements. **Engineer value:** Practice reading execution plans, balancing read and write cost, and validating performance without weakening tenant boundaries. **Company value:** Create reproducible query diagnostics and reversible index or query proposals for an operational application. **Delivery agreement:** Ten tickets from workload definition to guarded migration. Estimates exclude database setup and predecessor tickets; all destructive data fixtures remain local and synthetic. ### Setup prerequisites - Create an isolated local database with tenants, work orders and assignment history. - Seed 200,000 synthetic work orders with documented skew and a repeatable random seed; record PostgreSQL version, settings and resource limits. ### Reproduce the slow inbox Make skew, query behavior and baseline measurements inspectable. #### PQUERY-101 — Seed an inbox where one tenant owns most of the closed history **Task · Medium priority · Foundational** noCV practice brief v5 · PQUERY-101 · Keep the operations inbox fast as history grows Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Reproduce the slow inbox. Depends on: No preceding ticket. Difficulty: Foundational. Estimated focused work: 75 minutes; setup and prerequisite tickets are additional. Estimated field mix: Data engineering 50% · Quality engineering 30% · Performance engineering 20%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. The existing fixture spreads rows evenly across tenants. It cannot reproduce the large account whose open-work view is slow. Acceptance criteria - Seed 200,000 rows across 100 tenants with one tenant owning 60% of rows and documented open/closed ratios. - Include tied creation times, unassigned work and tenants with no matching rows. - Generate deterministic expected inbox IDs for named filter and ordering cases. Implementation constraints - Keep tenant identities fictitious and use a manifest so row counts and skew are explicit. Verification - Rebuild from the same seed and compare per-tenant counts and expected inbox results. - Run the empty-tenant and timestamp-tie cases and verify a deterministic order. Deliverables - Seed generator, distribution summary and expected-result fixtures Rollout and recovery: Load only a disposable development database; make the rebuild command reject an unrecognized database target. Project prerequisites: Create an isolated local database with tenants, work orders and assignment history. Seed 200,000 synthetic work orders with documented skew and a repeatable random seed; record PostgreSQL version, settings and resource limits. Engineer value: Practice reading execution plans, balancing read and write cost, and validating performance without weakening tenant boundaries. Company value: Create reproducible query diagnostics and reversible index or query proposals for an operational application. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence. #### PQUERY-102 — Capture buffer reads and row estimates for the slow inbox query **Task · Medium priority · Foundational** noCV practice brief v5 · PQUERY-102 · Keep the operations inbox fast as history grows Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Reproduce the slow inbox. Depends on: PQUERY-101. Difficulty: Foundational. Estimated focused work: 90 minutes; setup and prerequisite tickets are additional. Estimated field mix: Database engineering 50% · Performance engineering 50%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. The ticket says the query takes seconds but contains no plan, parameters or indication of whether the cache was warm. Acceptance criteria - Save EXPLAIN ANALYZE with buffers for the large, median and empty tenant fixtures. - Record query parameters, statistics freshness, cache-warmup procedure and database resource settings. - Identify estimate-versus-actual row differences and time-consuming nodes without claiming a fix from plan shape alone. Implementation constraints - Use SELECT-only plans here; running ANALYZE on mutating statements would execute them. Verification - Repeat each parameter case three times after the declared warmup and report all durations. - Verify the captured query returns the fixture IDs from the baseline contract. Deliverables - Annotated plans and reproducible capture command Rollout and recovery: Keep the baseline artifacts unchanged when evaluating candidate queries; new fixture revisions get new run labels. Project prerequisites: Create an isolated local database with tenants, work orders and assignment history. Seed 200,000 synthetic work orders with documented skew and a repeatable random seed; record PostgreSQL version, settings and resource limits. Engineer value: Practice reading execution plans, balancing read and write cost, and validating performance without weakening tenant boundaries. Company value: Create reproducible query diagnostics and reversible index or query proposals for an operational application. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence. #### PQUERY-103 — Count hidden assignment queries made by one inbox request **Bug · High priority · Intermediate** noCV practice brief v5 · PQUERY-103 · Keep the operations inbox fast as history grows Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Reproduce the slow inbox. Depends on: PQUERY-101. Difficulty: Intermediate. Estimated focused work: 120 minutes; setup and prerequisite tickets are additional. Estimated field mix: Performance engineering 40% · Backend 30% · Database engineering 30%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. The main SQL query is quick, but the API loads the latest assignee separately for every visible work order. Acceptance criteria - Record the query count for page sizes 1, 20 and 100 without storing raw tenant payloads in logs. - Add a request-level assertion exposing query growth proportional to returned rows. - Preserve assignment ordering, including equal timestamps and work orders with no history. Implementation constraints - Instrument the local database client boundary so lazy ORM loads are counted as well as explicit queries. Verification - Request all three page sizes and compare total database calls. - Use a work order with multiple assignment records and verify the selected assignee matches the documented tie-breaker. Deliverables - Query-count regression and assignment fixture Rollout and recovery: Enable detailed counting in the local investigation only; retain a bounded aggregate if instrumentation is later adapted for deployment. Project prerequisites: Create an isolated local database with tenants, work orders and assignment history. Seed 200,000 synthetic work orders with documented skew and a repeatable random seed; record PostgreSQL version, settings and resource limits. Engineer value: Practice reading execution plans, balancing read and write cost, and validating performance without weakening tenant boundaries. Company value: Create reproducible query diagnostics and reversible index or query proposals for an operational application. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence. ### Change the expensive paths Reduce unnecessary reads while preserving filter, ordering and authorization semantics. #### PQUERY-104 — Batch latest-assignee lookup for the current inbox page **Story · High priority · Advanced** noCV practice brief v5 · PQUERY-104 · Keep the operations inbox fast as history grows Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Change the expensive paths. Depends on: PQUERY-102, PQUERY-103. Difficulty: Advanced. Estimated focused work: 210 minutes; setup and prerequisite tickets are additional. Estimated field mix: Database engineering 50% · Backend 30% · Performance engineering 20%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. Fetching one assignee per work order makes latency increase with page size even after the main inbox query is tuned. Acceptance criteria - Resolve assignments in a bounded number of queries independent of page size up to 100. - Preserve tenant scope in every lookup and return one deterministic latest assignment per visible work order. - Return identical response data to the baseline fixture, including missing assignments. Implementation constraints - Compare a batched lookup and a lateral or window-query alternative; record the chosen tradeoff using the seeded distribution. Verification - Compare complete responses and query counts for page sizes 1, 20 and 100. - Inject a foreign-tenant assignment ID into the request fixture and verify it cannot be returned. Deliverables - Batched implementation, response equivalence tests and query-count results Rollout and recovery: Switch the lookup path independently; revert to the previous implementation if equivalence or tenant checks fail. Project prerequisites: Create an isolated local database with tenants, work orders and assignment history. Seed 200,000 synthetic work orders with documented skew and a repeatable random seed; record PostgreSQL version, settings and resource limits. Engineer value: Practice reading execution plans, balancing read and write cost, and validating performance without weakening tenant boundaries. Company value: Create reproducible query diagnostics and reversible index or query proposals for an operational application. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence. #### PQUERY-105 — Evaluate a partial index for open work without slowing closure updates **Task · High priority · Advanced** noCV practice brief v5 · PQUERY-105 · Keep the operations inbox fast as history grows Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Change the expensive paths. Depends on: PQUERY-101, PQUERY-102. Difficulty: Advanced. Estimated focused work: 240 minutes; setup and prerequisite tickets are additional. Estimated field mix: Database engineering 50% · Performance engineering 50%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. Only a small fraction of work orders remain open. A broad index makes the open inbox faster but adds substantial storage and maintenance cost. Acceptance criteria - Compare the current plan with at least one partial index aligned with the actual open-state predicate and order. - Measure index size, inbox latency and update throughput for work moving into and out of the indexed state. - Keep the candidate only if its read benefit and write tradeoff satisfy explicitly stated exercise budgets. Implementation constraints - Use three paired runs on the same synthetic data and settings; do not force index use or disable planner options to manufacture a result. Verification - Run open, closed and mixed-state filters and verify both result equivalence and selected plans. - Close and reopen a fixed batch while reading the inbox; report lock waits, write duration and index size. Deliverables - Index decision record, migration draft and comparative measurements Rollout and recovery: Apply the index in the isolated database first; the rollback drops only the added index and preserves all work-order data. Project prerequisites: Create an isolated local database with tenants, work orders and assignment history. Seed 200,000 synthetic work orders with documented skew and a repeatable random seed; record PostgreSQL version, settings and resource limits. Engineer value: Practice reading execution plans, balancing read and write cost, and validating performance without weakening tenant boundaries. Company value: Create reproducible query diagnostics and reversible index or query proposals for an operational application. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence. #### PQUERY-106 — Replace deep offset paging with a stable inbox cursor **Story · High priority · Advanced** noCV practice brief v5 · PQUERY-106 · Keep the operations inbox fast as history grows Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Change the expensive paths. Depends on: PQUERY-101, PQUERY-102. Difficulty: Advanced. Estimated focused work: 240 minutes; setup and prerequisite tickets are additional. Estimated field mix: Database engineering 40% · API design 40% · Performance engineering 20%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. An operator reaches old open work through hundreds of pages. Each offset request scans and discards an increasing number of matching rows. Acceptance criteria - Use a stable sort tuple with a unique tie-breaker and an opaque versioned cursor. - Bind the cursor to the tenant and filter contract; reject mismatched or malformed cursors. - Document concurrent-insert behavior and preserve a bounded page size without promising a snapshot across requests. Implementation constraints - Measure shallow and deep page requests against the same fixture; keep the response contract explicit for clients migrating from offsets. Verification - Traverse the unchanged fixture and compare all returned IDs with the expected sorted set without duplicates or omissions. - Insert a tied-timestamp row between requests and test the documented behavior; reject a cursor from another tenant. Deliverables - Cursor contract, implementation and depth-comparison report Rollout and recovery: Offer the cursor route as an explicit version and migrate the local client behind a switch; retain the offset route during rehearsal. Project prerequisites: Create an isolated local database with tenants, work orders and assignment history. Seed 200,000 synthetic work orders with documented skew and a repeatable random seed; record PostgreSQL version, settings and resource limits. Engineer value: Practice reading execution plans, balancing read and write cost, and validating performance without weakening tenant boundaries. Company value: Create reproducible query diagnostics and reversible index or query proposals for an operational application. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence. #### PQUERY-107 — Explain the bad prepared plan used for the largest tenant **Bug · Medium priority · Expert** noCV practice brief v5 · PQUERY-107 · Keep the operations inbox fast as history grows Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Change the expensive paths. Depends on: PQUERY-101, PQUERY-102, PQUERY-105. Difficulty: Expert. Estimated focused work: 330 minutes; setup and prerequisite tickets are additional. Estimated field mix: Database engineering 60% · Performance engineering 40%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. The same prepared inbox statement serves tiny tenants and the dominant tenant. A plan that works for one distribution performs poorly for the other. Acceptance criteria - Reproduce the plan difference with documented preparation and execution sequences. - Compare statistics correction, query restructuring and bounded per-query planning choices; select a justified option. - Keep tenant scope and result ordering unchanged, and document planning overhead as part of the decision. Implementation constraints - Do not change a global planner setting as an unexplained workaround; record the exact PostgreSQL version because planning behavior matters. Verification - Alternate large and small tenant executions across repeated sessions and retain planning plus execution measurements. - Rebuild statistics after a distribution change and verify the selected solution remains correct or clearly flags an invalid baseline. Deliverables - Prepared-plan reproduction and a supported mitigation with regression cases Rollout and recovery: Limit any configuration change to the affected query path; remove it and restore the baseline query if small-tenant latency breaches its budget. Project prerequisites: Create an isolated local database with tenants, work orders and assignment history. Seed 200,000 synthetic work orders with documented skew and a repeatable random seed; record PostgreSQL version, settings and resource limits. Engineer value: Practice reading execution plans, balancing read and write cost, and validating performance without weakening tenant boundaries. Company value: Create reproducible query diagnostics and reversible index or query proposals for an operational application. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence. ### Check write cost and deployment behavior Choose a supported improvement and rehearse its migration and rollback. #### PQUERY-108 — Rehearse the inbox index migration while writes continue **Chore · High priority · Advanced** noCV practice brief v5 · PQUERY-108 · Keep the operations inbox fast as history grows Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Check write cost and deployment behavior. Depends on: PQUERY-105. Difficulty: Advanced. Estimated focused work: 210 minutes; setup and prerequisite tickets are additional. Estimated field mix: Database engineering 60% · Site reliability 30% · Performance engineering 10%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. The proposed index works after a clean restore. Nobody has checked what happens when creation is interrupted while the application is updating work orders. Acceptance criteria - Run the migration with a bounded concurrent write fixture and record locks and write failures. - Detect an interrupted or invalid index and define an idempotent retry procedure. - Provide an explicit rollback that does not remove an index belonging to another migration. Implementation constraints - Use PostgreSQL-supported online index operations with their transaction restrictions; record commands and ownership checks in the runbook. Verification - Interrupt index creation in the disposable database, inspect its state and exercise recovery. - Compare work-order counts and IDs before and after migration, retry and rollback while the write fixture runs. Deliverables - Migration, interruption rehearsal and operator recovery notes Rollout and recovery: Require the local concurrent-write rehearsal before proposing deployment; stop if lock duration or data reconciliation violates the stated budget. Project prerequisites: Create an isolated local database with tenants, work orders and assignment history. Seed 200,000 synthetic work orders with documented skew and a repeatable random seed; record PostgreSQL version, settings and resource limits. Engineer value: Practice reading execution plans, balancing read and write cost, and validating performance without weakening tenant boundaries. Company value: Create reproducible query diagnostics and reversible index or query proposals for an operational application. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence. #### PQUERY-109 — Keep performance fixtures useful after the inbox schema changes **Chore · Medium priority · Intermediate** noCV practice brief v5 · PQUERY-109 · Keep the operations inbox fast as history grows Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Check write cost and deployment behavior. Depends on: PQUERY-101, PQUERY-104, PQUERY-106. Difficulty: Intermediate. Estimated focused work: 120 minutes; setup and prerequisite tickets are additional. Estimated field mix: Quality engineering 50% · Data engineering 30% · Performance engineering 20%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. A new nullable column changed the seed loader. The benchmark still completes, but most rows now take a cheap fallback path that users rarely see. Acceptance criteria - Validate row counts, state distributions, null ratios and assignment fan-out before a run. - Reject fixtures whose version or invariants do not match the query comparison manifest. - Retain representative expected responses so a faster wrong query cannot pass. Implementation constraints - Express fixture invariants separately from the query implementation; avoid asserting only that SQL returns some rows. Verification - Deliberately skew null ratios and remove assignment history; the fixture gate must report both changes. - Regenerate the approved fixture and run the same checks successfully without hand-edited counts. Deliverables - Fixture-validation command and schema-change procedure Rollout and recovery: Run validation before every benchmark; invalid data stops comparison and is rebuilt in the disposable database. Project prerequisites: Create an isolated local database with tenants, work orders and assignment history. Seed 200,000 synthetic work orders with documented skew and a repeatable random seed; record PostgreSQL version, settings and resource limits. Engineer value: Practice reading execution plans, balancing read and write cost, and validating performance without weakening tenant boundaries. Company value: Create reproducible query diagnostics and reversible index or query proposals for an operational application. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence. #### PQUERY-110 — Publish the inbox query decision with both latency and write cost **Task · High priority · Expert** noCV practice brief v5 · PQUERY-110 · Keep the operations inbox fast as history grows Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Check write cost and deployment behavior. Depends on: PQUERY-104, PQUERY-106, PQUERY-107, PQUERY-108, PQUERY-109. Difficulty: Expert. Estimated focused work: 300 minutes; setup and prerequisite tickets are additional. Estimated field mix: Performance engineering 50% · Database engineering 50%. Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence. The review has several attractive charts but no single explanation of which changes should ship together or what would trigger rollback. Acceptance criteria - Compare baseline and candidate with three paired warmed runs at fixed concurrency, reporting p95, p99, query count and buffer reads. - For the exercise require at least 20% lower median p95 for the dominant-tenant open view, no more than 10% loss in closure-update throughput and exact fixture equivalence. - List the retained and rejected changes, noisy or inconclusive cases, migration order and rollback triggers. Implementation constraints - These relative thresholds are local exercise gates; report all repetitions and do not claim production improvement from synthetic results. Verification - Run both large and median tenant workloads alongside the closure-update fixture. - Rehearse rollback and rerun correctness plus the baseline workload to confirm recovery is understood. Deliverables - Query-performance decision, raw run results and migration handoff Rollout and recovery: Advance only the documented candidate within the exercise environment; keep previous query and index definitions available for a bounded rollback. Project prerequisites: Create an isolated local database with tenants, work orders and assignment history. Seed 200,000 synthetic work orders with documented skew and a repeatable random seed; record PostgreSQL version, settings and resource limits. Engineer value: Practice reading execution plans, balancing read and write cost, and validating performance without weakening tenant boundaries. Company value: Create reproducible query diagnostics and reversible index or query proposals for an operational application. AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior. Planning status does not create Outcome Evidence or Ownership Evidence.