Offline Mode Schema Design

Status: Analysis & Design Phase Created: 2025-12-29 Purpose: Database schema additions to support offline-capable healthcare applications

Executive Summary

Many healthcare facilities, especially in India and GCC regions, operate in areas with unreliable internet connectivity. This document describes the database schema additions needed to support offline mode in the new architecture.

Key capabilities:

Clinical data can be created/modified while offline
Changes sync reliably when connectivity returns
Conflicts are detected and resolved appropriately
Complete audit trail is maintained for compliance

How Offline Sync Works

Important: PostgreSQL and Offline Mode

PostgreSQL is a server-side database and does not have native offline sync capabilities. Offline sync is an application-layer concern, not a database feature.

+---------------------+                      +---------------------+
|       CLIENT        |                      |       SERVER        |
+---------------------+                      +---------------------+
|                     |                      |                     |
|  IndexedDB          |<--- HTTP/API ------->|  PostgreSQL         |
|  (local database)   |    sync protocol     |  (source of truth)  |
|                     |                      |                     |
+---------------------+                      +---------------------+

What Each Layer Does

Client-Side (Browser/App):

IndexedDB stores data locally for offline access
Service Worker intercepts network requests
Background Sync API queues mutations when offline
Application logic handles conflict detection

Server-Side (PostgreSQL):

Remains the source of truth
The schema additions in this document track sync state
Handles conflict resolution when data syncs back
Maintains audit trail for compliance

What This Schema Provides

The tables documented here enable the server to:

Track sync state - What was synced, when, from which device
Detect conflicts - Using version columns and timestamps
Resolve conflicts - Per-entity resolution strategies
Audit offline activity - Complete trail for compliance
Manage devices - Authorize and revoke offline access

Schema Additions Overview

Schema	New Entity	Purpose
audit	OfflineSyncLog	Track all offline operations and sync status
audit	OfflineDevice	Authorized devices for offline access
audit	SyncBatch	Group operations for atomic sync
clinical	OfflinePrescription	Review workflow for offline prescriptions
clinical	OfflineEncounter	Track offline encounter modifications
identity	FacilityOfflineSettings	Per-facility offline configuration

Total New Tables: 6

Audit Schema: Offline Sync Tracking

Entity: OfflineSyncLog

Purpose: Track all operations performed offline and their sync status

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
tenant_id	UUID	Yes	Tenant reference
facility_id	UUID	Yes	Facility where operation occurred
user_id	UUID	Yes	User who performed operation
device_id	String	Yes	Unique device identifier
device_name	String	No	Device display name
operation_type	Enum	Yes	create, update, delete
resource_schema	String	Yes	Schema name (clinical, billing)
resource_type	String	Yes	Table/entity name
resource_id	UUID	Yes	Resource identifier
offline_id	UUID	No	Client-generated temp ID (for creates)
payload	JSONB	Yes	Full operation payload
client_timestamp	Timestamp	Yes	When created on client
received_at	Timestamp	No	When received by server
processed_at	Timestamp	No	When processed by server
sync_status	Enum	Yes	queued, received, processing, synced, conflict, failed
conflict_type	Enum	No	version_mismatch, deleted_resource, validation_error, business_rule
conflict_data	JSONB	No	Server state at conflict time
resolution_strategy	Enum	No	client_wins, server_wins, merged, manual
resolved_by	UUID	No	User who resolved conflict
resolved_at	Timestamp	No	When conflict was resolved
error_message	Text	No	Error details if failed
retry_count	Integer	No	Number of sync attempts

Sync Status Values:

Status	Description
`queued`	Waiting in client queue
`received`	Received by server, not yet processed
`processing`	Currently being processed
`synced`	Successfully synced
`conflict`	Conflict detected, needs resolution
`failed`	Permanently failed (after retries)

Conflict Type Values:

Type	Description
`version_mismatch`	Resource was modified by another user
`deleted_resource`	Resource was deleted on server
`validation_error`	Data fails server validation
`business_rule`	Business rule violation

Relationships:

Belongs to: Tenant, Facility, User
References: OfflineDevice (via device_id)

Entity: OfflineDevice

