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.