HubSpot High-level Implementation Plan
Following is the outline of what we need to deploy VesselVanguard → HubSpot usage analytics, written for the Next.js backend Dan does this map to AWS Amplify?
Database Migration
Tracks login events + future usage events:
migrations/2025-analytics-events.sql
CREATE TABLE IF NOT EXISTS analytics_events (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id VARCHAR(255) NOT NULL,
company_id VARCHAR(255) NOT NULL,
event VARCHAR(255) NOT NULL,
metadata JSONB,
timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_analytics_events_company_time
ON analytics_events (company_id, timestamp);
CREATE INDEX IF NOT EXISTS idx_analytics_events_user_time
ON analytics_events (user_id, timestamp);
Event Logger Utility
Drop-in utility for the backend:
/lib/analytics/eventLogger.js
import db from ‘../db’
export const EventLogger = {
async track({ userId, companyId, event, metadata = {} }) {
if (!userId || !companyId || !event) {
console.warn(“EventLogger missing fields”, { userId, companyId, event }) return
}
await db.query(
`INSERT INTO analytics_events (user_id, company_id, event, metadata)
VALUES ($1, $2, $3, $4)`,
[userId, companyId, event, metadata]
)
}
}
Instrument Login Flow
In the login API route (Dan is this Next.js or AWS component?): /pages/api/login.js (example only)
import { EventLogger } from “@/lib/analytics/eventLogger”;
export default async function handler(req, res) {
const user = await authenticate(req);
await EventLogger.track({
userId: user.id,
companyId: user.companyId,
event: “user_login”
});
res.status(200).json({ success: true, user });
}
Analytics Queries
Shared DB functions to compute metrics:
/lib/analytics/queries.js
import db from “../db”;
export async function getLogins30Days(companyId) {
const { rows } = await db.query(
`SELECT COUNT(*) AS count
FROM analytics_events
WHERE company_id = $1
AND event = ‘user_login’
AND timestamp > NOW() – INTERVAL ’30 days’`
[companyId]
);
return Number(rows[0].count);
}
export async function getLastLogin(companyId) {
const { rows } = await db.query(
`SELECT MAX(timestamp) AS ts
FROM analytics_events
WHERE company_id = $1 AND event = ‘user_login’`,
[companyId]
);
return rows[0].ts;
}
export async function getMAU(companyId) {
const { rows } = await db.query(
`SELECT COUNT(DISTINCT user_id) AS count
FROM analytics_events
WHERE company_id = $1
AND event = ‘user_login’
AND timestamp > NOW() – INTERVAL ’30 days’`,
[companyId]
);
return Number(rows[0].count);
}
export async function getNumberOfVessels(companyId) {
const { rows } = await db.query(
`SELECT COUNT(*) AS count
FROM vessels
WHERE company_id = $1 AND archived = false`,
[companyId]
);
return Number(rows[0].count);
}
Health Score Calculator
/lib/analytics/healthScore.js
export function computeHealthScore({ mau, logins30, vesselCount, lastLogin }) {
const now = Date.now();
const recencyDays = lastLogin ? (now – new Date(lastLogin).getTime()) / 86400000 : 999;
// Normalize metrics
const normMAU = Math.min(mau / 10, 1);
const normLogins = Math.min(logins30 / 30, 1);
const normVessels = Math.min(vesselCount / 10, 1);
const recencyScore = recencyDays < 3 ? 1
: recencyDays < 7 ? 0.7
: recencyDays < 14 ? 0.4
: recencyDays < 30 ? 0.2
: 0;
return Math.round( (
normMAU * 0.4 +
normLogins * 0.3 +
normVessels * 0.2 + recencyScore * 0.1) * 100
);
}
HubSpot Sync Service
/lib/hubspot/sync.js
import axios from “axios”;
const HUBSPOT_API = “https://api.hubapi.com”;
const HS_TOKEN = process.env.HUBSPOT_PRIVATE_APP_TOKEN;
export async function updateHubSpotCompany(companyId, payload) {
try {
const url = `${HUBSPOT_API}/crm/v3/objects/companies/${companyId}`;
await axios.patch(url,
{ properties: payload },
{ headers: { Authorization: `Bearer ${HS_TOKEN}` }}
);
return true;
} catch (err) {
console.error(“HubSpot sync error:”, err?.response?.data || err);
return false;
}
}
Nightly Cron Job
Runs once per day → computes metrics → syncs to HubSpot.
/cron/dailyUsageSync.js
import {
getLogins30Days,
getLastLogin,
getMAU,
getNumberOfVessels
} from “@/lib/analytics/queries”;
import { computeHealthScore } from “@/lib/analytics/healthScore”;
import { updateHubSpotCompany } from “@/lib/hubspot/sync”;
import db from “@/lib/db”;
async function main() {
const { rows: companies } = await db.query(`SELECT id, hubspot_company_id
FROM companies`);
for (const company of companies) {
const companyId = company.id;
const hubspotId = company.hubspot_company_id;
if (!hubspotId) continue; // Skip unmapped companies
const logins30 = await getLogins30Days(companyId);
const lastLogin = await getLastLogin(companyId);
const mau = await getMAU(companyId);
const vessels = await getNumberOfVessels(companyId);
const health = computeHealthScore({
mau,
logins30,
vesselCount: vessels,
lastLogin
});
await updateHubSpotCompany(hubspotId, {
vv_logins_30_days: logins30,
vv_last_login: lastLogin,
vv_monthly_active_users: mau,
vv_number_vessels: vessels,
vv_usage_health_score: health
});
}
}
main();
Cron Scheduling
Dan I doubt AWS works like this
In production:
Linux cron:
0 2 * * * /usr/bin/node /app/cron/dailyUsageSync.js
Docker Compose: Use cron container or run via GitHub Actions nightly.
Implementation Notes
This code provides a complete starting point for the HubSpot integration. Adjust paths, database connections, and scheduling mechanisms based on your actual AWS Amplify or Next.js deployment architecture.
Folder Structure
Tracks login events + future usage events.