Markwell Futures Data Model

Reusable schema for projects, signals, drivers, and scenarios (so far). I built this with input from the Digital Futures group and hope it can serve as a common foundation for interoperable tools. I'd wecome feedback or contributions via my Support page. Generated from live models on 2025-11-08T01:22:12.659176Z.

Download ERD (.mmd) Download JSON schema Download DDL (.sql)

erDiagram
  futures_domain_categories {
    UUID category_id
    UUID project_id FK
    VARCHAR category_name
    UUID parent_id FK
    VARCHAR category_path
  }
  futures_driver_signals {
    INT id
    UUID driver_id FK
    UUID signal_id FK
    VARCHAR short_description
    DATE created_on
    UUID project_id FK
  }
  futures_drivers {
    UUID driver_id
    UUID driver_set_id FK
    INT driver_number
    VARCHAR driver_name
    VARCHAR driver_description
    FLOAT weight_past
    FLOAT push_present
    FLOAT pull_future
    DATE created_on
    DATE last_modified
    UUID project_id FK
  }
  futures_drivers_sets {
    UUID driver_set_id
    VARCHAR driver_set_status
    DATE created_on
    UUID project_id FK
  }
  futures_projects {
    UUID project_id
    VARCHAR domain
    VARCHAR client
    VARCHAR geography
    VARCHAR horizon
    VARCHAR questions
    DATE created_on
    BOOLEAN is_public
  }
  futures_scenario_drivers {
    INT id
    UUID scenario_id FK
    UUID driver_id FK
    VARCHAR driver_value
    DATE created_on
    DATE last_modified
    UUID project_id FK
  }
  futures_scenario_sets {
    UUID scenario_set_id
    VARCHAR scenario_set_status
    DATE created_on
    UUID project_id FK
    VARCHAR scenario_methodology
    UUID driver_set_id FK
  }
  futures_scenarios {
    UUID scenario_id
    UUID scenario_set_id FK
    VARCHAR scenario_logics
    VARCHAR scenario_title
    TEXT scenario_text
    UUID project_id FK
    DATE created_on
    DATE last_modified
    JSON scenario_sections
  }
  futures_signals {
    UUID signal_id
    VARCHAR title
    VARCHAR author
    VARCHAR source_name
    VARCHAR url
    DATE signal_date
    VARCHAR steep
    VARCHAR horizon
    JSON keywords
    VARCHAR signal_type
    VARCHAR description
    VARCHAR change_desc
    INT impact
    INT likelihood
    INT credibility
    INT novelty
    DATE created_on
    UUID project_id FK
    VARCHAR url_normalized
  }
  futures_stakeholders {
    UUID stakeholder_id
    UUID project_id FK
    VARCHAR stakeholder_name
  }
  futures_vision {
    UUID id
    UUID project_id FK
    TEXT statement
    JSON prompts
    VARCHAR status
    DATE updated_at
    DATE created_at
  }
  futures_projects ||--o{ futures_domain_categories : project_id
  futures_domain_categories ||--o{ futures_domain_categories : parent_id
  futures_drivers ||--o{ futures_driver_signals : driver_id
  futures_signals ||--o{ futures_driver_signals : signal_id
  futures_projects ||--o{ futures_driver_signals : project_id
  futures_drivers_sets ||--o{ futures_drivers : driver_set_id
  futures_projects ||--o{ futures_drivers : project_id
  futures_projects ||--o{ futures_drivers_sets : project_id
  futures_scenarios ||--o{ futures_scenario_drivers : scenario_id
  futures_drivers ||--o{ futures_scenario_drivers : driver_id
  futures_projects ||--o{ futures_scenario_drivers : project_id
  futures_projects ||--o{ futures_scenario_sets : project_id
  futures_drivers_sets ||--o{ futures_scenario_sets : driver_set_id
  futures_scenario_sets ||--o{ futures_scenarios : scenario_set_id
  futures_projects ||--o{ futures_scenarios : project_id
  futures_projects ||--o{ futures_signals : project_id
  futures_projects ||--o{ futures_stakeholders : project_id
  futures_projects ||--|| futures_vision : project_id

Tip: The ERD above is rendered client-side with Mermaid. Use the download to embed it in your own docs, or remix the JSON to generate DDL for your database.

Field details

Field	Type	Null	Unique
`category_id` PK	uuid	False	True
`project_id` FK → futures_projects	fk	False	False
`category_name`	varchar	False	False
`parent_id` FK → futures_domain_categories	fk	True	False
`category_path`	varchar	False	False

Field	Type	Null	Unique
`id` PK	integer	False	True
`driver_id` FK → futures_drivers	fk	False	False
`signal_id` FK → futures_signals	fk	False	False
`short_description`	varchar	True	False
`created_on`	date	False	False
`project_id` FK → futures_projects	fk	False	False

Field	Type	Null	Unique
`driver_id` PK	uuid	False	True
`driver_set_id` FK → futures_drivers_sets	fk	False	False
`driver_number`	integer	False	False
`driver_name`	varchar	False	False
`driver_description`	varchar	True	False
`weight_past`	float	True	False
`push_present`	float	True	False
`pull_future`	float	True	False
`created_on`	date	False	False
`last_modified`	date	False	False
`project_id` FK → futures_projects	fk	False	False

Field	Type	Null	Unique	Choices
`driver_set_id` PK	uuid	False	True
`driver_set_status`	varchar	False	False	`Draft, Deprecated, Final`
`created_on`	date	False	False
`project_id` FK → futures_projects	fk	False	False

Field	Type	Null	Unique
`project_id` PK	uuid	False	True
`domain`	varchar	True	False
`client`	varchar	True	False
`geography`	varchar	True	False
`horizon`	varchar	True	False
`questions`	varchar	True	False
`created_on`	date	False	False
`is_public`	boolean	False	False

Field	Type	Null	Unique
`id` PK	integer	False	True
`scenario_id` FK → futures_scenarios	fk	False	False
`driver_id` FK → futures_drivers	fk	False	False
`driver_value`	varchar	True	False
`created_on`	date	False	False
`last_modified`	date	False	False
`project_id` FK → futures_projects	fk	False	False

Field	Type	Null	Unique	Choices
`scenario_set_id` PK	uuid	False	True
`scenario_set_status`	varchar	False	False	`Draft, Deprecated, Final`
`created_on`	date	False	False
`project_id` FK → futures_projects	fk	False	False
`scenario_methodology`	varchar	True	False
`driver_set_id` FK → futures_drivers_sets	fk	False	False

Field	Type	Null	Unique	Hint
`scenario_id` PK	uuid	False	True
`scenario_set_id` FK → futures_scenario_sets	fk	False	False
`scenario_logics`	varchar	True	False
`scenario_title`	varchar	True	False
`scenario_text`	text	True	False
`project_id` FK → futures_projects	fk	False	False
`created_on`	date	False	False
`last_modified`	date	False	False
`scenario_sections`	json	False	False	{ "type": "object", "properties": { "overview": { "type": "string" }, "narrative": { "type": "string" }, "timeline": { "type": "array", "items": { "type": "string" } }, "wildcards": { "type": "array", "items": { "type": "string" } } }, "required": [ "overview", "narrative" ] }

Field	Type	Null	Unique	Choices	Hint
`signal_id` PK	uuid	False	True
`title`	varchar	True	False
`author`	varchar	True	False
`source_name`	varchar	True	False
`url`	varchar	True	False
`signal_date`	date	True	False
`steep`	varchar	True	False	`Social, Technological, Economic, Environmental, Political`
`horizon`	varchar	True	False	`H1, H2, H3`
`keywords`	json	True	False		{ "type": "array", "items": { "type": "string" }, "example": [ "AI", "telehealth", "policy" ] }
`signal_type`	varchar	True	False	`Event, Trend, Plan, Projection, Issue, Obstacle`
`description`	varchar	True	False
`change_desc`	varchar	True	False
`impact`	integer	True	False	`0, 1, 2, 3, 4, 5`
`likelihood`	integer	True	False	`0, 1, 2, 3, 4, 5`
`credibility`	integer	True	False	`0, 1, 2, 3, 4, 5`
`novelty`	integer	True	False	`0, 1, 2, 3, 4, 5`
`created_on`	date	False	False
`project_id` FK → futures_projects	fk	True	False
`url_normalized`	varchar	False	False

Field	Type	Null	Unique
`stakeholder_id` PK	uuid	False	True
`project_id` FK → futures_projects	fk	False	False
`stakeholder_name`	varchar	False	False

Field	Type	Null	Unique	Choices
`id` PK	uuid	False	True
`project_id` FK → futures_projects	fk	False	True
`statement`	text	False	False
`prompts`	json	False	False
`status`	varchar	False	False	`Draft, Final`
`updated_at`	date	False	False
`created_at`	date	False	False