AdminHQ (Core) Complete Schema Documentation

Overview

The AdminHQ Core module provides the foundational infrastructure for the Tellus EHS platform, including multi-tenant company management, user authentication, role-based access control (RBAC), subscription management, and onboarding workflows.

Table Naming Convention

All tables follow a consistent naming convention:

core_config_* : Configuration tables (system-wide, read-mostly, seeded in migrations)
core_data_* : Data tables (tenant-specific, frequently written, user-generated)
core_junction_* : Junction tables (many-to-many relationships, association tables)
core_module_* : Module-specific tables

Key Design Principles

Multi-Tenancy: Complete data isolation via company_id foreign keys
RBAC: Hierarchical role-based access control with system templates and company customization
Subscription Model: Versioned plans with entitlements and company-level overrides
Module System: Industry-specific module availability with feature capabilities
Audit Trails: Complete logging of authentication and configuration changes

Architecture Overview

Multi-Tenant Architecture

┌─────────────────────────────────────────────────────────────────────────────┐
│                           PLATFORM CONFIGURATION                             │
│                    (System-wide, seeded in migrations)                       │
├─────────────────────────────────────────────────────────────────────────────┤
│  Plans & Entitlements    │  Modules & Features    │  System Roles           │
│  ├─ core_config_plans    │  ├─ core_config_modules│  ├─ core_config_system_ │
│  ├─ core_config_plan_    │  ├─ core_config_module_│  │   roles              │
│  │   versions            │  │   features          │  └─ core_config_        │
│  ├─ core_config_plan_    │  └─ core_config_       │      permissions        │
│  │   entitlements        │      feature_          │                         │
│  └─ core_config_         │      capabilities      │                         │
│      entitlement_defs    │                        │                         │
└─────────────────────────────────────────────────────────────────────────────┘
                                      │
                                      ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                              COMPANY (TENANT)                                │
│                        (Isolated per company_id)                             │
├─────────────────────────────────────────────────────────────────────────────┤
│  core_data_companies                                                         │
│      │                                                                       │
│      ├─► Sites (core_data_sites)                                            │
│      │   └─► Locations (core_data_site_locations)                           │
│      │                                                                       │
│      ├─► Subscriptions (core_data_company_subscriptions)                    │
│      │   └─► Plan Version (core_config_plan_versions)                       │
│      │                                                                       │
│      ├─► Enabled Modules (core_junction_company_enabled_modules)            │
│      │                                                                       │
│      ├─► Company Roles (core_data_company_roles)                            │
│      │   └─► Role Permissions (core_junction_company_role_permissions)      │
│      │                                                                       │
│      ├─► User Memberships (core_junction_company_user_memberships)          │
│      │   └─► User Roles (core_junction_company_user_roles)                  │
│      │                                                                       │
│      ├─► Invites (core_data_invites)                                        │
│      │                                                                       │
│      └─► Onboarding Checklist (core_data_onboarding_checklist)              │
└─────────────────────────────────────────────────────────────────────────────┘
                                      │
                                      ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                                  USERS                                       │
│                        (Can belong to multiple companies)                    │
├─────────────────────────────────────────────────────────────────────────────┤
│  core_data_users                                                             │
│      │                                                                       │
│      ├─► Login Audit (core_data_user_login_audit)                           │
│      ├─► Sessions (core_data_user_sessions)                                 │
│      └─► Company Memberships (core_junction_company_user_memberships)       │
│          └─► Company Roles (core_junction_company_user_roles)               │
└─────────────────────────────────────────────────────────────────────────────┘

RBAC Architecture

┌──────────────────────────────────────────────────────────────────────────┐
│                        SYSTEM ROLE TEMPLATES                              │
│                    (Platform-wide, seeded data)                           │
├──────────────────────────────────────────────────────────────────────────┤
│  SystemRole (ADMIN, MANAGER, COORDINATOR, EMPLOYEE, CONSULTANT)          │
│      │                                                                    │
│      └─► SystemRolePermissions ──► Permissions                           │
│                                    (module:feature:action)               │
└──────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ▼ (template for)
┌──────────────────────────────────────────────────────────────────────────┐
│                        COMPANY-SPECIFIC ROLES                             │
│                    (Customizable per company)                             │
├──────────────────────────────────────────────────────────────────────────┤
│  CompanyRole (can rename, add/remove permissions)                         │
│      │                                                                    │
│      ├─► base_system_role_id (optional template reference)               │
│      │                                                                    │
│      └─► CompanyRolePermissions ──► Permissions                          │
│                                                                           │
│  Example: Company renames "MANAGER" to "Site Supervisor"                  │
│           and adds custom permissions                                     │
└──────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ▼ (assigned to)
┌──────────────────────────────────────────────────────────────────────────┐
│                          USER ROLE ASSIGNMENTS                            │
│                    (Per user, per company, optional site scope)           │
├──────────────────────────────────────────────────────────────────────────┤
│  CompanyUserRole                                                          │
│      │                                                                    │
│      ├─► company_id                                                       │
│      ├─► user_id                                                          │
│      ├─► company_role_id                                                  │
│      └─► site_id (optional - for site-scoped roles)                      │
│                                                                           │
│  Example: User is "Site Supervisor" only at "Downtown Warehouse"          │
└──────────────────────────────────────────────────────────────────────────┘

Part 1: User & Authentication System

Table 1: core_data_users

Purpose: User identity and authentication management.

Key Features:

Supabase authentication integration
Multi-company user support
Last company tracking for UX improvement
Active/inactive status management

Important Columns:

Column	Type	Constraints	Description
user_id	UUID	PK	Primary identifier
supabase_uid	UUID	UNIQUE	Supabase authentication ID
email	String(255)	UNIQUE, indexed	User email address
full_name	String(255)		User's full name
phone_number	String(50)		Contact phone
avatar_url	String(500)		User profile picture URL
is_active	Boolean	default=true	Active status flag
last_company_id	UUID	FK	Last company user accessed
created_at	Timestamp		Account creation time
updated_at	Timestamp		Last update time

Indexes:

B-tree index on email
Unique constraint on supabase_uid

Relationships:

One-to-many: login_audits → UserLoginAudit
One-to-many: sessions → UserSession
One-to-many: company_memberships → CompanyUserMembership
Many-to-one: last_company → Company

