Store Accounts

Manage bookstore owner accounts and their associated store records on BookWish.

Overview

Store accounts are user accounts with the bookstore tier that have permission to:

• Create and manage a Store record
• Add and manage inventory
• Process sales through point-of-sale
• Accept and fulfill orders
• Manage store settings and appearance

Store Account Structure

User Account (Tier: Bookstore)

Store owners have a regular user account with elevated tier:

{
  id: string;
  email: string;
  username: string;
  displayName: string;
  tier: 'bookstore',        // Key field
  // ... other user fields
}

Store Record

Each store owner can create a Store:

{
  id: string;
  ownerId: string;          // Links to user.id
  name: string;
  slug: string;             // URL-friendly store identifier
  description?: string;

  // Location
  street?: string;
  city?: string;
  state?: string;
  zip?: string;
  country?: string;

  // Contact
  email?: string;
  phone?: string;
  website?: string;

  // Branding
  logoUrl?: string;
  bannerUrl?: string;

  // Settings
  isActive: boolean;
  allowsPickup: boolean;
  allowsShipping: boolean;

  // Payment Integration (Square)
  squareLocationId?: string;
  squareAccessToken?: string;     // Encrypted
  squareMerchantId?: string;

  // Custom Domain
  customDomain?: string;
  customDomainVerified: boolean;

  createdAt: DateTime;
  updatedAt: DateTime;
}

Finding Store Accounts

All Store Owners

Database Query:

SELECT u.*, s.name as store_name, s.is_active
FROM users u
LEFT JOIN stores s ON u.id = s.owner_id
WHERE u.tier = 'bookstore'
ORDER BY u.created_at DESC;

Active Stores

SELECT s.*, u.username, u.email
FROM stores s
JOIN users u ON s.owner_id = u.id
WHERE s.is_active = true
ORDER BY s.created_at DESC;

Inactive Stores

SELECT s.*, u.username, u.email
FROM stores s
JOIN users u ON s.owner_id = u.id
WHERE s.is_active = false
ORDER BY s.updated_at DESC;

Stores by Location

SELECT * FROM stores
WHERE city = 'Seattle'
  AND state = 'WA'
  AND is_active = true;

Store Account Management

Creating Store Account

Step 1: Create/Upgrade User to Bookstore Tier

-- New user
INSERT INTO users (id, email, username, tier, is_guest)
VALUES (gen_random_uuid(), 'owner@bookstore.com', 'bookstore_user', 'bookstore', false);

-- Upgrade existing user
UPDATE users
SET tier = 'bookstore'
WHERE id = 'existing_user_id';

Step 2: Store Owner Creates Store Record

Store owners typically create their own store through the app/API:

POST /api/stores
{
  name: "Happy Pages Bookstore",
  slug: "happy-pages-seattle",
  description: "Independent bookstore in Seattle",
  city: "Seattle",
  state: "WA",
  email: "info@happypages.com"
}

Step 3: Verify and Activate

See Store Verification for verification process.

Viewing Store Details

Complete Store Profile:

SELECT s.*,
       u.username, u.email, u.display_name,
       COUNT(DISTINCT i.id) as inventory_count,
       COUNT(DISTINCT o.id) as order_count
FROM stores s
JOIN users u ON s.owner_id = u.id
LEFT JOIN inventory_items i ON s.id = i.store_id
LEFT JOIN orders o ON s.id = o.store_id
WHERE s.id = 'store_id'
GROUP BY s.id, u.username, u.email, u.display_name;

Store Inventory:

SELECT * FROM inventory_items
WHERE store_id = 'store_id'
ORDER BY created_at DESC;

Store Orders:

SELECT * FROM orders
WHERE store_id = 'store_id'
ORDER BY created_at DESC;

Store Account Actions

Activate Store

Enable Store for Business:

UPDATE stores
SET is_active = true
WHERE id = 'store_id';

Requirements Before Activation:

• Business verified
• Payment processing configured
• Store details complete
• Owner tier set to 'bookstore'

Deactivate Store

Temporarily Disable:

UPDATE stores
SET is_active = false
WHERE id = 'store_id';

When to Deactivate:

• Vacation/temporary closure
• Policy violation (suspension)
• Payment processing issues
• Owner request
• Pending investigation

Effects:

• Inventory hidden from search
• No new orders accepted
• Store page shows inactive
• Owner retains backend access

Change Store Owner

Transfer Ownership:

-- Step 1: Update store owner
UPDATE stores
SET owner_id = 'new_owner_user_id'
WHERE id = 'store_id';

-- Step 2: Ensure new owner has bookstore tier
UPDATE users
SET tier = 'bookstore'
WHERE id = 'new_owner_user_id';

-- Step 3: Optionally downgrade old owner
UPDATE users
SET tier = 'free'  -- or keep as 'bookstore' if they'll open new store
WHERE id = 'old_owner_user_id';

When to Transfer:

• Business sale/acquisition
• Ownership change
• Account recovery
• Legal requirement

Delete Store

Permanent Deletion:

-- This will cascade delete inventory and related records
-- BE CAREFUL - PERMANENT ACTION
DELETE FROM stores WHERE id = 'store_id';

-- Optionally downgrade owner
UPDATE users
SET tier = 'free'
WHERE id = 'owner_user_id';

When to Delete:

• Business permanently closed
• Owner request
• Fraudulent store
• Irrecoverable violations

Store Settings Management

Update Store Information

Change Store Name:

UPDATE stores
SET name = 'New Store Name'
WHERE id = 'store_id';

Update Location:

UPDATE stores
SET street = '123 Main St',
    city = 'Seattle',
    state = 'WA',
    zip = '98101'
WHERE id = 'store_id';

Update Contact Info:

UPDATE stores
SET email = 'new@email.com',
    phone = '555-1234',
    website = 'https://newsite.com'
WHERE id = 'store_id';

Fulfillment Options

Enable Pickup:

UPDATE stores
SET allows_pickup = true
WHERE id = 'store_id';

Enable Shipping:

UPDATE stores
SET allows_shipping = true
WHERE id = 'store_id';

Custom Domain

Enable Custom Domain:

UPDATE stores
SET custom_domain = 'shop.mybookstore.com',
    custom_domain_verified = false
WHERE id = 'store_id';

Verify Custom Domain:

UPDATE stores
SET custom_domain_verified = true
WHERE id = 'store_id';

Requirements:

• DNS records configured
• SSL certificate valid
• Domain ownership verified

Payment Processing

Square Integration

BookWish uses Square for payment processing:

{
  squareLocationId: string;      // Square location ID
  squareAccessToken: string;     // Encrypted access token
  squareMerchantId: string;      // Merchant identifier
}

Managing Square Connection

View Square Status:

SELECT id, name,
       square_location_id IS NOT NULL as has_square,
       square_merchant_id
FROM stores
WHERE id = 'store_id';

Remove Square Connection:

UPDATE stores
SET square_location_id = NULL,
    square_access_token = NULL,
    square_merchant_id = NULL
WHERE id = 'store_id';

When to Remove:

• Store requests disconnection
• Square account suspended
• Security concerns
• Migration to new Square account

Payment Issues

Common Problems:

• Expired access tokens
• Square account suspended
• Location misconfigured
• Failed transactions

Resolution Steps:

1. Contact store owner
2. Verify Square account status
3. Request token refresh
4. Test payment flow
5. Document resolution

Store Performance Monitoring

Inventory Metrics

Inventory Health:

SELECT s.name,
       COUNT(i.id) as total_items,
       COUNT(CASE WHEN i.status = 'available' THEN 1 END) as available_items,
       COUNT(CASE WHEN i.status = 'sold' THEN 1 END) as sold_items
FROM stores s
LEFT JOIN inventory_items i ON s.id = i.store_id
WHERE s.id = 'store_id'
GROUP BY s.name;

Order Metrics

Order Performance:

SELECT s.name,
       COUNT(o.id) as total_orders,
       COUNT(CASE WHEN o.status = 'completed' THEN 1 END) as completed,
       COUNT(CASE WHEN o.status = 'cancelled' THEN 1 END) as cancelled,
       AVG(o.total_amount) as avg_order_value
FROM stores s
LEFT JOIN orders o ON s.id = o.store_id
WHERE s.id = 'store_id'
  AND o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY s.name;

Engagement Metrics

Store Followers:

SELECT COUNT(*) as follower_count
FROM follows
WHERE followed_store_id = 'store_id';

Store Activity:

SELECT
  COUNT(DISTINCT i.id) as new_inventory,
  COUNT(DISTINCT o.id) as new_orders
FROM stores s
LEFT JOIN inventory_items i ON s.id = i.store_id
  AND i.created_at >= NOW() - INTERVAL '7 days'
LEFT JOIN orders o ON s.id = o.store_id
  AND o.created_at >= NOW() - INTERVAL '7 days'
WHERE s.id = 'store_id';

Store Quality Management

Quality Indicators

Healthy Store:

• Active status
• Regular inventory updates
• Timely order fulfillment
• Positive customer feedback
• Complete store information

Warning Signs:

• No inventory added
• High order cancellation rate
• Customer complaints
• Incomplete setup
• Payment issues

Intervention Actions

Warning Email:

• Identify specific concerns
• Request improvement plan
• Set timeline for resolution
• Offer support resources

Temporary Suspension:

• Deactivate store
• Explain reason clearly
• Outline reinstatement steps
• Set review timeline

Permanent Closure:

• Close store permanently
• Downgrade user tier
• Archive store data
• Handle pending orders

Store Owner Support

Common Store Owner Issues

Setup Problems:

• Payment integration difficulties
• Inventory upload questions
• Settings configuration
• Custom domain setup

Operational Issues:

• Order fulfillment questions
• Shipping integration
• Inventory management
• Customer communication

Technical Issues:

• Login problems
• Payment processing errors
• Integration failures
• Performance issues

Support Process

1. Receive Support Request

   • Via email, in-app, or direct contact
   • Identify store and owner
   • Understand the issue

2. Investigate

   • Review store settings
   • Check error logs
   • Test functionality
   • Identify root cause

3. Resolve

   • Fix issue if admin-level
   • Guide owner through solution
   • Escalate to engineering if needed
   • Document resolution

4. Follow Up

   • Confirm issue resolved
   • Provide additional resources
   • Update documentation
   • Track common issues

Store Analytics

Platform-Wide Metrics

Total Stores:

SELECT
  COUNT(*) as total_stores,
  COUNT(CASE WHEN is_active THEN 1 END) as active_stores,
  COUNT(CASE WHEN created_at >= NOW() - INTERVAL '30 days' THEN 1 END) as new_stores
FROM stores;

Geographic Distribution:

SELECT state, COUNT(*) as store_count
FROM stores
WHERE is_active = true
  AND state IS NOT NULL
GROUP BY state
ORDER BY store_count DESC;

Top Performing Stores:

SELECT s.name, s.city, s.state,
       COUNT(o.id) as order_count,
       SUM(o.total_amount) as total_revenue
FROM stores s
LEFT JOIN orders o ON s.id = o.store_id
  AND o.created_at >= NOW() - INTERVAL '30 days'
WHERE s.is_active = true
GROUP BY s.id, s.name, s.city, s.state
ORDER BY order_count DESC
LIMIT 10;

Best Practices

Store Onboarding

Smooth Onboarding:

• Welcome email with resources
• Setup checklist/guide
• Video tutorials
• Responsive support
• Community connection

Fair Treatment

Consistent Management:

• Apply policies uniformly
• Support all stores equally
• Document all actions
• Allow appeals
• Communicate clearly

Growth Support

Help Stores Succeed:

• Share best practices
• Provide marketing tips
• Highlight successful stores
• Offer performance insights
• Foster store community

Platform Health

Maintain Quality:

• Monitor store performance
• Address issues proactively
• Remove bad actors
• Protect customers
• Maintain trust

Next Steps

• User Management
• Subscriptions
• Store Verification