Database Schema

PostgreSQL with the pgvector extension (required for face embedding storage).

Schema files live in packages/db/src/schema/ — one file per domain entity. After any schema change, run pnpm --filter=@repo/db db:generate and commit the generated migration.

All tables use:

• UUID primary keys (uuid_generate_v4())
• createdAt timestamp (default now)
• updatedAt where rows are updated
• deletedAt (soft delete) where applicable

---

Users & Auth

users

End user accounts.

Column	Type	Notes
id	uuid PK
name	varchar(255)
email	varchar(255)	unique
password_hash	text	bcrypt
address	text	nullable
billing_address	text	nullable
created_at	timestamp

operators

Operator accounts (manage projects, photographers, packages).

Column	Type	Notes
id	uuid PK
name	varchar(255)
email	varchar(255)	unique
password_hash	text	bcrypt
currency	varchar(3)	ISO 4217, default from IP geo
locale	varchar(10)	BCP 47
timezone	varchar(64)	IANA timezone
profile_picture_url	text	nullable
created_at	timestamp

photographers

Photographers belong to an operator.

Column	Type	Notes
id	uuid PK
operator_id	uuid FK → operators	indexed
name	varchar(255)
email	varchar(255)	unique
password_hash	text	bcrypt
address	text	nullable
created_at	timestamp

---

Projects

projects

Column	Type	Notes
id	uuid PK
operator_id	uuid FK → operators	indexed
name	varchar(255)
location_name	varchar(255)	nullable
start_at	timestamp	nullable
end_at	timestamp	nullable
project_link	text	nullable
terms_and_conditions	text	nullable
timezone	varchar(64)	nullable
created_at	timestamp

project_members

Users who have joined a project (via QR).

Column	Type	Notes
id	uuid PK
project_id	uuid FK → projects	indexed
user_id	uuid FK → users	indexed
joined_at	timestamp
terms_accepted_at	timestamp	nullable

Unique index on (project_id, user_id).

project_photographers

Photographers assigned to a project.

Column	Type	Notes
id	uuid PK
project_id	uuid FK → projects	indexed
photographer_id	uuid FK → photographers	indexed

project_packages

Packages linked to a project (composite PK).

Column	Type	Notes
project_id	uuid FK → projects
package_id	uuid FK → packages

project_coaches

Coaches associated with a project.

Column	Type	Notes
id	uuid PK
project_id	uuid FK → projects	indexed
name	varchar(255)

---

Media

packages

Pricing packages created by operators.

Column	Type	Notes
id	uuid PK
operator_id	uuid FK → operators	indexed
name	varchar(255)
price	integer	in cents
photo_count	integer
created_at	timestamp
updated_at	timestamp

photos

Project photos uploaded by photographers.

Column	Type	Notes
id	uuid PK
project_id	uuid FK → projects	indexed
photographer_id	uuid FK → photographers	indexed
storage_key	varchar(512)	R2 key for original
thumbnail_key	varchar(512)	nullable, 400px thumb
watermarked_key	varchar(512)	nullable
status	varchar(20)	processing / ready / failed
uploaded_at	timestamp

selfies

User selfies for face recognition.

Column	Type	Notes
id	uuid PK
user_id	uuid FK → users	indexed
storage_key	varchar(512)	R2 key
embedding	vector(128)	pgvector, from Rekognition
quality_score	real	nullable, Rekognition confidence
status	varchar(20)	pending / approved / rejected
rejection_reason	text	nullable
created_at	timestamp

photo_tags

Face recognition results linking users to photos.

Column	Type	Notes
id	uuid PK
photo_id	uuid FK → photos	indexed
user_id	uuid FK → users	indexed
confidence	real	raw Rekognition score, always stored
source	varchar(20)	auto / manual
review_status	varchar(20)	confirmed / pending / rejected
created_at	timestamp

WARNING

Only confirmed tags are visible to end users. pending tags are in the manual review queue.

---

Commerce & Consent

purchases

Column	Type	Notes
id	uuid PK
user_id	uuid FK → users	indexed
project_id	uuid FK → projects	indexed
package_id	uuid FK → packages	indexed
stripe_payment_intent_id	varchar(255)	unique
status	varchar(20)	pending / paid / failed
created_at	timestamp

user_consents

Biometric processing consent records.

Column	Type	Notes
id	uuid PK
user_id	uuid FK → users	indexed
consent_type	varchar(50)	e.g. biometric_processing
consent_version	varchar(20)
given_at	timestamp
revoked_at	timestamp	nullable — null means active

qr_tokens

Short-lived tokens embedded in project QR codes.

Column	Type	Notes
id	uuid PK
project_id	uuid FK → projects	indexed
token	varchar(255)	unique
expires_at	timestamp
used_at	timestamp	nullable
created_at	timestamp