Use Cases:

User account creation and management
Supabase authentication integration
Multi-company user support (consultants)
Last company tracking for quick access

Table 2: core_data_user_login_audit

Purpose: Audit trail for authentication events.

Key Features:

Complete login history
IP address and device tracking
Geographic metadata via JSONB
Authentication method tracking

Important Columns:

Column	Type	Constraints	Description
audit_id	UUID	PK	Log entry identifier
user_id	UUID	FK, indexed	User who logged in
login_at	DateTime	indexed	Timestamp of login
ip_address	INET		Client IP address
user_agent	Text		Browser/device information
device_type	String(50)		Device classification
method	String(50)		Auth method: password, otp, magic_link, sso
status	String(20)		success, failed, blocked
geo_metadata	JSONB		Geographic information

geo_metadata Structure:

{
  "country": "US",
  "city": "San Francisco",
  "region": "California",
  "timezone": "America/Los_Angeles"
}

Indexes:

Composite index on (user_id, login_at DESC)

Relationships:

Many-to-one: user → User

Use Cases:

Security audit trails
Login failure detection and blocking
Geographic access patterns
Multi-device tracking

Table 3: core_data_user_sessions

Purpose: Active session management.

Key Features:

JWT/session token storage
Expiration handling
Company context per session
Activity tracking

Important Columns:

Column	Type	Constraints	Description
session_id	UUID	PK	Session identifier
user_id	UUID	FK, indexed	User who owns session
company_id	UUID	FK	Company context for session
token	String(500)	UNIQUE	JWT or session token
expires_at	DateTime		Token expiration time
created_at	Timestamp		Session creation time
last_activity_at	Timestamp		Last activity timestamp

Indexes:

B-tree index on user_id
Unique index on token

Relationships:

Many-to-one: user → User
Many-to-one: company → Company

Use Cases:

Session tracking and validation
Multi-session management per user
Token expiration handling
Activity monitoring

Part 2: Company & Organizational Structure

Table 4: core_config_company_types

Purpose: Industry-specific company classifications with module configuration rules.

Key Features:

Industry classification during onboarding
Determines available modules per industry
Industry-specific default configurations
Regulatory compliance filtering

Important Columns:

Column	Type	Constraints	Description
company_type_id	UUID	PK	Primary identifier
code	String(50)	UNIQUE	Industry code
name	String(100)		Display name
description	Text		Detailed description
created_at	Timestamp		Creation time

Seed Data Examples:

Code	Name	Description
MANUFACTURER	Manufacturing	Manufacturing facilities
AUTO_SHOP	Auto Repair Shop	Automotive repair services
PEST_CONTROL	Pest Control	Pest management services
EHS_TRAINER	EHS Trainer	Environmental Health & Safety trainers
SCHOOL_DISTRICT	School District	K-12 educational facilities
HEALTHCARE	Healthcare	Hospitals and clinics
CONSTRUCTION	Construction	Construction companies

Relationships:

One-to-many: companies → Company
One-to-many: module_rules → CompanyTypeModuleRule
One-to-many: screen_rules → CompanyTypeScreenRule

Use Cases:

Industry classification during onboarding
Determine available modules per industry
Industry-specific default configurations
Regulatory compliance filtering

Table 5: core_data_companies

Purpose: Master company record and tenant isolation.

Key Features:

Primary tenant isolation boundary
Complete company profile
Onboarding status tracking
Regulatory identifiers (EIN, EPA ID)

Important Columns:

Column	Type	Constraints	Description
company_id	UUID	PK	Primary identifier
company_type_id	UUID	FK, indexed	Industry type
legal_name	String(255)		Official registered name
display_name	String(255)	UNIQUE	User-facing company name
address_line1	String(255)		Street address line 1
address_line2	String(255)		Street address line 2
city	String(100)		City
state_province	String(100)		State/Province
postal_code	String(20)		Postal/ZIP code
country	String(100)	default='USA'	Country
phone_number	String(50)		Contact phone
email	String(255)		Contact email
website	String(255)		Company website
business_size	String(50)		Number of employees
ein	String(20)		Employer Identification Number
epa_id	String(50)		EPA facility identifier
license_number	String(100)		Business license
onboarding_status	String(20)	default='pending'	pending, in_progress, completed
created_at	Timestamp		Creation time

Indexes:

B-tree index on company_type_id
Unique constraint on display_name

Relationships:

Many-to-one: company_type → CompanyType
One-to-many: sites → CompanySite
One-to-many: user_memberships → CompanyUserMembership
One-to-many: subscriptions → CompanySubscription
One-to-many: invites → Invite
One-to-many: onboarding_checklists → OnboardingChecklist
One-to-many: roles → CompanyRole
One-to-many: enabled_modules → CompanyEnabledModule

Use Cases:

Tenant isolation and multi-tenancy
Company profile management
Onboarding workflow tracking
Industry-specific configuration

Table 6: core_data_sites

Purpose: Company sites/facilities.

Key Features:

Multi-site company support
Site-level configuration
Site manager assignment
Geographic inventory tracking

Important Columns:

Column	Type	Constraints	Description
site_id	UUID	PK	Primary identifier
company_id	UUID	FK, indexed	Parent company
code	String(50)		Site identifier code
name	String(255)		Display name
description	Text		Site details
address_line1	String(255)		Street address line 1
address_line2	String(255)		Street address line 2
city	String(100)		City
state_province	String(100)		State/Province
postal_code	String(20)		Postal/ZIP code
country	String(100)	default='USA'	Country
timezone	String(50)		Site timezone
site_manager_name	String(255)		Primary contact name
site_manager_contact	String(255)		Contact information
is_active	Boolean	default=true	Active flag
created_at	Timestamp		Creation time
updated_at	Timestamp		Last update time

Indexes:

B-tree index on company_id
Unique constraint on (company_id, code)

Relationships:

Many-to-one: company → Company
One-to-many: locations → SiteLocation

Use Cases:

Multi-site company management
Geographic inventory tracking
Site-specific role scoping
Regulatory compliance per location

Table 7: core_data_site_locations

Purpose: Specific locations within a site (buildings, warehouses, departments).

Key Features:

Granular location tracking
Hazard classification
Geographic coordinates
Ventilation status for chemical storage

Important Columns:

Column	Type	Constraints	Description
location_id	UUID	PK	Primary identifier
site_id	UUID	FK, indexed	Parent site
name	String(255)		Location name
location_type	String(50)		warehouse, office, manufacturing, storage
hazard_type	String(100)		Hazard classification
ventilation_status	String(50)		Ventilation level
address_line1	String(255)		Street address line 1
address_line2	String(255)		Street address line 2
city	String(100)		City
state_province	String(100)		State/Province
postal_code	String(20)		Postal/ZIP code
country	String(100)	default='USA'	Country
latitude	Float		Geographic latitude
longitude	Float		Geographic longitude
created_at	Timestamp		Creation time

Indexes:

B-tree index on site_id

Relationships:

Many-to-one: site → CompanySite

Use Cases:

Granular inventory tracking by location
Hazard-specific storage rules
Geographic mapping
Environmental health & safety tracking

Part 3: Role-Based Access Control (RBAC)

Table 8: core_config_system_roles

Purpose: System-wide role templates that can be used or customized by companies.

Key Features:

Platform-wide role definitions
Template for company-specific roles
Permission bundling
Industry-standard role definitions

Important Columns:

Column	Type	Constraints	Description
system_role_id	UUID	PK	Primary identifier
code	String(50)	UNIQUE	Role code
display_name	String(100)		Display name
description	Text		Role description
created_at	Timestamp		Creation time

Seed Data:

Code	Display Name	Description
ADMIN	Administrator	Full system access
MANAGER	Manager	Site/department management
PROGRAM_COORDINATOR	Program Coordinator	EHS program coordination (OSHA Step 1)
EMPLOYEE	Employee	Basic access
CONSULTANT	EHS Consultant	Multi-company access
VIEWER	Viewer	Read-only access

Relationships:

One-to-many: permissions → SystemRolePermission
One-to-many: company_roles → CompanyRole (as base template)

Use Cases:

Define standard roles across platform
Template for company-specific role creation
Permission bundling
Industry-standard role definitions

Table 9: core_config_permissions

Purpose: Atomic permissions tied to specific features and capabilities.

Key Features:

Fine-grained access control
Hierarchical permission codes (module:feature:action)
Feature-specific permissions
Capability-level restrictions

Important Columns:

Column	Type	Constraints	Description
permission_id	UUID	PK	Primary identifier
code	String(100)	UNIQUE	Permission code (hierarchical)
name	String(255)		Display name
description	Text		What this permission allows
module_id	UUID	FK, indexed	Associated module
feature_id	UUID	FK	Associated feature
capability_id	UUID	FK	Associated capability
created_at	Timestamp		Creation time

Permission Code Examples:

Code	Description
adminhq:company:read	Read company details
adminhq:company:write	Modify company details
adminhq:users:invite	Send user invitations
chemiq:sds:upload	Upload SDS documents
chemiq:sds:delete	Delete SDS documents
chemiq:inventory:write	Modify inventory

Relationships:

Many-to-one: module → Module
Many-to-one: feature → ModuleFeature
Many-to-one: capability → FeatureCapability
One-to-many: system_role_permissions → SystemRolePermission
One-to-many: company_role_permissions → CompanyRolePermission

Use Cases:

Fine-grained access control
Feature-specific permissions
Module capability restrictions
Audit trail of who can do what

Table 10: core_junction_system_role_permissions

Purpose: Many-to-many relationship between system roles and permissions.

Important Columns:

Column	Type	Constraints	Description
system_role_id	UUID	PK, FK	System role identifier
permission_id	UUID	PK, FK	Permission identifier
created_at	Timestamp		Assignment time

Relationships:

Many-to-one: system_role → SystemRole
Many-to-one: permission → Permission

Use Cases:

Define permissions for system role templates
Audit default role capabilities
Role-to-permission mapping

Table 11: core_data_company_roles

Purpose: Company-specific roles that can be based on system templates but are customizable.

Key Features:

Customizable per company
Based on system role templates (optional)
Company-specific naming
Active/inactive status

Important Columns:

Column	Type	Constraints	Description
company_role_id	UUID	PK	Primary identifier
company_id	UUID	FK, indexed	Company that owns this role
base_system_role_id	UUID	FK	System role template (can be NULL)
role_code	String(50)		Internal code (unique within company)
display_name	String(100)		Customizable display name
description	Text		Role description
is_system_default	Boolean	default=false	Auto-created from system role
is_active	Boolean	default=true	Active status
created_at	Timestamp		Creation time
updated_at	Timestamp		Last update time

Indexes:

B-tree index on company_id
Unique constraint on (company_id, role_code)

Relationships:

Many-to-one: company → Company
Many-to-one: base_system_role → SystemRole
One-to-many: permissions → CompanyRolePermission
One-to-many: user_roles → CompanyUserRole

Use Cases:

Customize roles per company (rename "Manager" to "Site Supervisor")
Create company-specific custom roles
Role-based access control at company level
Flexible permission assignment

Table 12: core_junction_company_role_permissions

Purpose: Many-to-many relationship between company roles and permissions.

Important Columns:

Column	Type	Constraints	Description
company_role_id	UUID	PK, FK	Company role identifier
permission_id	UUID	PK, FK	Permission identifier
created_at	Timestamp		Assignment time

Relationships:

Many-to-one: company_role → CompanyRole
Many-to-one: permission → Permission

Use Cases:

Assign permissions to company-specific roles
Override system role permissions
Audit trail of role-permission assignments

Part 4: User Membership & Role Assignment

Table 13: core_junction_company_user_memberships

Purpose: User membership in companies (multi-company support).

Key Features:

Multi-company user support
Membership status tracking
Invite tracking
Join date recording

Important Columns:

Column	Type	Constraints	Description
company_id	UUID	PK, FK	Company identifier
user_id	UUID	PK, FK, indexed	User identifier
status	String(20)	default='active'	active, inactive, invited, suspended
invited_by	UUID	FK	User who sent invite
joined_at	Timestamp	default=now	When user joined

Indexes:

Composite primary key on (company_id, user_id)
B-tree index on user_id

Relationships:

Many-to-one: company → Company
Many-to-one: user → User
Many-to-one: invited_by_user → User
One-to-many: roles → CompanyUserRole

Use Cases:

Multi-company user support (consultants)
Invite tracking
User status management
Company-wide membership queries

