Status: Placeholder: for later development

Since this application involves a database, a separate document is required to outline the database schema, relationships, and data storage details.

Often the discipline underlying RDB schema design and the processes they are intended to support uncovers additional attributes, relations, meanings and processes. we should expect that this part of the design process will add software functional requirements.

Here is a Chat GPT query and reply related to how to structure a taxonomy so as to be able to filter Prospects on VAlues and how to enter those Prospects in such a way that they are semantically zoomable and searchable – we expect both a formal hierarchy and user based tagging could play a role.

I envisage producing various forms of SQL databases and stocking them with value data to allow development of the Infographic and the other processes during prototyping and development…This is one possible starting point although better to think through on paper I feel…

. Core Prospect Registration Flow

When a contributor registers a Prospect (an “idea / proposal / need / project”), the app should guide them through structured steps:

Title (short, clear name).
Description (what it is, why it matters).
Scope (global → local, future → present).
Affected Values (selected from the Schwartz values circle, or your adapted framework).
Impact assessment (draft) (intended beneficiaries, risks, positives vs negatives).
Activity type(s) (the taxonomy we’ll design below).
Tags / keywords (user-generated — extra flexibility).
Attachments (optional images, links, docs, etc.).

2. Activity Type Taxonomy

This is the piece you’re focused on: how to create a hierarchical or faceted list of “activity types” so Prospects can be searched, filtered, and compared.

A good model is to mix hierarchical categories (big umbrellas) with faceted tags (cross-cutting themes). Here’s a draft hierarchy, blending social innovation taxonomies, project management categories, and practical everyday activities:

Level 1 (broad domains)

Social & Community
Education & Knowledge
Health & Wellbeing
Environment & Sustainability
Economy & Work
Governance & Civic Life
Personal & Household

Level 2 (subcategories, examples)

Social & Community

Social enterprise / cooperative
Charity / volunteering
Social improvement project
Community services (transport, housing, food, safety)

Education & Knowledge

School curriculum / lesson plans
Adult learning / skill development
Research & innovation
Public awareness campaigns

Health & Wellbeing

Physical health (exercise, medical services)
Mental health (counselling, stress reduction)
Public health campaigns
Social care / elder care

Environment & Sustainability

Energy efficiency & renewables
Conservation / biodiversity
Pollution reduction (air, water, plastics)
Circular economy / recycling

Economy & Work

Fair work / employment practices
Local enterprise support
Global trade & equity (e.g. “Share Work with Poor Countries”)
Consumer reviews / product transparency

Governance & Civic Life

Policy proposals
Civic dialogue & participation
Norms & ethics setting
Justice & fairness initiatives

Personal & Household

Home improvement / decoration
Family & relationships
Personal needs (dog walker, childcare, errands)
Lifestyle changes (diet, travel, hobbies)

3. Searchability

For others to find Prospects, you’ll need multi-layered discoverability:

Structured filters:
- By Scope (global ↔ local, future ↔ present)
- By Value affected (from Schwartz circle)
- By Activity type (taxonomy above)
Free-text search (full-text index of titles + descriptions + tags).
Cross-linking:
- Prospects can belong to multiple categories.
- Tag clouds or folksonomies grow over time as users add their own terms.
Ranking (by popularity, urgency, impact scores, or recency).

4. Technical Requirements (high-level)

Database schema:
- Prospects table with attributes (title, description, scope, creator, etc.).
- Values table (linked via many-to-many relationship).
- ActivityTypes table (hierarchical: parent_id, child_id).
- Tags table (folksonomy-style, user-generated).
APIs for filtering:
- filterProspects(scope=X, value=Y, activityType=Z, tags=[...]).
UI / UX:
- Circle infographic as entry point.
- Side panel or overlay for filters.
- Search bar for text queries.

✅ So in short:

Hierarchy is best for broad domains → subdomains.
Tags give flexibility for edge cases.
Search & filters combine these so people can find a dog walker, a school lesson plan, or a climate project with equal ease.

Would you like me to sketch a first-pass database schema for Prospect registration and classification (tables, relationships, fields)? That would make the technical side concrete for your computing collaborators.

You said:

yes please do that

ChatGPT said:

Awesome — here’s a clean, production-friendly first-pass schema for Prospect registration and discovery in Value Prospecting. I’ll use PostgreSQL terms (works nicely with JSONB, full-text search, and hierarchies), but the model is portable.

Core entities & relationships (ER sketch)

