{ "policy": { "version": 5, "patterns": { "version": 1, "method": "CURATED_PRACTICE_TOPIC", "notice": "Pattern topics identify design choices to practice. Read the ticket's acceptance criteria and justify the simplest suitable approach. Tags are not capability or ownership evidence; an untagged ticket has no curated pattern topic assigned." }, "fieldMix": { "version": 1, "method": "CURATED_ESTIMATE", "notice": "Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence." }, "contentStatus": "PRACTICE_BRIEF", "assessmentStatus": "NOT_QUALIFIED", "evidenceUse": "NONE", "aiPolicy": "AI tools are welcome during implementation. Record assumptions, review the result, and verify its behavior.", "notice": "Fictional engineering practice briefs. Starter repositories, fixtures, automated grading, and verified ownership are not included.", "outcomeEvidence": "Tests, patches, and runbooks are requested deliverables. They become Outcome Evidence only through a qualified Mission and immutable Evidence IDs.", "ownershipEvidence": "Independent adaptation must be observed under a declared verification policy and cite immutable Evidence IDs. Completing a planning ticket establishes no Ownership Evidence." }, "patternTopics": [ { "id": "factory-method", "label": "Factory Method", "group": "Creational" }, { "id": "abstract-factory", "label": "Abstract Factory", "group": "Creational" }, { "id": "builder", "label": "Builder", "group": "Creational" }, { "id": "prototype", "label": "Prototype", "group": "Creational" }, { "id": "singleton", "label": "Singleton", "group": "Creational" }, { "id": "adapter", "label": "Adapter", "group": "Structural" }, { "id": "bridge", "label": "Bridge", "group": "Structural" }, { "id": "composite", "label": "Composite", "group": "Structural" }, { "id": "decorator", "label": "Decorator", "group": "Structural" }, { "id": "facade", "label": "Facade", "group": "Structural" }, { "id": "flyweight", "label": "Flyweight", "group": "Structural" }, { "id": "proxy", "label": "Proxy", "group": "Structural" }, { "id": "chain-of-responsibility", "label": "Chain of Responsibility", "group": "Behavioral" }, { "id": "command", "label": "Command", "group": "Behavioral" }, { "id": "interpreter", "label": "Interpreter", "group": "Behavioral" }, { "id": "iterator", "label": "Iterator", "group": "Behavioral" }, { "id": "mediator", "label": "Mediator", "group": "Behavioral" }, { "id": "memento", "label": "Memento", "group": "Behavioral" }, { "id": "observer", "label": "Observer", "group": "Behavioral" }, { "id": "state", "label": "State", "group": "Behavioral" }, { "id": "strategy", "label": "Strategy", "group": "Behavioral" }, { "id": "template-method", "label": "Template Method", "group": "Behavioral" }, { "id": "visitor", "label": "Visitor", "group": "Behavioral" }, { "id": "ports-and-adapters", "label": "Ports and Adapters", "group": "Architectural" }, { "id": "cqrs", "label": "CQRS", "group": "Architectural" }, { "id": "strangler-fig", "label": "Strangler Fig", "group": "Architectural" }, { "id": "saga", "label": "Saga", "group": "Distributed and reliability" }, { "id": "transactional-outbox", "label": "Transactional Outbox", "group": "Distributed and reliability" }, { "id": "circuit-breaker", "label": "Circuit Breaker", "group": "Distributed and reliability" }, { "id": "bulkhead", "label": "Bulkhead", "group": "Distributed and reliability" } ], "projects": [ { "id": "e9d0551d-7419-432e-947d-50e5387b3f13", "key": "PQUERY", "title": "Keep the operations inbox fast as history grows", "summary": "Tune a tenant-scoped PostgreSQL inbox using plans, representative distributions and reversible changes.", "context": "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.", "stack": [ "PostgreSQL", "SQL", "TypeScript", "pg_stat_statements" ], "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." ], "developerValue": "Practice reading execution plans, balancing read and write cost, and validating performance without weakening tenant boundaries.", "companyValue": "Create reproducible query diagnostics and reversible index or query proposals for an operational application.", "delivery": "Ten tickets from workload definition to guarded migration. Estimates exclude database setup and predecessor tickets; all destructive data fixtures remain local and synthetic.", "phases": [ { "id": "baseline", "title": "Reproduce the slow inbox", "goal": "Make skew, query behavior and baseline measurements inspectable." }, { "id": "tune", "title": "Change the expensive paths", "goal": "Reduce unnecessary reads while preserving filter, ordering and authorization semantics." }, { "id": "guard", "title": "Check write cost and deployment behavior", "goal": "Choose a supported improvement and rehearse its migration and rollback." } ], "field": "Performance engineering", "tickets": [ { "id": "c93946e4-c4ea-4a15-ad08-a200a122ddf1", "key": "PQUERY-101", "title": "Seed an inbox where one tenant owns most of the closed history", "type": "TASK", "priority": "MEDIUM", "difficulty": "FOUNDATIONAL", "estimateMinutes": 75, "phaseId": "baseline", "dependsOn": [], "scenario": "The existing fixture spreads rows evenly across tenants. It cannot reproduce the large account whose open-work view is slow.", "acceptanceCriteria": [ "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." ], "implementationNotes": [ "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": "Load only a disposable development database; make the rebuild command reject an unrecognized database target.", "skills": [ "Synthetic datasets", "SQL" ], "fieldMix": [ { "field": "Data engineering", "percentage": 50 }, { "field": "Quality engineering", "percentage": 30 }, { "field": "Performance engineering", "percentage": 20 } ], "patterns": [] }, { "id": "d2f744ef-8f09-4053-9bfc-6695907b27d6", "key": "PQUERY-102", "title": "Capture buffer reads and row estimates for the slow inbox query", "type": "TASK", "priority": "MEDIUM", "difficulty": "FOUNDATIONAL", "estimateMinutes": 90, "phaseId": "baseline", "dependsOn": [ "PQUERY-101" ], "scenario": "The ticket says the query takes seconds but contains no plan, parameters or indication of whether the cache was warm.", "acceptanceCriteria": [ "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." ], "implementationNotes": [ "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": "Keep the baseline artifacts unchanged when evaluating candidate queries; new fixture revisions get new run labels.", "skills": [ "Execution plans", "Measurement" ], "fieldMix": [ { "field": "Database engineering", "percentage": 50 }, { "field": "Performance engineering", "percentage": 50 } ], "patterns": [] }, { "id": "149520ab-ea32-4694-9413-635139c3061b", "key": "PQUERY-103", "title": "Count hidden assignment queries made by one inbox request", "type": "BUG", "priority": "HIGH", "difficulty": "INTERMEDIATE", "estimateMinutes": 120, "phaseId": "baseline", "dependsOn": [ "PQUERY-101" ], "scenario": "The main SQL query is quick, but the API loads the latest assignee separately for every visible work order.", "acceptanceCriteria": [ "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." ], "implementationNotes": [ "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": "Enable detailed counting in the local investigation only; retain a bounded aggregate if instrumentation is later adapted for deployment.", "skills": [ "N+1 diagnosis", "ORM behavior" ], "fieldMix": [ { "field": "Performance engineering", "percentage": 40 }, { "field": "Backend", "percentage": 30 }, { "field": "Database engineering", "percentage": 30 } ], "patterns": [] }, { "id": "64ae1231-84de-443c-9165-2b34f6f1be9c", "key": "PQUERY-104", "title": "Batch latest-assignee lookup for the current inbox page", "type": "STORY", "priority": "HIGH", "difficulty": "ADVANCED", "estimateMinutes": 210, "phaseId": "tune", "dependsOn": [ "PQUERY-102", "PQUERY-103" ], "scenario": "Fetching one assignee per work order makes latency increase with page size even after the main inbox query is tuned.", "acceptanceCriteria": [ "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." ], "implementationNotes": [ "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": "Switch the lookup path independently; revert to the previous implementation if equivalence or tenant checks fail.", "skills": [ "Query design", "Tenant isolation" ], "fieldMix": [ { "field": "Database engineering", "percentage": 50 }, { "field": "Backend", "percentage": 30 }, { "field": "Performance engineering", "percentage": 20 } ], "patterns": [] }, { "id": "b24a4a9d-abdc-42d9-a75d-4fbfea5d2920", "key": "PQUERY-105", "title": "Evaluate a partial index for open work without slowing closure updates", "type": "TASK", "priority": "HIGH", "difficulty": "ADVANCED", "estimateMinutes": 240, "phaseId": "tune", "dependsOn": [ "PQUERY-101", "PQUERY-102" ], "scenario": "Only a small fraction of work orders remain open. A broad index makes the open inbox faster but adds substantial storage and maintenance cost.", "acceptanceCriteria": [ "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." ], "implementationNotes": [ "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": "Apply the index in the isolated database first; the rollback drops only the added index and preserves all work-order data.", "skills": [ "Indexing", "Read/write tradeoffs" ], "fieldMix": [ { "field": "Database engineering", "percentage": 50 }, { "field": "Performance engineering", "percentage": 50 } ], "patterns": [] }, { "id": "1c29710e-8ef0-4ee5-ab11-353c848386f2", "key": "PQUERY-106", "title": "Replace deep offset paging with a stable inbox cursor", "type": "STORY", "priority": "HIGH", "difficulty": "ADVANCED", "estimateMinutes": 240, "phaseId": "tune", "dependsOn": [ "PQUERY-101", "PQUERY-102" ], "scenario": "An operator reaches old open work through hundreds of pages. Each offset request scans and discards an increasing number of matching rows.", "acceptanceCriteria": [ "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." ], "implementationNotes": [ "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": "Offer the cursor route as an explicit version and migrate the local client behind a switch; retain the offset route during rehearsal.", "skills": [ "Pagination", "Query performance" ], "fieldMix": [ { "field": "Database engineering", "percentage": 40 }, { "field": "API design", "percentage": 40 }, { "field": "Performance engineering", "percentage": 20 } ], "patterns": [] }, { "id": "6b8fafaf-dd3d-4015-92ce-6f75d78ce455", "key": "PQUERY-107", "title": "Explain the bad prepared plan used for the largest tenant", "type": "BUG", "priority": "MEDIUM", "difficulty": "EXPERT", "estimateMinutes": 330, "phaseId": "tune", "dependsOn": [ "PQUERY-101", "PQUERY-102", "PQUERY-105" ], "scenario": "The same prepared inbox statement serves tiny tenants and the dominant tenant. A plan that works for one distribution performs poorly for the other.", "acceptanceCriteria": [ "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." ], "implementationNotes": [ "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": "Limit any configuration change to the affected query path; remove it and restore the baseline query if small-tenant latency breaches its budget.", "skills": [ "Query planning", "Data skew" ], "fieldMix": [ { "field": "Database engineering", "percentage": 60 }, { "field": "Performance engineering", "percentage": 40 } ], "patterns": [] }, { "id": "3850f93e-fd71-4f66-ab9c-f88e4fc04c00", "key": "PQUERY-108", "title": "Rehearse the inbox index migration while writes continue", "type": "CHORE", "priority": "HIGH", "difficulty": "ADVANCED", "estimateMinutes": 210, "phaseId": "guard", "dependsOn": [ "PQUERY-105" ], "scenario": "The proposed index works after a clean restore. Nobody has checked what happens when creation is interrupted while the application is updating work orders.", "acceptanceCriteria": [ "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." ], "implementationNotes": [ "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": "Require the local concurrent-write rehearsal before proposing deployment; stop if lock duration or data reconciliation violates the stated budget.", "skills": [ "Database migrations", "Recovery" ], "fieldMix": [ { "field": "Database engineering", "percentage": 60 }, { "field": "Site reliability", "percentage": 30 }, { "field": "Performance engineering", "percentage": 10 } ], "patterns": [] }, { "id": "a447fb87-a48d-46db-a62c-1395756cbdf7", "key": "PQUERY-109", "title": "Keep performance fixtures useful after the inbox schema changes", "type": "CHORE", "priority": "MEDIUM", "difficulty": "INTERMEDIATE", "estimateMinutes": 120, "phaseId": "guard", "dependsOn": [ "PQUERY-101", "PQUERY-104", "PQUERY-106" ], "scenario": "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.", "acceptanceCriteria": [ "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." ], "implementationNotes": [ "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": "Run validation before every benchmark; invalid data stops comparison and is rebuilt in the disposable database.", "skills": [ "Regression fixtures", "Data validation" ], "fieldMix": [ { "field": "Quality engineering", "percentage": 50 }, { "field": "Data engineering", "percentage": 30 }, { "field": "Performance engineering", "percentage": 20 } ], "patterns": [] }, { "id": "bff7ea37-a75c-4696-85bb-b6088d15d13f", "key": "PQUERY-110", "title": "Publish the inbox query decision with both latency and write cost", "type": "TASK", "priority": "HIGH", "difficulty": "EXPERT", "estimateMinutes": 300, "phaseId": "guard", "dependsOn": [ "PQUERY-104", "PQUERY-106", "PQUERY-107", "PQUERY-108", "PQUERY-109" ], "scenario": "The review has several attractive charts but no single explanation of which changes should ship together or what would trigger rollback.", "acceptanceCriteria": [ "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." ], "implementationNotes": [ "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": "Advance only the documented candidate within the exercise environment; keep previous query and index definitions available for a bounded rollback.", "skills": [ "Performance evaluation", "Engineering tradeoffs" ], "fieldMix": [ { "field": "Performance engineering", "percentage": 50 }, { "field": "Database engineering", "percentage": 50 } ], "patterns": [] } ] } ] }