CRM / NetSuite Replatform · Partner Compensation

Master Partner Parameters
Design & Implementation Guide

Analysis of the Treadstone Commercial Compensation Model against the Hyperion OMNI Partner Agreement (v1 Mar 2024 and v6 Jun 2025) — with a complete field specification for the NetSuite Master Partner Parameters tab and technical steps for PostgreSQL synchronization.

Section 1

Gap Analysis

The Treadstone model is architecturally sound — its 14 normalized parameters provide a valid framework. However, several contractual parameters in the Partner Agreement either fall between the model's parameters or require more granular sub-fields to be calculable in NetSuite and PostgreSQL.

Core Parameters — Coverage Confirmed

Gaps — Parameters Missing or Under-Specified

Section 2

NetSuite — Master Partner Parameters Tab

Complete field specification for the Master Partner Parameters tab on the Partner (Vendor/Customer) form. All fields are implemented as custom fields on either the Partner record or a child custom record linked one-to-one per active agreement.

Section 3

PostgreSQL Schema

The PostgreSQL database stores the authoritative compensation parameters (synced from NetSuite) and performs all compensation calculations. NetSuite is the system of record for parameters; PostgreSQL is the calculation and payment engine.

-- ============================================================
-- HYPERION PARTNER COMPENSATION — PostgreSQL Schema
-- ============================================================

-- Partners (master record, synced from NetSuite Partner/Vendor)
CREATE TABLE partners (
  partner_id          UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  ns_internal_id      INTEGER      UNIQUE NOT NULL,   -- NetSuite internalid
  entity_name         TEXT         NOT NULL,
  legal_address       TEXT,
  status              TEXT         NOT NULL DEFAULT 'active',
  created_at          TIMESTAMPTZ  DEFAULT now(),
  updated_at          TIMESTAMPTZ  DEFAULT now()
);

-- Partner Agreement Parameters (one row per active agreement)
CREATE TABLE partner_agreement_params (
  param_id                   UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  partner_id                 UUID         REFERENCES partners(partner_id) ON DELETE CASCADE,
  ns_record_id               INTEGER      UNIQUE,       -- NS custom record internalid

  -- Agreement identification
  agreement_type             TEXT         NOT NULL,     -- 'strategic_partner' | 'partner_referral'
  agreement_template_ver     TEXT,
  agreement_reference        TEXT,
  effective_date             DATE,
  expiration_date            DATE,
  agreement_status           TEXT         DEFAULT 'active',
  termination_date           DATE,
  termination_type           TEXT,
  postterm_residual_continues BOOLEAN     DEFAULT false,
  postterm_hold_days         INTEGER      DEFAULT 210,
  bidirectional_enabled      BOOLEAN      DEFAULT false,

  -- Active exhibits
  exhibit_a_active   BOOLEAN DEFAULT true,
  exhibit_b_active   BOOLEAN DEFAULT true,
  exhibit_c1_active  BOOLEAN DEFAULT false,
  exhibit_d_active   BOOLEAN DEFAULT false,
  exhibit_d1_active  BOOLEAN DEFAULT false,  -- v6 only
  exhibit_e_active   BOOLEAN DEFAULT false,
  exhibit_f_active   BOOLEAN DEFAULT false,
  exhibit_h_active   BOOLEAN DEFAULT false,

  -- Exhibit B — carrier revenue share
  b_commission_struct      TEXT,         -- 'tiered' | 'flat' | 'custom'
  b_lang_variant           TEXT,
  b_flat_rate_pct          NUMERIC(5,2), -- e.g. 50.00
  b_tier_select_pct        NUMERIC(5,2) DEFAULT 50,
  b_tier_premier_pct       NUMERIC(5,2) DEFAULT 60,
  b_tier_elite_pct         NUMERIC(5,2) DEFAULT 70,
  b_tier_titan_pct         NUMERIC(5,2) DEFAULT 80,
  b_current_tier           TEXT,         -- 'SELECT' | 'PREMIER' | 'ELITE' | 'TITAN'
  b_tier_elig_start_date   DATE,
  b_current_6mra           NUMERIC(12,2),
  b_tier_last_evaluated    DATE,
  b_spiff_in_passthru      BOOLEAN       DEFAULT false,
  b_spiff_rate_pct         NUMERIC(5,2),
  b_withhold_thresh_mult   NUMERIC(5,2)  DEFAULT 300, -- 300% trigger
  b_withhold_hold_pct      NUMERIC(5,2)  DEFAULT 20,
  b_min_payment_amt        NUMERIC(12,2),

  -- Exhibit C.1 — HYP-billed products
  c1_fee_pct               NUMERIC(5,2)  DEFAULT 10,
  c1_basis                 TEXT          DEFAULT 'net',
  c1_payment_terms_days    INTEGER       DEFAULT 30,
  c1_custom_note           TEXT,

  -- Exhibit D — VAST Smart SIM
  d_sim_pct                NUMERIC(5,2)  DEFAULT 10,
  d_sim_discounted_pct     NUMERIC(5,2),

  -- Exhibit D.1 — VAST Track (v6)
  d1_flat_commission_amt   NUMERIC(12,2),
  d1_reg_method            TEXT,         -- 'discount_code' | 'registered_lead'

  -- Exhibit E — HYP-generated lead (commission to HYP)
  e_onetime_pct            NUMERIC(5,2),
  e_recurring_pct          NUMERIC(5,2),
  e_eligible_products      TEXT,
  e_ineligible_products    TEXT,

  -- Exhibit F — Partner-generated lead (commission to Partner)
  f_onetime_pct            NUMERIC(5,2),
  f_recurring_pct          NUMERIC(5,2),
  f_eligible_products      TEXT,
  f_ineligible_products    TEXT,

  -- Exception / override
  exception_active         BOOLEAN       DEFAULT false,
  exception_type           TEXT,
  exception_authorized_by  TEXT,
  exception_eff_date       DATE,
  exception_exp_date       DATE,
  exception_notes          TEXT,

  -- Sync bookkeeping
  ns_last_modified         TIMESTAMPTZ,
  pg_synced_at             TIMESTAMPTZ   DEFAULT now(),
  sync_version             INTEGER       DEFAULT 1
);

