Data Model Overview

Date: 2026-05-14 Scope: Postgres schema backing the Rōvn platform, 89+ sequential migrations (plus the 2026_04_14_audit_log_harden.sql hotfix). Posture: LIVE schema for core rails (workers, profiles, sessions, documents, verifications, decisions, audit, source receipts, hospitals, SSO). PARTIAL for consent_events and full ai_runs call-site coverage. TARGET noted where applicable.

1. Domain map

                     ┌──────────────────────────────────────────────┐
                     │                  IDENTITY                    │
                     │  nurses ─┬─ nurse_profiles ─┬─ nurse_sessions │
                     │          └─ magic_links     └─ anon_intake    │
                     └──────────┬───────────────────────────────────┘
                                │
        ┌───────────────────────┼────────────────────────┐
        │                       │                        │
┌───────▼────────┐    ┌─────────▼────────┐      ┌────────▼───────┐
│  CREDENTIALS   │    │   VERIFICATION   │      │   DECISIONS    │
│  documents     │    │   verifications  │      │   decisions    │
│  licenses      │    │   credential_    │      │   privileging_ │
│  certifications│    │   source_        │      │   recommendation_log
│  worker_trust_ │    │   receipts       │      │   recredentialing_
│  records       │    │   source_        │      │   cycles       │
│                │    │   authority_     │      │   oppe_fppe    │
│                │    │   policies       │      │                │
└────────────────┘    └──────────────────┘      └────────────────┘
                                │
                       ┌────────▼─────────┐
                       │   AUDIT + AI     │
                       │   audit_log      │  ← hash-chained
                       │   ai_runs        │  ← every AI call
                       │   ai_prompt_     │
                       │   templates      │
                       │   ai_policy_     │
                       │   controls       │
                       │   ai_vendor_     │
                       │   baa_matrix     │
                       │   consent_events │  ← TARGET schema
                       └──────────────────┘

       ┌──────────────────────────────────────────────────────────┐
       │                     FACILITY SIDE                        │
       │  hospitals ─ hospital_profiles ─ facility_sso_connections │
       │              facility_invites                            │
       └──────────────────────────────────────────────────────────┘

       ┌──────────────────────────────────────────────────────────┐
       │                MARKETPLACE + PRICING                     │
       │  role_wage_benchmarks · employer_pricing_tiers           │
       │  nurse_premium_tiers · payor_partners · hris_partners    │
       │  healthcare_role_catalog · per_diem_shifts               │
       └──────────────────────────────────────────────────────────┘

2. Identity tables

nurses

Root identity row for every worker. Created at the start of /start flow. Links Cognito sub, Persona inquiry, and optional anonymous-intake handoff. PHI-light at this layer (name, contact); deeper PHI lives in linked tables.

nurse_profiles

Extended profile data: bio, photo URL, public slug, locations, role catalog refs, premium tier (free / pro). The public_slug powers passport.rovn.to/p/{slug}, the public-readable Passport surface (zero-PHI when worker has chosen public visibility; otherwise gated).

nurse_sessions

Auth session rows. Both Cognito-backed sessions (post-073_worker_auth_v2_signup.sql and 075_worker_authkit.sql) and legacy magic-link sessions are represented. Session TTL is short; refresh is rotated.

magic_links and anonymous_intake_sessions

• magic_links, short-lived signed tokens for email-based session start, used pre-Cognito and as a fallback path.
• anonymous_intake_sessions, tracks pre-account intake. Allows a worker to walk through /start and capture state before account creation; binds to a nurses row at signup.

nlc_compact_states

Reference table for Nurse Licensure Compact state membership. Drives compact-license logic (a worker with a multistate license in a compact state has practice privilege in every other compact state, verification logic depends on this).

3. Credential and verification tables

documents

Worker-uploaded credential documents. S3 object key, mime type, upload timestamp, extracted fields, extraction confidence, parser version. PHI-encrypted columns via pgcrypto where applicable.

worker_trust_records

The canonical "this fact about this worker is at tier N" table. Migration 031_workforce_trust_record_ai.sql. Holds: - tier (1-5: imported → attested → processed → source-verified → approved; see truth ladder in AI_ARCHITECTURE_MEMO.md §7) - source_receipt_id FK (NULL until source-verified) - last_verified_at - expires_at (drives recredentialing cadence) - ai_extraction_confidence (NULL after source-verified)

