Data Models
===========

The platform uses PostgreSQL with SQLAlchemy ORM. All models use UUID primary
keys and timestamp tracking.

Entity Relationship Diagram
----------------------------

.. code-block:: text

   ┌──────────┐       ┌──────────────┐       ┌───────────────────┐
   │   User   │◀──────│ ProjectUser  │──────▶│     Project        │
   │          │  1..N │              │ N..1  │                    │
   │ • id     │       │ • role       │       │ • name             │
   │ • name   │       │ • status     │       │ • domain           │
   └──────────┘       └──────────────┘       │ • inviteCode       │
                                              │ • currentPhaseId   │
                                              └────────┬───────────┘
                                                       │ 1..N
                                              ┌────────▼───────────┐
                                              │ TemplateInstance    │
                                              │                    │
                                              │ • templateCode     │
                                              │ • instanceName     │
                                              │ • status           │
                                              │ • completionPct    │
                                              └────────┬───────────┘
                                                       │ 1..1
                            ┌───────────────────────────┼──────────────────┐
                            │                           │                  │
                   ┌────────▼────────┐     ┌────────────▼──┐   ┌──────────▼──────┐
                   │ StakeholderCard │     │  ProjectCard   │   │ EcosystemMatrix │
                   │ (Phase 1)       │     │  (Phase 1)     │   │ (Phase 1)       │
                   └─────────────────┘     └────────────────┘   └─────────────────┘

Core Models
------------

User
~~~~~

.. list-table::
   :header-rows: 1

   * - Field
     - Type
     - Description
   * - id
     - UUID
     - Primary key
   * - name
     - String
     - Display name (from JWT)
   * - createdAt
     - DateTime
     - First seen timestamp

Project
~~~~~~~~

.. list-table::
   :header-rows: 1

   * - Field
     - Type
     - Description
   * - id
     - UUID
     - Primary key
   * - author
     - String
     - Creator user ID
   * - name
     - String
     - Project name
   * - slug
     - String
     - URL-friendly identifier
   * - description
     - String
     - Optional description
   * - currentPhaseId
     - String
     - Active phase (1-4)
   * - status
     - String
     - draft / active / completed
   * - objectives
     - String
     - Comma-separated goals
   * - domain
     - String
     - climate, water, energy, etc.
   * - organization
     - String
     - Lead organization
   * - expertise
     - String
     - Required expertise
   * - serviceIdea
     - String
     - The service being designed
   * - inviteCode
     - String
     - 8-char alphanumeric (permanent)
   * - progress_summary
     - String
     - AI-generated progress overview

ProjectUser (Membership)
~~~~~~~~~~~~~~~~~~~~~~~~~~

.. list-table::
   :header-rows: 1

   * - Field
     - Type
     - Description
   * - projectId
     - FK → Project
     - Associated project
   * - userId
     - FK → User
     - Associated user
   * - role
     - Enum
     - ADMIN / MEMBER
   * - status
     - Enum
     - PENDING / ACCEPTED / REJECTED
   * - threads
     - List[String]
     - User's chat thread IDs

TemplateInstance
~~~~~~~~~~~~~~~~~

.. list-table::
   :header-rows: 1

   * - Field
     - Type
     - Description
   * - id
     - UUID
     - Primary key
   * - projectId
     - FK → Project
     - Parent project
   * - templateCode
     - String
     - Template type (e.g., "1.0.1")
   * - instanceName
     - String
     - User-given name
   * - instanceNumber
     - Integer
     - Sequence number
   * - status
     - String
     - not_started / in_progress / completed
   * - completionPercentage
     - Integer
     - 0–100
   * - updatedBy
     - String
     - Last editor user ID
   * - updatedByName
     - String
     - Last editor display name

Phase-Specific Models
----------------------

Phase 1 — Stakeholder ID Card
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Captures full stakeholder profile:

- **formalInfo:** name, type, location, website, logo, user/pilot potential
- **comprehensiveInfo:** activities, ongoing projects, DestinE uses, contacts
- **assessment:** 8-dimension radar (0–5 scale per dimension + observations)
- **contactInfo:** email, phone, address, contact person

Phase 1 — Ecosystem Matrix
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Singleton per project. Maps stakeholder × project connections:

- **connections:** list of ``{stakeholder_name, project_name, role, contribution}``
- **validationMetadata:** timestamp, version

Phase 2 — DIV (Data-Information-Value)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

- **needsIdentification:** User needs mapping
- **userJourneyMapping:** Interaction flows
- **integrationMap:** Integration points

Phase 3 — User Research Templates
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

- **UserProfile** — Personas and needs
- **UserJourney** — Interaction flows
- **UserNeedsExpansion** — Pain point deep dives
- **IntegrationChallenges** — Adoption obstacles
- **UserFeedbackForm** — Structured feedback
- **FacilitatorCaptureSheet** — Workshop notes

Phase 4 — Outcomes Templates
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

- **OutcomesDiagram** — Actions, projects, time horizons, capacity building
- **SustainabilityCanvas** — 6 dimensions: value proposition, user value,
  user relationship, technical solution, workflow, economic equation

Initial Diagnosis
~~~~~~~~~~~~~~~~~~

.. list-table::
   :header-rows: 1

   * - Field
     - Type
     - Description
   * - id
     - UUID
     - Primary key
   * - responses
     - JSONB
     - 12 question-answer pairs
   * - mode
     - String
     - flash / project
   * - projectId
     - FK
     - If project mode
   * - llmSynthesis
     - String
     - AI-generated analysis
   * - recommendations
     - String
     - AI suggestions