-- Audit trail for all parameter changes
CREATE TABLE parameter_audit_log (
  log_id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  param_id       UUID        REFERENCES partner_agreement_params(param_id),
  changed_at     TIMESTAMPTZ DEFAULT now(),
  changed_by     TEXT,        -- 'netsuite_sync' | 'manual' | 'scheduled_script'
  field_name     TEXT        NOT NULL,
  old_value      TEXT,
  new_value      TEXT,
  source_ref     TEXT        -- NS record ID or change order ref
);

-- Compensation calculations (results, not parameters)
CREATE TABLE compensation_calculations (
  calc_id           UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  partner_id        UUID         REFERENCES partners(partner_id),
  param_id          UUID         REFERENCES partner_agreement_params(param_id),
  commission_period DATE         NOT NULL,  -- first day of month
  exhibit           TEXT         NOT NULL,  -- 'B' | 'C1' | 'D' | 'D1' | 'E' | 'F'
  carrier           TEXT,
  gross_revenue     NUMERIC(14,2),
  net_revenue       NUMERIC(14,2),
  escr              NUMERIC(14,2),
  applied_rate_pct  NUMERIC(5,2),
  applied_tier      TEXT,
  comp_type         TEXT,         -- 'upfront' | 'residual' | 'spiff' | 'vast' | 'referral'
  gross_comp        NUMERIC(14,2),
  withholding_amt   NUMERIC(14,2) DEFAULT 0,
  chargeback_offset NUMERIC(14,2) DEFAULT 0,
  net_payable       NUMERIC(14,2),
  payment_status    TEXT          DEFAULT 'pending',
  calculated_at     TIMESTAMPTZ   DEFAULT now()
);

-- Index for common queries
CREATE INDEX idx_calc_partner_period
  ON compensation_calculations(partner_id, commission_period);
CREATE INDEX idx_params_ns_id
  ON partner_agreement_params(ns_record_id);

Section 4

Sync Architecture — NetSuite ↔ PostgreSQL

