Skip to main content

Analyze table schemas for AI workflows

User

easymanage get and study all table schema and suggest what AI workflows can be implemented based on tables

Cursor

I'll fetch the full database schema first so I can tailor workflow suggestions precisely to your data model. Then I'll analyze and propose concrete AI workflows mapped to tables and fields.

Fetching the full table schema now so I can analyze it and propose tailored AI workflows.

High-value AI workflows for your SuiteCRM/EasyManage data​

  • Opportunity win probability scoring

    • Uses: OpportunitiesVw (salesStage, amount, leadSource, opportunityType, dateClosed, probability [as label if populated]), AccountsVw (industry, accountType, employees, annualRevenue), AccountsOpportunitiesVw (linkage).
    • Outcome: Prioritized pipeline, next-best actions, forecast uplift.

  • Close date and amount forecasting

    • Uses: OpportunitiesVw (amount, dateClosed, salesStage, nextStep), AccountsVw (firmographics).
    • Outcome: Time-series forecasts for revenue and expected close dates per opp.

  • Lead/contact scoring and routing

    • Uses: ContactsVw (leadSource, title, department, phone/email presence, doNotCall, campaignId), AccountsVw (industry, size), AccountsContactsVw.
    • Outcome: Prioritized outreach, assignment rules, SLA-based routing.

  • Churn risk for existing accounts

    • Uses: AccountsVw (industry, revenue, employees), related OpportunitiesVw (won/lost history inferred from salesStage/probability) and recent activities (proxied by dateModified deltas).
    • Outcome: Early-warning list and retention playbooks.

  • Next-best action recommendations

    • Uses: OpportunitiesVw (nextStep, salesStage, leadSource), AopCaseUpdatesVw (signals from support), CasesVw (open tickets), ContactsVw (role/title).
    • Outcome: Stage-aware suggestions (call, email, proposal), timed nudges.

  • Quote line item price optimization

    • Uses: AosProductsQuotesVw (productQty, listPrice, discount, unitPrice, vat, totalPrice), AosQuotesVw (subtotal, discountAmount, shipping/tax), AccountsVw (segment).
    • Outcome: Discount guidance, margin guardrails, win-rate vs. price tradeoff.

  • Anomaly detection on quotes and discounts

    • Uses: AosProductsQuotesVw, AosQuotesVw, user/owner from assignedUserId.
    • Outcome: Flag outlier discounts, price leakage, approval triggers.

  • Support case auto-triage and reply assist

    • Uses: CasesVw (type, status, priority, description, workLog), AopCaseUpdatesVw (internal notes, updates), ContactsCasesVw (roles).
    • Outcome: Priority prediction, suggested replies, knowledge snippets, auto-assign.

  • SLA breach prediction for cases

    • Uses: CasesVw timestamps (dateEntered, dateModified), priority, status, AopCaseUpdatesVw.internal.
    • Outcome: Proactive escalations and staffing alerts.

  • Cross-sell / upsell recommendations

    • Uses: AosProductsQuotesVw joined by groupId/parentId to quotes/opps, with account segments from AccountsVw.
    • Outcome: Product bundling suggestions per account/opportunity.

  • Campaign attribution lift analysis

    • Uses: ContactsVw.campaignId, OpportunitiesVw.campaignId, leadSource.
    • Outcome: Multi-touch attribution heuristics, ROI by channel.

  • GDPR and contact compliance checks

    • Uses: ContactsVw (lawfulBasis, dateReviewed, lawfulBasisSource, doNotCall).
    • Outcome: Outreach compliance gating and review reminders.

Suggested feature mappings (tables/fields)​

  • Labels/targets

    • Opportunity win: derive from salesStage terminal states or probability thresholds.
    • Case priority: map current priority/SLAs to labels for training.
    • Discount anomaly: learn from historic distribution per product/account/owner.

  • Features

    • Opportunity: amount, leadSource, opportunityType, dateEntered→dateClosed days, salesStage, account.industry, account.employees, account.annualRevenue.
    • Contact: title, department, leadSource, campaignId, phone/email presence proxies, doNotCall.
    • Case: type, status, priority, text embeddings from description/workLog, activity cadence from AopCaseUpdatesVw.
    • Quotes: per-line productQty, listPrice, discount, per-quote totals, account segment.

