Overview
The Applicant Identity Layer provides a shared infrastructure for tracking people who interact with the Unify platform across multiple applications. When someone applies for housing assistance through Terra and later uses Pathfinder to screen for other benefits, they’re recognized as the same person.
The identity layer is implemented in the @unify/identity package and can be used by any app in the monorepo.
Architecture
┌─────────────────────────────────────────────────────────────────┐
│ UNIFIED IDENTITY LAYER │
├─────────────────────────────────────────────────────────────────┤
│ │
│ WorkOS (Auth) ──▶ applicants ──▶ applicant_profiles │
│ │ │ │
│ │ ▼ │
│ │ applicant_pii │
│ │ │ │
│ ▼ ▼ │
│ profile_contributions │
│ │ │
│ ┌───────────────┼───────────────┐ │
│ ▼ ▼ ▼ │
│ Terra Pathfinder Future Apps │
│ (submissions) (screeners) │
│ │
└─────────────────────────────────────────────────────────────────┘
Database Tables
applicants
Core identity table linking authentication to applicant data.
| Column | Type | Description |
|---|
id | UUID | Primary key |
workos_id | TEXT | WorkOS user ID (null for anonymous) |
session_id | TEXT | Session tracking for anonymous users |
email | TEXT | Primary email address |
display_name | TEXT | Full name for display |
profile_version | INTEGER | Increments on profile changes |
last_active_at | TIMESTAMPTZ | Last interaction timestamp |
applicant_profiles
Non-PII data that’s fully queryable for eligibility and reporting.
| Column | Type | Description |
|---|
household_size | INTEGER | Number of people in household |
children_under_18 | INTEGER | Count of minor children |
monthly_income_cents | INTEGER | Total monthly income in cents |
fpl_percentage | INTEGER | Federal Poverty Level percentage |
housing_status | TEXT | ’stable’, ‘behind_on_rent’, ‘homeless’, etc. |
food_security | TEXT | ’secure’, ‘low_security’, ‘urgent’ |
needs_childcare | BOOLEAN | Childcare assistance needed |
needs_transportation | BOOLEAN | Transportation assistance needed |
has_crisis_flags | BOOLEAN | Any urgent needs flagged |
applicant_pii
Personal information with cleartext queryable fields and encrypted secrets.
| Column | Queryable | Description |
|---|
first_name, last_name | Yes | Name for search and matching |
date_of_birth | Yes | DOB for age calculations and verification |
ssn_last_four | Yes | Last 4 digits for duplicate detection |
ssn_encrypted | No | Full SSN, AES-256 encrypted |
address_* | Yes | Full address (street, city, state, zip, county) |
email, phone | Yes | Contact information |
applicant_bank_accounts
Bank information for disbursements.
| Column | Queryable | Description |
|---|
bank_name | Yes | Institution name |
routing_number | Yes | ABA routing number (public info) |
account_last_four | Yes | Last 4 digits for display |
account_number_encrypted | No | Full account, encrypted |
is_primary | Yes | Primary account flag |
is_verified | Yes | Verification status |
profile_contributions
Tracks data provenance - where each piece of profile data came from.
| Column | Description |
|---|
source_app | ’terra’, ‘pathfinder’, ‘admin’, ‘api’ |
source_type | ’form_submission’, ‘screener’, ‘manual’, ‘import’ |
source_id | Reference to original record |
field_paths | Array of fields that were contributed |
is_verified | Whether data has been verified |
verified_by | ’admin’, ‘system_ocr’, ‘partner_api’ |
Usage
Finding or Creating an Applicant
The find_or_create_applicant function handles identity resolution:
import { supabaseAdmin } from "@/lib/supabase";
// From a form submission
const { data: applicantId } = await supabaseAdmin.rpc(
"find_or_create_applicant",
{
p_email: "maria@example.com",
p_workos_id: session?.user?.id || null,
p_session_id: null,
p_first_name: "Maria",
p_last_name: "Garcia",
}
);
- Looks for existing applicant by
workos_id (authenticated user)
- Falls back to matching by
email
- Falls back to matching by
session_id (anonymous)
- Creates new applicant if no match found
- Automatically creates
applicant_profiles and applicant_pii records
Using the Identity Package
import {
findOrCreateApplicant,
getApplicantWithDetails,
updateApplicantProfile,
findDuplicates,
} from "@unify/identity";
// Get full applicant details
const result = await getApplicantWithDetails(supabase, applicantId);
if (result.success) {
const { applicant, profile, pii, bank_accounts, contributions } = result.data;
}
// Update profile
await updateApplicantProfile(supabase, applicantId, {
household_size: 4,
fpl_percentage: 85,
housing_status: "behind_on_rent",
needs_childcare: true,
});
// Check for duplicates
const duplicates = await findDuplicates(supabase, applicantId);
// Returns matches based on SSN4+DOB, name+DOB, email, phone, address
Listing Applicants
import { listApplicants } from "@unify/identity";
const result = await listApplicants(supabase, {
search: "garcia",
housing_status: "behind_on_rent",
has_crisis: true,
fpl_max: 150,
state: "WA",
limit: 50,
offset: 0,
});
Duplicate Detection
The find_potential_duplicates function identifies possible duplicate records:
SELECT * FROM find_potential_duplicates('applicant-uuid-here');
| Match Type | Confidence | Criteria |
|---|
ssn4_dob_exact | 95% | Same SSN last 4 + date of birth |
name_dob_exact | 90% | Same first name + last name + DOB |
email_exact | 80% | Same email address |
phone_exact | 70% | Same phone number |
address_exact | 50% | Same street address + zip |
Data Flow
When a form is submitted:
- Extract email from session or form data
- Call
find_or_create_applicant() to get/create applicant
- Insert submission with
applicant_id linked
- Record contribution in
profile_contributions
// In submitFormResponse()
const { data: applicantId } = await supabaseAdmin.rpc(
"find_or_create_applicant",
{ p_email: email, p_workos_id: userId, ... }
);
await supabaseAdmin.from("submissions").insert({
form_id: formId,
applicant_id: applicantId, // Linked!
data: formData,
});
await supabaseAdmin.from("profile_contributions").insert({
applicant_id: applicantId,
source_app: "terra",
source_type: "form_submission",
field_paths: Object.keys(formData),
});
Benefits Screener (Pathfinder)
When a screener is completed:
- Same identity resolution flow
- Update
applicant_profiles with household/income data
- Record contribution with
source_app: "pathfinder"
Security Considerations
What’s Encrypted
Only truly sensitive secrets are encrypted:
- Full SSN (
ssn_encrypted)
- Full ITIN (
itin_encrypted)
- Full bank account numbers (
account_number_encrypted)
What’s Queryable (Cleartext)
For operational needs (fraud detection, reporting, duplicate matching):
- Names, date of birth
- SSN last 4 digits
- Full address
- Bank name, routing number, account last 4
- Income amounts, household composition
RLS Policies
All applicant tables have Row Level Security enabled:
- Service role has full access (for server-side operations)
- No direct client access (all operations through server actions)
Common Queries
Find applicants in crisis
SELECT a.*, p.*, pii.*
FROM applicants a
JOIN applicant_profiles p ON p.applicant_id = a.id
JOIN applicant_pii pii ON pii.applicant_id = a.id
WHERE p.has_crisis_flags = TRUE
ORDER BY a.last_active_at DESC;
Detect potential fraud (same bank account)
SELECT
routing_number,
account_last_four,
COUNT(*) as applicant_count,
ARRAY_AGG(applicant_id) as applicants
FROM applicant_bank_accounts
GROUP BY routing_number, account_last_four
HAVING COUNT(*) > 1;
Cross-program participation
SELECT
a.display_name,
a.email,
COUNT(DISTINCT pc.source_app) as apps_used,
ARRAY_AGG(DISTINCT pc.source_app) as apps
FROM applicants a
JOIN profile_contributions pc ON pc.applicant_id = a.id
GROUP BY a.id
HAVING COUNT(DISTINCT pc.source_app) > 1;
Migration
To add the identity layer to an existing deployment, run migration 022_applicant_identity.sql in your Supabase SQL editor.
Existing submissions will have applicant_id = NULL. New submissions will automatically create and link applicant records.
-- Create applicants from existing submissions with user_id
INSERT INTO applicants (workos_id, email)
SELECT DISTINCT
s.user_id,
up.email
FROM submissions s
JOIN user_profiles up ON up.user_id = s.user_id
WHERE s.user_id IS NOT NULL
AND s.applicant_id IS NULL
ON CONFLICT (workos_id) DO NOTHING;
-- Link submissions to applicants
UPDATE submissions s
SET applicant_id = a.id
FROM applicants a
WHERE s.user_id = a.workos_id
AND s.applicant_id IS NULL;