Tables

Types below are the PostgreSQL types. Nullability is the application requirement. UUID primary keys and created_at are present on every table. updated_at is present on rows that change. Append-only tables omit updated_at.

Amounts are cents. currency is char(3) and must be USD.

Identity

users

Customer and administrator accounts.

Column	Type	Required	Notes
id	uuid	yes	Primary key.
full_name	text	yes	Display name.
email	text	yes	Unique, lowercase, trimmed.
phone	text	no	Contact number.
role	enum	yes	CUSTOMER or ADMIN.
status	enum	yes	PENDING_SETUP, ACTIVE, SUSPENDED, DISABLED.
created_at, updated_at	timestamptz	yes	UTC.

user_credentials

Column	Type	Required	Notes
user_id	uuid	yes	Primary key and foreign key to users.
password_hash	text	yes	Argon2id hash.
password_changed_at	timestamptz	yes	Rotated on reset and change.

refresh_tokens

The access token is a signed JWT and is not stored. This table stores only refresh tokens.

Column	Type	Required	Notes
id	uuid	yes	Primary key.
user_id	uuid	yes	Foreign key.
token_hash	text	yes	Unique SHA-256 of the refresh token.
expires_at	timestamptz	yes	Initial lifetime is 14 days.
revoked_at	timestamptz	no	Set on logout, rotation, or password reset.

account_setup_tokens

One-time account activation after a verified first payment.

Column	Type	Required	Notes
id	uuid	yes	Primary key.
user_id	uuid	yes	Foreign key.
token_hash	text	yes	Unique hash. The raw token is emailed once.
expires_at	timestamptz	yes	Initial lifetime is 72 hours.
used_at	timestamptz	no	Null until consumed. One unused row per user.

password_reset_tokens

Same shape as account setup tokens. Initial lifetime is 1 hour. Supporting table for password recovery.

user_mfa_factors

Column	Type	Required	Notes
id	uuid	yes	Primary key.
user_id	uuid	yes	Foreign key.
factor_type	text	yes	totp for this edition.
secret_ciphertext	text	yes	Encrypted shared secret. Never the plaintext secret.
enabled_at	timestamptz	no	Null until the administrator confirms a code.

Catalog and bookings

services

Column	Type	Required	Notes
id	uuid	yes	Primary key.
name	text	yes	Catalog name.
description	text	no	Public description.
active	boolean	yes	Inactive services are hidden.

service_packages

Column	Type	Required	Notes
id	uuid	yes	Primary key.
service_id	uuid	yes	Foreign key.
name	text	yes	Package name.
price_cents	integer	yes	USD cents, zero or greater.
currency	char(3)	yes	USD.
duration_minutes	integer	yes	Greater than zero.
active	boolean	yes	Inactive packages cannot be booked.

bookings

Column	Type	Required	Notes
id	uuid	yes	Primary key.
customer_id	uuid	yes	Foreign key to users. Indexed.
package_id	uuid	yes	Foreign key to service_packages.
scheduled_at	timestamptz	yes	Indexed.
location	text	yes	Session location supplied by the customer.
status	enum	yes	See the booking status model.
approved_by	uuid	no	Administrator who confirmed the booking.
approved_at	timestamptz	no	Required when status is CONFIRMED.
decision_note	text	no	Reason recorded with a rejection or cancellation.

orders

One order per booking.

Column	Type	Required	Notes
id	uuid	yes	Primary key.
customer_id	uuid	yes	Indexed.
booking_id	uuid	yes	Unique foreign key.
order_number	text	yes	Unique human-readable number.
subtotal_cents	integer	yes	Service subtotal before the reward discount.
discount_cents	integer	yes	Reward discount. Zero when none is applied.
total_cents	integer	yes	subtotal_cents - discount_cents.
currency	char(3)	yes	USD.
status	enum	yes	Indexed. Pending, paid, failed, refund states, or cancelled.

order_items

