Patient Portal Service — Data Model

Status: populated Owner: TBD Last updated: 2026-04-18 Companion: Service Template · 03 platform-services · 02 DDD

1. ID Prefix Convention

Prefix	Entity
pact_	PortalAccount
pdel_	ProxyDelegation
demreq_	DemographicsUpdateRequest
paev_	PortalAccessEvent
rxreq_	MedicationRefillRequest
apptreq_	AppointmentRequest
expjob_	ExportJob

All IDs are ULIDs with the prefix prepended, e.g., pact_01JXXXXXXXXXXXXXXXXXXXXXXX.

---

2. TypeScript Interfaces

// PortalAccount
export interface PortalAccount {
  id: PortalAccountId;          // pact_*
  tenantId: TenantId;
  patientId: PatientId;
  identityProviderSubject: string; // Keycloak sub
  status: AccountStatus;          // active | suspended | pending_verification | closed
  mfaEnabled: boolean;
  preferredLanguage: string | null; // BCP 47
  lastLoginAt: Date | null;
  createdAt: Date;
  updatedAt: Date;
}

// ProxyDelegation
export interface ProxyDelegation {
  id: ProxyDelegationId;        // pdel_*
  tenantId: TenantId;
  grantorPatientId: PatientId;
  proxyPortalAccountId: PortalAccountId;
  relationshipType: ProxyRelationship;
  scope: DelegationScope[];
  validFrom: Date;
  validTo: Date | null;
  status: DelegationStatus;     // active | revoked | expired
  revokedAt: Date | null;
  createdAt: Date;
}

// DemographicsUpdateRequest
export interface DemographicsUpdateRequest {
  id: DemographicsRequestId;    // demreq_*
  tenantId: TenantId;
  patientId: PatientId;
  portalAccountId: PortalAccountId;
  requestedChanges: Record<string, unknown>; // JsonB
  status: RequestStatus;        // pending | approved | rejected | cancelled
  requestedAt: Date;
  reviewedBy: StaffId | null;
  reviewedAt: Date | null;
  rejectionReason: string | null;
}

// PortalAccessEvent (append-only)
export interface PortalAccessEvent {
  id: AccessEventId;            // paev_*
  tenantId: TenantId;
  portalAccountId: PortalAccountId;
  patientId: PatientId;
  actingAsProxy: boolean;
  proxyDelegationId: ProxyDelegationId | null;
  eventType: PortalEventType;
  resourceType: string | null;
  resourceId: string | null;
  ipHash: string | null;
  occurredAt: Date;
}

// ExportJob
export interface ExportJob {
  id: ExportJobId;              // expjob_*
  tenantId: TenantId;
  portalAccountId: PortalAccountId;
  patientId: PatientId;
  status: ExportJobStatus;      // pending | in_progress | complete | failed
  requestedAt: Date;
  completedAt: Date | null;
  downloadUrl: string | null;
  expiresAt: Date | null;
  errorDetail: string | null;
}

---

3. PostgreSQL Schema

