adr-creator

# 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`

```bash
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:**

```markdown
- [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:

```sql
-- 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