prospects (the thing users submit)
- ↔ prospect_values ↔ values (Schwartz or your adapted set; many-to-many)
- ↔ prospect_activity_types ↔ activity_types (hierarchical catalogue; many-to-many)
- ↔ prospect_tags ↔ tags (folksonomy; many-to-many)
- ↔ attachments (files/links)
- ↔ stories (dialogue/testimonies; 1-to-many)
- ↔ impacts (positive/negative assessments; 1-to-many)
scopes (time & place lens used for filtering/zoom)
users (submitters, moderators)

1) Lookup tables

`values` (Schwartz values)

CREATE TABLE values (
  id SERIAL PRIMARY KEY,
  code TEXT UNIQUE NOT NULL,          -- e.g., "UNIVERSALISM", "BENEVOLENCE"
  label TEXT NOT NULL,                -- display name
  domain TEXT,                        -- optional: self-transcendence, etc.
  color TEXT                          -- hex for the navigator UI
);

`activity_types` (hierarchical catalogue)

Use an adjacency list + a materialized path for fast filtering.

CREATE TABLE activity_types (
  id SERIAL PRIMARY KEY,
  parent_id INT REFERENCES activity_types(id) ON DELETE SET NULL,
  label TEXT NOT NULL,                -- e.g., "Environment & Sustainability"
  slug TEXT UNIQUE NOT NULL,          -- "environment-sustainability"
  path LTREE,                         -- materialized path (use ltree extension)
  sort_order INT DEFAULT 0
);
-- Keep path in sync via triggers or app logic (e.g., root 'env', child 'env.energy', etc.)
CREATE INDEX idx_activity_types_path ON activity_types USING GIST (path);

`tags` (folksonomy)

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  label CITEXT UNIQUE NOT NULL        -- case-insensitive, user-supplied
);

`scopes` (time & location “lens” presets)

CREATE TABLE scopes (
  id SERIAL PRIMARY KEY,
  label TEXT NOT NULL,                -- e.g., "Global (next 100 years)"
  time_horizon ENUM('far_future','next_100y','next_10y','now') DEFAULT 'now',
  geo_level ENUM('universal','global','national','local','family','personal') NOT NULL,
  country_code CHAR(2),               -- optional ISO-3166
  region TEXT                         -- optional region/area text
);

If ENUMs are too rigid for you, use TEXT + CHECK constraints.

2) Core content

`prospects`

CREATE TABLE prospects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  summary TEXT,                       -- short pitch
  description TEXT NOT NULL,          -- full description (markdown ok)
  scope_id INT REFERENCES scopes(id),
  created_by UUID REFERENCES users(id),
  status ENUM('draft','published','archived') DEFAULT 'published',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  -- discovery aids
  location GEOGRAPHY(Point, 4326),    -- optional geo pin
  ftv tsvector                        -- full-text vector (title+desc+tags)
);
CREATE INDEX idx_prospects_status ON prospects(status);
CREATE INDEX idx_prospects_scope ON prospects(scope_id);
CREATE INDEX idx_prospects_location ON prospects USING GIST (location);
CREATE INDEX idx_prospects_ftv ON prospects USING GIN (ftv);

Keep ftv updated via trigger:

CREATE FUNCTION prospects_ftv_update() RETURNS trigger AS $$
BEGIN
  NEW.ftv :=
    setweight(to_tsvector('english', coalesce(NEW.title,'')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.summary,'')), 'B') ||
    setweight(to_tsvector('english', coalesce(NEW.description,'')), 'C');
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_prospects_ftv BEFORE INSERT OR UPDATE
ON prospects FOR EACH ROW EXECUTE FUNCTION prospects_ftv_update();

M:N links

CREATE TABLE prospect_values (
  prospect_id UUID REFERENCES prospects(id) ON DELETE CASCADE,
  value_id INT REFERENCES values(id) ON DELETE CASCADE,
  weight NUMERIC(4,2) DEFAULT 1.0,    -- optional importance weighting
  PRIMARY KEY (prospect_id, value_id)
);

CREATE TABLE prospect_activity_types (
  prospect_id UUID REFERENCES prospects(id) ON DELETE CASCADE,
  activity_type_id INT REFERENCES activity_types(id) ON DELETE CASCADE,
  PRIMARY KEY (prospect_id, activity_type_id)
);

CREATE TABLE prospect_tags (
  prospect_id UUID REFERENCES prospects(id) ON DELETE CASCADE,
  tag_id INT REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (prospect_id, tag_id)
);

