ADR Creator Skill | Agent Skills

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:

Context - Why the decision was needed
Alternatives - What options were considered (and why rejected)
Decision - What was chosen and how to implement it
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:

Data Model Decisions (e.g., ADR-007: Separate fact tables, ADR-009: 2×2 stat model)
Infrastructure Choices (e.g., ADR-004: GitHub Actions, ADR-006: GCS storage)
Data Quality Policies (e.g., ADR-005: Server-side copy, ADR-008: Idempotency)
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:

List all viable alternatives (aim for 2-4 options)
Research each option:
- Technical feasibility
- Implementation complexity
- Performance implications
- Cost (development time, runtime, maintenance)
- Compatibility with existing architecture
Consult existing ADRs for precedents or conflicts
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:

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.

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)
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:

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

Step 5: File & Update Index

Filing:

Save ADR: docs/adr/ADR-{NNN}-{slug}.md
Update index: Add entry to docs/adr/README.md
Link from specs: Reference ADR from SPEC-1 or other specs (if applicable)
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:

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

ADR Lifecycle

ADRs progress through statuses:

Draft - Initial proposal, under discussion
Accepted - Decision approved and being implemented
Superseded - Replaced by newer ADR (include ADR number)
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:

strategic-planner creates SPEC-1 (high-level architecture)
adr-creator documents key technical decisions from spec
sprint-planner breaks ADR implementations into tasks
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