Purpose: Track authorized devices for offline access

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
tenant_id	UUID	Yes	Tenant reference
facility_id	UUID	Yes	Authorized facility
user_id	UUID	Yes	Device owner
device_id	String	Yes	Unique device fingerprint
device_name	String	No	User-friendly name
device_type	Enum	Yes	desktop, tablet, mobile
os_info	String	No	Operating system
browser_info	String	No	Browser/app info
authorized_at	Timestamp	Yes	When authorized
authorized_by	UUID	No	Admin who authorized
last_sync_at	Timestamp	No	Last successful sync
last_seen_at	Timestamp	No	Last activity
offline_token	String	No	Encrypted offline auth token
token_expires_at	Timestamp	No	Token expiration
max_offline_hours	Integer	No	Max offline duration allowed
status	Enum	Yes	active, suspended, revoked
revoked_at	Timestamp	No	When revoked
revoked_by	UUID	No	Who revoked
revoked_reason	String	No	Revocation reason

Device Status Values:

Status	Description
`active`	Device can operate offline
`suspended`	Temporarily disabled (e.g., security concern)
`revoked`	Permanently disabled

Relationships:

Belongs to: Tenant, Facility, User
Has many: OfflineSyncLogs

Entity: SyncBatch

Purpose: Group related offline operations for atomic sync

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
tenant_id	UUID	Yes	Tenant reference
device_id	String	Yes	Originating device
user_id	UUID	Yes	User who created batch
batch_number	Integer	Yes	Sequential batch number per device
operation_count	Integer	Yes	Number of operations in batch
created_at	Timestamp	Yes	Client creation time
received_at	Timestamp	No	Server receive time
completed_at	Timestamp	No	All operations processed
status	Enum	Yes	pending, processing, completed, partial, failed
sync_logs	UUID[]	No	Array of sync_log IDs in batch

Batch Status Values:

Status	Description
`pending`	Batch received, not yet processed
`processing`	Currently processing operations
`completed`	All operations successfully synced
`partial`	Some operations failed or have conflicts
`failed`	Batch processing failed

Relationships:

Belongs to: Tenant, User, OfflineDevice
Has many: OfflineSyncLogs

Clinical Schema: Offline-Aware Extensions

Entity: OfflinePrescription

Purpose: Track prescriptions created offline for mandatory review workflow

Prescriptions created offline require clinical review because they may have been created without access to the latest patient data (allergies, current medications, recent lab results).

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
tenant_id	UUID	Yes	Tenant reference
prescription_id	UUID	Yes	Link to clinical.medication_requests
offline_id	UUID	Yes	Client-generated ID
device_id	String	Yes	Device that created it
created_offline	Boolean	Yes	Always true for this table
created_at	Timestamp	Yes	Offline creation time
synced_at	Timestamp	No	When synced to server
review_status	Enum	Yes	pending_review, reviewed, flagged
reviewed_by	UUID	No	Practitioner who reviewed
reviewed_at	Timestamp	No	Review timestamp
review_notes	Text	No	Reviewer comments
flagged_reason	String	No	If flagged for concern

Review Status Values:

Status	Description
`pending_review`	Awaiting clinical review
`reviewed`	Reviewed and approved
`flagged`	Flagged for concern (drug interaction, allergy, etc.)

Clinical Workflow:

Offline Creation --> Sync --> pending_review --> reviewed/flagged
                                   |
                                   +--> Drug interaction check
                                   +--> Allergy check
                                   +--> Formulary check

Relationships:

Belongs to: Tenant, MedicationRequest

Entity: OfflineEncounter

Purpose: Track encounters created or modified offline

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
tenant_id	UUID	Yes	Tenant reference
encounter_id	UUID	Yes	Link to clinical.encounters
offline_id	UUID	Yes	Client-generated ID
device_id	String	Yes	Device used
started_offline	Boolean	Yes	Encounter started offline
ended_offline	Boolean	No	Encounter ended offline
synced_at	Timestamp	No	Initial sync time
last_offline_update	Timestamp	No	Last offline modification
offline_duration_mins	Integer	No	Total offline time during encounter

Relationships:

Belongs to: Tenant, Encounter

Identity Schema: Facility Offline Settings

