Audit Schema

Purpose: Compliance logging, activity tracking, PHI access, and AI audit trails Tenant Scoped: Yes (all tables) RLS Enabled: Yes

Overview

The audit schema provides comprehensive logging for:

General activity tracking
PHI (Protected Health Information) access for HIPAA/DPDP compliance
LLM/AI call logging for AI audit trails
Clinical document change tracking

All audit tables are append-only with long-term retention policies.

Entities

Activity Logging

Entity: ActivityLog

Purpose: General user activity tracking

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
tenant_id	UUID	Yes	Owning tenant
user_id	UUID	No	Acting user
user_email	String	No	User email (denormalized)
session_id	String	No	Session identifier
action	Enum	Yes	create, read, update, delete, login, logout, export, print
resource_type	String	Yes	Resource type (e.g., "Patient", "Encounter")
resource_id	UUID	No	Resource identifier
resource_name	String	No	Resource display name
description	Text	No	Activity description
old_values	JSONB	No	Previous values (for updates)
new_values	JSONB	No	New values (for updates)
metadata	JSONB	No	Additional context
ip_address	String	No	Client IP address
user_agent	String	No	Browser/client info
facility_id	UUID	No	Facility context
created_at	Timestamp	Yes	Activity timestamp

Action Types:

create - Resource creation
read - Resource access/view
update - Resource modification
delete - Resource deletion
login - User authentication
logout - Session end
export - Data export
print - Document print
share - Data sharing
bulk_action - Bulk operations

Metadata Structure:

{
  "source": "web",
  "endpoint": "/api/patients/123",
  "method": "PATCH",
  "changes_summary": "Updated contact information",
  "duration_ms": 245
}

Relationships:

Belongs to: Tenant, User, Facility

PHI Access Logging

Entity: PhiAccessLog

Purpose: Protected Health Information access tracking (HIPAA/DPDP compliance)

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
tenant_id	UUID	Yes	Owning tenant
user_id	UUID	Yes	Accessing user
user_role	String	No	User role at time of access
patient_id	UUID	Yes	Patient whose data was accessed
resource_type	String	Yes	Type of PHI accessed
resource_id	UUID	No	Specific resource
access_type	Enum	Yes	view, download, print, share, export, amend
access_reason	Enum	Yes	treatment, payment, operations, emergency, patient_request, audit
reason_detail	Text	No	Detailed reason
data_categories	String[]	No	Categories of data accessed
emergency_access	Boolean	No	Break-the-glass access
emergency_justification	Text	No	Emergency justification
ip_address	String	No	Client IP
session_id	String	No	Session identifier
facility_id	UUID	No	Facility context
created_at	Timestamp	Yes	Access timestamp

Access Types:

view - Viewed patient record
download - Downloaded patient data
print - Printed patient document
share - Shared with another provider
export - Exported to external system
amend - Amended patient record

Access Reasons (HIPAA-aligned):

treatment - Treatment purposes (TPO)
payment - Payment processing (TPO)
operations - Healthcare operations (TPO)
emergency - Emergency access (break-the-glass)
patient_request - Patient-requested access
audit - Compliance audit
legal - Legal/court order
research - De-identified research

Data Categories:

["demographics", "conditions", "medications", "lab_results",
 "imaging", "notes", "billing", "insurance"]

Relationships:

Belongs to: Tenant, User, Patient, Facility

Retention: 6 years minimum (HIPAA requirement)

AI/LLM Audit Logging

Entity: LlmCallLog

Purpose: AI/LLM call audit trail for transparency and compliance

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
tenant_id	UUID	Yes	Owning tenant
user_id	UUID	No	Triggering user
facility_id	UUID	No	Facility context
encounter_id	UUID	No	Related encounter
patient_id	UUID	No	Related patient
call_type	Enum	Yes	transcription, suggestion, summary, analysis, chat
provider	String	Yes	AI provider (openai, anthropic, google)
model	String	Yes	Model identifier
input_type	Enum	Yes	audio, text, structured
input_tokens	Integer	No	Input token count
output_tokens	Integer	No	Output token count
input_hash	String	No	Hash of input (no PII stored)
output_summary	Text	No	Summary of output (no PII)
confidence_score	Decimal	No	Model confidence
latency_ms	Integer	No	Response time
status	Enum	Yes	success, error, timeout, filtered
error_message	Text	No	Error details
moderation_flags	JSONB	No	Content moderation results
cost_usd	Decimal	No	API call cost
created_at	Timestamp	Yes	Call timestamp