Column	Type	Required	Notes
id	uuid	yes	Primary key.
order_id	uuid	yes	Foreign key.
package_id	uuid	yes	Package at time of purchase.
quantity	integer	yes	Greater than zero. This edition uses 1.
unit_price_cents	integer	yes	Snapshot of the package price.
currency	char(3)	yes	USD.
description	text	yes	Snapshot of the package name.

Payments

payments

Column	Type	Required	Notes
id	uuid	yes	Primary key.
order_id	uuid	yes	Foreign key. Indexed.
provider	enum	yes	STRIPE.
provider_payment_id	text	no	Unique with provider when present.
amount_cents	integer	yes	USD cents.
currency	char(3)	yes	USD.
status	enum	yes	Indexed. Pending, succeeded, failed, or refund states.

payment_webhook_events

Deduplication table. A row is inserted in the same transaction that applies the event.

Column	Type	Required	Notes
id	uuid	yes	Primary key.
provider	enum	yes	STRIPE.
event_id	text	yes	Unique together with provider.
payload_hash	text	yes	Hash of the verified payload.
processed_at	timestamptz	no	Set when the transaction commits the effect.
received_at	timestamptz	yes	Arrival time.

There is no foreign key to payments. The event can be the first time the API learns the provider payment id.

idempotency_keys

Supporting table for safe retries of booking and checkout posts.

Column	Type	Required	Notes
id	uuid	yes	Primary key.
user_id	uuid	no	Null for a first checkout before login.
scope	text	yes	Operation name, such as booking.create.
key	text	yes	Client Idempotency-Key. Unique with scope.
request_hash	text	yes	Detects reuse of a key with a different body.
response_code	integer	yes	Stored response status.
response_body	jsonb	yes	Stored response.
expires_at	timestamptz	yes	Keys expire after 24 hours.

Galleries

galleries

Column	Type	Required	Notes
id	uuid	yes	Primary key.
customer_id	uuid	yes	Indexed.
booking_id	uuid	yes	Unique. One gallery for one booking.
title	text	yes	Gallery title.
publication_status	enum	yes	DRAFT, PUBLISHED, ARCHIVED.
access_status	enum	yes	Indexed. PENDING_APPROVAL, APPROVED, REVOKED.
approved_by	uuid	no	Required while access is APPROVED.
approved_at	timestamptz	no	Required while access is APPROVED.

The current access status stays on this table so authorization is a single-row check. History is the next table.

gallery_access_reviews

Column	Type	Required	Notes
id	uuid	yes	Primary key.
gallery_id	uuid	yes	Foreign key. Indexed with created_at.
reviewed_by	uuid	yes	Administrator.
decision	enum	yes	APPROVED, REJECTED, or REVOKED.
reason	text	no	Optional note.
created_at	timestamptz	yes	Append-only.

photos

Column	Type	Required	Notes
id	uuid	yes	Primary key.
gallery_id	uuid	yes	Foreign key.
storage_key	text	yes	Private object key for the original.
thumbnail_key	text	no	Set when the worker finishes the thumbnail.
status	enum	yes	PENDING_UPLOAD, PROCESSING, READY, FAILED, REMOVED.
content_type	text	no	Detected type after signature check.
byte_size	integer	no	Size of the original.

photo_ratings

Column	Type	Required	Notes
id	uuid	yes	Primary key.
photo_id	uuid	yes	Unique together with customer_id.
customer_id	uuid	yes	Must be the gallery owner.
rating	integer	yes	1 through 5.
feedback	text	no	Optional comment.

Referrals

referral_codes

Column	Type	Required	Notes
id	uuid	yes	Primary key.
user_id	uuid	yes	Unique. One code per customer.
code	text	yes	Unique public code.
active	boolean	yes	Inactive codes cannot be attributed.

referrals

Column	Type	Required	Notes
id	uuid	yes	Primary key.
referrer_id	uuid	yes	Indexed. Must differ from referred_user_id.
referred_user_id	uuid	yes	Unique. One attribution per customer.
qualifying_order_id	uuid	no	Unique once the first paid order qualifies.
status	enum	yes	ATTRIBUTED, QUALIFIED, REWARDED, REJECTED, REVERSED.

