Skip to main content

Data Model

This document defines the complete database schema for BookWish, organized by the 4-layer architecture model.

Core Entities (Layer 1)​

Users​

Supports both guest and registered accounts with tier-based feature access.

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  device_id VARCHAR(255),  -- For guest sessions
  email VARCHAR(255) UNIQUE,
  password_hash VARCHAR(255),
  display_name VARCHAR(100),
  username VARCHAR(50) UNIQUE,
  avatar_url TEXT,
  home_store_id UUID REFERENCES stores(id),
  tier VARCHAR(20) DEFAULT 'free',  -- guest|free|premium|bookstore
  is_guest BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Stores​

Independent bookstore profiles with location, branding, and integrations.

CREATE TABLE stores (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_user_id UUID NOT NULL REFERENCES users(id),
  name VARCHAR(200) NOT NULL,
  slug VARCHAR(100) UNIQUE NOT NULL,
  description TEXT,

  -- Location
  address_line1 VARCHAR(255),
  address_line2 VARCHAR(255),
  city VARCHAR(100),
  state VARCHAR(50),
  postal_code VARCHAR(20),
  country VARCHAR(2) DEFAULT 'US',
  latitude DECIMAL(10, 8),
  longitude DECIMAL(11, 8),

  -- Contact & Hours
  phone VARCHAR(20),
  email VARCHAR(255),
  hours JSONB,  -- {"mon": "9am-6pm", ...}

  -- Branding
  logo_url TEXT,
  banner_url TEXT,
  primary_color VARCHAR(7),

  -- Website
  website_enabled BOOLEAN DEFAULT false,
  custom_domain VARCHAR(255),

  -- Integrations
  square_merchant_id VARCHAR(255),
  square_access_token TEXT,  -- Encrypted
  inventory_source VARCHAR(20) DEFAULT 'native',  -- native|square

  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Books​

Global book catalog with external IDs for integrations.

CREATE TABLE books (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  isbn_13 VARCHAR(13) UNIQUE,
  isbn_10 VARCHAR(10),
  title VARCHAR(500) NOT NULL,
  subtitle VARCHAR(500),
  authors TEXT[],  -- Array for multiple authors
  publisher VARCHAR(255),
  published_date DATE,
  description TEXT,
  page_count INTEGER,
  categories TEXT[],
  cover_image_url TEXT,
  thumbnail_url TEXT,

  -- External IDs
  google_books_id VARCHAR(50),
  open_library_id VARCHAR(50),

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_books_isbn_13 ON books(isbn_13);
CREATE INDEX idx_books_title_gin ON books USING gin(to_tsvector('english', title));

Wishlists​

User-created collections of books with privacy controls.

CREATE TABLE wishlists (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100),
  is_primary BOOLEAN DEFAULT false,
  is_private BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(user_id, slug)
);

Wishlist Items​

Books added to wishlists with priority and status tracking.

CREATE TABLE wishlist_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  wishlist_id UUID NOT NULL REFERENCES wishlists(id) ON DELETE CASCADE,
  book_id UUID NOT NULL REFERENCES books(id),
  priority VARCHAR(20) DEFAULT 'normal',  -- high|normal|low
  status VARCHAR(20) DEFAULT 'wish',  -- wish|reading|finished
  added_at TIMESTAMPTZ DEFAULT NOW(),
  status_changed_at TIMESTAMPTZ,

  UNIQUE(wishlist_id, book_id)
);

Orders​