Call Types:

transcription - Audio transcription (AI Scribe)
suggestion - Clinical suggestion generation (CDS)
summary - Document/encounter summarization
analysis - Data analysis
chat - Interactive AI chat
coding - Diagnosis/procedure coding assistance

Moderation Flags Structure:

{
  "categories": {
    "hate": false,
    "violence": false,
    "self_harm": false,
    "sexual": false
  },
  "flagged": false
}

Note: Actual PHI is NOT stored in this table. Only hashes and summaries for audit purposes.

Relationships:

Belongs to: Tenant, User, Facility, Encounter, Patient

Retention: 2 years minimum

Document Audit Logging

Entity: DocumentAuditLog

Purpose: Clinical document change tracking with immutability

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
tenant_id	UUID	Yes	Owning tenant
document_type	String	Yes	Document type (CarePlan, Prescription, Note)
document_id	UUID	Yes	Document identifier
action	Enum	Yes	created, amended, signed, cosigned, addendum, voided
actor_id	UUID	Yes	User/practitioner who acted
actor_role	String	No	Actor role
previous_version_id	UUID	No	Previous version reference
change_summary	Text	No	Summary of changes
change_details	JSONB	No	Detailed changes
reason	Text	No	Reason for change
signature_data	JSONB	No	Digital signature info
is_legal_record	Boolean	No	Part of legal medical record
attestation	Text	No	Attestation statement
created_at	Timestamp	Yes	Action timestamp

Action Types:

created - Initial document creation
amended - Amendment to existing document
signed - Provider signature
cosigned - Co-signature by supervising provider
addendum - Addendum added
voided - Document voided/cancelled
locked - Document locked from further edits
unlocked - Document unlocked (emergency/correction)

Signature Data Structure:

{
  "signer_id": "uuid",
  "signer_name": "Dr. John Smith",
  "signer_credentials": "MD, FACC",
  "signed_at": "2024-01-15T10:30:00Z",
  "signature_type": "electronic",
  "certificate_hash": "sha256:..."
}

Relationships:

Belongs to: Tenant
References: Various clinical documents

Retention: Permanent (legal medical record)

Entity: ConsentAuditLog

Purpose: Patient consent tracking for DPDP/GDPR compliance

Attributes:

Attribute	Type	Required	Description
id	UUID	Yes	Primary identifier
tenant_id	UUID	Yes	Owning tenant
patient_id	UUID	Yes	Patient reference
consent_type	Enum	Yes	treatment, data_sharing, research, marketing
action	Enum	Yes	granted, revoked, updated
scope	JSONB	No	Consent scope details
granted_to	JSONB	No	Entities consent given to
valid_from	Timestamp	No	Consent start
valid_until	Timestamp	No	Consent end
collection_method	Enum	No	written, electronic, verbal
witness_id	UUID	No	Witness user
ip_address	String	No	If electronic
created_at	Timestamp	Yes	Action timestamp

Consent Types:

treatment - Consent to treatment
data_sharing - Share data with providers/payers
abdm - ABDM health data sharing
research - Research participation
marketing - Marketing communications
telemedicine - Telemedicine services

Relationships:

Belongs to: Tenant, Patient

Offline Sync Tracking

The offline sync tables track operations performed while devices are offline, enabling reliable synchronization when connectivity returns.

See Offline Mode for complete documentation.

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
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
payload	JSONB	Yes	Full operation payload
client_timestamp	Timestamp	Yes	When created on client
sync_status	Enum	Yes	queued, received, processing, synced, conflict, failed
conflict_type	Enum	No	version_mismatch, deleted_resource, validation_error, business_rule
resolution_strategy	Enum	No	client_wins, server_wins, merged, manual
error_message	Text	No	Error details if failed

Relationships:

Belongs to: Tenant, Facility, User

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_type	Enum	Yes	desktop, tablet, mobile
status	Enum	Yes	active, suspended, revoked
last_sync_at	Timestamp	No	Last successful sync
token_expires_at	Timestamp	No	Token expiration

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
operation_count	Integer	Yes	Number of operations in batch
status	Enum	Yes	pending, processing, completed, partial, failed

Relationships:

Belongs to: Tenant, OfflineDevice
Has many: OfflineSyncLogs

Schema Diagram

+---------------+       +---------------+       +---------------+
| activity_logs |       | phi_access_   |       | consent_      |
|               |       | logs          |       | audit_logs    |
+---------------+       +---------------+       +---------------+
       |                       |                       |
       v                       v                       v
