Design

Layered Architecture

The application follows a strict layered architecture to keep concerns separated and to make the business logic independently testable.

flowchart TD
    Web["Web Layer\n(Jinja2 templates + HTMX)"]
    API["API Layer\n(FastAPI routers)"]
    Services["Service Layer\n(Business logic)"]
    Repos["Repository ABCs\n(domain/repositories.py)"]
    Infrastructure["Infrastructure Layer\n(SQLAlchemy repositories)"]
    Domain["Domain Layer\n(models, enums, schemas)"]
    DB[(SQLite)]

    Web --> API
    API --> Services
    Services --> Repos
    Repos --> Infrastructure
    Infrastructure --> DB
    Services --> Domain
    Infrastructure --> Domain

Layers

Layer	Module(s)	Responsibility
Domain	domain/	ORM models, Pydantic schemas, enums, repository ABCs
Infrastructure	infrastructure/	Concrete SQLAlchemy repositories, database engine
Services	services/	All business logic; accepts repository ABCs (testable without DB)
API	api/	FastAPI routers; parse HTTP requests, call services, return responses
Web	web/	Jinja2 templates rendered server-side; HTMX for partial updates

Repository Pattern

All database access is hidden behind abstract interfaces defined in domain/repositories.py. Services depend on the interfaces, not the SQLAlchemy implementations. This makes unit tests straightforward — pass in an in-memory SQLite session or a mock.

class AbstractIssueRepository(AbstractRepository[Issue]):
    @abstractmethod
    def list_filtered(self, params: IssueListParams) -> tuple[list[Issue], int]: ...
    @abstractmethod
    def search(self, query: str) -> list[Issue]: ...

Concrete implementations live in infrastructure/repositories.py and are injected via FastAPI's dependency-injection system (api/dependencies.py).

Factory Method — Import Parsers

The import service uses the Factory Method pattern to select the correct spreadsheet parser at runtime.

class ParserFactory:
    @staticmethod
    def create(path: Path) -> SpreadsheetParser:
        if path.suffix.lower() == ".csv":
            return CSVParser()
        if path.suffix.lower() in {".xlsx", ".xls"}:
            return XLSXParser()
        raise ValueError(f"Unsupported extension: {path.suffix}")

Both CSVParser and XLSXParser implement SpreadsheetParser.parse() returning (headers, rows).

Strategy Pattern — Export

The export service uses the Strategy pattern so that new export formats can be added without modifying the ExportService class.

class ExportService:
    _strategies = {
        "csv":      CSVExportStrategy(),
        "xlsx":     XLSXExportStrategy(),
        "markdown": MarkdownExportStrategy(),
    }

    def export(self, issues: list[Issue], format: str) -> tuple[bytes, str]:
        return self._strategies[format].export(issues)

Data Model

erDiagram
    ISSUE {
        int id PK
        string title
        string category
        string status
        string priority
        string issue_type
        text description
        text example
        text comment
        text markdown_notes
        string source_file
        string source_sheet
        int source_row
        datetime created_at
        datetime updated_at
        datetime resolved_at
    }
    SOURCE_RECORD {
        int id PK
        int issue_id FK
        string source_file
        string source_sheet
        int source_row
        text raw_values_json
    }
    LINK {
        int id PK
        int issue_id FK
        string url
        string label
        string link_type
    }
    ATTACHMENT {
        int id PK
        int issue_id FK
        string filename
        string storage_path
        string mime_type
        string caption
        datetime created_at
    }
    DECISION {
        int id PK
        int issue_id FK
        text decision
        text rationale
        datetime decided_at
    }
    ACTION_ITEM {
        int id PK
        int issue_id FK
        string title
        string owner
        string status
        date due_date
    }

    ISSUE ||--o| SOURCE_RECORD : "has"
    ISSUE ||--o{ LINK : "has"
    ISSUE ||--o{ ATTACHMENT : "has"
    ISSUE ||--o{ DECISION : "has"
    ISSUE ||--o{ ACTION_ITEM : "has"

Issue Lifecycle

stateDiagram-v2
    [*] --> Imported : import
    Imported --> Triaged : triage
    Triaged --> InReview : start review
    InReview --> Blocked : block
    Blocked --> InReview : unblock
    InReview --> Resolved : resolve
    Resolved --> Closed : close
    Closed --> Reopened : reopen
    Reopened --> InReview : restart review

Technology Choices

Component	Technology	Rationale
Web framework	FastAPI	Modern, async, great DX, automatic OpenAPI docs
ORM	SQLAlchemy 2.0	Mature, flexible, excellent SQLite support
Database	SQLite	Zero-infrastructure, file-based, sufficient for single-user
Templates	Jinja2 + HTMX	Server-side rendering with minimal JS; no build step
CSS framework	Pico CSS	Classless semantic HTML; minimal customisation needed
Config	pydantic-settings	Type-safe, supports .env and env vars
Schemas	Pydantic v2	Fast validation, good ORM integration
XLSX	openpyxl	Pure Python, no native dependencies
CLI	Click	Ergonomic, well-documented
Tests	pytest + httpx	Async-compatible, TestClient for integration tests