`stories` (dialogue / testimonies; supports semantic zoom “detail”)

CREATE TABLE stories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  prospect_id UUID REFERENCES prospects(id) ON DELETE CASCADE,
  created_by UUID REFERENCES users(id),
  kind ENUM('testimony','conversation','evidence','media') DEFAULT 'testimony',
  body TEXT NOT NULL,                 -- narrative text
  media JSONB,                        -- links to images/audio/video, transcripts
  created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_stories_prospect ON stories(prospect_id);

`impacts` (positives/negatives; for sizing Prospects in the navigator)

CREATE TABLE impacts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  prospect_id UUID REFERENCES prospects(id) ON DELETE CASCADE,
  dimension ENUM('social','environmental','economic','health','cultural') NOT NULL,
  direction ENUM('positive','negative') NOT NULL,
  magnitude NUMERIC(6,2) NOT NULL,    -- normalized score
  confidence NUMERIC(3,2) DEFAULT 0.7,
  rationale TEXT,                     -- why we scored it this way
  created_by UUID REFERENCES users(id),
  created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_impacts_prospect ON impacts(prospect_id);

`attachments`

CREATE TABLE attachments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  prospect_id UUID REFERENCES prospects(id) ON DELETE CASCADE,
  url TEXT NOT NULL,
  kind ENUM('image','document','dataset','link') DEFAULT 'link',
  title TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

3) Users & moderation (minimal)

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email CITEXT UNIQUE NOT NULL,
  display_name TEXT,
  role ENUM('member','moderator','admin') DEFAULT 'member',
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE moderation_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  prospect_id UUID REFERENCES prospects(id) ON DELETE CASCADE,
  moderator_id UUID REFERENCES users(id),
  action ENUM('flag','approve','reject','redact') NOT NULL,
  reason TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

4) Example activity-type hierarchy (seed)

-- Level 1
INSERT INTO activity_types (id, parent_id, label, slug, path) VALUES
(1, NULL, 'Social & Community', 'social-community', 'social'),
(2, NULL, 'Education & Knowledge', 'education-knowledge', 'edu'),
(3, NULL, 'Health & Wellbeing', 'health-wellbeing', 'health'),
(4, NULL, 'Environment & Sustainability', 'environment-sustainability', 'env'),
(5, NULL, 'Economy & Work', 'economy-work', 'econ'),
(6, NULL, 'Governance & Civic Life', 'governance-civic', 'civic'),
(7, NULL, 'Personal & Household', 'personal-household', 'home');

-- Level 2 examples
INSERT INTO activity_types (parent_id, label, slug, path) VALUES
(4, 'Pollution Reduction (plastics, air, water)', 'pollution-reduction', 'env.pollution'),
(4, 'Conservation & Biodiversity', 'conservation-biodiversity', 'env.conservation'),
(4, 'Energy Efficiency & Renewables', 'energy-renewables', 'env.energy'),
(2, 'School Lesson Plans', 'school-lesson-plans', 'edu.school.lessons'),
(2, 'Public Awareness Campaigns', 'public-awareness', 'edu.public-awareness'),
(1, 'Social Enterprise / Cooperative', 'social-enterprise', 'social.enterprise'),
(7, 'Personal Services (dog-walker, childcare)', 'personal-services', 'home.services'),
(5, 'Consumer Product Reviews / Transparency', 'product-reviews', 'econ.consumer.reviews');

With ltree, you can filter all descendants of env by path <@ 'env.*', or just env.pollution.*, etc.

5) Typical read queries

a) Search prospects by scope + value + activity

SELECT p.id, p.title, p.summary
FROM prospects p
JOIN prospect_values pv ON pv.prospect_id = p.id
JOIN values v ON v.id = pv.value_id
JOIN prospect_activity_types pat ON pat.prospect_id = p.id
JOIN activity_types at ON at.id = pat.activity_type_id
WHERE p.status = 'published'
  AND p.scope_id = $1                     -- e.g., "Global (next 100 years)"
  AND v.code IN ('UNIVERSALISM','BENEVOLENCE')
  AND at.path <@ 'env.*'                  -- any environmental subtype
ORDER BY p.created_at DESC
LIMIT 50;

b) Full-text search + filters

SELECT p.id, p.title, ts_rank(p.ftv, plainto_tsquery($q)) AS rank
FROM prospects p
WHERE p.ftv @@ plainto_tsquery($q)
  AND EXISTS (SELECT 1 FROM prospect_tags pt
              JOIN tags t ON t.id = pt.tag_id
              WHERE pt.prospect_id = p.id AND t.label ILIKE ANY($tags))
