SureNetwork Global Database Schema

The SureNetwork Global service owns the platform-level relational database. All tables are version-controlled via Liquibase XML changelogs in SC/SureNetwork-Global/network-global-service/src/main/resources/db/changelog/schema/ — one XML file per table.

Database: PostgreSQL
Migration tool: Liquibase
Primary keys: SERIAL / auto-increment integers for most tables; VARCHAR natural keys where appropriate (e.g. tenant.name)
Schema source: SC/SureNetwork-Global/network-global-service/src/main/resources/db/changelog/db.changelog-schema.xml

The schema is grouped into five domain areas.

---

1. Identity and Access

Covers the multi-tenant authentication boundary, platform users, user roles, and hub-based access control.

---

2. Person

Covers the person entity and all person-scoped sub-entities: contact info, addresses, organisation membership, tenancy, signing certificates, and clinical profile.

---

3. Organisation

Covers the organisation entity and its sub-entities: addresses, contacts, clinical classification, customer codes, tenant membership, and network roles.

---

4. Project

Covers the project entity and all project-scoped relationships: applications, clinical metadata, conditions, connectors, editors, hubs, roles, sponsors, and person/organisation membership.

---

5. Clinical and Provider

Covers provider taxonomies, NPI identifiers, credentials, therapeutic areas, experience records, and reference tables.

---

Complete Table Reference

All 50 tables with full column lists.

Expand full table reference

tenant

Column	Type	Constraints
name	VARCHAR	PK
server_name	VARCHAR	NOT NULL
created_date	TIMESTAMP	NOT NULL
modified_date	TIMESTAMP	NOT NULL
version	INTEGER	NOT NULL

tenant_user

Column	Type	Constraints
id	INTEGER	PK, auto-increment
tenant_name	VARCHAR	NOT NULL, FK → tenant.name (CASCADE)
person_id	INTEGER	NOT NULL, FK → person.id
type	VARCHAR	NOT NULL
username	VARCHAR	NOT NULL, UNIQUE with tenant_name
active	BOOLEAN	NOT NULL
created_date	TIMESTAMP	NOT NULL
modified_date	TIMESTAMP	NOT NULL
version	INTEGER	NOT NULL

user_role

Column	Type	Constraints
id	INTEGER	PK, auto-increment
code	VARCHAR	NOT NULL, UNIQUE with type, tenant_name
name	VARCHAR	NOT NULL
type	VARCHAR	NOT NULL
description	VARCHAR
tenant_name	VARCHAR	NOT NULL, FK → tenant.name (CASCADE)
created_date	TIMESTAMP	NOT NULL
modified_date	TIMESTAMP	NOT NULL
version	INTEGER	NOT NULL

tenant_user_role

Column	Type	Constraints
user_id	INTEGER	PK, FK → tenant_user.id (CASCADE)
role_id	INTEGER	PK, FK → user_role.id

user_role_sub_role

Column	Type	Constraints
role_id	INTEGER	PK, FK → user_role.id (CASCADE)
sub_role_id	INTEGER	PK, FK → user_role.id (CASCADE)

role

Column	Type	Constraints
id	INTEGER	PK, auto-increment
name	VARCHAR	NOT NULL, UNIQUE with tenant_name
description	VARCHAR
tenant_name	VARCHAR	NOT NULL, FK → tenant.name (CASCADE)
created_date	TIMESTAMP	NOT NULL
modified_date	TIMESTAMP	NOT NULL
version	INTEGER	NOT NULL

hub

Column	Type	Constraints
id	INTEGER	PK, auto-increment
name	VARCHAR	NOT NULL
description	VARCHAR
tenant_name	VARCHAR	NOT NULL, FK → tenant.name (CASCADE)
created_date	TIMESTAMP	NOT NULL
modified_date	TIMESTAMP	NOT NULL
version	INTEGER	NOT NULL

hub_role

Column	Type	Constraints
hub_id	INTEGER	PK, FK → hub.id (CASCADE)
role_id	INTEGER	PK, FK → role.id

hub_user_role

Column	Type	Constraints
hub_id	INTEGER	PK, FK → hub.id (CASCADE)
user_role_id	INTEGER	PK, FK → user_role.id

gadget

Column	Type	Constraints
id	INTEGER	PK, auto-increment
hub_id	INTEGER	NOT NULL, FK → hub.id (CASCADE)
name	VARCHAR	NOT NULL
type	VARCHAR	NOT NULL
alignment	VARCHAR
created_date	TIMESTAMP	NOT NULL
modified_date	TIMESTAMP	NOT NULL
version	INTEGER	NOT NULL

thumbnail