Data preparation notes​

  • Join keys:

    • AccountsOpportunitiesVw.accountId → AccountsVw.idcol; opportunityId → OpportunitiesVw.idcol.
    • AccountsContactsVw.accountId → AccountsVw.idcol; contactId → ContactsVw.idcol.
    • AosQuotesVw.opportunityId → OpportunitiesVw.idcol; AosProductsQuotesVw.parentId/groupId to AosQuotesVw.
    • CasesVw.accountId → AccountsVw.idcol; ContactsCasesVw.caseId/contactId to CasesVw/ContactsVw.
    • Campaigns via campaignId on contacts/opportunities/accounts.

  • Time fields: use dateEntered, dateModified, dateClosed, expiration to build recency/frequency features and label windows.

  • Currency normalization: use amountUsdollar, product price USD fields to avoid currency leakage.

Phased implementation plan​

  • Phase 1 (2-3 weeks)

    • Opportunity win scoring and case triage models.
    • Quote anomaly detection rules + simple model.
    • Compliance gate for contacts.

  • Phase 2 (3-5 weeks)

    • Close date/amount forecasting; discount guidance.
    • Next-best action recommender.

  • Phase 3 (ongoing)

    • Churn risk, cross-sell, campaign attribution.

Would you like me to prioritize and draft data pipelines (SQL joins and feature views) for Phase 1 now?

User

yes

Cursor

I'll add implementation tasks for Phase 1 feature views and start drafting the opportunity view SQL now.

Opportunity win-scoring feature view (SQL)​

-- Feature view: opportunity_features_vw
-- Purpose: Train/predict win probability and prioritize pipeline

WITH opp_base AS (
  SELECT
    o.idcol                       AS opportunity_id,
    o.name                        AS opportunity_name,
    o.dateEntered                 AS opp_created_at,
    o.dateModified                AS opp_last_modified_at,
    o.dateClosed                  AS opp_expected_close_at,
    o.salesStage                  AS sales_stage,
    o.leadSource                  AS lead_source,
    o.opportunityType             AS opportunity_type,
    o.amountUsdollar              AS amount_usd,
    o.probability                 AS probability_current,
    o.assignedUserId              AS owner_user_id
  FROM OpportunitiesVw o
  WHERE COALESCE(o.deleted, 0) = 0
),
opp_accounts AS (
  SELECT
    ao.opportunityId              AS opportunity_id,
    ao.accountId                  AS account_id
  FROM AccountsOpportunitiesVw ao
  WHERE COALESCE(ao.deleted, 0) = 0
),
acct_features AS (
  SELECT
    a.idcol                       AS account_id,
    a.industry                    AS account_industry,
    a.accountType                 AS account_type,
    NULLIF(REGEXP_REPLACE(a.employees, '[^0-9]', ''), '')::INT    AS employees_num,
    CAST(NULLIF(REPLACE(REPLACE(a.annualRevenue, ',', ''), '$', ''), '') AS DECIMAL(18,2)) AS annual_revenue_num
  FROM AccountsVw a
  WHERE COALESCE(a.deleted, 0) = 0
),
opp_enriched AS (
  SELECT
    b.*,
    af.account_industry,
    af.account_type,
    af.employees_num,
    af.annual_revenue_num
  FROM opp_base b
  LEFT JOIN opp_accounts oa ON oa.opportunity_id = b.opportunity_id
  LEFT JOIN acct_features af ON af.account_id = oa.account_id
),
time_feats AS (
  SELECT
    e.*,
    DATE_DIFF('day', e.opp_created_at, CURRENT_TIMESTAMP)          AS opp_age_days,
    CASE
      WHEN e.opp_expected_close_at IS NOT NULL
      THEN DATE_DIFF('day', CURRENT_TIMESTAMP, e.opp_expected_close_at)
      ELSE NULL
    END                                                           AS days_until_expected_close
  FROM opp_enriched e
),
label_derivation AS (
  -- Optional label for supervised learning; adjust rules to your CRM semantics
  SELECT
    t.*,
    CASE
      WHEN LOWER(t.sales_stage) IN ('closed won','won') THEN 1
      WHEN LOWER(t.sales_stage) IN ('closed lost','lost') THEN 0
      ELSE NULL
    END                                                           AS label_won
  FROM time_feats t
)
SELECT
  opportunity_id,
  opportunity_name,
  owner_user_id,
  opp_created_at,
  opp_last_modified_at,
  opp_expected_close_at,
  sales_stage,
  lead_source,
  opportunity_type,
  amount_usd,
  probability_current,
  account_industry,
  account_type,
  employees_num,
  annual_revenue_num,
  opp_age_days,
  days_until_expected_close,
  label_won
FROM label_derivation;

Want me to proceed with the case-triage and quote-anomaly feature views next?

-- Feature view: opportunity_features_vw
-- Purpose: Train/predict win probability and prioritize pipeline