Table 14: core_junction_company_user_roles

Purpose: User role assignments within companies.

Key Features:

Site-scoped role assignments
Multiple roles per user
Assignment audit trail
Flexible role management

Important Columns:

Column	Type	Constraints	Description
company_id	UUID	PK, FK	Company identifier
user_id	UUID	PK, FK	User identifier
company_role_id	UUID	PK, FK	Assigned role
site_id	UUID	FK	Optional site scope for this role
assigned_at	Timestamp	default=now	Assignment timestamp
assigned_by	UUID	FK	User who made assignment

Indexes:

B-tree index on user_id
B-tree index on company_id

Relationships:

Many-to-one: company_role → CompanyRole
Many-to-one: site → CompanySite
Many-to-one: user → User
Many-to-one: assigned_by_user → User

Use Cases:

Site-scoped role assignment (user is manager only at Site A)
Multi-role users (user has Admin role and Coordinator role)
Role audit trails
Permission resolution at query time

Part 5: Subscription & Entitlements

Table 15: core_config_plans

Purpose: Plan tiers defining feature sets and pricing.

Key Features:

Pricing tier definitions
Feature bundling
Version support
Upgrade/downgrade logic

Important Columns:

Column	Type	Constraints	Description
plan_id	UUID	PK	Primary identifier
code	String(50)	UNIQUE	Plan code
name	String(100)		Display name
description	Text		Plan description
created_at	Timestamp		Creation time

Seed Data:

Code	Name	Description
STARTER	Starter	Basic features for small teams
STANDARD	Standard	Full features for growing businesses
PRO	Professional	Advanced features with AI capabilities

Relationships:

One-to-many: versions → PlanVersion

Use Cases:

Define pricing tiers
Feature bundling
Versioning for plan changes
Upgrade/downgrade logic

Table 16: core_config_plan_versions

Purpose: Versioned plan configurations with effective dates.

Key Features:

Plan versioning for backward compatibility
Effective date management
Active/inactive status
Historical plan tracking

Important Columns:

Column	Type	Constraints	Description
plan_version_id	UUID	PK	Primary identifier
plan_id	UUID	FK	Plan identifier
version	Integer		Version number
is_active	Boolean	default=true	Active status
effective_from	Date		When version becomes effective
effective_to	Date		When version expires
created_at	Timestamp		Creation time

Indexes:

Unique constraint on (plan_id, version)

Relationships:

Many-to-one: plan → Plan
One-to-many: subscriptions → CompanySubscription
One-to-many: entitlements → PlanEntitlement
One-to-many: default_limits → PlanDefaultLimit

Use Cases:

Plan versioning for backward compatibility
Effective date management
Support for plan changes without migration
Historical plan tracking

Table 17: core_data_company_subscriptions

Purpose: Company subscription to specific plan versions.

Key Features:

Subscription tracking
Trial period management
Stripe integration
Status management

Important Columns:

Column	Type	Constraints	Description
subscription_id	UUID	PK	Primary identifier
company_id	UUID	FK, indexed	Company identifier
plan_version_id	UUID	FK	Subscribed plan version
started_at	DateTime		Subscription start date
ends_at	DateTime		Subscription end date (NULL = ongoing)
trial_ends_at	DateTime		Trial period end
status	String(20)	default='active'	active, trial, past_due, canceled
stripe_subscription_id	String(100)		Stripe payment subscription ID
created_at	Timestamp		Creation time

Indexes:

B-tree index on company_id
Unique constraint on (company_id, status) where status='active'

Relationships:

Many-to-one: company → Company
Many-to-one: plan_version → PlanVersion

Use Cases:

Track active subscriptions
Trial period management
Subscription status
Payment integration (Stripe)

Table 18: core_config_plan_default_limits

Purpose: Default usage limits per plan tier.

Key Features:

Plan quota definitions
Multiple limit types
Unit of measure support
Tier-based restrictions

Important Columns:

Column	Type	Constraints	Description
plan_limit_id	UUID	PK	Primary identifier
plan_version_id	UUID	FK	Plan version
limit_code	String(50)		Limit identifier
limit_value	BigInteger		Limit value (NULL = unlimited)
unit	String(20)		Unit of measure
description	Text		Limit description
created_at	Timestamp		Creation time

Limit Code Examples:

Code	Unit	Description
MAX_SITES	count	Maximum number of sites
MAX_USERS	count	Maximum number of users
MAX_SDS_UPLOADS	per_month	SDS uploads per month
MAX_STORAGE	GB	Storage quota

Indexes:

B-tree index on plan_version_id
Unique constraint on (plan_version_id, limit_code)

Relationships:

Many-to-one: plan_version → PlanVersion

Use Cases:

Define plan quotas
Rate limiting
Resource allocation
Upsell/downgrade logic

Table 19: core_config_entitlement_definitions

Purpose: Master catalog of all controllable features and limits.

Key Features:

Feature flag definitions
Limit/quota definitions
Type differentiation (FEATURE vs LIMIT)
Centralized entitlement catalog

Important Columns:

Column	Type	Constraints	Description
entitlement_id	UUID	PK	Primary identifier
code	String(100)	UNIQUE	Entitlement code
name	String(255)		Display name
type	String(20)		FEATURE (boolean) or LIMIT (numeric)
unit	String(20)		Unit of measure (count, per_month, GB)
description	Text		What this entitlement controls
created_at	Timestamp		Creation time

Entitlement Examples:

Code	Type	Description
sds_ai_parsing	FEATURE	AI-powered SDS parsing
sds_web_search	FEATURE	Web search for SDS documents
max_sds_documents	LIMIT	Maximum SDS documents
max_inventory_items	LIMIT	Maximum inventory items

Indexes:

B-tree index on type

Relationships:

One-to-many: plan_entitlements → PlanEntitlement
One-to-many: company_overrides → CompanyEntitlementOverride

Use Cases:

Define all controllable features
Feature flags management
Quota/limit definitions
Entitlement catalog

Table 20: core_config_plan_entitlements

Purpose: Default entitlements included in each plan tier.

Key Features:

Plan feature sets
Tier-specific capabilities
Selectable entitlements for onboarding
Module association

Important Columns:

Column	Type	Constraints	Description
plan_entitlement_id	UUID	PK	Primary identifier
plan_version_id	UUID	FK	Plan version
entitlement_id	UUID	FK	Entitlement definition
feature_enabled	Boolean		For type='FEATURE', is it enabled?
limit_value	BigInteger		For type='LIMIT', what is the quota?
is_selectable	Boolean	default=true	Can user toggle in onboarding?
module_id	UUID	FK	Associated module
feature_level	Text	nullable	Feature level descriptor (e.g. tier detail)
created_at	Timestamp		Creation time

Indexes:

B-tree index on plan_version_id
Unique constraint on (plan_version_id, entitlement_id)

Relationships:

Many-to-one: plan_version → PlanVersion
Many-to-one: entitlement → EntitlementDefinition
Many-to-one: module → Module

Use Cases:

Define plan feature sets
Tier-specific feature control
Onboarding module selection
Feature visibility

Table 21: core_data_company_entitlement_overrides

Purpose: Per-company exceptions to plan entitlements (custom contracts, upgrades).

Key Features:

Custom enterprise contracts
Sales concessions
Feature upgrades
Override tracking with reasons

Important Columns:

Column	Type	Constraints	Description
override_id	UUID	PK	Primary identifier
company_id	UUID	FK	Company getting override
entitlement_id	UUID	FK	Entitlement being overridden
feature_enabled	Boolean		Override for FEATURE type
limit_value	BigInteger		Override for LIMIT type
reason	Text		Why override exists
created_at	Timestamp		Creation time

Indexes:

B-tree index on company_id
Unique constraint on (company_id, entitlement_id)

Relationships:

Many-to-one: company → Company
Many-to-one: entitlement → EntitlementDefinition

Use Cases:

Custom enterprise contracts
Sales concessions
Feature upgrades
Upsell tracking

Part 6: Modules, Features & Capabilities

Table 22: core_config_modules

Purpose: Core platform modules (ADMINHQ, CHEMIQ, SAFEPATH, etc.).

Key Features:

Module definitions
Category classification (REQUIRED, USER_OPTION, UNDERLYING)
Parent-child relationships
Visibility control

Important Columns:

Column	Type	Constraints	Description
module_id	UUID	PK	Primary identifier
code	String(50)	UNIQUE	Module code
name	String(100)		Display name
description	Text		Module description
category	String(20)		REQUIRED, USER_OPTION, UNDERLYING
parent_module_id	UUID	FK	Parent module (for nesting)
is_visible	Boolean	default=true	Whether to show in UI
created_at	Timestamp		Creation time

Category Definitions:

Category	Description
REQUIRED	Always enabled, cannot be disabled
USER_OPTION	Selectable by user during onboarding
UNDERLYING	System modules, hidden from UI

Module Examples:

Code	Name	Category
ADMINHQ	Admin HQ	REQUIRED
CHEMIQ	ChemIQ	USER_OPTION
SAFEPATH	SafePath	USER_OPTION
INCIDENTIQ	IncidentIQ	USER_OPTION
INSIGHTS	Insights	UNDERLYING

Indexes:

B-tree index on category
B-tree index on parent_module_id
CHECK constraint: UNDERLYING modules must have is_visible=false

Relationships:

One-to-many: screens → ModuleScreen
One-to-many: company_type_rules → CompanyTypeModuleRule
Self-referencing: parent_module
One-to-many: enabled_companies → CompanyEnabledModule
One-to-many: features → ModuleFeature

Use Cases:

Define platform modules
Industry-specific feature availability
Parent-child module relationships
Visibility control

Table 23: core_config_module_features

Purpose: Features within modules.

Key Features:

Feature definitions per module
Display ordering
Active/inactive status
Capability grouping

Important Columns:

Column	Type	Constraints	Description
feature_id	UUID	PK	Primary identifier
module_id	UUID	FK	Parent module
code	String(50)		Feature code
name	String(100)		Display name
description	Text		Feature description
display_order	Integer		Order for UI display
is_active	Boolean	default=true	Active flag
created_at	Timestamp		Creation time

Feature Examples (ChemIQ):

Code	Name	Description
SDS_BINDER	SDS Binder	SDS document management
INVENTORY	Chemical Inventory	Inventory tracking
BARCODE_SCAN	Barcode Scanning	Barcode-based lookup
EPA_LABELS	EPA Labels	FIFRA pesticide labels

Indexes:

B-tree index on module_id
Unique constraint on (module_id, code)

Relationships:

Many-to-one: module → Module
One-to-many: capabilities → FeatureCapability

Use Cases:

Define module features
Feature availability per tier
Capability grouping

Table 24: core_config_feature_capabilities

Purpose: Tier-specific capabilities for each feature.

Key Features:

Tier differentiation (STARTER, STANDARD, PRO)
Capability-level detail
Optional permission binding
Feature comparison across tiers

Important Columns:

Column	Type	Constraints	Description
capability_id	UUID	PK	Primary identifier
feature_id	UUID	FK	Parent feature
capability_code	String(100)		Capability identifier
tier	String(20)		STARTER, STANDARD, or PRO
description	Text		What this capability includes
requires_permission	String(100)		Optional permission code
created_at	Timestamp		Creation time

Capability Examples:

Feature	Tier	Capability	Description
SDS_BINDER	STARTER	manual_upload	Manual SDS upload
SDS_BINDER	STANDARD	web_search	Web search for SDS
SDS_BINDER	PRO	ai_parsing	AI-powered SDS parsing

Indexes:

B-tree index on feature_id
B-tree index on tier
Unique constraint on (feature_id, capability_code, tier)

Relationships:

Many-to-one: feature → ModuleFeature

Use Cases:

Define tier-specific capabilities
Feature comparison across tiers
Permission binding to capabilities

Table 25: core_junction_company_type_module_rules

Purpose: Define module availability per company type/industry.

Key Features:

Industry-specific module availability
Availability levels (REQUIRED, DEFAULT_ON, OPTIONAL, HIDDEN)
Reason tracking
Regulatory compliance

Important Columns:

Column	Type	Constraints	Description
rule_id	UUID	PK	Primary identifier
company_type_id	UUID	FK	Company type
module_id	UUID	FK	Module
availability	String(20)		REQUIRED, DEFAULT_ON, OPTIONAL, HIDDEN
reason	Text		Why module is available/hidden
created_at	Timestamp		Creation time