Column	Type	Constraints
id	INTEGER	PK, auto-increment
content	BYTEA	NOT NULL

person

Column	Type	Constraints
id	INTEGER	PK, auto-increment
domain	VARCHAR	NOT NULL
first_name	VARCHAR	NOT NULL
middle_name	VARCHAR
last_name	VARCHAR	NOT NULL
gender	CHAR(1)
title	VARCHAR
job_role	VARCHAR
description	VARCHAR
email	VARCHAR	NOT NULL, UNIQUE
time_zone	VARCHAR
verification_status	VARCHAR
comments	VARCHAR
thumbnail_id	INTEGER	FK → thumbnail.id
created_date	TIMESTAMP
modified_date	TIMESTAMP
version	INTEGER

person_address

Column	Type	Constraints
person_id	INTEGER	PK, FK → person.id
address_id	INTEGER	PK, FK → address.id (CASCADE)

person_contact

Column	Type	Constraints
person_id	INTEGER	PK, FK → person.id
contact_id	INTEGER	PK, FK → contact.id (CASCADE)

person_organization

Column	Type	Constraints
person_id	INTEGER	PK, FK → person.id (CASCADE)
organization_id	INTEGER	PK, FK → organization.id (CASCADE)

person_tenant

Column	Type	Constraints
person_id	INTEGER	PK, FK → person.id (CASCADE)
tenant_name	VARCHAR	PK, FK → tenant.name (CASCADE)

person_organization_role

Column	Type	Constraints
person_id	INTEGER	PK, FK → person.id (CASCADE)
role_id	INTEGER	PK, FK → role.id (CASCADE)

address

Column	Type	Constraints
id	INTEGER	PK, auto-increment
type	VARCHAR(4)	NOT NULL
first_line	VARCHAR
second_line	VARCHAR
city	VARCHAR
state	VARCHAR(2)
province	VARCHAR
post_code	VARCHAR
country	VARCHAR(2)	NOT NULL

address_location

Column	Type	Constraints
address_id	INTEGER	PK, FK → address.id (CASCADE)
latitude	DOUBLE	NOT NULL
longitude	DOUBLE	NOT NULL

contact

Column	Type	Constraints
id	INTEGER	PK, auto-increment
email	VARCHAR
phone	VARCHAR
mobile_phone	VARCHAR
fax	VARCHAR

signature

Column	Type	Constraints
person_id	INTEGER	PK, FK → person.id (CASCADE)
appearance	VARCHAR	NOT NULL
created_date	TIMESTAMP	NOT NULL

signer

Column	Type	Constraints
person_id	INTEGER	PK, FK → person.id (CASCADE)
status	VARCHAR	NOT NULL
status_date	DATE
created_date	TIMESTAMP	NOT NULL
modified_date	TIMESTAMP	NOT NULL
version	INTEGER	NOT NULL

signer_certificate

Column	Type	Constraints
person_id	INTEGER	PK, FK → person.id (CASCADE)
mime_type	VARCHAR	NOT NULL
content	BYTEA	NOT NULL
issue_date	DATE	NOT NULL
created_date	TIMESTAMP	NOT NULL

person_clinical

Column	Type	Constraints
person_id	INTEGER	PK, FK → person.id (CASCADE)

person_study

Column	Type	Constraints
person_id	INTEGER	PK, FK → person.id (CASCADE)
study_id	VARCHAR	PK (trigger-enforced FK → ctgov.studies)

organization

Column	Type	Constraints
id	INTEGER	PK, auto-increment
domain	VARCHAR	NOT NULL
name	VARCHAR	NOT NULL
other_name	VARCHAR
label	VARCHAR
website_url	VARCHAR	UNIQUE
email_domains	VARCHAR[]
description	VARCHAR
time_zone	VARCHAR
comments	VARCHAR
thumbnail_id	INTEGER	FK → thumbnail.id
review_status	VARCHAR	NOT NULL
created_date	TIMESTAMP
modified_date	TIMESTAMP
version	INTEGER

organization_address

Column	Type	Constraints
organization_id	INTEGER	PK, FK → organization.id
address_id	INTEGER	PK, FK → address.id (CASCADE)

organization_contact

Column	Type	Constraints
organization_id	INTEGER	PK, FK → organization.id
contact_id	INTEGER	PK, FK → contact.id (CASCADE)

organization_tenant

Column	Type	Constraints
organization_id	INTEGER	PK, FK → organization.id (CASCADE)
tenant_name	VARCHAR	PK, FK → tenant.name (CASCADE)

organization_clinical

Column	Type	Constraints
organization_id	INTEGER	PK, FK → organization.id (CASCADE)
type	VARCHAR	NOT NULL

