Database Schema

This document describes the current PostgreSQL schema direction for Nexus.

Source: live database metadata via information_schema and pg_constraint
Generated: 2026-06-25

Schema Direction

Nexus is moving away from a single-schema PostgreSQL layout.

Current state: almost all tables live in public
Target state: Nexus uses multiple PostgreSQL schemas to separate platform concerns from domain modules
First namespace: platform

Planned PostgreSQL Schemas

`platform`

System objects for accounts, tenancy, sessions, preferences, token revocation, and group authorization.

platform.users
platform.user_preferences
platform.user_sessions
platform.revoked_tokens
platform.user_groups
platform.user_group_memberships
platform.tenants

This schema is the first extraction from public because it contains the tenancy boundary, authentication records, and authorization relationships used by every other module.

`public`

For now, the remaining module tables can continue to live in public until each domain gets its own namespace strategy.

`census`

Person and identity records used across modules.

census.persons
census.identities

`education`

Education records. These tables currently live in the census namespace while the product module is named Education.

census.schools
census.school_years
census.school_calendars
census.school_enrollments
census.school_employments
census.school_employment_assignments

`assets`

Asset catalog, inventory, assignment, circulation, and repair records.

assets.asset_manufacturers
assets.asset_types
assets.asset_models
assets.assets
assets.asset_assignments
assets.asset_circulations
assets.asset_repairs

`locations`

Place hierarchy and location typing records.

locations.location_types
locations.locations

Initial examples:

public.issues
public.projects
public.configuration_items
public.configuration_item_relationships

Schema Pages

Use these focused pages for module-level schema reference:

System Users (platform)
System Tenants (platform)
Projects
Education Records (census)
Assets (assets)
Census (census)
Configurations
Locations (locations)

This page remains the schema hub and design reference. The lower snapshot section still reflects the current single-schema database state and has not yet been rewritten into namespace-qualified tables.

alembic_version

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`version_num`	`character varying`	NO	`-`	Yes	Yes	No	-	-

None

asset_assignments

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`asset_id`	`character`	NO	`-`	No	No	Yes	assets	CASCADE
`person_id`	`character`	YES	`-`	No	No	Yes	persons	RESTRICT
`location_id`	`character`	YES	`-`	No	No	Yes	locations	RESTRICT
`start_date`	`date`	NO	`-`	No	No	No	-	-
`end_date`	`date`	YES	`-`	No	No	No	-	-
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`tenant_id`	`character`	NO	`-`	No	No	Yes	tenants	RESTRICT

CHECK (((((person_id IS NOT NULL))::integer + ((location_id IS NOT NULL))::integer) = 1))
CHECK (((end_date IS NULL) OR (end_date >= start_date)))

asset_circulations

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`asset_id`	`character`	NO	`-`	No	No	Yes	assets	CASCADE
`circulation_type`	`text`	NO	`-`	No	No	No	-	-
`person_id`	`character`	YES	`-`	No	No	Yes	persons	RESTRICT
`location_id`	`character`	YES	`-`	No	No	Yes	locations	RESTRICT
`occurred_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`notes`	`text`	YES	`-`	No	No	No	-	-
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`tenant_id`	`character`	NO	`-`	No	No	Yes	tenants	RESTRICT

CHECK ((circulation_type = ANY (ARRAY['issued_to_person'::text, 'returned_from_person'::text, 'moved_to_location'::text, 'received_at_location'::text, 'location_confirmation'::text])))

asset_manufacturers

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`tenant_id`	`character`	NO	`-`	No	Yes	Yes	tenants	RESTRICT
`name`	`character varying`	NO	`-`	No	Yes	No	-	-
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-

None

asset_models

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`name`	`text`	NO	`-`	No	No	No	-	-
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`tracking_type`	`text`	NO	`'serialized'::text`	No	No	No	-	-
`tenant_id`	`character`	NO	`-`	No	No	Yes	tenants	RESTRICT
`manufacturer_id`	`character`	NO	`-`	No	No	Yes	asset_manufacturers	RESTRICT
`end_of_support`	`date`	YES	`-`	No	No	No	-	-
`asset_type_id`	`character`	NO	`-`	No	No	Yes	asset_types	RESTRICT

CHECK ((tracking_type = ANY (ARRAY['serialized'::text, 'countable'::text])))

asset_repairs

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`asset_id`	`character`	NO	`-`	No	No	Yes	assets	CASCADE
`title`	`text`	NO	`-`	No	No	No	-	-
`description`	`text`	YES	`-`	No	No	No	-	-
`location_id`	`character`	YES	`-`	No	No	Yes	locations	RESTRICT
`status`	`text`	NO	`'open'::text`	No	No	No	-	-
`priority`	`text`	NO	`'normal'::text`	No	No	No	-	-
`reported_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`resolved_at`	`timestamp with time zone`	YES	`-`	No	No	No	-	-
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`tenant_id`	`character`	NO	`-`	No	No	Yes	tenants	RESTRICT

