Skip to main content

Data Model Overview

Tellus uses PostgreSQL with a multi-tenant architecture.

Schema Organization​

The database is organized into logical domains:

DomainPrefixDescription
Core(none)Companies, users, roles
ChemIQchemiq_Chemical inventory, SDS
AdminHQ(none)Subscriptions, modules, settings
SafePathtraining_Training and certifications

Core Entities​

Companies & Users​

┌─────────────────┐      ┌─────────────────┐
│   companies     │      │     users       │
├─────────────────┤      ├─────────────────┤
│ company_id (PK) │◀────▶│ user_id (PK)    │
│ name            │      │ company_id (FK) │
│ company_type_id │      │ email           │
│ subscription_id │      │ full_name       │
└─────────────────┘      │ role_id (FK)    │
                         └─────────────────┘

Sites & Locations​

┌─────────────────┐      ┌─────────────────┐
│     sites       │      │   locations     │
├─────────────────┤      ├─────────────────┤
│ site_id (PK)    │◀────▶│ location_id (PK)│
│ company_id (FK) │      │ site_id (FK)    │
│ name            │      │ name            │
│ address         │      │ building        │
└─────────────────┘      └─────────────────┘

ChemIQ Domain​

Chemical Inventory Flow​

┌─────────────────────┐
│ chemiq_product_     │     Global product catalog
│ catalog             │     (shared across companies)
└──────────┬──────────┘
           │
           ▼
┌─────────────────────┐
│ chemiq_company_     │     Company-specific products
│ product_catalog     │     (per company)
└──────────┬──────────┘
           │
           ▼
┌─────────────────────┐
│ chemiq_inventory    │     Inventory at locations
│                     │     (quantity tracking)
└─────────────────────┘

SDS Document Structure​

┌─────────────────────┐
│ chemiq_sds_documents│     SDS PDF storage
├─────────────────────┤
│ sds_id              │
│ product_name        │
│ s3_bucket, s3_key   │
│ parsed_json         │
└──────────┬──────────┘
           │
     ┌─────┴─────┬─────────────┐
     ▼           ▼             ▼
┌─────────┐ ┌─────────┐ ┌───────────┐
│ sections│ │ hazard  │ │composition│
│ (1-16)  │ │ _info   │ │           │
└─────────┘ └─────────┘ └───────────┘

Key Relationships​

Company → All Data​

Every transactional table has company_id:

  • Ensures tenant isolation
  • Enables row-level security
  • Simplifies multi-tenant queries

Product → SDS → Inventory​

chemiq_company_product_catalog
  └── current_sds_id → chemiq_sds_documents
  └── company_product_id → chemiq_inventory

User → Roles → Permissions​

users
  └── role assignments (user_roles)
      └── role → permissions

Common Query Patterns​

Get All Inventory for Company​

SELECT i.*, p.product_name, s.site_name, l.location_name
FROM chemiq_inventory i
JOIN chemiq_company_product_catalog p USING (company_product_id)
JOIN sites s USING (site_id)
JOIN locations l USING (location_id)
WHERE i.company_id = :company_id
  AND i.is_active = true;

Get SDS with Hazard Info​

SELECT d.*, h.signal_word, h.pictograms
FROM chemiq_sds_documents d
LEFT JOIN chemiq_sds_hazard_info h USING (sds_id)
WHERE d.sds_id = :sds_id;

Schema Documentation​