Schema overview

PostgreSQL is the system of record for accounts, bookings, payments, gallery decisions, and rewards. prisma generate produces the Prisma Client from schema.prisma. The API and the worker import that client and connect only through DATABASE_URL. They do not open a second database driver or a second connection string. prisma migrate uses the same URL. The implementation schema is on the migrations page.

Conventions

• Primary keys are UUIDs generated in the database.
• Foreign keys reference those UUIDs. Deletes are restricted for business records. Refresh-token rows and one-time token rows cascade from the user. Approver columns use ON DELETE SET NULL only where the column is optional.
• Timestamps are timestamptz, stored in UTC.
• Email is trimmed and lowercased before insert. A check constraint rejects any other form, and a unique constraint rejects duplicates.
• Status changes that depend on each other run in one transaction. That includes order fulfillment, reward allocation, and discount redemption.
• Account setup tokens and password reset tokens are stored as SHA-256 hashes. Each unused token is single-use. A partial unique index allows one unused token per user.
• reward_ledger, gallery_access_reviews, audit_logs, and processed webhook claims are append-only. Corrections are new rows.

Money

All order amounts, payment amounts, and reward values use USD. The column type is an integer of cents. currency is the three-character code USD.

Display	Stored
$200.00	20000 and currency = 'USD'
$10.00 reward	1000 and currency = 'USD'
$0.51 reward	51 and currency = 'USD'

There is no floating-point money column. A refund, a discount, and a reward all use the same integer unit.

The eligible referral base is subtotal_cents - discount_cents on the service order. Taxes and travel charges are not in this schema. If they are added later, they get their own columns and stay out of subtotal_cents. Refunded service amounts reduce the eligible base before a reversal is posted. The rounding rule is defined in referrals and rewards.

Separate status models

Payment, booking approval, and gallery access are different states. A paid booking is not a confirmed booking. A published gallery is not an accessible gallery.

Booking status

Status	Meaning
PENDING_PAYMENT	Order exists and payment is not verified.
PENDING_APPROVAL	Payment is verified. The photographer has not decided.
CONFIRMED	The photographer approved the booking.
REJECTED	The photographer rejected it. A paid booking starts a refund.
CANCELLED	The booking was cancelled before completion. A captured payment starts a refund.
COMPLETED	The session is done.

Order and payment status

The order records the commercial result: pending payment, paid, payment failed, refund pending, partially refunded, refunded, or cancelled. Each provider attempt is a payments row with its own status. Provider payment ids are unique per provider when the id is present.

Gallery status

Field	Values	Used for
publication_status	DRAFT, PUBLISHED, ARCHIVED	Whether the photographer has released the gallery from draft.
access_status	PENDING_APPROVAL, APPROVED, REVOKED	Whether the customer may view and download.

Authorization reads access_status and publication_status on galleries. History is appended to gallery_access_reviews and is not the authorization lookup.

Integrity rules

1. Normalized email, unique.
2. UNIQUE(provider, event_id) on payment webhook events.
3. UNIQUE(provider, provider_payment_id) where the provider payment id is present.
4. One qualifying reward for a referral and qualifying order, and one reward for a given order.
5. Photo ratings are integers from 1 to 5, one row per photo and customer.
6. Reward percentages are integers from 0 to 10,000 basis points.
7. Indexes cover customer ids, foreign keys, booking scheduled_at, order status, payment status, gallery access status, and background-job availability.
8. Order fulfillment, reward allocation, and discount redemption are transactional.
9. Setup and reset tokens are hashed and single-use.
10. Reward ledger entries are immutable. A mistake is a reversal entry.
11. A referral's referrer and referred user are different people.
12. At most one reward policy is active.
13. A confirmed booking has approved_by and approved_at. An approved gallery has approved_by and approved_at.

Supporting tables

The core catalog in the table dictionary includes three groups the authorization and payment design needs beyond the first entity sketch:

• gallery_access_reviews for approval history.
• password_reset_tokens and user_mfa_factors for recovery and administrator MFA.
• idempotency_keys so a retried checkout does not create a second booking.

Balance

Spendable reward balance is the sum of reward_ledger.amount_cents for that user. It is not a cached column. Redemption locks the user row, computes the sum, and inserts the redemption and the ledger entry in that same transaction.