CHECK (((resolved_at IS NULL) OR (resolved_at >= reported_at)))
CHECK ((priority = ANY (ARRAY['low'::text, 'normal'::text, 'high'::text, 'critical'::text])))
CHECK ((status = ANY (ARRAY['open'::text, 'in_progress'::text, 'resolved'::text, 'cancelled'::text])))
CHECK ((char_length(title) > 0))

asset_types

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`tenant_id`	`character`	NO	`-`	No	Yes	Yes	tenants	RESTRICT
`name`	`character varying`	NO	`-`	No	Yes	No	-	-
`end_of_support_label`	`character varying`	YES	`-`	No	No	No	-	-
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-

None

assets

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`name`	`text`	NO	`-`	No	No	No	-	-
`serial_number`	`text`	YES	`-`	No	Yes	No	-	-
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`model_id`	`character`	NO	`-`	No	No	Yes	asset_models	RESTRICT
`quantity`	`integer`	NO	`1`	No	No	No	-	-
`tenant_id`	`character`	NO	`-`	No	No	Yes	tenants	RESTRICT

CHECK ((quantity > 0))

identities

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`person_id`	`character`	NO	`-`	No	No	Yes	persons	CASCADE
`last_name`	`text`	NO	`-`	No	No	No	-	-
`first_name`	`text`	NO	`-`	No	No	No	-	-
`middle_name`	`text`	YES	`-`	No	No	No	-	-
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`tenant_id`	`character`	NO	`-`	No	No	Yes	tenants	RESTRICT

None

location_types

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`name`	`character varying`	NO	`-`	No	Yes	No	-	-
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`tenant_id`	`character`	NO	`-`	No	No	Yes	tenants	RESTRICT

None

locations

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`parent_id`	`character`	YES	`-`	No	No	Yes	locations	SET NULL
`name`	`character varying`	NO	`-`	No	No	No	-	-
`type_id`	`character`	NO	`-`	No	No	Yes	location_types	RESTRICT
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`tenant_id`	`character`	NO	`-`	No	No	Yes	tenants	RESTRICT

None

persons

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`current_identity_id`	`character`	YES	`-`	No	No	Yes	identities	SET NULL
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`tenant_id`	`character`	NO	`-`	No	No	Yes	tenants	RESTRICT

None

