Skip to main content

Support Tools

Administrative tools and utilities for supporting users and troubleshooting platform issues.

Overview​

Support tools help administrators:

  • Look up user orders and transactions
  • Process refunds and cancellations
  • Export user data
  • Debug issues
  • Assist with account recovery
  • Investigate problems

Order Management​

Order Lookup​

Find Order by ID:

SELECT o.*,
       u.username, u.email,
       s.name as store_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN stores s ON o.store_id = s.id
WHERE o.id = 'order_id';

Find User's Orders:

SELECT o.id, o.created_at, o.status,
       o.total_amount, o.payment_method,
       s.name as store_name
FROM orders o
JOIN stores s ON o.store_id = s.id
WHERE o.user_id = 'user_id'
ORDER BY o.created_at DESC;

Find Store's Orders:

SELECT o.id, o.created_at, o.status,
       o.total_amount,
       u.username, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.store_id = 'store_id'
ORDER BY o.created_at DESC;

Recent Orders:

SELECT o.id, o.created_at, o.status,
       u.username,
       s.name as store_name,
       o.total_amount
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN stores s ON o.store_id = s.id
WHERE o.created_at >= NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC;

Order Details​

Complete Order Information:

SELECT o.*,
       json_build_object(
         'username', u.username,
         'email', u.email,
         'displayName', u.display_name
       ) as user,
       json_build_object(
         'name', s.name,
         'email', s.email,
         'phone', s.phone
       ) as store
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN stores s ON o.store_id = s.id
WHERE o.id = 'order_id';

Order Items:

SELECT oi.*,
       i.title, i.author, i.isbn,
       i.condition, i.price
FROM order_items oi
JOIN inventory_items i ON oi.inventory_item_id = i.id
WHERE oi.order_id = 'order_id';

Order Actions​

Cancel Order:

UPDATE orders
SET status = 'cancelled',
    updated_at = NOW()
WHERE id = 'order_id';

Update Order Status:

UPDATE orders
SET status = 'completed',
    fulfilled_at = NOW(),
    updated_at = NOW()
WHERE id = 'order_id';

Add Admin Note:

UPDATE orders
SET notes = CONCAT(COALESCE(notes, ''), '\n[Admin ', NOW(), '] ', 'Note text'),
    updated_at = NOW()
WHERE id = 'order_id';

Refund Processing​

Order Refunds​

Process Refund:

  1. Verify Refund Eligibility

    • Check order status
    • Verify payment processed
    • Review refund policy
    • Confirm reason

  2. Issue Refund in Payment System

    • Square: Use Square dashboard or API
    • Stripe: Use Stripe dashboard or API
    • Document transaction ID

  3. Update Order Status:

UPDATE orders
SET status = 'refunded',
    refunded_at = NOW(),
    refund_amount = [amount],
    refund_reason = '[reason]',
    updated_at = NOW()
WHERE id = 'order_id';
  1. Notify User and Store
    • Email user confirming refund
    • Notify store of refund
    • Document communication

Partial Refunds​

Refund Specific Items:

  1. Calculate partial refund amount
  2. Process in payment system
  3. Update order record:
UPDATE orders
SET refund_amount = [partial_amount],
    refund_reason = 'Partial refund: [items]',
    updated_at = NOW()
WHERE id = 'order_id';

Trade Credit Refunds​

Issue Store Credit Instead:

-- Add to user's trade credit balance
INSERT INTO trade_credits (user_id, store_id, amount, source, created_at)
VALUES ('user_id', 'store_id', [amount], 'refund', NOW());

User Account Support​

Account Recovery​

Reset Password:

-- Clear password to force reset
UPDATE users
SET password_hash = NULL
WHERE id = 'user_id';

Send password reset email via backend service.

Update Email:

UPDATE users
SET email = 'new_email@example.com',
    updated_at = NOW()
WHERE id = 'user_id';

Unlock Account:

-- If account was suspended/locked
UPDATE users
SET tier = 'free',  -- Or previous tier
    updated_at = NOW()
WHERE id = 'user_id';

Account Merging​

Merge Duplicate Accounts:

If user created multiple accounts by mistake:

  1. Identify Primary Account

    • Most recent activity
    • Most content
    • Preferred by user

  2. Transfer Content:

-- Transfer lines
UPDATE lines SET user_id = 'primary_user_id' WHERE user_id = 'duplicate_user_id';

-- Transfer reviews
UPDATE reviews SET user_id = 'primary_user_id' WHERE user_id = 'duplicate_user_id';

-- Transfer wishlists
UPDATE wishlists SET user_id = 'primary_user_id' WHERE user_id = 'duplicate_user_id';

-- Transfer follows
UPDATE follows SET follower_id = 'primary_user_id' WHERE follower_id = 'duplicate_user_id';
  1. Delete Duplicate:
DELETE FROM users WHERE id = 'duplicate_user_id';

Account Deletion​