ORDER BY rank DESC, p.created_at DESC
LIMIT 50;

c) Compute “size” for the Navigator (benefit score)

SELECT prospect_id,
       SUM(CASE WHEN direction='positive' THEN magnitude ELSE -magnitude END) AS net_score
FROM impacts
GROUP BY prospect_id;

6) Prospect registration API (example)

POST /prospects
- body: { title, summary, description, scope_id, values:[{id,weight}], activity_types:[id], tags:[...], location:{lat,lng} }
POST /prospects/{id}/stories
POST /prospects/{id}/impacts
GET /prospects?scope=...&value=...&activity=...&q=...
GET /activity-types?parent=env (for faceted drilldown)
GET /values (to render the Navigator wheel)

7) Notes for semantic zoom

Zoom out: aggregate by activity_types.path depth (env.* vs env.energy), by scope, and by values (domain or individual codes).
Zoom in: show stories (testimonies), attachments, and impacts for the selected Prospect.
Semantic classification: the prospect_activity_types table (many-to-many) + activity_types.path provides the hierarchy needed to “roll up” or “drill down.”

8) Moderation, versioning, i18n (optional next steps)

Versioning: add prospect_revisions (prospect_id, diff, edited_by, created_at).
Language: move title/summary/description to a prospect_i18n table if you need multilingual content early.
Reputation: add lightweight upvotes / flags to help ranking and trust.

TL;DR

Use hierarchical activity types (with ltree) + folksonomy tags for flexible discovery.
Use values (Schwartz) via M:N for empathy-aligned filtering.
Use scopes to bundle time+place lenses.
Store stories and impacts separately to power the Navigator’s size/links and the semantic zoom detail.

If you want, I can export this as a single SQL file (DDL + seed data) you can hand to the student developers, or adapt it to Prisma/TypeORM models if you’re going Node.js.

DSD: Database Schema Design: Development Team document.

. Core Prospect Registration Flow

2. Activity Type Taxonomy

Level 1 (broad domains)

Level 2 (subcategories, examples)

3. Searchability

4. Technical Requirements (high-level)

You said:

ChatGPT said:

Core entities & relationships (ER sketch)

1) Lookup tables

`values` (Schwartz values)

`activity_types` (hierarchical catalogue)

`tags` (folksonomy)

`scopes` (time & location “lens” presets)

2) Core content

`prospects`

M:N links

`stories` (dialogue / testimonies; supports semantic zoom “detail”)

`impacts` (positives/negatives; for sizing Prospects in the navigator)

`attachments`

3) Users & moderation (minimal)

4) Example activity-type hierarchy (seed)

5) Typical read queries

a) Search prospects by scope + value + activity

b) Full-text search + filters

c) Compute “size” for the Navigator (benefit score)

6) Prospect registration API (example)

7) Notes for semantic zoom

8) Moderation, versioning, i18n (optional next steps)

TL;DR

Comments

Leave a Reply Cancel reply

DSD: Database Schema Design: Development Team document.

. Core Prospect Registration Flow

2. Activity Type Taxonomy

Level 1 (broad domains)

Level 2 (subcategories, examples)

3. Searchability

4. Technical Requirements (high-level)

You said:

ChatGPT said:

Core entities & relationships (ER sketch)

1) Lookup tables

values (Schwartz values)

activity_types (hierarchical catalogue)

tags (folksonomy)

scopes (time & location “lens” presets)

2) Core content

prospects

M:N links

stories (dialogue / testimonies; supports semantic zoom “detail”)

impacts (positives/negatives; for sizing Prospects in the navigator)

attachments

3) Users & moderation (minimal)

4) Example activity-type hierarchy (seed)

5) Typical read queries

a) Search prospects by scope + value + activity

b) Full-text search + filters

c) Compute “size” for the Navigator (benefit score)

6) Prospect registration API (example)

7) Notes for semantic zoom

8) Moderation, versioning, i18n (optional next steps)

TL;DR

Comments

Leave a Reply Cancel reply

`values` (Schwartz values)

`activity_types` (hierarchical catalogue)

`tags` (folksonomy)

`scopes` (time & location “lens” presets)

`prospects`

`stories` (dialogue / testimonies; supports semantic zoom “detail”)

`impacts` (positives/negatives; for sizing Prospects in the navigator)

`attachments`