{ "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": "19627c06-2c38-4110-8cc2-2c32afccb03c", "key": "BDBMIG", "title": "Database migration review workbench", "field": "Developer tooling", "summary": "Help application engineers inspect and rehearse schema changes before deployment.", "context": "A fictional subscription service has accumulated migration scripts with unclear lock risks and no consistent rehearsal output.", "stack": [ "TypeScript", "PostgreSQL", "SQL" ], "prerequisites": [ "Create a disposable local database with synthetic subscriptions and a separate scratch database; author all fixtures locally." ], "developerValue": "Practice migration tooling, lock reasoning, and clear developer feedback.", "companyValue": "Provide reviewers with repeatable migration checks and explicit recovery limits.", "delivery": "Deliver a local review CLI and SQL rehearsals; do not connect to production databases.", "phases": [ { "id": "inspect", "title": "Inspect changes", "goal": "Make proposed SQL and risk assumptions visible." }, { "id": "rehearse", "title": "Rehearse locally", "goal": "Check application compatibility and interrupted execution." }, { "id": "handoff", "title": "Prepare deployment", "goal": "Package evidence and recovery steps for review." } ], "tickets": [ { "id": "8bea7c72-6b21-47f3-bfb1-fe6dd577788c", "key": "BDBMIG-101", "title": "Add a migration inventory with checksums and sequence gaps", "type": "TASK", "priority": "MEDIUM", "difficulty": "FOUNDATIONAL", "estimateMinutes": 60, "phaseId": "inspect", "dependsOn": [], "scenario": "Two branches introduced migrations with the same sequence number.", "acceptanceCriteria": [ "List migration order and file digests.", "Reject duplicate sequence identifiers.", "Report missing predecessors without executing SQL." ], "implementationNotes": [ "Read migration files as text only." ], "verification": [ "Inventory a valid sequence.", "Detect duplicate IDs and an edited historical file." ], "deliverables": [ "Inventory command." ], "rollout": "Adopt read-only checks first; resolve history discrepancies before applying migrations.", "skills": [ "CLI design", "SQL" ], "fieldMix": [ { "field": "Developer tooling", "percentage": 60 }, { "field": "Database engineering", "percentage": 40 } ], "patterns": [] }, { "id": "4038c11e-19bf-40ea-a135-9637ef65d13e", "key": "BDBMIG-102", "title": "Flag table rewrites and long-held locks for review", "type": "TASK", "priority": "HIGH", "difficulty": "ADVANCED", "estimateMinutes": 210, "phaseId": "inspect", "dependsOn": [ "BDBMIG-101" ], "scenario": "A small-looking column change can block writes on a large table.", "acceptanceCriteria": [ "Identify a documented bounded set of risky SQL forms.", "Explain potential lock or rewrite behavior.", "Mark unrecognized forms as requiring manual review." ], "implementationNotes": [ "This heuristic must not label arbitrary SQL safe." ], "verification": [ "Flag a configured rewrite example.", "Leave an unsupported statement explicitly unclassified." ], "deliverables": [ "Risk annotations and limitations." ], "rollout": "Run advisory checks; preserve manual review for unsupported SQL.", "skills": [ "PostgreSQL", "Static analysis" ], "fieldMix": [ { "field": "Database engineering", "percentage": 50 }, { "field": "Developer tooling", "percentage": 50 } ], "patterns": [] }, { "id": "e53cb2d2-b466-4a2d-8243-2429217623ad", "key": "BDBMIG-103", "title": "Prevent migration commands from targeting unapproved databases", "type": "TASK", "priority": "HIGH", "difficulty": "INTERMEDIATE", "estimateMinutes": 120, "phaseId": "inspect", "dependsOn": [ "BDBMIG-101" ], "scenario": "An engineer copied a connection string from another terminal.", "acceptanceCriteria": [ "Require an explicit local target allowlist.", "Display sanitized target identity before rehearsal.", "Reject missing, remote, or ambiguous target configuration." ], "implementationNotes": [ "Never print passwords or complete connection URLs." ], "verification": [ "Accept the named scratch database.", "Reject a remote hostname and inspect sanitized errors." ], "deliverables": [ "Target guard." ], "rollout": "Default to no execution until a local target is configured.", "skills": [ "Configuration", "Safety boundaries" ], "fieldMix": [ { "field": "Developer tooling", "percentage": 50 }, { "field": "Security", "percentage": 30 }, { "field": "Database engineering", "percentage": 20 } ], "patterns": [] }, { "id": "34fb3acb-3c0b-46e4-acac-31b821ba710a", "key": "BDBMIG-104", "title": "Capture lock waits during a synthetic concurrent workload", "type": "TASK", "priority": "HIGH", "difficulty": "ADVANCED", "estimateMinutes": 240, "phaseId": "rehearse", "dependsOn": [ "BDBMIG-102", "BDBMIG-103" ], "scenario": "Reviewers need observations of a migration while ordinary writes continue.", "acceptanceCriteria": [ "Run bounded synthetic writes against the local fixture.", "Capture migration duration and sampled lock waits.", "Record dataset size and workload parameters with results." ], "implementationNotes": [ "Set statement and lock timeouts; avoid extrapolating local results to production." ], "verification": [ "Observe a compatible migration under writes.", "Force lock contention and verify timeout cleanup." ], "deliverables": [ "Lock rehearsal report." ], "rollout": "Use reports for review; stop rehearsal on timeout and reset scratch data.", "skills": [ "PostgreSQL", "Concurrency" ], "fieldMix": [ { "field": "Database engineering", "percentage": 40 }, { "field": "Performance engineering", "percentage": 30 }, { "field": "Developer tooling", "percentage": 30 } ], "patterns": [] }, { "id": "d0042e0e-0875-4a9c-836a-889d3c76115d", "key": "BDBMIG-105", "title": "Test old and new application queries during expand-contract changes", "type": "TASK", "priority": "HIGH", "difficulty": "ADVANCED", "estimateMinutes": 240, "phaseId": "rehearse", "dependsOn": [ "BDBMIG-103" ], "scenario": "An application rollback fails because the migration removed the column the previous build reads.", "acceptanceCriteria": [ "Define old and new query fixtures.", "Run both after the expansion migration.", "Reject contraction while the old-query support window is open." ], "implementationNotes": [ "Limit scope to one renamed subscription column." ], "verification": [ "Verify dual compatibility after expansion.", "Demonstrate the old query failing after premature contraction." ], "deliverables": [ "Compatibility rehearsal." ], "rollout": "Ship expansion first; delay contraction until the documented rollback window closes.", "skills": [ "Schema evolution", "Compatibility" ], "fieldMix": [ { "field": "Database engineering", "percentage": 50 }, { "field": "Quality engineering", "percentage": 30 }, { "field": "Developer tooling", "percentage": 20 } ], "patterns": [] }, { "id": "a2de3864-6ef3-4743-a45c-54e37af21854", "key": "BDBMIG-106", "title": "Resume a chunked backfill after a process interruption", "type": "BUG", "priority": "HIGH", "difficulty": "ADVANCED", "estimateMinutes": 240, "phaseId": "rehearse", "dependsOn": [ "BDBMIG-104", "BDBMIG-105" ], "scenario": "The backfill restarts from the beginning and repeatedly touches already-migrated subscriptions.", "acceptanceCriteria": [ "Checkpoint a stable key after committed chunks.", "Skip completed rows without changing their meaning.", "Bound batch size and transaction duration." ], "implementationNotes": [ "Make derived values deterministic and record invalid rows separately." ], "verification": [ "Interrupt after two chunks and resume.", "Retry one committed chunk and verify unchanged final values." ], "deliverables": [ "Resumable backfill command." ], "rollout": "Start with small batches; pause safely and retain checkpoints on failures.", "skills": [ "Batch processing", "Transactions" ], "fieldMix": [ { "field": "Database engineering", "percentage": 60 }, { "field": "Developer tooling", "percentage": 40 } ], "patterns": [] }, { "id": "59c0fd55-0506-4253-811d-bf17fbec348d", "key": "BDBMIG-107", "title": "Distinguish reversible schema changes from irreversible data loss", "type": "TASK", "priority": "HIGH", "difficulty": "EXPERT", "estimateMinutes": 300, "phaseId": "handoff", "dependsOn": [ "BDBMIG-105", "BDBMIG-106" ], "scenario": "The deployment template says every migration can be rolled back, even when original values are discarded.", "acceptanceCriteria": [ "Classify schema reversal and data recovery separately.", "Identify the exact point original data becomes unavailable.", "Compare forward repair, backup restore, and application rollback for this migration." ], "implementationNotes": [ "Do not invent recoverability beyond captured data." ], "verification": [ "Rehearse the selected recovery with synthetic rows.", "Show the unrecoverable case when no original data was retained." ], "deliverables": [ "Recovery decision record." ], "rollout": "Block contraction until recovery assumptions and retained-data lifetime are reviewed.", "skills": [ "Migration design", "Recovery planning" ], "fieldMix": [ { "field": "Database engineering", "percentage": 60 }, { "field": "Developer tooling", "percentage": 20 }, { "field": "System design", "percentage": 20 } ], "patterns": [] }, { "id": "04497546-cbbb-4887-9c27-efaff237edf1", "key": "BDBMIG-108", "title": "Produce a bounded migration review bundle", "type": "STORY", "priority": "MEDIUM", "difficulty": "INTERMEDIATE", "estimateMinutes": 150, "phaseId": "handoff", "dependsOn": [ "BDBMIG-104", "BDBMIG-107" ], "scenario": "Reviewers currently receive terminal screenshots without the SQL version or workload context.", "acceptanceCriteria": [ "Include SQL digests, tool version, target class, and observations.", "Exclude connection secrets and row contents.", "Fail if referenced rehearsal output is missing." ], "implementationNotes": [ "Use structured artifacts with documented size limits." ], "verification": [ "Build a complete bundle.", "Reject missing output and scan for seeded secret markers." ], "deliverables": [ "Review bundle exporter." ], "rollout": "Attach bundles to proposed changes; rerun if any migration digest changes.", "skills": [ "Developer experience", "Provenance" ], "fieldMix": [ { "field": "Developer tooling", "percentage": 80 }, { "field": "Privacy engineering", "percentage": 20 } ], "patterns": [] }, { "id": "7331fd6d-886a-486d-a958-db5d71fdd02b", "key": "BDBMIG-109", "title": "Add migration drift checks for an already-applied history", "type": "BUG", "priority": "HIGH", "difficulty": "INTERMEDIATE", "estimateMinutes": 120, "phaseId": "handoff", "dependsOn": [ "BDBMIG-101", "BDBMIG-108" ], "scenario": "A historical migration was edited after one environment had already applied it.", "acceptanceCriteria": [ "Compare stored applied digests to local files.", "Stop on missing or changed applied history.", "Allow only append-only new migrations." ], "implementationNotes": [ "Do not silently repair the migration ledger." ], "verification": [ "Accept appended migrations.", "Reject changed bytes in an applied migration." ], "deliverables": [ "History drift guard." ], "rollout": "Fail closed on drift; document a reviewed corrective migration instead of rewriting history.", "skills": [ "Integrity checks", "Database tooling" ], "fieldMix": [ { "field": "Database engineering", "percentage": 50 }, { "field": "Developer tooling", "percentage": 50 } ], "patterns": [] }, { "id": "ff59af5a-79bf-4d92-a8de-193165ed35ab", "key": "BDBMIG-110", "title": "Write the handoff checklist for a paused backfill", "type": "CHORE", "priority": "LOW", "difficulty": "FOUNDATIONAL", "estimateMinutes": 60, "phaseId": "handoff", "dependsOn": [ "BDBMIG-106", "BDBMIG-109" ], "scenario": "A different engineer must resume a backfill after the original author goes offline.", "acceptanceCriteria": [ "Identify the checkpoint and current migration version.", "Show safe status, resume, and abort commands.", "Describe who decides whether contraction can proceed." ], "implementationNotes": [ "Keep operator instructions scoped to the local rehearsal." ], "verification": [ "Resume using only the checklist.", "Detect a mismatched migration digest before resuming." ], "deliverables": [ "Backfill handoff guide." ], "rollout": "Require the guide with the rehearsal bundle; retain expansion compatibility until completion.", "skills": [ "Runbooks", "Technical writing" ], "fieldMix": [ { "field": "Database engineering", "percentage": 50 }, { "field": "Developer tooling", "percentage": 30 }, { "field": "Site reliability", "percentage": 20 } ], "patterns": [] } ] } ] }