Skip to main content

Identify sensitive data in SuiteCRM schema

User

Analyze the SuiteCRM database schema to identify columns that contain sensitive data, PII, confidential business information, or data revealing sensitive personal information

Cursor

Scope​

Below is a concise, security-focused review of SuiteCRM’s provided schema, highlighting columns that are likely to hold sensitive data: PII, SPI (sensitive personal information), credentials/tokens, confidential business data, or free-text fields that may incidentally contain such data.

High‑risk credentials, secrets, tokens​

  • Users and auth:
    • users.user_hash, users.authenticate_id, users.is_admin, users.factor_auth
  • Email systems:
    • inbound_email.email_password
    • outbound_email.mail_smtppass
    • outbound_email.signature, outbound_email.signature_html
    • eapm.password
  • OAuth/OIDC:
    • oauth2clients.secret, oauth2clients.redirect_url, oauth2clients.allowed_grant_type
    • oauth2tokens.access_token, oauth2tokens.refresh_token, oauth2tokens.*_expires, oauth2tokens.token_is_revoked
    • oauth_tokens.access_token, oauth_tokens.refresh_token, oauth_tokens.secret, oauth_tokens.verify, oauth_tokens.callback_url, oauth_tokens.token_ts, oauth_tokens.consumer
    • external_oauth_connections.client_id, external_oauth_connections.client_secret, external_oauth_connections.access_token, external_oauth_connections.refresh_token
    • external_oauth_providers.client_id, external_oauth_providers.client_secret, external_oauth_providers.scope, external_oauth_providers.*_mapping, external_oauth_providers.*_options
  • Other secrets/keys:
    • email_addresses.confirm_opt_in_token

Contact info and direct identifiers (PII)​

  • Names and identifiers across core entities:
    • contacts.* (first_name, last_name, salutation, title, photo, department, reports_to_id, joomla_account_id)
    • leads.* (first_name, last_name, salutation, title, photo, department)
    • prospects.* (first_name, last_name, salutation, title, photo, department)
    • accounts.name, contacts_users.*, users.user_name, users.first_name, users.last_name, users.photo
  • Phones:
    • *_phone_* across contacts, leads, prospects, users, accounts (e.g., phone_mobile, phone_work, phone_fax, etc.)
  • Email addresses and relationships:
    • email_addresses.email_address, email_addresses.email_address_caps
    • email_addr_bean_rel.* (links emails to entities; primary_address, reply_to_address)
    • emails.* (name, message_id, uid, from/to status fields)
    • emails_text.from_addr, emails_text.reply_to_addr, emails_text.to_addrs, emails_text.cc_addrs, emails_text.bcc_addrs
  • Physical addresses:
    • *_address_* in contacts, leads, prospects, accounts (primary/alt/billing/shipping street, city, state, postal code, country)
    • users.address_*
    • fp_event_locations.* (address, city, state, country, postalcode)
  • Location and geo:
    • jjwg_* tables and *_cstm fields: jjwg_maps_lat, jjwg_maps_lng, jjwg_maps_address_c, lat, lng, coordinates
  • Birthdates and demographics:
    • contacts.birthdate, prospects.birthdate
  • Online IDs:
    • meetings.join_url, meetings.host_url, meetings.displayed_url, meetings.external_id
    • users.messenger_id, users.messenger_type
    • emails.mailbox_id, emails.uid
    • folders.assign_to_id
  • Consent/communication preferences (PII/PD):
    • contacts.do_not_call, leads.do_not_call, prospects.do_not_call
    • email_addresses.opt_out, email_addresses.invalid_email, email_addresses.confirm_opt_in*
    • users.receive_notifications