Parameters are authored and governed in NetSuite (source of record). PostgreSQL receives them via a push-on-save mechanism and a nightly reconciliation batch. Calculations flow in the opposite direction for visibility in NetSuite.

Option A — Push on Save (Recommended for real-time)

A SuiteScript 2.x UserEvent Script fires after any save of the Partner Agreement Parameters custom record. It calls a RESTlet (deployed on NetSuite) or an external HTTPS endpoint that writes to PostgreSQL.

// SuiteScript 2.x — UserEvent AfterSubmit
// File: hyp_ue_partner_params_sync.js

define(['N/https', 'N/record', 'N/log'], function(https, record, log) {
  function afterSubmit(context) {
    if (context.type === context.UserEventType.DELETE) return;

    var rec = context.newRecord;
    var payload = {
      ns_record_id:             rec.id,
      partner_ns_id:            rec.getValue('custrecord_hyp_partner'),
      agreement_type:           rec.getValue('custrecord_hyp_agmt_type'),
      agreement_template_ver:   rec.getValue('custrecord_hyp_agmt_tmpl_ver'),
      agreement_reference:      rec.getValue('custrecord_hyp_agmt_ref'),
      effective_date:           rec.getValue('custrecord_hyp_agmt_eff_date'),
      exhibit_b_active:         rec.getValue('custrecord_hyp_exh_b'),
      b_commission_struct:      rec.getValue('custrecord_hyp_b_struct'),
      b_flat_rate_pct:          rec.getValue('custrecord_hyp_b_flat_pct'),
      b_current_tier:           rec.getValue('custrecord_hyp_b_cur_tier'),
      b_spiff_in_passthru:      rec.getValue('custrecord_hyp_b_spiff_passthru'),
      b_withhold_thresh_mult:   rec.getValue('custrecord_hyp_b_withhold_thresh'),
      b_withhold_hold_pct:      rec.getValue('custrecord_hyp_b_withhold_pct'),
      exhibit_d1_active:        rec.getValue('custrecord_hyp_exh_d1'),
      d1_flat_commission_amt:   rec.getValue('custrecord_hyp_d1_flat_amt'),
      // ... all other fields ...
      ns_last_modified:         new Date().toISOString()
    };

    var response = https.post({
      url:     'https://your-sync-api.internal/api/partner-params/upsert',
      body:    JSON.stringify(payload),
      headers: {
        'Content-Type':  'application/json',
        'Authorization': 'Bearer ' + runtime.getCurrentScript()
                          .getParameter('custscript_hyp_sync_token')
      }
    });

    log.audit('ParamSync', 'Status ' + response.code + ' for record ' + rec.id);
    if (response.code !== 200) {
      log.error('SyncFailed', response.body);
      // Queue for retry via custom record
    }
  }
  return { afterSubmit: afterSubmit };
});

Sync API — Upsert Endpoint (Node.js/Express example)

// POST /api/partner-params/upsert
// Receives NS payload, transforms, upserts into PostgreSQL

app.post('/api/partner-params/upsert', verifyHmac, async (req, res) => {
  const p = req.body;
  const sql = `
    INSERT INTO partner_agreement_params (
      ns_record_id, partner_id, agreement_type, agreement_template_ver,
      effective_date, exhibit_b_active, b_commission_struct, b_flat_rate_pct,
      b_current_tier, b_spiff_in_passthru, b_withhold_thresh_mult,
      b_withhold_hold_pct, exhibit_d1_active, d1_flat_commission_amt,
      ns_last_modified, pg_synced_at
    )
    VALUES ($1, (SELECT partner_id FROM partners WHERE ns_internal_id=$2),
            $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, now())
    ON CONFLICT (ns_record_id) DO UPDATE SET
      agreement_type         = EXCLUDED.agreement_type,
      b_flat_rate_pct        = EXCLUDED.b_flat_rate_pct,
      b_current_tier         = EXCLUDED.b_current_tier,
      b_withhold_thresh_mult = EXCLUDED.b_withhold_thresh_mult,
      exhibit_d1_active      = EXCLUDED.exhibit_d1_active,
      d1_flat_commission_amt = EXCLUDED.d1_flat_commission_amt,
      ns_last_modified       = EXCLUDED.ns_last_modified,
      pg_synced_at           = now(),
      sync_version           = partner_agreement_params.sync_version + 1
    RETURNING param_id, sync_version`;
  await db.query(sql, [p.ns_record_id, p.partner_ns_id, p.agreement_type, ...]);
  await logAuditDiff(p);  // write field-level diffs to parameter_audit_log
  res.json({ ok: true });
});

