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 2026-04-10T01:38:26.112134Z.

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_driver_trends {
    INT id
    UUID driver_id FK
    UUID trend_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
    VARCHAR source_type
    VECTORFIELD embedding
  }
  futures_stakeholders {
    UUID stakeholder_id
    UUID project_id FK
    VARCHAR stakeholder_name
  }
  futures_digestuploadchunk {
    INT id
    UUID task_id FK
    INT chunk_index
    BINARYFIELD data
  }
  futures_vision {
    UUID id
    UUID project_id FK
    TEXT statement
    JSON prompts
    VARCHAR status
    DATE updated_at
    DATE created_at
  }
  futures_artifact_sets {
    UUID artifact_set_id
    VARCHAR artifact_set_status
    DATE created_on
    UUID project_id FK
    UUID scenario_set_id FK
  }
  futures_steep_trend {
    UUID trend_id
    VARCHAR steep
    VARCHAR topic
    VARCHAR name
    TEXT summary
    TEXT detail
    VARCHAR source_label
    VARCHAR source_url
    VARCHAR status
    DATE last_synthesized_at
    VECTORFIELD embedding
    DATE created_on
    DATE updated_on
  }
  futures_steep_trend_evidence {
    UUID evidence_id
    UUID trend_id FK
    UUID signal_id FK
    UUID news_item_id FK
    VARCHAR source_label
    VARCHAR source_url
    BOOLEAN is_contrary
    TEXT relevance_note
    DATE added_at
    DATE expires_at
  }
  futures_trend_recommendation {
    UUID rec_id
    UUID task_id FK
    VARCHAR kind
    VARCHAR status
    TEXT justification
    UUID target_trend_id FK
    JSON proposed_data
    DATE created_at
    DATE reviewed_at
    UUID reviewed_by_id FK
    VARCHAR reviewer_note
  }
  futures_trends_config {
    INT id
    VARCHAR access_level
    DATE discovery_processed_through
    DATE signals_curated_through_date
  }
  futures_artifacts {
    UUID artifact_id
    UUID artifact_set_id FK
    UUID scenario_id FK
    UUID project_id FK
    VARCHAR title
    VARCHAR artifact_type
    VARCHAR who_uses_it
    VARCHAR significance
    VARCHAR relationship_to_scenario
    IMAGEFIELD image
    DATE created_on
    DATE last_modified
  }
  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 ||--o{ futures_driver_trends : driver_id
  futures_steep_trend ||--o{ futures_driver_trends : trend_id
  futures_projects ||--o{ futures_driver_trends : 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_apptask ||--o{ futures_digestuploadchunk : task_id
  futures_projects ||--|| futures_vision : project_id
  futures_projects ||--o{ futures_artifact_sets : project_id
  futures_scenario_sets ||--o{ futures_artifact_sets : scenario_set_id
  futures_steep_trend ||--o{ futures_steep_trend_evidence : trend_id
  futures_signals ||--o{ futures_steep_trend_evidence : signal_id
  futures_newsitem ||--o{ futures_steep_trend_evidence : news_item_id
  futures_apptask ||--o{ futures_trend_recommendation : task_id
  futures_steep_trend ||--o{ futures_trend_recommendation : target_trend_id
  users_user ||--o{ futures_trend_recommendation : reviewed_by_id
  futures_artifact_sets ||--o{ futures_artifacts : artifact_set_id
  futures_scenarios ||--o{ futures_artifacts : scenario_id
  futures_projects ||--o{ futures_artifacts : 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
`id` PK	integer	False	True
`driver_id` FK → futures_drivers	fk	False	False
`trend_id` FK → futures_steep_trend	fk	True	False
`short_description`	varchar	False	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
`source_type`	varchar	True	False	`manual, link, gdelt, rss, import`
`embedding`	VectorField	True	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
`id` PK	integer	False	True
`task_id` FK → futures_apptask	fk	False	False
`chunk_index`	integer	False	False
`data`	BinaryField	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

Field	Type	Null	Unique	Choices
`artifact_set_id` PK	uuid	False	True
`artifact_set_status`	varchar	False	False	`Draft, Deprecated, Final`
`created_on`	date	False	False
`project_id` FK → futures_projects	fk	False	False
`scenario_set_id` FK → futures_scenario_sets	fk	False	False

Field	Type	Null	Unique	Choices
`trend_id` PK	uuid	False	True
`steep`	varchar	False	False	`Social, Technological, Economic, Environmental, Political`
`topic`	varchar	False	False
`name`	varchar	False	False
`summary`	text	False	False
`detail`	text	False	False
`source_label`	varchar	False	False
`source_url`	varchar	False	False
`status`	varchar	False	False	`active, watching, archived`
`last_synthesized_at`	date	True	False
`embedding`	VectorField	True	False
`created_on`	date	False	False
`updated_on`	date	False	False

Field	Type	Null	Unique
`evidence_id` PK	uuid	False	True
`trend_id` FK → futures_steep_trend	fk	False	False
`signal_id` FK → futures_signals	fk	True	False
`news_item_id` FK → futures_newsitem	fk	True	False
`source_label`	varchar	False	False
`source_url`	varchar	False	False
`is_contrary`	boolean	False	False
`relevance_note`	text	False	False
`added_at`	date	False	False
`expires_at`	date	False	False

Field	Type	Null	Unique	Choices
`rec_id` PK	uuid	False	True
`task_id` FK → futures_apptask	fk	False	False
`kind`	varchar	False	False	`add, update, archive, change_status, merge, split, attach_evidence, flag_contrary, consolidate_topics, flag_weak_evidence`
`status`	varchar	False	False	`pending, approved, denied`
`justification`	text	False	False
`target_trend_id` FK → futures_steep_trend	fk	True	False
`proposed_data`	json	False	False
`created_at`	date	False	False
`reviewed_at`	date	True	False
`reviewed_by_id` FK → users_user	fk	True	False
`reviewer_note`	varchar	False	False

Field	Type	Null	Unique	Choices
`id` PK	integer	False	True
`access_level`	varchar	False	False	`staff, paying, members`
`discovery_processed_through`	date	True	False
`signals_curated_through_date`	date	True	False

Field	Type	Null	Unique
`artifact_id` PK	uuid	False	True
`artifact_set_id` FK → futures_artifact_sets	fk	False	False
`scenario_id` FK → futures_scenarios	fk	False	False
`project_id` FK → futures_projects	fk	False	False
`title`	varchar	True	False
`artifact_type`	varchar	True	False
`who_uses_it`	varchar	True	False
`significance`	varchar	True	False
`relationship_to_scenario`	varchar	True	False
`image`	ImageField	True	False
`created_on`	date	False	False
`last_modified`	date	False	False