Availability Definitions:

Availability	Description
REQUIRED	Always enabled, cannot disable
DEFAULT_ON	Enabled by default, can disable
OPTIONAL	Not enabled by default, can enable
HIDDEN	Not available for this industry

Indexes:

B-tree index on company_type_id
B-tree index on module_id
Unique constraint on (company_type_id, module_id)

Relationships:

Many-to-one: company_type → CompanyType
Many-to-one: module → Module

Use Cases:

Industry-specific module availability
Regulatory requirement enforcement
Default module selection for onboarding

Table 26: core_junction_company_enabled_modules

Purpose: Track which modules are enabled for each company.

Key Features:

Module enablement tracking
User vs. system enablement distinction
Enablement timestamp
Audit trail

Important Columns:

Column	Type	Constraints	Description
company_id	UUID	PK, FK	Company identifier
module_id	UUID	PK, FK	Module identifier
enabled_by	UUID	FK	User who enabled (NULL for system)
enabled_at	Timestamp	default=now	When enabled

Indexes:

B-tree index on company_id

Relationships:

Many-to-one: company → Company
Many-to-one: module → Module
Many-to-one: enabled_by_user → User

Use Cases:

Track enabled modules per company
Distinguish user-selected vs. system-attached modules
Audit trail of module activation
Feature availability checking

Part 7: Invitations & Onboarding

Table 27: core_data_invites

Purpose: User invitation tracking.

Key Features:

Unique invite tokens
Expiration handling
Status tracking
Role pre-assignment

Important Columns:

Column	Type	Constraints	Description
invite_id	UUID	PK	Primary identifier
company_id	UUID	FK, indexed	Company sending invite
email	String(255)		Invited email address
company_role_id	UUID	FK	Role to assign upon acceptance
role_template	UUID	FK	System role template for invite
site_id	UUID	FK	Optional site scope
invite_token	String(100)	UNIQUE, indexed	Unique token for invite link
expires_at	DateTime		Expiration timestamp
accepted_at	DateTime		When user accepted
invited_by	UUID	FK	User who sent invite
status	String(20)	default='pending'	pending, accepted, expired, revoked
invite_metadata	JSONB		Additional user data
created_at	Timestamp		Creation time

invite_metadata Structure:

{
  "full_name": "John Doe",
  "department": "Safety",
  "custom_message": "Welcome to the team!"
}

Indexes:

B-tree index on company_id
Unique index on invite_token where status='pending'

Relationships:

Many-to-one: company → Company
Many-to-one: company_role → CompanyRole
Many-to-one: role_template → SystemRole
Many-to-one: site → CompanySite
Many-to-one: invited_by_user → User

Use Cases:

User invitations
Temporary access tokens
Invite status tracking
Role pre-assignment

Table 28: core_data_onboarding_checklist

Purpose: Track onboarding progress for companies.

Key Features:

Step-by-step progress tracking
Status per step (pending, in_progress, done, skipped)
Metadata storage per step
Completion verification

Important Columns:

Column	Type	Constraints	Description
company_id	UUID	PK, FK	Company identifier
step_code	String(50)	PK	Step identifier
status	String(20)	default='pending'	pending, in_progress, done, skipped
meta_json	JSONB		Step-specific metadata
updated_at	Timestamp	onupdate	Last update time

Unified Onboarding Steps:

Step Code	Title	Required	Order
industry	Industry & Company Type	Yes	1
modules	Modules & Plan	Yes	2
details	Company Details	Yes	3
coordinator	Program Coordinator	Yes	4
roles	Roles & Permissions	No	5
sites	Sites	Yes	6
locations	Locations	No	7
team	Team Members	No	8
finalize	Review & Complete	Yes	9

Relationships:

Many-to-one: company → Company

Use Cases:

Track onboarding progress
Multi-step workflow management
Skip logic for optional steps
Onboarding completion verification

Table 29: core_data_audit_logs

Purpose: Audit trail for all entity changes across the platform.

Key Features:

Complete change history for any entity
Tracks old and new values
User and company context
Action type categorization

Important Columns:

Column	Type	Constraints	Description
audit_id	UUID	PK	Primary identifier
company_id	UUID	FK, indexed	Company context
user_id	UUID	FK	User who made the change
target_table	Text		Table that was modified
target_id	UUID		ID of the modified record
action	Text		create, update, delete
changed_fields	JSONB		Fields that were changed
old_values	JSONB		Previous values
new_values	JSONB		New values
occurred_at	Timestamp		When change occurred

Indexes:

B-tree index on company_id
B-tree index on occurred_at

Relationships:

Many-to-one: company → Company
Many-to-one: user → User

Use Cases:

Compliance audit trails
Track all entity changes
Debug data issues
Generate change reports for regulators

Complete Table Summary

Total Core Module Tables: 29

Configuration Tables (System-wide, seeded in migrations): 10

core_config_company_types
core_config_system_roles
core_config_permissions
core_config_modules
core_config_module_features
core_config_feature_capabilities
core_config_plans
core_config_plan_versions
core_config_plan_default_limits
core_config_entitlement_definitions
core_config_plan_entitlements

Data Tables (Tenant-specific, user-generated): 12 12. core_data_users 13. core_data_user_login_audit 14. core_data_user_sessions 15. core_data_companies 16. core_data_sites 17. core_data_site_locations 18. core_data_company_roles 19. core_data_company_subscriptions 20. core_data_company_entitlement_overrides 21. core_data_invites 22. core_data_onboarding_checklist 23. core_data_audit_logs

Junction Tables (Many-to-many relationships): 6 24. core_junction_system_role_permissions 25. core_junction_company_role_permissions 26. core_junction_company_user_memberships 27. core_junction_company_user_roles 28. core_junction_company_enabled_modules 29. core_junction_company_type_module_rules

Key Relationships Summary

Company
├── company_type (CompanyType)
├── subscriptions (CompanySubscription)
│   └── plan_version (PlanVersion)
│       └── plan (Plan)
├── sites (CompanySite)
│   └── locations (SiteLocation)
├── user_memberships (CompanyUserMembership)
│   └── roles (CompanyUserRole)
│       └── company_role (CompanyRole)
├── roles (CompanyRole)
│   ├── base_system_role (SystemRole)
│   └── permissions (CompanyRolePermission)
│       └── permission (Permission)
├── enabled_modules (CompanyEnabledModule)
│   └── module (Module)
├── invites (Invite)
├── onboarding_checklists (OnboardingChecklist)
└── entitlement_overrides (CompanyEntitlementOverride)
    └── entitlement (EntitlementDefinition)