Sensitive personal information (SPI) and behavioral​

  • GDPR/consent fields:
    • contacts.lawful_basis, contacts.date_reviewed, contacts.lawful_basis_source
    • Similar fields in leads, prospects
  • Behavioral/usage tracking:
    • tracker.* (user_id, session_id, module_name, action, item_id, date_modified)
    • campaign_log.* (activity_type, activity_date, related_id/type, hits, target info)
    • emailman.* (campaign/list IDs, send attempts, related_confirm_opt_in)
  • Notes and free text (may contain SPI/PII incidentally):
    • notes.description
    • cases.work_log, bugs.work_log, cases.description, bugs.description
    • sugarfeed.link_url, sugarfeed.link_type, description
    • reminders_invitees.* linking people to reminders
    • meetings.location, meetings.description
    • calls.description, calls_reschedule.reason
    • emails_text.description, emails_text.description_html, emails_text.raw_source
    • documents.* names/urls; document_revisions.* filenames/mime
    • Many MEDIUMTEXT across modules (reports/workflows/templates) should be treated as potentially sensitive.

Confidential business information (CBI)​

  • Financials and pricing:
    • opportunities.amount, opportunities.amount_usdollar, currency_id, probability, sales_stage, date_closed
    • aos_quotes.* totals, discounts, taxes, shipping, currency, expiration, opportunity_id
    • aos_invoices.* totals, taxes, shipping, currency, number, quote_number/date
    • aos_contracts.* totals, currency, contract dates, parties
    • aos_products.cost, aos_products.price, aos_products.cost_usdollar, aos_products.price_usdollar
    • aos_products_quotes.* pricing, discounts, totals, VAT, currency
    • aos_line_item_groups.* totals/taxes/currency
    • fp_events.budget, project.* status/priority/timing, project_task.* efforts/durations
  • Company and market details:
    • accounts.annual_revenue, accounts.employees, accounts.sic_code, accounts.rating, accounts.ownership, accounts.ticker_symbol
    • campaigns.* (budget, expected_cost, actual_cost, expected_revenue, objectives, content)
    • releases.*, upgrade_history.* (can leak tooling/versions)
  • Contracts, documents, legal:
    • aos_contracts.* (start/end dates, values, parties)
    • documents.*, document_revisions.*, linked_documents.*
  • Product and sales operations:
    • aos_product_categories.*, aos_products.*, aos_pdf_templates.*
    • projects_* relation tables (deal structures), projects_products, projects_opportunities

Health/special category data​

  • Not explicitly present as dedicated health fields, but free-text fields (cases, notes, emails, surveys) could incidentally contain such data.

Free‑text fields likely to hold sensitive data (audit/log/config content)​

  • Any MEDIUMTEXT or large text fields across modules (examples, non-exhaustive):
    • *_audit.before_value_text, *_audit.after_value_text
    • description, content, objective, work_log, module_path, parameters, raw_source, manifest, contents, signature_html
    • Reports/Workflows/Templates: aor_*, aow_*, email_templates.*, aos_pdf_templates.*
    • fields_meta_data.* (could reference custom fields of a sensitive nature)
    • user_preferences.contents, folders.dynamic_query, job_queue.message, job_queue.data

Other privacy-impacting relational/behavioral fields​

  • Attendance/invite status:
    • meetings_*, calls_*, fp_events_* relations with invite_status, accept_status, email_responded
  • Role/group memberships and access control:
    • securitygroups_*.*, roles_*.*, acl_roles_*.*, acl_actions.*
  • Survey data:
    • surveyquestionresponses.answer, answer_datetime, surveyresponses.happiness
  • Keys/IDs that can link individuals to activities:
    • Widespread assigned_user_id, created_by, modified_user_id, parent_id, related_* across tables

Prioritized remediation suggestions (quick wins)​

  • Encrypt at rest:
    • Credentials/tokens/secrets, passwords, OAuth data, email passwords, SMTP creds.
  • Minimize access and mask in UI/exports:
    • Phones, emails, addresses, birthdates, geolocation, consent fields, behavioral logs.
  • Tighten retention:
    • tracker, campaign_log, email caches, audits, raw email sources.
  • Scrub/redact free-text on export and logs:
    • MEDIUMTEXT fields in notes, cases, emails, audits, surveys.
  • Strengthen consent handling:
    • Treat lawful_basis*, do_not_call, opt_out as protected; enforce policy gates.

If you want, I can output a CSV mapping of table.column to sensitivity category to drive DLP rules or masking.