+------------------------------------------------------------------+
|                     Compliance Dashboard                          |
+------------------------------------------------------------------+
       ^                       ^                       ^
       |                       |                       |
+---------------+       +---------------+       +---------------+
| llm_call_logs |       | document_     |       | offline_      |
|               |       | audit_logs    |       | sync_logs     |
+---------------+       +---------------+       +---------------+
                                                       ^
                                                       |
                                        +---------------+---------------+
                                        |                               |
                                +---------------+               +---------------+
                                | offline_      |               | sync_batches  |
                                | devices       |               |               |
                                +---------------+               +---------------+

Retention Policies

Table	Retention Period	Regulatory Driver
activity_logs	2 years	General compliance
phi_access_logs	6 years	HIPAA (US), DPDP (India)
llm_call_logs	2 years	AI transparency
document_audit_logs	Permanent	Medical record law
consent_audit_logs	6 years	GDPR, DPDP
offline_sync_logs	1 year	Operational
offline_devices	Until revoked + 1 year	Security
sync_batches	90 days	Operational

RLS Policies

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

CREATE POLICY tenant_isolation ON audit.phi_access_logs
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

CREATE POLICY tenant_isolation ON audit.llm_call_logs
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

CREATE POLICY tenant_isolation ON audit.document_audit_logs
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

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

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

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

Compliance Reporting

The audit schema supports:

HIPAA Compliance (USA)
- PHI access reports
- Breach notification data
- Security incident tracking
DPDP Compliance (India)
- Data processing records
- Consent management
- Data principal access requests
AI Transparency
- LLM usage reports
- Model decision audit
- Bias/fairness monitoring
Clinical Quality
- Document completion metrics
- Amendment tracking
- Signature compliance
Offline Operations
- Sync status monitoring
- Conflict resolution tracking
- Device authorization audit

Security Considerations

Append-Only: Audit tables should be append-only (no UPDATE/DELETE)
Encryption: Sensitive fields encrypted at rest
Access Control: Limited to compliance officers and admins
Integrity: Checksums for tamper detection
Archival: Automated archival to cold storage after retention period

Entity Summary

Entity	Tenant Scoped	RLS	Description
ActivityLog	Yes	Yes	General activity tracking
PhiAccessLog	Yes	Yes	HIPAA/DPDP PHI access
LlmCallLog	Yes	Yes	AI/LLM call audit
DocumentAuditLog	Yes	Yes	Clinical document changes
ConsentAuditLog	Yes	Yes	Patient consent tracking
OfflineSyncLog	Yes	Yes	Offline operation tracking
OfflineDevice	Yes	Yes	Authorized offline devices
SyncBatch	Yes	Yes	Atomic sync groups

Total Tables: 8

Audit Schema

OverviewCopy Anchor Link

EntitiesCopy Anchor Link

Activity LoggingCopy Anchor Link

Entity: ActivityLogCopy Anchor Link

PHI Access LoggingCopy Anchor Link

Entity: PhiAccessLogCopy Anchor Link

AI/LLM Audit LoggingCopy Anchor Link

Entity: LlmCallLogCopy Anchor Link

Document Audit LoggingCopy Anchor Link

Entity: DocumentAuditLogCopy Anchor Link

Consent Audit LoggingCopy Anchor Link

Entity: ConsentAuditLogCopy Anchor Link

Offline Sync TrackingCopy Anchor Link

Entity: OfflineSyncLogCopy Anchor Link

Entity: OfflineDeviceCopy Anchor Link

Entity: SyncBatchCopy Anchor Link

Schema DiagramCopy Anchor Link

Retention PoliciesCopy Anchor Link

RLS PoliciesCopy Anchor Link

Compliance ReportingCopy Anchor Link

Security ConsiderationsCopy Anchor Link

Entity SummaryCopy Anchor Link

On this page

Overview

Entities

Activity Logging

Entity: ActivityLog

PHI Access Logging

Entity: PhiAccessLog

AI/LLM Audit Logging

Entity: LlmCallLog

Document Audit Logging

Entity: DocumentAuditLog

Consent Audit Logging

Entity: ConsentAuditLog

Offline Sync Tracking

Entity: OfflineSyncLog

Entity: OfflineDevice

Entity: SyncBatch

Schema Diagram

Retention Policies

RLS Policies

Compliance Reporting

Security Considerations

Entity Summary