User
├── login_audits (UserLoginAudit)
├── sessions (UserSession)
├── company_memberships (CompanyUserMembership)
│   └── company (Company)
└── company_roles (CompanyUserRole)
    └── company_role (CompanyRole)

Module
├── screens (ModuleScreen)
├── features (ModuleFeature)
│   └── capabilities (FeatureCapability)
├── company_type_rules (CompanyTypeModuleRule)
└── enabled_companies (CompanyEnabledModule)

Plan
├── versions (PlanVersion)
│   ├── subscriptions (CompanySubscription)
│   ├── entitlements (PlanEntitlement)
│   │   └── entitlement (EntitlementDefinition)
│   └── default_limits (PlanDefaultLimit)

SystemRole
├── permissions (SystemRolePermission)
│   └── permission (Permission)
└── company_roles (CompanyRole) [as base_system_role]

SQLAlchemy Models

Location: tellus-ehs-hazcom-service/app/db/models/

User Models (`user.py`)

User - Main user model
UserLoginAudit - Login audit trail
UserSession - Active sessions

Company Models (`company.py`)

CompanyType - Industry classification
Company - Main company model
CompanySite - Company sites
SiteLocation - Site locations

Role Models (`role.py`)

SystemRole - System role templates
Permission - Atomic permissions
SystemRolePermission - System role-permission mapping
CompanyRole - Company-specific roles
CompanyRolePermission - Company role-permission mapping

Membership Models (`membership.py`)

CompanyUserMembership - User-company membership
CompanyUserRole - User role assignments

Plan Models (`plan.py`)

Plan - Plan definitions
PlanVersion - Plan versions
CompanySubscription - Company subscriptions
PlanDefaultLimit - Plan limits

Module Models (`module.py`)

Module - Module definitions
ModuleScreen - Module screens
CompanyTypeModuleRule - Industry module rules
CompanyTypeScreenRule - Industry screen rules
CompanyEnabledModule - Company module enablements
ModuleFeature - Module features
FeatureCapability - Feature capabilities

Entitlement Models (`entitlement.py`)

EntitlementDefinition - Entitlement catalog
PlanEntitlement - Plan entitlements
CompanyEntitlementOverride - Company overrides

Other Models

Invite (invite.py) - User invitations
OnboardingChecklist (onboarding.py) - Onboarding progress

Example Queries

Get user's permissions for a company

SELECT DISTINCT p.code, p.name
FROM core_config_permissions p
JOIN core_junction_company_role_permissions crp ON p.permission_id = crp.permission_id
JOIN core_data_company_roles cr ON crp.company_role_id = cr.company_role_id
JOIN core_junction_company_user_roles cur ON cr.company_role_id = cur.company_role_id
WHERE cur.company_id = :company_id
  AND cur.user_id = :user_id;

Get company's enabled modules with features

SELECT m.code, m.name, mf.code AS feature_code, mf.name AS feature_name
FROM core_config_modules m
JOIN core_junction_company_enabled_modules cem ON m.module_id = cem.module_id
LEFT JOIN core_config_module_features mf ON m.module_id = mf.module_id
WHERE cem.company_id = :company_id
  AND m.is_visible = true
ORDER BY m.code, mf.display_order;

Get company's subscription with plan details

SELECT c.display_name, p.code AS plan_code, pv.version,
       cs.status, cs.trial_ends_at
FROM core_data_companies c
JOIN core_data_company_subscriptions cs ON c.company_id = cs.company_id
JOIN core_config_plan_versions pv ON cs.plan_version_id = pv.plan_version_id
JOIN core_config_plans p ON pv.plan_id = p.plan_id
WHERE c.company_id = :company_id
  AND cs.status IN ('active', 'trial');

Get onboarding progress

SELECT step_code, status, updated_at
FROM core_data_onboarding_checklist
WHERE company_id = :company_id
ORDER BY
  CASE step_code
    WHEN 'industry' THEN 1
    WHEN 'modules' THEN 2
    WHEN 'details' THEN 3
    WHEN 'coordinator' THEN 4
    WHEN 'roles' THEN 5
    WHEN 'sites' THEN 6
    WHEN 'locations' THEN 7
    WHEN 'team' THEN 8
    WHEN 'finalize' THEN 9
  END;

Check if user has specific permission

SELECT EXISTS (
  SELECT 1
  FROM core_junction_company_user_roles cur
  JOIN core_junction_company_role_permissions crp
    ON cur.company_role_id = crp.company_role_id
  JOIN core_config_permissions p
    ON crp.permission_id = p.permission_id
  WHERE cur.company_id = :company_id
    AND cur.user_id = :user_id
    AND p.code = :permission_code
    AND (cur.site_id IS NULL OR cur.site_id = :site_id)
) AS has_permission;

Performance Considerations

Indexes

Compound indexes on (company_id, user_id) for membership queries
Covering indexes on (company_id, status) for filtered queries
Foreign key indexes on all junction tables for join optimization
Unique constraints prevent duplicate assignments

Query Patterns

Fast company membership checks
Efficient role-permission lookups (cached in app layer)
Site-scoped role queries
Module availability per company type

Data Volume Expectations

Table	Expected Volume	Growth Pattern
core_data_users	Hundreds of thousands	Slow, steady
core_data_companies	Thousands	Slow growth
core_junction_company_user_memberships	Millions	Moderate
core_data_user_login_audit	Tens of millions	High, time-partitioned
core_data_user_sessions	Ephemeral	Cleaned after expiration

Caching Strategy

Configuration tables (core_config_*) cached at app startup
Permission lookups cached per user session
Module/feature availability cached per company

Security Considerations

Multi-Tenancy Isolation

All queries must include company_id filter
No cross-company data leakage
Site-scoped permissions for granular access

Authentication

Supabase integration for auth
JWT token validation
Session management with expiration

Audit Trails

Login audit for all authentication events
Role assignment tracking
Onboarding step changes

Data Protection

No PII in logs beyond user_id
Invite tokens expire after 7 days
Sessions expire after 12 hours (configurable)

