# System Architecture Design

> System Architecture Design

- Skill: `lucadominguez/system-architecture-design` (Agent Skill)
- Install (CLI): `npx skillmds@latest add lucadominguez/system-architecture-design`
- Raw SKILL.md: https://api.skillmd.com/api/skills/lucadominguez/system-architecture-design/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Design & Media
- Author: lucadominguez (https://skillmd.com/u/lucadominguez)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/lucadominguez/system-architecture-design

---

# System Architecture Design

## Overview

Design system architectures, database schemas, data taxonomies, and product specifications. The user has strong preferences for how this work is done — follow these rules or expect corrections.

## Core Principles

### 1. TAXONOMY FIRST, DATA SECOND

Never lead with product listings or raw data. When asked to design or research for a system:
- Step 1: Map the categories/taxonomy/entity types
- Step 2: Design the schema/structure
- Step 3: Populate with data

User correction example: "I don't want you to find the products right now I need data sources, the goal is to index all of the data people want."

### 2. VERSION EVERYTHING

Every deliverable gets a version number and a changelog. Use semantic naming:
```
projectname-v1.0-description.md
projectname-v1.3-schema.sql
VERSIONS.md (manifest tracking all versions)
```

The VERSIONS.md manifest tracks:
- Active files (with descriptions)
- Version history (what changed per version)
- Superseded files (kept for reference)
- Next planned version

### 3. ARCHITECTURAL CRITIQUE: ADDRESS POINT BY POINT

When the user provides architectural feedback (numbered concerns, bullet points), respond by addressing each point individually with a concrete design fix. Do NOT:
- Summarize the feedback generically
- Defend the current design
- Merge multiple points into one response

Instead, for each concern: state the problem in one line, then give the fix with specific table names, field names, or architectural changes.

### 4. SCHEMA AS PRIMARY DELIVERABLE

Database schemas are first-class outputs. Deliver:
- Entity relationship diagram (text-based, showing foreign key relationships)
- Complete CREATE TABLE statements (SQL-ready)
- Indexes, triggers, constraints, seed data
- Materialized views for search

Schema design patterns this user prefers:
- Category-specific spec tables (not EAV/sparse tables) — one table per component type, 1:1 with base entity
- Polymorphic reference tables use `item_type` + `item_id` columns, not separate join tables per type
- Field-level provenance tracking via a `field_provenance` table, not row-level `source` columns
- Weighted evidence hierarchies with explicit source type enums
- `JSONB` for flexible requirements/config fields, typed columns for queryable specs
- Materialized views with GIN indexes for full-text search

### 5. DOCUMENT STRUCTURE

Architecture documents should follow this structure:
1. **Version + Changelog** at the top
2. **Entity relationship overview** (text diagram)
3. **Per-section design** with:
   - Problem statement (one line — what's wrong with the naive approach)
   - Solution (the design fix)
   - Concrete example (real data showing how it works)
4. **Schema summary** (table count, categories, what each layer does)

### 6. PILLAR-BASED PRODUCT ARCHITECTURE

When designing platforms with multiple functional areas:
- Define pillars with clear one-line definitions
- Separate data layer from presentation layer (e.g., Compare is a presentation layer over canonical data, not a separate data store)
- AI is a system operator that reads and writes structured tables, not a chatbot beside the product
- Collaboration is first-party structured contributions, not indexed external content

## Pitfalls

- **Leading with data instead of taxonomy.** User wants the structure first, then populate it.
- **Unversioned files.** Always use semantic versioning in filenames.
- **Generic responses to architectural critique.** Address each concern individually with concrete fixes.
- **EAV/sparse table designs.** Use category-specific spec tables, not generic key-value specs.
- **Missing provenance.** Every field should track its source, confidence, and retrieval date.
- **Boolean compatibility.** "Compatible" is a continuum — use scored dimensions with evidence gates.
- **Treating AI as a chatbot.** Define AI behaviors as system operators that read/write structured tables.
- **Duplicating data for comparison views.** Compare is always computed from canonical data, never stored separately.

## Reusable Patterns

The reference file `references/schema-patterns.md` contains 8 reusable database patterns from real architecture sessions:
- Field-level provenance (not row-level source columns)
- Category-specific spec tables (not EAV)
- Scored integration readiness (not boolean compatible)
- Typed search indices with intent classifier
- Polymorphic references (item_type + item_id)
- Build as structured state machine
- Compare as ephemeral presentation layer
- Versioning discipline

Load it with `skill_view(name='system-architecture-design', file_path='references/schema-patterns.md')` when designing data-heavy platforms.