Database Schema

Ricoya uses two Supabase PostgreSQL databases (dev and prod) with identical schemas. Auth is handled by Supabase Auth; application data lives in these tables.

Table Overview

Table	Purpose	Primary Key	Repository
users	User accounts and roles	auth_uid (UUID)	usersRepo.ts
restaurants	Restaurant profiles	id (integer)	restaurantsRepo.ts
addresses	Delivery and restaurant addresses	id (integer)	addressesRepo.ts
menu_items	Restaurant menu items	id (integer)	menuItemsRepo.ts
restaurant_categories	Menu categories per restaurant	id (integer)	categoriesRepo.ts
menu_customization_groups	Reusable customization groups	id (string)	menuCustomizationGroupsRepo.ts
tax_rates	Tax rate definitions	id (string)	taxesRepo.ts
restaurant_category_tax_mappings	Category → tax rate mapping	id (string)	—
carts	Shopping carts	id (integer)	cartRepo.ts
cart_items	Items in carts	id (integer)	cartRepo.ts
orders	Customer orders	id (integer)	ordersRepo.ts
order_items	Items within orders	id (integer)	orderItemsRepo.ts
assignments	Staff → restaurant access	id (string)	admin/assignments/route.ts
discount_codes	Promo/discount codes	id (integer)	discountCodesRepo.ts
discount_code_logs	Discount code audit trail	id (string)	discountCodesRepo.ts
ratings	Order ratings/reviews	id (integer)	ratingsRepo.ts
config	Key-value application config	id (string)	configRepo.ts
device_tokens	Mobile push notification tokens	device_token (string)	deviceTokensRepo.ts
push_subscriptions	Web push subscriptions	id (string)	pushSubscriptionsRepo.ts
saved_cards	Tokenized payment cards	id (integer)	saved-cards/route.ts
credit_balances	User credit balances	id (string)	creditRepo.ts
credit_ledger	Credit transaction history	id (string)	creditRepo.ts
file_uploads	Uploaded files metadata	id (string)	fileUploadsRepo.ts
restaurant_logs	Restaurant change audit trail	id (string)	admin/restaurants/route.ts

Core Tables

users

Column	Type	Notes
auth_uid	UUID	PK — Supabase Auth UID
legacy_numeric_id	integer	Migrated from legacy system
username	string	Display name
email	string	Unique
phone	string	Phone number
role	enum	customer, restaurant_owner, clerk, driver, admin, super_admin
manager_restaurant_id	integer	FK → restaurants.id
rtn	string	Tax ID for invoicing
created_at	timestamp

restaurants

Column	Type	Notes
id	integer	PK
name	string	Restaurant name
owner_user_id	string	FK → users.auth_uid
image_url	string	Logo/header image
summary	string	Description
cuisine	string	Cuisine type
tags	text[]	Searchable tags
location_address	string	Street address
location_lat	decimal	Latitude
location_lng	decimal	Longitude
is_active	boolean	Visible to customers
coming_soon	boolean	Preview mode
open_hours_json	jsonb	`{ mon: { open: "HH:mm", close: "HH:mm" }, ... }`
delivery_radius_km	decimal	Service area
default_prep_minutes	smallint	Prep time estimate
min_order_cents	integer	Minimum order value
default_menu_currency	string	HNL or USD
created_at	timestamp
updated_at	timestamp

orders

Column	Type	Notes
id	integer	PK
code	string	Human-visible order code
user_id	string	FK → users.auth_uid (customer)
restaurant_id	integer	FK → restaurants.id
address_id	integer	FK → addresses.id
delivery_address_snapshot	jsonb	Address frozen at order time
fulfillment_type	enum	delivery, pickup
status	enum	placed, pending_payment, accepted, ready, out_for_delivery, delivered, payment_received, provider_paid, canceled
payment_status	enum	pending, authorized, paid, card_verified, cash_completed, failed, refunded
payment_provider	string	cybersource, paypal, cash
payment_method	string	card, wallet, cash
payment_intent_id	string	Provider transaction ID
cash_payment_amount_cents	integer	Cash amount for COD
driver_user_id	string	FK → users.auth_uid (driver)
currency	string	HNL or USD
subtotal_cents	integer
tax_cents	integer
delivery_fee_cents	integer
service_fee_cents	integer
discount_cents	integer
tip_cents	integer
total_cents	integer
delivery_distance_km	decimal
estimate_prep_minutes	integer
estimate_delivery_minutes	integer
scheduled_for	timestamp	Null = ASAP
kitchen_instructions	string	Special notes
discount_code_id	integer	FK → discount_codes.id
proof_of_delivery	jsonb	Photo, signature, GPS
placed_at	timestamp
accepted_at	timestamp
ready_at	timestamp
picked_up_at	timestamp
delivered_at	timestamp
canceled_at	timestamp
cancel_reason	string

menu_items

Column	Type	Notes
id	integer	PK
restaurant_id	integer	FK → restaurants.id
name	string
price_cents	integer
currency	string	Default HNL
image_url	string
category_id	integer	FK → restaurant_categories.id
description	string
tax_rate_percent	decimal	Null = inherit from category
tax_id	string	FK → tax_rates.id
customizations	jsonb	Inline customization groups
linked_customization_group_ids	text[]	Reusable group references
allergen_tags	text[]
is_active	boolean
created_at	timestamp

assignments

Column	Type	Notes
id	string	PK
user_id	string	FK → users.auth_uid
restaurant_id	integer	FK → restaurants.id
access_role	enum	clerk, restaurant_owner
created_at	timestamp

Used by push notifications to find restaurant staff and by API endpoints for access control.

Relationship Map

users ──┬── orders (user_id)
        ├── addresses (user_id)
        ├── assignments (user_id)
        ├── device_tokens (user_uid)
        ├── push_subscriptions (user_uid)
        ├── saved_cards (user_auth_uid)
        └── credit_balances (user_uid)

restaurants ──┬── menu_items (restaurant_id)
              ├── restaurant_categories (restaurant_id)
              ├── menu_customization_groups (restaurant_id)
              ├── orders (restaurant_id)
              ├── assignments (restaurant_id)
              └── restaurant_logs (restaurant_id)

orders ──┬── order_items (order_id)
         ├── ratings (order_id)
         └── discount_codes (discount_code_id)

Config Table Keys

Key	Default	Purpose
exchange-rate	2600	HNL cents per 1 USD
service-radius-km	—	Max delivery radius
payment-fee-cash	0	Cash service fee %
payment-fee-card	2.5	Card service fee %
payment-fee-paypal	2.5	PayPal service fee %
app-version	1.0.3	Latest manager app version
app-download-url	https://ricoya.net/download	App download page

Database Functions (RPC)

Function	Purpose
create_order_with_discount	Atomic order creation + discount validation
claim_order_for_driver	Driver assignment with concurrency guard
grant_credit	Add credit to user ledger
redeem_credit	Deduct credit from user ledger

Supabase Projects

Environment	Ref	Account
Dev/Staging	elopzpfftytxncfyutju	sean@omelasai.com
Production	vxabwjsddplhnpqtnuef	admin@ricoya.net

Both databases have identical schemas. Changes must be applied to both.