-- PortalAccount
CREATE TABLE portal_accounts (
  id              TEXT PRIMARY KEY,
  tenant_id       TEXT NOT NULL,
  patient_id      TEXT NOT NULL,
  idp_subject     TEXT NOT NULL,
  status          TEXT NOT NULL DEFAULT 'pending_verification',
  mfa_enabled     BOOLEAN NOT NULL DEFAULT FALSE,
  preferred_lang  TEXT,
  last_login_at   TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX uq_portal_accounts_tenant_patient
  ON portal_accounts(tenant_id, patient_id);
CREATE UNIQUE INDEX uq_portal_accounts_idp_subject
  ON portal_accounts(idp_subject);
ALTER TABLE portal_accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON portal_accounts
  USING (tenant_id = current_setting('app.tenant_id'));

-- ProxyDelegation
CREATE TABLE proxy_delegations (
  id                      TEXT PRIMARY KEY,
  tenant_id               TEXT NOT NULL,
  grantor_patient_id      TEXT NOT NULL,
  proxy_portal_account_id TEXT NOT NULL REFERENCES portal_accounts(id),
  relationship_type       TEXT NOT NULL,
  scope                   TEXT[] NOT NULL,
  valid_from              DATE NOT NULL,
  valid_to                DATE,
  status                  TEXT NOT NULL DEFAULT 'active',
  revoked_at              TIMESTAMPTZ,
  created_at              TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_proxy_delegations_grantor
  ON proxy_delegations(tenant_id, grantor_patient_id);
CREATE INDEX idx_proxy_delegations_proxy_account
  ON proxy_delegations(tenant_id, proxy_portal_account_id);
ALTER TABLE proxy_delegations ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON proxy_delegations
  USING (tenant_id = current_setting('app.tenant_id'));

-- DemographicsUpdateRequest
CREATE TABLE demographics_update_requests (
  id                TEXT PRIMARY KEY,
  tenant_id         TEXT NOT NULL,
  patient_id        TEXT NOT NULL,
  portal_account_id TEXT NOT NULL REFERENCES portal_accounts(id),
  requested_changes JSONB NOT NULL,
  status            TEXT NOT NULL DEFAULT 'pending',
  requested_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  reviewed_by       TEXT,
  reviewed_at       TIMESTAMPTZ,
  rejection_reason  TEXT
);
CREATE INDEX idx_demreq_tenant_patient
  ON demographics_update_requests(tenant_id, patient_id);
ALTER TABLE demographics_update_requests ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON demographics_update_requests
  USING (tenant_id = current_setting('app.tenant_id'));

-- PortalAccessEvent (append-only; partitioned by month)
CREATE TABLE portal_access_events (
  id                  TEXT NOT NULL,
  tenant_id           TEXT NOT NULL,
  portal_account_id   TEXT NOT NULL,
  patient_id          TEXT NOT NULL,
  acting_as_proxy     BOOLEAN NOT NULL DEFAULT FALSE,
  proxy_delegation_id TEXT,
  event_type          TEXT NOT NULL,
  resource_type       TEXT,
  resource_id         TEXT,
  ip_hash             TEXT,
  occurred_at         TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (occurred_at);
CREATE INDEX idx_pae_tenant_account
  ON portal_access_events(tenant_id, portal_account_id, occurred_at DESC);
ALTER TABLE portal_access_events ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON portal_access_events
  USING (tenant_id = current_setting('app.tenant_id'));
-- Monthly partitions managed by pg_partman

-- ExportJob
CREATE TABLE export_jobs (
  id                TEXT PRIMARY KEY,
  tenant_id         TEXT NOT NULL,
  portal_account_id TEXT NOT NULL REFERENCES portal_accounts(id),
  patient_id        TEXT NOT NULL,
  status            TEXT NOT NULL DEFAULT 'pending',
  requested_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  completed_at      TIMESTAMPTZ,
  download_url      TEXT,
  expires_at        TIMESTAMPTZ,
  error_detail      TEXT
);
CREATE INDEX idx_export_jobs_account
  ON export_jobs(tenant_id, portal_account_id);
ALTER TABLE export_jobs ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON export_jobs
  USING (tenant_id = current_setting('app.tenant_id'));

-- Outbox (transactional outbox)
CREATE TABLE outbox (
  id          TEXT PRIMARY KEY,
  tenant_id   TEXT NOT NULL,
  subject     TEXT NOT NULL,
  payload     JSONB NOT NULL,
  published   BOOLEAN NOT NULL DEFAULT FALSE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_outbox_unpublished
  ON outbox(published, created_at) WHERE published = FALSE;

---

4. Indexes Summary

Table	Index	Purpose
portal_accounts	uq_portal_accounts_tenant_patient	Unique patient per tenant
portal_accounts	uq_portal_accounts_idp_subject	Keycloak sub lookup
proxy_delegations	idx_proxy_delegations_grantor	List delegations for grantor
proxy_delegations	idx_proxy_delegations_proxy_account	List delegations for proxy
portal_access_events	idx_pae_tenant_account	Access log pagination
export_jobs	idx_export_jobs_account	Poll export status
outbox	idx_outbox_unpublished	Relay worker polling

---

5. Data Residency

portal_accounts, proxy_delegations, and portal_access_events contain PHI. PostgreSQL is deployed within the tenant's designated data-residency region (Afghanistan MoPH: AFG region). Cross-region replication is disabled for PHI tables. export_jobs download URLs point to tenant-region object storage only.