Order records supporting multiple fulfillment types and payment methods.

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_number VARCHAR(20) UNIQUE NOT NULL,
  user_id UUID REFERENCES users(id),  -- Nullable for guest orders
  store_id UUID REFERENCES stores(id),  -- Null = BookWish Direct
  is_bookwish_direct BOOLEAN DEFAULT false,

  -- Fulfillment
  fulfillment_type VARCHAR(20) NOT NULL,  -- pickup|ship|gift

  -- Status
  status VARCHAR(30) DEFAULT 'pending',
  -- pending|confirmed|processing|ready_for_pickup|shipped|delivered|cancelled

  -- Addresses
  shipping_address_id UUID REFERENCES addresses(id),
  billing_address_id UUID REFERENCES addresses(id),

  -- Gift info
  is_gift BOOLEAN DEFAULT false,
  gift_message TEXT,
  gift_recipient_name VARCHAR(100),
  gift_recipient_email VARCHAR(255),

  -- Pricing
  subtotal_cents INTEGER NOT NULL,
  tax_cents INTEGER DEFAULT 0,
  shipping_cents INTEGER DEFAULT 0,
  discount_cents INTEGER DEFAULT 0,
  trade_credit_applied_cents INTEGER DEFAULT 0,
  total_cents INTEGER NOT NULL,

  -- Payment
  stripe_payment_intent_id VARCHAR(255),
  payment_status VARCHAR(20) DEFAULT 'pending',

  -- Shipping
  shipping_carrier VARCHAR(50),
  tracking_number VARCHAR(100),
  easypost_shipment_id VARCHAR(100),

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Order Items​

Line items for orders.

CREATE TABLE order_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  book_id UUID NOT NULL REFERENCES books(id),
  inventory_id UUID REFERENCES inventory(id),
  quantity INTEGER DEFAULT 1,
  unit_price_cents INTEGER NOT NULL,
  is_used BOOLEAN DEFAULT false,

  created_at TIMESTAMPTZ DEFAULT NOW()
);

Addresses​

Shipping and billing addresses for users.

CREATE TABLE addresses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id),
  label VARCHAR(50),  -- home|work|other
  name VARCHAR(100) NOT NULL,
  line1 VARCHAR(255) NOT NULL,
  line2 VARCHAR(255),
  city VARCHAR(100) NOT NULL,
  state VARCHAR(50) NOT NULL,
  postal_code VARCHAR(20) NOT NULL,
  country VARCHAR(2) DEFAULT 'US',
  phone VARCHAR(20),
  is_default BOOLEAN DEFAULT false,

  created_at TIMESTAMPTZ DEFAULT NOW()
);

Store Operations Entities (Layer 2)​

Inventory​

Per-store book inventory with condition tracking and Square sync.

CREATE TABLE inventory (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  book_id UUID NOT NULL REFERENCES books(id),

  sku VARCHAR(100),
  square_item_id VARCHAR(100),

  quantity INTEGER DEFAULT 0,
  reserved_quantity INTEGER DEFAULT 0,  -- Held during checkout

  price_cents INTEGER NOT NULL,
  compare_at_price_cents INTEGER,  -- Original price for sales

  condition VARCHAR(20) DEFAULT 'new',  -- new|like_new|good|fair
  is_used BOOLEAN DEFAULT false,

  location VARCHAR(100),  -- Shelf/section in store

  last_synced_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(store_id, book_id, condition)
);

CREATE INDEX idx_inventory_store ON inventory(store_id);
CREATE INDEX idx_inventory_book ON inventory(book_id);

Trade Credit Accounts​

Per-customer, per-store credit balances.

CREATE TABLE trade_credit_accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  store_id UUID NOT NULL REFERENCES stores(id),
  balance_cents INTEGER DEFAULT 0,

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(user_id, store_id)
);

Trade Credit Transactions​

Ledger of credit and debit transactions.

CREATE TABLE trade_credit_transactions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id UUID NOT NULL REFERENCES trade_credit_accounts(id),
  amount_cents INTEGER NOT NULL,  -- Positive = credit, Negative = debit
  transaction_type VARCHAR(30) NOT NULL,  -- trade_in|purchase|adjustment
  reference_id UUID,  -- Order ID or trade-in record
  notes TEXT,

  created_at TIMESTAMPTZ DEFAULT NOW()
);

Trade-Ins​

Trade-in sessions for used book purchases.

CREATE TABLE trade_ins (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id),
  customer_user_id UUID REFERENCES users(id),
  staff_user_id UUID NOT NULL REFERENCES users(id),

  status VARCHAR(20) DEFAULT 'pending',  -- pending|accepted|rejected|completed

  total_credit_cents INTEGER DEFAULT 0,
  total_cash_cents INTEGER DEFAULT 0,
  payout_method VARCHAR(20),  -- credit|cash

  created_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ
);

