PGH Dive Labs · Proposed schema v1

The Variables.

Every editable field across the platform, organized as the database structure to build against. Each table lists fields, types, and whether they're member-editable, design/theme, or system-derived.

Member-editableDesign / themeSystem / derivedRelation (FK)

01 / Identity

User & account

The root auth record. One user may hold several roles (diver + instructor + center staff), so role is a join, not a column.

User users

usersauth root

Field	Type	Notes	Source
id	uuid	Primary key	system
email	string · unique	Login + contact	editable
password_hash	string	Never returned to client	system
phone	string	Optional, for SMS reminders	editable
full_name	string	Display name	editable
avatar_url	string	Profile photo	editable
location	string	City / region	editable
primary_role	enum	diver · instructor · center — set at signup	editable
oauth_provider	enum · null	google · apple · email	system
created_at	timestamp	Member since	system

Role grant user_roles

user_rolesmany-to-one → users

Field	Type	Notes	Source
id	uuid	PK	system
user_id	uuid	→ users.id	relation
role	enum	diver · instructor · center_staff · center_owner	system
center_id	uuid · null	→ centers.id (for staff roles)	relation
status	enum	pending · active · suspended	system

02 / Identity

Diver profile

The diver's public/social profile. 1:1 with a user that has the diver role.

diver_profiles1:1 → users

Field	Type	Notes	Source
id / user_id	uuid	→ users.id	relation
handle	string · unique	pghdivelabs.com/@handle	editable
tagline	string(120)	Hero one-liner	editable
bio	text	Long-form bio	editable
cover_image_url	string	Hero background	editable
home_location	string	Neighborhood	editable
highest_cert	enum	OW · AOW · Rescue · DM…	editable
total_dives	int	Derived from dive_logs (or manual seed)	derived
social_links	json[]	{platform, label, url}	editable
visibility	enum	everyone · members · buddies	editable
theme_id	uuid	→ page_themes.id	relation

03 / Identity

Instructor profile

Extends a diver with teaching data. An instructor is also a diver (has a log), so this references the diver profile.

instructor_profiles1:1 → users (instructor role)

Field	Type	Notes	Source
id / user_id	uuid	→ users.id	relation
handle	string · unique	/@instructor	editable
role_line	string	"Independent Instructor"	editable
tagline / bio	string / text	Hero + long bio	editable
cover_image_url / portrait_url	string	Hero + portrait	editable
agencies	string[]	SSI · PADI · TDI…	editable
specialties	string[]	Drysuit · Deep · Rescue…	editable
years_teaching	int		editable
verified	bool	"Verified by PGH Dive Labs"	system
accepting_students	bool	Shows booking buttons	editable
affiliated_center_ids	uuid[]	→ centers.id	relation
highlights	json[]	{year, title, detail}	editable
stats	json	students_certified, pass_rate, rating…	derived
theme_id	uuid	→ page_themes.id	relation

04 / Identity

Dive center

centersowned by users (center_owner)

Field	Type	Notes	Source
id	uuid	PK	system
slug	string · unique	/steel-city-scuba	editable
name / logo_initials	string	Display name + 2–3 char mark	editable
strapline / tagline / bio	string / text	Hero + about copy	editable
cover_image_url	string	Hero background	editable
established_year / center_type	int / enum	full-service · retail · training…	editable
address / phone / email / hours	string / json	Contact card; hours = {day:range}	editable
geo	{lat,lng}	Map pin	editable
agencies / brands_carried	string[]	Affiliations + retail brands	editable
services	json[]	{label, turnaround} for service dept	editable
why_choose	json[]	Selling points {title, body}	editable
staff_ids	uuid[]	→ instructor_profiles	relation
rating / review_count	decimal / int	Aggregated	derived
theme_id	uuid	→ page_themes.id	relation

05 / Customization

Page theme

Exactly what the profile editor writes. One row per profile (diver / instructor / center). This is the "within the walls" customization layer — everything here maps to a control in the editor.

