Resource Library + Coordinated Entry Lite

Overview

This module adds comprehensive resource management and referral capabilities to the PathSync CRM, enabling organizations to manage their programs, match clients with appropriate resources, and track referrals through a coordinated entry process.

Date Implemented

November 21, 2025

Features Implemented

1. Resource Library

A centralized library where organizations can create and manage their resources/programs.

Key Capabilities:

• Create, view, edit, and manage resources
• Categorize resources (housing, food, energy, employment, healthcare, transportation, other)
• Define detailed eligibility requirements
• Track availability (ongoing, slot-based, funding-based, or seasonal)
• Geographic service area configuration
• Tag-based organization
• Status management (active, inactive, full, closed)

Access Control:

• Admin: View all resources, manage any resource
• Org Users: View all active resources, manage only their organization's resources
• Staff/Caseworkers: View all active resources (read-only by default)

2. Client/Household Management

Client profile pages with integrated resource matching.

Key Capabilities:

• Household and individual client records
• Household composition tracking
• Income and demographic information
• Location data for matching
• Head of household designation

Suggested Resources Panel:

• Automatically matches resources based on:
  • Location (city, county, zip code)
  • Income eligibility
  • Household size
  • Other eligibility criteria
• Match scoring algorithm
• Direct "Start Referral" action from suggestions

3. Referral Management

Complete referral workflow from creation to completion.

Referral Lifecycle:

1. Draft - Initial creation
2. Submitted - Submitted for review
3. In Review - Being evaluated by resource provider
4. Accepted - Referral approved
5. Denied - Referral declined (with reason)
6. Completed - Service delivered
7. Cancelled - Referral withdrawn

Key Capabilities:

• Create referrals from client profiles or resource pages
• Prefill application data from household records
• Priority scoring and urgency levels
• Multiple note types (caseworker, internal, provider)
• Activity timeline tracking
• Status update workflow
• Organization-specific views

Access Control:

• Caseworkers: Create referrals for their organization's clients
• Org Users: View referrals to their resources or from their organization
• Resource Providers: Update status and add notes for referrals to their resources
• Admin: View and manage all referrals

Database Schema

New Tables

organizations

Service provider organizations (separate from landlords)

• Organization details
• Contact information
• Organization type (service_provider, coc, government)

households

Client household records

• Household composition and demographics
• Income information
• Location data
• CoC code and service area
• Assigned caseworker

clients

Individual client records

• Personal information (name, DOB, contact)
• Demographics (gender, race/ethnicity, veteran status, disability)
• Relationship to household
• Head of household designation

resources

Resource/program library

• Basic information (name, description, category)
• Eligibility requirements (income, household size)
• Availability tracking (slots, funding, dates)
• Service area (geographic coverage)
• Application and screening process
• Contact information
• Organization ownership

resource_tags

Tagging system for resources

• Flexible categorization
• Search and filtering support

referrals

Referral tracking

• Links household to resource
• Status workflow
• Priority scoring and urgency
• Prefilled application data
• Multiple note fields
• Date tracking (created, submitted, reviewed, completed)

referral_activities

Activity log for referrals

• Timeline of changes
• Status transitions
• Note additions
• User attribution

Views

resource_availability

Computed availability status based on slots, funding, or dates

referral_summary

Denormalized referral data with household and resource details

organization_stats

Dashboard statistics for organizations

Functions

is_household_eligible_for_resource(household_id, resource_id)

Checks eligibility based on configured criteria

get_matched_resources(household_id)

Returns eligible resources with match scores

Updated Tables

profiles

Added org_id column to link users to organizations Updated role support for: 'staff', 'caseworker', 'org_admin', 'landlord', 'admin'

File Structure

