Skip to main content

Relationships

The diagrams use the physical table names. Cardinality is the rule the foreign keys enforce. Webhook events are drawn next to payments even though they do not store a payment foreign key: an event is how a payment becomes verified, and it must be stored before that update.

Commerce​

A booking has one order. An order has one or more item snapshots. A failed payment stays on the order, and a retry inserts another payments row. The unique provider payment id stops two rows from claiming the same Stripe payment.

galleries.access_status is the column the download check reads. gallery_access_reviews keeps every approval, rejection, and revocation. A gallery belongs to one booking and one customer. The customer on the gallery is the only customer who can rate its photos.

Referrals and the ledger​

One referred user has at most one referral. That referral has at most one qualifying order. That order funds at most one reward. The reward's qualifying order must be the referral's qualifying order. The ledger is the balance. Redemption rows remember which future order received the discount.

Platform records​

background_jobs has no foreign key. The line from a delivery to a job is logical: the email job payload stores the delivery id. Audit rows may have a null actor when a verified webhook is the cause. Idempotency keys may have a null user when the first checkout happens before account setup.

Access path the API must join​

A photo download is allowed only when all of these are true:

  1. The user id in the access token equals galleries.customer_id.
  2. galleries.publication_status is PUBLISHED.
  3. galleries.access_status is APPROVED.
  4. photos.status is READY.
  5. photos.gallery_id matches the gallery in the request.

The review history is not consulted for this check. It is the record of how access_status got its current value.