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, pricing).

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)
type	project_type enum	private / public — default private
status	project_status enum	draft / live / closed / archived — default draft
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; falls back to operator timezone if null
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
terms_version_id	uuid FK → project_terms	nullable, indexed — the exact T&C version accepted at join time

Unique index on (project_id, user_id). terms_version_id is nullable for backwards compatibility with rows created before versioning was introduced.

project_terms

Versioned snapshots of a project's Terms & Conditions. A new row is created when the first user joins (version "1") and whenever an operator publishes updated T&C (version "2", "3", …).

Storing a snapshot at join time ensures users can always retrieve exactly the text they agreed to, even if the operator later changes the T&C.

Column	Type	Notes
id	uuid PK
project_id	uuid FK → projects	indexed
version	varchar(50)	sequential per project: "1", "2", …
content	text	full T&C text at the time this version was created
effective_at	timestamp	when this version came into effect (default now)
created_at	timestamp

Unique index on (project_id, version).

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_discounts

Discounts linked to a project (composite PK).

Column	Type	Notes
project_id	uuid FK → projects	indexed
discount_id	uuid FK → discounts	indexed

project_coaches

Coaches associated with a project.

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

---

Media

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
taken_at	timestamp	nullable — EXIF date extracted during thumbnail generation
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(512)	pgvector, 512-dim from Retina clustering service
quality_score	real	nullable, Retina face detection score (0–1)
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	face detection score × 100 (0–100 scale)
source	varchar(20)	auto / manual
review_status	varchar(20)	confirmed / pending / rejected
created_at	timestamp

Unique index on (photo_id, user_id).

WARNING

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

photo_favorites

Photos bookmarked by a user ("favourited"). Used for the Favourites filter in the main-web photo grid.

Column	Type	Notes
id	uuid PK
user_id	uuid FK → users	indexed
photo_id	uuid FK → photos	cascade delete
created_at	timestamp

Unique index on (user_id, photo_id).

photo_tag_reports

User-submitted tag accuracy reports.

Column	Type	Notes
id	uuid PK
photo_id	uuid FK → photos	indexed
user_id	uuid FK → users	indexed
report_type	varchar(20)	incorrect_tag / missing_tag
created_at	timestamp

Unique index on (photo_id, user_id, report_type) — one report per type per user per photo.

---

Pricing & Promotions

pricing_rules

Per-photo price set by an operator. A rule with project_id IS NULL is the operator default; a rule with a project_id overrides the default for that project. The project override wins.

Column	Type	Notes
id	uuid PK
operator_id	uuid FK → operators	indexed
project_id	uuid FK → projects	nullable, indexed — null = operator default
price_per_photo_cents	integer	in cents, not null
compare_at_price_cents	integer	nullable — shown as strikethrough original price
valid_from	timestamp	default now
valid_until	timestamp	nullable — null = no expiry
is_active	boolean	default true
created_at	timestamp

discounts

Operator-defined discounts that can be linked to projects.

Column	Type	Notes
id	uuid PK
operator_id	uuid FK → operators	indexed
name	varchar(255)	display name
type	enum	percentage / fixed / bundle
value	integer	nullable — pct points or cents depending on type
bundle_min_photos	integer	nullable — required for bundle type
valid_from	timestamp	default now
valid_until	timestamp	nullable
is_active	boolean	default true
created_at	timestamp

Discount types:

• percentage — value % off subtotal (e.g. value=20 → 20% off)
• fixed — value cents off subtotal (e.g. value=200 → $2.00 off)
• bundle — value % off if photoCount >= bundleMinPhotos

Only one discount is applied per purchase (the one with the highest savings).

promo_codes

Redeemable codes that apply a specific discount at checkout.

Column	Type	Notes
id	uuid PK
operator_id	uuid FK → operators	indexed
code	varchar(50)	unique
discount_id	uuid FK → discounts	indexed
max_uses	integer	nullable — null = unlimited
max_uses_per_user	integer	default 1
used_count	integer	default 0, atomically incremented on paid purchase
valid_from	timestamp	default now
valid_until	timestamp	nullable
is_active	boolean	default true
created_at	timestamp

---

Commerce & Consent

purchases

Column	Type	Notes
id	uuid PK
user_id	uuid FK → users	indexed
project_id	uuid FK → projects	indexed
photo_count	integer	number of photos in this purchase
subtotal_cents	integer	photoCount × pricePerPhotoCents before discount
discount_applied_cents	integer	discount savings, default 0
total_cents	integer	subtotalCents - discountAppliedCents
applied_discount_id	uuid FK → discounts	nullable
applied_promo_code	varchar(50)	nullable — promo code string if used
stripe_payment_intent_id	varchar(255)	nullable, partial unique index
status	varchar(20)	pending / paid / failed / refunded
created_at	timestamp

Indexes: user_id, project_id, status, partial unique on stripe_payment_intent_id WHERE NOT NULL.

purchase_downloads

Tracks which photos a user has access to after a purchase.

Column	Type	Notes
id	uuid PK
purchase_id	uuid FK → purchases	indexed
user_id	uuid FK → users	indexed
photo_id	uuid FK → photos	indexed
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

invites

Pending email invitations sent to users who opted in to receive photos from a project. Created by the bulk invite feature in the backoffice.

Column	Type	Notes
id	uuid PK
project_id	uuid FK → projects	indexed
email	text	recipient address (lowercase)
token	uuid	unique — embedded in the invite link
status	text	pending / used / expired
expires_at	timestamp	30 days from creation
used_at	timestamp	nullable — set when the user registers via the invite link
created_at	timestamp

---

qr_tokens

Tokens issued when a user joins a project (via QR or shareable link). Used for event check-in.

Column	Type	Notes
id	uuid PK
project_id	uuid FK → projects	indexed
user_id	uuid FK → users	indexed
token	varchar(255)	unique
expires_at	timestamp	30 days from issue
used_at	timestamp	nullable
created_at	timestamp

---

password_resets

One-time password reset tokens issued when a user requests a password reset via email.

Column	Type	Notes
id	uuid PK
user_id	uuid FK → users	indexed
token_hash	text	SHA-256 hash of the reset token; unique
expires_at	timestamp	1 hour from creation
used_at	timestamp	nullable — set when the password is successfully reset
created_at	timestamp