Option B — Nightly Reconciliation Batch (Safety net)

A NetSuite Scheduled Script runs nightly and exports all custrecord_hyp_partner_params records modified since last sync. This catches any saves that missed the UserEvent push (e.g., CSV imports, bulk updates).

// SuiteScript 2.x Scheduled Script — nightly delta export
// Runs at 2 AM; queries NS saved search, pushes deltas to PG

define(['N/search', 'N/https'], function(search, https) {
  function execute(context) {
    var s = search.create({
      type: 'customrecord_hyp_partner_params',
      filters: [['lastmodified', 'onorafter',
                 'yesterday']],   // or use a stored last-run timestamp
      columns: [/* all custrecord_ fields */]
    });
    var batch = [];
    s.run().each(function(result) {
      batch.push(mapResultToPayload(result));
      if (batch.length >= 50) { flushBatch(batch); batch = []; }
      return true;
    });
    if (batch.length) flushBatch(batch);
  }
  return { execute: execute };
});

Option C — PostgreSQL → NetSuite (write-back)

For calculated fields (Current 6MRA, Current Tier, Last Tier Evaluated), PostgreSQL pushes results back to NetSuite after each monthly calculation run, using the NetSuite REST API:

# Python — write 6MRA + tier back to NS custom record after monthly calc

import requests, json

def write_tier_to_netsuite(ns_record_id: int, tier: str, sixmra: float, eval_date: str):
    url = (f"https://<account>.suitetalk.api.netsuite.com"
           f"/services/rest/record/v1"
           f"/customrecord_hyp_partner_params/{ns_record_id}")
    payload = {
        "custrecord_hyp_b_cur_tier":        tier,
        "custrecord_hyp_b_6mra":            sixmra,
        "custrecord_hyp_b_tier_last_eval":   eval_date
    }
    resp = requests.patch(url,
        json=payload,
        auth=OAuth1(consumer_key, consumer_secret, token, token_secret),
        headers={"Content-Type": "application/json"})
    resp.raise_for_status()

Section 5

Implementation Plan

Recommended sequence across four phases. Phases 1–2 can be run in parallel after alignment; Phase 3 requires Phase 1 custom records to exist in NetSuite; Phase 4 requires Phase 3 endpoints to be live.

Phase 1 — NetSuite Configuration

1. Create Custom Record Type: Partner Agreement Parameters

   In NetSuite: Setup → Customization → Record Types → New. Name: "Partner Agreement Parameters"; Internal ID: customrecord_hyp_partner_params. Add a parent relationship field (custrecord_hyp_partner) linking to the Partner/Vendor record. Set "Show in List" and "Allow Quick Add" as appropriate. Enable versioning via a sync_version integer field.
2. Add all custom fields per the specification in Section 2

   Create each field listed in the Master Partner Parameters spec above. Group them by exhibit section using field groups. Use List/Record type for all enumerated fields to enforce valid values. For Exhibit D.1 fields, set a display condition: show only when Agreement Template Version = v6 or Exhibit D.1 Active = true.
3. Build the "Master Partner Parameters" subtab on the Partner form

   Navigate to Customization → Forms → Partner Form → Subtabs. Add a new subtab labeled "Master Partner Parameters." Use a Custom Sublist to display all customrecord_hyp_partner_params records linked to the current partner. This allows viewing all agreement versions in one place.
4. Create validation SuiteScript (UserEvent BeforeSubmit)

   Write a BeforeSubmit script to: (a) enforce that Exhibit A is always active if Exhibit B is active; (b) require VAST Track Commission Amount when Exhibit D.1 is activated; (c) auto-set Post-Term Residual Continues based on Termination Type; (d) calculate Tier Eligibility Start Date as Effective Date + 6 months. Deploy to the custom record type.
