Data Model & Tenancy with HubSpot
Why This Matters
HubSpot provides strong CRM, ServiceHub, and content tools, but it is not a general-purpose database. When building multi-tenant systems around HubSpot, the challenge is deciding what lives in HubSpot versus what needs external persistence. This note outlines patterns for tenancy-aware data modeling.
HubSpot as System of Record
- Contacts → represent people (Innovators, Site Managers, Staff).
- Companies → represent organizations (Sites, Cohorts, Partners).
- Deals / Tickets → can be reused as program records, assignments, or service requests.
- Custom Objects → extend CRM schema (e.g., Roadmap Item, Course Module).
- HubDB → lightweight tables for reference data (taxonomies, static resources).
- Workflows → automate email, routing, and state transitions.
⚠️ Limitations:
- No per-row ACLs beyond list-based membership.
- No real-time collaboration or versioning.
- Weak per-user state (no pins, recent, read/unread tracking).
- Query limits (API pagination, rate limits).
External Persistence (Next.js + Postgres)
For stateful features that HubSpot cannot support natively:
- Roadmaps → goals/KPIs, version control, comments.
- Shared Calendar UI → week/month view, color-coded, Teams sync.
- Notifications → in-app feed, read/unread, per-user targeting.
- Assignments → submissions, statuses, feedback history.
- Per-user State → pins, recent activity, personalized dashboards.
Core Tables (starter schema)
tenants(id, name, …)
users(id, email, hubspot_contact_id, …)
memberships(user_id, tenant_id, role)
events(id, tenant_id, source, starts_at, …)
roadmap_items(id, tenant_id, title, status, …)
roadmap_versions(id, roadmap_item_id, version, snapshot_json, …)
assignments(id, tenant_id, title, due_at, …)
submissions(id, assignment_id, user_id, status, feedback, …)
notifications(id, tenant_id, user_id, kind, payload_json, read_at)
Tenancy Patterns
1. Tenant Identifier Everywhere
- Every record tied to a
tenant_id
. - Enforce scoping at DB, API, and service layers.
- Test row-level security with unit tests.
2. HubSpot ↔ External Sync
- HubSpot remains the CRM record of truth.
- External DB holds stateful or collaborative features.
- Use webhooks + nightly jobs for sync.
3. Auth & Access
- Option A: HubSpot membership gating (Private Content → contact lists).
- Option B: SSO (Azure AD/Entra ID) with user/tenant mapping.
- Roles (
innovator
,site_manager
,staff
,admin
) resolved in API, never on client.
4. Separation of Concerns
- HubSpot = static content, CRM, tickets, workflows.
- External = stateful features, personalization, analytics.
Security & Compliance
- Data residency: keep external DB in DIANA’s approved region.
- Access control: enforce tenant/role server-side.
- Audit logging: log every mutation with tenant_id + diff.
- Encryption: TLS in transit, AES-256 at rest.
- Backups: PITR + restore drills.
Expansion Path
- Add new custom objects in HubSpot for structured but low-frequency data.
- Extend Postgres schema for real-time or high-volume state.
- Move toward a hybrid model where HubSpot is always CRM/system-of-record, while Next.js + Postgres powers UX and state.
Open Questions
- Where should tenant boundaries live in HubSpot (separate portals vs lists vs partitions)?
- When does it make sense to duplicate records (HubSpot + Postgres) vs proxy them?
- What are DIANA’s long-term requirements for SSO and cross-tenant analytics?
erDiagram
TENANTS {
uuid id PK
string name
}
USERS {
uuid id PK
string email
string hubspot_contact_id
string name
}
MEMBERSHIPS {
uuid id PK
uuid user_id FK
uuid tenant_id FK
string role
}
EVENTS {
uuid id PK
uuid tenant_id FK
string source
datetime starts_at
datetime ends_at
}
ROADMAP_ITEMS {
uuid id PK
uuid tenant_id FK
string title
string status
}
ROADMAP_VERSIONS {
uuid id PK
uuid roadmap_item_id FK
int version
json snapshot
}
ASSIGNMENTS {
uuid id PK
uuid tenant_id FK
string title
datetime due_at
}
SUBMISSIONS {
uuid id PK
uuid assignment_id FK
uuid user_id FK
string status
}
NOTIFICATIONS {
uuid id PK
uuid tenant_id FK
uuid user_id FK
string kind
}
PINS {
uuid id PK
uuid user_id FK
string entity_type
uuid entity_id
}
AUDIT_LOGS {
uuid id PK
uuid actor_id FK
uuid tenant_id FK
string action
}
TENANTS ||--o{ MEMBERSHIPS : has
USERS ||--o{ MEMBERSHIPS : assigned_to
TENANTS ||--o{ EVENTS : owns
TENANTS ||--o{ ROADMAP_ITEMS : owns
ROADMAP_ITEMS ||--o{ ROADMAP_VERSIONS : versioned
TENANTS ||--o{ ASSIGNMENTS : owns
ASSIGNMENTS ||--o{ SUBMISSIONS : has
USERS ||--o{ SUBMISSIONS : makes
USERS ||--o{ NOTIFICATIONS : receives
USERS ||--o{ PINS : creates
USERS ||--o{ AUDIT_LOGS : actor
TENANTS ||--o{ AUDIT_LOGS : scope