3. Database Schema

Nine custom tables extending Medusa v2's internal data model.

All primary keys are UUIDs. Price fields are stored as integers in cents (AUD) to avoid floating-point issues. All tables use soft deletes (deleted_at) where applicable.

3.1 Tables Overview

Table	Purpose	Key Relations
`customers`	Staff/customer accounts with auth fields	Root entity
`products`	Wallpacks, t-shirts, digital items	→ files (1:1), → order_items (1:N)
`orders`	Purchase records with payment/fulfilment status	→ customers, → order_items
`order_items`	Line items with price snapshot at purchase	→ orders, → products
`files`	ZIP file metadata stored in R2	→ products (1:1)
`download_links`	Secure download tokens per order item	→ customers, orders, products, files
`invite_tokens`	Staff invite system	→ customers (invited_by)
`promo_codes`	Discount codes with usage limits	→ orders (1:N)
`promo_code_uses`	Audit log of promo code usage	→ promo_codes, customers, orders

3.2 Table Definitions

customers

id                    UUID PRIMARY KEY
email                 VARCHAR(255) UNIQUE NOT NULL
password_hash         TEXT NOT NULL
first_name            VARCHAR(50)
last_name             VARCHAR(50)
phone                 VARCHAR(20)
status                ENUM('active', 'inactive', 'suspended')
failed_login_attempts INTEGER DEFAULT 0
locked_until          TIMESTAMP
deleted_at            TIMESTAMP
anonymised_at         TIMESTAMP
created_at            TIMESTAMP NOT NULL
updated_at            TIMESTAMP NOT NULL

products

id              UUID PRIMARY KEY
title           VARCHAR(255) NOT NULL
description     TEXT
sku             VARCHAR(100) UNIQUE
price           INTEGER NOT NULL  -- cents AUD
thumbnail       TEXT
status          ENUM('draft', 'published', 'archived')
type            ENUM('digital', 'physical')
collection_id   UUID REFERENCES collections(id)
download_count  INTEGER DEFAULT 0
created_by      UUID REFERENCES customers(id)
deleted_at      TIMESTAMP
created_at      TIMESTAMP NOT NULL
updated_at      TIMESTAMP NOT NULL

orders

id                  UUID PRIMARY KEY
invoice_number      VARCHAR(20) UNIQUE NOT NULL
customer_id         UUID REFERENCES customers(id) NOT NULL
promo_code_id       UUID REFERENCES promo_codes(id)
status              ENUM('pending', 'completed', 'cancelled', 'refunded')
payment_status      ENUM('awaiting', 'captured', 'refunded', 'failed')
fulfillment_status  ENUM('not_fulfilled', 'fulfilled', 'partially_fulfilled')
currency            VARCHAR(3) DEFAULT 'AUD'
subtotal            INTEGER NOT NULL
discount_total      INTEGER DEFAULT 0
tax_total           INTEGER DEFAULT 0
total               INTEGER NOT NULL
ip_address          VARCHAR(45)
created_at          TIMESTAMP NOT NULL
updated_at          TIMESTAMP NOT NULL

order_items

id                UUID PRIMARY KEY
order_id          UUID REFERENCES orders(id) NOT NULL
product_id        UUID REFERENCES products(id) NOT NULL
quantity          INTEGER NOT NULL DEFAULT 1
unit_price        INTEGER NOT NULL  -- snapshot at purchase
product_name      VARCHAR(255) NOT NULL  -- snapshot
product_thumbnail TEXT  -- snapshot
subtotal          INTEGER NOT NULL
created_at        TIMESTAMP NOT NULL

files

id          UUID PRIMARY KEY
product_id  UUID REFERENCES products(id) UNIQUE NOT NULL
filename    VARCHAR(255) NOT NULL
storage_key TEXT NOT NULL  -- R2 object key
size_bytes  BIGINT NOT NULL
mime_type   VARCHAR(100) NOT NULL
checksum    VARCHAR(255)
uploaded_by UUID REFERENCES customers(id)
created_at  TIMESTAMP NOT NULL
updated_at  TIMESTAMP NOT NULL

download_links

id                  UUID PRIMARY KEY
customer_id         UUID REFERENCES customers(id) NOT NULL
order_id            UUID REFERENCES orders(id) NOT NULL
product_id          UUID REFERENCES products(id) NOT NULL
file_id             UUID REFERENCES files(id) NOT NULL
token               UUID UNIQUE NOT NULL
status              ENUM('active', 'expired', 'exhausted', 'revoked')
download_count      INTEGER DEFAULT 0
max_downloads       INTEGER DEFAULT 3
expires_at          TIMESTAMP NOT NULL  -- 30 days from creation
last_downloaded_at  TIMESTAMP
created_at          TIMESTAMP NOT NULL
updated_at          TIMESTAMP NOT NULL

invite_tokens

id          UUID PRIMARY KEY
email       VARCHAR(255) NOT NULL
token       UUID UNIQUE NOT NULL
status      ENUM('pending', 'accepted', 'expired', 'revoked')
invited_by  UUID REFERENCES customers(id) NOT NULL
expires_at  TIMESTAMP NOT NULL  -- 30 days
accepted_at TIMESTAMP
created_at  TIMESTAMP NOT NULL
updated_at  TIMESTAMP NOT NULL

promo_codes

id              UUID PRIMARY KEY
code            VARCHAR(50) UNIQUE NOT NULL
discount_type   ENUM('percentage', 'fixed')
discount_value  INTEGER NOT NULL
min_order_value INTEGER DEFAULT 0
max_uses        INTEGER
uses_count      INTEGER DEFAULT 0
is_active       BOOLEAN DEFAULT true
expires_at      TIMESTAMP
created_by      UUID REFERENCES customers(id)
created_at      TIMESTAMP NOT NULL
updated_at      TIMESTAMP NOT NULL

promo_code_uses

id              UUID PRIMARY KEY
promo_code_id   UUID REFERENCES promo_codes(id) NOT NULL
customer_id     UUID REFERENCES customers(id) NOT NULL
order_id        UUID REFERENCES orders(id) NOT NULL
discount_amount INTEGER NOT NULL
used_at         TIMESTAMP NOT NULL

3.3 Database Indexes

Table	Column	Reason
`customers`	`email`	Login lookup — unique constraint
`orders`	`customer_id`	Order history queries
`download_links`	`token`	Download validation — unique constraint
`download_links`	`customer_id`	Account download list
`invite_tokens`	`token`	Invite validation — unique constraint
`products`	`status`	Filter published products

3.4 Key Design Decisions

Price snapshots on order_items — unit_price, product_name, product_thumbnail are copied at purchase time. Product price changes don't affect past orders.
Brute force fields on customers — failed_login_attempts + locked_until enforced at the application layer. 5 failures → 30 min lockout.
Download token — crypto.randomUUID(), never user-controlled, never sequential. Expiry and max downloads enforced on every request.

2. System Architecture 4. API Design