Case Management Data Model

Overview

A comprehensive data model for public sector case management, supporting multi-agency public benefits and services portals. The model handles clients, external partner organizations, staff, cases, notices, and transactions within a single Salesforce org.

Prerequisites

Required Knowledge:

• Understanding of Salesforce Case object and case management workflows
• Knowledge of multi-tenant data models and data isolation patterns
• Familiarity with Record Types and their use in data separation
• Understanding of Experience Cloud (Communities) and portal architecture
• Knowledge of sharing rules, sharing sets, and data visibility patterns

Recommended Reading:

• rag/architecture/portal-architecture.md - Portal architecture patterns
• rag/security/sharing-fundamentals.md - Sharing model fundamentals
• rag/security/sharing-sets-and-portals.md - Experience Cloud sharing
• rag/identity-sso/multi-tenant-identity-architecture.md - Multi-tenant identity
• rag/data-modeling/external-ids-and-integration-keys.md - External ID patterns

Core Entity Model

Client Accounts/Contacts

Representation: Individual citizens/clients

Implementation:

• Potentially person-style accounts for 1:1 Account-Contact relationships
• Record Types to differentiate client types
• External system IDs for correlation with legacy systems
• Portal user identities linked to Contacts and Cases

External Partner (Provider) Accounts

Representation: Organizations that provide services on behalf of the state

Implementation:

• Separate Account Record Type for external partners
• Link to external partner staff through Contact relationships
• Support for multiple external partners per client
• External partner performance tracking capabilities

External Partner Contacts

Representation: Staff working for provider organizations

Implementation:

• Separate Contact Record Type for external partner staff
• Link to external partner Accounts
• Organization tenant identity provider integration
• Access restricted to assigned clients/cases

Staff Contacts and Users

Representation: Internal employees managing cases and services

Implementation:

• Separate Contact Record Type for internal staff
• Internal SAML SSO integration
• Broader data access than portal users
• Cross-agency visibility for authorized staff

Cases

Representation: Primary records for ongoing service/benefits activity

Implementation:

• Cases as primary records for service/benefits activity
• Link Cases to Contacts (clients) and Accounts (organizations)
• Include external system IDs for correlation with legacy systems
• Support multiple case types through Record Types
• Track case status and workflow stages explicitly

Custom Notice Objects

Representation: Mirrors notices generated by external systems

Implementation:

• Custom Notice objects to track outbound communications from external systems
• Include fields for Source/Template/Purpose codes owned by external system
• Link notices to Cases and Contacts for reporting
• Track notice delivery status and outcomes
• Support notice history and audit trails

Custom Transaction Objects

Representation: Mirrors transactions logged by external engines

Implementation:

• Custom Transaction objects to track transactions logged by external engines
• Include transaction type, status, and outcome fields
• Link transactions to Cases and Notices
• Support transaction history and reporting
• Track transaction timestamps and external system correlation IDs

Case Management Model

Intake from Multiple Channels

Channels:

• Portal submissions
• Staff entry
• Integration-driven creation

Implementation:

• Support case creation from multiple sources
• Track case origin/source
• Maintain audit trail of case creation
• Link cases to appropriate clients and external partners

Case Creation Tied to External IDs

Pattern: Case creation tied to external IDs from legacy eligibility systems

Implementation:

• Use external IDs to correlate with legacy systems
• Support automated case creation from integrations
• Maintain mapping between external system records and Salesforce cases
• Enable reconciliation between systems

Status Tracking and Workflow Management

Implementation:

• Track case status through workflow stages
• Support status transitions and automation
• Track case milestones and deadlines
• Enable case escalation and reassignment workflows

Assignment to Queues and Staff

Implementation:

• Assignment to queues based on case type and criteria
• Assignment to staff based on workload and expertise
• Support for case routing rules
• Track assignment history

Linking Cases to Related Records

Implementation:

• Link cases to clients (Contacts)
• Link cases to external partners (Accounts)
• Link cases to notices (Notice objects)
• Link cases to transactions (Transaction objects)
• Support multiple relationships per case