Trade-In Items​

Individual books in a trade-in session.

CREATE TABLE trade_in_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  trade_in_id UUID NOT NULL REFERENCES trade_ins(id) ON DELETE CASCADE,
  book_id UUID NOT NULL REFERENCES books(id),

  condition VARCHAR(20) NOT NULL,
  offered_credit_cents INTEGER,
  offered_cash_cents INTEGER,

  -- BooksRun integration
  booksrun_offer_cents INTEGER,
  flip_to_booksrun BOOLEAN DEFAULT false,

  accepted BOOLEAN DEFAULT true,

  created_at TIMESTAMPTZ DEFAULT NOW()
);

Social Entities (Layer 3)​

Lines​

Short posts about books (the social unit of BookWish).

CREATE TABLE lines (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  author_user_id UUID NOT NULL REFERENCES users(id),
  book_id UUID NOT NULL REFERENCES books(id),

  -- Optional context
  store_id UUID REFERENCES stores(id),  -- For store posts
  club_id UUID REFERENCES clubs(id),
  challenge_id UUID REFERENCES challenges(id),

  content TEXT NOT NULL,

  -- Engagement counts (denormalized for performance)
  like_count INTEGER DEFAULT 0,
  reply_count INTEGER DEFAULT 0,

  -- Reply threading
  parent_line_id UUID REFERENCES lines(id),

  is_deleted BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_lines_author ON lines(author_user_id);
CREATE INDEX idx_lines_book ON lines(book_id);
CREATE INDEX idx_lines_created ON lines(created_at DESC);

Reviews​

Book reviews with ratings and optional program context.

CREATE TABLE reviews (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  book_id UUID NOT NULL REFERENCES books(id),

  rating INTEGER CHECK (rating >= 1 AND rating <= 5),
  content TEXT,

  -- Optional context
  club_id UUID REFERENCES clubs(id),
  challenge_id UUID REFERENCES challenges(id),

  like_count INTEGER DEFAULT 0,

  is_deleted BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(user_id, book_id)  -- One review per user per book
);

Notes (Scribbles)​

Private book notes visible only to the user.

CREATE TABLE notes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  book_id UUID NOT NULL REFERENCES books(id),

  content TEXT NOT NULL,

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_notes_user_book ON notes(user_id, book_id);

Follows​

User-to-user following relationships.

CREATE TABLE follows (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  follower_id UUID NOT NULL REFERENCES users(id),
  followed_id UUID NOT NULL REFERENCES users(id),

  created_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(follower_id, followed_id),
  CHECK (follower_id != followed_id)
);

Store Follows​

User-to-store following relationships.

CREATE TABLE store_follows (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  store_id UUID NOT NULL REFERENCES stores(id),

  created_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(user_id, store_id)
);

Likes​

Engagement on lines and reviews.

CREATE TABLE likes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),

  -- Polymorphic: either line_id or review_id
  line_id UUID REFERENCES lines(id) ON DELETE CASCADE,
  review_id UUID REFERENCES reviews(id) ON DELETE CASCADE,

  created_at TIMESTAMPTZ DEFAULT NOW(),

  CHECK (
    (line_id IS NOT NULL AND review_id IS NULL) OR
    (line_id IS NULL AND review_id IS NOT NULL)
  )
);

Programs Entities (Layer 4)​

Book Clubs​

Community reading groups.

CREATE TABLE clubs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  host_user_id UUID NOT NULL REFERENCES users(id),
  host_store_id UUID REFERENCES stores(id),  -- If store-hosted

  name VARCHAR(200) NOT NULL,
  slug VARCHAR(100),
  description TEXT,
  cover_image_url TEXT,

  is_public BOOLEAN DEFAULT true,
  max_members INTEGER,

  start_date DATE,
  end_date DATE,

  status VARCHAR(20) DEFAULT 'active',  -- draft|active|completed|archived

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Club Books​