app/
├── (crm)/
│   ├── layout.tsx                    # CRM section layout with navigation
│   ├── resources/
│   │   ├── page.tsx                  # Resource list view
│   │   ├── new/
│   │   │   └── page.tsx              # Create new resource
│   │   └── [id]/
│   │       ├── page.tsx              # Resource detail view
│   │       └── edit/
│   │           └── page.tsx          # Edit resource (future)
│   ├── clients/
│   │   └── [id]/
│   │       └── page.tsx              # Client profile with suggested resources
│   └── referrals/
│       ├── page.tsx                  # Referral list view
│       ├── new/
│       │   └── page.tsx              # Create new referral
│       └── [id]/
│           └── page.tsx              # Referral detail view
├── components/
│   └── CRMNavigation.tsx             # Navigation for resource library section
├── lib/
│   └── supabase.ts                   # Updated with new TypeScript interfaces
└── middleware.ts                     # Updated to protect new routes

database/
└── migrations/
    └── DATABASE_MIGRATION_RESOURCE_LIBRARY.sql

Pages

Resource Library (/resources)

• List View: Browse all resources with filters (category, status)
• Create: Form to add new resources with full configuration
• Detail View: Complete resource information with contact details
• Edit: Update existing resources (org users only)

Client Profiles (/clients/[id])

• Household and client information
• Suggested resources panel with match scoring
• Referrals list
• Quick actions (create referral, browse resources)

Referrals (/referrals)

• List View: All referrals with status filtering
• Create: Form to create new referrals with household and resource selection
• Detail View: Complete referral information with activity timeline
• Status Updates: Workflow actions for authorized users

User Roles and Permissions

Admin

• View and manage all resources, clients, and referrals
• Access all notes and internal information
• Override organization boundaries

Org Admin / Org User

• Manage their organization's resources
• View referrals to their resources
• View referrals from their organization
• Update referral status for their resources

Caseworker / Staff

• View all active resources
• View clients in their organization
• Create referrals
• View and update their organization's referrals
• Add caseworker notes

Landlord

• No access to resource library features (separate portal)

Access Control Implementation

Row Level Security (RLS) Policies

All tables have RLS enabled with appropriate policies:

Resources:

• Admin sees all
• Org users see and manage their own
• Staff/caseworkers see only active resources

Households & Clients:

• Admin sees all
• Org users see only their organization's clients
• Tied to caseworker assignment

Referrals:

• Admin sees all
• Caseworkers see their organization's referrals
• Resource providers see referrals to their resources
• Multiple parties can update based on their role

Middleware Protection

Routes are protected by middleware:

• /resources/* - Requires authentication and appropriate role
• /clients/* - Requires authentication and appropriate role
• /referrals/* - Requires authentication and appropriate role

Allowed roles: staff, caseworker, org_admin, admin

Matching Algorithm

The resource matching system uses a simple scoring algorithm:

Location Match:

• City match: +50 points
• County match: +30 points
• Other: +10 points

Income Match:

• Well within limit (≤80% of max): +30 points
• Within limit: +20 points
• Other: +10 points

Resources are only suggested if all hard eligibility criteria are met (income limits, household size).

Usage Examples

Creating a Resource

1. Navigate to /resources
2. Click "Add Resource"
3. Fill in required information:
   • Name
   • Category
   • Eligibility requirements
   • Availability type and limits
   • Service area
   • Contact information
4. Submit to create

Matching Clients with Resources

1. Navigate to client profile /clients/[id]
2. View "Suggested Resources" panel
3. Review match scores and eligibility
4. Click "Start Referral" on appropriate resource

Creating a Referral

From Client Profile:

1. On client profile, click "Start Referral" on suggested resource
2. Form is prefilled with household and resource
3. Add notes and set urgency
4. Submit referral

Manual Creation:

1. Navigate to /referrals
2. Click "Create Referral"
3. Select household and resource
4. Add notes and set urgency
5. Submit referral

Managing Referrals

1. Navigate to /referrals
2. Filter by status, urgency, or search
3. Click referral to view details
4. Update status as appropriate (if authorized)
5. Add provider notes or denial reasons

Future Enhancements

Potential additions for future versions:

1. Advanced Matching

   • Additional criteria (veteran status, disability, etc.)
   • Machine learning-based scoring
   • Historical success rates

2. Communication

   • Email notifications for status changes
   • In-app messaging between caseworkers and providers
   • Document attachments

3. Reporting

   • Referral success rates
   • Resource utilization metrics
   • Wait time analytics
   • Demographic reporting

4. Integration

   • HMIS (Homeless Management Information System) integration
   • 211 database synchronization
   • Calendar scheduling for appointments

5. Client Portal

   • Self-service resource browsing
   • Application status tracking
   • Document upload

6. Bulk Operations

   • Batch referral creation
   • CSV import/export
   • Resource templates

Testing

Test Data

Sample data is included in the migration:

• 3 sample organizations
• Sample resource "Emergency Rental Assistance"

Manual Testing Steps

1. Resource Management:

   • Create a resource as org user
   • Verify it appears in list
   • Edit resource details
   • Change status to inactive

2. Client Matching:

   • Create household with income and location
   • Create matching resource with same location
   • Verify resource appears in suggested resources
   • Verify match score

3. Referral Workflow:

   • Create referral from client profile
   • Verify it appears in referral list
   • Update status as resource provider
   • Add notes and verify they appear
   • Check activity timeline

4. Permissions:

   • Test as different role types
   • Verify org boundaries are enforced
   • Verify status update permissions

Troubleshooting

Resources Not Appearing

Issue: Org user can't see their resources Solution:

• Verify user has org_id set in profiles table
• Verify resource org_id matches user's org
• Check RLS policies are enabled

Matching Not Working

Issue: Eligible resources not showing as matches Solution:

• Verify household has required fields (income, location)
• Verify resource eligibility criteria are configured
• Check function is_household_eligible_for_resource is working
• Ensure resource status is 'active'

Permission Denied Errors

Issue: Users getting unauthorized errors Solution:

• Verify user role in profiles table
• Check middleware allows the role
• Verify RLS policies for the table
• Ensure user is authenticated

Migration Instructions

To implement this feature in your database:

1. Run Migration:

   -- In Supabase SQL Editor
   -- Copy and paste contents of:
   database/migrations/DATABASE_MIGRATION_RESOURCE_LIBRARY.sql
   

2. Verify Tables:

   -- Check tables were created
   SELECT table_name 
   FROM information_schema.tables 
   WHERE table_schema = 'public' 
     AND table_name IN (
       'organizations', 'households', 'clients', 'resources', 
       'resource_tags', 'referrals', 'referral_activities'
     );
   

3. Create Test Organization:

   INSERT INTO organizations (name, org_type, city, state)
   VALUES ('Your Organization', 'service_provider', 'Your City', 'Oregon');
   

4. Link User to Organization:

   UPDATE profiles 
   SET org_id = (SELECT id FROM organizations WHERE name = 'Your Organization')
   WHERE id = 'your-user-id';
   

5. Test Resource Creation:

   • Log in as the user
   • Navigate to /resources
   • Create a test resource

Security Considerations

1. Data Privacy:

   • SSN stored as last 4 digits only
   • Internal notes not shared with external providers
   • RLS policies enforce data boundaries

2. Access Control:

   • Middleware prevents unauthorized access
   • RLS provides database-level security
   • Role-based permissions throughout

3. Audit Trail:

   • All referral changes logged in activities table
   • Created_by and updated_by tracking
   • Timestamp tracking for all actions

Support and Maintenance

Regular Maintenance Tasks

1. Data Cleanup:

   • Archive completed referrals (quarterly)
   • Remove stale draft referrals (monthly)
   • Update resource availability

2. Monitoring:

   • Check for referrals stuck in status
   • Monitor match accuracy
   • Review denied referrals for patterns

3. Updates:

   • Add new resources as programs launch
   • Update eligibility criteria as policies change
   • Adjust service areas as coverage expands

Related Documentation

• Database Schema
• Supabase Setup
• Quick Start Guide

Version History

• v1.0 (November 21, 2025) - Initial implementation
  • Resource library
  • Client matching
  • Referral workflow
  • Basic reporting views