Refactor user filtering to use database-level constraints

[mcull]

Background

Currently, user status filtering (status: 'active') is implemented inconsistently across the API. Some endpoints properly filter for active users at the query level, while others rely on runtime filtering after data is fetched.

Current State

✅ Properly Filtered Endpoints:

• /api/collections/[id]/members - Has user: { status: 'active' } filter
• /api/collections/[id] - Has owner: { status: 'active' } and user: { status: 'active' } filters

⚠️ Runtime-Filtered Endpoints (Fixed with Band-aid):

• /api/borrow-requests - Now filters borrower/lender post-query
• /api/items/[id] - Now filters owner/borrower/lender post-query
• /api/user/items - Now filters borrower post-query
• /api/borrow-requests/[id] - Now filters borrower/lender post-query

Problem

Prisma doesn't support where clauses directly on include relations for users:

// ❌ This doesn't work:
borrower: {
  where: { status: 'active' },  // Error: 'where' does not exist
  select: { id: true, name: true }
}

This forces us to either:

1. Select the status field and filter post-query (current band-aid solution)
2. Make separate queries for user validation
3. Use more complex query patterns

Long-term Solutions

Option 1: Database Views

Create database views that only show active users:

• active_users view with WHERE status = 'active'
• Update Prisma schema to reference views where appropriate
• Maintains referential integrity at DB level

Option 2: Prisma Query Refactoring

Restructure queries to validate users separately:

// Validate users first
const [borrower, lender] = await Promise.all([
  db.user.findFirst({ where: { id: borrowerId, status: 'active' }}),
  db.user.findFirst({ where: { id: lenderId, status: 'active' }})
]);

if (!borrower || !lender) {
  return NextResponse.json({ error: 'User not found' }, { status: 404 });
}

// Then fetch the main data
const borrowRequest = await db.borrowRequest.findUnique({
  where: { id },
  include: { /* ... */ }
});

Option 3: Prisma Middleware

Implement global Prisma middleware to automatically filter inactive users:

prisma.$use(async (params, next) => {
  if (params.model === 'User') {
    if (params.action === 'findMany' || params.action === 'findFirst') {
      params.args.where = { ...params.args.where, status: 'active' };
    }
  }
  return next(params);
});

Acceptance Criteria

• All user relations consistently filter for active status at the database level
• No post-query filtering needed for user status
• Performance is maintained or improved
• Code is cleaner and more maintainable
• Tests updated to cover edge cases with inactive users

Priority

Medium - The current band-aid solution works, but we should implement a more robust approach before the codebase grows larger.

---

Related to active user filtering verification completed in commit [current].