-- Unified applicant profile (extends @unify/identity)
CREATE TABLE hub_profiles (
id UUID PRIMARY KEY,
applicant_id UUID REFERENCES applicants,
-- Aggregated data
total_applications INTEGER DEFAULT 0,
total_approved INTEGER DEFAULT 0,
total_benefits_received BIGINT DEFAULT 0, -- cents
first_application_at TIMESTAMPTZ,
last_application_at TIMESTAMPTZ,
-- Risk summary
overall_risk_level TEXT DEFAULT 'none',
active_fraud_flags INTEGER DEFAULT 0,
-- Sync metadata
last_synced_at TIMESTAMPTZ,
sync_sources JSONB, -- {terra: timestamp, pathfinder: timestamp, ...}
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Cross-system application tracking
CREATE TABLE hub_applications (
id UUID PRIMARY KEY,
applicant_id UUID REFERENCES applicants,
profile_id UUID REFERENCES hub_profiles,
-- Source identification
source_system TEXT NOT NULL, -- 'terra', 'pathfinder', 'airtable'
source_id TEXT NOT NULL, -- ID in source system
-- Application data
program_id UUID,
program_name TEXT,
status TEXT,
submitted_at TIMESTAMPTZ,
decided_at TIMESTAMPTZ,
decision TEXT, -- 'approved', 'denied', 'pending'
-- Benefit data (if approved)
benefit_amount BIGINT, -- cents
payment_date TIMESTAMPTZ,
payment_status TEXT,
-- Sync metadata
last_synced_at TIMESTAMPTZ,
raw_data JSONB,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE(source_system, source_id)
);
-- Unified timeline events
CREATE TABLE hub_timeline (
id UUID PRIMARY KEY,
profile_id UUID REFERENCES hub_profiles,
applicant_id UUID REFERENCES applicants,
-- Event data
event_type TEXT NOT NULL,
event_subtype TEXT,
description TEXT,
metadata JSONB,
-- Source tracking
source_system TEXT,
source_id TEXT,
occurred_at TIMESTAMPTZ NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Saved cross-reference queries
CREATE TABLE hub_saved_queries (
id UUID PRIMARY KEY,
name TEXT NOT NULL,
description TEXT,
query_type TEXT NOT NULL, -- 'address', 'bank', 'ip', 'custom'
query_config JSONB NOT NULL,
created_by UUID REFERENCES users,
created_at TIMESTAMPTZ DEFAULT NOW()
);