Wireframes, database schema & tech stack.

-- ───── B2C members + auth ───── CREATE TABLE users ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), line_user_id text UNIQUE, -- LIFF sub claim email text UNIQUE, phone text, display_name text, avatar_url text, tier text DEFAULT 'soft', -- soft|warm|gold points integer DEFAULT 0, locale text DEFAULT 'th', created_at timestamptz DEFAULT now(), last_login timestamptz ); CREATE TABLE addresses ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), user_id uuid REFERENCES users(id) ON DELETE CASCADE, label text, -- home, office recipient text, phone text, line1 text, district text, city text, zip text, is_default boolean DEFAULT false ); -- ───── catalog (shared by web + mini-app) ───── CREATE TABLE products ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), slug text UNIQUE, name_th text NOT NULL, name_en text NOT NULL, desc_th text, desc_en text, category text, -- pillow|mattress|sheet|blanket|topper material text, base_price integer, -- THB compare_at integer, active boolean DEFAULT true, created_at timestamptz DEFAULT now() ); CREATE TABLE product_variants ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), product_id uuid REFERENCES products(id) ON DELETE CASCADE, sku text UNIQUE, size text, color text, price integer -- override base if set ); CREATE TABLE inventory ( variant_id uuid PRIMARY KEY REFERENCES product_variants(id), stock integer DEFAULT 0, -- single source of truth reserved integer DEFAULT 0, -- in-flight orders updated_at timestamptz DEFAULT now() ); -- ───── B2C orders ───── CREATE TABLE orders ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), order_no text UNIQUE NOT NULL, -- BB-XXXXXX user_id uuid REFERENCES users(id), channel text, -- web | line_mini status text DEFAULT 'pending', -- pending|paid|shipped|delivered|refunded subtotal integer, shipping integer, total integer, payment text, -- cod|promptpay|card|line_pay address_id uuid REFERENCES addresses(id), notes text, created_at timestamptz DEFAULT now() ); CREATE TABLE order_items ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), order_id uuid REFERENCES orders(id) ON DELETE CASCADE, variant_id uuid REFERENCES product_variants(id), qty integer, unit_price integer ); -- ───── loyalty + reviews ───── CREATE TABLE loyalty_ledger ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), user_id uuid REFERENCES users(id), delta integer, -- + earn / - redeem reason text, -- order:BB-... / reward:R-... ref_id uuid, created_at timestamptz DEFAULT now() ); CREATE TABLE reviews ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), product_id uuid REFERENCES products(id), user_id uuid REFERENCES users(id), order_id uuid REFERENCES orders(id), -- verified purchase stars smallint CHECK (stars BETWEEN 1 AND 5), body_th text, body_en text, photos text[], -- UGC image URLs status text DEFAULT 'pending', -- pending|approved|rejected created_at timestamptz DEFAULT now() ); -- ───── B2B lead gen ───── CREATE TABLE b2b_leads ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), company text NOT NULL, contact text, email text, phone text, biz_type text, -- hotel|dorm|retail|other qty_band text, -- 50-99|100-499|... products uuid[], -- product IDs of interest message text, status text DEFAULT 'new', -- new|contacted|quoted|won|lost assigned_to uuid, -- sales rep id source text, -- web|line|referral created_at timestamptz DEFAULT now() ); CREATE TABLE b2b_accounts ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), lead_id uuid REFERENCES b2b_leads(id), company text NOT NULL, tax_id text, credit_terms text, -- net30, prepaid primary_rep uuid, active boolean DEFAULT true ); CREATE TABLE price_tiers ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), product_id uuid REFERENCES products(id), min_qty integer, max_qty integer, unit_price integer ); CREATE TABLE quotes ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), quote_no text UNIQUE, lead_id uuid REFERENCES b2b_leads(id), account_id uuid REFERENCES b2b_accounts(id), total integer, pdf_url text, valid_until date, status text DEFAULT 'draft', -- draft|sent|accepted|expired created_at timestamptz DEFAULT now() );