WITH opp_base AS (
  SELECT
    o.idcol                       AS opportunity_id,
    o.name                        AS opportunity_name,
    o.dateEntered                 AS opp_created_at,
    o.dateModified                AS opp_last_modified_at,
    o.dateClosed                  AS opp_expected_close_at,
    o.salesStage                  AS sales_stage,
    o.leadSource                  AS lead_source,
    o.opportunityType             AS opportunity_type,
    o.amountUsdollar              AS amount_usd,
    o.probability                 AS probability_current,
    o.assignedUserId              AS owner_user_id
  FROM OpportunitiesVw o
  WHERE COALESCE(o.deleted, 0) = 0
),
opp_accounts AS (
  SELECT
    ao.opportunityId              AS opportunity_id,
    ao.accountId                  AS account_id
  FROM AccountsOpportunitiesVw ao
  WHERE COALESCE(ao.deleted, 0) = 0
),
acct_features AS (
  SELECT
    a.idcol                       AS account_id,
    a.industry                    AS account_industry,
    a.accountType                 AS account_type,
    NULLIF(REGEXP_REPLACE(a.employees, '[^0-9]', ''), '')::INT    AS employees_num,
    CAST(NULLIF(REPLACE(REPLACE(a.annualRevenue, ',', ''), '$', ''), '') AS DECIMAL(18,2)) AS annual_revenue_num
  FROM AccountsVw a
  WHERE COALESCE(a.deleted, 0) = 0
),
opp_enriched AS (
  SELECT
    b.*,
    af.account_industry,
    af.account_type,
    af.employees_num,
    af.annual_revenue_num
  FROM opp_base b
  LEFT JOIN opp_accounts oa ON oa.opportunity_id = b.opportunity_id
  LEFT JOIN acct_features af ON af.account_id = oa.account_id
),
time_feats AS (
  SELECT
    e.*,
    DATE_DIFF('day', e.opp_created_at, CURRENT_TIMESTAMP)          AS opp_age_days,
    CASE
      WHEN e.opp_expected_close_at IS NOT NULL
      THEN DATE_DIFF('day', CURRENT_TIMESTAMP, e.opp_expected_close_at)
      ELSE NULL
    END                                                           AS days_until_expected_close
  FROM opp_enriched e
),
label_derivation AS (
  -- Optional label for supervised learning; adjust rules to your CRM semantics
  SELECT
    t.*,
    CASE
      WHEN LOWER(t.sales_stage) IN ('closed won','won') THEN 1
      WHEN LOWER(t.sales_stage) IN ('closed lost','lost') THEN 0
      ELSE NULL
    END                                                           AS label_won
  FROM time_feats t
)
SELECT
  opportunity_id,
  opportunity_name,
  owner_user_id,
  opp_created_at,
  opp_last_modified_at,
  opp_expected_close_at,
  sales_stage,
  lead_source,
  opportunity_type,
  amount_usd,
  probability_current,
  account_industry,
  account_type,
  employees_num,
  annual_revenue_num,
  opp_age_days,
  days_until_expected_close,
  label_won
FROM label_derivation;

User

yes for all to-dos

Cursor

I'll mark the opportunity view done and start the case-triage SQL, then proceed through quote-anomaly and compliance views, and finish with the implementation plan, updating tasks as I go.

Case triage feature view (SQL)​

-- Feature view: case_triage_features_vw
-- Purpose: Predict case priority/urgency, auto-assign, and SLA risk