organization_customer

Column	Type	Constraints
organization_id	INTEGER	PK, FK → organization.id (CASCADE)
code	VARCHAR	NOT NULL
name	VARCHAR	NOT NULL

organization_role

Column	Type	Constraints
organization_id	INTEGER	PK, FK → organization.id (CASCADE)
role_id	INTEGER	PK, FK → role.id (CASCADE)

organization_therapeutic_area

Column	Type	Constraints
organization_id	INTEGER	PK, FK → organization_clinical.organization_id (CASCADE)
mesh_id	VARCHAR	PK, FK → medical_subject_heading.id
ordinal	SMALLINT	NOT NULL

organization_provider_identifier

Column	Type	Constraints
organization_id	INTEGER	PK, FK → organization_clinical.organization_id
provider_identifier_id	INTEGER	PK, FK → provider_identifier.id (CASCADE)
ordinal	SMALLINT	NOT NULL

organization_provider_taxonomy

Column	Type	Constraints
organization_id	INTEGER	PK, FK → organization_clinical.organization_id
provider_taxonomy_id	INTEGER	PK, FK → provider_taxonomy.id (CASCADE)
ordinal	SMALLINT	NOT NULL

project

Column	Type	Constraints
id	INTEGER	PK, auto-increment
domain	VARCHAR	NOT NULL
name	VARCHAR	NOT NULL
owner_id	INTEGER	FK → person.id
countries	VARCHAR[]
start_date	DATE
end_date	DATE
description	VARCHAR
thumbnail_id	INTEGER	FK → thumbnail.id
tenant_name	VARCHAR	NOT NULL, FK → tenant.name (CASCADE)
created_date	TIMESTAMP	NOT NULL
modified_date	TIMESTAMP	NOT NULL
version	INTEGER	NOT NULL

project_application

Column	Type	Constraints
project_id	INTEGER	PK, FK → project.id (CASCADE)
application	VARCHAR	PK
sure_clinical_id	VARCHAR

project_clinical

Column	Type	Constraints
project_id	INTEGER	PK, FK → project.id (CASCADE)
identifier	VARCHAR
protocol_id	VARCHAR
indication	VARCHAR
product_name	VARCHAR
product_type	VARCHAR
overall_status	VARCHAR
enrollment_status	VARCHAR
phase	VARCHAR	NOT NULL
regulatory_agency	VARCHAR
registry_id	VARCHAR
registry_url	VARCHAR
study_id	VARCHAR

project_condition

Column	Type	Constraints
project_id	INTEGER	PK, FK → project_clinical.project_id (CASCADE)
mesh_id	VARCHAR	PK, FK → medical_subject_heading.id
ordinal	SMALLINT	NOT NULL

project_connector

Column	Type	Constraints
project_id	INTEGER	PK, FK → project.id (CASCADE)
connector	VARCHAR	PK

project_editor

Column	Type	Constraints
project_id	INTEGER	PK, FK → project.id (CASCADE)
person_id	INTEGER	PK, FK → person.id (CASCADE)

project_hub

Column	Type	Constraints
project_id	INTEGER	PK, FK → project.id (CASCADE)
hub_id	INTEGER	PK, FK → hub.id (CASCADE)

project_role

Column	Type	Constraints
id	INTEGER	PK, auto-increment
name	VARCHAR	NOT NULL
description	VARCHAR
project_id	INTEGER	NOT NULL, FK → project.id (CASCADE)
role_id	INTEGER	FK → role.id
upload	BOOLEAN	NOT NULL
share	BOOLEAN	NOT NULL
delete	BOOLEAN	NOT NULL
created_date	TIMESTAMP	NOT NULL
modified_date	TIMESTAMP	NOT NULL
version	INTEGER	NOT NULL

project_role_template

Column	Type	Constraints
id	INTEGER	PK, auto-increment
name	VARCHAR	NOT NULL
description	VARCHAR
role_id	INTEGER	FK → role.id
upload	BOOLEAN	NOT NULL
share	BOOLEAN	NOT NULL
delete	BOOLEAN	NOT NULL
tenant_name	VARCHAR	NOT NULL, FK → tenant.name (CASCADE)
created_date	TIMESTAMP	NOT NULL
modified_date	TIMESTAMP	NOT NULL
version	INTEGER	NOT NULL

project_sponsor

Column	Type	Constraints
project_id	INTEGER	PK, FK → project.id (CASCADE)
organization_id	INTEGER	PK, FK → organization.id (CASCADE)
ordinal	SMALLINT	NOT NULL

organization_project