CHECK constraint: tier = 4 (source-verified) requires a non-null source_receipt_id AND last_verified_at within the source's TTL window.

credential_source_receipts

Migrations 032_source_receipts_and_authority_policies.sql, 062_source_adapter_evidence.sql, 068_source_receipts_s3_artifact.sql. Each row is one verification attempt against one source: - source (e.g., NPDB, OIG_LEIE, NURSYS, STATE_BON_CA, DEA, ABMS, AMA) - status (match, mismatch, not_found, conflict, error) - source_url, source_timestamp, payload_hash (SHA-256 of returned payload) - s3_artifact_key (S3 object holding the raw vendor response, Object Lock retention) - ttl_seconds (per-source policy) - actor (system for automated, user_id for human-initiated)

source_authority_policies

Declares the canonical authority per credential type per jurisdiction, plus the TTL window and pass_criteria. Driven by 032. Editable only by admin role; edits write to audit_log.

licenses and dea_registrations

• Licenses: per-state nursing/medical license rows. licenses carries state, number, type, expiration. Has its own composite index for fast lookup (migration 008_licenses_state_number_idx.sql).
• dea_registrations (migration 063_dea_registrations.sql): DEA controlled-substance registration numbers, schedules, address-of-record.

Requirements graph

• requirements_graph_nodes (migration 030_requirements_evidence_baseline.sql): a directed graph of credentialing requirements. Nodes are individual requirements (e.g., "active state RN license," "current BLS card," "negative OIG LEIE result"). Edges encode prerequisite relationships. Used by facility workflow layer to build a packet's "what's missing" view.
• requirements_graph_edges: companion edges table.

4. Decisions and recredentialing

decisions

Human-actor decisions. Privileging committee approve/deny, hire/no-hire, credential committee vote. Every row has: - actor_user_id (the human) - decision_type, decision, decision_rationale - ai_advisory_id NULL or FK to the ai_runs advisory row (referenced, not delegated) - Audit-chain hash

privileging_recommendation_log

Migration 059_privileging_recommendation_log.sql. AI-generated privileging advisory recommendations. NOT a decision. Always referenced by a decisions row when used.

recredentialing_cycles

Migration 058_recredentialing_cycles.sql. NCQA-cadence-driven recredentialing schedule per worker per facility. Generates reminder events and re-runs the source-verification rail at the policy-defined cadence.

oppe_fppe_tables

Migration 055_oppe_fppe_tables.sql. Ongoing Professional Practice Evaluation and Focused Professional Practice Evaluation rows. The data source for committee-level privileging review.

5. Audit and AI ledger

audit_log (hash-chained)

Migrations 029_ai_trust_layer.sql, hardening hotfix 2026_04_14_audit_log_harden.sql. Schema:

• seq (monotonic BIGINT, primary key)
• ts (server timestamp)
• actor_kind (human, system, ai_advisory)
• actor_id
• event_type
• payload_jsonb (the structured event)
• payload_hash (SHA-256 of canonicalized payload)
• prev_hash (the previous row's entry_hash; genesis is all-zeros)
• entry_hash (SHA-256 of seq || ts || actor || event_type || payload_hash || prev_hash)

Chain discipline: - Genesis row inserted at deploy with prev_hash = 0x00...0. - Every subsequent insert recomputes entry_hash server-side. - Hot mirror to S3 Object Lock bucket (COMPLIANCE mode) per row. - Verification endpoint /audit/chain-head exposes the latest entry_hash; auditors can replay forward by reading /audit/events/recent.

ai_runs

See AI_ARCHITECTURE_MEMO.md §8. LIVE schema. PARTIAL call-site coverage. Holds advisor calls inline (advisor_calls JSONB column) so an escalation's token cost rolls up with the parent executor call.

ai_prompt_templates, ai_policy_controls, ai_vendor_baa_matrix

Migration 029_ai_trust_layer.sql. - ai_prompt_templates, versioned prompt strings (no PHI in template body; templates use variable placeholders). - ai_policy_controls, per-tenant toggles (e.g., "require human review for all PHI extraction", "block AI from drafting committee responses"). Enforced at ai_gateway.py. - ai_vendor_baa_matrix, registry of AI vendors with active BAA + ZDR status. Code-level check: a vendor not in this matrix cannot receive PHI traffic.

consent_events (TARGET schema)

Worker consent ledger. Captures per-worker, per-data-class, per-recipient consent grants and revocations. Schema TARGET, partially landed via consent fields on nurse_profiles, full table is on the post-close roadmap.

6. Facility-side tables

hospitals, hospital_profiles

Facility identity. Migration 033_rovn_connect_command_center.sql and later extensions.

facility_sso_connections

Migration 074_facility_sso.sql. WorkOS connection rows; per-hospital SSO with metadata + activation state.

facility_invites

Per-facility invitation rows for clinicians, committee members, admins.

connect_* tables

• connect_command_center family (migrations 033, 035, 036)
• connect_worker_detail_events (045)
• connect_worker_view_state (047)
• connect_worker_audit_and_license_hardening (048)
• connect_activation_workbench (038)
• connect_integration_retry_workbench (037)

These power the facility workflow layer facility cockpit, the paid SKU for facility ops.

7. Marketplace, pricing, payer/HRIS

Pricing tier tables

• employer_pricing_tiers, nurse_premium_tiers, migrations 026_pricing_tiers.sql, 027_free_tier_enhancements.sql, 034_connect_pricing_reposition.sql.
• Pricing surfaces (Pilot/Core/Ops/Platform) are encoded here.

Network and demand

• role_wage_benchmarks (migration 023_data_benchmarking.sql), role/geo wage data feeding facility workflow layer's demand monitoring.
• payor_partners, hris_partners, partner-system identifiers and integration state (migration 022_hris_integration.sql, 024_ai_copilot_outcomes_payor.sql).
• healthcare_role_catalog (migration 072_universal_healthcare_workforce_catalog.sql), canonical role taxonomy (RN, LPN, CNA, MD, DO, PA, NP, RT, etc., with subspecialties).

Marketplace + employment

• worker_network_apply_and_event_spine (049)
• worker_referral_and_network_growth (051)
• open_employment_marketplace (054)
• credentialing_employment_eligibility (053)
• per_diem_shifts (020)

Payer ops

• payer_enrollment_foundation (039) + coverage_readiness (040) + payer_ops_v2 (041) + payer_enrollment_extensions (056)

8. Source-adapter persistence

The 36-adapter rail (see SOURCE_AUTHORITY_RAIL.md) writes per-source artifact rows. Adapter-specific tables include:

• nursys_events (007), e-Notify license-status change events (LIVE; DBID 399700000147857 per memory log).
• leie_exclusions (005), OIG LEIE exclusion list snapshots.
• persona_inquiries (006), Persona IAL2 inquiry rows.
• npdb_query_workflow (052), NPDB query lifecycle (form, submit, response, receipt).
• dea_registrations (063), DEA reg + verification rows.
• state_bon_dispatch_extension (065), state Board of Nursing dispatch table.
• verifiable_integration (067), Verifiable.com integration rows.
• anti_fraud_signals (064), cross-source anomaly flags (used by the AI Governance Engine).
• license_source_metadata (046)
• source_adapter_evidence (062), source_receipts_s3_artifact (068)
• expirables_reminder_log (057)
• monitoring_action_center (043)
• trust_explorer_hardening (044)

9. Relationships and integrity

• Foreign-key discipline. Every link from a derived table back to nurses or hospitals is enforced at the database level. ON DELETE behavior is mostly RESTRICT, we do not silently delete PHI. Soft-delete is the default.
• Depth-tier enums. PostgreSQL enums for tier, actor_kind, source, decision_type. Migrations encode every enum value transactionally.
• Audit chain. No row in audit_log can be UPDATEd or DELETEd, enforced by row-level trigger plus app-side IAM policy on the deploy role.

10. What this overview does not claim

• We do not claim every table is at SOC 2 Type 2 evidence-coverage; Drata evidence collection is in progress.
• We do not claim consent_events is fully shipped, TARGET.
• We do not claim full ai_runs coverage on every router, PARTIAL.
• We do not claim third-party DB observability (e.g., DataDog DB monitoring) is wired, CloudWatch + native RDS metrics today.

End of overview.