WITH cases_base AS (
  SELECT
    c.idcol                     AS case_id,
    c.name                      AS case_name,
    c.dateEntered               AS case_created_at,
    c.dateModified              AS case_last_modified_at,
    c.type                      AS case_type,
    c.status                    AS case_status,
    c.priority                  AS case_priority,
    c.resolution                AS case_resolution,
    c.workLog                   AS case_worklog,
    c.description               AS case_description,
    c.accountId                 AS account_id,
    c.assignedUserId            AS owner_user_id,
    c.state                     AS case_state
  FROM CasesVw c
  WHERE COALESCE(c.deleted, 0) = 0
),
case_updates AS (
  SELECT
    u.caseId                   AS case_id,
    COUNT(*)                   AS num_updates,
    SUM(CASE WHEN COALESCE(u.internal, 0) = 1 THEN 1 ELSE 0 END) AS num_internal_updates,
    MAX(u.dateModified)        AS last_update_at
  FROM AopCaseUpdatesVw u
  WHERE COALESCE(u.deleted, 0) = 0
  GROUP BY u.caseId
),
account_feats AS (
  SELECT
    a.idcol                    AS account_id,
    a.industry                 AS account_industry,
    a.accountType              AS account_type,
    a.rating                   AS account_rating
  FROM AccountsVw a
  WHERE COALESCE(a.deleted, 0) = 0
),
joined AS (
  SELECT
    b.*,
    af.account_industry,
    af.account_type,
    af.account_rating,
    cu.num_updates,
    cu.num_internal_updates,
    cu.last_update_at
  FROM cases_base b
  LEFT JOIN account_feats af ON af.account_id = b.account_id
  LEFT JOIN case_updates cu ON cu.case_id = b.case_id
),
time_feats AS (
  SELECT
    j.*,
    DATE_DIFF('minute', j.case_created_at, CURRENT_TIMESTAMP)      AS age_minutes,
    DATE_DIFF('minute', COALESCE(j.last_update_at, j.case_created_at), CURRENT_TIMESTAMP) AS minutes_since_last_update
  FROM joined j
),
label_derivation AS (
  -- Optional training label using current priority as proxy
  SELECT
    t.*,
    CASE
      WHEN LOWER(t.case_priority) IN ('p1','high','urgent','critical') THEN 1
      WHEN LOWER(t.case_priority) IN ('low','p3','p4') THEN 0
      ELSE NULL
    END AS label_high_priority
  FROM time_feats t
)
SELECT
  case_id,
  case_name,
  owner_user_id,
  case_created_at,
  case_last_modified_at,
  case_type,
  case_status,
  case_priority,
  case_state,
  account_id,
  account_industry,
  account_type,
  account_rating,
  num_updates,
  num_internal_updates,
  last_update_at,
  age_minutes,
  minutes_since_last_update,
  case_description,
  case_worklog,
  case_resolution,
  label_high_priority
FROM label_derivation;

Proceeding to draft the quote anomaly and contact compliance views next.

-- Feature view: case_triage_features_vw
-- Purpose: Predict case priority/urgency, auto-assign, and SLA risk

WITH cases_base AS (
  SELECT
    c.idcol                     AS case_id,
    c.name                      AS case_name,
    c.dateEntered               AS case_created_at,
    c.dateModified              AS case_last_modified_at,
    c.type                      AS case_type,
    c.status                    AS case_status,
    c.priority                  AS case_priority,
    c.resolution                AS case_resolution,
    c.workLog                   AS case_worklog,
    c.description               AS case_description,
    c.accountId                 AS account_id,
    c.assignedUserId            AS owner_user_id,
    c.state                     AS case_state
  FROM CasesVw c
  WHERE COALESCE(c.deleted, 0) = 0
),
case_updates AS (
  SELECT
    u.caseId                   AS case_id,
    COUNT(*)                   AS num_updates,
    SUM(CASE WHEN COALESCE(u.internal, 0) = 1 THEN 1 ELSE 0 END) AS num_internal_updates,
    MAX(u.dateModified)        AS last_update_at
  FROM AopCaseUpdatesVw u
  WHERE COALESCE(u.deleted, 0) = 0
  GROUP BY u.caseId
),
account_feats AS (
  SELECT
    a.idcol                    AS account_id,
    a.industry                 AS account_industry,
    a.accountType              AS account_type,
    a.rating                   AS account_rating
  FROM AccountsVw a
  WHERE COALESCE(a.deleted, 0) = 0
),
joined AS (
  SELECT
    b.*,
    af.account_industry,
    af.account_type,
    af.account_rating,
    cu.num_updates,
    cu.num_internal_updates,
    cu.last_update_at
  FROM cases_base b
  LEFT JOIN account_feats af ON af.account_id = b.account_id
  LEFT JOIN case_updates cu ON cu.case_id = b.case_id
),
time_feats AS (
  SELECT
    j.*,
    DATE_DIFF('minute', j.case_created_at, CURRENT_TIMESTAMP)      AS age_minutes,
    DATE_DIFF('minute', COALESCE(j.last_update_at, j.case_created_at), CURRENT_TIMESTAMP) AS minutes_since_last_update
  FROM joined j
),
label_derivation AS (
  -- Optional training label using current priority as proxy
  SELECT
    t.*,
    CASE
      WHEN LOWER(t.case_priority) IN ('p1','high','urgent','critical') THEN 1
      WHEN LOWER(t.case_priority) IN ('low','p3','p4') THEN 0
      ELSE NULL
    END AS label_high_priority
  FROM time_feats t
)
SELECT
  case_id,
  case_name,
  owner_user_id,
  case_created_at,
  case_last_modified_at,
  case_type,
  case_status,
  case_priority,
  case_state,
  account_id,
  account_industry,
  account_type,
  account_rating,
  num_updates,
  num_internal_updates,
  last_update_at,
  age_minutes,
  minutes_since_last_update,
  case_description,
  case_worklog,
  case_resolution,
  label_high_priority