Integration Points

Supabase

User authentication via supabase_uid
Token validation
Password reset flows

Stripe

Subscription management via stripe_subscription_id
Payment processing
Plan upgrades/downgrades

ChemIQ Module

Company isolation via company_id
Site/location scoping
Permission checks for SDS/inventory access

Background Services

Invite expiration cleanup
Session cleanup
Subscription renewal checks

Migration Files

Key Alembic migrations for AdminHQ Core:

Initial Schema - Creates all core tables
Add Onboarding Checklist - core_data_onboarding_checklist
Add Module Features - core_config_module_features, core_config_feature_capabilities
Add Entitlements - Entitlement system tables
Add Company Roles - Company-specific role system

See tellus-ehs-hazcom-service/alembic/versions/ for complete migration history.

Summary

The AdminHQ Core schema provides the foundational infrastructure for the Tellus EHS platform:

Multi-tenant isolation - Companies completely isolated via company_id
Flexible RBAC - Customizable roles with granular permissions
Feature management - Per-tier capability control via entitlements
Guided onboarding - Step-by-step company setup with progress tracking
Multi-company users - Consultants can access multiple companies
Industry compliance - Industry-specific module rules and requirements
Audit trails - Complete authentication and action logging
Subscription management - Plan versioning with Stripe integration

This schema integrates seamlessly with the ChemIQ module and other future modules, providing a robust foundation for scalable multi-tenant SaaS operations.

Overview​

Table Naming Convention​

Key Design Principles​

Architecture Overview​

Multi-Tenant Architecture​

RBAC Architecture​

Part 1: User & Authentication System​

Table 1: core_data_users​

Table 2: core_data_user_login_audit​

Table 3: core_data_user_sessions​

Part 2: Company & Organizational Structure​

Table 4: core_config_company_types​

Table 5: core_data_companies​

Table 6: core_data_sites​

Table 7: core_data_site_locations​

Part 3: Role-Based Access Control (RBAC)​

Table 8: core_config_system_roles​

Table 9: core_config_permissions​

Table 10: core_junction_system_role_permissions​

Table 11: core_data_company_roles​

Table 12: core_junction_company_role_permissions​

Part 4: User Membership & Role Assignment​

Table 13: core_junction_company_user_memberships​

Table 14: core_junction_company_user_roles​

Part 5: Subscription & Entitlements​

Table 15: core_config_plans​

Table 16: core_config_plan_versions​

Table 17: core_data_company_subscriptions​

Table 18: core_config_plan_default_limits​

Table 19: core_config_entitlement_definitions​

Table 20: core_config_plan_entitlements​

Table 21: core_data_company_entitlement_overrides​

Part 6: Modules, Features & Capabilities​

Table 22: core_config_modules​

Table 23: core_config_module_features​

Table 24: core_config_feature_capabilities​

Table 25: core_junction_company_type_module_rules​

Table 26: core_junction_company_enabled_modules​

Part 7: Invitations & Onboarding​

Table 27: core_data_invites​

Table 28: core_data_onboarding_checklist​

Table 29: core_data_audit_logs​

Complete Table Summary​

Total Core Module Tables: 29​

Key Relationships Summary​

SQLAlchemy Models​

User Models (user.py)​

Company Models (company.py)​

Role Models (role.py)​

Membership Models (membership.py)​

Plan Models (plan.py)​

Module Models (module.py)​

Entitlement Models (entitlement.py)​

Other Models​

Example Queries​

Get user's permissions for a company​

Get company's enabled modules with features​

Get company's subscription with plan details​

Get onboarding progress​

Check if user has specific permission​

Performance Considerations​

Indexes​

Query Patterns​

Data Volume Expectations​

Caching Strategy​

Security Considerations​

Multi-Tenancy Isolation​

Authentication​

Audit Trails​

Data Protection​

Integration Points​

Supabase​

Stripe​

ChemIQ Module​

Background Services​

Migration Files​

Summary​

Overview

Table Naming Convention

Key Design Principles

Architecture Overview

Multi-Tenant Architecture

RBAC Architecture

Part 1: User & Authentication System

Table 1: core_data_users

Table 2: core_data_user_login_audit

Table 3: core_data_user_sessions

Part 2: Company & Organizational Structure

Table 4: core_config_company_types

Table 5: core_data_companies

Table 6: core_data_sites

Table 7: core_data_site_locations

Part 3: Role-Based Access Control (RBAC)

Table 8: core_config_system_roles

Table 9: core_config_permissions

Table 10: core_junction_system_role_permissions

Table 11: core_data_company_roles

Table 12: core_junction_company_role_permissions

Part 4: User Membership & Role Assignment

Table 13: core_junction_company_user_memberships

Table 14: core_junction_company_user_roles

Part 5: Subscription & Entitlements

Table 15: core_config_plans

Table 16: core_config_plan_versions

Table 17: core_data_company_subscriptions

Table 18: core_config_plan_default_limits

Table 19: core_config_entitlement_definitions

Table 20: core_config_plan_entitlements

Table 21: core_data_company_entitlement_overrides

Part 6: Modules, Features & Capabilities

Table 22: core_config_modules

Table 23: core_config_module_features

Table 24: core_config_feature_capabilities

Table 25: core_junction_company_type_module_rules

Table 26: core_junction_company_enabled_modules

Part 7: Invitations & Onboarding

Table 27: core_data_invites

Table 28: core_data_onboarding_checklist

Table 29: core_data_audit_logs

Complete Table Summary

Total Core Module Tables: 29

Key Relationships Summary

SQLAlchemy Models

User Models (`user.py`)

Company Models (`company.py`)

Role Models (`role.py`)

Membership Models (`membership.py`)

Plan Models (`plan.py`)

Module Models (`module.py`)

Entitlement Models (`entitlement.py`)

Other Models

Example Queries

Get user's permissions for a company

Get company's enabled modules with features

Get company's subscription with plan details

Get onboarding progress

Check if user has specific permission

Performance Considerations

Indexes

Query Patterns

Data Volume Expectations

Caching Strategy

Security Considerations

Multi-Tenancy Isolation

Authentication

Audit Trails

Data Protection

Integration Points

Supabase

Stripe

ChemIQ Module

Background Services

Migration Files

Summary