page_themes1:1 → each profile

Field	Type / values	Notes	Source
id	uuid	PK	system
accent	enum	aqua · cobalt · marine · abyss · coral · amber	design
style	enum	editorial · refined · modern · bold	design
cover_image	string · url	Preset key or uploaded URL	design
portrait_image	string · url	Portrait (or logo initials for center)	design
section_order	string[]	Ordered section keys, e.g. ["bio","schedule","highlights","reviews","gallery"]	design
section_visibility	json	{key: bool} — locked core sections forced true	design
field_overrides	json	{name, role, tagline, bio} edited inline in editor	editable
updated_at	timestamp	Last published	system

Maps 1:1 to the editor. accent + style set the CSS token + data-pgh-style; section_order/section_visibility drive the reorder + toggle panel; field_overrides are the inline text controls. Stored client-side today in localStorage['pgh-edit-{role}'].

// page_themes row — JSON shape the editor saves
{
  "accent": "coral",
  "style": "refined",
  "cover_image": "https://…/cover.jpg",
  "portrait_image": "https://…/me.jpg",
  "section_order": ["bio","schedule","highlights","reviews","gallery"],
  "section_visibility": { "reviews": false },
  "field_overrides": { "tagline": "Cold water, warm welcome." }
}

06 / Content

Certifications

Both diving and non-diving (CPR, O2, AED, tech). Shown on profiles & the Cert Wall.

certificationsmany → users

Field	Type	Notes	Source
user_id	uuid	→ users.id	relation
category	enum	diving · safety · technical	editable
name / agency	string	"Advanced Open Water" · SSI	editable
cert_number	string	For verification	editable
issued_date / expires_date	date	Expiry drives "current" badge	editable
card_image_url	string	Uploaded C-card scan	editable
verification_status	enum	pending · verified · expired	system

07 / Content

Equipment

The "My Equipment" locker — gear items with service tracking, shared by divers & instructors.

equipment_itemsmany → users

Field	Type	Notes	Source
user_id	uuid	→ users.id	relation
category	enum	exposure · life_support · propulsion · instruments · accessories	editable
type / make_model	string	"Regulator" · "Scubapro MK19"	editable
notes	string	Config / serial	editable
owned_since	date		editable
last_serviced / service_due	date	Drives service alerts	editable
show_on_profile	bool	Public equipment profile toggle	editable

08 / Content

Dive log

dive_logsmany → users

Field	Type	Notes	Source
user_id / dive_number	uuid / int	Auto-incrementing per diver	relation
site_id / site_name	uuid / string	→ dive_sites (or free text)	editable
date / type	date / enum	quarry · reef · night · training · wreck	editable
max_depth / avg_depth	decimal (m)		editable
run_time / bottom_temp	int / int	minutes / °F	editable
gas / gas_in / gas_out	enum / int	air · EAN32 · bar	editable
weight / exposure / conditions	json	vis, weather, entry, suit	editable
buddy_ids / notes / photos	uuid[] / text / string[]	Buddies, log notes, images	editable
depth_profile	json[]	{t, depth} samples for the chart	editable
shared_to_feed	bool	Posts to social feed	editable

Dive site dive_sites

Shared reference of quarries/reefs with live conditions.

name / region / drive_time	string	"Bainbridge" · OH · 90 min	system
status / vis / temp	enum / int	open · warning · closed — drives the dot color	system
photo_url / geo / permit_required	string / json / bool		system

09 / Content

Classes & sessions

Exactly the fields the New Class wizard collects. A class has many scheduled sessions and many enrollments.

classesby instructor / center

Field	Type	Notes	Source
instructor_id / center_id	uuid	Owner(s)	relation
code / name / agency	string	"DRY" · "Drysuit Diver" · SSI	editable
category	enum	entry · con_ed · specialty · technical · pro	editable
price / deposit	decimal	All-in + deposit due	editable
lectures / confined_dives / open_water_dives	int	The spec counts	editable
elearning / rental_included	bool	Toggles	editable
max_students / ratio	int / string	Capacity, "1:4"	editable
description / prerequisites / includes	text / string[]	Public page copy	editable
listed_public	bool	Directory + bookable	editable
status	enum	draft · open · full · completed	system