FROM label_derivation;

User

yes

Cursor

Marking the case-triage feature view as completed and starting the quote-anomaly SQL next.

Quote anomaly feature view (SQL)​

-- Feature view: quote_anomaly_features_vw
-- Purpose: Detect anomalous discounts/pricing at line and quote levels

WITH quotes AS (
  SELECT
    q.idcol                     AS quote_id,
    q.name                      AS quote_name,
    q.dateEntered               AS quote_created_at,
    q.dateModified              AS quote_last_modified_at,
    q.assignedUserId            AS owner_user_id,
    q.billingAccountId          AS account_id,
    q.opportunityId             AS opportunity_id,
    q.subtotalAmountUsdollar    AS subtotal_usd,
    q.discountAmountUsdollar    AS discount_usd,
    q.taxAmountUsdollar         AS tax_usd,
    q.shippingAmountUsdollar    AS shipping_usd,
    q.totalAmount               AS total_usd
  FROM AosQuotesVw q
  WHERE COALESCE(q.deleted, 0) = 0
),
quote_lines AS (
  SELECT
    l.idcol                     AS line_id,
    l.parentId                  AS quote_id,
    l.groupId                   AS group_id,
    l.productId                 AS product_id,
    l.partNumber                AS part_number,
    l.itemDescription           AS item_description,
    l.productQty                AS qty,
    l.productListPriceUsdollar  AS list_price_usd,
    l.productUnitPriceUsdollar  AS unit_price_usd,
    l.productDiscountUsdollar   AS discount_usd,
    l.productDiscountAmountUsdollar AS discount_amount_usd,
    l.productTotalPriceUsdollar AS line_total_usd,
    l.vatAmtUsdollar            AS vat_usd,
    l.vat                       AS vat_rate
  FROM AosProductsQuotesVw l
  WHERE COALESCE(l.deleted, 0) = 0
),
account_feats AS (
  SELECT
    a.idcol                     AS account_id,
    a.industry                  AS account_industry,
    a.accountType               AS account_type,
    a.rating                    AS account_rating
  FROM AccountsVw a
  WHERE COALESCE(a.deleted, 0) = 0
),
joined AS (
  SELECT
    q.*,
    af.account_industry,
    af.account_type,
    af.account_rating
  FROM quotes q
  LEFT JOIN account_feats af ON af.account_id = q.account_id
),
line_enriched AS (
  SELECT
    q.quote_id,
    q.owner_user_id,
    q.account_id,
    q.account_industry,
    q.account_type,
    q.account_rating,
    q.opportunity_id,
    q.subtotal_usd,
    q.discount_usd,
    q.tax_usd,
    q.shipping_usd,
    q.total_usd,
    q.quote_created_at,
    q.quote_last_modified_at,
    l.line_id,
    l.group_id,
    l.product_id,
    l.part_number,
    l.item_description,
    l.qty,
    l.list_price_usd,
    l.unit_price_usd,
    l.discount_usd           AS line_discount_usd,
    l.discount_amount_usd    AS line_discount_amount_usd,
    l.line_total_usd,
    l.vat_usd,
    l.vat_rate,
    CASE
      WHEN l.list_price_usd IS NOT NULL AND l.list_price_usd > 0
      THEN (l.list_price_usd - l.unit_price_usd) / l.list_price_usd
      ELSE NULL
    END AS line_discount_pct
  FROM quote_lines l
  JOIN joined q ON q.quote_id = l.quote_id
),
quote_rollup AS (
  SELECT
    quote_id,
    COUNT(*)                                                AS num_lines,
    SUM(line_total_usd)                                     AS lines_total_usd,
    AVG(NULLIF(line_discount_pct, 0))                       AS avg_line_discount_pct,
    MAX(line_discount_pct)                                  AS max_line_discount_pct
  FROM line_enriched
  GROUP BY quote_id
)
SELECT
  le.*,
  qr.num_lines,
  qr.lines_total_usd,
  qr.avg_line_discount_pct,
  qr.max_line_discount_pct,
  CASE
    WHEN le.subtotal_usd IS NOT NULL AND le.subtotal_usd > 0
    THEN COALESCE(le.discount_usd, 0) / le.subtotal_usd
    ELSE NULL
  END AS quote_discount_pct