5. Deploy UserEvent AfterSubmit sync script

   Deploy the SuiteScript push-on-save shown in Section 4. Store the sync API token in a Script Parameter (Setup → Script → Deployment → Parameters). Set governance usage to ≤100 units. Test with a sandbox partner record first.
6. Create Saved Search for nightly delta export

   Create a saved search on the custom record type filtering Last Modified on or after {yesterday} with all compensation fields as columns. This saved search is used by both the nightly Scheduled Script and as an ODBC data source for reporting.

Phase 2 — PostgreSQL Setup

1. Deploy the schema from Section 3

   Run the DDL script in your PostgreSQL instance (14+ recommended for gen_random_uuid() default). Enable pg_audit extension for row-level auditing, or use the parameter_audit_log table with triggers. Create a dedicated database user hyp_comp_api with INSERT/UPDATE/SELECT on the three tables and no DDL access.
2. Build the Sync API microservice

   Deploy a lightweight Node.js (Express) or Python (FastAPI) service with two endpoints: POST /api/partner-params/upsert (single record) and POST /api/partner-params/batch (up to 50 records). Implement HMAC-SHA256 request verification using a shared secret stored in NetSuite Script Parameters and in the microservice's environment variables.
3. Implement audit diff logging

   In the upsert endpoint, before executing the INSERT … ON CONFLICT, SELECT the current row and compute field-level diffs. Insert one row per changed field into parameter_audit_log in the same transaction. This provides a complete amendment history aligned with Exhibit G (Change Requests).
4. Write the 6MRA calculation function

   Create a PostgreSQL function calculate_6mra(partner_id UUID, as_of_date DATE) that queries the last 6 months of ESCR from compensation_calculations, computes the average, determines the applicable tier, and updates b_current_tier, b_current_6mra, and b_tier_last_evaluated in partner_agreement_params. Schedule this as a monthly pg_cron job or call it from the nightly batch.

Phase 3 — Initial Data Load

1. Map existing partner agreements to the new field structure

   For each active partner, review the executed agreement to identify: which template version was used (v1 or v6), which exhibits are active, and the agreed rates. Particularly check Exhibit B for tiered vs flat structure and Exhibit E/F for any filled-in eligible product lists. Document any partners with custom rates.
2. Bulk-load existing partners via NetSuite CSV Import

   Use NetSuite's CSV Import → Custom Records to load all partners' agreement parameters. The import maps spreadsheet columns to custrecord_hyp_ field IDs. Validate that each record triggers the AfterSubmit sync script (check Sync API logs) or run the nightly batch manually after import.
3. Backfill historical ESCR data into compensation_calculations

   For tiered partners, backfill historical monthly ESCR from your existing system into PostgreSQL. This allows the 6MRA function to calculate accurate current tiers at go-live rather than resetting all partners to SELECT tier.

Phase 4 — Ongoing Operations

1. Run monthly compensation calculation

   Call calculate_6mra() for all tiered partners at the start of each commission period. Then run the compensation calculation job, which reads partner_agreement_params, applies the applicable rate (tier or flat), applies the withholding check (300% rule), and writes results to compensation_calculations.
2. Push tier updates back to NetSuite

   After each monthly 6MRA calculation, use the NetSuite REST API PATCH call (Python example in Section 4) to update custrecord_hyp_b_cur_tier, custrecord_hyp_b_6mra, and custrecord_hyp_b_tier_eval_dt on the partner's NS record. This ensures the NS form always shows current tier for the Channel team's reference.
3. Monitor sync health

   Build a reconciliation query: compare COUNT(*) of NS custom records (via ODBC or Scheduled Script export) against PostgreSQL rows by ns_record_id. Alert if any NS record has ns_last_modified > pg_synced_at + 5 minutes — that record missed the real-time push and will need the nightly batch to catch it. Log to a sync_health table and expose a dashboard.
4. Process Exhibit G change requests

   When a partner signs a Change Order (Exhibit G), update the relevant fields on the NS custom record. The AfterSubmit script auto-pushes the delta to PostgreSQL. The audit log captures the old and new values with a reference to the Change Order number (store in exception_notes or a dedicated change_order_ref field). Future amendments automatically version via sync_version increment.