GDPR/User Request:

  1. Verify Request

    • Confirm user identity
    • Verify deletion request
    • Document request

  2. Export User Data (if requested):

    • See Data Export section below

  3. Delete Account:

-- This cascades to related records
DELETE FROM users WHERE id = 'user_id';
  1. Confirm Completion:
    • Email confirmation
    • Document in records
    • Remove from external systems

Data Export​

User Data Export​

Complete User Data:

-- User profile
SELECT * FROM users WHERE id = 'user_id';

-- User's lines
SELECT * FROM lines WHERE user_id = 'user_id';

-- User's reviews
SELECT * FROM reviews WHERE user_id = 'user_id';

-- User's wishlists
SELECT w.*,
       json_agg(wi.*) as items
FROM wishlists w
LEFT JOIN wishlist_items wi ON w.id = wi.wishlist_id
WHERE w.user_id = 'user_id'
GROUP BY w.id;

-- User's orders
SELECT o.*,
       json_agg(oi.*) as items
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 'user_id'
GROUP BY o.id;

-- User's follows
SELECT * FROM follows WHERE follower_id = 'user_id';

Export Format:

Provide data in JSON or CSV format:

// Export service
async function exportUserData(userId: string): Promise<UserDataExport> {
  const user = await db.users.findUnique({ where: { id: userId } });
  const lines = await db.lines.findMany({ where: { userId } });
  const reviews = await db.reviews.findMany({ where: { userId } });
  const wishlists = await db.wishlists.findMany({
    where: { userId },
    include: { items: true }
  });
  const orders = await db.orders.findMany({
    where: { userId },
    include: { items: true }
  });

  return {
    user,
    lines,
    reviews,
    wishlists,
    orders,
    exportedAt: new Date(),
  };
}

Store Data Export​

Complete Store Data:

-- Store profile
SELECT * FROM stores WHERE id = 'store_id';

-- Store inventory
SELECT * FROM inventory_items WHERE store_id = 'store_id';

-- Store orders
SELECT o.*,
       json_agg(oi.*) as items
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.store_id = 'store_id'
GROUP BY o.id;

-- Store followers
SELECT * FROM follows WHERE followed_store_id = 'store_id';

Platform Data Export​

Analytics Export:

-- User growth data
COPY (
  SELECT DATE(created_at) as date,
         tier,
         COUNT(*) as count
  FROM users
  WHERE created_at >= '2025-01-01'
  GROUP BY DATE(created_at), tier
  ORDER BY date, tier
) TO '/tmp/user_growth.csv' CSV HEADER;

-- Order volume data
COPY (
  SELECT DATE(created_at) as date,
         COUNT(*) as orders,
         SUM(total_amount) as revenue
  FROM orders
  WHERE created_at >= '2025-01-01'
  GROUP BY DATE(created_at)
  ORDER BY date
) TO '/tmp/order_volume.csv' CSV HEADER;

Debugging Tools​

Log Viewing​

Application Logs:

Access backend logs to investigate issues:

# Recent errors
grep ERROR /var/log/bookwish/app.log | tail -n 100

# Specific user's activity
grep "user_id:abc123" /var/log/bookwish/app.log

# API endpoint errors
grep "POST /api/orders" /var/log/bookwish/app.log | grep ERROR

Database Query Logs:

# Slow queries
cat /var/log/postgresql/postgresql.log | grep "duration" | sort -rn

Error Investigation​

Find Recent Errors:

-- If error logging table exists
SELECT * FROM error_logs
WHERE created_at >= NOW() - INTERVAL '24 hours'
ORDER BY created_at DESC
LIMIT 50;

User-Specific Errors:

SELECT * FROM error_logs
WHERE user_id = 'user_id'
  AND created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;

Performance Monitoring​

Slow Queries:

-- Enable pg_stat_statements extension
SELECT query,
       calls,
       total_time,
       mean_time,
       max_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 20;

Database Size:

SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Connection Count:

SELECT count(*) as connections FROM pg_stat_activity;

Inventory Management​

Inventory Lookup​

Find Inventory Item:

SELECT i.*,
       s.name as store_name,
       b.title as book_title
FROM inventory_items i
JOIN stores s ON i.store_id = s.id
LEFT JOIN books b ON i.book_id = b.id
WHERE i.id = 'inventory_id';

Find by ISBN:

SELECT i.*,
       s.name as store_name
FROM inventory_items i
JOIN stores s ON i.store_id = s.id
WHERE i.isbn = '9781234567890'
  AND i.status = 'available';

Inventory Actions​

Mark as Sold:

UPDATE inventory_items
SET status = 'sold',
    updated_at = NOW()
WHERE id = 'inventory_id';

Return to Available:

UPDATE inventory_items
SET status = 'available',
    updated_at = NOW()
WHERE id = 'inventory_id';

Delete Inventory Item:

DELETE FROM inventory_items
WHERE id = 'inventory_id';

Payment Investigation​

Payment Lookup​

Square Transactions:

Access Square dashboard to:

  • View transaction details
  • Check payment status
  • Issue refunds
  • Investigate disputes

Stripe Subscriptions:

Access Stripe dashboard to:

  • View subscription status
  • Check payment methods
  • Handle failed payments
  • Process refunds

Payment Issues​

Failed Payment:

  1. Check payment gateway for error
  2. Review user's payment method
  3. Check for fraud flags
  4. Attempt retry (if appropriate)
  5. Contact user for resolution

Disputed Charge:

  1. Review dispute details in gateway
  2. Gather evidence (order details, communication)
  3. Respond to dispute with evidence
  4. Document outcome
  5. Update internal records

Reporting Tools​

User Reports​

User Activity Report:

SELECT u.id, u.username, u.email,
       COUNT(DISTINCT l.id) as lines_count,
       COUNT(DISTINCT r.id) as reviews_count,
       COUNT(DISTINCT o.id) as orders_count
FROM users u
LEFT JOIN lines l ON u.id = l.user_id
LEFT JOIN reviews r ON u.id = r.user_id
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.tier != 'guest'
GROUP BY u.id, u.username, u.email
ORDER BY orders_count DESC;

Store Reports​

Store Performance Report:

SELECT s.name,
       COUNT(DISTINCT i.id) as inventory_count,
       COUNT(DISTINCT o.id) as order_count,
       SUM(o.total_amount) as revenue,
       COUNT(DISTINCT f.follower_id) as followers
FROM stores s
LEFT JOIN inventory_items i ON s.id = i.store_id
LEFT JOIN orders o ON s.id = o.store_id
  AND o.created_at >= NOW() - INTERVAL '30 days'
LEFT JOIN follows f ON s.id = f.followed_store_id
WHERE s.is_active = true
GROUP BY s.id, s.name
ORDER BY order_count DESC;

Platform Reports​

Daily Activity Report:

SELECT DATE(created_at) as date,
       'users' as type,
       COUNT(*) as count
FROM users
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY DATE(created_at)

UNION ALL

SELECT DATE(created_at) as date,
       'orders' as type,
       COUNT(*) as count
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY DATE(created_at)

ORDER BY date DESC, type;

Admin Utilities​

Bulk Operations​

Bulk User Tier Update:

-- Upgrade multiple users to premium
UPDATE users
SET tier = 'premium'
WHERE id IN ('user1', 'user2', 'user3');

Bulk Content Hide:

-- Hide multiple reported items
UPDATE lines
SET moderation_status = 'hidden'
WHERE id IN (
  SELECT reported_line_id
  FROM reports
  WHERE status = 'pending'
    AND reason = 'spam'
);

Data Cleanup​

Remove Old Guest Accounts:

-- Delete guests inactive for 90 days
DELETE FROM users
WHERE is_guest = true
  AND created_at < NOW() - INTERVAL '90 days'
  AND id NOT IN (
    SELECT DISTINCT user_id FROM lines
    UNION
    SELECT DISTINCT user_id FROM reviews
    UNION
    SELECT DISTINCT user_id FROM orders
  );

Clean Up Expired Data:

-- Remove old pending reports
UPDATE reports
SET status = 'dismissed',
    resolution_notes = 'Auto-dismissed after 90 days'
WHERE status = 'pending'
  AND created_at < NOW() - INTERVAL '90 days';

Best Practices​

Security​

Protect User Data:

  • Log all support actions
  • Verify user identity before changes
  • Use secure channels for sensitive info
  • Follow principle of least privilege
  • Document all access to user data

Documentation​

Record Keeping:

  • Document all support actions
  • Note reasoning for decisions
  • Track communication with users
  • Maintain audit trail
  • Review actions periodically

Communication​

User Support:

  • Respond promptly to requests
  • Be clear and helpful
  • Provide steps to resolve
  • Follow up to confirm resolution
  • Document interactions

Escalation​

When to Escalate:

  • Legal issues
  • Security concerns
  • Complex technical problems
  • High-value disputes
  • Unclear policy application

Escalation Process:

  1. Document the issue thoroughly
  2. Note what has been tried
  3. Explain why escalation needed
  4. Provide relevant data
  5. Follow up on resolution

Admin API Endpoints​

Proposed Support Endpoints​

Order Lookup:

GET /api/admin/orders/:orderId
GET /api/admin/users/:userId/orders
GET /api/admin/stores/:storeId/orders
Authorization: Bearer <admin_token>

Process Refund:

POST /api/admin/orders/:orderId/refund
Authorization: Bearer <admin_token>
Content-Type: application/json

{
  "amount": 29.99,
  "reason": "Customer request"
}

Export User Data:

GET /api/admin/users/:userId/export
Authorization: Bearer <admin_token>

Bulk Operations:

POST /api/admin/bulk/update-tiers
Authorization: Bearer <admin_token>
Content-Type: application/json

{
  "userIds": ["user1", "user2"],
  "tier": "premium"
}

Next Steps​