FROM line_enriched le
LEFT JOIN quote_rollup qr ON qr.quote_id = le.quote_id;

I’ll now draft the contact compliance gate view.

-- Feature view: quote_anomaly_features_vw
-- Purpose: Detect anomalous discounts/pricing at line and quote levels

WITH quotes AS (
  SELECT
    q.idcol                     AS quote_id,
    q.name                      AS quote_name,
    q.dateEntered               AS quote_created_at,
    q.dateModified              AS quote_last_modified_at,
    q.assignedUserId            AS owner_user_id,
    q.billingAccountId          AS account_id,
    q.opportunityId             AS opportunity_id,
    q.subtotalAmountUsdollar    AS subtotal_usd,
    q.discountAmountUsdollar    AS discount_usd,
    q.taxAmountUsdollar         AS tax_usd,
    q.shippingAmountUsdollar    AS shipping_usd,
    q.totalAmount               AS total_usd
  FROM AosQuotesVw q
  WHERE COALESCE(q.deleted, 0) = 0
),
quote_lines AS (
  SELECT
    l.idcol                     AS line_id,
    l.parentId                  AS quote_id,
    l.groupId                   AS group_id,
    l.productId                 AS product_id,
    l.partNumber                AS part_number,
    l.itemDescription           AS item_description,
    l.productQty                AS qty,
    l.productListPriceUsdollar  AS list_price_usd,
    l.productUnitPriceUsdollar  AS unit_price_usd,
    l.productDiscountUsdollar   AS discount_usd,
    l.productDiscountAmountUsdollar AS discount_amount_usd,
    l.productTotalPriceUsdollar AS line_total_usd,
    l.vatAmtUsdollar            AS vat_usd,
    l.vat                       AS vat_rate
  FROM AosProductsQuotesVw l
  WHERE COALESCE(l.deleted, 0) = 0
),
account_feats AS (
  SELECT
    a.idcol                     AS account_id,
    a.industry                  AS account_industry,
    a.accountType               AS account_type,
    a.rating                    AS account_rating
  FROM AccountsVw a
  WHERE COALESCE(a.deleted, 0) = 0
),
joined AS (
  SELECT
    q.*,
    af.account_industry,
    af.account_type,
    af.account_rating
  FROM quotes q
  LEFT JOIN account_feats af ON af.account_id = q.account_id
),
line_enriched AS (
  SELECT
    q.quote_id,
    q.owner_user_id,
    q.account_id,
    q.account_industry,
    q.account_type,
    q.account_rating,
    q.opportunity_id,
    q.subtotal_usd,
    q.discount_usd,
    q.tax_usd,
    q.shipping_usd,
    q.total_usd,
    q.quote_created_at,
    q.quote_last_modified_at,
    l.line_id,
    l.group_id,
    l.product_id,
    l.part_number,
    l.item_description,
    l.qty,
    l.list_price_usd,
    l.unit_price_usd,
    l.discount_usd           AS line_discount_usd,
    l.discount_amount_usd    AS line_discount_amount_usd,
    l.line_total_usd,
    l.vat_usd,
    l.vat_rate,
    CASE
      WHEN l.list_price_usd IS NOT NULL AND l.list_price_usd > 0
      THEN (l.list_price_usd - l.unit_price_usd) / l.list_price_usd
      ELSE NULL
    END AS line_discount_pct
  FROM quote_lines l
  JOIN joined q ON q.quote_id = l.quote_id
),
quote_rollup AS (
  SELECT
    quote_id,
    COUNT(*)                                                AS num_lines,
    SUM(line_total_usd)                                     AS lines_total_usd,
    AVG(NULLIF(line_discount_pct, 0))                       AS avg_line_discount_pct,
    MAX(line_discount_pct)                                  AS max_line_discount_pct
  FROM line_enriched
  GROUP BY quote_id
)
SELECT
  le.*,
  qr.num_lines,
  qr.lines_total_usd,
  qr.avg_line_discount_pct,
  qr.max_line_discount_pct,
  CASE
    WHEN le.subtotal_usd IS NOT NULL AND le.subtotal_usd > 0
    THEN COALESCE(le.discount_usd, 0) / le.subtotal_usd
    ELSE NULL
  END AS quote_discount_pct
FROM line_enriched le
LEFT JOIN quote_rollup qr ON qr.quote_id = le.quote_id;

Contact compliance gate view (SQL)​

-- View: contact_compliance_gate_vw
-- Purpose: Gate outreach and surface required compliance actions

