adr-creator

name	adr-creator
description	Create Architecture Decision Records (ADRs) documenting significant technical decisions for the FF Analytics platform. This skill should be used when making architectural choices, evaluating alternatives for data models or infrastructure, documenting trade-offs, or when the user asks "should we use X or Y approach?" Guides through the ADR creation workflow from context gathering to documentation.

ADR Creator

Create Architecture Decision Records (ADRs) that document significant technical decisions for the Fantasy Football Analytics project following the established ADR format and workflow.

When to Use This Skill

Use this skill proactively when:

• Making architectural decisions (data model, infrastructure, tooling)
• User asks "should we use X or Y?" or "what's the best approach for Z?"
• Evaluating alternatives with trade-offs
• Documenting decisions that will impact future development
• Creating records referenced by strategic-planner skill
• Superseding or updating previous ADRs

ADR Purpose

ADRs serve as permanent records of significant technical decisions, capturing:

1. Context - Why the decision was needed
2. Alternatives - What options were considered (and why rejected)
3. Decision - What was chosen and how to implement it
4. Consequences - Trade-offs, benefits, and risks

Benefits:

• Future developers understand "why" not just "what"
• Prevents re-litigating settled decisions
• Documents trade-offs for later review
• Creates institutional memory

ADR Categories

The FF Analytics project uses ADRs for:

1. Data Model Decisions (e.g., ADR-007: Separate fact tables, ADR-009: 2×2 stat model)
2. Infrastructure Choices (e.g., ADR-004: GitHub Actions, ADR-006: GCS storage)
3. Data Quality Policies (e.g., ADR-005: Server-side copy, ADR-008: Idempotency)
4. Identity & Conformance (e.g., ADR-010: mfl_id canonical, ADR-011: Team conformance, ADR-014: Pick identity)

ADR Creation Workflow

Step 1: Identify Decision Context

Ask these questions:

• What problem needs solving?
• What constraints exist (technical, business, time)?
• Is this a new decision or updating an existing ADR?
• Does this supersede a previous ADR?
• What's the scope (data model, infrastructure, process)?

Decision Scope Examples:

HIGH - Needs ADR:

• Choosing between DuckDB vs PostgreSQL for local development
• Deciding on SCD Type 1 vs Type 2 for player dimension
• Selecting canonical player ID (mfl_id vs gsis_id)
• Establishing batch vs streaming architecture

LOW - Does NOT need ADR:

• Naming a specific model or column
• Minor code refactoring
• Formatting/style choices covered by linting
• Tactical bug fixes

Rule of thumb: If the decision impacts multiple components or will be referenced in future work, create an ADR.

Step 2: Research Alternatives

Thoroughly evaluate options:

1. List all viable alternatives (aim for 2-4 options)
2. Research each option:
   • Technical feasibility
   • Implementation complexity
   • Performance implications
   • Cost (development time, runtime, maintenance)
   • Compatibility with existing architecture
3. Consult existing ADRs for precedents or conflicts
4. Prototype if needed (for high-risk decisions)

Example (from ADR-010):

Alternative	Pros	Cons	Verdict
gsis_id canonical	NFL authoritative	NFL-specific, couples to nflverse	Rejected
mfl_id canonical	Platform-neutral, nflverse provides crosswalk	Indirect (via crosswalk)	Chosen
Generate synthetic ID	Complete control	No provider alignment, complex migration	Rejected

Step 3: Draft ADR

Use the template from assets/adr_template.md:

1. Get next ADR number: Run scripts/get_next_adr_number.py

cd .claude/skills/adr-creator
python scripts/get_next_adr_number.py  # Returns ADR-015, ADR-016, etc.

2. Create file: docs/adr/ADR-{NNN}-{slug}.md

   • Number: Zero-padded 3 digits (ADR-004, ADR-015)
   • Slug: Kebab-case summary (e.g., mfl-id-canonical-identity)

3. Fill all sections completely:

Context Section

• Problem statement: Clearly define what needs deciding
• Current state: Describe relevant existing architecture
• Constraints: Technical, business, or timeline limitations
• Stakeholders: Who is affected by this decision?

Good context example:

"The platform integrates 19+ fantasy data providers, each using different player IDs. We need a canonical player_id for dim_player that enables identity resolution across all providers while remaining stable as providers are added/removed."

Decision Section

• Be specific and actionable: Not "use DuckDB" but "use DuckDB for local development with External Parquet materialization for large models"
• Include technical details: Schemas, configurations, implementation approach
• Reference code/SQL if helpful: Concrete examples clarify intent

Good decision example (from ADR-010):

"Use nflverse's mfl_id as the canonical player_id throughout the dimensional model. Create dim_player_id_xref crosswalk seed table mapping mfl_id to 19 provider-specific IDs."

Consequences Section

• Positive consequences: What benefits does this provide?
• Negative consequences: What trade-offs or risks?
• Mitigation strategies: How to address the negatives?

Be honest about trade-offs. Example:

Negative: Requires join to crosswalk table for provider ID lookup Mitigation: Crosswalk is small (~5K rows), joins are fast; SCD Type 1 so no history bloat

Step 4: Review & Refine

Self-review checklist:

• Context is clear and complete (someone unfamiliar with project understands the problem)
• Decision is specific and actionable (implementation team knows what to build)
• Alternatives are documented with rationale for rejection
• Consequences are realistic (not overly optimistic)
• References are complete (links to specs, related ADRs, external docs)
• Status is correct (Draft for new, Accepted after implementation, Superseded if replaced)

Verification:

Does this ADR answer:

1. Why did we make this decision? (Context)
2. What are we doing? (Decision)
3. What else did we consider? (Alternatives in Context)
4. What are the implications? (Consequences)

Step 5: File & Update Index

Filing:

1. Save ADR: docs/adr/ADR-{NNN}-{slug}.md
2. Update index: Add entry to docs/adr/README.md
3. Link from specs: Reference ADR from SPEC-1 or other specs (if applicable)
4. Link related ADRs: Update superseded ADRs or related decisions

Example index entry:

- [ADR-015: DuckDB External Parquet for Large Marts](ADR-015-duckdb-external-parquet.md) - **Accepted** (2024-11-08)

Superseding previous ADRs:

When ADR supersedes an older decision:

1. Update old ADR status: Change to "Superseded by ADR-{NNN}"
2. Reference in new ADR: Add "Supersedes: ADR-{XXX}" to frontmatter
3. Explain why: In Context section, explain why original decision no longer applies

ADR Lifecycle

ADRs progress through statuses:

1. Draft - Initial proposal, under discussion
2. Accepted - Decision approved and being implemented
3. Superseded - Replaced by newer ADR (include ADR number)
4. Deprecated - No longer relevant (explain why)

Update status as decisions evolve:

• Draft → Accepted when implementation begins
• Accepted → Superseded when replaced by newer decision
• Accepted → Deprecated if approach is abandoned (with explanation)

Best Practices

Be Specific

Bad: "Use DuckDB for better performance" Good: "Use DuckDB with External Parquet materialization for mart models >1M rows to reduce dev.duckdb file size and improve query performance via columnar scans"

Document "Why Not" for Rejected Alternatives

Don't just state the decision - explain why alternatives were rejected:

Example:

Alternative: PostgreSQL

• ✅ Mature, widely used, excellent ecosystem
• ❌ Requires separate server process (complexity for local dev)
• ❌ No native Parquet support (needs external tools)
• Verdict: Rejected due to local development complexity

Include Concrete Examples

When possible, show actual code/SQL/schemas:

-- dim_player_id_xref (crosswalk seed)
CREATE TABLE dim_player_id_xref (
    player_id VARCHAR PRIMARY KEY,  -- mfl_id (canonical)
    gsis_id VARCHAR,                -- NFLverse
    sleeper_id INTEGER,             -- Sleeper
    ...
)

Link Liberally

Reference:

• Related ADRs (supersedes, complements, conflicts)
• Specs (SPEC-1, feature specs)
• External resources (documentation, blog posts, papers)
• Code locations (when decision manifests in specific files)

Keep Them Immutable (Mostly)

Once accepted, ADRs should rarely change. If decision evolves:

• Small clarifications: Edit in place (note date of edit)
• Major changes: Create new ADR that supersedes the old one

Integration with Other Skills

• strategic-planner: Specs reference ADRs for architectural decisions
• sprint-planner: Sprint tasks implement decisions from ADRs
• dbt-model-builder: Model designs follow patterns established in ADRs (e.g., ADR-009: 2×2 model)

Workflow:

1. strategic-planner creates SPEC-1 (high-level architecture)
2. adr-creator documents key technical decisions from spec
3. sprint-planner breaks ADR implementations into tasks
4. Implementation references ADRs for guidance

Resources

assets/

• adr_template.md - Complete ADR template with all sections

references/

• example_adr.md - Real ADR from project (ADR-010: mfl_id canonical identity)

scripts/

• get_next_adr_number.py - Returns next available ADR number
• create_adr.py - Interactive ADR creation helper (use this to create new ADRs)

Common Patterns

Data Model ADRs

Pattern: Dimensional modeling decisions (facts, dimensions, grain, SCDs)

Examples:

• ADR-007: Separate fact tables per measure type
• ADR-009: 2×2 stat model (actuals vs projections, real-world vs fantasy)
• ADR-010: mfl_id canonical player identity
• ADR-014: Pick identity resolution

Typical structure:

• Context: Data model requirements, query patterns
• Decision: Specific schema design with SQL examples
• Consequences: Query complexity, join performance, storage

Infrastructure ADRs

Pattern: Tooling and platform choices

Examples:

• ADR-004: GitHub Actions for Sheets sync
• ADR-006: GCS for cloud storage

Typical structure:

• Context: Infrastructure requirements, constraints
• Decision: Tool selection with configuration details
• Consequences: Cost, maintenance, learning curve