Notice and Transaction Objects

Notice Object Structure

Fields:

• Template codes
• Purpose codes
• Source codes
• Delivery status
• Linkage back to cases and persons

Implementation:

• Custom Notice objects mirroring external system outputs
• Source/Template/Purpose code taxonomy owned by external system
• Track notice generation, delivery, and outcomes
• Support notice history and reporting

Transaction Object Structure

Fields:

• Transaction type
• Status
• Outcome
• Linkage to cases and notices

Implementation:

• Custom Transaction objects mirroring external engine transactions
• Track transaction processing and outcomes
• Link transactions to cases and notices
• Support transaction history and audit trails

Identity and Linkage Fields

External System IDs

Purpose: Correlation with legacy systems

Implementation:

• External system ID fields on all integrated objects
• Maintain mapping between external systems and Salesforce
• Support reconciliation between systems
• Enable troubleshooting and audit

Portal User Identities

Purpose: Link portal users to Contacts and Cases

Implementation:

• Identity provider GUIDs stored on Contacts
• Login handler matching external identity to Contacts
• User creation on first login
• Maintain identity mapping for audit

Cases to Notices and Transactions

Purpose: Link cases to external system outputs

Implementation:

• Lookup relationships from Notice to Case
• Lookup relationships from Transaction to Case
• Support multiple notices/transactions per case
• Enable reporting and tracking

Client to External Partner Relationships

Purpose: Track service provider assignments

Implementation:

• Relationship objects for client-external partner assignments
• Track service provider assignments and changes over time
• Support multiple external partners per client
• Track external partner performance metrics

Record Type Strategy

Account Record Types

Types:

• Client
• External Partner
• Internal Organization

Implementation: Use Record Types to distinguish between different account types

Contact Record Types

Types:

• Citizen
• External Partner Staff
• Internal Staff

Implementation: Use Record Types to distinguish between different contact types

Case Record Types

Types: Different case types by service category

Implementation: Use Record Types to support different case workflows and processes

Field Design Patterns

Source System Fields

Pattern: Source system field (e.g., HIX, MA21, MMIS) - should be picklist

Implementation: Convert Text fields with limited values to Picklists for data quality

Role Fields

Pattern: Role field (Client, External Partner Eligibility Worker, Staff) - should be picklist

Implementation: Use picklists for role identification and data quality

Language Code Fields

Pattern: Language code field (ENG, etc.) - should be picklist

Implementation: Standardize language codes using picklists

Status Fields

Pattern: Status field (Printed, Hold, Release) - picklist

Implementation: Use picklists for status tracking and workflow management

Action Fields

Pattern: Action field (Hold, Release) - picklist

Implementation: Use picklists for action tracking and workflow automation

Data Quality Patterns

Field Description Quality

Standards:

• Good descriptions include: source, purpose, population, business rules
• Poor descriptions just repeat label
• Document field purpose and usage

Help Text

Pattern: Add help text for user-facing fields

Implementation: Provide guidance to users on field purpose and expected values

Picklist Conversions

Pattern: Convert Text fields with limited values to Picklists

Implementation: Improve data quality by using picklists for fields with limited valid values

Multi-Tenant Data Isolation

Record Type-Based Separation

• Use Record Types to distinguish between client, external partner, and internal records
• Apply Record Type-specific sharing rules and field-level security
• Route users to Record Type-appropriate landing pages

Sharing Sets

• Implement Sharing Sets to enforce data visibility rules per user type
• Client users see only their own cases/records
• External partner users see only records for their associated organizations
• Internal staff can see cross-agency as needed according to policy

Role Hierarchy

• Design role hierarchies that support cross-agency visibility for authorized staff
• Restricted access for portal users
• Organization-based access for external partner users

Field-Level Security

• Use field-level security to protect sensitive PII at the field level
• Apply different field access based on user type
• Support compliance requirements

Best Practices

Case Object Design

• Use Cases as the primary record for ongoing service/benefits activity
• Link Cases to Contacts (clients) and Accounts (organizations)
• Include external system IDs for correlation with legacy systems
• Support multiple case types through Record Types
• Track case status and workflow stages explicitly

