Data Model¶

The Tech Strategy Tool's domain model is a hierarchy of strategy entities managed through an event-sourced processor. This page describes the entity relationships, storage model, and how the in-memory document relates to persistent storage.

Entity Hierarchy¶

graph TD
    S[Strategy Document] --> T1[Team]
    S --> T2[Team]
    T1 --> G1[Group]
    T1 --> G2[Group]
    T1 --> P1[Principle]
    T1 --> P2[Principle]
    T1 --> O1[Objective]
    T1 --> O2[Objective]
    O1 --> I1[Initiative]
    O1 --> I2[Initiative]
    O2 --> I3[Initiative]
    O1 -.->|references| P1
    O2 -.->|references| P1
    O2 -.->|references| P2
    O1 -.->|assigned to| G1
    O2 -.->|assigned to| G2

Entity Relationships¶

erDiagram
    STRATEGY ||--o{ TEAM : "contains (ordered)"
    TEAM ||--o{ PRINCIPLE : "owns (ordered)"
    TEAM ||--o{ GROUP : "owns (ordered)"
    TEAM ||--o{ OBJECTIVE : "owns (ordered)"
    GROUP ||--o{ OBJECTIVE : "organizes (optional)"
    OBJECTIVE }o--o{ PRINCIPLE : "references"
    OBJECTIVE ||--o{ INITIATIVE : "contains (ordered)"

    TEAM {
        guid id
        string name
        string color
    }
    PRINCIPLE {
        guid id
        string title
        string description
    }
    GROUP {
        guid id
        string name
        string description
    }
    OBJECTIVE {
        guid id
        string title
        int totalProgress
    }
    INITIATIVE {
        guid id
        string name
        int progress
        string jiraKey
    }

Ownership vs. reference¶

Solid lines in the diagram indicate ownership — the parent entity's deletion cascades to the child. The Objective-to-Principle relationship is a reference — deleting a Principle removes the association but does not delete the Objective.

Relationship	Type	Cascade behavior
Strategy -> Team	Ownership (ordered)	Deleting strategy removes all teams
Team -> Principle	Ownership (ordered)	Deleting team removes all principles
Team -> Group	Ownership (ordered)	Deleting team removes all groups
Team -> Objective	Ownership (ordered)	Deleting team removes all objectives
Objective -> Initiative	Ownership (ordered)	Deleting objective removes all initiatives
Objective -> Principle	Reference (many-to-many)	Deleting principle removes association only
Objective -> Group	Reference (optional)	Deleting group unassigns objectives (they become ungrouped)

Domain Entities¶

Strategy¶

The root document. Contains an ordered list of teams. There is exactly one Strategy instance in the system, held in memory by the event processor.

Team¶

A team represents an engineering group with its own strategy space. Teams are created and managed exclusively in the Admin Site.

Field	Type	Description
Id	Guid	Unique identifier
Name	string	Display name
Color	string	Hex color code (e.g., `#3498db`) — validated on create and update
Principles	List<Principle>	Ordered list of principles
Objectives	List<Objective>	Ordered list of objectives
Groups	List<Group>	Ordered list of groups for organizing objectives

Group¶

A grouping mechanism for objectives within a team. Groups provide visual organization in the Objectives view.

Field	Type	Description
Id	Guid	Unique identifier
Name	string	Display name
Description	string	Optional description

Deleting a group unassigns its objectives (they become ungrouped and display under a default heading) but does not delete them.

Principle¶

A guiding belief or standard that a team follows. Principles represent the "why" behind technology decisions.

Field	Type	Description
Id	Guid	Unique identifier
Title	string	Principle title (supports `highlight` syntax — highlighted text renders in the team color)
Description	string	Detailed description

Cross-team sharing: When assigned to an Objective in a different team, an independent copy is created in the Objective's team. The copy is a separate entity — there is no live link to the source. Each team can edit its copy independently.

Cascade on delete: Deleting a principle removes it from the PrincipleIds list of all objectives that reference it in the same team.

Objective¶

A measurable goal aligned to one or more principles. Objectives track progress through their initiatives.

Field	Type	Description
Id	Guid	Unique identifier
Title	string	Objective title
Description	string	Detailed description
GroupId	Guid?	Optional group assignment
PrincipleIds	List<Guid>	Linked principles (many-to-many by reference)
Initiatives	List<Initiative>	Ordered list of initiatives

Total progress: Computed as the arithmetic mean of all child initiative progress values, rounded to the nearest integer. Returns 0% if the objective has no initiatives.

Principle linking: A principle can be associated with an objective only once — duplicate assignment is rejected.

Initiative¶

A concrete project or work item that drives progress on an objective.

Field	Type	Description
Id	Guid	Unique identifier
Name	string	Initiative name (plain text, no highlight syntax)
Progress	int	Completion percentage (0-100)
JiraKey	string?	Optional Jira issue key

Deleting an initiative immediately recalculates the parent objective's total progress.

Entity Identity¶

All entity IDs are globally unique GUIDs. The processor locates entities by ID alone without needing to know the parent hierarchy. This simplifies lookups for events like update_principle_title where only the principle ID is needed.

Ordering¶

All collections use List<T>, which preserves insertion order and supports insert-at-index operations:

Principles within their Team
Groups within their Team
Objectives within their Group (or the ungrouped section)
Initiatives within their Objective

Ordering is modified via reorder events (reorder_objective, reorder_initiative, reorder_group) that specify a target index. The UI exposes this through drag-and-drop.

Storage Model¶

The domain model exists in two forms:

In-memory document¶

The EventProcessor holds the current Strategy document in memory. This is the authoritative state for all reads. The document is a plain C# object graph with mutable properties and List<T> collections.

Persistent storage (PostgreSQL)¶

The database stores the building blocks for reconstructing the in-memory document:

Table	Purpose
`events`	Append-only event log (JSONB `data` column)
`checkpoints`	Periodic document snapshots (JSONB `document_json` column)
`history_associations`	Maps events to all entities they affect (for entity history queries)
`users`	User accounts with password hashes
`sessions`	Active login sessions

No entity tables

There are no tables for teams, principles, objectives, etc. The current state is always derived from events. The checkpoints table stores serialized snapshots of the entire strategy document for efficient cold start.

Database indexes¶

Table	Column	Type	Purpose
`events`	`sequence_number`	Unique	Event ordering
`events`	`target_id`	Non-unique	Entity history queries
`checkpoints`	`sequence_number`	Unique	Checkpoint ordering
`history_associations`	`entity_id`	Non-unique	Entity history queries
`history_associations`	`event_sequence`	Non-unique	Event-based lookups and restore cleanup
`users`	`username`	Unique	Login lookups
`sessions`	`user_id`	Non-unique	Per-user session queries

Serialization¶

Events¶

Event data is stored as a JSON dictionary in the data JSONB column. Each event type has its own set of expected keys:

// create_team event data
{ "id": "a1b2c3d4-...", "name": "Platform Team", "color": "#3498db" }

// update_principle_title event data
{ "title": "API-First Design" }

Checkpoints¶

Checkpoints serialize the entire Strategy document as JSON using System.Text.Json. The JSON is stored in a JSONB column, which PostgreSQL normalizes (whitespace is stripped).