coworkunion/chattyness
SHA256
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions

feat: profiles and /set profile, and id cards

Browse source 
* New functionality to set meta data on businesscards.
* Can develop a user profile.
* Business cards link to user profile.
This commit is contained in:
Evan Carroll 2026-01-25 10:50:10 -06:00
parent cd8dfb94a3
commit 710985638f
35 changed files with 4932 additions and 435 deletions
Download patch file Download diff file Expand all files Collapse all files

52
db/schema/policies/001_rls.sql
View file

@ -337,6 +337,58 @@ CREATE POLICY auth_inventory_view ON auth.inventory
GRANT SELECT, INSERT, UPDATE, DELETE ON auth.inventory TO chattyness_app;
-- auth.user_contacts
ALTER TABLE auth.user_contacts ENABLE ROW LEVEL SECURITY;
-- Full access to own contacts
CREATE POLICY auth_user_contacts_own ON auth.user_contacts
FOR ALL TO chattyness_app
USING (user_id = public.current_user_id())
WITH CHECK (user_id = public.current_user_id());
-- Visibility-based SELECT for other users' contacts (uses parent user's contacts_visibility)
CREATE POLICY auth_user_contacts_view ON auth.user_contacts
FOR SELECT TO chattyness_app
USING (
user_id = public.current_user_id()
OR EXISTS (
SELECT 1 FROM auth.users u WHERE u.id = user_contacts.user_id
AND (
u.contacts_visibility = 'public'
OR (u.contacts_visibility = 'members' AND public.current_user_id() IS NOT NULL)
OR (u.contacts_visibility = 'friends' AND auth.are_friends(u.id, public.current_user_id()))
)
)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON auth.user_contacts TO chattyness_app;
-- auth.user_organizations
ALTER TABLE auth.user_organizations ENABLE ROW LEVEL SECURITY;
-- Full access to own organizations
CREATE POLICY auth_user_organizations_own ON auth.user_organizations
FOR ALL TO chattyness_app
USING (user_id = public.current_user_id())
WITH CHECK (user_id = public.current_user_id());
-- Visibility-based SELECT for other users' organizations (uses parent user's organizations_visibility)
CREATE POLICY auth_user_organizations_view ON auth.user_organizations
FOR SELECT TO chattyness_app
USING (
user_id = public.current_user_id()
OR EXISTS (
SELECT 1 FROM auth.users u WHERE u.id = user_organizations.user_id
AND (
u.organizations_visibility = 'public'
OR (u.organizations_visibility = 'members' AND public.current_user_id() IS NOT NULL)
OR (u.organizations_visibility = 'friends' AND auth.are_friends(u.id, public.current_user_id()))
)
)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON auth.user_organizations TO chattyness_app;
-- auth.avatars
ALTER TABLE auth.avatars ENABLE ROW LEVEL SECURITY;

76
db/schema/tables/020_auth.sql
View file

@ -16,6 +16,7 @@ CREATE TABLE auth.users (
username auth.username NOT NULL,
email auth.email,
phone TEXT, -- TODO: migrate to auth.phone_number domain
password_hash TEXT,
auth_provider auth.auth_provider NOT NULL DEFAULT 'local',
oauth_id TEXT,
@ -24,6 +25,16 @@ CREATE TABLE auth.users (
bio TEXT,
avatar_url public.url,
-- HOSS membership profile fields
name_first TEXT,
name_last TEXT,
summary TEXT, -- Brief one-liner tagline
homepage public.url,
avatar_source auth.avatar_source NOT NULL DEFAULT 'local',
profile_visibility auth.profile_visibility NOT NULL DEFAULT 'public',
contacts_visibility auth.profile_visibility NOT NULL DEFAULT 'members',
organizations_visibility auth.profile_visibility NOT NULL DEFAULT 'members',
-- User preferences for default avatar selection
birthday DATE,
gender_preference auth.gender_preference NOT NULL DEFAULT 'gender_neutral',
@ -127,6 +138,71 @@ CREATE INDEX idx_auth_friendships_friend_a ON auth.friendships (friend_a);
CREATE INDEX idx_auth_friendships_friend_b ON auth.friendships (friend_b);
CREATE INDEX idx_auth_friendships_pending ON auth.friendships (is_accepted) WHERE is_accepted = false;
-- =============================================================================
-- User Contacts (Social/Contact Links)
-- =============================================================================
CREATE TABLE auth.user_contacts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
platform auth.contact_platform NOT NULL,
value TEXT NOT NULL,
label TEXT, -- Optional display label
sort_order SMALLINT NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT uq_auth_user_contacts_platform UNIQUE (user_id, platform, value),
CONSTRAINT chk_auth_user_contacts_value_nonempty CHECK (length(trim(value)) > 0)
);
COMMENT ON TABLE auth.user_contacts IS 'User social/contact platform links';
COMMENT ON COLUMN auth.user_contacts.platform IS 'Type of contact (discord, github, linkedin, etc.)';
COMMENT ON COLUMN auth.user_contacts.value IS 'Platform-specific identifier (handle, URL, phone, etc.)';
COMMENT ON COLUMN auth.user_contacts.label IS 'Optional user-friendly display label';
COMMENT ON COLUMN auth.user_contacts.sort_order IS 'Display order (lower = first)';
CREATE INDEX idx_auth_user_contacts_user ON auth.user_contacts (user_id);
CREATE INDEX idx_auth_user_contacts_platform ON auth.user_contacts (platform);
-- =============================================================================
-- User Organizations (Affiliations)
-- =============================================================================
CREATE TABLE auth.user_organizations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
wikidata_qid public.wikidata_qid, -- Wikidata Q-number for org
name TEXT NOT NULL, -- Display name (may differ from Wikidata)
role TEXT, -- User's role/title
department TEXT, -- Department/division
start_date DATE,
end_date DATE,
is_current BOOLEAN NOT NULL DEFAULT true,
sort_order SMALLINT NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT chk_auth_user_orgs_name_nonempty CHECK (length(trim(name)) > 0),
CONSTRAINT chk_auth_user_orgs_dates CHECK (start_date IS NULL OR end_date IS NULL OR start_date <= end_date)
);
COMMENT ON TABLE auth.user_organizations IS 'User organizational affiliations';
COMMENT ON COLUMN auth.user_organizations.wikidata_qid IS 'Wikidata Q-number for standardized organization lookup';
COMMENT ON COLUMN auth.user_organizations.name IS 'Display name for the organization';
COMMENT ON COLUMN auth.user_organizations.role IS 'User role/title at the organization';
COMMENT ON COLUMN auth.user_organizations.is_current IS 'Whether this is a current affiliation';
CREATE INDEX idx_auth_user_organizations_user ON auth.user_organizations (user_id);
CREATE INDEX idx_auth_user_organizations_wikidata ON auth.user_organizations (wikidata_qid)
WHERE wikidata_qid IS NOT NULL;
CREATE INDEX idx_auth_user_organizations_current ON auth.user_organizations (user_id, is_current)
WHERE is_current = true;
-- =============================================================================
-- Block List
-- =============================================================================

10
db/schema/tables/045_scene.sql
View file

@ -207,6 +207,9 @@ CREATE TABLE scene.loose_props (
server_prop_id UUID REFERENCES server.props(id) ON DELETE CASCADE,
realm_prop_id UUID REFERENCES realm.props(id) ON DELETE CASCADE,
-- Custom prop name (overrides source prop name when set)
prop_name TEXT,
-- Position in scene (PostGIS point, SRID 0)
position public.virtual_point NOT NULL,
@ -223,11 +226,15 @@ CREATE TABLE scene.loose_props (
is_locked BOOLEAN NOT NULL DEFAULT false,
locked_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
-- Public state columns (loose props only have public state)
-- Public state columns
server_state JSONB NOT NULL DEFAULT '{}',
realm_state JSONB NOT NULL DEFAULT '{}',
user_state JSONB NOT NULL DEFAULT '{}',
-- Private state (transferred to picker, not visible while on ground)
-- Used for business card snapshots captured at drop time
server_private_state JSONB NOT NULL DEFAULT '{}',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-- Must reference exactly one source
@ -238,6 +245,7 @@ CREATE TABLE scene.loose_props (
);
COMMENT ON TABLE scene.loose_props IS 'Props dropped in instances that can be picked up';
COMMENT ON COLUMN scene.loose_props.prop_name IS 'Custom prop name overriding the source prop name (used for renamed items like business cards)';
COMMENT ON COLUMN scene.loose_props.position IS 'Location in scene as PostGIS point (SRID 0)';
COMMENT ON COLUMN scene.loose_props.expires_at IS 'When prop auto-decays (NULL = permanent)';
COMMENT ON COLUMN scene.loose_props.is_locked IS 'If true, only moderators can move/scale/pickup this prop';

8
db/schema/triggers/001_updated_at.sql
View file

@ -60,6 +60,14 @@ CREATE TRIGGER trg_auth_active_avatars_updated_at
BEFORE UPDATE ON auth.active_avatars
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
CREATE TRIGGER trg_auth_user_contacts_updated_at
BEFORE UPDATE ON auth.user_contacts
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
CREATE TRIGGER trg_auth_user_organizations_updated_at
BEFORE UPDATE ON auth.user_organizations
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
-- =============================================================================
-- Realm Schema Triggers
-- =============================================================================

57
db/schema/types/001_enums.sql
View file

@ -179,6 +179,63 @@ CREATE TYPE auth.auth_provider AS ENUM (
);
COMMENT ON TYPE auth.auth_provider IS 'Authentication method used for account';
-- Avatar source for profile pictures
CREATE TYPE auth.avatar_source AS ENUM (
'local', -- Uploaded to local server
'discord', -- Discord avatar
'github', -- GitHub avatar
'google_scholar', -- Google Scholar profile picture
'libravatar', -- Libravatar service
'gravatar' -- Gravatar service
);
COMMENT ON TYPE auth.avatar_source IS 'Source of profile avatar image';
-- Contact/social platform identifiers
CREATE TYPE auth.contact_platform AS ENUM (
'discord',
'linkedin',
'facebook',
'twitter',
'instagram',
'threads',
'pinterest',
'youtube',
'spotify',
'substack',
'patreon',
'linktree',
'github',
'gitlab',
'gitea',
'codeberg',
'stackexchange',
'crates_io',
'pause_cpan',
'npm',
'devpost',
'google_scholar',
'huggingface',
'steam',
'amazon_author',
'openstreetmap',
'wikidata',
'wikimedia_commons',
'wikipedia',
'phone',
'email_alt',
'website'
);
COMMENT ON TYPE auth.contact_platform IS 'Social/contact platform identifiers for user profiles';
-- Profile visibility levels
CREATE TYPE auth.profile_visibility AS ENUM (
'public', -- Visible to everyone
'members', -- Visible to logged-in members only
'friends', -- Visible to friends only
'private' -- Visible only to self
);
COMMENT ON TYPE auth.profile_visibility IS 'Visibility level for profile information';
-- =============================================================================
-- Realm Enums
-- =============================================================================

25
db/schema/types/002_domains.sql
View file

@ -58,6 +58,31 @@ CREATE DOMAIN public.asset_path AS TEXT
CHECK (VALUE ~ '^[a-zA-Z0-9/_.-]+$' AND length(VALUE) <= 500);
COMMENT ON DOMAIN public.asset_path IS 'Valid asset storage path';
-- Wikidata QID (Q-number identifier)
CREATE DOMAIN public.wikidata_qid AS TEXT
CHECK (VALUE ~ '^Q[1-9][0-9]*$');
COMMENT ON DOMAIN public.wikidata_qid IS 'Wikidata Q-number identifier (e.g., Q42 for Douglas Adams)';
-- =============================================================================
-- Auth Domains
-- =============================================================================
-- Phone number (RFC 3966 tel: URI format or E.164)
-- Accepts formats: +1234567890, tel:+1-234-567-8901, etc.
CREATE DOMAIN auth.phone_number AS TEXT
CHECK (VALUE ~ '^(\+|tel:\+?)[0-9\-. ()]+$' AND length(VALUE) <= 30);
COMMENT ON DOMAIN auth.phone_number IS 'Phone number in RFC 3966 or E.164 format';
-- Discord snowflake ID (unsigned 64-bit integer as BIGINT)
CREATE DOMAIN auth.discord_id AS BIGINT
CHECK (VALUE > 0);
COMMENT ON DOMAIN auth.discord_id IS 'Discord snowflake identifier (unsigned 64-bit)';
-- Generic social handle (username on external platforms)
CREATE DOMAIN auth.social_handle AS TEXT
CHECK (length(trim(VALUE)) BETWEEN 1 AND 100);
COMMENT ON DOMAIN auth.social_handle IS 'Social platform username/handle (1-100 characters)';
-- =============================================================================
-- PostGIS Spatial Domains (SRID 0 for virtual 2D world)
-- =============================================================================