Session class_sessions

class_id	uuid	→ classes.id	relation
date / time / location	datetime / string	Each meeting; one row per session	editable
seats_total / seats_taken	int	Drives "3 seats left"	derived

10 / Content

Group trips

tripsby center / instructor

Field	Type	Notes	Source
title / destination	string	"Bonaire 2026"	editable
start_date / end_date / nights	date / int		editable
leader_id	uuid	→ instructor_profiles	relation
itinerary	json[]	{day_label, title, detail}	editable
room_options	json[]	{key, label, price} — double/single/non-diver	editable
deposit / includes / excludes	decimal / string[]		editable
capacity / spots_taken / gallery	int / string[]		derived

11 / Content

Bookings & payments

One record per enrollment (class or trip), linked to a Stripe payment. Powers the roster, the diver's "registered classes," and confirmations.

bookingsdiver ↔ class/trip

Field	Type	Notes	Source
user_id	uuid	The diver	relation
bookable_type / bookable_id	enum / uuid	class · trip → polymorphic	relation
session_id / room_option	uuid / string	Chosen cohort / room	editable
confirmation_code	string	"SCS-2026-0001"	system
status	enum	held · deposit_paid · paid · waitlist · cancelled	system
amount_total / amount_paid	decimal	Deposit vs balance	system
stripe_payment_intent	string	→ Stripe	system
progress / paperwork	json	Roster progress %, waiver/medical flags	system

Payments live in Stripe. We store the payment_intent id and status only — never card data. Subscriptions (instructor Indie Pro $39/mo) use a separate subscriptions table mirroring Stripe.

12 / Social

Buddies · posts · messages

buddies — user_id, buddy_id, status (pending/accepted), sincefollows — follower_id, followee_id

posts · comments · reactionsthe feed

Field	Type	Notes	Source
author_id / author_type	uuid / enum	diver · instructor · center · official	relation
post_type	enum	dive · cert · class · shop · site · text	system
body / media / ref_id	text / string[] / uuid	Links to dive_log / class / cert	editable
like_count / comment_count	int	Aggregated	derived

conversations · messagesDMs & class chats

conversation: type / participant_ids	enum / uuid[]	direct · class_chat	system
message: sender_id / body / sent_at / read_at	uuid / text / ts	Thread messages	editable

Notifications notifications

user_id / category / actor_id	uuid / enum	social · class · cert · system	system
body / ref / read	text / uuid / bool	Deep-links to the source object	system

13 / Reference

Enumerations

The controlled vocabularies referenced above — the "walls" for dropdowns and toggles.

Enum	Values
role	diver · instructor · center_staff · center_owner
accent	aqua · cobalt · marine · abyss · coral · amber
style	editorial · refined · modern · bold
cert_level	open_water · advanced_ow · rescue · divemaster · instructor · instructor_trainer · course_director
class_category	entry · con_ed · specialty · technical · pro_safety
dive_type	quarry · reef · night · training · wreck
equipment_category	exposure · life_support · propulsion · instruments · accessories
booking_status	held · deposit_paid · paid · waitlist · cancelled · completed
site_status	open · warning · closed · permit_only
visibility	everyone · members · buddies_only
verification_status	pending · verified · expired

14 / Reference

How it connects

The spine of the relationships, in plain terms.

users 1—N user_roles (multi-hat)users 1—1 diver / instructor / center profile per roleeach profile 1—1 page_themes (the editor output)users 1—N certifications, equipment_items, dive_logsinstructors N—N centers (affiliation)centers / instructors 1—N classes 1—N class_sessionsclasses / trips 1—N bookings N—1 usersbookings 1—1 Stripe payment_intentusers N—N buddies / followsusers 1—N posts 1—N comments / reactions

← Back to the Theme style guide