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