Entity: FacilityOfflineSettings

Purpose: Configure offline capabilities per facility

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
tenant_id	UUID	Yes	Tenant reference
facility_id	UUID	Yes	Facility reference
offline_enabled	Boolean	Yes	Offline mode enabled
max_offline_hours	Integer	No	Max offline duration (default: 48)
max_devices_per_user	Integer	No	Device limit per user (default: 3)
auto_approve_devices	Boolean	No	Auto-approve new devices
require_admin_approval	Boolean	No	Admin must approve devices
allowed_offline_features	JSONB	No	Feature flags for offline
sync_priority_rules	JSONB	No	Priority rules for sync queue
conflict_default_strategy	Enum	No	Default conflict resolution
data_retention_days	Integer	No	Days to keep offline data
require_sync_on_login	Boolean	No	Force sync before new session
updated_at	Timestamp	Yes	Last update
updated_by	UUID	No	Who updated

Allowed Offline Features Structure:

{
  "queue_management": true,
  "patient_checkin": true,
  "view_patient_records": true,
  "create_prescriptions": true,
  "create_appointments": false,
  "billing_invoices": true,
  "lab_orders": false
}

Sync Priority Rules Structure:

{
  "priority_order": [
    { "resource": "encounters", "priority": 1 },
    { "resource": "prescriptions", "priority": 2 },
    { "resource": "appointments", "priority": 3 },
    { "resource": "invoices", "priority": 4 }
  ],
  "batch_size": 50,
  "retry_intervals_seconds": [30, 60, 300, 900, 3600]
}

Conflict Default Strategy Values:

Strategy	Description
`client_wins`	Client changes always win
`server_wins`	Server state always wins
`manual`	Require manual resolution

Relationships:

Belongs to: Tenant, Facility

Version Tracking for Conflict Detection

All offline-syncable entities need version tracking columns for optimistic locking.

Version Tracking Columns

Add these columns to offline-syncable tables in clinical.* and billing.* schemas:

Column	Type	Description
version	Integer	Incremented on each update
updated_at	Timestamp	Last modification time
updated_by	UUID	User who last modified
offline_created	Boolean	Was created offline
offline_modified	Boolean	Was last modified offline

Optimistic Locking Pattern

Client State                    Server State
-------------                   ------------
version: 5                      version: 5
data: {...}                     data: {...}

Client edits offline            Server receives another update
version: 5 (unchanged)          version: 6 (incremented)

Client syncs with version: 5
Server detects: 5 < 6
--> CONFLICT (version_mismatch)

Conflict Resolution Patterns

Different data types require different conflict resolution strategies based on clinical safety and business rules.

Pattern 1: Last-Write-Wins (Optimistic)

Use for: Queue position, check-in status, non-critical metadata

Server version: N
Client sends: version N, changes
Result: Accept if server still at N, reject if N+1

Rationale: Low-risk data where latest state is most important.

Pattern 2: Server-Authoritative

Use for: Appointments, slot availability, financial transactions

Conflict detected
Result: Server state wins, client notified of rejection

Rationale: Consistency critical - can't double-book appointments or create duplicate payments.

Pattern 3: Client-Wins with Mandatory Review

Use for: Prescriptions, clinical notes, diagnoses

Conflict detected
Result: Client state applied, flagged for clinical review

Rationale: Clinical data entered during patient encounter should be preserved, but requires review for safety.

Pattern 4: Merge with Manual Resolution

Use for: Patient demographics, contact information

Conflict detected
Result: Both versions preserved, user resolves via UI

Rationale: Both updates may contain valid information that should be merged.

Resolution Strategy by Entity

Entity	Default Strategy	Rationale
Queue Position	Last-Write-Wins	Real-time state
Patient Check-in	Last-Write-Wins	Real-time state
Appointment	Server-Authoritative	No double-booking
Encounter	Client-Wins + Review	Clinical data preservation
Prescription	Client-Wins + Review	Clinical safety
Vitals/Observations	Client-Wins	Clinical data preservation
Invoice	Server-Authoritative	Financial integrity
Payment	Server-Authoritative	Financial integrity
Patient Demographics	Manual Merge	May need human judgment

