Initial data model draft

interface User {
  id: string
	username: string
  email: string  
	verifiedEmail: boolean
  firstName: string
	lastName: string
	image: string
  services: [string] // Service slug ids
  skills: [string] // Skill slug ids
	onlineAccounts [ // JSON instead of another table
		{
			type: string // account slug, e.g. twitter, github
			url: string
		}
	]
	roleStakepoolOperator: boolean
	roleCommunityAdvisor: boolean
  lookingToCollaborateOnProposals: boolean
	createdAt: string
	updatedAt: string
}

interface Skill {
	id: string
	name: string
}

interface Service {
	id: string
	name: string
}

interface Category {
	id: string
	name: string
}

interface SkillCategory {
	skill: string // Skill slug id
	category: string // Category slug id
}

interface ServiceCategory {
	service: string // Service slug id
	category: string // Category slug id
}

interface Tag {
	id: string
	name: string
}

interface Group {
	id: string
	name: string
}

interface TagGroup {
	group: string
	tag: string
}

interface Project {
	id: string
	name: string
	description: string
	onlineAccounts [ // JSON instead of another table
		{
			type: string
			url: string
		}
	]
	tags: [string] // Tag slug ids
	owner: string // User id
	members: [string] // User ids
	createdAt: string
	updatedAt: string
}

interface Currency {
	code: string
  name: string
	symbol: string
}

enum ProposalStatus {
	PENDING
	FUNDED
	ARCHIVED
}

interface Proposal {
  // Data newly added to proposals to be made available on the API
  id: string
  fundCategoryId: string // id for FundCategory data model
  tags: [string] // Link to Tag slug ids
  currency: string // Currency code such as ADA or USD
	status: string // Enum values such as PENDING, FUNDED, ARCHIVED

  // Data extracted from Ideascale
  title: string
  problemDescription: string
  solutionDescription: string
  relevantExperience: string
  outcomeObjectives: string
  description: string
  url: string
  createdAt: string
  requestedAmount: number
  ideascaleId: number
  ideascaleUrl: string
  ideascale: {
    stage: string
    ideaNumber: number
    author: string
    commentsTotal: number
    annotations: string
    attachmentLinks: string
    requestedAmountText: string
  }
}

interface FundRound {
  id: string
  name: string // e.g. Fund 3
}

interface FundCategory {
	id: string
	fundRoundId: number
	name: string
	fundingAmount: number
	currency: string
}

Static data

To keep some parts simple some data may not be stored in a database for the short term

• Fund dates - Funding dates likely bundled in the site as a JSON file. These may be valuable to add to the API in the future.
• Resources - All the resources data can be added statically to the site. Overtime this may go into a CRM for more people to easily manage depending on the rate of change.

Notes

• Users can create projects. Projects are only visible when they have atleast one linked proposal. Proposals can only be linked to projects by users which are members of that proposal.
• Users can't create proposals. Proposals initially are immutable and copies from Ideascale data. Users can claim proposals created from Ideascale that they are members of.
• Initially all users will be part of the collaborator list. Once it grows there could be an option in the future to allow people to decide whether they show up in that list. Users will be able to mark themselves as available to collaborate or not as part of being within the list.
• Collaborator data is added to the user table. The purpose of not putting this data in a separate collaborators table is there isn't enough value separating the data. It will be more performant than needing to do SQL joins on two tables when loading the collaborator list. This will ideally slow down the need for more tooling such as Redis or Elastic search in the short term. Much of this collaborator data will ideally become more self sovereign over time so this structure should suffice for the scale that the website needs until it can be shifted to a self sovereign identity solution. If the site for whatever reason needed more scale or a different structure then this data can still be easily migrated out to a separate table or as an alternative make use of tools to cache the data for performance.