WITH contacts AS (
  SELECT
    c.idcol                    AS contact_id,
    c.firstName                AS first_name,
    c.lastName                 AS last_name,
    c.title                    AS title,
    c.department               AS department,
    c.leadSource               AS lead_source,
    c.dateEntered              AS contact_created_at,
    c.dateModified             AS contact_last_modified_at,
    c.lawfulBasis              AS lawful_basis,
    c.dateReviewed             AS lawful_basis_reviewed_at,
    c.lawfulBasisSource        AS lawful_basis_source,
    COALESCE(c.doNotCall, 0)   AS do_not_call
  FROM ContactsVw c
  WHERE COALESCE(c.deleted, 0) = 0
),
account_links AS (
  SELECT
    ac.contactId               AS contact_id,
    ac.accountId               AS account_id
  FROM AccountsContactsVw ac
  WHERE COALESCE(ac.deleted, 0) = 0
),
accounts AS (
  SELECT
    a.idcol                    AS account_id,
    a.name                     AS account_name,
    a.industry                 AS account_industry,
    a.accountType              AS account_type
  FROM AccountsVw a
  WHERE COALESCE(a.deleted, 0) = 0
),
joined AS (
  SELECT
    ct.*,
    acc.account_id,
    a.account_name,
    a.account_industry,
    a.account_type
  FROM contacts ct
  LEFT JOIN account_links acc ON acc.contact_id = ct.contact_id
  LEFT JOIN accounts a        ON a.account_id  = acc.account_id
)
SELECT
  j.contact_id,
  j.first_name,
  j.last_name,
  j.title,
  j.department,
  j.lead_source,
  j.contact_created_at,
  j.contact_last_modified_at,
  j.account_id,
  j.account_name,
  j.account_industry,
  j.account_type,
  j.lawful_basis,
  j.lawful_basis_source,
  j.lawful_basis_reviewed_at,
  j.do_not_call,
  CASE
    WHEN j.do_not_call = 1 THEN 'BLOCK_OUTREACH'
    WHEN j.lawful_basis IS NULL OR j.lawful_basis = '' THEN 'NEED_LAWFUL_BASIS'
    WHEN j.lawful_basis_reviewed_at IS NULL THEN 'NEED_REVIEW'
    ELSE 'OK'
  END AS compliance_status
FROM joined j;

Do you want me to finalize with the implementation plan next?

-- View: contact_compliance_gate_vw
-- Purpose: Gate outreach and surface required compliance actions

WITH contacts AS (
  SELECT
    c.idcol                    AS contact_id,
    c.firstName                AS first_name,
    c.lastName                 AS last_name,
    c.title                    AS title,
    c.department               AS department,
    c.leadSource               AS lead_source,
    c.dateEntered              AS contact_created_at,
    c.dateModified             AS contact_last_modified_at,
    c.lawfulBasis              AS lawful_basis,
    c.dateReviewed             AS lawful_basis_reviewed_at,
    c.lawfulBasisSource        AS lawful_basis_source,
    COALESCE(c.doNotCall, 0)   AS do_not_call
  FROM ContactsVw c
  WHERE COALESCE(c.deleted, 0) = 0
),
account_links AS (
  SELECT
    ac.contactId               AS contact_id,
    ac.accountId               AS account_id
  FROM AccountsContactsVw ac
  WHERE COALESCE(ac.deleted, 0) = 0
),
accounts AS (
  SELECT
    a.idcol                    AS account_id,
    a.name                     AS account_name,
    a.industry                 AS account_industry,
    a.accountType              AS account_type
  FROM AccountsVw a
  WHERE COALESCE(a.deleted, 0) = 0
),
joined AS (
  SELECT
    ct.*,
    acc.account_id,
    a.account_name,
    a.account_industry,
    a.account_type
  FROM contacts ct
  LEFT JOIN account_links acc ON acc.contact_id = ct.contact_id
  LEFT JOIN accounts a        ON a.account_id  = acc.account_id
)
SELECT
  j.contact_id,
  j.first_name,
  j.last_name,
  j.title,
  j.department,
  j.lead_source,
  j.contact_created_at,
  j.contact_last_modified_at,
  j.account_id,
  j.account_name,
  j.account_industry,
  j.account_type,
  j.lawful_basis,
  j.lawful_basis_source,
  j.lawful_basis_reviewed_at,
  j.do_not_call,
  CASE
    WHEN j.do_not_call = 1 THEN 'BLOCK_OUTREACH'
    WHEN j.lawful_basis IS NULL OR j.lawful_basis = '' THEN 'NEED_LAWFUL_BASIS'
    WHEN j.lawful_basis_reviewed_at IS NULL THEN 'NEED_REVIEW'
    ELSE 'OK'
  END AS compliance_status
