# Database schema guide
- Active application roles are `admin`, restricted `cashier`, and `customer`; `users.role_id` remains linked to historical role rows until they are unreferenced. `users.is_demo` marks locally managed demo accounts.
- `users.email` and `users.phone` are unique when present; `phone` stores normalized Philippine E.164 numbers. Self-registered customers have `email_verified=0` and `status='pending'` until successful email OTP verification. Existing active accounts retain verified status during migration.
- `users` also stores the customer's default delivery-address type, manual address components, optional instructions, and GPS coordinates.
- `contact_verifications` stores password-hashed OTPs, purpose (`registration` or `contact_change`), target email or phone, expiration, attempt count, used timestamp, request IP, and creation time; it never stores the plaintext OTP. Registration OTPs expire after five minutes and are limited to five attempts.
- `categories` -> `products.category_id`
- `users` -> `products.created_by`, `orders.customer_id` and the nullable legacy `cashier_id`, `payments.paid_by`, `transactions.created_by`, `activity_log.user_id`. The old column remains for historical-order compatibility and new customer orders leave it `NULL`.
- `orders` (including customer name/contact and delivery address) -> `order_items.order_id` (CASCADE), `payments.order_id` (CASCADE), `transactions.order_id`
- Delivery orders also store address type, city/municipality, barangay, street, optional building/landmark/instructions, and optional GPS latitude/longitude on that same `orders` row; the existing customer phone is used as the delivery contact.
- `order_status_history` records each order state, actor, note, and timestamp; initial existing statuses are backfilled when the installer runs.
- `orders.completed_at` stores the final completion time; `orders.status` and `order_status_history.status` include `out_for_delivery` for delivered orders.
- `payments.method` and `reference_no` retain existing historical values. Customer online GCash orders are inserted only after a verified Xendit payment; manual GCash payments remain unpaid until Administrator or authorized Cashier verification.
- `payments` stores currency, unique manual and normalized GCash references, receipt path and SHA-256 digest, OCR-extracted reference/amount/date/status and warnings, amount-mismatch and duplicate-reference review flags, verification/rejection timestamps, reviewer IDs, and rejection reason. Receipt digests and normalized references are uniquely indexed; files are stored outside the public document root and served inline only to the owning customer, an administrator, or a cashier for a pending receipt under the authenticated receipt endpoint. Existing databases apply migration 13 after migration 12 for duplicate-reference review fields.
- `payment_verification_history` records receipt submissions and Administrator/Cashier approval or rejection decisions, including actor, state transition, reason, and timestamp. OCR results and receipt uploads never set payment status to paid; only an authorized merchant review or verified provider callbacks do.
- `payments` -> `transactions.payment_id`
- Auth hardening: `users.failed_attempts/locked_until`, `login_attempts`, `password_resets`
- Audit: `activity_log(user, action, entity, details, ip, date/time)`
- Indexes: orders(status,created), payments(status,created), transactions(type,created), log(user,time), attempts(ip,time).
Diagram: roles 1-N users; categories 1-N products; users 1-N orders; orders 1-N items/payments/transactions.

`payment_checkouts` stores the customer, exact server-priced amount, Xendit payment-request/payment identifiers, web and mobile redirect URLs, status, and recoverable order snapshot. It has unique payment/order references and foreign keys to customers and orders. The additive `database/07_payment_checkouts.sql` migration creates this table, and `database/10_payment_deeplink.sql` adds the optional mobile redirect URL; neither changes existing order or payment records. Back up and rehearse these migrations on a restored staging database before applying them to an existing deployment. To roll back migration 10 after reverting the application code, drop only its added column with `ALTER TABLE payment_checkouts DROP COLUMN payment_deeplink_url;`.

The local installer includes the payment checkout, mobile redirect, manual-review schema, migration 14, and migration 15 for fresh local installations, but also seeds/updates menu and account data; do not run it against production or unbacked-up databases. Existing databases need migrations 07 and 10 for Xendit online GCash, migration 09 for manual payment review fields, migration 14 to disable legacy cashier/staff accounts, and migration 15 to restore the restricted cashier role and add the demo marker. Apply reviewed migrations separately after a backup and restore rehearsal. Both account migrations preserve orders, receipts, and payment history. The demo reset only removes marked duplicate demo logins while retaining linked business records and all non-demo users. No card, wallet PIN, or payment credentials are collected.