reward_policies

Column	Type	Required	Notes
id	uuid	yes	Primary key.
percentage_basis_points	integer	yes	0 through 10,000.
reward_validity_days	integer	no	Null means credits do not expire.
active	boolean	yes	Only one row may be active.
valid_from	timestamptz	yes	Inclusive start.
valid_until	timestamptz	no	Exclusive end when set.

referral_rewards

Column	Type	Required	Notes
id	uuid	yes	Primary key.
referral_id	uuid	yes	Unique together with qualifying_order_id.
qualifying_order_id	uuid	yes	Unique. One reward per order.
policy_id	uuid	yes	Policy used for the calculation.
eligible_subtotal_cents	integer	yes	Snapshot of the eligible USD base.
basis_points_applied	integer	yes	Snapshot of the rate.
amount_cents	integer	yes	Calculated reward.
currency	char(3)	yes	USD.
status	enum	yes	PENDING, AVAILABLE, REDEEMED, REVERSED, EXPIRED.
expires_at	timestamptz	no	Set from the policy when the reward is created.

reward_ledger

Immutable credits, redemptions, reversals, and expirations. Balance is the sum of amount_cents.

Column	Type	Required	Notes
id	uuid	yes	Primary key.
user_id	uuid	yes	The referrer who holds the balance. Indexed with created_at.
reward_id	uuid	no	Set for credits, reversals, and expirations of a reward.
redemption_id	uuid	no	Set when the entry reserves or releases a redemption.
entry_type	enum	yes	CREDIT, REDEMPTION, REVERSAL, EXPIRATION.
amount_cents	integer	yes	Signed. Credits are positive. Redemptions and expirations are negative.
currency	char(3)	yes	USD.

reward_redemptions

Column	Type	Required	Notes
id	uuid	yes	Primary key.
user_id	uuid	yes	Referrer spending the balance.
order_id	uuid	yes	Future order receiving the discount.
amount_cents	integer	yes	Positive discount amount.
currency	char(3)	yes	USD.
status	enum	yes	RESERVED, APPLIED, RELEASED, REVERSED.

The ledger entry that reserves the discount is written in the same transaction as the redemption row. APPLIED does not write a second debit.

Operations

notifications

Column	Type	Required	Notes
id	uuid	yes	Primary key.
user_id	uuid	yes	Recipient. Indexed with read_at.
type	text	yes	For example booking.payment_received or gallery.access_approved.
payload	jsonb	yes	Structured context. No secrets and no raw tokens.
read_at	timestamptz	no	Null until read.

notification_deliveries

Column	Type	Required	Notes
id	uuid	yes	Primary key.
notification_id	uuid	yes	Foreign key.
channel	enum	yes	EMAIL or IN_APP.
status	enum	yes	PENDING, SENT, FAILED, CANCELLED.
attempt_count	integer	yes	Starts at 0.
last_attempt_at	timestamptz	no	Last delivery attempt.

background_jobs

Column	Type	Required	Notes
id	uuid	yes	Primary key.
job_type	text	yes	See the component job list.
payload	jsonb	yes	Ids the worker needs.
status	enum	yes	Indexed with available_at.
available_at	timestamptz	yes	Claim when this time has passed.
attempt_count	integer	yes	Retry count.
last_error	text	no	Last failure message, without secrets.
locked_at	timestamptz	no	Set while a worker owns the job.

audit_logs

Column	Type	Required	Notes
id	uuid	yes	Primary key.
actor_user_id	uuid	no	Null for a verified webhook.
action	text	yes	For example booking.approve or gallery.access.revoke.
resource_type	text	yes	Table or aggregate name.
resource_id	uuid	yes	Target row.
metadata	jsonb	no	Before and after status. No passwords, tokens, or card data.
created_at	timestamptz	yes	Append-only. Indexed.