Schema Diagram

+------------------------------------------------------------------+
|  AUDIT SCHEMA - Offline Support                                   |
+------------------------------------------------------------------+
|                                                                    |
|  +------------------+       +------------------+                   |
|  | offline_devices  |------>| offline_sync_    |                   |
|  +------------------+       | logs             |                   |
|         |                   +------------------+                   |
|         |                          |                               |
|         v                          v                               |
|  +------------------+       +------------------+                   |
|  | sync_batches     |       | (links to any    |                   |
|  +------------------+       |  synced entity)  |                   |
|                             +------------------+                   |
+------------------------------------------------------------------+

+------------------------------------------------------------------+
|  CLINICAL SCHEMA - Offline Extensions                             |
+------------------------------------------------------------------+
|                                                                    |
|  +------------------+       +------------------+                   |
|  | offline_         |       | offline_         |                   |
|  | prescriptions    |       | encounters       |                   |
|  +------------------+       +------------------+                   |
|         |                          |                               |
|         v                          v                               |
|  +------------------+       +------------------+                   |
|  | medication_      |       | encounters       |                   |
|  | requests         |       |                  |                   |
|  +------------------+       +------------------+                   |
+------------------------------------------------------------------+

+------------------------------------------------------------------+
|  IDENTITY SCHEMA - Offline Configuration                          |
+------------------------------------------------------------------+
|                                                                    |
|  +------------------+                                              |
|  | facility_offline |                                              |
|  | _settings        |                                              |
|  +------------------+                                              |
|         |                                                          |
|         v                                                          |
|  +------------------+                                              |
|  | facilities       |                                              |
|  +------------------+                                              |
+------------------------------------------------------------------+

RLS Policies

All offline tables use tenant-based Row-Level Security:

-- OfflineSyncLog: tenant isolation
CREATE POLICY tenant_isolation ON audit.offline_sync_logs
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- OfflineDevice: tenant isolation
CREATE POLICY tenant_isolation ON audit.offline_devices
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- SyncBatch: tenant isolation
CREATE POLICY tenant_isolation ON audit.sync_batches
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- OfflinePrescription: tenant isolation
CREATE POLICY tenant_isolation ON clinical.offline_prescriptions
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- OfflineEncounter: tenant isolation
CREATE POLICY tenant_isolation ON clinical.offline_encounters
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- FacilityOfflineSettings: tenant isolation
CREATE POLICY tenant_isolation ON identity.facility_offline_settings
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

Security Considerations

Device Authorization

Explicit Authorization: Offline devices must be explicitly authorized per facility
Admin Approval Option: Facilities can require admin approval for new devices
Device Limits: Configurable max devices per user (default: 3)

Token Management

Token Expiry: Offline tokens have configurable expiry (default: 48 hours)
Force Sync: Can require sync before new session starts
Remote Revocation: Devices can be remotely revoked, forcing re-authentication

Data Protection

Client Encryption: Client-side storage must encrypt PHI at rest (application responsibility)
Minimal Data: Only sync data needed for offline functionality
Retention Limits: Configurable data retention on client

Audit Trail

Complete Logging: All offline operations logged with device and timestamp
Conflict Tracking: All conflicts and resolutions tracked
Device History: Full device authorization/revocation history

Compliance Considerations

HIPAA (USA)

PHI access logged in audit.offline_sync_logs
Device authorization tracked for access control
Conflict resolution audited for data integrity

DPDP (India)

Consent for offline data storage (application layer)
Data retention limits enforced via data_retention_days
Right to erasure supported via device revocation

General

All offline activity creates audit trail
Conflict resolution traceable
Device lifecycle fully documented

Client-Side Technology Options

While this document focuses on the server-side schema, here are the client technologies that work with this design:

Technology	Purpose	Notes
IndexedDB	Local database	Store offline data
Service Worker	Network proxy	Intercept requests, serve cached data
Background Sync API	Queue mutations	Sync when online
Workbox	Service Worker toolkit	Simplifies implementation
Dexie.js	IndexedDB wrapper	Easier IndexedDB API

Alternative: Postgres-Native Sync

If custom sync is too complex, consider:

Option	Description	Trade-offs
PowerSync	Postgres sync service	Commercial, production-ready
ElectricSQL	Postgres-native sync	Open source, newer

These add a sync layer between PostgreSQL and the client, potentially simplifying implementation.

Entity Summary

Schema	Entity	Purpose	RLS
audit	OfflineSyncLog	Track offline operations	Yes
audit	OfflineDevice	Authorized devices	Yes
audit	SyncBatch	Atomic sync groups	Yes
clinical	OfflinePrescription	Prescription review workflow	Yes
clinical	OfflineEncounter	Encounter offline tracking	Yes
identity	FacilityOfflineSettings	Per-facility configuration	Yes

Schema Overview - Overall schema architecture
Clinical Schema - Clinical entities that support offline
Audit Schema - Audit logging patterns
Identity Schema - Facility configuration

Offline Mode Schema Design

Executive SummaryCopy Anchor Link

How Offline Sync WorksCopy Anchor Link

Important: PostgreSQL and Offline ModeCopy Anchor Link

What Each Layer DoesCopy Anchor Link

What This Schema ProvidesCopy Anchor Link

Schema Additions OverviewCopy Anchor Link

Audit Schema: Offline Sync TrackingCopy Anchor Link

Entity: OfflineSyncLogCopy Anchor Link

Entity: OfflineDeviceCopy Anchor Link

Entity: SyncBatchCopy Anchor Link

Clinical Schema: Offline-Aware ExtensionsCopy Anchor Link

Entity: OfflinePrescriptionCopy Anchor Link

Entity: OfflineEncounterCopy Anchor Link

Identity Schema: Facility Offline SettingsCopy Anchor Link

Entity: FacilityOfflineSettingsCopy Anchor Link

Version Tracking for Conflict DetectionCopy Anchor Link

Version Tracking ColumnsCopy Anchor Link

Optimistic Locking PatternCopy Anchor Link

Conflict Resolution PatternsCopy Anchor Link

Pattern 1: Last-Write-Wins (Optimistic)Copy Anchor Link

Pattern 2: Server-AuthoritativeCopy Anchor Link

Pattern 3: Client-Wins with Mandatory ReviewCopy Anchor Link

Pattern 4: Merge with Manual ResolutionCopy Anchor Link

Resolution Strategy by EntityCopy Anchor Link

Schema DiagramCopy Anchor Link

RLS PoliciesCopy Anchor Link

Security ConsiderationsCopy Anchor Link

Device AuthorizationCopy Anchor Link

Token ManagementCopy Anchor Link

Data ProtectionCopy Anchor Link

Audit TrailCopy Anchor Link

Compliance ConsiderationsCopy Anchor Link

HIPAA (USA)Copy Anchor Link

DPDP (India)Copy Anchor Link

GeneralCopy Anchor Link

Client-Side Technology OptionsCopy Anchor Link

Alternative: Postgres-Native SyncCopy Anchor Link

Entity SummaryCopy Anchor Link

Related DocumentationCopy Anchor Link

On this page

Executive Summary

How Offline Sync Works

Important: PostgreSQL and Offline Mode

What Each Layer Does

What This Schema Provides

Schema Additions Overview

Audit Schema: Offline Sync Tracking

Entity: OfflineSyncLog

Entity: OfflineDevice

Entity: SyncBatch

Clinical Schema: Offline-Aware Extensions

Entity: OfflinePrescription

Entity: OfflineEncounter

Identity Schema: Facility Offline Settings

Entity: FacilityOfflineSettings

Version Tracking for Conflict Detection

Version Tracking Columns

Optimistic Locking Pattern

Conflict Resolution Patterns

Pattern 1: Last-Write-Wins (Optimistic)

Pattern 2: Server-Authoritative

Pattern 3: Client-Wins with Mandatory Review

Pattern 4: Merge with Manual Resolution

Resolution Strategy by Entity

Schema Diagram

RLS Policies

Security Considerations

Device Authorization

Token Management

Data Protection

Audit Trail

Compliance Considerations

HIPAA (USA)

DPDP (India)

General

Client-Side Technology Options

Alternative: Postgres-Native Sync

Entity Summary

Related Documentation