Data Model
Core Principle
Courses are platform assets owned by GritCert. Tenants receive entitlements via
the organization_courses join table. A student enrolled at two different tenants
in the same course produces two distinct enrollment rows, two distinct certificates,
and two distinct compliance records.
Tables
courses
Platform-owned course catalog. GritCert is the author-of-record for all content.
| Column | Type | Notes |
|---|---|---|
| id | int PK | |
| slug | text | e.g. “cdl-basic”, “dot-hazmat” |
| title | text | |
| vertical | enum | CDL, HAZMAT, EPA_608, OSHA_10, OSHA_30, FORKLIFT… |
| regulatory_module | text | 49_CFR_172_704, 40_CFR_82… |
| base_price_cents | int |
course_versions
Published versions of each course. Enrollments pin to a specific version row.
| Column | Type | Notes |
|---|---|---|
| id | uuid PK | |
| course_id | FK → courses | |
| version_semver | text | e.g. “1.0.0” |
| status | enum | draft, published, archived |
| published_at | timestamptz | |
| content_payload | jsonb | versioned body |
organizations
Every tenant. Each row is one tenant.
| Column | Type | Notes |
|---|---|---|
| id | int PK | |
| slug | text | e.g. “freightpath” |
| name | text | display name |
| primary_hostname | text | {slug}.gritcert.com |
| custom_domain | text | nullable, unique |
| billing_contact_email | text | nullable |
| is_active | bool |
organization_courses (entitlement join)
Grants a tenant the right to offer a course. Holds per-tenant pricing overrides.
| Column | Type | Notes |
|---|---|---|
| id | uuid PK | |
| organization_id | FK → organizations | |
| course_id | FK → courses | |
| retail_price_cents | int | nullable — tenant sets retail |
| wholesale_price_cents | int | GritCert charges tenant |
| visibility | enum | published, draft |
| expires_at | timestamptz | nullable |
| UNIQUE | (organization_id, course_id) |
organization_memberships
User × tenant join. Authoritative membership record.
| Column | Type | Notes |
|---|---|---|
| id | uuid PK | |
| user_id | FK → users | |
| organization_id | FK → organizations | |
| role | enum | student, admin, owner, support |
| is_primary | bool | one per user |
| UNIQUE | (user_id, organization_id) |
course_enrollments
A student in a course at a tenant. Version-pinned.
| Column | Type | Notes |
|---|---|---|
| id | int PK | |
| user_id | FK → users | |
| organization_id | FK → organizations | |
| course_id | FK → courses | |
| course_version_id | FK → course_versions | pinned at enrollment |
| status | enum | active, completed… |
| UNIQUE | (user_id, course_id, organization_id) |
certificates
Issued per enrollment, branded per tenant.
| Column | Type | Notes |
|---|---|---|
| id | int PK | |
| user_id | FK → users | |
| course_id | FK → courses | |
| organization_id | FK → organizations | NOT NULL |
| course_version_id | FK → course_versions | NOT NULL |
| status | enum | active, expired, revoked |
| format | enum | ELDT_TPR, HM_126F, EPA_608 |
| UNIQUE | (user_id, course_id, organization_id, course_version_id) |
platform_audit_log
Immutable log of every platform admin action. No delete policy.
| Column | Type | Notes |
|---|---|---|
| id | uuid PK | |
| organization_id | FK → organizations | nullable |
| actor_user_id | FK → users | platform admin who acted |
| action | text | ”admin_added”, “entitlement_granted”… |
| entity_type | text | ”organization”, “user”… |
| metadata | jsonb | before/after values |
| created_at | timestamptz | immutable |
Invariants
- No tenant-specific course content in V1. Tenant overrides are pricing, visibility, and certificate header only.
- All tenant-scoped queries enforce
WHERE organization_id = req.user.organizationId. - Enrollments are version-pinned. New course versions never mutate in-flight enrollments.
- BYPASS_AUTH stays false in every environment.