Database schema

The database schema consists of multiple tables and schemas that can be categorized into several groups:

• FHIR resource and history tables

• System schemas and tables

FHIR resource and history tables

All FHIR resources are stored in the public schema. Aidbox creates a table for each FHIR resource type with the same name as the resource type in lowercase (e.g., patient for the Patient resource type). All resource tables have the same structure:

• id: Resource ID (primary key, text)

• txid: Version ID (bigint)

• cts: Creation timestamp (timestamp with time zone)

• ts: Last update timestamp (timestamp with time zone)

• resource_type: The type of resource (text)

• status: Resource status (enum)

• resource: The actual resource data (jsonb)

Additional details

• txid: Aidbox tracks version IDs using PostgreSQL sequence transaction_id_seq. This sequence is shared between all resource tables, functioning as a global counter incremented for each new resource version.

• status: Aidbox uses PostgreSQL enum type resource_status for the status column to describe the lifecycle of a resource:

  CREATE TYPE resource_status AS ENUM (
    'created', 
    'updated', 
    'deleted', 
    'recreated'
  );    

• resource: FHIR resources are stored as JSONB documents, which enables efficient storage and querying using PostgreSQL's JSON functions.

History tables

For each resource table, Aidbox creates a matching history table by adding _history to the table name (e.g., patient_history). These history tables track all previous versions of resources. When you update or delete a resource, Aidbox moves the existing version to the history table before saving the new version.

Both resource and history tables share the same structure and columns, but only history tables have a composite primary key: (id, txid).

For example, the Patient resource table and its history table are defined as follows:

create table "patient" (
  id text primary key,
  txid bigint not null,
  ts timestamptz default now(),
  cts timestamptz default now(),     
  resource_type text,                
  status resource_status not null,  
  resource jsonb not null            
);

create table "patient_history" (
  id text,               
  txid bigint not null,              
  ts timestamptz default now(),      
  cts timestamptz default now(),     
  resource_type text,                
  status resource_status not null,  
  resource jsonb not null,
  primary key (id, txid)
);

System schemas

SOF (SQL On FHIR)

The sof schema contains views and tables related to SQL on FHIR functionality. All ViewDefinition-defined views and tables are created in this schema, providing simplified SQL-based access to FHIR data.

FAR Schema (FHIR Artifact Registry)

The far schema contains tables related to FHIR artifact management and registry functionality. It's primarily used for internal Aidbox operations and typically not accessed directly by users:

• atlas: Stores Atlas configurations and metadata

• canonicalresource: Manages canonical FHIR resources

• package: Stores FHIR packages

• packagename: Manages package names and metadata

• resource: Stores general FHIR resources

TDS Schema (Topic Destination Storage)

The tds schema contains tables related to event and message handling:

• event_storage: Stores events and their destinations