Data Models

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

Entity Relationship Diagram

┌──────────┐       ┌──────────────┐       ┌───────────────────┐
│   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

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

Project

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)

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

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

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