---

name: Advanced Patterns description: SCD Type 2, Closure Table, Shared Family Budget patterns version: 3.0.0 author: Family Budget Team tags: [scd-type-2, closure-table, shared-budget, hierarchy, versioning] dependencies: [db-management, api-development] architecture_refs:

• $ref: ../../docs/architecture/database/history.yaml
• $ref: ../../docs/architecture/database/hierarchy.yaml
• $ref: ../../docs/architecture/guides/change-checklist.yaml#/checklists/add_history_tracking

---

Advanced Patterns Skill

Реализация продвинутых архитектурных паттернов: SCD Type 2, Closure Table, Shared Family Budget.

When to Use

• Добавить SCD Type 2 versioning к dimension таблице
• Создать Closure Table для иерархии
• Реализовать Shared Family Budget endpoint
• Обновить History tracking

Architecture Context

References:

• History Tables: $ref
• Hierarchy: $ref
• Change Checklist: $ref

Key Patterns:

SCD Type 2 (Slowly Changing Dimension Type 2)

Purpose: Track full history of dimension changes

Current Implementation: SCD Type 1 (main tables) + SCD Type 2 (history tables)

Main Tables (SCD Type 1 - In-place updates):

• Article, User, FinancialCenter, CostCenter - NO versioning fields
• Updates modify existing row directly
• Stable PK for fact table FK references

History Tables (SCD Type 2 - Full versioning):

• ArticleHistory, UserHistory, etc. - ALL changes tracked
• Fields: is_current, valid_from, valid_to, change_type
• Audit trail with complete snapshot of each version

Why This Hybrid?

• Simple queries on main tables (no is_current filter needed)
• Complete audit history in separate tables
• Stable PKs for fact table relationships

Closure Table

Purpose: O(1) hierarchical queries without recursion

Table Structure:

CREATE TABLE t_d_article_hierarchy (
    ancestor_id INT,      -- Parent (or self)
    descendant_id INT,    -- Child (or self)
    depth INT,            -- 0 = self, 1 = direct child, 2+ = nested
    PRIMARY KEY (ancestor_id, descendant_id)
);

Queries:

• Get all descendants: WHERE ancestor_id = X
• Get all ancestors: WHERE descendant_id = X
• Get direct children: WHERE ancestor_id = X AND depth = 1

Shared Family Budget

Purpose: 2-5 family members see ALL transactions (full transparency)

Rules:

• Fact tables: NO user_id filtering (all see all)
• Dimension tables: Admin-only CREATE/UPDATE/DELETE, all can READ
• user_id: Audit trail only (who created/modified)

Reference: _shared/validation-logic.md#3-shared-budget-model-consistency

Commands

Command: add-scd2-history

Usage:

Добавь SCD Type 2 history tracking к модели <ModelName>.

What It Does:

1. Create History table model with ALL fields from main table
2. Add is_current, valid_from, valid_to, change_type fields
3. Create service functions for history tracking
4. Create Alembic migration for History table

Template Reference:

• templates/scd-type-2-service.py - History service
• examples/article-history.md - Real Article history implementation

Critical: History table MUST have ALL fields from main table!

Reference: _shared/validation-logic.md#2-history-table-field-completeness

Command: add-closure-table

Usage:

Добавь Closure Table для модели <ModelName> hierarchy.

What It Does:

1. Create Hierarchy table (ancestor_id, descendant_id, depth)
2. Create indexes on (ancestor_id, depth), (descendant_id, depth)
3. Initialize self-references (depth=0)
4. Create HierarchyService for CRUD operations

Template Reference:

• templates/closure-table.py - Hierarchy model
• templates/hierarchy-service.py - HierarchyService implementation

Command: implement-shared-budget

Usage:

Реализуй Shared Budget pattern для endpoint <endpoint-name>.

What It Does:

1. Remove user_id filtering from fact queries
2. Add admin checks for dimension CREATE/UPDATE/DELETE
3. Keep user_id for audit trail only

Template Reference:

• templates/shared-budget-endpoint.py - Shared Budget pattern

Validation Checklist

SCD Type 2 History

• History table has ALL fields from main table
• NO NULL in NOT NULL columns
• change_type set (CREATE/UPDATE/DELETE)
• valid_from, valid_to, is_current set correctly
• Service layer updates history on changes

Closure Table

• Hierarchy table created (ancestor_id, descendant_id, depth)
• Indexes on (ancestor_id, depth), (descendant_id, depth), (depth)
• Self-references initialized (depth=0)
• HierarchyService handles updates
• NO direct SQL updates to closure table

Shared Budget

• Fact tables: NO user_id filtering
• Dimension tables: Admin-only CREATE/UPDATE/DELETE
• user_id used for audit trail only

Common Mistakes

Forgot History field:

• Symptom: IntegrityError: null value in column "record_type"
• Fix: Copy ALL fields from main table to History table
• Reference: _shared/validation-logic.md#2

User_id filtering in Shared Budget:

• Symptom: Users can't see family transactions
• Fix: Remove .where(BudgetFact.user_id == current_user.id)
• Reference: _shared/validation-logic.md#3

Direct UPDATE on dimension (instead of history):

• Symptom: No history record created, audit trail broken
• Fix: Use service layer to create history records
• Reference: _shared/validation-logic.md#5

Related Skills

• db-management: Create models and migrations
• api-development: Implement endpoints with patterns

Quick Links

• History Tables Architecture: $ref
• Hierarchy Architecture: $ref
• Article Example: backend/app/models/article.py, backend/app/models/article_history.py