Books selected for club reading.

CREATE TABLE club_books (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  club_id UUID NOT NULL REFERENCES clubs(id) ON DELETE CASCADE,
  book_id UUID NOT NULL REFERENCES books(id),

  reading_order INTEGER,
  discussion_start_date DATE,

  created_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(club_id, book_id)
);

Club Members​

Club membership tracking.

CREATE TABLE club_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  club_id UUID NOT NULL REFERENCES clubs(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id),

  role VARCHAR(20) DEFAULT 'member',  -- host|moderator|member
  joined_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(club_id, user_id)
);

Challenges​

Reading challenges with time-based goals.

CREATE TABLE challenges (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  host_store_id UUID REFERENCES stores(id),
  is_bookwish_hosted BOOLEAN DEFAULT false,

  name VARCHAR(200) NOT NULL,
  slug VARCHAR(100),
  description TEXT,
  cover_image_url TEXT,

  start_date DATE NOT NULL,
  end_date DATE NOT NULL,

  status VARCHAR(20) DEFAULT 'active',

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Challenge Books​

Books included in a challenge.

CREATE TABLE challenge_books (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  challenge_id UUID NOT NULL REFERENCES challenges(id) ON DELETE CASCADE,
  book_id UUID NOT NULL REFERENCES books(id),

  is_spotlight BOOLEAN DEFAULT false,

  created_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(challenge_id, book_id)
);

Challenge Participants​

Challenge participation tracking.

CREATE TABLE challenge_participants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  challenge_id UUID NOT NULL REFERENCES challenges(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id),

  joined_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(challenge_id, user_id)
);

Notifications & Feed Support​

Notifications​

In-app and push notifications.

CREATE TABLE notifications (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),

  type VARCHAR(50) NOT NULL,
  -- follow|like|reply|order_update|stock_alert|club_invite|challenge_start

  title VARCHAR(200),
  body TEXT,

  -- Polymorphic references
  actor_user_id UUID REFERENCES users(id),
  line_id UUID REFERENCES lines(id),
  review_id UUID REFERENCES reviews(id),
  order_id UUID REFERENCES orders(id),
  club_id UUID REFERENCES clubs(id),
  challenge_id UUID REFERENCES challenges(id),
  book_id UUID REFERENCES books(id),

  is_read BOOLEAN DEFAULT false,

  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_notifications_user ON notifications(user_id, created_at DESC);

Push Tokens​

Device push notification tokens.

CREATE TABLE push_tokens (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  token TEXT NOT NULL,
  platform VARCHAR(20) NOT NULL,  -- ios|android|web

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(token)
);

Order Notification Digest​

Queue for daily digest emails to store owners.

-- Order digest queue (for stores with daily_digest preference)
CREATE TABLE order_digest_queue (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  is_special_order BOOLEAN DEFAULT false,
  processed BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- BookWish operations digest queue (always daily)
CREATE TABLE bookwish_ops_digest_queue (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  processed BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Feed Query Strategy​

The Share tab feed uses fan-out-on-read with caching:

// Feed query filters
interface FeedQuery {
  scope: 'all' | 'following' | 'local' | 'reviews' | 'challenges';
  cursor?: string;
  limit?: number;
}

// Feed composition (pseudo-SQL)
SELECT lines.*, users.*, books.*
FROM lines
JOIN users ON lines.author_user_id = users.id
JOIN books ON lines.book_id = books.id
WHERE (
  -- Following filter
  (scope = 'following' AND author_user_id IN (SELECT followed_id FROM follows WHERE follower_id = ?))
  OR
  -- Local filter (home store + nearby)
  (scope = 'local' AND store_id = ?)
  OR
  -- All: following + home store + clubs + system content
  (scope = 'all' AND (
    author_user_id IN (SELECT followed_id FROM follows WHERE follower_id = ?)
    OR store_id = ?
    OR club_id IN (SELECT club_id FROM club_members WHERE user_id = ?)
  ))
)
ORDER BY created_at DESC
LIMIT 20;