# 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. ## ATENANT — Enforce organization boundaries in a relational case schema A fictional case-management application filters most requests correctly, but imports and background commands can connect a case to another organization's project or assignee. **Field:** Database engineering. **Suggested stack:** PostgreSQL, Prisma, TypeScript. **Engineer value:** Practice tenant-aware keys, foreign keys and direct-write denial tests. **Company value:** Review defense in depth for data isolation across ordinary and privileged writers. **Delivery agreement:** Ten scoped tickets across three phases. Build a synthetic local service or select a ticket after recreating its prerequisites; estimates exclude setup. ### Setup prerequisites - Create two synthetic organizations with overlapping display names. - Use repository-level authorization and migration-backed constraints. ### Inventory tenant relationships Find and constrain cross-organization references. #### ATENANT-101 — Map tenant ownership for case-management entities **Task · Medium priority · Foundational** noCV practice brief v5 · ATENANT-101 · Enforce organization boundaries in a relational case schema Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Inventory tenant relationships. Depends on: No preceding ticket. Difficulty: Foundational. Estimated focused work: 90 minutes; setup and prerequisite tickets are additional. Estimated field mix: Database engineering 60% · Security 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 schema lists organization IDs on cases but not on comments or attachment relationships, leaving ownership implicit. Acceptance criteria - Identify the owning organization for every selected entity. - List relationships that must stay within one organization. - Separate truly global reference data from tenant data. Implementation constraints - Use a bounded case/project/comment schema. Verification - Trace ownership from a case attachment to its organization. - Identify an intentionally ambiguous relationship and mark it unresolved. Deliverables - Tenant ownership map Rollout and recovery: Review the map before migrations; keep ambiguous relationships out of new writes. Project prerequisites: Create two synthetic organizations with overlapping display names. Use repository-level authorization and migration-backed constraints. Engineer value: Practice tenant-aware keys, foreign keys and direct-write denial tests. Company value: Review defense in depth for data isolation across ordinary and privileged writers. 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. #### ATENANT-102 — Add composite uniqueness for tenant-owned relationship targets **Task · Medium priority · Intermediate** noCV practice brief v5 · ATENANT-102 · Enforce organization boundaries in a relational case schema Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Inventory tenant relationships. Depends on: ATENANT-101. Difficulty: Intermediate. Estimated focused work: 150 minutes; setup and prerequisite tickets are additional. Estimated field mix: Database engineering 80% · Security 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. Globally unique row IDs do not by themselves let a foreign key enforce that case and project belong to the same organization. Acceptance criteria - Add the composite target keys needed for tenant-bound references. - Preserve existing globally unique identities. - Document index duplication and selected constraint names. Implementation constraints - Inspect actual migration SQL and query patterns. Verification - Create identical display names in different organizations. - Attempt a duplicate target identity within the same composite key and verify rejection. Deliverables - Composite-key migration Rollout and recovery: Apply additive keys first; remove only demonstrably redundant indexes after usage review. Project prerequisites: Create two synthetic organizations with overlapping display names. Use repository-level authorization and migration-backed constraints. Engineer value: Practice tenant-aware keys, foreign keys and direct-write denial tests. Company value: Review defense in depth for data isolation across ordinary and privileged writers. 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. #### ATENANT-103 — Reject null organization scope in protected repository methods **Bug · High priority · Foundational** noCV practice brief v5 · ATENANT-103 · Enforce organization boundaries in a relational case schema Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Inventory tenant relationships. Depends on: ATENANT-101, ATENANT-102. Difficulty: Foundational. Estimated focused work: 75 minutes; setup and prerequisite tickets are additional. Estimated field mix: Security 50% · Backend 30% · Database 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 optional organization argument defaults to an unscoped query when an internal caller forgets to pass it. Acceptance criteria - Require nonempty scope in protected method signatures and validation. - Remove unscoped fallback branches. - Keep global reference methods explicitly separate. Implementation constraints - Test direct service calls rather than relying solely on controllers. Verification - Read a project using valid tenant scope. - Omit scope at the runtime boundary and verify no database query executes. Deliverables - Required-scope repository contract Rollout and recovery: Deploy boundary validation before new callers; deny ambiguous internal requests. Project prerequisites: Create two synthetic organizations with overlapping display names. Use repository-level authorization and migration-backed constraints. Engineer value: Practice tenant-aware keys, foreign keys and direct-write denial tests. Company value: Review defense in depth for data isolation across ordinary and privileged writers. 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. ### Protect every write path Carry scope through imports, jobs and transactions. #### ATENANT-104 — Enforce same-organization case-to-project references with a foreign key **Task · High priority · Advanced** noCV practice brief v5 · ATENANT-104 · Enforce organization boundaries in a relational case schema Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Protect every write path. Depends on: ATENANT-102, ATENANT-103. Difficulty: Advanced. Estimated focused work: 210 minutes; setup and prerequisite tickets are additional. Estimated field mix: Database engineering 60% · Security 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. An import can connect a case in one organization to a globally valid project in another organization. Acceptance criteria - Create a composite foreign key including organization identity. - Reject mismatched direct SQL and application writes. - Retain valid same-organization associations. Implementation constraints - Backfill and inspect existing mismatches before validating the constraint. Verification - Insert a valid synthetic case/project relationship. - Insert a cross-organization relationship directly and observe database rejection. Deliverables - Tenant-bound foreign-key migration Rollout and recovery: Validate after a clean discrepancy report; quarantine invalid legacy relationships without guessing ownership. Project prerequisites: Create two synthetic organizations with overlapping display names. Use repository-level authorization and migration-backed constraints. Engineer value: Practice tenant-aware keys, foreign keys and direct-write denial tests. Company value: Review defense in depth for data isolation across ordinary and privileged writers. 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. #### ATENANT-105 — Keep case-comment inserts scoped during concurrent parent changes **Task · Medium priority · Advanced** noCV practice brief v5 · ATENANT-105 · Enforce organization boundaries in a relational case schema Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Protect every write path. Depends on: ATENANT-104. Difficulty: Advanced. Estimated focused work: 240 minutes; setup and prerequisite tickets are additional. Estimated field mix: Database engineering 60% · Backend 20% · Security 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 comment command checks the case once, then writes after an administrator changes related project state. Acceptance criteria - Authorize and insert against current parent ownership in one boundary. - Prevent parent reassignment that would violate child scope. - Return a conflict rather than attach a comment ambiguously. Implementation constraints - Prefer immutable tenant ownership for existing entities. Verification - Insert a comment under stable ownership. - Race a prohibited parent-scope change and verify no cross-tenant comment results. Deliverables - Comment transaction and ownership race test Rollout and recovery: Enable the guarded command; disable tenant reassignment paths that lack a migration protocol. Project prerequisites: Create two synthetic organizations with overlapping display names. Use repository-level authorization and migration-backed constraints. Engineer value: Practice tenant-aware keys, foreign keys and direct-write denial tests. Company value: Review defense in depth for data isolation across ordinary and privileged writers. 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. #### ATENANT-106 — Make bulk case imports validate every tenant relationship before commit **Story · Medium priority · Intermediate** noCV practice brief v5 · ATENANT-106 · Enforce organization boundaries in a relational case schema Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Protect every write path. Depends on: ATENANT-103, ATENANT-104, ATENANT-105. Difficulty: Intermediate. Estimated focused work: 180 minutes; setup and prerequisite tickets are additional. Estimated field mix: Database engineering 50% · Security 30% · Backend 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 bulk import checks the organization of the first row but trusts project IDs in subsequent rows. Acceptance criteria - Validate each relationship under the requested organization. - Report row coordinates with nondisclosing reason codes. - Follow an explicit all-or-nothing import contract. Implementation constraints - Use a synthetic file containing one cross-tenant project reference. Verification - Import a valid multi-row file. - Place an invalid reference late in the file and verify zero case rows commit. Deliverables - Scoped bulk importer and late-row regression Rollout and recovery: Canary small synthetic imports; retain rejected files under bounded restricted storage. Project prerequisites: Create two synthetic organizations with overlapping display names. Use repository-level authorization and migration-backed constraints. Engineer value: Practice tenant-aware keys, foreign keys and direct-write denial tests. Company value: Review defense in depth for data isolation across ordinary and privileged writers. 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. #### ATENANT-107 — Carry tenant identity through background case-processing commands **Bug · High priority · Expert** noCV practice brief v5 · ATENANT-107 · Enforce organization boundaries in a relational case schema Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Protect every write path. Depends on: ATENANT-103, ATENANT-104, ATENANT-106. Difficulty: Expert. Estimated focused work: 300 minutes; setup and prerequisite tickets are additional. Estimated field mix: Security 40% · Backend 30% · Distributed systems 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 queue payload stores only a case ID, and the worker resolves its organization from mutable caller-supplied metadata. Acceptance criteria - Bind durable command identity to case and authoritative tenant. - Reload scoped state before each guarded mutation. - Reject conflicting tenant metadata without executing the provider. Implementation constraints - Queue messages contain opaque IDs, not case contents or secrets. Verification - Process a valid tenant-bound synthetic command. - Replay its case ID with another organization and verify no provider call or mutation. Deliverables - Scoped job command and replay-denial probe Rollout and recovery: Deploy worker guards before new dispatch; quarantine old commands lacking required authority. Project prerequisites: Create two synthetic organizations with overlapping display names. Use repository-level authorization and migration-backed constraints. Engineer value: Practice tenant-aware keys, foreign keys and direct-write denial tests. Company value: Review defense in depth for data isolation across ordinary and privileged writers. 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. ### Prove boundary coverage Reconcile legacy rows and verify database denials. #### ATENANT-108 — Find legacy cross-tenant references without exposing case contents **Chore · Medium priority · Intermediate** noCV practice brief v5 · ATENANT-108 · Enforce organization boundaries in a relational case schema Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Prove boundary coverage. Depends on: ATENANT-104, ATENANT-107. Difficulty: Intermediate. Estimated focused work: 180 minutes; setup and prerequisite tickets are additional. Estimated field mix: Database engineering 50% · Privacy engineering 30% · Security 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 new constraint cannot validate until operators understand existing mismatches, but incident exports should not include case bodies. Acceptance criteria - Report safe entity IDs and mismatch category only. - Bound scans by table and cursor. - Do not automatically choose which tenant should own an invalid relationship. Implementation constraints - Make the reconciliation command read-only. Verification - Scan a valid synthetic dataset with no mismatches. - Insert a deliberate legacy mismatch in a fixture and identify its exact relationship. Deliverables - Tenant-integrity report Rollout and recovery: Run before constraint validation; repair only reviewed mappings through audited commands. Project prerequisites: Create two synthetic organizations with overlapping display names. Use repository-level authorization and migration-backed constraints. Engineer value: Practice tenant-aware keys, foreign keys and direct-write denial tests. Company value: Review defense in depth for data isolation across ordinary and privileged writers. 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. #### ATENANT-109 — Verify tenant constraints through a least-privilege database writer **Task · Medium priority · Expert** noCV practice brief v5 · ATENANT-109 · Enforce organization boundaries in a relational case schema Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Prove boundary coverage. Depends on: ATENANT-104, ATENANT-105, ATENANT-107, ATENANT-108. Difficulty: Expert. Estimated focused work: 300 minutes; setup and prerequisite tickets are additional. Estimated field mix: Database engineering 50% · Security 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. Application tests pass, but direct SQL imports use a role that can bypass expected safeguards. Acceptance criteria - Inventory grants for the application and import roles. - Verify ordinary writers cannot disable constraints or change schema. - Run same-tenant and cross-tenant write checks under the actual limited role. Implementation constraints - Use disposable local roles; do not alter production permissions. Verification - Write a valid relationship through the limited import role. - Attempt cross-tenant insertion and constraint disabling and verify both are denied. Deliverables - Role/grant verification and direct-write tests Rollout and recovery: Apply the limited role in the local import path; stop imports if required checks are bypassable. Project prerequisites: Create two synthetic organizations with overlapping display names. Use repository-level authorization and migration-backed constraints. Engineer value: Practice tenant-aware keys, foreign keys and direct-write denial tests. Company value: Review defense in depth for data isolation across ordinary and privileged writers. 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. #### ATENANT-110 — Document the tenant-data repair protocol and its non-goals **Task · Medium priority · Foundational** noCV practice brief v5 · ATENANT-110 · Enforce organization boundaries in a relational case schema Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included. Phase: Prove boundary coverage. Depends on: ATENANT-108, ATENANT-109. Difficulty: Foundational. Estimated focused work: 90 minutes; setup and prerequisite tickets are additional. Estimated field mix: Database engineering 60% · Security 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. Support wants to move an incorrectly linked case by editing organization IDs directly, risking a cascade of broken ownership. Acceptance criteria - Require a reviewed explicit mapping and affected-relationship inventory. - Preserve audit references and report unresolved children. - Separate repairing a bad link from transferring entity ownership. Implementation constraints - Use one fabricated mismatch as the worked example. Verification - Repair a reviewed synthetic project link with all constraints enabled. - Attempt an incomplete ownership transfer and stop before mutation. Deliverables - Tenant repair runbook Rollout and recovery: Review repairs in a disposable database first; retain invalid rows quarantined when ownership is uncertain. Project prerequisites: Create two synthetic organizations with overlapping display names. Use repository-level authorization and migration-backed constraints. Engineer value: Practice tenant-aware keys, foreign keys and direct-write denial tests. Company value: Review defense in depth for data isolation across ordinary and privileged writers. 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.