feat: Book exchange counter-offer flow has no timeout — a pending exchange offer can sit unanswered indefinitely, blocking the book from being offered to others

[divyanshim27]

🚀 Problem Statement

BookCAT's Book Exchange feature allows users to propose book swaps with friends. The documented flow includes: "Counter-offer and negotiation flow" and "Exchange status tracking."

However, exchange offers have no expiry mechanism. Once a user sends an exchange offer, it sits in pending status indefinitely until the recipient manually accepts or declines. This means:

1. A user who sends an exchange offer and gets no response cannot offer the same book to another friend — it appears "in negotiation"
2. Users who go inactive leave pending offers permanently blocking books
3. The exchange dashboard fills with stale "pending" offers that will never be resolved

Proposed Fix

1. Add expires_at to the exchange_offers table

-- New migration file: 017_exchange_offer_expiry.sql
ALTER TABLE exchange_offers
ADD COLUMN expires_at TIMESTAMPTZ DEFAULT (NOW() + INTERVAL '7 days');

CREATE INDEX idx_exchange_offers_expires_at
ON exchange_offers (expires_at)
WHERE status = 'pending';

2. Add a Supabase Edge Function or pg_cron job to auto-expire stale offers

-- Run via pg_cron (already enabled per migrations 006, 008)
SELECT cron.schedule(
  'expire-stale-exchange-offers',
  '0 2 * * *',   -- Run daily at 2 AM
  $$
    UPDATE exchange_offers
    SET status = 'expired'
    WHERE status = 'pending'
    AND expires_at < NOW();
  $$
);

3. Show expiry countdown in the frontend exchange dashboard

// In Exchange.jsx — show time remaining on pending offers
function ExpiryBadge({ expiresAt }) {
  const daysLeft = Math.ceil((new Date(expiresAt) - new Date()) / (1000 * 60 * 60 * 24));
  if (daysLeft <= 0) return <span className="text-red-500">Expired</span>;
  if (daysLeft <= 2) return <span className="text-orange-500">Expires in {daysLeft}d</span>;
  return <span className="text-gray-400">Expires in {daysLeft}d</span>;
}

4. Notify offer sender when an offer is about to expire (1 day before)

Add a Realtime notification when expires_at is within 24 hours and status is still pending.

Files to Create/Modify

File	Change
supabase/migrations/017_exchange_offer_expiry.sql	Add expires_at column + index + cron job
apps/web/src/pages/Exchange.jsx	Show expiry countdown, filter expired offers

📊 Impact

Without expiry, the exchange system degrades over time into a graveyard of unresolved offers. 7-day auto-expiry ensures books can be circulated to other friends and keeps the exchange dashboard actionable.

Suggested labels: enhancement, backend, frontend, gssoc:level2

I would like to work on this. Could you please assign it to me?

[divyanshim27]

Hey @AnirbansarkarS ,
Please assign it to me under GSSOC 2026.
/claim /assign /claim-assignment