The pre-call brief: what to know before every customer meeting, and how to send it automatically

-- Everything a pre-call brief needs about one customer, from every linked system.
-- Only confirmed links count. A source that isn't linked comes back as NULL: unknown, not fine.
CREATE FUNCTION pre_call_brief(p_customer text, p_as_of date)
RETURNS TABLE (
  customer text, missing_sources text[],
  billed_mrr numeric, contract_mrr numeric,
  overdue_count int, overdue_eur numeric, overdue_days int, overdue_number text,
  open_tickets int, urgent_tickets int, urgent_subject text, urgent_waiting_days int,
  top_tag text, top_tag_count int,
  avg_daily_active_users int, usage_change_pct int,
  renewal_date date, days_to_renewal int, notice_deadline date, days_to_notice int,
  last_meeting_days int, last_meeting_by text,
  latest_note_days int, latest_note_by text, latest_note text
)
LANGUAGE sql STABLE AS $$
WITH ids AS (
  SELECT source, source_id FROM customer_source_ids
  WHERE customer_id = p_customer AND status = 'confirmed'
),
billing AS (
  SELECT sum(mrr_eur) AS mrr FROM stripe_subscriptions
  WHERE customer IN (SELECT source_id FROM ids WHERE source = 'stripe') AND status IN ('active', 'past_due')
),
overdue AS (
  SELECT count(*)::int AS n, sum(amount_due_eur) AS eur,
         max(p_as_of - due_date)::int AS days,
         (array_agg(number ORDER BY due_date))[1] AS first_number
  FROM stripe_invoices
  WHERE customer IN (SELECT source_id FROM ids WHERE source = 'stripe')
    AND status = 'open' AND due_date < p_as_of
),
tickets AS (
  SELECT * FROM zendesk_tickets
  WHERE organization_id IN (SELECT source_id FROM ids WHERE source = 'zendesk')
    AND status IN ('new', 'open', 'pending')
),
urgent AS (
  SELECT subject, (p_as_of - coalesce(last_agent_reply_at, created_at)::date)::int AS waiting
  FROM tickets WHERE priority = 'urgent'
  ORDER BY coalesce(last_agent_reply_at, created_at) LIMIT 1
),
tag AS (
  SELECT t AS tag, count(*)::int AS n FROM tickets, unnest(tags) t
  GROUP BY t ORDER BY count(*) DESC, t LIMIT 1
),
usage AS (
  SELECT avg(active_users) FILTER (WHERE day >= p_as_of - 30)                       AS last30,
         avg(active_users) FILTER (WHERE day <  p_as_of - 30 AND day >= p_as_of - 60) AS prev30
  FROM product_usage_daily
  WHERE account_id IN (SELECT source_id FROM ids WHERE source = 'product')
    AND day < p_as_of AND day >= p_as_of - 60
),
company AS (
  SELECT * FROM hubspot_companies WHERE id IN (SELECT source_id FROM ids WHERE source = 'hubspot')
),
notes AS (
  SELECT * FROM hubspot_activities WHERE company_id IN (SELECT source_id FROM ids WHERE source = 'hubspot')
),
last_meeting AS (
  SELECT occurred_at, logged_by FROM notes WHERE type = 'meeting' ORDER BY occurred_at DESC LIMIT 1
),
latest_note AS (
  SELECT occurred_at, logged_by, summary FROM notes ORDER BY occurred_at DESC LIMIT 1
)
SELECT
  c.name,
  ARRAY(SELECT s FROM unnest(ARRAY['hubspot', 'stripe', 'zendesk', 'product']) s
        WHERE s NOT IN (SELECT source FROM ids)),
  (SELECT mrr FROM billing),
  (SELECT contract_mrr_eur FROM company),
  (SELECT n FROM overdue), (SELECT eur FROM overdue), (SELECT days FROM overdue), (SELECT first_number FROM overdue),
  CASE WHEN EXISTS (SELECT 1 FROM ids WHERE source = 'zendesk') THEN (SELECT count(*)::int FROM tickets) END,
  CASE WHEN EXISTS (SELECT 1 FROM ids WHERE source = 'zendesk')
       THEN (SELECT count(*)::int FROM tickets WHERE priority = 'urgent') END,
  (SELECT subject FROM urgent), (SELECT waiting FROM urgent),
  (SELECT tag FROM tag), (SELECT n FROM tag),
  (SELECT round(last30)::int FROM usage),
  (SELECT round(100 * (last30 - prev30) / nullif(prev30, 0))::int FROM usage),
  (SELECT renewal_date FROM company),
  (SELECT renewal_date - p_as_of FROM company),
  (SELECT renewal_date - notice_days FROM company),
  (SELECT (renewal_date - notice_days) - p_as_of FROM company),
  (SELECT (p_as_of - occurred_at::date)::int FROM last_meeting), (SELECT logged_by FROM last_meeting),
  (SELECT (p_as_of - occurred_at::date)::int FROM latest_note), (SELECT logged_by FROM latest_note),
  (SELECT summary FROM latest_note)