Notice Object Modeling

• Create custom Notice objects to track outbound communications from external systems
• Include fields for Source/Template/Purpose codes owned by external system
• Link notices to Cases and Contacts for reporting
• Track notice delivery status and outcomes
• Support notice history and audit trails

Transaction Object Modeling

• Create custom Transaction objects to track transactions logged by external engines
• Include transaction type, status, and outcome fields
• Link transactions to Cases and Notices
• Support transaction history and reporting
• Track transaction timestamps and external system correlation IDs

External System Integration Fields

• Add fields to support external system integration
• External system IDs for correlation
• Last sync timestamp fields
• Integration job tracking fields
• Source system identification fields
• Error tracking fields for failed integrations

Multi-Tenant Data Isolation

• Use Record Types to distinguish between client, external partner, and internal records
• Implement Sharing Sets to enforce data visibility rules per user type
• Design role hierarchies that support cross-agency visibility for authorized staff
• Use field-level security to protect sensitive PII
• Portal users see only their own records; internal users see broader data sets

Tradeoffs

Advantages

• Supports multi-agency operations in single org
• Enables unified reporting across agencies
• Reduces data silos
• Centralized administration

Challenges

• Complex sharing rule design required
• Careful Record Type management needed
• External system integration complexity
• Data quality management across multiple sources

When to Use This Model

Use this data model when:

• Implementing public sector case management
• Supporting multiple user types (clients, external partner organizations, staff)
• Integrating with external benefits/eligibility systems
• Need to track notices and transactions from external systems
• Require multi-tenant data isolation

When Not to Use This Model

Avoid this model when:

• Simple case management only
• No external system integration
• Single user type
• Different data model requirements exist

Q&A

Q: What is a case management data model for public sector?

A: A case management data model for public sector supports multi-agency public benefits and services portals. It handles: (1) Clients (citizens/clients accessing services), (2) External partner organizations (service providers), (3) Staff (internal employees managing cases), (4) Cases (service requests, benefits applications), (5) Notices and transactions (from external systems). The model supports multiple user types with different identity providers and access patterns.

Q: How do I separate different user types in a case management model?

A: Separate user types by: (1) Record Types on Account/Contact (client vs. external partner vs. internal staff), (2) Different identity providers (OIDC for clients, SAML for staff, organization tenant for partners), (3) Sharing rules (data visibility per user type), (4) Portal experiences (Experience Cloud for clients, internal Service Cloud for staff), (5) Login handlers (route users to appropriate experiences).

Q: How do I model external partner organizations in case management?

A: Model external partners by: (1) Separate Account Record Type for external partners, (2) Link to external partner staff through Contact relationships, (3) Support multiple partners per client (client can have multiple service providers), (4) External partner performance tracking (track provider performance), (5) Organization tenant identity (external partner staff use organization tenant identity provider).

Q: How do I handle cases in a multi-tenant case management model?

A: Handle cases by: (1) Case object for service requests and benefits applications, (2) Link cases to clients (Contact relationships), (3) Link cases to external partners (if applicable), (4) Link cases to staff (case assignment, ownership), (5) Case sharing rules (clients see their cases, partners see assigned cases, staff see based on sharing), (6) Case status tracking (track case progression).

Q: How do I integrate with external benefits/eligibility systems?

A: Integrate by: (1) External system IDs (correlate Salesforce records with external systems), (2) Notices object (track notices from external systems), (3) Transactions object (track transactions from external systems), (4) Integration jobs (batch or real-time synchronization), (5) Data reconciliation (ensure data consistency). External IDs enable stable record mapping.

Q: What is the difference between client Accounts and external partner Accounts?

A: Client Accounts represent individual citizens/clients (potentially person-style accounts for 1:1 Account-Contact relationships). External partner Accounts represent organizations providing services (separate Record Type, link to partner staff, support multiple partners per client). Use Record Types to differentiate and apply different sharing rules and access patterns.

