User profile sectors and other content

[underbluewaters]

Basic profiles

I've added a basic user profile that incorporates standard openid info that is coming from our authentication provider, as well as some fields carried over from the SeaSketch legacy db. So far it looks like this:

CREATE TABLE public.user_profiles (
    id integer NOT NULL,
    user_id integer NOT NULL,
    fullname text,
    nickname text,
    picture text,
    email public.email,
    affiliations text,
    bio text,
    CONSTRAINT user_profiles_picture_check CHECK ((picture ~* 'https?:\/\/(www\.)?[-a-zA-Z0-9@:%._\+~#=]{2,255}\.[a-z]{2,9}\y([-a-zA-Z0-9@:%_\+.~#?&//=]*)$'::text))
);

https://github.com/seasketch/next/blob/master/packages/db/migrations/committed/000004.sql

I'm looking for some feedback on whether this is adequate. Keep in mind how PII will have to be treated to comply with GDPR and other similar regulations. All of this information is optional.

Sectors

We've briefly discussed having users categorize themselves by indicating which "sectors" they identify with. Are they a fisher, scientist, policy maker, etc? There are few outstanding questions about how we implement this.

1. Is this list of sectors customizable, or standardized across the SeaSketch platform?
2. If customizable, how? Are we providing defaults? Can project admins remove those defaults, or just add to them?
3. If there are defaults or a standardized list, what is on that list? Who will develop it and on what timeline?

[twelch]

created and last_updated timestamp fields?

[madelinemberger]

@underbluewaters @wmcclin re: sectors. my intuition is that a customizable list might better since there I've seen some variation across the places we work (Maldives sector list is slightly different than Bermuda, for example) and people like to feel like their identity or livelihood is included. However, if it seems like there has been a pretty standard "core" list of sectors across all the projects you guys have done then I think a standard is fine, especially if it simplifies things. Happy to help develop a standard list, or at least a list of defaults - I'll default to whatever timeline you feel is adequate

[wmcclin]

I agree. I think we need to have a customizable list. I would include the following as defaults:

• Aquaculture / Mariculture
• Boating
• Commercial Fishing
• Recreational Fishing
• Recreation
• Conservation
• Shipping
• Swimming / Snorkeling / Diving
• Tourism
• Utilities
• Military / Law Enforcement
• Education & Research

[underbluewaters]

Implementing this gets fairly complex so I'm going to punt on it for now. We'll need a few tables, to hold default sectors, to hold project-specific sectors, triggers to copy the defaults on project creation, and a ui for admins to manage their stuff. Something like:

create table default_user_sectors (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text not null
);

create table project_user_sectors (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  project_id integer NOT NULL UNIQUE REFERENCES projects (id) ON DELETE CASCADE,
  name text not null
);

create table users_project_user_sectors (
  user_id integer NOT NULL UNIQUE REFERENCES users (id) ON DELETE CASCADE,
  project_user_sector_id integer NOT NULL UNIQUE REFERENCES project_user_sectors (id) ON DELETE CASCADE,
);

# store whether a user has selected their sectors, since their choice could be "nothing"
create table users_has_selected_project_user_sectors (
  user_id integer NOT NULL UNIQUE REFERENCES users (id) ON DELETE CASCADE,
  project_id integer NOT NULL UNIQUE REFERENCES projects (id) ON DELETE CASCADE,
  made_choices boolean not null default true
);