FROM customers c
WHERE c.customer_id = p_customer;
$$;

// Turns the facts into five short lines. Rules, not AI: every line is a fact with a source.
const eur = n => '€' + Math.round(Number(n)).toLocaleString('en-US');
const days = n => n === 1 ? '1 day' : `${n} days`;
const unknown = src => `_unknown: ${src} not linked to this customer yet_`;
const end = s => /[.!?]["”]?$/.test(s) ? s : s + '.';
const label = { stripe: 'billing', zendesk: 'support', product: 'product usage', hubspot: 'CRM' };
const out = [];

for (const [i, { json: f }] of $input.all().entries()) {
  const missing = new Set(f.missing_sources ?? []);
  const time = new Date(f.start).toLocaleTimeString('en-GB', { hour: '2-digit', minute: '2-digit', timeZone: 'Europe/Amsterdam' });
  const lines = [];
  const flags = [];

  // Guests from more than one customer: say so instead of picking one.
  if (f.matches > 1) {
    const text = `*${f.title}* · tomorrow ${time}\nGuests from ${f.matches} customers are invited, so no brief was written. Check which account this meeting is about.`;
    out.push({ json: { meetingId: f.meeting_id, customer: null, text }, pairedItem: { item: i } });
    continue;
  }

  // 1. Money
  if (missing.has('stripe')) lines.push(`*Money:* ${unknown('billing')}`);
  else {
    let m = `${eur(f.billed_mrr)}/mo billed`;
    if (f.contract_mrr && Number(f.contract_mrr) !== Number(f.billed_mrr)) {
      // Show the difference; don't call it an error. It can be a discount, a seat change or a mistake.
      const gap = Number(f.billed_mrr) - Number(f.contract_mrr);
      m += `, ${eur(f.contract_mrr)}/mo in the contract (${eur(Math.abs(gap))}/mo ${gap < 0 ? 'less' : 'more'}: a discount, a seat change or a billing error)`;
    }
    if (f.overdue_count > 0) {
      m += `. Invoice ${f.overdue_number} (${eur(f.overdue_eur)}) overdue ${days(f.overdue_days)}`;
      flags.push('overdue');
    } else m += '. No overdue invoices';
    lines.push(`*Money:* ${m}. → Stripe, HubSpot`);
  }

  // 2. Support
  if (missing.has('zendesk')) lines.push(`*Support:* ${unknown('support')}`);
  else if (f.open_tickets === 0) lines.push('*Support:* no open tickets. → Zendesk');
  else {
    let s = `${f.open_tickets} open ticket${f.open_tickets === 1 ? '' : 's'}`;
    if (f.urgent_tickets > 0) {
      s += `, ${f.urgent_tickets} urgent: "${f.urgent_subject}", no reply for ${days(f.urgent_waiting_days)}`;
      flags.push('urgent');
    }
    if (f.top_tag_count >= 3) s += `. ${f.top_tag_count} of ${f.open_tickets} are tagged "${f.top_tag}"`;
    lines.push(`*Support:* ${s}. → Zendesk`);
  }

  // 3. Usage
  if (missing.has('product')) lines.push(`*Usage:* ${unknown('product usage')}`);
  else {
    const dir = f.usage_change_pct < 0 ? `down ${-f.usage_change_pct}%` : `up ${f.usage_change_pct}%`;
    lines.push(`*Usage:* ${f.avg_daily_active_users} daily active users on average over the last 30 days, ${dir} from the 30 days before. → product data`);
    if (f.usage_change_pct <= -20) flags.push('usage');
  }

  // 4. Contract
  if (missing.has('hubspot') || !f.renewal_date) lines.push(`*Contract:* ${unknown('contract dates')}`);
  else {
    const fmt = d => new Date(d).toLocaleDateString('en-GB', { day: 'numeric', month: 'short' });
    let c = `renews ${fmt(f.renewal_date)}, in ${days(f.days_to_renewal)}`;
    if (f.days_to_notice >= 0 && f.days_to_notice <= 30) {
      c += `. Notice deadline (last day to cancel) ${fmt(f.notice_deadline)}, in ${days(f.days_to_notice)}`;
      flags.push('notice');
    }
    lines.push(`*Contract:* ${c}. → HubSpot`);
  }

  // 5. Last contact
  if (missing.has('hubspot')) lines.push(`*Last contact:* ${unknown('CRM')}`);
  else {
    let l = f.last_meeting_days != null ? `last meeting ${days(f.last_meeting_days)} ago (${f.last_meeting_by})` : 'no meeting logged';
    if (f.latest_note_days != null && f.latest_note_days !== f.last_meeting_days)
      l += `. ${f.latest_note_by}, ${days(f.latest_note_days)} ago: "${f.latest_note}"`;
    lines.push(`*Last contact:* ${end(l)} → HubSpot`);
  }

  // One suggestion, from the flags only.
  let tip;
  if (missing.size > 0) tip = `Parts of this customer aren't linked yet (${[...missing].map(m => label[m]).join(', ')}). Ask RevOps to confirm the links, or check those systems yourself.`;
  else if (flags.includes('notice')) tip = `They have ${days(f.days_to_notice)} left to give notice. Ask about their renewal plans`
      + (flags.includes('urgent') ? ', and start with the urgent ticket.' : '.');
  else if (flags.includes('urgent')) tip = 'Start with the urgent ticket.';
  else if (flags.includes('overdue')) tip = 'Mention the overdue invoice, after the agenda.';
  else tip = 'Nothing unusual. Use the time for their goals.';

  const text = [`*${f.customer}* · ${f.title} · tomorrow ${time}`, ...lines.map(x => '• ' + x), `*Before you call:* ${tip}`].join('\n');
  out.push({ json: { meetingId: f.meeting_id, customer: f.customer, text }, pairedItem: { item: i } });
}
return out;

-- The customer layer. All companies, people and numbers are fictional.

-- One ID per customer, and the records that belong to it in each system.
CREATE TABLE customers (
  customer_id  text PRIMARY KEY,
  name         text NOT NULL,
  owner_name   text,
  owner_slack  text            -- Slack user ID of the account owner
);

CREATE TABLE customer_source_ids (
  customer_id text REFERENCES customers,
  source      text,            -- hubspot, stripe, zendesk, product
  source_id   text,
  status      text,            -- proposed or confirmed; only confirmed counts
  PRIMARY KEY (source, source_id)
);

-- Email domains used to recognise a customer in a calendar invite.
CREATE TABLE customer_domains (
  domain      text PRIMARY KEY,
  customer_id text REFERENCES customers
);

-- Copies of the source data, kept up to date by the sync workflow.
CREATE TABLE stripe_subscriptions (
  id text PRIMARY KEY, customer text, status text, mrr_eur numeric, synced_at timestamptz DEFAULT now()
);
CREATE TABLE stripe_invoices (
  id text PRIMARY KEY, customer text, number text, status text,
  amount_due_eur numeric, due_date date, synced_at timestamptz DEFAULT now()
);
CREATE TABLE zendesk_tickets (
  id bigint PRIMARY KEY, organization_id text, subject text, status text, priority text,
  created_at timestamptz, last_agent_reply_at timestamptz, tags text[],
  synced_at timestamptz DEFAULT now()
);
CREATE TABLE hubspot_companies (
  id text PRIMARY KEY, name text, contract_mrr_eur numeric, renewal_date date,
  notice_days int, synced_at timestamptz DEFAULT now()
);
CREATE TABLE hubspot_activities (
  id text PRIMARY KEY, company_id text, type text, occurred_at timestamptz,
  logged_by text, summary text, synced_at timestamptz DEFAULT now()
);

-- Product usage usually already lives in your own database.
CREATE TABLE product_usage_daily (
  account_id text, day date, active_users int, PRIMARY KEY (account_id, day)
);

-- Which briefs were sent, so nobody gets the same one twice.
CREATE TABLE brief_log (
  meeting_id  text PRIMARY KEY,
  customer_id text,
  sent_at     timestamptz DEFAULT now(),
  brief       text
);

INSERT INTO customers VALUES
 ('CUST_001', 'Northwind Analytics', 'Sanne de Vries', 'U01SANNE'),
 ('CUST_002', 'Vanderberg Mobility', 'Sanne de Vries', 'U01SANNE');

INSERT INTO customer_source_ids VALUES
 ('CUST_001', 'hubspot', 'hs_101',  'confirmed'),
 ('CUST_001', 'stripe',  'cus_N1',  'confirmed'),
 ('CUST_001', 'zendesk', 'org_501', 'confirmed'),
 ('CUST_001', 'product', 'acc_7',   'confirmed'),
 ('CUST_002', 'hubspot', 'hs_204',  'confirmed'),
 ('CUST_002', 'stripe',  'cus_V9',  'proposed');   -- suggested by a rule, nobody confirmed it yet

INSERT INTO customer_domains VALUES
 ('northwind.io', 'CUST_001'),
 ('vanderberg-mobility.nl', 'CUST_002');

-- Product usage: Northwind's active users fall from about 40 to about 26.
INSERT INTO product_usage_daily
SELECT 'acc_7', d::date,
       CASE WHEN d < date '2026-09-15' THEN 40 + (extract(day FROM d)::int % 3)
            ELSE 26 + (extract(day FROM d)::int % 2) END
FROM generate_series(date '2026-08-16', date '2026-10-14', interval '1 day') d;