Q: How do I track notices and transactions from external systems?

A: Track by: (1) Notices object (track notices received from external systems), (2) Transactions object (track transactions from external systems), (3) Link to Cases (associate notices/transactions with cases), (4) External system IDs (correlate with external systems), (5) Integration tracking (track when notices/transactions were received). Notices and transactions provide audit trail and integration tracking.

Q: How do I ensure data isolation between user types?

A: Ensure isolation by: (1) Record Type-based separation (different Record Types per user type), (2) Sharing rules (restrict data visibility per user type), (3) Field-level security (control field access per user type), (4) Portal sharing (Experience Cloud sharing for clients), (5) Login handlers (route users to appropriate experiences). Data isolation is critical for multi-tenant models.

Q: When should I use this case management model?

A: Use when: (1) Implementing public sector case management (multi-agency, public benefits), (2) Supporting multiple user types (clients, external partners, staff), (3) Integrating with external systems (benefits/eligibility systems), (4) Tracking notices and transactions (from external systems), (5) Requiring multi-tenant data isolation (different access per user type).

Q: What are best practices for case management data models?

A: Best practices include: (1) Use Record Types to separate user types, (2) Implement sharing rules for data isolation, (3) Use external IDs for integration, (4) Track external system data (notices, transactions), (5) Support multiple identity providers (OIDC, SAML, organization tenant), (6) Design for scalability (handle large case volumes), (7) Maintain data quality (validation, duplicate prevention).

Edge Cases and Limitations

Edge Case 1: Multi-Tenant Data Isolation Verification

Scenario: Verifying that data isolation between user types (clients, external partners, staff) is working correctly.

Consideration:

• Test data access from each user type perspective (clients, partners, staff)
• Verify Record Type-based separation is enforced
• Test sharing rules and sharing sets for each user type
• Monitor for cross-community data access issues
• Implement audit trails to track data access by user type

Edge Case 2: High-Volume Case Creation

Scenario: Creating thousands of cases from external systems or bulk operations, causing performance issues.

Consideration:

• Use Bulk API for high-volume case creation
• Disable automation during bulk import (re-enable after import)
• Implement batch processing for case creation
• Monitor case creation performance and adjust as needed
• Test case creation with production-like data volumes

Edge Case 3: Complex Case Relationships

Scenario: Cases with many related records (notices, transactions, case comments, attachments) creating large data volumes.

Consideration:

• Limit relationship depth when querying cases
• Use pagination for related record queries
• Consider data archiving for historical cases
• Monitor case-related record performance
• Test case queries with large related record sets

Edge Case 4: External System Integration Failures

Scenario: Integration failures with external benefits/eligibility systems, causing data inconsistency.

Consideration:

• Implement retry logic for transient integration failures
• Use dead-letter queues for unprocessable records
• Track integration job status and errors
• Implement data reconciliation processes
• Support manual data correction and reprocessing

Edge Case 5: Notice and Transaction Volume

Scenario: High-volume notices and transactions from external systems, creating large data volumes.

Consideration:

• Use Bulk API for high-volume notice/transaction imports
• Implement data archiving for historical notices/transactions
• Monitor notice/transaction table size and performance
• Consider batch processing for notice/transaction creation
• Test notice/transaction processing with production-like volumes

Limitations

• Record Type Limits: Maximum 200 record types per object (may limit user type separation)
• Sharing Rule Limits: Maximum 300 sharing rules per object (varies by org edition)
• Sharing Set Limits: Maximum 50 Sharing Sets per Experience Cloud site
• Case Relationship Limits: Cases have relationship limits (lookups, master-detail)
• External System Integration: Integration complexity and error handling requirements
• Data Isolation Verification: Difficult to verify complete data isolation between user types
• High-Volume Processing: High-volume case creation may require special handling
• Notice/Transaction Volume: Large notice/transaction volumes may impact performance

Related Patterns

• Multi-Tenant Identity Architecture - Identity provider patterns
• Sharing Sets and Portals - Experience Cloud sharing patterns
• External IDs and Integration Keys - External ID patterns