revoked_tokens

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`jti`	`character`	NO	`-`	Yes	Yes	No	-	-
`user_id`	`character`	NO	`-`	No	No	Yes	users	CASCADE
`expires_at`	`timestamp with time zone`	NO	`-`	No	No	No	-	-
`revoked_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`tenant_id`	`character`	NO	`-`	No	No	Yes	tenants	RESTRICT

None

school_calendars

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`school_id`	`character`	NO	`-`	No	No	Yes	schools	CASCADE
`calendar_id`	`character`	NO	`-`	No	No	Yes	school_years	RESTRICT
`name`	`character varying`	NO	`-`	No	No	No	-	-
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`tenant_id`	`character`	NO	`-`	No	No	Yes	tenants	RESTRICT

None

school_employment_assignments

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`tenant_id`	`character`	NO	`-`	No	No	Yes	tenants	RESTRICT
`person_id`	`character`	NO	`-`	No	No	Yes	persons	CASCADE
`school_id`	`character`	NO	`-`	No	No	Yes	schools	CASCADE
`start_date`	`date`	NO	`-`	No	No	No	-	-
`end_date`	`date`	YES	`-`	No	No	No	-	-
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-

CHECK (((end_date IS NULL) OR (end_date >= start_date)))

school_employments

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`tenant_id`	`character`	NO	`-`	No	No	Yes	tenants	RESTRICT
`person_id`	`character`	NO	`-`	No	No	Yes	persons	CASCADE
`start_date`	`date`	NO	`-`	No	No	No	-	-
`end_date`	`date`	YES	`-`	No	No	No	-	-
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-

CHECK (((end_date IS NULL) OR (end_date >= start_date)))

school_enrollments

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`person_id`	`character`	NO	`-`	No	No	Yes	persons	CASCADE
`calendar_id`	`character`	NO	`-`	No	No	Yes	school_calendars	CASCADE
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`tenant_id`	`character`	NO	`-`	No	No	Yes	tenants	RESTRICT

None

school_years

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`name`	`character varying`	NO	`-`	No	Yes	No	-	-
`start_date`	`date`	NO	`-`	No	No	No	-	-
`end_date`	`date`	NO	`-`	No	No	No	-	-
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`tenant_id`	`character`	NO	`-`	No	No	Yes	tenants	RESTRICT

CHECK ((end_date >= start_date))

schools

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`name`	`character varying`	NO	`-`	No	Yes	No	-	-
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`tenant_id`	`character`	NO	`-`	No	No	Yes	tenants	RESTRICT

None

tenants

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	Yes	user_groups	RESTRICT
`name`	`character varying`	NO	`-`	No	Yes	No	-	-
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`tenant_admin_group_id`	`character`	NO	`-`	No	No	Yes	user_groups	RESTRICT
`tenant_users_group_id`	`character`	NO	`-`	No	No	Yes	user_groups	RESTRICT

None

user_group_memberships

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`tenant_id`	`character`	NO	`-`	No	Yes	Yes	tenants	RESTRICT
`user_id`	`character`	NO	`-`	No	Yes	Yes	users	CASCADE
`user_group_id`	`character`	NO	`-`	No	Yes	Yes	user_groups	CASCADE
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-

None

user_groups

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`tenant_id`	`character`	NO	`-`	No	Yes	Yes	tenants	RESTRICT
`name`	`character varying`	NO	`-`	No	Yes	No	-	-
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-

None

users

Column	Type	Nullable	Default	PK	Unique	FK	FK Target	On Delete
`id`	`character`	NO	`-`	Yes	Yes	No	-	-
`username`	`text`	NO	`-`	No	Yes	No	-	-
`email`	`text`	NO	`-`	No	Yes	No	-	-
`password_hash`	`text`	NO	`-`	No	No	No	-	-
`created_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`updated_at`	`timestamp with time zone`	NO	`now()`	No	No	No	-	-
`person_id`	`character`	YES	`-`	No	No	Yes	persons	SET NULL
`tenant_id`	`character`	NO	`-`	No	No	Yes	tenants	RESTRICT

None

Database Schema ​

Schema Direction ​

Planned PostgreSQL Schemas ​

platform ​

public ​

census ​

education ​

assets ​

locations ​

Schema Pages ​

alembic_version ​

asset_assignments ​

asset_circulations ​

asset_manufacturers ​

asset_models ​

asset_repairs ​

asset_types ​