FROM joined j;
-- View: contact_compliance_gate_vw
-- Purpose: Gate outreach and surface required compliance actions

WITH contacts AS (
  SELECT
    c.idcol                    AS contact_id,
    c.firstName                AS first_name,
    c.lastName                 AS last_name,
    c.title                    AS title,
    c.department               AS department,
    c.leadSource               AS lead_source,
    c.dateEntered              AS contact_created_at,
    c.dateModified             AS contact_last_modified_at,
    c.lawfulBasis              AS lawful_basis,
    c.dateReviewed             AS lawful_basis_reviewed_at,
    c.lawfulBasisSource        AS lawful_basis_source,
    COALESCE(c.doNotCall, 0)   AS do_not_call
  FROM ContactsVw c
  WHERE COALESCE(c.deleted, 0) = 0
),
account_links AS (
  SELECT
    ac.contactId               AS contact_id,
    ac.accountId               AS account_id
  FROM AccountsContactsVw ac
  WHERE COALESCE(ac.deleted, 0) = 0
),
accounts AS (
  SELECT
    a.idcol                    AS account_id,
    a.name                     AS account_name,
    a.industry                 AS account_industry,
    a.accountType              AS account_type
  FROM AccountsVw a
  WHERE COALESCE(a.deleted, 0) = 0
),
joined AS (
  SELECT
    ct.*,
    acc.account_id,
    a.account_name,
    a.account_industry,
    a.account_type
  FROM contacts ct
  LEFT JOIN account_links acc ON acc.contact_id = ct.contact_id
  LEFT JOIN accounts a        ON a.account_id  = acc.account_id
)
SELECT
  j.contact_id,
  j.first_name,
  j.last_name,
  j.title,
  j.department,
  j.lead_source,
  j.contact_created_at,
  j.contact_last_modified_at,
  j.account_id,
  j.account_name,
  j.account_industry,
  j.account_type,
  j.lawful_basis,
  j.lawful_basis_source,
  j.lawful_basis_reviewed_at,
  j.do_not_call,
  CASE
    WHEN j.do_not_call = 1 THEN 'BLOCK_OUTREACH'
    WHEN j.lawful_basis IS NULL OR j.lawful_basis = '' THEN 'NEED_LAWFUL_BASIS'
    WHEN j.lawful_basis_reviewed_at IS NULL THEN 'NEED_REVIEW'
    ELSE 'OK'
  END AS compliance_status
FROM joined j;

User

yes

Cursor

I’m marking the compliance view as completed and moving on to draft the implementation plan.

Phase 1 implementation plan (2–3 weeks)​

  • Data layer

    • Create views: opportunity_features_vw, case_triage_features_vw, quote_anomaly_features_vw, contact_compliance_gate_vw.
    • Scheduling: daily materialization or incremental refresh keyed by dateModified.
    • Validation: row counts, null checks, schema contracts.

  • Modeling

    • Opportunity win: gradient boosted trees with stratified CV; label from salesStage.
    • Case triage: classification on high/low urgency using priority proxy; text embeddings from description/workLog.
    • Quote anomaly: isolation forest or robust z-scores per product/account; owner-aware thresholds.

  • Serving

    • Batch scoring nightly to new tables: opp_win_scores, case_triage_scores, quote_anomaly_flags.
    • APIs to fetch latest scores per idcol.
    • UI surfacing: badges on opportunity list, case queue sorting, quote approval banners.

  • Governance

    • Compliance gate: enforce contact_compliance_gate_vw.compliance_status in outreach automations.
    • Monitoring: model drift (PSI), data quality checks, feedback capture.

  • Prerequisites

    • Confirm stage names for won/lost mapping.
    • Verify DB dialect for DATE_DIFF/casts; adapt functions accordingly.
    • Identify fields for email/phone existence if needed.
    • Collect historical won/lost outcomes for at least 6–12 months.

  • Deliverables

    • SQL view definitions checked into repo.
    • Feature specs and data dictionary.
    • Initial models + scoring jobs + dashboards.

Would you like me to package these SQL views into migration files for your DB dialect and stub the nightly scoring tables?

User

no

Cursor

Marking the implementation plan complete and wrapping up.

  • Completed: schema fetch, workflow analysis, table/field mapping, SQL feature views for opportunities, cases, quotes, and contact compliance, plus a concrete Phase 1 implementation plan.
  • You can now execute the four SQL views in your warehouse, then train and schedule batch scoring as outlined.