Column	Type	Constraints
organization_id	INTEGER	PK, FK → organization.id (CASCADE)
project_id	INTEGER	PK, FK → project.id (CASCADE)
project_role_id	INTEGER	FK → project_role.id

person_project

Column	Type	Constraints
person_id	INTEGER	PK, FK → person.id (CASCADE)
project_id	INTEGER	PK, FK → project.id (CASCADE)
project_role_id	INTEGER	FK → project_role.id
experience	BOOLEAN	NOT NULL

credential

Column	Type	Constraints
id	INTEGER	PK
acronym	VARCHAR	NOT NULL
category	VARCHAR	NOT NULL
degree	VARCHAR	NOT NULL
description	VARCHAR

person_credential

Column	Type	Constraints
person_id	INTEGER	PK, FK → person.id (CASCADE)
credential_id	INTEGER	PK, FK → credential.id
ordinal	SMALLINT	NOT NULL

experience

Column	Type	Constraints
id	INTEGER	PK, auto-increment
person_id	INTEGER	NOT NULL, FK → person.id (CASCADE)
type	VARCHAR	NOT NULL
organization_id	INTEGER	NOT NULL, FK → organization.id
role	VARCHAR	NOT NULL
start_date	DATE	NOT NULL
end_date	DATE
location	VARCHAR
description	VARCHAR

experience_certification

Column	Type	Constraints
experience_id	INTEGER	PK, FK → experience.id (CASCADE)
person_id	INTEGER	NOT NULL, FK → person.id
date	DATE	NOT NULL
description	VARCHAR

professional_organization

Column	Type	Constraints
id	INTEGER	PK
acronym	VARCHAR	NOT NULL
name	VARCHAR	NOT NULL

person_professional_organization

Column	Type	Constraints
person_id	INTEGER	PK, FK → person_clinical.person_id (CASCADE)
professional_organization_id	INTEGER	PK, FK → professional_organization.id
ordinal	SMALLINT	NOT NULL

taxonomy

Column	Type	Constraints
id	INTEGER	PK, auto-increment
code	VARCHAR(10)	NOT NULL, UNIQUE
grouping	VARCHAR	NOT NULL
classification	VARCHAR	NOT NULL
specialization	VARCHAR
definition	VARCHAR

provider_taxonomy

Column	Type	Constraints
id	INTEGER	PK, auto-increment
taxonomy_id	INTEGER	NOT NULL, FK → taxonomy.id
primary	BOOLEAN	NOT NULL
state	VARCHAR(2)
country	VARCHAR(2)	NOT NULL

provider_license

Column	Type	Constraints
provider_taxonomy_id	INTEGER	PK, FK → provider_taxonomy.id (CASCADE)
number	VARCHAR	NOT NULL
confirmed	BOOLEAN	NOT NULL

provider_license_content

Column	Type	Constraints
provider_license_id	INTEGER	PK, FK → provider_license.provider_taxonomy_id (CASCADE)
name	VARCHAR	NOT NULL
mime_type	VARCHAR	NOT NULL
content	BYTEA	NOT NULL

provider_identifier

Column	Type	Constraints
id	INTEGER	PK, auto-increment
identifier	VARCHAR	NOT NULL
issuer	VARCHAR	NOT NULL
state	VARCHAR(2)
country	VARCHAR(2)	NOT NULL

person_provider_taxonomy

Column	Type	Constraints
person_id	INTEGER	PK, FK → person_clinical.person_id
provider_taxonomy_id	INTEGER	PK, FK → provider_taxonomy.id (CASCADE)
ordinal	SMALLINT	NOT NULL

person_provider_identifier

Column	Type	Constraints
person_id	INTEGER	PK, FK → person_clinical.person_id
provider_identifier_id	INTEGER	PK, FK → provider_identifier.id (CASCADE)
ordinal	SMALLINT	NOT NULL

medical_subject_heading

Column	Type	Constraints
id	VARCHAR	PK
heading	VARCHAR	NOT NULL
tree_numbers	VARCHAR[]	NOT NULL
synonym	VARCHAR

person_therapeutic_area

Column	Type	Constraints
person_id	INTEGER	PK, FK → person_clinical.person_id (CASCADE)
mesh_id	VARCHAR	PK, FK → medical_subject_heading.id
ordinal	SMALLINT	NOT NULL

---

Schema Source Files

File	Purpose
SC/SureNetwork-Global/network-global-service/src/main/resources/db/changelog/db.changelog-schema.xml	Master Liquibase changelog — includes all schema files
SC/SureNetwork-Global/network-global-service/src/main/resources/db/changelog/schema/	Individual table XML files (50 files, one per table)